Mini Post: DTUTIL.exe

Hey everyone! Welcome back to SQL Something!

Let's see if we can get a couple posts in on the last day of the month.

In the next few posts we're gonna take a brief look at some of the command prompt utilities that get installed when you install Integrated Services (IS). These utilities are 32-bit unless you also install Client Tools or Data Tools.

The first of which is here (DTEXEC.exe).

The second of which is DTUTIL.exe.

DTUTL.exe is a command prompt utility used to manage SSIS packages. DTUTIL allows you to COPY, MOVE, DELETE or verify if a package EXISTs. The utility can be used on packages stored in:

• A SQL Server DB
• A SSIS Package Store
• The File System

(You designate the package location via the location options: /SQL, /DTS, /FILE respectively)

Syntax is as follows: DTUTIL /OPTION[Value] [/OPTION[Value]...]
Example: DTUTIL /SQL PackageName /DELETE

The above example calls the utility and tells it to delete the package named 'PackageName' in a SQL Server DB.

There are several parameters for the utility, but we'll just look at a few key ones below:

• /COPY - allows you to copy a package to a designated location.
• /DELETE - allows you to delete apackage in a designated location.
• /EXISTS - verifies that a package exists in a designated location.
• /MOVE - moves a package from a designation source to a designated destination.

When copying to a SQL Server DB, sometimes credentials are required. For this you can use the below optional parameters:

• /SOURCEU - Source Username
• /DESTU - Destination Username
• /SOURCEP - Source Password
• /DESTP - Destination Password

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: DTEXEC.exe

Hey everyone! Welcome back to SQL Something!

Let's see if we can get a couple posts in on the last day of the month.

In the next few posts we're gonna take a brief look at some of the command prompt utilities that get installed when you install Integrated Services (IS). These utilities are 32-bit unless you also install Client Tools or Data Tools.

The first of which is DTEXEC.exe.

DTEXEC.exe is a command prompt utility that is used to configure and execute SSIS packages.

Configure:

• It let's you load packages from SQL Server, A SSIS package store and the File System.
• It let's you access package configurations which include: Parameters, Variables, Properties and Logging.

Execution:

Actual execution is done by calling three stored procedures.

• CATALOG.CREATE_EXECUTION - Creates an instance of execution in the SSIS catalog.
• CATALOG.SET_EXECUTION_PARAMETER_VALUE - Sets parameter values.
• CATALOG.START_EXECUTION - Starts an instance of execution.

You can view examples of syntax here.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Showing Permissions Granted To A User, Via T-SQL

Hey everyone! Welcome back to SQL Something!

Today we are going to take a quick look at how to view the available permissions a user was granted. I needed this the other day when I wanted to create a user with similar permissions for another DB. Luckily fn_my_permissions has all the answers we need.

We can query fn_my_permissions to find out permissions info at a DB level like so:

USE YourDBName;
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');

This, however, will list the permissions for the current user doing the query (which might not actually be the user you want the info for).

To find permissions for a specific user, you must first impersonate that user like so:

USE YourDBName;
EXECUTE AS USER 'User1';
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;

Please note that using the EXECUTE command will give you only the permissions of the user you are impersonating. You need the REVERT command at the end of the query to give yourself back the permissions you previously had.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Getting Database Creation Date Via T-SQL

Hey everyone! Welcome back to SQL Something!

The first post for April is going to be pretty simple. Today we are going to look at how to retrieve the date that a database was created via T-SQL. Could be useful in some situations. I, for instance, just wanted to know when a DB was swapped out for a empty one. The new empty DB wasn't put to use the same day/time it was created, so the timestamps of the actual data in the DB didn't exactly provide me with what I wanted.

Anyway, I digress. Let's look at what sys.databases can do in order to help with our problem.

Mini Post: Getting A List of Connections By IP Address

Hello and welcome back to SQL Something!

I was looking at how to view current connections the other day (much like this past blog post) when I think I saw someone mention checking SQL connections by IP address. I thought this would be pretty cool if it was possible, and after some light searching I found this nice, succinct post by Glenn Berry.

I won't post the query here, but suffice to say it does the job very well.

It uses fields found in the sys.dm_exec_connections and sys.dm_exec_sessions views in order to display the client_net_address (the IP address), the host_name and the login_name as well as a count of session_id per login which provides a connection count per login. Awesome sauce.

Finally, there is a second query in the original post by Mr. Berry that gives you strictly a count by login name using only the sys.dm_exec_connections, which is neat (though honestly I'm not too sure how useful; If you can think of a great way the second query can be used, please let me know by commenting).

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: SQL Replication - Changing The Default Snapshot Folder

Hello and good day, wherever you are! Welcome back to SQL Something!

Today's Mini Post is how to change your default snapshot folder for SQL replication. A snapshot is necessary to initially initialize the database that the replicated data is being sent to. So let's look at the process, quickly and with pictures!

Pictures make everything better.

Mini Post: Group By Month (or Year)

Hello to all of you out there! Welcome back to SQL Something!

Today's Mini Post is showing how to group by a date part (such as month, year) when given a start and end date.

Today specifically we are going to group by month in the following example:

SELECT DATENAME(month, YourDateField) MonthName,
                DATEPART(month, YourDateField) MonthNumber,
                COUNT (YourField) FieldTotal
FROM YourTable
WHERE (YourDateField >= '01/01/2013' and YourDateField < '01/01/2014')
 -- AND add other criteria here
GROUP BY DATENAME(month, YourDateField),
                     DATEPART(month, YourDateField)
ORDER BY MonthNumber;

And that's all there is too it. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)