Order Of Inner Joins And Cross Apply

Sep 29, 2015

Will the order of inner joins and cross apply effect the results of a query?

Example:
FROM dbo.vw_Info v
CROSS APPLY dbo.usf_LastFee(v.StoreID, v.AgreementID, v.CustomerID, ABS(v.PrePaymentBalance), cp.ConfirmationNo) lf
INNER JOIN dbo.Customers c

[Code] ....

I want to change the position of doing "CROSS APPLY". Will it effects query results?

View 2 Replies


ADVERTISEMENT

CROSS APPLY Vs OUTER APPLY Example Messed Up?

Nov 27, 2007

Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.

On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:




Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END



and then run a sample T-SQL statement





Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc

My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?

When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.

Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.

So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?

(Please refrain from introducing another example into this question.)

View 8 Replies View Related

Cross Apply

May 22, 2008

What is Cross Apply, when it will be used ?

View 2 Replies View Related

Cross Apply And Newid

Apr 28, 2008

Hi,

Why am I getting a different numbers of distinct ids in those queries?


USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';

select locations.*, T.c.value('.','nvarchar(max)') from
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
) as locations cross apply @myXml.nodes('(/lol)') T(c);

select mytable.* , T.c.value('.','nvarchar(max)') from
(
select newid() as Id
union
select newid()
) as mytable cross apply @myXml.nodes('(/lol)') T(c);


Thanks,

Victor

View 8 Replies View Related

CROSS APPLY Equivalent

Apr 29, 2008

I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?



I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.





SELECT *

FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att





what I need is to join a function and passing it a dynamic parameter.



I need it urgently





Thanks in advance,

Imad Elayyan

View 1 Replies View Related

CROSS APPLY Equivalent

Apr 29, 2008



I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?

I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.



SELECT *

FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att



what I need is to join a function and passing it a dynamic parameter.

I need it urgently


Thanks in advance,
Imad Elayyan

View 6 Replies View Related

CROSS APPLY Sp_addextendedproperty

Nov 7, 2007

I am trying to add Extended properties to each of the columns in my table('MyTable')to the columns

DECLARE @NewTableName as nvarchar(128);

SET @NewTableName = 'MyTable'


SELECT ColumnName FROM(

SELECT c.name as ColumnName

FROM syscolumns c

INNER JOIN sysobjects o

ON o.id = c.id

WHERE o.name = @NewTableName) as T

OUTER APPLY

sp_addextendedproperty( 'Caption', ColumnName,





'user', 'dbo',

'table', @NewTableName,

'column', ColumnName)


I am getting an invalid object name 'sp_addextendedproperty' error.


Jaime

View 3 Replies View Related

Cross Apply And Newid

Apr 28, 2008

I've been trying to figure out why these two return a different amount of distinct ids...
Is that a bug in optimization?




Code Snippet


USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';

WITH locations as
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
)
select locations.*, T.c.value('.','nvarchar(max)') from locations cross apply @myXml.nodes('(/lol)') T(c);

with mytable as
(
select newid() as Id
union
select newid()
)
select mytable.* , T.c.value('.','nvarchar(max)') from mytable cross apply @myXml.nodes('(/lol)') T(c);

View 4 Replies View Related

Multiple Cross Apply

Nov 14, 2006

Good Afternoon,

I'm attempting to leverage SQL's new 'APPLY" operator and I guess I don't fully understand it proper usage.

This is a relatively simple request, first i want to count the models produced within a valid period of time. The first 'Cross Apply' gets the valid starting and ending dates and looks ups the number of models produced for the period of time. This section of code works perfectly fine.

The problem appears to be with the second "Cross Apply".  What I'm attempting to accomplish is to count all models produced, regardless of time frame.

When executed the query appears to go into an loop and I end up canceling out the request.

Any ideas where I went wrong?? Any help is greatly appreciated!

 

select b1.model                            as Model 
      ,b1.MinDate                            as Mfg_Str_Date
      ,b1.MaxDate                           as Mfg_End_Date
      ,Count(b2.Model+B2.Serial) as Mfg_Date_Valid
      ,Count(b3.Model+B3.Serial) as All_Units

from        (select b.model, min(b.build_date) as MinDate ,max(b.build_date) as MaxDate
             from etbl_models_Serial as b
             group by b.model) as b1

--These are Units produced within Valid Window
cross apply (select b2.model,b2.Serial
             from etbl_Production as b2
             where b2.Model = b1.Model
               and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2

--These are all units produced
cross apply (select b3.model,b3.Serial
             from etbl_Production as b3
             where b3.Model = b2.Model) as b3
      
Group by b1.Model, b1.MinDate, b1.MaxDate
Order by b1.Model

View 4 Replies View Related

Cross Apply Issue (Last Call) :)

