is user dba

Is this statement correct :
In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not valid.
One must use suser_sname instead of suser_name.
See example below:
select 1 where suser_sname()='sa'
go
And if so is there a patch available to correct this ?
Don
"don" <don@.discussions.microsoft.com> wrote in message
news:549733A3-F0B5-4CAD-819A-141BCAF05497@.microsoft.com...
> Is this statement correct :
> In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not
> valid.
> One must use suser_sname instead of suser_name.
> See example below:
> select 1 where suser_sname()='sa'
> go
> And if so is there a patch available to correct this ?
>
Um.
From BOL: SUSER_NAME always returns NULL when used in Microsoft SQL
ServerT 2000. This system built-in function is included only for backward
compatibility. Use SUSER_SNAME instead.
But suser_sname() is not right either. Since many different logins may be
system administrators.
Try
select IS_SRVROLEMEMBER('sysadmin')
David

is user dba

Is this statement correct :
In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not valid.
One must use suser_sname instead of suser_name.
See example below:
select 1 where suser_sname()='sa'
go
And if so is there a patch available to correct this ?
Don"don" <don@.discussions.microsoft.com> wrote in message
news:549733A3-F0B5-4CAD-819A-141BCAF05497@.microsoft.com...
> Is this statement correct :
> In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not
> valid.
> One must use suser_sname instead of suser_name.
> See example below:
> select 1 where suser_sname()='sa'
> go
> And if so is there a patch available to correct this ?
>
Um.
From BOL: SUSER_NAME always returns NULL when used in Microsoft SQL
ServerT 2000. This system built-in function is included only for backward
compatibility. Use SUSER_SNAME instead.
But suser_sname() is not right either. Since many different logins may be
system administrators.
Try
select IS_SRVROLEMEMBER('sysadmin')
David

Is this statement vulnerable to code injection?

