[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.
TIA.
--
Max Schwanekamp
NeptuneWebworks.com
541-517-9064
More information about the thelist
mailing list