First of all apologies for the novel but there's a lot to explain...

We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be exactly the same, but this is where over 20 years of product development gets you). For reasons I cannot say my task is to write a script which imports ALL data for a specific client from their DB. There can be many clients in a single DB so we have to detect if customerID is exists on each table and filter on that column if it does. I am using linked servers, a SQL Server Agent job, stored procedures, a cursor for all the tables and dynamic SQL to get as far as importing all of the data using select * into. The job simply drops / recreates the DB on the test server, sets compatibility to 80, alters the DB to match the settings of the source DB, creates the tables and then the stored procedures / triggers / functions / etc. This works perfectly up to creating the stored procedures, triggers, functions etc.

Basically there is one table, on the live server we're running this against in our tests, which used to have columns 'insertedpersonid' and 'updatedpersonid', but for reasons unknown these can now only be found on one server to my knowledge (and there may well be other tables where this is an issue on other servers, so simply creating the columns on the servers which don't have them is not an option). There is a stored procedure which references these columns, whether or not they exist, so when I try to create the stored procedure from a DB which does not have these columns the script just kinda falls over. I'm using try catch around every stored procedure to get it to go past the erroneous stored procedure, but I'm not happy with that as the erroneous stored procedure is not created and therefore the DB is not an exact copy of live. I could also be missing other errors in this way.

I have done a lot of searching on this topic, but cannot find any way to ignore object dependencies. Does anyone know of a way to do this?

Also if you can think of any better ways to do any or all of this then please feel free to say so. Note that this will be used by several teams around the world through a web interface (select the server > select the client > click import), so it can't be a simple backup / restore onto another server (firstly not all teams have access to the server and if we import 10 clients then in some cases the DB size will exceed 300GB, which simply won't fit onto the server we're importing to).

Lastly I am afraid I cannot give any detail on what version of SQL Server we're using as there are over 100 servers, some of which are on different versions of Windows Server. We're not happy that the testing server may not be on the same OS or SQL Server version, but it's the way it has to be. In the ideal world we'd have 1 server per SQL Server version per OS.

Thanks in advance. Regards, Richard

  • @Norla: We have a release process in place so it would take 1-2 months to make this change. Since we have hundreds of clients, each with different config, we cannot tell what effect a change will have for certain. There are many many differences across all the DBs, I've just presented one (I think it is the main issue we're going to come across in terms of replicating a client setup). Even just the max length of a column can vary between two servers, so some data may be truncated if the length of the same column in another DB is too small. Basically this is not a way forward, unfortunately.
    – ClarkeyBoy
    Dec 7, 2012 at 20:32
  • @Blam: We have over 100 servers, 1 DB per server, up to 15 clients per DB (i.e. 15 clients per server). To import 1 client would require that we exactly replicate the DB which is on the server they're based on and import all of the master data and the data for that client.
    – ClarkeyBoy
    Dec 7, 2012 at 20:36
  • All I need to do is ignore object dependencies - I don't believe Microsoft would've made this impossible for one moment.. but I suspect the need to do this is rare hence I haven't come across the answer in my searches.
    – ClarkeyBoy
    Dec 7, 2012 at 20:40
  • A client is only ever based in 1 DB, but we have hundreds of clients (well over 500) so we split them up into, at most, 15 clients per DB and we have, at most, 15 clients per server. For example clients A, B & C might be based at www.example.com/, client D might pay for their own server (i.e. they're the only client on their file server and in their DB) so might be based at www20.example.com/. If we select clients A, B or C, we would import from the same DB but with a filter on the customerID column. If we select client D, however, we import and match the DB structure of a different DB.
    – ClarkeyBoy
    Dec 7, 2012 at 20:55
  • Basically ALL DBs should match in terms of structure, but the product has been through ~20 years of development so understandably the databases are out of sync in places. To replicate a live environment for testing or development purposes we would need to exactly match the testing DB structure to the live DB structure and import the live data.
    – ClarkeyBoy
    Dec 7, 2012 at 20:59

1 Answer 1


As others have said, you have problems which are goign to require serious re-evaluation of how you do business. You can keep patching things with virtual duct-tape, or you can come up with a plan to fix the problem.

However, a duct-tape solution for this particular problem would be to check for the existance of those columns first (using information_schema.columns), and then create the appropriate version of the stored procedure (or not at all). This will continue to create havoc, but it will at least buy you some time.

  • Thanks for the response Stuart. Unfortunately whether we carry on trying to patch it up or whether we go and synchronise the structure of all the DBs is not down to me (else we would be comparing all of the DBs already and working out which ones need fixing and how). In terms of checking if the columns exist, it is not that simple. We're just using sys.sql_modules to get the definition of each procedure, function etc. (this returns the creation scripts). Parsing each of these to find out which columns are being used would take far too long and be far too unreliable.
    – ClarkeyBoy
    Dec 9, 2012 at 16:10
  • Having said that, I have heard of sys.dependencies, although I haven't read into it much. If this lists the tables / columns which are required for a procedure to work, we could work out what needs to be added.
    – ClarkeyBoy
    Dec 9, 2012 at 16:12
  • I was thinking more along the lines of since you know which procedure is broken and which columns are missuing, you alter your deployment script to use either version a or version b of your stored procedure based on that knowledge. There's only so much you can do with automation. Dec 9, 2012 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.