Friday, March 9, 2012

server w/ text field gets Connection Broken error

We have a stored proc on Server B called:

my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL

this proc calls:

my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL

This second stored procedure just selects back an ID that is passed to it (to keep things simple).

If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.

It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.

Any ideas?If I change the linked server from SQL Server to ODBC I get this error message:

Server: Msg 0, Level 19, State 1, Line 15
SqlDumpExceptionHandler: Process 244 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.|||Here is the DML:

-- Run on DATABASE_1
CREATE PROCEDURE my_sp_server_a
@.the_id int, @.the_text TEXT = NULL
AS

SELECT @.the_id

GO

-- Run on DATABASE_2
CREATE PROC my_sp_server_b @.my_text TEXT = NULL
AS

EXEC MY_LINKED_SERVER.DATABASE_1.DBO.my_sp_server_a @.the_id = 1, @.the_text = @.my_text

GO

No comments:

Post a Comment