Hi I'm trying to set-up a stored

[CliveW]

Hi

I'm trying to set-up a stored procedure that will run through a number of tables and export the data into a text file on the server. I believe that the easiest way for this is to use bcp. But have hit on a problem with my stored procedure and wondered if anyone can see the answer.

Thanks.
The error is
output ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Password:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@output'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

@RETURN_VALUE = N/A

sqlstate 37000 = Syntax error or access violation
As the other bit of code works okay it must be a syntax error but I can not see it.

When running in debug the variable output is declared.

The code is.
CREATE PROCEDURE dbo.sp_predictionout AS

Declare @directory char(50), @pkzipname char(50), @pkzipcmd char(50), @tfilename char(50), @bcpcommand char(50)
Declare @name char(8), @filename char(50), @nrow int

DECLARE cName CURSOR FOR
select name from IC_PREDICTION.dbo.sysobjects
where crdate>=CONVERT(char, getdate(), 112) and xtype = 'U' and name like '_K%'

SELECT @nrow = @@rowcount

OPEN cName

FETCH NEXT FROM cName INTO @NAME

declare @output Char (40)

set quoted_identifier off

WHILE @@FETCH_STATUS = 0



BEGIN

SET @FILENAME = 'IC_PREDICTION.dbo.'+@NAME
SET @OUTPUT = 'IC_PREDICTION.DBO.'+@NAME+'A'


EXEC('SELECT custno+urn+stuffermedia+surname+space(30)+firstname+
addr1+addr2+addr3+postalcode+space(7)+SUBSTRING(addr4,1,18)+sex+RTRIM(sdob)+"2400"+birthtown+RTRIM(atlas) as col1
INTO '+@output+' from '+@filename)


SET @Directory = 'Y:\NEWINNER\PREDICTIONS\uk\'
SET @tFilename = 'Y:\NEWINNER\PREDICTIONS\uk\P'+CONVERT(char, getdate(), 112)+@filename+'.txt'
SET @bcpCommand = 'bcp "SELECT * FROM @output " queryout "'
SET @bcpCommand = @bcpCommand + @output+'" -c'

exec master..xp_cmdshell @bcpCommand

FETCH NEXT FROM cName INTO @NAME
End

deallocate cname
GO

The following bit of code works okay on its own.

CREATE PROCEDURE DBO.OUTPREDIC AS

DECLARE @directory varchar(50),
@filename varchar(50),
@bcpCommand varchar(2000),
@command varchar(50)

select custno+urn+stuffermedia+surname+space(30)+firstname+
addr1+addr2+addr3+postalcode+space(7)+SUBSTRING(addr4,1,18)+sex+RTRIM(sdob)+'2400'+birthtown+RTRIM(atlas) as col1
INTO ic_prediction.dbo.bK030402a from ic_prediction.dbo.bk030402

SET @Filename = 'Y:\NEWINNER\PREDICTIONS\UK\P20030402\bk030402.txt'

SET @bcpCommand = 'bcp "SELECT * FROM ic_prediction.dbo.bk030402a " queryout "'
SET @bcpCommand = @bcpCommand + @Filename+'" -c'

exec master..xp_cmdshell @bcpCommand


Thanks in advance

Clive

 

[SQLSister]

HAve you considered using DTS?

 

[ClaireHCM]

since you declare @name as char,before cancat with any others you should use ltrim and rtrim to eliminate the empty space
1.
SET @FILENAME = 'IC_PREDICTION.dbo.'+rtrim(ltrim(@NAME))

2.
SET @OUTPUT = 'IC_PREDICTION.DBO.'+ltrim(rtrim(@NAME))+'A'

3.
SET @bcpCommand = 'bcp "seLECT * FROM '+rtrim(ltrim(@output))+' " queryout '

4.I dont think after these modification your problem can be solved.There should be many other fault should be modified.Maybe you should print or select the statement before executing it.

 

[CliveW]

I would look at using DTS but it will need to pick up a new datestamped file each time, not sure on how to set this within DTS.

i.e Today would be MK030611
Tomorrow would be MK030612

DTS will need to now which file to pick up and what to call it when sending out to a txt file.

 

[hot1]

I think you will have to write VB script into your DTS to format dates strings. I don't have sample code but there should be lots in SQL programming forum.