Creation of linked server from SQL Server Management Studio
1. Open SQL server Management Studio
2. Connect to the database where you would like to create the linked server.
3. Expand the note Sever Objects

4. Choose the option New linked server.
5. Update the below details

a. Update the linked server name (This will be the name of the Linked server)
b. Set the provider name as specified in the screen shot

c. Enter the product name
d. Specify the Data source as
Driver={SQL Server};Database=databasename;Server=servername;UID=userid;PWD=password;

Notes : if you are facing any issues with accessing the database using the credentials provided , click on the Security tab and choose the security content and set the credentials.

sql 2005 Linked server error

Why am i getting the error in Linked server :
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "Xyz".

Mostly thie error appear when the link table referance is not made correctly. The select statement should refer the linked server table as
linkserver name.database.dbo.table.

Debug procedures in SQL 2005

Steps to Debug a stored procedure in sql 2005

1. Open the Microsoft Visual Studio 2005
2. Select Server Explorer option from Menu as follows:



3. From the Data Connections node, 'Add connection'.



4. Expand the data connection navigate to the Stored Procedures node.

5. Select the intended SP to be debugged.

6. Right click and to view the Stored procedure.

7. Place a break point on the intended line of code where debugging needs to be started.




10. Right click on the stored procedure select 'Step-Into Stored Procedure' as shown below.



11. This action brings parameter screen.



12. Enter the needful values and click Ok.

Return the name and deaprtment in single column using coalesce and function

-- Create some temp tables
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPT]') AND type in (N'U'))
DROP TABLE [dbo].[DEPT]
GO
CREATE TABLE DEPT (ID INT , DEPT VARCHAR(100))
INSERT INTO DEPT VALUES (10,'ACCOUNTING')
INSERT INTO DEPT VALUES (20,'MARKETING')
INSERT INTO DEPT VALUES (30,'FINANCE')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SNAME]') AND type in (N'U'))
DROP TABLE [dbo].[SNAME]
GO
CREATE TABLE SNAME (ID INT , DEPT INT , SNAME VARCHAR(100))
INSERT INTO SNAME VALUES (1,10,'HARISH')
INSERT INTO SNAME VALUES (2,20,'HARISH')
INSERT INTO SNAME VALUES (2,20,'CIJU')
INSERT INTO SNAME VALUES (2,30,'CIJU')
INSERT INTO SNAME VALUES (5,10,'CIJU')
GO
-- Drop and create the Functions for Comma seperated value return.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COMA_CP1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[COMA_CP1]
GO
CREATE FUNCTION COMA_CP1(@SNAME1 VARCHAR(100)) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @RES VARCHAR(100)
SELECT @RES = COALESCE(@RES + ', ', '') + DEPT1.DEPT
FROM SNAME
INNER JOIN DEPT DEPT1
ON SNAME.DEPT = DEPT1.ID
WHERE SNAME = @SNAME1
RETURN @SNAME1 +','+ @RES
END
GO
-- Final select to Return
SELECT DBO.COMA_CP1(SNAME)
FROM SNAME
INNER JOIN DEPT DEPT1
ON SNAME.DEPT = DEPT1.ID
GROUP BY SNAME

LPAD function in sql server

In PL/SQL we have the statement LPAD(string, length, pad), RPAD(string, length, pad)Returns string padded on left or right to length characters using the pad string as padding. Here is an example that provied the similar functioanlity in sql server

declare @in_val int
set @in_val = 1
select 'R'+right(replicate('0',3)+CAST(@in_val AS VARCHAR),2)
If you pass 1 as input val this will return 'R01' , for value 10 this will return 'R10'

Distinct xtype values in sysobjects

xtype values in sysobjects
This is a list of all of the possible values for the xtype column in the sysobjects table of a SQL Server database:
C - CHECK constraint
D - Default or DEFAULT constraint
F - FOREIGN KEY constraint
L - Log
P - Stored procedure
PK - PRIMARY KEY constraint
RF - Replication filter stored procedure
S - System table
TR - Trigger
U - User table
UQ - UNIQUE constraint
V - View
X - Extended stored procedure

To get the current user in TSQL - SQL 2005

To get current user run following script in Query Editor

SELECT SYSTEM_USER

SYSTEM_USER will return current user. From Book On-Line – SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER returns the name of the impersonated context.