[cc lang=”sql”]
SET NOCOUNT ON
DECLARE @BatchSize INT, @BatchCount INT, @totalCount INT
SET @BatchSize = 10000
SET @BatchCount = 0
SET @totalCount = (SELECT COUNT(*) FROM states)
WHILE EXISTS(SELECT * FROM states where desc like ‘%US%’)
BEGIN
UPDATE TOP (@BatchSize) states
set desc = ‘United States of America’
where desc like ‘%US%’
SET @BatchCount = @BatchCount + @@ROWCOUNT
END
SET NOCOUNT OFF
[/cc]
Sample templates which can be used for any SQL Stored procedure is needed if you want to quickly develop stored procedures with proper error handling and transactional concurrency. Basically it is placeholder or blueprint upon which the actual logic can be built. Quite handy, so check it out!
[cc lang=”sql”]
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_standard_proc]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_standard_proc]
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Purpose: To create sample template
Usage: exec usp_standard_proc
Author:
Created Date:
Modification Versions: Base Version : Added by on for the
Modified Version : Added by on for the
*/
CREATE PROCEDURE [dbo].[usp_standard_proc]
AS
BEGIN
SET nocount ON;
DECLARE @ProcName varchar(255)
SELECT @ProcName = ‘usp_standard_proc’
DECLARE @trancount INT;
SET @trancount = @@trancount;
BEGIN try
IF @trancount = 0
BEGIN TRANSACTION
ELSE
SAVE TRANSACTION usp_standard_proc;
— UNCOMMENT BELOW TO DISPLAY DIFFERENT ERROR MESSAGES
/*
SELECT CAST(‘RAM’ AS INT)
SELECT 1/0
*/
SELECT GETDATE()
IF @trancount = 0
COMMIT;
END try
BEGIN catch
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200),
@XactState INT
— Assign variables to error-handling functions that capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), ‘-‘),
@XactState = XACT_STATE()
— Build the message string that will contain original error information.
SELECT @ErrorMessage =
N’Error %d, Level %d, State %d, Procedure %s, Line %d, ‘ + ‘Message: ‘+ ERROR_MESSAGE();
IF @XactState = -1
ROLLBACK;
IF @XactState = 1
AND @trancount = 0
ROLLBACK
IF @XactState = 1
AND @trancount > 0
ROLLBACK TRANSACTION usp_standard_proc;
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine
)
END catch
END
GO
[/cc]
Believe me, remembering server names is one major headache when you are new to a certain environment on a job.
Imagine you just joined a company and faintly remember server name but not sure if
that’s correct, here is a way to list all the servers on a particular network:
1. click on start, and select run (or press windows logo + R simultaneously)
2. type cmd to launch dos prompt window
3. depending on the version of SQL server type
a) pre SQL 2005 Servers: osql -L
b) post SQL 2005 Servers : sqlcmd -L
and then press enter.
List Servers using MS Cmd Prompt
To add further either of above commands are internally run when we use SSMS too.
Checkout the screenshot:
SQL Server List
Thinking Hat says:
This is not a first option at all and rather may be just a fun exercise.
You will be better of contacting immediate supervisor as some of shops have 1000’s of servers on a particular domain (that you are part of) and might take a long time to retrieve the results.
By default the port number is 1433
For dynamic ports IP go to configuration manager.
(to be contd..)
A small overview of the constraints in RDBMS systems:
1. Entity Integrity: Makes sure that each row is uniquely identified.
2. Referential Integrity: Preserve the relationship between 2 tables and tying using foreign key constraints.
3. Domain Integrity: Check for default, unique and check constraints.
Sometimes when we are doing bulk ELT (yes..it is not ETL) and not doing proper cleansing while loading the data into staging tables, we would like to load the raw data by disabling the constraints as below:
[cc lang=”sql”]
CREATE TABLE T5 (
id int identity(1, 1) not null,
productQty varchar(10) null
)
— Add a check constraint
ALTER TABLE t5 WITH CHECK ADD constraint UK_t5_product CHECK(productQty > 100)
— Succeeds
INSERT into t5
SELECT 200
— Fails as it violates
INSERT into t5
SELECT 10
— Ignore the constraint by adding NOCHECK
ALTER TABLE t5 NOCHECK constraint UK_t5_product
— Now it succeeds
INSERT into t5
SELECT 10
— now check constraint by adding CHECK again after inserting the violation
ALTER TABLE t5 CHECK constraint UK_t5_product
— Catch the violators using DBCC command
dbcc checkconstraints(t5)
— clean up
DROP TABLE T5
[/cc]