Dynamic query, local cursor variable and global cursors

Hi all.

I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.

So, first things first: let me explain what I need to do. I am

designing a web application that will allow users to consult info

available in a SQL2000 database. The user will enter the search

criterea, and hopefully the web page will show matching results.

The problem is the results shown aren't available per se in the DB, I

need to process the data a bit. I decided to do so on the SQL Server

side, though the use of cursors. So, when a user defines his search

criteria, I run a stored procedure that begins by building a dynamic

sql query and creating a cursor for it. I used a global cursor in order

to do so. It looked something like this:

SET @.sqlQuery = ... (build the dinamic sql query)

SET @.cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor

This works fine, if there's only one instance of the

stored procedure running at a time. Should another user connect to the

site and run a search while someone's at it, it'll fail due to the

atempt to create a cursor with the same name.

My first thought was to make the cursor name unique, which led me to:

...

SET @.cursorName = 'myCursor' + @.uniqueUserID

SET @.cursorQuery = 'DECLARE '+ @.cursorName + 'CURSOR FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

...

The problem with this is that I can't do a FETCH NEXT FROM @.cursorName since

@.cursorName is a char variable holding the cursor name, and not a

cursor variable. So to enforce this unique name method the only option

I have is to keep creating dynamic sql queries and exucting them. And

this makes the sp a bitch to develop and maintain, and I'm guessing it

doesn't make it very performant.

So I moved on to my second idea: local cursor variables. The problem with

this is that if I create a local cursor variable by executing a dynamic

query, I can't extract it from the EXEC (or sp_executesql) context, as

it offers no output variable.

I guess my concrete questions are:

Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?

Anybody sees another way arround this?Thanks in advance,

Carlos

First off, let me just say that I kind of hate myself for the answer I am going to give you, because almost certainly the processing you are trying to do with a cursor in SQL could be done easier/better/faster/etcer outside of T-SQL, and you would be far happier with the final result.

On the other hand, I am pretty sure there is a way to do this, using sp_executesql by passing a parameter of type cursor to the proc:

declare @.query nvarchar(max), @.number int, @.mainCursor cursor

set @.query = ' set @.cursor = cursor for select 1 as number
open @.cursor'

exec sp_executesql @.query,N'@.cursor cursor output',@.mainCursor output

fetch next from @.maincursor into @.number
select @.number

Good luck with this, but seriously consider doing this outside of T-SQL :)

|||Hey Louis.

I can only say I was amazed to see that your piece of code worked,

since I had tried about the same thing a while ago and it didn't work.

So I did a little digging to see what the difference was between your

implementation and mine. And then I discovered something odd: if you

put the OPEN @.cursor after the sp_executesql command, instead of in it, you get an error saying that your variable has no cursor allocated to it. Go figure.

Well, I guess this is part of why you're telling me to give up T-SQL.

Believe me, I'm no masochist. I know this would be much easier if I did

it on the webserver's side, where I have a beautiful JVM eager to do

the job. But I have to disagree when you say it would be faster.

The trouble is that I have to go through a great amount of data to

display but a few lines of result to the user. The overhead involved in

transfering all this data to another system is just too great (it's a

web application, so the time scale is very short: a few seconds will be

enough to hamper it). So I guess I'll have to live with it, right?

Anyways, many thanks for your help. Problem solved. Moving on.

Carlos

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

sql

Dynamic Parameter Question

I have a report that has different layers of resolution: Global, Region, State, Facility and Issue. For different each level
chosen I want to display a different set of parameters and execute a different SP for my dataset (which will return the same rows).
I've seen posts where I can do the dynamic SP calls but I'm stumped on the showing and hiding of the parameters that I need. I
tried putting a =IIF(blah... as the prompt but that just displayed the expression and didn't evaluate it.
ANY help is appreciated to keep me from having to do 5 different reports :)
Thanks
ScottI think to do this you would want cascading parameters. Then when you call
your stored procedure you will need a wrapper sp that determines which to
call.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> I have a report that has different layers of resolution: Global, Region,
State, Facility and Issue. For different each level
> chosen I want to display a different set of parameters and execute a
different SP for my dataset (which will return the same rows).
> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
showing and hiding of the parameters that I need. I
> tried putting a =IIF(blah... as the prompt but that just displayed the
expression and didn't evaluate it.
> ANY help is appreciated to keep me from having to do 5 different reports
:)
> Thanks
> Scott
>|||Or you create 1 report with all the parameters and then use linked reports
in which you enable specific parameters.
It's like creating 5 reports, but way faster :-)
Hth,
Tom
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||Yeah, but how can I set up my parameters to be cascading like that?
IE
Global requires no parameters
Region requires a FK of a region
State requires a two letter state
Facility requires a facilities key value (int)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
>I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
>> I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
>> chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
>> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
>> tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
>> ANY help is appreciated to keep me from having to do 5 different reports
> :)
>> Thanks
>> Scott
>>
>|||even if you have cascading paprameters, it would still show up on the report
though greyed out, making the report parameter section really cluttered.
Having the IIF at the prompt of the parameters is a real cool add on. Should
be added to the wish list.
I would just create subreports, as suggested later
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||I was thinking that these were somehow related. Only certain regions based
on Global, only certain states based on region, only certain facilities
based on State. If so, they you have each of them based on a dataset where
the dataset query parameter is based on the preceding parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:Oez$zzbvEHA.716@.TK2MSFTNGP10.phx.gbl...
> Yeah, but how can I set up my parameters to be cascading like that?
> IE
> Global requires no parameters
> Region requires a FK of a region
> State requires a two letter state
> Facility requires a facilities key value (int)
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
> >I think to do this you would want cascading parameters. Then when you
call
> > your stored procedure you will need a wrapper sp that determines which
to
> > call.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in
message
> > news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> >> I have a report that has different layers of resolution: Global,
Region,
> > State, Facility and Issue. For different each level
> >> chosen I want to display a different set of parameters and execute a
> > different SP for my dataset (which will return the same rows).
> >> I've seen posts where I can do the dynamic SP calls but I'm stumped on
the
> > showing and hiding of the parameters that I need. I
> >> tried putting a =IIF(blah... as the prompt but that just displayed the
> > expression and didn't evaluate it.
> >>
> >> ANY help is appreciated to keep me from having to do 5 different
reports
> > :)
> >>
> >> Thanks
> >> Scott
> >>
> >>
> >
> >
>