How to use OPENQUERY to access another SQL Server

Original: How to use OPENQUERY to access another SQL Server

In a project, you often encounter a database that accesses another database, "cnvferpdb" is the server name, "CE3" is the library name

1 SELECTDtl.* 2  fromcnvferpdb. CE3.ce3.ZTLE0125 Dtl3 INNER JOINcnvferpdb. CE3.ce3.ZTLE0124 Mst4  onDtl.recvsuppno=Mst.recvsuppno andDtl.mandt=Mst.mandt5 WHEREMst.mandt= ' -'  andDtl.brandcode='MD'

The above method is directly accessed through the server name and library name, so that there are multiple connections to another server, the execution speed will be slow

You can do this in the following form, and the execution speed will be improved:

1 SELECT *  2  from OPENQUERY(cnvferpdb3     , 4     ' 5 SELECT dtl.*6 From CE3.ce3.ZTLE0125 Dtl7 INNER JOIN CE3.ce3.ZTLE0124 Mst8 On dtl.recvsuppno = mst.recvsuppno and Dtl.mandt = Mst.mandt9 WHERE Mst.mandt ="' -"'and dtl.brandcode="'MD"'Ten     '   One)

OPENQUERY (linked_server, ' query ')
Precautions for use:
Linked_server
Represents the identifier for the linked server name.
' Query '
The query string executed in the linked server.  The maximum length of the string is 8 KB.


Add

1 Declare @Day VARCHAR(Ten)=CONVERT(CHAR(8),DATEADD( Day,-1,GETDATE()), the)2 3 --Set @Day = ' 20140605 '4 5 IF EXISTS(Select *  fromApplogwhereDates=@Day)6 return7 8 Declare @sql VARCHAR( -)='SELECT *9 Into #tempTen From OpenQuery One ([CNSASPLOGDB01], A "' - Select - A.programid the ," '"+@Day+" '"As Dates - , Count (Duration) as COUNT - , SUM (CAST (Duration as DECIMAL (12,3)))/1000 as Sumtime - , AVG (CAST (Duration as DECIMAL (12,3)))/1000 as Avgtime + , MAX (CAST (Duration as DECIMAL (12,3)))/1000 as MaxTime -  from + ( A SELECT * from LogCSLK01.dbo.AppLog_'+@Day+'With (NOLOCK) at Union - SELECT * from LogCSLK02.dbo.AppLog_'+@Day+'With (NOLOCK) - Union - SELECT * from LogCSLK03.dbo.AppLog_'+@Day+'With (NOLOCK) - ) A - GROUP BY A.programid in "' - ) to  + INSERT INTO Applog - SELECT * the From #temp *  $ drop table #tempPanax Notoginseng ' - --Print @sql the  + exec(@sql)

How to use OPENQUERY to access another SQL Server