How to exec stored proc dynamically

Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMain and
sp_RebuildIndexesSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndexesSub(@.listOnly bit=0, @.maxfrag Decimal=30.0)

This runs fine if I do pubs..sp_RebuildIndexesSub
However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndexesMain(@.dbName sysname, @.listOnly bit=0,
@.maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @.dbName

Declare @.execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @.dbName
If (@.@.FETCH_STATUS<>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @.dbName + ''''
Return -1
End

While(@.@.FETCH_STATUS=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @.dbName
Print Char(13)
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
EXEC sp_executesql @.execstr, N'@.listOnly bit, @.maxFrag Decimal',
@.listOnly, @.maxFrag
Fetch crDbs INTO @.dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
sunitjoshi@.netzero.comI believe if you change:
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
to
Set @.execstr = '[' + @.dbName + '..sp_RebuildIndexesSub] '

it should work.

Personally, instead of creating sp_RebuildIndexesSub in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndexesSub 0, 30.0
END'

Be sure not to run "exec master..sp_RebuildIndexesSub 0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.|||Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL..sp_RebuildIndexesSub'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

*** Sent via Developersdex http://www.developersdex.com ***|||Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@.execstr).

SET @.execstr = 'USE ' + @.dbname + ' exec sp_RebuildIndexesSub ' +
RTRIM(@.listOnly) + ',' + RTRIM(@.maxFrag)
EXEC (@.execstr)|||Got it. Had to change to this

Set @.execstr = @.dbName + '..sp_RebuildIndexesSub'
Exec @.execstr @.listOnly, @.maxFrag

thanks
Sunit|||You are right. Your code is much cleaner :)

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")

Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.

works:
[build @.RestoreCmd]
exec (@.RestoreCmd)

doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder

Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.

"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]

Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder

Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.

Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.

Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.
Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.
|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp