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:


Monday, July 28, 2014

[SQLSTATE 23000] (Error 547) The statement has been terminated. [SQLSTATE 01000] (Error 3621)

SQL Agent job failed with below Error

Message
Executed as user: ArrivalsDepartures. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DepartureStatusArchive_TechnicianName". The conflict occurred in database "DatabaseName", table "dbo.Users", column 'UserID'. [SQLSTATE 23000] (Error 547)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.


Root Cause: Error is not refering the table which is having Foreign-key to the Users table rather it mentioned the constraint name FK_DepartureStatusArchive_TechnicianName.
Also the procedure is inserting data from Table A to Table B. Table B is having the foreign key to Users Table. This foreign key column is accepting NULLs but the column in Users table is Primary Key.

Fix: Actually the culprit table is Archive table and the table structure was changed recently as part of one change. As the table is Archive and it not required referential key to Users table I dropped the foreign key and now the job executed successfully.

Dropped Foreign Key: Expand the table B->expand the Keys->right click and script the key to be deleted-> run it against the Database and the constraint is deleted.

Tuesday, July 22, 2014

MAX DEGREE OF PARALLELISM AND AFFINITY MASK

                                      
This is very rare interview question I faced in one of my interview and thought to write a quick article with reference of MSDN.
When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. The maximum value for the degree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

Use below guidelines to configure
1.        For servers that use more than eight processors, use the following configuration:
          MAXDOP=8
2.        For servers that use eight or fewer processors, use the following configuration:
MAXDOP=0 to N (N represents the number of processors)
Also, the maximum value of 8 that is mentioned in these guidelines is applicable for typical SQL Server activity and the overhead for the exchange operators that are used in parallel query plans. You can vary this maximum value, depending on your specific application patterns and the concurrent activity on the instance of SQL Server. For example, consider the following situations:
·         If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value. For example, you can set the MAXDOP value to 16.
·         If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value. For example, you can set the MAXDOP value to 4.
The max degree of parallelism option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change max degree of parallelism only when show advanced options is set to 1. The setting takes effect immediately (without restarting the MSSQLSERVER service).
The following example sets the max degree of parallelism option to 8.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

Using Management Studio
1.        In Object Explorer, right-click a server and select Properties.
2.        Click the Advanced node.
3.        In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.
The default value of 0 uses all available processors. Set max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single parallel query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.
Note: The max degree of parallelism configuration option does not limit the number of processors that SQL Server uses. To configure the number of processors that SQL Server uses, use the affinity mask configuration option.

                                                       Affinity Mask Option
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads and reducing thread migration across processors (thereby reducing context switching); such an association between a thread and a processor is called processor affinity.
Affinity support for servers with 33 to 64 processors is only available on 64-bit operating systems.
Changes to the affinity masks occur dynamically, allowing for on-demand startup and shutdown of the CPU schedulers that bind process threads within SQL Server. This can occur as conditions change on the server. For example, if a new instance of SQL Server is added to the server, it may be necessary to make adjustments to the affinity mask option to redistribute processor load.
Modifications to the affinity bitmasks require SQL Server to enable a new CPU scheduler and disable the existing CPU scheduler. New batches can then be processed on the new or remaining schedulers.
To start a new CPU scheduler, SQL Server creates a new scheduler and adds it to the list of its standard schedulers. The new scheduler is considered only for the new incoming batches. Current batches continue to run on the same scheduler. The workers migrate to the new scheduler as they free up, or as new workers are created. Shutting down a scheduler requires all batches on the scheduler to complete their activities and exit. A scheduler that has been shut down is marked as offline so that no new batch is scheduled on it.
Whether a new scheduler is added or removed, the permanent system tasks such as lockmonitor, checkpoint, system task thread (processing DTC), and signal process continue to run on the scheduler while the server is operational. These permanent system tasks do not dynamically migrate. To redistribute processor load for these system tasks across schedulers, it is necessary to restart the SQL Server instance. If SQL Server attempts to shut down a scheduler associated with a permanent system task, the task continues to run on the offline scheduler (no migration). This scheduler is bound to the processors in the modified affinity mask and should not put any load on the processor it was affinitized with before the change. Having extra offline schedulers, should not significantly affect the load of the system. If this is not the case, a database server reboot is required to reconfigure these tasks.
The I/O affinity tasks (such as lazywriter and logwriter) are directly affected by the I/O affinity mask. If the lazywriter and logwriter tasks are not affinitized, they follow the same rules defined for the other permanent tasks such as lockmonitor or checkpoint.
The values for affinity mask are as follows:
1.        A one-byte affinity mask covers up to 8 CPUs in a multiprocessor computer.
2.        A two-byte affinity mask covers up to 16 CPUs in a multiprocessor computer.
3.        A three-byte affinity mask covers up to 24 CPUs in a multiprocessor computer.
4.        A four-byte affinity mask covers up to 32 CPUs in a multiprocessor computer.
5.        To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.
As an example of setting the affinity mask option, if processors 1, 2, and 5 are selected as available with bits 1, 2, and 5 set to 1 and bits 0, 3, 4, 6, and 7 set to 0, a hexadecimal value of 0x26 or the decimal equivalent of 38 is specified. Number the bits from right to left. The affinity mask option starts counting processors from 0 to 31, so that in the following example the counter 1 represents the second processor on the server.

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 38;
RECONFIGURE;
GO

These are affinity mask values for an 8-CPU system.

Decimal value
     Binary bit mask
    Allow SQL Server threads on processors
1
    00000001
   0
3
    00000011
   0 and 1
7
    00000111
   0, 1, and 2
15
    00001111
   0, 1, 2, and 3
31
    00011111
   0, 1, 2, 3, and 4
63
    00111111
   0, 1, 2, 3, 4, and 5
127
    01111111
   0, 1, 2, 3, 4, 5, and 6
255
    11111111
   0, 1, 2, 3, 4, 5, 6, and 7
The affinity mask option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change affinity mask only when show advanced options is set to 1. After executing the Transact-SQL RECONFIGURE command, the new setting takes effect immediately without requiring a restart of the SQL Server instance.

Refer below links for detailed information