linkedserver

[niloofar norouzi]

با سلام
برای insert به جداول دیگر روی سرور متفاوت از سیستم فعلی از دستور زیر استفاده می کنم

DECLARE @server NVARCHAR(100)
SET @server = N'172.16.11.44'


EXEC sp_addlinkedserver
@server,
@srvproduct = N'',
@provider = N'SQLNCLI',
@datasrc = N''



EXEC sp_addlinkedsrvlogin '172.16.11.44',
'false',
'sa',
'sa',
'cdssa'



DECLARE @TranName VARCHAR(20);
SELECT @TranName = 'MyTransaction';
DECLARE @SaleTransactionID BIGINT,
@TableName NVARCHAR(150),
@ID BIGINT,
@Action NVARCHAR(50),
@sqlString VARCHAR(8000),
@COLUMN_NAME NVARCHAR(100),
@InvoiceID BIGINT


--BEGIN TRANSACTION @TranName



DECLARE curSaleTransactions CURSOR
FOR
SELECT SaleTransactionID,
TableName,
ID,
[Action]
FROM tS007SaleTransactions
WHERE [Status] = 0
ORDER BY
SaleTransactionID


OPEN curSaleTransactions


FETCH NEXT FROM curSaleTransactions INTO @SaleTransactionID,@TableName, @ID,
@Action
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAME = COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
WHERE a.table_name = @TableName
AND constraint_type = 'Primary key'


IF @TableName = 'tS004Invoice'
BEGIN
--EXEC('DISABLE TRIGGER ALL ON tS004Invoice') AT ([172.16.11.44].[CDS.Savin].dbo.tS004Invoice)


--ALTER TABLE [172.16.11.44].[CDS.Savin].dbo.tS004Invoice DISABLE TRIGGER ALL


INSERT INTO [172.16.11.44].[CDS.Savin].dbo.tS004Invoice
(
InvoiceNumber,
InvoiceDate,
CustomerID,
Discount,
SumPrice,
InvoiceNote,
LocationID,
UserID,
DateIn,
SumPriceTax,
AccountingDocNo,
SellerID,
OrganizationID,
SumTax,
SumToll
)
SELECT InvoiceNumber,
InvoiceDate,
CustomerID,
Discount,
SumPrice,
InvoiceNote,
LocationID,
UserID,
DateIn,
SumPriceTax,
AccountingDocNo,
SellerID,
OrganizationID,
SumTax,
SumToll
FROM tS004Invoice
WHERE InvoiceID = @ID
END


IF @TableName = 'tS004InvoiceDetail'
BEGIN
SELECT @InvoiceID = InvoiceID
FROM [172.16.11.44].[CDS.Savin].dbo.tS004Invoice
WHERE InvoiceNumber = (
SELECT InvoiceNumber
FROM tS004Invoice
WHERE InvoiceID = (
SELECT InvoiceID
FROM tS004InvoiceDetail
WHERE InvoiceDetailID = @ID
)
)


INSERT INTO [172.16.11.44].[CDS.Savin].dbo.tS004InvoiceDetail
(
InvoiceID,
GoodsID,
Price,
PriceT,
Discount,
UnitID,
Quantity,
UserID,
Date,
TotalPriceTax,
TotalPrice,
Tax,
Toll
)
SELECT @InvoiceID,
GoodsID,
Price,
PriceT,
Discount,
UnitID,
Quantity,
UserID,
Date,
TotalPriceTax,
TotalPrice,
Tax,
Toll
FROM tS004InvoiceDetail
WHERE InvoiceDetailID = @ID
END


IF @TableName = 'tS004InvoicePayment'
BEGIN
INSERT INTO [172.16.11.44].[CDS.Savin].dbo.tS004InvoicePayment
(
PayTypeID,
InvoiceNumber,
LocationID,
BankName,
Date,
Price,
DocumentNo,
UpdateUserID,
DateIn
)
SELECT PayTypeID,
InvoiceNumber,
LocationID,
BankName,
Date,
Price,
DocumentNo,
UpdateUserID,
DateIn
FROM tS004InvoicePayment
WHERE InvoicePaymentID = @ID
END


IF @TableName = 'tS005AccountingDocs'
BEGIN
INSERT INTO [172.16.11.44].[CDS.Savin].dbo.tS005AccountingDocs
(
LocationID,
AccountingDocNumber,
AccountingDocDate,
AccountID,
DetailAccountID,
CustomerID,
[Description],
BedPrice,
BesPrice,
DateIn,
UserID
)
SELECT LocationID,
AccountingDocNumber,
AccountingDocDate,
AccountID,
DetailAccountID,
CustomerID,
[Description],
BedPrice,
BesPrice,
DateIn,
UserID
FROM tS005AccountingDocs
WHERE AccountingDocID = @ID
END
SET @sqlString = 'SELECT * FROM ' + @TableName + ' WHERE ' + @COLUMN_NAME
+
' = ' + CONVERT(NVARCHAR, @ID)



EXEC (@sqlString)
-- SELECT *
-- FROM [172.16.11.44].[CDS.Savin].dbo.tS000Actions
UPDATE tS007SaleTransactions
SET [Status] = 1
WHERE SaleTransactionID = @SaleTransactionID


FETCH NEXT FROM curSaleTransactions INTO @SaleTransactionID,@TableName,
@ID,
@Action
END
CLOSE curSaleTransactions
DEALLOCATE curSaleTransactions


--COMMIT TRANSACTION @TranName



EXEC sp_droplinkedsrvlogin '172.16.11.44',
'sa'


EXEC sp_dropserver '172.16.11.44',
NULL
اما با error زیر روبرو می شوم
Transaction context in use by another session.