Problem with Linked servers between SQL Server 2005 and MSDE 2000
-
I have made an SQL Server 2005 instance (SERVER2005) be a linked server in an MSDE 2000 instance (MSDE2000). Everything is fine with the setup and I can run queries on tables in a database in Server 2005 instance from the MSDE 2000 instance as follows: SELECT * FROM [SERVER2005].[ContactsDB].[dbo].[Contacts] This is how I added the linked server to MSDE2000 instance: USE master; GO EXEC sp_addlinkedserver 'SERVER2005', N'SQL Server' GO The problem is that if I execute a stored procedure as follows: DECLARE @ContactID INT DECLARE @ContactName NVARCHAR(36) SET @ContactID = 1 EXEC [SERVER2005].[ContactsDB].[dbo].[dalsp_GetContactName] @ContactID, @ContactName OUTPUT the execution of the stored procedure is not error-free and the following error message accompanies the results: 'A severe error occurred on the current command. The results, if any, should be discarded.' If I link two SQL Server 2005 instances the execution is smooth. What can the problem be? Is it the provider? Why does executing a SELECT statement not bring and error yet executing a stored procedure bring an error?
-
I have made an SQL Server 2005 instance (SERVER2005) be a linked server in an MSDE 2000 instance (MSDE2000). Everything is fine with the setup and I can run queries on tables in a database in Server 2005 instance from the MSDE 2000 instance as follows: SELECT * FROM [SERVER2005].[ContactsDB].[dbo].[Contacts] This is how I added the linked server to MSDE2000 instance: USE master; GO EXEC sp_addlinkedserver 'SERVER2005', N'SQL Server' GO The problem is that if I execute a stored procedure as follows: DECLARE @ContactID INT DECLARE @ContactName NVARCHAR(36) SET @ContactID = 1 EXEC [SERVER2005].[ContactsDB].[dbo].[dalsp_GetContactName] @ContactID, @ContactName OUTPUT the execution of the stored procedure is not error-free and the following error message accompanies the results: 'A severe error occurred on the current command. The results, if any, should be discarded.' If I link two SQL Server 2005 instances the execution is smooth. What can the problem be? Is it the provider? Why does executing a SELECT statement not bring and error yet executing a stored procedure bring an error?
Did some research myself and found the cause. Thought I should share it with you. In the event that you add an SQL Server 2005 instance as a linked server to an MSDE 2000 instance, any stored procedure in SQL Server 2005 instance you choose to execute from the MSDE 2000 instance should not have a PRINT statement. This applies to even stored procedures called by the stored procedure you are calling directly. If you have statements like: PRINT N’Hello’ , then error, "A severe error occurred on the current command. The results, if any, should be discarded" will be returned. Have a nice day.