In our production environment, the Database Administrators like to know when a new database is created or dropped. As well they should. They normally use a DDL trigger to send out an email when one of these events occurs. The problem we ran into was that in Windows SharePoint Services 3 (WSS3/SharePoint 2007), the trigger broke SharePoint’s ability to create new content databases! Perhaps the account that ran the procedure didn’t have enough rights to run sp_send_dbmail, but we don’t know for sure. If you know why, tell everyone in the comments.
The way we fixed it was to, instead, create a nightly agent job on the SQL Server that just looked for new database records in sys.databases.
SELECT name, create_date
order by create_date desc
Just add a WHERE clause that filters the databases based on how often you run your job. If any databases exist since the last time you ran the job, send out an email.
If you’re using Windows Authentication for your SQL Server database you’ll need to run SQL Server Management Studio (SSMS) as that Windows account to test queries. SSMS normally runs as the user logged in to Windows, but there is a simple way to run it as the database access account.
Just shift-right-click on a shortcut and select “Run as a different user“. (Windows 7)
You’ll be presented with a Windows login prompt. Log in and SSMS will open. Note that the connection dialog has the new user in the Windows Authentication area
Note: You’re now running the program as a different user, so it probably won’t have access to the files you normally use. My fix was to give the account read/write to the folder where I hold my SQL scripts.
This tip will work for other programs too. Let me know how you used it in the comments below.
When looking at long queries you’ll typically need to remember what data is in one of the tables. SSMS allows the creation of shortcut keys that can be used to quickly look at the first row in a table. You’ll then be able to see the column names and some example data.
In SQL Server Management Studio (2012 shown), create a new shortcut key by going to:
Add the beginning of a query to the shortcut key you like, later on you’ll see how the table name will be appended when the query is run.
“SELECT TOP 1 * FROM”
After you click ok on the dialog, and create a new query window, you’ll be able to select a table name in a query and use your shortcut key. The selection in the query analyzer will be appended to your shortcut query and run.
The screenshots are from SQL Server Management Studio 2012, but older versions have the shortcut key option too. I bet it even works in the old Query Analyzer.
Cool eh? Maybe you like to get the top 200 rows to get a better picture of the table’s data? I’m sure you’ll have tons of ideas on cool shortcuts you can run. Be nice and leave me some ideas in the comments.
If you have intranet sites using Windows Auth that are not fully qualified domain names (fqdn) (like http://companyintranet/) you probably noticed that Firefox constantly prompts your users to enter their windows authentication credentials. You can easily get Firefox to work like IE by allowing automatic logins.
Go to “about:config”
Filter the keys down to “network.automatic”
Edit network.automatic-ntlm-auth.allow-non-fqdn value to true
In a Visual Studio 2012 class file, type “prop” and then press tab twice. There are a few options, but at least check out “prop” and “propfull”. This will create a template getter/setter property for you! When you modify the datatype and the variable names, they’ll update in both. Try it!
Properties are especially useful in Web Development for accessing Query Strings. In my opinion, Query Strings should never be accessed directly, but only through properties. That way you can validate the data! Like this:
public int ProjectId
int temp = -1;
object o = Request.QueryString["id"];
if (o != null)
int.TryParse(o.ToString(), out temp);
There tons of other Code Snippets in Visual Studio that can make development easier. You can see all the code snippets that are currently installed, plus their location on disk, by clicking Tools/Code Snippets Manager. If you’re not using them, you’re not getting the full benefits of Visual Studio!