June 2, 2003 at 10:09 am
Can anyone tell me the best way to get DTS to output a query to a fixed length file?
I have a query that concatenates all the individual column values into one long column (query runs fine in Query Analyzer)and I keep getting an 'Invalid Column Size' error when trying to output to a text file with DTS. The rows returned from the query are < 8k bytes
TIA!!!
June 2, 2003 at 1:57 pm
William, it'd help to know a little more about the structure of what you're trying to export. I have several files that I have to create from data into flat FL files for export to my billing companies, so I may have some help for you. <br> <br>In my case, I have created views that return the line formatted up the way I want it, and I run the DTS packages using the views as the data sources.<br><br>Give me a little more to go on if you would, because this sounds a lot like the battles we faced until we figured out to use pre-formatted views.<br><br>Carpe carp (sieze the fish)
June 2, 2003 at 2:29 pm
Thanks for the reply.
The script that I have written requires creating temp table to be able to get the final rowset...so I can't use a view (to my knowledge).
Here is an example of the script I use. If you put it into DTS Export Data from Northwind you'll see the error I get.
USE NORTHWIND
SELECT * INTO #tmp_customers
FROM Customers
SELECT CAST(CustomerID AS CHAR(10))
+
CAST(CompanyName as CHAR(50))
+
CAST(SPACE(50) AS CHAR(50))
+
CAST(City AS CHAR(5))
AS FixedLengthRow
FROM #tmp_customers
June 2, 2003 at 3:11 pm
I guess I didn't understand the question then... if you're having the DTS package create the flat file, how are you getting the number of rows as part of the last row (or am I completely mistreading your posts?)?
June 3, 2003 at 7:38 am
I don't think I did a good job on the post.
Here's another way to tell me what I want to know.
Take the above script and export the results to a file using DTS (or some other automatable means).
TIA!
June 3, 2003 at 10:41 am
Okay, I think I got it now: you're running twice the work. You don't need to load the temp table first. Just create a view like this:
CREATE VIEW FormattedString
AS
SELECT
CAST(CustomerID AS CHAR(10)) +
CAST(CompanyName as CHAR(50)) +
SPACE(50) +
CAST(City AS CHAR(5))
FROM Customers
Then run your DTS package from the view.
That should solve your problem.
June 3, 2003 at 10:48 am
In my production script (which is considerably longer) I do HAVE to build a temp table to get the result set it need.
June 3, 2003 at 2:22 pm
I figured it out.
I created a stored procedure out of the script and have DTS executing the SP. Also I stopped concatenating the results together and it works brilliantly.
The reason I originally recieved errors is because I didn't set SET NOCOUNT OFF. Silly me.
June 3, 2003 at 2:24 pm
I hope I helped. Congrats on fixing it!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy