Online Data Transform: 2012

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 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?sql

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 join

Hi

I have a SP which queries a linked server using OpenQuery function.
The remote query includes a join and an IN clause to get the desire result. ( The linked server uses Transoft ODBC driver.)

The qry looks something like this:

select * from OpenQuery(SERVER1,
'select
distinct C.item, C.operation, C.STD_OPERATION,
D.operation_desc , C.operation_desc operation_desc
from TABLE1 C left join
(select distinct operation, operation_desc from TABLE1
where operation in (select distinct STD_OPERATION from TABLE1 where
item = ''9999999'' AND STD_OPERATION <> 0)
AND item = ''STANDARD'') D
on D.operation = C.STD_OPERATION where C.item = ''9999999'' ')

When I run this Qry I get the following error:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Transoft][TSODBC][usqlsd]')' expected here (DISTINCT)]

Any help would be greatly appreciated.
thxIs the problem that Transoft can't handle the distinct keyword? It is SQL-92 compliant, but maybe the driver can't handle it? Have you tried removing distinct and running the query again?

If this is the problem, you should be able to work around the problem using a group by clause.

Hth.

Paul Barbin

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. sp_executesql which is better?

I have recently seen the OPENQUERY function in a stored procedure which
was used to INSERT values into a table on a remote server. However, I
normally use the sp_executesql stored proc.
Can any one shed some light on which method is better and in what
cases?
The following code is from Microsoft website, not my own.
http://msdn.microsoft.com/library/d...br />
5xix.asp
<MS CODE>
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
</MS CODE>
VS.
<MYCODE>
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
-- OR
OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
</MYCODE>
Thanks for your time folks.
Johnny DWhy would you even consider sp_executesql in your case? There is no need to
use dynamic sql to access a linked server.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148649898.280530.174390@.j73g2000cwa.googlegroups.com...
>I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
> The following code is from Microsoft website, not my own.
> http://msdn.microsoft.com/library/d... />
z_5xix.asp
> <MS CODE>
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
>
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
> </MS CODE>
> VS.
> <MYCODE>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
> -- OR
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
> </MYCODE>
>
> Thanks for your time folks.
>
> Johnny D
>|||Sorry Andrew, this was an oversight in me writing a simple query to
illustrate my question...
The reason for this is I would use a cursor to loop through my
different servernames
DECLARE @.rc INT
DECLARE @.v_sql NVARCHAR(4000)
DECLARE @.vc_servername VARCHAR(100)
DECLARE c_myservers CURSOR
FOR SELECT
servername
FROM listservers
WHERE active=1
FOR READ ONLY
OPEN c_myservers
FETCH NEXT FROM c_myservers INTO @.vc_servername
WHILE (@.@.FETCHSTATUS = 0 )
BEGIN
SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
'].dbname.joe.titles'
EXEC @.rc = sp_executesql @.v_sql
-- if @.rc <> 0
-- etc...
FETCH NEXT FROM c_myservers INTO @.vc_servername
END
CLOSE c_myservers
DEALLOCATE c_myservers
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'|||Well one key point of using OPENQUERY is that it passes the statement to the
other server where it is executed as is. That way the remote server can
choose the proper plan for that statement without regard to what the rest of
the statement is that was issued locally. Kind of hard to explain but it is
truly a pass-through query where as a linked server query may be influenced
by the rest of the statement. For instance a join to a local table.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148652836.663893.44660@.g10g2000cwb.googlegroups.com...
> Sorry Andrew, this was an oversight in me writing a simple query to
> illustrate my question...
> The reason for this is I would use a cursor to loop through my
> different servernames
> DECLARE @.rc INT
> DECLARE @.v_sql NVARCHAR(4000)
> DECLARE @.vc_servername VARCHAR(100)
> DECLARE c_myservers CURSOR
> FOR SELECT
> servername
> FROM listservers
> WHERE active=1
> FOR READ ONLY
> OPEN c_myservers
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> WHILE (@.@.FETCHSTATUS = 0 )
> BEGIN
> SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
> '].dbname.joe.titles'
> EXEC @.rc = sp_executesql @.v_sql
> -- if @.rc <> 0
> -- etc...
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> END
> CLOSE c_myservers
> DEALLOCATE c_myservers
>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
>|||Johnny D (john.dacosta@.gmail.com) writes:
> I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
I think they are as comparable as apples and oranges.
Apparently you loop over servers. That is nothing OPENQUERY can help
you with - the server name must be a constant.
Rather your choice is between:
SELECT @.sql = 'SELECT ... FROM ' + @.server + 'catalog.schema.tbl'
and
SELECT @.sql = 'SELECT ... FROM OPENQUERY(' + @.server + ', ' +
'''SELECT ... FROM catalog.schema.tbl'')'
That is, accessing the table in four-partnotation, or running a
passthrough query.

><MYCODE>
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
></MYCODE>
Ehum, I don't think you will find sp_executesql on Oracle...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

OPENQUERY vs EXECUTE on a linked server -- what is better?

Can anyone tell me, if, generally, the performance or the cost of executing a pass-through command on a linked server in SQL Server 2005 would be better using OPENQUERY or the new option with EXECUTE -- whether the two servers are on the same box or not? I haven't been able to find a comparison between the two.

Have there been any tests of the difference?

What effect on performance is there with 'rpc out' set with sp_serveroption so EXECUTE can be used?

To be more specific I have a development box with SQL Server 2005 and Oracle 9.2.

The new option with EXECUTE would be something like the example in MSDN (Example J.) at:

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

EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO

Well, openquery/rowset/datasource only takes literal string (i.e. you cannot user string variable). The new Execute syntax allows you do the same pass-through as with openquery but also allows the use of variable. If you're doing lots of cross server invocation, this is certainly a major benefit.

As for perf implication, there wouldn't be much of a difference between both methods. I would bet the Exec would actually be better.

sql

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

This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C45DBF.3F502450
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table = where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero = hits.
Can anybody help?
Jochen
--=_NextPart_000_000A_01C45DBF.3F502450
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

what is the difference between =

a) select * from = server.database.owner.table where [id] =3D 15

and

b) select * from openquery(server, = 'select * from database.owner.table where [id] =3D 15')

I have the effect that b) gives the = correct result while a) has zero hits.

Can anybody help?

Jochen

--=_NextPart_000_000A_01C45DBF.3F502450--This is a multi-part message in MIME format.
--=_NextPart_000_0261_01C45DD6.DCF0CE50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 Br=FCggemann" <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] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table =where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0261_01C45DD6.DCF0CE50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

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 Br=FCggemann" wrote =in message news:udF3I4aXEHA.736@.T=K2MSFTNGP10.phx.gbl...
Hi,

what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15

and

b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')

I have the effect that b) gives the =correct result while a) has zero hits.

Can anybody help?

Jochen

--=_NextPart_000_0261_01C45DD6.DCF0CE50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0043_01C45DD2.46A106D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 Br=FCggemann" <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] =3D 15
and
b) select * from openquery(server, 'select * from =database.owner.table where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0043_01C45DD2.46A106D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

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. Withselect * from server.database.owner.table where [id] < 6000the result =table ist still empty. Withselect * from server.database.owner.table where =[id] < 6001all 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" 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 Br=FCggemann" =wrote in message news:udF3I4aXEHA.736@.T=K2MSFTNGP10.phx.gbl...
Hi,

what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15

and

b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')

I have the effect that b) gives the =correct result while a) has zero hits.

Can anybody help?

Jochen

--=_NextPart_000_0043_01C45DD2.46A106D0--|||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 Brüggemann" <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|||This is a multi-part message in MIME format.
--=_NextPart_000_0050_01C45DAD.F4088160
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 Br=FCggemann" <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] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table =where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0050_01C45DAD.F4088160
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

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 MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Jochen Br=FCggemann" wrote =in message news:udF3I4aXEHA.736@.T=K2MSFTNGP10.phx.gbl...
Hi,

what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15

and

b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')

I have the effect that b) gives the =correct result while a) has zero hits.

Can anybody help?

Jochen

--=_NextPart_000_0050_01C45DAD.F4088160--

OpenQuery using parameters

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

OpenQuery using a variable

Hi,

Here's what I did:

1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @.CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @.CQUERY = 'SELECT ...'

Now, when I try to execute the OpenQuery method using that variable, it fails.

Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @.CQUERY)

I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@.CQUERY'.

Don't tell me I can't use a variable instead of a static query? What am I doing wrong?

Thanks,

Skip.i don't think you can do that, putting a variable in the from clause

you'll have to use dynamic sql

so put that statement in a EXEC(.....)|||Alright,

I tried it but I'm still having troubles with it. Here's my code (simplified version):

DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)

When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

SELECT * FROM mytable WHERE last_name =

which is incorrect.

Any other suggestions?

Thanks again,

Skip.|||sorry if i mislead you the first time, what i meant is use EXEC if you are going to use a variable for openquery.

if you are not using a variable for openquery, then just do this:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = ''DOE''')|||Originally posted by Skippy_sc
Alright,

I tried it but I'm still having troubles with it. Here's my code (simplified version):

DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)

When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

SELECT * FROM mytable WHERE last_name =

which is incorrect.

Any other suggestions?

Thanks again,

Skip.

Try this for instance:

DECLARE @.CQUERY varchar(8000)
SET @.CQUERY = 'SELECT * FROM OPENQUERY(MSSQL20,
''SELECT top 10 * FROM master.dbo.sysobjects where name=''+'sysobjects'+'')'
select @.CQUERY
EXECUTE(@.CQUERY)|||Thank you very much fattyacid, it works fine now!

Skipsql

OPENQUERY UPDATE Syntax help needed

Hi All

I am updating a local table based on inner join between local table
and remote table.

Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

Thanks
RS(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

I am updating a local table based on inner join between local table
and remote table.
>
Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3
>
This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

UPDATE LocalTable
SET Field1 = B.Field1
FROM OPENQUERY(LINKEDSERVER,
'SELECT Field1, Field2, Field3 FROM dbname.dbo.RemoteTable) B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

I would not really expect this to perform better.

Distributed queries are always difficult, but it's difficult to suggest
anything without further knowledge about the table. How big are the
two tables?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

openquery update and optimistic concurrency

Hi, I need to update a mySQL database through a linked server in SQL.

I can successfully add, delete, but struggle to update a row twice.

exec ('UPDATE OPENQUERY (SIBC, SELECT UID, value1, value2 FROM table1 WHERE UID= "SCEP"'')
SET value1= "hello" WHERE UID= "SCEP"')

The first time I run the update, it succeeds, but thereafter I get the following error message :

OLE DB provider 'MSDASQL' could not UPDATE table '[MSDASQL]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].

Any ideas ?

Thanks.

Here are some suggestions:
1. This might be a specific limitation in the ODBC driver and/or how it interacts with the OLE/DB for ODBC drivers Provider (MSDASQL). You might try recreating this on another type of back-end to see if it reproduces there.

2. If another process is updating values on the mysql database, you may very well have optimistic concurrency issues...

3. You could try using 4-part names instead of openquery:
update sibc.dbo.db.table1 set value1='hello' where uid='scep';

4. You could do a pass-through query, as you are really only running queries against this back-end and not passing any data from SQL Server.

Conor Cunningham

|||Hi Conor, thanks for your reply.

The only process updating the system is my application as it's on the development environment.

I have an unusual issue in that I can update a datetime field in mySQL only once I've provided it a value explicitly through a mySQL query analyser utility.

The other odd problem I have is that when I perform the update, it has to actually update a field otherwise it fails, thus if I try update a column TEMP1 with a value of 1, but it already contains a value of 1, it fails.

PS: the provider is a mySQL provider, which doesn't allow 4 part naming in SQL.

I've a feeling the issue could exist with the ODBC driver, but unfortunitely the mySQL and Microsoft communities do not seem to work together too nicely.

Thanks for your help.

Karlo
|||This looks unclear.

It doesen't make sense to me to update the results of a select query.
If this worked the first time, my guess is that the table in the database did not change, only the clients memory-representation of it, and this confused the driver at the second try.

Not sure if I'm on the right track, but you could try to send the update query directly to the linked server.

openquery update and optimistic concurrency

Hi, I need to update a mySQL database through a linked server in SQL.

I can successfully add, delete, but struggle to update a row twice.

exec ('UPDATE OPENQUERY (SIBC, SELECT UID, value1, value2 FROM table1 WHERE UID= "SCEP"'')
SET value1= "hello" WHERE UID= "SCEP"')

The first time I run the update, it succeeds, but thereafter I get the following error message :

[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]

Thanks.

2. If another process is updating values on the mysql database, you may very well have optimistic concurrency issues...

3. You could try using 4-part names instead of openquery:
update sibc.dbo.db.table1 set value1='hello' where uid='scep';

4. You could do a pass-through query, as you are really only running queries against this back-end and not passing any data from SQL Server.

Conor Cunningham

|||Hi Conor, thanks for your reply.

The only process updating the system is my application as it's on the development environment.

I have an unusual issue in that I can update a datetime field in mySQL only once I've provided it a value explicitly through a mySQL query analyser utility.

PS: the provider is a mySQL provider, which doesn't allow 4 part naming in SQL.

I've a feeling the issue could exist with the ODBC driver, but unfortunitely the mySQL and Microsoft communities do not seem to work together too nicely.

Thanks for your help.

Karlo|||This looks unclear.

Not sure if I'm on the right track, but you could try to send the update query directly to the 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 throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,

Sorry but exiting problem!

The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.

Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).

I'm importing data from Excel. Before import, I want to validate it against my master list values.

[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]

In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]

I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.

I think you got what the situation is!! Woh!

Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!

- The Code -

create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx

create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')

-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine

Thanks in advance...

Hi,

normally the OLEDB provider is expecting to get something back from the query. So try to put in either a return 0 or a simple Select or string stating ('Statement executed') at the bottom of the query.

BTW, why don′t you just use the select statement rather than creating tables and so on.. select 1 union select 2 ?

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de

sql

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 syntax for function

There is a very complex query where I'm trying to call a function from a
linked server with input paramaters from the local database. This is the
general idea:
select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
from table1 t1
and table t2
where ...
I get a syntax error that doesn't recognize t1 and t2. How should I fix thi
s?
Thanks,Openquery() (i.e. ad-hoc/pass through function) only takes literal strings.
So, it's not possible to pass in any parameters.
Also, it's not possible to call a remote user-defined function in sqlserver
(i.e. srv.db.dbo.udf() is not allowed). So, you would have to create the
function locally.
-oj
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||Lisa
Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Statement
Table-Valued UDF?
Look at this technique written by Itzik Ben-Gan
CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
AS
BEGIN
RETURN(SELECT newinvid FROM OPENQUERY([server_name],
'SET NOCOUNT ON; DECLARE @.invid AS INT;
UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
SELECT @.invid AS newinvid;') AS O)
END
CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
AS
BEGIN
RETURN(
SELECT newinvid
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
INSERT INTO tempdb..Seq2 DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
END
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||I'm looking at more in line with your first function. but I have two
parameters (one an integer) and one a date that is being to the function tha
t
I want to use in the openquery statment. I can't get the sql right for it
though.
"Uri Dimant" wrote:

> Lisa
> Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Stateme
nt
> Table-Valued UDF?
> Look at this technique written by Itzik Ben-Gan
> CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
> AS
> BEGIN
> RETURN(SELECT newinvid FROM OPENQUERY([server_name],
> 'SET NOCOUNT ON; DECLARE @.invid AS INT;
> UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
> SELECT @.invid AS newinvid;') AS O)
> END
> CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
> AS
> BEGIN
> RETURN(
> SELECT newinvid
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> INSERT INTO tempdb..Seq2 DEFAULT VALUES
> ROLLBACK;
> SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
> END
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
>
>

OPENQUERY Returning only 2 Rows

I have successfully set up a linked server to an AS400. However, when I try to set up a view for it using OPENQUERY, I aonly get 2 records. The SQL code itself should return all of the records in the file. Does anyone know if this is a quirk of SQL Server and what the workaround might be?I am posting a reply to my own query, as I have found the solution. It turns out that I had the RECBLOCK setting for Client Access Express DSN set to 0. After I changed it to 0, I was able to get all of the records.|||I am posting a reply to my own query, as I have found the solution. It turns out that I had the RECBLOCK setting for Client Access Express DSN set to 0. After I changed it to 1, I was able to get all of the records.|||Sorry for the confusion with the above 2 replies.

The sentence that reads, "After I changed it to 0, I was able to get..." should say, "After I changed it to 1 , I was able to get all of the records."|||Thanks for posting the solution. :)

OPENQUERY Question. How to update using 2 tables

All,

Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.

Some of the relevant information is as follows:

ORACLE_HBCPRD04 is a linked Oracle Server.
SITEADDRESS is a table in Oracle
#SiteAddress_New is a table in SQL Server.

UPDATE ORACLE_HBCPRD04...SITEADDRESS
SET
CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR1 END,
CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR2 END ,
CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR3 END,
CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR4 END ,
CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
SiteAddress_New.CTY_NM END,
ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
SiteAddress_New.ST_ABBR END,
POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
SiteAddress_New.POST_CD END,
CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTY_NM END,
CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTRY_NM END,
ADDR_STAT = NULL ,
LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE

FROM
ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
#SiteAddress_New SiteAddress_New ON
SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID

WHERE
UPPER(SiteAddress_New.PROCESS_CODE) = 'U'

Best Regards,

addi"addi" <addi_s@.hotmail.com> wrote in message
news:6f426fb3.0406090845.6afdbf32@.posting.google.c om...
> All,
> Can someone help me with the following SQL and help me write it in an
> OPENQUERY format. I am running the following code from a SQL Server 7
> box, trying to update a table in an Oracle Linked Server. The code
> runs fine, except it takes almost an hour to complete. I know if I run
> via OPENQUERY,I can get the same done in much less time.
> Some of the relevant information is as follows:
> ORACLE_HBCPRD04 is a linked Oracle Server.
> SITEADDRESS is a table in Oracle
> #SiteAddress_New is a table in SQL Server.
> UPDATE ORACLE_HBCPRD04...SITEADDRESS
> SET
> CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR1 END,
> CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR2 END ,
> CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR3 END,
> CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR4 END ,
> CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CTY_NM END,
> ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
> SiteAddress_New.ST_ABBR END,
> POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
> SiteAddress_New.POST_CD END,
> CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTY_NM END,
> CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTRY_NM END,
> ADDR_STAT = NULL ,
> LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE
> FROM
> ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
> #SiteAddress_New SiteAddress_New ON
> SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID
> WHERE
> UPPER(SiteAddress_New.PROCESS_CODE) = 'U'
> Best Regards,
> addi

I don't really understand your question - OPENQUERY() executes entirely on
the linked server, so it would not be possible to join to a local MSSQL
table.

It's hard to say what the issue is without knowing more about how many rows
are involved, but you might want to look at the REMOTE join hint, to see if
a remote join is faster. This may help if the MSSQL table is much smaller
than the Oracle one.

OPENQUERY question

I'd like to use a linked server to a Setcim database to retrieve results fro
m
a procedure in the Setcim database.
The syntax used to call the view in Setcim is:
start record 'BSW1View'
which works when using the SQLplus tools for Setcim.
I was assuming I could OPENQUERY to call this record and then put the data
into a SQL Server database but I get syntax errors when I try various
combinations of the following:
SELECT *
FROM OPENQUERY(PRISM,'start record 'BSW1View'')
The issue appears to be the single quotes around the procedure name.
Can I do what I want using OPENQUERY? If not, what options are available?
Thanks in advance,
RaulDouble them (the inner apostrophes).
SELECT *
FROM OPENQUERY(PRISM,'start record ''BSW1View''')
AMB
"Raul" wrote:

> I'd like to use a linked server to a Setcim database to retrieve results f
rom
> a procedure in the Setcim database.
> The syntax used to call the view in Setcim is:
> start record 'BSW1View'
> which works when using the SQLplus tools for Setcim.
> I was assuming I could OPENQUERY to call this record and then put the data
> into a SQL Server database but I get syntax errors when I try various
> combinations of the following:
> SELECT *
> FROM OPENQUERY(PRISM,'start record 'BSW1View'')
> The issue appears to be the single quotes around the procedure name.
> Can I do what I want using OPENQUERY? If not, what options are available?
> Thanks in advance,
> Raul
>|||Your suggestion worked. The only problem is I got the following error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'start record 'BSW1View''. The OLE DB provider
'MSDASQL' indicates that the object has no columns.
I'll modify the procedure on the Setcim side and try again.
Thanks for the help,
Raul
"Alejandro Mesa" wrote:
> Double them (the inner apostrophes).
> SELECT *
> FROM OPENQUERY(PRISM,'start record ''BSW1View''')
>
> AMB
> "Raul" wrote:
>

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 Problems with Date Function Different MS-SQL 7 / MS-SQL

Hi
It is a change in the Microsoft Oracle driver to support the newer versions
of Oracle. In effect, it is a change required by Oracle that Microsoft had t
o
comply with so that the driver would continue to work. AFAIK, the change was
introduced in MDAC v2.5
Regards
Mike
"Klaus" wrote:

> hi,
>
> Currently we move a couple of SQL Server 7.0 to SQL Server 2000 and right
> now we habe big problems with a OPENQUERY Statement
> The SQL Server 7.0 and Windows 2000 is a german version, the MS-SQL Server
> 2000 and Windows 2000 is in english
>
> The Query on the MS-SQL Server 7 works fine
> SELECT * FROM OPENQUERY (ABKG002,
> 'SELECT DISTINCT NODP.AU_AUD_BENUTZER AS BA_AUD_ERST_VON FROM
> V3FADM.AUODP_ORDERPOS_OTAB NODP WHERE NODP.AU_AUD_ZEITPUNKT >
> ''01.03.2005''')
>
> With the same Query under MS-SQL 2000 is the result
> Server: Msg 7330, Level 16, State 2, Line 1
> Could not fetch a row from OLE DB provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-01843: not a valid month]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returne
d
> 0x80040e07].
>
>
> if we change the Query like below, the OPENQUERY is fine. IS THIS REALLY
> only the way the OPENQUERY works fine? That means for us we have to recrea
te
> a lot of VB Applications
> SELECT * FROM OPENQUERY (ABKG002, 'SELECT DISTINCT NODP.AU_AUD_BENUTZER AS
> BA_AUD_ERST_VON FROM V3FADM.AUODP_ORDERPOS_OTAB NODP WHERE
> NODP.AU_AUD_ZEITPUNKT > to_date(''22.03.2005'',''dd.mm.yyyy'')')
>
>
> Has anybody an IDEA?
>
>
> thx
> klaus
>
>
>Hi Mike
thanks for the very fast and good answer !
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:5C2EC99E-CB18-4B1D-8DCA-6A04ED22255C@.microsoft.com...
> Hi
> It is a change in the Microsoft Oracle driver to support the newer
> versions
> of Oracle. In effect, it is a change required by Oracle that Microsoft had
> to
> comply with so that the driver would continue to work. AFAIK, the change
> was
> introduced in MDAC v2.5
> Regards
> Mike
> "Klaus" wrote:
>

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 Parameter

I am trying to do the following:

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

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

OPENQUERY on AS 2005 produces different results than AS 2000

I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:

With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '

select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])

Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.

Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance

Any ideas at all? I am still having this issue and it needs to be resolved.

Thanks

OPENQUERY on AS 2005 produces different results than AS 2000

I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:

With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance

Any ideas at all? I am still having this issue and it needs to be resolved.

Thanks

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 ISSUE

I am trying to pass a variable to an openquery stmt within a proc. The
following worked a couple of times, but I am receiving the error below now ?
Any suggestions on a method to pass the variable into the OPENQUERY Statemen
t
?
CREATE PROCEDURE usp_GetData (@.REQUEST_ID as varchar(10))
AS
declare @.RESULT varchar(20)
Declare c Cursor For
SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM oracle_view
WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Open C
Fetch c into @.request_ID, @.RESULT -- Get First record data
While @.@.FETCH_STATUS = 0
Begin
INSERT INTO RESULTS (REQUEST_ID, RESULT)
VALUES (@.request_ID, @.RESULT)
Fetch c into @.request_ID, @.RESULT -- Get Next Record
End
Close C
Deallocate C
GO
-- Error received.
Server: Msg 7330, Level 16, State 2, Procedure usp_GetRequestID_Data, Line 2
2
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01722: invalid number> SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM
> oracle_view WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Usually it helps to manually evaluate the expression you are trying to execu
te.
DECLARE @.TEST VARCHAR(200)
SET @.TEST = 'SELECT REQUEST_ID, RESULT FROM oracle_view WHERE REQUEST_ID=''+
@.REQUEST_ID+'''
SELECT @.TEST|||Hi,
as far as I know, OPENQUERY cannot accept variable.
normally what I do is to create a dynamic sql string first then only execute
it.
In BOL, you should be able to find something like this:
--
OPENQUERY
Executes the specified pass-through query on the given linked server, which
is an OLE DB data source. The OPENQUERY function can be referenced in the
FROM clause of a query as though it is a table name. The OPENQUERY function
can also be referenced as the target table of an INSERT, UPDATE, or DELETE
statement, subject to the capabilities of the OLE DB provider. Although the
query may return multiple result sets, OPENQUERY returns only the first one.
Syntax
OPENQUERY ( linked_server , 'query' )
Arguments
linked_server
Is an identifier representing the name of the linked server.
'query'
Is the query string executed in the linked server.
Remarks
OPENQUERY does not accept variables for its arguments.
--
As you can see, variable is not allowed. I am not so sure about SQL Server
2005.
I get this from SQL Server 2000 BOL.
hope this will help.
Leo

OPENQUERY Informix Dirty Read

Has anyone know of a way to ensure that query on a remote database (Informix
in this case) using OPENQUERY [via a linked server] does not start a
transaction. If the query in is written directly on Informix, you would
issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
However in OPENQUERY you cannot issue:
SELECT *
FROM OPENQUERY(linkinfx,
' SET ISOLATION TO DIRTY READ
SELECT field1
FROM table1
'
I was wondering if an ODBC escape sequence might work:
SELECT *
FROM OPENQUERY(linkinfx,
' {SET ISOLATION TO DIRTY READ}
SELECT field1
FROM table1
'
All though it does not error, I am not sure that it does not start a
transaction. Unfortuanaly, I don't have a local Informix system to test
this against.
The Informix Linked server is set up via ODBC. Preferrable I would like to
set a session level setting on the Linked Server to set the transaction
isolation level to be a read uncommitted value.
Any suggestions would be appreciated.
MikeMichael
Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
"Michael McCallum" <mmccallum@.honovi.com> wrote in message
news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Has anyone know of a way to ensure that query on a remote database
> (Informix in this case) using OPENQUERY [via a linked server] does not
> start a transaction. If the query in is written directly on Informix, you
> would issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
> However in OPENQUERY you cannot issue:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' SET ISOLATION TO DIRTY READ
> SELECT field1
> FROM table1
> '
> I was wondering if an ODBC escape sequence might work:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' {SET ISOLATION TO DIRTY READ}
> SELECT field1
> FROM table1
> '
> All though it does not error, I am not sure that it does not start a
> transaction. Unfortuanaly, I don't have a local Informix system to test
> this against.
> The Informix Linked server is set up via ODBC. Preferrable I would like
> to set a session level setting on the Linked Server to set the transaction
> isolation level to be a read uncommitted value.
> Any suggestions would be appreciated.
> Mike
>|||In SQL Server ti would be Read Uncommitted, in Informix I believe that it is
Dirty Read.
In either case, I am trying to prevent the Informix system (and SQL Server)
from starting a transaction.
Thanks, Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekROn4lpFHA.3004@.TK2MSFTNGP15.phx.gbl...
> Michael
> Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
>
> "Michael McCallum" <mmccallum@.honovi.com> wrote in message
> news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
>

Friday, March 30, 2012

OpenQuery with variable

OpenQuery with variable

OpenQuery With Large String?

OpenQuery with join

Openquery with a sub-sql referening sqlserver table.

OPENQUERY vs. sp_executesql which is better?

OPENQUERY vs EXECUTE on a linked server -- what is better?

OPENQUERY vs 4-part-tablenames with linked server

OPENQUERY vs 4-part-tablenames with linked server

OPENQUERY vs 4-part-tablenames with linked server

OpenQuery using parameters

OpenQuery using a variable

OPENQUERY UPDATE Syntax help needed

openquery update and optimistic concurrency

openquery update and optimistic concurrency

OpenQuery to a LinkedServer

OPENQUERY throws Error 7357 when the source SP uses temporary table.

OpenQuery Syntax Using Variables

Openquery syntax for function

OPENQUERY Returning only 2 Rows

OPENQUERY Question. How to update using 2 tables

OPENQUERY question

Openquery q

OpenQuery Problems with Date Function Different MS-SQL 7 / MS-SQL

OPENQUERY problem with dynamic result set

OPENQUERY problem with dynamic result set

OPENQUERY Problem

OPENQUERY Parameter

OPENQUERY on AS 2005 produces different results than AS 2000

OPENQUERY on AS 2005 produces different results than AS 2000

OpenQuery not working after applyin SQL Server SP4

OPENQUERY ISSUE

OPENQUERY Informix Dirty Read

Online Data Transform

Blog Archive

About Me