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

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?
JochenJochen
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 connecti
on 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 delet
e 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 th
e 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 connecti
on 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 s
uggest 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

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

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

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

Openquery Help Requested

sql server = server A
sybase = server B (set up as a linked server in A)
I have a stored procedure in A that contains the following statement:
SELECT * FROM OPENQUERY(B, 'exec storedproc')
which runs the procedure in B just fine. It truncates a table in B and
then populates it with data from various queries. The issue is later in
the stored procedure issued from A I perform an insert where I grab the
data inserted into the table in B and insert that data into a table in
A. It seems as though everytime a number of records are inserted into
the table in A the statement above: SELECT * FROM OPENQUERY(B, 'exec
storedproc') is run again and again. Does anybody know why this is
occurring and how I can stop this from occurring?
Thanks,
E> the stored procedure issued from A I perform an insert where I grab the
> data inserted into the table in B and insert that data into a table in
> A. It seems as though everytime a number of records are inserted into
> the table in A the statement above: SELECT * FROM OPENQUERY(B, 'exec
> storedproc') is run again and again. Does anybody know why this is
> occurring and how I can stop this from occurring?
You mean that later within 'storedproc' you are perfoming insert into a
table that is resided on server 'A' and you have seen in SQL Server Profiler
that it runs SELECT * FROM OPENQUERY(B, 'exec> storedproc') many times. Am
I right?
"w" <ebrooks@.sbcglobal.net> wrote in message
news:1136953074.780909.282800@.g14g2000cwa.googlegroups.com...
> sql server = server A
> sybase = server B (set up as a linked server in A)
> I have a stored procedure in A that contains the following statement:
> SELECT * FROM OPENQUERY(B, 'exec storedproc')
> which runs the procedure in B just fine. It truncates a table in B and
> then populates it with data from various queries. The issue is later in
> the stored procedure issued from A I perform an insert where I grab the
> data inserted into the table in B and insert that data into a table in
> A. It seems as though everytime a number of records are inserted into
> the table in A the statement above: SELECT * FROM OPENQUERY(B, 'exec
> storedproc') is run again and again. Does anybody know why this is
> occurring and how I can stop this from occurring?
> Thanks,
> E
>|||Well the reason I know that it keeps reissuing that statement is that
while the table in A is being populated after a number of records have
been inserted the table in B is truncated and repopulated again and
this cycle goes on until the table in A has been completely populated.
E
Uri Dimant wrote:
> You mean that later within 'storedproc' you are perfoming insert into a
> table that is resided on server 'A' and you have seen in SQL Server Profil
er
> that it runs SELECT * FROM OPENQUERY(B, 'exec> storedproc') many times. A
m
> I right?
>
> "w" <ebrooks@.sbcglobal.net> wrote in message
> news:1136953074.780909.282800@.g14g2000cwa.googlegroups.com...|||Well the reason I know that it keeps reissuing that statement is that
while the table in A is being populated after a number of records have
been inserted the table in B is truncated and repopulated again and
this cycle goes on until the table in A has been completely populated.
E
Uri Dimant wrote:
> You mean that later within 'storedproc' you are perfoming insert into a
> table that is resided on server 'A' and you have seen in SQL Server Profil
er
> that it runs SELECT * FROM OPENQUERY(B, 'exec> storedproc') many times. A
m
> I right?
>
> "w" <ebrooks@.sbcglobal.net> wrote in message
> news:1136953074.780909.282800@.g14g2000cwa.googlegroups.com...

OPENQUERY from ASP.NET Page Problem?

I'm performing a particular word Search in MS Word, Text, PDF docs and
displaying the results through Index Server linked to SQL Server when
it is matched. For which I'm using Openquery in the stored procedure
which works fine in Query Analyzer of the SQL Server but doesn't work (
shows none of the results) when i call it from the ASP.NET Page. I am
not able to figure out Where and What is the problem?
The Stored Proc which is i'm using is shown below
Any help will be greatly appreciated. Thanks for your time and help in
Advance
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO
which works with followin stat in Query Analyzer
Exec SelectIndexServerCVpaths
@.searchstring = 'The Search text'
but doesn't work when i connect it to a Datagrid in my ASP.NET Page
objcmd = new SqlCommand("SelectIndexServerCVpaths", objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@.searchstring",strsearchstrings);
objConn.Open();
objRdr = objcmd.ExecuteReader();
dgcvs.DataSource=objRdr;
dgcvs.DataBind();
objRdr.Close();
objConn.Close();Did you try it with impersonation on?
http://support.microsoft.com/kb/323293/en-us
Also why don't you just query indexing services directly through ixsso, or
msidxs?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"savvy" <johngera@.gmail.com> wrote in message
news:1137934280.281102.175800@.o13g2000cwo.googlegroups.com...
> I'm performing a particular word Search in MS Word, Text, PDF docs and
> displaying the results through Index Server linked to SQL Server when
> it is matched. For which I'm using Openquery in the stored procedure
> which works fine in Query Analyzer of the SQL Server but doesn't work (
> shows none of the results) when i call it from the ASP.NET Page. I am
> not able to figure out Where and What is the problem?
> The Stored Proc which is i'm using is shown below
> Any help will be greatly appreciated. Thanks for your time and help in
> Advance
>
> CREATE PROCEDURE SelectIndexServerCVpaths
> (
> @.searchstring varchar(100)
> )
> AS
> SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
> TABLE_NAME = 'FileSearchResults')
> DROP VIEW FileSearchResults
> EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
> OPENQUERY(FileSystem,''SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM
> SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
> FREETEXT('' + @.searchstring + '')'')')
> SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
> F.PATH AND C.DefaultID=1
> GO
> which works with followin stat in Query Analyzer
> Exec SelectIndexServerCVpaths
> @.searchstring = 'The Search text'
> but doesn't work when i connect it to a Datagrid in my ASP.NET Page
> objcmd = new SqlCommand("SelectIndexServerCVpaths", objConn);
> objcmd.CommandType = CommandType.StoredProcedure;
> objcmd.Parameters.Add("@.searchstring",strsearchstrings);
> objConn.Open();
> objRdr = objcmd.ExecuteReader();
> dgcvs.DataSource=objRdr;
> dgcvs.DataBind();
> objRdr.Close();
> objConn.Close();
>|||Thanks for your time and help
I'll try out the above|||Thanks for your time Hillary
I tried with impersonation with both true and false as well
it didn't make any difference
At present i'm getting some results which are static not changing with
the search word
I can't query just Indexing Services as you can see in my stored
procedure i'm linking my SQL Server Database table with the Index
Server Results
and displaying the results
Is there any problem in my Connection String which is shown below
SqlConnection objConn = new
SqlConnection(" Server=MISC\\MISC;Database=sapresources;
User
ID=sap;Password=sapres;");
that's it i'm not using any provider name, catalog name nothing of that
sort, Is that right ?sql

OPENQUERY from ASP.NET Page Problem?

I'm performing a particular word Search in MS Word, Text, PDF docs and displaying the results through Index Server linked to SQL Server when
it is matched. For which I'm using Openquery in the stored procedure
which works fine in Query Analyzer of the SQL Server but doesn't work ( displays none of the results) when i call it from the ASP.NET Page. I am
not able to figure out Where and What is the problem?
The Stored Proc which is i'm using is shown below
Any help will be greatly appreciated. Thanks for your time and help in
Advance

CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO

which works with followin stat in Query Analyzer
Exec SelectIndexServerCVpaths
@.searchstring = 'The Search text'

but doesn't work when i connect it to a Datagrid in my ASP.NET Page
objcmd = new SqlCommand("SelectIndexServerCVpaths", objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@.searchstring",strsearchstrings);
objConn.Open();
objRdr = objcmd.ExecuteReader();
dgcvs.DataSource=objRdr;
dgcvs.DataBind();
objRdr.Close();
objConn.Close();

Has No-one got any idea the above Question ? Is the above problem that complicated ?

|||

savvy wrote:

Has No-one got any idea the above Question ? Is the above problem that complicated ?

You question is not complicated but it is not valid implementation because SQL Server can perform what you want back in SQL Server 7.0 in 1999. Now if you can interested in valid solution post again and I can give you some links.

The reason Information Schema Views and Openquery are ANSI SQL for inter RDBMS( relational database management system) communication not for SQL Server and IIS index server. Hope this helps.

|||

Thanks for your help. So, Is my analogy wrong ? I have implemented this because i need to link SQL server and Index Server so that i can grab the data in the SQL Server and i had no idea of other ways of achieving this .

If you got any information to get around this problem that will be really great as I've been trying to solve this problem since a week.

Thanks in Advance

|||

Try these links the first deals with using both Image and text columns to get what you want and the second is SQL Server Full Text Blog, he was with the Microsoft SQL Server Full Text team. If you cannot find your solution in his blog he will answer your post at SQL Server Central forums. Hope this helps.

http://forums.asp.net/949146/ShowPost.aspx

http://spaces.msn.com/members/jtkane/?partqs=cat%3DSQL+Server+2000+Full-Text+Search&_c11_blogpart_blogpart=blogview&_c=blogpart

|||

Thanks for your help

Can you tel me is there any way to read the Word or PDF documents and store the text it in a database field (ntext) . Is this is possible?

Thanks in advance

|||

Savvy,

I think you have skipped design and is coding so you are complicating simple problems. The create table statement below comes from Microsoft new sample database AdventureWorks, you can store the files as Word or PDF on image columns but also use text to store the same files so you can use the Microsoft Full Text Index and do the key word search you want. What design do for you is look for alternative implementations which takes the complications out of the problem. Run a search for the AdventureWorks database on Microsoft site install it run tests and take the tables you need for your application. Hope this helps.

CREATE TABLE [ProductPhoto] (
[ProductPhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_ProductPhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_ProductPhoto_ProductPhotoID] PRIMARY KEY CLUSTERED
(
[ProductPhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Thanks Caddre for your help. It was a really long journey after i got your reply, when i got shifted totally from linked servers to FULL TEXT INDEXING in MICROSOFT SEARCH SERVICE. I had many problems to solve such as my FULL TEXT INDEXING was grayed out so i need to install DEVELOPER Edition on my System coz my O/S is Win XP Prof. I read many of your Posts regarding this topic. I personally thank u alot for your help which u have rendered in this field.

Thank you very much

|||What error are you getting?|||

Savvy,

Thanks for the complement and I am glad I could help.

Openquery Error

Hi I am trying to connect to teradata using SQL Query Analyzer
Teradata is linked through our Linked Server
Below is the query I run
select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
mktg is schema is teradata database
The error it gives me is:
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 error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
returned 0x80040e14].
Did you try the exact command on your terradata ? Are you sure that you
are connected to the right entity (database etc, don=B4t know the
details of that one) ?
HTH; Jens Suessmeyer.
|||Jens wrote:
> Did you try the exact command on your terradata ? Are you sure that you
> are connected to the right entity (database etc, don=B4t know the
> details of that one) ?
> HTH; Jens Suessmeyer.
I can access the same teradata database using Queryman and also using
SAS no problems. I have access to three databases in Teradata and out
of three i can acess one easily using Query Analyzer but other two it
gives me error as described above.
Since I can connect to teradata through Queryman and SAS that means my
ODBC drivers and access to these database both are fine. But I am
failing to understand why Openquery is failing.
|||pradeep_raina@.hotmail.com (pradeep_raina@.hotmail.com) writes:
> Hi I am trying to connect to teradata using SQL Query Analyzer
> Teradata is linked through our Linked Server
> Below is the query I run
> select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
> mktg is schema is teradata database
> The error it gives me is:
> 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 error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
> returned 0x80040e14].
The errors from queries to linked servers are often very difficult to
understand. Error 0x80040e14 is DB_E_ERRORSINCOMMAND, and the explanation
I find in the description for ICommandPrepare::Prepare is "The command text
contained one or more errors. Providers should use OLE DB error objects to
return details about the errors."
My interpretation is that the command fails for some reason.
Now, I don't know Teradata at all, but it looks a little funny
when you say that mktg is your schema, and then you say that you
have access to three databases on Teradata. Shouldn't you specify
the database as well? My guess is that your command fails, because
Teradata cannot find mktg.vtcsr in the database where it is looking.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

OPENQUERY and unicode from MySQL

Hi, all. Using SQL 2005. When I execute this query against a MySQL database set up as a linked server, cyrillic text stored in unicode in MySQL shows up as question marks (?).

SELECT *
FROM
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM documents
)

Clearly, some kind of unicode issue. I've spent a few hours reading in BOL and searching online. Must be something simple, but I'm not finding a solution. If anyone has suggestions, I'd be very grateful.

Stephen

Just to follow up, my testing this morning suggests that this is an issue with the MySQL OBDC driver, used to create the connection to the linked server in SQL Server. So if anyone has solved this issue before, of course I'd be happy to hear the answer. Has anyone used a different driver to connect to MySQL as a linked server? I don't think I can use the Cherry City Software OLE DB provider, because it doesn't seem to support large text columns (http://cherrycitysoftware.com/CCS/Providers/ProvMySQL.aspx). I need a way to connect to MySQL as a linked server, using unicode (multilingual text in different rows) and text storage in some row columns at 50,000 bytes or more.

Thanks for any suggestions.

Stephen