Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    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.

    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?

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    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.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •