Can't create function: "Incorrect syntax near the keyword 'FUNCTION'."

[BezBIS]

Apologies if this is something really obvious, but I'm rather new to using Tiberius and SQLServer in general. I can connect to a db fine and run fairly complex quieries. However, when I try to create a function I get the error.

Error: Server(
    TokenError { 
        code: 156,
        state: 1,
        class: 15,
        message: "Incorrect syntax near the keyword 'FUNCTION'.",
        server: "DESKTOP-0B5FUJ6",
        procedure: "",
        line: 1
    }
)

This happens with any attempt to create a function or stored procedure. A minimal example of a function I've tried is:

CREATE FUNCTION dbo.ReturnNumber(@Number int) RETURNS int 
AS 
BEGIN 
    RETURN @Number
END;

I could can create all the functions in SSMS, but would rather have them living in code so everything can be completely automated.

Thank you.

[tracker1]

Not familiar with this project... just starting some research... are you doing the function creation as part of a larger script doing more calls to the database? Another thing to note, is there are two call methods in some other SQL Server libraries, where one is query only and the other can do database operations as part of the call... don't know if this is a lower-level thing or not.

[Stefan99353]

I have the same problem when creating functions/procedures. Even simple procedures failed:

CREATE PROCEDURE dbo.select_test
AS
SELECT * FROM customers
;

I used Client::execute() for execution as it seemed to be best suited for creating procedures.

I tried to use Client::simple_query() and it worked. I don't really know TDS, so I can't say if this is a problem with tiberius or intended.

[BezBIS]

I have the same problem when creating functions/procedures. Even simple procedures failed:

CREATE PROCEDURE dbo.select_test
AS
SELECT * FROM customers
;

I used Client::execute() for execution as it seemed to be best suited for creating procedures.

I tried to use Client::simple_query() and it worked. I don't really know TDS, so I can't say if this is a problem with tiberius or intended.

That's really interesting. After posting the bug report I tried using SQLX to create a function and it worked as intended. Looks like, as @tracker1 suggested, I've probably misunderstood the crate and am using the wrong methods.

[pimeys]

execute uses prepared statements, simple_query not. I think creating a procedure with the rpc statement does not work with SQL Server, but I'd like to be sure it is like that and this is not a bug...

[aersam]

Well, it's debatable. execute uses sp_executesql under the hood which seems to have trouble. For me this is a bug in MS SQL Server, but maybe they sell it as security feature.

Simple_query is the only method that does not use sp_executesql and therefore does not suffer from this behavior.