Saturday, January 21, 2012

SSRS 2008R2: How to configure a domain user to browse Reports?

I have some reports which are hosted on a SQL Server 2008R2 machine. I can browse my reports at any time. This is because I have the administrator privilege on this machine and all my authentication are in place.

Recently I had to give browse permisions on reports to some domain users. The task is quite easy if you follow the right steps to do it.

First I shared the URL of the reports which was in the format of http://servername/Reports/ with the domain users. When the domain users tried to navigate the reports they got the error that

The permissions granted to user 'domain\username' are insufficient for performing this operation.

In order to solve this issue, I performed the following steps which resovle the issue and the domain users were able to browse the reports on their machine with their own domain credentials.

Step 1: The first thing I checked was Reporting Services Configuration Manager settings. I found that the Service Account was set to Local System. I changed it to Network Service.

Step 2: The second step I did was to check the Local groups . I right click on My Computers->Manage. In the Computer Management window I selected Local Users and Groups and under Groups I find the group I was looking after i.e. SQLServerReportServerUsers$machineName$MSRS10_50.MSSQLSERVER. I double click it and added the domain users in the SQL Server Report Server group.

Step 3: I open the SQL Server Report servcies path(http://servername/reports/) in browser and click on the report folders. I selected the Folder Settings.

From the left frame I selected the Security and clicked on New Role Asignment

I assigned the domain\user the Browser role

…and clicked on OK.

I did the same steps to grant permission on my Data Sources folder.

I thought I am done. I asked the domain users to view the reports now. They did and they got a new error message:

…I realized that I forget to add the domain users in the SQL Server Logins groups as the reports I have are using SQL Server databases and my SQL Server is setup with windows Authentication. I quickly jump to the SSMS and created the logins and grant appropriate permissions to the domain users.

This worked and all my domain users were able to see the reports with their own windows credentials.

Labels: , , ,

SQLServer.GETUTCDATE() function

Often while working with the people in different time zone, we stuck and stop to calculate what the current time is in their time zone and what the current time it is in my time zone. There are a lot of fine utilities available on the internet to use. I thought of why not a dashboard kind of report which can tell me what the current time is in different time zone. So I fired an SSMS and write down this script which can tell me what is the current time in different time zone.

I used the GETUTCDATE() function which has been introduced with SQL Server 2005 onwards; This function returns the UTC date and time. UTC Time are also referred as Co-ordinated Universal Time or Greenwich Mean Time. This function internally calculate the local date and time and time zone of the SQL Server box and convert that to UTC date and time.

For the different time zone(like EST, PST, AST etc.), I just had to know that how far they are from the UTC. Like ATS (Atlantic Time) is 4 hours before UTC Time so I have to calculate UTC Date/Time and deduct 4 hours from it to get the current ATS Time.

--UTC Time
SELECT 'UTC Time' as 'TimeZone','UTC' as 'TimeAbb' ,CONVERT(varchar(20),GETUTCDATE(),113) as 'Current Time'
UNION ALL

--IST Time i.e. India Time=UTC+5:30
SELECT 'India Time' as 'TimeZone','IST' as 'TimeAbb',CONVERT(varchar(20),DATEADD(MI,30,DATEADD(hh,5,GETUTCDATE())),113) as 'Current Time'
UNION ALL

-- Atlantic Time = UTC-4
SELECT 'Atlantic Time' as 'TimeZone','ATS' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-4,GETUTCDATE()),113) as 'Current Time'
UNION ALL

-- Eastern Time = UTC-5
SELECT 'Eastern Time' as 'TimeZone','ETS' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-5,GETUTCDATE()),113) as 'Current Time'
UNION ALL

-- Mountain Time = UTC-6
SELECT 'Mountain Time' as 'TimeZone','MTS' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-6,GETUTCDATE()),113) as 'Current Time'
UNION ALL

-- Pacific Time = UTC-8
SELECT 'Pacific Time' as 'TimeZone','PST' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-8,GETUTCDATE()),113) as 'Current Time'
UNION ALL

-- Alaska Time = UTC-9
SELECT 'Alaska Time' as 'TimeZone','AKST' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-9,GETUTCDATE()),113) as 'Current Time'
UNION ALL

-- Hawai Time = UTC-10
SELECT 'Hawai Time' as 'TimeZone','HST' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-10,GETUTCDATE()),113) as 'Current Time'

I put this script in a SSRS report and this is now seating at my desktop and every time I need to see the current date time of different time zone; I am just one click away.

So I can enjoy my knowledge and have my own little world clock…:)

What do you and Super Computers have in Common?

Recently I was flipping through one of the leading magazine and came to know that the world’s fastest Super computer has been arrived in China. This made me to think how the technology has evolved in last few decades that building a faster Super Computer has become benchmark for success. Last year when the list of word Top 500 Super Computers was published it was “K Computer” of Japan which took the #1 spot.

