Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
robert693
Starting Member
42 Posts |
 Posted - 2010-10-19 : 11:24:58
|
| 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. Is there a way to insert blank fields, and give them the proper Outlook titles, in the Select statement? The BCP command is below. Thank you for all your help!BCP "SELECT 'Subject', 'Start Date', 'Start Time', 'Meeting Organizer', 'Description', 'Location' UNION ALL SELECT [vw_Schedule].[Activity], CONVERT(CHAR(8), [vw_Schedule].[App_DtTm],1), CONVERT(CHAR(8), [vw_Schedule].[App_DtTm],8), [vw_Schedule].[STF_INITIALS], [vw_Schedule].[Notes], [vw_Schedule].[LOC_INITIALS] 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 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 11:35:03
|
Yes.In the first SELECT you can insert the needed column names and in the UNION ALL SELECT you can insert NULL or '' at the right positions.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
robert693
Starting Member
42 Posts |
Posted - 2010-10-19 : 11:46:14
|
| I tried that but I get an error that says that a Union must have an equal number of expressions in their target lists. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 11:59:13
|
You have to insert as many comma seperated NULL or '' in the UNION ALL SELECT as you have inserted your needed column names in the first SELECT.For example you have this (simplified):SELECT 'Subject','Start Date' UNION ALL SELECT [Activity], [App_DtTm] FROM [vw_Schedule]and you need another column at the end:SELECT 'Subject','Start Date', 'MyNewCol' UNION ALL SELECT [Activity], [App_DtTm], '' FROM [vw_Schedule]or you need another column in the middle:SELECT 'Subject', 'MyNewCol', 'Start Date' UNION ALL SELECT [Activity], '', [App_DtTm] FROM [vw_Schedule]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
robert693
Starting Member
42 Posts |
Posted - 2010-10-19 : 12:20:39
|
| I changed it to this and got the error: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 |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 12:23:00
|
I see no error message...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
robert693
Starting Member
42 Posts |
Posted - 2010-10-19 : 12:29:08
|
| I get the error when I run this query in the command prompt. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 14:00:04
|
Please understand: I see no error message, can you tell us what you see?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
robert693
Starting Member
42 Posts |
Posted - 2010-10-19 : 16:36:06
|
| Sorry! This is the error message:"SQL State = 42000, Native Error = 205Error = [Microsoft][Native Error][SQL Server] AllQueries 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 couldnot be prepared." |
|
|
|
robert693
Starting Member
42 Posts |
Posted - 2010-10-19 : 17:01:13
|
| Thank you for all of your help!! It turns out that a comma was missing!! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-20 : 02:24:56
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Insert blank fields into query