Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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, sprocs, 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 compatability to 80, alters the DB to match the settings of the source DB, creates the tables and then the sprocs / triggers / functions / etc. This works perfectly up to creating the sprocs, 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 sproc which references these columns, whether or not they exist, so when I try to create the sproc from a DB which does not have these columns the script just kinda falls over. I'm using try catch around every sproc to get it to go past the erroneous sproc, but I'm not happy with that as the erroneous sproc 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

share|improve this question

migrated from stackoverflow.com Dec 8 '12 at 22:00

This question came from our site for professional and enthusiast programmers.

1  
Can you just create the missing columns on the other servers and leave them empty? –  Nick Vaccaro Dec 7 '12 at 19:04
    
In the 2nd paragraph you refer to their DB (singular). Not clear to me how multiple severs come into play. –  Blam Dec 7 '12 at 19:04
    
@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 '12 at 20:32
    
Ok, serious answer. You need to hire a consultant. Clearly you've got a massive project - something that no Q and A site in the world is going to fix. Recommend hiring someone really, really good. –  Nick Vaccaro Dec 7 '12 at 20:35
1  
You say you have one SQL-Server instance per server (machine) and one database per instance. Why then don't you backup these DBs in every instance and restore them in your servers (probably in one server for 2008 dbs, one for 2008R2, etc.)? –  ypercube Dec 7 '12 at 23:43

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.

share|improve this answer
    
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 '12 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 '12 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. –  Stuart Ainsworth Dec 9 '12 at 17:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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