how to find data source name. 1

[wvdba]

i'm getting this error when i try to connect to my database:
any ideas?
here's the connection string:
strCon = "Server=A20ootb05l01457\A032299;Database=master;Trusted_Connection=True;"
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open strCon
the error is:
Error: [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified
Code: 80004005
any ideas?
thanks

 

[bborissov]

Yep.
The connection string is not right.
You should add a Provider there:
Check

http://www.connectionstrings.com/

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[wvdba]

thanks.
i have tried every one of those strings - no luck.

 

[bborissov]

What is your SQL Server?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[wvdba]

sql server 2008 R2
i need to get the connecting string,
connect to it
and open it.

thanks.

 

[bborissov]

OK Try:

strCon = "Provider=SQLNCLI10;Server=A20ootb05l01457\A032299;Database=master;Trusted_Connection=True;"
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open strCon

If that didn't work you have no SQL Native Client 10 installed on your computer.
You should download and install it.

Try to connect with the old (SQL Server 2000) provider that comes preinstalled with Windows:

strCon = "Provider=sqloledb;Server=A20ootb05l01457\A032299;Database=master;Trusted_Connection=True;"
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open strCon

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bborissov]

My mistake:
Second should be:

strCon = "Provider=sqloledb;Data Source=A20ootb05l01457\A032299;Initial Catalog=master;Trusted_Connection=True;"
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open strCon

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[wvdba]

thanks.
i tried both.
on the first one i get this error:
Invalid value specified for connection string attribute 'Trusted_Connection'
on the second one i get this error:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

 

[bborissov]

strCon = "Provider=SQLNCLI10;Server=A20ootb05l01457\A032299;Database=master;Trusted_Connection=yes;"
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open strCon

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[wvdba]

thanks.
i tried the new connection string/open.
this is the error i'm getting:
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xffffffff].

 

[bborissov]

OK,
try to create a regular File ODBC DSN connection from Control Panel->Administrative Tools->ODBC.

Then when you save this file, open it and paste the file here.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[TheBugSlayer]

Can you create a connection string elsewhere, test that it works then copy the string and use it in your application?
For instance, in Visual Studio, create a project, right-click the project name, choose Properties; on the left select the Settings tab then create a Setting of type (Connection String), test it; if it works copy the text and paste it into your program.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[wvdba]

TheBugSlayer,
i'm not sure if i understand what you're saying.
thanks.

 

[wvdba]

thanks everybody.
the last connection string worked after i removed user name. server name is the computer name. so i removed A032299 from the string.
this string works now and select records:

strCon = "Provider=SQLNCLI10;Server=A20ootb05l01457;Database=master;Trusted_Connection=yes;"
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open strCon
Set objRs = WScript.CreateObject("ADODB.Recordset")
sql_string = "SELECT * FROM persons"  
Set objRs = objConn.Execute(sql_string)

thanks so much.

 

[JoeAtWork]

In the connection strings you posted, A032299 is not a user name, it is an instance name of SQL Server.

Server=A20ootb05l01457\A032299

Means "use the SQL Server instance called 'A032299' on the server 'A20ootb05l01457'".

By changing it to just

Server=A20ootb05l01457

you are telling it to use the default SQL Server instance.

 

[wvdba]

thanks, JoeAtWork
i think i understand what you are saying. but, my connection string/open didn't work with the instance after the server name. now it works. what can i do to fix it, please? what should my connection string look lile?
thanks.

 

[TheBugSlayer]

Oh, I remember something from MCIT class: is the SQL Browser service running? If it' s installed and not running, start the service in Server Configuration Manager and try again. If it' s not installed it can be turned on in Surface Configuration Area.

Good luck!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

The alternative to using an instance name is to specify the port that the instance is using. Default instances use port 1433 (be default and this can be changed). When you install a named instance, another port will be used for it.

When you know what port your named instance is using, you can put it in the connection string like this...

[tt][blue]
strCon = "Provider=SQLNCLI10;Server=A20ootb05l01457[!],1433[/!];Database=master;Trusted_Connection=yes;"
[/blue][/tt]

In the example above, I used the number 1433, but you would want to use the actual port number for the named instance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Keep in mind that specifying the port the named instance is listening to will only work if it is static, so to speak. If it' s dynamic then it' s best to start SQL Server Browser and not worry about specifying the port number in the connection string.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

I didn't specifically say it, but...

I agree that it's better to use the SQL Server Browser service instead of hardcoding a port number. I also recognize that we don't always have the ability to start services or configure firewalls.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom