Business Intelligence ???

Simple string parsing using TSql using the SPLIT and CharIndex functions

2008-07-14T13:41:00.002-05:00

Simple string parsing using TSql using the SPLIT and CharIndex functions
Here I need to extract the data AFTER the $ in the input.

--Split out instance Name
SELECT SUBSTRING('MSSQL$SQLDBT01',CHARINDEX('$','MSSQL$SQLDBT01')+1,len('MSSQL$SQLDBT01'))

Here is the output
------------
SQLDBT01

Simple way to add Clustered Primary Keys to multiple tables

2008-07-14T12:33:00.003-05:00

I used this to create clustered primary keys for each table that i cared about within a database. I joined the
SELECT i.TABLE_NAME--, i.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLES i
with a table that stored all of the objects that I cared about and created a list of all the obects.

So for example
SELECT i.TABLE_NAME--, i.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLES i
INNER JOIN
(select t.Trace_Table_Name + '_id' as Trace_Table_Name
from Trace_Directory t
join sysobjects o on t.Trace_Table_Name = o.name) t
ON t.Trace_Table_Name =i.table_name
ORDER BY i.TABLE_NAME

Complete script

DECLARE cPK CURSOR FOR
SELECT i.TABLE_NAME--, i.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLES i
--Remember to remove this for all tables or add your own logic here
INNER JOIN
(select t.Trace_Table_Name + '_id' as Trace_Table_Name
from Trace_Directory t
join sysobjects o on t.Trace_Table_Name = o.name) t
ON t.Trace_Table_Name =i.table_name
ORDER BY i.TABLE_NAME
DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
--Loop through all tables
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable--, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT PK_' + @PkTable + '_1 PRIMARY KEY CLUSTERED ( id ASC , rowNumber ASC) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
SET @PKSQL= @PKSQL + char(10) + char(13)
SET @PKSQL= @PKSQL + 'GO '
-- Print the primary key statement
PRINT @PKSQL
FETCH NEXT FROM cPK INTO @PkTable--, @PkName
END
CLOSE cPK
DEALLOCATE cPK

Here is the output

ALTER TABLE Trace_johnsqlp66sqlprod02_20080707_0001_id ADD CONSTRAINT PK_Trace_johnsqlp66sqlprod02_20080707_0001_id_1 PRIMARY KEY CLUSTERED ( id ASC , rowNumber ASC) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Very Easy Explination of Setting up a new storage SATA drive for windows XP

2007-11-28T10:08:00.000-05:00

This one did save me some time. I recently added another SATA drive to my personal, non-gaming machine to save video and picture files. After I installed the drive I was frustrated to find that I could see it on post but within Windows I could not see the drive associated with a drive letter. Stupid me I never thought about the fact that it was not partitioned or formatted. This really helped quickly.
http://www.pcstats.com/articleview.cfm?articleid=1778&page=9

SMO - Database Status Property

2007-06-07T15:05:00.000-05:00

Have a very strange error - or what seems to be an error when running some SMO code...

When doing a simple loop though all of the databases on a server I noticed something strange with one of the properties; the database.status property. I was originally running my code against a 2000 server and there is one db on that server that is set to Offiline. When looping though the dbs on that server and looking at the status property the offline db reported just like the rest - online. When I switched to a 2005 server and set a db to offline I ran my bit of code and noticed that the status now reported 544 as a code for the offline db, this in turn reports to NOTHING as a code from the enum
Debug.WriteLine([Enum].GetName(GetType(DatabaseStatus), db.Status)) again this return an empty value.
Any ideas on this one?

Datawarehouse or Business Intelligence

2007-05-23T10:10:00.001-05:00

I know this is a post from last year but it is worth a look.

http://www.sqlservercentral.com/columnists/vRainardi/businessintelligenceordatawarehouse.asp

Ms Build - A Start

2007-05-16T15:20:00.000-05:00

I have been getting some questions about MSBuild so here is a start.
I hope to look into creating a wrapper app for our MSBuild/DBPro projects very soon.
http://msdn2.microsoft.com/en-us/library/0k6kkbsd.aspx

SSIS - OLE DB Source Component - Stored Procedure Error

2007-05-16T08:56:00.000-05:00

Here is something we recently ran into when using a stored procedure inside OLE DB Source Component.

A change was made to a stored procedure that was originally working and providing SSIS OLE DB Source Component with all of the mappings and meta data. After a change to that proc, the mappings could no longer be validated and the meta data was not available. when configuring the component were suddenly not seen. What happened...?
Apparently the proc was changed in such a way that the original use of a table variable to store some temp results was removed and replaced with a #temp table. This stopped the transmission or access to the metadata from the proc. After some searching Dave M. was able to find this post http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

I added the SET FMTONLY OFF and tried to validate the SQL, SSIS did not like this and it threw an error. The proc was being called with 2 parameters and I could not validate with the SET FMTONLY OFF hint and the two parameters present. So what did I do...???
I punted...
Actually the proc was changed back to using the table variable and all is working again - after a host of other issues.

I plan on working on this again this week and will test out various methods to find a workaround that suits our needs. Stay tuned.

Like a termite in a termite mound

2007-05-11T07:04:00.000-05:00

They have just started a program here at work that forces us to change our network log on password. Now I know what your thinking, they are just doing this now???? Scott it is 2007!! I know it is a big company and change happens slow which supports my discussion. I spent 10 min yesterday and signed into the program to create my own new password; a password of my own choosing - with the mandatory mixture of numbers and letters. My point is from the first time I had to re-log on to my workstation to the most recent time, I just cannot break out of the habit of typing in my old password and realizing, the moment I hit the enter key, I have become a termite in the termite mound. Merely a worker siding creativity for compliance and redundancy; I am now scared! I always took great pride in being creative, a go-getter, passionate, driven and now I see I am loosing a lot of that edge that got me to where I am now. I need something refreshing - maybe a vacation in Vegas!

Not even a comment...

2007-03-09T08:19:00.000-05:00

Wow this is killing me... Not a single reply or comment. Looks like I need to up the content.

SQL Server 2000 Constraints and scripting in Enterprise Manager

2007-02-07T16:15:00.000-05:00

Recent post at SSC that I found the need to reply to - it eventualy lead to talk about scripting out of enterprise manager. I wanted to talk about the pitfall of EM chaning the constraint signature when scripting this out.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=342753&post=true

Southern New England SQL Server User Group

2007-02-06T15:32:00.000-05:00

I would like to invite everyone in the Southern New England area to become a member of SNESSUG (Southern New England SQL Server User Group). Myself and fellow colleagues have created this group and we are now a recognized PASS chapter http://www.sqlpass.org/.

We are new so bear with us as we work out the kinks. We will be creating a web site listed under the sqlpass.org site listed above. Membership is free and the meetings will be held on the SECOND Wednesday of every month. If you are interested in more information contact me at finalist2@comcast.net and I will be happy to answer any questions you may have.

TSQL Training - Well worth my time

2007-02-06T15:12:00.000-05:00

Since my last posting I had the pleasure of attending a week long course on Advanced TSQL Programming. This course was taught by Itzik Ben-Gan founder of Solid Quality Learning (S.Q.L). Anyone who has had the opportunity to attend a course taught by Itzik will certianly confirm my feelings that Itzik is a fantastic teacher and has an incredible command of the subject matter. I also urge anyone with a SQL background to pay attention to Itzik's postings and comments in the world of all things SQL Server; he is an amazing individual and a major contibuter to the SQL Server community. Itzik, thank you for an amazing course and keep up the great work, it was an absolute pleasure to learn from one of the best in the field!

Here is a link to Itzik's site : http://www.sql.co.il/books/insidetsql2005/

SOA and I will post more on this soon

2007-01-22T09:08:00.000-05:00

Interesting link to some SOA and data talk. I think the author hit this right on the head when he mentioned that data will never be as cool as application code and methods. This is VERY evident at my place of employment as much, if not all, of the focus is on creating the services and the DBA will get the data that we need. We went from tightly coupled systems to what is becoming tons of small services all over the place that would seem to be difficult to remember exactly what services were created for an application and where those services reside. When I get more time I will elaborate on this more.
So here is the link:
http://blogs.msdn.com/rogerwolterblog/archive/2007/01/19/soa-and-data.aspx

The First Post

2007-01-12T13:54:00.001-05:00

Well this is the first official post in my wonderful world of the blog.
As you can see from the title (as sarcastic as it may seem) it will have some ties to Business Intelligence and many ties to technology;
VB.Net
Object Orientated Programming models and patterns
All things Sql Server
and fun things of course

I should introduce myself here as well. I am a Sql Server database developer for a major insurance company and have been a developer for about 12 years now.

Now the personal stuff - My family consists of my wife Brenda and my two girls; Madison and Samantha. At the time of writing this Sammie is 6 weeks old and the lack of sleep is a reality
and may cause me to post some interesting items to say the very least.