How to get table info from remote server.

Question

How to get table info from remote server.

Roust_m

SSCoach

Points: 17410
More actions
November 5, 2003 at 7:06 am

#358505
Hi!
I have information about table location in theses variables:
@server_name, @db_name & @table_name,
that I fetch from this cursor.
I need to execute sp_helprotect and OBJECTPROPERTY (object_id(@table_name), 'TableHasUpdateTrigger') against a number of tables from a cursor.
For sp_helprotect I tried this:
```
SET @full_table_name = @server_name + '.' + @db_name  + '..' + @table_name 

PRINT @full_table_name 

EXEC sp_helprotect @full_table_name 
```
It says:
Server: Msg 15302, Level 11, State 1, Procedure sp_helprotect, Line 87
Database_Name should not be used to qualify owner.object for the parameter into this procedure.
For OBJECTPROPERTY:
```
IF OBJECTPROPERTY ( object_id(@full_table_name),'TableHasUpdateTrigger') = 1

   print 'Table has Update Trigger'

ELSE 

   print 'Table has NO Update Trigger' 
```
It says:
'Table has NO Update Trigger'
all the time! (Even when table has such trigger)
How can I program this?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

These stored procedure and function return information about objects in the current database(local) only.

quote:
For sp_helprotect I tried this:
SET @full_table_name = @server_name + '.' + @db_name + '..' + @table_name PRINT @full_table_name EXEC sp_helprotect @full_table_name
It says:
Server: Msg 15302, Level 11, State 1, Procedure sp_helprotect, Line 87
Database_Name should not be used to qualify owner.object for the parameter into this procedure.

exec [remoteservername].northwind.dbo.sp_helprotect parts1

quote:
For OBJECTPROPERTY:
IF OBJECTPROPERTY ( object_id(@full_table_name),'TableHasUpdateTrigger') = 1 print 'Table has Update Trigger'ELSE print 'Table has NO Update Trigger'
It says:
'Table has NO Update Trigger'
all the time! (Even when table has such trigger)

You have to create stored procedure in your remote server to retrive information from OBJECTPROPERTY and run the sp from local server.

create procedure OBJECTPROPERTY_EX @tablename varchar(25)

as

declare @cmd varchar(255)

select @cmd = "select OBJECTPROPERTY ( object_id('" + @tablename + "'),'TableHasUpdateTrigger')"

exec (@cmd)

exec [remoteservername].northwind.dbo.OBJECTPROPERTY_EX @tablename = 'parts1'

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

quote:
Hi!
I have information about table location in theses variables:
@server_name, @db_name & @table_name,
that I fetch from this cursor.
I need to execute sp_helprotect and OBJECTPROPERTY (object_id(@table_name), 'TableHasUpdateTrigger') against a number of tables from a cursor.
For sp_helprotect I tried this:
SET @full_table_name = @server_name + '.' + @db_name + '..' + @table_name
PRINT @full_table_name
EXEC sp_helprotect @full_table_name



EXEC(@Server_Name + '.' + @DB_Name + '..sp_helprotect ' + @Table_Name)

quote:
For OBJECTPROPERTY:
IF OBJECTPROPERTY ( object_id(@full_table_name),'TableHasUpdateTrigger') = 1
print 'Table has Update Trigger'
ELSE
print 'Table has NO Update Trigger'
It says:
'Table has NO Update Trigger'
all the time! (Even when table has such trigger)
How can I program this?



DECLARE @sql nvarchar(1000), @i int

SET @sql = 'EXEC ' + @Server_Name + '.' + @DB_Name + '..sp_executesql N''SELECT @i = OBJECTPROPERTY(OBJECT_ID(''''' 

+ @Table_Name + '''''),''''tablehasupdatetrigger'''')'',N''@i int OUTPUT'',@i = @i OUTPUT'

EXEC sp_executesql @sql, N'@i int OUTPUT', @i = @i OUTPUT

IF @i = 1 PRINT 'Table has Update Trigger'

ELSE print 'Table has NO Update Trigger'

--Jonathan

Edited by - Jonathan on 11/05/2003 08:08:07 AM

--Jonathan