A question about bulk insert and partitioned views

To all,
I posted this question in the server list and that may not have been the
appropriate place so I am posting here.
I have built a partitioned view exactly as described in BOL. I can insert
data into just fine and it places the values in the correct sub-tables
according to the check constraint.
I would like to do bulk inserts on this partitioned view but I get an error
message stating that this view cannot be a target of a bulk operation. I
did include the CHECK_CONTRAINTS setting to the bulk insert call.
My question is are bulk inserts allowed into partitioned views (I cannot
find anything in BOL that states they cannot) or am I missing something in
setting up the view?
Thanks.
--seanCreate an "instead of" trigger on your view and use option FIRE_TRIGGERS wit
h
BULK INSERT.
Example:
use northwind
go
exec master..xp_cmdshell 'bcp "select orderid, convert(char(8), orderdate,
112) from northwind.dbo.orders where orderdate >= ''19970101'' and orderdate
< ''19990101''" queryout "c:\temp\test.bcp" -S server_name -T -c'
go
create table dbo.t1 (
colA int not null primary key check (colA between 10400 and 10807),
colB datetime not null
)
go
create table dbo.t2 (
colA int not null primary key check (colA between 10808 and 11077),
colB datetime not null
)
go
create view dbo.vw_myview
as
select colA, colB from dbo.t1
union all
select colA, colB from dbo.t2
go
create trigger tr_vw_myview_io_ins on dbo.vw_myview
instead of insert
as
insert into dbo.t1
select * from inserted where colA between 10400 and 10807
insert into dbo.t2
select * from inserted where colA between 10808 and 11077
go
bulk insert northwind.dbo.[vw_myview]
from 'c:\temp\test.bcp'
with
(
fieldterminator = '\t',
rowterminator = '\n',
fire_triggers
)
go
select * from dbo.vw_myview where colA = 10810
go
drop view dbo.vw_myview
go
drop table dbo.t1, dbo.t2
go
AMB
"Sean Shanny" wrote:

> To all,
> I posted this question in the server list and that may not have been the
> appropriate place so I am posting here.
> I have built a partitioned view exactly as described in BOL. I can insert
> data into just fine and it places the values in the correct sub-tables
> according to the check constraint.
> I would like to do bulk inserts on this partitioned view but I get an erro
r
> message stating that this view cannot be a target of a bulk operation. I
> did include the CHECK_CONTRAINTS setting to the bulk insert call.
> My question is are bulk inserts allowed into partitioned views (I cannot
> find anything in BOL that states they cannot) or am I missing something in
> setting up the view?
> Thanks.
> --sean
>
>

A filtered parameter list for a report using Cube

I am trying to report against a cube and I want the user to be able to choose the date for the report.

Now the cube dimension has been built against a calendar which contains values for the next 30 years(!) so when I create a parameterized report the list of dates is ridiculously huge.

I want to filter the dates out and show only the dates for which there is data. How do I filter out the list of dates for a parameter?

You need to apply an MDX filter to the parameter, something like:

EXISTS([Date].[Month].[Month].Members, ,'<measure group name>')

|||

I gathered using an MDX expression for the filter, but I'm unsure as to how to apply it.

Could someone explain exactly how.. there are so many different places for changing parameters and I'm confused as to where to do this.

Documentation is, as ever, as clear as mud...

|||

You select the MDX option in the Operator column on the filter pane. This report uses the Adventure Works sample cube to demonstrate the final result.

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="AdventureWorksAS">

<DataSourceReference>AdventureWorksAS</DataSourceReference>

<rd:DataSourceID>389e88fe-3514-42d7-a5b9-9bc3126a67bf</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Height>2in</Height>

</Body>

<rd:ReportID>2c2b18e1-e6e7-4ee2-b5c9-e664fd36f35f</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText> SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( EXISTS([Date].[Month].Members, ,'Reseller Sales') ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Sales Amount</MeasureName><UniqueName>[Measures].[Sales Amount]</UniqueName></ID><ItemCaption>Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems><FilterItem><HierarchyUniqueName>[Date].[Calendar]</HierarchyUniqueName><Condition xsi:type="MDX"><Text>EXISTS([Date].[Month].Members, ,'Reseller Sales')</Text></Condition></FilterItem></FilterItems></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement> SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( EXISTS([Date].[Month].Members, ,'Reseller Sales') ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

</Query>

<Fields>

<Field Name="Category">

<rd:TypeName>System.String</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Category]" /&gt;</DataField>

</Field>

<Field Name="Subcategory">

<rd:TypeName>System.String</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Subcategory]" /&gt;</DataField>

</Field>

<Field Name="Product">

<rd:TypeName>System.String</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Product]" /&gt;</DataField>

</Field>

<Field Name="Sales_Amount">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Sales Amount]" /&gt;</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>