SQL 2012 Copy/Paste to Excel Splits Rows

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.

Example:

declare @t table(col1 nvarchar(20))

insert into @t(col1)
values('test' + char(13) + char(13))

insert into @t(col1)
values('test2' + ' ' + char(13) + char(13) + 'test3')

select *
from @t

When you copy these results from SSMS in SQL 2008, and paste into Excel 2010 or Excel 2013, you will see 2 rows.

pic1

However, if you do the same using SQL 2012, you will have 6 rows. As you can see, the data has been split by the carriage returns.

pic2

How do we resolve this issue? No…you don’t have to go back to SQL 2008. For starters, you can clean this data in your query by simply replacing the carriage returns as such:

declare @t table(col1 nvarchar(20))

insert into @t(col1)
values('test' + char(13) + char(13))

insert into @t(col1)
values('test2' + ' ' + char(13) + char(13) + 'test3')

select 
	replace(col1, char(13), ''),
	replace(replace(col1, char(13), ''), char(10), '') --get rid of line feeds too
from @t

This solution produces the following:

pic3

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.

Advertisements

SQL Server 2012 SP1 – MsiInstaller is going crazy!

Woah! I just installed SQL Server 2012 SP1 and MsiInstaller is attempting to reconfigure management studio constantly!

Event Viewer: Windows Installer reconfigured the product. Product Name: SQL Server 2012 Management Studio. Product Version: 11.1.3000.0. Product Language: 1033. Manufacturer: Microsoft Corporation. Reconfiguration success or error status: 0.

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.

SQL 2012–Unique Constraint Violation Reporting

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.

For example:

create table #temp
	(
	id int,
	constraint pk_temp primary key clustered
		(
		id
		)
	)

insert into #temp(id) values(1)
insert into #temp(id) values(1)

Error:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 11

Violation of PRIMARY KEY constraint ‘pk_temp’. Cannot insert duplicate key in object ‘dbo.#temp’. The duplicate key value is (1).

The statement has been terminated.

While you may still have to go back and group the data up to see what all of the causes of the violation could be, this is quite a nice enhancement to be able to do a quick spot check of the data.

TRY_CONVERT() and TRY_PARSE() for Data Conversions

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.

Implementing Paging in a SQL 2012 Stored Procedure

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.

/* set up a test table */
	declare @temp table(id int not null)
	insert into @temp(id)
	values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

/* set your params */
	declare @page_number int = 2
	declare @per_page int = 3

/* determine the offset */
	declare @offset int = (@page_number * @per_page) - @per_page

/* select the page of information */
	select
		t.id
	from @temp t
	order by
		t.id
	offset @offset rows
	fetch next @per_page rows only

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!

Reloading Agent Settings in SQL Agent Logs

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.

Go to registry on your SQL Server machine and navigate to corresponding SQL Instance registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL11.YOUR_INSTANCE_NAME\SQLServerAgent
Change registry settings called “AutoRegistryRefresh” from 1 to 0.