The new Super Computer build by China is called “Tianhe–1”. The word “Tianhe” means “Milky Way”. This is 1.4 times faster than the current title holder - “K Computer”.

I was amazed to know the facts about Super Computers. All the top 5 Super computers use Linux as their Operating System. Wow! (… and we still debate L(inux) Vs M(icrosoft) Vs XYZ).

What Processors these Super Computers use?

A quick look at the processors used by these super computers reveals that out of top 100 Super Computer more than 76 percent uses Intel processor. Wow! (…Thank god, I have something common with Super Computer)

If you want to explore more than the list of top 500 Super Computers are located here. http://top500.org/list/2011/06/100
Labels: ,

SSIS: How to read Excel Meta Data?

A lot of our learning comes from different forums we visits on daily or weekly basis. This post is also inspired by one of the popular forum which I recently visited and found a very interesting question on Ms-Excel and SSIS. The reader had asked how he can check in the Excel file, a particular column exist or not before processing the file with SSIS.

This made me to think what solution we can offer to this question. I did some finding with the Excel and BIDS and come out with one of the solution. I am sure there might be a better solution than this but I thought to share it get some more understanding. I would request you to leave some comments if you have a better solution or find this fine. So here goes my solution.

I have an Excel file which has data like this:

I want to check whether the Population column exists or not in the excel file. I created an Excel Connection Manager in my SSIS package which points to this excel file.

I added a Script Task on the control flow tab. I am using the System.Data.OleDb namespace to read the excel schema. I added following code snippet in my script task:

public void Main()
{
OleDbConnection myConn;
DataTable DT;
int blColExist;
blColExist = 0;
myConn = new OleDbConnection(Dts.Connections["ExcelManager"].ConnectionString.ToString());
myConn.Open();
DT = myConn.GetSchema("Columns");

/******************************
* Check the Column name
******************************/
foreach (DataRow DR in DT.Rows)
{
if (DR["Column_Name"].ToString() == "Population")
{
blColExist = 1;
}
}

if (blColExist.Equals(1))
{
System.Windows.Forms.MessageBox.Show("Population Column exist");
}
else
{
System.Windows.Forms.MessageBox.Show("Population Column DOES NOT exist");
}
}

As you see in the code I am reading the excel schema details about Columns. The column_name column return the name of each column in the excel file. I put an IF statement to check whether the “Population” column exist or not. After running the SSIS package I was greeted was this message box.

Wow!, the objective is met and I can check the columns existence. But I am know sure this is the only way. Let me ask it to the reader of this Post.

If you want to explore more with the Source code of this post; please visit Download Zone.

Related Article:

SQL Server: User Defined Global Variables Solutions

Recently I received a very good question from one of the developer in my team. She wanted to create user defined variable in a stored procedure and use the same global variable in a user define function.

I know we can create Global variables in DTS packages (SQL Server 2000 or prior) and in SSIS (SQL Server 2005 onwards) and in SSRS (SQL 2012 onwards). I know we can create Global Temporary tables, global temporary stored procedures but I have never heard about the user defined global variables.

I checked the BOL and other stuffs and I was assure to know this

"SQL Server does not allow users to create user defined global variables OR update the value of system defined global variable."

So what is the solution if I have to create a user defined global variable, the solution which I come across during my finding is pretty simple. The solution involves following steps

Step 1: Create a table with two columns (variableName and varaiableValue) .

Create table tblGloablVariable
(
variableName varchar(100),
varaiableValue varchar(100)
)

This table will hold all the Global variables we will create in our database.

Step 2: Create a stored procedure which will create and initialize the user defined global variables in our database.

/*********************************************
Procedure to set the Global Variable
**********************************************/
Create procedure setGloablVariable
@param1 varchar(100),
@param1Value varchar(100) As
BEGIN
IF EXISTS(SELECT * from HumanResource.dbo.tblGloablVariable Where variableName=@param1)
BEGIN
UPDATE HumanResource.dbo.tblGloablVariable SET varaiableValue=@param1Value where variableName=@param1
RETURN -1
END
ELSE
BEGIN
INSERT INTO HumanResource.dbo.tblGloablVariable VALUES(@param1,@param1Value)
RETURN 1
END
END

Step 3: Create a stored procedure which will return the user defined global variables values

/*****************************************
Procedure to get the Global Variable
******************************************/
Create procedure GetGloablVariable
@param1 varchar(100),
@param1Value varchar(100) OUTPUT As
BEGIN
IF EXISTS(SELECT * from HumanResource.dbo.tblGloablVariable Where variableName=@param1)
BEGIN
SELECT @param1Value=varaiableValue from HumanResource.dbo.tblGloablVariable Where variableName=@param1
RETURN 1
END
ELSE
RETURN -1
END

Step 4: We can define as many user defined global variables as we need in the system. I defined and initialized following four variables.

