Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Problem connecting to sql server  using vbscript

Posted on 2008-10-02
4
494 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
In this tutorial I will focus on how to use WhizBase as a tool for sending ICQ messages to ICQ. Here I will use a new technology in WhizBase, published in WhizBase 5.1 version. In this tutorial I will use 3 files, pager.wbsp for the processing, e…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

808 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