0

After search through google I came to know that the SQLSRV32 odbc driver do not support MARS.What are the workarounds for this. One way i guess is stop loop through the results of several SQL commands. But in my case i have to create 30-40 table and insert about 400-500 rows of data at a time. Is it a good idea to open and close connection for every single sql commands.Please Help

arjun
  • 625
  • 10
  • 27

1 Answers1

1

Don't open and close connection for each statement, open the connection and create multiple commands to use that one connection. Inserting ~15,000 records shouldn't take too long. I don't know if ODBC has support for it, but you can also look into SQL Server's Bulk Copy functionality to do something like this.

A final word about MARS. MARS only matters when you want to have multiple simultaneous queries on the same connection that are returning result sets. That isn't really an issue here as you are doing inserts.

Also, there isn't anything stopping you from running multiple threads to do the inserts. I would do perhaps one thread per table, with a thread for each core. Parallel.ForEach could help out here.

Darren Kopp
  • 76,581
  • 9
  • 79
  • 93
  • +1 for the first two paragraphs. But is it really safe to use a _single_ (ODBC) connection from multiple threads? I wouldn't have imaged that. – Christian.K May 04 '12 at 05:45
  • I got this error 'Connection is busy with results for another command'. i have OdbcCommand for dropping table , creating table ,and inserting data on loops. There is absolutely no problem with sql native client drivers with mars enabled but except with sqlsrv32 – arjun May 04 '12 at 06:17
  • Is MARS related only to retrieval of resultset and not with insert and update – arjun May 04 '12 at 07:03
  • No, don't use a single connection with multiple threads. Use multiple connections with multiple threads. But if you use one thread, then use one connection and keep it open. And I do believe MARS only relates to reads. MARS lets you say, iterate over a result set, and while you are iterating, execute another query for a related piece of data on item you are on. – Darren Kopp May 04 '12 at 14:25