HI Lynn and all others
This is a heading banging against the wall type of thing that I have been trying to get the upper management to understand for a little while now, right after I first discovered it. They are using the same schema in not only SQL Server, but in DB2.
So when I create a table variable to load data, thru my ETL process. I use 8 to 10 different tables to route the data accordingly. I fill a RowID very easily and simple in SQL Server. But I have to turn right around on the next step, query a "Keys" table to select the rowcount, which someone named "Count"(a nice reserved work in SQL Server) , select the count for the table where I am loading said data, and then reseed the temp table that I am using.
Makes me want to beat the crap out some of the old time thinkers.
I did figure out that I can use a hard table, since only the ETL process will be touching and only once a day, to get around this issue. SSIS will use the service account to perform the reseed, and it does this with no errors.
Notice my little guy at the computer pulling his hair out, that is exactly the way I feel some of the time working with a poor performing schema of DB2 within SQL Server. I am thankful this is for only one little database and one fairly simple data load ETL. All the other, I have designed using SQL Server process, standards and coding methods.