Automated SQL to Flat File Export

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

SQL Server Development (2000)

Automated SQL to Flat File Export

Author

Topic

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 10:52:27

Hello again ladies and gents,

I am needing to export a query from my SQL database to a flat file automatically every night at a designated time.

Can anyone give me a step by step to set this up?

Thanks a ton!

Ken

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 10:57:34

Oh and if i cant get it to export as a Flat then other formats might work as well (XLS,XML)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:02:38

use bcp with query out option

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/bcp-export-data-to-text-file.aspx

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 11:12:43

TY, but how can i set it up to export every day at a certain time? (please excuse me as I am very much a novice with SQL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:16:36

quote:
Originally posted by KenP

TY, but how can i set it up to export every day at a certain time? (please excuse me as I am very much a novice with SQL)

set up a sql job in sql server agent with step as the above statement and schedule it to execute periodically. see below for details

http://doc.ddart.net/mssql/sql70/automaem_5.htm

http://doc.ddart.net/mssql/sql70/automaem_15.htm

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 11:20:27

Thank you! I will let you know if i have any troubles!

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 11:38:43

ok sorry...

how do I put my select statement into the stored proc without messing it up?

SELECT Product.product_number, Product.product_name, Product.product_price, Product.product_saleprice, Product.product_onsale, Product.product_shipping,
Product.product_weight, Product.product_shipping_type, Product.product_shipping_cost
FROM Product INNER JOIN
Products_Categories ON Product.product_id = Products_Categories.product_id INNER JOIN
Categories ON Products_Categories.cat_id = Categories.cat_id
WHERE (Categories.cat_site = 1) AND (Product.product_deleted = 0)

into:

CREATE Procedure BCP_Text_File
(
@table varchar(100),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
Declare @str varchar(1000)
set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''
Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 14:17:07

anyone?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 14:30:47

try modify your SP into something like:


CREATE Procedure BCP_MY_Text_File
(
@FileName varchar(100)
)
as
declare @sqlstr varchar(1000)
Declare @str varchar(8000)

set @sqlstr = 'SELECT Product.product_number, Product.product_name, Product.product_price, Product.product_saleprice, Product.product_onsale, Product.product_shipping,
Product.product_weight, Product.product_shipping_type, Product.product_shipping_cost
FROM Product INNER JOIN
Products_Categories ON Product.product_id = Products_Categories.product_id INNER JOIN
Categories ON Products_Categories.cat_id = Categories.cat_id
WHERE (Categories.cat_site = 1) AND (Product.product_deleted = 0)'

set @str='Exec Master..xp_Cmdshell ''bcp "'+@sqlstr+'" queryout "'+@FileName+'" -c'''
Exec(@str)

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 15:41:10

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL

is what i get when i run the SP

BCP_MY_Text_File 'C:\testfile.txt'

anyone else have any help?

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 17:28:30

Super Sad Face... :(

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 17:33:08

You'll have to turn this thing to 1 line:


set @sqlstr = 'SELECT Product.product_number, Product.product_name, Product.product_price, Product.product_saleprice, Product.product_onsale, Product.product_shipping,Product.product_weight, Product.product_shipping_type, Product.product_shipping_cost FROM dbname..Product INNER JOIN dbname..Products_Categories ON Product.product_id = Products_Categories.product_id INNER JOIN dbname..Categories ON Products_Categories.cat_id = Categories.cat_id WHERE (Categories.cat_site = 1) AND (Product.product_deleted = 0)'

Also replace "dbname.." with your databasename

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 17:46:58

My database name is MD1

Password:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MD1.Product'.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MD1.Products_Categories'.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MD1.Categories'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

set @sqlstr = 'SELECT Product.product_number, Product.product_name, Product.product_price, Product.product_saleprice, Product.product_onsale, Product.product_shipping,Product.product_weight, Product.product_shipping_type, Product.product_shipping_cost FROM MD1.Product INNER JOIN MD1.Products_Categories ON Product.product_id = Products_Categories.product_id INNER JOIN MD1.Categories ON Products_Categories.cat_id = Categories.cat_id WHERE (Categories.cat_site = 1) AND (Product.product_deleted = 0)'

KenP
Starting Member

16 Posts

Posted - 2008-10-27 : 17:48:55

its odd because the query works fine when I just run it as a normal query.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 18:23:59

You'll need double ".."

MD1..Product, MD1..Products_Categories, MD1..Categories

KenP
Starting Member

16 Posts

Posted - 2008-10-28 : 09:44:56

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure BCP_MY_Text_File
(
@FileName varchar(100)
)
as
declare @sqlstr varchar(1000)
Declare @str varchar(8000)

set @sqlstr = 'SELECT Product.product_number, Product.product_name, Product.product_price, Product.product_saleprice, Product.product_onsale, Product.product_shipping,Product.product_weight, Product.product_shipping_type, Product.product_shipping_cost FROM MD1..Product INNER JOIN MD1..Products_Categories ON Product.product_id = Products_Categories.product_id INNER JOIN MD1..Categories ON Products_Categories.cat_id = Categories.cat_id WHERE (Categories.cat_site = 1) AND (Product.product_deleted = 0)'

set @str='Exec Master..xp_Cmdshell ''bcp "'+@sqlstr+'" queryout "'+@FileName+'" -c'''
Exec(@str)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I then run:
BCP_MY_Text_File 'C:\testfile.txt'

and i get:

Password:
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Categories' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Product' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Products_Categories' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Products_Categories' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 107
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'Categories' does not match with a table name or alias name used in the query.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

So i change it to:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure BCP_MY_Text_File
(
@FileName varchar(100)
)
as
declare @sqlstr varchar(1000)
Declare @str varchar(8000)

set @sqlstr = 'SELECT Product..product_number, Product..product_name, Product..product_price, Product..product_saleprice, Product..product_onsale, Product..product_shipping, Product..product_weight, Product..product_shipping_type, Product..product_shipping_cost FROM MD1..Product INNER JOIN MD1..Products_Categories ON Product..product_id = Products_Categories..product_id INNER JOIN MD1..Categories ON Products_Categories..cat_id = Categories..cat_id WHERE (Categories..cat_site = 1) AND (Product..product_deleted = 0)'

set @str='Exec Master..xp_Cmdshell ''bcp "'+@sqlstr+'" queryout "'+@FileName+'" -c'''
Exec(@str)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

and I got:

Password:
SQLState = 37000, NativeError = 1004
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column prefix 'Product.': No table name specified
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

I am so lost as to whats going on.. Please help.. i feel like I am so close.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 10:18:56

I told you to replace the DBname with DBNAME.. not the table name

Give your table Product an alias like "P", category with "C" etc...


set @sqlstr = 'SELECT p.product_number, p.product_name, p.product_price, p.product_saleprice, p.product_onsale, p.product_shipping,p.product_weight, p.product_shipping_type, p.product_shipping_cost FROM MD1..Product P INNER JOIN MD1..Products_Categories pc ON p.product_id = pc.product_id  INNER JOIN MD1..Categories c ON pc.cat_id = c.cat_id WHERE (c.cat_site = 1) AND (p.product_deleted = 0)'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:20:47

try like this


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER Procedure BCP_MY_Text_File
(
@FileName varchar(100)
)
as
declare @sqlstr varchar(1000)
Declare @str varchar(8000)

set @sqlstr = 'SELECT p.product_number, p.product_name, p.product_price, p.product_saleprice, p.product_onsale, p.product_shipping, p.product_weight, p.product_shipping_type, p.product_shipping_cost FROM MD1..Product p INNER JOIN MD1..Products_Categories pc ON p.product_id = pc.product_id INNER JOIN MD1..Categories c ON pc.cat_id = c.cat_id WHERE (c.cat_site = 1) AND (p.product_deleted = 0)'

set @str='Exec Master..xp_Cmdshell ''bcp "'+@sqlstr+'" queryout "'+@FileName+'" -c'''
Exec(@str)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO