• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Multiple Servers and their databases in one script

I am attempting to write one script that use four tables. Three tables come from one server and the third table comes from a second server.   I am receiving the error below.  I need help in understanding how to accomplish my goal.

The first server is [IND-SL-35RKF12].  The second server is [DAXCSO3SQL01].  The first servers database used is [KBM400MFG],  the second server database is [DmStagingVD]. The first servers tables are [FLPODET], [FLPOSUM], [FLSTCMST]. The second servers table is [DMFPRODUCTENTITY].   The first server is a linked server and uses catalog S1022466. The second server is not a linked server.

Any help is appreciated.

The error:
OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "Token *N was not valid. Valid tokens: *N. SQLSTATE: 42601, SQLCODE: -104".
Msg 7321, Level 16, State 2, Line 5
An error occurred while preparing the query " SELECT    
                         b.PELINE
                  ,b.PENO                 
                        ,b.PECSDT
                        ,c.POCATG
                        ,b.PEPN
                        ,c.POCO
                        ,d.POCOM1
                        ,c.POCOST
                        ,c.POQTY
                        ,c.POVEND
                        ,b.peco
                        
                        



       FROM  [KBM400MFG].[FLPODET] B  
            LEFT OUTER JOIN [IND-SL-35RKF12].[KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO]
            LEFT OUTER JOIN  [IND-SL-35RKF12].[KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO]
 
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG".






USE  [DmStagingVD]

SELECT    TOP  150
			 ISNULL([PELINE],' ')  as [Line Number]
			,ISNULL([PENO],' ')  as [Purchase Order] 
			,ISNULL([PECSDT],'01-01-1900 00:00:00') as [Delivery Date] 
			,ISNULL([POCATG], ' ')as [External] 
			,ISNULL([PEPN],' ') as [Item Number] 
			,ISNULL([POCOM1],' ') AS NAME 
			,ISNULL([POCOST],00.00)  as [Unit Price] 
			,ISNULL([POQTY],0)   as [Quantity]  
			,ISNULL([POVEND],99999999)  as [Vendor Account] 
			,1  as [RecId]
			,' ' as [ACCOUNTINGDISTRIBUTIONTEMPLATE]
			,' ' as [ActivityNumber]
			,' ' as [ASSETBOOKID]
			,' ' as [ASSETGROUP]
			,' ' as [ASSETID]
			,' ' as [BARCODE]	
			,' ' as [BARCODETYPE]
			,' ' as [BLOCKED]
			,' ' as [CASETAGGING]
			,' ' as [CFOPTABLE_BR_CFOPID]
			,' ' as [COMPLETE]
			,' ' as [CONFIGID]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [CONFIRMEDDLV]
			,0.00 as [CONFIRMEDTAXAMOUNT]
			,' ' as [CONFIRMEDTAXWRITECODE]
			,' ' as [COUNTYORIGDEST]
			,' ' as [COVREF]
			,' ' as [CREATEFIXEDASSET]
			,' ' as [CURRENCYCODE]
			,' ' as [CUSTOMERREF]
			,' ' as [CUSTPURCHASEORDERFORMNUM]
			,' ' as [DEFAULTDIMENSION]
			,' ' as [DEFINITIONGROUP]
			,' ' as [DELIVERYADDRESS]
			,' ' as [DELIVERYCITY]
			,' ' as [DELIVERYCOUNTRYREGIONID]
			,' ' as [DELIVERYCOUNTY]
			,' ' as [DELIVERYLOCATIONROLE]
			,' ' as [DELIVERYNAME]
			,' ' as [DELIVERYSTATE]
			,' ' as [DELIVERTYSTREET]
			,' ' as [DELIVERYTYPE]
			,' ' as [DELIVERYZIPCODE]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [DEPRECIATIONSTARTDATE]
			,0.00 as [DISCAMOUNT]
			,0.00 as [DISCPERCENT]
			,' '  as [EDITABLEINWORKFLOW]
			,' ' as [EXECUTIONID]
			,' ' as [GSTHSTTAXTYPE_CA]
			,' ' as [INTERCOMPANYINVENTTRANSID]
			,' ' as [INTERCOMPANYORIGIN]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [INTRASTATFULFILLMENTDATE_HU]
			,' ' as [INVENTBATCHID]
			,' ' as [INVENTCOLORID]
			,0.00 as [INVENTINVOICENOW]
			,' ' as [INVENTLOCATIONID]
			,0.00 as [INVENTRECEIVEDNOW]
			,' ' as [INVENTREFID]
			,' ' as [INVENTREFTRANSID]
			,' ' as [INVENTSERIALID]
			,' ' as [INVENTSITEID]
			,' ' as [INVENTSIZEID]
			,' ' as [INVENTSTYLEID]
			,' ' as [ISDELETED]
			,' ' as [ISFINALIZED]
			,' ' as [ISINVOICEMATCHED]
			,' ' as [ISMODIFIED]
			,' ' as [ISPWP]
			,' ' as [ISSELECTED]
			,' ' as [ITEMBOMID]
			,' ' as [ITEMPBAID]
			,' ' as [ITEMROUTEID]
			,' ' as [ITEMTAGGING]
			,' ' as [LEDGERDIMENSION]
			,0.00 as [LINEAMOUNT]
			,' ' as [LINEDELIVERYTYPE]
			,0.00 as [LINEDISC]
			,' ' as [LINEHEADER]
			,0.00 as [LINEPERCENT]
			,0.00 as [MARKUPTRANS_CALCULATEDAMOUNT]
			,' ' as [MARKUPTRANS_CURRENCYCODE]
			,' ' as [MARKUPTRANS_DOCUMENTSTATUS]
			,' ' as [MARKUPTRANS_INTERCOMPANYCOMPANYID]
			,' ' as [MARKUPTRANS_INTERCOMPANYINVOICEID]
			,' ' as [MARKUPTRANS_INTERCOMPANYMARKUPUSEVALUE]
			,0.00 as [MARKUPTRANS_INTERCOMPANYMARKUPVALUE]
			,' ' as [MARKUPTRANS_ISDELETED]
			,' ' as [MARKUPTRANS_ISMODIFIED]
			,' ' as [MARKUPTRANS_KEEP]
			,0 as [MARKUPTRANS_LINENUM]
			,' ' as [MARKUPTRANS_MARKUPCATEGORY]
			,' ' as [MARKUPTRANS_MARKUPCODE]
			,' ' as [MARKUPTRANS_MODULECATEGORY]
			,' ' as [MARKUPTRANS_MODULETYPE]
			,' ' as [MARKUPTRANS_ORIGTABLEID]
			,0.00 as [MARKUPTRANS_POSTED]
			,0.00 as [MARKUPTRANS_TAXAMOUNT]
			,' '  as [MARKUPTRANS_TAXAUTOGENERATED]
			,' ' as [MARKUPTRANS_TAXGROUP]
			,' ' as [MARKUPTRANS_TAXITEMGROUP]
			,' ' as [MARKUPTRANS_TAXWRITECODE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [MARKUPTRANS_TRANSDATE]
			,' ' as [MARKUPTRANS_TRANSTABLEID]
			,' ' as [MARKUPTRANS_TXT]
			,0.00 as [MARKUPTRANS_VALUE]
			,' ' as [MARKUPTRANS_VOUCHER]
			,' ' as [MATCHINGPOLICY]
			,0.00 as [MULTILNDISC]
			,0.00 as [MULTILNPERCENT]
			,' ' as [OPERATIONTYPE_MX]
			,0.00 as [OVERDELIVERYPCT]
			,' ' as [PALLETTAGGING]
			,' ' as [PDSCALCULATIONID]
			,0.00 as [PDSCWINVENTRECEIVEDNOW]
			,0 as [PDSCWQTY]
			,0.00 as [PDSCWREMAININVENTFINANCIAL]
			,0  as [PDSCWREMAININVENTPHYSICAL]
			,' ' as [PORT]
			,0.00 as [PRICEUNIT]
			,' ' as [PROJCATEGORYID]
			,' ' as [PROJID]
			,' ' as [PROJLINEPROPERTYID]
			,' ' as [PROJSALESCURRENCYID]
			,0.00 as [PROJSALESPRICE]
			,' ' as [PROJSALESUNITID]
			,' ' as [PROJTAXGROUPID]
			,' ' as [PROJTAXITEMGROUPID]
			,' ' as [PPROJTRANSID]
			,' ' as [PSARETAINSCHEDULEID]
			,0.00 as [PSATOTALRETAINAMOUNT]
			,' ' as [PURCHASETYPE]
			,0.00 as [PURCHMARKUP]
			,0 as [PURCHRECEIVEDNOW]
			,' ' as [PURCHREQID]
			,' ' as [PURCHSTATUS]
			,' ' as [PURCHUNIT]
			,0 as [RBOPACKAGELINENUM]
			,0 as [REMAINDER]
			,' ' as [REQATTENTION]
			,' ' as [REQPLANIDSCHED]
			,' ' as [REQPOID]
			,0 as [RETAILLINENUMEX1]
			,' ' as [RETAILPACKAGEID]
			,0.00 as [RETAILTEMPVALUEEX2]
			,' ' as [RETURNACTIONID]
			,' ' as [RETURNDISPOSITIONCODEID]
			,' ' as [RETURNSTATUS]
			,' ' as [ROWID]
			,' ' as [SCRAP]
			,' ' as [SEARCHKEY]
			,' ' as [SERVICEADDRESS]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SERVICEDATE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [SHIPPINGDATECONFIRMED]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SHIPPINGDATEREQUESTED]
			,' ' as [SKIPUPDATE]
			,0.00 as [STATISTICVALUE_LT]
			,' ' as [STATPROCID]
			,' ' as [STATTRIANGULARDEAL]
			,' ' as [STOCKEDPRODUCT]
			,' ' as [SYSTEMENTRYSOURCE]
			,0.00 as [TAX1099AMOUNT]
			,' '  as [TAX1099BOX]
			,' ' as [TAX1099STATE]
			,' ' as [TAXGROUP]
			,0.00 as [TAX1099STATEAMOUNT]
			,' ' as [TAX1099TYPE]
			,' ' as [TAXAUTOGENERATED]
			,' ' as [TAXITEMGROUP]
			, ' ' as [TAXSERVICECODE_BR]
			,0.00 as [TAXWITHHOLDBASECUR_TH]
			,0.00 as [TAXWITHHOLDGROUP_TH]
			,0.00 as [TAXWITHHOLDITEMGROUP_TH]
			,' ' as [TRANSACTIONCODE]
			,' ' as [TRANSFERSTATUS]
			,' ' as [TRANSPORT]
			,0.00 as [UNDERDELIVERYPCT]
			,' ' as [VARIANTID]
			,' ' as [VENDGROUP]
			,' ' as [WMSLOCATIONID]
			,' ' as [WMSPALLETID]
			,' ' as [WORKFLOWSTATE]
			,' ' as [RECVERSION]
			,0   as [PARTITION]



FROM OPENQUERY  
      (KBM400MFG,

	 ' SELECT    
				 b.PELINE 
	            ,b.PENO	            
				,b.PECSDT
				,c.POCATG
				,b.PEPN
				,c.POCO
				,d.POCOM1
				,c.POCOST
				,c.POQTY
				,c.POVEND
				,b.peco
				
				



	 FROM  [KBM400MFG].[FLPODET] B  
	      LEFT OUTER JOIN [IND-SL-35RKF12].[KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO] 
	      LEFT OUTER JOIN  [IND-SL-35RKF12].[KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO] 
  
  '
   ) AS A 
 
 		  inner join [DAXCSO3SQL01].[DmStagingVD].[dbo].[DMFPRODUCTENTITY]  E on  E.[IMPN] = a.[PEPN] and E.[IMCO] = a.[PECO]
             where  [IMCO] in(01,03,05,08)

Open in new window

0
Leogal
Asked:
Leogal
2 Solutions
 
John_VidmarCommented:
Four-part notation is [server].[database].[owner].[object], you will need the brackets if there are special characters in any name part.  If the owner is the default-schema then you may leave it out but you still need the period, i.e., [server].[database]..[object]

In your example, there are 3 parts, and the first part is server, so something is wrong... there is no way you can have a 3-part name with server.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You are referencing the object wrong. If you have linked server you just need the linked server name and not the server name. Here's how your SELECT should looks like (deleted the references for first server "[IND-SL-35RKF12]"):
SELECT    
                          b.PELINE 
                   ,b.PENO                  
                         ,b.PECSDT
                         ,c.POCATG
                         ,b.PEPN
                         ,c.POCO
                         ,d.POCOM1
                         ,c.POCOST
                         ,c.POQTY
                         ,c.POVEND
                         ,b.peco
        FROM  [KBM400MFG].[FLPODET] B  
             LEFT OUTER JOIN [KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO] 
             LEFT OUTER JOIN  [KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO] 

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now