sql - Reference a table from a scalar UDF - tied to schema name? -

-

due rollout/architecture of our system, create scalar udf use within select statement in stored procedure

because our various dev/test/live environments have different schema names, ideally name udf dbo.myfunctionname, however, because function contains logic uses select statement values database table return answer means cannot use dbo must put under schema of database (ie: test) note - right, right?

when calling function select statement, must provide two-part name, must use test.myfunctionname

select name, age, test.myfunctionname(personid) people

is there way parameterise schema name when roll out different schema, not need create function under each 1 and/or amend how called? ideally, i'm looking 

select name, age, schema_name().myfunctionname(personid) people

but i'd not use dynamic sql if possible

many thanks

your question 2 questions:

  • does function need have same schema tables it's selecting from? answer no. dbo.myfunction() can select test.mytable fine. however, may run issues ownership chaining -- different schemas mean explicit permissions must exist user select table. see books online details.
  • can invoke function parameterized schema, without using dynamic sql? answer no. object name static -- although sql have "feature" in form of deferred name resolution allows refer objects in stored procedures if not exist, still doesn't allow make schema name variable.

Comments

Post a Comment

Popular posts from this blog

c# - how to use buttonedit in devexpress gridcontrol -

-
Image
i trying create gridcontrol buttonedit in 1 of columns. when user clicks button edit, popups form select product. when selection done on popup, returns datarow selection main grid below. but when column loses focus, value wrote column disappear. here code creates gridcontrol's data , buttonedit's click event. private void frmsiparisnew_load(object sender, eventargs e) { dt = new datatable(); dt.columns.add("malzeme_kodu",typeof(string)); dt.columns.add("malzeme_aciklama", typeof(string)); dt.columns.add("adet", typeof(decimal)); dt.columns.add("birim", typeof(string)); dt.columns.add("fiyat", typeof(decimal)); dt.columns.add("kur", typeof(string)); dt.columns.add("tutar", typeof(decimal)); datarow dr = dt.newrow(); dt.rows.add(dr); gc.datasource = dt; } private void repositoryitembuttoneditmal...
Read more

java - Oracle EBS .ClassNotFoundException: oracle.apps.fnd.formsClient.FormsLauncher.class ERROR -

-
i need in configuring java on machine because, when try loading forms, loader goes in circles , gives error: java.lang.classnotfoundexception: oracle.apps.fnd.formsclient.formslauncher.class @ sun.plugin2.applet.applet2classloader.findclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass0(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass(unknown source) @ java.lang.classloader.loadclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadcode(unknown source) @ sun.plugin2.applet.plugin2manager.createapplet(unknown source) @ sun.plugin2.applet.plugin2manager$appletexecutionrunnable.run (unknown source) @ java.lang.thread.run(unknown source) i have tried re-installing firefox, older versions of java runtime environments, disabling proxy. what missing? application working on other machines. i spent around 100 hours finding s...
Read more

c# - System.FormatException' occurred in MongoDB.Bson.dll - XXX is not a valid 24 digit hex string -

-
i have created c# class this: public class employee { [bsonrepresentation(bsontype.objectid)] public string name { get; set; } public int age { get; set; } public list<string> address { get; set; } } when try save information (using mongodb) this: var e = new employee(); e.address = new list<string>(); e.address.add("address 1"); e.address.add("address 2"); e.age = 333; e.name = "some name"; context.employees.insert(e); i getting following error: an unhandled exception of type 'system.formatexception' occurred in mongodb.bson.dll additional information: 'some name' not valid 24 digit hex string. how can make string field act objectid in mongodb? reading docs: ... in case serializer convert objectid string when reading data database , convert string objectid when writing data database ( the string value must valid objectid )...
Read more