Online Data Transform: microsoft

Showing posts with label microsoft. 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 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. :)

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

Online Data Transform

Blog Archive

About Me