Dynamic SQL Doesn’t Have To Be That Hard

Published On: 2017-08-21By:

sign that says dynamic on itThere’s a lot of misunderstandings about dynamic SQL out there. I have heard this from clients on occasion, and I see it from other consultants on their blogs. If done correctly, dynamic SQL is not  difficult to use or troubleshoot.  Let’s look though some of the complaints that I’ve seen.

If you’re using NVARCHAR strings, you need to prefix all of your string concatenations with N, or you could end up silently truncating your final product.

Yes, if you are using NVARCHAR you do need to prefix everything with N so that SQL Server knows that all your strings are Unicode. There is no risk of truncate your final product.  If you forget to put the N in front of a string when concatenating strings together, the only problem you will have is that a question mark where will appear you should have a Unicode character. You won’t suddenly end up with a shorter string than you were expecting.

If you use CASE to branch concatenation paths, it can also silently truncate a string.

Yes,  CASE can do this, if you don’t put an ELSE block in your CASE expression.  If you don’t have the ELSE block in your CASE expression, then your code could return NULL from CASE. Unless you’re changed your CONCAT_NULL_YIELDS_NULL setting from the default, this will return a NULL value, which is the expected result.  I am sorry, but you don’t get to complain that SQL Server is doing what you told it do.  Adding an ELSE block that returns a blank string so that your statement looks like CASE WHEN … ELSE ” END isn’t all that hard.

Figuring out how many single quotes you need to properly quote things is awful, sometimes.

Generally speaking, once you are in a dynamic string instead of a single quote you just need two single quotes for each single quote that you want.  Unless you are using dynamic SQL to generate dynamic SQL it shouldn’t be that hard. If you are using dynamic SQL to generate more dynamic SQL (which I’ve done) then you’ll want to stab yourself in the eye.

PRINT is limited to 8000 characters, RAISERROR even fewer.

Both of these statements are true. Keep in mind that PRINT shouldn’t be used to return information to a user;  it just returns it to the messages tab in SSMS. In an application that output from PRINT isn’t displayed to the user at all (even though it is sent to the client, so it still has to travel over the network).  If you need to get a warning or error to the user RAISERROR would be the way to do that, and 2048 characters should be enough space to do that in, as that’s a lot of text to send back to a user. Especially as most users won’t even read the error message.  THROW, which was introduced in SQL 2012,  has the same size limitation as RAISERROR.

If you’re using sp_executesql, it’s a real chore to get the variables as the query ran without additional logging.

Passing variables into sp_executesql is pretty straight forward. You simply declare than as an input parameter, which is the second parameter for sp_executesql. Then you pass them in as named parameters to sp_executesql and use then within the dynamic SQL as needed.  The nice thing about dynamic SQL is that your can pass in parameters that you aren’t even using, so if you remove parts of the code that use the parameter you can still pass it in to the dynamic SQL and SQL won’t care.  As for not being able to log what parameters are being passed in to the query, who cares? Logging the queries that are passed in introduces so many potential security issues not the least of which may include storing PII information in an unencrypted format that I’d never recommended doing this. Not to mention that this is adding additional overhead and risk to the system for not useful benefit.  If you need to see how the query procedure dealt with the variables before running the dynamic SQL then

Concatenating non-string data requires CAST/CONVERT.

Yeah, this is a real pain in a strongly typed language. Just like in C, C++, C#, etc. Also stop stringing non-text fields together. You simply pass them into dynamic SQL as variables and handle like you would with normal variables.  There are very few cases when I’d want to handle dates and/or numbers (as an example) in this way.

Sometimes surprise NULL concatenations leave you with empty strings.

Yeah, that happens when you concatenate NULL and something together in SQL Server. This is by design.  If you don’t want this check for NULL using ISNULL, COLLASE, CASE or change the CONCAT_NULL_YIELDS_NULL setting.

In summary, I’d have to say that Dynamic SQL really isn’t all that hard to work with.  If you remember the rules of strings and NULLs then working with dynamic SQL isn’t pretty straight forward.  Dynamic SQL makes lots of sense to use in order to reduce the complexity of SQL Statements, and simple SQL statements are easier for SQL to execute.

Denny

 

The post Dynamic SQL Doesn’t Have To Be That Hard appeared first on SQL Server with Mr. Denny.

I really hope that Zuckerberg doesn’t run for President

Published On: 2017-08-16By:

zuckerberg tweeting he might run for POTUSLast week Mark Zuckerberg made a couple of astonishing announcements.  The first is that he’s no longer an atheist.  The second is the one that’s a little more important to the rest of the world, that he believes “running for president [is] very important”.

Now I can’t tell you how opposed to this I am.  So much so that I’m going to talk about politics on my technology blog, knowing that it’ll be picked up by my companies websites (among others).

Zuckerberg is completely unqualified to be the President of the United States that we live in today.  He’s had one job, where he’s the boss.  He has no work experience outside of people doing whatever he tells them to.  He has no experience in any sort of public service or government.

He’s started a non-profit to help with education, where he’s still the boss and things will still be done his way.

He’s go no idea how to negotiate for the betterment of others. He’s got no idea how to negotiate anything that we know of, as he’s got people for that.

He’s basically still in his 20s but with unlimited money, and being President seems like a nice thing to do to impress the wife’s parents.

We currently have a President with no political experience, who’s never had a job where he wasn’t the boss (and I’d argue never had a job at all) and who has no sense of reality or real life. Remember a couple of months ago when Zuckerberg went on tour all around the US to learn what “normal” people did?  If that wasn’t an attempt to look more like a normal guy, getting his picture taken on a tractor for example, I don’t know what is.

If Zuckerberg wants to do something useful in the next presidential election, he should give a billion or two to whatever liberal candidate he wants to support (I’m assuming he’s a liberal as he was atheist and now he’s apparently Buddhist according to one article I read) and leave the politics to the professionals. We’re trying this whole let someone who’s a business man (I’d argue that Zuckerberg isn’t a business man) be the President and it’s going about as expected.

Denny

The post I really hope that Zuckerberg doesn’t run for President appeared first on SQL Server with Mr. Denny.

An Unusual R Services Problem

Published On: 2017-08-11By:

I have had the good luck of having a customer was onboard with SQL Server 2016 very early—like we started testing in March of 2015, and went live in August of 2015. In fact, their home directory refers to vNext instead of 2016. This customer also adopted what felt like most of the new features list. Temporal tables, columnstore, PolyBase, and R Services amongst other features. Anyway, we had R up and running, and it ran for a while.

Recently, and unfortunately I don’t have an exact date on when this started failing (though it was around service pack 1 install time) with the following error:

Error
Msg 39012, Level 16, State 1, Line 10
Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime.
STDERR message(s) from external script:
 
DLL ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1601\MSSQL\Binn\sqlsatellite.dll’ cannot be loaded.
Error in eval(expr, envir, enclos) :
   DLL ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1601\MSSQL\Binn\sqlsatellite.dll’ cannot be loaded.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
STDOUT message(s) from external script:
 
Failed to load dll ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1601\MSSQL\Binn\sqlsatellite.dll’ with 182 error.

I troubleshot this with some colleagues at Microsoft and we weren’t able to resolve. We tried a couple of different approaches including reinstalling CU1, but all to no avail. Yesterday, I got on a call with a couple of folks on the product team to try an isolate the problem. We looked at binaries and timestamps and it looked like everything matched up. Then, my friend Arvind Shyamsundar (b|t) suggested we run procmon on the server.

image

There Arvind noticed these odd calls to sqlos.dll in the shared directory. We then looked at add/remove programs and found the following item installed:

Screen Shot 2017-08-11 at 8.19.38 AM

The T-SQL compiler service which was a legacy of CTP 2.3 was there, and as soon as we uninstalled our problems went away. So, if you happen to be running on a server that you’ve upgraded since very early versions of SQL Server 2016, you may see this issue.

Just to give credit to Microsoft and the current SQL Server install process, this server has had nearly every release of SQL Server 2016 on it (we’re behind a couple of CUs), and this is the only issue we’ve had. Thanks again to Arvind and UC for solving this tough issue.


Analyzing Your Dump Files

Published On: 2017-08-08By:

I’m blogging about this, because A) It’s something really awesome that the SQL Server team built and B) it seems to have terrible SEO, because it took me like three google searches to find the page. With the introduction of SQL Server Management Studio 17, the Tiger team at Microsoft built a plugin that allows you to debug and resolve memory dumps you may have encountered during otherwise normal operations. This is really awesome, as it is something that usually requires a support case with CSS.

For those of you wearing aluminum hats, this does require you do upload the dump file to Azure, where it is analyzed for free (as in beer) on Microsoft’s software. You can even choose your region if you have data provenance concerns. And according to this blog post the memory dumps are stored in accorded with Microsoft’s Privacy Policy.

You will need SSMS 17 for this, as well as to install the plug in, which you can get here.

image

After that you can quickly get feedback on your dumps. Microsoft have even built an API, so if you want to built something automated to upload your dump files using Python or PowerShell you can.


1 2 3 338

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.