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 possible?

Lets say I have an integer value 2002012, I want to convert it to a string so I can cut the value 4 spaces so that it reads 2002 and then convert it back to an int to add one to it. The name of the field is PERIOD.

CONVERT(Int, SUBSTRING(CAST(PERIOD As Varchar),1,4)) + 1

Is that possible at all or will I get an error?

Is that possible at all or will I get an error?

Give a try, then. It's easy, isn't it?

|||

This is also possible

declare @.i int
select @.i = 2002012

select left(@.i,4) +1

they will both work see below

declare @.i int
select @.i = 2002012

select left(@.i,4) +1,CONVERT(Int, SUBSTRING(CAST(@.i As Varchar),1,4)) + 1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Is this connection string formated correctly?

Data Source=**COMPUTERNAME**\SQLEXPRESS;Initial Catalog=*CAT NAME*;User ID=*USR*;Password=*PASS*;Min Pool Size=5;Max Pool Size=10000;Connect Timeout=5

I can't connect to the DB. Everythime I try to connect to a page within my site that uses DB data I get an: "The resource cannot be found." error.

Hi,

sems ok, dom′t know why you stared the USERNAME and COMPUTERNAME tey should just have no stars or quotes. You can just take also one of the connectionstrings here: http://www.connectionstrings.com they are work fine.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||"The resource cannot be found." error is not raised by sql server db connection driver. So there is someother place in your app that cause the failure. Possbile IIS.

Is this an efficient way to return a comma string

Hi there,
I have created a sp and function that returns amongst other things a
comma seperated string of values via a one to many relationship, the
code works perfectly but i am not sure how to test its performance.. Is
this an efficient way to achieve my solution.. If not any suggestions
how i can improve it.. What are the best ways to check query speed?
MY SP:
CREATE PROCEDURE sp_Jobs_GetJobs
AS
BEGIN
SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
AS location, salary, summary, logo
FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
ORDER BY j.Inserted DESC
END
GO
---
MY Function:
CREATE FUNCTION fn_GetJobLocations (@.JobID int)
RETURNS varchar(5000) AS
BEGIN
DECLARE @.LocList varchar(5000)
SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
FROM Jobs_Locations l inner join List_Locations ll on
ll.LocationID = l.LocationID
WHERE l.JobID = @.JobID
RETURN @.LocList
END
Any help or guidance much appreciated...First of all, what you have in your UDF is a unsupported construct. It
exploits certain physical behaviours that might seem to work in some cases,
but can fail in a variety of situations. Being undocumented, it can change
between versions, service packs or patches.
Doing this in SQL Server invariably requires some level of looping, either
using a cursor, WHILE loop, recursion etc. In SQL 2005, there are some work
arounds using FOR XML method which in some cases can be complex and error
prone.
A good approach is to retrieve the resultset to the client side and generate
the string you need to create.
Also, just noted that you use sp_ prefix to your procedure which is not at
all recommended, since they are reserved for system procedures and can
affect performance adversely.
Anith|||3rd time tonight i've posted this solution, interesting :).
Anyway, something like this (SQL Server 2005) will do the trick and will
perform blisteringly...
select j.Id, j.Inserted, Title, Reference,
(
select location_name + ',' as [text()]
from Jobs_Locations soi
where soi.Job_ID = t.Job_ID
order by location_name
for xml path( '' ), type
)
from Jobs_Jobs as j
It will give one line per job and concatenating each location seperating
them by commas.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<anthonykallay@.hotmail.com> wrote in message
news:1133804859.768819.33930@.g14g2000cwa.googlegroups.com...
> Hi there,
>
> I have created a sp and function that returns amongst other things a
> comma seperated string of values via a one to many relationship, the
> code works perfectly but i am not sure how to test its performance.. Is
> this an efficient way to achieve my solution.. If not any suggestions
> how i can improve it.. What are the best ways to check query speed?
>
> MY SP:
> CREATE PROCEDURE sp_Jobs_GetJobs
> AS
> BEGIN
> SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
> AS location, salary, summary, logo
> FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
> ORDER BY j.Inserted DESC
>
> END
> GO
> ---
> MY Function:
> CREATE FUNCTION fn_GetJobLocations (@.JobID int)
>
> RETURNS varchar(5000) AS
> BEGIN
> DECLARE @.LocList varchar(5000)
> SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
> FROM Jobs_Locations l inner join List_Locations ll on
> ll.LocationID = l.LocationID
> WHERE l.JobID = @.JobID
> RETURN @.LocList
>
> END
>
> Any help or guidance much appreciated...
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?No.
And next time please don't re-post your question in every single group.
Asking in ONE group should be sufficient.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%23fR04M2YGHA.3704@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?no
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:ewcJ9M2YGHA.508@.TK2MSFTNGP02.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?no
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:ewcJ9M2YGHA.508@.TK2MSFTNGP02.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?Only 8000 characters. No more
Nathan H. Omukwenyi
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>|||I don't believe there is a limit other than available memory.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathan H. Omukwenyi" <nathan@.e-tools.com> wrote in message
news:eXO8V8wZGHA.428@.TK2MSFTNGP02.phx.gbl...
> Only 8000 characters. No more
> Nathan H. Omukwenyi
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
>|||I believe the only limitation is the underlying data type in sql. For
example a varchar can only be max 8000 chars. but text could be up to 2
gigs.
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>|||Agreed. I had only tested with the varchar SQL data type. Testing with TEXT
shows that it can handle much more but reaching the maximum is taking too
much time and resources. So I suppose Dan could be right about available
memory.
Nathan H. Omukwenyi
"Jeremy" <nospam@.here.com> wrote in message
news:O5fxDqBaGHA.4752@.TK2MSFTNGP02.phx.gbl...
>I believe the only limitation is the underlying data type in sql. For
> example a varchar can only be max 8000 chars. but text could be up to 2
> gigs.
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
>

is there any IsString() function in vb.net?

hi

i want to check that only string in textbox. the textbox shoould enterd the strings so is there IsString() function in vb.net

plz help me....

Hi,
first of all your subject does say nothing about your problem, so many people won't read it..
secondly try to use the forum search, your problem has been discusses many times..
for example:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=432801&SiteID=1

Is there an equivalent Replicate() function in Access

Hi,

I am trying to build a string from a datetime datetype and want to make sure that if a day or month is only one digit long then it is padded with a leading 0.

In SQL I can do it like this;

declare @.datetime datetime

select @.datetime = getdate()

select 'TPR'

+ replicate('0', 2 - len(datepart(dd,@.datetime))) + convert(varchar(2),datepart(dd,@.datetime))

+ replicate('0', 2 - len(datepart(mm,@.datetime))) + convert(varchar(2),datepart(mm,@.datetime))

+ convert(varchar(4),datepart(yyyy,@.datetime))

Do you know how I can do the same thing in Access?

Thanks for your help

It would be SO-O-O-O much easier to do it in this manner:

SELECT right( '00' + cast( datepart( day, @.DateTime ) AS varchar(2) ), 2 )

(Should work in Access AND SQL Server.)

But really, wouldn't something like this be a better solution: (this is SQL)

SELECT ( 'TPR' + SELECT replace( convert( varchar(10), @.DateTime, 101 ), '/', '' ))

For Access, you would have to use the FORMAT function instead of convert. Refer to the help for FORMAT().

|||

Yes Buddy.. You have it..

STRING(<number>, <Char>)

example:

String(5,"M") => MMMMM