Stored Procedures and OpenQuery 1

[warpped]

I am trying to write a stored procedure that uses openquery and gets a date range fron the user.

The query works when I use:
Create Procedure (proc1)
as
Select * from OpenQuery(Server,&quot;select avg(col1),avg(col2),avg(col3) from tablename where col1 > 0 and col2 > '31 oct 01 12:00pm' and col2 < '31 oct 01 3:00pm'&quot

I want to be able to get date input from a user, so I tried this two ways:

Create Procedure (proc1)

@startdate datetime,
@enddate datetime
as
Select * from OpenQuery(Server,&quot;select avg(col1),avg(col2),avg(col3) from tablename where col1 > 0 and col2 > '&quot; + @startdate + &quot;' and col2 < '&quot; + @enddate + &quot;'&quot

This did not pass Sql Server's Syntax check.

I also tried:

Create Procedure (proc1)

@startdate datetime,
@enddate datetime
as
Select * from OpenQuery(Server,&quot;select avg(col1),avg(col2),avg(col3) from tablename where col1 > 0 and col2 > &quot; + @startdate + &quot; and col2 < &quot; + @enddate)

This also did not pass.

Create Procedure (proc1)

@startdate datetime,
@enddate datetime
as
Select * from OpenQuery(Server,&quot;select avg(col1),avg(col2),avg(col3) from tablename where col1 > 0 and col2 > ' + @startdate + ' and col2 < ' + @enddate + '&quot

This passed the syntax check, but would not execute. It told me there was incorrect syntax near ' + @enddate + '.
Is there any way of doing this? Please help!

 

[tlbroadbent]

Change the data type of @startdate and @enddate to varchar. Or use convert function in the Select statement to convert the content of the variables to a character data type. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jhall156]

You cannot do it this way. Unfortunately SQL Server does not allow any expressions in the second argument of OPENQUERY. The workaround I have discovered (not a very elegant one) is to insert your where clause information into a temporary table on your linked server and then you can use an inner join in your query. You'll take a performance hit but it works. I can't understand why in this time of object orientation Microsoft hasn't given us the ability to build the SQLString property of these linked server queries. C'est la vie.

 

[mfairchild]

You can use parameters in a openquery statement, but you need to make the entire statement (local statement and openquery statement) a string and execute it using EXEC. It becomes a pain to get all of the ' and &quot; correct, but it does work.

 

[tlbroadbent]

Write the query with this format rather than using OpenQuery.

select avg(col1),avg(col2),avg(col3)
from Server.dbname.dbo.tablename
where col1 > 0
and col2 > @startdate
and col2 < @enddate Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mfairchild]

I've used that too. The only thing that I've always wondered is how a statement like that is executed. I'd think that OpenQuery with parameters would make the remote server limit the return resultset before returning it to SQL Server. I'm not sure exactly what the remote and local server is doing with the resultset using the Server.dbname.dbo.tablename method. Is the remote server limiting the resultset or is SQL Server doing something with it?

 

[tlbroadbent]

I believe it depends on the remote server and the version of SQL Server. I only have experience with linked SQL Servers so I don't have a broad knowledgebase. I have found that queries with the four part name are sometimes more efficient than using OpenQuery. That surprised me but analysis showed that those queries used indexes on the remote server that OpenQuery didn't use when querying a View.

I always try different methods. As has been suggested, you can execute a SQL string. That may prove more effiecient in your case. On the other hand, with the selection criteria you have, the difference may be negligible. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jhall156]

Wow, I didn't know you could execute a string that way. Many thanks! You just reduced my processing time 3 fold.

 

[mfairchild]

Most of my experience with this is with a linked server to a huge poorly managed RDB platform. It's interesting to hear that OpenQuery doesn't use indexes and the four part name method does (against another SQL Server). Thanks Terry.

 

[warpped]

I'd like to start by thanking all of you for responding. Here is what I did to get it working, mfairchild was right passing the string did it, but the hardest part were the ' and &quot; - here it is:
The stored procedure looks like this:

create procedure (proc1)
@runsql varchar(2000)
as
exec (@runsql)

I then created a pass-through query in access saved as query3. I have a command button on a form with two fields called 'start' and 'end' for the date and time. The code behind the button passes a string to the Access query3. That query is passed to the stroed procedure as @runsql. I don't know if it is the best way to do it, but it works and it's fast. Here is the code from the button:

Private Sub Command4_Click()
Dim qd

Set qd = CurrentDb.QueryDefs(&quot;query3&quot

qd.sql = &quot;exec proc1 'select * from openquery (linkserver,&quot; & Chr$(34) & &quot;select &quot; _
& &quot;avg(col1),avg(col2),avg(col3)&quot; _
& &quot; from tablename &quot; _
& &quot;where col1 > 0 and datetime > ''&quot; & Me.start & &quot;'' and &quot; _
& &quot;datetime < ''&quot; & Me.end & &quot;'' &quot; & Chr$(34) & &quot'&quot;

End Sub

I had to use Chr$(34) to pass the double-quote to the query. After clicking the command button, query3 looks like this:

exec proc1 'select * from openquery(linkserver,&quot;select avg(col1),avg(col2),avg(col3) from tablename where col1 > 0 and datetime > ''10-31-01 15:00:00'' and datetime < ''10-31-01 20:00:00''&quot'