Another BI & Programming Blog - Jason Yousef

Hi, Today's requirement is to create a month's table, to be used in a SSRS report parameters, to be passed to a stored procedure. the parameters needs to have Month Name, Number, Abbreviation, Start and End of month. Please find the T-sql code below: ; WITH months (MonthNumber) AS ( SELECT 1 UNION ALL SELECT MonthNumber + 1 FROM months WHERE MonthNumber < 12) SELECT MonthNumber, DATENAME( month , DATEADD( month , MonthNumber, -1)) AS [MonthName], UPPER ( LEFT (DATENAME( month , DATEADD( month , MonthNumber, -1)), 3)) AS MonthAbbr, DATEADD( month , DATEDIFF( month , 0, CONVERT ( VARCHAR (2), MonthNumber) + '/1/' + CONVERT ( VARCHAR (4), YEAR (GETDATE()))), 0) AS StartOfMonth, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, CONVERT ( VARCHAR (2), MonthNumber) + '/1/' + CONVERT ( VARCHAR (4), YEAR (GETDATE()))) + 1, 0)) AS EndOfMont...

Today, I've stumbled upon a nice resource of various SQL server date formats, and here I'm sharing it. http://www.sql-server-helper.com/tips/date-formats.aspx hope it helps when converting from one format to another !

It’s T-SQL Tuesday time again, and this time it's about Trick Shots, my trick today is using SSRS to accept user input! Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!! The Trick: 1-I started by creating a new project in VS 2008, “Report Server Project” type. 2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin” CREATE DATABASE [Admin] 3-I created a test table called “Employees” contains only 3 columns. Create TABLE Employees ( ID int IDENTITY (1,1), Name varchar(50), Comments varchar(MAX) ) 4- Inserted 4 test records… Insert Employees VALUES('Jason', 'Lazy Employee') ,('Yousef', NULL) ,('John', 'Anything Goes') ,('Smith', NULL) 5-back to VS, Crea...

Well, this is my first time to participate in the T-SQL Tuesday, and I'm honored to talk about a new SQL server 2012 feature, and I hope I can do it more often.  SQL 2012 is really a major release and rich full of features, but as an ETL developer I'll pick SSIS as my candidate to demo and share my happiness about one new feature. It really saved me headache and made me say 'Thanks Microsoft' when I heard that it's finally here!  it might sounds simple but it was really a big deal in SSIS. The feature is the new and improved Flat File Source , it now can support ragged uneven number of columns!! yes! finally they thought about it..... Before if you had a flat file that has uneven column numbers, you're out of luck!! you need to use a script task, or read it as one line and do some monkey work or even use bulk insert with the annoying format file to skip a column!! Or use a community task such the " Delimited File Source " oh my god! I remember...

70-433 is the Microsoft® SQL Server® 2008 Database Development exam code, basically it is the necessarily basic skills for any database professional.  If you are a database administrator, developer or a business intelligence developer, you will need to master these basic skills to understand queries and how the basic T-SQL language is all about. So you are invited to join the SQL 70-433 Developer track study group, If you're studying for the 70-433 or need to be a SQL developer or even if you need to contribute to our study group. Each week a new topic will be hosted by a community member, a link and a discussion will be opened for that topic. Please download the "Skills being measured by topic Cheat Sheet" LINK We'll be using the MCTS 70-433 "Self-paced Training Kit" book from Microsoft Press, the book could be purchased from LINK Also we'll be using the Adventure Works database for SQL server 2008, throughout the demos and examples, it co...

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

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 

I needed to search for a specific keyword and didn't have the chance to know what's the table's name or even what's the column's name!! Just replace the KEYWORD in the below query to your needs. DECLARE @Schema varchar(3) DECLARE @Table Nvarchar(max) DECLARE @Column varchar(30) DECLARE @Keyword Nvarchar(30) SELECT @Keyword = 'BMAC2120-01' DECLARE Curs CURSOR FAST_FORWARD FOR SELECT  s.name As [Schema], o.Name AS [Table],c.Name AS [Column] FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.type = 'U' and c.collation_name IS NOT null ORDER BY o.Name,c.Name OPEN Curs  FETCH NEXT FROM Curs  INTO @Schema,@Table, @Column WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Table = 'SELECT top 5 [' + @Column + '],''' + @Table + ''' as [Table Name] from ' + +@Schema +'.' + @Table + ' where [' + @Column + '] LIKE ''...

I've a case where I have 2 columns, Date and Time. Date column's datatype is DATETIME Time Column's datatype is INT Example: Date:  2009-04-14 00:00:00.000 Time:  can be 743  or  0834 I need to combine them in one DATETIME column. Select 'DTColumn' = CASE WHEN len(convert(varchar(12),Time)) = 3      THEN cast(CONVERT(char(8),Date, 112) + ' ' +       convert(varchar(10),Substring(convert(varchar(12),Time, 108),1,1) + ':' + right(Time, 2) + ':00'      ,108) AS datetime)      WHEN len(convert(varchar(12),Time)) = 4      THEN  cast(CONVERT(char(8),Date, 112) + ' ' +       convert(varchar(10),Substring(convert(varchar(12),Time),1,2) + ':' + right(Time, 2) + ':00'      ,108)  AS datetime)          End from Table That'...

Pinal Dave has a huge list of qestions for the DBA interview questions. http://blog.sqlauthority.com/2011/07/01/sql-server-interview-questions-and-answers-frequently-asked-questions-complete-downloadable-list-day-0-of-31/

Introduction This is a review of a great utility that I evaluated as a possible solution for replacing the Intellisense in SQL server management studio named dbForge SQL Complete which could be found at http://www.devart.com/dbforge/sql/sqlcomplete/ . I was surfing the SQL Server newsgroups for help with some T-SQL issues and I saw a post with a link to that utility. I went to their website and downloaded a free evaluation for 30 days. I could not believe how simple it was to use and how thoroughly it solved my code writing problems. I am still a fan and hooked and it's one of the tools that I need to install on each machine that I’m working on, wherever I go! The Environment The evaluation was done on various operating systems and various SQL server versions, windows vista 32 BIT with SQL server 2008, windows 7 enterprise 64Bit with SQL server 2008R2 and windows 7 enterprise 64Bit with SQL server 2005. Download The best thing ever that companies could offer a free trial of their p...

Ever wondered when that index was created ! Select  s.name, t.name, t.create_date, t.modify_date,i.name, c.name From sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = t.object_id         inner join sys.columns c on c.object_id = t.object_id and                 ic.column_id = c.column_id Where i.index_id > 0    and i.type in (1, 2) -- clustered & nonclustered only and i.is_primary_key = 0 -- do not include PK indexes and i.is_unique_constraint = 0 -- do not include UQ and i.is_disabled = 0 and i.is_hypothetical = 0 and ic.key_ordinal > 0 Order by ic.key_ordinal well I'm not sure what's the best way, or if there's any way to get this info. but you could get the table's creation date and use that as a reference...

Declare @Date datetime = '03/15/2011' ------- Your Date select dateadd(day,( -datepart(weekday,dateadd(day,(7)-datepart(weekday,@Date),@Date))+((7)-datepart(weekday,@Date)))+(1),@Date) AS [First_Day_Of_The_Week] Select dateadd(day,(7)-datepart(weekday,@Date),@Date) AS [Last_Day_Of_The_Week]

Declare @Date datetime, @Start Datetime, @End Datetime SET @Date = '02/17/2011' ------- Your Date SET @Start = DATEADD(dd, 1, DATEADD(dd, -DATEPART(dd, @Date), @Date)) SET @End = DATEADD(dd, -1, DATEADD(mm, 1, @Start)) SELECT @Start AS [Start Date], @End  AS [End Date]

Before I used to do it through several 'Views' and grant different permissions to each View, but recently I came across this good article by K. Brian Kelley.... How to Setup Row-Level Security for SQL Server

SELECT Number + 1 as [Month Number], DateName(mm,DATEADD(mm,Number,0)) as [Month Name] FROM master..spt_values WHERE Type = 'P' and Number < 12

SELECT CHAR([number]) AS [ALBHABET] FROM master..spt_values WHERE number BETWEEN  65 and 90

Arabic_CI_AS is the test collation, and being used in my work environment. This white paper is helps a lot .. http://technet.microsoft.com/en-us/library/cc295829%28SQL.90%29.aspx Gives you a detailed overview of the Arabic collation.