Solved

'SERVERPROPERTY' is not a recognized function name.

Posted on 2004-08-24
10
3,488 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

627 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