I found a bit of code my comapny got froma third party group that I
thought was vulnerable to code injection but I can't prove it since I
can't figure out a way to hack it. I can point the boss to sources that
say this type of programming is a Bad Thing (TM) but they want more.
Can someone help me?
The code is in asp and looks something like the following:
sLookupText = <users free text input>
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
sLookupText = Trim(sLookupText)
...
sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
Have com object execute sql.
The tricky part is that the dynamic sql is executed by the same third
party venders com objects so I don't see what happens to the query
between the time the asp hands it off and it is executed.
This is the error I get from the com obvject when I run it with the
input 1/'';select * from x;
Zero records match the criteria '1/'';select * from x;'
If I take the exact same query and run it in Query Analyzer I get the
message
Unclosed quotation mark before the character string ...
Which says I'm wrong when I think \' escapes the ' mark. So now I'm
wondering if maybe the code IS safe since the com object is sending a
different message and all the quotes are doubled in the message and all
the % are stripped.
Do the two lines
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
make it safe?
Thoughts?Michael, the only way to know what the com object sent to SQL Server is usin
g
SQL Profiler. I think the error show the com object is replacing de ' with
'', but using SQL Profiler is the easer way to know.|||sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
sLookupText = "1;-- ALTER DATABASE MSDB SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;GO;DROP DATABASE MSDB;--"
Upps... That can be something very wacky. Dynamic SQL is the hell for
security, so better have a chat with the vendor :-)
HTH, Jens Suessmeyer.|||I, uh, don't think I want to test that particular example! that drop
database part looks scary, though I'm guessing that rollback immediate
does something to undo it?
Anyway, from what I can tell, with your intput the query will be
coverted into.
select <parameter list> from table where <some field> like '1;-- ALTER
DATABASE MSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;GO;DROP DATABASE
MSDB;--%' order by <order clause>
As far as I can tell you never escaped out of the single quotes! Why do
you think any sql is injected?|||Michael wrote:
> I found a bit of code my comapny got froma third party group that I
> thought was vulnerable to code injection but I can't prove it since I
> can't figure out a way to hack it. I can point the boss to sources
> that say this type of programming is a Bad Thing (TM) but they want
> more. Can someone help me?
> The code is in asp and looks something like the following:
> sLookupText = <users free text input>
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> sLookupText = Trim(sLookupText)
> ...
> sSQL = "select <parameter list> from table where <some field> like " +
> sLookupText + "% order by <order clause>"
> Have com object execute sql.
> The tricky part is that the dynamic sql is executed by the same third
> party venders com objects so I don't see what happens to the query
> between the time the asp hands it off and it is executed.
> This is the error I get from the com obvject when I run it with the
> input 1/'';select * from x;
> Zero records match the criteria '1/'';select * from x;'
> If I take the exact same query and run it in Query Analyzer I get the
> message
> Unclosed quotation mark before the character string ...
> Which says I'm wrong when I think ' escapes the ' mark. So now I'm
> wondering if maybe the code IS safe since the com object is sending a
> different message and all the quotes are doubled in the message and
> all the % are stripped.
> Do the two lines
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> make it safe?
>
They make it safe when the hacker uses ' to close the quote. However, what
if he gets clever and uses the char function? OK you can do some
validation/replacing on that. But then, what if he switches to one of the
other techniques to be found in these articles:
http://www.nextgenss.com/papers/adv...l_injection.pdf
http://www.nextgenss.com/papers/mor...l_injection.pdf
The safest way to run the above query is by using parameters. There is no
way to inject sql if data is being passed via parameters.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Llike Bob already said, he could use the CHAR function.
BTW, Rollback won=B4t do any undo of the ALTER DATABASE rather than
rolling back all transaction which are currently running.
HTH, jens Suessmeyer.|||Well, I found another section of the web site with a less protected
query. I was able to convert the query (where the stuff in <> is either
table specific information not relevant to the question or user
input)...
select <columnlist> from <table> where <column> like '<users input>%'
order by <columnlist>
to
select <columnlist> from <table> where <column> like '%'; update
<table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
by <columnlist>
by entering
%'; update <table2> set <column> = 1 where <pk column> = <pk
value>;commit;--
as the input. Nice, huh! But when I checked the database nothing
happened. When I went to the DBA he used Embarcadero DBArtisan
(whatever that is) to verify that the test was sent as expected. It
was, but no injection attack worked! Any idea why? Does sqlserver allow
multiple commands on a single line? I seem to recall on my Internet
travels that one guru said that it didn't.
I really don't like seeing a dynamic query being constructed like this
but I can't seem to prove it is a problem :( I feel it is WRONG in my
bones, but I need proof to leverage a change.|||Can anyone explain why the injection attack did not work?|||Can you post a repro, so we have something to test?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michael" <miteke@.gmail.com> wrote in message
news:1143732749.601504.212240@.e56g2000cwe.googlegroups.com...
> Can anyone explain why the injection attack did not work?
>|||On 29 Mar 2006 11:53:30 -0800, Michael wrote:

>Well, I found another section of the web site with a less protected
>query. I was able to convert the query (where the stuff in <> is either
>table specific information not relevant to the question or user
>input)...
>select <columnlist> from <table> where <column> like '<users input>%'
>order by <columnlist>
>to
>select <columnlist> from <table> where <column> like '%'; update
><table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
>by <columnlist>
>by entering
>%'; update <table2> set <column> = 1 where <pk column> = <pk
>value>;commit;--
>as the input. Nice, huh! But when I checked the database nothing
>happened. When I went to the DBA he used Embarcadero DBArtisan
>(whatever that is) to verify that the test was sent as expected. It
>was, but no injection attack worked! Any idea why? Does sqlserver allow
>multiple commands on a single line? I seem to recall on my Internet
>travels that one guru said that it didn't.
Hi Michael,
SQL Server definitely allows multiple commands on one line.
Have you checked that the command that was eventually actually sent to
the server for execution was exactly as above? I'd recommend you to
insert a PRINT statement in the application just before the SQL gets
sent to the server. Or, if you can't touch the application, set up
Profiler to run a trace. A smart parser that removes quotes, reserverd
words, punctuation marks and such would have prevented your attempt to
inject SQL.
If you have verified that this is the actual code that ran, then you can
be 100% sure that SQL Server tried to do the update. It might have
failed because the SQL is executed in the context of an account with
limited permissions (that would have resulted in an error - did you see
an error when you tested it? Maybe the app has intercepted the error
message? Again, running Profiler might reveal more info). Another reason
why this update might fail is an AFTER trigger initiating a ROLLBACK, or
an INSTEAD OF trigger simply disregarding your change; in those cases,
you don't even get an error message.
Hugo Kornelis, SQL Server MVP

