Database names can get out of sync. After you move a database from staging to production and back again, or if you’re pulling a backup from one server to put on another server, the names can get confused in all…
Category: SQL
Use SQL to Check for a Field before using Alter Table
Wow, what a boring headline. But this is a quick tip. This is especially helpful when creating a DotNetNuke module and you want to run an Alter Table command on a table. You don’t want the install script to fail…
Restoring a Multi-File Database
Recently I was trying to restore a standard SQL Server database from .bak file and this error came up: Here are the steps I took to overcome the error 1. If one doesn’t already exist, create a new empty database…
SQL Server says: Saving Changes Not Permitted
If you’ve run into this error then you’ve probably recently set up a new installation of SQL Server Management Studio. You then made a change to a table and to your horror this error popped up: Frustrating, isn’t it. But…
SQL Server: Group records by Week
Quick code snippet to group a recordset by week. In this case we are summing hours worked per week. SELECT DATEPART(YEAR,myDateField) AS ‘Year’, DATEPART(wk,myDateField) AS ‘Week #’, MIN(DATEADD(wk, DATEDIFF(wk,0,myDateField), 0)) AS ‘Week date’, SUM(COALESCE([Hours],0)) AS ‘Hours’ FROM…
SQL – Creating and Dropping Temp Tables
I run into this all the time but can’t ever remember the syntax. First, check to see if it exists and if it does, drop it. IF OBJECT_ID(‘tempdb..#MyTempTable’) IS NOT NULL Drop Table #MyTempTable Second, build your table. The “#”…
Orphaned SQL Server Users
The Scenario: You’re working on a SQL database locally and when you restore the database on the production server the login no longer works. The user has been orphaned. Solution #1 – Delete and recreate the user from scratch Solution…
Care and Feeding of SQLParameters and SQLHelper
I chased my tail for hours on this one, but finally found the combination that works. Here’s the basic layout Function GetProjects(ID as int64, Name as string) Dim arParams As SqlParameter() = New SqlParameter(2) {} arParams(0) = New SqlParameter(“@ID”, IIf(ID…