January 28, 2009 at 6:53 am
This is nuts!
I am trying to create a function in a database in SQL2000. I have created and used this function on MANY other databases both 2000 and 2005. The problem database(s) throws the following:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@nCount'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@cSExp'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@nCount'.
Server: Msg 137, Level 15, State 1, Line 11
Must declare the variable '@cSExp'.
Server: Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return value cannot be used in this context.
I can create the function in master on this server but none of the other databases. Now here is the REALLY crazy part, if I copy another function already compiled in the database, change the name and try to compile that I get similar errors. The account I am logged into is Administrator and has the top level sysadmin role assigned to it. This has got to be some privilege issue but neither the DBAs at the client site or anyone I work with has seen this. Any ideas??????
THANKS!!!!
jb
January 28, 2009 at 7:39 am
Think you could maybe post the code that you're running? Without that I can't begin to guess why you're getting a syntax error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 9:04 am
The syntax is not the issue. As I said (or thought I said), the function WILL compile against the "master" database on the server as well as another database. It also compiles on about a dozen other servers.
This code
***************************************************************************************************
CREATE FUNCTION [dbo].[AT] (@cSExp NVARCHAR(4000), @cExp2S NVARCHAR(4000), @nCount SMALLINT = 1 )
RETURNS SMALLINT
AS
BEGIN
IF @nCount > 0
BEGIN
DECLARE
@i SMALLINT,
@StartPos SMALLINT
SELECT @i = 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN)
WHILE @StartPos <> 0 and @nCount > @i
SELECT @i = @i + 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN, @StartPos+1 )
END
ELSE
SET @StartPos = NULL
RETURN @StartPos
END
*****************************************************************************************************
gives this error:
****************************************************************
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@nCount'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@cSExp'.
Server: Msg 137, Level 15, State 1, Line 11
Must declare the variable '@nCount'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@cSExp'.
Server: Msg 178, Level 15, State 1, Line 18
A RETURN statement with a return value cannot be used in this context.
****************************************************************
When run against the problem databases, yet on the SAME server runs just fine against master and a couple of other databases on the same server. AND the permissions are EXACTLY the same because I'm trying it under the SA account.
??????????????????????????????
jb
January 28, 2009 at 9:09 am
Check this out, if you remove the GO before the create function, I get the same list of errors you get :
SET NOCOUNT ON
GO
CREATE FUNCTION [dbo].[AT] (@cSExp NVARCHAR(4000), @cExp2S NVARCHAR(4000), @nCount SMALLINT = 1 )
RETURNS SMALLINT
AS
BEGIN
IF @nCount > 0
BEGIN
DECLARE
@i SMALLINT,
@StartPos SMALLINT
SELECT @i = 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN)
WHILE @StartPos <> 0 and @nCount > @i
SELECT @i = @i + 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN, @StartPos+1 )
END
ELSE
SET @StartPos = NULL
RETURN @StartPos
END
GO
--DROP FUNCTION AT
I also had similar problems between case sensitive and case insensitive servers / db (assuming solution 1 doesn't work).
January 28, 2009 at 9:25 am
Nope, same error message. I figure it's got to be a permission thing because it'll run on other DBs on the same SQL server. I keep coming back to that or some wacky corruption. But I've been banging my head on this for a while now. This client has contract DBs come in and they're unwilling to pay for them to come in and look at this. Since it's my code it's my problem or so they reason.
Thanks for the efforts though!
jb
January 28, 2009 at 9:30 am
Can't be permissions with SA... unless there's something I'm not aware of.
And I checked it on my server (case sensitive) and the code runs just fine.
I have no idea what else can be causing this.
Have you tried login on the server with a login that is in the built-in administrators group so that you're sure to be considered like a sa on the server?
January 28, 2009 at 9:37 am
Yes, I've tried 3 different accounts with sysadmin roles. They all do the same thing. I've tried using both windows authentication and an entered login (SA) logging into SQL server. Same behavior.
jb
January 28, 2009 at 9:44 am
i get that error if the database is set to compatibility mode "70"; but at 80 or above it works fine....
maybe the server you are playing with is an upgrade from SQL 7, and the compatibility mode is left at 70?
Lowell
January 28, 2009 at 9:47 am
Thanks Lowell, I'll have to look at that!
jb
ps big Calvin fan here!
January 29, 2009 at 6:35 am
Just an extra thought here, but why would you want to use a reserved word for the name of a function? It's not the least bit descriptive of what the function does, and, while unlikely, still allows for the possibility of some arcane bug related to reserved word checking...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 29, 2009 at 6:46 am
smunson,
I submit to an old coder's tenet "Why write it if somebody else already did, just borrow it" I found this code out on a MS (I believe) forum. It is supposed to be 2 things, a throwback to some old FoxPro functions and an included function in a future release of SQL Server. The code does exactly what I need it to do. Previously I had a VERY long string which I needed to parse thru using CHRINDEX. This function removes a ton of code and makes it a lot more readable.
😎
jb
January 29, 2009 at 6:52 am
jb, was my guesstimate right? were all the databases on that server at compatibility 70, but master at 80 due to an upgrade?
you are all set on that server?
Lowell
January 29, 2009 at 6:57 am
hey lowell,
was running around with fire hoses all day so I didn't get a chance to get back on. Hopefully today although my emails are piling up already. I will most certainly get back to you though.
jb
January 29, 2009 at 7:01 am
I'm sure you can find the time to run this statement and get your answer :
SELECT name, crdate, cmptlevel FROM master.sys.sysdatabases ORDER BY cmptlevel
January 29, 2009 at 7:22 am
Lowell,
You are the man! The databases that I can't create the functions on are 70. Where I can create them is 80. So, next question is what happens if I change the 70 to 80? I am not a DBA, just a lowly SQL coder.
jb
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply