Friday, September 27, 2013

[T-SQL] Duplicate key was ignored warning returned even when no duplicates are found

[T-SQL] Duplicate key was ignored warning returned even when no duplicates are found

Duplicate key was ignored warning returned even when no duplicates are found

Posted: 26 Sep 2013 08:39 PM PDT

Hi I am having problems with the "Duplicate key was ignored" warning message. The problem is that the message seems to happen randomly and cannot be reproduced. If i take the same set of data and run the stored procedure that causes the problem i don't get the warning message a second or subsequent time. Also all the SELECT statements have criteria set to remove duplicates before they are inserted into the tables.[b]Background to the problem:[/b]I have a data feed that pulls data from a DB2 database to a SQL Server 2008 staging table as a flattened set of records. A stored procedure in SQL Server is run to load the data into the destination tables. The data feed is run hourly for new and updated records in DB2 Monday-Friday 09:00-17:00 and then there is a midnight run of all the records going back for the last 12 months.The data feed was originally sent from DB2 as a CSV file and pulled into SQL Server using SSIS but is now an Informatica workflow that pulls the data directly from DB2. It is the Informatica workflow that is returning the "duplicate key was ignored" warning message and this stops the workflow. The workflow is restarted and the data is always loaded the second time without the warning message. The warning does not happen every time the workflow is run - it can run for a number of days with no warnings and then one will come throughI can see in Profiler that it is SQL Server that returns the Duplicate key was ignored warning message so it is not an issue with Informatica. I cannot reproduce the problem to get to the root cause of the issue. I would expect that if i run the same set of data through the stored procedure i would get the warning message every time, but this is not the case. Even when i step through the stored procedure i do not get the message. As the midnight data feed returns the records from the last 12 months, so by definition would include duplicates, the warning message only appears randomly and is not consistent.Is this a bug in SQL Server or does anyone have a suggestion of where else to look?

ORDER BY clause based on values IN ()

Posted: 26 Sep 2013 10:26 AM PDT

Hi all,So my client want to run a SQL query to list data along the lines of SELECT field1, field2, field3 FROM table WHERE field1 IN (83944, 83955, 83954, 83951,83947, 83946, 83953)The output needs to be in the same order that the numbers are in the IN clause, in other words 83944, field2, field383955,field2, field383954, field2, field383951, field2, field383947, field2, field383946, field2, field383953, field2, field3There is no other ordering based on another field possible. The client is manually typing in those numbers into the IN in the specific order he wants the output.Is there some way that SQL knows the order of each element in the IN brackets?I know I could write some fairly simple T-SQL with a temp table and an identity field. Then do an INSERT INTO #TempTable.SELECT * FROM #TempTable ORDER BY IdentityFieldBut looking for another way maybe?

Data not loaded in email?

Posted: 26 Sep 2013 08:00 PM PDT

HiResult set is not loaded in Email body, what could be worng in this script?[code="sql"]declare @results varchar(max)select MACHINE, COUNT(*) as Instance into #tableAfrom LIMSPROD.dbo.CM_INSTANCE group by MACHINESelect COUNT(*)/4 as InstanceA into #tableBfrom LIMSPROD.dbo.CM_INSTANCESelect A.MACHINE, A.Instance, B.InstanceA, (B.InstanceA-A.Instance) as Diff into #resultsfrom #tableA A, #tableB Bwhere (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3-- number of records to not be zeroIf ((select count(*) from #results) <> 0)BeginSET @Results = 'select * from #results'EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',@recipients='ananda.murugesan@xyz.com',@subject='Alert!-Verify Instance',@query=@ResultsEnddrop table #tableAdrop table #tableBdrop table #results[/code]Error msg in job historyNT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.Thanksananda

Query that returns only columns with data

Posted: 26 Sep 2013 08:07 AM PDT

I have a table with a large amount columns. Instead of trying to manually go thru the table and determine which columns have data. Is there a way to make a select that will return only the columns with data in them (omitting any columns w/out data). Without data, I am referring to all nulls or empty strings, etc.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog