Geek asks and answers

How to pass parameter to bcp command in sql server

I want to send the result of the request to a file, so I tried using the bcp command. But it cannot pass any parameters. It shows an error.

EXEC xp_cmdshell 'bcp "SELECT * FROM CG.dbo.cdyy where EndTime between     DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,'+@date+'),0)) and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,1,'+@date+')+1,0))  " queryout "D:\cdr_cg.txt" -T -c -t,'
+5
source share
2 answers

Place your options in front of the call wizard. xp_cmdshell

DECLARE @date varchar(10) = '20130311',
        @bcp varchar(8000)

SELECT @bcp = 'bcp "SELECT * FROM CG.dbo.cdyy WHERE EndTime between DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,''' + @date + '''),0)) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,1,'''  + @date + ''')+1,0))  " queryout "D:\cdr_cg.txt" -T -c -t,'

EXEC master..xp_cmdshell @bcp
+4
source

I use the following query, outputting as follows, and the file was not found at the specified location.

Query:   exec xp_cmdshell 'bcp " Mimsadaptor, 10 * logdate" queryout "D:\cdr_cg.txt" -T -c -t,'   :       ...   SQLState = 01000, NativeError = 5701    = [Microsoft] [ SQL Server ODBC] [SQL Server] "MIMSAdaptor".       10 .    (): 4096    (.): 16   NULL

0

More articles:


All Articles