dbo.People

Modify to suit – I just saved a few minutes of your time. You’re welcome!

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.People')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.People (
     ID BIGINT PRIMARY KEY IDENTITY(1,1),
     Salutation NVARCHAR(30) , -- Mr, Mrs, Dr, Miss, etc.
     Title NVARCHAR(100) , --
     FirstName1 NVARCHAR(50) ,
     FirstName2 NVARCHAR(50) ,
     MiddleName1 NVARCHAR(50) ,
     MiddleName2 NVARCHAR(50) ,
     MiddleInitial CHAR(1) ,
     LastName1 NVARCHAR(100) ,
     LastName2 NVARCHAR(100) ,
     Suffix NVARCHAR(30) );
END
GO
Advertisements

dbo.Addresses

I just saved you a few minutes of time – you’re welcome!

IF NOT EXISTS ( SELECT *
                FROM   sys.Objects
                WHERE  object_id = OBJECT_ID(N'dbo.Addresses')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.Addresses (
     ID BIGINT PRIMARY KEY IDENTITY(1,1),
     AddressType INT , -- Foreign Key to AddressTypes table
     ContactID BIGINT , -- Foreign Key to a People or Contacts table
     PreDirectional NVARCHAR(20) , -- South, SW, NE, East
     StreetNo NVARCHAR(20) , -- location on street
     StreetName NVARCHAR(50) , -- name of the street, if applicable
     CrossStreetName NVARCHAR(50) , -- for purposes of wayfinding
     PostDirectional NVARCHAR(20) , -- South, SW, NE, East
     StreetType NVARCHAR(30) , -- Street, Road, Boulevard, Annex, Canyon
     UnitNo NVARCHAR(20) , -- Apt, Suite, etc.
     PostOfficeBox NVARCHAR(20) ,
     FloorNo NVARCHAR(20) ,
     BuildingNo NVARCHAR(20) ,
     BuildingName NVARCHAR(60) ,
     CampusName NVARCHAR(50) ,
     LotNo NVARCHAR(20) ,
     PlatNo NVARCHAR(20) ,
     PlatName NVARCHAR(20) ,
     CityNo BIGINT , -- FK to Cities.CityID
     CityName NVARCHAR(20) ,
     ParishName NVARCHAR(50) ,
     PostalCode NVARCHAR(10) , -- USPS, Canada and other foreign postal services
     MailStop NVARCHAR(20) ,
     USPS_StopCode BIGINT , -- USPS postal stop code
     Latitude DECIMAL (18,4) ,
     Longitude DECIMAL (18,4) ,
     CountyID BIGINT ,
     CountyName NVARCHAR(30) ,
     StateID INT ,
     StateAbbr NVARCHAR(20) ,
     CountryID INT ,
     RegionID INT ,
     MSACode NVARCHAR(20)
);
END
GO

Cities, Counties, States

IF NOT EXISTS ( SELECT *
                FROM   sys.Objects
                WHERE  object_id = OBJECT_ID(N'dbo.Cities')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.Cities (
     CityID    BIGINT IDENTITY(1,1),
     CityName  NVARCHAR(50) ,
     StateID   INT ,
     MsaID     INT
  );
END
GO

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.Counties')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.Counties (
     CountyID    BIGINT IDENTITY(1,1),
     CountyName  NVARCHAR(50) ,
     StateID     BIGINT
  );
END
GO

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.States')
                AND    type in (N'U')
)
BEGIN
  CREATE TABLE dbo.States(
     ID            INT IDENTITY(1,1) NOT NULL,
     Name          NVARCHAR(40)      NOT NULL,
     Abbreviation  NVARCHAR(2)       NOT NULL,
     CountryID     INT               NOT NULL,
     CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED ([Id] ASC),
     CONSTRAINT [uc_States_Abbreviation]
     UNIQUE NONCLUSTERED ([Abbreviation] ASC)
);
SET IDENTITY_INSERT States ON;

DECLARE @US_CountryId INT = 226;
DECLARE @CAD_CountryId INT = 38;

