Intellistant
Time can make us forget some memories but there are some memories make the life sweeter.
Tuesday, August 25, 2009
Saturday, August 22, 2009
Enable SQL Broker
The proper syntax for the alter database statement is:
ALTER DATABASE OperationsManager SET ENABLE_BROKER WITH ROLLBACK AFTER 5
The options after the "WITH" also include NO_WAIT & ROLLBACK IMMEDIATE
Providing for the New Data Base
ALTER DATABASE DatbaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE OperationsManager SET ENABLE_BROKER WITH ROLLBACK AFTER 5
The options after the "WITH" also include NO_WAIT & ROLLBACK IMMEDIATE
Providing for the New Data Base
ALTER DATABASE DatbaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
Tuesday, August 11, 2009
Deleting script data from Data Base
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Delete_SpamCode]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @tablename nvarchar(20)
declare @schema_name nvarchar(20)
declare @column_name nvarchar(20)
declare @strQ nvarchar(1000)
declare testcursor cursor for
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
open testcursor
FETCH NEXT FROM testcursor INTO @tablename,@schema_name,@column_name
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
set @strQ= 'update '+@tablename+' set '+@column_name+'=SUBSTRING('+@column_name+',0,CHARINDEX(''<'','+@column_name+'))
WHERE CHARINDEX(''<'','+@column_name+')>0 AND CHARINDEX(''>'','+@column_name+')>0'
print @strQ
EXECUTE(@strQ)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
FETCH NEXT FROM testcursor INTO @tablename,@schema_name,@column_name
END
CLOSE testcursor
DEALLOCATE testcursor
END
--exec [sp_Delete_SpamCode]
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Delete_SpamCode]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @tablename nvarchar(20)
declare @schema_name nvarchar(20)
declare @column_name nvarchar(20)
declare @strQ nvarchar(1000)
declare testcursor cursor for
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
open testcursor
FETCH NEXT FROM testcursor INTO @tablename,@schema_name,@column_name
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
set @strQ= 'update '+@tablename+' set '+@column_name+'=SUBSTRING('+@column_name+',0,CHARINDEX(''<'','+@column_name+'))
WHERE CHARINDEX(''<'','+@column_name+')>0 AND CHARINDEX(''>'','+@column_name+')>0'
print @strQ
EXECUTE(@strQ)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
FETCH NEXT FROM testcursor INTO @tablename,@schema_name,@column_name
END
CLOSE testcursor
DEALLOCATE testcursor
END
--exec [sp_Delete_SpamCode]
Subscribe to:
Posts (Atom)