| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2011-02-09 : 11:35:13
|
Greetings all,I am not quite sure what I am doing wrong here.I am attempting to export 3 months worth of of data from sql server into a pipe-delimited txt file with quotes around the fields and values and I am running into Incorrect syntax near 'queryout' error message.The 3 months data includes current month. In other words, from today - 3 months back.What am I doing wrong anyone?Thanks alot in advancebcp 'select [NME1],[SPOUSE], [LIC NBR], [BK NBR], [PGE NBR], [DTE MARG] from [PCS60417_MARG_XREF] WHERE CONVERT(Char,DateAdd(mm,-3,getdate()),101) = CONVERT(Char,getdate(),101)' queryout c:\filename.out -Sdcrt3 -T -t"|", -c |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 11:48:16
|
well your Select Query and output file path should be in double qoutes as far i remember about BCP. But i dont know about the .out format file  and the type of authentication you used in there ... Following is an example as how you can extract data from a table to excel file using windows authentication. bcp "Exec select col1,col2,col3,...,ColN from tableName" queryout "D:\test9.xls" -c -T |
 |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-02-09 : 11:54:57
|
| Thanks Mik_2008 for your response.You can use single quotes as well. Using double quotes creates an error that that... it is too long and the maximum length is 128.So, using double quotes works. The error is happening just before queryout. |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-02-09 : 11:57:32
|
| Also, if I use your example just for testing, I get Incorrect syntax near "D:\test9.xls" |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 11:57:35
|
quote:
Originally posted by simflex
So, using double quotes works.
Pardon i dont understand do you Mean the issue has been fixed or still there? |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-02-09 : 11:59:22
|
| Sorry for the confusion.What I meant was that you can use single quotes as well as double quotes. The issue is not fixed. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 12:07:18
|
quote:
Originally posted by simflex
Also, if I use your example just for testing, I get Incorrect syntax near "D:\test9.xls"
Apologise for a mistake in the previous code ...  Well are you running the commands through SSMS or command prompt? Also for test purpose run the below command via SSMS hope it should not give an error... Declare @generatingExcelFile varchar(8000)SET @generatingExcelFile='bcp "select 1,1,1" queryout "D:\test1.xls" -c -T'exec master..xp_cmdshell @generatingExcelFile |
|
|
|
alejandrolepetittomas
Starting Member
9 Posts |
Posted - 2011-02-09 : 12:07:38
|
| you should build an SSIS package... that would allow you to transform data (adding quotes) after the data flow source and the data flow destination |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-02-09 : 12:12:08
|
| Answer to your question: Yes, I am running the commands through SSMS.And yes, the second one didn't give any errors.What should I change?alejandrolepetittomas, I am using bcp because it is easier for me than SSIS package. If I am struggling with this, imagine using ssis package?If you could help with SSIS package, that would be greatly appreciated. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 12:15:36
|
quote:
Originally posted by simflex
Answer to your question: Yes, I am running the commands through SSMS.And yes, the second one didn't give any errors.What should I change?
Simply place your SELECT query insides those double qoutes and check its result ..  |
|
|
|
alejandrolepetittomas
Starting Member
9 Posts |
Posted - 2011-02-09 : 12:25:10
|
| first of all, check that you have 'SQL Server Business Intelligence Development Studio' installed. If you have it, I will guide you; it's pretty simple and it would take 5 minutes to create and execute the package |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-02-09 : 12:25:14
|
| MIK_2008,I got it to work.But I have problem with the WhERe clause.Can you assist there please?I just want to export 3 months' worth of data.Just this part:WHERE CONVERT(Char,DateAdd(mm,-3,getdate()),101) = CONVERT(Char,getdate(),101)' is where I need help and I will be set.Thanks for your help |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 12:28:52
|
| check this out ... Where tableName.DateColumn between dateadd(m,-3,getdate()) and Getdate()replace the TableName and DateColumn with the names as per your table structure |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-02-09 : 12:30:35
|
| That would have made it much easier but it doesn't have a date column sadly.So, I am left to manipulate thingsDo you know of another work around? |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 12:44:37
|
| dude how could you then decide which data is laying in the 3 months criteria .. there must be a reference point which should be used for selection!!! OK explain me in layman language in context of the following... let suppose there are 100K rows returning from your "select" statement but none of the resulting column is of type Date. then how would you decide as which portion of data suits at "3 month" criteria. to be clear, i am unable to understand your term "3 months' worth of data". If you could explain this to me perhaps i might help you out too ... also post the query you have so that i can examine it in light of your explanation (if i understand ) |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-02-09 : 12:49:59
|
| WHERE CONVERT(Char,DateAdd(mm,-3,getdate()),101) = CONVERT(Char,getdate(),101) Maybe I'm missing something but that statement will never return any rows as it will always be false? |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-02-09 : 13:12:21
|
| ZZartin, we know that.That's why we are trying to fix it.MIK_2008, I didn't create any of these tables unfortunately.Yes, it is tough to give 3 months data when there is no date. |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-02-09 : 13:17:07
|
| Well if you have no date to query against you can't get 3 months worth of data. |
|
|
|
|
I need help with data export to txt file, please??