Dynamic Scripting for USE [DB]

Greetings,

I am having a difficult time figuring out a way to get this to work. The scripting executes without error, but does nothing. What I want it to do is change the connection to the database retrieved by the cursor.

DECLARE
@.PAR1 VARCHAR (256);
DECLARE DB_PAR CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST';

OPEN DB_PAR
FETCH DB_PAR INTO @.PAR1
BEGIN
EXECUTE ('USE ' + @.PAR1 +';')
PRINT @.PAR1 /*Show what the par value is */
END;
CLOSE DB_PAR;
DEALLOCATE DB_PAR;

Anyone have any ideas?

Something else I thought of. Oracle provides the ability to change the database connection by issuing "CONN USER/PASSWORD@.INSTANCE". I suppose SQL Server might provide something like this in SQLCMD, but I'm not using that right now.

Hi,

dioes the query SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST'; bring something back ? I guess not, right ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Yes, it does return a value. The database that I am looking for is "jeff_hrn_test" and taht is what returns in the variable when I print it.

If you run SELECT * FROM SYS.SYSDATABASES on any database within a SQL Server instance, it returns the full listing of all Databases on that Server. I just am looking for a specific name. It must contain HRN and TEST in the name of the Database.

So, it has a value, but the dynamic SQL doesn't actually do anything from what I can see.

|||

I have been able to determine an answer, potentially.

The problem is that I am executing this within dynamic sql. So, as soon as the execute completes, the context switches back.

Therefore, if I want to set the context, then run my next block of SQL Code, I have to make it into one gigantic EXECUTE!

I'm hoping to find a better way to do this. FRUSTRATING!!

By the way, Here's a sample code block:

DECLARE @.DB VARCHAR (50);
SET @.DB=(SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST')
EXECUTE('USE [' + @.DB + '];
SELECT * FROM TABLE')