Linked server performance

Ask a question

Monday, February 18, 2013 10:20 PM

0

Hi,

I need some advise

IT have set up a new Server 2008 for our unit in a virtual environment, with Linked objects back to a "Remote Server 2008

All objects appear as Views in our server.

I was concerned about possible performance issues, IT assured us that if we have any slowing then they would address the sharing of memory etc..

A paticular query is run once every 6 mths, the first time on the new linked server the query ran in 2 min (Great), 6mths later it has slowed to a point where it is not worth the effort.

So i went to the Remote server and ran the query and it ran in 2min.

I contact IT and advised them, they asked to look at the query.

The answer is

I've had a good look at the script, and the problem is that its not processing most of it on the remote server - its pulling back the data to this server and then processing it, which is why its so slow.

In order to make it do more processing on the remote server - its easiest to put that part of the query into an in-line subquery (which is filtered so it only returns what is necessary), and then link that subquery to the local tables at the last step. This is so that it brings as little as possible back from the remote server.

Ok; I dont think this answers my problem, as we have a large amount of queries that are migarted to this new server and are not built to take into account this type of senario.

this may be a fault of mine for not identifying this, is there other factors that could be at work here or??
thanks for any advise

David
- Reply
- Quote

All Replies

Tuesday, February 19, 2013 1:41 AM

0

If I understand your post correcty, you run queries from one server agasint data on another server.

In such case, it may be better to use OPENQUERY to guaranteen that the query is executed on the remote server, and you could stick this into the view.

Note also that there is a gotcha with statistics when you run query on a linked eerver: if you don't have sufficient permissions on the remote server (db_ddladmin reqiured), you will not be able to retrieve statistics, why you can get a very bad plan. (This problem has been addressed in SQl 2012 SP1.)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Reply
- Quote
Tuesday, February 19, 2013 2:31 AM

0

Hi Erland,

can you explain a little more about OPENQUERY, do I run this from our link server are there privilages i need to assign etc..or do you have an example

thanks
David
- Reply
- Quote
Tuesday, February 19, 2013 2:38 AM

0

Hi Erland,

can you explain a little more about OPENQUERY, do I run this from our link server are there privilages i need to assign etc..or do you have an example

thanks

David

See

http://msdn.microsoft.com/en-us/library/ms188427.aspx

Definitely you do need privileges to the relevant tables/objects inside the remote server.
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Tuesday, February 19, 2013 3:35 AM

0
HI HUA,

Sorry i am a little confused.

I have under the view container in my database, all of the linked objects from the remote server.

the name HHT is the LINK.

So i have a query as a test
```
Select * from OPENQUERY ( HHT,'Select * from dbo.table' )
```
So am i going back to the Remote server through the Link objects and processing on the remote server or am i still processing on my link server

Also can you process temp tables through the OPENQUERY

i hope i have explained this

David
- Edited by KIWI DAVE Tuesday, February 19, 2013 3:37 AM change
- Reply
- Quote
Tuesday, February 19, 2013 3:39 AM

0
Quote:
- You can add the users to the role db_ddladmin, but since this gives them right to add and drop tables, this is not recommendable.
- By default, when a users connect to a remote server they connect as themselves, but you can set up a login mapping with sp_addlinkedsrvlogin, so that users map to a proxy account that has membership in db_ddladmin. Note that this proxy account must be an SQL login, so this is not an option if the remote server does not have SQL authentication enabled. This solution too is somewhat dubious from a security perspective, although its better the previous suggestion.
- In some cases you can rewrite the query with OPENQUERY to force evaluation on the remote server. This can be particularly useful, if the query includes several remote tables. (But it can also backfire, because the optimizer now gets even less statistics information from the remote server.)
- You could of course use the full battery of hints and plan guides to get the plan you want.
- Finally, you should ask yourself whether that linked-server access is needed. Maybe the databases could be on the same server? Could data be replicated? Some other solution?
from:http://www.sommarskog.se/query-plan-mysteries.html#linkedservers
Tks Gavin Dong
- Marked As Answer by KIWI DAVE Wednesday, February 20, 2013 4:21 AM
- Reply
- Quote
Tuesday, February 19, 2013 3:43 AM

0

Hi Gavin,

No we have to stick with the LINK server senario.

I now wonder how much you can do in a OPENQUERY, i have tried writing a script that has a case statement inside and it is erroring out.

not sure
David
- Reply
- Quote
Tuesday, February 19, 2013 4:00 AM

0
HI HUA,
Also can you process temp tables through the OPENQUERY
i hope i have explained this

David

I think it is possible for doing this. Or you can directly put your process inside the remote server and only to pass the results to your current server.

Many Thanks & Best Regards, Hua Min
- Edited by HuaMin ChenMicrosoft Community Contributor Tuesday, February 19, 2013 4:00 AM
- Reply
- Quote
Tuesday, February 19, 2013 6:15 AM

0

I now wonder how much you can do in a OPENQUERY, i have tried writing a script that has a case statement inside and it is erroring out.

OPENQUERY permits you to run a pass-through query which is executed entierly on the remote data source. From the verbal-only description of that you have given of your situation, it seems that it could be a fit.

If you get errors, we can't help you to sort them out, if we don't see the code and the error message.

BuDong0000: thanks for quoting my article. I had forgotten that I had that text in there. I need to update it for SQL 2012 SP1.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Reply
- Quote
Tuesday, February 19, 2013 8:33 AM

0
Hi Erland

Thanks for your reply

I am home, so tomorrow i will look at posting the script.

I know i am still going on a bit, but the following is still confusing me.

Sorry i am a little confused.

I have under the view container in my database, all of the linked objects from the remote server.

the name HHT is the Name of the LINK.

So i have a query as a test
```
Select * from OPENQUERY ( HHT,'Select * from dbo.table' )
```
My Question here is:
So is the OPENQUERY going to the Remote server through the Link objects on my database and processing on the remote server or am i still processing on my link server?

Am i making sense?

David
- Reply
- Quote
Tuesday, February 19, 2013 8:51 AM

0
Hi Erland

Thanks for your reply

I am home, so tomorrow i will look at posting the script.

I know i am still going on a bit, but the following is still confusing me.

Sorry i am a little confused.

I have under the view container in my database, all of the linked objects from the remote server.

the name HHT is the Name of the LINK.

So i have a query as a test
```
Select * from OPENQUERY ( HHT,'Select * from dbo.table' )
```
My Question here is:
So is the OPENQUERY going to the Remote server through the Link objects on my database and processing on the remote server or am i still processing on my link server?

Am i making sense?

David
David,

It is to send one request to remote DB server and have one data retrieval done remotely on the linked server, and further pass back the data to your current DB server.
Many Thanks & Best Regards, Hua Min
- Marked As Answer by KIWI DAVE Wednesday, February 20, 2013 4:21 AM
- Reply
- Quote
Tuesday, February 19, 2013 3:19 PM

0
The query 'Select * from dbo.table' runs on the remote server. Then rhe result set is proceess locally, and specifically if you would have:

SELECT ...
FROM   OPENQUERRY (HHT, 'SELECT * FROM dbo.table1) AS a
JOIN   OPENQUERRY (HHT, 'SELECT * FROM dbo.table2) AS b ON ...

The join is performed locally. Thus, it is important that you feed OPENQUERY the total query.

If you run something like:

SELECT ...
FROM   HHT.dn.dbo.table1 a
JOIN   HHT.dn.dbo.table2 b ON ...

The optimizeer may decide to remote the entire operation, but if statistics are incorrect (because of the perrmission problem), or there are contructs in the query that precludes remoting, it will not happen. Thus to be safr, you would best write:

SELECT *
FROM   OPENQUERY(HHT, 'SELECT ...
   FROM   HHT.dn.dbo.table1 a
   JOIN   HHT.dn.dbo.table2 b ON ...')

In the examples I used JOIN, but the same applies to WHERE clauses. If you have:

Select * from OPENQUERY ( HHT,'Select * from dbo.table' )
WHERE col = 9

The filtering is performed locally. That is, you should have the full query inside OPENQUERY to be safe.

Or stop using the linked server, becauuse as you see, it can be difficult to manage and be sure of getting decent performance.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Marked As Answer by KIWI DAVE Wednesday, February 20, 2013 4:21 AM
- Reply
- Quote
Wednesday, February 20, 2013 12:53 AM

0
Thanks Erland

Fantastic stuff,

Previous i said i was getting an error, this is when i try to copy and paste code into the 'SELECT * FROM dbo.table1' part of the OPENQUERY

Here is the sample, i can see where it falls over CASE WHEN DimReference.ReferenceDescription = 'Did Not Attend' but not sure on the fix

SELECT * FROM OPENQUERY(HCPW,'SELECT     DimLocation.HospitalCode, DimPatient.URNumber, FactWaitingList.WaitingListId, FactWaitingList.ReferralId, FactWaitingList.AppointmentByDateTime,
                      FactWaitingList.OperatingHospitalName, FactWaitingList.TotalWaitingDays,
                      CASE WHEN DimReference.ReferenceDescription = 'Did Not Attend' THEN DATEDIFF(day, FactWaitingList.WaitingStartDateTime,
                      FactOutpatientsAppointment.AppointmentStartDateTime) ELSE '' END AS 'DNAnumberDay',
                      FactWaitingList.PreviousSuspTotalDays, FactWaitingList.CurrentSuspTotalDays,
                      FactWaitingList.WaitingStartDateTime, FactOutpatientsAppointment.AppointmentStartDateTime, DimReference.ReferenceDescription,
                      DimLocation.LocationDescription
FROM         FactWaitingList INNER JOIN
                      DimLocation ON FactWaitingList.AppointmentClinicLocationId = DimLocation.LocationId INNER JOIN
                      DimPatient ON FactWaitingList.PatientId = DimPatient.PatientId LEFT OUTER JOIN
                      FactOutpatientsAppointment ON FactWaitingList.PatientId = FactOutpatientsAppointment.PatientId AND
                      FactWaitingList.AppointmentClinicLocationId = FactOutpatientsAppointment.AppointmentClinicLocationId AND
                      FactOutpatientsAppointment.AppointmentStartDateTime >= FactWaitingList.WaitingStartDateTime LEFT OUTER JOIN
                      DimReference ON FactOutpatientsAppointment.AppointmentOutcomeRefId = DimReference.ReferenceId AND
                      DimReference.ReferenceDescription = 'Did Not Attend'
WHERE     (FactWaitingList.WaitingListType = 'OutpatientService') AND (FactWaitingList.RemovalDateTime IS NULL) AND (DimLocation.HospitalCode = '2101') AND
                      (FactWaitingList.ArchiveFlag = 0) AND
                      (FactWaitingList.WaitingStartDateTime >= '2007-07-01 00:00:00.000')'
                      -- AND (DimLocation.LocationDescription = 'Neurosurgery')AND (DimReference.ReferenceDescription = 'Did Not Attend')'

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near 'Did'.

David
- Edited by KIWI DAVE Wednesday, February 20, 2013 12:58 AM update
- Marked As Answer by KIWI DAVE Wednesday, February 20, 2013 4:20 AM
- Unmarked As Answer by KIWI DAVE Wednesday, February 20, 2013 4:21 AM
- Reply
- Quote
Wednesday, February 20, 2013 2:53 AM

0

Try

SELECT * FROM OPENQUERY(HCPW,'SELECT     DimLocation.HospitalCode, DimPatient.URNumber, FactWaitingList.WaitingListId, FactWaitingList.ReferralId, FactWaitingList.AppointmentByDateTime,
                      FactWaitingList.OperatingHospitalName, FactWaitingList.TotalWaitingDays,
                      CASE WHEN DimReference.ReferenceDescription = ''Did Not Attend'' THEN DATEDIFF(day, FactWaitingList.WaitingStartDateTime,
                      FactOutpatientsAppointment.AppointmentStartDateTime) ELSE '''' END AS ''DNAnumberDay'',
                      FactWaitingList.PreviousSuspTotalDays, FactWaitingList.CurrentSuspTotalDays,
                      FactWaitingList.WaitingStartDateTime, FactOutpatientsAppointment.AppointmentStartDateTime, DimReference.ReferenceDescription,
                      DimLocation.LocationDescription
FROM         FactWaitingList INNER JOIN
                      DimLocation ON FactWaitingList.AppointmentClinicLocationId = DimLocation.LocationId INNER JOIN
                      DimPatient ON FactWaitingList.PatientId = DimPatient.PatientId LEFT OUTER JOIN
                      FactOutpatientsAppointment ON FactWaitingList.PatientId = FactOutpatientsAppointment.PatientId AND
                      FactWaitingList.AppointmentClinicLocationId = FactOutpatientsAppointment.AppointmentClinicLocationId AND
                      FactOutpatientsAppointment.AppointmentStartDateTime >= FactWaitingList.WaitingStartDateTime LEFT OUTER JOIN
                      DimReference ON FactOutpatientsAppointment.AppointmentOutcomeRefId = DimReference.ReferenceId AND
                      DimReference.ReferenceDescription = ''Did Not Attend''
WHERE     (FactWaitingList.WaitingListType = ''OutpatientService'') AND (FactWaitingList.RemovalDateTime IS NULL) AND (DimLocation.HospitalCode = ''2101'') AND
                      (FactWaitingList.ArchiveFlag = 0) AND
                      (FactWaitingList.WaitingStartDateTime >= ''2007-07-01 00:00:00.000'')'
                      -- AND (DimLocation.LocationDescription = 'Neurosurgery')AND (DimReference.ReferenceDescription = 'Did Not Attend')'
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Wednesday, February 20, 2013 4:44 AM

0

HI,

I have tried to add a #tempTable in this senario

i get a error msg, i am not sure what this actually means, is this simply not accepting the temptable or is there another issue with string length as this temptable runs fine.

thanks

Msg 103, Level 15, State 1, Line 6

The character string that starts with 'CREATE TABLE #stg_AccountMapforHRT$(

[Code] [nvarchar](255) NULL,

[Code Description] [nvarchar](255) NULL,

[Financial Class' is too long. Maximum length is 8000.

David
- Reply
- Quote
Wednesday, February 20, 2013 5:00 AM

0

HI,

I have tried to add a #tempTable in this senario

i get a error msg, i am not sure what this actually means, is this simply not accepting the temptable or is there another issue with string length as this temptable runs fine.

thanks

Msg 103, Level 15, State 1, Line 6

The character string that starts with 'CREATE TABLE #stg_AccountMapforHRT$(

[Code] [nvarchar](255) NULL,

[Code Description] [nvarchar](255) NULL,

[Financial Class' is too long. Maximum length is 8000.

David

It means you're updating the column with a too long string.
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Wednesday, February 20, 2013 5:08 AM

0

Hi,

I have removed the temptables, but now i am getting the same error on a different column and this is only a varchar(8)

Msg 103, Level 15, State 1, Line 6

The character string that starts with 'SELECT

DISTINCT (PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK), PIC.view_FactInPatientEpisode1.HospitalCo' is too long. Maximum length is 8000.

David
- Reply
- Quote
Wednesday, February 20, 2013 5:15 AM

0

I think your script for Openquery is too long. Try this
SELECT * FROM OPENQUERY(HCPW,'SELECT     DimLocation.HospitalCode, DimPatient.URNumber, FactWaitingList.WaitingListId, FactWaitingList.ReferralId, FactWaitingList.AppointmentByDateTime,'+
                      'FactWaitingList.OperatingHospitalName, FactWaitingList.TotalWaitingDays,'+
                      'CASE WHEN DimReference.ReferenceDescription = ''Did Not Attend'' THEN DATEDIFF(day, FactWaitingList.WaitingStartDateTime,'+
                      ...
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Wednesday, February 20, 2013 5:35 AM

0

Ok,

I checked the word count, i have 11000 characters, must be that,

Handy to know there is a limit.

I will try breaking it up.

thanks

:)
David
- Reply
- Quote
Wednesday, February 20, 2013 6:15 AM

0

Hi,

sorry for the extra bother,

I cannot get this to work when i add '+

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near '+'.

David
- Reply
- Quote
Wednesday, February 20, 2013 6:28 AM

0

Sorry, try this

SELECT * FROM OPENQUERY(HCPW,'SELECT DimLocation.HospitalCode, DimPatient.URNumber, FactWaitingList.WaitingListId, FactWaitingList.ReferralId, FactWaitingList.AppointmentByDateTime,
FactWaitingList.OperatingHospitalName, FactWaitingList.TotalWaitingDays,
CASE WHEN DimReference.ReferenceDescription = ''Did Not Attend'' THEN DATEDIFF(day, FactWaitingList.WaitingStartDateTime,
FactOutpatientsAppointment.AppointmentStartDateTime) ELSE '''' END AS ''DNAnumberDay'',
FactWaitingList.PreviousSuspTotalDays, FactWaitingList.CurrentSuspTotalDays,
FactWaitingList.WaitingStartDateTime, FactOutpatientsAppointment.AppointmentStartDateTime, DimReference.ReferenceDescription,
DimLocation.LocationDescription
FROM FactWaitingList INNER JOIN
DimLocation ON FactWaitingList.AppointmentClinicLocationId = DimLocation.LocationId INNER JOIN
DimPatient ON FactWaitingList.PatientId = DimPatient.PatientId LEFT OUTER JOIN
FactOutpatientsAppointment ON FactWaitingList.PatientId = FactOutpatientsAppointment.PatientId AND
FactWaitingList.AppointmentClinicLocationId = FactOutpatientsAppointment.AppointmentClinicLocationId AND
FactOutpatientsAppointment.AppointmentStartDateTime >= FactWaitingList.WaitingStartDateTime LEFT OUTER JOIN
DimReference ON FactOutpatientsAppointment.AppointmentOutcomeRefId = DimReference.ReferenceId AND
DimReference.ReferenceDescription = ''Did Not Attend''
WHERE (FactWaitingList.WaitingListType = ''OutpatientService'') AND (FactWaitingList.RemovalDateTime IS NULL) AND (DimLocation.HospitalCode = ''2101'') AND
(FactWaitingList.ArchiveFlag = 0) AND
(FactWaitingList.WaitingStartDateTime >= ''2007-07-01 00:00:00.000'')')
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Wednesday, February 20, 2013 9:55 AM

0

Hi Hua

thanks for this,

Sorry,

I dont see anywhere in this script a indicatiation of how to resolve the error, can i actually have a script greater than 8000 charcters?

thanks

I cannot get this to work when i add '+

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near '+'.

David
- Reply
- Quote
Wednesday, February 20, 2013 4:37 PM

0

When I attempt the code in Hua Min's post, I get

OLE DB provider "SQLNCLI11" for linked server "SONNYBOY\KATMAI" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'FactWaitingList'.

Which tells me that the syntax is correct. (And then it fails, because I don't have the table.)

You are running Hua Min's query from a query window, don't you? Don't use the view designer; it's useless.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Reply
- Quote
Wednesday, February 20, 2013 10:44 PM

0

Hi Erland

Thanks for all your input.

Just to go back

What i did was run Hua's query, and yes it runs fine, fantastic increase in speed.

What i did then was take the sql from a procedure and try that, the problem is that i got this error, which indicated that OPENQUERY has a limit of characters that can be passed.

Msg 103, Level 15, State 1, Line 6

The character string that starts with 'SELECT

DISTINCT (PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK), PIC.view_FactInPatientEpisode1.HospitalCo' is too long. Maximum length is 8000.

So Hua offered the idea of concating the scritp using a +' and when i tried that i got another error (incorrect sytax) so i am wondering how do you deal with scripts larger than 8000 characters, or is there another problem

thanks

David
- Reply
- Quote
Thursday, February 21, 2013 1:09 AM

0

Hi David,
Can you copy and paste this query below to retry it? Are you sure you have got the over-length error with these codes?
SELECT * FROM OPENQUERY(HCPW,'SELECT DimLocation.HospitalCode, DimPatient.URNumber, FactWaitingList.WaitingListId, FactWaitingList.ReferralId, FactWaitingList.AppointmentByDateTime,
FactWaitingList.OperatingHospitalName, FactWaitingList.TotalWaitingDays,
CASE WHEN DimReference.ReferenceDescription = ''Did Not Attend'' THEN DATEDIFF(day, FactWaitingList.WaitingStartDateTime,
FactOutpatientsAppointment.AppointmentStartDateTime) ELSE '''' END AS ''DNAnumberDay'',
FactWaitingList.PreviousSuspTotalDays, FactWaitingList.CurrentSuspTotalDays,
FactWaitingList.WaitingStartDateTime, FactOutpatientsAppointment.AppointmentStartDateTime, DimReference.ReferenceDescription,
DimLocation.LocationDescription
FROM FactWaitingList INNER JOIN
DimLocation ON FactWaitingList.AppointmentClinicLocationId = DimLocation.LocationId INNER JOIN
DimPatient ON FactWaitingList.PatientId = DimPatient.PatientId LEFT OUTER JOIN
FactOutpatientsAppointment ON FactWaitingList.PatientId = FactOutpatientsAppointment.PatientId AND
FactWaitingList.AppointmentClinicLocationId = FactOutpatientsAppointment.AppointmentClinicLocationId AND
FactOutpatientsAppointment.AppointmentStartDateTime >= FactWaitingList.WaitingStartDateTime LEFT OUTER JOIN
DimReference ON FactOutpatientsAppointment.AppointmentOutcomeRefId = DimReference.ReferenceId AND
DimReference.ReferenceDescription = ''Did Not Attend''
WHERE (FactWaitingList.WaitingListType = ''OutpatientService'') AND (FactWaitingList.RemovalDateTime IS NULL) AND (DimLocation.HospitalCode = ''2101'') AND
(FactWaitingList.ArchiveFlag = 0) AND
(FactWaitingList.WaitingStartDateTime >= ''2007-07-01 00:00:00.000'')')
Many Thanks & Best Regards, Hua Min
- Reply
- Quote

Thursday, February 21, 2013 1:25 AM

Hi Hua

this script is fine and runs like a dream,

The next test i did was take another script

the following and this script is larger than 8000 characters, and it gives the error

Msg 103, Level 15, State 1, Line 6

The character string that starts with 'SELECT

DISTINCT (PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK), PIC.view_FactInPatientEpisode1.HospitalCo' is too long. Maximum length is 8000.

SELECT 
-- --Convert(Varchar (40),DateDiff(Minute,PIC.view_TheatreCase.IntoTheatreDateTime,PIC.view_TheatreCase.SurgeryEndDateTime)%(24*60)/60)As [Procedure Hour],
 --Difference between miniutes DateDiff(mi,DateADD(hh,DateDiff(hh,PIC.view_TheatreCase.IntoTheatreDateTime, PIC.view_TheatreCase.SurgeryEndDateTime),PIC.view_TheatreCase.IntoTheatreDateTime), PIC.view_TheatreCase.SurgeryEndDateTime)As [ProcMin],
               DISTINCT (PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK), PIC.view_FactInPatientEpisode1.HospitalCode AS [Hospital Identifier], 
                   RIGHT('0'+CAST(900000 + Row_Number() Over (Order by PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK  ASC) as varchar),7) AS 'Sequence Number',
                      PIC.view_FactInPatientEpisode1.MRN AS [Unit Record Number], 
                      PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK AS [Episode Number], DimRefAdmType.NatAdmUrgStatus AS [Admission Type], '' AS [Emergency Event ID],
                      PIC.DimRefAdmSource.NatMap AS [Source of Referal],
                      Convert(Varchar,PIC.view_FactInPatientEpisode1.ADT,103) AS [Admission Date], 
                      PIC.view_FactInPatientEpisode1.AdmHour AS [Admission Hour], Convert(Varchar,PIC.view_FactInPatientEpisode1.SDT,103) AS [Seperation Date], 
                      PIC.view_FactInPatientEpisode1.DisHour AS [Seperation Hour],
                      Convert(Varchar,PIC.view_FactInPatientEpisode1.DateOfBirth,103) AS [Birth Date], 
                      PIC.view_FactInPatientEpisode1.AGEY AS [Age in Years on Admission Date],
                      Case When AGEY < 1 Then DateDiff(d,PIC.view_FactInPatientEpisode1.DateOfBirth, PIC.view_FactInPatientEpisode1.ADT) 
							Else NULL
								End AS [Age in days], 
					  PIC.view_FactInPatientEpisode1.AdmW AS [Admission Weight], ''[Neonatal linkage to Mothers Episode],
                      PIC.view_FactInPatientEpisode1.Gender AS [Gender], IntendLOS.ReferenceDescription As [Intended LOS], PIC.view_FactInPatientEpisode1.HMV AS [Hours of Mechanical Ventilation], 
                      PIC.view_FactInPatientEpisode1.cLOS AS [Acute LOS], PIC.view_FactInPatientEpisode1.IcuLosInMins / 60 AS [ICU Hours], '' AS [Hospital in the Home], PIC.DimRefMHLSMap.MHLSMode AS [Mental Health Legal Status],
                      PIC.view_FactInPatientEpisode1.LVD AS [Leave Days], '' AS [Admission Diagnosis], 
                      PIC.view_FactInPatientEpisode1.DX1 AS [Principal Diagnosis], 
                      PIC.view_FactInPatientEpisode1.DX2, PIC.view_FactInPatientEpisode1.DX3, PIC.view_FactInPatientEpisode1.DX4, 
                      PIC.view_FactInPatientEpisode1.DX5, PIC.view_FactInPatientEpisode1.DX6, PIC.view_FactInPatientEpisode1.DX7, 
                      PIC.view_FactInPatientEpisode1.DX8, PIC.view_FactInPatientEpisode1.DX9, PIC.view_FactInPatientEpisode1.DX10, 
                      PIC.view_FactInPatientEpisode1.DX11, PIC.view_FactInPatientEpisode1.DX12, PIC.view_FactInPatientEpisode1.DX13, 
                      PIC.view_FactInPatientEpisode1.DX14, PIC.view_FactInPatientEpisode1.DX15, PIC.view_FactInPatientEpisode1.DX16, 
                      PIC.view_FactInPatientEpisode1.DX17, PIC.view_FactInPatientEpisode1.DX18, PIC.view_FactInPatientEpisode1.DX19, 
                      PIC.view_FactInPatientEpisode1.DX20, PIC.view_FactInPatientEpisode1.DX21, PIC.view_FactInPatientEpisode1.DX22, 
                      PIC.view_FactInPatientEpisode1.DX23, PIC.view_FactInPatientEpisode1.DX24, PIC.view_FactInPatientEpisode1.DX25, 
                      PIC.view_FactInPatientEpisode1.DX26, PIC.view_FactInPatientEpisode1.DX27, PIC.view_FactInPatientEpisode1.DX28, 
                      PIC.view_FactInPatientEpisode1.DX29, PIC.view_FactInPatientEpisode1.DX30, PIC.view_FactInPatientEpisode1.DX31, 
                      PIC.view_FactInPatientEpisode1.DX32, PIC.view_FactInPatientEpisode1.DX33, PIC.view_FactInPatientEpisode1.DX34, 
                      PIC.view_FactInPatientEpisode1.DX35, PIC.view_FactInPatientEpisode1.DX36, PIC.view_FactInPatientEpisode1.DX37, 
                      PIC.view_FactInPatientEpisode1.DX38, PIC.view_FactInPatientEpisode1.DX39, PIC.view_FactInPatientEpisode1.DX40, 
                      PIC.view_FactInPatientEpisode1.DX41, PIC.view_FactInPatientEpisode1.DX42, PIC.view_FactInPatientEpisode1.DX43, 
                      PIC.view_FactInPatientEpisode1.DX44, PIC.view_FactInPatientEpisode1.DX45, PIC.view_FactInPatientEpisode1.DX46, 
                      PIC.view_FactInPatientEpisode1.DX47, PIC.view_FactInPatientEpisode1.DX48, PIC.view_FactInPatientEpisode1.DX49, 
                      PIC.view_FactInPatientEpisode1.DX50, PIC.view_FactInPatientEpisode1.PROC1 AS [Principal Procedure], PIC.view_FactInPatientEpisode1.PROC2, 
                      PIC.view_FactInPatientEpisode1.PROC3, PIC.view_FactInPatientEpisode1.PROC4, PIC.view_FactInPatientEpisode1.PROC5, 
                      PIC.view_FactInPatientEpisode1.PROC6, PIC.view_FactInPatientEpisode1.PROC7, PIC.view_FactInPatientEpisode1.PROC8, 
                      PIC.view_FactInPatientEpisode1.PROC9, PIC.view_FactInPatientEpisode1.PROC10, PIC.view_FactInPatientEpisode1.PROC11, 
                      PIC.view_FactInPatientEpisode1.PROC12, PIC.view_FactInPatientEpisode1.PROC13, PIC.view_FactInPatientEpisode1.PROC14, 
                      PIC.view_FactInPatientEpisode1.PROC15, PIC.view_FactInPatientEpisode1.PROC16, PIC.view_FactInPatientEpisode1.PROC17, 
                      PIC.view_FactInPatientEpisode1.PROC18, PIC.view_FactInPatientEpisode1.PROC19, PIC.view_FactInPatientEpisode1.PROC20, 
                      PIC.view_FactInPatientEpisode1.PROC21, PIC.view_FactInPatientEpisode1.PROC22, PIC.view_FactInPatientEpisode1.PROC23, 
                      PIC.view_FactInPatientEpisode1.PROC24, PIC.view_FactInPatientEpisode1.PROC25, PIC.view_FactInPatientEpisode1.PROC26, 
                      PIC.view_FactInPatientEpisode1.PROC27, PIC.view_FactInPatientEpisode1.PROC28, PIC.view_FactInPatientEpisode1.PROC29, 
                      PIC.view_FactInPatientEpisode1.PROC30, PIC.DimRefCareTypeMap.CareType AS [Care Type], 
                      PIC.DimRefSepMap.NatSepMode AS [Seperation Mode], PIC.DenseRankDiagProc.code AS [DRG Assigned by Hospital], 
                     '06' AS [Facility region identifier], 
						  StatLocal.SLA AS [Area of Residence], 
						  BIU.stg_AccountMapforHRT.[Financial Class] AS [Financial Class], 
						 Convert(DateTime,Convert(Char(10),BIU.stg_FirstSurgHRT.SurgeryStartDateTime,103),103) AS [Procedure Date],
							DatePart(hh,BIU.stg_FirstSurgHRT.SurgeryStartDateTime) AS [Procedure Hour],
							Convert(Varchar,DateDiff(SS,BIU.stg_FirstSurgHRT.IntoTheatreDateTime, BIU.stg_FirstSurgHRT.SurgeryEndDateTime)%(24*3600)/60) AS [Procedure Time for prinicpal procedure],
										EthnicOrigin.ReferenceIdentifier AS [Ethnic Origin],
								PIC.view_FactInPatientEpisode1.DisWard,
							ClinicalSubUnitID.MainIdentifier AS [Clinical Subunit Identifier],
                      Convert(Varchar,PIC.view_FactInPatientEpisode1.DateOfDeath,103) AS [Date of Death],
                     Convert(DATETIME,Convert(Char(10),PIC.view_FactInPatientEpisode1.SDT,103),103)as [FilterDate],
                     DischargeUnitDesc.MainIdentifier As [Discharge Unit],DischargeUnitDesc.SpecialtyDescription as [Discharge Unit Desc]  
--INTO 
--BIU.stg_HRTCaseMix							
FROM         PIC.view_FactInPatientEpisode1 
					  INNER JOIN
                      dbo.FactInpatientEpisode ON PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK = dbo.FactInpatientEpisode.InpatientEpisodeId 
                             INNER JOIN BIU.stg_DimRefAdmTypeHRT as DimRefAdmType ON dbo.FactInpatientEpisode.AdmissionTypeRefId = DimRefAdmType.HIQAdmType
								  INNER JOIN
										  dbo.DimPatient ON dbo.FactInpatientEpisode.PatientId = dbo.DimPatient.PatientId 
												  INNER JOIN
												  PIC.DimRefSepMap ON PIC.view_FactInPatientEpisode1.DisCode = PIC.DimRefSepMap.SepModeHIQ 
												  INNER JOIN
													PIC.DimRefCareTypeMap ON PIC.view_FactInPatientEpisode1.AdmCareType = PIC.DimRefCareTypeMap.[HlthIQRefIdentifier] 
														INNER JOIN PIC.DimRefMHLSMap ON PIC.view_FactInPatientEpisode1.MHLS = PIC.DimRefMHLSMap.HlthMHLS	  
												  INNER JOIN
												  PIC.DenseRankDiagProc ON PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK = PIC.DenseRankDiagProc.inpatientepisodeid 
									  INNER JOIN
									  dbo.DimLocation ON dbo.FactInpatientEpisode.DischargeLocationId = dbo.DimLocation.LocationId 
									INNER JOIN dbo.DimSpecialty AS DischargeUnitDesc ON dbo.FactInpatientEpisode.DischargeSpecialtyId = DischargeUnitDesc.SpecialtyId
						INNER JOIN			
						  dbo.DimCarer AS ClinicalSubUnitID ON dbo.FactInpatientEpisode.DischargeDoctorCarerId = ClinicalSubUnitID.CarerId
			LEFT OUTER JOIN
					BIU.stg_FirstSurgHRT ON PIC.view_FactInPatientEpisode1.TheatreIdFk = BIU.stg_FirstSurgHRT.OpNumber
		INNER JOIN dbo.DimReference As BirthCountry ON dbo.DimPatient.BirthCountryRefId = BirthCountry.ReferenceId
		
   INNER JOIN dbo.DimReference AS IntendLOS ON dbo.FactInpatientEpisode.AdmissionManagementIntentionRefId = IntendLOS.ReferenceId
INNER JOIN PIC.DimRefAdmSource ON dbo.FactInpatientEpisode.AdmissionSourceRefId = PIC.DimRefAdmSource.ReferenceId
 INNER JOIN dbo.DimReference AS EthnicOrigin ON dbo.DimPatient.EthnicStatusRefId = EthnicOrigin.ReferenceId
	INNER JOIN BIU.stg_AccountMapforHRT ON PIC.view_FactInPatientEpisode1.AdmClass = BIU.stg_AccountMapforHRT.Code
		LEFT OUTER JOIN dbo.StatisticalLocalArea AS StatLocal ON dbo.DimPatient.HomeAddressSuburb = StatLocal.SLALocality AND 
                    StatLocal.SLAPostcode = dbo.DimPatient.HomeAddressPostCode
 
			WHERE		
				(PIC.DenseRankDiagProc.NewCodeOrder = 1) AND (PIC.DenseRankDiagProc.codesystem = 'DRG6X') AND 
				 PIC.view_FactInPatientEpisode1.SDT Between '2012-07-01 00:00:00.000' AND '2012-12-31 23:59:59.997' and 
				  PIC.view_FactInPatientEpisode1.HospitalCode ='2101'

David

Thursday, February 21, 2013 1:47 AM

0

To deal with this over-length error, you have to trim your script like
SELECT
-- --Convert(Varchar (40),DateDiff(Minute,PIC.view_TheatreCase.IntoTheatreDateTime,PIC.view_TheatreCase.SurgeryEndDateTime)%(24*60)/60)As [Procedure Hour],
--Difference between miniutes DateDiff(mi,DateADD(hh,DateDiff(hh,PIC.view_TheatreCase.IntoTheatreDateTime, PIC.view_TheatreCase.SurgeryEndDateTime),PIC.view_TheatreCase.IntoTheatreDateTime), PIC.view_TheatreCase.SurgeryEndDateTime)As [ProcMin],
DISTINCT (PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK), PIC.view_FactInPatientEpisode1.HospitalCode AS [Hospital Identifier],
RIGHT('0'+CAST(900000 + Row_Number() Over (Order by PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK ASC) as varchar),7) AS 'Sequence Number',
PIC.view_FactInPatientEpisode1.MRN AS [Unit Record Number],
PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK AS [Episode Number], DimRefAdmType.NatAdmUrgStatus AS [Admission Type], '' AS [Emergency Event ID],
PIC.DimRefAdmSource.NatMap AS [Source of Referal],
Convert(Varchar,PIC.view_FactInPatientEpisode1.ADT,103) AS [Admission Date],
PIC.view_FactInPatientEpisode1.AdmHour AS [Admission Hour], Convert(Varchar,PIC.view_FactInPatientEpisode1.SDT,103) AS [Seperation Date],
PIC.view_FactInPatientEpisode1.DisHour AS [Seperation Hour],
Convert(Varchar,PIC.view_FactInPatientEpisode1.DateOfBirth,103) AS [Birth Date],
PIC.view_FactInPatientEpisode1.AGEY AS [Age in Years on Admission Date],
Case When AGEY < 1 Then DateDiff(d,PIC.view_FactInPatientEpisode1.DateOfBirth, PIC.view_FactInPatientEpisode1.ADT)
Else NULL
End AS [Age in days],
PIC.view_FactInPatientEpisode1.AdmW AS [Admission Weight], ''[Neonatal linkage to Mothers Episode],
PIC.view_FactInPatientEpisode1.Gender AS [Gender], IntendLOS.ReferenceDescription As [Intended LOS], PIC.view_FactInPatientEpisode1.HMV AS [Hours of Mechanical Ventilation],
PIC.view_FactInPatientEpisode1.cLOS AS [Acute LOS], PIC.view_FactInPatientEpisode1.IcuLosInMins / 60 AS [ICU Hours], '' AS [Hospital in the Home], PIC.DimRefMHLSMap.MHLSMode AS [Mental Health Legal Status],
PIC.view_FactInPatientEpisode1.LVD AS [Leave Days], '' AS [Admission Diagnosis],
PIC.view_FactInPatientEpisode1.DX1 AS [Principal Diagnosis],
PIC.view_FactInPatientEpisode1.DX2, PIC.view_FactInPatientEpisode1.DX3, PIC.view_FactInPatientEpisode1.DX4,
PIC.view_FactInPatientEpisode1.DX5, PIC.view_FactInPatientEpisode1.DX6, PIC.view_FactInPatientEpisode1.DX7,
PIC.view_FactInPatientEpisode1.DX8, PIC.view_FactInPatientEpisode1.DX9, PIC.view_FactInPatientEpisode1.DX10,
PIC.view_FactInPatientEpisode1.DX11, PIC.view_FactInPatientEpisode1.DX12, PIC.view_FactInPatientEpisode1.DX13,
PIC.view_FactInPatientEpisode1.DX14, PIC.view_FactInPatientEpisode1.DX15, PIC.view_FactInPatientEpisode1.DX16,
PIC.view_FactInPatientEpisode1.DX17, PIC.view_FactInPatientEpisode1.DX18, PIC.view_FactInPatientEpisode1.DX19,
PIC.view_FactInPatientEpisode1.DX20, PIC.view_FactInPatientEpisode1.DX21, PIC.view_FactInPatientEpisode1.DX22,
PIC.view_FactInPatientEpisode1.DX23, PIC.view_FactInPatientEpisode1.DX24, PIC.view_FactInPatientEpisode1.DX25,
PIC.view_FactInPatientEpisode1.DX26, PIC.view_FactInPatientEpisode1.DX27, PIC.view_FactInPatientEpisode1.DX28,
PIC.view_FactInPatientEpisode1.DX29, PIC.view_FactInPatientEpisode1.DX30, PIC.view_FactInPatientEpisode1.DX31,
PIC.view_FactInPatientEpisode1.DX32, PIC.view_FactInPatientEpisode1.DX33, PIC.view_FactInPatientEpisode1.DX34,
PIC.view_FactInPatientEpisode1.DX35, PIC.view_FactInPatientEpisode1.DX36, PIC.view_FactInPatientEpisode1.DX37,
PIC.view_FactInPatientEpisode1.DX38, PIC.view_FactInPatientEpisode1.DX39, PIC.view_FactInPatientEpisode1.DX40,
PIC.view_FactInPatientEpisode1.DX41, PIC.view_FactInPatientEpisode1.DX42, PIC.view_FactInPatientEpisode1.DX43,
PIC.view_FactInPatientEpisode1.DX44, PIC.view_FactInPatientEpisode1.DX45, PIC.view_FactInPatientEpisode1.DX46,
PIC.view_FactInPatientEpisode1.DX47, PIC.view_FactInPatientEpisode1.DX48, PIC.view_FactInPatientEpisode1.DX49,
PIC.view_FactInPatientEpisode1.DX50, PIC.view_FactInPatientEpisode1.PROC1 AS [Principal Procedure], PIC.view_FactInPatientEpisode1.PROC2,
PIC.view_FactInPatientEpisode1.PROC3, PIC.view_FactInPatientEpisode1.PROC4, PIC.view_FactInPatientEpisode1.PROC5,
PIC.view_FactInPatientEpisode1.PROC6, PIC.view_FactInPatientEpisode1.PROC7, PIC.view_FactInPatientEpisode1.PROC8,
PIC.view_FactInPatientEpisode1.PROC9, PIC.view_FactInPatientEpisode1.PROC10, PIC.view_FactInPatientEpisode1.PROC11,
PIC.view_FactInPatientEpisode1.PROC12, PIC.view_FactInPatientEpisode1.PROC13, PIC.view_FactInPatientEpisode1.PROC14,
PIC.view_FactInPatientEpisode1.PROC15, PIC.view_FactInPatientEpisode1.PROC16, PIC.view_FactInPatientEpisode1.PROC17,
PIC.view_FactInPatientEpisode1.PROC18, PIC.view_FactInPatientEpisode1.PROC19, PIC.view_FactInPatientEpisode1.PROC20,
PIC.view_FactInPatientEpisode1.PROC21, PIC.view_FactInPatientEpisode1.PROC22, PIC.view_FactInPatientEpisode1.PROC23,
PIC.view_FactInPatientEpisode1.PROC24, PIC.view_FactInPatientEpisode1.PROC25, PIC.view_FactInPatientEpisode1.PROC26,
PIC.view_FactInPatientEpisode1.PROC27, PIC.view_FactInPatientEpisode1.PROC28, PIC.view_FactInPatientEpisode1.PROC29,
PIC.view_FactInPatientEpisode1.PROC30, PIC.DimRefCareTypeMap.CareType AS [Care Type],
PIC.DimRefSepMap.NatSepMode AS [Seperation Mode], PIC.DenseRankDiagProc.code AS [DRG Assigned by Hospital],
'06' AS [Facility region identifier],
StatLocal.SLA AS [Area of Residence],
BIU.stg_AccountMapforHRT.[Financial Class] AS [Financial Class],
Convert(DateTime,Convert(Char(10),BIU.stg_FirstSurgHRT.SurgeryStartDateTime,103),103) AS [Procedure Date],
DatePart(hh,BIU.stg_FirstSurgHRT.SurgeryStartDateTime) AS [Procedure Hour],
Convert(Varchar,DateDiff(SS,BIU.stg_FirstSurgHRT.IntoTheatreDateTime, BIU.stg_FirstSurgHRT.SurgeryEndDateTime)%(24*3600)/60) AS [Procedure Time for prinicpal procedure],
EthnicOrigin.ReferenceIdentifier AS [Ethnic Origin],
PIC.view_FactInPatientEpisode1.DisWard,
ClinicalSubUnitID.MainIdentifier AS [Clinical Subunit Identifier],
Convert(Varchar,PIC.view_FactInPatientEpisode1.DateOfDeath,103) AS [Date of Death],
Convert(DATETIME,Convert(Char(10),PIC.view_FactInPatientEpisode1.SDT,103),103)as [FilterDate],
DischargeUnitDesc.MainIdentifier As [Discharge Unit],DischargeUnitDesc.SpecialtyDescription as [Discharge Unit Desc]
--INTO
--BIU.stg_HRTCaseMix
FROM PIC.view_FactInPatientEpisode1
INNER JOIN
dbo.FactInpatientEpisode ON PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK = dbo.FactInpatientEpisode.InpatientEpisodeId
INNER JOIN BIU.stg_DimRefAdmTypeHRT as DimRefAdmType ON dbo.FactInpatientEpisode.AdmissionTypeRefId = DimRefAdmType.HIQAdmType
INNER JOIN
dbo.DimPatient ON dbo.FactInpatientEpisode.PatientId = dbo.DimPatient.PatientId
INNER JOIN
PIC.DimRefSepMap ON PIC.view_FactInPatientEpisode1.DisCode = PIC.DimRefSepMap.SepModeHIQ
INNER JOIN
PIC.DimRefCareTypeMap ON PIC.view_FactInPatientEpisode1.AdmCareType = PIC.DimRefCareTypeMap.[HlthIQRefIdentifier]
INNER JOIN PIC.DimRefMHLSMap ON PIC.view_FactInPatientEpisode1.MHLS = PIC.DimRefMHLSMap.HlthMHLS
INNER JOIN
PIC.DenseRankDiagProc ON PIC.view_FactInPatientEpisode1.InPatientEpisodeIdFK = PIC.DenseRankDiagProc.inpatientepisodeid
INNER JOIN
dbo.DimLocation ON dbo.FactInpatientEpisode.DischargeLocationId = dbo.DimLocation.LocationId
INNER JOIN dbo.DimSpecialty AS DischargeUnitDesc ON dbo.FactInpatientEpisode.DischargeSpecialtyId = DischargeUnitDesc.SpecialtyId
INNER JOIN
dbo.DimCarer AS ClinicalSubUnitID ON dbo.FactInpatientEpisode.DischargeDoctorCarerId = ClinicalSubUnitID.CarerId
LEFT OUTER JOIN
BIU.stg_FirstSurgHRT ON PIC.view_FactInPatientEpisode1.TheatreIdFk = BIU.stg_FirstSurgHRT.OpNumber
INNER JOIN dbo.DimReference As BirthCountry ON dbo.DimPatient.BirthCountryRefId = BirthCountry.ReferenceId
INNER JOIN dbo.DimReference AS IntendLOS ON dbo.FactInpatientEpisode.AdmissionManagementIntentionRefId = IntendLOS.ReferenceId
INNER JOIN PIC.DimRefAdmSource ON dbo.FactInpatientEpisode.AdmissionSourceRefId = PIC.DimRefAdmSource.ReferenceId
INNER JOIN dbo.DimReference AS EthnicOrigin ON dbo.DimPatient.EthnicStatusRefId = EthnicOrigin.ReferenceId
INNER JOIN BIU.stg_AccountMapforHRT ON PIC.view_FactInPatientEpisode1.AdmClass = BIU.stg_AccountMapforHRT.Code
LEFT OUTER JOIN dbo.StatisticalLocalArea AS StatLocal ON dbo.DimPatient.HomeAddressSuburb = StatLocal.SLALocality AND
StatLocal.SLAPostcode = dbo.DimPatient.HomeAddressPostCode
WHERE
(PIC.DenseRankDiagProc.NewCodeOrder = 1) AND (PIC.DenseRankDiagProc.codesystem = 'DRG6X') AND
PIC.view_FactInPatientEpisode1.SDT Between '2012-07-01 00:00:00.000' AND '2012-12-31 23:59:59.997' and
PIC.view_FactInPatientEpisode1.HospitalCode ='2101'

to further put it into openquery. I also want to suggest you define views in the remote DB instead of using the big script above to resolve this over-length error.
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Thursday, February 21, 2013 1:51 AM

0

Hi Hua,

I can not add views to the Remote server,

In the case of the script being too large then i may have to have part of the script as a INLINE

Thanks so much for all the effort

Cheers

David
- Reply
- Quote
Thursday, February 21, 2013 10:17 PM

0

Seeing your queries, I sense that you have a political problem - it's completely crazy that you would run this query over a linked server, all against remote tables. I just can't envision what wouldd drive that arrangemnet.

True, it is none of my business, but there may bee better solutions. For instance, replicating the data to your local server. If nothing else, your evil queries will not load that other serve.

In the meanwhile, I have one more trick up the sleece. You can run the query this way:

EXEC('SELECT * FROM thl WHERE col = ?', 65) AT REMOTESVR

That is you wrao the query in EXEC() as with dynamic SQL, but you use AT to specify the server. In difference to OPENQUERY, you are not limited to 8000 characters. And for extra bells and whistles, you can specify parameters. The ? is a parameter holder and 65 in the example is the parameter value. However, you cannot stick this in a view; it would have to be a stored procedure.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Reply
- Quote
Thursday, February 21, 2013 10:44 PM

0

Hi Erland,

Thanks i will try this,

Just so you know.

Yes we would like to run these on the remote server, in fact we would like to stay working on the remote server but there is political problems.

We did want a complete dump of all objects to our server everynight rather than linking, but IT advised this was to large a task to complete so we went with linking and building tables.

So we may link to the remote server but these large queries are run in procedures overnight and Insert data into a local table, this gives us the speed and optimisation to run data out to users, however we are going to be running more and more so i need to increase the runtime to fit more jobs into the run over night.

anyway, if we did not have challenges, we would learn very little, and let me tell you Hospital Data is full of poor data quality.

David
- Reply
- Quote