Solved

Problem connecting to sql server  using vbscript

Posted on 2008-10-02
4
491 Views
Last Modified: 2012-05-05
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
Comment
Question by:utahalee
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:JMT116
ID: 22626025
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
 

Author Comment

by:utahalee
ID: 22635258
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
 
LVL 3

Accepted Solution

by:
JMT116 earned 100 total points
ID: 22636831
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
 

Author Comment

by:utahalee
ID: 22650548
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question