October 19, 2010 at 11:10 am
Hello,
I have a BCP command that queries our database and creates a CSV file. This file is going to be imported into the Outlook calendar. The query that runs in the BCP command pulls 6 fields but the Outlook calendar has 22 fields. the fields have to match for Outlook to import the CSV file. I have tried to use NULL fields in the Select statement part but I get an error that says that a Union must have an equal number of expressions in their target lists. Is there a way to insert blank fields, and give them the proper Outlook titles, in the Select statement? The query that I am running is below:
BCP "SELECT 'Subject', 'Start Date', 'Start Time', 'End Date', 'End Time', 'All day event', 'Reminder on/off', 'Reminder Date', 'Reminder Time', 'Meeting Organizer', 'Required Attendees', 'Optional Attendees', 'Meeting Resources', 'Billing Information', 'Categories', 'Description', 'Location' 'Mileage', 'Priority', 'Private', 'Sensitivity', 'Show time as' UNION ALL SELECT [vw_Schedule].[Activity], CONVERT(CHAR(8), [vw_Schedule].[App_DtTm],1), CONVERT(CHAR(8), [vw_Schedule].[App_DtTm],8), NULL as [End Date], NULL as [End Time], NULL as [All day event], NULL as [Reminder on/off], NULL as [Reminder Date], NULL as [Reminder Time], [vw_Schedule].[STF_INITIALS], NULL as [Required Attendees], NULL as [Optional Attendees], NULL as [Meeting Resources], NULL as [Billing Information], NULL as [Categories], [vw_Schedule].[Notes], [vw_Schedule].[LOC_INITIALS], NULL as [Mileage], NULL as [Priority], NULL as [Private], NULL as [Sensitivity], NULL as [Show time as] FROM [MOSAIQ].[dbo].[vw_Schedule] [vw_Schedule] WHERE [vw_Schedule].[STF_INITIALS] <> '' and CONVERT( Varchar(12), [vw_Schedule].[App_DtTm],1) = CONVERT( Varchar(12), GetDate(),1) AND [vw_Schedule].[Activity]='MTG' " queryout C:\Scheduletext.csv -c -t, -SMosaiqreports -T
October 19, 2010 at 12:29 pm
Where you have NULL as [End Date], can you substitute a blank character as:
'' ' AS [End Date]
For example
SELECT Subject, ' ' AS [End Date], ' ' As [End Time]
October 19, 2010 at 12:40 pm
I tried it and got the same results.
October 19, 2010 at 2:40 pm
This is the error message:
"SQL State = 42000, Native Error = 205
Error = [Microsoft][Native Error][SQL Server] All
Queries combined using a UNION, INTERSECT, or EXCEPT operator must
have an equal number of expressions in their target lists.
SQL State = 42000, Native Error = 8180
Error = [Microsoft][Native Error][SQL Server] Statements could
not be prepared."
October 19, 2010 at 2:52 pm
Now this could be a result of your typing in the BCP command, and NOT an error in the actual BCP command you are using, however in looking at what you did input, I noticed that there appears to be a comma missing between
"'Location' 'Mileage'".
October 19, 2010 at 3:00 pm
The comma was it!! Thank you very much! Sometimes after looking at something like this over and over it takes another set of eyes to see the obvious!
October 19, 2010 at 3:08 pm
robert693
Thanks for your reply, and I like you did not spot it first off, now let me ask did you use a blank character or stick with those NULLs?
October 19, 2010 at 3:13 pm
The empty strings worked, the NULL values didn't seem to work. Although I didn't try the null value approach after adding the missing comma.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply