December 31, 2001 at 7:34 am
Hi All:
I installed SS2K (as domain admin) and created a simple DTS package using ADO
to return the number of records in a specific table meeting a condition.
When I try to run the pkg, I get an error stating SQL Server cannot be found or access is denied on the line of code that attempts the connection to SS.
This happens regardless of the id and pwd combination I try, including sa.
I can query and manipulate the entire db using QA, but DTS appears to be operating differently.
Any help appreciated.
dj
December 31, 2001 at 9:28 am
quote:
created a simple DTS package using ADO
Can you post the connection string, with replacements for what you are using for userid (e.g.,MyUserName) and password (MyPassword)? Let's make sure it's not an ADO connection string issue.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
December 31, 2001 at 10:12 am
Thanks for responding -
I copied the connect string from another (working) DTS pkg on a different machine/network. Here's the connect string:
strConnect = "Provider=sqloledb;Data Source=myserver;Initial Catalog=mydatabase;User Id=sa;Password=;"
When I attempt to run the pkg, I'm logged in as the network domain admin, not the best practice but it is a lab setting.
Thanks!
dj
December 31, 2001 at 10:47 am
Looks like you are using SQL auth. Is the server set for SQL Auth? or NT only?
Steve Jones
December 31, 2001 at 11:08 am
How are you establishing the ado connection, trusted or sql login? I'd double check that line. Maybe post code here so we can see.
Andy
December 31, 2001 at 4:09 pm
The Authentication was set to 'Windows only', the startup service account set to 'System account', which is how I installed SS2K (Developer Edition).
I tried changing the Authentication mode to SQL Server and Windows, cycled SQL Server and the Agent account, and tried again. Exact same result.
Here are the actual error particulars:
Error Code: 0
Error Source = Micorsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB](ConnectionOpen(Connect())).]SQL Server does not exist or access denied.
The DBNETLIB bit seems odd. I'm running TCP/IP and Named Pipes, and everything is on the same (W2K Pro sp2) box. It almost sounds like the DTS pkg can't see the (SQL) server.
dj
December 31, 2001 at 10:23 pm
I think you might have a problem relating to the package owner. You don't need DTS to use ADO - you could test it using a simple .vbs file.
Andy
January 1, 2002 at 2:05 am
Can you post your ADO code? Let's look at that to make sure there aren't any issues.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 1, 2002 at 1:10 pm
Ok, I got it.
My connect string had the SQL Server name, but not the host server name. In my case they are the same (probably not a good idea)
so ADO/ODBC couldn't resolve the (SQL) server name correctly. I added the host server name to the connect string and it now works in VBS and DTS.
BAD:
strConnect = "Provider=sqloledb;Data Source=Chicago;Initial Catalog=myDB;User Id=sa;Password=;"
GOOD:
strConnect = "Provider=sqloledb;Data Source=Chicago\Chicago;Initial Catalog=myDB;User Id=sa;Password=;"
The light went on when I took Andy's suggestion and tried the code as straight VBScript and got the same error.
Thanks to both of you!
dj
January 1, 2002 at 2:47 pm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply