none
Passing table of values from Excel/Access to SQL Stored Proc

    Question

  • Assuming I have a scenario where client uses Excel sheets or Access tables to update a table of values. The table maps exactly to a table in SQL Server. The client periodically updates the values from Excel / Access to SQL, using VBA / ADO (classic), which will execute a stored proc in SQL Server, passing the table of values as a parameter to the Stored Proc. The table of values can sometimes be quite high-say upto 50,000 rows.

    If I was using ADO.Net, I could have used a datatable on the client and then pushed data into a Table Valued Parameter which is passed to the Stored Proc. One could also pass the table as XML which is then parsed by SQL Server using nodes() or Openxml or something. Another option could be to write CLR to achieve the objective.

    Sadly, TVP's are not available in classic ADO / VBA. Passing the table as an XML string is an option and so is writing some complicated CLR code to parse a string into a table (although CLR is frankly little beyond my current abilities)

    I am not interested in any solution using SSIS etc. or any periodical job updates. My updates need to be initiated by the client using VBA or Access pass through queries, the way one would insert a row of data into a table, using standard ADO command object and parameters.

    However, my basic question is this -

    Why dont I find any articles on the net on "pulling" data from the client machine into SQL Server from an SQL Server stored procedure. The client (excel/access) uses VBA to execute a Stored Proc on the SQL Server. The client passes a linked server name or path/file name for Openrowset to SQL Server Stored Proc.

    e.g. of such a procedure -

    create proc mysp_something @mylinkedserver as nvarchar(200) as declare @mystr as nvarchar(500);

    set @mystr = 'insert into sqlserverTableName select * from ' + @mylinkedserver' + '...AccessTableName' --(or alternatively using openrowset etc.);

    exec sp_executesql @mystr

    Ofcourse, the main issue with this method is that one has to use Dynamic SQL (which can be insecure) since each client will have to pass info on path/file or linked server details to the SQL Stored procedure....

    However, assuming one still uses dynamic SQL taking due care of security concerns, what would be the speed of this method, and how would it compare with the various other methods listed above?

    What is the reason why this method is never listed anywhere as a means of transferring a table of data from client to SQL Server? Am I missing out on something basic? e.g. possibly there will be windows permission issues in the SQL Server accessing the client? But what if the client excel/mdb is on a shared folder? Also, if the client has a dynamic IP, this will also need to be retrieved by SQL Server...however, these issues can be tackled if one uses dynamic sql.

    Would very much appreciate a clarification on this as this is an issue that has been troubling me a lot on whether the method listed by me has some basic flaw due to which it should never be used....!!!

    Friday, March 04, 2011 2:13 PM

Answers

  • Based on feedback from several experts, I have reached the conclusion that the based way to pass a large rowset from client to SQL Stored Proc is to create an XML string in ADO, and pass the same as a parameter into the SP. Thereafter, use the XML data type in SQL Server to convert the XML into relational data using the nodes() method. This is due to the absence of support for Table Valued Parameters in ADO.

    Although linked servers/openrowset can be quick and easy, it has possibility of running into several operational issues such as lack of Windows permission, possibility to end-user moving the file from one location to another on his PC, issue relating to dynamic IP's in a Wireless network etc.

     

    Hence, although the XML method is slightly more convoluted, it appears to be a more scalable method. 

     


    Chaitanya
    Thursday, March 10, 2011 5:09 AM

All replies

  • The problem with OpenRowSet or a Linked Server is that they can be fast in writing into the target table but they will be slow in reading from your Excel/Access tables.

    Your best and easiest solution is probably to use an ADO recordset in batch mode.  The driver should be able to optimize the transfert of the data over the wire and insert them efficiently using a prepared statement.

    If you want to use either a SP (via a command object) or a textual Insert command, look about the possibility of sending something like 10 rows at a time.  I don't know if this will be of any good with a textual insert command - as this might be block the re-usage of a compiled plan in the cache but with SP, you should obtain a good performance for sure.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Marked as answer by KJian_ Thursday, March 10, 2011 2:54 AM
    • Unmarked as answer by Chaitanya Dabholkar Thursday, March 10, 2011 4:51 AM
    Friday, March 04, 2011 11:14 PM
  • Based on feedback from several experts, I have reached the conclusion that the based way to pass a large rowset from client to SQL Stored Proc is to create an XML string in ADO, and pass the same as a parameter into the SP. Thereafter, use the XML data type in SQL Server to convert the XML into relational data using the nodes() method. This is due to the absence of support for Table Valued Parameters in ADO.

    Although linked servers/openrowset can be quick and easy, it has possibility of running into several operational issues such as lack of Windows permission, possibility to end-user moving the file from one location to another on his PC, issue relating to dynamic IP's in a Wireless network etc.

     

    Hence, although the XML method is slightly more convoluted, it appears to be a more scalable method. 

     


    Chaitanya
    Thursday, March 10, 2011 5:09 AM