Unable to connect to SQL2005 using VBScript

Question

Using the connection string below I can connect to a SQL2000 DB but not a SQL2005. I have the code in an ASP file.

Dim connStr, cn, rs, sql
connStr = "Provider=SQLOLEDB;Persist Security Info=True" _
           & ";Initial Catalog=mydatabase"  _
           & ";User ID=dbuser" _
           & ";Password=dbpwd" _
           & ";Data Source=servername" 
sql = "SELECT TOP 1 [Column1] FROM [dbo].[MyTable] order by NEWID()"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open cn

set rs= server.CreateObject("ADODB.Recordset")
 rs.CursorLocation=3
 rs.Open sql,cn,3,4

 if not rs.EOF then
       Response.Write("<b>Column1: " & rs("Column1") & "</b><br />")
 end if
set rs.ActiveConnection= nothing    
 rs.Close
 set rs= nothing
if ucase(TypeName(cn)) = "CONNECTION" then
 cn.Close
 Set cn = Nothing
end if

I have even tired with SQLOLEDB.1 Sql login is enabled on the sql server.

Error: The connection cannot be used to perform this operation. It is either closed or invalid in this context. Happens on rs.Open sql,cn,3,4

Answer 1

Not a specific answer sorry, but this is a great site for all-things-connection-strings...

Answer 2

What is the error? SQL Server 2005/8 install with remote connections disabled -- check this support article.

I see, try setting your connString on your Connection object (you use conn instead of connStr). Uee option explicit to avoid these errors.

Answer 3

It happens to everybody sometime:

Dim connStr, cn, rs, sql
connStr = "Provider=SQLOLEDB;Persist Security Info=True" _
           & ";Initial Catalog=mydatabase"  _
           & ";User ID=dbuser" _
           & ";Password=dbpwd" _
           & ";Data Source=servername" 
sql = "SELECT TOP 1 [Column1] FROM [dbo].[MyTable] order by NEWID()"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open connStr

You are calling a variable conn as connection string but you have declared and filled connStr

Change "cn.Open conn" with "cn.Open connStr"