Problem running alter table statement on a linked server

[DLN]

Hello all,

I'm using SQL Server 2005 and I'm trying to run the following command
against a linked server (also SQL Server 2005):

alter table "<link-srv-name>.<db-name>.dbo.<table>" enable trigger all

The error being returned is:

Msg 4902, Level 16, State1, Line 1
Cannot find the object "<link-srv-name>.<db-name>.dbo.<table>" because it
does not exist or you do not have permissions.

If I run normal Select, Update, Delete, etc... statements against the linked
server and database, the operation completes without error. I'm looking
through the books online and noticed that the syntax listed in the online
help for the "ALTER TABLE" statement allows you to target, at most,
database.schema.table - there's no mention of being able to target a linked
server. If I take a look at the online help documentation for the "UPDATE"
statement, online help explicitly specifies that you _can_ target a linked
server. Does this mean that alter table can't be used with a linked server?

Thanks.

[Tibor Karaszi]

> Does this mean that alter table can't be used with a linked server?

Correct, only DML, not DDL against linked servers. If the linked server is an SQL Server, you might
be able to execute sp_execute sql as a remote stored procedure, though.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"DLN" <dnadon...@hotmail.com> wrote in message news:Ou2GR3Rf...@TK2MSFTNGP04.phx.gbl...

[DLN]

Calling a stored procedure on the remote server to do the alter table
command works. Thanks for the help.

"Tibor Karaszi" <tibor_please.n...@hotmail.nomail.com> wrote in
message news:OptoYPSf...@TK2MSFTNGP02.phx.gbl...

[annamalai samy K]

http://www.tek-tips.com/viewthread.cfm?qid=1259668&page=1

This feature appears to be in SQL-Server 2005 Transact-SQL.
It is the pass-through feature. Here is how it would work:

Execute a pass-through command against a linked server
{ EXEC | EXECUTE }
( { @string_variable | [ N ] 'command_string [ ? ] ' } [ + ...n ]
[ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]
)
[ AS { LOGIN | USER } = ' name ' ]
[ AT linked_server_name ]
[;]

Source: MSDN.MICROSOFT.COM

>> On Thursday, April 12, 2007 12:59 PM Tibor Karaszi wrote:

>> Correct, only DML, not DDL against linked servers. If the linked server is an SQL Server, you might
>> be able to execute sp_execute sql as a remote stored procedure, though.
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "DLN" <dnadon...@hotmail.com> wrote in message news:Ou2GR3Rf...@TK2MSFTNGP04.phx.gbl...

>>> On Thursday, April 12, 2007 1:12 PM DLN wrote:

>>> Calling a stored procedure on the remote server to do the alter table
>>> command works. Thanks for the help.

>>> Submitted via EggHeadCafe
>>> .NET Windows Services - Timer, Debugging, and Installation
>>> http://www.eggheadcafe.com/tutorials/aspnet/0ddb1434-d3c9-424c-8b12-bdf75c50588c/net-windows-services--timer-debugging-and-installation.aspx