Carl,

The databse changes for the duration of the batch and EXEC() is a separate batch. Here's a sample code to show that.

DECLARE @cmd varchar(256)
SET @cmd = 'USE pubs SELECT db_name()'
USE northwind
SELECT db_name()
EXEC(@cmd)           // pubs
SELECT db_name()     //northwind

Keep in mind, that the USE command isn't allowed in a stored procedure.


>I have this process that I would like to select the name of an archive database the was just created archive SQL script. The problem is that I do not know the complete name because the date is attach to the name.
>
>SET @ArchDatabase = 'Archiveme' + CAST(DATEPART(month,@dtLogTime) as varchar(10)) + CAST(DATEPART(day,@dtLogTime) as varchar(10))+ CAST(DATEPART(year,@dtLogTime) as varchar(10))
>
>When I tried to do the following to select this database

>SET @SQL = 'SELECT * FROM sysdatabases WHERE name like '''+ @ArchDatabase + ''''
>EXEC (@SQL)
>IF @@ROWCOUNT > 0
>	BEGIN
>		SET @SQLDatabase = 'USE ' + @ArchDatabase
>		EXEC (@SQLDatabase)
>	END

>The database remains UNSELECTED.
>
>Is there another way to attack this problem, then the way I'm going about it. I know I can redo this in a DTS job with ActiveX scripting, but I hoping that I can accomplish it this way.
>