Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem connecting to sql server  using vbscript

Posted on 2008-10-02
4
Medium Priority
?
501 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
[X]
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
  • 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 300 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
This article will show, step by step, how to integrate R code into a R Sweave document
The viewer will learn how to dynamically set the form action using jQuery.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

670 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