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