Posts Tagged ‘sql server’

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050)

Monday, December 12th, 2011

I just tripped over this problem, and despite a fair amount of Googlage, I didn’t find anything that directly resolved my issue. I was trying to use dbMail in SQL Server 2008 to send an email on a schedule which included the results of a query. Doing this should be fairly straightforward, by executing the sp_send_dbmail stored procedure, which is in MSDB:

This query works fine in SSMS, but when run as a SQL Server Agent Job, it fails, with the error

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050)

A few things need to happen to make this work:

1) The account that the task is running as (e.g., SQL Server Agent account) needs to be a member of MSDB. I also granted it rights on the Agent roles, and DBMailUser role, as well as Read, Insert, Connect, Execute, etc.
2) It will also need permissions on the database that you’re trying to connect to query.

I saw a lot of other hints on ways around this, like add a “USE DatabaseName” as part of the expression, but none worked. Here’s what got it working for me.

In the Agent Job setup, you have the option to specify which database to use. I instinctively set this to the database I wanted to query. But alas, this is not right. You need to set it to MSDB, and then update your query to include the @execute_query_database parameter:

After that, things should work.

Turbocharge your SQL development: get more out of SQL Server Management Studio

Tuesday, December 14th, 2010

When SQL Server 2000 became SQL Server 2005, a huge number of things changed. DTS became SQL Server Integration Services, and as part of the package we saw the likes of Analysis Services and Reporting Services. All these additions to the SQL Server family necesitated a change in the suite of management tools – and that meant the death of Enterprise Manager and SQL Query Analyzer, tools loved by everyone, everywhere. They were combined and rebranded as a single tool: SQL Server Management Studio. DBAs and SQL developers cried out in horror – the new tool was, well, new – it was very different and required a change in working practice. What’s more, there was no choice – if you wanted SQL Server you had to use SSMS – there’s no using Enterprise Manager (although you can use SSMS to manage SQL Server 2000.)

The thing is, SSMS actually provides a lot of productivity tools to make your life easier, but they often go overlooked as people are used to working in Enterprise Manager, even now, some 6 years after it was released with two further releases since (SQL Server 2008, SQL Server 2008 R2). So here’s a quick look at some of the things you can do to speed up your development work, without even touching on the core features of SQL Server.

1) Explore the context menus

All items in SSMS have a context menu from the right click, and most, if not all, database objects provide a wealth of ability to quickly script tasks to either a query, a file or the clipboard. For instance, right click a table, click SELECT TO > Query Window and you’ll be provided with a query window with a full list of fields you can select. In SSMS for SQL 2005, the SELECT TOP ROWS option was removed. In SSMS 2008, it’s back in. You can rapidly build store procedures and other scripts using this functionality.

2) Empower the editor – search and replace with regex

Imagine you’ve used tip 1 to output 100 lines of SELECT SQL to a new query window. But you want to refactor it a bit. You could either edit each line by hand (yawn) or use some Regex. CTRL + H opens up the find/replace window.

Example 1

– Find e.g., ,<CaseKey, varchar(50),>)
,\<{[a-zA-Z ]+}, [a-z]+[\(\)0-9]+,\>

– Replace with e.g., ,CaseKey = ”
,\1 = ”

Example 2

”                                          AS [Some Alias Name],

{”}:b+AS:b{\[[a-zA-Z ]+\]},

Square brackets optional
{”}:b+AS:b{[\[a-zA-Z \]]+},

,\2 = ”

,[Some Alias Name] = ”

3) Visual Studio style development . The editor inherits some of the functionality that you can get in Visual Studio. Explore the keyboard shortcuts, e.g., CTRL+K, CTRL + C to quickly comment out a section*.

We also get simple debugging

And in SSMS 2008, the piece of resistance everyone wanted, Intellisense:

4) Zero in on errors

If the VS style debugger isn’t your thing, you easily debug and check the syntax and run any piece of your script at any time, simply by selecting it and CTRL + F5 to check syntax, or just F5 to run it. So if you’re testing a long sproc, you can quickly check individual blocks by selecting each one in turn and hitting F5.

5) Deployment made easy

When deploying a database, questions are always asked… should I backup and restore to the new site, or recreate it by hand, or? Well that question just got answered: use the built in Generate Scripts functionality to quickly create all the SQL required to build the database. Useful for deployment or DR scenarios:

Hopefully some of the tips will make your SQL development more enjoyable.

* Request to the SSMS developers: can we please get more of these quick formats? CTRL + K, CTRL + D would be the obvious one to quickly format an area of code, especially the code created by the visual editor, which is ugly! For now, an online tool will have to suffice.

Quick tip: Use OVER to get row numbers in subsets

Friday, December 10th, 2010

Consider the following. You have a set of data. Within the data are multiple subsets that share an identifier. WIthin your main set, you need to number the subsets for each item within it, e.g.,


ID SubsetID Name Lots more columns…
———- ———–
1 ABC Dave
2 DEF Trev
3 DEF Bob
4 DEF Steve
5 HIJ Jim
6 HIJ Bilbo

and you want something like

ID SubsetID SubsetPosition Name Lots more columns…
———- ———–
1 ABC 1 Dave
2 DEF 1 Trev
3 DEF 2 Bob
4 DEF 3 Steve
5 HIJ 1 Jim
6 HIJ 2 Bilbo

How to achieve this? Using OVER with ROW_NUMBER(). What this essentially does is to partition your data in to the relevant subsets, and then use the window function ROW_NUMBER() to count them:

SELECT id, subsetid, subsetposition, row_number() over (partition by subsetid order by subsetid) as subsetposition, name frøm tblTable


Reporting a product bug to Microsoft

Friday, March 12th, 2010

I needed to update our installation of SQL Server 2008 to include Integration Services so that maintenance plans would run. I have local admin privilege on the machine, but as with most Microsoft related installation tasks, you routinely get so far through the process and you’re hit with the SeSecurityPrivilege error – that is, you don’t have some permission or other. The installation process goes wrong and you have to cancel out of the whole process in order to restart it with an account that has the privilege.

Except in this case, that doesn’t actually work – there’s a bug in the SQL Server 2008 installation mechanise.

SQL Server 2008 installation error

SQL Server 2008 installation error

Clicking Retry fails again because you don’t have the privilege.
Clicking Cancel fails… because you don’t have the privilege.

Granted, it’s a fairly obscure bug, but it’s a bug nonetheless. My only option is to physically kill a process, and I really don’t like doing that sort of operation on production machines which are half way through trying to update the configuration of a live SQL server*.

I figured I’d report the issue to someone at Microsoft. Long story short, after a bit of hunting around, the endeavour was futile (as per this guy and this guy) and I can’t find a single way to report a bug to Microsoft about their arsing software (that doesn’t involve going through direct support channels, paying fees, checking license blah blah blah. I care about this a bit… but not that much.)

I’m sure someone somewhere in Microsoft’s vast array of tech support type people could monitor a “” email address and actually help to listen to its customers. I love my Mac.. I don’t know if a bug reporting mechanism exists as above… but then I’ve never had to do it. [CueFlame] “They just work” ! [/Flame]

* As it turns out, this is actually a serious issue. Manually killing the installation process halfway through means the installation files that it creates aren’t rolled back/destroyed. So when you then go run the installation again… SQL Server thinks you’ve already installed the thing you’re trying to install. So you’re then faced with “repair”-ing an instance which is absolutely fine.

Microsoft software: an example

Wednesday, September 10th, 2008

The “I hate Microsoft brigade” vs the “I love Microsoft brigade” have debated for eons the merits of the company. The argument will rage until you and I are long gone. Personally, I don’t hate Microsoft as a corporation, I’m by no means their biggest fan, but I have no reason to hate them and it’s irrefutable that they created the computer industry as it is today. But I do have issues with some of their software. It’s a boring subject, so I’m not going to bang on about the evils of the Microsoft and laud the wonder that is Apple. It’s a pointless no-win argument – some people like Microsoft, some people like Apple. You choose, and you do what you want.

But here is a good example why Microsoft software has a bad reputation.