| Author |
Topic |
|
jpiscit1
Posting Yak Master
130 Posts |
 Posted - 2004-10-28 : 08:39:24
|
| Hi.Im using Windows 2K, SP3 and SQL 2K, SP3I have a job set up (through schedules) to run in DTS that, until last night, seemed to run fine. It performs some select statements, dumps the results to Excel, and emails the file. The job was created in Enterprise Manager from my local PC remote to the Actual SQL server. I can access the SP work flow properties (within DTS) from my local machine, but when I try to do it on the actual server i get the following error:Package Error:Error Source: Microsoft OLE DB Provider for SQL ServerError Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.Anyone have any ideas what might be going on here?Thanks in advance...John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-28 : 13:03:49
|
| DBNETLIB means you are using named pipes. Don't you want to use TCP/IP for yours connections? If so, check out Client Network Utility on the database server.Tara |
 |
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2004-10-28 : 14:39:49
|
| Thanks for the reply Tara.Named Pipes was enabled. However, TCP/IP was also enabled. I removed named pipes from the Client network Utility and from the Server configuration. Stopped and restarted SQL. I still get the same error.Drilling down im finding that the actual job error is this:Executed as user: FULMS255\SYSTEM. ...TSRun: Executing... DTSRun OnStart: Drop table Results Step DTSRun OnFinish: Drop table Results Step DTSRun OnStart: Create Table Results Step DTSRun OnFinish: Create Table Results Step DTSRun OnStart: Copy Data from Results to Results Step DTSRun OnError: Copy Data from Results to Results Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Copy Data from Results to Results Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.I somewhat puzzled because I did not change anything and it seems to have suddenly quit working. The error above seems to imply it could possibly be something to do with security?? It seems to be failing when it attempts to write to the excel file... Any other thoughts??John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-28 : 14:41:45
|
| It's failing connecting to SQL Server. Try registering this server (the one in the DTS package) on the database server's EM. BTW, the error is still referencing named pipes. I'd suggest creating an alias to use TCP/IP.Tara |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2004-10-28 : 16:27:18
|
| I checked and the SQL server in the DTS package also has named Pipes configured along with TCP/IP. I removed them but can't restart right now. I will try this tomorrow and see what i get... This makes sense....Thanks again... |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2004-10-28 : 21:06:37
|
| Just a suggestion and i'm probably a mile off:Are you connecting to another SQL server which your PC can see/login into but the server cant?Ive had this before with ODBC files within a DTS package (albeit to our bespoke UNIX system) but the other way round, ie the connection(s) existed on the server but not on my PC which threw a not to disimilar error.Andy |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-28 : 21:14:06
|
| Yep, that's why I mentioned "Try registering this server (the one in the DTS package) on the database server's EM." This'll show whether or not the database server can even connect to the other server.Tara |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2004-10-28 : 21:20:56
|
Ah, sorryI missed that bit, it is 2am here (match sticks keeping my eyes open)Think its time for bed  |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2004-10-29 : 08:25:01
|
| Interesting what I found here. Thought you guys might want a follow up. It would appear there were two problems. First, let me state I do not work in our IT Dept. and am not always privy to changes made. Typically I find out second hand or when something breaks.1) Our DNS services apparently stopped functioning. We have a mixed Microsoft/Novell network here. Our IT admin is anti-Microsoft so typically it does as much as it can through Netware. The DNS servers are NetWare servers with MS servers as a back up. My experience has shown me that these two do not play nice together particularly with ADS and NDS.. In any case I had to add a custom entry into the host file on the server that was running the job. After that I was able to at least ping the remote SQL box... lol2) The network config on both SQL servers used named pipes. Not sure if this is a default on load but it's not something I set up. In any case, it did not appear to be a problem until just recently. Come to find out our IT staff recently disabled RPC functionality between LANs at the router level. I am in the process of finding out why. I suspect they did this due some the security vulnerabilities with RPC. I’m thinking this may have been what caused the error because - Yes Tara, I could not communicate with the remote server in EM until I made the changes above. After I disabled the named pipes and corrected the DNS issues with the host file, I re-registered the server successfully and was able to run the jobs.I do, however, have a couple of general questions I would like to throw out to the group. 1) This is not necessarily related to DTS and I understand if I don't get any responses. But ....Does anyone else here use a mixed Microsoft/Netware environment? We have continually had problems with this marriage (probably a bad term). I’m just wondering what others experiences are related to SQL and this. As a result of this situation I have been forced to do things like use SQL local security as opposed to the MS Domain security, use custom procedures for lotus notes apps (smtp) because IT wont use MS mail, not to mention the continual problems we see with DNS.2) The SQL boxes appear to have some funky names. Again, not something I set up. There is the machine name and then what appears to be the instance of SQL "name". What role does the SQL instance "name" play? In other words, if I had to a host reference, should it have been:IP blaa, blaa BOXNAME\SQLNAMEorIP blaa, blaa BOXNAMEAlso, when i set up ODBC connections or the like, should I be using the full name BOXNAME\SQLNAME or just the box name. It would seem i need the full name. What do others do here??Not quite sure I understand this.Thanks again for your help....John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-29 : 12:36:50
|
| At my last job, we had a mixed environment. We used the hosts for everything. Our DNS didn't resolve anything for us. I had probably 20-50 entries in my hosts file for all the various servers that I needed to connect to. It was ridiculous. I had a ton of IP addresses memorized for these servers as well. At my current job, I only have a couple IP addresses memorized since DNS handles the name resolution for us here.For the hosts entry with named instances, you would just add the information for machine name and not the instance name. Then you'd use Client Network Utility (a MS SQL Server client tool) to add an alias for the instance name. For the ODBC connections, you use the BOXNAME\InstanceName or you could use BOXNAME,PortNumberThatInstanceIsListeningOnTara |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2004-10-30 : 07:08:44
|
| Interesting....It's good to see there are others that had to feel the same pain. Until now, I never bothered with the Network Client Utility. I assume its primary purpose is to provide Alias broadcast at the server level?? In other words, if I run EM on my local machine I would not need to set up a duplicate alias (in my local Network Client Utility) that’s already set up on the server machine..?? As of now, all of our ODBC connections reference ServerName\InstanceName. Network utility at the server currently has no references in it. If I configure the utility at the server and say:ServerName\InstanceName = ServerNamewill this require me to modify all my remote connections to just ServerName or is this an either/or situation?Also, my host files are looking like yours did Tara....lol. It's a real pain...Anyone else having to use Lotus Notes as an emailer? What have been others experience with this? So far I have found custom SP's that allow me to email. But have not figured out how to configure it in the Support Services or if there is even away. It appears to only accept a MAPI configuration, not SMTP.Just curious.....Thanks again. This site is excellent!!!John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-02 : 12:19:50
|
| The purpose of Client Network Utility is to tell the client how to connect to the database server. If an alias exists at the server, only the server can use it. An alias is actually just a registry entry, so no other computers can make use of it. In Client Network Utility, you tell the client which protocol to use (TCP/IP being the most popular) and the other information required for that protocol. For TCP/IP that would be IP address or server name (depending if DNS can resolve it for you, in your case you'd hard code the IP address) and the port number that the instance of SQL Server you are trying to connect to is listening on.Tara |
|
|
|
|
Strange Error with DTS Job