My work recently upgraded SQL from SQL 2008 R2 to SQL 2014 and I switched to Visual Studio 2017 from VS 2008. Everything went smoothly…not! The SSIS packages wouldn’t open. These are the specific roadblocks I ran in: Things to try when nothing is working (for the specific combo of VS 2017 and SQL 2014 […]
The transaction logs on my databases tend to grow a lot. There are two things that can be done to force shrink them (only if the automatic log truncation that the server does isn’t doing it for you). Truncate the transaction log entries by running a backup but telling SQL you only want to […]
When you create a database, if you’re not careful, it automatically creates the database in a default location. If you don’t want that — due to space or company policy issues — you can move the database after it’s been created. Note: The sample scripts below were written for SQL Server 2008 R2. First, detach […]
DECLARE @Date DATETIME SET @Date = GETDATE() SELECT Dateadd(d,-DATEPART(d,@Date),@Date)
Sometimes, you need to do things like get the 15th of the month. This is the code for getting SQL to return the xth day of the month: DECLARE @xthDay INT, @date DATETIME SET @xthDay = 15 SET @date = GETDATE() SELECT @date – DATEPART(dd, @date ) + @xthDay (e.g. to return the 15th of the current month:) SELECT […]
Need to modify an old Access application in design mode (to edit code/queries, etc?) To open an Access application in design mode, hold down the Shift key and double click on the Access application.
1) If you are restoring a database backup created on another server, the user logins may no longer work. That’s because the SIDs are different. Fix it this way: USE EXEC sp_change_users_login @Action=’update_one’, @UserNamePattern=’sqlLoginName’,@LoginName=’ sqlLoginName ‘; Source: Technet Blog 2) Make sure you are restoring “database” and not “filegroups”. 3) Make sure you are backing […]
HTML5 has decided to deprecate the classic anchor tag, <A NAME=”anchorname”>. What’s the replacement? An already existing functionality supported by most browsers (including IE from at least version 5.5!) Use the “id” attribute of any element in the document as an anchor. So clicking <a href=”#myanchor”>Jump here</a> will jump to the first element in the […]
This post will be updated occasionally How to format datetime & date http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ Script: Get the most recent Friday’s date SQL http://stackoverflow.com/questions/5998084/get-the-most-recent-fridays-date-sql Script: Get the last day of the month: SELECT DATEPART(DD,DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE())+1,0)))
So you have a job in SQL Agent that executes a stored procedure in one of its steps. How do you get the agent to see that the sproc failed? Use Raiserror with a priority higher than 10 (that is, 11-19). Otherwise, SQL Agent sees the failure, but doesn’t consider the step as failed. Sample […]
Random IT Bytes 