-- Define the Global Variable
EXEC setGloablVariable "empName","Vikash Kumar Singh"
EXEC setGloablVariable "Age","29"
EXEC setGloablVariable "OrderNo","9876229"
EXEC setGloablVariable "ItemName","Pencil"

Step 4: We can return the values of the user defines global variables as and when needed.

Hmm, this solution is not new but a very effective solution if you are in a situation to use user defined Global variables. There are many concerns with this approach – the first thing is that you need to have proper rights and roles on the database which will host your tables, stored procedures. The solution will overwrite your changes if some other user is also trying to initialize the same variable. Although, this solution can be extended or customized to override this behavior. /

Let me know what you think or you can point to a better solution to create a used defined global variable.

Labels: ,

Tuesday, January 3, 2012

SSIS: How to pull Tweets from Twitter?

Today I learn a very simple way to pull tweets from Twitter using SSIS. It is very easy to read RSS feed of a twitter user using following mechanism. Simply navigate to the following web address.

http://api.twitter.com/1/statuses/user_timeline.rss?screen_name=?

The only thing we have to do is to place the twitter user id to the screen_name parameter. So, I put my twitter id (vksingh24) and go the following in my screen.

http://api.twitter.com/1/statuses/user_timeline.rss?screen_name=vksingh24

Now my next goal is to pull this information from Twitter periodically using SSIS and store this to a table. The tweets data stored will be used by different applications (windows, web and mobile) of mine.

I created an SSIS package and added a Data Flow Task in Control Flow tab. I have added two components Script Component and Row Sampling in the Data Flow tab.

I have used Script Component as a Source in my SSIS package. I have added two output columns (TwitterFeed and TwitDate) with the data type as string(DT_STR).

The next step is to write some piece of code to pull the Twitter RSS Feed. Before we go ahead and do this few points we have to understand. The .NET framework version 3.5 provides us namespace System.ServiceModel.Syndication to read the RSS feed. My code snippet is using .NET Framework 3.5 to read the above shown RSS Feed. Here is my code snippet in Script component.

After setting up my script component, I added Row Sampling component. I actually have no use of this component, I just wanted to pass the data from Script Component to Row Sampling. I have added a Data Viewer between Script Component and Row Sampling. After setting up all this I run my SSIS package and Data Viewer shows me all the Tweets and its published date.

Now, my proof of concept was ready and I took this approach to design my SSIS package which runs every hour and pull the tweets of the Twitter users.

I hope you like this post.

If you want to explore with this SSIS package, please visit the Download Zone to get hands on the source code

Related Article:
Labels: , , ,

Mr. vLookup() you are awesome!!!

Ms-Excel world is amazing in itself. I believe we spend a lot of our life's precious time dealing with computers, laptops, I-Pad and various other gazettes. We spend a lot of time dealing with ms-office or similar products. Ms-Excel world in the form of rows and columns offers, so many functionalities that sometime we realized that there is a lot to learn about these basic software’s.

Ms-Excel in its kitty has a rich library of functions and formulas which helps in our daily routines. Recently I came to know about one of superman function of Ms-Excel “vLookup”.

vLookup() stands for Vertical Lookup. This function searches for values vertically that is in a column row after row.

What does the vLookup() function do?

vLookup() function searches for a value in a RANGE/list/column(s) and returns the corresponding value from the right side columns. That is it gives you the facility to search for a string or value in a list and you can specify what values from the right side columns you want to return. This will be clear from an example. Let us say we have data like this in our excel sheet.

Using vLookup() we can search of any employee about his Age, Dept or Salary. Our RANGE/list in this case will be all columns (Empname, age, dept and salary) and all rows. We can specify any employeeName in the search criteria of vLookup() and return any columns(age, dept or salary) from the right side . The vLookup() will search in the very first column i.e. in EmpName. For example if I have to return salary of “Vijay” the vLookup() function will take following parameter:

=VLOOKUP("Vijay",B3:E6,4)

Wild card searches with vLookup

One of the interesting fact about vLookup() function is that it gives the flexibility to search with wild card character. If you are not sure about the search value, you can use the wild card character(*,?) to search with. That is if you want to search in above shown data for salary of employee, starting with “S” character you can write the vLookup formula like this:

=VLOOKUP("S*",B3:E6,4,FALSE)

You can apply the “*” (represent any character) and “?” (represent one character) wild card character to search for values in Range/colum(s). So if you want to search for employee salary having only three character in his name, vLookup() will work for you as following:

=VLOOKUP("???",B2:E6,4,FALSE)

This will return the salary of “Jay”, whose name has only 3 characters.

Limitation of vLookup function.

One of the limitation of vLookup() function is that it cannot search for columns in it right side. That is it always returns the values from his left side of column.

Although, this limitation can be overlooked by placing the search column as very first column in our list or range.

Once you know and be familiar with the use of vLookup(), you will be amazed to see how this superman function when club with the other Ms-Excel function works like a charm.

Labels: ,
Subscribe to: Posts (Atom)

Popular Posts

Real Time Web Analytics