Solved

'SERVERPROPERTY' is not a recognized function name.

Posted on 2004-08-24
10
3,465 Views
Last Modified: 2008-01-09
I'm building a function that is supposed to work on both Sql Server 7 and 2000, when run in a Sql 7 enviroment I get "'SERVERPROPERTY' is not a recognized function name."

And this is ok since there is no such thing on Sql 7, but the SERVERPROPERTY function will never be executed on Sql 7 like in the example code bellow.

How can I work around this so I get a generic script that will work on both Sql Server versions ??

________________________________________________________________
Declare @SQLVersion varchar(26)
SET @SQLVersion = LEFT(@@version, 26)


If @SQLVersion = 'Microsoft SQL Server  7.00' Begin      
          SELECT @@version AS 'Version', 'N/A' AS 'Collation'
End

Else Begin
      SELECT @@version AS 'Version', SERVERPROPERTY ('Collation') AS 'Collation'
End
________________________________________________________________
0
Comment
Question by:SNilsson
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11881046
you could use dynamic SQL as in

If @SQLVersion = 'Microsoft SQL Server  7.00' Begin      
         exec('SELECT @@version AS ''Version'', ''N/A'' AS ''Collation''')
End

Else Begin
     exec ('SELECT @@version AS ''Version'', SERVERPROPERTY (''Collation'') AS ''Collation''')
End
0
 
LVL 8

Expert Comment

by:bukko
ID: 11881066
Declare @SQLVersion varchar(26)
SET @SQLVersion = LEFT(@@version, 26)


If @SQLVersion = 'Microsoft SQL Server  7.00' Begin      
         SELECT @@version AS 'Version', 'N/A' AS 'Collation'
End

Else Begin
     EXEC('SELECT @@version AS ''Version'', SERVERPROPERTY (''Collation'') AS ''Collation''')
End
0
 
LVL 8

Expert Comment

by:bukko
ID: 11881071
DOH!!!

Beaten to it!

:)
0
 
LVL 8

Author Comment

by:SNilsson
ID: 11881425

Thanks, but the snippet I gave is just a tiny example.

I rather have it in the same query context and not use the execute statement, This script can be hundreds of rows when ready,  and I dont like the idéa of setting hundred of aphostrofs in the right place, very hard to read and very easy to do a typing error.

Is Any other way to do this ??
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11881729
How about creating a small function called MYSERVERPROPERTY which you pass the "Collation" etc to. In here you just need a small piece of dynamic SQL to return NA if version is 7, or call SERVERPROPERTY function if 2K?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Expert Comment

by:arbert
ID: 11882114
"How about creating a small function called MYSERVERPROPERTY "

Functions don't work in 7.0, so you would have to create a proc...
0
 
LVL 8

Author Comment

by:SNilsson
ID: 11882925

Okey, something like this then, two proc is created on each server no matter if it's 2000 or 7 and I start by calling sp_SQLInfo in my example below, would this work ?

And can I even create the sp_SQL2KInfo proc on Sql 7 with the SERVERPROPERTY function in it, will it let me ?

And last can I check the server version in a more elegant way (LEFT(@@version, 26)) it seems clumsy.

__________________________________
Create Procedure sp_SQLInfo AS
Declare @SQLVersion varchar(26)
SET @SQLVersion = LEFT(@@version, 26)

If @SQLVersion = 'Microsoft SQL Server  7.00' Begin      
         SELECT @@version AS 'Version', 'N/A' AS 'Collation'
End

Else Begin
     EXECUTE sp_SQL2KInfo
End
__________________________________
Create Procedure sp_SQL2KInfo AS
SELECT @@version AS 'Version', SERVERPROPERTY ('Collation') AS 'Collation'
__________________________________




0
 
LVL 8

Author Comment

by:SNilsson
ID: 11901371

Can I get some feedback on my last post here please, so I can close the question.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 300 total points
ID: 11901427
I don't have SQL7 to test, but I think you will need to make your sp_SQL2KInfo  dynamicSQL - you don't need to make it a seperate proc though :

Create Procedure sp_SQLInfo AS
Declare @SQLVersion varchar(26)
SET @SQLVersion = LEFT(@@version, 26)

If @SQLVersion = 'Microsoft SQL Server  7.00' Begin      
         SELECT @@version AS 'Version', 'N/A' AS 'Collation'
End

Else Begin
     EXECUTE ( 'SELECT @@version AS ''Version'', SERVERPROPERTY (''Collation'') AS ''Collation''')
End
0
 
LVL 8

Author Comment

by:SNilsson
ID: 11901859

I just tested it it's not possible to create proc on sql 7 with unknown functions, and I'm kind of back where I started if I need to use dynamic sql.
I dont want to add dual apostophs in a miljon places it would be a nightmare to do changes in that proc.

I guess I'll have to do a seperate sp for each Sql version...
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now