SSDT generate script throws an “object not set to an instance of an object” error
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