utahalee
asked on
Problem connecting to sql server using vbscript
I receive the following error message :
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
in err.description and con.state = 0 which I thought meant no problems for con.open statement.
I am using the computer name found in the properties of "My Computer" of the server that is running sql server for the "server=" in the connection string. Not sure if that may be the problem. I get the same error with both connection strings in the code below.
here is the code snippet:
on error resume next
Set con = CreateObject("ADODB.Connec tion")
'constring = "Provider=SQLOLEDB.1; server=uta-treatlink; database=totalh; uid=xxxx; pwd=xxxx'"
constring = "Driver={SQL Server}; server=uta-treatlink; database=totalh; uid=xxxx; uid=xxxx"
con.connectionstrong = constring
con.open
msgbox err.description & " (state = " & con.state & ")"
cnt = 0
Set rs = CreateObject("ADODB.Record set")
rs = con.Execute ("Select * from xxx")
do until rs.EOF
cnt = cnt + 1
loop
set rs = nothing
con.close
set con = nothing
msgbox ("count = " & cnt)
-------------
this seems too simple to not be working.
Thanks
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
in err.description and con.state = 0 which I thought meant no problems for con.open statement.
I am using the computer name found in the properties of "My Computer" of the server that is running sql server for the "server=" in the connection string. Not sure if that may be the problem. I get the same error with both connection strings in the code below.
here is the code snippet:
on error resume next
Set con = CreateObject("ADODB.Connec
'constring = "Provider=SQLOLEDB.1; server=uta-treatlink; database=totalh; uid=xxxx; pwd=xxxx'"
constring = "Driver={SQL Server}; server=uta-treatlink; database=totalh; uid=xxxx; uid=xxxx"
con.connectionstrong = constring
con.open
msgbox err.description & " (state = " & con.state & ")"
cnt = 0
Set rs = CreateObject("ADODB.Record
rs = con.Execute ("Select * from xxx")
do until rs.EOF
cnt = cnt + 1
loop
set rs = nothing
con.close
set con = nothing
msgbox ("count = " & cnt)
-------------
this seems too simple to not be working.
Thanks
ASKER
Oops, didn't notice the uid in there twice. That typo is just in my snippet I posted.
I still have the connection problem using "myservername". How do I find the "\sqlServerInstanceName"?
This is a 1 1/2 person shop so there is no sql server administrator to ask. The sql server database was setup by a contractor and has been running fine since.
Thanks for your help!
I still have the connection problem using "myservername". How do I find the "\sqlServerInstanceName"?
This is a 1 1/2 person shop so there is no sql server administrator to ask. The sql server database was setup by a contractor and has been running fine since.
Thanks for your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Unforunately I have no experience with SQL Server Configuration Manager or setting up SQL Server. This database was setup a few years by a contractor. It has run on its own since then. I have defined tables, columns, and stored procedures for a database and programmed to access the database but no administration. A sql administrator has always given me the connection string info but there is not one here. I looked at the link and it was for a sql adminstrator and not for me. I am only familiar with query analyser and enterprise manager. Can I find server="???" for the connection string from either one of those?
Also depending on how the server is setup sometimes for server name you need to include this "myServerName\theSQLServer