Dynamic SQL help.
-
I want to generate dynamic sql for
@sqlstr ='USE <databasename>
GO'
i am entering the database name in the form of a cursor loop and trying to execute it. But does not work (i have already tried the newline charchaters addition, in vain). I want to execute the create procedure in various databases (which also has dynamic sql in it). (The create proc does not allow me to enter the DBname in front of it like DBNAME.dbo.Procname which is why i wanted to run the USE DB and then execute it.
-
EXEC SP_MSFOREACHDB 'Use ? Create proc...'
-
I did not get you. I did not even find the SP_MSFOREACHDB.
My problem is i have a very complex procedure which i need to install and run from every database in my server (as i am taking the data from that db into my DBA database, this proc retrieves some data from system tables specific to that database, and cannot be accessed while you are in a differnt db)
-
Hi,
It's always worth seaching the articles and script library before posting a problem...
http://www.sqlservercentral.com/scripts/contributions/900.asp
You normally find eveything you need in there
Have fun
Steve
We need men who can dream of things that never were.
-
Normally i would have found it in BOL if it existed but my BOL does not show these sp_MSForEachDB and sp_ForEachTable.
thanx. Found them in Undocumented procs.
-
Can you post the proc you need to copy? I think there's a way to install it just once and use it from any db.
-
EXEC ('USE ' + @dataname )
where @dataname can be set with a cursor or an input parm
-
Your "Use ..." is out of scope. It works only within script you run via EXEC.
As soon as it's finished you are back to your original environment.
Add call for SP you want to execute to SQL string and run EXEC. Should help.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply