Hi
I posted a question a while back about passing dates through a BCP SQL statement and received the answer that they should look as follows
declare @.sql as varchar(1000)
select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'
exec master..xp_cmdshell @.sql
Now I need to do it differently and I have declared date variables and set the values and now i want to place the varaible names into the statement but i am receiving errors such as cannot convert character to datetime and once again i am looking for the correct way to type the bcp statement
I have the following example
Declare @.EndDate Datetime
Declare @.StartDate DateTime
Declare @.FilePath varchar (250)
Declare @.ServerName varchar (250)
Declare @.sql varchar(8000)
SET @.EndDate = '05/01/2007'
SET @.StartDate = '06/01/2007'
SET @.FilePath = 'C:\test.txt'
SET @.ServerName = 'SQLEXPRESSSERVERPATH'
select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor @.EndDate, @.StartDate " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'
exec master..xp_cmdshell @.sql
I have tried
select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor '' + @.EndDate+ '', '' + @.StartDate + ''" queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'
And many many other variations but am mystified as to the correct format.
Can anyone help?
Syvers
Try:
select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor ' + @.EndDate + ', ' + @.StartDate + ' " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'
exec master..xp_cmdshell @.sql
|||
Code Snippet
select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''' + convert(varchar(10), @.EndDate, 101) + ''', '''+ convert(varchar(10), @.StartDate, 101) + '''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'
|||Thanks Dale, my thinking was not on all cylinders this morning -had to rush out for a meeting.
|||Team work!
|||Thank you for your help, works great now.