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
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Friday, March 30, 2012
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 and string
Hi
Does anyone know how to include a string in the statement of an open query?
I want to execute the following query:
select * from TEST where A like 'A'
But if use this it in an openquery like it follows
SELECT *
FROM OPENQUERY (MD_AS400, 'select * from TEST where A like 'A'')
The 'A' is not recognize like a string.
This is due to the single quote around 'A'
try this
''A'''
rule is if u need a quoted string put TWO quotes.
Gurpreet S. Gill
|||You should escape quote by putting another quote.
So your query would be
' select * from TEST where A like ''A'' '
|||Lot of lanugaues accepted the escape sequence char starts with \.
But in SQL Server (i remember in VB & MDX also) the same character will be repeated.
Code Snippet
SELECT *
FROM OPENQUERY (MD_AS400, 'select * from TEST where A like ''A''')
sqlWednesday, March 28, 2012
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".
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".
Opening View does not reflect SQL statement
I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.
Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005
Hi,this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?
Thanks!
|||I apply Service Pack 1 for microsoft SQL Server 2005
Version : 9.00.2047
and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.
the workaround for this is not using the View and write direct SQL statements with ORDER BY.
Thanks
|||Hi Jens,
Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT
I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but
only for NUMBER and DATE.
Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..
Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?
RICz
Software Specialist
Tokyo,Japan
www.rajibul.com
|||
Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.
--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-
Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY
It will work until microsoft fix it to work automatically
Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html
RICZ
www.rajibul.com
Opening View does not reflect SQL statement
I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.
Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005
Hi,this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?
Thanks!
|||I apply Service Pack 1 for microsoft SQL Server 2005
Version : 9.00.2047
and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.
the workaround for this is not using the View and write direct SQL statements with ORDER BY.
Thanks
|||Hi Jens,
Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT
I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but
only for NUMBER and DATE.
Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..
Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?
RICz
Software Specialist
Tokyo,Japan
www.rajibul.com
|||
Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.
--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-
Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY
It will work until microsoft fix it to work automatically
Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html
RICZ
www.rajibul.com
Subscribe to:
Posts (Atom)