INSERT INTO dbo.States ( Id, Name, Abbreviation, CountryId )
VALUES
(1, 'Alaska', 'AK', @US_CountryId),
(2, 'Alabama', 'AL', @US_CountryId),
(3, 'American Samoa', 'AS', @US_CountryId),
(4, 'Arizona', 'AZ', @US_CountryId),
(5, 'Arkansas', 'AR', @US_CountryId),
(6, 'California', 'CA', @US_CountryId),
(7, 'Colorado', 'CO', @US_CountryId),
(8, 'Connecticut', 'CT', @US_CountryId),
(9, 'Delaware', 'DE', @US_CountryId),
(10, 'District of Columbia', 'DC', @US_CountryId),
(11, 'Florida', 'FL', @US_CountryId),
(12, 'Georgia', 'GA', @US_CountryId),
(13, 'Guam', 'GU', @US_CountryId),
(14, 'Hawaii', 'HI', @US_CountryId),
(15, 'Idaho', 'ID', @US_CountryId),
(16, 'Illinois', 'IL', @US_CountryId),
(17, 'Indiana', 'IN', @US_CountryId),
(18, 'Iowa', 'IA', @US_CountryId),
(19, 'Kansas', 'KS', @US_CountryId),
(20, 'Kentucky', 'KY', @US_CountryId),
(21, 'Louisiana', 'LA', @US_CountryId),
(22, 'Maine', 'ME', @US_CountryId),
(23, 'Maryland', 'MD', @US_CountryId),
(24, 'Massachusetts', 'MA', @US_CountryId),
(25, 'Michigan', 'MI', @US_CountryId),
(26, 'Minnesota', 'MN', @US_CountryId),
(27, 'Mississippi', 'MS', @US_CountryId),
(28, 'Missouri', 'MO', @US_CountryId),
(29, 'Montana', 'MT', @US_CountryId),
(30, 'Nebraska', 'NE', @US_CountryId),
(31, 'Nevada', 'NV', @US_CountryId),
(32, 'New Hampshire', 'NH', @US_CountryId),
(33, 'New Jersey', 'NJ', @US_CountryId),
(34, 'New Mexico', 'NM', @US_CountryId),
(35, 'New York', 'NY', @US_CountryId),
(36, 'North Carolina', 'NC', @US_CountryId),
(37, 'North Dakota', 'ND', @US_CountryId),
(38, 'Northern Mariana Islands', 'MP', @US_CountryId),
(39, 'Ohio', 'OH', @US_CountryId),
(40, 'Oklahoma', 'OK', @US_CountryId),
(41, 'Oregon', 'OR', @US_CountryId),
(42, 'Palau', 'PW', @US_CountryId),
(43, 'Pennsylvania', 'PA', @US_CountryId),
(44, 'Puerto Rico', 'PR', @US_CountryId),
(45, 'Rhode Island', 'RI', @US_CountryId),
(46, 'South Carolina', 'SC', @US_CountryId),
(47, 'South Dakota', 'SD', @US_CountryId),
(48, 'Tennessee', 'TN', @US_CountryId),
(49, 'Texas', 'TX', @US_CountryId),
(50, 'Utah', 'UT', @US_CountryId),
(51, 'Vermont', 'VT', @US_CountryId),
(52, 'Virgin Islands', 'VI', @US_CountryId),
(53, 'Virginia', 'VA', @US_CountryId),
(54, 'Washington', 'WA', @US_CountryId),
(55, 'West Virginia', 'WV', @US_CountryId),
(56, 'Wisconsin', 'WI', @US_CountryId),
(57, 'Wyoming', 'WY', @US_CountryId),
-- included here for lookup convenience
(58, 'Alberta', 'AB', @CAD_CountryId),
(59, 'British Columbia', 'BC', @CAD_CountryId),
(60, 'Manitoba', 'MB', @CAD_CountryId),
(61, 'New Brunswick', 'NB', @CAD_CountryId),
(62, 'Newfoundland and Labrador', 'NL', @CAD_CountryId),
(63, 'Northwest Territories', 'NT', @CAD_CountryId),
(64, 'Nova Scotia', 'NS', @CAD_CountryId),
(65, 'Nunavut', 'NU', @CAD_CountryId),
(66, 'Ontario', 'ON', @CAD_CountryId),
(67, 'Prince Edward Island', 'PE', @CAD_CountryId),
(68, 'Québec', 'QC', @CAD_CountryId),
(69, 'Saskatchewan', 'SK', @CAD_CountryId),
(70, 'Yukon Territory', 'YT', @CAD_CountryId);

SET IDENTITY_INSERT dbo.States OFF;
END
GO

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.CanadianProvinces')
                       AND type in (N'U')
)
BEGIN
CREATE TABLE dbo.CanadianProvinces
(
   ProvinceID    BIGINT PRIMARY KEY IDENTITY(1,1) ,
   ProvinceName  NVARCHAR(50) ,
   Abbreviation  CHAR(2)
);
INSERT INTO dbo.CanadianProvinces
VALUES
('British Columbia','BC'),
('Manitoba','MB'),
('Alberta','AB'),
('Ontario','ON'),
('Saskatchewan','SK'),
('Quebec','QE'),
('Nunavut','NU'),
('Yukon','YK'),
('Northwest Territories','NT'),
('Nova Scotia','NS'),
('New Brunswick','NB'),
('Newfoundland & Labrador','NL'),
('Prince Edward Island','PE');
END
GO

T-SQL Try-Catch Template

–TRY/CATCH TEMPLATE
BEGIN TRY
–check to see if .Net initiated a SqlTransaction before calling this proc
IF @@TRANCOUNT = 0
BEGIN
–.NET did not initiate an explicit SqlTransaction outside of the procedure
— as a result, we initiate an explicit T-SQL transaction here.
BEGIN TRAN;
END

/* PUT SCRIPT CODE RIGHT HERE */

— PRINT ‘>> COMMITING TRANSACTION’ — optional
— no error yet, so commit the transaction
— if query is select only; this statement is meaningless.
IF @@TRANCOUNT = 1 COMMIT TRAN;
END TRY

BEGIN CATCH
/*
There will be either a .Net SqlTransaction or T-SQL transaction here to rollback;
we need to roll back that transaction before handling the exception.

Test XACT_STATE for 0, 1, or -1.
1 = the transaction is COMMITTABLE (we are going to roll back anyways).
-1 = the transaction is UNCOMMITTABLE and should be rolled back.
0 = there is no transaction and a commit or rollback operation would generate an error.

Test whether the transaction is uncommittable.
*/

IF (XACT_STATE() 0)
BEGIN
PRINT ‘>> ROLLING BACK TRANSACTION’
ROLLBACK TRANSACTION;
END

—- OPTIONAL EXCEPTION PROPERTIES TO CATCH AS NEEDED.
–@ErrorNo INT = ERROR_NUMBER(),
–@ErrorProcedure NVARCHAR(200) = ERROR_PROCEDURE(),
–@ErrorLine AS INT = ERROR_LINE()

DECLARE @ErrorMessage NVARCHAR(4000) ,
@ErrorSeverity INT ,
@ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE();

— Use RAISERROR inside the CATCH block to return error information
— about the original error that caused execution to jump to the CATCH block.
PRINT ‘>> RAISING ERROR’
https://technet.microsoft.com/en-us/library/ms177497(v=sql.105).aspx
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState );
END CATCH

SQL Server – error 64 : Server-name no longer available

I ran across a crazy issue this am that I’ve never seen before. I was setting up SQL Server Express 2008R2 on a smaller server at home, the likes of which I have done many times before with no issues. Then I did the following, per my usual config routine:

  • Opened ports 1433 and 1434 on this server for “listening” for SQL requests
  • Opened a hole in the firewall for SQLBrowser.exe
  • Enabled TCP/IP listening for those same ports in SQLServerManager10.msc (running on Windows 10 Professional operating system).
  • Enabled remote connections to the Server process
  • Set up a login account for Administrator with full rights to the SQL server processes

So then I went to test the setup from my laptop, fired up SQL Server Management Studio, and connected to the SQL Server.  Connection made – check.  Opened up a known scalar function to modify – check.  Run the alter script – check.  Run it again to double check the persistence of the network connection – ERROR 64.

SQL_Server_Cannot_Connect

I had never seen that one before, but I knew it was network or network protocol related.

I remoted into this particular server and started double-checking all settings that would seem to have any relevance to that issue.

After A LOT of Googling and matching snippets of information together, I finally found resolution with the following regedit modification for the local registry.

  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters
  • Create a DWORD “Sesstimeout” key with a decimal value of 360.

reg_Edit

It seems the session-layer of the connection was not persistent enough for remote connections.  Why doesn’t Microsoft take care of this by default with a config script? We might never live long enough to know…

Problem solved, for now.  If I see it again, I will update this post.

Find all triggers in a SQL Server database

Use the following small script to find all hidden triggers in a SQL Server database.  Pretty much all competent developers using SQL agree that using triggers is a bad practice, but few know how to find them before they have wasted a lot of development and testing time not understanding why things aren’t working the way they want them to (because of triggers doing behind the scenes work, undesirably).

Trigger_the_horse
Trigger the horse, with Roy Rogers.
-- Finds all "hidden" triggers in a database. Using triggers is not a best practice.
USE <database_name>;
GO
SELECT  name AS [TriggerName]
FROM    sys.objects
WHERE   OBJECTPROPERTY(object_id, 'IsTrigger') <> 0;