Search results matching tag 'Quick Tip'

Preventing projects from deploying when debugging a SSIS package

jamserra — Fri, 02 Sep 2011 05:00:00 GMT

This is a quick tip to help quickly solve a problem I run into sometimes, and now that I am writing a blog about it, will be able to solve it in no time (a big benefit of blogging is to never have to try to remember a solution to a problem you have had before…just search your blog!):

In Visual Studio, I have a solution that encompasses three projects: a SSIS project, a SSAS project, and a SSRS project. Say I’m working on the SSAS project in this solution and it’s in an incomplete state. Then I get an email that there is a problem with a SSIS package, and that package is in the same solution under the SSIS project. So I go to that package and hit debug, and the SSIS package does not run because it tried to deploy the SSAS project and there were errors. Why does it try to deploy the SSAS project when I’m just trying to run a SSIS package?

The problem is your solution is set up to build and/or deploy ALL the projects when you just want to debug one project. The solution is simple - right click the solution, select “Configuration Manager…”, and uncheck the “Build” and “Deploy” boxes:

Now, when you debug the SSIS package, the SSAS project and any other packages will be ignored.

How to Run Programs as a Domain User from a Non-domain Computer

jamserra — Wed, 03 Aug 2011 05:00:00 GMT

As a consultant or contractor, many times the client we are at does not provide us with a laptop, so we must use our own. Usually that is preferred anyway, as we have all the software and tools installed and setup the way we want to make us as productive as possible. And since we may be working for multiple clients, that means there are multiple client domains we have to access. But it’s a real pain to have to join all the clients domains, for the following reasons:

The constant switching between domains if I am working on different clients at the same time can be a real hassle
Each domain join requires that each companies domain admin has to authorize the join by typing in their credentials when prompted on my laptop
When joining a domain, each companies Group Policy would be applied to my laptop
The domain admins would have full rights to my machine and therefore data from other clients

Usually, not being joined to a client’s domain is not a big deal. If you need access to a network share or printer, just browse to it and you will be prompted for your domain credentials. The fact that you’re using different domain credentials to access the resource from those that you logged in with doesn’t matter. If you want to maintain a persistent connection to a network share (and avoid a authentication time-out), you can create a batch file and include commands like:

NET USE \\server /user:domain\username /persistent:yes

However, there are certain tools, such as SQL Server Management Studio (SSMS), SQL Query Analyzer, and Visual Studio, that don’t allow you to specify alternate domain credentials for authentication. For example, SSMS allows you to log into a SQL Server instance using Windows Authentication or SQL Server Authentication. If the SQL instance requires Windows Authentication – the recommended configuration – SQL Server Management Studio uses your logged in credentials. This works well if your computer is part of the domain, but fails if it is not. It doesn’t let you specify alternate credentials or even prompt you for alternate credentials if the log-in fails.

So how do you get around this problem? NET USE doesn’t help here because NET USE is specifically for network shares. Using the SHIFT right-click on a program that gives you the “Run as different user” prompt does not work either as it tries to run the command locally as the domain user, who is unknown by your computer because you’re not part of the domain.

There are a few “undesirable” solutions: remote desktop into a domain computer so that you could run the tools such as SSMS, use a domain-joined virtual machine, or ask co-workers to run commands for you.

The two “desirable” solutions are:

1) Use the RUNAS command with an option that most people have not noticed: /netonly. The command is run as your local user, but uses the supplied domain credentials only when accessing the network. For example, to run SSMS:

RunAs /netonly /user:domain\username “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe”

You will be prompted to enter a password when you run this command. That could be a pain, but fortunately there is a utility that is a RunAs replacement that allows you to specify a password. It is called CPAU.

2) Use the ShellRunAs utility. It provides functionality similar to that of RunAs to launch programs as a different user via a convenient shell context-menu entry. It is essentially the same thing as using the SHIFT right-click on a program which gives you the “Run as different user” prompt, except this gives you a “Run as different user (netonly)” prompt, meaning it adds the “netonly” switch. This is more convenient than the RunAs command as you don’t have to type a long DOS command.

I have found these solutions very helpful for testing purposes. For example, say I created a role in a SSAS cube called “Northeast Role” that is granted access to only a particular subset of data, the northeast region. The membership of this role includes a windows group, say “Northeast region”. I can have a test windows account created, say “NEtest”, and have that account placed in the “Northeast region” group. To make sure that user is only able to see the northeast region, I can use one of the above solutions to login as “NEtest” via Excel to access the cube and make sure it’s working as it is supposed to.

Dirty looking SQL or MDX? Clean it up

jamserra — Wed, 11 May 2011 06:13:00 GMT

A quick tip for you: Ever get some SQL or MDX sent to you in an email, or copied from a website, or pulled from SQL Profiler, that is difficult to understand because it’s not formatted? Well, there are a couple of free tools that will clean up your SQL: SQLinForm and Instant SQL Formatter. Simple copy and paste in the SQL, hit the “Format” button, and wallah, nice clean SQL!

The tools to cleanup MDX are from Nick Medveditskov at this Microsoft site (does DAX too) and from Mosha Pasumansky.

A quicker way than using count(*)

jamserra — Mon, 09 May 2011 13:16:00 GMT

A quick little tip I found a couple of years ago. Ever use the count(*) syntax and it takes forever to count all the rows in a table? I had this problem when I would sometimes run counts on a bunch of tables to check that my replication was working correctly. Here is a much, much quicker way using the DMV sys.dm_db_partition_stats:

SELECT
	SUM(row_count) AS TotRows
FROM 
	sys.dm_db_partition_stats
WHERE 
	object_name(object_id) = 'YourTableName'
	AND index_id < 2

Running the count(*) on one of my large tables took 33 seconds. The above statement took 1 second.

If you want to do a record count on all tables in a database:

SELECT 
	OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM 
	sys.dm_db_partition_stats st
WHERE 
	index_id < 2
    ORDER BY st.row_count DESC

Another little known way to get table counts quickly is in SSMS click View -> Object Explorer Details, then click on the “Tables” folder for any database. By default you will see all the table names, but not the row count. To get that, right-click on a column header and select “Row Count”.