Configuring a Linked Server on SQL 2005 Server

Configuring a Linked Server on SQL 2005 Server

A] Configuring a Linked Microsoft Access Server on SQL 2005 Server - Using the Management Studio to set up a Linked Server

If you highlight the Linked Servers node and right click on the mouse, you can add a linked server by choosing the New Linked Server... ellipsis link as shown.

This pops up the dialog New Linked Server with three tabs as shown, with the General one in the default view. You must give a name to the linked server, in this case, NWIND11 (this is a copy of Northwind  from the Office 11 samples folder). From the drop-down choose the Provider as shown.

If you do not provide a product name you may generate an error as shown.

However you may give any irrelevant product name and no error is raised. This next picture shows such an example for a linked server, NWIND12.

NWID12 is configured with the default security, which is shown in the next picture. For NWIND11 the Security context was that of the external login and no mappings were made to local logins.

The Server options for the NWIND12 linked server are revealed by the Server Options tab as shown in this picture. Running a query similar to the 'AccessDb' returns the same results as in the case of 'AccessDb' (both are Northwind databases).

Note: with same procedure to set up database also

B]

First verify the "Distributed Transaction Coordinator" Service is

Running on both database server computer and client computers

1.      Go to "Administrative Tools > Services"

2.      Turn on the "Distributed Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as

the database server, on the computer running database server

1.      Go to "Administrative Tools > Component Services"

2.      On the left navigation tree, go to "Component Services > Computers > My    Computer"

3.      Right click on "My Computer", select "Properties"

4.      Select "MSDTC" tab

5.      Click "Security Configuration"

6.      Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound", "Enable TIP"

7.      The service will restart

8.      You might have to restart the computer(s)

C]

Now execute the following lines on the Source server database.

sp_configure 'remote query timeout', 0

go

reconfigure with override

go

List of DBCC in SQL SERVER 2005

List of DBCC in SQL SERVER 2005
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases.
Example, maintenance, validation task and status checks.
DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands