w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » SQL SERVER » Page 1
SSRS report to email subscription only when there is data
The workaround we use for this problem is kind of silly, but very effective. Add a row count check at the beginning of your code like: IF (SELECT COUNT(X) FROM TABLES)>0 BEGIN RAISERROR ('No Rows to Report',2,1) END The error will halt execution of the subscription.

Categories : Sql Server

most recent values entered for a column in SQL Server 2012
try to use sql server IMPORT tool. that may help you do your conversion with minimum work. You may find Imports in : Right click on your DB in SQL Server/ Select Tasks/ Select Import Data

Categories : Sql Server

Count block of time
do a count on your case statements then groupy by whatever you need, this will give you a count on sessions since you didnt really let us know how to differenciate between visits and sessions CASE WHEN DATEPART(hour, min(ApptStart)) BETWEEN 0 AND 12 THEN count(distinct appointmentid) END AS 'MORNING_SESSION', CASE WHEN DATEPART(hour, min(ApptStart)) BETWEEN 13 AND 17 THEN count(distinct appointme

Categories : Sql Server

Full-text search does not find some prefixes
Thank you for posting this question Mathias. I just wanted to point out the suggestion from Microsoft (via https://connect.microsoft.com/SQLServer/feedback/details/1032815) was to switch to the format SELECT * FROM [Profile].[DocumentView] WHERE FREETEXT(Content, '"Friedensein*"') While this doesn't help Mathias, it was able to help me out and might help the reader. In my case, I had a pro

Categories : Sql Server

Custom Data Service Provider (WCF Data Service) for a dynamic table in MS SQL Server
I tried this about half a year ago (Web API OData 2). What I did was to create the EDM model on the fly for every request and set it in a custom OData Route constraint. In the controller I returned EdmEntityObjects of the type corresponding to the current request. I expected that returning an IQueryable would do all the magic of filtering, but it failed saying that the operation is not supported.

Categories : Sql Server

Insert statement conflicted with the Foreign key
The first column in the insert list is Quantity but you select cost price which is the 2nd column in the insert list. You are inserting Quantity as Production_ID causing the FK violation. Change to: insert into [pp dw].dbo.Dimfact(Quantity,costprice,Prod_ID,MaterialQuantity,Production_ID) SELECT Production_Detail.Quantity, [cost price], Production_Detail.[ProdDetail ID], [Mater

Categories : Sql Server

Pentaho Spoon Tool Transformation Order
Inserting/updating the same database in parallel, i am afraid will throw you an error (like you have done). please try to separate these transformations into multiple ktr files. I assume you will not face the same issue again. Hope it helps:)

Categories : Sql Server

BIDS 2008 - Lookup value in source against two columns in lookup table
You can use a union as the source in the lookup transformation, then use a single transform: SELECT id, column_a FROM dbo.Table UNION SELECT id, column_b from dbo.Table Conversely, you can use two lookups with the first looking at column_a and pass the no match output to a second lookup with column_b, then union the results.

Categories : Sql Server

Find duplicate values in SQL Server 2008
There is a similar question here about address standardizations using soundex in SQL Server SQL address data is messy, how to clean it up in a query? The relevant function is soundex() http://msdn.microsoft.com/en-us/library/ms187384(v=sql.100).aspx

Categories : Sql Server

If an error occurs in my stored procedure, will a cursor local to that procedure get cleaned up?
Based on the very useful comment from SubqueryCrunch, I've empirically proved that the cursor gets cleaned up (without all the transaction stuff), at least on SQL Server 2012 with the options we're using: CREATE PROCEDURE tjtemp AS BEGIN DECLARE @foo TABLE ( id INT ) DECLARE @id INT INSERT INTO @foo (id) VALUES (1), (2), (3), (4), (

Categories : Sql Server

FOR XML PATH - how to implement grouping?
The code: select '' as "OrderResponse-Lines/Line/Line-Item/ItemDescription", '' as "OrderResponse-Lines/Line/Line-Item/ItemStatus", '' as "OrderResponse-Lines/Line/Line-Item/ItemType" for xml path(''), root('Document-OrderResponse'), type; results in: <Document-OrderResponse> <OrderResponse-Lines> <Line> <Line-Item> <ItemDescription><

Categories : Sql Server

IIF to Case statement
IIf in JET SQL translates to CASE WHEN in SQL Server as follows: IIf(condition, whenTrue, whenFalse) translates directly to CASE WHEN condition THEN whenTrue ELSE whenFalse END For nested expressions IIf(condition1, whenTrue1, IIf(condition2, whenTrue2, whenFalse)) you can either translate them directly: CASE WHEN condition1 THEN whenTrue1 ELSE CASE WHEN condition2

Categories : Sql Server

Ihow to merge two flat files have different data type in ssis
Since your 2 sources have different columns, you could try to use Derived Column transforms to create additional columns. This will make the number of columns equal for all 2 sources. Next, you could use a Union All transform to combine input from all sources, and then write to your flat file.

Categories : Sql Server

Join two tables on matching ID?
Rewrite your query to this: SELECT DENumber, AcquiredDate, ItemDescription, ItemName, LocationName FROM dbo.Assets INNER JOIN dbo.Locations ON Assets.LocationId = Locations.LocationId WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) >= 6 To clarify: In a JOIN, you must specify the names of both tables on either side of the JOIN keyword. Then, you specify the join criteria after the

Categories : Sql Server

SQL Pivot IF EXISTS
Try this. SELECT custid, Max(CASE WHEN value = 'a' THEN 'YES' else 'NO' END) A, Max(CASE WHEN value = 'b' THEN 'YES' else 'NO' END) B, Max(CASE WHEN value = 'c' THEN 'YES' Else 'NO' END) C FROM <tablename> GROUP BY CustID

Categories : Sql Server

how to insert data to one table from 2 different tables?
As I didnt get any common column in both table here the query insert all rows from two table separately. INSERT INTO NEWTABLE (patientUid,PracticeUid) SELECT patientUid, NULL FROM PatientTable UNION ALL SELECT NULL, PracticeUid FROM IndividualTable If there exist a relation. Then we can use joins. INSERT INTO NEWTABLE (patientUid,PracticeUid) SELECT A.patientUid, B.PracticeUid

Categories : Sql Server

Compare when one field is empty but concatenation is to be compared
Use NOT EXISTS rather than NOT IN. It allows you to use more than one field for comparison when doing the exclusion: SELECT * FROM tbl_Students AS s WHERE NOT EXISTS ( SELECT 1 FROM tbl_Exclusion AS e WHERE e.Class = s.Class AND (e.Section IS NULL OR e.Section = s.Section) ); WORKING EXAMPLE CREATE TABLE #tbl_Students (Na

Categories : Sql Server

SQL Server : trigger AFTER INSERT,UPDATE
Try changing your sub-query to JOIN. Something like this. UPDATE A SET idWork = b.idWork FROM [HOURS] A JOIN (SELECT U.idWork, i.id FROM [USER] U JOIN inserted I ON U.id = i.idUser) B ON a.id = b.id Or UPDATE A SET idWork = b.idWork FROM [HOURS] A JOIN inserted I ON a.id

Categories : Sql Server

Get Miliseconds and NanoSeconds in SQL Server
You are trying to get result in milliseconds for a long period of date and that caused the error. If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years. Source : http://msdn.micros

Categories : Sql Server

SQL Query to Calculate the Rolling Difference by Date
Try this. Use correlated sub-query to find rolling difference CREATE TABLE #tem (SKU INT,DataDate DATETIME,Web_qty INT) INSERT #tem VALUES( 2,'2014-11-17 00:00:00',404), (2,'2014-11-18 00:00:00',373), (2,'2014-11-19 00:00:00',1350), (66,'2014-11-17 00:00:00',3624), (66,'2014-11-18 00:00:00',3576), (66,'2014-11-19 00:00:00',3570), (67,'2014-11-17

Categories : Sql Server

SQL Script to replace all FK values based on the dependencies? Is it even possible?
Yes, if you can first create a new user and after inserting , update the reference based on condition. like declare @newUserid int , @olduserid insert user values (colval1, colval2,..) select @newUserid = @@identity update tablename set userid = @newUserid where userid = @olduserid if multiple user, then create a dynamic query for update statement. Declare @query nvarchar(max) Set @query =

Categories : Sql Server

How to load data from oracle and sql server to HAWQ using Spring XD
You need to integrate sqoop jobs with Spring XD. See link below for sqoop jobs with springxd https://github.com/tzolov/spring-xd-sqoop-job

Categories : Sql Server

Execution Plan in SQL Server?
Shitty statistics which simply do not match reality because they are outdated. Complex queries where the math behind assumptions goes wrong. Statistics entries are always simplified (A histogram) and some values may not fit into it. Both can have part in it.

Categories : Sql Server

Null values stored in table via Stored Procedure
Your code reads SELECT @PasswordOld = Password,@Type=ClientType,@IP=IPAddress FROM Clients WHERE Username = @userid Wouldn't this mean that no row will be returned for a Username that does not exist? So, the values for ClientType and IPAddress will not get populated and will remain NULL, which would be the expected functionality. However, if you want to store some value, or these fi

Categories : Sql Server

how to do instr in tsql join
Try something like this. But this is not a efficient code. SELECT DISTINCT a.fullname, 'Matched' [status] FROM (select distinct fullname from huge_table) a JOIN tiny_table b ON a.fullname LIKE '%' + namepart1 + '%' AND a.fullname LIKE '%' + case when len(namepart2)=0 then ' ' else namepart2 end + '%' AND a.fullname LIKE '%' + namepart3 + '%' UNION

Categories : Sql Server

I cant figure out how to expand my code
You can do it like this: Dim stmt As String = "SELECT * FROM members WHERE members.User = '" & txtUser.Text & "'" if Not txtPC.text.trim.length = 0 then stmt = stmt & " AND members.PC = '" & txtPC.Text & "'" End If if Not txtIP.text.trim.length = 0 then stmt = stmt & " AND members.IP = '" & txtIP.Text & "'" End If if Not txtAsset.text.trim.length = 0 then

Categories : Sql Server

SQL Error transfer one field to another database and table
If you haven't already, backup your database(s) before trying this code. It is untested: update DMSTAG.dbo.TEMP_AKAUN_MARA set new_TarikhTamatPengajian = ( select TOP 1 try_convert(datetime,LEFT([CRSE_EDTE], 4)+'-'+RIGHT([CRSE_EDTE],2)+'-28',111) as new_CRSE_EDTE FROM [SMBM_DBPELJ].[DBPELJ].[MSTUMASTER] where DMSTAG.dbo.TEMP_AKAUN_MARA.STUDENTN = [SMBM_DBPELJ].[DBPELJ].[MSTUMASTER].[STUDENTN] co

Categories : Sql Server

SQL - Setting a Default Value from Trigger
You have to handle the Update trigger instead of the delete trigger, because you need to catch when dno field is being UPDATED. Here is the code: CREATE TRIGGER empdeptfk_update ON employee INSTEAD OF UPDATE AS BEGIN IF UPDATE(dno) BEGIN DECLARE @DNO INT SELECT @DNO = dno FROM Inserted IF @DNO IS NU

Categories : Sql Server

Displaying results based on different selection in sql server stored procedure
you are setting default value to empty string and comparing with NOT NULL as empty string is NOT NULL, search is done for empty string. First part to get results when only filters condition is met SELECT Id,Name,Work,YEAR,Rep from #SalesRepVisit WHERE ( @RepCode ='' OR Rep = @RepCode ) AND ( @Name ='' OR Name = @Name ) AND ( @Work ='' OR Work = @Work ) AND ( @Year ='

Categories : Sql Server

Creating a new SQL Table with data from existing
What this most likely means is that the original table has duplicates, that is, titles.pub_id is currently not a unique list. That is, you're trying to make it unique, and it is not. To confirm this, run this query: SELECT titles.pub_id, COUNT(*) As CountForPub FROM titles GROUP BY titles.pub_id ORDER BY CountForPub DESC LIMIT 20 If you see any pub_id's that have the count greater than 1, yo

Categories : Sql Server

Best SELECT syntax performance with 5 parameter
Second approach is better, you can simplify it with or condition based on parameter value SELECT * FROM Table1 WHERE (@param1 ='' or Column1 like '%'+ @param1 +'%' ) AND (@param2 ='' or Column2 like '%'+ @param2 +'%' ) AND...

Categories : Sql Server

Removing duplicates based on systemID, primary key and email
Try this: use tempdb create table temp( id int, email varchar(100), systemId varchar(100) ) insert into temp select 1,'1@email.com',null union all select 2,'1@email.com','systemID2' union all select 3,'2@email.com',null union all select 4,'2@email.com',null union all select 5,'3@email.com','systemID3' union all select 6,'1@email.com',null ;with cte as( select *,

Categories : Sql Server

change display of rows into column using PIVOT in sql server
Use the PIVOT function: WITH Data AS ( -- this is your actual query --select d.disposition, count(a.serialnumber) AS SUBTOTAL --FROM FADB a, FARepair b, Disposition d --WHERE a.ID = b.ID AND b.DispositionID = d.DispositionID --GROUP BY d.Disposition -- this is a sample just to make up the example SELECT * FROM ( VALUES ('a', 45), ('b', 100), ('c', 39)

Categories : Sql Server

SQL Server : merge statement error
SOURCE is a keyword used in MERGE. i.e. WHEN NOT MATCHED BY SOURCE. The error is in this line USING temp_table_inventory as SOURCE Change the name to Src

Categories : Sql Server

SSIS 2008 - Script Component - Microsoft.SqlServer.Dts.Pipeline.Wrapper - doesn't contain any public member or cannot be found
check if all the refernces are there use namespaces like this : using Microsoft.SqlServer.Dts.Runtime; using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper; using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

Categories : Sql Server

MS SQL Server select statement: display all combinations from two tables
If you don't have a departments table, you'll need to create a subquery to get the distinct list of dept_ids to cross join on: select emp_id, first_name, last_name, dept.dept_id, status from empl cross join (select distinct dept_id from empdept) dept left join empdept on empl.emp_id = empdept.empt_id and dept.dept_id = empdept.dept_id SQL Fiddle Demo

Categories : Sql Server

ms access linked table missing primary key
The second question you asked about why TRUE is represented as -1 has been asked before. It's the same convention as used in BASIC. TRUE is equal to NOT FALSE. If FALSE is represented with a zero in 16 bits, and if NOT is the bitwise complement, then TRUE is represented by 16 bits set to one. 16 bits set to one, in twos complement notation, is equal to minus one.

Categories : Sql Server

SQL Server UniqueIdentifier as FK
I do believe the following should do the trick (in the case that you're wanting to set up CustomerUsers.CustomerUniqueID as a FK) ALTER TABLE customerusers ADD CONSTRAINT fk_customerusers_users FOREIGN KEY (customeruniqueid) REFERENCES customers(uniqueid) what I changed: inferred that error was related to column not existing, noticed that you had already created a column which looked like

Categories : Sql Server

SQL Server 2012 INNER JOIN returning records that it shouldn't
I would use EXISTS in this case: select T1.ID ,T2.F2 ,T2.F3 ,T2.F4 from A2 T2 where T2.F2 not in (V1,V2,V3,V4) and T2.F3 in (V5,V6,V7,V8) and exists( select * from A1 T1 where T1.ID = T2.ID) This is much simplere and will give you only those records in T2 that have a matching record in T1.

Categories : Sql Server




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.