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 BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

sql