Range Values of DATETIME vs DATETIME2

Came across an error today generated by Entity Framework:

‘The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.’

Typically a datetime2 type can be inserted into a datetime type column provided the value of the datetime2 is between the minimum and maximum values of the datetime type. Continue reading

Database Project Entropy

With every new release of a software product, some features are altered at the request of the majority of users with the aim to improve the product. And generally these changes are correct. Most, but not all. Some are short sighted at best. And if there was one feature change which Microsoft acquiesced to which I just cannot abide by was to remove the automatic creation of the folder structure in Visual Studio database projects. Continue reading

PRINT and SELECT Character Limits

trying something a little different today… writing a post that is the examples. A simple one to start off this method of blogging. Enjoy!

		SET @SQL = 
		'There is a character limit set when outputting the query results to text to 256 character. This message will not print out fully. So the question is:
		"How do i change this?" Well it is quite simplereally: what you need to do is go to go to the menus at the top of the screen, and click on tools ---> 
		options. Then, on the left-hand side, Expand Query Results, and then expand the SQL Server, and highlight "Results to Text". On the right hand side 
		there is an option called "maximum number of characters displayed in each column:. Increase this number (I usually increase to 4000, which is the character 
		limit for PRINT statements... although worth noting that results to grid are capped at 65535 for non-xml data and 2mb for XML) and click ''OK''. You will need 
		to re-open your query windows for this change to take affect.'

		select 'Hello. ' + @SQL

		SET @SQL =  
		'note that this character limit does not apply to a PRINT statement. As stated before, a PRINT statement can be longer. For example, this sentence is pretty long*
		and yet it prints out no issues. 
		( * In fact, the total length of this sentence is actually: '

		DECLARE @SQLLEN nvarchar (3) = LEN (@sql)

		PRINT 'Hello again. ' + @SQL + @SQLLEN +')' +CHAR (10)


What You Need to Know About SSAS Processor Affinity

I’ve been looking into using processor affinity on our SSAS instances to help control the workload on our servers. Sometimes a process can run which will grab as much CPU resource as it can, which means that queries on the cubes cannot run, grinding the front end to a halt. Unlike the db engine, there is no resource governor for Analysis Services. Sure you can set the min/max memory simple enough, but setting limits on IO and CPU is a more complex task. Continue reading

Creating Snapshots of a Database Script

Creating snapshots for databases is one of those features that is reserved for the Enterprise edition of SQL Server. A database snapshot is a read only transactionally-consistent copy of a database taken from a point-in-time. To create a snapshot of a database you need to execute a T-SQL statement; there is not an option through the UI. It is much the same as a CREATE DATABASE statement, but with a few differences:

  • you do not need to specify the log file
  • the files that you do create do not need to be of any particular file type (they are sparse files, a feature of NTFS file systems)
  • the database snapshot needs to reside on the same instance of SQL as the original database.

Continue reading