query linked server

[sshafe]

I am trying to query a number of linked servers using a recordset and receive the error below:

QUERY:
Set rsDBName = ConnectionName.Execute("select name from " & rsServers.Fields("sqlservername&quot & _ ".master.dbo.sysdatabases&quot

ERROR:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON"

Is there a workaround for this?

Thanks in advance

 

[spazman]

You may find is easier and faster to create a view on your server quering the remote server tables.

 

[sshafe]

I'm not sure what would be involved to do it without creating views, but I do know that that would involve creating about 150 views and I would like to make this an automated process for the future. (Not adding a view everytime we add a new database or server).

So...how would I go about setting it up the other way?

Thanks

 

[spazman]

What you could do it creata a connection from your web server to each SQL server, then use each connection to each server as you need it. For Example
SQLConnect1 = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=USERNAME;Password=PASSWORD;Initial Catalog=DB_NAME;Data Source=SQL_SERVER_NAME"

SQLConnect2 = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=USERNAME_2;Password=PASSWORD_2;Initial Catalog=DB_NAME;Data Source=SQL_SERVER_NAME_2"

Then query the DB

the syntax to query a connected server, if you have configured it correctly using SQL enterprise manager is
SELECT * FROM SERVERNAME.DBNAME.OWNER.TABLE_OR_VIEW AS ALIAS