HELP - Invalid object error related to sqlole null bug in MSSQL 2000?
We had a contract programmer come in last year to write a set of scripts in VB6 that select records from a MSSQL 2000 Enterprise Edition datbase table for display on a web site. Everything was going fine until a few days ago. The drive that contained the table fried. All the rest of the scripts were on a different drive, as was the database instance itself.
So, we created another table under the domain administrator account in Windows Server 2003 with the exact same name, created tables exactlyu as they were before, but if any special triggers or stored procedures were present , we had no record of them and they are lost.
Now, when we use the same scripts to access the tables, they all work perfectly except for one. THis tells us that the basic installation and tables are healthy and can be logged in to and accessed, and the connection strings are all fine.
But, the developer last year had this same problem until he told us that he had discovered a bug relating to nulls, if present in the tables. He said it had to do with if items are null, they do not pass to OLE servers properly. This is all we remember, and it may not be correct. But, there is a built-in stored procedure in MSSQL 2000 called dt_checkinobjects_u that has text in it that states there is a bug that if items are null they do not pass to OLE servers in the comment text.
What could be making SQL server produce this message with the recreated database and tables now, when it never did before?:
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'mailuser.xyz_com'.
/secure.one/genplans.inc, line 565
The script source follows:
<% Do while not soRs.eof
RowCount = RowCount + 1 %>
<table border="0" width="100%" cellspacing="1" cellpadding="0">
<% x = instr(1,soRs("EmailAddress"),"@")
m_userid = mid(soRs("EmailAddress"), 1, x - 1)
m_domain = mid(soRs("EmailAddress"), x + 1, len(soRs("EmailAddress")) - x)
Set mConn = Server.CreateObject("ADODB.Connection")
Set moRs = Server.CreateObject("ADODB.Recordset")
mConn.Open Session("ODBC2"), Session("ODBC2USER"), Session("ODBC2Pass")
Sql = "select MailTableName, ServerIPAddress, Computername from EmailDomains where MailDomain = '" & m_domain & "'"
moRs.open sql, mConn, 1, 1
m_MailTableName = moRs("MailTableName")
m_MailServerIP = mors("ServerIPAddress")
m_computername = mors("ComputerName")
moRs.Close
sql = "Select ConnectionString from email_servers where computername = '" & m_computername & "'"
mors.open sql, mConn, 1, 1
ConnectionString = mors("ConnectionString")
mors.Close
mConn.close
Set aConn = Server.CreateObject("ADODB.Connection")
Set aoRs = Server.CreateObject("ADODB.Recordset")
'aConn.open "msmail","mailuser","password"
aConn.Open ConnectionString
sql = "Select password, fullname from mailuser." & m_MailTableName & " where Userid = '" & m_userid & "'"
If Test then
Response.write "Sql: " & sql & "<br>"
End If
aoRs.open sql, aConn, 1, 1 %>
Everything is fine above until the script runs the last line, and that generates the error.
Could anyone point me toward what was left out of our rebuild of the server that would result in the error:
Invalid object name 'mailuser.xyz_com'.
And, does it indeed involve the fact that the table has nulls in it? There are no null entries at all in the specific fields being retrieved. THe nulls are in fields elsewhere in the table that are not being selected.
Thanks in advance, I know this is a hard one perhaps, but deeply appreciate any help anyone could offer!!!!
Elys Rand
<elys@elysrand.com>