data

You are currently browsing articles tagged data.

Now and then I encounter forum questions in the style of the following:

I have a report with a title.  When rendered through the Report Manager and when exported to PDF, I want it to render as normal.  However, when exported to Excel I do not want to get the title.  How can I hide it?

Because I don’t like re-inventing the wheel each time I decided to write a blog post about it.

As of SQL Server 2008 R2, we’ve got a built-in global field that can help us out.  This field is called Globals!RenderFormat.  It has two properties: Name and IsInteractive.  Name represents the unique name that indicates the chosen renderer, and IsInteractive indicates whether or not the chosen report format is, well, interactive.

Depending on the renderer, the values of the properties differ.  To be able to use the variable in an expression, we need to know its values for each rendering format.  Here’s the list of different possibilities:

Renderer RenderFormat.Name RenderFormat.IsInteractive
Preview in BIDS or rendered through Report Manager RPL True
XML file with report data XML False
CSV (comma delimited) CSV False
TIFF file or Print button IMAGE False
PDF PDF False
MHTML (web archive) MHTML True
Excel EXCEL False
Word WORD False

If these names for RenderFormat look familiar to you, you’re probably right.  Have a look at the rsreportserver.config file in the C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer folder.  Note that you may need to adapt the folder to your specific settings.  In my case my instance is called “SQL2008R2”.  Near the bottom of that configuration file you can find the <Render> node, located under <Extensions>.  The names that you see there are those used by the RenderFormat.Name property.

Now that we know what values to test on, let’s get started.

If we get back to the example of hiding a title, or textbox, when exporting to Excel, here’s what needs to happen.  Locate the Hidden property of the textbox that you want to hide, and give it the following expression:

=IIF(Globals!RenderFormat.Name = "EXCEL", True, False)

What we’re saying here is: if the RenderFormat is EXCEL, then the Hidden property should be set to True.  Which results in a hidden textbox whenever the report is exported to Excel!

As Erik pointed out in the comments, in this particular case you don’t need the IIF() statement.  The result of the expression results in True when the expected value should be True, and False when False is expected.

As a quick note: when building your expression through the expression builder, you’ll notice that the Intellisense doesn’t know the new RenderFormat field yet.  Do not worry about that, just continue typing and ignore any errors being indicated.  If you use the syntax as I highlighted above, it will work!  Well, unless you’re running an earlier version than SQL Server 2008 R2 of course.  In that case it won’t work.

Intellisense doesn't know RenderFormat yet

In contradiction to the Intellisense, the bottom part of the expression builder screen has been updated to show the new properties.  So if you don’t remember the syntax, you can just locate the field in the Built-in Fields category and give it a good double-click.

RenderFormat is located in the Built-in Fields category

Of course, the Excel example in this post is just one of many possibilities that this new field offers.  Is your company environment-friendly and does it want to prevent wasting paper?  Now it’s possible, just hide that 50-pages long table when the report is being rendered for print!

Have fun!

Valentino.

References

Globals!RenderFormat aka Renderer Dependent Report Layout by Robert Bruckner

Share

Tags: , , ,

Last month we had the pleasure of enjoying the third edition of our Belgian SQL Server Day.  If you were there and you were unable to attend a particular session, even though you were interested in it, here’s your answer.  The sessions have been recorded, and the videos are now available online!  How’s that for a New Year’s present?

SQL Server Day 2010: The Videos

And if those videos didn’t still your hunger, or you just have plenty of time to kill, check out this blog post on Chris Webb’s BI blog.  He has posted a link to the 152 videos of SQL Bits, a three-day SQL Server event in the UK.

Still not satisfied?  Maybe these SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos can keep you busy for a little while then. :-)

Have fun!

Valentino.

Share

Tags: , , ,

Have you ever needed to write a script that would store the Windows login of the user that executes the script in a table, without hard-coding it?  And you couldn’t find your way through the user jungle of T-SQL?  Then read on!

A normal approach would be to rely on the IntelliSense feature of SSMS and start typing something like:

select use

Which gives you a dropdown like:

image

Cool you think, there are four system functions to return something user-related, one of them would be the Windows user, right?  Well, wrong.

Let’s find out what these functions actually return, and locate a method that does return what we require.

The Different User-Related System Functions

USER

According to the BOL:

Allows a system-supplied value for the database user name of the current user to be inserted into a table when no default value is specified.

So, how many times did you read that?  And do you now know what you’ll get?  Me neither.  As far as I understand it, you’ll get the database user name of the connection context.  Not what we need.

USER_ID

According to BOL:

