OpenQuery using parameters

I need a little help with using parameter values in the where clause
with the OpenQuery statement.
What would the syntax for the following be if 10 was a variable? And
does the syntax change depending on the datasource? In my case I am
querying an Oracle database. But I can't get it work on an Oracle or
Access database.
SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
WHERE deptno = 10'
Many thanks to the answer for this.
johnny
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenetJohnny
Did you create a linked server to the Oracle database?
BOL has some examples how to create a linked server to Oracle adatabase.
SELECT * FROM OracleServer.Database.dbo.Table WHERE blblbl
Does the aboe example work for you?
For Access database you can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Johnny" <myob@.beatles.com> wrote in message
news:PBoof.631530$_o.29628@.attbi_s71...
>I need a little help with using parameter values in the where clause
> with the OpenQuery statement.
> What would the syntax for the following be if 10 was a variable? And
> does the syntax change depending on the datasource? In my case I am
> querying an Oracle database. But I can't get it work on an Oracle or
> Access database.
> SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
> WHERE deptno = 10'
> Many thanks to the answer for this.
> johnny
> -- Posted with NewsLeecher v3.0 Beta 6
> -- http://www.newsleecher.com/?usenet

OpenQuery Syntax Using Variables

I have an openquery statement with a parameter embeded as a variable:

declare @.product varchar(3)

set @.product= 'ABC'

select * from openquery(SomeServer,'

SELECT Description, Size

FROM Products

WHERE

Group = ''XY'' AND

Code = ''' + @.product + '''')

When I run it I get the following message:

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near '+'.

When I hard code the "Code" value, like so:

Code = ''ABC''')

...it works fine.

I am at a loss and would appreciate any help on this.

Thanks in advance

SQL Servant

Can you try SET QUOTED_IDENTIFIER OFF?

cheers,

Andrew

|||

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @.product?

e.g. you want "Drink", or 'Drink'?

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)

Code Snippet

EXEC ('SELECT * FROM OPENQUERY(SERVER, ''SELECT * FROM TABLE WHERE Table_Id = ' + @.Table_Id_Str + ''')')

|||

Tried it... same error.

Thanks,

SQL Servant

Reply to --

Can you try SET QUOTED_IDENTIFIER OFF?

cheers,

Andrew

|||

The query sent to the server needs to have 'ABC' rather than "ABC".

I have used EXEC before and got the same error. Anyway, this query is part of an IF structure...

IF EXISTS (select * from openquery(...))

[do this ] ELSE [do that]

Thanks,

SQL Servant

Reply to -

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @.product?

e.g. you want "Drink", or 'Drink'?

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)

|||

I have fixed the problem...

Apparently it is to do with scope and stuff...

If I put the query in a EXEC command then it works. So, the code becomes this:

EXEC('

select * from openquery(SomeServer,''

SELECT Description, Size

FROM Products

WHERE

Group = ''''XY'''' AND

Code = ''''' + @.product + ''''''')

')

In fact, I have put the entire IF structure (that this query is a part of) inside an EXEC command.

Over and out,

SQL Servant

|||

Wasn't that the idea of what I posted? ;-P

I think I deserve a star, hee hee

Anyway, please mark this thread as Answered

Glad it worked out for you

OPENQUERY Parameter

I am trying to do the following:

SELECT ExpireDate
FROM OPENQUERY([10.22.10.79], Expire Date
From Product Where [ExpireDate] > 2005-12-31')
GO

However the above sql statement doesn't get the dates greater than the date provided unless there are quotes around the date. How do I add a variable that will cover this date and include the identifiers to get the correct recordsTry this:
SELECT ExpireDate
FROM OPENQUERY([10.22.10.79], 'select Expire Date From Product Where [ExpireDate] > ''2005-12-31''')
GO|||I thought I did already try that. Let me double check...|||This gives me an "Invalid Column Name" error.|||Chang "Expire Date" to "ExpireDate":
SELECT ExpireDate
FROM OPENQUERY([10.22.10.79], 'select ExpireDate From Product Where [ExpireDate] > ''2005-12-31''')

OPENQUERY

HI,

Can we use OPENQUERY with a parameter? Something like this:

SELECT * FROM OPENQUERY(@.SOURCE_SERVER_NAME, 'Select * from dbo.FEED')

Please let me know at the earliest. Thanks a lot,.

Mannu.

Not supported. You need to build dynamic TSQL to run it.

|||Thanks phe...sql

OPENQUERY

HI,

Can we use OPENQUERY with a parameter? Something like this:

SELECT * FROM OPENQUERY(@.SOURCE_SERVER_NAME, 'Select * from dbo.FEED')

Please let me know at the earliest. Thanks a lot,.

Mannu.

Not supported. You need to build dynamic TSQL to run it.

|||Thanks phe...

Open the link in new window

When i giving a url passing some parameter..in Jump to Url of cell navigation properties..

In VS Studio its opening in new window ..but when deployed in server and its opening same browser while my requirement is to open in new window..

You need to set the LinkTarget deviceinfo to "_blank". This can be set using a url parameter or in the rsreportserver.config file.

See this link for more information on setting the device info:

http://msdn2.microsoft.com/en-us/library/ms155397.aspx

This link has information on the device info settings for the html renderer.

http://msdn2.microsoft.com/en-us/library/ms155395.aspx

|||

I have Used this code in jump to url of navigation text box void(window.open(url))" its working and had satisfied mine requirement..

sql

Open the link in new window

When i giving a url passing some parameter..in Jump to Url of cell navigation properties..

In VS Studio its opening in new window ..but when deployed in server and its opening same browser while my requirement is to open in new window..

You need to set the LinkTarget deviceinfo to "_blank". This can be set using a url parameter or in the rsreportserver.config file.

See this link for more information on setting the device info:

http://msdn2.microsoft.com/en-us/library/ms155397.aspx

This link has information on the device info settings for the html renderer.

http://msdn2.microsoft.com/en-us/library/ms155395.aspx

|||

I have Used this code in jump to url of navigation text box void(window.open(url))" its working and had satisfied mine requirement..

Open Report with Parameter over URL

Hello,
i want to open a report with one parameter over hyperlink.
The URL from the report is:
http://wh-report/Pages/Report.aspx?ItemPath=%2fVertrieb%2fKundeninfo
Can somebody tell me the solution.
The parameter is a customer number like: 123431
Thanks for your help,
HorstThis sample I picked up from online help.. Please go through for more details.
http://server/reportserver?/Sales/Northwest/Employee Sales
Report&rs:Command=Render&EmployeeID=1234
Here "Employee Sales Summary" is the name of the report and "EmployeeID =1234" is the parameter. be careful of ambersand and the syntax.
Amarnath
"Horst Wutscher" wrote:
> Hello,
> i want to open a report with one parameter over hyperlink.
> The URL from the report is:
> http://wh-report/Pages/Report.aspx?ItemPath=%2fVertrieb%2fKundeninfo
> Can somebody tell me the solution.
> The parameter is a customer number like: 123431
> Thanks for your help,
> Horst
>
>

Open Obects Param

Does anyone know how to check the current amount of "Open Objects"? I am getting an error that says my Open Objects parameter is set too low. If I cannot tell what it is currently set at, how can I tell how high to set it to?
Use sp_configure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rich" <anonymous@.discussions.microsoft.com> wrote in message
news:2F1B9867-385F-4984-B6E4-B62F538BBB53@.microsoft.com...
> Does anyone know how to check the current amount of "Open Objects"? I am getting an error that says my Open
Objects parameter is set too low. If I cannot tell what it is currently set at, how can I tell how high to
set it to?
|||Execute sp_configure and check the open objects run_value.
You can find more info on this setting in books online under
the help topic open objects Option
-Sue
On Tue, 27 Apr 2004 07:11:03 -0700, "Rich"
<anonymous@.discussions.microsoft.com> wrote:

>Does anyone know how to check the current amount of "Open Objects"? I am getting an error that says my Open Objects parameter is set too low. If I cannot tell what it is currently set at, how can I tell how high to set it to?

Open Obects Param

Does anyone know how to check the current amount of "Open Objects"? I am ge
tting an error that says my Open Objects parameter is set too low. If I can
not tell what it is currently set at, how can I tell how high to set it to?Use sp_configure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rich" <anonymous@.discussions.microsoft.com> wrote in message
news:2F1B9867-385F-4984-B6E4-B62F538BBB53@.microsoft.com...
> Does anyone know how to check the current amount of "Open Objects"? I am getting
an error that says my Open
Objects parameter is set too low. If I cannot tell what it is currently set
at, how can I tell how high to
set it to?|||Execute sp_configure and check the open objects run_value.
You can find more info on this setting in books online under
the help topic open objects Option
-Sue
On Tue, 27 Apr 2004 07:11:03 -0700, "Rich"
<anonymous@.discussions.microsoft.com> wrote:

>Does anyone know how to check the current amount of "Open Objects"? I am getting a
n error that says my Open Objects parameter is set too low. If I cannot tell what i
t is currently set at, how can I tell how high to set it to?

Open Obects Param

Does anyone know how to check the current amount of "Open Objects"? I am getting an error that says my Open Objects parameter is set too low. If I cannot tell what it is currently set at, how can I tell how high to set it to?Use sp_configure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rich" <anonymous@.discussions.microsoft.com> wrote in message
news:2F1B9867-385F-4984-B6E4-B62F538BBB53@.microsoft.com...
> Does anyone know how to check the current amount of "Open Objects"? I am getting an error that says my Open
Objects parameter is set too low. If I cannot tell what it is currently set at, how can I tell how high to
set it to?|||Execute sp_configure and check the open objects run_value.
You can find more info on this setting in books online under
the help topic open objects Option
-Sue
On Tue, 27 Apr 2004 07:11:03 -0700, "Rich"
<anonymous@.discussions.microsoft.com> wrote:
>Does anyone know how to check the current amount of "Open Objects"? I am getting an error that says my Open Objects parameter is set too low. If I cannot tell what it is currently set at, how can I tell how high to set it to?

Only parameter values which have facts...

In a reporting services report based on a cube I have a parameter in which I
only want to show the values which have measures in a measuregroup asociated
with it.
For Example: I only want to choose products which have sales in the cube.
(retrieving the values outside the cube is not an option)
How can I achieve this'
Thanx
PieterChange the select for the product list to include a where clause for the
measure:
WHERE ([Measures].[Product Sales])
"Pieter M" <Pieter M@.discussions.microsoft.com> wrote in message
news:0CA17070-4932-445B-A8ED-AA529B48FAE7@.microsoft.com...
> In a reporting services report based on a cube I have a parameter in which
> I
> only want to show the values which have measures in a measuregroup
> asociated
> with it.
> For Example: I only want to choose products which have sales in the cube.
> (retrieving the values outside the cube is not an option)
> How can I achieve this'
> Thanx
> Pieter|||Thaks Tim,
but where do I have to put this? In SQL I would know where, but in MDX?
My quey looks like this:
WITH MEMBER [Measures].[ParameterCaption] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS
'[Kalender].[Jaar Nummer Lang].CURRENTMEMBER.UNIQUENAME' MEMBER
[Measures].[ParameterLevel] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Kalender].[Jaar Nummer Lang].ALLMEMBERS ON ROWS FROM [Cube]
Thanks.
Pieter
"Tim Dot NoSpam" wrote:
> Change the select for the product list to include a where clause for the
> measure:
> WHERE ([Measures].[Product Sales])
> "Pieter M" <Pieter M@.discussions.microsoft.com> wrote in message
> news:0CA17070-4932-445B-A8ED-AA529B48FAE7@.microsoft.com...
> > In a reporting services report based on a cube I have a parameter in which
> > I
> > only want to show the values which have measures in a measuregroup
> > asociated
> > with it.
> > For Example: I only want to choose products which have sales in the cube.
> > (retrieving the values outside the cube is not an option)
> > How can I achieve this'
> >
> > Thanx
> >
> > Pieter
>
>|||HI,
When I change my query to this:
WITH MEMBER [Measures].[ParameterCaption] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS
'[Kalender].[Jaar Nummer Lang].CURRENTMEMBER.UNIQUENAME' MEMBER
[Measures].[ParameterLevel] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Kalender].[Jaar Nummer Lang].ALLMEMBERS ON ROWS FROM [Cube] WHERE
([Measures].[Product Sales])
I get the following error:
TITLE: Microsoft Visual Studio
--
Query preparation failed.
--
ADDITIONAL INFORMATION:
The Measures hierarchy already appears in the Axis0 axis. (Microsoft SQL
Server 2005 Analysis Services)
--
BUTTONS:
OK
--
Can anybody tell me what I am doing wrong and and maybe a suggestion how to
solve this
"Tim Dot NoSpam" wrote:
> Change the select for the product list to include a where clause for the
> measure:
> WHERE ([Measures].[Product Sales])
> "Pieter M" <Pieter M@.discussions.microsoft.com> wrote in message
> news:0CA17070-4932-445B-A8ED-AA529B48FAE7@.microsoft.com...
> > In a reporting services report based on a cube I have a parameter in which
> > I
> > only want to show the values which have measures in a measuregroup
> > asociated
> > with it.
> > For Example: I only want to choose products which have sales in the cube.
> > (retrieving the values outside the cube is not an option)
> > How can I achieve this'
> >
> > Thanx
> >
> > Pieter
>
>