SA Developer .NET

terencen:

 
Dertest17.TestPro is a SQL Server 2005 database and the database where the stored procedure

"pr_MOVE_Defects_test" is located is a SQL Server 2000 database. I think that it might be the problem.

It shouldn't be. 2000 can talk to 2005 without a problem. Can you have a look at the linked server config. What provider it's using, what options are set?

Is there any chance of temporarily (for a test) disabling the constraints on the remote table?
 

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

the provider is Microsoft OLE DB Porvider for SQL Server. It has the following server options:

 

Collation Compatible: False

Data Access: True

Rpc: True

Rpc Out: False

Use Remote Collation: True

Connection Timeout: 0

Query Timeout: 0

 

I'll see if I can drop the contraints on the table.

I've tried creating the stored procedure remotely(on Dertest17.TestPro which is a SQL Server 2005 DB) by backing up and restoring the original SQL 2000 database on Dertest17.TestPro. The stored proc worked fine, so it's not the contraints on the table that's a problem.

Strange.Check how DTS is configured on the two machines please. Check that it's started, what the security settings are, if the servers are clustered what group it's in.

You may have to contact customer support on this one. I'm almost out of ideas on what could be wrong.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

I did a SELECT @@VERISON and I get this back:

 

"It's spelt Verizon, you idiot! But darn, IS sued us and kinda won and we had to give all their documents back and we had to pay them moneyz:( :( Epic fail:("

---

And so the kief looked and lo, it was kief.