is this possible?

hi

I would like to display agregated data, as well as my image, but it seems that this requires me to put my image file field in my group by statement too, but sql does not seem to allow sorting of images. Is what I am trying to possible? Or do I have to do both things separate?

here is the code:

select tbltimesheets.weekno, sum(tblentries.hrsnorm) as hrsnorm, sum(tblentries.hrspot) as hrspot, sum(tblentries.hrsnpot) as hrsnpot, tblusers.username, images.imagefile from
tbltimesheets
inner join tblentries on tbltimesheets.tskey = tblEntries.TSKey
inner join tblusers on tbltimesheets.userkey = tblusers.userkey
inner join images on images.userkey = tblusers.userkey
group by tbltimesheets.weekno, tblusers.username, images.imagefileYou have to ddo the thing separate. YOu can use some smart SQL for this, though, working, and still do it in one query.

Or, instead summing, you could use another method (like one returning the first image in the group).

Is this possible......??

Say I have a table with one field and there are 26 records in it... A, B, C, D, E, ... etc.

If I do a select statement on that table I get 26 records, one for each field. (select fieldName from tblName)

Is it possible to set a variable equal to a single string off of the select statement and delimit it with a chosen delimiter (ie "A,B,C,D,E,F......")

Thank You for the help!!!With which DBMS? There is no standard SQL answer to this.|||Using MS SQL 7|||Looking for something like

set @.stringName = (select fieldName & ',' from tblName)

So that @.stringName is set to a string 'A,B,C,D,E,....'|||What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str|||Originally posted by snail
What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.|||Originally posted by gman_gsxr750
That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.

Just try and you'll see...|||Originally posted by snail
Just try and you'll see... Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!|||Originally posted by gman_gsxr750
Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!

Why not?|||Originally posted by snail
Why not? Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?|||Originally posted by gman_gsxr750
Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?

I have no idea...|||Originally posted by snail
I have no idea... OK, last question, hopefully you can help me with this.

Here's my code:
declare @.idpeople int
set @.idpeople = 200002
declare @.str varchar(8000)
set @.str = ''

select @.str = @.str + ',' + ideventcode from tblPeopleEvents where idpeople = @.idpeople

let's say this returns the following ',CXL,AS' (two codes CXL and AS)

this works fine, but if I add an order by clause to it (so it returns AS,CXL instead), I only get one of the two values (CXL)

any ideas on how far I can take the select portion (where, order by, group by, etc)?|||it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)|||OK, here's my final code. I used a subquery to get the result set the way I needed it. Much thanks to Snail for the help. And to r937 for the sarcasm ;).

create procedure spGetEventString
@.idpeople int,
@.eventString varchar(255) OUTPUT

as

set @.eventString = ''

-- Create string of Event codes
-- use sub query to order result set
select @.eventString = @.eventString + rTPE.ideventcode
from
(
select top 100 idEventCode + ',' as idEventCode
from tblPeopleEvents
where idpeople = @.idpeople
order by idEventCode
) as rTPE

--Remove Trailing Comma
set @.eventString = left(@.eventString,len(@.eventString)-1)

return|||Originally posted by r937
it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)

I am not a magician I am only learning... ;)|||I think it should be called the Loophole query, because it doesn't look like it should work, but it does.|||Originally posted by blindman
I think it should be called the Loophole query, because it doesn't look like it should work, but it does.

That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593|||Originally posted by Enigma
That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593 Yep, that's it. Works like a charm too!|||This type of query is the coolest thing I've learned from DBForums, but I haven't seen any Microsoft Documentation that talks about it. That's why it seems like a loophole to me.

Does anybody know of any BOL or MS Support references regarding this self-referential query?|||Thats what it should be called -->"A self-referential query"|||and it even does what Enigma was asking in the referenced post:

select @.str=@.str+case @.str when '' then '' else ',' end + code from test

Is this possible ?

is it possible to have a select statement from a stored proceedure ? example ...

CREATE PROCEDURE spA
SELECT * FROM ( exec spB '1','1' )

go

the reason for doing this is becos spB does some data massaging and is used across many stored procedures, spB will be passing back a table if it is possible.

Thanks.I'd recommend that you rewrite spB as a User-defined table function. Then you can reference it in stored procedures, views, triggers, etc, just like any other table or subquery:

CREATE PROCEDURE spA
SELECT * FROM dbo.spB ('1','1')

blindman|||Or it that's something you would like to avoid, you could use

Insert Into #TmpSpB Exec SpB '1','1'

where #TmpSpB is a temporary table which has the structure of the result return by the stored procedure. The only problem with this, is that can not be nested, and I am not sure if it's working "below" SQL2000.

Best regards!|||thanks for the replies ... you've been a great help ...

is this possible

hey all,
i have a 2 table join select statement that i would like to turn into an
update statement? is this possible?
thanks,
rodcharPlease post DDL and DML statements in order to understand better you request
.
--
Current location: Alicante (ES)
"rodchar" wrote:

> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar|||I normally do it using aliases, like so:
-- SELECT version
SELECT *
FROM TableA a
INNER JOIN TableB b ON a.key_field = b.key_field
-- UPDATE version
UPDATE a
SET a.update_field = b.update_field
FROM TableA a
INNER JOIN TableB b ON a.key_field = b.key_field
Let me know how you get on.
Damien
"rodchar" wrote:

> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar|||thank you everyone. this helped.
rodchar
"rodchar" wrote:

> hey all,
> i have a 2 table join select statement that i would like to turn into an
> update statement? is this possible?
> thanks,
> rodchar

Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3

SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>

is this even possible in SQL?

Hi i have a sql statement as follows:

SELECT
seq = (select rts.seq from sj_rts rts where lhm.oper = rts.oper and lhm.route=rts.route),
lhm.date_time,
lhm.route,
lhm.oper,
x3o.operName,
(lhm.date_time - (SELECT max(lhm1.date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm1, x3oprs x3o1 WHERE lhm1.lot

= 'S6D0IQ002A' AND lhm1.oper = x3o1.oper and lhm1.date_time < lhm.date_time)) as ActualTime,
theoreticalTime = (subquery that returns theoretical time for particular row depending on value of oper and route)

FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o

WHERE
lhm.lot ='S6D0IQ002A' AND
lhm.oper = x3o.oper

UNION

SELECT
rts.seq,
PROJECTEDTIME = '' -- (Contains the estimated projected time by adding the theoretical time to the date_time value in

the row above it)
rts.route,
rts.oper,
rts.name AS operName,
ActualTime = '', -- Blank since this is the future
theoreticalTime = ( subquery that returns theoretical time for particular row depending on value of oper and route)

FROM
Routes_X3 rts

where
rts.route=(subquery)

and rts.seq > ( subquery the returns the seq from the history)

Order By
rts.seq asc

The first sql statement is the history for a particular item and gets its data from a history table. the second query

is the next steps that item needs to go through and gets its data from another table that just lists all the steps

according to its seq number. Each row in the whole UNION has a related theoretical time that is specific to the route

and the oper values.

What I am trying to do is create a column called projectedTime in the second query that will take the LAST date_time

from the first query (i can do this with a max(date_time) ) and add the theoretical time to produce the projected

date_time for the current row in the second query. THen for the next row, i want to add its theoretical time to the

projectedtime of the row above to get the next projected time. i want to do this for all the rows in the next steps

query (the second query). Essentially what i would get is a report detailing the steps already completed and the

projected completion dates for the next steps.

The issue is that I have to add the theoretical time for the second query's row to something. For the first row of

query2 its easy, just add the theoretical time to the last row of the first query (i can use a subquery to get the

date) to create the projected time. However, then for the next row, adding the theoretical time to the last row of

the first query won't give me the projected time, instead i need to add the theoretical time to the projected time of

the first row and then continue doing this to get the rest of the projected times.

I'm not sure how i can accomplish this in SQL or if its even possible. If i could somehow either create another

column on the second query that adds together the theoretical times of that row and that of the rows above it and add

the sume to the last date_time from the first query, i could get what i need. Or if i could somehow use a CURSOR to

bring back the date_time from the row above and add the theoretical time to that datetime and stick it in the column,

it could work. However, I read somewhere that you cant use cursors with more than one select statement, such as my

UNION.

I am trying to decide if i should do this in SQL or just do it programmatically.

Thanks for any help into my problem.Stop! You are giving me a headache.

The answer to your question is "Yes".

Yes you should to it using SQL. Yes, you should do it programmatically.

Create an SQL Stored procedure to generate your recordset, and use declared variables, temporary tables, etc, liberally in order to break your task down into smaller components.sql

Is there WHERE for columns

Hi everybody,
Is there a way to filter columns at runtime based on their name?

I create SELECT statement which includes all columns, then, if I get some parameter I leave some columns out and don't select data from them.

Is it possible?

I'm working with MS SQL 2000.You can't omit columns. The closest thing you can do is use CASE expressions to hide the data you don't want to show, such as:

SELECT Col1, Col2, CASE WHEN Col1 = 'someValue' THEN Col3 ELSE NULL END AS Col3
FROM YourTable|||Is this possible:

SELECT Col1, Col2, CASE WHEN SomethingOutsideThisSelectCommand = 'someValue' THEN Col3 ELSE NULL END AS Col3
FROM YourTable|||As long as it's a valid SQL expression, it shouldn't be a problem.

Is there support for the ANS LIST Statement in SQL Server?

We are considering moving our application to MS SQL Server from another
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn Barber
You can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net
|||Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility built with with SQLServer SP's? I will investigate it.
As a software publisher there are lots of considerations for us and our customers when straying outside of the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
I'd still like to know if the LIST command is supported or planned to be supported in a future release.
|||It is not there and I have not seen it in the planned feature list for the next version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
> I'd still like to know if the LIST command is supported or planned to be supported in a future release.
|||Thanks Tibor
I will definitely send my request to the wishlist.
Is there an efficient way of creating an equivalent function in a Stored Procedure?
Glenn

Is there support for the ANS LIST Statement in SQL Server?

We are considering moving our application to MS SQL Server from another
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn BarberYou can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net|||Thanks - I don't know much about RAC. - Is this a proprietary product - or s
ome publically available utility built with with SQLServer SP's? I will inv
estigate it.
As a software publisher there are lots of considerations for us and our cust
omers when straying outside of the vendor's supported syntax. It would be m
uch easier if the ANS LIST syntax was directly supported.
I'd still like to know if the LIST command is supported or planned to be sup
ported in a future release.|||It is not there and I have not seen it in the planned feature list for the n
ext version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some pu
blically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers
when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly
supported.
> I'd still like to know if the LIST command is supported or planned to be supported
in a future release.|||Thanks Tibor
I will definitely send my request to the wishlist.
Is there an efficient way of creating an equivalent function in a Stored Pro
cedure?
Glenn

Is there support for the ANS LIST Statement in SQL Server?

We are considering moving our application to MS SQL Server from another
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn BarberYou can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net|||Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility built with with SQLServer SP's? I will investigate it
As a software publisher there are lots of considerations for us and our customers when straying outside of the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported
I'd still like to know if the LIST command is supported or planned to be supported in a future release.|||It is not there and I have not seen it in the planned feature list for the next version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
> I'd still like to know if the LIST command is supported or planned to be supported in a future release.

Is there sp_helptext for tables

Hello Everybody, Please help me out:

Is there a system stored procedure for retrieving the sql statement that created a table.

I know i can use sp_helptext for views etc; i want the equivalent for tables.

sp_columns is not adequate either.

please help! thanks in advance;)sp_help will return all the columns of a table.|||sp_help will return all the columns of a table.
Hi Blindman,

I ran exec sp_help tblcustomers and i got:

Name: tblcustomers
Owner: dbo
Type: user table
Created_datetime: 4/18/2007 2:26:12 PM

Am i missing something??

is there any way to sort the SELECT statement?

hi,

i have a stored procedure

SELECT PictureID,Left(Name, 16) +'...'AS ShortNameFROM Pictures

some pictures has their name lower that 16 characters, and when i show on the page is something like "Sunrise..."

how can i 'sort' the Select so to return the ShortName if the name is greater than 16 characters or return Name if is lower

i hope you understand what i mean...

thanks

Hi,

You have to use the Case Statement

SELECT PictureId, Case When Len(Name)>16 THEN Left(Name,16)+'...' ELSE Name End AS ShortName From Pictures.

Hope this helps.

|||

thank you v v much

|||

You are welcome. I am glad I could help.

Is there another way to obtain '-all-' the fields?

Hi all,
I'm using a Reporting Services webpart for Sharepoint. I made the reports
using Parameters with the SELECT UNION statement to bring 'ALL' the rows,
something like this:
SELECT priority AS prior, prioridad AS value
FROM dbo.priority
WHERE (priority <> ' ')
UNION
SELECT '-ALL-' AS prior, NULL AS value
ORDER BY priority
This worked excelent..!!, But know that i'm integrating it to the webpart it
doesn´t shows the parameters.
My question is, if there is another way to bring ALL the rows without using
the UNION statement? cause i proved that without it, the parameter appears in
my Reporting Services WebPart in Sharepoint.
Please help me, Masters of the Reporting Services and SQL..!!
--
Greetings from Mexico..!!Currently, this is the way to do this. We will support multi-select
parameters in the SQL 2005 version.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gerardo" <Gerardo@.discussions.microsoft.com> wrote in message
news:954039F6-A83D-478C-B915-F862EF8FB032@.microsoft.com...
> Hi all,
> I'm using a Reporting Services webpart for Sharepoint. I made the reports
> using Parameters with the SELECT UNION statement to bring 'ALL' the rows,
> something like this:
> SELECT priority AS prior, prioridad AS value
> FROM dbo.priority
> WHERE (priority <> ' ')
> UNION
> SELECT '-ALL-' AS prior, NULL AS value
> ORDER BY priority
> This worked excelent..!!, But know that i'm integrating it to the webpart
> it
> doesn´t shows the parameters.
> My question is, if there is another way to bring ALL the rows without
> using
> the UNION statement? cause i proved that without it, the parameter appears
> in
> my Reporting Services WebPart in Sharepoint.
> Please help me, Masters of the Reporting Services and SQL..!!
> --
> Greetings from Mexico..!!|||Thanks a lot, Brian, now i know what to say to my clients.
Cheers.
"Brian Welcker [MSFT]" wrote:
> Currently, this is the way to do this. We will support multi-select
> parameters in the SQL 2005 version.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Gerardo" <Gerardo@.discussions.microsoft.com> wrote in message
> news:954039F6-A83D-478C-B915-F862EF8FB032@.microsoft.com...
> > Hi all,
> >
> > I'm using a Reporting Services webpart for Sharepoint. I made the reports
> > using Parameters with the SELECT UNION statement to bring 'ALL' the rows,
> > something like this:
> >
> > SELECT priority AS prior, prioridad AS value
> > FROM dbo.priority
> > WHERE (priority <> ' ')
> > UNION
> > SELECT '-ALL-' AS prior, NULL AS value
> > ORDER BY priority
> >
> > This worked excelent..!!, But know that i'm integrating it to the webpart
> > it
> > doesn´t shows the parameters.
> >
> > My question is, if there is another way to bring ALL the rows without
> > using
> > the UNION statement? cause i proved that without it, the parameter appears
> > in
> > my Reporting Services WebPart in Sharepoint.
> >
> > Please help me, Masters of the Reporting Services and SQL..!!
> >
> > --
> > Greetings from Mexico..!!
>
>

Is there an equivalent to an MSAccess IIF statement when creating Store Procs

I'm creating a stored proc and need to use some sort of IF or IIF statement. Is theresome sort of equivalent statement i can use?T-SQL supports an IF and CASE statment. you can read up on both in Books Online.

IF...ELSE
Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Syntax
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

CASE
Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.

The searched CASE function evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END|||IF(<CHECK CONDITION>)
BEGIN
<CODE>
END
ELSE
BEGIN
< CODE>
END|||IIF doesn't exists in SQL but you can always use the old fashion way:

IF <your_condition>
BEGIN
:
:
END
ELSE
BEGIN
:
:
END

Originally posted by Sammy_S
I'm creating a stored proc and need to use some sort of IF or IIF statement. Is theresome sort of equivalent statement i can use?