Skip to main content

Adding a Linked Server in SQL Server 2008

You can do the following in order to add a linked server to another SQL Server Instance

Step 1: Run the Add Linked Server Stored Procedure
EXEC sp_addlinkedserver
@server=N'Server1_Instance1', --Give a name to the linked server
@srvproduct=N'',
@provider=N'SQLNCLI', --Provider name for SQL Server
@datasrc=N'Server1\Instance1';

Step 2: Add credentials to access the linked server
If the linked server can be accessed via Windows Authentication this step is not required. But if you need to access is via SQL Server authentication you need to run the following procedure by replacing the text in bold appropriately.

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname= N'Server1_Instance1',@useself=N'False',@locallogin=NULL,@rmtuser=N'user_id',@rmtpassword='password'
GO

Step 3: Access the linked server
You can access the linked server in the following format
linked_server.database_name.schema_name.table_name
Example: Select * from Server1_Instance1.Database1.dbo.Table1
Labels:

Comments

Post a Comment

Popular posts from this blog

Clear Validation Errors and Validation Summary messages

ASP.net built in validation does not provide us a straight forward to clear all the validation errors. This would be really helpful while resetting a form. The reset html button would simply reset the form values but will not clear the validation errors. The following javascript code snippet can be used to clear the validation error messages. Have a reset button on your form and call the following js function onclick. <input type="reset" onclick="HideValidationErrors();" /> function HideValidationErrors() { //Hide all validation errors if (window.Page_Validators) for (var vI = 0; vI < Page_Validators.length; vI++) { var vValidator = Page_Validators[vI]; vValidator.isvalid = true; ValidatorUpdateDisplay(vValidator); } //Hide all validaiton summaries if (typeof (Page_ValidationSummaries) != "undefined") { //hide the validation summaries ...
2 comments
Read more

Find the cause of poor performance in Sql Server

I found the following two part article by Gail Shaw on Simple-Talk really helpful in trouble shooting poorly performing queries in Sql Server. The articles talks about spotting poorly performing queries with the help of the Profiler, understand Sql Server Query plans and fine tune the peformance using proper indexes. Part 1: http://tinyurl.com/ccl6gj Part 2: http://tinyurl.com/okcuqg
18 comments
Read more

SharePoint: Comparing dates in XSLT

In one of my sharePoint projects, I had to compare a date with the current date in XSLT. As it turned out, there is no support for date comparision in XSLT. This is how I went about doing it. The trick involves converting the date string into a number Say the date is in ISO format YYYY-MM-DDTHH:MM:SSZ (for example, 2001-01-21T22:54:47Z). Say we have a variable DueDate <xsl:variable name="DueDate" select="'2001-01-21T22:54:47Z'"> Replace the dashes in the string with empty string Take the first 10 character in the date sring convert the string to number number(translate(substring(@DueDate,1,10),'-','')) On doing the above we get: 20010121 We can do the same thing with the date to compare. We can compare the duedate with the current date in SharePoint as follows number(translate(substring(@DueDate,1,10),'-','')) & lt; number(translate(substring(ddwrt:TodayIso(),1,10),'-','')) You can apply the same log...
2 comments
Read more