How to output the results of an procedure in SQL server?

TIMEOUT EXPIRED calling Stored Procedure from Access 2000 on SQL Server 7

• I have an Access Data Project that links via odbc to my SQL Server, with no problems (I'm running windows NT). Now, i want to give someone else access to the SQL Server, so i created the usernames and gave her a copy of the ADP file and set up the odbc on her computer. I can browse my tables from her computer, but when i call a stored procedure, I get TIMEOUT EXPIRED every time. Any ideas?

• Answer:

  Hi, Den: Since the dbconnection property in Access is set to 0 (right? in your Access code?), I'm suspecting a driver incompatibility. The following should gather information about that, and also handle the case of truly long running queries. The ODBC connections can be configured from an applet in the Control Panel. In Windows NT it's called "ODBC Data Sources" and has a yellow 32 in the icon (to distinguish it from 16-bit ODBC drivers). If you created the data source from within Access, then it will probably show up under the "File DSN" tab. More to the point, your user is running Win98. The Control Panel applet says "ODBC Data Sources (32bit)" and the icon lacks the flashy yellow 32. These applets can create, remove, and configure the ODBC data sources, as well as provide version information about the underlying ODBC drivers. One possibility is a version conflict between the ODBC SQL Server driver on your user's machine and the version of SQL Server you are trying to connect her to. If you check that information (and the version on your NT machine, as well, for the sake of completeness), I can look into that. At any rate if you highlight the data source in question (under the File DSN tab, or possibly under User DSN depending on how security was set on the Win98 machine), and click the Configure button, it goes throw a configuration "wizard". The second "page" of this wizard has a "Client Configuration..." button, that basically corresponds to the SQL Client Configuration Utility. Of those settings the most important is the choice of Network Library, probably either TCP/IP or Named Pipes. I would make the user's configuration consistent in this respect with what is working on your NT machine. Close the client configuration utility and go back to the ODBC data source configuration wizard. There may be a checkbox at the bottom of that second page that offers to obtain additional settings by connecting to SQL Server (requires you to enter the loginID, which should be all you need if you are using Windows authentication). This can be useful as a quick test that SQL Server is reachable (you cannot proceed if it cannot connect, because it pops up an error message), but eventually you will simply want to be consistent with your own set up. The next page offers to create temporary stored procedures, which you don't need in this case, for "prepared SQL statements". I'd leave this unchecked but it shouldn't be a big deal either way. Leave the Use ANSI quoted identifiers and Use ANSI nulls _checked_, unless you suspect the stored procedure might depend on non-ANSI behavior in a specific way. You probably don't have a failover SQL Server, so leave the last box unchecked. Next page! We finally come to some settings that might help solve the problem. Leave the Perform translation for character data box checked; it's probably the only one checked by default, and leave Use regional settings unchecked. Add checks to the Save long running queries to the log file (might as well use the default filename, C:\QUERY.LOG, and to the Log ODBC driver statistics to the log file (by default, C:\STATS.LOG). The default query time for sending stuff to file is 30000 milliseconds, or 30 seconds. You can leave that for now. Click the finish button. Bring up Access and do some things to exercise the ODBC connection (like running the trivial stored procedure from the Pass Through Query definition), or just browsing the list of linkable tables (under Import Data...) on SQL Server. See if that puts much of anything into the Log file; I've never found much interesting there, but it may help in the next step. Try and run the report, and see if it either works or at least puts the error message in some sort of context with the logging file. regards, mathtalk-ga