Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-09 : 09:27:49
|
| Shawn writes "I am trying write a SQL Server Stored Procedure that creates dynamic SQL and then returns the result set to a Microsoft Access query.When I run it, the SQL statement is placed into the Access query instead of the results. So I get one row with the SQL statement. However, If I run this query from SQL Server it shows the SQL statement and returns the rows. I am using the sp_ExecuteSQL command. My SQL Server Stored procedure is shown below. It uses the Northwind database. Below that is the Access function that calls the procedure. I also tried to insert the results into a temporary table, but that didn't work.CREATE PROCEDURE SSR @STUDYCUST VARCHAR(30), @STUDYCNTRY VARCHAR(15)AS DECLARE @SQL nVarchar(4000) SELECT @SQL = ' Select CompanyName, Country, OrderDate from Customers, Orders where ' IF NOT RTRIM(@STUDYCUST) IS NULL and LEN(RTRIM(@STUDYCUST)) > 0 BEGIN SELECT @SQL = @SQL + "CompanyName = '" + @STUDYCUST +"' " END IF @STUDYCNTRY IS NOT NULL BEGIN IF NOT RTRIM(@STUDYCUST) IS NULL and LEN(RTRIM(@STUDYCUST)) > 0 SELECT @SQL = @SQL + " and" SELECT @SQL = @SQL + " Country = '" + @STUDYCNTRY+"'" END SELECT SQL = @SQL Exec sp_executeSQL @SQL******* Access Code that calls the stored procedure above ******Private Sub cbxCountry_AfterUpdate() DoCmd.DeleteObject acQuery, "qryPassThru" Set db = DBEngine(0)(0) Set qd = db.CreateQueryDef("qryPassThru") qd.Connect = cs qd.SQL = "exec Northwind..SSR '" & Trim(cbxCompanyName) & "', '" & cbxCountry & "'" Set rs = db.OpenRecordset("qryPassThru") DoCmd.OpenQuery "qryPassThru"End Sub" |
|
|
ksw
Starting Member
24 Posts |
Posted - 2002-09-09 : 18:20:12
|
I haven't tried doing something like this, but my first guess would be that since your SQL Server stored procedure is actually trying to return 2 recordsets, only the first one is being saved in the rs variable in Access. Try commenting out the SELECT SQL = @SQL line and see if you get the results you're looking for. -- is the comment for T-SQL. It only affects that one line. If you really need to have the sql statement returned as well, you could do some more complicated stuff with output parameters. I'm guessing that what you really want is just the output from the Exec sp_executeSQL @SQL line. Also, in the Access code, I don't think that you need both the Set rs = db.OpenRecordset("qryPassThru") line and the DoCmd.OpenQuery "qryPassThru" line. Do you use the rs variable somewhere else in your code? I would think that the OpenQuery line would be enough. But it has been awhile since I've worked with creating queries in code. Hope this helps some,--KSW |
 |
|
|
JozzaTheWick
Starting Member
16 Posts |
Posted - 2002-10-09 : 13:36:59
|
| Shawn,It seems that you essentially want to have an access query call your stored procedure? So that when you run the query from with Access, it executes the sp and returns the results?What I did in this situation was to create a pass-through query that simply called the stored procedure (I saved the connection info in the query). The SQL in the pass through query would then be:ssr 'testcust','testcountry'for example.Your code would then need to open the query, edit the SQL so that the correct parameters are being passed in e.g: Queries("qryPassThru").SQL= "srr 'cust2, country3' Close the query, then run (open) it.It probably isn't the best way to do it (and I may have misunderstood your actual intent) but it worked for me.J. |
|
|
|
|
|
|
|
|
SQL Server dynamic query stored procedure won't return results to Microsoft query.