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


Show quoteHide quote

How to 'call' UDFs from SPs
How to backup and restore related databases?
Exporting data using T-SQL... something opposite of BULK INSERT.
Scale Up or Scale Out with SQL Server 2005
Is it possible to restore SQL Server from Registry and System file
could not find stored procdure xp_availablemedia
copy data from one database to another
query on data and log file
ASKING FOR JDBC AND SQL92 COMPATIBILITY
Backing up a database?