I'm running SQL Anywhere 9.0.2.3249. When saving a stored procedure or function to the database, SQL Anywhere applies it's own formatting to the code before storing it. The issue I'm having is that some databases convert the case of domains and some do not. For example, if I have code like this:

DECLARE @my_variable INTEGER;

It will be saved like so, on all databases I've tried it on:

declare @my_variable integer;

This is fine. However, I'm running into an issue with a few domains. It appears to be just system-supplied domains (money, datetime, image, etc) and not user domains or the base data types (integer, double, decimal, etc). Of course, I verified that they were indeed all lowercase in each database ;)

For example, the "money" domain is being treated differently by different databases which I thought were configured the same way. Here is the input:

DECLARE @my_money MONEY;

The result on one database is all lowercase. The result on another database has "MONEY" still uppercase. My question is- what determines this behavior? I tried searching the manual for database options which could have something to do with this, but I haven't found any.

These databases are involved in SQL Remote replication, so I have tried issuing the updates via PASSTHROUGH as well as manually saving the procedures on the databases using iSQL and Sybase Central.

I should provide a little background on why a silly little thing like this is an issue for me- I am creating a database schema validation tool for use in our Continuous Integration environment. The idea is that I take any two databases, create full schema DDL of all the objects, then compare them and create a report of the differences.

Despite the fact everyone knows schema changes, which include 700 procedures in our case, need to be changed via PASSTHROUGH, you'd be surprised at how many times people forget.

I considered using dbunload, but it was going to be more of a hassle to parse everything out of the resulting reload.sql reliably than it would be to simply create the DDL by hand.

PRE-POST LAST SECOND REVELATION: So, I thought to myself right before I posted this- "Hey, wait, maybe I should check to see if Foxhound lists something under 'Curiosities'..." And I saw this:

SET OPTION PUBLIC.Preserve_source_format = 'OFF'; -- different from default 'On'

Manual Page

I was told 2 and a half years ago that such a thing didn't exist. OK, so maybe I should turn this on and pull the procedure definitions from the system "source" column :) But out of curiosity, my original question still stands.