Unanswered: linked 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.
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.