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.