Executing on a different database.

Gday guys.

I have a question on a problem I'm unable to solve. Help would be much appreciated.

I'm using SQL2005 at the moment,

Basically what I'm trying to do is Execute a string. The trick is that I want to do it on a different database than the one I'm currently working on.

I get the execution string from the Information_Schema.ROUTINES system table, so they cannot be modified to suit my needs.

Basically, I'm trying to move all UDF's and stored procedures that meet certain criteria to a different database using one script.

I know all about the USE statement, but I can't append it infront of the execution string, because it complains that CREATE or ALTER needs to be the first statement. I also can't use the USE statement before I call EXECUTE, because it complains that a USE statement may not be used within a procedure.

Does anybody know of a way I can execute those strings on a different database?

Although I always suggest not using dynamic SQL its the appropiate way to do some adhoc things:

DECLARE @.SQLText VARCHAR(MAX)
DECLARE @.DatabaseName VARCHAR(MAX)

SET @.DatabaseName = 'Mydatabase'
SET @.SQLTEXT = 'USE ' + @.DatabaseName

SELECT @.SQLTEXT = @.SQLTEXT + ' GO ' + ROUTINE_DEFINTION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE <YourFiltercriteriaHere>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Executing Multiple Scripts

Hey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly....
any suggestions...
thanks,
jonathanOSQL with a batch file. DTS/SSIS. Scheduled job.
Many ways to do this...|||do you know of a site or book where I can read how to do so....
a good search string for google....|||just giving it away today...

DECLARE @.SQLServer VARCHAR(100)
DECLARE @.Database VARCHAR(100)
DECLARE @.UserName VARCHAR(100)
DECLARE @.Password VARCHAR(100)
DECLARE @.UseWindowsAuthentication BIT
DECLARE @.Path VARCHAR(1000)
DECLARE @.FilePathToSQLFiles VARCHAR(200)

/*################################################# ###########################
If you are unsure of your sql server name, you can use the following
SELECT @.@.SERVERNAME. This should be the sql server where the database resides
that you are updating.
################################################## ###########################*/
SET @.SQLServer = 'MyServer'

/*################################################# ###
@.Database is the name of the database you are updating.
################################################## ####*/
SET @.Database = 'MyDB'

/*################################################# ###
Is the path to the sql files that you wish to execute.
Example FilePath : C:\SQL Scripts\
################################################## ###*/
SET @.FilePathToSQLFiles = 'C:\SQL Scripts\Create Scripts\'

/*################################################# ###################################
If you choose to use windows auth, you do not have to fill in a user name or password,
but your network account has to be a sysadmin on the sql server.
1 = use windows auth
0 = sql auth
################################################## ##################################*/

SET @.UseWindowsAuthentication = 1
SET @.UserName = ''
SET @.Password = ''

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Path

DECLARE cFiles CURSOR FOR
SELECT DISTINCT [SQLFileName]
FROM #SQLFiles
WHERE [SQLFileName] IS NOT NULL AND
[SQLFileName] <> 'NULL'
ORDER BY [SQLFileName]

DECLARE @.vFileName VARCHAR(200)
DECLARE @.vSQLStmt VARCHAR(4000)

OPEN cFiles

IF @.UseWindowsAuthentication = 0
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -U ' + @.UserName + ' -P ' + @.Password + ' -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

IF @.UseWindowsAuthentication = 1
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -E -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

CLOSE cFiles
DEALLOCATE cFiles

Print '################################################# ################################################'
Print 'Please review the log file located at ' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt'
Print '################################################# ################################################'
GO
DROP TABLE #SQLFiles
GO|||Much much appreciated...|||just giving it away today...

Slut

This message is to short

Executing more than one stored procedures in a DataReader

Hey guys,

I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,

However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?

I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)

Hope to get some suggestions and advice from you guys,

Thank you very much in advance.

Combine the two stored procedures into a third that will execute both.

|||

thanks Mike for your response,

any other better ways?

as there would be too many storedprocs just for combining procedures and it might get messy..

I was thinking to write a custom class and configure the class as I would for Sqlcommand storedprocedure, and then combine the various StoredProc Classes with a ";" and pass into the sqlcommand text.

But would this add-on to more performance defect? as there will be extra objects involved..