Returns the identification number for a database user.

The function accepts one argument that accepts a string representing a username.  When no argument is provided, it will return the user ID of the execution context.

But that’s not important because the BOL also mentions that it will be removed in a future version and that you thus shouldn’t use it anymore.  No problem if you need it though, its replacement is called DATABASE_PRINCIPAL_ID.

USER_NAME

According to BOL:

Returns a database user name from a specified identification number.

This is the inverse of USER_ID.  It accepts one argument representing the user ID.  When omitted, the user of the current execution context will be returned.  Which is actually the equivalent of the USER function.

Still no Windows user though, so let’s move on to the next one.

USER_SID

Not documented in the BOL.

It seems to represent the sid column found through following query:

select * from sys.sysusers

And the sys.sysusers view is documented.  Apparently “sid” stands for “security identifier” and it’s a varbinary.  But not only is the view documented, it is also deprecated.  You should use the following view instead:

select * from sys.database_principals

That query returns a list of principals in the current database, and one of the fields is indeed our dear sid.  However, we can now conclude that sid is definitely not what we’re looking for in this little quest, it doesn’t even closely resemble a Windows user name.  So let’s move on again!

CURRENT_USER

Hmm, “current user”, will this finally be the Windows user with which I’m running my queries?

According to BOL:

Returns the name of the current user. This function is equivalent to USER_NAME().

Equivalent to USER_NAME, so still not what we’re looking for.

SESSION_USER

According to BOL:

SESSION_USER returns the user name of the current context in the current database.

You can probably guess by now that again this is not what we’re searching for.  Next.

SYSTEM_USER

According to BOL:

Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.

Ah, finally a description that contains the word “login”!  And indeed, here’s what it returns:

ORDINA\VaVr

That is indeed my Windows user.  Mission accomplished!

Please note: if you’re running this through a window connected via a SQL Server login, you will get that login as opposed to your Windows login.  And if you get ‘sa’ while you’re connected to a production server you should look into making some security changes. :-)

SUSER_NAME and SUSER_SNAME

Two more similar functions exist: SUSER_NAME and SUSER_SNAME.

Both return a login name as well, and both accept a parameter.  When executed without parameter, they return the login name of the current user.  Their only difference is the parameter that they accept.  SUSER_NAME accepts the “login identification number of the user”, which is an int, and SUSER_SNAME accepts the “login security identification number”, a varbinary(85) (remember sid from above?).

Quick Query

The following query shows how to use all the functions, with the default values for any parameters.

select USER as cUSER, USER_ID() as cUSER_ID, USER_NAME() as USER_NAME,
    USER_SID() as cUSER_SID, CURRENT_USER as cCURRENT_USER,
    SESSION_USER as cSESSION_USER, SYSTEM_USER as cSYSTEM_USER,
    SUSER_NAME() as cSUSER_NAME, SUSER_SNAME() as cSUSER_SNAME

Have fun!

Share

Tags: , ,

Here’s a quick post about one of my favorite pages on the Microsoft Support site.  The page explains how to retrieve your SQL Server version, and also mentions the different version numbers of all major releases and service packs.

One of my favorite statements since many years is the following:

select @@version;

Here’s the output that it produces on my laptop running Windows 7, while connected to my R2 server:

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

It not only retrieves the version, edition and CPU architecture of SQL Server, on top of that it also shows you the operating system version on which it is running!

The statement is easy to remember and gives you all the details that you need.  Well, that depends on the situation of course.  In one of my previous jobs I was, as developer, last-line support for an application that was used worldwide.  Which means that now and then I had people on the phone that had “some vague issue” with our application.  And even though I speak four languages (more or less), I can assure you that getting details through a phone line can be a tough job.  I remember one case where I had to spend half an hour to get the person on the other side find the Start button.  Yes, that stupid thing on the bottom left, the thing that you need all the time as a regular Windows user.  And no, they we’re not on Windows 3.x anymore!  In those times, it even had the letters S T A R and T all over it!  But alas, it remained impossible to find.  Then I switched to finding the clock.  Guess what: wasn’t in the right bottom corner either!  Further during the conversation I tried all other corners, also tried to explain that the status bar may have been set to automatically hide.  Oh well, at a certain point they’d suddenly found it!  I don’t know where, maybe on another PC, at that point I didn’t care anymore, I just wanted them to open up the Management Studio, er, Query Analyzer more likely.

Anyway, at times like that, you’re glad that easy SQL commands still exist.

The statement above is not the only possibility to get details about the SQL Server version.  Another method is through the SERVERPROPERTY function.  Here’s a sample statement (from the Books Online) to retrieve version information:

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;

Try getting that to the other side of the phone line!  And no, email was not an option either…

Here’s the statement’s output:

Output of SERVERPROPERTY sample statement

Not only is the statement more difficult to remember, the output is less readable too.  For instance you need to know that EngineEdition 3 means Enterprise Edition.

Nevertheless, it is a useful function with more possibilities than @@version.  Check out the BOL for all the details and available properties.

Let’s get back on topic now.  I was going to mention a certain page on the Microsoft Support site.  Here it is: How to identify your SQL Server version and edition.

I think this page is a great reference in terms of versions.  If someone wants to know what version of SQL Server he’s running and doesn’t know the version numbers, this is the place to be!  I just whish that Microsoft would keep it more up-to-date.  At this moment it’s not even mentioning SQL Server 2008 R2, while that was released about half a year ago.  No sign of SP2 for SQL Server 2008 either.  I’m going to provide feedback through the textbox at the bottom of the page with this exact statement.  Hopefully that has some effect.

In case you are looking for those latest version numbers, here they are:

SQL Server 2008 SP2 10.00.4000.00
SQL Server 2008 R2 RTM 10.50.1600.1

 

Have fun!

Valentino.

Share

Tags: ,

Not that long ago I posted an item showing off my third Microsoft Certified Technology Specialist (MCTS) certificate on SQL Server 2008.  With the MTCS certificates all in my pocket, the time had come to start on the Microsoft Certified IT Professional (MCITP) certification track.

Microsoft Certified IT Professional (MCITP), Database Developer 2008Yes, I’m happy to announce that I have passed the 70-451 exam.  For those who are not familiar with the numbers, it’s the exam to get the MCITP Database Developer 2008 certificate.

Obviously I can’t say anything about the content of the exam, but what I can do is explain the questioning style with a fictitious example.

Fictitious MCTS Question

If you’ve done one of the MCTS exams, you know that there’s quite some focus on syntax stuff.  Here’s an example.

Question Example

Select the answer that completes the following sentence:

Microsoft SQL Server 2008 is a …

A. floppy drive system

B. Beta Ace Service

C. Database Server

D. Operating System

(Correct answer is C.)

Obviously this is fairly simple.  Now, imagine that the question mentions an incomplete query and you need to select the right answer to complete it.  And there are two (or more) similar answers that seem plausible to you.  Until you start thinking, sometimes thinking hard.  And then it hits you: three out of the four answers contain something to invalidate the query because of a syntax issue.

That’s the MCTS style.  I don’t really like it to be honest.  In real-life, when I’m not sure of the syntax, first of all the Management Studio will tell me .  If that’s not enough, I do a quick search in the Books Online and I’m good to go.  Obviously you won’t be able to answer these questions if you’re not familiar with the statements.  But then again, if you read about the syntax an hour before the exam, you’ll probably still remember it, and you will be able to answer the question.  What does that prove then?  I’ll leave the conclusion to you.  To be fair I need to mention that not all questions are like this.

Fictitious MCITP Question

The questioning style for the MCITP exam is a bit different.  There’s less focus on syntax details, or just one single detail, and more on the broader picture.  Again I’ll demonstrate with an example.

Question ExampleYearling T-bone steak

You’re hungry and you need to do something about it by method of eating.

What answer will solve your problem, taking into account that:

  • you need to be able to do sports in an hour time
  • you don’t have a microwave oven
  • you’re allergic to certain types of fruit

A. a T-Bone steak (500 grams) with a creamy pepper sauce accompanied by French Fries

B. cold pasta that you really don’t want to eat unheated

C. hot pasta with meat and zucchini

D. cold pasta with onion, apple and tomato

(Correct answer is C.)

Do you notice the difference?  You need a much broader knowledge to be able to solve these types of question.  For our example, you need to know that a microwave oven can be used to heat up food and you need to know that zucchini is not fruit.  Furthermore you need to be aware that answer A would be in conflict with the ability to do sports an hour after eating.

I prefer these types of question over the MCTS style.  I believe these require more knowledge of and on-the-job experience with the product.  And after all, proving that you have some knowledge about it is the whole point of the exams, right?

For another great story related to the 70-433 and 70-451 exams – that’s the MCTS and MCITP for the Database Developer 2008 credential – check out Brent Ozar’s blog post: MCM Prep Week: Microsoft Exams 70-433 and 70-451.  He wrote it while preparing for the MCM certification track.

That’s it for now, have fun!

Valentino.

Share

Tags: , , , ,

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved