May 1, 2009 at 4:20 am
Hi,
i hope someone can help me with this problem:
1: i've created a linked server (test) on instance A
2: created a procedure to collect databasenames and instancenames on instance A
create proc databaseview as
IF EXISTS(SELECT name FROM sys.tables
WHERE name = 'databases')
BEGIN
DROP TABLE databases
END
create table databases (dbname varchar(20), instancename varchar(20))
insert into databases (dbname,instancename)
select name as dbname ,substring(filename,7,charindex('\', filename, 7)-7) as instancename from sys.sysdatabases
3: the same procedure was implemented on instance B (this is also the linked server created on instance A)
4: what i am trying to do is to execute the procedure via the linked server on instance B and insert the data collected in the table of instance A.
maybe this is the wrong way of approaching.
actually what i want to achieve is to enumurate all the databasenames and instancenames from all the instances we have from within one instance and put
all the information in one table.
i hope my question is not to cryptical an someone can help me on this.
kind regards,
bryan
May 1, 2009 at 4:42 am
Hi,
Have you enabled remote procedure calls (RPC) for your Linked Server?
May 1, 2009 at 5:14 am
Hi John,
yes i have.
bryan
May 1, 2009 at 5:25 am
What is the error message that you are getting?
When you set up the linked server, there's the Security tab where you specify how the login will be made on the remote server - the security context. Is the security context you specified valid for the remote server? Can you log in using those credentials to the remote server directly (not as a linked server) ?
May 1, 2009 at 8:03 am
Hi,
wasn't clear i think about the thing i wanted to do.
got a step closer, but not there yet.
- created a linked server for the instance(s)
- created a procedure called databaseview which gathers and inserts all databases with their instancename in a table called databases
- through the linked server i am able to call the remote procedure of every instance (EXEC [instancename] .master.dbo.databaseview)
- now trying to join the info from the remote call into one table ( insert databases EXEC [instancename] .master.dbo.databaseoverzicht) but the following error occurs:
(Msg 8501, Level 16, State 3, Line 1
MSDTC on server 'test_acc\test_ACC' is unavailable.)
- MSDTC is turned on.
does someone have another idea/solution for dealing with this issues
thanks in advance
bryan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply