Book Recommendations...
-
I've been working with SQL Server 2000 for quite a while, but my actual work has been very limited. Usually, it has been Select queries or stored procedures for reports or backend data changes. My experience with DTS packages and jobs has been very, very limited. Now, I'm just starting to work in SQL Server 2005 and I'm getting into more and more programming.
I have a client request to create a flat text file. The client wants a header row (that includes a row count) and then the detail rows. I've been looking through some SQL 2005 books such as: SQL Server 2005 Complete Reference (which isn't as good as the Complete Reference for SQL), T-SQL Querying and Programming SQL Server 2005. I can't find any really good descriptions on how to create jobs in any of these books.
I'm not looking for someone to offer code -- I was trying to use a shell to create the file, but that's been turned off for security reasons -- I'd like someone to recommend any books that have been helpful.
Thanks!
-
Books Online doesn't have the info you want/need?
--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)
Intro to Tally Tables and Functions -
I've searched the help and it wasn't there.
-
Not sure what they call it in 2k5, but have you done a search on Bulk Copy or BCP in Books Online?
--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)
Intro to Tally Tables and Functions -
Sounds more like you want some sort of SSIS package. The header row with rowcount will be slightly tricky. You need this info first, so it's probably two queries you're joining to get the data.
BCP should work. The export wizard could work as well if you build a view or a union query to put the data together.
Jobs in SQL Server are typically automated tasks. They're not necessarily related to programming. A job gets run by the SQL Agent scheduler.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Actually, I hadn't searched for that because I didn't know what it was called. Cool. Thanks for the info!
-
Okay, I have another question...I've found a ton of information on bcp, but from what I've read, you can either use the bcp from the windows command prompt, which I don't want to do or you have to use the shell (see below example). Now, my DBAs have disabled the cmdShell and say I can't use it. Anyone know of any other way to do the same thing?
Select * into mytemptable from #HYRECV1 T
Exec master..xp_cmdShell 'bcp "Select * from MSQL_CAPRET.dbo.mytemptable" queryout "C:\MyFile.dat" -c'
Drop table mytemptable
go
Also, I've tried this:
bcp ReportingDB
.dbo.MyFile59683 out MYFILE.dat -T -c
And I received the following error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
I really appreciate all the help you have all ready given me, btw.
-
This was a major part of my last good SQL Server job. I was formatting data then spitting out a file for uploading to a mainframe. The first record was a summary, and the data records were totally different in layout.
The general framework was something like this. First, it required two tables. Both tables were exact copies of the file spec, including filler fields. They had defaults of spaces. Second, the DTS job produced two files. I found it easiest to build a record in the first table based on a query that summarized the data, the second file was just the output of the detail records. DTS (this was on 2000) put both files out as text files, then used the copy command to merge them. Poof! One file with a header record.
I built the header as a table record so that the default data required for that record would be an easily changed constant: update the query for a different year rather than updating a DTS package. Strictly personal preference.
I don't remember the exact syntax, but merging files with the copy command use a + between multiple file names, so it would be something like copy file.head + file.body file.out, obviously you'd have to play with the syntax. I used the /Y and /Z options. I used only two file names and appended the body into the header text file.
If you are doing this for mainframe processing, a lot of input programs require batch count records to appear every X number of records. On another job I had to send payroll records to a mainframe in their very funky format. Again, if you're dealing with a mainframe, make sure to default any filler fields and also any trailing blanks to make sure they're spaces, mainframes don't like binary zeros when they're expecting spaces and they'll start spitting core dumps all over the place.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I'm thinking that DTS is going to be your only way out if you don't want to or can't use a command prompt of xp_CmdShell.
If you had a really cool DBA, like mine, (s)he'd let you write and test using xp_CmdShell and then setup a job that you could execute through a proxy. That way, no individuals or roles used by "users" have the xp_CmdShell privs.
--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)
Intro to Tally Tables and Functions -
While we're on bcp, I thought that it would dynamically create output files (without a format file) going from sql to a text file. Am I wrong about that? If a format file is required in each case, it's pretty time-consuming if you have to send 50 or 60 tables to their respective text files. (Our support company required this in order to have an accurate test environment for testing a mod). They did not need the entire data base, just these tables, or I would have sent a copy.
Any other ideas you have for sending table data for quite a few tables would be very appreciated.
PS Using SQL 2005
-
Before I make a suggest let me please clarify something.
You state they want the count in the header row. Exactly what do you mean?
Do you mean the header woulkd look like this?
COLA COLB COLC CNT81543
1 2 2
2 2 3
3 8 10
Or Like this?
CNT81543 COLA COLB COLC
1 2 2
2 2 3
3 8 10
Or like this?
CNT81543
COLA COLB COLC
1 2 2
2 2 3
3 8 10
Or like this?
CNT
81543
COLA COLB COLC
1 2 2
2 2 3
3 8 10
To give you a proper answer I need to understand the expected results.
-
I wish I had a DBA like that!
My DBA locked down the system so hard, that when I tried to run a query in production -- one I had to run -- I had no access! But, HE DIDN'T TELL ANYONE he was taking away access! Isn't that just peachy? -
The header row would look like this:
1XXXXX2XXXXX3XXXX4 -- no column headers and all fixed width.
-
So where is the count represented in that?
-
Hi BentleyGadget,
I had to do something similiar recently. I am including a version of my stored procedure I used to resolve the problem. I have changed many of the variable and table names to protect the innocent
. The way I get the data out to a file is by calling the stored procedure from a DTS Package. So without further adieu here is my code:/*
05/03/2007 JJD - FixedWidthOutput
OUTPUT: Client required a fixed width output file with a header and trailer record included. A table
variable was used to create a fixed width table to hold the data as fixed width. The data is inserted
into the table in the following order header record, data, trailer record. A SortOrder column is added
to the fixed width table to ensure this order.
*/
CREATE PROCEDURE [dbo].[GetFixed WidthFile]
AS
DECLARE @4WkRollWavCode char(5)
, @QtrWavCode char(5)
, @YR char(4)
, @WkInYr varchar(2)
, @WkInQtr varchar(2)
, @CurrDate char(8) --Current Date represents date file is being sent
, @WklyFileDate char(8) --Weekly File Date represents last date of data being sent
, @RecordCount varchar(9) --Record Count of data being sent
, @Header char(70) --Header record that is required by client for file being sent
, @Trailer char(70) --Trailer record that is required by client for file being sent
--CREATE a table variable for the FIXED WIDTH Results for file
DECLARE @FixedLengthTable table
(ORGCode char(70) NOT NULL DEFAULT '',
QuotaCell char(5) NOT NULL DEFAULT '',
SiteName char(30) NOT NULL DEFAULT '',
SiteState char(20) NOT NULL DEFAULT '',
Attribute char(10) NOT NULL DEFAULT '',
Stat char(5) NOT NULL DEFAULT '',
Value_4Wk char(5) NOT NULL DEFAULT '',
Value_Qtr char(5) NOT NULL DEFAULT '',
SortOrder tinyint --only used to ensure order of Header, Data, and Trailer rows in final query
)
SET NOCOUNT ON
--GET Dates for Header and Trailer records
SET @CurrDate = CONVERT(char(8), GETDATE(), 112)
SELECT @WklyFileDate = CONVERT(char(8), MAX(Date_Cmp), 112) FROM Calendar WHERE WeekID = @WeekID
/*CREATE Header Record AND INSERT INTO @FixedLengthTable
Contains header indicator, file creation date(@CurrDate), data date(@WklyFileDate) in that order
FORMAT: HDYYYYMMDDYYYYMMDD
ex. HD2007050420070428
*/
SET @Header = 'HD' + @CurrDate + @WklyFileDate
INSERT INTO @FixedLengthTable (ORGCode, SortOrder) VALUES (@Header, 1)
--GET Data for the file
INSERT INTO @FixedLengthTable
(ORGCode, QuotaCell, SiteName, SiteState, Attribute, Stat, Value_4Wk, Value_Qtr, SortOrder)
SELECT w.ORGCode as ORGCode
, '' as QuotaCell
, '' as SiteName
, '' as SiteState
, w.ATTCode as Attribute
, w.SUMCode as STAT
, w.Value as Value_4Wk
, @WkInQtr as Value_Qtr --Don't display QTD value until there are 4 weeks of data
, 2 as SortOrder
FROM (SELECT * FROM SomeTable WHERE WAVCode = @4WkRollWavCode) w
INNER JOIN (SELECT * FROM SomeTable WHERE WAVCode = @QtrWavCode) q
ON w.ORGCode = q.ORGCode
WHERE w.ORGCode = 'ORGCode'
--Get Record Count for the data
SELECT @RecordCount = @@RowCount
/*CREATE Trailer Record AND INSERT INTO @FixedLengthTable
Contains trailer indicator, file creation date(@CurrDate), data date(@WklyFileDate)
, and record count(@RecordCount)
FORMAT: TRYYYYMMDDYYYYMMDD
ex. TR2007050420070428000000097
*/
SET @Trailer = 'TR' + @CurrDate + @WklyFileDate + RIGHT('000000000' + @RecordCount, 9)
INSERT INTO @FixedLengthTable (ORGCode, SortOrder) VALUES (@Trailer, 3)
SELECT ORGCode, QuotaCell, SiteName, SiteState, Attribute, Stat, Value_4Wk, Value_Qtr
FROM @FixedLengthTable
ORDER BY SortOrder
I hope this helps you. If not, I hope this spurs some conversation that will.
John
**Edited**: I just wanted to mention BentleyGadget that I did cheat a little bit because my first data column was 70 characters long so I was able to fit the header and trailer records into that column without a problem. A possible alternative would be to concatenate your columns into 1, unless of course someone else can think of a better way. I put this script together in a rush.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply