| Author |
Topic |
|
Grah
Starting Member
5 Posts |
 Posted - 2009-05-01 : 12:21:39
|
| Hi,wondering if anyone knows an easy what to manage this.All Clients DBs can be named differently but most are called Prod_db. Sql requires functions to have the qualiifer to be called.ie.--Get dollars by rate and productset @cslDollars = Prod_db.function_calc( @payPeriodEnd, 'Product', 'UserType', @loadTicketTons)Due to client customisations no db is 100% the same.When there is a problem we get a copy of the clients DBOf course we can't call them all Prod_DB inhouse so we use Client_prod_db. But now the function calls in views and procedures need to be changed.--Get dollars by rate and productset @cslDollars = Client_Prod_db.function_calc(@loadID, @payPeriodEnd, 'Product', 'UserType', @loadTicketTons)Does anyone have an easy method / solution to this?we currently have SQL 2005 inhouse but majority of clients are still running Sql 2000.Getting bored scripting all the objects replacing the qualifier, replace the create Proc,Function, View with Alter Proc,Function, View everytime - Cheers for any help. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-05-01 : 13:16:58
|
| I've never used them, but if you're on SQL 2005 synonyms may come in handy in this situation.Jim |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-05-01 : 13:44:36
|
| You do not have to qualify a function, procedure, table, or other object name with a database name, provided it is in the same database.CODO ERGO SUM |
|
|
|
Grah
Starting Member
5 Posts |
Posted - 2009-05-01 : 15:52:12
|
quote:
Originally posted by Michael Valentine Jones
You do not have to qualify a function, procedure, table, or other object name with a database name, provided it is in the same database.
Sorry unless things have changed recently you do for Functions.MSDN quote:
http://msdn.microsoft.com/en-us/library/ms175562(SQL.90).aspx Scalar-valued functions must be invoked by using at least the two-part name of the function.
Anyone else, done something like this? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-01 : 16:30:18
|
| Michael's point is still valid, you don't need the database name, only the schema and object names in two-part format. Your functions are probably all created under dbo, so dbo.function_calc() should be sufficient. |
|
|
|
Grah
Starting Member
5 Posts |
Posted - 2009-05-01 : 18:27:03
|
quote:
Originally posted by robvolk
Michael's point is still valid, you don't need the database name, only the schema and object names in two-part format. Your functions are probably all created under dbo, so dbo.function_calc() should be sufficient.
No, all our functions are not created under "dbo." they are all in the schema "PROD_DB" so all functions calls are prod_db.FunctionWhen we create the new schema "client_prod_db" from a back up of Prod_db we make "client_prod_db" schema the owner of all its own objects. That way applications all call schema.object and use the customised code inside those functions, procs views etc. For instance 1 company calls a ticket a workticket, another calls it a load_ticket and stores the details in custom tables we need a function to get the ticket id and the ticket amount - be it volume, weight or dollars.So instead of writing many functions we write 1 getticket.Then customise that function, we cannot have 20 different dbo.getticket functions in our development environment, can we?Process works well, only pig is just goiing into all the objects to change the schema each time.Thank you. Seems as if the issue is using Multiple schemas in the same database with the object name with different internal coding is not a normal MSSQL situation. Back to generate scripts and replace. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-05-01 : 19:31:25
|
You should be able to use Synonyms for this by creating a synonym in the Prod_db schema that points at the base object in the other schema.CREATE SYNONYM Prod_db.Myfunction FOR MyOtherSchema.MyOtherfunction; You should refer to the CREATE SYNONYM and Understanding Synonyms topics in SQL Server Books Online for more details on how they can be used.CODO ERGO SUM |
|
|
|
Grah
Starting Member
5 Posts |
Posted - 2009-05-04 : 14:48:55
|
| Hi MichaelReally must be confusing you Prod_db.getticket -- Gets ticket info from customised_by_Client_1 table for client 1Prod_db.getticket -- Gets ticket info from customised_by_Client_2 table for client 2The columns aren't the same the table names aren't the same. Core code calls {schema}.getticketI have Client_1_prod_db and Client_2_prod_db schemas in my database I do not have a prod_db schema.When I import the client scehmas in I cannot have 2 objects with the same name in dbo or another schema can I?I'm confused where would you put the 2 functions? and how would I have to program the quailifer call in the two environments "Client" and "Shared inhouse" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Grah
Starting Member
5 Posts |
Posted - 2009-05-29 : 11:18:10
|
quote:
Originally posted by tkizer
If I were you, I'd put the objects all under dbo, and then just perform restores to different database names in order to work with your client's data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
OK "Let's begin with the premise that everything I've done up until this point is wrong."-- Not being a DBA, which book, course would point to this sounds simple resolution?My mental image is Give thank you flowers to dbo.taraBut when the flowers get to dboThere are multiples of dbo.tara how does the flower get to the right one?is it set up as dbo.client_1.taradbo.client_2.taradbo.client_3.taradbo.client_4.taraand therefore as I'm in Client_1 it would go to dbo.client_1.tara. |
|
|
|
|
Changing the qualifier on UDF when changing DBs