Linked Server in Management Studio

I have created Linked Servers for Access databases. SPs are able to select data from the Linked Servers.

In Management Studio, the General Properties are greyed out and therefore can't be modified. I need to modify the Data Source to connect to different Access databases. Can't find a way to do this with a SP either. Searches seem to indicate I should be able to modify the properties using Management Studio.

Management Studio does not display the list of tables, etc. in the Access database as Enterprise Manager did for a Linked Server. The data source is on a mapped drive. The definition:

/****** Object: LinkedServer [ACCESS GENERAL] Script Date: 02/05/2007 11:56:43 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'ACCESS GENERAL',

@.srvproduct=N'OLE DB Provider for Jet',

@.provider=N'Microsoft.Jet.OLEDB.4.0',

@.datasrc=N'\\tacir2k3\Infrastructure\Databases\July 2005 Databases\General\GenDb2006_TR_Db.mdb'

Security is "Be made without a security context".

What is causing this behaviour?

Also, I cannot compile a stored procedure in VS2005 that references the Linked Server. But I can compile it using Management Studio.

Have you tried if the connection is at all made to the linked server. You can do that using the stored procedure sp_testlinkedserver @.servername.
Also, try connecting with the login's current security context unless your datasource is available for all.

Thanks,
Kuntal

|||

After creation of a Linked Server, the General Properties page will be greyed out (hence can't be modified), this is by design. You can delete the existing Linked Server and create a new one for the other Data Source.

Thanks
Arpita

|||

True in this case for the new connection you have to recreate the Linked server connection again.

If you want to import the data from that access database with some tables then you can take help of DTS too.

|||

Is it also by design that it no longer displays a list of the tables in the linked database?

Does anyone know why I can't compile a SP in VS2005 that references a linked database? It will compile in Management Studio and it will execute in Management Studio.