Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

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.Connection")
'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.Recordset")
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
0
utahalee
Asked:
utahalee
  • 2
  • 2
1 Solution
 
JMT116Commented:
Not sure if this will help, but in the uncommented constring uid appears twice.

Also depending on how the server is setup sometimes for server name you need to include this "myServerName\theSQLServerInstanceName".  
0
 
utahaleeAuthor Commented:
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!

0
 
JMT116Commented:
Use this reference to find the instance name.
http://support.microsoft.com/kb/265808

You should be able to find the instance name from the SQL Server Configuration Manager.
0
 
utahaleeAuthor Commented:
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?
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now