Sometimes when you have a really complex expression in an SSIS derived column, it is easy to miss something simple and spend too much time trying to figure it. Just when you think you have corrected the expression, another error keeps it from validating. If you are sure you have the logic figured out but the expression is still “red” try this tip. Start a new Derived Column to start re-creating the same expression with which you're having trouble. Make sure you work from left to right and drag e...
Read More
This puzzle may not have been much of a challenge, but it introduced two new MDX concepts. I have to admit, one of my colleagues and I chatted about this puzzle, and I realized that my requirements were a little unrealistic. As a result, I changed the requirements a little. Instead of returning data between the Calendar Years of 2006 and 2008, the data should be filtered by Ship Date Calendar Year. So, here is what I started with: SELECT NON EMPTY ( [Ship Date ].[Calendar Year ].Children,\ [Meas...
At some point or another, you've probably run into the road block that is the lack of an ISNUMERIC() equivalent within the SSIS expression language. While you can't use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn't mean you can't check to see if a field is numeric using the SSIS expression language (If you feel so inclined, you can use a Script Task to check if a field is numeric. Tim Murphy covers that in his blog here ). As ...
It’s too bad there isn’t an IsNumeric Expression that can be used in a derived column to check and see if a string value is numeric in SSIS. In converting a DTS package to SSIS I ran across this script that needed to be converted. Here’s how the old DTS script looked: IF IsNumeric( DTSSource("Col049")) Then DTSDestination("InsDT") = "*" Else DTSDestination("InsDT") = " " End IF First, drag a Script Component into the Data Flow, selecting Transformation as the Type. Click OK and insert into Data ...
Congratulations to Brian McDonald who has triumphed as the winner of this month's Zune contest. Brian achieved more than 700 points on BIDN over the past 30 days and was also promoted to Jedi Knight status because of that :).
How many of you keep falling for this one…You open up Report Manager for a user that you know you have given access to view reports. Only to see this annoying error! It is basically stating that the user BIGMAC has not been given the appropriate permissions to browse to that object. In this case it is the Home directory. Figure 1: No Permissions For BIGMAC So, you go back to report manager as an admin and can see plain as day that BIGMAC is a browser for the folder that you were navigating to. W...
Today I was working on a date column -- create_date. I wanted to show whether the row was created in the morning or in the evening i.e., AM/PM This is what I did to achieve this. SELECT rn_create_date, SUBSTRING(CONVERT(varchar(20), Rn_create_date, 22), 18, 3) AS Expr1 FROM tablename; Here is the output-
In sql server most of the identity columns are oftern used to generate a unique number for each row in a specific table. If we try to insert a value in the normal way, it does not allow us to enter values. If you have to insert values into these columns at some point here is how you can do it. Use the following code. set identity_insert youtablename on Insert into youtablename (UniqueID) values (5647) set identity_insert TableName off
Today I had to calculate the unique number of records in an excel spreadhseet. First I thought I could I achieve this by pivot table. But I could not do a distinct count. Here is the solution I found after some research. I copied the column for which I wanted a distinct count and pasted into a new sheet. Select the above column. choose Data -- Advanced in Excel 2007 and Data-- filter -- Advanced filter in 2003 and earlier versions. The following screen pops up. Set your list range to the column ...
If you have ever searched for “commenting in TSQL” scripts, I am sure that you’ve found hundreds or maybe even thousands of blogs or articles written about them. This blog won’t be much different from others out there and it is a very, very, very basic concept. However, time and time again, I come across stored procedures or scripts that aren’t very well documented. This was especially in past environments that I have worked and this is not meant to be negative in nature, but documenting your sc...
In this quick blog, I want to show you one way of how to find out what indexes you have on a table using SQL Server Management Studio (SSMS). When you have SSMS running and are connected to the database engine, navigate down the database hierarchy to the Tables folder. You should see the tables that you have access to within that particular database. If you expand on a table you should see a subfolder called Indexes. Expand Indexes and there you will find your indexes for that table. For this bl...
Indexes play a huge role in the performance of a query. Without indexes, your very simple query to get the last 100 records from a table could take forever to return. Well, maybe not forever, but you get the point. For example, I have a query and its results of the Execution Plan shown in figure 1. As you can see, it took 1 minute and 36 seconds to complete. Not very acceptable in my eyes! In this quick and dirty blog, I just want to show you an example of what results one might see by creating ...
Blog Title: Checking Users AD Groups Using Windows CMD Depending on your role in the organization, there may be a time when you have to try and determine active directory information for a given user on the network. As a DBA or a consultant, I have found that I’ve had to do this quite often. As such, I thought that I would write a quick blog about it. It’s pretty simple really and once you use it a few times, it’ll be burnt into your brain housing group quite well. Go to the Start Menu and selec...
In the first blog post of this series, I showed you how to create a database schema and how to create a view. In this post, I will wrap it all up and show you how to create a SQL Server login account. I will also show you how to give them access to the view that we created in the Reporting schema. If you want to follow along and you haven’t read part 1, you can read it here . Like in part 1, I’m going to fire up SQL Server Management Studio (SSMS). In this first step, we are going to create a SQ...
End users may have a need to do some form of reporting of data from source systems. Opening up the database tables to end users normally isn’t the best practice, but different situations often require different implementations right? In this blog series, I am going to show you one method of limiting access to the tables containing your data, while also providing the needed data for your’ end users. For part 1 of this two part series, I am going to show you how you can create a schema that will b...
I work for a very large company where divisions are spread out literally all over the world. There are many times where I have to confer with someone else remotely in order to get something done or figure out an issue. When working on a server we all know and love using Remote Desktop to connect but did you know there's a way to share your session so both parties can interact? Well did you know it's built in to Windows 2003/2008? To do this is actually quite simple and I'm surprised it's not mor...
If we are used to the Query Analyser of SQL Server 2000, we might find it frustrating that the query window does not automatically come up when you start SSMS. You have to either press Ctrl+N or Click on New Query to start it up. Here is a way to start up the New Query window automatically. Click on Tools-- Options click on the + to exapnd environment and click on General. The following screen comes up. Click on the drop down beside start up and choose ' Open Object Exlpoer and New query'. Click...
What are we talking about? Recently Microsoft released a new version of SQL Server 2008 R2 called Parallel Data Warehouse Edition. there has been a lot of buzz about this new architecture because it is Microsoft’s entry into the Massive Parallel Processing (MPP) Scale out data warehousing arena. Typically Microsoft has offered SQL Server in a SMP or Symmetric Multi-processing architecture where all the CPUS memory and storage are in one physical architecture while the database operations take pl...
On a recent project that I was working on we came across an issue where we needed to replicate several databases on a new server. Much of the ETL had places where tables were truncated and reloaded. Normally this is not an issue at all, simply TRUNCATE TABLE and away you go. This does however cause a problem when you have a replicated database. According to the Microsoft documentation, which I obviously haven't read all of, you can't use TRUNCATE TABLE where the tables are referenced by a foreig...
A fellow BIDN'er posted in the forums reporting some weird behavior in one of their SSRS 2008 reports. Apparently there's a known bug centering around using Me.Value in an expression in a report that features a header and/or footer that contains a Global variable, such as Execution Time or Page Number. The behavior is pretty screwy, so rather than try to explain it, I figure I'd just post some screen shots in case anyone else was experiencing this issue. I created a basic report that uses a tabl...
Now that we are into the third quarter of 2010 I decided to post an update of my 2nd quarter goals and post goals for the 3rd quarter. If you all have been following my blog or me on twitter (patrickdba) you know that I was selected as a SQL Server MVP for the first time this year and that the SQL Lunch is doing great. I am really excited about both and I can't wait for you all to see the great Lunches that are coming up in the next few months. So now onto my goals. In the second quarter I met a...
Once you have installed Sql Server Management Studio, usually Object Explorer will open. If Object Explorer doesn’t immediately open after you get Sql Svr installed, you can go to: 1. View and click 2. Click on Objects Explorer You can also show the list of Registered Servers that you have by going to: 1. View again 2. Click on Registered Servers It’s convenient to have as part of your view because you are able to open your tree view and at minimum you will see your local machine under this list...
This puzzle is rather simple, but it does introduce a few new things. Using T-SQL you typically filter queries with a WHERE clause. You can also do the same with MDX, but just like T-SQL there are a few ways to accomplish this. In this puzzle you will see an example. Here are the requirements: Show: Internet Sales Amount Columns: Ship Date Calendar Year Rows: Product SubCategory Filter: Only return Product SubCategories that have sales greater than 1,000,000 and only return data between Calendar...
This is just a quick note to say that I'll be in South Florida this weekend presenting at SQLSaturday #40. This weekend and the following week are particularly awesome for a few reasons: My birthday is Friday! Look at this lineup , it's PACKED with #awesomesauce SQL BBQ on Sunday SQL Cruise next week!!!! So...yeah, it's going to be an awesome week for sure. On Saturday I'll be presenting two sessions: SQL University 101 - Starting the SQL journey SQL University 202 - Managing a SQL Server pt 2 I...
If you didn’t catch Patrick's post on SQL Saturday Wiki , I'd suggest you give it a quick read. The SQL Saturday Wiki will be an excellent tool to help first time and repeat organizers of SQL Saturday events. We really did pour as much as we could into a one day "Lock Down / Brain dump" event at "The GodFather's" lab of evil, but I think the real value of this Wiki will be when more and more event organizers put down their lessons learned into this tool. Not ever SQL Saturday is the same but the...