Menu
Consider the following code.
DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'SELECT * FROM caseinformation'
EXEC(@sql)
When I run this in SSMS, it displays the same results as it would had I run SELECT * FROM caseinformation. But when I run this as an Access pass-through query, I get the following error message:
Pass-through query with ReturnsRecords property set to True did not return any records.
What gives?
555
Here is a very simple trick for the same thing. Here is a script that announces an additional parameter, which is Name, and returns the value to it. WHERE DepartmentID = @ID'. You can use this script and example as a template for your need, where you want to run dynamic SQL and save the result in a variable.
We use two ways to execute dynamic SQL: EXEC command and sp_executesql stored procedure.
To run a dynamic SQL statement, run the stored procedure sp_executesql as shown below : EXEC sp_executesql N'SELECT statement'; Use prefix N with the sp_executesql to use dynamic SQL as a Unicode string.
Add the following at the beginning of your dynamic pass-through query:
SET NoCount ON
and you will be able to use the query from MS Access.
Why this works, I'll have to leave for others to explain, but I know it does work.
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
