asked on

Embed OPENROWSET in CTE expression

Hi,

I have a stored procedure which select some records with a CTE expression. I have been doing the developement on a test server, but the real server is actually remote and I need to use OPENROWSET.

It is a MS 2005 SQL Server.

I am not sure how to write the query

Here is the SP:

GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/08/2011 10:59:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
	@InputGUID as uniqueidentifier,
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime


declare @maxStop datetime
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate



CREATE TABLE #tmtBalance
(Park_GSRN varchar(50))


;with CTE 
as
(
	
	SELECT
	Park_GSRN,
	ProductionDate,
	DiscontinuedDate,
	StopProductionDate,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN) LEFT JOIN Vendor ON tmtB.SupplierID=Vendor.id
	where ((ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
						and 
                        (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
                        and
                        (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
                        and
	Startdate <= @InputDate and vendorGUID = @InputGUID
	and (StopDate >= @InputDate or StopDate is null or 
	@InputDate >= @maxStop --entire subquery replaced here
))
)
INSERT INTO #tmtBalance
select Park_GSRN from CTE A
where A.RN = 1
order by A.Park_GSRN

DROP TABLE #tmtBalance

Open in new window

Qlemo

Why are you assuming you need OpenRowSet?
Which part is remote?
You are creating a temporary table, filling it with the CTE result, and then drop it - no further processing of the results, so that snippet doesn't make much sense.

Madsing

ASKER

The tables tmtB, Customer and Vendor is placed on a remote server. The above snippet work on my test server, where I created the 3 tables while I am developing but not gonna work in live mode...

Qlemo

And you can't create the procedure on the target machine, and just call it remotely?

Madsing

ASKER

No unfortunately not. A policy issue in the corporation.

Qlemo

Instead of using OpenRowSet, you can just use a Linked Server. You would need it anyway, and after you have set it up in Management Studio, you can use syntax like (only showing a snippet):

...
select ...
from ((rmtsrv.rmtdb.rmtschema.customer customer left join rmtsrv.rmtdb.rmtschema.tmpB tmpB on ...
...

Open in new window

But that looks rather clumsy. IMO better to create synonyms:

create synonym Customer for rmtsrv.rmtdb.rmtschema.Customer;
create synonym tmpB for rmtsrv.rmtdb.rmtschema.tmpB;
create synonym Vendor or rmtsrv.rmtdb.rmtschema.Vendor;

Open in new window

allow to just use the table names like you did, without further changes. But of course the tables may not exist in your local schema.

Madsing

ASKER

Why would I need it anyway? I would very much like if a solution could be done in OPENROWSET.

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Madsing

ASKER

Great Qlemo, I will get back to you. Thank you very much for the detail explanation so far!

Best Regards