When writing complex sproc’s in T-SQL you can sometimes run into the problem that IDEs and other tools, while trying to parse the query, are unable to realise that the query actually works. This is because they try and interpret the query to get the metadata (the format of the result, rather than the result itself), rather than execute it – not such a bad thing since you don’t want an IDE to execute a sproc willy-nilly, which could result in all sorts of side effects.
The main place where developers will encounter this is when trying to configure a TableAdapter (or something) that uses temp tables.
Consider the following sproc:
CREATE PROCEDURE [dbo].[DoSomething]
AS
CREATE TABLE #MyTable (Id int, Name nvarchar(50))
INSERT INTO #MyTable
VALUES(1,'Name1')
SELECT * FROM #MyTable
Executing this procedure via SQL renders the expected result as follows:
(1 row(s) affected)
Id Name
----------- --------------------------------------------------
1 Name1
(1 row(s) affected)
However if you try and create a TableAdapter in Visual Studio using the same sproc, you get an error that looks something like this:
The short solution is not to use temp tables, but rather table variables – which for lots of other reasons is arguably better. The altered sproc would then look like this:
ALTER PROCEDURE [dbo].[DoSomething]
AS
DECLARE @MyTable table (Id int, Name nvarchar(50))
INSERT INTO @MyTable
VALUES(1,'Name1')
SELECT * FROM @MyTable
That is fine, but occasionally you may need to use temp tables and developers land up creating ‘permanent’ temp tables or some other approach. The question remains, how can we force Visual Studio to ignore the metadata and actually execute the sproc? In some cases temp tables may be unavoidable – particularly if you are using someone else’s sproc. The quick solution is to use the SET FMTONLY statement which when turned off would seem to return data as well as metadata to the client. The SQL Books say the SET FMTONLY ‘Returns only metadata to the client.’ I am making the assumption therefore that setting it to ‘OFF’ returns something else as well – I suspect it is the data.
So, to use a sproc that uses temp tables in Visual Studio, the sproc could be changed to:
ALTER PROCEDURE [dbo].[DoSomething]
AS
SET FMTONLY OFF
CREATE TABLE #MyTable (Id int, Name nvarchar(50))
INSERT INTO #MyTable
VALUES(1,'Name1')
SELECT * FROM #MyTable
… making Visual studio happy.
Interestingly, SSIS (SQL Server Integration Services) has an additional quirk. Firstly, if you use a temp table without turning FMTONLY to OFF, and write a source query of ‘EXEC DoSomething’ you receive an error message ‘This SQL statement is not a query’ – SSIS doesn’t even parse your carefully constructed SQL. You know that
- It works in the query analyser
- It is a query
- You are more intelligent than your PC, so if you know it is a query then it must be a query.
The best way to convince SSIS that the query is actually a query is to insert the SET FMTONLY OFF line into the sproc (without changing the source query that SSIS uses – see it was a query!). You may think that the solution is the same as with Visual Studio (use table variables), but SSIS was written by some other development team and has a different attitude and personality.
If you execute a sproc with table variables or temp tables with FMTONLY OFF you get something like the following:
If you look carefully, the ‘Executing’ successful (is a valid query) but the ‘Pre-Execute’ fails. I tried to turn off the pre-execute as an option in the designer with no success. The quick answer is to add SET NOCOUNT ON to the sproc, which for some reason lets the pre-execute run successfully. I am not sure why, the SQL Books say that NOCOUNT ‘Stops the message that shows the number of rows affected by a Transact-SQL statement from being returned as part of the results’, which doesn’t seem relevant to pre-execute – but it does work. The query would then be changed to:
ALTER PROCEDURE [dbo].[DoSomething]
AS
SET FMTONLY OFF
SET NOCOUNT ON
CREATE TABLE #MyTable (Id int, Name nvarchar(50))
INSERT INTO #MyTable
VALUES(1,'Name1')
SELECT * FROM #MyTable
Or, if using table variables:
CREATE PROCEDURE [dbo].[DoSomething1]
AS
SET NOCOUNT ON
DECLARE @MyTable table (Id int, Name nvarchar(50))
INSERT INTO @MyTable
VALUES(1,'Name1')
SELECT * FROM @MyTable
In summary:
- Rather use table variables than temp tables
- When using temp tables use SET FMTONLY OFF
- When using SSIS, use SET NOCOUNT ON
- If SSIS says your query is not a query it may be trying to fool you.
Simon Munro
