SSDT generate script throws an “object not set to an instance of an object” error

Posted on by

The Issue

I created a Visual Studio Database project by scripting the code directly off the server in question.

When I then try to publish the project clicking Deploy –> Generate Script, I get an “Object not set to the instance of an object” error.

I get no other information about the problem

The Investigation

So to work out what was going on I excluded all the stored procedure and function logic from the project and tried to generate the script, and it worked.

The next step was to include scripts back into the project, trying to “Generate Script” every time I added scripts back until it failed.

The problem

CREATE FUNCTION [dbo].[GetSomething]
(	
@date DATETIME
)
RETURNS TABLE 
AS
RETURN 
(
SELECT 1 as column1, 2 as column2
);

A script like the one above causes it to fail to generate successfully.

The solution

The problem is that it does not like the function returning a select statement directly.

Change the code to use a table variable, like so:

CREATE FUNCTION [dbo].[GetSomething]
(	
@date DATETIME
)
RETURNS @returntable TABLE
(
  column1 int,
  column2 int
)
AS
BEGIN
    Insert Into @returntable
    SELECT 1 as column1, 2 as column2

    return;
END

The project will now build