April 2, 2002 at 11:50 am
I am using ASP pages to connect to a SQLServer7 database. I have defined a System ODBC connection on the server. When I am trying to execute the ASP pages, I get the message: "[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied." This appears to happen whenever I try to open a second ADO recordset on the same ADO connection. If I use 1 recordset per connection, the problem does not appear. I don't want to have to create a new connection for each recordset.
Is there any connection between these symptoms and the fact that the server was renamed, and the @@SERVERNAME variable does not match the server name?
Any help greatly appreciated.
Edited by - minerj on 04/02/2002 11:50:33 AM
April 2, 2002 at 2:04 pm
Can you post the code snippet for your connection? Also, ADO has always worked better for me using the SQLOLEDB provider instead of ODBC.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 2, 2002 at 3:31 pm
The connection string is passed in as a string value aCnString in the following code:
' Get Connection Ready
Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
cn.ConnectionString = aCnString
Response.Write "ConnectionString 1: " & cn.ConnectionString & "<br><br>"
cn.Open
Response.Write "ConnectionString 2: " & cn.ConnectionString & "<br><br>"
The above code when executed generates the following output:
ConnectionString 1: DSN=ASLdata;UID=ASL_Admin;PWD=&*corr45;
ConnectionString 2: Provider=MSDASQL.1;Extended Properties="DSN=ASLdata; UID=ASL_Admin;PWD=&*corr45;APP=Microsoft Transaction Server; WSID=INCOMTEKSERVER;DATABASE=ASLdata;Network=DBMSSOCN; Address=incomtekserver,7443"
Further down the page, two more recordsets are defined and opened:
Set rsJoints = Server.CreateObject("ADODB.Recordset")
Set rsFasteners = Server.CreateObject("ADODB.Recordset")
aJQuery = "select * from tblJoints WHERE SpecimenID = '" & _
rs("SpecimenID") & "'"
aFQuery = "select * from tblFasteners WHERE SpecimenID = '" & _
rs("SpecimenID") & "'"
rsJoints.Open aJQuery, cn
rsFasteners.Open aFQuery, cn
The following error occurs on the line beginning rsJoints.Open :
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
/specimen.asp, line 150
This error appears to occur whenever I am trying to open a second Recordset on the same Connection, i.e. two open Recordsets on the same Connection. If I create a new Connection object for each open Recordset, the problem disappears; however, I shouldn't have to do this....?
Any help greatly appreciated.
April 2, 2002 at 3:45 pm
Try the OLE driver.
cn.ConnectionString = "Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password;"
I use it quite often and handle upwards of three recordsets in some pages for various reasons.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 2, 2002 at 4:05 pm
There is a difference between opening multiple recordsets and returning multiple recordsets. You can open a basically unlimited number of recordsets using one connection. Good practice to disconnect them if you don't the connection by setting activeconnection = nothing. What Antares mentioned and I agree with is the practice of returning multiple recordsets in one call, then iterating through them to get your data. I've got one proc now that returns six recordsets, works well.
I would guess that your connection is getting closed - or maybe is related to connection pooling. Not sure what you've got going on with the connection strings, from what I see you have one connection should have only one connect string unless you are closing/reopening it with a different string.
Andy
April 2, 2002 at 6:30 pm
You didn't set the ActiveConnection property of the two new recordsets.
April 3, 2002 at 8:16 am
Using the SQLOLEDB driver instead of the MSDASQL ODBC driver did the trick. Once more the world is spinning on its proper axis. Thanks very much.
April 3, 2002 at 4:26 pm
Great to hear, and I was only stabbing based on issues we have seen with Crystal Reports pulling data over the ODBC as compared to the OLE driver.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply