Tuesday, December 16, 2014

How to Find and Replace Text in All Stored Procedures

I need to restore the backup of production DB into the test server. The production linked server name should be replaced with the test linked server but it is difficult to find the procedures with specific linked server name. Below is the helpful script which I used.

-- set "Result to Text" mode by pressing Ctrl+T


SET NOCOUNT ON

DECLARE
@sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)

-- text to search for
SET @searchFor = '[MY-SERVER]'
-- text to replace with
SET @replaceWith = '[MY-SERVER2]'

-- this will hold stored procedures text
DECLARE @temp TABLE (spText VARCHAR(MAX))

DECLARE curHelp CURSOR FAST_FORWARD
FOR
-- get text of all stored procedures that contain search string
-- I am using custom escape character here since i need to espape [ and ] in search string
SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'
ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '

OPEN curHelp

FETCH next FROM curHelp INTO @sqlToRun

WHILE @@FETCH_STATUS = 0
BEGIN
--insert stored procedure text into a temporary table
INSERT INTO @temp
EXEC (@sqlToRun)

-- add GO after each stored procedure
INSERT INTO @temp
VALUES ('GO')

FETCH next FROM curHelp INTO @sqlToRun
END

CLOSE
curHelp
DEALLOCATE curHelp

-- find and replace search string in stored procedures
-- also replace CREATE PROCEDURE with ALTER PROCEDURE
UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER PROCEDURE'),@searchFor,@replaceWith)

SELECT spText FROM @temp
-- now copy and paste result into new window
-- then make sure everything looks good and run
GO

I have tested this in my environment and found it working fine.

Monday, December 15, 2014

SQL SERVER – Can Database Primary File Have Any Other Extention Than MDF


I just opened Pinal’s blog to check the updated articles and happened to read this. This option got my interest to test. Downloaded SQL 2014 Express edition and tested it. It was working fine and interesting for me.

But I am not sure how it helps. We cannot format our DB in to PDF and attach so obviously the DB is not useful.

Install Express edition using below link and test:


Use below script to create the DB.

USE [master]
GO
CREATE DATABASE [tests] ON
( FILENAME = N'D:\data\tests.pdf' ),
( FILENAME = N'D:\data\tests_log.ldf' )
FOR ATTACH
GO

My DB Screen:

 
 
 
 
 
 
 
Pinal has given DB files in the below link


References:

Function to get rid of all HTML tags in the summary


I had to provide Ticket summary to one of my user and got revert by asking to have the summary without HTML tags  L

Wondering if we have any such option and started google.

My goodness..found below function and it was working as per my request.

Create this function in your user Database and execute in the below mentioned way.

Function:

USE [DB NAME]

GO

/****** Object:  UserDefinedFunction [dbo].[udf_StripHTML]    Script Date: 12/15/2014 11:34:50 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))

RETURNS VARCHAR(MAX) AS

BEGIN

    DECLARE @Start INT

    DECLARE @End INT

    DECLARE @Length INT

    SET @Start = CHARINDEX('<',@HTMLText)

    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

    SET @Length = (@End - @Start) + 1

    WHILE @Start > 0 AND @End > 0 AND @Length > 0

    BEGIN

        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')

        SET @Start = CHARINDEX('<',@HTMLText)

        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

        SET @Length = (@End - @Start) + 1

    END

    RETURN LTRIM(RTRIM(@HTMLText))

END

 

Execution:

 

declare @htmltext varchar(max);

declare @ret varchar(max);

 

 

exec @ret= [dbo].[udf_StripHTML] @htmltext='<p> <strong><u>ATTENTION SERVICES TEAM - </u></strong><br /> Contract # is being supported by Terix who provide TAC, Field support and NBD

replacement for the server equipment.<br /> <br /> will be fielding calls and looping in Terix as needed<br /> Our engineers are

to be on the call at all times<br /> Please do not mention Terix as the customer doesnt need to know this information<br /> Please

open a ticket with the customer’s contact details, serial #, and address where the equipment is located<br /> <br /> 1. Do not refuse

service<br /> 2. Let the customer know we will call them back shortly with a member from their dedicated support team<br /> 3. Call

Terix and establish a ticket or log in thru portal with provided username and password<br /> 4. When ready, conference in the customer

<br /> 5. All calls need an  tech on them, DO NOT GET OFF THE CALL<br /> 6. Terix IT handles Support, Spares & Logistics<br />

<br /> We have an arrangement with a 3rd party (Terix ) to support this customers equipment. Here is the procedure for creating a

ticket. You can also click on the truck next to the serial number for contact info.<br /> <br /> To open a ticket at any time 24 hours

/day<br /> Please call 1, ask for a support engineer and email </a><br /> Or Log in a ticket thru the portal @ < SOP for detials Should

an engineer not be available immediately please describe the problem and provide the following information: account name, your name,

call back number and summary of the issue you are experiencing.<br /> An engineer will call you back within 15- 30 minutes to begin

resolving your issue..<br />  </p>'

 

print @ret

 

Output:

ATTENTION SERVICES TEAM -  Contract # is being supported by Terix who provide TAC, Field support and NBD

replacement for the server equipment.  will be fielding calls and looping in Terix as needed Our engineers are

to be on the call at all times Please do not mention Terix as the customer doesnt need to know this information Please

open a ticket with the customer’s contact details, serial #, and address where the equipment is located  1. Do not refuse

service 2. Let the customer know we will call them back shortly with a member from their dedicated support team 3. Call

Terix and establish a ticket or log in thru portal with provided username and password 4. When ready, conference in the customer

 5. All calls need an  tech on them, DO NOT GET OFF THE CALL 6. Terix IT handles Support, Spares & Logistics

 We have an arrangement with a 3rd party (Terix ) to support this customers equipment. Here is the procedure for creating a

ticket. You can also click on the truck next to the serial number for contact info.  To open a ticket at any time 24 hours

/day Please call 1, ask for a support engineer and email  Or Log in a ticket thru the portal @  An engineer will call you back within 15- 30 minutes to begin

resolving your issue..

 

References: