Solved

'SERVERPROPERTY' is not a recognized function name.

Posted on 2004-08-24
10
3,476 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
SQL, add where clause 5 24
Database Integrity 1 50
Help With SQL Query 9 33
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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