Integer Types
There are many different ways to store integers in SQL, how do you know which is the best?
Column name or number of supplied values does not match table definition
I came across this familiar error recently when writing some scripts. Normally I see this error when doing an insert, and the number of columns you’ve specified doesn’t match the number of values you’ve provided. But this time it was in an UPDATE script.
Asking for Help
I always think it’s a great idea to ask for help. When I was a kid, my parents taught me that it’s always a good idea to ask for help – the worst that will happen is they’ll say no. In IT there is too much to learn so I always think it’s a great idea […]
Can you use a case statement as part of a join?
I had a question yesterday about whether or not you can use a case statement as part of a join condition. I was pretty sure the answer was yes, but we can always just run a simple test to prove it. It’s a pretty simple test to setup, I’m going to create 2 tables, populate them […]
Run SSMS as a domain user on a different domain
Like a lot of companies that use Windows, my PC is connected to a domain and this is great when I’m connecting to the companies SQL Servers using Windows Credentials in SSMS because it “just works”™. But sometimes I need to VPN to another domain and connect to SQL Servers on the other domain using Windows Credentials. […]
Cleanup Backup History
While looking at long running queries today I noticed that one of our worst performing queries was a monitoring tool that checked when the last backup ran. SELECT database_name = DB_NAME(DB_ID([database_name])) , type = [type] , backup_start_date = MAX([backup_start_date]) , backup_finish_date = MAX([backup_finish_date]) FROM msdb..backupset WHERE DB_ID([database_name]) IS NOT NULL GROUP BY DB_NAME(DB_ID([database_name])) ,[type] I had […]
CDC Jobs missing after Database Restore
We use Change Data Capture (cdc) in our production environment on a few tables so we can do some asynchronous processing of data. Our developers have a pre-deployment environment which is a nightly restore of our nightly backups (this has an added bonus of testing our nightly backups). Usually the testers don’t care about testing the CDC […]
Failing Mirror Backups
Failing Backups! Is there anything more worrying for a DBA? Recently we have been having multiple failures a week for our full and differential backups. We use Ola Hallengren’s backup scripts, and unless you are using a 3rd party product, you should be using Ola’s scripts as they are amazing and so simple to use. We […]
Using named pipes
We run Analysis Services on the same server as an asynchronous copy of SQL (mainly for licensing reasons). We have been having problems when processing the cube that it drops the TCP connection to SQL. It was suggested that we use Named Pipes instead of TCP connections. We don’t want to turn off TCP completely, so […]
Replicating Jobs Between SQL Instances – Disable Selected Jobs
This is the final optional step in replicating jobs between SQL instances in an AlwaysOn Availability Group. We use SQL Reporting Services to generate reports for the business. Some of the reports are scheduled to run by business users. When you schedule a job in SSRS it creates a SQL Agent Job. We want to […]