August 1, 2019 at 7:04 am
Hi
I'm troubleshooting with profiler on a SQL 2008 box.
When I capture the execution of one of the stored procedures, which is initiated by an ODBC connection form a application server, it shows in profiler as
EXECUTE update_address "", "12", "Street 1"
If I copy this and run it in SSMS, I get he error
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
By adding a space between the quotes it executes fine. I know this stored proc has been running successfully for years. Is this a quirk of profiler? Has anyone got a good explanation of why this runs ok from an ODBC connection with no spaces in the empty value?
Cheers
Alex
August 1, 2019 at 8:23 am
Originally, when SQL Server was released, you could use both singe quote(') and double quote(") as string delimiter. However, when SQL was standardised, it was decided that only single quote would serve as string delimiter, and the double quote was to be used to quote identifiers, so that you could have table or column names with spaces or other interesting characters in them. (On SQL Server, we commonly use brackets [] instead.)
Microsoft started to address this in SQL 6.5, where they introduced the command SET QUOTED_IDENTIFIER ON, so that you could use the double quotes to quote identifier. With SQL 7 they took it one step further and made that the default for connections from newer clients.
Apparently, this client is running with legacy settings, that is, it has QUOTED_IDENTIFIER OFF. And judging from the commands it produces, it seems to need it... Running with this setting in the OFF position can cause problems, because some features requires this setting to be ON, for instance filtered indexes. However, as long as the application only runs stored procedures, there are no problem, because this setting is saved with the stored procedure, so the procedure itself may be running with QUOTED_IDENTIFIER OFF. (Nevertheless, the above is still bad, because it indicates that the application builds EXEC statements as strings rather than using RPC and passing parameters. Which means that it is open for SQL injection.)
Thus, you could run the command from Profiler as-is from SSMS by first running SET QUOTED_IDENTIFIER OFF, but it is better to replace the double quotes with single quotes.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply