Re: Create Job to dump SP results into a text file.

From: Baisong Wei[MSFT] (v-baiwei_at_online.microsoft.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 03:09:14 GMT

Hi moodaddy,

I am glad to hear that the osql works fine now.

As in my last reply, based on my knowledge, by using osql, you cannot get a
TAB limited file. Here is one method I could figure out now is:
1) Save the result set of a stored procedur in a table.
2) using BCP to save the content of this table to a tab limited log file OR
using DTS to save it directly to an Excel file.
The pre-requirment is that the result set should be saved in a table

Here is what I have done:
1) A simple stored procedure
create proc au_all
as
select * from authors

First, I create the table to hold the result from the table ( it is a
temporary table)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO

CREATE TABLE [dbo].[authorsx] (
        [au_id] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [contract] [bit] NOT NULL
) ON [PRIMARY]
GO

and also, I use BCP to create a format file for the output ( this could be
run under the command prompt)
BCP PUBS..authorsx out c:\c.txt -U sa -P Mypassword
( For this part, please refer to the 'bcp utility, format files' in the
Books Online)
Then you could press Enter until it appear
'Do you want to save this format information in a file? [Y/n] '
Type 'Y' and then it appear:
'Host filename: [bcp.fmt]', type a name to generate the format file, I use
'authorsx.fmt'

After that, you will get a data file c.txt and the format file authorsx.fmt
Use the Notepad to open this format file and you will get files like

8.0
9
1 SQLCHAR 0 11 "" 1 au_id
       SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 40 "" 2
au_lname SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 20 "" 3
au_fname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 12 "" 4 phone
       SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 40 "" 5
address SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 20 "" 6 city
       SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "" 7 state
       SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 5 "" 8 zip
       SQL_Latin1_General_CP1_CI_AS
9 SQLBIT 0 1 "" 9
contract ""

Change it to

8.0
9
1 SQLCHAR 0 11 "\t" 1
au_id SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 40 "\t" 2
au_lname SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 20 "\t" 3
au_fname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 12 "\t" 4
phone SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 40 "\t" 5
address SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 20 "\t" 6
city SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
state SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 5 "\t" 8 zip
         SQL_Latin1_General_CP1_CI_AS
9 SQLBIT 0 1 "\t" 9
contract ""

as \t is for TAB.

Then, the job you create would have this steps ( you could check each step
by run them in the Query Analyzer)
step1: create a table 'authorsx' to hold the result set of the stored
procedure (au_all)
step2: insert into authorsx exec('au_all')
step3: exec xp_cmdshell 'BCP PUBS..AUTHORS out c:\authorsx_log.log -f
c:\authorsx.fmt -U sa -P MyPassWord'
step4: drop table authorsx

Then you will create a log file with TAB delimitation and later, if you
want to copy it to Excel file, I think you should use the DTS.

For more information, you could find this part in the Books Online: BCP
utility

Hope this helps.

Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Relevant Pages

  • Re: Create Job to dump SP results into a text file.
    ... > I am glad to hear that the osql works fine now. ... > 2) using BCP to save the content of this table to a tab limited log file ... ', type a name to generate the format file, I use ... create a table 'authorsx' to hold the result set of the stored ...
    (microsoft.public.sqlserver.programming)
  • Re: bcp problem
    ... You could try using a format file, where the column delimiters would be ... qualifiers be part of the delimiter and thus get stripped out. ... If you want to stick with bcp, then just try using the Bulk Insert Task, as ... > f text 16 nulls ...
    (microsoft.public.sqlserver.dts)
  • Re: BCP Troubles
    ... The SQL server we are tying to BCP into is SQL 2000. ... > way I got it to work was to run BCP and have it create a ForMaT file. ... So it sounds like the first time BCP is not executed at ...
    (comp.databases.ms-sqlserver)
  • BCP Troubles
    ... The SQL server we are tying to BCP into is SQL 2000. ... I compared the ForMat file I created by hand,to the ForMat file I ... compare to check it). ...
    (comp.databases.ms-sqlserver)
  • Re: BCP csv file only required Rows
    ... Since I use a format file to BCP only the Columns needed for BCP, ... fails the destination type column is Dateval type.So it fails. ...
    (microsoft.public.sqlserver.dts)
Loading