| Author |
Topic |
|
stmod
Starting Member
5 Posts |
 Posted - 2010-11-22 : 12:19:28
|
| Hello, hopefully this is the right section to post my question.I have to migrate a set of databases currently in MsAccess to SqlServer2008. No problem in doing this.My problems raises because I also have a huge set of asp pages where are lots of queries that largely use the MsAccess functions ucase and cdate (that are upper and convert on SQLServer).So I am looking for a workaround about to not modify all the codes (focusing on ucase).I thought to write a custom function named ucase that simply applies the sqlserver upper function to the input. I hope that, in this way, i can avoid lots of changes in the code. That's why I don't want the prefix for calling the function/stored procedure.I don't care to use a stored rather than a function because i have no performance problems.Surfing the web I got confused:- Somewhere they say it is possible to create system function symply creating a function with a name in the form fn_myfunction on the master db- Somewhere they say that it is possible only to create a stored callable in my suitable way, but I was non able to get it working.- Somewhere they say that a full rebuild of the database is necessary.Can someone help me suggesting the right way to get accomplish my task?Thanksstmodp.s.: this is the most common error I got in my trials "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myproc", or the name is ambiguous." |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 13:57:51
|
| I agree with Brett. A global search and replace is going to be your best bet. Then testing.Make sure you have backups and/or proper version control first. |
 |
|
|
stmod
Starting Member
5 Posts |
Posted - 2010-11-23 : 02:31:21
|
| Thanks for your answer.I cannot do a global replacement because the command uCase is also an ASP command so in my code I have "ucase" used both for sql statement and pure code.You are right about sql written in front-end, but I joined this team to reengineer the site and this kind of code is what I found.I really appreciate if someone can suggest me a workaround for using the uCase function in SqlServer because we already started to manually change the queries in the code and we are at 700 changes being at half way.... (and once finished we have to report all the changes in production environment)thanksstmod |
|
|
|
stmod
Starting Member
5 Posts |
Posted - 2010-11-25 : 13:34:06
|
| Any other suggestion, info workaround?thanks stmod |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-25 : 14:29:46
|
You could use a User Defined Function in SQL ... but the problem with that is that I do not thing you can have the exact same syntaxSELECT UCase(MyColumn)FROM MyTable but I think you have to use the OWNER prefix thus:SELECT dbo.UCase(MyColumn)FROM MyTable if that is NOT the case then you will be able to just make a User Defined Function called "UCase" |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-25 : 14:44:19
|
I am pretty sure you need to reference the owner prefix on the udf calls in a Select statement.The error you mentioned in the OP -"Cannot find either column..." would occur if you call a table valued function but selected it as a column, but would also occur if the procedure/function didn't exist (it would treat the nonexistent function as a column name instead, and thus not find it). Make sure the connecting user ID has execute permissions on the UDF.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
stmod
Starting Member
5 Posts |
Posted - 2010-11-26 : 13:59:25
|
| thanks Kristen, I tried your solution and it works fine, but if I have to add the owner prefix I can directly change the function in "upper", so it is not really useful for me.@dataguru1971. I usually use the SA user so I am sure I have all the rights. Maybe I got that error using a different user (I did lots of tries and i dont remember); anyway, can you help me in avoiding the use of prefix? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-26 : 14:10:48
|
| Tried it in SQL 2008:SELECT dbo.MyFunction('FooBar')works fine. ButSELECT MyFunction('FooBar')Server: Msg 195, Level 15, State 10, Line 1'MyFunction' is not a recognized built-in function name.So that ain't going to work. There is no other route that will allow you to repurpose "UCase(xxx)" that I am aware of. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-26 : 14:12:16
|
Sorry, but it has to be prefixed with the scheman name or it won't recognize it as a function--unless I am missing something.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-26 : 14:21:36
|
| "unless I am missing something"I'm looking Left and Right but I don't see any pigs flying by either! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-26 : 14:32:21
|
quote:
Originally posted by Kristen
"unless I am missing something"I'm looking Left and Right but I don't see any pigs flying by either!
Maybe you should be looking up? They would be flying after all 
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
stmod
Starting Member
5 Posts |
Posted - 2010-11-27 : 11:10:53
|
| Ok, thanks to all...I will handly modify all the code :(hoping in the future to find better written code :)stmod |
|
|
|
|
Call a Function or Store Procedure without prefix