'Non-schema statement was ignored' warnings

mjacobs

:shock: Some-how one of our developers has managed to edit and check in one of the schema files directly in the source control folder. The end result is a warning message every time we run a compare from source control.

I have identified the exact item that is causing this problem. It is actually one of our stored procedures but what also strange is that we have the same file suffixed with a 1;

dbo.up_kpi_salesmanager.sql
dbo.up_kpi_salesmanager1.sql

In the database we only have dbo.up_kpi_salesmanager.

It looks like the original file dbo.up_kpi_salesmanager.sql is no longer being used as dbo.up_kpi_salesmanager1.sql has more recent history.

I believe that my best cause of action is to stop the warning/error is to edit the offending file directly and correct the content. Another alternative could be to role back the change on the file and then reapply the correct update from the database.

However, before I try this I am wondering if the creation of the dbo.up_kpi_salesmanager1.sql file is expected behaviour and how can I 'merge' the 2 files back to a single file? I guess that there must be a file used by SQL Source Control to link a file to a database object?

-M

Brian Donahue

I think the root of the problem is the numbered stored procedure, since SQL Source Control doesn't support them. It is probably also responsible for creating the "1.sql" file, because failure to write to a script file will often result in a new one being created. You should probably check that the original file is not read-only (in the file properties) and delete the newly-created one.

mattkarp

I get this warning as well. It happens on procs and on some .sql files we have in the "data" folder (putting data under source control) and don't understand what is causing it or how to resolve it. Please help.

Brian Donahue

In a nutshell, it's SQL code that the parser doesn't understand. In the start of this topic, we were able to work out that it was the "numbered" stored procedure. These were supported only in SQL Server 2000 and then deprecated, so none of the Red Gate tools support them. A numbered stored procedure has a semicolon after the procedure, then a number. That allows you to have many SPs with the same name.

I can't tell you want is wrong with your data scripts - I'd need to see their content.