I would be interested to see the answer to this question. Is this a bug or by design? Thanks.

Thanks to you both for your suggestions.  I ended up just reindexing every table in the database.  That might be kind of heavy handed but it worked.  The script now...

Yes, we were running the job from a remote machine which did not have SSIS installed.  When the job is run directly on the server it works.  Thanks for your...

Actually, I sent a query to the folks at NGS and it turns out that NGSSquirrel does not yet officially support SQL 2005. It will probably be another two...

Thanks for the review. It's nice to know that there are alternatives to AppDetective, which doesn't even support SQL 2005 yet.

This did the trick. Thanks to everyone for your help!

Here is another similar problem that will fit in with this thread. When I run this script:

DECLARE @table varchar(50)

SET @table = 'pubs..sysobjects'

PRINT @table

SELECT name FROM @table WHERE type='P' AND...

This provides the expected result after uncommenting "exec (@sqlt)". Thanks for your help.

One of the things that I'm trying to do is add users in a db. The script starts out running in Master but then, for testing purposes, I would...

Thanks for your reply. This runs without error but it does not change the db context.

DECLARE @dbname varchar(30)

SET @dbname = 'pubs'

declare @sqlt varchar (50)

set @sqlt = 'USE ' +...

Got it, I was missing "END" to end the cursor. Thanks again.

Thanks for your response. I followed your suggestions and now it appears that there is a problem with deallocating the cursor. I also tried "DEALLOCATE CURSOR for_each_loop".

CREATE PROCEDURE...

But will using DIR with xp_cmdshell allow you to plug in variables and funtions like this?

DECLARE @dir varchar(255)

SET @dir = 'DIR "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" + @dbname + "_db_" +...

Here is a good intro article from MS:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops4.mspx

See the sections on daily, weekly, and monthly tasks...

Thanks for joining back in with your comments. It's true that these backup files are copied from another server so this SQL server has no record of it in...