SQL Pass through multiple connections

sameer112217 — Mon, 20 May 2019 08:26:32 GMT

Hello I am running a query in SQL. I have to use multiple pass through in the Proc SQL and the code inside the SQL passthrough is the code of SQL. The issue is there are two tables from two different database.

Let me know how can I do it here

LIBNAME web_srv SQLSVR Datasrc=webservices SCHEMA=dbo USER=sasproduction
PASSWORD="***

LIBNAME sqlsas SQLSVR Datasrc=WebServices SCHEMA=dbo USER=sasproduction
PASSWORD=**** ;

Below is the query in SQL

Select
MOT.Policyno as PolicyNo,
POLS.PolicyStatus as PolicyStatus
,case when MOT.PolicyEndDate > GETDATE() then 'Y' Else 'N' end
POLICY_ACTIVE_INACTIVE_FLG
,MOT.SMCode as SMCode
--,PR.Channel M200_Channel
--,PR.SM_Name M200_SalesManagerName
,MOT.ProductCode ProductCode
,MOT.PolicyStartDate as PolicyStartDate
,MOT.PolicyEndDate as PolicyEndDate
,MOT.AgentCode AgentCode
,MOT.BasCode BasCode
,isnull(PR.Branch_Code,substring(mot.policyno,1,4)) Branch_Code
,MOT.SumInsured SumInsured
--,PR.NET_PREMIUM M400_NetPremium
,MOT.netOD+MOT.netTP as TotalPremium
,DATEDIFF(YY,MOT.PolicyStartDate,MOT.PolicyEndDate) policyTenure
,PR.PAYMENT_MODE as PaymentMode
,POLS.PolicyStatus as CANCELLED_FLAG
,null as EndorsementCnt
from
webservices..tbl_motor MOT with(nolock)
inner join sqlsas..CMDM_Product_Master PM WITH (nolock) on MOT.ProductCode =PM.Product_code

Now I want to run the above SQL query in pass through but pass through has single statement. I want it from two different databases in SQL..please help

Re: SQL Pass through multiple connections

Kurt_Bremser — Mon, 20 May 2019 11:16:45 GMT

You can do that, you must add the database name to the table qualifiers:

https://stackoverflow.com/questions/11426929/can-we-use-join-for-two-different-database-tables

Note this is the first result of a google search for "ms sql server join tables from different databases". See Maxim 6, Google Is Your Friend.

topic Re: SQL Pass through multiple connections in New SAS User

SQL Pass through multiple connections

Re: SQL Pass through multiple connections