Apr 07

SQL Server String to Table User Defined Function

The following will create a user defined function (in the master table), and will allow a user to essentially split an entire string into individual rows by a character.

Why use it? I found it to be useful for creating a translation/obfuscation results.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2014-04-07
 Description: Splits a string into a table
  Parameters:
    @Input - String to split into a table
     Returns: Table
                Id INT
                Value NVARCHAR(1)
*/

IF EXISTS (SELECT * FROM sys.objects WHERE name='udf_TextToTable' AND schema_id=SCHEMA_ID('dbo') AND type='TF')
BEGIN
    DROP FUNCTION dbo.udf_TextToTable
END
GO

CREATE FUNCTION dbo.udf_TextToTable
(
    @Input NVARCHAR(MAX)
)
RETURNS 
@Return TABLE (Id INT, Value NVARCHAR(1))
AS
BEGIN
	-- Fill the table variable with the rows for your result set

    INSERT INTO @Return
        SELECT  T.ID, SUBSTRING(@Input, T.ID, 1) Value
        FROM    master.dbo.Tally T
        WHERE   T.ID BETWEEN 1 AND LEN(@Input)

    RETURN
END
GO

SELECT * FROM dbo.udf_TextToTable('Welcome to Denver Code Monkey!')
Posted in SQL
Feb 13

Drop All SQL Server Objects

The following script will drop all of the SQL Server objects.

PLEASE. USE. CAUTION.

DECLARE @n CHAR(1)
SET @n = CHAR(10)

DECLARE @stmt NVARCHAR(MAX)

PRINT 'Dropping Stored Procedures'
-- PROCEDURES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP PROCEDURE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.procedures

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Check Constraints'
-- CHECK CONSTRAINTS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.check_constraints

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Functions'
-- FUNCTIONS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP FUNCTION [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.objects
WHERE   TYPE IN ( 'FN', 'IF', 'TF' )

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Views'
-- VIEWS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP VIEW [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.views

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Foreign Keys'
-- FOREIGN KEYS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.foreign_keys

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Tables'
-- TABLES
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP TABLE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.tables

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Types'
-- USER DEFINED TYPES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP TYPE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.types
WHERE   is_user_defined = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Schemas'
-- SCHEMAS
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP SCHEMA [' + name + ']'
FROM    sys.schemas
WHERE   name <> N'dbo'
  AND   principal_id = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL
Posted in SQL
Dec 26

Backup Database Stored Procedure

The following script will handle Full backups, Differential backups, and transaction log backups.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2013-12-26
 Description: Backup Databases
  Parameters:
    @DatabaseName   - Name of the database
    @BackupType     - F=full, D=differential, L=log
    @BackupLocation - backup directory location
*/ 
CREATE PROCEDURE [dbo].[sp_BackupDatabase]  
    @DatabaseName NVARCHAR(100),
    @BackupType CHAR(1),
    @BackupLocation NVARCHAR(200) 
AS
BEGIN
    SET NOCOUNT ON; 
           
    -- Declare variables
    DECLARE @BackupName NVARCHAR(100),
            @BackupFile NVARCHAR(100),
            @SqlCommand NVARCHAR(1000),
            @Date NVARCHAR(8),
            @Time NVARCHAR(6)

    -- Provide the backup a name for storing in the media
    SELECT  @Date = REPLACE(CONVERT(VARCHAR, GETDATE(), 102), '.', ''), --YYYYMMDD
            @Time = REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), --HHMMSS
            @BackupFile = @backupLocation + REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') +
                CASE
                    WHEN @BackupType='F' THEN '_FULL_' + @Date + '_' + @Time
                    WHEN @BackupType='D' THEN '_DIFF_' + @Date + '_' + @Time
                    ELSE '_LOG_' + @Date + '_' + @Time
                END + 
                CASE
                    WHEN @BackupType='F' THEN '.BAK'
                    WHEN @BackupType='D' THEN '.BAK'
                    ELSE '.TRN'
                END, --[c:\directory\][Database]_[LongType]_[Date][_Time].[Ext]
            @BackupName = REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') + ' ' +
                CASE
                    WHEN @BackupType='F' THEN 'full'
                    WHEN @BackupType='D' THEN 'differential'
                    ELSE 'log'
                END + ' backup for ' + @Date + '_' + @Time, -- [Database] [BackupType] backup for [Date]_[Time]
            @SQLCommand = 'BACKUP ' +
                CASE
                    WHEN @BackupType='F' THEN 'DATABASE'
                    WHEN @BackupType='D' THEN 'DATABASE'
                    ELSE 'LOG'
                END + ' ' +
                @DatabaseName + ' TO DISK = ''' + @BackupFile + ''' WITH ' +
                CASE
                    WHEN @BackupType='D' THEN 'DIFFERENTIAL, '
                    ELSE ''
                END + 'INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'

    -- Execute the generated SQL command
    EXEC(@sqlCommand)
END

Run it as the following

--Full Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'F', 'c:\temp\'

--Differential Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'D', 'c:\temp\'

--Transaction Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'L', 'c:\temp\'
Posted in SQL
Dec 19

SQL Server Tally Table

The following Tally Table will help in future posts.

The Tally table is simply a table with an ID column, that will help in reducing most loops; a personal preference of mine.

CREATE TABLE dbo.Tally (
    ID INT NOT NULL
)
GO
ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally
    PRIMARY KEY CLUSTERED ( ID )
GO

--2048 * 8 = 16384 Results
INSERT INTO dbo.Tally
    SELECT  a.number * 2048 + b.number + 1 ID
      FROM  [master].dbo.spt_values a,
            [master].dbo.spt_values b
    WHERE   a.[type] = 'p'
      AND   b.[type] = 'p'
      AND   b.number < 2048
      AND   a.number < 8
    ORDER BY a.number, b.number
GO

If you want more results, you will need to mess with the “a.number < 8" line in the where clause. Each increase will result in an extra 2048 entries. I find that 16k+ suits my needs just fine.

Posted in SQL
Dec 05

Spam from Software Outsourcing Company BairesDev

Below is an email I got from the day job. I haven’t done business with this person or company, and my LinkedIn profile (nor any public profile) has my work email address. So, it is more than likely that this person saw my name, utilized a common email form, and sent off the email.

Since I haven’t agreed to the confidentiality agreement at the bottom of his email, I do not respect, nor need to follow it.

I sincerely hope that they clean up their marketing campaign, as it is NOT appreciated by this developer.

Ignacio, please enjoy the free publicity that I am giving your company. After all, bad press is still press, right?


Dear Rj,

First of all I’d like to introduce myself. My name is Ignacio De Marco and I am the Chief Business Officer at BairesDev. I have found your profile on LinkedIn and, given your role, thought that it might be a good idea to contact you and offer some services that could be of interest to you or your company.

As a brief introduction, our United States & Argentina-based development centers offer a variety of services:

  • Software Outsourcing
  • Software Development
  • Test Automation
  • Remote Support & Cloud Computing

All of these services can be delivered on or off-site, depending on your needs. With more than 200 bilingual employees, we provide services for companies worldwide, from major global companies like Google (GOOG), Netgear (NTGR) or Demand Media (DMD) in the US and Kapsch in Europe (KTCG) to small companies focused on specific markets. More details about our services can be found on http://www.bairesdev.com.

If you are interested in outsourcing any services, I would be very happy to organize an initial call to discuss potential opportunities with you.

Do you think any of the services above can be useful to you or your colleagues?

Kind regards,

Ignacio De Marco

Chief Business Officer

US: +1 408 600 1331

AR: +54 11 5353 9840

Cel: +54 911 3809 1901

Email: idemarco@bairesdev.com

Skype: ignaciodemarco

LinkedIn: http://www.linkedin.com/in/idemarco

BairesDev's Spamming Website Center

This Email (including any attachments) is strictly confidential and intended solely for the addressee. If you are not the intended addressee, you must not disclose, copy, or take any action on account of this message. If you have received this message in error, please notify the sender immediately and delete all copies.

Este correo electrónico (incluyendo cualquier archivo adjunto) es estrictamente confidencial y está destinado exclusivamente a su destinatario original. Si usted no es el destinatario, no debe revelar, copiar, o tomar cualquier otra acción sobre este mensaje. Si usted ha recibido este mensaje por error, por favor notifique al remitente inmediatamente y elimine todas las copias.

iso yeah right

BairesDev is a quality endorsed business to the international quality standard ISO9001:2008. This standard ensures that we deliver what we promise, when we promise it, every time.

Nov 25

Dropping SQL Server Cache

If you are anything like me, you want to know if your updates to your database have improved on a 0-60 basis or not.

Unfortunately, if you run the same query back-to-back, then you will often run into SQL Server caching the query and optimizing your results.

The following will clean out the cache for your testing and analysis purposes.

USE {DATABASE};
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
Posted in SQL
Oct 30

Drop All SQL Server Default Constraints on a Table

Recently I was working on updating some tables, and the SQL that was generated for the update included some code that looked like this:

ALTER TABLE [schema].[table]
    DROP CONSTRAINT [DF__Table__Column__a789cv78s7s]

Well, it dawned on me that the various environments we have *COULD* have different constraint names.

Now, these constraints will be deleted when the table is dropped, so an explicit drop is not necessarily required.

However, if you actually name your default constraints, rather than utilize the shorthand in the table definition, then you may run into an issue of duplicate default constraint names.

This handy script will drop all of the defaults on a particular table.

DECLARE @SchemaName NVARCHAR(256),
        @TableName NVARCHAR(256),
        @SQL NVARCHAR(MAX),
        @NewLine CHAR(1)

SELECT  @SchemaName = N'dbo',
        @TableName = N'TheNameOfTheTable',
        @NewLine = CHAR(10)

SELECT  @SQL = ISNULL(@SQL + @NewLine, '') + 
            'ALTER TABLE [' + S.name + '].[' + T.name + '] ' +
                'DROP CONSTRAINT [' + D.name + ']'
FROM    sys.tables T
    INNER JOIN sys.default_constraints D
        ON D.parent_object_id = T.object_id
    INNER JOIN sys.columns C
        ON C.object_id = T.object_id
            AND C.column_id = D.parent_column_id
    INNER JOIN sys.schemas S
        ON T.schema_id = S.schema_id
WHERE   S.name = @SchemaName
  AND   T.name = @TableName

PRINT @SQL
EXECUTE (@SQL)
Posted in SQL
Sep 26

SQL Bulk Insert

So I’m sure there are a ton of resources already out there regarding this, so this is more for me.

To use the BULK INSERT command, you’ll first need to create your data file.

ID|StateID|ZipCode|City|County|State|TimeZone|PopulationPercent|FIPS
1|33|00501|HOLTSVILLE|Suffolk|NY|EST|100|36103
2|33|00501|I R S SERVICE CENTER|Suffolk|NY|EST|100|36103
3|33|00544|HOLTSVILLE|Suffolk|NY|EST|100|36103
4|33|00544|IRS SERVICE CENTER|Suffolk|NY|EST|100|36103
5|58|00601|ADJUNTAS|Adjuntas|PR||100|43001
...

Then you’ll need to create the SQL statement.

BULK INSERT [Table]
FROM 'c:\Dir\File.txt'
WITH (
     DATAFILETYPE = 'char',
     FIELDTERMINATOR = '|',
     FIRE_TRIGGERS,
     MAXERRORS = 50,
     TABLOCK
     )

And there you go!

Much faster than a crap-ton of insert statements 🙂

Posted in SQL
Apr 25

How to Get Rid of Cursors for Inserting Bulk Data

This is pseudo code, but you should be able to get the idea from here…

Will develop more as requested…

DECLARE @t TABLE (Columns Def, repeat ...)

insert into @t
  select from flat_file

insert into parent
  select parent, fields from @t

insert into child
  select p.parentid, c.child, c.fields
  from	@t c
    inner join parent p
      on c.parentkey = p.parentkey
Posted in SQL
Apr 15

Why I love .NET – Reason 314 – Config Files

While working the day job, I am porting over some sites from one virtual site to another.

Our clients will typically have their own external domain, which will get http redirected to a client specific sub-domain. Unfortunately, each external website has pretty much the exact same redirect page, under construction page, landing page, etc. The only difference is the redirect URL. Which, luckily in our case, can be found in the database (though, not really used).

During a deployment, we must manually turn each of the default pages to use the Under Construction page in IIS. This will prevent the redirect from occurring. However, you can see how the success of our company could lead to 4 hour long deployments, just in switching the default page.

“Why not script it?” you might be asking… That’s really the equivalent of saying “buy a nail gun if you’re nailing things” and not asking the question “is this implementation the right implementation?” In this case, it’s not.

I wrote an app that will determine the exact same thing, and do it in a fraction of the footprint, and will let us grow with little production deployment pains.

Unfortunately, we will still need to implement the updates in the production environment to make this a reality. No small task if you’re looking at doing this through the IIS manager.

But, let’s not forget that this is a server application, and therefore, should have itself a command line interface. IT DOES! It’s called appcmd, and is found in the %windows%\System32\inetsrv directory. But after doing some research, it looks like it may have some limitations when retrieving the data.

Fortunately, there is another way, and it’s found in the %windows%\System32\inetsrv\config\applicationHost.config file. I will be playing with this more in the next few hours, and so I hope to report some success.