June 27, 2008 at 5:01 am
I have DTS scripts which run perfectly well when run from the server itself.
If I try to run them on a client machine via Enterprise Manager, or If I try to run them in VB, I get the error
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
The server is logged in with a domain account, and not the local admin account. The account and password have been verified.
The client uses TCP to connect to the server.
I can access the tables in the databases via ODBC (i.e. attach and use them in MS Access)
I can run each step of the DTS in turn using local Enterprise Manager, but not the DTS package itself.
The Connection objects refer to the server by name and not (Local)
This is my first attempt to try and run DTS from VB or a local client, so nothing has 'stopped working', however the server logging in as a domain account is a recent change
There appears to be many web pages regarding this error, and I have searched dozens, but nothing seems to apply.
My SQL Admin skills are fairly novice as are my TCP/Networking expertise.
What can I look for next?
Update 1 30th June: I have recreated the DTS from scratch using the local EM and it works fine (They were initially created on the server with a different account). This is great except for the fact that I have dozens of these to resolve, and I don't want to have to rebuild them all, especially as you can't copy and paste every step, e.g. Transformations.
Update 2 30th June: I deleted all unused connections from the package, using both the script and the technique shown here: http://sql-server-performance.com/Community/forums/t/19056.aspx but no change.
Update 3 30th June: So I figured - if a new package works and and old one doesn't then what is the difference? I saved both as a VB file and compared them line by line. The package that doesn't work was also trying to log to the (local) server! I had never set the logging, so didn't think to look there. I updated the logging to the explicit server name and PROBLEM SOLVED!
Thanks to all who responded with suggestions. All useful tips!
June 28, 2008 at 11:12 pm
[font="Verdana"]Create an alias in the client machine to point to the server in right port and then try again.[/font]
Regards..Vidhya Sagar
SQL-Articles
June 29, 2008 at 10:20 am
Is this VB6? Are you stepping through the code? Is this the same workstation that ran them from EM?
That message usually indicates a network level error, the server address is wrong, wrong port, firewall, something like that.
June 29, 2008 at 5:41 pm
In your DTS package, you will have at least one connection to a database. I have seen a lot of people using local/localhost and a SQL account (generally, I see them using sa which is a very bad idea).
So, if your connections are set to local (localhost) this needs to be changed to the appropriate server and if you are using a SQL account make sure the password entered is correct. I personally would modify the connection to use Windows Authentication myself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 30, 2008 at 2:13 am
Is this VB6? Are you stepping through the code? Is this the same workstation that ran them from EM?
That message usually indicates a network level error, the server address is wrong, wrong port, firewall, something like that.
It is Access 2003. The code fails on the line in bold underline
oPKG.LoadFromSQLServer strServerName, username, password, DTSSQLStgFlag_UseTrustedConnection, , , , strPackageName
' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next
' Execute
oPKG.Execute
oPkg.Execute
June 30, 2008 at 2:15 am
In your DTS package, you will have at least one connection to a database. I have seen a lot of people using local/localhost and a SQL account (generally, I see them using sa which is a very bad idea).
So, if your connections are set to local (localhost) this needs to be changed to the appropriate server and if you are using a SQL account make sure the password entered is correct. I personally would modify the connection to use Windows Authentication myself.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
the SQL connection is explicitly set to the server name. Both simple and fully qualified names have been tried
June 30, 2008 at 2:16 am
Create an alias in the client machine to point to the server in right port and then try again.
Regards..Vidhya Sagar
already tried. No difference.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply