exporting using xp_cmdshell error

Question

Post reply

exporting using xp_cmdshell error

Ghanta

Hall of Fame

Points: 3999
More actions
July 19, 2010 at 9:37 am

#226969

Hi Guys,
I have a table with all varchar fields and one of the fields acct_nbr has the account number. Based on that field, I need to created distinct file... I used the code below, but get error which is provided below... not sure what is the issue? The number which is an issue is one of the account number... thanks for help guys.
---select * From dbo.Bank_Data_Source
DECLARE @FileName varchar(500),
@bcpCommand varchar(2000),
@ACCT_NBR varchar(255),
@i int
DECLARE @keepDifferentAccts table (ACCT_NBR varchar(255), IsDone char(1))
insert @keepDifferentAccts select ACCT_NBR, 0 from Bank_Data_Source group by ACCT_NBR
--select * from @keepDifferentAccts
set @i=0
while @i <= (select count(*) from @keepDifferentAccts where IsDone = '0')
begin
select top 1 @ACCT_NBR= ACCT_NBR from @keepDifferentAccts where IsDone=0
SET @FileName = REPLACE('e:\\outfile\Acct_'+@ACCT_NBR+'_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM Dev_Adhoc.dbo.Bank_Data_Source where ACCT_NBR='+@ACCT_NBR+'" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
update @keepDifferentAccts set IsDone='1' where ACCT_NBR= @ACCT_NBR
set @i=@i+1
end
-------------------------
I used the above code to create export-files based on account number... it did create some files; however, there was this error:
NULL
Starting copy...
SQLState = 22003, NativeError = 248
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The conversion of the varchar value '7986011679' overflowed an int column.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to resolve column level collations
NULL
BCP copy out failed
NULL
All the fields in dbo.Bank_Data_Source are VARCHAR... I am not sure about -T -c... is that the issue here?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

If I am not mistaken the BCP is trying to store the value 7986011679 as an INT. The max value for an int is 2147483647.

-Roy

Ghanta Hall of Fame Points: 3999 More actions · Answer 2

But I am using varchar for every field... does the bcp try to export the data in int format if they numbers?

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 3

I suggest creating a format file. I don't know if what you're trying to do "should" work without it, but in the past, I have always used format files to handle the data conversion. More information on format files can be found at http://msdn.microsoft.com/en-us/library/ms191516.aspx

HTH

Cindy

Ghanta Hall of Fame Points: 3999 More actions · Answer 4

All the data in tables are in varchar... seems like export is trying to change the varchar to int and the number is out of bound for int datatype... anyone had similar experience?

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 5

If 7986011679 is an account number, then I think this may be your problem. Your input query is going end up being

SELECT * FROM Dev_Adhoc.dbo.Bank_Data_Source where ACCT_NBR=7986011679

and without apostrophes there will be a cast to INT for what looks like an int in that case. Try to change your script to add apostrophes around the acct_nmbr field in the bcp query and see if that stops it. When weird casts seem to start happening you have to make sure that all possible sources of casts are removed from your code and this is one of them.

Ghanta Hall of Fame Points: 3999 More actions · Answer 6

jeff.mason (7/19/2010)
If 7986011679 is an account number, then I think this may be your problem. Your input query is going end up being
SELECT * FROM Dev_Adhoc.dbo.Bank_Data_Source where ACCT_NBR=7986011679
and without apostrophes there will be a cast to INT for what looks like an int in that case. Try to change your script to add apostrophes around the acct_nmbr field in the bcp query and see if that stops it. When weird casts seem to start happening you have to make sure that all possible sources of casts are removed from your code and this is one of them.

Thanks Jeff for that suggestion. I was missing quotes around @acct_nbr and after using that I was able to export most of the files EXCEPT for couple I got errors like:

SQLState = 37000, NativeError = 102

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '5712326718'.

SQLState = 37000, NativeError = 8180

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

NULL

Ghanta Hall of Fame Points: 3999 More actions · Answer 7

Ok this is working fine now... the main issue was the WHILE loop... I constructed it wrong and it did not loop enough times to export all the files. THanks guys for help.