Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:
create table #SurveyData
(
tableName varchar(50),
columnName varchar(50),
columnId int,
rownum int
)
create table #results
(
SurveyId int,
InstanceId int,
QuestionNumber int,
Response varchar(1000)
)
-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join
(
SELECT *
FROM sys.all_objects
WHERE type = 'U'
AND upper(name) like 'LIBRARY%'
) Tables1
ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;
declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);
Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';
WHILE @RowCount <= @TotalRecords
BEGIN
SELECT @TableName = tableName, @ColumnName = columnName
FROM #SurveyData
WHERE @RowCount = rownum
SET @sql = 'INSERT INTO #results ' +
' SELECT s.SurveyId
, s.InstanceId
, CASE WHEN columnName = ''' + @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
, Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
FROM #SurveyData t
INNER JOIN ' + @TableName + ' s' +
' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
' WHERE t.columnName = ''' + @ColumnName + ''''
exec(@sql)
SET @RowCount = @RowCount + 1
END
SELECT SurveyId, InstanceId, QuestionNumber, Response
FROM #results
drop table #SurveyData
drop table #results
See SQL Fiddle with the final script
As there are two servers there are no real good options. Pretty much your only choice will be to use triggers in both databases that write to the remote database. You'll need logic in the trigger so that the trigger does nothing if the call is coming from the other SQL instance. Otherwise you'll end up with a deadlock every time.
If one instance is down than nothing will work until that server is back up and running.
Best Answer
Back in the day we had an
IDtable. Single column, single row with anintvalue. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe. There's an example in the documentation that illustrates this:
I've edited the example to highlight this usage.
An identical outcome could be achieved by generating the globally unique numbers in the application code, before they are passed to the database. Were I to implement this I imagine it would be as a static method of some utility class compiled into the executable (though other implementations would be possible). Say the application needs to write a customer's details to the database. As it is marshaling the customer name, address, telephone number etc. it also generates a new global ID. The ID is passed to the INSERT statement (or stored procedure) as just another parameter value.
Whether the ID values are produced by the application architectural tier or the database tier would depend on the specific design considerations. If the app can scale out coordination between instances becomes problematic. After an application re-start the code must figure out the next value to use. The DB server has these features, and others, written into it already.
What I would definitely not do is have the application call the database just for the next ID, then marshal that with the business data into an INSERT. That's too many round-trips to the database when only one is required.