Decoded values in report

Hi,
I have a report generated from a dataset which has the state and county
codes. I have some other general purpose datasets which have the correspoding
county/state codes and descriptions. How do I link these to the report so
that I can see the descriptions instead of the code? I tried to drag the
description field onto the report, but it only pulls up the first value.
For example,
Folder (fldr) State (n_cd)
1234 2
4444 3
(Decode Dataset)
State Cd (n_cd) Description (n_desc)
2 FL
3 WA
When I drop n_desc onto the n_cd field of dataset 1, I get FL for all rows,
since it is picking up only the first row.
Please help.
Thanks,
ArshadMake sure it says =Fields!n_desc and not =First(Fields!n_desc)
regards,
Stas K.|||I did that and it gives the message: "....Report item expressions can only
refer to fields within the current data set scope, or if inside an aggregate,
the specified data set scope."
Thanks,
Arshad
"Sorcerdon" wrote:
> Make sure it says =Fields!n_desc and not =First(Fields!n_desc)
> regards,
> Stas K.
>|||Mr. Syed,
It is not possible to merge/link 2 datasets in a single report control
or lookup a value across datasets.
Ideally, you'd create a single dataset where the translation/decoding
of the state code to a description happens via a join between tables.
Andy Potter|||Thanks to all for your help. They need to make this more explicit in the
documentation!
"Potter" wrote:
> Mr. Syed,
> It is not possible to merge/link 2 datasets in a single report control
> or lookup a value across datasets.
> Ideally, you'd create a single dataset where the translation/decoding
> of the state code to a description happens via a join between tables.
> Andy Potter
>

Declare cursor based on dynamic query

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @.tempSQL varchar(1000)

This query will be generated based on my other conditon and will be stored in a variable

set @.tempsql = 'select * from orders'

declare cursor test for @.tempsql

open test

This code is not working.

please suggest

Nitin

Hi

I am writing the code as below

Declare @.testSQl varchar(1000)

set @.testsql = 'select * from orders'

declare test1 cursor for @.testSQl

The declare statement is not working . My @.testsql will be generated at run time.

Help

Nitin

|||You can not use dynamic sql while opening the cursors..

it should be like this

Declare Test1 cursor for
Select * From Orders|||

You could add the cursor creation to your dynamic sql and then just call sp_executesql for the built up string. Something like...

DECLARE @.sql nvarchar(4000)

--Get beginning of cursor

SELECT @.sql = 'DECLARE c CURSOR FOR'

--Decision code for what query is built

SELECT @.sql = @.sql + 'SELECT * FROM orders'

--Remainder of cursor with specific columns from above query

SELECT @.sql = @.sql + 'OPEN c FETCH NEXT FROM c INTO ....'

--Execute the string we just built

EXEC sp_executesql @.sql

|||

I don't like to ever advocate the use of cursors, but you can do this using a global cursor, if you really must:

create procedure test
as
declare @.name nvarchar(128)
exec ('declare bob cursor global for select name from sys.objects')
open bob

fetch next from bob into @.name
select @.name as works
close bob
deallocate bob
go

test

|||

Hi,

I dont know for the moment how to declare a cursor on a query from a string.. I dont think its possible this way. An alternative is to find a solution other than using the cursor, else you'd lose development time in trying to find a solution.

If you cannot find a solution, try to explain the problem, someone will try help out, and also cursors generally tend to be less performant.

|||this is not possible. i agree with waaz|||

Instead of local cursor, you can create a Global cursor with dynamic sql, which is available beyond the scope the dynamic sql

like this

set @.sql='declare test cursor global for '+ @.tempsql

exec sp_executesql @.sql

open test

close test

|||You can use dynamic SQL to create a global cursor as shown in another reply in this thread. But what are you trying to do? Why do you need to use a cursor? And why do you need to use dynamic SQL? Both have performance implications. And dynamic SQL has serious security implications that can compromise your database system and/or network. You will have to use techniques (both in the database and client-side depending on how you call your SP) that avoid SQL injection to protect your database and network from malicious users. Apart from these problems, dynamic SQL requires more maintainence because you have to grant more permissions to end users since checks are deferred to run-time unlike SPs with static SQL statements. So it is easy to create a cursor dynamically but that is not the right thing to do in majority of the cases.|||

Also, try not to ask the same question twice. This question was also answered in another thread. I have merged the threads into one.

|||

hey whitney,

i got the same problem of dynamic query with cursors..

You gave the alternative but i got the big cursor and its difficult for me to put the entire stuff in string.

Because it gets difficult to maintain for me.

Any help or comment regarding this will be appreciated.

Thanks a ton!!

dromyl@.hotmail.com

Debugging temporary SPs?

Sorry for cross-post, not really sure which group is best...
Is there any way to debug and or see the temporary SPs generated by
SQL/Server?
I'm seeing many problems that can only relate to these SPs since, eg,
preparing and executing:
Call somefunc(?,?)
results in
Incorrect syntax near the keyword 'SET'
and since 'somefunc' does not include the keyword SET, Im guessing it's
SQL/Server that is the culprit.Hi
Can you post the script
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:ILc4i.9138$tp3.37866@.nasal.pacific.net.au...
> Sorry for cross-post, not really sure which group is best...
> Is there any way to debug and or see the temporary SPs generated by
> SQL/Server?
> I'm seeing many problems that can only relate to these SPs since, eg,
> preparing and executing:
> Call somefunc(?,?)
> results in
> Incorrect syntax near the keyword 'SET'
> and since 'somefunc' does not include the keyword SET, Im guessing it's
> SQL/Server that is the culprit.|||vt wrote:
> Hi
> Can you post the script
>
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
And the call is:
{Call LK_LocLvl2(?,?)}
where each parameter is bound as an INPUT parameter.|||What I *think* I really need to know is: can I see the SP that
SQL/Server generates? I think Im seeing a lot of weird behaviour because
of these...and Id like to be sure one wqay or the other.|||Hi
well I am getting confused here, do you generate the procedure code
dynamically using a string variable, because the error message
Incorrect syntax near the keyword 'SET'
making me the thing you creating the code dynamically
if that the case then the error might be in where part
let me know if this correct
to find out what sqserver is doing use sql profiler
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:465170EE.8000801@.rhyme.com.au...
> vt wrote:
>> Hi
>> Can you post the script
> Procedure is:
> Create Procedure LK_LocLvl2(
> @.ll1 Varchar(12),
> @.ll2 Varchar(12))
> as
> Begin
> Select Distinct
> (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
> LOCATION_LEVEL_2,
> LOCATION_LEVEL_1
> From LOCATION_VW -- This is a view
> Where
> LOCATION_LEVEL_1 Like @.ll1+'%'
> and LOCATION_LEVEL_2 Like @.ll2+'%'
> and LOCATION_LEVEL_3 = ''
> and LOCATION_LEVEL_2 <> ''
> ;
> End;
> And the call is:
> {Call LK_LocLvl2(?,?)}
> where each parameter is bound as an INPUT parameter.
>|||vt wrote:
> well I am getting confused here, do you generate the procedure code
> dynamically using a string variable, because the error message
> Incorrect syntax near the keyword 'SET'
> making me the thing you creating the code dynamically
No; I am using ODBC and *it* (combined with SQL/Server) does the
automatic creation (according to the docs). It used to be possible to
turn that "feature" off, but as of 2000, it always creates temporary
SPs. And, as far as I can see, sometimes creates buggy ones (or at least
ones that highlight bugs).
The SP I am calling is permanent, and the code I prepare and execute via
ODBC is as I stated in the prior messages.
What I need is access to what ODBC/SQLServer are doing behind the
scenes. The ODBC log does not show the SQLServer side of things...
The profiler may shed some light, but so far it has not shown me any
temp SPs being created...so I guess Ill keep playing.
Thanks for the help, any further insights would be appreciated.|||Hi
In SQL profiler
Errors and Warning->Exception
Objects->objects@.created
stored procedures-> sp:starting, sp:stmtstarting
TSQL->sql:stmtstarting and sql:batchstarting
Hope this might help you
I still think the problem is at the WHERE clause,
execute the sp in query analyser like
exec LK_LocLvl2 xx,xxx
where xx and xxx with valid parameter and let see what happens
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:46518BE2.4010807@.rhyme.com.au...
> vt wrote:
>> well I am getting confused here, do you generate the procedure code
>> dynamically using a string variable, because the error message
>> Incorrect syntax near the keyword 'SET'
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>|||>> Incorrect syntax near the keyword 'SET'
By removing the SP and putting the statement directly in code, the SQL
that ODBC/SQLServer generates is definitely wrong.
Here is what I send to prepare then try to execute:
Select Distinct (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like ? and LOCATION_LEVEL_2 LIKE ? and
LOCATION_LEVEL_3 = '' and LOCATION_LEVEL_2 <> ''
and here is what ODBC/SQLServer generates (my ***emphasis***):
declare @.P1 int
set @.P1=NULL
declare @.P2 char(6)
set @.P2=' '
exec sp_prepexec @.P1 output, N'@.P1 char(6),@.P2 char(6) OUTPUT', N'Select
Distinct (LOCATION_LEVEL_2 + '' - '' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like @.P1 and LOCATION_LEVEL_2 LIKE @.P2
***OUTPUT*** and LOCATION_LEVEL_3 = '''' and LOCATION_LEVEL_2 <> ''''
Why it adds the 'OUTPUT' part to the middle of the 'where' clause seems
hard to fathom. I am looking into how they are bound...but it any case,
the ODBC/SQLServer interface is producing the wrong results.
Thanks for the help & suggestions...if you have any more thoughts, they
are always welcome.
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>

Debugging temporary SPs?

Sorry for cross-post, not really sure which group is best...
Is there any way to debug and or see the temporary SPs generated by
SQL/Server?
I'm seeing many problems that can only relate to these SPs since, eg,
preparing and executing:
Call somefunc(?,?)
results in
Incorrect syntax near the keyword 'SET'
and since 'somefunc' does not include the keyword SET, Im guessing it's
SQL/Server that is the culprit.Hi
Can you post the script
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:ILc4i.9138$tp3.37866@.nasal.pacific.net.au...
> Sorry for cross-post, not really sure which group is best...
> Is there any way to debug and or see the temporary SPs generated by
> SQL/Server?
> I'm seeing many problems that can only relate to these SPs since, eg,
> preparing and executing:
> Call somefunc(?,?)
> results in
> Incorrect syntax near the keyword 'SET'
> and since 'somefunc' does not include the keyword SET, Im guessing it's
> SQL/Server that is the culprit.|||vt wrote:
> Hi
> Can you post the script
>
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
And the call is:
{Call LK_LocLvl2(?,?)}
where each parameter is bound as an INPUT parameter.|||What I *think* I really need to know is: can I see the SP that
SQL/Server generates? I think Im seeing a lot of weird behaviour because
of these...and Id like to be sure one wqay or the other.|||Hi
well I am getting confused here, do you generate the procedure code
dynamically using a string variable, because the error message
Incorrect syntax near the keyword 'SET'
making me the thing you creating the code dynamically
if that the case then the error might be in where part
let me know if this correct
to find out what sqserver is doing use sql profiler
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:465170EE.8000801@.rhyme.com.au...
> vt wrote:
> Procedure is:
> Create Procedure LK_LocLvl2(
> @.ll1 Varchar(12),
> @.ll2 Varchar(12))
> as
> Begin
> Select Distinct
> (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
> LOCATION_LEVEL_2,
> LOCATION_LEVEL_1
> From LOCATION_VW -- This is a view
> Where
> LOCATION_LEVEL_1 Like @.ll1+'%'
> and LOCATION_LEVEL_2 Like @.ll2+'%'
> and LOCATION_LEVEL_3 = ''
> and LOCATION_LEVEL_2 <> ''
> ;
> End;
> And the call is:
> {Call LK_LocLvl2(?,?)}
> where each parameter is bound as an INPUT parameter.
>|||vt wrote:
> well I am getting confused here, do you generate the procedure code
> dynamically using a string variable, because the error message
> Incorrect syntax near the keyword 'SET'
> making me the thing you creating the code dynamically
No; I am using ODBC and *it* (combined with SQL/Server) does the
automatic creation (according to the docs). It used to be possible to
turn that "feature" off, but as of 2000, it always creates temporary
SPs. And, as far as I can see, sometimes creates buggy ones (or at least
ones that highlight bugs).
The SP I am calling is permanent, and the code I prepare and execute via
ODBC is as I stated in the prior messages.
What I need is access to what ODBC/SQLServer are doing behind the
scenes. The ODBC log does not show the SQLServer side of things...
The profiler may shed some light, but so far it has not shown me any
temp SPs being created...so I guess Ill keep playing.
Thanks for the help, any further insights would be appreciated.|||Hi
In SQL profiler
Errors and Warning->Exception
Objects->objects@.created
stored procedures-> sp:starting, sp:stmtstarting
TSQL->sql:stmtstarting and sql:batchstarting
Hope this might help you
I still think the problem is at the WHERE clause,
execute the sp in query analyser like
exec LK_LocLvl2 xx,xxx
where xx and xxx with valid parameter and let see what happens
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:46518BE2.4010807@.rhyme.com.au...
> vt wrote:
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>