Comments by JohnLBevan

JohnLBevan 5-May-11 6:13am View

Thanks Silim. Sadly I have no control over the DB used in France, and there would be a huge refactoring exercise if they did change over. On a similar theme, we have played with the idea of setting up sybase as a linked server to allow a stored proc in SQL to send the data direct to Sybase. The issue there is we only have x86 providers for Sybase, and our SQL install is x64, so it wouldn't be able to see the DSN.

JohnLBevan 5-May-11 6:10am View

Hey John, thanks for the suggestion. I like this idea, but feel it will be a little too complex. My aim is to keep the solution simple to make it easier to support and resolve errors. The Sybase machine is hosted on Unix, so playing with binary files between windows and this OS scares me; i.e. if something went wrong, I wouldn't know where to start looking for the issue. It's a good suggestion for anyone more comfortable with those technologies though.

JohnLBevan 5-May-11 6:06am View

Thanks Karthik. Unfortunately sp_executesql does not exist in Sybase, though exec() does. I had a play based on this idea and came up with some code to dynamically generate SQL to insert the data in batches, using single statements in the form:
insert destinationTable (col1, col2) select r1c1val, r1c2val union select r2c1val, r2c2val;
This works well for sending 100 records at a time, and gives a significant boost over sending the records one at a time (over 10 times faster). However, when I tried to send 1000 records at a time I received an error back from sybase due to insufficient stack space, so clearly this uses up a lot of resources on the DB side.

Comments by JohnLBevan (Top 3 by date)