SQL 2000: Transport-level error on SELECT statement

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Yashgt
    #1

    SQL 2000: Transport-level error on SELECT statement

    I am facing a wierd problem with SQL Server standard edition SP2.

    I have a statement like
    SELECT A,B,C, ....,
    dbo.TEST_GETATT ENDEEDETAILS(CI .SI_ID) AS SI_ATTENDEENAME ,
    X,Y,Z
    from .....

    The function emits a CSV string. When I run the above command, I get
    an error that says:

    A transport-level error has occurred when receiving results from the
    server. (provider: Shared Memory Provider, error: 0 - The system
    cannot open the file.)

    The client loses contact with the DB server. From the logs I can see
    that SQL server gets autostared.
    If I comment out the function call, the statement runs fine. If I
    uncomment the function call then, it works fine and gives the expected
    result.

    What could be the reason?

    Thanks,
    Yash
    Tags: None
    • Tibor Karaszi
      #2
      Re: SQL 2000: Transport-level error on SELECT statement

      Most probably you have some bug or issue inside that function. You need to talk to whoever provided
      that function so that they can rectify this bug.

      --
      Tibor Karaszi, SQL Server MVP
      403 Forbidden
      http://www.karaszi.com/sqlserver/default.asp

      http://sqlblog.com/blogs/tibor_karaszi



      "Yashgt" <yashgt@gmail.c omwrote in message
      news:dedaa8ab-62be-4cae-a215-a9a2c79e5b22@n3 8g2000prl.googl egroups.com...
      >I am facing a wierd problem with SQL Server standard edition SP2.
      >
      I have a statement like
      SELECT A,B,C, ....,
      dbo.TEST_GETATT ENDEEDETAILS(CI .SI_ID) AS SI_ATTENDEENAME ,
      X,Y,Z
      from .....
      >
      The function emits a CSV string. When I run the above command, I get
      an error that says:
      >
      A transport-level error has occurred when receiving results from the
      server. (provider: Shared Memory Provider, error: 0 - The system
      cannot open the file.)
      >
      The client loses contact with the DB server. From the logs I can see
      that SQL server gets autostared.
      If I comment out the function call, the statement runs fine. If I
      uncomment the function call then, it works fine and gives the expected
      result.
      >
      What could be the reason?
      >
      Thanks,
      Yash

        Comment

        • Erland Sommarskog
          #3
          Re: SQL 2000: Transport-level error on SELECT statement

          Yashgt (yashgt@gmail.c om) writes:
          I am facing a wierd problem with SQL Server standard edition SP2.
          >
          I have a statement like
          SELECT A,B,C, ....,
          dbo.TEST_GETATT ENDEEDETAILS(CI .SI_ID) AS SI_ATTENDEENAME ,
          X,Y,Z
          from .....
          >
          The function emits a CSV string. When I run the above command, I get
          an error that says:
          >
          A transport-level error has occurred when receiving results from the
          server. (provider: Shared Memory Provider, error: 0 - The system
          cannot open the file.)
          >
          The client loses contact with the DB server. From the logs I can see
          that SQL server gets autostared.
          If I comment out the function call, the statement runs fine. If I
          uncomment the function call then, it works fine and gives the expected
          result.
          Examine the SQL Server log for crash dumps. The most likely reason is
          that the function provokes a bug in SQL Server.


          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

          Links for SQL Server Books Online:
          SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
          SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
          SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

            Comment

            • --CELKO--
              #4
              Re: SQL 2000: Transport-level error on SELECT statement

              >The function emits a CSV string. <<

              Since you are not going to follow even First Normal Form, why use SQL
              at all? Columns are suppose to be scalar values and not list
              structures. Get rid of the non-1NF code and write SQL instead of a
              weird dialect.

                Comment

                • Erland Sommarskog
                  #5
                  Re: SQL 2000: Transport-level error on SELECT statement

                  --CELKO-- (jcelko212@eart hlink.net) writes:
                  >>The function emits a CSV string. <<
                  >
                  Since you are not going to follow even First Normal Form, why use SQL
                  at all? Columns are suppose to be scalar values and not list
                  structures. Get rid of the non-1NF code and write SQL instead of a
                  weird dialect.
                  Yeah Joe, tell the people who want a comma-separated list on their
                  reports that they are violating first normal form. They will probably
                  tell you it is the most normal form of presentation they can think of...



                  --
                  Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                  Books Online for SQL Server 2005 at
                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                  Books Online for SQL Server 2000 at
                  http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                    Comment

                    • Yashgt
                      #6
                      Re: SQL 2000: Transport-level error on SELECT statement


                      The ERRORLOG only has messages of the server starting up. There is no
                      messgae of the crash.

                      Here is the code of the function:
                      REATE FUNCTION TEST_GETATTENDE EDETAILS ( @CLAIMITEMID INT )
                      RETURNS VARCHAR(8000)
                      AS
                      BEGIN
                      DECLARE @RETURNSTRING VARCHAR(8000)
                      SET @RETURNSTRING = ''

                      IF @CLAIMITEMID IS NOT NULL
                      BEGIN
                      SELECT @RETURNSTRING =
                      @RETURNSTRING + ',' +
                      isnull(SI_NAME, '') +',' + isnull(SI_JOBTI TLE,'') +',' +
                      isnull(SI_NOTES ,'')
                      FROM SI_ATTENDEE (NOLOCK)
                      WHERE SI_CLAIMITEMID = @CLAIMITEMID -- This lookup is an index seek
                      END

                      RETURN @RETURNSTRING

                      END
                      GO

                      I know for sure that the CSV will never exceed 8000 characters. We are
                      creating a CSV because that is the way it will be shown in a report.

                      Thanks,
                      Yash

                      Erland Sommarskog wrote:
                      --CELKO-- (jcelko212@eart hlink.net) writes:
                      >The function emits a CSV string. <<
                      Since you are not going to follow even First Normal Form, why use SQL
                      at all? Columns are suppose to be scalar values and not list
                      structures. Get rid of the non-1NF code and write SQL instead of a
                      weird dialect.
                      >
                      Yeah Joe, tell the people who want a comma-separated list on their
                      reports that they are violating first normal form. They will probably
                      tell you it is the most normal form of presentation they can think of...
                      >
                      >
                      >
                      --
                      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
                      >
                      Books Online for SQL Server 2005 at
                      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                      Books Online for SQL Server 2000 at
                      http://www.microsoft.com/sql/prodinf...ons/books.mspx

                        Comment

                        • Erland Sommarskog
                          #7
                          Re: SQL 2000: Transport-level error on SELECT statement

                          Yashgt (yashgt@gmail.c om) writes:
                          The ERRORLOG only has messages of the server starting up. There is no
                          messgae of the crash.
                          Strange. That would indicate that something goes wrong in SQL Client.
                          What happens if you run the statement from SQLCMD or OSQL?

                          And since the error talks about shared memory, what happens when
                          you connect from a different machine
                          Here is the code of the function:
                          REATE FUNCTION TEST_GETATTENDE EDETAILS ( @CLAIMITEMID INT )
                          RETURNS VARCHAR(8000)
                          AS
                          BEGIN
                          DECLARE @RETURNSTRING VARCHAR(8000)
                          SET @RETURNSTRING = ''
                          >
                          IF @CLAIMITEMID IS NOT NULL
                          BEGIN
                          SELECT @RETURNSTRING =
                          @RETURNSTRING + ',' +
                          isnull(SI_NAME, '') +',' + isnull(SI_JOBTI TLE,'') +',' +
                          isnull(SI_NOTES ,'')
                          FROM SI_ATTENDEE (NOLOCK)
                          WHERE SI_CLAIMITEMID = @CLAIMITEMID -- This lookup is an index seek
                          END
                          >
                          RETURN @RETURNSTRING
                          Beware that this function relies on undefined behaviour, and the result
                          from the function (when it works) may not be what you expect it to be.
                          See http://support.microsoft.com/default.aspx?scid=287515. Note
                          particular the first sentence under CAUSE.
                          --
                          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                          Links for SQL Server Books Online:
                          SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
                          SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
                          SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

                            Comment

                            • Ed Murphy
                              #8
                              Re: SQL 2000: Transport-level error on SELECT statement

                              Erland Sommarskog wrote:
                              --CELKO-- (jcelko212@eart hlink.net) writes:
                              >>>The function emits a CSV string. <<
                              >Since you are not going to follow even First Normal Form, why use SQL
                              >at all? Columns are suppose to be scalar values and not list
                              >structures. Get rid of the non-1NF code and write SQL instead of a
                              >weird dialect.
                              >
                              Yeah Joe, tell the people who want a comma-separated list on their
                              reports that they are violating first normal form. They will probably
                              tell you it is the most normal form of presentation they can think of...
                              The proper answer is "let the reporting layer combine multiple values
                              into a comma-separated list".

                                Comment

                                • Yash
                                  #9
                                  Re: SQL 2000: Transport-level error on SELECT statement

                                  Everything works fine after installing SP4.

                                    Comment

                                    • Erland Sommarskog
                                      #10
                                      Re: SQL 2000: Transport-level error on SELECT statement

                                      Yash (yashgt@gmail.c om) writes:
                                      Everything works fine after installing SP4.
                                      Great to hear that it worked out. Thanks for reporting back!

                                      --
                                      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                                      Books Online for SQL Server 2005 at
                                      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                                      Books Online for SQL Server 2000 at
                                      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                                        Comment

                                        Previous template Next
                                        Working...