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

I have other .Net apps hitting that server without any problems. — Picflight
– Picflight, Commented Jun 17, 2009 at 0:10
Double check your permissions and tables on the sql2005 server; your connection code works fine for me with sql 2000, 2005 and 2008. — SqlACID
– SqlACID, Commented Jun 17, 2009 at 0:14

backslash17 · Accepted Answer · 2009-06-17 00:19:00Z

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"

answered Jun 17, 2009 at 0:19

backslash17

5,3905 gold badges34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RBarryYoung Over a year ago

And always use "Option Explicit" to prevent this in the future.

JP Alioto · Accepted Answer · 2009-06-17 00:14:08Z

0

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.

edited Jun 17, 2009 at 0:14

answered Jun 17, 2009 at 0:01

JP Alioto

45.2k6 gold badges91 silver badges112 bronze badges

1 Comment

Picflight Over a year ago

I updated the post with the error. The SQL 2005 is configured for remote connections. I have other .Net apps hitting that server without any problems.

Collectives™ on Stack Overflow

Unable to connect to SQL2005 using VBScript

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related