The best way to create a fixed length text file? osql?bcp? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Import/Export (DTS) and Replication (2000)

The best way to create a fixed length text file? osql?bcp?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-15 : 07:54:44

Simon writes "Hi,

I have a SQL Job which has the following statement in it:

use ARSystem
exec sp_Warehouse_Order_Job

which executes a stored procedure which, amongst other things, calls another stored procedure using the osql utility to output the results to a fixed length text file.

SELECT @command = 'osql -U Username -P Password -S server -d database -w 8000 -Q "exec sp_Warehouse_Create_Order_File" -o "' + @MainDir + @Filename + '"'

EXEC Master..xp_cmdshell @Command

This second stored procedure performs a query and outputs the results using the print command within a cursor. i.e.

Print '02' + 'D' + @Order_ref_Suffixed + isnull(@Line_Carriage_Type, space(15)) + ' 1' + isnull(str(@Line_Carriage_Cost * 100, 7), space(7)) + isnull(str(@Line_Carriage_Vat_Amount * 100, 7), space(7)) + space(1)

The file line lengths need to be over 255 chars(which i believe osql wraps lines greater than this)
I've tried using the bcp utility but don't seem to be able to get it to work from within the stored procedure called within the SQL job.

SELECT @command = 'bcp "exec sp_Warehouse_Create_Order_File" queryout "' + @MainDir + @Filename + '" -S server -U username -P password -c'

EXEC Master..xp_cmdshell @Command

It does seem to work if called directly in a step in the job itself. I'm not able to run it this way though as the call to the bcp utility needs to be integrated with the rest of the code in the stored procedure, which is greater in size than will fit into a step in the sql job.

Any ideas??

Many Thanks.

Simon"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-15 : 08:05:54

Use bcp with a format file. The easiest way to create one is to run your posted bcp command without the -c flag. For each column you will be prompted for the data type, prefix length, size and column terminator character(s). Make sure the datatype is ALWAYS char, prefix length is ALWAYS zero, and do NOT specify a column terminator character. You'll be prompted at the end to save that format file. Once you do, you can then run bcp with the -f switch and include the name of the format file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources