November 10, 2014 at 3:31 am
Hi guys,
I am trying to execute the following script to get the object script in a file.
SELECT @lCommand = 'bcp "SELECT text FROM syscomments WHERE Id = ' + CAST(@lObjectId AS NVARCHAR(100)) + '" queryout '
+ 'd:\' + @lObjectName + '.sql -w'
+ ' -T -S' + @@servername
EXEC master..xp_cmdshell @lCommand
It is executing without any error and file also creating in the respective folder. But script is not there the file. It is blank.
Missing something? Please help.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
November 10, 2014 at 3:51 am
It's looking for the objects in master. Specify a database name in the query:
SELECT @lCommand = 'bcp "SELECT text FROM YourDatabaseName.dbo.syscomments WHERE Id = ' + CAST(@lObjectId AS NVARCHAR(100)) + '" queryout '
+ 'd:\' + @lObjectName + '.sql -w'
+ ' -T -S' + @@servername
EXEC master..xp_cmdshell @lCommand
-- Gianluca Sartori
November 10, 2014 at 4:15 am
Done :). Thank you very much.
SELECT @lCommand = 'bcp "SELECT text FROM ' + DB_NAME() + '.dbo.syscomments WHERE Id = ' + CAST(@lObjectId AS NVARCHAR(100)) + '" queryout '
+ 'd:\' + @lObjectName + '.sql -w'
+ ' -T -S' + @@servername
EXEC master..xp_cmdshell @lCommand
_____________________________________________
One ounce of practice is more important than tonnes of dreams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply