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

I have a statement like
SELECT A,B,C, ....,
dbo.TEST_GETATTENDEEDETAILS(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

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
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Yashgt" <yashgt@gmail.com> wrote in message
news:dedaa8ab-62be-4cae-a215-a9a2c79e5b22@n38g2000prl.googlegroups.com...
>I am facing a wierd problem with SQL Server standard edition SP2.
>
> I have a statement like
> SELECT A,B,C, ....,
> dbo.TEST_GETATTENDEEDETAILS(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

Yashgt (yashgt@gmail.com) writes:
> I am facing a wierd problem with SQL Server standard edition SP2.
>
> I have a statement like
> SELECT A,B,C, ....,
> dbo.TEST_GETATTENDEEDETAILS(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@sommarskog.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

>> 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.

--CELKO-- (jcelko212@earthlink.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@sommarskog.se

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

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_GETATTENDEEDETAILS ( @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_JOBTITLE,'') +',' +
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@earthlink.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@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yashgt (yashgt@gmail.com) 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_GETATTENDEEDETAILS ( @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_JOBTITLE,'') +',' +
> 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@sommarskog.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

Erland Sommarskog wrote:

> --CELKO-- (jcelko212@earthlink.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".

Everything works fine after installing SP4.

Yash (yashgt@gmail.com) writes:
> Everything works fine after installing SP4.

Great to hear that it worked out. Thanks for reporting back!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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