So you just moved to SQL 2012, excellent news! The very next day, someone is running an ad hoc query and copy/paste from the results grid is adding rows when they paste into Excel! The data hasn’t changed, so what is the cause? The answer is most likely that your data has carriage returns in it, and you just didn’t notice before. There is a behavioral difference in SQL Server 2012 that when you copy data from the grid and paste into Excel, the carriage returns are taken into account and data is split into new rows. In previous versions of SQL Server, the carriage returns are not copied as such. The problem appears to stem from moving SSMS into the Visual Studio shell, which took place in SQL Server 2012.

The great news here is that your data is actually cleaner than it was in SQL 2008. Notice that the extra spaces between “test2” and “test3” are now removed. The carriage returns in SQL 2008 displayed as blank spaces, thus the quality of your data was impacted by these even if they did not split your data. Remember, this was only cleansing for a single character that you did not want. At the very least if you are experiencing this, you should probably check for a line feed next (char(10)) and replace that as well. You could also make a general purpose UDF for stripping out all low ascii characters.

This brings us to our next point – Data Quality. Now that you have your workaround, the next step is to get a project together to clean up your old data, and also to clean your data inputs.

Other options here involve some analysis around the process you are using. Should you be running a query and copying the data into Excel? In some cases, other options may be available. For example, this issue does not exist when you load data into PowerPivot. Perhaps a Business Intelligence Semantic Model or PowerPivot Model would be a better solution moving forward.

Finally, feel free to vote up the suggestion on Connect related to this topic. Having too many rows is a much larger deal for most users than some extra whitespace, thus the manner in which this was handled prior to SQL 2012 seems to be the desired output.

If this is happening to you, the good news is, there is a hotfix. I have had this happen on one of my SQL 2012 SP1 VMs, but I haven’t been able to exactly replicate how the issue came about. The issue has more documentation on Connect, and is resolved by requesting SQL Server SP1 CU2.

I wrote previously about some enhancements to the information in the SQL Server Log, whereby you can tell what database failed to be connected to. That doesn’t appear to be the only enhancement that I have stumbled across while working with SQL 2012. When working with unique constraints, you not only find out about a violation, but also the value which prompted this issue is also indicated. This message is indicated as long as you are running 2012 tools, as you will get the feedback when connecting to a 2008 R2 instance as well.

It is common for a developer to have string data that needs to be parsed into a specific format. However, it is even more common that not all of the data in the dataset is able to be converted to the specified format. This leads to SQL Server throwing errors and queries failing unless those scenarios are taken into account – a tedious process of data cleansing. In SQL Server 2012, some new keywords are available to help with converting data types.

Quick example:

select convert(int, 1)
select convert(int,'abc')

While the first statement will succeed, the second will throw an error. You could handle this with some general error handling or with an ISNUMERIC(data) = 1, but those can present issues as well when working with some data sets that you need to cleanse. See if the results to ISNUMERIC(‘2e5’) are what you would expect. ISNUMERIC() appears to interpret this as 2 x 10^5, but conversion to an int does not.

Now, bump up your database compatibility to 110 (Compatibility level must be bumped up to 110 to use the new keywords).

select try_convert(int, 1)
select try_convert(int,'abc')

Aha! The second statement returns NULL, indicating that the data isnt appropriate for your data conversion. This information is quite useful when dealing with string data that can be of many formats.

With SQL Server 2012, the OFFSET clause is introduced. In combination with FETCH, you can construct a query to pull “pages” of information. Of course, this assumes that your underlying dataset is unchanging or you are comfortable with your underlying data set to cause fluctuations in your paged data. See below for one such implementation. Here we are looking for “page 2”, where each page has 3 items on it.

Changing the page # or the # of items per page will change your results, and those can be parameters for your stored procedure. Ensure that your data set is unchanging, or that you are okay with fluctuations in what data is on your pages!

Update: This issue is resolved as of SQL Server 2012 SP1, which can be downloaded here.

After upgrading to SQL Server 2012, you may notice that your SQL Agent Log is sprinkled with “Reloading agent settings” every 90 seconds. This does not appear to be harmful as this is just the automatic registry refresh. However, this can be painful if you have set up log monitoring. Until this is resolved, the workaround as detailed on Connect is to update your registry to disable the auto refresh. The downside here is that if you change any of the default agent settings, you may need to restart agent for them to take effect.