Inserting data from one database to another on separate server

Question

Post reply

Inserting data from one database to another on separate server

Andrew Murphy-239027

SSC Enthusiast

Points: 123
More actions
August 25, 2005 at 8:26 am

#88782

Hi
I'm sure this is very simple to do, but I cannot find anywhere within stored procedure documentation how to insert data from one database table to a table on a separate physical server.
Not sure how to set up the permissions, etc.
Is it as simple as specifying the full server/database name in the sql?

Andrew

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

check openrowset, openquery and sp_addlinkedserver in bols.

Andrew Murphy-239027 SSC Enthusiast Points: 123 More actions · Answer 2

Remi

Many thanks for your post - have got the linked server set up and have wrote sp to move the data, only issue I now have is when I try to run the sp to insert data into my "live" table I get the error:

Server: Msg 8501, Level 16, State 3, Procedure sp_test, Line 3

MSDTC on server 'LBBTESTSVR01' is unavailable.

(LBBTESTSVR01 is the server where the sp has been created)

The strange thing is that I can execute the sp on another table, but am wondering what would cause the above error.

It doesnt seem like its permissions to do with the table itself - could anyone point me in the right direction....

Andrew

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

August 26, 2005 at 6:43 am

#585347

Can you show me the code you are using?

Andrew Murphy-239027 SSC Enthusiast Points: 123 More actions · Answer 4

CREATE PROCEDURE [dbo].[sp_test] AS

INSERT INTO aim_t_holding

(CAN, Transaction_Date, Machine_Code, Transaction_Type, Ref_No, Ref_No2, Tran_Amt_Base, Pay_Amt_Base, Tran_Amt_NonBase,

Pay_Amt_NonBase, Fund_Code, Pay_Code, Pay_Reference, Group_Number, Posting_Date, Batch_Number, Import_Code, VAT_Amt_Base,

VAT_Amt_NonBase, VAT_Code, Balance, User_Code, Override_User_Code, Posted, Other_Indicators, Source_Code, Area_Code, Location_Code,

Account_Code, Tran_Code, Sort_Code, Narrative, Receipt_Code, Line_Number, Misc, Reversal, Reversed, LinkCAN, Auth_Code, Auth_Type,

Issue_Number, Card_Number, Start_Date, Expiry_Date, Card_Tran_Type, Merchant_Number, SAN, CashBack_Amount, Name, Surname, Address_1,

Address_2, Address_3, Address_4, Post_Code, Entry_Date, Import_Record_Number, Import_Record_Type, Import_File_Date, Socket_Number,

User_Long1, User_Long2, User_Long3, User_Long4, User_String1, User_String2, User_String3, User_String4, User_String5, Key_Swipe,

Split_Seq_No, AFund_Code, APay_Code, Surrendered, Link_Transaction_Date, Link_Machine_Code, System_Code, Unique_Tran_ID, Account_Name,

Account_Surname, Response_Verbosity, From_Transaction_Date, AVV_CVV_Response, Card_Mnemonic, Receipt_Cancelled, Process_Date,

Settlement_Interface)

SELECT CAN, Transaction_Date, Machine_Code, Transaction_Type, Ref_No, Ref_No2, Tran_Amt_Base, Pay_Amt_Base, Tran_Amt_NonBase,