Please advice. Thanks.

|||

I don't think by any means or ways its a good idea to execute 2 or more procedures simultaneously from code. If you create such a class which can call and handle 2 or more SPs then also you'll have some questions to answer, such as what about the parameters ? You must be having different parameters for different SPs. Out of them, some may be out put type. What if an error occurs while executing any of the SPs ?.

So, my advice to you is better you execute them one by one if you don't have the compulsion ( which I don't think you'll be having ) to execute them simultaneously.

|||

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Suprotim Agarwal:

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Hi,

Thanks for showing this sample but I already know about this, just wanted to find out if there is an alternative.

To Dhimant: It is possible to handle Output Parameters and so on and it is more effective as it only takes one trip to the server. However, I only need to do this mostly for queries with select statements, other stored procs which require calling two or more procs, i usually execute them in one proc itself. The reason why i didnt want to combine the selection procs is because, there may be too many combinations and things will get messy. Thanks for your advice.

Executing a Stored Procedure result

Hi Guys..

How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..

I got this results from running (sp_dropallindex) like this:

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO
ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO
DROP INDEX table1.index1 GO
DROP INDEX table1.index2 GO

I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...

I tried something like this:

DECLARE @.DROP AS VARCHAR(8000)
SET @.DROP='exec sp_drop_allindex'
EXECUTE (@.DROP)

and i see the same out , but my indexes and PK still there ... i'm confused about it ..

PLEASE HELP ME OUT :DI'd use BCP with QUERYOUT and then OSQL against the output file.

BCP "exec db_name.dbo.sp_dropallindexes" queryout drop_all_indexes.sql /S server_name /T
OSQL -i drop_all_indexes.sql -S server_name -E -d db_namesql

execute SQL task with in a For Each Loop

hi guys,

i'm trying to do a bulk insert through a t-sql which accepts 3 params.

i frist run a SQL statement to get a list of DB's to update through a SQL task, then i save the output to a variable object. which is been used by the For each loop.

i've done the mapping with the object fine. but i want to call a SQL task within for each loop, and parse the above results as parameters. i tried parsing it as

exec mysp3 @.[User::fundid], @.[User::dbCode], @.[User::subfund]

where fundiid, dbcode and subfund are user defined variables that gets updated by the ado recordset.

this fails with the error msg:

SSIS package "SP test.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC @.[User::fundid], @.[User::dbcode], @.[User::subfund] failed with the following error: "Incorrect syntax near 'User::fundid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Shred the contents of the variable: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "SP test.dtsx" finished: Failure.

i also tried

exec mysp3 ?, ?, ?

then mapping the inputs still doesn't work

same thing with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC ?,?,? failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Look here:

http://www.sqlis.com/default.aspx?81

|||

Thanks Loom,

just what i wanted. i didn't know you have to interpret your expressions differently depending on the connection type. well now i do

by the way because of these different data conection types, my variable types are different. for example i save my fundid variable as int32. when using a OLE DB connection under parameter mapping there's no int, but numeric and it craps out when i try to do the mapping. is there a way to cast this? when passing to the t-sql?

Execute process task depending on query result

Hi Guys,

I wonder if you can help with the following requirement.

I want to be able to conditionally execute an 'execute process task' depending on the result of a query. I have a table which I will select one record/row from upon each execution, this record has a char 1 'type' field which is the indicator for what process to then execute.

This should be quite a simple package and will be run every 60 seconds so needs to be as efficient as possible.

I am thinking I should go along the lines of using an Execute SQL task to select my row in to a result set, and using a series of precedence expressions to determine what process to execute. But im not really sure how.....

I am a newbie to SSIS and 2005 in general so would appreciate any help you can provide

Chris

You can use your Execute SQL Task to store the column value to a variable. This post explains how to do so (it uses Excel as the data source, but the process is the same for a SQL source) http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772416&SiteID=1.

Drag a precedence constaint from the Execute SQL to the Execute Process. Double-click the precedence constraint, change the Evaluation option to Expression and Constraint, set Value to success, and set the Expression to @.your_variable=="1" (replace 1 with the appropriate value for this task). Continue adding Execute Process tasks and precedence constraints, altering the expression as appropriate.

Hope this helps.

|||

Thanks for your help!

All works