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
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.
Dynamic Scripting for USE [DB]
m_3ryan
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')
michivo
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.
weehyong
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
---