Today’s Monday Morning Mistake issue is another painfully common one that we all run into at some point. You’ve got some new code given to you by a developer that’s supposed to be ran against QA (because you’re a good DBA and you don’t grant developers access to anything outside of Development). Part of the code drops some existing tables and recreates them with new schemas, objects, etc. You hit the execute button when the blood drains from your face when you realize the instance you were connected to was a production instance!
You have multiple instances of SQL Server to manage and you want a visual indicator in SQL Server Management Studio (SSMS) to let you quickly know which environment you’re connected to.
Welcome back to another addition of Monday Morning Mistakes series. Today’s issue is one I tend to run into quite often with clients and is an important topic to know about as a database administrator. Without further ado, let’s get to our issue
You have SQL Server database engine installed on a system with other services such as Analysis Services, Reporting Services and/ or Integration Services and you constantly seem to run out of memory. Restarting service seems to fix the issue temporarily but some time later the same problem returns.
Expressions in SSIS are great. They allow you to create dynamic values for all sorts of stuff like variables, connection strings, properties for almost anything, etc. One huge issue that tends to trip up a lot of folks, especially those new to SSIS, is the evaluation of those expressions when using variables.
You create an SSIS variable with an expression but at runtime the expression is not evaluating as expected. Instead the expression value is using the default/static value of the variable.
Make sure property for ‘Evaluate as an Expression” on the variable is set to True. Without it being set to true, the variable will evaluate to the hard set value.
Inspired by common emails and questions I see, I figured I’d do a series of blog posts on common mistakes folks make with SQL Server called Monday Morning Mistakes (or #sqlM3 for short, since we all love quick hashtags these days). These are meant as quick fixes, nothing too comprehensive. Also since I just made up a hashtag, feel free to share your own #sqlM3 tips on Twitter anytime! Without further ado…
Today’s quick issue: Can connect to SQL Server locally but can’t connect from other server or computer.
Quick answer: Remote connections (read also: any connections that are not local) to SQL Server are disabled by default. This behavior is default in SQL Server 2005 and higher. You have to manually enable TCP/IP protocol to instance to allow connectivity. This requires a service restart to take effect.