osql Cursor values to output file
-
guys,
I have following stored procedure which runs osql cmd to write to a file every next value of a cursor, but the problem is the file is overwritten for each value of the cursor. Is there any way to modify the stored proc to append values to the same outputfile. Basically the outputfile contains the output only for last value of the cursor.
ALTER PROCEDURE [dbo].[INSERTTEST2]
AS
BEGIN
declare C_mf1 cursor for
select tab_name from meta
DECLARE @cmd varchar(2000)
DECLARE @TAB_NAME VARCHAR(20)
DECLARE @FETCHCOUNT INT
set @tab_name = ''
set @fetchcount = 0
open C_mf1 fetch next from C_mf1 into @tab_name
WHILE @@fetch_status<>-1
BEGIN
set @cmd = 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" -oc:\myTable.sql'
select @cmd -- + '...'
EXEC master.dbo.xp_cmdShell @cmd
set @fetchcount=@fetchcount+1
fetch next from C_mf1 into @tab_name
END
END
Any suggestions and inputs would help
Thanks
-
'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" >>c:\myTable.sql'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
... and, just to be on the safe side, you should enclose the server instance name in double quotes because of the special characters it contains.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Perfect Jeff Thanks
-
You bet... thank you for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
One more quickie, is there any way to replace the output file with a new output file each time when I run the osql command.
Thanks
-
Absolutely! You want a new file name each time or do you want to reuse the same file name?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
I want to use the same file name but one thing though, from the stored proc definition you would see that I pass cursor values into osql commmand.
If I use '>' it creates new file, but it loads data only for the last value of the cursor. Whereas '>>' appends the data for all values of the cursor but it works on the same output file with out creating the new file when it runs.
Is there any solution ? can I run a windows command to delete the file from the stored procedure before it executes the osql command.
Thanks
-
Good to see you read the post the Tara wrote on the "other" forum, but she's not entirely correct. [ > ] will erase a file if it exists and then create a new one. [ >> ] will append to a file if it exists and still create a new one if it does not.
So... just before you run the cursor with the append output ">>", why don't you just run another dip to the operating system that deletes a file if it exists?
ALTER PROCEDURE [dbo].[INSERTTEST2]
AS
BEGIN
declare C_mf1 cursor for
select tab_name from meta
DECLARE @cmd varchar(2000)
DECLARE @TAB_NAME VARCHAR(20)
DECLARE @FETCHCOUNT INT
set @tab_name = ''
set @fetchcount = 0
set @cmd = 'DEL c:\myTable.sql /q' --/q is the "quiet" or "promptless" mode of the command
EXEC master.dbo.xp_cmdShell @cmd
open C_mf1 fetch next from C_mf1 into @tab_name
WHILE @@fetch_status<>-1
BEGIN
set @cmd = 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" >> c:\myTable.sql'
select @cmd -- + '...'
EXEC master.dbo.xp_cmdShell @cmd
set @fetchcount=@fetchcount+1
fetch next from C_mf1 into @tab_name
END
END
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Would it be better to have 1 file per table?
set @cmd = 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" > "c:\' + @TAB_NAME + '.sql" '
Separate files will allow you to order execution of inserts without editing "sql" file.
_____________
Code for TallyGenerator -
Thanks again I didnt know that we can run OS commands from OSQL command.
-
You're not... the DEL command is being executed by xp_CmdShell and the >> is just part of any command line as a redirection of output from whatever command is on the same line.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply