Another BI & Programming Blog - Jason Yousef

Properly there's tons of free services that you can monitor your stats and which sites has refereed your visitors and which keywords they're using to find you, I personally don't really check it that much but I track my blog visitors using 3 monitor services, the first one is using the blogspot's native tracking, and it's not that cool but helpful to track visits to each individual post. Recently I signed up with http://www.clustrmaps.com  which gives you a nice map layout of your visitors and it's a free service too, you'll find it in the right hand side of my blog. it looks cool, but can you tell me how these 2 guys are accessing my blog? on a cruise ship or on a boat!! first let me thank him for checking my blog out, but I'm just wondering... My third tracking is using http://webstats.motigo.com  it's also a free service and gives you more statistics than clustermaps and also layout the visits on a map, and I still see the same ...

Today's post will take us back almost 14 years, back to SQL server 7! I often see that question in forums, and some people are confused it with BIDS or don't know what its usage. SSMS stands for SQL Server Management Studio, lets you connect to your database servers to explore ,edit and create data, tables, views, stored procedure and such database objects. where you write your T-SQL. Also allows you to configure your server's properties, do your backups, connect to the SQL agent, and it gets installed as part of the client tools installation, using the SQL server CD or DVD. It was introduced in SQL 2005 and above, prior to 2005 for example in SQL 2000 it used to be called enterprise manager. SQL server 7 Enterprise Manager SQL server 2000 Enterprise Manager SQL server 2008 SSMS   SQL server 2012 SSMS You can read more about it here... http://en.wikipedia.org/wiki/SQL_Server_Management_Studio http://msdn.microsoft.com/...

A recent question in EE about how to update a blob column using SSIS, so I decided to create a blog on it as it's a lot of steps and I'll need to send hi a lot of screen shots. You have file names such as 04-191-388086.jpg & 04-191-388087.jpg and the 388086 & 388087 corresponds to the ID column on that table and you need to update these 2 IDs. Preparations: CREATE TABLE [Files](  [ID] [int] NOT NULL,  [FilePath] [varbinary](max) NULL) Insert test records with no filePath...   INSERT INTO [Files](ID) VALUES (388086), (388087) So I thought about using 3 variables: 1-String to hold the file name and path. 2-Int to hold the ID, An Expression of the FileNameLocation variable. 3-String to hold the update statement, and EXPRESSION using the ID and the FileNameLocation variable (Optional you could use a direct T-SQL in the execute SQL Task) I've added a value to the FileNameLocation or SSIS will yell and gives you error...

While installing SQL server 2012 RC0 on a Windows server 2008 R2 machine You might get an error telling you to install .Net 3.5 SP1. Although I see it installed and did all the windows updates and .NET 4.0 is even installed. So when you go to the download site and try to install it. http://www.microsoft.com/download/en/confirmation.aspx?id=22 You get another error.... It seems that Windows server 2008 R2 comes pre-installed but not configured or enabled. You just need to go to Sever Manager ---> Features--->Add a feature .  and enable it... Or run that command... %windir%\system32\ocsetup.exe NetFx3   to configure it too. Hope that helps someone...

Hi, This is simple but people ask about it all the time...instead of keep writing the steps, I finally decided to blog about it. you have a table that holds some names along with their e-mail addresses. You'll need 2 variables, one of type OBJECT, one of type STRING 1-Add a "Execute SQL Task" Add the connection Add your query that brings the e-mail addresses Get back the FULL RESULT SET  In the result set, use your OBJECT type variable, to hold the result set 2- Add Foreach Loop Container Use Foreach ADO Select your OBJECT variable Rows in the first table In the variables mapping, map your STRING variable. Now the container will iterate over the OBJECT variable and shred the contents into the STRING variable. Now add your SEND MAIL TASK, configure it, then on the expressions, select To LINE and click on the ellipsis, add your STRING variable... Now it'll iterate over the rows and send a mail one by one ...

A Recent challenge about how to get the folder's list of files int oa SQL table for file comparision. I create a table called "FileList" under my test database called "Admin" Use [Admin] GO CREATE TABLE [dbo].[FileList](     [ListID] [int] IDENTITY(1,1) NOT NULL,     [DateTime] [datetime] NOT NULL ,     [Folder] [varchar](100) NOT NULL,     [FileName] [varchar](100) NOT NULL) I'll use the below script in a script task in SSIS. Make sure to change the scripting language to Visual Basic 2008 Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Imports System.Data.SqlClient <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute(False)> _ Partial Public Class ScriptMain     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase   ...

A weired requirement about how to prevent a string value at the database level...at EE I advised to use a cursor to iterate over the tables, grab all the string data types and add a check constraint to that column. Test: CREATE TABLE help (id INT IDENTITY(1,1), Fname VARCHAR(100) )   ALTER TABLE help ADD CONSTRAINT ckname CHECK (Fname not LIKE '%sauerkraut%' ) INSERT help SELECT ('sau') INSERT help SELECT ('I love sauerkraut very much') INSERT help SELECT ('sauerkrautttttt') Cursor Script: DECLARE @Stmt NVARCHAR(4000), @DBName SYSNAME SET @DBName = DB_NAME() DECLARE @CName VARCHAR(255), @TName SYSNAME, @OName SYSNAME, @Sql VARCHAR(8000) DECLARE curcolumns CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR SELECT C.Name AS Colname , T.Name AS DType , QUOTENAME(U.Name) + '.' + QUOTENAME(O.Name) AS ...

You might get that error while installing SQL server 2012, it's when you select a non domain user while in the Distributed Replay Controller step. You need to choose a domain user or no user at all...Hopefully they improve that error MSG to a meaning error in future releases :) TITLE: SQL Server Setup failure. ------------------------------ SQL Server Setup has encountered the following error: There was a failure to validate setting CTLRUSERS in validaton function ValidateUsers. Error code 0x85640004. For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xFB87BFDA%25400x601E39D7 ------------------------------ BUTTONS: OK ------------------------------

A recent challenge about how to combine 2 columns; The date column has 2011-08-24 00:00:00.000 and the Time column has 120622 DECLARE @D datetime, @T INT select @D ='2011-08-24 00:00:00.000 ', @t = 120622 SELECT @D + CAST(DATEADD(SECOND, @T %100 + (60*( @T %10000 / 100)) + 3600*(@T /10000),0) AS time) And that gives you: 2011-08-24 12:06:22.000

I had recently a weired requirement to get the SQL server AGENT Account from multiple servers, so I'm sharing how to get it through T-SQL DECLARE @SrvAccount varchar(100) set @SrvAccount ='' EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT', N'ObjectName', @SrvAccount OUTPUT, N'no_output' SELECT @SrvAccount as SQLAgent_ServiceAccount And the location the registry would be at ...  SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT 

Some of us has a daily or weekly process that puts some data in the tables and has some reports that report on top of these data, part of the daily process in any business it schedule report subscriptions ... Sometime you get empty reports if the data is missing or no data for that day and you need to filter and not to send these reports.... There's are many ways to do it...but the most easiest one is to schedule your report normally, SSRS will create a new SQL agent job with a GUID numbers and later, just edit this job, go to the steps and add your protection code. The simplest code will check if therefore any rows or not, you can also get notified using DBMail if no data, instead of sending empty reports to your boss!!  You can alter this step, or add a step before... Up to you. Well I'm lazy, i'll alter this one and add a simple code to check if the table has rows or not !! You can also use your own query based on a date and time column that you have ...

A recent question about how to check the value of a STRING variable in SSIS 2008 ? If you need to check for file existence on the disk...use that 1-Imports System.IO  2- assuming your var holds a string of full pat ,ex:    C:\file.txt Public Sub Main()         Dim varCollection As Variables = Nothing         Dts.VariableDispenser.LockForRead("User::FilePath")         Dts.VariableDispenser.GetVariables(varCollection)         If File.Exists(varCollection("User::FilePath").Value.ToString()) Then             MessageBox.Show("File found.")         Else             MessageBox.Show("File not found.")         End If   Dts.TaskResult = Scri...