|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > User Defined Function Error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
User Defined Function Error
|
|
| Kevin M. Gore 2002-08-13, 3:23 pm |
| Something has changed in two of our 4 databases that
causes errors when running simple SQL Transact statements
like:
CREATE function kg_two_times(@qty_in int)
returns int
as
begin
return (2 * @qty_in)
end
The above code produces this error:
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 14
Must declare the variable '@qty_in'.
This same code works perfectly (as it always has) in two
of our 4 databases. It used to work in all 4.
What could possibly cause this:
| |
| Dinesh.T.K. 2002-08-13, 3:23 pm |
| Kevin,
May be some one changed the database compatiblity level.For UDF to work the
database compatibility level must be 80 .
Check the compatibility level of your database by using :
sp_dbcmptlevel <databasename>
See if it returns 80.
If not, set it at 80 by doing sp_dbcmptlevel <databasename>,80
or
In enterp.manager, right click on the database | properties | and in the
options tab make the change.
You have to exercise caution before changing compatibility level.First test
your applications against the new comp.level and then change it in the
production environment.You can find more details in the BooksOnLine topic -
sp_dbcmptlevel
Dinesh
--
--
SQL Server FAQ at
www.tkdinesh.com
"Kevin M. Gore" <kgore@fastindustries.com> wrote in message
news:2de101c24308$a145c9e0$9ae
62ecf@tkmsftngxa02...
> Something has changed in two of our 4 databases that
> causes errors when running simple SQL Transact statements
> like:
>
> CREATE function kg_two_times(@qty_in int)
> returns int
> as
> begin
> return (2 * @qty_in)
> end
>
> The above code produces this error:
>
> Server: Msg 170, Level 15, State 1, Line 10
> Line 10: Incorrect syntax near 'function'.
> Server: Msg 137, Level 15, State 1, Line 14
> Must declare the variable '@qty_in'.
>
> This same code works perfectly (as it always has) in two
> of our 4 databases. It used to work in all 4.
>
> What could possibly cause this:
>
| |
| Kevin M. Gore 2002-08-14, 8:23 am |
| >>Check the compatibility level of your database... <<
You are exactly right. Thank you very much. If you can
believe it, I asked this same question to the consulting
firm who caused this problem when they upgraded some
software we use. Neither the consulting firm nor the
company who makes the software could figure this out. We
are in the process now of finding out whether there really
is a reason to maintain a compatibility level of 70. We're
using SQL 2000, always have, and until the upgrade the
compatibility was 80 and everything worked fine. Hopefully
this "upgrade" will not turn out to be a down grade.
Thanks again.
>-----Original Message-----
>Kevin,
>
>May be some one changed the database compatiblity
level.For UDF to work the
>database compatibility level must be 80 .
>
>Check the compatibility level of your database by using :
>
>sp_dbcmptlevel <databasename>
>
>See if it returns 80.
>If not, set it at 80 by doing sp_dbcmptlevel
<databasename>,80
>
>or
>
>In enterp.manager, right click on the database |
properties | and in the
>options tab make the change.
>
>
>You have to exercise caution before changing
compatibility level.First test
>your applications against the new comp.level and then
change it in the
>production environment.You can find more details in the
BooksOnLine topic -
>sp_dbcmptlevel
>
>
>
>
>Dinesh
>--
>--
>SQL Server FAQ at
>www.tkdinesh.com
>
>"Kevin M. Gore" <kgore@fastindustries.com> wrote in
message
> news:2de101c24308$a145c9e0$9ae
62ecf@tkmsftngxa02...
>> Something has changed in two of our 4 databases that
>> causes errors when running simple SQL Transact
statements
>> like:
>>
>> CREATE function kg_two_times(@qty_in int)
>> returns int
>> as
>> begin
>> return (2 * @qty_in)
>> end
>>
>> The above code produces this error:
>>
>> Server: Msg 170, Level 15, State 1, Line 10
>> Line 10: Incorrect syntax near 'function'.
>> Server: Msg 137, Level 15, State 1, Line 14
>> Must declare the variable '@qty_in'.
>>
>> This same code works perfectly (as it always has) in two
>> of our 4 databases. It used to work in all 4.
>>
>> What could possibly cause this:
>>
>
>
>.
>
|
|
|
|
|