Prev: Strategy for data partition?
Next: Syntax error, permission violation, or other nonspecific error when preparing a procedure call
From: Ada on 8 Jul 2010 16:21
The following code gave me what I want. Thank you all.

SELECT CAST(CAST(SERVERPROPERTY('MachineName') as varchar(50)) + '_' +
CASE WHEN charindex('\', @@servername) > 0
THEN CAST(SERVERPROPERTY('InstanceName') as varchar(50))
ELSE @@servername
END + '_' +
REPLACE(CONVERT(varchar(10),getdate(),110),'-','') +
REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80))

--
SQL Server DBA


"John Bell" wrote:

> On Mon, 5 Jul 2010 16:06:17 -0700, Ada <Ada(a)discussions.microsoft.com>
> wrote:
>
> >John,
> >Thanks for the script.
> >
> >I want what your script intents to do
> >(<MachineName>_<InstanceName>_<Timestamp>), but there is a problem.
> >It gives <MachineName>_<MachineName>!<InstanceName>_<Timestamp>
> >
> >InstanceName should be the MachineName for Default Instances.
> >I can not use SERVERPROPERTY('InstanceName ') since it returns NULL if it's
> >Default instance.
> >
> >SET NOCOUNT ON;
> >SELECT CAST(CAST(SERVERPROPERTY('MachineName ') as
> >varchar(50))+ '_' + REPLACE(@@SERVERNAME,'\','!') + '_' +
> >REPLACE(CONVERT(varchar(10),getdate(),110),'-','') +
> >REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80))
>
>
> I use @@SERVERNAME because of instance name returning null if you
> don't want to use SERVERPROPERTY('InstanceName') you can still do
>
> SET NOCOUNT ON;
> SELECT CAST(CAST(SERVERPROPERTY('MachineName ') as varchar(50))+ '_' +
> RIGHT(@SERVERNAME,LEN(@SERVERNAME)-CHARINDEX('\',@SERVERNAME)) + '_' +
> REPLACE(CONVERT(varchar(10),getdate(),110),'-','') +
> REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80))
>
>
> As CHARINDEX('\',@SERVERNAME)) will return 0 for defaut instances.
>
> John
> .
>
First  |  Prev  | 
Pages: 1 2 3 4
Prev: Strategy for data partition?
Next: Syntax error, permission violation, or other nonspecific error when preparing a procedure call