Server ErrorLogs

[olutimi]

Please help.

I wrote a script to read SQL Server Error Logs and dump it into a table.
The script error where it attempts to connect to other servers to read the server error messages.

INSERT INTO S1.db1.dbo.t_errorlog(Id, Servername,ErrorMessage,ContinuationRow)
EXEC S2.Master.dbo.xp_readerrorlog

The script and table exist on S1 and would be run from there as a schedule job.
Should i use linked server or opendatasource for the individual server connections.

Help appreciated.

[MAK]

Just for this purpose if you want to connect to other box then use openrowset or else linked servers

[skhanal]

I don't think you can call a extended stored procedure using openrowset, you will have to use linked server.

[MAK]

exec opendatasource('SQLOLEDB','Data Source=remoteserver;User ID=sa;Password=yearight').master.dbo.xp_readerrorl og

[skhanal]

That's great.

[olutimi]

Thanks.

I found your tip very useful.
Could you advice please, on the above script, I would like to add the remote server name in the servername column. At the moment, it reports the host (S1) server name rather than the remote
(S2) server

Help Appreciated.

[MAK]

exec opendatasource('SQLOLEDB','Data Source=S2;User ID=sa;Password=yearight').master.dbo.xp_readerrorl og

[olutimi]

Thanks Mak.

The Servername of the Insert Script reports the host server(S1) rather than the remote server(S2).
Please advice.

Help appreciated.

[MAK]

Lets step back a bit.

what you are trying to do. which server you logged on to (S1 or S2). where you are inserting (S1 or S2). what is the statement you are using to insert.

[olutimi]

Thanks Mak.

INSERT INTO S1.db1.dbo.t_errorlog(Id, Servername,ErrorMessage,ContinuationRow)
EXEC opendatasource('SQLOLEDB','Data Source=S2;User ID=sa;Password=yearight').Master.dbo.xp_readerrorl og

The above is my insert statement.

Am logged into S2 from S1 and am inserting to table t_errorlog on S1.
I figured if i use the @@servername function as default in the column (servername) definition it will always insert S1 servername. Suppose i use the function @@REMSERVER to obtain and insert the remote server(S2).

Thanks for your help.

[VaLaNDiL]

Hi!
I have a similar problem...

I have this query:

NSERT INTO local_table
EXEC opendatasource('SQLOLEDB','Data Source=S2;User ID=xx;Password=xxxx').remote_db.dbo.procedure 'variable'

That query return an error and I can't insert the result into my local table.

Sorry, I don't write in english very well...


If I execute this:

EXEC opendatasource('SQLOLEDB','Data Source=S2;User ID=xx;Password=xxxx').remote_db.dbo.procedure 'variable'

It's allright... but I need to insert the answer of this procedure on my local table....