SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using CTE with Parameters in OLEDB Data Source in SSDT (or why is SSDT so braindead?)


Using CTE with Parameters in OLEDB Data Source in SSDT (or why is SSDT so braindead?)

Author
Message
Scott In Sydney
Scott In Sydney
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 701
<FRUSTRATED RANT>
Another title for this post could be "Why is SSDT so braindead?" or "Does Microsoft have a clue about the needs of SSDT developers?"

It's ***software*** Microsoft - you could make this work if you invested a bit of thought and development effort into SSDT.

I really *want* to like SSDT, but run into crap like this almost daily requiring a convoluted workaround...
</FRUSTRATED RANT>

The below query works fine in SSMS. It also works as a view, but would require a linked server. But it fails miserably in SSDT as an OLEDB data source. Any ideas how to get this to work?

I could split the extract and deduping into two separate steps using an intermediate table, but am trying to improve performance by having it all execute on the source server before sending the results to the target server.

(My actual query doesn't use SELECT *, I'm just trying to save hundreds of lines in this post.)

-- Delta source table extract, new or changed rows between start and end dates
WITH cteDelta AS (
SELECT *
FROM [dbo].[EPISODE]
WHERE (
(? <= [valid_to_date] AND [valid_to_date] < ?)
OR
(? <= [valid_from_date] AND [valid_from_date] < ?)
)
)
,
-- Dedup, only want the most recent row within the keys
cteDedup AS (
SELECT *
FROM (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier, stay_number, episode_sequence_number
ORDER BY valid_from_date DESC
)
FROM cteDelta
) x
WHERE ROW=1
)
SELECT *
FROM cteDedup

Scott In Sydney
Scott In Sydney
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 701
Nesting the CTE doesn't work either:

SELECT *
FROM (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier, stay_number, episode_sequence_number
ORDER BY replica_valid_from_date DESC
)
FROM (
SELECT *
FROM [dbo].[EPISODE]
WHERE (
(? <= [replica_valid_to_date] AND [replica_valid_to_date] < ?)
OR
(? <= [replica_valid_from_date] AND [replica_valid_from_date] < ?)
)
) x
) x
WHERE ROW=1


The error message says put the entire block of code in a variable - also doesn't work.
Scott In Sydney
Scott In Sydney
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 701
So I found this SO post: https://stackoverflow.com/questions/42782464/error-in-ssis-parameters-cannot-be-extracted-when-parsing-query-syntax-error

Which echoes my original sentiment: SSDT is braindead.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211060 Visits: 24517
Have you tried building the SQL in an expression and using that as your data source (ie, no parameters)?
You might have more success if you explicitly name your columns too.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Martin Schoombee
Martin Schoombee
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16664 Visits: 4715
Could be due to the type of your parameters, if they are not text/string types. Also could be that the variables used do not have any values when the package goes through the pre-validation phase.

I agree with Phil though. In these situations I prefer to use expressions and build the query that way, making it easier to see and troubleshoot the query.



Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160643 Visits: 22697
Have you tried making it an inline table-valued function?
I don't get why would you need a linked server to make it a view or how can you send parameters to a CTE inside a view. The problem might be on how you're calling this, but I have no idea of what you're doing.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Martin Schoombee
Martin Schoombee
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16664 Visits: 4715
Luis Cazares - Thursday, March 8, 2018 9:35 AM
Have you tried making it an inline table-valued function?
I don't get why would you need a linked server to make it a view or how can you send parameters to a CTE inside a view. The problem might be on how you're calling this, but I have no idea of what you're doing.

I'd rather wrap it in a parameterized stored proc before creating a function...




Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160643 Visits: 22697
Martin Schoombee - Thursday, March 8, 2018 9:40 AM
Luis Cazares - Thursday, March 8, 2018 9:35 AM
Have you tried making it an inline table-valued function?
I don't get why would you need a linked server to make it a view or how can you send parameters to a CTE inside a view. The problem might be on how you're calling this, but I have no idea of what you're doing.

I'd rather wrap it in a parameterized stored proc before creating a function...


Why?


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Martin Schoombee
Martin Schoombee
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16664 Visits: 4715
Luis Cazares - Thursday, March 8, 2018 9:45 AM
Martin Schoombee - Thursday, March 8, 2018 9:40 AM
Luis Cazares - Thursday, March 8, 2018 9:35 AM
Have you tried making it an inline table-valued function?
I don't get why would you need a linked server to make it a view or how can you send parameters to a CTE inside a view. The problem might be on how you're calling this, but I have no idea of what you're doing.

I'd rather wrap it in a parameterized stored proc before creating a function...


Why?

For the same reasons you would otherwise use stored procedures to logically store and execute business logic.




tim.ffitch 25252
tim.ffitch 25252
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1391 Visits: 200
You might have to put this in a stored procedure and EXECUTE WITH RESULTS SETS where you define the schema expected. Here is MS's example

EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
(
([Reporting Level] int NOT NULL,
[ID of Employee] int NOT NULL,
[Employee First Name] nvarchar(50) NOT NULL,
[Employee Last Name] nvarchar(50) NOT NULL,
[Employee ID of Manager] nvarchar(max) NOT NULL,
[Manager First Name] nvarchar(50) NOT NULL,
[Manager Last Name] nvarchar(50) NOT NULL )
);
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search