Everytime we create a cube for analysis services Date and Time Dimensions are needed to slice and dice the historical Data. Sql Server Analysis Services uses the Time Intelligence Features to populate the date and time. Its posted in detail in this blog.
However we need to use them in our relational Databases to do some [...]
Jan
20
2010
20
2010
SQL SERVER :Date and Time Dimensions in T-SQL using CTE
Dec
31
2009
31
2009
Happy New Year in TSQL
I did use SQL String manipulations a lot this year. I want to show an example of how to use CHAR.
Open your Management Studio – and paste the following in your Query Window
SELECT CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15) + CHAR(15) + CHAR(15) + CHAR(15) + CHAR(13)
+ CHAR(72) + CHAR(65) + CHAR(80) + CHAR(80)+ CHAR(89) + ‘ ‘ + [...]
Dec
7
2009
7
2009
Creating a simple FrontEnd using Reporting Services and Merge
Problem:
A Business User wants to update/insert/delete the values in a table that sits in the metadata of the Datawarehouse on a regular basis. Based on these values numerous calculations will be done in the warehouse which generates a data driven Report on a weekly basis.
Initially I got the following Solutions
1. Create a frontend app [...]
Nov
15
2009
15
2009
List of Temp Tables in your SQL server
select create_date,modify_date,OBJECT_NAME(object_id) as TableName
from tempdb.sys.objects
where name like ‘%#%’
Nov
7
2009
7
2009
Encryption in SQL SERVER using SSIS
I have been recently working on an Sql Server Encryption Project. It includes getting the data from a source and importing them into the Datawarehouse via the ETL(SSIS in this case) and then Encrypting it. I decided to blog on Encryption to share my experience.
a. When you Encrypt the Data in SSIS make sure you [...]
Oct
13
2009
13
2009
Checking TSQL in SQL Server Reporting Services Datasets
Recently I have been checking all the Dependencies on the Reports in SSRS(Sql server Reporting Services) because of a Major deployment on the ETL. The ETL change was to accomodate the transfer of a few columns from old Table TableA to the new table TableB. Some of the Datasets in the Reports are Stored Procedures [...]
Oct
6
2009
6
2009
DMV Report for Index Fragmentation and Statistics Update
Recently the ETL load times of my Datawarehouse are increasing and so I decided to check the Indexes status on the tables . I found some fragmentation on the indexes and also some Statistics that are not being updated regularly . I searched online and found this blog by Ben Nevarez(SQL Blog).
I decided to check [...]
Sep
23
2009
23
2009
Procedure to export text files
Recently I have read a lot of Blog posts on how to export files in different formats. But I use a custom procedure which exports text files in Pipe Delimited format using BCP.This procedure can be used in other processes for Output and Error Handling.
Here is the Script for the procedure.You can modify the below [...]
Sep
8
2009
8
2009
Problem with SQL Server numeric data : Error converting data type varchar to numeric.
Today I stuck with an interesting problem of converting number data in Varchar fields to numeric datatypes. I have created the following sample data
The data in the table contains Decimal and scientific values in varchar format as shown in the figure below.
Now when I tried to convert the above values to numeric as below it [...]
Jun
22
2009
22
2009
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
The Error Occurs when the input is date and the given day values are not in the range of the expected days for a given month(1-31)
For eg.
You cannot specify the date parameter as Feb 31 2009 since february only has 28 days in a non-Leap year and it throws the above error.
