SQLHunt.com Aggregated MS SQL Resources Feed

SQL Server 2012 SSIS – Sending files using FTP Task

2012-02-23T15:00:00Z

This article will show you how to configure the FTP task in SSIS send files to remote server using the FTP protocol.

Using SQL Test Database Unit Testing with TeamCity Continuous Integration

2012-02-23T15:00:00Z

With database applications, the process of test and integration can be frustratingly slow because so much of it is based on manual processes. Everyone seems to agree that automation of the process provides the answer to accomodating shorter development cycles, but how, exactly? Dave Green describes a successful process that integrates third-party tools.

SQL Server - Changing Rows to Columns Using PIVOT

2012-02-23T05:06:37Z

(Paresh Prajapati) During working with one logic, i got chance to work with PIVOT operation. Sometime we need do require rowdata as column in our custom logic, then we can use some temp table and then populate agreegate data in temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as column.

What Does Open Systems Mean? (2/23/2012)

2012-02-23T05:06:37Z

What Does Open Systems Mean? All Open System software is not the same. There are number of different licensing models allowing different rights. May people equate Open System with free…but that is not necessarily true. One thing (I think) Open System software has in common is that the...(more)

Adding Time in HH:MM Format - Useful SQL Query

2012-02-23T05:06:37Z

(Jobless Creature) In one of our projects related to employee shift management, the shift details are captured and total hours worked per day is stored in HH:MM format. A new reporting requirement is that we need to display the total hours worked per week in HH:MM format.

Issues with the system_health session in SQL Server 2012

2012-02-23T05:06:37Z

The ever alert Jonathan Kehayias (Blog | Twitter) sent me a question recently asking about the Extended Event UI used for showing predicates. In particular, he was wondering about the predicate for the wait_info event that is defined in the system_health session and was wondering what was going on.

Execute Multiple 2008/2005 SSIS Packages with T-SQL

2012-02-23T05:06:37Z

(Mike Davis) If you want to execute a set of SSIS packages in SQL Server 2008 or 2005, you can do this using T-SQL. First you will need a table with all of your package names on it. Then a While loop to execute each package.

Automate MDX Query Testing

2012-02-23T04:25:00Z

In one my recent MDX classes, a student asked about automation of MDX queries for the purposes of testing query performance, so today I’ll answer that question and draw your attention to a few resources available.

Get the ASCMD Utility

If you’re running SQL Server 2008 Analysis Services or SQL Server 2008 R2 Analysis Services, you can download the ASCMD_StressTestingScripts from the Analysis Services Samples page on Codeplex to get the main prize, the compiled ASCMD executable. (This version should also work with SQL Server 2005 Analysis Services, but I haven’t tested it.)

This utility allows you to execute MDX or DMX queries or XMLA commands from the command line. This capability means that you can build out some complex batch files to automate your MDX query testing, along with other repetitive tasks such as database processing. You can view the 2005 version of the ReadMe file to see the syntax as well as various ways that you might you use this utility (if you scroll all the way to the bottom of the page).

The old ReadMe file doesn’t include all of the arguments that are in the latest version of ASCMD. You can download a more current ReadMe document that’s buried deep in the bowels of Codeplex to get more information.

Try a Simple Command

In theory, the stress testing scripts let you execute ASCMD as a single client with single query or multiple queries, or as multiple clients with single query, or as multiple clients with multiple queries. I wasn’t able to get that piece working as a file seems to be missing from the download, but you can create your own batch files to do similar things. You can get the basic idea by reviewing the RunASCMDParallelStressTest.cmd and RunASCMDSerialStressTest.cmd files that come in the download.

If you don’t want to wade through all that batch file stuff to figure out how to make ASCMD work, let me cut to the chase. You can execute asmcd /? to see the parameters available. For running an MDX query, assuming that you want to capture trace information to see the query duration and key trace events like QuerySubcubeVerbose and GetDataFromCache, you use the following syntax:

 ascmd –S <server\instance> -d <database name> -I <mdx file> -o Output.xml -T <CSV file>

So, for example, to run on my local instance, using a query file found in the Queries subfolder of the download, I would run the following command:

ascmd -S localhost -d "Adventure Works DW 2008R2" -i Queries\Query1-3.mdx -o Output.xml -T Trace.csv

The Output.xml will contain the query results and a lot of metadata about the cube that you can probably ignore if your goal is to get performance testing data. The Trace file is a pipe-delimited CSV file that contains the same type of trace data that you get when you run a SQL Server Profiler trace for Analysis Services. The trace file gets overwritten on each execution, except as noted below.

You can adjust the level of detail in your trace file by adding the –Tl argument with one of the following values

High (default) – captures everything.
Medium – captures everything except ProgressReportCurrent and Notification events.
Low – captures only events with “End” or “Error”.
Duration – captures only execution duration and writes one line in the trace file with current time, duration, execution text, database, and server name. If you use this argument, and execute ASCMD multiple times, each execution appends new data to the file.
Duration-result – captures the same results as when you use “duration” but includes an addition column to store the result of the execution. Each execution appends new data to the file.

Batch It

The ASCMD utility can run one file at a time, but you can put multiple queries into the file using a GO command between the queries. Or you can set up a batch process with a loop to call ASCMD multiple times.

You can leave out the –o argument but you’ll get the output stream on your screen, unless of course you put this into a batch file and use echo off and echo on after executing the ASCMD.

For performance testing, you should also include some additional steps before you run the MDX query to get true baseline performance measurements. Put these steps into an MDX or XMLA script file and use ASCMD to execute them in your batch file before running the MDX query that you’re testing:

Clear the Analysis Services cache

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ClearCache>
    <Object>
      <DatabaseID>Adventure Works DW 2008R2</DatabaseID>
    </Object>
  </ClearCache>
 </Batch>

Clear the file system cache.
Execute the MDX script independently.

SELECT {} ON 0 FROM [Adventure Works]

Learn More About Query Performance Analysis

Now that you’ve captured trace event information, what do you do with it? Here are some resources to help you determine what it means:

Contact Manager Web API

2012-02-16T09:56:08Z

This sample uses ASP.NET Web API to build a simple contact manager application. The application consists of a single static HTML page that makes requests to a backend contact manager web API for data and to manipulate application state.

Created by: Daniel Roth
Published Date: 2/16/2012

Want Another Reason to Hate iTunes?

2012-02-22T15:42:00Z

I'm not one to whine. Really. I'm totally not a whiner. However, I'm going to sound like one with this statement... I fricken HATE iTunes. There, I said it. I'm already starting to feel better. Playing on Elisebeth Barrett Browning and her fantastic poem, "How do I love thee? Let me count the ways." , I'm going to count some ways that iTunes is filling me with inhuman, Hulk-like rage: Ridiculously frequent updates . Not the "Update Tuesday" sort of thing we get from Microsoft, but the "I'm going...(read more)

Which Replication Databases Can I Mirror?

2010-08-24T10:10:00Z

By Paul Randal
We recently had some database corruption caused by a faulty drive, and I’d now like to add protection to some of our databases using database mirroring. It won’t be a problem to implement database mirroring on most of these databases, but some of the databases are involved in transactional replication and I’ve heard conflicting information about whether it’s possible to mirror replication databases. Can you shed some light on this topic?

How to Incrementally Process your #Tabular Model by Using Process Add in #ssas

2012-02-22T12:31:00Z

I recently wrote an article about how to implement a Process Add on a table in a Tabular model. This is an area where there is a lack of documentation by Microsoft, especially if you want to use a custom binding query for every Process Add command, which should be the common case for a Process Add scenario. Cathy Dumas wrote several blog posts about this argument and I tried to put everything together, showing the same example written in XMLA Script, AMO and PowerShell. You can also use Integration Services, but considering you probably want to customize the binding query, chances are that you will opt for a more programmatic approach, maybe embedding an XMLA Script or an AMO Script in a standard Task in SSIS.

The next step for me will be studying how much “near real time” a Tabular model can be by using this type of Process option. In fact, it should be possible to use a push model processing, like in a Multidimensional model, and my initial tests say that there is no “out of service” window when you process data in Tabular. So you can forget the lock issue you may have in a Multidimensional model in Analysis Services. But you need memory and it is hard today to say how much this architecture can scale in terms of concurrent users when you start processing data incrementally. Also the quality of compression made by Vertipaq might suffer.

If you have any experience in this area, please contact me and share your knowledge! Otherwise, stay tuned, I’ll try to further study this topic, then blogging what I’ll found.

Is the SSIS Catalog going to be enough?

2012-02-22T09:00:00Z

Over the past year or so I have written at length about the SSIS Catalog that will be provided in the upcoming version of SQL Server Integration Services (SSIS 2012) and the capabilities that it will bring; one of the biggest benefits (in my opinion) is that logging is now something that is taken care of on your behalf.

Many SSIS developers have built their own logging solutions (often referred to as "frameworks") over the past few years and it occurred to me that many of those developers may have a need to carry on running their own logging solutions alongside that which the SSIS Catalog provides. To that end I have raised two Connect submissions requesting enhancements to SSIS that will make it easier for developers to do just that:

One of those (the one about the ExecutionID variable) has already been implemented and will be available in the final release of SQL Server 2012 however this blog post isn't about drumming up support for Connect submissions. I am more interested in knowing whether you think you will need to log information over and above what the SSIS Catalog provides and if so, what exactly? I can envisage a number of scenarios in which additional logging may be necessary:

Rowcounts captured using the Row Count Transformation or by the Execute SQL Task.
Capturing custom errors that are specific to the implementation rather than the generic errors that SSIS will throw.

Do you plan to use your own logging solutions alongside the SSIS Catalog? If so, why? Let me know in the comments, I'm really interested to discover how folks plan to use this.

@jamiet

A Few Thoughts on the SQL Server MCM Program

2012-02-22T07:11:00Z

I took the MCM lab a couple of weeks ago and passed. I still don’t believe it - it’s been quite the journey.

There have been many decisive moments in my career; one of those was back in 2010 when I decided I would work on obtaining the certification after Brent Ozar blogged rather extensively about his experience with the program. I progressively worked on aspects of SQL Server that were out of my comfort zone – and that paid off. But simply put, I have the cert - but still don’t consider myself a true Master. And I probably never will.

I might be okay in certain areas of SQL, maybe even good in others - but I don’t master them all. I intend on getting better on those areas, but that won’t happen overnight.

SQL Server is a mammoth of a product. It’s actually a suite of products – and the MCM certification focuses on only one aspect of it: the Database Engine. No SSAS, no SSIS, no SSRS.

So you might be wondering: Is it worth it then? Why work so hard on obtaining a certification that calls you “Master” when it doesn’t test your skills on every single feature of SQL Server?

To me, it was. And then some.

The fact that I value most after going through the program? Knowing exactly what I don’t know. In other words, looking at a problem and knowing that I won’t be able to solve it right away, yet still I will have an idea of where to begin digging for information to conquer it.

You see, when I decided to go for the MCM I would have probably ranked myself a 6/10. So I went and trained myself on those areas where I felt I wasn’t strong enough. I read blogs, I watched videos, I attended SQLSkills immersion events (lucky me!), I went to SQLSaturdays, and even a couple of PASS Summits – but all of that was theory, not a whole lot of practice. And when you get to the MCM Lab, you better had practiced. You better had familiarized with that particular feature you’re being challenged with – at least a tiny bit. Learning from BOL on the fly during the MCM Lab is probably just going to be a waste of time – trust me on that one.

The other thing I learned along the way? The power of networking. If you are a SQL Server professional, in any function – DBA, Dev or what have you – allow yourself a couple of minutes to brag about what you do: our community is awesome. Better than anything I’ve seen out there. I haven’t met a single person in the SQL community that’s not willing to help a colleague.

And that’s precisely what helped me prepare the most for the MCM: helping others. Perhaps that will help you get there as well?

Cheers,

-Argenis

Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management

2012-02-22T15:00:00Z

Examines the scale-out capabilities of SQL Server Agent, using the MSX/TSX subsystem. This capability allows you to run jobs on multiple systems and consolidate the job results/history onto a single server.

SQL Server 2008 R2 Data Types

2012-02-22T15:00:00Z

A list of SQL Server 2008 R2 Data Types and Storage Sizes.

SQL Server Reporting Services Image Source Report Options

2012-02-22T15:00:00Z

Companies often need to generate reports and forms from scanned images and various government supplied formats. Proper knowledge of how to incorporate images in a report is a must.

Database Storage Survey – win 3 x $50 Amazon vouchers

2012-02-22T15:00:00Z

We’d like to find out about who handles SQL Server database storage at your organization. Enter our quick survey now for the chance to win one of three $50 Amazon vouchers.

Would You Use Open Systems Software? (2/22/2012)

2012-02-22T05:09:28Z

SSWUGtv - New Show! With Stephen Wynkoop In this edition... Privacy policies and data warehouses, Oracle and their 70x performance increase, Career tips from Laura Rose. Also, our guest segment is all about security... and so much more. Watch the Show Would You Use Open Systems Softwa...(more)

Tips for using SQL Server 2008 configuration options (Part 1)

2012-02-22T05:09:28Z

In this article, you can find some useful tips to use SQL Server 2008 configuration options.