i came across a compatibility issue today, as a customer upgraded from Windows XP to Windows 7.
The (12 year old code) is calling a stored procedure on the SQL Server called
ai_nextid
Except that when it calls the stored procedure it is using the name:
ai_nextid;1
Yes, with a ";1
" appended. Apparently the SQL Server driver in Windows 95, Windows 2000, Windows XP, and possibly Windows Vista, are fine with this specifically added suffix. But the SQL Server ODBC driver in Windows 7 is different, and causes the error:
General SQL Error.
[Microsoft][ODBC SQL Driver][SQL Server]Could not find stored procedure 'ai_nextid;1'.
[Microsoft][ODBC SQL Driver][SQL Server]Indicator variable requried but not supplied'.
With native error 2812.
This brings up 4 questions:
;1
to the end of the stored procedure name? (what does it accomplish)The last two questions would probably be the same, since if they document it, they would justify it.
No, you should end your procedure with RETURN . The GO is really meant to separate commands in a sql script.
BEGIN and END are used in Transact-SQL to group a set of statements into a single compound statement, so that control statements such as IF … ELSE, which affect the performance of only a single SQL statement, can affect the performance of the whole group.
see CREATE PROCEDURE (Transact-SQL) SQL Server 2008 documentation
--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] <<<<<<
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
;number
An optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement.
Note:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.
you can use this system view to find all of these and begin to rewrite them as separate procedures:
sys.numbered_procedures (Transact-SQL)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With