Trigger Insert Update Delete

Create TRIGGER trgTransactions on dbo.test1 AFTER INSERT, DELETE, UPDATE
AS
BEGIN
DECLARE @DeletedId INT, @InsertId int


SELECT @InsertId = Test from inserted
SELECT @DeletedId= Test from Deleted




IF (@InsertId > 0 AND isnull(@DeletedId,'') = '')

BEGIN
--DELETE FROM TomsWebDBNProdV2_clone_test.dbo.test1 where Test = @InsertId

INSERT INTO TomsWebDBNProdV2_clone_test.dbo.test1
SELECT * FROM INSERTED

END

-- insert
IF (@InsertId > 0 AND @DeletedId > 0)
BEGIN
DELETE FROM TomsWebDBNProdV2_clone_test.dbo.test1 where Test = @InsertId

INSERT INTO TomsWebDBNProdV2_clone_test.dbo.test1
SELECT * FROM INSERTED

END

-- update
IF( isnull(@InsertId,'') = '' AND @DeletedId > 0)

-- delete
DELETE FROM TomsWebDBNProdV2_clone_test.dbo.test1 where Test = @DeletedId


END

Call C# Function in javascript

var vCaller = '<%=txtSAP_TextChanged() %>';

Get Maximum Numeric Value From Alpha Numeric Row

select max(Convert(int,AlphaNumeric_Field))+1 from Table_Name where ISNUMERIC(AlphaNumeric_Field) = 1

Search Text in All Columns in Sqlserver Database

CREATE PROC dbo.SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2010 Mohanraj Muthusamy. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Mohanraj Muthusamy
-- Tested on: SQL Server 7.0 and SQL Server 2000 and SQL Server 2005
-- Date modified: 28th July 2010 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END

Like operator for date in sqlserver

create PROCEDURE sp_Delete_RehabilitationDtls
(
@EmpId VARCHAR(50), @RehabDt datetime
)
AS
declare @Dtdate varchar(50)
select @Dtdate=replace((convert(varchar, @RehabDt,107) +'%'),',','')
delete
FROM Pro_Emp_Rehabilitation_Dts where Emp_Id=@EmpId and RehabilitationDate like @Dtdate

Multiple rows in a single row with separator in SqlServer

Here is a simple way to make a single row value from multiple rows with a separator in SqlServer. I have a table like the following…
Table Name: People
Column name :ID,Name


Now I need the ‘Name’ column values where matching the value of ‘aaaa’ in to a single row. For this I have tried to make a query. Just use the following query to get the solution. declare @res varchar(1000) select @res = coalesce(@res + ',', '') +Name from people WHERE Name = 'aaaa' select @res

How to join tables from different servers?

EXEC sp_addlinkedserver SERVER_01
GO

/* The following command links 'sa' login on SERVER_02 with the 'sa' login of SERVER_01*/

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER_01', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa password of SERVER_01'

GO

SELECT a.title_idFROM SERVER_01.pubs.dbo.titles aINNER JOIN SERVER_02.pubs.dbo.titles bON a.title_id = b.title_idGO