April 7, 2008 at 8:30 am
Hey guys, I've been working on this stored procedure that basically copies everything from one set of tables on one computer to a database on the network. The catch is is that I'm dealing with multiple linked servers to do this with. I only want to move records to one computer at a time though (user specified). Here is what I'm looking at:
CREATE PROCEDURE usp_SyncTables (@NewVersion char(10), @Line int, @WaitDateTime datetime, @StationId int) AS
DECLARE @TransName varchar(20)
SELECT @TransName = 'Sync'
BEGIN TRANSACTION @TransName
if @Line = 1
Begin
DELETE [LinkedComputer1\SQLEXPRESS].Test.dbo.Codes
INSERT INTO [LinkedComputer1\SQLEXPRESS].Test.dbo.Codes SELECT * FROM Test.dbo.Codes
UPDATE [LinkedComputer1\SQLEXPRESS].Test.dbo.Version SET WaitingVersion = @NewVersion, WaitingDateTime = @WaitDateTime WHERE StationId = @StationId
End
else
if @Line = 2
Begin
DELETE [LinkedComputer2\SQLEXPRESS].Test.dbo.Codes
INSERT INTO [LinkedComputer2\SQLEXPRESS].Test.dbo.Codes SELECT * FROM Test.dbo.Codes
UPDATE [LinkedComputer2\SQLEXPRESS].Test.dbo.Version SET WaitingVersion = @NewVersion, WaitingDateTime = @WaitDateTime WHERE StationId = @StationId
End
else
if @Line = 3
Begin
DELETE [LinkedComputer3\SQLEXPRESS].Test.dbo.Codes
INSERT INTO [LinkedComputer3\SQLEXPRESS].Test.dbo.Codes SELECT * FROM Test.dbo.Codes
UPDATE [LinkedComputer3\SQLEXPRESS].Test.dbo.Version SET WaitingVersion = @NewVersion, WaitingDateTime = @WaitDateTime WHERE StationId = @StationId
End
GO
COMMIT TRANSACTION Sync
GO
Sorry for any syntax errors. This is my first stored procedure with transaction. What I'd really like to do is just make a variable passed into the stored procedure called @LinkedComputer and just use that variable in the sql command strings. However, I get errors with that. Here are my questions for the elite SQL guys:
1. Is it possible to pass a variable for the linked computer name?
2. Is there a switch statement I could use to do the above code based on @Line
3. Is there a better approach to this that I'm not thinking of?
April 7, 2008 at 8:40 am
Use dynamic SQL - i.e.
declare @LinkedServer sysname, @sql nvarchar(max)
set @LinkedServer = 'MYTESTSERVER'
set @sql = 'select top 100 * from ' + @LinkedServer + '.master.dbo.sysdatabases'
exec sp_executesql @sql
Tommy
Follow @sqlscribeApril 7, 2008 at 8:50 am
Wow, thanks! That fixed that problem fast. Thanks for the help!
-Jeremy
April 7, 2008 at 9:05 am
NP 🙂 Have a good one -
Tommy
Follow @sqlscribeApril 8, 2008 at 10:19 am
Welp, now that I have the linked server queries working, I'm having trouble with putting them into a stored procedure. I know each of the queries runs by itself, but when I put it into a distributed transaction procedure, I get this error:
OLE DB provider "SQLNCLI" for linked server "server1\SQLEXPRESS" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "server1\SQLEXPRESS" was unable to begin a distributed transaction.
(server1 is the server that I'm pushing the data to).
I've been doing alot of reading on this and found that the MSDTC could be the culprit. I enabled inbound and outbound transactions, made sure that the names were the same (network service), restarted both services, then both computers. No dice. Could someone help me with this? server1 is win2k3 R2 and my dev laptop is running winXP. Both are SQL2005 Express. Here is the stored procedure (simplified):
CREATE PROCEDURE usp_SyncTables2 (@LinkedServer sysname) AS
declare @sql nvarchar(max)
BEGIN DISTRIBUTED TRANSACTION
set @sql = 'DELETE [' + @LinkedServer + '].Test.dbo.Codes'
exec sp_executesql @sql
set @sql = 'INSERT INTO [' + @LinkedServer + '].Test.dbo.Codes SELECT * FROM Test.dbo.Codes'
exec sp_executesql @sql
COMMIT TRANSACTION
GO
Thanks for the help again!
-Jeremy
Edit/Update: Also, I ran the dtctester.exe program and didn't come up with any errors. ugh! Please help me.
April 8, 2008 at 11:10 am
Why a distributed transaction? MSSQL will promote the local transaction to a distributed one automatically. Also,
issue this statement before you run your query:
SET XACT_ABORT ON
The XACT_ABORT option must be set to ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. This option is not required if the provider supports nested transactions.
Tommy
Follow @sqlscribeApril 16, 2008 at 11:58 am
Thanks for that. I don't totally understand the use of XACT_ABORT ON but when I used it and took off the distributed transaction, the code worked... all the way till the last sql command:
set @sql = 'UPDATE [' + @LinkedServer + '].Test.dbo.Version SET WaitingVersion = ' + @NewVersion + ', WaitingDateTime = CONVERT(DATETIME, ' + @WaitDateTime + ', 102) WHERE StationId = ' + CAST(@StationId AS nvarchar)
exec sp_executesql @sql
(@StationId is defined as int)
The error is at the very end:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'. <-- @StationId is set to 1
I've tried cast and convert and the error keeps showing up. Any ideas? Thanks for all the help btw.
-Jeremy
April 29, 2008 at 7:47 am
JeremyVnc (4/16/2008)
Thanks for that. I don't totally understand the use of XACT_ABORT ON but when I used it and took off the distributed transaction, the code worked... all the way till the last sql command:
set @sql = 'UPDATE [' + @LinkedServer + '].Test.dbo.Version SET WaitingVersion = ' + @NewVersion + ', WaitingDateTime = CONVERT(DATETIME, ' + @WaitDateTime + ', 102) WHERE StationId = ' + CAST(@StationId AS nvarchar)
exec sp_executesql @sql
(@StationId is defined as int)
The error is at the very end:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'. <-- @StationId is set to 1
I've tried cast and convert and the error keeps showing up. Any ideas? Thanks for all the help btw.
-Jeremy
What is the definition of "StationId" - NOT @StationId - could it be that you have a type mismatch here? For instance, if your field is NOT character, then that could be causing your problem, no?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply