Hello All,
We're migrating to SQL Server 2008 and have run into an interesting problem.
We have two databases:
1) Main production database; contains all tables, SPs, views, etc. for the app.
Size is roughly 300 megabytes.
2) Support database; used to accept data for updates of main database. This
database has an empty table for each table in the Main database as well as
STORED PROCEDURES to control the data during the update process.
A bi-weekly database update consists of roughly 1 megabyte of zipped BCP data
that is destined for roughly 60 tables or so.
The data is BCP'd into the Support database and stored procedures check the
validity of the data against the Main database. If everything is OK, the
stored procedures will perform the necessary Delete/Insert/Update operations
to apply the new data.
The Delete and Update operations don't present a problem.
The Insert SQL syntax seems to have the constraint that data can only be "pulled"
into the database to be updated. To illustrate:
Insert Into Main..SomeTable
Select * From Support..SomeTable
If this SQL is executed with the Default database set to Main - no problem.
If this SQL is in a Support..StoredProcedure, then it throws an error.
Something like "Can't find database nnnn ...".
The Support database's stored procedures can't be simply modified to have:
"Use Main" before the SQL ... so what's the solution. I hope the solution
isn't to move the Support SPs into the Main database.
Thanks in advance,
Wayne
We're migrating to SQL Server 2008 and have run into an interesting problem.
We have two databases:
1) Main production database; contains all tables, SPs, views, etc. for the app.
Size is roughly 300 megabytes.
2) Support database; used to accept data for updates of main database. This
database has an empty table for each table in the Main database as well as
STORED PROCEDURES to control the data during the update process.
A bi-weekly database update consists of roughly 1 megabyte of zipped BCP data
that is destined for roughly 60 tables or so.
The data is BCP'd into the Support database and stored procedures check the
validity of the data against the Main database. If everything is OK, the
stored procedures will perform the necessary Delete/Insert/Update operations
to apply the new data.
The Delete and Update operations don't present a problem.
The Insert SQL syntax seems to have the constraint that data can only be "pulled"
into the database to be updated. To illustrate:
Insert Into Main..SomeTable
Select * From Support..SomeTable
If this SQL is executed with the Default database set to Main - no problem.
If this SQL is in a Support..StoredProcedure, then it throws an error.
Something like "Can't find database nnnn ...".
The Support database's stored procedures can't be simply modified to have:
"Use Main" before the SQL ... so what's the solution. I hope the solution
isn't to move the Support SPs into the Main database.
Thanks in advance,
Wayne
