April 4, 2017 at 6:59 pm
Hi everyone,
I need to run stored procedure from Server1, located on linked Server2.
All linked servers has one SQL account, My access on Server2 is different.
SQL server administrator created mapped login on both servers, all rpc & in/out on both servers set to trues.
Stored procedure is running successfully (no errors) and returning only columns names.
Any ideas? Thank you.
April 5, 2017 at 5:54 am
Hi,
does the stored procedure creates some result if you run it on the server 2, without linked server?
And, what kind of stored procedure do you use, maybe the code can help us.
Kind regards,
Andreas
April 5, 2017 at 7:36 pm
Hello,
Yes, I can get results when I am running it on linked server under my login.
Stored procedure code: below. The problem is it's working under my account, For linked servers it's another account
created by our company. Mapping does not work. Any ideas how I can run it?
Thank you.
USE [dobipub01]
GO
/****** Object: StoredProcedure [rpt].[LARS405] Script Date: 3/04/2017 12:03:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [rpt].[LARS405_TEST]
(
@StartDate smalldatetime
,@EndDate smalldatetime
)
as
set NOCOUNT ON
declare @params VARCHAR(100)
select @params = 'Presentations from ' + CONVERT(VARCHAR(22), @StartDate, 120)
+ ' to ' + CONVERT(VARCHAR(22), @EndDate, 120)
exec obidml.sp_InsertLARS_Reportlog 'LARS405', 'CALHN_EPAS_ED_Extract',
@params
select
CONVERT(Varchar(20), Ep.episode_sk) As episode_sk
,Ep.hospital_id
,Ep.patient_mrn
,CONVERT(Varchar(20), Ep.service_id) As service_id
,dim.Patient_PAT.date_of_birth
,dim.Sex_PAT.sex
,dim.Patient_PAT.postcode
,dim.Indigenous_Status_PAT.indigenous_status
,dim.Clinical_Unit_IP.clinical_unit as Admit_Clinic
,dim.Clinical_Unit_IP.clinical_unit_stream as Admit_Stream
,dim.Ward_IP.ward_id as Admit_Ward
,Ep.presentation_dtm
,Ep.seen_dtm
,Ep.decision_to_admit_dtm
,Ep.ed_admit_dtm
,Ep.departure_dtm
,dim.Arrival_Mode_ED.arrival_mode
,dim.Departure_Referral_ED.departure_referral
,dim.Departure_Status_ED.departure_status
,Ep.diagnosis_cd
,dim.Presenting_Problem_ED.presenting_problem
,dim.Triage_Category_ED.triage_category
,visit_type
,comp.compensable_status
,Country.country_of_birth_code
,Country.country_of_birth_desc
,Ep.epas_visit_id_code
,SrcRef.source_of_referral
from obidml.OBI_FACT_ED_Episode as Ep WITH (NOLOCK)
Inner Join obidml.OBI_DIM_Hospital H On H.hospital_sk = Ep.hospital_sk
left outer join dim.Presenting_Problem_ED on Ep.present_prob_sk = dim.Presenting_Problem_ED.presenting_problem_ed_sk
left outer join dim.Visit_Type_ED on Ep.visit_type_sk = dim.Visit_Type_ED.visit_type_ed_sk
left outer join dim.Departure_Status_ED on Ep.departure_status_sk = dim.Departure_Status_ED.departure_status_ed_sk
left outer join dim.Patient_PAT on Ep.patient_sk = dim.Patient_PAT.patient_pat_sk
left outer join dim.Sex_PAT on dim.Patient_PAT.sex_pat_sk = dim.Sex_PAT.sex_pat_sk
left outer join dim.Indigenous_Status_PAT on dim.Patient_PAT.indigenous_status_pat_sk = dim.Indigenous_Status_PAT.indigenous_status_pat_sk
left outer join dim.Triage_Category_ED on Ep.triage_priority_sk = dim.Triage_Category_ED.triage_category_ed_sk
left outer join dim.Departure_Referral_ED on Ep.dept_refrl_sk = dim.Departure_Referral_ED.departure_referral_ed_sk
left outer join dim.Arrival_Mode_ED on Ep.arrival_mode_sk = dim.Arrival_Mode_ED.arrival_mode_ed_sk
left outer join dim.Ward_IP on Ep.admit_ward_sk = dim.Ward_IP.ward_ip_sk
left outer join dim.Clinical_Unit_IP on dim.Clinical_Unit_IP.clinical_unit_ip_sk = Ep.admit_unit_sk
LEFT OUTER JOIN dim.Compensable_Status_ED Comp On Comp.compensable_status_ed_sk=Ep.compensable_sts_sk
Left Outer Join dim.Country_Of_Birth_PAT Country On Country.country_of_birth_pat_sk = Patient_PAT.country_of_birth_pat_sk
Left Outer Join dim.Source_Of_Referral_ED SrcRef On Ep.source_of_ref_sk = SrcRef.source_of_referral_ed_sk
where (Ep.source_system='EPAS' And H.LHN_desc='CALHN'
)
and ( Ep.presentation_dtm between @StartDate
and @EndDate )
--and ( SUSER_NAME() in ( select report_user_id
-- from obidml.LARS_ReportUsers
-- where report_id in ( 'ALL', 'LARS405' ) ) )
April 6, 2017 at 8:50 am
As it's execution context is going to rely on the account that the Linked Server uses, maybe you need to use a dynamic linked server instead, by using OPENROWSET, and specify the Linked Server yourself instead of using the provided one.. Alternatively, the account used by the Linked Server would need the same permissions that you have, but of course, then everyone able to access that Linked Server could end up being able to do anything you could do, so that might not be a good idea... You're probably going to want to talk to a DBA...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply