Linked server problem.

instcat is used to update the catalog SPs which get the database schemas for various things so would have something to do with the area you have problems.

Are both servers sp2?

What do you mean when you say 'write any select query from one server to another' - a query over a linked server?
I would suspect that you have ended up with incompatible servers in that case or one has some corruption - maybe a failed service pack. The best solution is probably to make sure all servers are at the same level.Yes. Both servers are having SQL 7 SP2 and MDAC 2.5 SP1 version (This config is forced to keep since our major clients are having same version). both service packs didn't resulted any errors during installation.

I am executing a select statement from server 1 to server 2

for ex. following query running from server 2. the database restored a copy from server 2.

select col1, col2 from server1.database.dbo.tablename

if I execute from server 1 the following query is working fine.

select col1, col2 from server2.database.dbo.tablename

Please let me know if you have any answer. Thanks for your kind help.

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.

Linked Server - Error

Hi all-

I created a another SQL Server as linked and I use them in some of my UDFs and SPs. Earlier these functions worked. But this morning, when I try to alter the definitions of these objects or query them, I get the following error message,
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

Iadded SET ANSI_WARNINGS ON , but I still get this error. Any ideas?

- CBhttp://www.dbforums.com/t492719.html

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch .asp%3FArticle%3D296769|||Thanks Snail. It worked.

- CB
Originally posted by snail
http://www.dbforums.com/t492719.html

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch .asp%3FArticle%3D296769