Troubleshooting MS SQL Server: December 2014

Dec 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.

Dec 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:

http://www.hanselman.com/blog/DownloadSqlServerExpress.aspx

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

http://www.pinaldave.com/bimg/data.zip

References:

http://blog.sqlauthority.com/2014/12/14/sql-server-can-database-primary-file-have-any-other-extention-than-mdf/#comment-1573847

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]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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=' 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 </a> 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. An engineer will call you back within 15- 30 minutes to begin

resolving your issue.. '

print @ret

Output:

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