| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2007-01-10 : 07:51:53
|
| I am trying to export data from SQL Server 7 to SQL Server 2000 and am hitting an issue that I am unsure how to troubleshoot. The situation is this - SQL Server 7 is part of a domain which my desktop also is part ofSQL Server 2000 is not part of the same domainSo to access SQL Server 7 I use Windows Authentication and to access SQL Server 2000 I use SQL authentication. (This single login and password is the only access I have to this server)From my machine I can only connect to the SQL Server 2000 by using its IP address but apart from that there are no problems. The DTS package works fineWhen I schedule the job it doesn't run. If I login to the SQL 7 server (where the DTS package is) using the account that the SQL Service runs under then I cannot connect to the SQL Server 2000 using IP Address, I have to use the server name. If I try using the IP address I get Server Msg 1326 ... ODBC SQL Server driver Client unable to establish connection. If I try using the server name I get Server Msg 6 ... ODBC Named Pipes Specified SQL Server not found.Does anyone have any clues how to start troubleshooting this please?Thanks in advancesteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-01-10 : 09:35:44
|
| not sure specifically what the problem, but I do have some questions.1. OS/service pack of all hosts2. service pack of SQL installs3. can you nslookup all hosts from each host? (example, nslookup the SQL 2000 host from the SQL 7 host and vice versa)4. ping all hosts by IP and name from each host involved5. Is the SQL 2000 a named instance or default instance? Is it running on a port other than 1433?6. any firewall between your servers?-ec |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-01-10 : 11:16:10
|
Thanks EC I don't have access to the SQL 2000 and the DBA isn't co-operating at the moment so it may be a while to get some of the answers, however - quote:
1. OS/service pack of all hosts2. service pack of SQL installs
SQL 7 is Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16 Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6) (this is SQL 7 SP4)SQL 2000 is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: ) (this is SQL 2000 SP3) quote:
3. can you nslookup all hosts from each host? (example, nslookup the SQL 2000 host from the SQL 7 host and vice versa)
I'm not familiar with nslookup. When I tried nslookup <IP address> from the SQL 7 box I got DNS request timed out. timeout was 2 seconds.*** Can't find server name for address 163.160.48.145: Timed out*** Default servers are not availableServer: UnKnownAddress: 163.160.48.145DNS request timed out. timeout was 2 seconds.*** Request to UnKnown timed-out The IP address above is not the same as the IP address I was trying to nslookupWhen I try nslookup <Server Name> I get exactly the same messageI can't get at the SQL 2000 box to try from there quote:
4. ping all hosts by IP and name from each host involved
from the SQL 7 box I can ping the SQL 2000 box fine. I cannot access the SQL 2000 box to try the other way quote:
5. Is the SQL 2000 a named instance or default instance? Is it running on a port other than 1433?
I believe it's the default instance though am not certain. I ran SQLPing on the IP address and it is saying that the port is 1433 quote:
6. any firewall between your servers?
I seriously doubt it, though the Domain that the SQL 7 box and my machine are part of are in some sort of V LAN (whatever that means!)Does any of that help?thankssteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-01-10 : 13:32:50
|
| doing the ping and nslookup tests from the SQL 2000 host is important. Is there any way you can get access or have an admin of the box perform the tests? Also, it would be helpful to perform the same tests from your workstation.btw, nslookup is a tool that asks the DNS to return the IP address of a given hostname. If you get a failure, it can mean that the hostname is not present in the DNS. In your case, the error we see is that your host cannot communicate with the DNS it is configured to use. This needs to be fixed, as it will cause other issues since DNS resolution will no longer be working at all. I would have someone look into that. I don't think that is the whole problem though, since using the IP address eliminates DNS from the mix. My first thought is that you have a routing problem, but it could also be a network configuration issue on your server (improper netmask, wrong DNS, etc.) Since DTS is really a client tool it kind of makes sense that you can perform your tests successfully from your workstation. The network being used when you run the DTS tests from your workstation does not really include your SQL 7 server - even thought the DTS packages are stored on it. This changes when you schedule the DTS to run as a task. In that situation the DTS package is running locally from the SQL 7 server (the server is now the client) and the network being used (and the settings) is the one the SQL 7 server is using.It is pretty clear that you have some kind of network issue though - maybe the use of the vLAN is causing a hiccup (a vLAN is a logical network inside a physical network http://en.wikipedia.org/wiki/Vlan). Your workstation can talk to the network your SQL 2000 box but maybe the SQL 7 box cannot communicate completely with the SQL 2000 machine. You can ping from SQL 7 to SQL 2000, but maybe you cannot ping the other direction. We won't know for sure until you can do these tests from the SQL 2000 host.Also, you are also going to need to make sure that you can successfully perform an nslookup from all hosts involved. if you cannot, then you are going to have strange problems since DNS resolution won't be working at all.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-10 : 16:50:08
|
| Set up a Linked Server using your login credentials?(I've got a script I use to set up linked servers - with dummy answers and a variety of "I tried this once and it worked" type comments and so on. I can mail this to you if you send me your email address via SQL Teams "Message to member" thingie. I'm out Thursday from 10am)Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-01-11 : 04:33:46
|
| Thanks guys for your prompt responses.EC I suspect that you are right about the network issues, knowing the way things seem to work here it is hopefully a DNS issue that can be resolved readily. I know there were issues with DNS before. The vLAN is essentially to "split" the network (for political purposes as far as I can tell). The people who control that are seperate to the central IT team and they have their own servers which they maintain, including DNS server(s)I'll try to beat the SQL 2000 guys over the head as well as the vLAN admins.Linked server - why didn't I think of that?! Thanks Kristen will give that a go too. Sadly it's almost 10 on Thursday so I will have a crack at it and see how I get on.If there are any developments I will let you know thanks againsteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-01-17 : 10:40:22
|
| EC I have some more information. - The SQL 2000 box can ping the SQL 7 box fine (using IP address). From the SQL 2000 box nslookup gives dbs1... (which I assume is the DNS server) cannot find <MY SQL 7 BOX>: Non existent domain. The SQL 7 box is in a full scale Windows domain, it's highly unlikely that the SQL 2000 box is in any domain (other than its own) and certainly isn't in the same domain as the SQL 7 boxSo I presume there is a DNS issue (possibly on both sides of the VLAN as they both have their own DNS servers and I know there have been issues with this in the past (I think it was just a case of the name not being in the DNS tables), though I'm not sure why that means that the connection still fails when I try to use IP.Anyone have any more advice or thoughts?Thanks in advancesteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-01-17 : 11:23:27
|
| you could edit the hosts file on each system and put the IP to host name lookup information there. The hosts file is checked first for name resolution, so this way we can bypass any problems you are having due to improper DNS configuration. Give that a shot and see what happens. If it works, then it is definitely DNS related and you need to pursue fixing that. If you still have other problems, then we move forward. The hosts file is usually located in C:\Windows\System32\drivers\etc-ec |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-17 : 18:25:20
|
| the service account (the one running sql agent) needs permissions on all of the servers/databases in the dts. has nothing to do with your permissions |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-01-25 : 04:24:32
|
| Thanks to all who tried to help with this. It seems that it has now been resolved. There was a DNS issue and that was compounded by named pipes being the default protocol on the SQL 7 box. Not entirely sure what these are but I get the impression from Ms Delaneys tome that they are not really needed any more.steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-25 : 04:27:30
|
| "Not entirely sure what these are""named pipes" you mean? Can't think of much reason to use them, we stock with TCP/IP - and AFAICR have to remove Named Pipes as its selected by default.Kristen |
|
|
|
|
Connection Issues