Scripting results to file using custom delim

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

Transact-SQL (2000)

Scripting results to file using custom delim

Author

Topic

lperrine
Starting Member

2 Posts

Posted - 2004-04-13 : 14:49:32

I would like to run a query that will send the results to a file using a custom delimiter | . The only way I am able to do it so far is changing the tools >> options >> results tab in Query Analyzer. I'd actually like to schedule this job however. Does anyone know a serect that would allow me to do this?

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-13 : 15:12:26

bcp and a format file?

Or you can code the output to indent the values....


USE Northwind
GO

 SELECT      ISNULL(CONVERT(varchar(25),OrderID),'')
	+'|'+ISNULL(CustomerID,'')
	+'|'+ISNULL(CONVERT(varchar(25),EmployeeID),'')
	+'|'+ISNULL(CONVERT(varchar(25),OrderDate,120),'')
	+'|'+ISNULL(CONVERT(varchar(25),RequiredDate,120),'')
	+'|'+ISNULL(CONVERT(varchar(25),ShippedDate,120),'')
	+'|'+ISNULL(CONVERT(varchar(25),ShipVia),'')
	+'|'+ISNULL(CONVERT(varchar(25),Freight),'')
	+'|'+ISNULL(ShipName,'')
	+'|'+ISNULL(ShipAddress,'')
	+'|'+ISNULL(ShipCity,'')
	+'|'+ISNULL(ShipRegion,'')
	+'|'+ISNULL(ShipPostalCode,'')
	+'|'+ISNULL(ShipCountry,'')
   FROM Orders

Brett

8-)

lperrine
Starting Member

2 Posts

Posted - 2004-04-13 : 15:25:30

Thanks. I shall try this clever suggestion. - Linda

quote:

Originally posted by X002548

bcp and a format file?

Or you can code the output to indent the values....


USE Northwind
GO

 SELECT      ISNULL(CONVERT(varchar(25),OrderID),'')
	+'|'+ISNULL(CustomerID,'')
	+'|'+ISNULL(CONVERT(varchar(25),EmployeeID),'')
	+'|'+ISNULL(CONVERT(varchar(25),OrderDate,120),'')
	+'|'+ISNULL(CONVERT(varchar(25),RequiredDate,120),'')
	+'|'+ISNULL(CONVERT(varchar(25),ShippedDate,120),'')
	+'|'+ISNULL(CONVERT(varchar(25),ShipVia),'')
	+'|'+ISNULL(CONVERT(varchar(25),Freight),'')
	+'|'+ISNULL(ShipName,'')
	+'|'+ISNULL(ShipAddress,'')
	+'|'+ISNULL(ShipCity,'')
	+'|'+ISNULL(ShipRegion,'')
	+'|'+ISNULL(ShipPostalCode,'')
	+'|'+ISNULL(ShipCountry,'')
   FROM Orders

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources