bcp syntax error help?

I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
ThanksI have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
Thanks

I have realized how to go around this thanks to all

BCP import - remove quotes from CSV File

My bcp process is now working. FINALLY.
But, my import data file is comma-delimited and everything is surrounded
by quotes; therefore, they data goes into the SQL databases with quotes.
Any suggestions on how I can remove these quotes, either before,during
or after the BCP process?
Sample CSV file:
"204980","33404","Cindy W
Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
21/02
*** Sent via Developersdex http://www.examnotes.net ***As you specified the column delimiter as “comma”, you can specify the te
xt
qualifier as “quotes”. It will solve the problem.
"Joey Martin" wrote:

> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
> *** Sent via Developersdex http://www.examnotes.net ***
>|||How would I do that with the bcp function? Or is it something that I
would do later?
*** Sent via Developersdex http://www.examnotes.net ***|||If you need to use the bcp you should create a bcp format file.
something like the following:-
6.0
4
1 SQLDATETIME 0 8 "\t" 1 ErrorDate
2 SQLCHAR 0 255 "\t" 3 ErrorMsg
3 SQLCHAR 0 255 "\t" 2 ErrorCaption
4 SQLCHAR 0 255 "\n" 4 ErrorClass
The problem is on the bcp utility you are allowed to only to specify the
field delimiter using [/t field_term] option.
I think the best way to do it to create a DTS package transfer the data from
your csv file to a table on SQL server. On the DTS package you can specify
the text
qualifier as “quotes”. Then you have an option to save it as a DTS packa
ge
or VBS.
I think the DTS is better than creating the bcp format file.
-Nader
"Joey Martin" wrote:

> How would I do that with the bcp function? Or is it something that I
> would do later?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Joey Martin (joey@.infosmiths.net) writes:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
As Nader said, you need to use a format file. Here is a sample of how it
would look like:
8.0
4
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\n" 3 col3 ""
This is fitted for a CSV file with three fields like:
"Field1","Field2","Field3"
The trick is that we define the file as having four fields. The first
field is just an empty dummy which we don't import. This is why it says
0 in the database-column field.
Also in a format file, what counts is the column numbers. Column names
are just informational, and not used by BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx