[thelist] SQL Server default db with ancient ASP

Max Schwanekamp lists at neptunewebworks.com
Wed Dec 27 19:31:04 CST 2006

Hello all,
We have an old ASP/VB site based on Win2k 2003 and MSSQL 2k housed on a 
shared host, accessed rarely and by staff only.  I haven't done any ASP 
in eons, so I must be missing something obvious.  My problem is that the 
default database indicated by the initial connection does not seem to be 
getting selected, forcing all queries to prefixed with the database 
name.  When the site was active, living on a Win2K Server with MSSQL 2k, 
this was not necessary. 
I have an include file at the top of all site pages, which has this:
strConn="Provider=SQLOLEDB; Data Source=sql.example.com; Initial 
Catalog=mydatabase; User ID=dbuser; password=dbpass"
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open strConn
if Conn.State<>1 then
    'error handling...
end if
This works fine, except that it seems that the Initial Catalog is 
getting ignored.  For queries to work, I must prefix all table 
references with the database name.  For example this code immediately 
following the above:
SQL="SELECT TOP 1 * FROM mytable"
set rs=conn.execute(SQL)
This will error out with "Invalid object name 'mytable'."  But if I 
change that query to:
SQL="SELECT TOP 1 * FROM mydatabase.mytable"
set rs=conn.execute(SQL)
It works as expected.  Any suggestions to solve this?  I'm trying to 
avoid having to edit all the queries in the code because A) it's time 
consuming drudgery, and B) the client is unwilling to pay for it.


Max Schwanekamp

More information about the thelist mailing list