November 13, 2015 at 11:23 am
I am trying to setup a globally available function, so that when user's add data I can strip out our domain name from their login. Everything works fine if the function is defined in the same schema as the table, but I only want to have one version of this function, which will be in a separate database from the tables we're building.
This is what I get when the function is in another schema (and I've granted EXECUTE to PUBLIC for now on the function, just to try & get beyond what I think is just a syntactic issue):
use DEMO
go
create table mytab
(code varchar(1) not null,
description varchar(30) not null,
create_id varchar(30) not null default utilities.dbo.func_utl_strip_domain_from_login(original_login()),
create_date datetime not null default getdate())
go
Msg 128, Level 15, State 1, Line 5
The name "utilities" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
When I create the function in the same schema as the table, then I get no errors:
create table mytab
(code varchar(1) not null,
description varchar(30) not null,
create_id varchar(30) not null default dbo.func_utl_strip_domain_from_login(original_login()),
create_date datetime not null default getdate())
go
Command(s) completed successfully.
November 13, 2015 at 1:55 pm
Never tried this, but try creating a SYNONYM for the function in the same database as the table and then reference the synonym in the constraint.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2015 at 2:31 pm
Ooo, that's pretty creative, actually. But it fails with the following error:
Synonyms are invalid in a schemabound object or a constraint expression.
You suggestion made me realize that I can probably create a local function which points to the shared function in the other database, for now. But I'd rather not have to add even that piece if possible.
November 14, 2015 at 9:24 am
I don't think it is possible to reference a database's/catalog's objects within another database's object definitions (even if the other database/catalog is TRUSTWORTHY), but I am always willing to be proved wrong :).
I don't think this is possible because databases would have to share their domain of objects, and yet a database is supposed to persist/manage objects within its own domain (a database's modules/code is a different matter, in that a module is itself a managed object). If this were theoretically possible, when the utilities database went offline, all references to it, within all other databases, would fail (including all tables that theoretically had used the utilities database's object domain for a DEFAULT constraint). The utilities database would become a single point of failure - perhaps not a good thing (and definitely not manageable by other databases/domains).
Regardless, you can create a standard set of objects that can be used in all databases, in two ways. The obvious way to run a script, using each database. The less obvious way is to run the same script, but instead (or in addition) use SQL Server's model database: Each time a new database is created , the objects within the model database will be within the newly created database. Loosely speaking, the model database is a SQL Server's domain :). For example, you could create a [utilities] schema within the model database.
November 14, 2015 at 10:21 am
It's not likely the function will change. If it does, you can use the Registered Server trick to push it to multiple databases in multiple servers. Just store the function in the database it will be used and call it a day.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2015 at 8:48 am
Registered server trick?
November 16, 2015 at 8:54 am
chuck.forbes (11/16/2015)
Registered server trick?
Yes. You register the servers in SSMS into a common group and then run the promotion script against the group. All of the underlying servers will be updated.
Disclaimer. I haven't ever had the need to do something like that but Ed Wagner (a fellow I have a huge amount of trust in) says he does it all the time for his servers.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2015 at 1:52 pm
I think what I am ultimately going to do is place these utility functions in a separate database per SQL Server instance. Then in all other database (including model) I'll setup wrapper functions within their own schema ("util", for example) which point to the actual function.
That seems to be working just fine.
--=cf
November 17, 2015 at 6:57 am
It may be worthwhile to think about a disaster scenario: If the utilities database goes offline, how many databases will be impacted?
November 17, 2015 at 8:10 am
SoHelpMeCodd (11/17/2015)
It may be worthwhile to think about a disaster scenario: If the utilities database goes offline, how many databases will be impacted?
Eggsactly (pun intended inferring "all eggs in one basket"). It's like the well meaning folks that have used PowerShell to make a centralized backup system. If that system goes offline, what happens to the log files on all the other systems. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2015 at 8:38 am
I suppose that's possible, but I'm not sure why this one database would go offline, when the remainder of the database within the same instance wouldn't also be having a similar problem? (Disclaimer, I've been working with SQL Server for about 4 months now, so I'm still in the infancy stage.) Currently, in the rare cases where there's been an issue, we've seen it affect the entire instance, as opposed to a database here & there.
If I did choose to go with a separate copy of these functions in each database, is it possible to create a pristine model database, back it up, and then restore it to each new instance that comes online as we move forward? Since it's one of the databases that's installed on my behalf, I wasn't sure if I was able to easily recover a copy like I would a database that I created. Could it be that easy?
--=cf
November 18, 2015 at 12:58 am
The model database can be restored to other instances, but each instance must be running the same SQL Server build. And if the master database is rebuilt, the model database will be rebuilt at the same time. Rather than relying upon a backup of model, it is probably just as easy to keep a script in the systems' disaster recovery (and setup) folders.
There is one problem, though. A newly created database gains the objects kept in model, but an existing database does not. If you update the script, it will need to be run on existing databases.
Years ago I worked a situation where a production system relied upon linked servers to a development system. A developer decided to simulate a network failure within their development environment. In less than a minute, that simulation brought down their production system (due to its dependence upon the development system via its use of linked servers) :hehe:
November 18, 2015 at 8:35 am
I should probably clarify, I was thinking about having a 'utilities' database on each SQL Server instance, as opposed to one single 'utilities' database and using linked servers to access it. It seemed like the best compromise between system stability, and having as few code-bases as possible. We're slowly reducing our SQL Server sprawl, and hopefully will be down to 2-3 instances in the near future.
I don't know if that changes anyone's opinion on the dependency issue.
November 18, 2015 at 8:28 pm
When an IO failure strikes, you do not always know what garbage was written (or failed to be written) until it is read... later... perhaps much later. Not every db will get hit, and a read only database doesn't prevent all scribblers from scribbling. You may not discover the corruption until after you have deleted your last known good backup. At that point, all you would have are backups that restore a corrupted database - not good.
To reduce the impact of that scenario, I have a restore strategy: I restore every one of my backups, and run a full checkdb upon them. That doesn't mean I can guarantee zero down time (it means I strive for minimal down time). Always on is in the works (new employee) but still won't protect me from SQL Server bugs. My strategy is beyond what Microsoft recommends, so some would call me paranoid. But back in the years when I specialized in disaster recovery, I saw at least one DBA each year who needed to brush up their resume??.
I cannot tell you what is a reasonable cost for insurance, and I don't know the risks exposed by your system (such as the state of its firmware, drivers, OS, SQL Server, and hardware), all I can do is wonder what would happen if that utilities db met the big bit bucket in the sky??.
November 18, 2015 at 8:34 pm
SoHelpMeCodd (11/18/2015)
The model database can be restored to other instances, but each instance must be running the same SQL Server build. And if the master database is rebuilt, the model database will be rebuilt at the same time. Rather than relying upon a backup of model, it is probably just as easy to keep a script in the systems' disaster recovery (and setup) folders.There is one problem, though. A newly created database gains the objects kept in model, but an existing database does not. If you update the script, it will need to be run on existing databases.
Years ago I worked a situation where a production system relied upon linked servers to a development system. A developer decided to simulate a network failure within their development environment. In less than a minute, that simulation brought down their production system (due to its dependence upon the development system via its use of linked servers) :hehe:
I wouldn't put any of this in the Model database. It would affect databases that I might not want to affect. For example, I wouldn't want all of these function in a scratch/sandbox database nor would I want them in an archive database.
I also agree about not making interdependencies between servers, never mind just databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply