I want to avoid dynamic pivot in SQL Server.

How to do cross-server processing with SQL Server?

• Here's the situation - I'm coming from a predominately mainframer background to a new job where I work with SQL Server databases that reside on different servers for different aspects of business (not all of which I have direct access to.) Some of these databases contain references to database key data in other servers but, because there's no direct connectivity, no regular foreign key constraints can be defined to enforce RI and, as a result, the databases get out-of-synch over time. One possible solution I thought of to at least identify the data inconsistencies was to create and schedule a job (probably weekly) on each server to a) Look for and (if not found) create a particular flat file b) Execute a query against each database that resides on that server that creates a resultset of 3 columns: the 'local' table key, the 'remote' table key and a constant identifier for that pairing (like tabA->tabB) c) pipe the resultset into the flat file in csv, xml or a similar format d) push the completed file from that server to a central one that basically works as a switchboard between all of them (which I have admin rights to, as opposed to most of the others) I then would periodically (depending on frequency of the other job schedules) verify the dates on the individual files on the central server and execute a job to a) truncate a synch_up table to empty it b) load the data from all the individual flat files c) run a query to identify all instances of a given tabA->tabB row without a corresponding tabB->tabA My questions are this: 1) Is this the best method for doing this or have I overlooked a simpler solution? 2) Am I correct that SSIS is the usual method for scheduling a job like this? 3) Are there any issues in creating a job to push a file from one server to another? Also, if anyone happens to have links to examples of T-SQL scripts that are relevant in formatting, piping and subsequent loading of resultsets, I'd appreciate it...I can probably work it out myself eventually, but I'd just as soon avoid as many red herrings as possible, especially since I will be having to walk others (some of which may be as much or more of a newbie to the inner workings of SQL Server) thru installing and scheduling the jobs on the servers I don't have direct access to.

• Answer:

  Indeed, you are "mad"... :-) This is a very complex question, that has no answer "here". It is a hands-on problem to be solved by someone IN SITU... Your solution appears correct and achievable, but it has to be experimented. The only advice I could give it to get your program to display, WITHOUT updating, what it intends to change, so you can have a visual "human" verification. Once this is proven "ok", run the program with the updates...