Hi guys, hope you all are doing fine, guys I am having a trouble I am using the bcp utility to copy the data from one table to a text file but I just wanted to give two date parameters in that so that the data withing those date parameters should come and should be copied to the text file but I am not bale to get the syntax right. ia m using the below mentioned command
exec master..xp_cmdshell 'bcp "select * from Watch.dbo.Dgattendance where logindate between '+@dt1+' and '+@dt2 '" queryout "c:\test.txt" -c -q -U"username" -P"password"'
I have declared the @dt1 and @dt2 parameters.
Please help me out urgently.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks ram for the comments but I ran the command it is giving me an error. The command that I ran is below
declare @dt1 as datetime, @dt2 as datetime
Set @dt1 = '2004/08/01'
Set @dt2 = '2004/08/10'
declare @sqlquery varchar(8000)
select @sqlquery = 'bcp "select * from Watch.dbo.Dgattendance where logindate between '+@dt1+' and ' + @dt2 + ' " queryout "c:\test.txt" -c -q -U"sa" -P"heesa"'
exec master..xp_cmdshell @sqlquery
It gives me an error like
Server: Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.
Thanks for helping
Declare both of them as varchar..I guess that will work
declare @dt1 as varchar(30), @dt2 as varchar(30)
Set @dt1 = '2004/08/01'
Set @dt2 = '2004/08/10'
declare @sqlquery varchar(8000)
select @sqlquery = 'bcp "select * from Watch.dbo.Dgattendance where logindate between '+@dt1+' and ' + @dt2 + ' " queryout "c:\test.txt" -c -q -U"sa" -P"heesa"'
exec master..xp_cmdshell @sqlquery
Can you try the following..........to avoid any date formatting issues
declare @dt1 as varchar(30), @dt2 as varchar(30)
Set @dt1 = '2004/08/01'
Set @dt2 = '2004/08/10'
declare @sqlquery varchar(8000)
select @sqlquery = 'bcp "select * from Watch.dbo.Dgattendance where logindate between '+cast(varchar(30),@dt1,10
exec master..xp_cmdshell @sqlquery
Sorry..I gave you wrong syntax..
declare @dt1 as varchar(30), @dt2 as varchar(30)
Set @dt1 = '08/01/2004'
Set @dt2 = '08/10/2004'
declare @sqlquery varchar(8000)
select @sqlquery = 'bcp "select * from Watch.dbo.Dgattendance where logindate between '+@dt1+' and ' + @dt2 + ' " queryout "c:\test.txt" -c -q -U"sa" -P"heesa"'
exec master..xp_cmdshell @sqlquery
I think that ram has been close to the solution, but assumes that the field LOGINDATE is indeed stored as datetime. If it is NOT (but as varchar), then the solution will be different, please tell us.
declare @dt1 as datetime, @dt2 as datetime
Set @dt1 = '2004/08/01'
Set @dt2 = '2004/08/10'
declare @sqlquery varchar(8000)
set @sqlquery = 'bcp "select * from Watch.dbo.Dgattendance where logindate between CONVERT(datetime, '''+ CONVERT( varchar(20), @dt1 , 101 ) +''', 101) and CONVERT(datetime, ''' + CONVERT(varchar(20), @dt2 , 101 ) + ''', 101) " queryout "c:\test.txt" -c -q -U"sa" -P"heesa"'
EXEC ( @sqlquery )
This should work for ANY regional setting you have.
CHeers
see the link below..it helps you on this........
http://www.experts-exchang
Business Accounts
Answer for Membership
by: ram2098Posted on 2004-09-15 at 01:21:06ID: 12062303
It is always good to write it like this
declare @sqlquery varchar(8000)
select @sqlquery = 'bcp "select * from Watch.dbo.Dgattendance where logindate between '+@dt1+' and '+@dt2 ' + ' " queryout "c:\test.txt" -c -q -U"username" -P"password"'
exec master..xp_cmdshell @sqlquery