Jim Lawrence
jlawrenc1 at shaw.ca
Mon May 7 13:36:45 CDT 2007
Hi John: Refer to DBA article: http://www.databaseadvisors.com/newsletters/newsletter112003/0311UnboundRepo rts.asp The section shows the method of parameterizing a request to MS SQL sever from Access. There is also a download attached to the article though in the demo it only connects to an Access DB BE. The modification required to allow it to work with a MS SQL Server only required the connection string to be changed. Here is a link to a short online video on the subject, from the server end: http://download.microsoft.com/download/b/3/8/b3847275-2bea-440a-8e2e-305b009 bb261/sql_12.wmv HTH Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Monday, May 07, 2007 7:06 AM To: 'Access Developers discussion and problem solving'; dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Thanks for the help I am making great progress on understanding how to create stored procedures and getting them functioning. The piece I am still missing for my particular application is how to get them to run from Access. As you probably know by now I am trying to do batch processing of data import / export. Last week I was working on getting a 100 million record data import happening, where the data came in from 56 different files of various size, one or more files per state, depending on the population of the state. I got a stored procedure built and, using a Bulk Insert SQL statement was able to up my import from a previous high less than 500 records / second to up above 12K records / second on average. What an improvement that has been! Again a million thanks to all those who so patiently talked me through this stuff. In the end I simply opened a query window inside of SQL Server, and keyed in the name of the stored procedure and a file name, manually recorded the time it took SQL Server to perform the insert, modified the filename and did the next etc. 56 times and I was done. Not efficient but with the import times so radically improved at least I could get it done. My next step has to be getting such a stored procedure functioning when run from Access. ATM my application that does the data transformation from fixed width to csv is the driver for this entire process, and ATM it is written in Access / VBA. Remember that these stored procedures simply do a BULK INSERT, passing in a file name. therefore these stored procedures do not yet return a recordset (or even a value), but I really do need to get them to return a value eventually. My strategy is to "baby step" this thing so that I can sort out where the inevitable problem lies and get it functioning one step at a time. So my next step is simply to get the stored procedure executing when called from VBA. If anyone has code that they are willing to share that executes a stored procedure in SQL Server , passing in a parameter, executed from VBA out in Access I would be most appreciative. On another note entirely, does anyone know how to, in SQL, specify a specific quantity of records, from a specific place in a table, without depending on an autonumber PK to do it. IOW, I need to pull the first 2 million records, then the second 2 million records, then the third 2 million records etc. I will be exporting these out to a CSV file. The table has an autoincrement PK but some records have been deleted because their address was not deliverable. Thus I could simply say "WHERE PKID >0 and <=2,000,000" and for the next set say "WHERE PKID > 2,000,000 and <=4,000,000" and in fact I will use this approach if required. The problem is that the result set will not be 2 million records, but rather 2 million minus the deleted records in that range. I suppose I could create another autoincrement field so that I would have a field where the numbers are consecutive and then use the approach above, using that field. I am just trying to discover whether it is possible with SQL to do this without depending on an autoincrementing number field. Thanks, John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com