Mini Post: Error 0xc02020a1: Data conversion failed. The data conversion for column returned status value 4

Helloooo! And welcome (or welcome back) to SQL Something!

Today's mini-post is about an error you can run into when attempting to use the Import/Export Wizard to import some data into a DB of your choice:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "COLUMN NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Fig. 1: Annoying Error.

Error: The Server Instance Witness Rejected Configure Request

Hey everyone out there! Welcome back to SQL Something!

Today we look at an interesting problem. I'm not sure as to the root cause of it, but we will look at how to solve it.

(P.S. If anyone out there knows why this occurred, please feel free to contribute in the comments below)

The error in question is:

The server instance Witness rejected configure request; read its error log file for more information. The reason 1427, and state 31, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

Fig. 1: So it begins.

I discovered this error repeating continuously in the Application Log in the Windows Event Viewer.

Naturally I checked all the databases currently being mirrored, however all seemed to be in order (both the Principals as well as the Mirrors). So how can we find out more about the issue and how can we fix it?

Mini Post: Creating and Configuring a UDL file

Good day everyone! And welcome back to SQL Something!
Here I am at the end of the month again, trying to fit in a couple last minute posts. Said I wouldn't do this again. Hopefully I spread them out a bit better next month.

Anyway, without any more delay let's look at creating a UDL file.

Firstly a UDL (Universal Data Link) file allows persons to test connectivity to OLE DB Providers. The OLE DB Provider we are interested in, of course, is the one for SQL Server. This is very useful to quickly test credentials on a machine that does not have SQL Server Management Studio installed, but needs access to a SQL Server instance (example testing SQL Server access from an application server to a SQL Server).

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. :-)

Product Spotlight: Idera's SQL Virtual Database

Hey everyone and welcome to (and/or welcome back, hopefully) to SQL Something!

Today I'm trying something a little different. Today I'm going to take a look at a specific SQL related product for the first time. I honestly don't know how often I will be doing these but we'll see!

So did you ever wish you could just query a backup? Yes, just like a DB but without having to restore it? Yes, without restoring it, so you don't have to cater for additional space for the restore or cater for the time taken to perform the restore.

Not possible?

It is with Idera's SQL Virtual Database.

Fig. 1: Main Screen.

Mini Post: The Process Could Not Execute sp_replcmds

Good day out there and welcome back to SQL Something!

Today we're going to look at a small error which is related to the permissions on a DB that is being used for publishing in SQL replication.

After setting up my publication and a subscriber to be used for transactional replication, I noticed that transactions were not being replicated. Upon checking the "View Log Agent Status" (right click the publication and choose the option), I saw the following error: "The process could not execute sp_replcmds".

Fig. 1: The Error in Log Reader Agent. It can also be found in the Replication Monitor, under the 'Agents' tab.

Mini Post: Backing Up/Restoring From The Command Line

Hey everyone and welcome back to SQL Something!

I happened to see an old post of mine (towards the bottom of the post) where I mentioned restoring from the command line but I did not actually say how.

So that's what I want to look at really quickly.

Convert Comma Delimited String To A Table

Welcome back to SQL Something everyone!

Just seeing if I could throw one more post in before the end of the month (trying so hard to stick to my "at least 2 posts per month" rule).

Today we'll look at how to convert a comma delimited string to a table, where each delimited value will be placed in a new row.

Mini Post: Restore of Database Failed; Access is Denied

Happy New Year! (Albeit a little late!)

Welcome back to SQL Something!

To start off the year we'll look at a very small issue: "The operating system returned the error '5(Access is denied)' while attempting restore".

Fig. 1: Access is Denied