SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 copy data to text file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

purisqlserver
Yak Posting Veteran

India
73 Posts

Posted - 06/13/2001 :  09:23:24  Show Profile  Reply with Quote
sir,
How do we copy selected data from a table to text file using stored procedures.It should be using SP,since it has to be placed as job in sql server.:)
thank you

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 06/13/2001 :  09:30:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can create a DTS package to do it, and then create a job that executes the DTS package. It's pretty standard and really the easiest way.

Another method is to create an SP that calls xp_cmdshell to run the bcp utility. Books Online has details on xp_cmdshell and bcp. I can't really recommend this option, as it is much more tricky than DTS (check this link to see what I mean)

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=4185&FORUM_ID=5&CAT_ID=3&Topic_Title=BCP+problem&Forum_Title=Developer



Edited by - robvolk on 06/13/2001 09:30:26
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 06/13/2001 :  09:32:40  Show Profile  Visit Merkin's Homepage  Reply with Quote
Or run the DTS package in your SP with this method

http://www.sqldts.com/main.asp?nav=1,6,210,0



Damian
Go to Top of Page

alexkreyn
Starting Member

USA
9 Posts

Posted - 06/27/2007 :  10:36:23  Show Profile  Reply with Quote
quote:
Originally posted by purisqlserver

sir,
How do we copy selected data from a table to text file using stored procedures.It should be using SP,since it has to be placed as job in sql server.:)
thank you





CREATE proc [dbo].[spExportData]
(
@dbName varchar(100) = 'master',
@sql varchar(5000) = '',
@fullFileName varchar(100) = '',
@ShowHeader bit =1,
@UniqueTempTblName varchar(100)
)
as
if LTRIM(RTRIM(@sql)) = '' or LTRIM(RTRIM(@fullFileName)) = '' or LTRIM(RTRIM(@UniqueTempTblName))=''
begin
return -1
end
SET @UniqueTempTblName=LTRIM(RTRIM(@UniqueTempTblName))
DECLARE @TempTable1 varchar(100)
DECLARE @TempTable2 varchar(100)
DECLARE @TempSQL varchar(8000)
SET @TempTable1 = '##'+ @UniqueTempTblName +'_1'
SET @TempTable2 = '##'+ @UniqueTempTblName +'_2'

-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id(@TempTable1) is not null
SET @TempSQL='drop table '+ @TempTable1
EXEC (@TempSQL)
if object_id(@TempTable2) is not null
SET @TempSQL='drop table '+ @TempTable2
EXEC (@TempSQL)
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000)
select @tempSQL = left(@sql, charindex('from', @sql)-1) +' into ' + @TempTable1 + ' ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
select -1 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = @TempTable1

-- execute select query to insert data and column names into new temp table
IF @ShowHeader = 1
BEGIN
SET @sql = 'select ' + @columnNames + ' into '+ @TempTable2 + ' from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from '+@TempTable1+ ' union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
END
ELSE
BEGIN
SET @sql = 'select ' + @columnNames + ' into '+ @TempTable2 + ' from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from '+@TempTable1+ ') t order by [temp##SortID]'
END
exec (@sql)

-- build full BCP query
set @sql = 'bcp "select * from '+@TempTable2+'" queryout '+ @fullFileName +' -c -T'
-- execute BCP( turn on xp_cmdshell first)

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE

Exec master..xp_cmdshell @sql

EXECUTE sp_configure 'xp_cmdshell', '0'
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

--turn off xp_cmdshell
if @@error > 0
begin
return -1 -- failure
end
SET @TempSQL ='drop table ' + @TempTable1
EXEC (@TempSQL)
SET @TempSQL ='drop table ' + @TempTable2
EXEC (@TempSQL)
return 0


RUN :

declare @sql varchar(6800), @dbName varchar(100), @fullFileName varchar(100)
select @dbName = 'ian_www', @sql = 'select top 10000 * from ian_www.dbo.v_form_data', @fullFileName = 'c:\yys1.txt'
exec master..spExportData @dbName, @sql, @fullFileName ,1,'x'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000