Parse SQL statement for list of tables

Hello,
I would like to make a procedure of sorts that accepts as input a full
sql statment and then is able to return a list (or print) of only the tables
referenced in the sql statement. Is this kind of code available?
Thanks.
Bentweak this
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO
Create table #tempTables (SplitString varchar(50))
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='select * from authors join publishers on bla bla
bla...'
insert into #tempTables
SELECT SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID + 1,
CHARINDEX(' ', ' ' + @.chvGroupNumbers + ' ', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(' ' + @.chvGroupNumbers + ' ') - 1
AND SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID, 1) = ' '
GO
select * from #tempTables where Splitstring in (SELECT table_name FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE')
Use Pubs for this example
http://sqlservercode.blogspot.com/
"Ben" wrote:

> Hello,
> I would like to make a procedure of sorts that accepts as input a full
> sql statment and then is able to return a list (or print) of only the tabl
es
> referenced in the sql statement. Is this kind of code available?
> Thanks.
> Ben

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:
> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >i am trying to write a stored procedure which parses the string query
> >passed
> > as input and returns whether it is a valid statement or not
> >
> > was trying to use "SET PARSEONLY ON" without any luck
> >
> > thanks
> > red
>
>|||No.
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> > is there any other way that i can make it to work
> >
> > srinivas
> >
> > "oj" wrote:
> >
> >> "Parseonly" does not parse for dynamic query. This is by design.
> >> Basically,
> >> 'parseonly' only parses for syntax and dynamic query is parsed at
> >> runtime.
> >>
> >> -- this would parse fine
> >> -- because @.sql is a valid variable
> >> -- and exec(@.sql) syntactically correct
> >> -- though this would err at runtime
> >> set parseonly on
> >> go
> >> declare @.sql sysname
> >> set @.sql='aflasfasfaslfsaf'
> >> exec(@.sql)
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >> >i am trying to write a stored procedure which parses the string query
> >> >passed
> >> > as input and returns whether it is a valid statement or not
> >> >
> >> > was trying to use "SET PARSEONLY ON" without any luck
> >> >
> >> > thanks
> >> > red
> >>
> >>
> >>
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
--
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> is there any other way that i can make it to work
>> srinivas
>> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red
"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red
|||is there any other way that i can make it to work
srinivas
"oj" wrote:

> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>
>
|||No.
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:

> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>
>
|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>
|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:

Partitioning "Alter Table Switch" Statement Failing

Help!! I can't seem to find information on the error that I'm getting anywhere:

ALTER TABLE SWITCH statement failed. Range defined by partition 1 in table 'DB1.dbo.Table1' is not a subset of range defined by partition 4 in table 'DB1.dbo.Table2'.

Here's some sample code that generates this error

Code Snippet

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE LEFT FOR VALUES (443, 444, 445)

CREATE PARTITION FUNCTION [Table2Range](int) AS RANGE LEFT FOR VALUES (440, 441, 442, 443)

GO

CREATE PARTITION SCHEME [Table1Scheme] AS PARTITION [Table1Range] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE PARTITION SCHEME [Table2Scheme] AS PARTITION [Table2Range] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

GO

CREATE TABLE [dbo].[Table1](

[session_id] [int] NOT NULL,

[ProcessLogID] [int] NOT NULL,

CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

(

[session_id] ASC,

[ProcessLogID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [Table1Scheme]([ProcessLogID])

) ON [Table1Scheme]([ProcessLogID])

CREATE TABLE [dbo].[Table2](

[session_id] [int] NOT NULL,

[ProcessLogID] [int] NOT NULL,

CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED

(

[session_id] ASC,

[ProcessLogID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [Table2Scheme]([ProcessLogID])

) ON [Table2Scheme]([ProcessLogID])

GO

insert into [Table1]

select 1, 443

insert into [Table1]

select 2, 444

insert into [Table1]

select 3, 445

insert into [Table2]

select 4, 440

insert into [Table2]

select 5, 441

insert into [Table2]

select 6, 442

ALTER TABLE [Table1] SWITCH PARTITION 1 to [Table2] PARTITION 4

I'd really appreciate any advice anyone has! Thanks so much.

Jess

Some further info on this

I discovered some minor changes to the example above that would get this working. I don't understand why, on a conceptual level, these changes would make a difference. Any ideas?

The 2 changes that make my above code work:

1)Declare the partition ranges using RIGHT instead of LEFT:

(i.e.

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE RIGHT FOR VALUES (443, 444, 445)

CREATE PARTITION FUNCTION [Table2Range](int) AS RANGE RIGHT FOR VALUES (440, 441, 442, 443))

2) Run the Alter Table Switch statements on Partition #s 2 & 5 instead of #s 1 & 4

(i.e. "ALTER TABLE [Table1] SWITCH PARTITION 2 to [Table2] PARTITION 5")

It's going to be a ROYAL PITA to switch the partition functions from RIGHT to LEFT in the db I'm working with. Is there anyway I can get this working keeping the LEFT definition?

Thanks!

Jess

|||

Alright- I've answered my own question. I wanted to post in case anyone runs into the same error.

I've got it working using the LEFT boundary condition by adding a Check Constraint on Table1.

ALTER TABLE Table1

ADD CONSTRAINT [CK_ProcessLogID]

CHECK ([ProcessLogID] >= 443)

Basically, when I declared the Partition Function connected to table 1 as:

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE LEFT FOR VALUES (443, 444, 445)

I am declaring Partion Number 1 to store all data where the ProcessLogID<= 443. The key is the LESS THAN or = 443. I was getting confused because in this particular example there was no data that was less than 443 in the table- but there was nothing in the table definition that prohibited it.

Partition Number 4 of Table2 is defined to be all data where the ProcessLogID> = 443 and ProcessLogID < 444 (or ProcessLogID = 443, since it is an integer column). Since a switch statement is actually just altering metadata there can be no data validation, and the definition of the table needs to be representitive that switching one partition to another will follow with the table's partition definition. By adding the check constraint to Table1, you can be assured that all data in Partition #1 of Table1 will be consistant with Partition #4 of Table2. Yippee!

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:

> "Parseonly" does not parse for dynamic query. This is by design. Basically
,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>
>|||No.
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:

> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>

Paramters in WITH part of MDX

Hi,

I need a parameter within the WITH part of an MDX Statement in Reporting Services. I tried several types, but all will bring an error. I will do it like:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure) SELECT { [Measures].[Amount] } on columns, ......

The @.MyMeasures should be a combobox with the values like

Name: Sales Volume (kg) Value: [Measures].[Sales Volume KG]
Name: Sales Volume (m2) Value: [Measures].[Sales Volume KG]
... and so on.

What's my failure?

Thanks
Hans

Whats the error?|||

Hi Adam,

The error is:

The query will not be retrieved from the query builder. Check the query for syntax error.
The syntax of

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure)

is not correct, but I didn't find the correct syntax to make a dynamic selectable measure from a combobox.

Thanks
Hans

|||I've seen a lot of these posts where using a parameter in the WITH section throws an exception. The only solution I've found so far is to revert to connecting via the OLEDB provider and using an expression based MDX query. See this thread for full details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726232&SiteID=1|||

Hi @.All,

After a lot of testing, I got it to work. The solution is, you have to mask the Parameter with apostrophes like this:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER("" + @.SalesFigure + "")
SELECT NON EMPTY .....

The key is, to mask it with double apostophes to escape the "single apostroph". Now this part of my many starting problems works!

Hans

Parameters with LIKE statement

I have a sql that I want to execute with LIKE and parameters:
I tried several options outlined that I found athttp://aspnet101.com/aspnet101/tutorials.aspx?id=10%20
but they all seem to return 0 records. When I try and execute my statement in Enterprise manager, it works fine.
Code Snippet:
...
Dim sql as string
sql = "SELECT * FROM tblName WHERE First LIKE '%' + @.fname + '%' AND Last LIKE '%' + @.lname + '%'"
Dim param(1) as sqlParameter
sqlParams(0) =New SqlParameter("@.fname", SqlDbType.VarChar, 50)
sqlParams(0).Value = Trim(fname.text)
sqlParams(1) =New SqlParameter("@.lname", SqlDbType.VarChar, 50)
sqlParams(1).Value = Trim(lname.text)
...
Can anyone tell me if there is anything wrong with my code above?Do you call Command.Parameters.Add after you set the values?
If not you must add the parameters to the Command after their values are set for example::

sqlParams(0) =New SqlParameter("@.fname", SqlDbType.VarChar, 50)
Command.Parameters.Add(sqlParams(0))

Sam

|||

Yes. I actually pass my parameter collection to a class that adds it to the command. It's a bit more complicated and so I don't want to post that part. I'm just wondering if the code section that I had posted was correct.
All my sqls work fine with parameters except when I use the LIKE clause.
Thanks.

|||Your SQL statement is fine.
Check out the way you pass parameters to ur Statement
regards|||Alternatively you could also do like this :
Dim sql as string
sql = "SELECT * FROM tblName WHERE First LIKE @.fname AND Last LIKE @.lname "
when you define the parameters and pass the value you could say:
sqlParams(0).Value = "%" & Trim(fname.text) & "%"
|||Use SQL Profile, and make sure "statement starting" is on, and see what SQL is actually being sent...also, what error are you seeing?|||

I don't see any error messages. It just returns 0 records when it should be return all records.

I simplified my code:

Dim strSQLTextAsString
strSQLText = "SELECT * FROM tbl WHERE Name LIKE @.name AND Email LIKE @.email"

Dim _conStringAsString
_conString = configurationSettings.appSettings("conString")

Dim _conSQLAs SqlConnection

Dim _SQLCommandAs SqlCommand

Try

_conSQL=New SqlConnection(_conString)
_SQLCommand =New SqlCommand
_SQLCommand.CommandText = strSQLText
_SQLCommand.Connection = _conSQL
_SQLCommand.Parameters.Add("@.name",String.Format("%{0}%", name.text))
_SQLCommand.Parameters.Add("@.email",String.Format("%{0}%", email.text))
_conSQL.Open()

Dim Data =New DataSet
Dim dataAdapter =New SqlDataAdapter(_SQLCommand)
dataAdapter.Fill(Data)

Dim dtAsNew DataTable
dt = Data.Tables(0)

Finally

_conSQL.Dispose()
_SQLCommand.Dispose()

EndTry

--
Can anyone tell me what's wrong with the code?
Thanks!!
|||Its a good idea to add the size of each of the parameters.|||

I figured out what was not working. I had created a sqlParameter array and was passing that to my data access funtion... (code in original posting). That I think was screwing up the %.

Anyhow, found a work around and is working fine now

Thanks much for everyone's pointers.

Parameters with a union statement

I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.

What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.

But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"

Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?

Thanks

Have you tried to use this sql structure

="select * from table_1 where Sale_date = '" & format(Parameters!date,"MM-dd-yyyy") & "'"
union
select * from table_2 where Sale_date = '" & format(Parameters!date,"MM-dd-yyyy") & "'"

|||

Can I do this in the data set?

Thanks

Parameters using like

Hi,
I have a parameter named County. County, can have multiple values in it -
ex: 01, 02, 03.
I need to somehow use a like statement with this parameter so that it will
find all the counties in this field. I tried like%@.County% but this doesn't
work.
Anybody have any suggestions'
ThanksJill,
I suspect that what you'd like to do is something like this:-
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (1,2,3,4,5)
and then your thinking probably goes that you'd want to replace (1,2,3,4,5)
with a parameter such as:
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (@.CountyParameter)
You could make this work like I'm about to show you. BUT DONT!
EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (' + @.CountyParameter+ ')')
The reason not to do this is that this is very insecure from attacks from
SQL INJECTION. Just consider what would happen if someone passed
4);INSERT INTO CountiesTable(CountyName,
CountyID)VALUES('DisneyFantasyCounty',667)--
as the value for @.CountyParameter (or something far worse).
So having told you what not to do - the correct thing to do is create a
function on the Server such as this one taken directly from the
'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages 534 -
537)
CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
RETURNS @.paramtable
TABLE ( Id int )
AS BEGIN
DECLARE @.len int,
@.index int,
@.nextindex int
SET @.len = DATALENGTH(@.delimString)
SET @.index = 0
SET @.nextindex = 0
WHILE (@.len > @.index )
BEGIN
SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
INSERT @.paramtable
SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
SET @.index = @.nextindex + 1
END
RETURN
END
What this function does is that you pass it a parameter of a delimited
string such as your counties '1;2;6;' and returns a table. This table can
then be joined into your query. This approach is safer from SQL Injection
attacks. And so your Query for the DataSet becomes:
SELECT CountyID, CountyName FROM CountiesTable
INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
ON CountiesTable.CountyID= ParamParserFn.Id
I hope this is able to help you. More details as I mentioned are available
in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
Services. (see http://www.sqlreportingservices.net ). I would heartily
recommend our book to you as a valuable resource that comes with 2.5 GB of
DVD content and video demonstrations, and we especially concentrate on
security matters throughout.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
> Hi,
> I have a parameter named County. County, can have multiple values in it -
> ex: 01, 02, 03.
> I need to somehow use a like statement with this parameter so that it will
> find all the counties in this field. I tried like%@.County% but this
> doesn't
> work.
> Anybody have any suggestions'
> Thanks
>|||We will also natively support multiple values in queries in the next release
of Reporting Services.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Blackburn (www.sqlreportingservices.net)"
<http://www.sqlreportingservices.net> wrote in message
news:u6Py%239gxEHA.2040@.tk2msftngp13.phx.gbl...
> Jill,
> I suspect that what you'd like to do is something like this:-
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (1,2,3,4,5)
> and then your thinking probably goes that you'd want to replace
> (1,2,3,4,5) with a parameter such as:
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (@.CountyParameter)
> You could make this work like I'm about to show you. BUT DONT!
>
> EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (' + @.CountyParameter+ ')')
> The reason not to do this is that this is very insecure from attacks from
> SQL INJECTION. Just consider what would happen if someone passed
> 4);INSERT INTO CountiesTable(CountyName,
> CountyID)VALUES('DisneyFantasyCounty',667)--
> as the value for @.CountyParameter (or something far worse).
>
>
> So having told you what not to do - the correct thing to do is create a
> function on the Server such as this one taken directly from the
> 'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages
> 534 - 537)
>
> CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
> RETURNS @.paramtable
> TABLE ( Id int )
> AS BEGIN
> DECLARE @.len int,
> @.index int,
> @.nextindex int
> SET @.len = DATALENGTH(@.delimString)
> SET @.index = 0
> SET @.nextindex = 0
> WHILE (@.len > @.index )
> BEGIN
> SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
> if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
> INSERT @.paramtable
> SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
> SET @.index = @.nextindex + 1
> END
> RETURN
> END
> What this function does is that you pass it a parameter of a delimited
> string such as your counties '1;2;6;' and returns a table. This table can
> then be joined into your query. This approach is safer from SQL Injection
> attacks. And so your Query for the DataSet becomes:
> SELECT CountyID, CountyName FROM CountiesTable
> INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
> ON CountiesTable.CountyID= ParamParserFn.Id
>
> I hope this is able to help you. More details as I mentioned are available
> in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
> Services. (see http://www.sqlreportingservices.net ). I would heartily
> recommend our book to you as a valuable resource that comes with 2.5 GB of
> DVD content and video demonstrations, and we especially concentrate on
> security matters throughout.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
>
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
>> Hi,
>> I have a parameter named County. County, can have multiple values in
>> it -
>> ex: 01, 02, 03.
>> I need to somehow use a like statement with this parameter so that it
>> will
>> find all the counties in this field. I tried like%@.County% but this
>> doesn't
>> work.
>> Anybody have any suggestions'
>> Thanks
>

Parameters on Reporting Services 2005 REPOST

Hi, I experiencing some problems to access the Parameters collection inside
a custom code to build a sql statement.
Below is my code (very simple):
DataSet:
=Code.SQL(Parameters)
Custom Code:
Public Function SQL(ByRef pars As Object) As String
Dim stmt as String
stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
return stmt
End Function
This code works just fine on the Preview(Designer) but if I test the report
on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianHi,
have you tried to declare pars As Parameter and not as Object ?
"Fabian von Romberg" wrote:
> Hi, I experiencing some problems to access the Parameters collection inside
> a custom code to build a sql statement.
> Below is my code (very simple):
> DataSet:
> =Code.SQL(Parameters)
> Custom Code:
> Public Function SQL(ByRef pars As Object) As String
> Dim stmt as String
> stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> return stmt
> End Function
>
> This code works just fine on the Preview(Designer) but if I test the report
> on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>
>|||Whatt!!!!!!!!!!!!,
I should have tried that before. It did it. I used this code on the older
version of Reporting Services and never got that error message, actually I
think I was not able to set a function parameter as type of Parameters but
Object. On 2005's seems to be the correct way to do it.
Thanks Cedric, I appreciated it.
Regards,
Fabian von Romberg
"Cedric" <Cedric@.discussions.microsoft.com> wrote in message
news:C8A38357-8A82-473A-8208-6DEEBF40DC9F@.microsoft.com...
> Hi,
> have you tried to declare pars As Parameter and not as Object ?
>
> "Fabian von Romberg" wrote:
> > Hi, I experiencing some problems to access the Parameters collection
inside
> > a custom code to build a sql statement.
> >
> > Below is my code (very simple):
> >
> > DataSet:
> > =Code.SQL(Parameters)
> >
> > Custom Code:
> >
> > Public Function SQL(ByRef pars As Object) As String
> > Dim stmt as String
> > stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> > return stmt
> > End Function
> >
> >
> > This code works just fine on the Preview(Designer) but if I test the
report
> > on the
> > browser, it doesnt work and returns the following error:
> > a.. An error has occurred during report processing.
> > a.. Cannot set the command text for data set 'ExpoMedios'.
> > a.. Error during processing of the CommandText expression of
dataset
> > 'ExpoMedios'.
> >
> > Doing some debugging the error message inside the function is:
> >
> > Attempt to access the method failed.
> >
> >
> > Can anyone pleae explain why this is happening. Your help will be
> > appreciated.
> >
> > Regards,
> > Fabian
> >
> >
> >
> >

Parameters in WITH part of MDX

Hi,

I need a parameter within the WITH part of an MDX Statement in Reporting Services. I tried several types, but all will bring an error. I will do it like:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure) SELECT { [Measures].[Amount] } on columns, ......

The @.MyMeasures should be a combobox with the values like

Name: Sales Volume (kg) Value: [Measures].[Sales Volume KG]
Name: Sales Volume (m2) Value: [Measures].[Sales Volume KG]
... and so on.

What's my failure?

Thanks
Hans

Whats the error?|||

Hi Adam,

The error is:

The query will not be retrieved from the query builder. Check the query for syntax error.
The syntax of

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure)

is not correct, but I didn't find the correct syntax to make a dynamic selectable measure from a combobox.

Thanks
Hans

|||I've seen a lot of these posts where using a parameter in the WITH section throws an exception. The only solution I've found so far is to revert to connecting via the OLEDB provider and using an expression based MDX query. See this thread for full details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726232&SiteID=1|||

Hi @.All,

After a lot of testing, I got it to work. The solution is, you have to mask the Parameter with apostrophes like this:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER("" + @.SalesFigure + "")
SELECT NON EMPTY .....

The key is, to mask it with double apostophes to escape the "single apostroph". Now this part of my many starting problems works!

Hans

parameters arent refreshing

hi,

im using RS 2000, i have a report that gets sent an SQL statement as a parameter. this works fine once. but lets say i send "SELECT * FROM table WHERE col1 = 'blah'" (and out put is correct)

but then i send "SELECT * FROM table WHERE col1 = 'foo'"

the second one still returns the results from the first query. this happens no matter what parameter i send - including null and blank.

anyone know a solution to this? i would really appreciate it. thanks.

note: i dont think this is a cache problem but im not sure. cache execution is set to use most recent data, no snapshots are stored. i think its something to do with the stored procedure keeping the first parameter. but then if i knew what it was, i wouldnt be asking here would i...?

also, the error still occurs even if i close and reopen the browser, if i use clearsession=true, if i close the browser and come back the next day...

ok this problem seems to be fixed - though i am still not sure what the problem was / is.

i think it was something wrong with the way RS was dealing with the stored procedure. eg. i changed the procedure's parameter name. but the report didnt pick up on the change and kept trying to force the old name.

to my knowledge, i didnt do anything to fix it, it just fixed itself. so im not confident it will stay fixed...

Parameters and MDX

Is there a customer Data Providor for Reporting Services that handles
Parameteized MDX statments
I.E.
For instance the below MDX Statement would have
<%TOPBOUNDS%>,<%BOOKINGS%>,<%DTLVL%> etc replaced by the parameters passed
into the data providor
WITH SET [MAIN] AS '<%TOPBOUNDS%>({[CUSTOMER CLASS].[CUSTOMER ID].MEMBERS},
<%SELCOUNT%>, [MEASURES].[YTD <%BOOKINGS%> <%STD%>])'
MEMBER [CUSTOMER CLASS].[OTHER] AS 'SUM(EXCEPT({[CUSTOMER CLASS].[CUSTOMER
ID].MEMBERS},{[MAIN]}))'
MEMBER [CUSTOMER CLASS].[TOTAL] AS 'SUM({[CUSTOMER CLASS].[ALL CUSTOMER
CLASS]})'
MEMBER [MEASURES].[PRIOR YR_<%DTLVL%> $] AS '[MEASURES].[LAST YEAR
<%BOOKINGS%> <%STD%>]'
MEMBER [MEASURES].[PRIOR YR YTD $] AS '[MEASURES].[LAST YTD <%BOOKINGS%>
<%STD%>]'
MEMBER [MEASURES].[DIFFERENCE $] AS '[MEASURES].[<%DTLVL%> <%BOOKINGS%>
<%BSTD%>]-[MEASURES].[PRIOR YR_<%DTLVL%> $]',FORMAT='$#,0.00'
MEMBER [MEASURES].[YTD DIFFERENCE $] AS '[MEASURES].[YTD <%BOOKINGS%>
<%STD%>]-[MEASURES].[PRIOR YR YTD $]',FORMAT='$#,0.00'
MEMBER [MEASURES].[% OF YTD <%BOOKINGS%>] AS '[MEASURES].[YTD <%BOOKINGS%>
<%STD%>]/SUM({[TOTAL]},[MEASURES].[YTD <%BOOKINGS%>
<%STD%>])',FORMAT='0.00%'
SELECT { [MEASURES].[<%DTLVL%> <%BOOKINGS%> <%BSTD%>],[MEASURES].[PRIOR
YR_<%DTLVL%> $],[MEASURES].[DIFFERENCE $],[MEASURES].[YTD <%BOOKINGS%>
<%STD%>],[MEASURES].[PRIOR YR YTD $],[MEASURES].[YTD DIFFERENCE
$],[MEASURES].[% OF YTD <%BOOKINGS%>] } ON COLUMNS,
{[MAIN],[CUSTOMER CLASS].[OTHER], [CUSTOMER CLASS].[TOTAL] } ON ROWS
FROM [BOOKINGS] WHERE (<%SELDATE%>,<%SRCAPPID%>)The OleDB provider for AS 2000 does not support parameterized MDX queries.
This MSDN article explains how to achieve parameterized MDX in RS 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
In addition, you may want to download this sample:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
BTW: RS 2005 Beta 2 contains graphical and text-based query designers for
MDX and DMX. They also support single-value parameters at this point.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:e36nP4EjEHA.3988@.tk2msftngp13.phx.gbl...
> Is there a customer Data Providor for Reporting Services that handles
> Parameteized MDX statments
> I.E.
> For instance the below MDX Statement would have
> <%TOPBOUNDS%>,<%BOOKINGS%>,<%DTLVL%> etc replaced by the parameters
passed
> into the data providor
> WITH SET [MAIN] AS '<%TOPBOUNDS%>({[CUSTOMER CLASS].[CUSTOMER
ID].MEMBERS},
> <%SELCOUNT%>, [MEASURES].[YTD <%BOOKINGS%> <%STD%>])'
> MEMBER [CUSTOMER CLASS].[OTHER] AS 'SUM(EXCEPT({[CUSTOMER CLASS].[CUSTOMER
> ID].MEMBERS},{[MAIN]}))'
> MEMBER [CUSTOMER CLASS].[TOTAL] AS 'SUM({[CUSTOMER CLASS].[ALL CUSTOMER
> CLASS]})'
> MEMBER [MEASURES].[PRIOR YR_<%DTLVL%> $] AS '[MEASURES].[LAST YEAR
> <%BOOKINGS%> <%STD%>]'
> MEMBER [MEASURES].[PRIOR YR YTD $] AS '[MEASURES].[LAST YTD <%BOOKINGS%>
> <%STD%>]'
> MEMBER [MEASURES].[DIFFERENCE $] AS '[MEASURES].[<%DTLVL%> <%BOOKINGS%>
> <%BSTD%>]-[MEASURES].[PRIOR YR_<%DTLVL%> $]',FORMAT='$#,0.00'
> MEMBER [MEASURES].[YTD DIFFERENCE $] AS '[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>]-[MEASURES].[PRIOR YR YTD $]',FORMAT='$#,0.00'
> MEMBER [MEASURES].[% OF YTD <%BOOKINGS%>] AS '[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>]/SUM({[TOTAL]},[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>])',FORMAT='0.00%'
> SELECT { [MEASURES].[<%DTLVL%> <%BOOKINGS%> <%BSTD%>],[MEASURES].[PRIOR
> YR_<%DTLVL%> $],[MEASURES].[DIFFERENCE $],[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>],[MEASURES].[PRIOR YR YTD $],[MEASURES].[YTD DIFFERENCE
> $],[MEASURES].[% OF YTD <%BOOKINGS%>] } ON COLUMNS,
> {[MAIN],[CUSTOMER CLASS].[OTHER], [CUSTOMER CLASS].[TOTAL] } ON ROWS
> FROM [BOOKINGS] WHERE (<%SELDATE%>,<%SRCAPPID%>)
>

Parameters

Isn't it possible to have parameters in the top of af statement, I get
syntax error if I try this and the parameter is there.
select top @.top etc.
JackTSQL doesn't support using a variable in a TOP statement, so reporting
services can't use a parameter in this case. The only way I know to do
this in TSQL is build the SQL as a string, and run it with
sp_executesql or EXEC.

Parameterizing BULK INSERT

I have a statement in a stored procedure as follows - which works a dream.
BULK INSERT cashpost
FROM 'c:\carparkfines\data\cashpost.txt'
WITH
(
FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
)
However I want to parameterize the two file paths.
I know how to get them as follows
select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo =
1)
However TSQL objects if I write
BULK INSERT cashpost
FROM @.filename01
WITH
(
FORMATFILE = @.filename02
)
So who is going to tell the new boy how to do it? Thanks in anticipation.
Andy Willis
Use some dynamic SQL, e.g.
DECLARE @.Sql nvarchar(4000)
SET @.Sql = '
BULK INSERT cashpost
FROM ''' + @.filename01 + '''
WITH
(
FORMATFILE = ''' + @.filename02 + '''
)
'
PRINT @.Sql
EXEC(@.Sql)
Darren Green
http://www.sqldts.com
"Andy Willis" <andrewrwillis@.blueyonder.co.uk> wrote in message
news:%23gqfKAlaEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I have a statement in a stored procedure as follows - which works a dream.
> BULK INSERT cashpost
> FROM 'c:\carparkfines\data\cashpost.txt'
> WITH
> (
> FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
> )
> However I want to parameterize the two file paths.
> I know how to get them as follows
> select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo
=
> 1)
> However TSQL objects if I write
> BULK INSERT cashpost
> FROM @.filename01
> WITH
> (
> FORMATFILE = @.filename02
> )
> So who is going to tell the new boy how to do it? Thanks in anticipation.
> Andy Willis
>

Parameterizing Allow Nulls Columns

Here's a question I though would be common but can't find an answer to!

My select statement, which pulls from SQL Server tables, has a column which allows nulls. When I try to add a parameter to this column, it no longer returns rows that have null in that column when I test it with the default value of %. I want it to return all the records.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer)

Parameter is;
ConvertEmptyStringToNull = True
DefaultValue = %
Direction = Input
Name = Engineer
QueryStringField = Engineer
Size = 0
Type = Empty

When I run this I get no records! Isn't % suppose to return anything including nulls?

Isn't % suppose to return anything including nulls?

No. % is only valid for the LIKE operator, and even then it will not return nulls.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

|||

This works and I thank you much but the thickness of my skull is prohibiting me from absorbing why it work.

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided - the default of % is used and this returns every value accept for nulls. How does it treat zero length strings?
if a parameter is provided - it returns the rows that match the parameter

OR (@.Engineer='%')
if a parameter is not provided - ?
if a parameter is provided - ?

I don't understand how this connects to Engineers.Engineer.

|||

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided, the default of % is used and only rows in which engineer contains EXACTLY % will be returned. I assume you have no engineers that contain exactly %, so no rows will be returned.

If a parameter is provided, it returns the rows that match the parameter.

OR (@.Engineer='%')
if a parameter is not provided, the default of % is used and % always is equal to %. So it will match on every row.

if a parameter is provided (Assuming of course the parameter isn't %), then it will never match.

Recap

WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

No parameter: WHERE (FALSE) OR (TRUE)

simplified: WHERE TRUE

Parameter: WHERE (Possibly TRUE -- if they match) OR (FALSE)

simplified: WHERE (Possibly TRUE -- if they match)

Make any sense now?

|||

Perfect Sense. Thank you! So I'm guessing the use of a wildcard really doesn't work in ASP.NET or Sql Server? In Dreamweaver I can provide a string parameter of '%' and it will return all rows including nulls. The WHERE clause is Field LIKE '%'.

|||

Yes, the like operator accepts wildcards, but the equals operator does not.

WHERE Field LIKE '%' will find all fields that have 0 or more characters (NULL is not 0 or more characters, it's NULL).

WHERE Field='%' will find all fields that contain one character, and that one character must be the percent symbol.

|||LIKE '%' returns NULL fields in SQL Server and in my Dreamweaver application but not in my asp.net application. I just posted a different thread asking why it doesn't work in asp.net.

Parameterized order by clause: doesnt work

Can someone tell me why SQL seems to ignore my order by clause?
I tried to run through the debugger, but the debugger stops at the
select statement line and then returns the result set; so, I have no
idea how it is evaluating the order by clause.
THANK YOU!

CREATE proc sprAllBooks

@.SortAscend varchar(4),
@.SortColumn varchar(10)

as

If @.SortAscend = 'DESC'
Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate

from authors
inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id

ORDER BY au_lname
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
DESC

ELSE

Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate

from authors

inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
GOOn 25 Feb 2005 08:39:07 -0800, JJ_377@.hotmail.com wrote:

>Can someone tell me why SQL seems to ignore my order by clause?
>I tried to run through the debugger, but the debugger stops at the
>select statement line and then returns the result set; so, I have no
>idea how it is evaluating the order by clause.
>THANK YOU!

Hi JJ,

You forgot to tell us how you call the procedure (what values for
@.SortAscend and @.SortColumn you use) and what results you get.

I tried your code and after fixing a syntax error, it works as I would
expect it to. You apparently expected something else, but what you
expected is not clear from your post.

Some small pointers to what might be your problem:

>If @.SortAscend = 'DESC'
(snip)
>ORDER BY au_lname
>CASE @.SortColumn WHEN 'title' THEN title END,
>CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
>CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
>DESC

This resulted in an error. You either have to append a comma after ORDER
BY au_lname, or remove au_lname. I figured the latter is what you want,
so that's how I tested it.

The last CASE expression won't refer to the formatted pubdate, but to
the pubdate column in the table. Even though the ORDER BY does allow you
to refer to an alias from the SELECT clause, it does not allow you to do
so in an expression.

The DESC will only apply to the ordering by pubdate. The title and
au_lname are in seperate expressions that require a seperate DESC:
ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END DESC,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo. Good points. For debugging purposes, I've been calling
the sproc from the debugger with the parameters @.SortAscend = 'DESC'
and @.SortColumn = 'title. The result set is not sorted by title desc,
as I would expect it should have been. Quite inexplicably (to me), the
result set now seems to be sorting by au_lname desc (!)

Note that the order by clause should only feature the case evaluation
statement (au_lname was there as a left-over from a debugging
attempt...):

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @.SortColumn WHEN 'PubDate' THEN PubDate END

I think I see what you are getting at about the PubDate (alias) and
pubdate (table column) name and will give that a try...

Also, you related that one part of the code resulted in an error...can
you tell me how to unearth that? The Sql debugger seemed to be silent
on that point to me...

Thank you again...

J.

Hugo Kornelis wrote:
> On 25 Feb 2005 08:39:07 -0800, JJ_377@.hotmail.com wrote:
> >Can someone tell me why SQL seems to ignore my order by clause?
> >I tried to run through the debugger, but the debugger stops at the
> >select statement line and then returns the result set; so, I have no
> >idea how it is evaluating the order by clause.
> >THANK YOU!
> Hi JJ,
> You forgot to tell us how you call the procedure (what values for
> @.SortAscend and @.SortColumn you use) and what results you get.
> I tried your code and after fixing a syntax error, it works as I
would
> expect it to. You apparently expected something else, but what you
> expected is not clear from your post.
> Some small pointers to what might be your problem:
> >If @.SortAscend = 'DESC'
> (snip)
> >ORDER BY au_lname
> >CASE @.SortColumn WHEN 'title' THEN title END,
> >CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
> >CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
> >DESC
> This resulted in an error. You either have to append a comma after
ORDER
> BY au_lname, or remove au_lname. I figured the latter is what you
want,
> so that's how I tested it.
> The last CASE expression won't refer to the formatted pubdate, but to
> the pubdate column in the table. Even though the ORDER BY does allow
you
> to refer to an alias from the SELECT clause, it does not allow you to
do
> so in an expression.
> The DESC will only apply to the ordering by pubdate. The title and
> au_lname are in seperate expressions that require a seperate DESC:
> ORDER BY
> CASE @.SortColumn WHEN 'title' THEN title END DESC,
> CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
> CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||I figured it out. The order by clause has to look like this:
ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END DESC,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC

I put the 'DESC' parameter inside each of the case statements and now
my result set is sorting!

JJ_377@.hotmail.com wrote:
> Thank you Hugo. Good points. For debugging purposes, I've been
calling
> the sproc from the debugger with the parameters @.SortAscend = 'DESC'
> and @.SortColumn = 'title. The result set is not sorted by title desc,
> as I would expect it should have been. Quite inexplicably (to me),
the
> result set now seems to be sorting by au_lname desc (!)
> Note that the order by clause should only feature the case evaluation
> statement (au_lname was there as a left-over from a debugging
> attempt...):
> ORDER BY
> CASE @.SortColumn WHEN 'title' THEN title END,
> CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
> CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
> I think I see what you are getting at about the PubDate (alias) and
> pubdate (table column) name and will give that a try...
> Also, you related that one part of the code resulted in an
error...can
> you tell me how to unearth that? The Sql debugger seemed to be silent
> on that point to me...
> Thank you again...
> J.
>
>
> Hugo Kornelis wrote:
> > On 25 Feb 2005 08:39:07 -0800, JJ_377@.hotmail.com wrote:
> > >Can someone tell me why SQL seems to ignore my order by clause?
> > >I tried to run through the debugger, but the debugger stops at the
> > >select statement line and then returns the result set; so, I have
no
> > >idea how it is evaluating the order by clause.
> > >THANK YOU!
> > Hi JJ,
> > You forgot to tell us how you call the procedure (what values for
> > @.SortAscend and @.SortColumn you use) and what results you get.
> > I tried your code and after fixing a syntax error, it works as I
> would
> > expect it to. You apparently expected something else, but what you
> > expected is not clear from your post.
> > Some small pointers to what might be your problem:
> > >If @.SortAscend = 'DESC'
> > (snip)
> > >ORDER BY au_lname
> > >CASE @.SortColumn WHEN 'title' THEN title END,
> > >CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
> > >CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
> > >DESC
> > This resulted in an error. You either have to append a comma after
> ORDER
> > BY au_lname, or remove au_lname. I figured the latter is what you
> want,
> > so that's how I tested it.
> > The last CASE expression won't refer to the formatted pubdate, but
to
> > the pubdate column in the table. Even though the ORDER BY does
allow
> you
> > to refer to an alias from the SELECT clause, it does not allow you
to
> do
> > so in an expression.
> > The DESC will only apply to the ordering by pubdate. The title and
> > au_lname are in seperate expressions that require a seperate DESC:
> > ORDER BY
> > CASE @.SortColumn WHEN 'title' THEN title END DESC,
> > CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
> > CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC
> > Best, Hugo
> > --
> > (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 25 Feb 2005 09:15:39 -0800, JJ_377@.hotmail.com wrote:

>Thank you Hugo. Good points. For debugging purposes, I've been calling
>the sproc from the debugger with the parameters @.SortAscend = 'DESC'
>and @.SortColumn = 'title. The result set is not sorted by title desc,
>as I would expect it should have been. Quite inexplicably (to me), the
>result set now seems to be sorting by au_lname desc (!)
>Note that the order by clause should only feature the case evaluation
>statement (au_lname was there as a left-over from a debugging
>attempt...):

Hi J,

I was unable to reproduce this. If I run the query you posted here
(after removing the left-over au_lname) with argument DESC and title, I
get the results in ascending order of title. Not sorted by au_lname.

The only way to get it to sort by descending au_lname is to leave the
left-over au_lname in and remove or comment the three CASE expressions.

>Also, you related that one part of the code resulted in an error...can
>you tell me how to unearth that? The Sql debugger seemed to be silent
>on that point to me...

I'm talking about the code as you posted it here in your original
message (with the left-over au_lname included). I get an error when I
try to create the procedure, or when I try to run that statements by
themselves. The only ways tol solve it are to remove au_lname, to add a
comma at the end of the line or to remove the three case expressions.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much again Hugo. All is well now -- with the DESC keywords
within each CASE statement:

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END DESC,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC

I am just starting to use the debugger in SQL and therefore am *very*
interested in learning as much as I can about using it to advantage.
Again, it didn't "complain" about:

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END ,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END
DESC

but, nor did the stored procedure return the intended result set!
I have used QA and the Profiler exclusively to help debug problems.

Jules|||On 25 Feb 2005 13:00:22 -0800, JJ_377@.hotmail.com wrote:

(snip)
>Again, it didn't "complain" about:
>ORDER BY
>CASE @.SortColumn WHEN 'title' THEN title END,
>CASE @.SortColumn WHEN 'au_lname' THEN au_lname END ,
>CASE @.SortColumn WHEN 'PubDate' THEN pubdate END
>DESC
>but, nor did the stored procedure return the intended result set!

Hi Jules,

It should not complain about this - it's valid T-SQL syntax. It
specifies that the results should be ordered by three columns:
* first by (depending on the value of @.SortColumn) either title or NULL,
ascending (since no direction is specified);
* next by (depending on the value of @.SortColumn) either au_lname or
NULL, again ascending;
* and finally, if the previous two didn't suffice to define the sort
order, by (depending on @.SortColumn) either pubdate or NULL, but for
this column a descending sort is specified.

>I have used QA and the Profiler exclusively to help debug problems.

Those tools are the best (IMO), both for writing and debugging SQL code.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||hmm...I understand what you are saying...interesting
it makes sense now - thanks!

Hugo Kornelis wrote:
> On 25 Feb 2005 13:00:22 -0800, JJ_377@.hotmail.com wrote:
> (snip)
> >Again, it didn't "complain" about:
> >ORDER BY
> >CASE @.SortColumn WHEN 'title' THEN title END,
> >CASE @.SortColumn WHEN 'au_lname' THEN au_lname END ,
> >CASE @.SortColumn WHEN 'PubDate' THEN pubdate END
> >DESC
> >but, nor did the stored procedure return the intended result set!
> Hi Jules,
> It should not complain about this - it's valid T-SQL syntax. It
> specifies that the results should be ordered by three columns:
> * first by (depending on the value of @.SortColumn) either title or
NULL,
> ascending (since no direction is specified);
> * next by (depending on the value of @.SortColumn) either au_lname or
> NULL, again ascending;
> * and finally, if the previous two didn't suffice to define the sort
> order, by (depending on @.SortColumn) either pubdate or NULL, but for
> this column a descending sort is specified.
>
> >I have used QA and the Profiler exclusively to help debug problems.
> Those tools are the best (IMO), both for writing and debugging SQL
code.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)