XP_CMDSHELL is one of those features that is turned off by default on SQL Server and needs to be turned on to function. If xp_cmdshell has not been turned on you will get this error when you try to use it. Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 17]SQL Server blocked access to procedure …
Most blogs have a RSS feed of some kind. The RSS feed is usually comprised of XML with a list of the most recent posts. I though it would be handy to try using TSQL to see who has been blogging about what lately. I created two procedures, the first one called httpGET, and another one called LatestBlogPosts. The httpGET …
Now is your chance to ask a question about corruption in SQL Server databases. We will do our best job of answering those questions on our February webcast on database corruption. Do you have a question about the output from DBCC CheckDB? Seeing a strange error in your SQL Server logs? Is your SQL Server sending alerts related to corruption? …
After my post yesterday on the performance implications of concatenating long strings with the + operator and the CONCAT function, today I am following up with a way to speed up concatenation if you need to build really long strings. What the previous show as that the longer the string gets the slower the concatenation is. So instead we declare …
Performance: Faster way to concatenate longer string Read more »
String concatenation in SQL Server can be pretty quick but under certain circumstances it can really slow down. Something to be aware of if you are working with larger varchar(max) values that need to be appended to using concatenation in TSQL. The experiment: Using a VARCHAR(MAX) variable, start with it empty, then in a loop concatenate another string to it …
One of the important settings on SQL Server is the job history log size and the maximum rows per job. Without these set accurately you may not be able to track down job failure issues. These are easy to locate through the SSMS dialogs, but if you are managing many servers you may not want to dig through all the …
Occasionally I need to look up what user the SQL Server services are running as, and when this happens, you can either run a quick query, or wade through the dialog boxes for the SQL Server services top see what user they are running as. I prefer the query, however this only runs on SQL Server 2008R2 SP1 or newer. …
TSQL Query to find the user the SQL services run as Read more »
I was investigating a slow sever recently and discovered almost a million files in the sql server error log directory. This contained job history and backup logs for many years. The error log directory is not a location that I normally check, so I created a script to quickly and easily check how many files are in a SQL Server …
How many files are in your SQL Error log directory? Read more »
As part of the XML and JSON month of blog posts, this post is going to cover how to make changes to XML and JSON in TSQL.We will be using the same sample data from the original post with a data structure holding employee data. Since we are just past the 4th of July (Independence Day in the US), we …
One handy thing when working with JSON or XML is to take part of the data and represent it as tabular data, since everything in SQL Server works better as a table, pulling values out and representing them as a table can be handy. Going back to our original data as shown below from my original blog post that kicked …