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
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Friday, March 30, 2012
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
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 UPDATE Syntax help needed
Hi All
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 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 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...
>
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...
>
Monday, March 26, 2012
opening my clients DB locally
My client has given me a DB file (.MDF) and I need to open it in order to export it to the remote DB.
In Enterprise Manager I go to 'New Database' - create a new DB and click on the 'Data' tab to browse to my clients file. Enterprise Manager recognises the file and everything seems hunky dory. but the resulting DB seems to have no tables.
The DB file is 1600K and has some stored procedures but if I try to export the tables there is nothing. His ASP files refer to tables in the SQL as you would expect.
Am I missing something? it's the weekend so he is not available and I don't want to look tooo stupid!look sp_attach_single_file_db up in sql server books online.|||Am I missing something? it's the weekend so he is not available and I don't want to look tooo stupid!It is always good to not look too stupid to a client... That kinda throws them off their feed for a bit. ;)
As Thrasymachus pointed out, you could use sp_attach_single_file_db (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_4wrm.asp), but carefully read the restrictions caused by not having the log file... Under certain circumstances, that can be a major problem.
-PatP
In Enterprise Manager I go to 'New Database' - create a new DB and click on the 'Data' tab to browse to my clients file. Enterprise Manager recognises the file and everything seems hunky dory. but the resulting DB seems to have no tables.
The DB file is 1600K and has some stored procedures but if I try to export the tables there is nothing. His ASP files refer to tables in the SQL as you would expect.
Am I missing something? it's the weekend so he is not available and I don't want to look tooo stupid!look sp_attach_single_file_db up in sql server books online.|||Am I missing something? it's the weekend so he is not available and I don't want to look tooo stupid!It is always good to not look too stupid to a client... That kinda throws them off their feed for a bit. ;)
As Thrasymachus pointed out, you could use sp_attach_single_file_db (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_4wrm.asp), but carefully read the restrictions caused by not having the log file... Under certain circumstances, that can be a major problem.
-PatP
Subscribe to:
Posts (Atom)