servers and DROP TABLE statement

I have a procedure that loops through the sysdatabases and information
scehma views to "clean up" drop tables that are no longer used. To
consolidate things the procedure preforms this task remotely using linked
servers. The only problem is the drop table statement is "drop table
[server].[databasename].[owner].[tablename]" which errors with "Server: Msg
117, Level 15, State 1, Line 1 The object name 'server.database.dbo.'
contains more than the maximum number of prefixes. The maximum is 2."
Additionally the system function DatabaseProperty() that I am using to
evaluate if the database is offline or readonly before trying to drop the
table retrurns null because it can not evaluate the status of a linked
server database properly. Is this a limitation of Linked Servers or is there
a linked server property that can be set to support 4 part naming
convention.
Any help or suggestions are appreciated.
Thanks.You can't do DDL over 4-part naming. Consider building the DROP in a variable and use sp_executesql
(on the linked server) to execute the statement:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick Butler" <rick.butler@.technekes.com> wrote in message
news:OCQ%23$jPmFHA.1044@.tk2msftngp13.phx.gbl...
>I have a procedure that loops through the sysdatabases and information scehma views to "clean up"
>drop tables that are no longer used. To consolidate things the procedure preforms this task
>remotely using linked servers. The only problem is the drop table statement is "drop table
>[server].[databasename].[owner].[tablename]" which errors with "Server: Msg 117, Level 15, State 1,
>Line 1 The object name 'server.database.dbo.' contains more than the maximum number of prefixes.
>The maximum is 2." Additionally the system function DatabaseProperty() that I am using to evaluate
>if the database is offline or readonly before trying to drop the table retrurns null because it can
>not evaluate the status of a linked server database properly. Is this a limitation of Linked
>Servers or is there a linked server property that can be set to support 4 part naming convention.
> Any help or suggestions are appreciated.
> Thanks.
>