0

I am a little stuck... I am trying to take the output from a query and break it into numerous files based on a single criteria. I am getting an error of converting a varchar type to int and I cannot figure out why. Working in SQL Server 2008...

DECLARE @LOOP AS INT;
DECLARE @SQL AS VARCHAR(MAX);
DECLARE @BCP AS VARCHAR(MAX);
DECLARE @COUNTER AS INT;
DECLARE @FILENAME AS VARCHAR(MAX);

SET @COUNTER='1'

SELECT @LOOP = COUNT(DISTINCT LIST_ID) FROM DATA_TABLE
WHERE STATUS='2' AND LIST_ID IS NOT NULL ;

SET @SQL=(SELECT CUSTOMER_NO FROM CUSTOMER A, DATA_TABLE B
WHERE A.CUSTOMER_ID=B.CUSTOMER_ID AND A.STATUS='2' AND LIST_ID='+@LOOP+');

SET @FILENAME='QUERYOUT C:\Projects\FILE_"'+@LOOP+'.TXT'

WHILE @COUNTER<=@LOOP
BEGIN
SELECT 
@BCP='BCP "'+@SQL+'+'+@FILENAME+''
SET @COUNTER=@COUNTER+1
END
GO

The error I am getting is:

Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value '+@LOOP+' to data type int.

I am trying to use the LOOP value to let me know the contents of each file. For example, LOOP='1' would mean the file contains the customer records associate with LIST_ID='1'

Thoughts on the error?

user3390448
  • 1
  • 1
  • 2

2 Answers2

0

I'm not sure I understand exactly what you need but if you want to issue the BCP command for every LIST_ID you need to loop though them and execute for each one.

This may not be what you need but rather than wait until I am home from work I will post it now. 

DECLARE @FILENAME AS VARCHAR(MAX);
DECLARE @LISTID INT
DECLARE @LOOP AS INT;
DECLARE @BCP AS VARCHAR(MAX);
DECLARE @SQL AS VARCHAR(MAX);

DECLARE cur CURSOR FOR SELECT DISTINCT LIST_ID FROM DATA_TABLE WHERE STATUS='2' AND LIST_ID IS NOT NULL
OPEN cur

FETCH NEXT FROM cur INTO @LISTID

WHILE @@FETCH_STATUS = 0 BEGIN
    SET @FILENAME='QUERYOUT C:\Projects\FILE_'+Cast(@LISTID AS Varchar)+'.TXT -c -t -T'
    SET @SQL='(SELECT CUSTOMER_NO FROM CUSTOMER A, DATA_TABLE B WHERE A.CUSTOMER_ID=B.CUSTOMER_ID AND A.STATUS=''2'' AND LIST_ID='+@LISTID+')';
    SELECT @BCP='BCP '+@SQL+' '+@FILENAME+''
    EXEC xp_cmdshell @BCP
    FETCH NEXT FROM cur INTO @LISTID
END

CLOSE cur    
DEALLOCATE cur
Fred
  • 5,663
  • 4
  • 45
  • 74
0

1.change varchar(max) to varchar(8000)
2.Add DBNAME.SCHEMA for all the tables, because by default it will point to the Master db.
Added double quotes(") below
3. @FILENAME='QUERYOUT "C:\Projects\FILE_'+Cast(@LISTID AS Varchar)+'.TXT" -c -t -T'
4. '("SELECT CUSTOMER_NO FROM CUSTOMER A, DATA_TABLE B WHERE A.CUSTOMER_ID=B.CUSTOMER_ID AND A.STATUS=''2'' AND LIST_ID='+@LISTID+'")';

Laxman Rao G
  • 1
  • 1