DECLARE @over_time datetime
DECLARE @card_id intDECLARE @filePath VARCHAR(100)
declare @date VARCHAR(100)
--declare @sql varchar(1000)
set @over_time='2010-09-30'
set @card_id=1
set @date=replace(replace(replace(CONVERT(varchar, @over_time, 120 ),'-',''),' ',''),':','')
SET @filePath = 'd:\过期时间'+@date+'Card_id='+convert(VARCHAR,@card_id)+'.xls'
--set @sql='bcp "select card_id,card_code,card_pass,date_time,over_time,ispay from game.dbo.stock_data_bak where card_id='+@card_id+' and over_time='+@over_time+'"'
select @filePath
EXEC('
EXEC master..xp_cmdshell ''bcp "select card_id,card_code,card_pass,date_time,over_time,ispay from game.dbo.stock_data_bak where card_id='+@card_id+' and over_time='+@over_time+'" queryout ' + @filePath + ' -c -T''
')
提示错误
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]第 1 行: '30' 附近有语法错误。
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]未能准备语句。
NULL
DECLARE @card_id intDECLARE @filePath VARCHAR(100)
declare @date VARCHAR(100)
--declare @sql varchar(1000)
set @over_time='2010-09-30'
set @card_id=1
set @date=replace(replace(replace(CONVERT(varchar, @over_time, 120 ),'-',''),' ',''),':','')
SET @filePath = 'd:\过期时间'+@date+'Card_id='+convert(VARCHAR,@card_id)+'.xls'
--set @sql='bcp "select card_id,card_code,card_pass,date_time,over_time,ispay from game.dbo.stock_data_bak where card_id='+@card_id+' and over_time='+@over_time+'"'
select @filePath
EXEC('
EXEC master..xp_cmdshell ''bcp "select card_id,card_code,card_pass,date_time,over_time,ispay from game.dbo.stock_data_bak where card_id='+@card_id+' and over_time='+@over_time+'" queryout ' + @filePath + ' -c -T''
')
提示错误
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]第 1 行: '30' 附近有语法错误。
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]未能准备语句。
NULL
解决方案 »
- *************记录谁用了我的查询分析器***************
- 请教 安装Sql Server 2005 选择的组件都是做什么的啊?
- 【求助】Tag数据库设计--千万级的数据查询问题,建立了索引了还是慢,谢谢
- 跪求~~ 在线等~~~麻烦看看如何能够更快的执行Batch语句
- 好像这样不行,那如何让变量充当字段名呢?
- sql 多表和视图混用建的视图,如何提高执行效率
- 研究生题目,大家试试,帮帮忙
- ACCESS的MDB数据库存取与IB和MSSQL的速度有多大差异?
- 在不同服务器之间查询,我怎么查不出
- sqlserver插入数据
- 请教一个sql
- 我SQL2005连接不上,之前还连接上了的,帮忙解答下,谢谢!
DECLARE @card_id intDECLARE @filePath VARCHAR(100)
declare @date VARCHAR(100)
--declare @sql varchar(1000)
set @over_time='2010-09-30'
set @card_id=1
set @date=replace(replace(replace(CONVERT(varchar, @over_time, 120 ),'-',''),' ',''),':','')
SET @filePath = 'd:\过期时间'+@date+convert(VARCHAR,@card_id)+'.xls'
--set @sql='bcp "select card_id,card_code,card_pass,date_time,over_time,ispay from game.dbo.stock_data_bak where card_id='+@card_id+' and over_time='+@over_time+'"'
select @filePath
declare @cmd varchar(max)
set @cmd ='bcp "select card_id,card_code,card_pass,date_time,over_time,ispay from game.dbo.stock_data_bak where card_id='
+convert(varchar(20),@card_id)
+' and over_time='''
+convert(varchar(20),@over_time)
+'''" queryout '
+ @filePath + ' -c -T''
'
EXEC master..xp_cmdshell @cmd
DECLARE @card_id intDECLARE @filePath VARCHAR(100)
declare @date VARCHAR(100)
declare @date1 varchar(200)
set @over_time='2010-09-30 12:10:45.000'
set @card_id=2
set @date1=CONVERT(varchar, @over_time, 120 )
set @date=replace(replace(replace(CONVERT(varchar, @over_time, 120 ),'-',''),' ',''),':','')
SET @filePath = 'd:\过期时间'+@date+'卡类型为'+convert(VARCHAR,@card_id)+'.xls'select @filePath
select @date1EXEC('EXEC master..xp_cmdshell ''bcp "select card_id,card_code,card_pass,date_time,over_time,ispay from game.dbo.stock_data_bak where card_id='+@card_id+' and over_time='''''+@date1+'''''" queryout ' + @filePath + ' -c -T''')
这样写就好了。
感谢大家了。