December 13, 2006 at 8:28 am
I am using Integration services to populate data from various regions (ie Europe, NorthAmerica and Asia). The data structure is the same yet it is required that the data be loaded into seperate tables.
To keep to a single code base to load the regions I have designed the DTS packages to load the tables into under seperate schemas named after the region.
For example if we take the Customer table for then Europe reiogn this would be Europe.Customer and for Asia it would be Asia.Customer
Using a different user for each region I can grant rights to the specific schema and deny rights to all other schemas. This enables me just to change the connection string in the package and the appropriate schema is used running the same code.
The problem is that SQL Server appears to get confused. If I run the Europe region this works fine. If I then run the NorthAmeria region the package fails with a permission denied error on the NorthAmerica. If I restart SQL Server and rerun the NorthAmerica region the packages works fine. The problem also happens if I run the regions in reverse order with a permissoin denied on the Europe region. The interesting thing is that if restart SQL in between running the packages they run perfectly.
Sorry if this is a bit vague but I am stumped on where to go with this.
Any ideas would be much appriciated.
Thanks
Daniel
December 14, 2006 at 5:45 pm
Hi
I have an integration service package with runs on multiple schema. My quick question would be is it a OLE db connection or a SQL Connection. The Sql connections seem to have issue with Scheme but if you use OLE DB Connection then it would work without issues.
cheers
Mani
December 18, 2006 at 5:14 am
Hey Mani,
They are definately OLEDB connections.
I'm having a rest from this problem until the development is completed. This will give me a chance to investigate this further having full control over the development server. Hopefully I'll be able to get a bit more info on the issue to post then.
Thanks
Daniel
January 3, 2007 at 5:50 am
This had me quite baffled for a while but I think and hope I have found the solution to this problem. Though I don't the exact technical details I am pretty sure that SQL is reusing a cached version of the execution plan it has previously generated.
Even though the SQL executed on the different connections is the same the tables being accessed are different. The compiled execution plan from the initial execution of the query is being cached and reused the next time the SQL run. However this is incorrect because the underlying tables are different.
I have added the Option (RECOMPILE) to all affected queries which seems to have cleared the problem by preventing SQL from caching their query plans.
Regards
Daniel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply