October 29, 2017 at 5:26 pm
I have some generic utility stored procedures and user defined functions that I would like to define/save once, and have available for use in all databases for this server.
After much Googling, I've come across two "schools of thought":
If I do this, it "works", with "works" being defined as running in the scope of the current database. For example:
USE FOO
GO
EXEC dbo.sp_XXX_MyStoredProcedure 'dbo.SomeTable' (where dbo.sp_XXX_MyStoredProcedure is in master as a system object)
Where XXX are initials designating my company to avoid future Microsoft SP naming collisions.
If MyStoredProcedure executes SET @object_id=OBJECT_ID('dbo.SomeTable'), if I follow the above process it runs under the context of the current database and dbo.SomeTable is "found".
However, Microsoft recommends not saving objects in the master database, nor naming objects with the sp_ prefix. Other StackOverflow posts said that master isn't (usually) backed up often, and it's just an "unexpected" place to stored these objects.
So, I create a database "Tools", stored my SP's there, even marked them as system objects, and use a full name to call the SP, i.e. Tools.dbo.sp_XXX_MyStoredProcedure 'dbo.SomeTable'.
In this second scenario, dbo.SomeTable is always not found, i.e. OBJECT_ID('dbo.SomeTable') is NULL. I suspect the SP is searching for dbo.SomeTable in its own context, i.e. in Tools.
Can you recommend a best practice approach for this scenario? Should I just store these objects in master and named 'sp_whatever', contrary to Microsoft guidance?
(I also note that, with some servers I work with, I may have permission to create SP's and UDF's at the database level but not in master.)
Thanks...
October 29, 2017 at 5:47 pm
Further information...
If I save my SP in master, named sp_MoH_GetColumnList, and marked as a system object, then this works:
EXEC dbo.sp_Moh_GetColumnList 'content.DIAGNOSIS', @debug=1
But this fails:
EXEC master.dbo.sp_Moh_GetColumnList 'content.DIAGNOSIS', @debug=1
So even in master, as a system object, passing a full path to the SP changes "something" such that OBJECT_ID('content.DIAGNOSIS') returns NULL.
I suspect this eliminates storing my SP's in a Tools database instead of master.
Can someone explain the above issue, or point me to documentation where I can learn more about the execution context of SP's?
October 29, 2017 at 6:11 pm
I have 2 databases on every server I manage. The DBA database is just that - a database of procedures and functions I use in administration of the server and databases on it. It tracks things like drive space over time and other tables store the configuration for the procedures I wrote to do maintenance on the databases and server. The only people with privs to this database are sysadmins. It contains the DBA stuff, so nobody else needs access to anything.
On the other side, I have a utility database named UTIL. It contains utilities for use by everyone. They're mostly functions, some procedures and a few tables. I've created users for every login on the server here and they all have privs to select or execute (as appropriate to the type) AND to view definition on everything. Everyone's free to use them for whatever they want. I grant VIEW DEFINITION on them in the hopes that people will learn from them. I have things like tally functions, string manipulation functions, anonymization and randomization functions, date set functions, mathematical functions, etc.
This setup works very well for me. If someone has a function they use in multiple databases, they sometimes bring it to me for inclusion in the util database. The developers know the functionality will be there, no matter which server they're running on.
I read what you posted above about the master database not being backed up frequently. The people who told you that are just like me in that none of us know what your company's backup strategy is, including frequency. It could be backed up like all the other databases or never get backed up at all. There's no way anyone's going to know that unless you tell us.
October 29, 2017 at 7:53 pm
Ed Wagner - Sunday, October 29, 2017 6:11 PMI have 2 databases on every server I manage. The DBA database is just that - a database of procedures and functions I use in administration of the server and databases on it. It tracks things like drive space over time and other tables store the configuration for the procedures I wrote to do maintenance on the databases and server. The only people with privs to this database are sysadmins. It contains the DBA stuff, so nobody else needs access to anything.On the other side, I have a utility database named UTIL. It contains utilities for use by everyone. They're mostly functions, some procedures and a few tables. I've created users for every login on the server here and they all have privs to select or execute (as appropriate to the type) AND to view definition on everything. Everyone's free to use them for whatever they want. I grant VIEW DEFINITION on them in the hopes that people will learn from them. I have things like tally functions, string manipulation functions, anonymization and randomization functions, date set functions, mathematical functions, etc.
This setup works very well for me. If someone has a function they use in multiple databases, they sometimes bring it to me for inclusion in the util database. The developers know the functionality will be there, no matter which server they're running on.
I read what you posted above about the master database not being backed up frequently. The people who told you that are just like me in that none of us know what your company's backup strategy is, including frequency. It could be backed up like all the other databases or never get backed up at all. There's no way anyone's going to know that unless you tell us.
Thanks Ed.
It tried this (TOOLS is analogous to your UTIL)
USE TOOLS
GO
CREATE PROCEDURE sp_print_object_id (
@TableName SYSNAME
)
AS
BEGIN
DECLARE @object_id INT
SET @object_id = OBJECT_ID(@TableName)
PRINT 'OBJECT_ID: ' + ISNULL(CAST(@object_id AS VARCHAR(12)),'NULL')
END
I created table Tools.dbo.foo, and to test:
EXEC sp_print_object_id 'dbo.foo'
EXEC dbo.sp_print_object_id 'dbo.foo'
EXEC Tools.dbo.sp_print_object_id 'dbo.foo'
This all worked, i.e. as long as the SP and table were in the same database.
I then created MyDevDB.dbo.bar, and to test:
USE MyDevDB
GO
EXEC sp_print_object_id 'dbo.bar'
EXEC dbo.sp_print_object_id 'dbo.bar'
EXEC Tools.dbo.sp_print_object_id 'dbo.bar'
Of course the first two invocations fail. The third invocation runs but returns NULL, i.e. it fails if the database context is not Tools, the table is in the current database context, and the full path is specified to execute the SP.
I then dropped Tools.dbo.sp_print_object_id, and ran:
USE master
GO
CREATE PROCEDURE sp_print_object_id (
@TableName SYSNAME
)
AS
BEGIN
DECLARE @object_id INT
SET @object_id = OBJECT_ID(@TableName)
PRINT 'OBJECT_ID: ' + ISNULL(CAST(@object_id AS VARCHAR(12)),'NULL')
END
EXECUTE sp_MS_marksystemobject 'sp_print_object_id'
GO
USE Tools
GO
EXEC sp_print_object_id 'dbo.foo'
EXEC dbo.sp_print_object_id 'dbo.foo'
EXEC master.dbo.sp_print_object_id 'dbo.foo'
USE MyDevDB
GO
EXEC sp_print_object_id 'dbo.bar'
EXEC dbo.sp_print_object_id 'dbo.bar'
EXEC master.dbo.sp_print_object_id 'dbo.bar'
In both scenarios, the first two invocations worked, i.e. returned an object ID, although they also generated a message:
sp_MS_marksystemobject: Invalid object name 'sp_print_object_id'
The third invocation always returned a NULL object ID.
Any advice on getting the same (and correct/desired) behavior across all databases? Note I haven't granted any permissions or anything other than what I've noted above.
Thanks...
P.S.: If any of your UTILs are in the public domain I'd love to learn from them. They are exactly the kind of utility processing I'm attempting to implement. I'll click your signature links but if there's anything else (GitHub, BitBucket, etc) please point me in the right direction.
October 30, 2017 at 8:26 am
1) It is stupid to not backup master regularly.
2) You can protect against that by having a single script (or easy mechanism to run a bunch of scripts) that create all the objects you need if they server evaporates and you need to rebuild.
3) I see no issue with creating such code objects in master.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 30, 2017 at 5:32 pm
TheSQLGuru - Monday, October 30, 2017 8:26 AM1) It is stupid to not backup master regularly.2) You can protect against that by having a single script (or easy mechanism to run a bunch of scripts) that create all the objects you need if they server evaporates and you need to rebuild.
3) I see no issue with creating such code objects in master.
Just to clarify...
1) I'm not the DBA, just a noob-ish SQL Server ETL Developer (approx. 2-2.5 years experience on-and-off with SQL Server, although experienced with other databases and SQL). So any stupidity with respect to master db backup is on the part of my DBA - I have no control over his backup strategy.
2) All my SP's and UDF's are saved in external files, so if master goes belly up, I can easily recreate my global SP's and UDF's. And I (and my DBA) probably have bigger issues at that point.
3) Here are some links I've found which led to this post:
https://nickstips.wordpress.com/2010/10/18/sql-making-a-stored-procedure-available-to-all-databases/
http://sqlserverplanet.com/dba/making-a-procedure-available-in-all-databases
https://www.sqlshack.com/sql-server-system-databases-the-master-database/
http://sqlrus.com/2011/10/add-a-procedure-to-master-database-yes-please/
https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database (scroll down to Recommendations, or search on "Do not create user objects in master")
I'm just going to put my generic, I-want-to-use-in-every-database SP's and UDF's (eg. TallyOh string splitter) in master, follow the approach in the above links, ignore Microsoft's admonishment about user objects in master, and move on to other issues.
@Ed Wagner, I'd still love to know how you get around these issues with your UTIL database approach, plus any of your utilities that you consider public domain.
Thanks for the help, much appreciated.
October 31, 2017 at 11:12 am
Microsoft's page has the following about putting things in master: "If you do, master must be backed up more frequently."
There is no reason for you to no do what you are wanting to do.
I will note that I still prefer to have everything I can have in a "DBATools" or such database instead of master.
On to the next task ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 31, 2017 at 1:24 pm
For things I have in my UTIL database, they were mostly born out of necessity. Here are some great starting points, but your needs will dictate what you write.
TallyN: http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
DelimitedSplit8K: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Misc Tally Functions: https://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
Discussion that resulted in several string cleaning functions: https://www.sqlservercentral.com/Forums/1585850/do-you-have-a-StripNonNumeric-ITVF-function
NGrams8K: http://www.sqlservercentral.com/articles/Tally+Table/142316/
PatternSplit8K: http://www.sqlservercentral.com/articles/String+Manipulation/94365/
The important thing to keep in mind isn't just what you create, but how it works. If it isn't performant, your server is going to pay the price.
November 1, 2017 at 10:21 pm
@TheSQLGuru and Ed Wagner:
I would love to put all these utilities in DBATools, UTIL, etc. But I'm grappling with execution context when code is in those locations; the code executes in the context of UTIL, not the current database. This issue is illustrated in the code I provided in https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1905018. If I get a solution to that issue, I'll put all my global code in a utility database rather than master.
Bueller? Bueller?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply