| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2007-09-24 : 03:59:34
|
| Hi all,one week ago we installed Windows Server 2003 SP2 on one of our production sql-servers (sql 2000) and everything seems to work fine except one job that transfers data to a linked server. The query is really simple:INSERT INTO table SELECT * FROM linkedserver.database.dbo.table The transfer is initiated from the target-server and the source server is a cluster. The really weird thing is that this job runs ever morning at 6 am and some days it works and some days it doesn't! I'm suspecting it has something to do with the distributed transactions coordinator but I'm not really sure where to look... What could be going on here?EDIT: This job has been running every morning for over a year without a single problem so I'm pretty sure it has something to do with SP2.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-24 : 04:05:30
|
New firewall rules?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-09-24 : 04:09:55
|
| No nothing like that...both servers are inside the same network and the same domain, hence behind the same firewalls. And the fact that the transfer works some days and not others rules out the firewall option.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-24 : 04:22:28
|
Do the log files give any clues?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-09-24 : 06:14:30
|
Actually it does! There is some sort of login-error recorded every few minutes since the install of SP2 so I'll have to investigate that further. Thanx so far  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-09-25 : 11:03:24
|
Well, to see if the login-error was the problem we switched the cluster nodes and when we did that the error messages disappeared from the logs. However, this didn't fix the problem with the transfer so I'm basically back to square one. This is the error message I get from the jobstep:Sql Severity 16Sql Message ID 7300MessageExecuted as user: domain\unsername. OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetNextRows returned 0x80004005:]. [SQLSTATE 01000] (Error 7300). The step failed. --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-25 : 11:08:01
|
Are you referring to the cluster node name or individual machine in the connection string?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-09-25 : 11:19:06
|
| I'm referring to the logical name, "mydbserver" in this example:mydbserver -> logical namemydbserver_node1 -> physical namemydbserver_node2 -> physical name--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-09-25 : 11:38:14
|
| When I think of it we did have some trouble with this transfer a while back and the resolution was to isntall the sql server hotfix referred to in this article: http://support.microsoft.com/kb/920930The error message was different but could it be that the Win2k3 SP "invalidated" the hotfix and that I have to install it again or something...?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
Linked server problems after Win2k3 SP2