May 28, 2008

I can't figure out what can be causing this.

When I use this query

Select top 1000 a.EmployeeID,b.*
from #TmpActiveEmployeesWSeverance a
cross apply
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID


It runs 4 seconds

If I try to insert the results into anything It runs > 5 minutes (I have yet to let it finish)

I have tried the two following pieces of code, both with the same results


Select top 1000 a.EmployeeID,b.*
into #Tmp
from #TmpActiveEmployeesWSeverance a
cross apply
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID

--and
Insert Into TRP_ActiveEmployeesWSeverance
(EmployeeID
,PK
,BeginningBalance
,BenefitInterestRowID
,BenefitInterestID
,BenefitTypeID
,DateReceived
,InvoiceDate
,Amount
,Hours
,Fraction1
,Fraction2
,Interest
,InterestAmount
,StartDate
,EndDate
,PeriodApplied
,Offset
,Reserve
,Account
,BenefitClosedID
,PaidOut
,ClosedAccount
,ai
,ClosedDate
,StartAgain
,PartialDividend
,PartialFraction
,SameDateCount)
Select top 1000 a.EmployeeID,b.*
from #TmpActiveEmployeesWSeverance a
cross apply
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID


Any thoughts as to what can be disrupting this?

View 5 Replies View Related

Error When Using XML Nodes As Cross Apply

Nov 12, 2012

 I have this query:

SELECT res.res_id,
sub.value('(./@id)[1]', 'char(2)') id
FROM vwResult res
CROSS APPLY report.nodes('/clue_personal_auto/report/search_dataset/subjects/*') report(sub)

It works just fine in SQL Query.After placing this into a view in SSDT November 2012 update, I get a compilation error.

View 11 Replies View Related

Using CROSS APPLY - Possibilities For Resolution

Apr 29, 2008



Hello,

is it possible to use CROSS APPLY like this:



SELECT [x]

FROM [Order] CROSS APPLY (SELECT .OrderNo + 3) [x]



i know you need [x].* but is it possible to use a function so that only [x] is needed?

thx in advance for help
MattGo

View 3 Replies View Related

Count Slowness Using CROSS APPLY

May 13, 2008



Hello,

I am doing a report that uses paging and in order to optimize it, i used row_number() so i could make it return x rows per page, so, in order to compute the number of pages needed, i have to count the total number of rows, which gets very slow because i'm using a cross apply with a table-valued function. Is there any way so i can get the number of rows processed by row_number() so i dont have the need to do count?

Thanks in advance !

View 3 Replies View Related

Problem With Cross Apply Query

Nov 10, 2006

Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time/execution_count DESC;

View 3 Replies View Related

Transact SQL :: Replace Group By With CTE And / Or Cross Apply

Jul 10, 2015

If I Have a table like

Id(identity), PupilPersonId, EducationTypeId,VehicleTypeId,EducationDate, EducatorId,Canceled
661187       9242382         2                       1                2015-07-07 00:00:00.000 O_2 False
661183       9242382         2                       1            2015-07-08 00:00:00.000 O_2 False
661186       9242382         1                       1                2015-07-08 00:00:00.000 O_2 False
661178       9242382         2                       1                2015-07-10 00:00:00.000 O_2 False
661185       9242382         2                       1                2015-07-10 00:00:00.000 O_2 False

The result I want is the unique rows from columns:  

PupilPersonId, EducationTypeId,VehicleTypeId AND there MAX EducationDate
SELECT er1.* FROM EducationResult er1
INNER JOIN
(
SELECT
er.PupilPersonId, er.EducationTypeId, er.VehicleTypeId, MAX(er.EducationDate) as EducationDate

[Code] ....

I like to know is there another approach with CTE and or Cross Apply I can use instead?

View 5 Replies View Related

Find Nearest Date Record Without Cross Apply

Oct 13, 2012

Table :

ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal

Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.

SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date

The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.

View 1 Replies View Related

SQL Server 2012 :: Using Cross Apply To UNPIVOT Data

Jan 15, 2014

I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....

CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,

[Code] ....

The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this

-- DDL and sample data for UNPIVOT Example 2
CREATE TABLE #Suppliers
(ID INT, Product VARCHAR(500)
,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500)
,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))

Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)

View 7 Replies View Related

T-SQL (SS2K8) :: String Occurrence Count With Cross Apply

Jun 17, 2014

See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.

DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))

INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Cross Apply With Parameterized Function?

Sep 9, 2015

I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.

The error is: Incorrect syntax near '.'

the query in question resembles this:

Select column1, column2
from Table1 T
cross apply function(t.column4,t.column5) F
where column3 = 'XXXX'

