Re: Create Job to dump SP results into a text file.
From: moondaddy (moondaddy_at_nospam.com)
Date: 03/24/04
- Next message: John Gilson: "Re: Age as of 15th of Mth"
- Previous message: Daniele: "How to create table to Linked Server for an Excel file"
- In reply to: Baisong Wei[MSFT]: "Re: Create Job to dump SP results into a text file."
- Next in thread: keene: "Re:Create Job to dump SP results into a text file."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Mar 2004 11:25:31 -0600
Great thanks for all of the information!
--
moondaddy@nospam.com
"Baisong Wei[MSFT]" <v-baiwei@online.microsoft.com> wrote in message
news:VDGYs1UEEHA.3924@cpmsftngxa06.phx.gbl...
> 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.
>
- Next message: John Gilson: "Re: Age as of 15th of Mth"
- Previous message: Daniele: "How to create table to Linked Server for an Excel file"
- In reply to: Baisong Wei[MSFT]: "Re: Create Job to dump SP results into a text file."
- Next in thread: keene: "Re:Create Job to dump SP results into a text file."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
