Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

'SERVERPROPERTY' is not a recognized function name.

Posted on 2004-08-24
10
Medium Priority
?
3,495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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
 
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 900 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 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