May 12, 2011 at 8:21 am
Hi all,
I'm trying to export a variable(@command) that is declared and set in a stored procedure. I broke down the procedure to it's bare bones to hopefully make this easier. I've tried the below with errors. My expected outcome is a text field that will list all the values that are stored within the variable(@Command).
DECLARE @dirvarchar(128)
DECLARE @Filenamevarchar(128)
DECLARE @Commandvarchar(250)
DECLARE @Exportvarchar(500)
SET @dir = '\\Export\Folder\'
SET @Filename = 'Index.txt'
SET @Command = 'Alter Index on [TEST] on [TEST] REBUILD'
SET @Export = 'bcp "@Command" queryout ' + @dir + @Filename + ' -T -S ' + 'Server\Instance'
EXEC master..xp_cmdshell @Export
error: Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@Command".
May 12, 2011 at 8:28 am
You just need to build your @Export string a little more carefully - something like this:
SET @Export = 'bcp "' + @Command + '" queryout ' + @dir + @Filename + ' -T -S ' + 'Server\Instance'
John
Edit: Actually, I'm not sure why you're trying to BCP an ALTER INDEX statement... what exactly are you trying to achieve?
May 12, 2011 at 8:37 am
John,
The procedure is creating two different statements. The first will Rebuild and the second Reorganize indexes in the database based on fragmentation percentage > 30 <. After the procedure generates all statements I execute them all using EXEC (@Command). I want to export all these statements that are run to a text file to keep track.
May 12, 2011 at 8:51 am
If I'm understanding you correctly, I think you need to use sqlcmd instead of bcp. And take the @Command outside the inverted commas in the way that I showed.
John
May 12, 2011 at 9:14 am
I use sqlcmd to run mass update scripts in a batch file. I've never thought to use it to export data from a procedure that runs once a month. Are you sure this is the best way to do that.
What I'm doing is basically Print @command but this print needs to save to a text file. How can I use sqlcmd to do that?
Thanks for your time John.
May 13, 2011 at 1:34 am
Look up the syntax for bcp - it needs to take a query that returns a result set. ALTER INDEX does not do that. Therefore your only option is to use sqlcmd. You will need to experiment with the options for that in order to get the output into a text file. I would start by trying either the -o switch, or piping the output manually to a file: [font="Courier New"]sqlcmd [options] > c:\Myfolder\Myfile.txt[/font].
John
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply