OPENQUERY and parameters

I am running OPENQUERY against Oracle database via a linked server.
How can I provide parameters into the Select statement?
ThanksWhen I have created openquery statements I create the appropriate SQL on the
fly (which is a pain when you get to single quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:e9ae05NPIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I am running OPENQUERY against Oracle database via a linked server.
> How can I provide parameters into the Select statement?
> Thanks
>|||On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am running OPENQUERY against Oracle database via a linked server.
> How can I provide parameters into the Select statement?
> Thanks
What you basically need to do is treat your query like you are writing
a String that contains your query, then EXEC the string at the end.
Any embeded quotation marks need to be "doubled", then you concatenate
your parameters using string concat symbols ( the + sign in SQL
Server ), then EXEC it at the end and you're golden.
DECLARE @.SQLSTR NVARCHAR(4000)
SET @.SQLSTR ='
SELECT * FROM OPENQUERY( PISERVER,
''SELECT TAG, TIME, VALUE
FROM piarchive.piavg
WHERE TAG = '' + @.tagname + ''
AND TIMESTEP = '' + @.timestep + ''
AND TIME >= '' + @.startdatetime + ''
AND TIME <= '' + @.enddatetime + '' '' ) Q
'
EXEC (@.SQLSTR)
-- Scott|||Thanks to you both I got it working:
DECLARE @.SQLSTR NVARCHAR(4000)
SET @.SQLSTR ='
SELECT * FROM OPENQUERY(PST,
''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
work_asgn_id,
descr, trunc(job_work_date) as job_work_date
from EWM.ASSIGNMENT_HISTORY a
inner join EWM.TERMINAL t
on a.work_terminal = t.terminal
where trunc(job_work_date) = to_date( '' + @.ForDate + '',
''''MM/DD/YYYY'''')' +
' and act_offduty_date_time is not null
and act_onduty_date_time is not null
and to_char(a.ASSIGNMENT_HISTORY_SID) in
(select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY b
where a.work_asgn_id = b.WORK_ASGN_ID
and a.WORK_TERMINAL = b.WORK_TERMINAL
and trunc(job_work_date) = to_date( '' + @.ForDate + '',
''''MM/DD/YYYY''''))''' +
')'
EXEC (@.SQLSTR)
I run it fine in Data tab, but in Layout when I try to assign an expression
to a field selecting dataset it says:
'DailySummary' dataset has no fields.
What's wrong?
"Orne" <polysillycon@.yahoo.com> wrote in message
news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
> What you basically need to do is treat your query like you are writing
> a String that contains your query, then EXEC the string at the end.
> Any embeded quotation marks need to be "doubled", then you concatenate
> your parameters using string concat symbols ( the + sign in SQL
> Server ), then EXEC it at the end and you're golden.
>
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY( PISERVER,
> ''SELECT TAG, TIME, VALUE
> FROM piarchive.piavg
> WHERE TAG = '' + @.tagname + ''
> AND TIMESTEP = '' + @.timestep + ''
> AND TIME >= '' + @.startdatetime + ''
> AND TIME <= '' + @.enddatetime + '' '' ) Q
> '
> EXEC (@.SQLSTR)
> -- Scott|||Try to click the refresh fields button (one of the buttons to the right of
the ...)
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Thanks to you both I got it working:
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY(PST,
> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
> work_asgn_id,
> descr, trunc(job_work_date) as job_work_date
> from EWM.ASSIGNMENT_HISTORY a
> inner join EWM.TERMINAL t
> on a.work_terminal = t.terminal
> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
> ''''MM/DD/YYYY'''')' +
> ' and act_offduty_date_time is not null
> and act_onduty_date_time is not null
> and to_char(a.ASSIGNMENT_HISTORY_SID) in
> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY
> b
> where a.work_asgn_id = b.WORK_ASGN_ID
> and a.WORK_TERMINAL = b.WORK_TERMINAL
> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
> ''''MM/DD/YYYY''''))''' +
> ')'
> EXEC (@.SQLSTR)
> I run it fine in Data tab, but in Layout when I try to assign an
> expression to a field selecting dataset it says:
> 'DailySummary' dataset has no fields.
> What's wrong?
>
> "Orne" <polysillycon@.yahoo.com> wrote in message
> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>|||Worked!!
I am also having infamous:
Invalid data for type "numeric".
Is there any fix available for this error?
Thanks
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Try to click the refresh fields button (one of the buttons to the right of
> the ...)
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
> news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Thanks to you both I got it working:
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY(PST,
>> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
>> work_asgn_id,
>> descr, trunc(job_work_date) as job_work_date
>> from EWM.ASSIGNMENT_HISTORY a
>> inner join EWM.TERMINAL t
>> on a.work_terminal = t.terminal
>> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY'''')' +
>> ' and act_offduty_date_time is not null
>> and act_onduty_date_time is not null
>> and to_char(a.ASSIGNMENT_HISTORY_SID) in
>> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY
>> b
>> where a.work_asgn_id = b.WORK_ASGN_ID
>> and a.WORK_TERMINAL = b.WORK_TERMINAL
>> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY''''))''' +
>> ')'
>> EXEC (@.SQLSTR)
>> I run it fine in Data tab, but in Layout when I try to assign an
>> expression to a field selecting dataset it says:
>> 'DailySummary' dataset has no fields.
>> What's wrong?
>>
>> "Orne" <polysillycon@.yahoo.com> wrote in message
>> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>>
>|||I have never seen this error.
You could try creating a stored procedure. In the stored procedure create a
temp table. Then do this:
insert #yourtemptable select * from openquery(pst, @.SQLSTR)
select * from #yourtemptable
return
Note that you would have to rework your string again.
Now, you know for sure what your output types are (based on how you created
the temp table) and you can thoroughly test outside of RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%233wMgCPPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Worked!!
> I am also having infamous:
> Invalid data for type "numeric".
> Is there any fix available for this error?
> Thanks
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> Try to click the refresh fields button (one of the buttons to the right
>> of the ...)
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
>> news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Thanks to you both I got it working:
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY(PST,
>> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
>> work_asgn_id,
>> descr, trunc(job_work_date) as job_work_date
>> from EWM.ASSIGNMENT_HISTORY a
>> inner join EWM.TERMINAL t
>> on a.work_terminal = t.terminal
>> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY'''')' +
>> ' and act_offduty_date_time is not null
>> and act_onduty_date_time is not null
>> and to_char(a.ASSIGNMENT_HISTORY_SID) in
>> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from
>> EWM.ASSIGNMENT_HISTORY b
>> where a.work_asgn_id = b.WORK_ASGN_ID
>> and a.WORK_TERMINAL = b.WORK_TERMINAL
>> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY''''))''' +
>> ')'
>> EXEC (@.SQLSTR)
>> I run it fine in Data tab, but in Layout when I try to assign an
>> expression to a field selecting dataset it says:
>> 'DailySummary' dataset has no fields.
>> What's wrong?
>>
>> "Orne" <polysillycon@.yahoo.com> wrote in message
>> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>>
>>
>|||On Dec 12, 2:12 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this error.
> You could try creating a stored procedure. In the stored procedure create a
> temp table. Then do this:
> insert #yourtemptable select * from openquery(pst, @.SQLSTR)
> select * from #yourtemptable
> return
> Note that you would have to rework your string again.
> Now, you know for sure what your output types are (based on how you created
> the temp table) and you can thoroughly test outside of RS.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark Goldin" <mgol...@.ufandd.com> wrote in message
> news:%233wMgCPPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>
> > Worked!!
> > I am also having infamous:
> > Invalid data for type "numeric".
> > Is there any fix available for this error?
> > Thanks
> > "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in message
> >news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
> >> Try to click the refresh fields button (one of the buttons to the right
> >> of the ...)
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Mark Goldin" <mgol...@.ufandd.com> wrote in message
> >>news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> >> Thanks to you both I got it working:
> >> DECLARE @.SQLSTR NVARCHAR(4000)
> >> SET @.SQLSTR => >> '
> >> SELECT * FROM OPENQUERY(PST,
> >> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
> >> work_asgn_id,
> >> descr, trunc(job_work_date) as job_work_date
> >> from EWM.ASSIGNMENT_HISTORY a
> >> inner join EWM.TERMINAL t
> >> on a.work_terminal = t.terminal
> >> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
> >> ''''MM/DD/YYYY'''')' +
> >> ' and act_offduty_date_time is not null
> >> and act_onduty_date_time is not null
> >> and to_char(a.ASSIGNMENT_HISTORY_SID) in
> >> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from
> >> EWM.ASSIGNMENT_HISTORY b
> >> where a.work_asgn_id = b.WORK_ASGN_ID
> >> and a.WORK_TERMINAL = b.WORK_TERMINAL
> >> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
> >> ''''MM/DD/YYYY''''))''' +
> >> ')'
> >> EXEC (@.SQLSTR)
> >> I run it fine in Data tab, but in Layout when I try to assign an
> >> expression to a field selecting dataset it says:
> >> 'DailySummary' dataset has no fields.
> >> What's wrong?
> >> "Orne" <polysilly...@.yahoo.com> wrote in message
> >>news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
> >> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am running OPENQUERY against Oracle database via a linked server.
> >> How can I provide parameters into the Select statement?
> >> Thanks
> >> What you basically need to do is treat your query like you are writing
> >> a String that contains your query, then EXEC the string at the end.
> >> Any embeded quotation marks need to be "doubled", then you concatenate
> >> your parameters using string concat symbols ( the + sign in SQL
> >> Server ), then EXEC it at the end and you're golden.
> >> DECLARE @.SQLSTR NVARCHAR(4000)
> >> SET @.SQLSTR => >> '
> >> SELECT * FROM OPENQUERY( PISERVER,
> >> ''SELECT TAG, TIME, VALUE
> >> FROM piarchive.piavg
> >> WHERE TAG = '' + @.tagname + ''
> >> AND TIMESTEP = '' + @.timestep + ''
> >> AND TIME >= '' + @.startdatetime + ''
> >> AND TIME <= '' + @.enddatetime + '' '' ) Q
> >> '
> >> EXEC (@.SQLSTR)
> >> -- Scott- Hide quoted text -
> - Show quoted text -
Sounds like your work is like mine... SQL Server managing linked
servers to Oracle servers of all flavors...
Your @.ForDate parameter is a DateTime, but when it is passed as a
parameter, the formatting matters. My guess is that you are running
into errors with the parsing of either the TO_CHAR function or the
TO_DATE function.
Go to the Dataset Properties button [...], and goto the Parameters
tab. Change the Expression for ForDate from:
=Parameters!ForDate.Value
to
=Format( CDate( Parameters!ForDate.Value ), "MM/dd/yyyy" )
This will take your DateTime parameter and convert it into a String in
MM/DD/YYYY format, then the string will be concatenated to the rest of
the string and executed in SQL Server, which will pass through the SQL
query to Oracle and the TO_DATE function will always parse correctly.
The next item is that TO_CHAR( ASSIGNMENT_HISTORY_SID ) thing that you
got going everywhere. I would make sure that the
ASSIGNMENT_HISTORY_FIELD always contains a convertable number...
-- Scott|||On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > I am running OPENQUERY against Oracle database via a linked server.
> > How can I provide parameters into the Select statement?
> > Thanks
> What you basically need to do is treat your query like you are writing
> a String that contains your query, then EXEC the string at the end.
> Any embeded quotation marks need to be "doubled", then you concatenate
> your parameters using string concat symbols ( the + sign in SQL
> Server ), then EXEC it at the end and you're golden.
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY( PISERVER,
> ''SELECT TAG, TIME, VALUE
> FROM piarchive.piavg
> WHERE TAG = '' + @.tagname + ''
> AND TIMESTEP = '' + @.timestep + ''
> AND TIME >= '' + @.startdatetime + ''
> AND TIME <= '' + @.enddatetime + '' '' ) Q
> '
> EXEC (@.SQLSTR)
> -- Scott
Hi Orhne,
I was trying to do the same thing what you were trying, but I was not
able to syccessfully insert a parameter into my SQL query for Oracle
database.
--
(DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
--
This line should be parameterized. 11/01/2007 should be start date and
11/07/2007 is supposed to be the end date.
Please let me know on how to solve this issue, I am going to
incorporate this in SQL Reporting for generating reports. Please let
me know ASAP.
Thanks a lot.|||On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
> On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
>
>
> > On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > > I am running OPENQUERY against Oracle database via a linked server.
> > > How can I provide parameters into the Select statement?
> > > Thanks
> > What you basically need to do is treat your query like you are writing
> > a String that contains your query, then EXEC the string at the end.
> > Any embeded quotation marks need to be "doubled", then you concatenate
> > your parameters using string concat symbols ( the + sign in SQL
> > Server ), then EXEC it at the end and you're golden.
> > DECLARE @.SQLSTR NVARCHAR(4000)
> > SET @.SQLSTR => > '
> > SELECT * FROM OPENQUERY( PISERVER,
> > ''SELECT TAG, TIME, VALUE
> > FROM piarchive.piavg
> > WHERE TAG = '' + @.tagname + ''
> > AND TIMESTEP = '' + @.timestep + ''
> > AND TIME >= '' + @.startdatetime + ''
> > AND TIME <= '' + @.enddatetime + '' '' ) Q
> > '
> > EXEC (@.SQLSTR)
> > -- Scott
> Hi Orhne,
> I was trying to do the same thing what you were trying, but I was not
> able to syccessfully insert a parameter into my SQL query for Oracle
> database.
> --
> (DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
> dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
> --
> This line should be parameterized. 11/01/2007 should be start date and
> 11/07/2007 is supposed to be the end date.
> Please let me know on how to solve this issue, I am going to
> incorporate this in SQL Reporting for generating reports. Please let
> me know ASAP.
> Thanks a lot.- Hide quoted text -
> - Show quoted text -
Try this, with quadruple single quotes. This first level is the '
that build the string, anything in that has to be doubled. You then
have another ' for the OPENQUERY function, so every quote in that has
to be doubled again:
SET @.SQLSTR = '
SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
SELECT * FROM DSS_CLIN.V_CLAIM_PAID
WHERE V_CLAIM_PAID.BATCH_DATE
BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
'' ) '
-- Scott|||I haven't had to do this for awhile because the code is stable but I wrote a
bunch of stored procedures to maintain a datamart. I was extracting data
from Sybase using linked servers (and unfortunately in SQL 2000 you had to
use openquery because four part naming was so awful). Anyway, lots and lots
of counting of single quotes.
One other point if on SQL 2005. If doing normal SQL statements, i.e. not any
Oracle extensions, then four part naming might work.
SELECT * FROM linkedservername.database.owner.tablename WHERE
V_CLAIM_PAID.BATCH_DATE
BETWEEN @.StartDate AND @.EndDate
In SQL 2000 this statement might have pulled all the records over. In SQL
2005 it realizes everything resides on the remote server and sends the whole
query over. You can use the queryplan statement to see if this is true
before running. Joins will work etc doing this. Always check query plan
first though.
I suggest in your work checking it out, there was such a dramatic difference
between versions with how well the four part naming worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Orne" <polysillycon@.yahoo.com> wrote in message
news:f34d6b78-9e05-4bc4-ba3e-05e2e8dc06db@.d21g2000prf.googlegroups.com...
> On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
Snip
> Try this, with quadruple single quotes. This first level is the '
> that build the string, anything in that has to be doubled. You then
> have another ' for the OPENQUERY function, so every quote in that has
> to be doubled again:
> SET @.SQLSTR = '
> SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
> SELECT * FROM DSS_CLIN.V_CLAIM_PAID
> WHERE V_CLAIM_PAID.BATCH_DATE
> BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
> AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
> '' ) '
> -- Scott|||On Dec 13, 8:50 am, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
>
>
> > On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
> > > On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > > > I am running OPENQUERY against Oracle database via a linked server.
> > > > How can I provide parameters into the Select statement?
> > > > Thanks
> > > What you basically need to do is treat your query like you are writing
> > > a String that contains your query, then EXEC the string at the end.
> > > Any embeded quotation marks need to be "doubled", then you concatenate
> > > your parameters using string concat symbols ( the + sign in SQL
> > > Server ), then EXEC it at the end and you're golden.
> > > DECLARE @.SQLSTR NVARCHAR(4000)
> > > SET @.SQLSTR => > > '
> > > SELECT * FROM OPENQUERY( PISERVER,
> > > ''SELECT TAG, TIME, VALUE
> > > FROM piarchive.piavg
> > > WHERE TAG = '' + @.tagname + ''
> > > AND TIMESTEP = '' + @.timestep + ''
> > > AND TIME >= '' + @.startdatetime + ''
> > > AND TIME <= '' + @.enddatetime + '' '' ) Q
> > > '
> > > EXEC (@.SQLSTR)
> > > -- Scott
> > Hi Orhne,
> > I was trying to do the same thing what you were trying, but I was not
> > able to syccessfully insert a parameter into my SQL query for Oracle
> > database.
> > --
> > (DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
> > dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
> > --
> > This line should be parameterized. 11/01/2007 should be start date and
> > 11/07/2007 is supposed to be the end date.
> > Please let me know on how to solve this issue, I am going to
> > incorporate this in SQL Reporting for generating reports. Please let
> > me know ASAP.
> > Thanks a lot.- Hide quoted text -
> > - Show quoted text -
> Try this, with quadruple single quotes. This first level is the '
> that build the string, anything in that has to be doubled. You then
> have another ' for the OPENQUERY function, so every quote in that has
> to be doubled again:
> SET @.SQLSTR = '
> SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
> SELECT * FROM DSS_CLIN.V_CLAIM_PAID
> WHERE V_CLAIM_PAID.BATCH_DATE
> BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
> AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
> '' ) '
> -- Scott- Hide quoted text -
> - Show quoted text -
This is the error which I am getting "Must declare the scalar variable
"@.StartDate".