• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1422
  • Last Modified:

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

0
Madsing
Asked:
Madsing
  • 4
  • 4
1 Solution
 
QlemoC++ DeveloperCommented:
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.
0
 
MadsingAuthor Commented:
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...
 
0
 
QlemoC++ DeveloperCommented:
And you can't create the procedure on the target machine, and just call it remotely?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
MadsingAuthor Commented:
No unfortunately not. A policy issue in the corporation.  
0
 
QlemoC++ DeveloperCommented:
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.

0
 
MadsingAuthor Commented:
Why would I need it anyway? I would very much like if a solution could be done in OPENROWSET.
0
 
QlemoC++ DeveloperCommented:
OpenRowSet is fine if you need to be dynamic (e.g. in conjunction with flat files and the Jet Driver), or just want to construct a once-in-a-while used query.

If you need to use the query more often, you always should try to use static connection info, like Linked Server. OpenRowSet requires you to provide all connection info each time, and no optimization is to be applied in any way (no caching, no choice of better execution plans).
And last but not least, SQL syntax using OpenRowSet is ugly, and difficult to maintain.
Not to forget ad-hoc queries are inhibited by default - but that doesn't matter if you are in full control of the source server.

If you want to stubbornly insist to not use linked server entries, despite all performance and security issues, I recommend to at least use OpenDataSource. OpenDataSource is used like a linked server, but allows you to provide all connection data. Since it is ad-hoc, too, the same performance and security restrictions apply as with OpenRowSet.
That would look like this:
...
select ...
from ((
opendatasource(''SQLNCLI', 'Server=rmtserver; Integrated Security=SSPI').rmtdb.rmtschema.customer customer
left join opendatasource(''SQLNCLI', 'Server=rmtserver; Integrated Security=SSPI').rmtdb.rmtschema.tmpB tmpB on ...
...

Open in new window


Having said that, OpenRowSet has one advantage over OpenDataSource: you can run the whole query remotely by putting the whole select into the OpenRowSet query argument. But since you use variables you need to construct the SQL first, including the openrowset call, and execute that as dynamic SQL. It's getting really ugly:
...
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))


declare @sql nvarchar(max)
set @sql = '
;with CTE 
as
(
    select * from OPENQUERY(''SQLNCLI'', ''Server=rmtserver; Trusted_Connection=yes;'',
    ''       
        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 <= ''' + convert(varchar, @InputDate, 120) + '''
	        and ProductionDate IS NOT NULL)
              and 
              (StopProductionDate IS NULL or StopProductionDate >= ''' + convert(varchar, @InputDate, 120) + ''')
                        and
              (DiscontinuedDate >= ''' + convert(varchar, @InputDate, 120) + ''' or DiscontinuedDate IS NULL)
                        and
        Startdate <= ''' + convert(varchar, @InputDate, 120) + ''' and vendorGUID = '''+@InputGUID+'''
        and (StopDate >= ''' + convert(varchar, @InputDate, 120) + ''' or StopDate is null or 
        ''' + convert(varchar, @InputDate, 120) + ''' >= ''' + convert(varchar, @maxStop, 120) + '''  --entire subquery replaced here
	))
     '')
)
INSERT INTO #tmtBalance
select Park_GSRN from CTE A
where A.RN = 1
order by A.Park_GSRN
'

exec (@sql)

DROP TABLE #tmtBalance

Open in new window

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

Best Regards
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now