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

[sql]
CREATE FUNCTION [dbo].[GetSomething]
(
@date DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT 1 as column1, 2 as column2
);
[/sql]
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:

[sql]
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
[/sql]

The project will now build