osql cursor values into output file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

osql cursor values into output file

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-08-03 : 09:38:52

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 outfile 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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-04 : 21:44:43

Why not just use bcp (or osql) with the query rather than a cursor?

If you must do this then output to a temp file then use a type command to append.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-06 : 07:43:13

The idea of using BCP or OSQL instead of a cursor is the proper way to go... that not withstanding...

set @cmd = 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" >>c:\myTable.sql'

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-06 : 07:44:49

P.S. Hmmm... This looks very familiar... Pretty sure I also answered this on another forum for the same person.

--Jeff Moden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-06 : 12:00:10

sqlcmd is v2005.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-08-06 : 17:13:47

IS there any way when my stored proc runs the following command that it creates a new file mytable.sql after deleting the old one.

'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" >>c:\myTable.sql'

Right now it is writing to the same file.

Any suggestions and inputs would help

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-06 : 18:01:01

>c:\myTable.sql

> is new, >> is append.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-08-06 : 21:44:09

Thank you for your reply tkizer.

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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-06 : 22:25:07

[ > ] 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

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-08-07 : 08:10:13

Thanks again Jeff that works just fine.

I didnt know that we can os commands through osql.

Thanks

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-07 : 08:17:52

You're not really... the DEL is not done using OSQL... It's done using xp_CmdShell just like OSQL. The > and >> are just part of the command line and not really part of OSQL at all.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources