Dynamic SQL does not work!

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

Here is the code I am using:
--EXECUTE SQL Statement on all Databases

DECLARE
@.DatabaseName varchar(100)
, @.SQL varchar(500)

DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysdatabases
ORDER BY
DatabaseName

OPEN DBNameCursor

FETCH NEXT FROM DBNameCursor
INTO @.DatabaseName

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Change to Database
SELECT
@.SQL = 'USE ' + @.DatabaseName

EXEC(@.SQL)

--SQL Statement to be run
EXEC dbo.SPName

FETCH NEXT FROM DBNameCursor
INTO @.DatabaseName
END

CLOSE DBNameCursor
DEALLOCATE DBNameCursor

-------
Thanks in advance!
Vishal SinhaOn Feb 7, 7:02 am, "SQLJunkie" <vsinh...@.gmail.comwrote:

Quote:

Originally Posted by

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?
>
Here is the code I am using:
-- EXECUTE SQL Statement on all Databases
>
DECLARE
@.DatabaseName varchar(100)
, @.SQL varchar(500)
>
DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysdatabases
ORDER BY
DatabaseName
>
OPEN DBNameCursor
>
FETCH NEXT FROM DBNameCursor
INTO @.DatabaseName
>
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Change to Database
SELECT
@.SQL = 'USE ' + @.DatabaseName
>
EXEC(@.SQL)
>
-- SQL Statement to be run
EXEC dbo.SPName
>
FETCH NEXT FROM DBNameCursor
INTO @.DatabaseName
END
>
CLOSE DBNameCursor
DEALLOCATE DBNameCursor
>
-------
Thanks in advance!
Vishal Sinha

If I recall correctly EXEC starts a new thread that won't know about
the preceeding USE statement.

My favorite dynamic SQL site:
http://www.sommarskog.se/dyn-search.html|||SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

Yes, but the effect of the USE lasts only for the duration of the of the
dynamic SQL.

Quote:

Originally Posted by

SELECT
@.SQL = 'USE ' + @.DatabaseName
>
EXEC(@.SQL)
>
-- SQL Statement to be run
EXEC dbo.SPName

If all you want to do is to run a stored procedure in each database,
this is the easiest way to do:

SELECT @.SPname = @.DatabaseName + '.dbo.SPName'
EXEC @.SPname

You may also be interested in sp_MSforeachdb:

EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'

This procedure is undocumented and unsupported, but it's nevertheless
fairly popular.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @.SPname = @.DatabaseName + '.dbo.SPName'
EXEC @.SPname

But what if I have to run SPs like SP_updatestats etc? then it will
not work.

Thanks!

On Feb 7, 5:35 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

SQLJunkie (vsinh...@.gmail.com) writes:

Quote:

Originally Posted by

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

>
Yes, but the effect of the USE lasts only for the duration of the of the
dynamic SQL.
>

Quote:

Originally Posted by

SELECT
@.SQL = 'USE ' + @.DatabaseName

>

Quote:

Originally Posted by

EXEC(@.SQL)

>

Quote:

Originally Posted by

-- SQL Statement to be run
EXEC dbo.SPName

>
If all you want to do is to run a stored procedure in each database,
this is the easiest way to do:
>
SELECT @.SPname = @.DatabaseName + '.dbo.SPName'
EXEC @.SPname
>
You may also be interested in sp_MSforeachdb:
>
EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'
>
This procedure is undocumented and unsupported, but it's nevertheless
fairly popular.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @.SPname = @.DatabaseName + '.dbo.SPName'
EXEC @.SPname
>
But what if I have to run SPs like SP_updatestats etc? then it will
not work.

Au contraire, it will work just fine! If you say:

EXEC mydb..sp_systemprocedure

the system procedure will execute in the context of mydb.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks - it worked!

Momentary lapse of reason :)

On Feb 8, 5:39 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

SQLJunkie (vsinh...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @.SPname = @.DatabaseName + '.dbo.SPName'
EXEC @.SPname

>

Quote:

Originally Posted by

But what if I have to run SPs like SP_updatestats etc? then it will
not work.

>
Au contraire, it will work just fine! If you say:
>
EXEC mydb..sp_systemprocedure
>
the system procedure will execute in the context of mydb.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

dynamic SQL as report source

I would like to use a stored procedure with parameters which creates and
executes dynamic sql as a source for a report.
I get an error, though, stating
"could not generate a list of fields for the query".
I tried adding the fields manually to the report definition and matching
them with the fields in the dynamic SQL, but I received error messages like:
"The value expression for the textbox 'blah' refers to the field 'blah'.
Report item expressions can only refer to fields within the current data set
scope. ..."
I guess I can just put all the sql into the dataset directly, but it would
be easier to manage in a stored procedure.
It needs to be dynamic because I have to build a dynamic Order By clause
with different alternative data types, so I can't use a Case statement.
The stored procedure is something like this:
Create Procedure sp
@.param1 int,
@.param2 int
as
declare @.sqlString (varchar(200))
set @.sqlString = 'select col1, col2
from tbl1
where col1 = ' + @.param1 +
'col2 = ' + @.param2
exec @.sqlString
Thanks!
Billwhat degree of dinamic is the order by clause you are making ... I mean, it
ends up being 3 or 4 cases, or the caller has complete control which column
to order?
Why do I say that? ... well, if there are 3 or 4 cases, u can put the static
part of the query in a user defined function, and have 3 or 4 cases where u
return a select of the function with a static order clause ...
Other than that, I cant think of anything ...
"bill" wrote:
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>|||Yes you have to add them manually or type them each time...
"bill" <belgie@.datamti.com> wrote in message
news:eHX3q2CnEHA.3684@.TK2MSFTNGP10.phx.gbl...
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>