Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim Wile
Jim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:
> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>