Joseph Gozlan, Information Systems Engineer

Elegant solution for splitting a Comma Delimited string in SQL

2012-01-12T14:20:00.000-08:00

came across this elegant simple solution for splitting a Comma Delimited string in SQL.
This will only work with SQL 2005 and up but it's so simple and elegant i had to share it.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id	AllNames
1	A,B,C
2	A,B
3	X,Y,Z

And here is the expected output -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

Create Sample Data :

-- Create Table for Sample Data

CREATE TABLE Test

(

ID INT,

AllNames VARCHAR(100)

)

GO

-- Load Sample Data

INSERT INTO test SELECT

1, 'A,B,C' UNION
ALL SELECT

2, 'A,B' UNION
ALL SELECT

3, 'X,Y,Z'

GO
-- Verify the Sample Data

SELECT Id,
AllNames

FROM Test

And here is the query for How to split a comma
delimited string :

;WITH Cte AS
(

    SELECT

        id,

        CAST('<M>'
+ REPLACE(
Allnames,  ',' , '</M><M>') + '</M>' AS XML) AS Names

    FROM Test

)

SELECT

    ID,

    Split.a.value('.', 'VARCHAR(100)') AS Names

FROM Cte

CROSS APPLY Names.nodes('/M') Split(a)

---------------------------------------------------------

Acknowledgement : SQL WITH MANGAL

Debugging VBS with Visual Studio

2011-10-02T08:17:00.000-07:00

If you must use VBS scripts (I can't think of a reason why you would WANT to use them) then there is an easier way to debug these (other than the old school printouts).

from the command prompt run:

cscript.exe test.vbs //X

this will pop up the debugger choice with the list of Visual Studio apps available (2003/05/08/10 etc.) and will allow you to step in and debug line by line.

Apppools on Windows Server 2008

2011-10-02T08:10:00.000-07:00

If you need to set a flag on the apppool in server 2008 you can also do that via script. Here is an example of how to set the "Enable 32bit application support" flag to "true" in server 2008 apppool:

cd %windir%\system32\inetsrv\
appcmd set apppool /apppool.name:CPOSBackOfficeWS /enable32BitAppOnWin64:true

Task failed because “sgen.exe” was not found, or the correct Microsoft Windows SDK is not installed.

2010-11-12T09:02:00.000-08:00

If you started using Visual Studio 2008 and didn't have a previous version of it then you will get this error trying to compile project that reference a web service. It only happens if you use .NET 2.0 version and compile in Release mode.

it's actually looking for a 2.0 component from the 2.0 SDK that is not being installed with your VS2008.

The solution:
Download and install .NET 2.0 SDK (from the Microsoft website) then copy the sgen.exe file from C:\Program Files\Microsoft.NET\SDK\v2.0 to C:\Windows\Microsoft.NET\Framework\v3.5

Call stack window missing from VS2008?

2010-11-11T10:58:00.000-08:00

I get asked that a lot recently so I thought I'll share it with the world. No Microsoft didn't remove the Call Stack window from Visual Studio 2008. they just moved it to another menu entry.

You can find it under Debug->Windows->Call Stuck

Keep coding...

Migrating databases from SQL 7/2000 to 2005/2008 version

2010-09-13T08:43:00.000-07:00

When you migrate a database from a SQL server 7 or 2000 to a SQL server 2005 & Up it's important to check that the database's Auto_Close flag is set to "FALSE" OR "OFF" (binary 0).

In order to find out which databases have it set to "TRUE" or "ON" (Binary 1) run this script:

SET NOCOUNT ON

SELECT [name] AS DatabaseName
, CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) AS RecoveryType
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink
FROM master.dbo.sysdatabases
Order By DatabaseName

Then in order to set a flag to "FALSE" / "OFF" run the following script:
ALTER DATABASE [DB_NAME_HERE] SET AUTO_CLOSE OFF WITH NO_WAIT

Here is Microsoft Best practice recommendation:
http://technet.microsoft.com/en-us/library/bb402929.aspx

Code Snippets in Visual Studio 2008/2010

2010-06-15T15:01:00.000-07:00

Another cool feature of Microsoft Visual Studio 2008/2010. Code snippets are pieces of codes that are pre-set in snippets files and has a shortcut associated with it. This feature can save you a lot of time coding.
we all build our "for" statements and "if" statements and all kind of code parts that we code repeatedly almost everyday.
VS comes with many snippets preloaded but you can add your own if you have a specific code snippets specific to you that you want to add to the library.

Here is how it looks when you start typing the shortcuts. The code snippets has their own icon (Click the Image to enlarge)::

Once you choose your snippet than click the "tab" button twice and the code will populate:

In order to see all the available snippets or add your own go to "tools" -> "Code Snippets Manager"

And you will see this screen (Click the Image to enlarge):

To create a code snippet of your own you will need to build the file in a n XML format and supply it with all the attributes (shortcut, code etc.).

Here is how the above "for" statement snippet file looks like (Click the Image to enlarge)::

ToDo List - an .NET 3.5 MVC2 project utilizing Entity framework & MS-SQL

2010-06-14T13:38:00.000-07:00

Well, Since my web hosting does not support MVC2 framework at the moment I can't give you a full demonstration but I Can show you some of the neat, cool stuff Microsoft added to the .NET 3.5 framework.

Motivation
The MVC is Microsoft's implementation of the MVC design pattern (Model–View–Controller) (Click the link in order to learn more about the design pattern). With the increasing importance of a strong web presence for today's business world and the constant change and evolution of the search engines (Google, Bing, Yahoo, etc.) there are new development requirements that didn't exist before.
The business' SEO (Search Engine Optimization) activities added new technical development requirement. If until now nobody cared how our URL addresses looked like or what keywords they contained, it has changes dramatically in the last year or so. Search engines these days look at the folder names, the page name and the suffixes as input for the ranking algorithms.
I do not wish to open a SEO debate here (I might add a post about it to my Blog soon), I want to show you a new EASY way to make your web application's URLs a lot more SE friendly and user friendly at the same time.

Technology
I used Visual Studio 2008 over .NET framework 3.5 and MS-SQL Server 2008
The new features introduced in this project are: ASP.NET MVC2.0 & ADO.NET Entity Framework . Combined together these new features of .NET 3.5 are very powerful and make the development of new, SEO friendly websites really simple.

The Main Idea
We have three main components:
•Model - the Data layer of our projects. Provide access to the data.
•Control - the Business layer, where we do all the business logic of our project and function as an intermediate between the Model (Data) layer and the View(UI) layer.
•View - a presentation layer, the UI (User Interface) layer. May contain some logic but only UI related logic (validations etc.).

Want to read more? Click Here

Super cool UNdocumented SQL procedure

2010-06-08T05:51:00.000-07:00

xp_dirtree is an UNdocumented SQL procedure. you can use it to get a list of folders in a directory, including sub-folder to any depth!!!

EXEC master..xp_dirtree '[directory_path]', [depth]

for example:
EXEC master..xp_dirtree 'c:\Installs', 1
will get you a list of folders in the directory c:\Installs.

another example
EXEC master..xp_dirtree 'c:\Installs', 1
will get you a list of folders in the directory c:\Installs AND all the folders under these folders (2 levels down)

small query that can make your life easier sometimes.

Search & Replace values in MS-SQL 2005/20088 text fields

2010-06-01T12:06:00.000-07:00

Starting from MS-SQL 2005 there is a new SQL function called "REPLACE" and you can use it to replace values in text fields (varchar/nvarchar/nchar...).

just put the right table names and the right column names in the following code:

update TableTable
set ArticleContent = (
SELECT REPLACE(TargetColumn, 'old value', 'new value')
FROM TargetTable Table_In
where Table_Outside.ArticleId = Table_In.ArticleId
)
from TargetTable Table_Outside

How to generate WSDL files

2010-05-28T15:42:00.000-07:00

So you created a new web service in .NET and want to start integration with the application that is going to use your web service.

Wait, there is a problem. The other application is written in JAVA and they ask you to get them the WSDL file so they can build a reference.

Here is how you get the *.wsdl file:
1) run CMD
2) go to C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin
********* can be other VS version
3) run: disco.exe [WS URL]

and there you have it.

How to launch the MS download manager

2010-05-26T07:12:00.000-07:00

Have you ever stated downloading from Microsoft's website using the MS download manager and it closed in the middle for some reason?

Here is what you can do to get it started again (open the UI):

1) Open a command window

2) Change Directory to %SystemRoot%\Downloaded Program Files\

3) Type TransferMgr.exe and press Enter

That's it. Simple.

New website - SQL Basic Training

2010-05-25T16:37:00.000-07:00

I recently uploaded a new website for SQL Basics training: www.SQLBasics.com

I tried to cover the basics just enough to get someone started.
I'll appreciate any comments, questions or requests you might have.

SQL tempdb and constraint names

2010-05-24T14:52:00.001-07:00

A weird thing I found out about MS-SQL's tempdb a few days ago was that the server does not hash the constraint names.

I don't know if you know the way tempdb is working in MS-SQL but the basic concept is that when a process is creating a temp table the tempdb is hashing the name to create a unique instance for the running session.

so if you write a code that looks like this:
create table #TestTabe (id int, descript nvarchar(50))

in runtime you will see in the tempdb something that looks like this:
#TestTable________________123213E2B

the main reason is that you can have multiple sessions running at the same time.

Let's add a constraint to the equation:

CREATE TABLE #TestTable(
id int, descript nvarchar(50),
CONSTRAINT [PK_#TestTable] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)

in runtime you will see something like this:

The normal way tempdb is operating is when the session ends (i.e. stored procedure finished it's execution) or when the user manually drop the table it's deleted from the tempdb with all of it's constraints.

if your session fails for some reason and you didn't catch it the tempdb is not cleaned and the table (with the hashed name) and it's constraints are left in the tempdb.

This is not a big deal in general cause the table name is hashed for each session so the next session will have no problem creating it's own instance of the table.

The weird stuff I found out and kind'a puzzled me was that MSSQL does not hash the constraint name! so the next session that tried to create the table FAILD with the error of "Constraint already exists in the db"!

So until the next cleanup of the tempdb (restart of the sql server should do the job) the process is stuck and will fail every time!

The solution?

Wrap your code with try & catch so you can drop all the temp tables in the case of a failure! The code I saw (not my code of course :-) )was not wrapped and this created an issue.

That's it for today.

Till the next time, keep coding...

Joseph Gozlan

Who am I?

2010-05-24T14:38:00.000-07:00

I guess what you are thinking to yourself is "who is this guy?" and "why should I bother reading his Blog?"

Well, I can answer the first question easily, my name is Joseph Gozlan and I'm an Information Systems Engineer from Plano, TX, USA.

I work for Retalix USA (RTLX). I've been with Retalix for the past 6 years doing all kind of jobs and filling a few positions. I've started as a .NET developer, been a .NET Team Leader and a technical expert.

I was Born and raised in Israel and relocated to Plano Texas in 2007.

Why should you read my Blog?
I don't have a really good answer for that there are many great Bloggers out there with great writing skills. I can only say what kind of info I plan on posting here and you will be the judge if you think this can interest you or not. Every once in a while I find out small technical things that I find interesting or helpful (see next entry about SQL's tempdb). So this is the kind of stuff I'll share here. it can be from the .NET development world, SQL server tricks or even some motivation related items (I follow Daniel Pink's great Blog)

That's it. If you have any questions, shoot.

First Blog entry

2010-05-24T14:34:00.000-07:00

So I figured it's about time I joined the Bloggers community.
for a while now I've been emailing people with interesting technological stuff and I figured It'll be easier to maintain a Blog than to send emails. Plus, let's face it, Blog helps with the SEO to my website: www.JosephGozlan.com