I made sure that the compatibility level is greater than 90 this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)

Can it be the .net framework? Although the machines had .net framework 3.52.

Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2

I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.

View 9 Replies View Related

T-SQL (SS2K8) :: Query Using Multiple APPLY Joins

Mar 20, 2014

I’ve never written a query with multiple APPLY joins before and I’m running into some troubles with my first one. The below SQL statement runs within 10 seconds if I comment out either one of the APPLY joins and its corresponding field columns. However, when I try to execute with both APPLY joins, the query runs indefinitely. The longest I’ve waited before cancelling it is 90 minutes.

Now, I know there are probably other ways I could write this query to get me the results I’m looking for. I’m posting this on the board because I’m curious about finding out why multiple APPLY joins could cause SQL Server to run away. I’m hoping to gain some insight so that I can better understand how APPLY joins work so that in case I have a big need to do this again in the future (without suitable workarounds) I can code it correctly.

Here are some things I’ve tried so far…

1.Changed the States table into a subquery that only returns a single state
2.Change all the references inside the APPLY subqueries so that they had different aliases (just in case they were conflicting with each other).
3.Changed the CROSS applies to OUTER applies. States has 50 records and only 32 have matching permit data so the 18 extra iterations using OUTER APPLY don’t impact performance any when an APPLY is used by itself.

SELECT s.state_name
, COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits
, SUM(DistinctPermits) AS DistinctPermits
FROM States S
CROSS APPLY (SELECT w.StateID, COUNT(*) as DistinctPermits

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: How To Vary Column Names In Cross Apply Based On Different Columns In Each Table

Feb 26, 2015

I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:

select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank

[code]....

Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:

GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)

[Code] ....

These are imported from an Excel Workbook so that's why all the columns with spaces for now.

View 9 Replies View Related

SQL Server 2012 :: CROSS APPLY Returning Records From Left Recordset Even When No Matching Record In Right One

Oct 7, 2014

Following is the query that I'm running:

create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);

select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;

Below is the result for the same:

idname sal
1John500
1ken500
2paulNULL

Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.

One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.

View 6 Replies View Related

Application Roles For Cross-Database Joins

Aug 25, 2005

I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases is via stored procs or views, no access to tables directly.Anyone know of a solution? Here is the error I get:Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel

View 4 Replies View Related

Outer Joiins And Cross Joins Not Working

Jun 17, 2007

full outer joins and cross joins not working!?!?


am using vc++2005, ADO, and MSAccess 2003. MS "documentation" straight out of the VC++2005 help facility at

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_vdt01/html/419ef633-5a89-41a2-aefe-03540afc9112.htm

provided the following code samples for different types of joins

inner join



Code:
SELECT title, pub_name
FROM titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id left join



Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles LEFT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idright join



Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idfull join



Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles FULL OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idjoin


Code:
SELECT *
FROM authors CROSS JOIN publishers i created two MSAccess Tables:

Merge1:

K1 x
---- ----
a 1
b 2
c 3

Merge2:

K1 x
---- ----
b 20
c 30
d 40
e 50

and executed the following code to test the different joins. the first three joins worked but the last two did not. would appreciate any insight. DBM is an instance of an AccessDBManager class i have written to encapsulate interactions with Access DBs.


Code:
DBM.SQLExtractString = "select * from Merge1 INNER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 2 records returned (K1 = b,c)



Code:DBM.SQLExtractString = "select * from Merge1 LEFT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 3 records returned (K1 = a,b,c)



Code:DBM.SQLExtractString = "select * from Merge1 RIGHT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 4 records returned (K1 = b,c,d,e)



Code:DBM.SQLExtractString = "select * from Merge1 FULL OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 5 (K1 should = a,b,c,d,e)



Code:DBM.SQLExtractString = "select * from Merge1 CROSS JOIN Merge2";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 20 (5 * 4)

appreciate any ideas/comments to get the last two joins to work.

thanks - j

View 3 Replies View Related

Analysis :: Cross Joins Across User Defined Hierarchies Aren't Supported

Sep 20, 2011

I have some confusion on crossjoin function within MDx.while I try to crossjoin the different level sets of same Hierarchy. It shows error as

For example.
‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’
select {
{[Customer].[Customer Geography].[Country].&[United States]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?Coz I really need to implement as above MDx within my real Cube.I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.with

member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]
select {
{[Customer].[Country].[United States ]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

View 11 Replies View Related

DB Engine :: Linked Server - Getting Error When Performing Cross Instance Query With Joins

Apr 26, 2015

I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?

Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.

View 3 Replies View Related

Order Of Joins

Jan 5, 2007

i have below sql with several joins. Under sql 2000 should it be assumed that sql will create the best plan for however you set the joins or is there an order when using left joins to follow

select p.account_id, p.sex, p.other_id_number,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
pe.create_timestamp as enc_date, loc.location_name, l.ngn_status, l.sign_off_person,
l.sign_off_date,
ltrim(rtrim(upper(pr.first_name))) + ' ' + ltrim(rtrim(upper(pr.last_name))) as signoff_name,
ltrim(rtrim(upper(prv.first_name))) + ' ' + ltrim(rtrim(upper(prv.last_name))) as prov_name,
r.req_accession, l.ufo_num, r.spec_rcv_date_time, r.date_time_reported,
r.test_desc, x.abnorm_flags, x.obs_id, x.result_desc, x.ref_range, x.units,
x.observ_value, c.comment_text
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join patient_encounter pe on pe.account_id = p.account_id
inner join location loc on loc.location_key = pe.location_id
inner join lab_nor l on l.enc_id = pe.enc_id
inner join provider prv on prv.provider_id = l.ordering_provider
left outer join profile pr on pr.user_id = l.sign_off_person
inner join p_lab_results_obr r on r.ngn_order_num = l.order_num
inner join i_lab_results_obx x on x.unique_obr_num = r.unique_obr_num
left outer join i_lab_results_comm c on c.unique_obr_num = r.unique_obr_num
and c.obr_seq_num = r.seq_num
and c.obx_seq_num = x.obx_seq_num

View 2 Replies View Related

Order Of Joins

Jun 2, 2008

Hi -

I have a general question about the order of joins. I have noticed that in some of the queries I am looking at, there are lots of LEFT and RIGHT Joins one after the other. My question is which table is considered the LEFT or RIGHT or original table if there are 9 or so joins after it?



For example, if a query says
select ORD.orderID, ORD.CustomerID, EMP.employeeID, SOX.AugitID
FROM Dbo.employees EMP
INNER JOIN
dbo.orders ORD
On
emp.employeeid=ORD.employeeid
LEFT OUTER JOIN
dbo.Customers CUST
ON CUST.CustomerID=ORD.CustomerID
RIGHT OUTER JOIN
SOX.Audit.ID
ON ORD.OrderID = SOX.Audit.ID

the query INNER JOINS Employee and Orders table, but for the LEFT and RIGHT joins, is the Original FROM dbo.Employees EMP always considered the original table from which a LEFT JOIN is done, and if so is the RIGHT OUTER JOIN, the SOX table, taking the JOIN on the original FROM Table FROM Dbo.employees EMP, or from the table preceding the RIGHT OUTER JOIN, the Customers table. This is a simple example, but these queries have one table after another for about 9 - 15 joins (RIGHT, LEFT, etc.). Whatever applies to the simple query above, I will take to the more complex ones.

Thanks -

View 8 Replies View Related

Order Of Joins And Performance

Jul 23, 2005

Does the order in which Joins are peformed (from left to right) matterwhether inner or outer when it comes to performance. Assuming that allindexes are the same but the size of the tables is different?For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3,TABLE4, TABLE5.For example:SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1INNER JOIN TABLE3 on Condition2LEFT JOIN TABLE4 on Condition3INNER JOIN TABLE5 on Condition4.Does SQL optimize the query and finds the best way to inner join or itstarts doing the JOINS from left to right?Thank you for your your feedback.Gent

View 4 Replies View Related

Order Of Tables In Joins

Jul 20, 2005

I am writing a download process in which i have a condition where ineed to join four tables. Each table have lot of data say around300000 recs.my question is when i am doing the joins on the columns is there anyspecific order i need to follow.for exampleMy original query looks like thisselect a.col1,a.col2from ainner join bon a.id1=b.id1and a.id2=b.id2inner join con c.id1=b.id1and c.id2=b.id2inner join don d.id1=c.id1and d.id2=c.id2If i change the query like below... does it make any differenceselect a.col1,a.col2from ainner join bon b.id1=a.id1and b.id2=a.id2inner join con c.id1=a.id1and c.id2=a.id2inner join don d.id1=a.id1and d.id2=a.id2Any help is appreciated.ThanksSri

View 4 Replies View Related

Joins On Views That Are Formed With Outer Joins

Nov 3, 2000

We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.

If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.


625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.

Error 4425 text would imply that joins with view formed by outer joins should be avoided.

Any ideas on the principles involved here.

View 1 Replies View Related

JOINS To Sub-Queries -vs- JOINS To Tables

Aug 11, 2005

SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig

View 3 Replies View Related

Ansi Joins Vs. SQL Joins

Oct 12, 1999

Hi,

Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?

The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)

However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.

Angel

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved