Can pass-through query to be used in WHERE clause

[ArviLaanemets]

Hi!

On SQL Server, I have some number of databases, where data are read into tables using pass-through queries to our ERP program. The data saved in those DB's are then used by other applications.

Currently I have a case, where I need to create a view based on data from 2 pass-through queries, which both are working really fast, and where saving the data into DB is the waste of time. So I had an idea I'd not prefer to check out on live server without consulting about it first.



Is it possible to run a query in SQL Server, where in WITH clauses are used pass-through queries directly? And when yes, then how exactly?

Edit: The header of thread must be 'Can pass-through query to be used in WITH clause' - I couldn't change it anymore!

[Minty]

I assume you are using the pass-throughs because you are including a parameter to restrict the returned records.

The view cannot reference a Pass through, however a stored procedure could take two (or more) parameters and return records from that view based around those parameters?

[ArviLaanemets]

I assume you are using the pass-throughs because you are including a parameter to restrict the returned records.

Not really. Pass-through queries are reading data from user-defined view's in ERP program (plus a couple of additional info like how much days remaining for delivery). SQL is combining the info read from ERP program to SQL Server View. The 3rd-party app is reading the SQL view and displaying it on screen in storage, so the workes there can see, which deliveries are most urgent, without scrolling through all of them.

We don't want to share our usernames and PW'ds for ERP to left and right. For SQL DB's, the access to DB's is RO, and is given through Domain User Groups for query access only.

Until now we did run such pass-through queries from VBA procedures run as Job steps on some schedule, and did write the result to tables in SQL Databases. I simply had an idea, that maybe it is possible to skip this data saving part for this DB, and to get 'live data' delivered (both pass-throughs run really fast).

[Gicu]

Would this help?
https://www.quora.com/Can-you-have-m...tements-in-SQL
Cheers,

[ArviLaanemets]

Would this help?

Thanks! But WITH statements I have used a lot. The question (more the hope) was about using pass-through queries there - and the answer was no!

[madpiet]

Can a CTE result be used in a WHERE clause?

Remember this part, and the rest is easy: a CTE is a fancy VIEW - the major differences are that CTEs support recursion and you can define multiple "table" expressions (views) inside the CTE. One thing that threw me moving from Access to SQL Server is that Access doesn't differentiate between queries that accept parameters and those that don't, while in SQL Server, unparameterized queries are usually views and parameterized ones are always stored procedures.

Can an Access query reference a CTE? Sure. It's just a View. Maybe this would be easier to explain if you posted some code.

So are you saying that you have no SQL Server instance where you can write your own queries against the database in question? You can, if you have the proper permissions, be in SQL Database A and write queries inside database A that run against tables or objects in database B. (So I could be in "MyScratchDB" and report on / run queries against "ERP Database"). The objects (views, tables etc) created would exist in "MyScratchDB" and not in "ERP Database".

Personally, I'd create everything on the "Scratch database" that you have permissions on. But that depends a lot on how familiar and comfortable you are writing TSQL.

[ArviLaanemets]

So are you saying that you have no SQL Server instance where you can write your own queries against the database in question? You can, if you have the proper permissions, be in SQL Database A and write queries inside database A that run against tables or objects in database B. (So I could be in "MyScratchDB" and report on / run queries against "ERP Database"). The objects (views, tables etc) created would exist in "MyScratchDB" and not in "ERP Database".

The problem is, the ERP Database is not a SQL Server database, and we don't have any direct access to it's data except through a read-only query. And of-course it is not even on same server, where our SQL Server is running. Even the query syntax can be slightly different there sometimes.

[madpiet]

If you can create linked tables in SQL Server that point at the tables in the ERP database, then that covers your objections. Then you can query them in SQL Server as if they were SQL Server tables. (See Linked Tables, Linked Servers in SQL Server documentation).

[Minty]

Linked servers are (IMHO) very difficult to play with, as permissions often don't pass between them very well, unless things have improved dramatically since I last played around with it.

If you can read the data successfully, I might be tempted to store a copy of it in your local SQL server, and perform any querying on it locally, where you can do what you like with it?

We operate a similar set of routines to extract Sage data into our Azure SQL DB and then query it on our terms.

[ArviLaanemets]

Tested it, and I could read the data from linked server. The syntax was like:
SELECT * FROM LinkedServerName.ServerName.DbName.TableName

The speed of query wasn't very different from speed of pass-through query, for the test table I used (about 100 000 records) even better (~3 minutes for direct query vs. ~4 minutes for pass-through one), but I had to drop the idea for using this to design a live-data view.
Reading the data of 2 views from ERP database, processing the read data, and writing the result to table did take about 10 seconds. Reading those 2 views as WITH clauses of query, and returning the data from one view, did take a couple of seconds. But combining the data from both of them (using JOIN) did take several minutes - probably because joining non-indexed data.

I think it is possible to use this technique to create a real-time view, when the result view is based on single query with limited number of rows. But for current task, the speed did win. For user, when the data is written to table on SQL Server, the time needed to get those data from ERP database practically doesn't count at all - but user gets the result practically immediately.

[madpiet]

One option (if it's data you use all the time) is to create a table in your SQL Server instance to store the result of the query, and then create a stored procedure to query the linked server and write the results to your local table. (only viable if the data is append only or if you replace the contents on a daily basis). Because then you can index it and return results more quickly. I've written fairly simple queries against unindexed tables and gotten terrible performance. Once I created an index and included all the necessary fields for my query, performance was sub-second. See CREATE INDEX, INCLUDE. (basically, you include other columns with the indexed columns, so SQL Server reads that and answers the query if it can with only that. If that index alone cannot satisfy the query, it goes back to the data (using the indexes) and retrieves the rest.

[NoellaG]

If the ERP database is ORACLE you can use replication
https://learn.microsoft.com/en-us/sq...l-server-ver16