I recently had an issue automating the creation of SQL Agent Jobs.  I need to set the Log File properties of the job steps, and was setting the path to a local share on the SQL Server.  No problem you say:


'\\' + @@SERVERNAME + '\Logs' 

should do the trick.

The problem came when I moved the code to a named instance of SQL Server.  @@SERVERNAME returns "<ServerName>\<InstanceName>".  So I briefly thought about parsing @@SERVERNAME, and figured there had to be a better way.  As it turns out, there is; it is called SERVERPROPERTY.

'\\' + CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName')) + '\Logs'

SERVERPROPERTY returns a sql_variant, so you might have to cast it into something you can use without type conversion issues.

The information you can get with SERVERPROPERTY is interesting:

  • Collation
  • Edition
  • Engine Edition
  • InstanceName
  • IsClustered
  • IsFullTextInstalled
  • IsIntegratedSecurityOnly
  • IsSingleUser
  • IsSyncWithBackup
  • LicenseType
  • MachineName
  • NumLicenses
  • ProcessID
  • ProductVersion
  • ProductLevel
  • ServerName