Check the settings on your OLE DB destination. Remove any value from Rows Per Batch and make sure Maximum commit size is zero.

Also, it might be worth looking at the...

Ok, for all intents and purposes, it's the same underlying technology. I'd start by removing the OLE DB destination and just sticking in a row count transformation instead to see...

Just to check, you've selected "Table or View - Fast Load" for the Data Access Mode of the OLE DB destination?

It's usually blisteringly fast going to SQL Server destinations as...

If you give the DDL for your tables and indexes, some readily consumable sample data and the actual execution plan of the query, you're likely to get some better advice.

Just...

If this is for an ETL process to merge data from a load table, you probably want to consider doing all of this within a MERGE statement (dynamic or otherwise),...

I'm pretty sure you can't accomplish this with INTERSECTS alone - you'd need to break out into dynamic SQL, build your key column list from the table metadata and use...

For a start, I'd convert your query to use ANSI joins. It probably doesn't make a difference for such a simple query, but I suspect your real world example is...

Using SSIS is probably the best way to do this. You can do what you're asking in SQL 2k8 with a trace flag I believe (I'll find the link when...

Gianluca Sartori (2/16/2012)

---

Use PortQry to test the sqlbrowser service from the client machine.

If you can't connect to it, you can't connect to named instances (you wouldn't know which port to...

Yep, that's the whole point of the schema collection. An insert will fail (with a specific XSD validation error) if you try to insert malformed documents

Sample below:

CREATE XML SCHEMA COLLECTION TestSchema

AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="foobar">

<xsd:complexType>

...

I think it's pretty much a case of declaring an XML variable using your SCHEMA COLLECTION, then assigning the result of your FOR XML to that variable.

This will then error...

I'm sure there are people who are doing it, but I see them as being more relevant to OLTP at the moment than OLAP.

In my opinion, in a data warehouse...

Just my 2p. I was in a similar situation a couple of years ago and thought I'd miss having my head stuck in code/administration 8 hours a day and that...

Although you can work around it using a UDF that references another table, you can't persist them as they're non-deterministic. If you need to index/persist the result, it might be...