CODEWITHSUNDEEP
sql-server
behi behi
behi behi

Reputation: 137

Why i get Error converting data type varchar to float error in bcp query out?

I'm new in sql server,write this bcp query for save select query result to csv file:

declare @Price as float
set @Price=1744

EXEC xp_cmdshell 'bcp "select [Telno],[Cycle],[Price] FROM [ClubEatc].[dbo].[CycleAnalysisTable] where CONVERT(float, [Price])>''CONVERT(float, @Price)''" queryout d:\F.txt -c -T'


But when i run that query,get this error:

Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Error converting data type varchar to float.


How can i solve that problem?thanks all.

Upvotes: 2

Views: 901

Answers (1)

gofr1
gofr1

Reputation: 15987

You got quotes, that is why your query ran as:

select [Telno],[Cycle],[Price] 
FROM [ClubEatc].[dbo].[CycleAnalysisTable] 
where CONVERT(float, [Price])>'CONVERT(float, @Price)'

And SQL Server can not CONVERT CONVERT(float, @Price) to float.

Try to run it like this:

declare @Price as float,
        @Query nvarchar(max),
        @sql nvarchar(max)

SELECT  @Price=1744

SELECT @Query = 'select [Telno],[Cycle],[Price] FROM [ClubEatc].[dbo].[CycleAnalysisTable] where CONVERT(float, [Price])>' +CAST(@Price as varchar(100))


SELECT @sql= 'EXEC xp_cmdshell ''bcp "'+@Query+'" queryout d:\F.txt -c -T'''

EXEC sp_executesql @sql

EDIT

One more way:

declare @Price as float,
        @bcp varchar(max)

SELECT @Price=1744

SELECT @bcp= 'bcp "select [Telno],[Cycle],[Price] FROM [ClubEatc].[dbo].[CycleAnalysisTable] where CONVERT(float, [Price])>' +CAST(@Price as nvarchar(100))+'" queryout d:\F.txt -c -T'

EXEC master..xp_cmdshell @bcp

Upvotes: 1

Related Questions