Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3496
  • Last Modified:

'SERVERPROPERTY' is not a recognized function name.

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
SNilsson
Asked:
SNilsson
  • 4
  • 3
  • 2
  • +1
1 Solution
 
BillAn1Commented:
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
 
bukkoCommented:
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
 
bukkoCommented:
DOH!!!

Beaten to it!

:)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SNilssonAuthor Commented:

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
 
BillAn1Commented:
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
 
arbertCommented:
"How about creating a small function called MYSERVERPROPERTY "

Functions don't work in 7.0, so you would have to create a proc...
0
 
SNilssonAuthor Commented:

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
 
SNilssonAuthor Commented:

Can I get some feedback on my last post here please, so I can close the question.
0
 
BillAn1Commented:
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
 
SNilssonAuthor Commented:

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now