bcp uitility - SQL Server Forums

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)

bcp uitility

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-07 : 09:54:39

Fortunate writes "I want to export data from a table to a text file. Below is my code or script;

DECLARE @FileName varchar(50),
@FileName2 varchar(50),

@bcpCommand varchar(2000)
SET @FileName = REPLACE ('c:\ak\bvouchers_'+CONVERT(char(8),GETDATE(),1), '/','-')
SET @FileName2 = REPLACE ('-'+CONVERT(char(8),GETDATE(),8)+'.txt', ':','-')

print @FileName+@Filename2

SET @bcpCommand='bcp "select serial_no,access_no from bwyz_test.dbo.uga_voucher_numbers ORDER BY serial_no" queryout'
SET @bcpCommand=@bcpCommand +@FileName+@FileName2+ '-c -Pstjude'

print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand

I have executed this in the query analyser and below is the result;

c:\ak\bvouchers_08-31-01-15-14-07.txt
bcp "select serial_no,access_no from bwyz_test.dbo.uga_voucher_numbers ORDER BY serial_no" queryoutc:\ak\bvouchers_08-31-01-15-14-07.txt-c -Pstjude
output
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Password:
NULL

(2 row(s) affected)
I have tried this at the cmd prompt and I get the file *.tx but there no output when I use the query analyser. Can someone help me get round this problem. Apparently maybe my passwordis not being passed on because I prompted to enter my password when I run it via the cmd prompt."

prbsql
Starting Member

4 Posts

Posted - 2006-06-21 : 07:07:23

Please find the below code.
I am trying to write the data from the table to file using bcp.
Please check the syntax. When i execute the procedure, i am not getting any error or the files have not been created in the specific folder.
pl help

alter PROCEDURE table2file
AS
BEGIN

DECLARE @bcpCommand nvarchar(1000),
@p_Message nvarchar(500),
@p_RecdTime nvarchar(500),
@FileName1 nvarchar(300),
@FileName2 nvarchar(300),
@FullFileName nvarchar(600),
@PathName nvarchar(100),
@Err_File nvarchar(500),
@Path_ErrLog nvarchar(500),
@Path_ErrLog1 nvarchar(500),
@Path_ErrLog2 nvarchar(500),
@ServerName nvarchar(50),
@RowCount numeric(10),
@Err_File1 nvarchar(500),
@Err_File2 nvarchar(500)

SET @ServerName=@@SERVERNAME

Declare cur_msg CURSOR SCROLL
FOR SELECT Message_Type,Receive_Date FROM test ORDER BY Message_Type,Receive_Date asc

Open cur_msg
Fetch From cur_msg into @p_Message,@p_RecdTime

While @@Fetch_Status =0
BEGIN
SET @PathName='C:\temp\'
SET @FileName1=@p_Message
SET @FileName2=@p_Recdtime
SET @FullFileName=@p_Message+'_'+@p_Recdtime
SET @bcpCommand='bcp "SELECT MESSAGE FROM Test WHERE MESSAGE_TYPE=@p_Message AND RECEIVE_DATE=@p_Recdtime" queryout"'+ '-o '
SET @bcpCommand=@bcpCommand+' '+@PathName+@FullFileName+'" -U sa -P deploy'+'-S'+ @@servername

print @bcpcommand
EXEC master.dbo.xp_cmdshell @bcpCommand
Fetch From cur_msg into @p_Message,@p_RecdTime
END
CLOSE cur_msg
DEALLOCATE cur_msg

END

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources