Bug in SSMS V17.4

 

If you are using SSMS V17.4 as I am

image

You may encounter a weird error. This can be repeated as follows:

Open a new SSMS window, and connect to any SQL Server instance (I tested on connections to SQL Server 2012/2014/2016/2017), and then execute the following

set showplan_all on;

and then just try to execute it again, or execute any query in the same window, like

print getdate(); — or

select getdate();

You will see a popup window with the error message like the following

Input string was not in a correct format. (mscorlib)

 

image

 

This issue does not exist in SSMS V17.0 and certainly not in earlier version of SSMS application as well.

Advertisements
Posted in SQL Development | Tagged | Leave a comment

Linguistics Crisis In Microsoft Technology

With technology expanding in both complexity and quantity, the task to name each component and functions of a technology product becomes more daunting and thus creates the room for confusion and dubious meaning regarding terms used to explain the technology itself.

As a long time SQL Server DBA, I start to notice this linguistics “mess” probably from SQL Server 2005 when I see terms like ‘securable’, ‘facet’. 

‘Securable’ is, strictly speaking, an adjective, but it is now treated as a noun in SQL Server, while ‘facet’ is a word I bet not many people know or use. To me, it is the same as ‘property’, which is way easier to understand.

Is it a big problem? No, of course not at this time, but can we have better and more meaningful words to replace them? I think so.

The even worse naming convention are found in SQL Server Extended Events terminology. For example, in sql server trace, ‘column’ of the trace is now called ‘action’ of the XE “session”, I just do not get it why this change is necessary or what advantages this change brings other than confusions. There are also numerous names that make you scratch your head. For example, there is an action called “task_time”

image

But I just do not know whether this means a time point, i.e. the time when the current task executes or a duration of time, i.e. the duration that current task executes.

Another example of an action “task_elapsed_quantum”, what does this mean?

image

Actually, there are many other places where reading MS documents (or message without any documentation) can drive you crazy and you really want to give up.

This kind of linguistics mess is not only happening to sql server, but also to other products. For example, .Net Core, .Net Standard, .Net Framework. But to my understanding, .Net Standard is better to be called the “Standard of .Net” to reduce any confusions, i.e. .Net Core is an implementation of the “Standard of .Net” (various versions).

Recently, in Azure domain (“Azure” itself is also very awkward in pronunciation) , Microsoft named one product as Microsoft Graph, again this is a very anti-intuitive name because graph really means graph, something related to picture drawing.

I’d like to define Technology Literature (TL) as the collection of technology documentation about product introduction / features, operation manuals, user guides and best practices etc.

TL is actually an ultimate User Interface between technology practitioners and the technology products. Microsoft does not do very well in this regard. I personally guess this is because most of people in Microsoft are STEM graduates instead of English literature graduates. As such, they usually generate less reader friendly TL works and they do not care. Or worse, some people who come up with those “weird” or “dubious” terms may feel proud of themselves as if this can prove their vocabulary superior. It will be very unfortunate if people think complexity and big-word description means better products (like Extended Events feature in SQL Server). I somehow do have this feeling towards SQL Server XE, which is still less used by DBAs than SQL Server trace (either via Profiler or Server side traces)

The long term impacts of low quality TL is you will lose your “readers” (i.e. IT practitioners) gradually, esp. the new “readers” will simply not “read” your works if there is better TL (from new or old competitors) somewhere.

I think Microsoft as a vendor of producing huge amount of software products (thus lots of TL works) should create a position called Chief Linguistics Officer (CLO) or establish such an office to take care of all these TL related work, like product naming, product feature designing and documentation. They probably can start by first creating a list of words (like 3000 words) that are allowed to use in naming and documentation, then making some other rules to ensure better quality TL.

I just hope, someday, in IT world, there will be a competition for “Nobel Prize Of Technology Literature”. Not until then will CEO pay enough attention to TL.

Posted in Uncategorized | 3 Comments

How to sign a SQL Server CLR Assembly in Visual Studio 2015

It took me quite a while to figure out how to sign a SQL Server CLR Assembly (like a CLR stored procedure / function / trigger etc) in VS 2015.

If you google “sign a SQL Server CLR Assembly”, you will most likely find out content published 5+ years ago when people are using Visual Studio 2010/2012. You will likely to see some instructions like

You can go to Project>project properties> “Signing” tab

With a snapshot screen like the following

image

Fig 1

But such snapshot simply does not exist in VS 2015.

If you check MSDN about signing an assembly in Visual Studio as documented here, you will have no clue either.

“You sign an application or component by using the Signing tab of the project properties window (right-click the project node in the Solution Explorer and select Properties, or type project properties in the Quick Launch window, or press ALT+ ENTER inside the Solution Explorer window). Select the Signing tab, then select the Sign the assembly check box.”

because in a SQL Server project created in VS 2015,  a project property window will be like the following

image

Fig 2

The “signing” button is actually in the [SQLCLR] tab as shown below

image

Fig 3

So the basic procedure to sign a SQL Server Assembly with strong name key file is:

1. In VS 2015, solution explorer, right click the project name, choose [properties], or in the menu [Project] click [xxx Properties], where xxx is the project name.

2. Fig2 widow will popup, click [SQLCLR] tab, and Fig3 will show up, click [Signing..] button, Fig 4 window will pop up

image

Fig 4

3. click the check box of [Sign the assembly], and then click <New…> in the drop-down list as shown below (of course, if you already have a key file, then you click <Browse…> )

image

4. Fig 5 window will pop up, and you can input the necessary info to generate a key file

image

Fig 5

5. Then click OK, and again click OK to close Fig5 and Fig 4, and then in [File] menu, click [Save All].

You are done to finish signing a SQL Server CLR assembly.

Posted in SQL Development | Leave a comment

CLR Stored Procedure Cannot Be Marked as System Object?

It is an open secret that in SQL Server, we can “create a system object” by using an undocumented stored procedure sp_MS_marksystemobject as explained here.

The benefit is that when you put a SP in the [master] database and mark it as system object, this SP can then be accessed in any other databases without using three-part naming convention.

I recently tried to create a CLR stored procedure and put it into [master] and mark it as system object, but I find this is NOT doable, while on the other hand, a pure T-SQL stored procedure has no problem being marked as a system object.

Here are the two simple examples just for proof purpose , 1st is a pure T-SQL.

use master
drop proc dbo.sp_Test;
go
create proc dbo.sp_Test --(@db sysname) 
as
begin
	print 'Hello World'
end
go
exec sp_ms_marksystemobject 'dbo.sp_Test'
go

and the 2nd is C# code for CLR stored procedure

using System;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "sp_test2")]
    public static void sp_test2()
    {
        SqlContext.Pipe.Send("Hello World" + Environment.NewLine);
    }
}

After deploy to [master] database as [dbo].[sp_test2], and run

use master
exec sp_MS_marksystemobject 'dbo.sp_test2';
go

Now if I run the following code in [TempDB], everything is fine though sp_test is actually in the [master] database not [TempDB]

image

However, if I run sp_test2, there will be an error

image

But if I use three-part name convention to run sp_test2 as shown below, everything will be fine just as expected

image

If I check the property of the two SPs

use master
select name, type, type_desc, is_ms_shipped 
from sys.procedures
where name in ('sp_test', 'sp_test2')

select [sp_test]=OBJECTPROPERTYEX(object_id('sp_test'), 'ismsshipped'), 
[sp_test2]=OBJECTPROPERTYEX(object_id('sp_test2'), 'ismsshipped')

I will get the following result:

image

This is an interesting finding, and I think I’d better blog it here for my own reference.

This has been tested in SQL Server 2016 Developer Edition.

Posted in SQL Development | Tagged | 1 Comment

What Is Your Career Investment Portfolio?

Every year, financial advisors will recommend their clients to take a review of their investment portfolio and make necessary changes according to one’s financial reality, like whether you are going to be married, to have a baby soon, to have a new house or to change your career etc, so as to make the best return within the risk tolerance boundary.

Actually, to me, an IT professional should also do something similar annually, i.e. review our professional skills, which is arguably the most valuable asset to a professional, and make corresponding change to their skill investment.

In an investment world, roughly there are three types of portfolios, Conservative, Balanced and Growth, they correspond to different combinations of return and risk.

Portfolio Type Risk ROI
Conservative low low
Balanced medium medium
Growth high high

I think this concept applies to our IT professional skill investment as well, I will use SQL Server DBA skills for illustration later.

I define career investment portfolio as the following (do not pay too much attention to the percentage number, it is just used as an illustration to indicate the relative quantity)

Portfolio Type

Definition

Conservative 100% of your resources (time and budget) on enhancing existing skills and knowledge that are needed to handle the 80% of your daily work.
Balanced 50% of your resources on your existing skills / knowledge that handle 80% of y0ur work, and another 50% on the knowledge and skills that are required for the 20% of your daily work.
Growth 100% of your resources on the knowledge and skills that are required for the 20% of your daily work OR those not directly related to your current work.

 

For example, as a DBA, if I choose Conservative approach, I will invest my resource to deepen my SQL Server internal knowledge, research more on SQL Server Extended events, understand better about various physical/logical operators in Query Plan, practice new T-SQL features etc. work more on service brokers, High Availability setup / troubleshooting, XQuery to manipulate XML, explore more about new features in SQL Server 2016 even if I am only working on SQL Server 2014 (or below) currently.

If I choose Balanced approach, I will also spend time on improving my skills on SSAS/SSIS/SSRS trouble-shooting, development and I may even spend time in master data service. I will also spend time in PowerShell to make my administration more automated or spend time in C# to learn how to create various CLR objects.

If I choose Growth approach, I will spend most of my resources in learning some trendy IT knowledge which may or may not help me as a DBA, such as development in iOS, Android or Linux (SSRS is now supporting mobile report, while SQL Server vNext will run on Linux), or Web development using various Google Frameworks (Google chart is my favourite topic and can be useful for DBAs when we need chart presentations), or other stuff like Docker, Chef, Hardoop or various AWS / Azure services. The new skills may greatly improve a DBA’s productivity in one way or another, but the resources needed and the risk that there is no ROI for your current work is also big.

All the investment targets are dynamic in nature. For example, if 10 years ago, learning PowerShell would be considered an investment in Growth portfolio because there is some risk to its ROI, but now it is mature and is adopted and hailed by the IT community with MS keeping on investing on this technology, so it should be considered as a good investment in Balanced portfolio for DBAs.

In career investment, there is always an opportunity cost. I believe everyone needs to make the decision based on one’s job stability, work requirements, personal aspiration and investment time window (long term or short term).

Posted in Career | Tagged | Leave a comment

SQL Server 2016 Bug in R Integration?

 

I am studying the latest R integration with SQL Server 2016, and I believe I encounter a bug when using sp_execute_external_script. So here is the re-play of the bug;

If I run the following code, I will get an error.

 

-- this will return an error

exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
, @output_data_1_name = N'result' -- the position of this param caused problem
with result sets ((test varchar(100)));

/* -- the following error will occur
Msg 8144, Level 16, State 2, Line 12
Procedure or function  has too many arguments specified.
*/

However, if I put @output_data_1_name in front of @params , everything works fine.

-- this will work fine 
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result'  -- correct position !
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
with result sets ((test varchar(100)));

/* -- this will return the expected result

test
----
hello
world
*/

So in short, it seems a named parameter’s position may cause the stored procedure to fail?

My environment is SQL Server 2016 Developer Edition with the latest SP1 + CU1, as shown below

select @@version

-- returns
---------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) 
	Jan  6 2017 14:24:37 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )
Posted in Administration | Tagged | 1 Comment

A DBA’s Developer Experience and After-Thoughts

After 10+ years of a dedicated DBA, for the past 17 months, I have purposely chosen to work as a database developer. I decided to do this mainly for two reasons:

1. I want to have a career travel to see whether the grass is greener on the other side.

2. I want to dedicate more time to sharpening my scripting skills in PowerShell, T-SQL and C# and some peripheral items such as XML, CSV and Log processing, which are related to DBA work in one way or another.

Now looking back, I feel the time is well spent. I actually find a lot of fun in developing various solutions for business, and some solutions may easily be transferable for DBA work,

In the first 6 months, I was working in an “Entity Framework Code First” environment, it was interesting initially, but later I felt uncomfortable when data model (from DBA perspective) kept changing somewhat on the fly when each developer team was responsible for a set of tables themselves and each team had the authority to make changes to the underlying tables with high-level agreements among team leaders. But I was the person who needed to migrate the data from old system to the new system thus I relied on underlying tables. With frequent changing of tables (from table name change to column changes to even tables merging and dropping), I kept on changing my code, re-test and then change again and again and again. It is not only me to do all this work, my business analyst had to schedule meetings with me to explain to me the changes and what I should do accordingly etc. It was very chaotic to say the least.

The “code first” principle is so different from my previous experience with data model centered approach, and IMHO, “code first” is probably best fit for small projects with few developers, and little dependency or coupling among product features when using the underlying tables..

In the next 11 months, I was involved in a project that dealt with various files, CSV, Excel, flat text files, XML files etc, and I used PowerShell and SQL CLR functions together with t-sql, bcp utility to process these files, it was fun and challenging. Currently in SQL Server 2016, we can run R script inside a t-sql script, I really hope someday, we can run PowerShell script (or C# script) inside a t-sql script too, this way, we will have one common interface, i.e. T-SQL for all sql server related programming tasks, like stored procedures and functions.

What have I learned as a database developer (after being long time DBA) ?

1. There is a bigger and fun world outside. Lots of SQL Server features can only be appreciated thoroughly when you see they are used to solve tough business issues, such as Service broker, encryption, fancy SSRS reports, columnstore index and various T-SQL windows functions.

2. Under deadline pressure, a developer always puts function/feature as first priority, while treating others, like performance, maintainability etc as secondary consideration. So now when I see some spaghetti codes, like in a select statement, each column is a subquery or select * from (select * from) etc, I somehow do not feel that frustrated as before.

My observation is that majority of developers, whose main program language are not SQL, are writing t-sql codes which are far from the expectations a DBA wants in terms of quality. That may explain why some architects whose skills are in C# / .Net may favour “Entity Framework Coding First” approach to handle business projects.

3. The best DBA is usually ignored by management while the best developer always catches attentions from management because DBAs are proud of “no news is good news” while developers enjoy broadcasting an implemented feature or achieving an critical milestone.

At the end of the day, I was asked which role is more interesting, developer or DBA? My thought is:

DBA is more suitable for people who enjoy keeping on polishing a craft, to make it better and better.

Developer is more suitable for people who like to create new crafts, one after another, non-stop.

So I’d say the grass is equally green on both sides. But with SQL Server vNext goes to Linux/Unix, SQL Server DBAs may find themselves in higher demands in market.

I personally believe it is beneficial for a DBA to plunge into a developer role once every few years. This experience will not only enrich one’s skills but also improve one’s understanding on the other side.

Posted in Uncategorized | 6 Comments