Wednesday, February 29, 2012

Ways to track your blog visitors

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 guys 2 and someone who's totally in the middle of the ocean !!


Really it's helpful to know where you visitors are coming from and which posts they're looking for, and to some people who place ads to generate revenue to their blog, it's a starting point to tweak their posts and their blog to attract more visitors.

Hope that helps someone and please contact me if you're that man in the boat!



Tuesday, February 28, 2012

What is SSMS (SQL Server Management Studio)

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


http://support.verio.com/documents/images/ves30071/ves30071_01.gif


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/en-us/library/ms174173.aspx


Hope that helps someone...

Thursday, February 23, 2012

SSIS iterate to update a blob column

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 errors.

And constructed my package as follow:

1-Foreach Loop Container to iterate over the files.
2-Execute SQL Task to run the update statement.

The steps:

1-will configure the Foreach Loop Container to iterate over the folder and get me the file names into the STRING variable "FileNameLocation".



2-Configure the Execute SQL Task to use my database connection, and the Query variable to update the table.


3- Add that expression* on the ID variable, to shred the ID out of the file name.
Reverse(Substring(Reverse(right(@[User::StrFileNameLocation],  FINDSTRING( REVERSE( @[User::StrFileNameLocation] ), "-",1) -1 ) ) ,5,20))
4-Add that expression* on the StrUpdate variable, which will be used as our query.
"Update Files set FilePath =( SELECT * FROM OPENROWSET (BULK '"+ @[User::StrFileNameLocation] +"',  SINGLE_BLOB) FilePath) where ID =" + @[User::IntID]
To add expressions to the variables, check that post http://asqlb.blogspot.com/2012/01/ssis-expressions-in-variablesdynamic.html
5-Run the package and check your table...








Wednesday, February 22, 2012

You must use the Role Management Tool to install or configure Microsoft .NET Framework 3.5 SP1

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...



Monday, February 13, 2012

SSIS send e-mail from a SQL table

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 to the TO LINE from the string variable.

Hope that helps someone..

Sunday, February 12, 2012

Get a folder's file list to SQL table using VB.net

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
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim SQLStr As String    'SQL string to hold the root query
        Dim ConnString As String
        'SQL server connection to "Localhost" and my Database called "Admin"
        ConnString = "Data Source=localhost;Initial Catalog=Admin;Integrated Security=SSPI;"
        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()
        SQLConn.ConnectionString = ConnString
        SQLConn.Open() 'open connection
        SQLCmd.Connection = SQLConn
        'write root file list
        Dim di As New IO.DirectoryInfo("c:\")
        Dim fi As IO.FileInfo() = di.GetFiles()
        Dim f As IO.FileInfo
        'list the names of all files in the specified directory
        For Each f In fi
            SQLStr = "INSERT into FileList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + di.ToString + "','" + f.ToString.Trim + "')"
            SQLCmd.CommandText = SQLStr
            SQLCmd.ExecuteNonQuery()
        Next
        SQLConn.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class


The results...

 Hope that helps someone...

Saturday, February 11, 2012

Prevent a certain text string from being entered in a SQL Server database

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 Tbl


FROM
 syscolumns C
 INNER JOIN systypes T
  ON C.xtype = T.xtype
 INNER JOIN sysobjects O
  ON C.ID = O.ID
 INNER JOIN sysusers u
  ON O.uid = u.uid

WHERE
 T.Name IN ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
 AND O.xtype IN ('U')
 AND objectProperty(O.ID, 'ismsshipped') = 0

ORDER BY
 3
  , 1


OPEN curcolumns

SET XACT_ABORT ON

BEGIN TRAN

FETCH curcolumns INTO @CName, @TName, @OName

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @Sql = 'ALTER TABLE ' + @OName + 
 ' ADD CONSTRAINT chk_' + @CName +' Check ( '+  @CName +' Not like ''%sauerkraut%'' )'


 --EXEC (@Sql) -- change this to print if you need only the script, not the action
PRINT @Sql

 FETCH curcolumns INTO @CName, @TName, @OName
END

CLOSE curcolumns

DEALLOCATE curcolumns

COMMIT TRAN
                                            

Wednesday, February 8, 2012

There was a failure to validate setting CTLRUSERS in validaton function ValidateUsers.

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
------------------------------

Tuesday, February 7, 2012

T-Sql Combine date and 6 digits time

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

SQL Agent Account Through T-SQL

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



Saturday, February 4, 2012

Don't send that empty SSRS report.

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 !! to check for new data that you'll report on.

If exists (select * from tableA where Date_Column = getdate() )
  Begin
Exec ...... 
  End
Else
Send yourself an E-mail....


Just be creative...
You can also use sp_send_dbmail  and send yourself an e-mail if it's empty.

Hope that helps..

Wednesday, February 1, 2012

Get file names using Vb.net

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 = ScriptResults.Success
 End Sub
You could pass 0 or 1 and use the 'Precedence constraints' to evaluate your next step.
Hope that helps someone.