Redirecting output to Txt file via SP

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

SQL Server Administration (2000)

Redirecting output to Txt file via SP

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-14 : 12:24:56

Hi,
I wrote an SP but I want the output to go to a certain directory as a text file instead of displaying the results in grid when i execute the sp. How do i achieve this?
Thanks,
Sarat.

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-14 : 12:27:41

I think I found a way via 'sp_add_job' which has an output parameter to be specified. This should suffice.
Thanks,
Sarat.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 12:42:24

Huh?

What are you trying to write to a text file?

Why not use bcp?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-14 : 12:53:54

You can do this using xp_cmdshell in your stored procedure. sp_add_job is used to create jobs in the SQLServerAgent.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-14 : 13:02:53

Ok, I spoke too early!! I don't think I can get what I want by sp_add_job procedure.
I wrote a reindex procedure and the output gives a list of tables for whom indexes were built, the time taken to complete the job etc.

ex:

Job started at: May 13 2003 2:57PM
Building Indexes for all PeopleSoft tables
********************************************
Rebuilding index PS_PSACCESSPRFL for table PSACCESSPRFL
Rebuilding index PS_PSACTARCHIVE for table PSACTARCHIVE
Rebuilding index PS_PSACTIVEXLIC for table PSACTIVEXLIC
Rebuilding index PS_XLATTABLEDEL for table XLATTABLEDEL
Rebuilding index PSAXLATTABLEDEL for table XLATTABLEDEL

Number of Indexes Built = 12809
Job ended at: May 13 2003 3:49PM
** End **

What I want is that this output should go to a text file instead of grid since i would schedule this to run every sunday and monday i want to see the text file with results.
Thanks,
Sarat.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-14 : 13:05:42

You should just use dynamic sql for this instead of outputting the statements to a text file. Have the output go into a variable and then execute the variable. Doing it this way will allow you to be able to add objects without having to change the code.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-14 : 13:41:41

Hey Tara,
I assigned the output to a variable,
ex:
select @output = 'Rebuilding index ' + @indexname + ' for table ' + @tablename
--
But what do you mean by executing a variable? also how/where do i specify the path?
thanks,
sarat

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-14 : 13:48:46

I misunderstood your last post.

DECLARE @indexname varchar(50)
SET @indexname = 'Test_index'
DECLARE @tablename varchar(50)
SET @tablename = 'SomeTable'

DECLARE @sql VARCHAR(7000)
SET @sql = 'master.dbo.xp_cmdshell ''echo Rebuilding index' + @indexname + ' for table ' + @tablename + '>C:\temp\SomeFile.txt'''

EXEC (@sql)

Note: > will output your data into a new file, >> will append the data to the file, so usually your first command uses >, then all of the others use >>.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources