Online Data Transform: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

OpenQuery with variable

I need to do this:
select field1, field2,... from openquery(LinkedServerName, 'select * from
tablename where field =' + @.myVar)
the problem is that openquery does not admit statement to be composed by
concatted strings...how to solve it?You need to build the entire SQL statement as a string and
then pass that string to an EXEC.
You can find more information and an example in the
following article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Tue, 24 Jan 2006 08:52:01 -0800, "Roberto Lo Baido"
<RobertoLoBaido@.discussions.microsoft.com> wrote:
>I need to do this:
>select field1, field2,... from openquery(LinkedServerName, 'select * from
>tablename where field =' + @.myVar)
>the problem is that openquery does not admit statement to be composed by
>concatted strings...how to solve it?

OpenQuery With Large String?

Hi,

I declare a variable @.MdxSyntax as NVARCHAR(4000) to store MDX OpenQuery syntax on Store Procedure.

SET @.mdxSyntax =
'
SELECT * INTO ##BU01505100
FROM OPENQUERY
(MOJOLAP,
''
WITH

'')
'

EXEC sp_executesql @.mdxSyntax

But maybe the syntax too long, system response syntax unclosed!

So, I change @.MdxSyntax as NVARCHAR(MAX), but it still response syntax unclosed.

Why? It's the limit of OpenQuery or MDX?

Thanks for help!

Note:

OPENQUERY does not accept variables for its arguments.

You have to use the query as String values on OPENQUERY.

|||

ManiD,

Thanks for your reply!

But my point is no matter what I declare @.mdxSyntax as NVARCHAR(4000) or NVARCHAR(MAX),

the query result always response syntax unclosed. WHY?

Openquery with a sub-sql referening sqlserver table.

Hello.
I need to do a openquery to a linked server, and get record with id no in a sub select pointing to a table stored in SQLServer.
I have something like this:

select * into tmptable
from openquery (select * from linkedserverTable where id not in (select distinct(id) from sqlserverTable))

How to make sqserverTable not pointing to linked server, but sqlserver ?

Rgds

JCselect *
into tmptable
from openquery (linkedserver,'select * from linkedserverTable')
where id not in (select distinct id from sqlserverTable)

OPENQUERY vs 4-part-tablenames with linked server

Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that the server answers with correct results when I start the query on itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that the server answers with correct results when I start the query on itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||In this case OPENQUERY should return the same result as the straight query...
Do as Tibor says, and check the query plan for both to see if you can learn anything from that...Also check/play with the collation order options on the linked server(although that should not matter with an integer comparison.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||In this case OPENQUERY should return the same result as the straight query...
Do as Tibor says, and check the query plan for both to see if you can learn anything from that...Also check/play with the collation order options on the linked server(although that should not matter with an integer comparison.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen

OPENQUERY vs 4-part-tablenames with linked server

OpenQuery to a LinkedServer

I have SQL Server 2000 with Pervasive SQL 2000i attached
as a linked server. When I run the following...
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT "START_ORDER_NO"
FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0
--The START_ORDER_NO field contains a 48
Changing the SET clause to ...
SET START_ORDER_NO = 1~9
--The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10
--The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
--The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
--The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value I write...
The LinkedServer is Pervasive SQL 2000i using 'OLE DB
Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code
page issue, but I haven't had any luck yet.
Any help would be greatly appreciated.
Hi Bob,
Thanks for your post.
From your descriptions, I understood that you would like to set the number
to be one and it appears to be something else. Correct me if I was wrong.
This issue seems strange, here are some steps I think you could make a try
to see whether it will make any further progress
First of all, try to use xp_enum_loedb_providers listed in the document
below to setup your linked server.
INF: xp_enum_oledb_providers Enumerates the OLE DB Providers
http://support.microsoft.com/?id=216575
Secondly, could you use INTEGER or UINTEGER in Pervasive.SQL 2000 instead
of NUMERIC(8,0)?
Pervasive.SQL 2000 Supported Data Types
http://www.pervasive.com/library/doc...BtrDType2.html
Unfortuantely, I do not have Pervasive SQL 2000 installed and cannot
reporduce it. If I may, I would like to suggest you seeing whether
Pervasive Software had meet this before.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||You understand correctly.
I am using 'OLEDB Provider for ODBC' or MSDASQL
The field types have been defined by the original vendor, Pervasive SQL
2000 is part of a proprietary ERP system. I don't have the luxury of
changing field definitions.
Thanks for the information though.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Bob,
Thanks for your prompt updates letting me know the status of the issue.
First of all, you may use (numeric(8,0), 0) to test whether it will make
any progress.
Secondly, what odbc driver you are using? I think you may try to check this
with that Pervasive ODBC Driver vendor about issue.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

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 q

Hi,
INSERT INTO my table (c1, c2, .cn)
SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"')
How can be retrieved only values From my table2( in the
linked server) that are NOT IN mytable ( or values that
are > max(values of my table )?
(The linked server is an Access, only openquery
statements are working)
Thanks for any suggestions.
DobbyThis is completely untested, but it may give you enough information to =get started...
SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn FROM "mytable2"') A
LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =3D B.PrimaryKeyColumn
WHERE B.PrimaryKeyColumn IS NULL
You could also try to write it using NOT EXISTS as well as NOT IN
-- Keith, SQL Server MVP
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message =news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
> Hi,
> > > INSERT INTO my table (c1, c2, .cn)
> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn > FROM "mytable2"')
> > How can be retrieved only values From my table2( in the > linked server) that are NOT IN mytable ( or values that > are > max(values of my table )?
> > (The linked server is an Access, only openquery > statements are working)
> > Thanks for any suggestions.
> > Dobby
> >|||Hi Keith,
It works fine!
Thank you very much!
Dobby
>--Original Message--
>This is completely untested, but it may give you enough
information to get started...
>SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"') A
>LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =B.PrimaryKeyColumn
>WHERE B.PrimaryKeyColumn IS NULL
>You could also try to write it using NOT EXISTS as well
as NOT IN
>--
>Keith, SQL Server MVP
>"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
>> Hi,
>>
>> INSERT INTO my table (c1, c2, .cn)
>> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
>> FROM "mytable2"')
>> How can be retrieved only values From my table2( in the
>> linked server) that are NOT IN mytable ( or values
that
>> are > max(values of my table )?
>> (The linked server is an Access, only openquery
>> statements are working)
>> Thanks for any suggestions.
>> Dobby
>>
>.
>

OPENQUERY problem with dynamic result set

I am having a problem with OPENQUERY in SQL2005.
SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
This generates an error along the lines of:
The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
that either the object has no columns or the current user does not have
permissions on that object.
I am pretty sure this is becuase my procedure returns a dynamic
resultset with a dynamic number of columns.
I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
other suggestions; however, I still get the same error. I am guessing
possibly, becuase my procedure in turn calls another dynamically
executed OPENQUERY MDX query against an OLAP data source.
The whole reason I am doing this is becuase I need to get the results
of the stored procedure into a table for further manipulation.
Modifying the underlying procedures is not really an option.
My procedure returns a dynamic number of columns so I can't create a
temp table ahead of time to stick the results into.
I have been going around in circles for a while on this one. Any
advice would be much appreciated.Hi
If you granted EXECUTE permission to stored procedure which performs dynamic
sql , it will not be enough , you'll have to grant permissions on the
object/ underlaying tables
However , in SQL Server 2005 you are be able to perfom EXECUTE AS ( If I
remember well) as more power user
<jalbenberg@.yahoo.com> wrote in message
news:1148426602.112398.67180@.i39g2000cwa.googlegroups.com...
>I am having a problem with OPENQUERY in SQL2005.
> SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
> This generates an error along the lines of:
> The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
> that either the object has no columns or the current user does not have
> permissions on that object.
> I am pretty sure this is becuase my procedure returns a dynamic
> resultset with a dynamic number of columns.
> I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
> other suggestions; however, I still get the same error. I am guessing
> possibly, becuase my procedure in turn calls another dynamically
> executed OPENQUERY MDX query against an OLAP data source.
> The whole reason I am doing this is becuase I need to get the results
> of the stored procedure into a table for further manipulation.
> Modifying the underlying procedures is not really an option.
> My procedure returns a dynamic number of columns so I can't create a
> temp table ahead of time to stick the results into.
> I have been going around in circles for a while on this one. Any
> advice would be much appreciated.
>|||Sorry if my post was misleading. This is not a permissions issue - the
queries run fine outside of the OPENQUERY context.
The problem is the dynamic recordset that comes back with an unknown
number of columns, so OPENQUERY cannot properly prepare the statement.

OPENQUERY problem with dynamic result set

OPENQUERY Problem

Hi,
I have created a linked server to oracle.
I executed the query as
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
ora_owner.appointment where update_dtm > to_date(''2007-oct-11
18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
Its executing fine.
But I want to get the date from another table from my sql server.
How can I form the OPENQUERY with a variable(contains date)?
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
tie_owner.rtt_appointment where update_dtm > to_date(''+
@.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
This statement is giving error...
Incorrect sysntax at +
How do I get date in yyyy-mmm-dd hh:mm:ss format?
The same date I will form in the openquery.
This is struggling me a lot. Pls suggest an idea.
Thanks in advanceSome examples
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
<mrajanikrishna@.gmail.com> wrote in message
news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
> Hi,
> I have created a linked server to oracle.
> I executed the query as
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> Its executing fine.
> But I want to get the date from another table from my sql server.
> How can I form the OPENQUERY with a variable(contains date)?
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> tie_owner.rtt_appointment where update_dtm > to_date(''+
> @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> This statement is giving error...
> Incorrect sysntax at +
> How do I get date in yyyy-mmm-dd hh:mm:ss format?
> The same date I will form in the openquery.
> This is struggling me a lot. Pls suggest an idea.
> Thanks in advance
>|||On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Some examples
> DECLARE @.SQLx VARCHAR(500)
> DECLARE @.var VARCHAR(20)
> SET @.var = 'abcd'
> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
> EXEC(@.SQLx)
> <mrajanikris...@.gmail.com> wrote in message
> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have created a linked server to oracle.
> > I executed the query as
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> > Its executing fine.
> > But I want to get the date from another table from my sql server.
> > How can I form the OPENQUERY with a variable(contains date)?
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > tie_owner.rtt_appointment where update_dtm > to_date(''+
> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> > This statement is giving error...
> > Incorrect sysntax at +
> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
> > The same date I will form in the openquery.
> > This is struggling me a lot. Pls suggest an idea.
> > Thanks in advance- Hide quoted text -
> - Show quoted text -
Hi thank u for the reply,
What is the problem in my procedure...
DECLARE @.ApptLastUPdateDateTime varchar(30)
BEGIN
DECLARE @.sql_str VARCHAR(4000)
SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
120), FROM [LastUpdateDateTime]
SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
@.ApptLastUPDateDateTime + ''''
SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
REPLACE(@.sql_str, '''', ''') + ''')'
EXEC @.sql_str
END
I am getting error
The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
01:01:00''')' is not a valid identifier.
I am unable to fix this error.|||Replace EXEC (@.sql) with PRINT @.sql to see what script it creates in order
to debug
<mrajanikrishna@.gmail.com> wrote in message
news:1192715912.147689.145840@.i13g2000prf.googlegroups.com...
> On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Some examples
>> DECLARE @.SQLx VARCHAR(500)
>> DECLARE @.var VARCHAR(20)
>> SET @.var = 'abcd'
>> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
>> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
>> EXEC(@.SQLx)
>> <mrajanikris...@.gmail.com> wrote in message
>> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>>
>> > Hi,
>> > I have created a linked server to oracle.
>> > I executed the query as
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
>> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
>> > Its executing fine.
>> > But I want to get the date from another table from my sql server.
>> > How can I form the OPENQUERY with a variable(contains date)?
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > tie_owner.rtt_appointment where update_dtm > to_date(''+
>> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
>> > This statement is giving error...
>> > Incorrect sysntax at +
>> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
>> > The same date I will form in the openquery.
>> > This is struggling me a lot. Pls suggest an idea.
>> > Thanks in advance- Hide quoted text -
>> - Show quoted text -
>
> Hi thank u for the reply,
> What is the problem in my procedure...
> DECLARE @.ApptLastUPdateDateTime varchar(30)
> BEGIN
> DECLARE @.sql_str VARCHAR(4000)
> SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
> 120), FROM [LastUpdateDateTime]
>
> SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
> @.ApptLastUPDateDateTime + ''''
> SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
> REPLACE(@.sql_str, '''', ''') + ''')'
> EXEC @.sql_str
> END
> I am getting error
> The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
> tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
> 01:01:00''')' is not a valid identifier.
> I am unable to fix this error.
>

OpenQuery not working after applyin SQL Server SP4

SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')

Issue:

We have Linked Server setup to OLAP Server. which was working fine for long time. After applying SP4 on SQL Server, we started getting this error. But the same query runs from another server with SP3.

After applying SP4, I have also applied 2 hotfixes to SQL Server. One hot fix is related to using using memory more than 3GB and another hotfix is for this Linked server issue. but still I have the problem.

SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')

Error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSOLAP' reported an error.
[OLE/DB provider returned message: Database 'PLS' does not exist.]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize returned 0x80004005: ].

Appreciate your help.

Thanks,

Varad

sql

openquery datasource not working

Hi,
I hope someone can help with a query that's puzzling me.
I have this query that I can't understand.
SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
'SELECT createdate,loginname FROM Master.dbo.syslogins where
isntname=1'
) AS p
When I run it, it returns data, but not from the server I specified in
the Datasource. In fact I can change the Datasource to anything even
gibberish and it still returns the same set of records.On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> Hi,
> I hope someone can help with a query that's puzzling me.
> I have this query that I can't understand.
> SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> isntname=1'
> ) AS p
> When I run it, it returns data, but not from the server I specified in
> the Datasource. In fact I can change the Datasource to anything even
> gibberish and it still returns the same set of records.
The connection string that you are using is wrong. Instead of
datasource=server1 it should be server=server1.
Adi|||The argument is called "Data Source" not "datasource".
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/|||On Sep 18, 4:25 pm, Adi <adic...@.hotmail.com> wrote:
> On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> > Hi,
> > I hope someone can help with a query that's puzzling me.
> > I have this query that I can't understand.
> > SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> > Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> > 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> > isntname=1'
> > ) AS p
> > When I run it, it returns data, but not from the server I specified in
> > the Datasource. In fact I can change the Datasource to anything even
> > gibberish and it still returns the same set of records.
> The connection string that you are using is wrong. Instead of
> datasource=server1 it should be server=server1.
> Adi
Thanks, that did the trick.I can't think where I got the Datasource
from.|||On Sep 18, 4:30 pm, ML <M...@.discussions.microsoft.com> wrote:
> The argument is called "Data Source" not "datasource".
> ML
> --
> Matija Lah, SQL Server MVPhttp://milambda.blogspot.com/
Thanks for your response. I did try Data Source but it did the same
thing. Problem resolved with Adi's response but thanks anyway.

OPENQUERY and string

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''')

sql

Wednesday, 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".

OpenQuery & Quotes Syntax

I'm trying to select records within a date range from a linked server. If I
don't include the WHERE part and it's variables, my sql retrieves all
records. I also can hard code the WHERE criteria like "loadID=500".
SQL returns an error on my below code saying"
Incorrect syntax near '+'
What am I doing wrong with my commas and plus syntax? It looks correct, but
doesn't like my plus syntax for some reason.
CODE ***********
declare @.dtStartDate datetime, @.dtEndDate datetime
set @.dtStartDate = GetDate()
set @.dtEndDate = '20060701'
SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM load
WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112) +
'''' + ' AND '
+ '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112) +
'''')On my system that string works fine. Using the current date for the
variables I get:
SELECT loadID, loadDate FROM load WHERE loadDate >= '20060515' AND
loadDate <= '20060515'
Are you certain that this is the line with the error?
Try to avoid building strings as parameters like this. Instead, assign the
string to a variable, and pass the variable as the parameter. You can
output the variable in order to make sure it contains the correct string.
You can then execute this string seperately in Query Analyzer to simplify
troubleshooting.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23jm8YzCeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> I'm trying to select records within a date range from a linked server. If
I
> don't include the WHERE part and it's variables, my sql retrieves all
> records. I also can hard code the WHERE criteria like "loadID=500".
> SQL returns an error on my below code saying"
> Incorrect syntax near '+'
> What am I doing wrong with my commas and plus syntax? It looks correct,
but
> doesn't like my plus syntax for some reason.
>
> CODE ***********
> declare @.dtStartDate datetime, @.dtEndDate datetime
> set @.dtStartDate = GetDate()
> set @.dtEndDate = '20060701'
> SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
> FROM load
> WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112)
+
> '''' + ' AND '
> + '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112)
+
> '''')
>|||SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM
load
WHERE
loadDate >= convert(char(8), getdate(), 112) +
AND loadDate <= convert(char(8), ''20060701'', 112)')
Could u please try with this?

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...

OpenQuery

OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')Ths will also work
exec server1.bb01_db.dbo.sp_executesql N'select * from bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001'' and
tran_dt =''2002-12-02'''
If those are datetimes then you should use yyyymmdd especially with remote
server calls just in case the dateformats are different.
"CurtM" wrote:
> OPENQUERY will solve your problem. Examples:
> --This will fail:
> select * from server1.bb01_db.dbo.bb01 (nolock)
> where process_dt = '2002-12-02' and mid = '03301001' and
> tran_dt = '2002-12-02'
> --This will work if you have your linked servers set up --
> correctly:
> select * from openquery(server1, 'select * from
> bb01_db.dbo.bb01 (nolock)
> where process_dt = ''2002-12-02'' and mid = ''03301001''
> and tran_dt = ''2002-12-02''')
>sql

Opening table in SQL Server 2005?

This ought to be easy. But I can't figure out how to do it.
Using Enterprise Manager in SQL Server 2000, I point to a table, select it,
right click and choose Open Table-> Return all rows. Now I have the table
open and I can edit to my heart's content.
How can I do the same thing in SQL Server 2005?
Hi
With Beta 2 of SQL Server 2005, that functionality is not included. It may
return in Beta 3.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David" <David@.blackdeck.com> wrote in message
news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> This ought to be easy. But I can't figure out how to do it.
> Using Enterprise Manager in SQL Server 2000, I point to a table, select
it,
> right click and choose Open Table-> Return all rows. Now I have the table
> open and I can edit to my heart's content.
> How can I do the same thing in SQL Server 2005?
|||You got to be kidding! I use that all the time! Will it definitely be in
the final release?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> With Beta 2 of SQL Server 2005, that functionality is not included. It may
> return in Beta 3.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "David" <David@.blackdeck.com> wrote in message
> news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> it,
>
>
sql

Friday, March 30, 2012

OpenQuery with variable

OpenQuery With Large String?

Openquery with a sub-sql referening sqlserver table.

OPENQUERY vs 4-part-tablenames with linked server

OPENQUERY vs 4-part-tablenames with linked server

OpenQuery to a LinkedServer

OpenQuery Syntax Using Variables

Openquery q

OPENQUERY problem with dynamic result set

OPENQUERY problem with dynamic result set

OPENQUERY Problem

OpenQuery not working after applyin SQL Server SP4

openquery datasource not working

OPENQUERY and string

Wednesday, March 28, 2012

OPENQUERY and parameters

OpenQuery & Quotes Syntax

OPENQUERY

OPENQUERY

OpenQuery

Opening table in SQL Server 2005?

Online Data Transform

Blog Archive

About Me