DISTINCT over Linked Server

Recently faced an issue where the destination table in a DB2 database had unique index and out insert from SQL Server using a linked server was failing. 

Even after using DISTINCT we received the same error:

OLE DB provider "MSDASQL" for linked server "SBLLS" returned message "[IBM][CLI Driver][DB2/XXXXX] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "46" constrains table "MYDB.S_DESTINATION_UPDATE" from having duplicate values for the index key.  SQLSTATE=23505

Then realized that DISTINCT clause hass no effect when inserting data over a linked server. Immediately we switched our approach to insert DISTINCT data into a temporary table and then execute the insert into DB2 from SQL Server.

Learned something new....

Multiple Identity columns in one table error

Multiple identity columns specified for table 'Foo'. Only one identity column per table is allowed.

As the error message above suggests, only one identity column per table. Incase you need multiple sequences in the table then create table with one identity column, insert data and then update the remaining fields (that need to be a sequence) with the identity column values.
Google saved me...

Quick Tip #3

Always create a out file using the -o switch in you BCP command to a local drive where SQL Server instance is running. That way you can guarantee the log file getting created without any issues.

Example: You are trying to BCP out a file on a shared network drive and also writing the log file to the same location or a different shared network location where all logs for you applications exists. In case of network failure, directory write permission changes, unexpected outage the data file wont be created and even the log file. To create the log file it needs to be created on a local drive so all errors can be captured and required teams can be alerted of the failure.

BCP Out Error

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

The service account did not have permissions to write to the shared network directory. Also learned the UNC (Universal Naming Convention) is enough for SQL Server to identify the location to write the file to.

If you map the drive in Windows there is a chance that you still wont be able to write to the desired location. It needs to be mapped at SQL Server level and not Windows.

One more way to confirm your BCP Out job/script is working is it should run from command line if you have access to write to the shared network directory.

Google is my Friend....

Quick Tip #2

Important cursor system tables: master..syscursortables and master..syscursors

Can be used to check any open cursors in the database and other cursor related information.

Runtime Error: Arithmetic overflow error converting expression to data type int.

Actually the script i was testing did not have any computation nor any calculations (is there any difference?)

It was a simple cursor retriving data from temp table and updating a physical table in the database. I had declared variable to hold value from the cursor and were defined with INT datatype.

The cursor data was definitely not longer then 20 numeric characters, but i was still getting this error. Since i was in a hurry to complete the task at hand, i changed the data type to varchar(20) and proceeded.