Is this T-SQL dangerous/not reliable

We are trying to create a string of column names for a
given table. (SQL 2000, SP3a--W2K Server, SP3)
The included code here works well so far, but I remember
reading in the past that the optimizer can sometimes break
this approach to creating strings.
If all column names are not null and the total len(string)
does not exceed varchar(8000), is this construct safe? If
not, why?
TIA, -- Brian
declare @.FldStr varchar(8000)
select @.FldStr = ''
select @.FldStr = @.FldStr + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.TableName
order by ORDINAL_POSITIONThis behavior isn't documented and also isn't supported. I believe someone
posted an example not too long ago that showed this syntax breaking, but
can't seem to find it on a quick search of google.
Why not return the set of column names to your application, and have the
application assemble them into a string?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
>
>
>|||Thanks, Aaron.
I'm taking it further and doing joins dynamically with
sp_executesql, thus would like to keep it on the backend.
Would sending the field names to a #temp table with an
identity field be a better to go, looping through 1 to n
records to build the string of field names?
I'll also try to find the post you mentioned. I'm very
interested in the behind the scenes stuff that would cause
this to break. If you find it at a later point, I'd
greatly appreciate it if you could forward it to me.
(brianglinebaugh@.yahoo.com)
Thanks very much for your time, -- Brian
>--Original Message--
>This behavior isn't documented and also isn't supported.
I believe someone
>posted an example not too long ago that showed this
syntax breaking, but
>can't seem to find it on a quick search of google.
>Why not return the set of column names to your
application, and have the
>application assemble them into a string?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c401c3b076$8d083260$a001280a@.phx.gbl...
>> We are trying to create a string of column names for a
>> given table. (SQL 2000, SP3a--W2K Server, SP3)
>> The included code here works well so far, but I remember
>> reading in the past that the optimizer can sometimes
break
>> this approach to creating strings.
>> If all column names are not null and the total len
(string)
>> does not exceed varchar(8000), is this construct safe?
If
>> not, why?
>> TIA, -- Brian
>> declare @.FldStr varchar(8000)
>> select @.FldStr = ''
>> select @.FldStr = @.FldStr + COLUMN_NAME
>> from INFORMATION_SCHEMA.COLUMNS
>> where TABLE_NAME = @.TableName
>> order by ORDINAL_POSITION
>>
>>
>>
>
>.
>|||"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
How about
set @.FldStr = '*'
?
David|||I don't know of a KB that states this but it will fail most of the time in
anything other than a simple select with no order by, join etc. You do not
want to put that code into your production env. Create a cursor and di it
that way if you must have it in a particular order.
--
Andrew J. Kelly
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2c01c3b07f$eef7fab0$a601280a@.phx.gbl...
> Thanks, Aaron.
> I'm taking it further and doing joins dynamically with
> sp_executesql, thus would like to keep it on the backend.
> Would sending the field names to a #temp table with an
> identity field be a better to go, looping through 1 to n
> records to build the string of field names?
> I'll also try to find the post you mentioned. I'm very
> interested in the behind the scenes stuff that would cause
> this to break. If you find it at a later point, I'd
> greatly appreciate it if you could forward it to me.
> (brianglinebaugh@.yahoo.com)
> Thanks very much for your time, -- Brian
>
>
>
> >--Original Message--
> >This behavior isn't documented and also isn't supported.
> I believe someone
> >posted an example not too long ago that showed this
> syntax breaking, but
> >can't seem to find it on a quick search of google.
> >
> >Why not return the set of column names to your
> application, and have the
> >application assemble them into a string?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"Brian" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> >>
> >> We are trying to create a string of column names for a
> >> given table. (SQL 2000, SP3a--W2K Server, SP3)
> >>
> >> The included code here works well so far, but I remember
> >> reading in the past that the optimizer can sometimes
> break
> >> this approach to creating strings.
> >>
> >> If all column names are not null and the total len
> (string)
> >> does not exceed varchar(8000), is this construct safe?
> If
> >> not, why?
> >>
> >> TIA, -- Brian
> >>
> >> declare @.FldStr varchar(8000)
> >> select @.FldStr = ''
> >>
> >> select @.FldStr = @.FldStr + COLUMN_NAME
> >> from INFORMATION_SCHEMA.COLUMNS
> >> where TABLE_NAME = @.TableName
> >> order by ORDINAL_POSITION
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
the optimizer behavior luckily generated a plan for that concatenated the
values in a way you intended. However, this is a risky proposition, since
this is undocumented, inconsistent and thus unreliable. Here are some trials
that can break your luck.
--#1 ( Add a TOP clause)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT TOP 100 PERCENT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#2 ( Add a DISTINCT)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT DISTINCT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#3 ( Add a CROSS JOIN)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1, (SELECT 1) D (n)
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#4 (Use another view)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE TABLE_NAME = 'test'
ORDER BY TABLE_NAME;
SELECT @.FldStr;
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" wrote
> In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
> the optimizer behavior luckily generated a plan for that concatenated the
> values in a way you intended..
Where I live everyone drives a SUV.I drive a Benz 560 SL.
They keep telling me 'this is a risky proposition, since
it's undocumented, inconsistent and thus unreliable' :~)

Is this piece of code dangerous?

I'm building a site, and while stress testing it I received a few exceptions when the SQL Server was under relatively high load. Originally I was opening the connection when required in a particular Sub as follows (and then closing it when I was finished with it):

If Not MyConnection.State = ConnectionState.Open Then MyConnection.Open()

The probelm however was that from time to time the connection state was Opening instead of Closed or Open. So I am considering using the following piece of code instead:

If MyConnection.State = ConnectionState.Connecting Then
Do Until MyConnection.State = ConnectionState.Open

Loop
ElseIf MyConnection.State = ConnectionState.Broken Or MyConnection.State = ConnectionState.Closed Then
MyConnection.Open()
End If

I'm a little worried about the Do...Loop in there, but I don't see how it should be a problem. Any thoughts?

plenderj:

If MyConnection.State = ConnectionState.Connecting Then
Do Until MyConnection.State = ConnectionState.Open

Loop

I'm a little worried about the Do...Loop in there, but I don't see how it should be a problem. Any thoughts?

Yes, it is a problem. While you're waiting, doing nothing, your thread is chewing up CPU, which could be better used by other threads and processes, including the SQL client. A much better solution would be to stick a Thread.Sleep(10) in your loop. It's a small enough interval that it won't hurt, but it won't peg your CPU at 100%.

A better solution yet would be to wait on the StateChange event of the connection, because then you wouldn't use ANY CPU while waiting on the connection, but it would require more code, and if this doesn't happen often, wouldn't buy you much.

|||

mpswaim:

A much better solution would be to stick a Thread.Sleep(10) in your loop. It's a small enough interval that it won't hurt, but it won't peg your CPU at 100%.

DOH! How did I forget about that lol. Thanks for spotting it :D