SQL Server 2005 native client ODBC query

[vainstah]

Hello Guys,

I am writing a high performance win32 telemetry midleware service which uploads to SQL server in bulk frequently. I know how to do simple things in ODBC native-client.

ODBC native-client documentation is terse to say the least. I would like to know from people who have used the native client bulk upload feature what the behaviour of a bulk upload is when one of the rows causes a server-side constraint error.

Specifically:

• Do the drivers respond with the row that fails,

• if it does, do previously inserted rows get rolled back or can you remove the erroneous row and resume the bulk upload?

If the above doesn't make sense should I look at the ole DB api. It looks like it has higher levels of abstraction as other COM api's it looks horendously bloated and terse.

[rkeith27]

If you are talking about the Bulk Insert utility, I imported the data to a temp table (every field varchar(200)) and then I validated the data in the temp table before moving it into production tables. If no errors occurred, I deleted the temp table record. Finally, I moved to the next record and repeated until I was done.

Hope this helps.

[vainstah]

Hello rkeith27,

The method you mention is one way of doing it, however the ODBC(and OLE DB) drivers that come with ms-sql-server 2005 and above support bulk inserts natively. It supports the method you mention above programaticall(i,e. Without having to call the BCP utility).

Additionaly though, it supports building the rows code side by binding memory locations to columns with bcp_bind and then calling bcp_sendrow (rinse and repeat).

At appropriate points one calls bcp_batch to commit the results sent so far or bcp_done if your done. these functions can return -1 indicating an error. From this error I want to know what row caused the error so I can remove it.

I would hope these semantics are exposed by the API.But they are not hinted to anywhere I can see.

[vainstah]

Hi Guys,

I sent this to the microsoft ODBC native client team :

I am writing an application that needs to perform bulk uploads. I want to go down the native client, program variable binding-bulk upload method.

However some of the tables I am inserting into have constraints.I need to detect any errors caused and prune the erroneous rows from my result set for application side logging.

bcp_done and bcp_batch just return -1 (according to the API) and thats just about all it says about errors and bulk uploading. I am sure you guys have accounted for this.

Do I need to go down the route of creating flat-constraint-less tables to stage my bulk uploads into and then call a stored procedure to make the insert and return the rows that failed ?

Help me out here, I am lost.

And got this response :

I believe you can get detailed error information using SQLGetDiagRec ODBC APIs once you see a failure happen by using the same HDBC that you used for performing the bulk operation. However, the catch is that once a constraint violation is detected by the server the whole batch is aborted meaning none of the rows in the batch are inserted. A low performance approach in this case could be to just have 1 row batches & on failures assume that the row caused a constraint violation (after verifying with SQLGetDiagRec calls that indeed that was the problem. There is no way for the server to ignore some type of constraints like primary key/nullness/uniqueness etc. The batch containing the violating row will be aborted & you will not be able to detect exactly which row had a problem.



The approach of doing a constraint-less insert and then filtering out the violating rows makes more sense in your scenario.

so I guess i'm gonna have to hack together a solution along the lines of what rkeith mentioned but without using the external bcp utility or varchars for the fields. :D