May 20, 2009 at 7:32 am
Please can you tell me how to create a package that will read data on one server and update data on another.
I have 2 SQL Servers connected via a VPN tunnel. I don't want to use linked server so my only option is to use SSIS to transfer data between the two. I can obviously insert data into tables but I can't work out the best method to take values of data in one table and update a table in the other server. See below.
Source Connection servera.erpsystem.userid
Destination Connection serverb.webstore.webid
Both database have table stock which is keyed by prodictID. I want to update serverb.webstore.stock.freeqty with the value of servera.erpsystem.stock.freeqty.
I have to many records to transfer the whole of servera.erpsystem.stock to serverb and then do an update from.
May 27, 2009 at 12:45 am
hi all,
am experiencing the same problem but the difference is that am using linked servers,am trying to update server B with data from server A which does not exist in in server B. am using the following script but it returns an error when i run it for update process:
INSERT INTO Vendor (Addr1, Addr2, APAcct, APSub, Attn, BkupWthld, City, ClassID, ContTwc1099, Country, Crtd_DateTime, Crtd_Prog, Crtd_User, Curr1099Yr,
CuryId, CuryRateType, DfltBox, DfltOrdFromId, DfltPurchaseType, DirectDeposit, EMailAddr, ExpAcct, ExpSub, Fax, LCCode, LUpd_DateTime,
LUpd_Prog, LUpd_User, MultiChk, Name, Next1099Yr, NoteID, PayDateDflt, PerNbr, Phone, PmtMethod, PPayAcct, PPaySub, RcptPctAct, RcptPctMax,
RcptPctMin, RemitAddr1, RemitAddr2, RemitAttn, RemitCity, RemitCountry, RemitFax, RemitName, RemitPhone, RemitSalut, RemitState, RemitZip,
S4Future01, S4Future02, S4Future03, S4Future04, S4Future05, S4Future06, S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12,
Salut, State, Status, TaxDflt, TaxId00, TaxId01, TaxId02, TaxId03, TaxLocId, TaxPost, TaxRegNbr, Terms, TIN, User1, User2, User3, User4, User5,
User6, User7, User8, Vend1099, VendId, Zip, tstamp)
SELECT Addr1, Addr2, APAcct, APSub, Attn, BkupWthld, City, ClassID, ContTwc1099, Country, Crtd_DateTime, Crtd_Prog, Crtd_User, Curr1099Yr, CuryId,
CuryRateType, DfltBox, DfltOrdFromId, DfltPurchaseType, DirectDeposit, EMailAddr, ExpAcct, ExpSub, Fax, LCCode, LUpd_DateTime, LUpd_Prog,
LUpd_User, MultiChk, Name, Next1099Yr, NoteID, PayDateDflt, PerNbr, Phone, PmtMethod, PPayAcct, PPaySub, RcptPctAct, RcptPctMax, RcptPctMin,
RemitAddr1, RemitAddr2, RemitAttn, RemitCity, RemitCountry, RemitFax, RemitName, RemitPhone, RemitSalut, RemitState, RemitZip, S4Future01,
S4Future02, S4Future03, S4Future04, S4Future05, S4Future06, S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12, Salut, State,
Status, TaxDflt, TaxId00, TaxId01, TaxId02, TaxId03, TaxLocId, TaxPost, TaxRegNbr, Terms, TIN, User1, User2, User3, User4, User5, User6, User7,
User8, Vend1099, VendId, Zip, NULL
FROM TEST.RASolApplicationPRL.dbo.vendor
where (select vendid from TEST.RASolApplicationPRL.dbo.vendor) not in (select vendid from TECHBIZAPP.dbo.vendor)
I get the following when i run the script::::
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
The statement has been terminated.
May 27, 2009 at 3:02 am
This looks like a T-SQL question to me - if so, this is the wrong forum. But as you're new here, that's OK 🙂
The error message actually tells you the problem here. You have only two subqueries, both in the WHERE clause.
This one
(select vendid from TEST.RASolApplicationPRL.dbo.vendor)
Looks like it will return multiple vendid's and that is your problem, I think.
Changing your WHERE clause to
where TEST.RASolApplicationPRL.dbo.vendor.vendid not in (select vendid from TECHBIZAPP.dbo.vendor)
looks like a probable fix. But I don't know your data, of course.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply