Delimiting a sql query 3

[sgd3476]

I need to send data to client in Pipe or comma delimited format. File is too large to open in Excel or wordpad. How can I insert pipes or commas into my query?

Thanks,
Steve

 

[SQLSister]

Suggest you l;ook at Data Transformation Services.

 

[mikeyb540]

Did you try DTS?

You set the delimiter there the destanation file format.

Mike

 

[sgd3476]

I don't think I gave enough info here's my query:

select acct_id, Client_name, Street_add from acct_id where acct_typ = 4

results are

12345 jane smith 121 smith st

I need it to look like this:

12345,jane smith, 121 smith st
or
12345|jane smith|121 smith st

 

[BuGlen]

Hi Steve,

Have you looked at using a DTS package for this? You can create a package with three elements, (Source, Destination, and Transformation Task), that will export data based on views, tables, or an SQL statement.

You can then schedule the package to run at regular intervals if you like.

Hope this helps.

Glen Appleton

VB.Net student.

 

[mikeyb540]

Or if you dont like DTS you can try the BCP solution:


DECLARE @aServerName varchar(50),
@aLoginName varchar(20),
@aPassword varchar(20),
@aQuery varchar(1000),
@aFileName varchar(200),
@aFileDelimiter char(1)

SELECT @aServerName =
@aLoginName =
@aPassword =

SELECT @aQuery = 'select * from pubs.dbo.authors',
@aFileName = 'c:\temp\query_output.txt',
@aFileDelimiter = '|'

Declare @aBcpCommand varchar(1000),
@iRes int

SELECT @aBcpCommand = 'bcp "' + @aQuery + '" queryout "'
SELECT @aBcpCommand = @aBcpCommand + @aFileName + '" -U ' + @aLoginName + ' -P ' + @aPassword + ' -c -t "' + @aFileDelimiter + '" -S ' + @aServerName

EXEC @iRes = master..xp_cmdshell @aBcpCommand
select @iRes

 

[sgd3476]

Thank you all for your responses. I like the idea of creating a dts package, mikeyb540, I need a little more help please. I've started creating the package, but I can't figure out how to put in the pipe or comma delimiters. Can you please help.

Thanks,
Steve

 

[SQLBill]

Or if you are running the query in Query Analyzer (manually), you can select Tools > Options go to Results tab and set where the results go to and in what format.

-SQLBill

 

[mikeyb540]

Steve,

If you look at he syntax of
SELECT @aQuery = 'select * from pubs.dbo.authors',
@aFileName = 'c:\temp\query_output.txt',
@aFileDelimiter = '|'

there is the pipe or if you want the comma just change it.

also
SELECT @aServerName = "SERVERNAME",
@aLoginName = "SQL_LOGIN",
@aPassword = "SQL_PASSWORD"

Let me know if you need more help.

 

[BuGlen]

Hi Steve,

If you want to create the DTS package using Enterprise Manager and a DTS wizard, do the following steps:

1. In the tree view, expand your server, expand Data Transformation Services, and click Local Packages
2. In the toolbar, click the "Run a Wizard" icon (looks like a magic wand)
3. Expand Data Transformation Services, select DTS Export Wizard, and click OK
4. Click Next on the opening screen
5. Select your data source (Microsoft OLE DB Provider for SQL Server), your server, and your database, then click Next
6. Select your destination (Text File), and enter a file name (full path), then click Next
7. Leave default of "Copy Tables and View from the source database", click Next
8. Select the parameters for the output file, click Next
9. Uncheck "Run Immediately", and check "Save DTS Package" with the default setting of SQL Server, click Next
10. Give the package a useful name, click Next
11. Click finished.
12. Finally, right click the new package and select "Execute Package" to test.

The wizard really does make this type of operation easy.

Hope this helps.

Glen Appleton

VB.Net student.

 

[sgd3476]

AWESOME..HELP!!!

I have a successful DTS Package and a great work around changing the settings for the file under tools.
Thanks all so much!

Steve