[SA 7.5.496 Pro] - $OBJECT()$ macro bug

[SA 7.5.496 Pro] - $OBJECT()$ macro bug

It looks like a bug that had been disposed of ages ago crept back. Both versions, the one that inserts qualified name and the one that should only return the object name yields the item selected from the popup with schema name.

Thank you very much for reporting this issue. I have logged it to our bug tracker.

Are you using $OBJECT(ins_object)$ macro?

No, I tried these:

	Code:
	$OBJECT(ins_schema, table, view)$ $OBJECT(ins_qualname, table, view)$ $OBJECT(table, view)$

They returned

	Code:
	dbo dbo.my_table dbo.my_table

I also tried

	Code:
	$OBJECT(ins_object, table, view)$

now that you mentioned it, and although it produces the desired

	Code:
	my_table

, it is not what the insert macro dialog inserts into editor (it pastes $OBJECT(table, view)$).

Making $OBJECT(table, view)$ non backward-compatible breaks several dozens of snippets. Was this change in behavior accidental or intentional?

I was told that the reversal to the original behavior was intentional because the mentioned fix was causing undesired side effects in several other places. To compensate for that a serious of additional options were added in 7.x releases to provide greater control over what's inserted int other code. But the dialog for $OBJECT(...)$ macro wasn't properly updated, resulting in some behavioral discrepancies. A fix for the dialog is in the works. In the case you described the dialog should have generated

	Code:
	$OBJECT(ins_object, table, view)$

in cases when only object name is required. If ins_.. options aren't explicitly selected, then the behavior is controlled by the state of SQL Assistance -> Auto Complete... -> Always Fully Qualify Object Names, which by default has a value of Only for objects in other schema or database

Ahh, I see. That makes sense.

Are there any other ways to replace all the outdated $OBJECT()$ macros with their correct counterpart except for exporting settings to xml, search-replace and re-import it again? Perhaps you could incorporate that part (the search-replace) into update (and import) process so that it happens automatically when a settings file is imported or a new version is installed.

Another issue. $OBJECT(ins_qualname, table, view)$ used to insert the object name with schema, that is, the result looked like

	Code:
	dbo.mytable

After the last update of 7.5.499 $OBJECT(ins_qualname, table, view)$ no longer qualifies the object with the schema alone but with the database name as well, resulting in

	Code:
	mydatabase.dbo.mytable

which has the undesired side-effect of completely ruining some of my custom made snippets.

I had the misconception that SA Options > DB Options > SQL Server > Auto Complete > Always Fully Qualify Object Names had some influence on this and I was obviously not entirely wrong assuming that because while it simply inserted the database.schema.object for both With schema name and With database and schema names, it actually made some difference setting it to With database and schema names, in which case even simple $OBJECT(table, view)$ macro results turned out to be 3-part qualified.

It looks as if $OBJECT(table, view)$ now behaves as $OBJECT(ins_qualname, table, view)$ had before, and $OBJECT(ins_qualname, table, view)$ yields a fully qualified objectname regardless of the setting mentioned above. Is this change in behavior by design? Are there any other changes to be expected? I already have a large number of snippets in inconsistent state because of the last change, I'd rather not add anything to that chaos :(

I'm also under impressions that the following is incorrect

	Quote:
	$OBJECT(ins_qualname, table, view)$ yields a fully qualified objectname regardless of the setting ... in SA Options > DB Options > SQL Server > Auto Complete > Always Fully Qualify Object Names.

It might be caused by a side effect of some other changes.

I believe that setting should drive name qualification logic, the above behavior is not likely by design. I have logged a bug report.

I have some clarifications for the previous post.

By design the 3-part name is supposed to be used for objects which are not in the current database. The tricky part here is treatment and meaning of "current" database. If in the object popup you select an object from database -> schema - object path, then it would insert 3-part name, it doesn't check if the selected database name equals the current database. You selected fully qualified name, you got it. But if you pick an object from schema -> object path, which is given for objects in the current database only, and the "Always Fully Qualify Object Names" settings don't require to always qualify with database name, then only schema qualification is supposed to be used.

	SysOp wrote:
	If in the object popup you select an object from database -> schema - object path, then it would insert 3-part name, it doesn't check if the selected database name equals the current database. You selected fully qualified name, you got it.

That'd be fine as it'd be expected.

	SysOp wrote:
	But if you pick an object from schema -> object path, which is given for objects in the current database only, and the "Always Fully Qualify Object Names" settings don't require to always qualify with database name, then only schema qualification is supposed to be used.

Agreed. But that's not what happens, it puts the database name right in front of the schema name even if the object was schema.object picked from the popup.

Any hints on when this is going to be fixed? Almost 90% of my snippets are broken because of this.

I was told that there is a fix available in the 8.0.xxx code branch, but that version is not yet stable. I have asked if we can and/or plan on back-porting that fix to 7.5.xxx branch, but didn't get their reply yet.

Thank you very much. Eagerly awaiting then...

What's the planned release date for 8.0?

An early beta version is expected by the end of the month (end of May if all goes well).