Using Machine Name in DOS Batch
in [Server Configuration]
|
Prev: Strategy for data partition?
Next: Syntax error, permission violation, or other nonspecific error when preparing a procedure call
From: John Bell on 2 Jul 2010 17:49 On Wed, 30 Jun 2010 19:49:42 -0700, Ada <Ada(a)discussions.microsoft.com> wrote: >I just want to see host names of the connected SQL Server in the output file >names. It's part of the requirements to cover named instance, clusters etc. > >Thanks, You could try something like: for /F %%a in (SQL2005servers.txt) do ( FOR /F %%f IN ( 'sqlcmd -S %%a -E -i filename.sql -h-1') DO ( sqlcmd -E -S %%a -d master -i c:\iSEC\ROG_iSEC-SQLSr_2005_2008_201006220000.sql -Y30 -s "|" -o c:\iSEC\OUT2005\ROG_SQL_Server_%%f.out )) filename.sql contains: SET NOCOUNT ON; SELECT CAST(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS ') as varchar(50))+ '_' + REPLACE(@@SERVERNAME,'\','!') + '_' + REPLACE(CONVERT(varchar(8),getdate(),110),'-','') + REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80)) John
From: Ada on 5 Jul 2010 19:06 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)) -- SQL Server DBA "John Bell" wrote: > On Wed, 30 Jun 2010 19:49:42 -0700, Ada > <Ada(a)discussions.microsoft.com> wrote: > > >I just want to see host names of the connected SQL Server in the output file > >names. It's part of the requirements to cover named instance, clusters etc. > > > >Thanks, > > You could try something like: > > for /F %%a in (SQL2005servers.txt) do ( > FOR /F %%f IN ( 'sqlcmd -S %%a -E -i filename.sql -h-1') DO ( > sqlcmd -E -S %%a -d master -i > c:\iSEC\ROG_iSEC-SQLSr_2005_2008_201006220000.sql -Y30 -s "|" -o > c:\iSEC\OUT2005\ROG_SQL_Server_%%f.out )) > > filename.sql contains: > > SET NOCOUNT ON; > SELECT CAST(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS ') as > varchar(50))+ '_' + REPLACE(@@SERVERNAME,'\','!') + '_' + > REPLACE(CONVERT(varchar(8),getdate(),110),'-','') + > REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80)) > > > John > . >
From: Dan on 6 Jul 2010 04:57 How about just checking to see if the InstanceName is null? SET NOCOUNT ON; SELECT CAST(CAST(SERVERPROPERTY('MachineName') as varchar(50))+ CASE WHEN SERVERPROPERTY('InstanceName') IS NOT NULL THEN '_' + CAST(SERVERPROPERTY('InstanceName') as varchar(50)) ELSE '' END + '_' + REPLACE(CONVERT(varchar(10),getdate(),110),'-','') + REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80)) This checks if SERVERPROPERTY('InstanceName') is NULL, if it isn't then it adds _<InstanceName>, if it is then it adds nothing. If you need the extra _ even if the instance is the default it's a little simpler to write SET NOCOUNT ON; SELECT CAST(CAST(SERVERPROPERTY('MachineName') as varchar(50))+ '_' + CAST(COALESCE(SERVERPROPERTY('InstanceName'),'') as varchar(50)) + '_' + REPLACE(CONVERT(varchar(10),getdate(),110),'-','') + REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80)) which simply uses COALESCE to convert a null to an empty string. You could even replace the empty string which something more obvious, such as DEFAULTINSTANCE, so it's clear it's the default instance involved. Dan "Ada" <Ada(a)discussions.microsoft.com> wrote in message news:C935210F-8911-437E-8E71-CC171BC31EDF(a)microsoft.com... > 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)) > > -- > SQL Server DBA > > > "John Bell" wrote: > >> On Wed, 30 Jun 2010 19:49:42 -0700, Ada >> <Ada(a)discussions.microsoft.com> wrote: >> >> >I just want to see host names of the connected SQL Server in the output >> >file >> >names. It's part of the requirements to cover named instance, clusters >> >etc. >> > >> >Thanks, >> >> You could try something like: >> >> for /F %%a in (SQL2005servers.txt) do ( >> FOR /F %%f IN ( 'sqlcmd -S %%a -E -i filename.sql -h-1') DO ( >> sqlcmd -E -S %%a -d master -i >> c:\iSEC\ROG_iSEC-SQLSr_2005_2008_201006220000.sql -Y30 -s "|" -o >> c:\iSEC\OUT2005\ROG_SQL_Server_%%f.out )) >> >> filename.sql contains: >> >> SET NOCOUNT ON; >> SELECT CAST(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS ') as >> varchar(50))+ '_' + REPLACE(@@SERVERNAME,'\','!') + '_' + >> REPLACE(CONVERT(varchar(8),getdate(),110),'-','') + >> REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80)) >> >> >> John >> . >>
From: Erland Sommarskog on 6 Jul 2010 05:04 Ada (Ada(a)discussions.microsoft.com) writes: > 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. Well, there is always coalaesce. But this seems better: SELECT CAST(CAST(SERVERPROPERTY('MachineName ') as varchar(50)) + '_' + CASE WHEN charindex('\', @@servername) > 0 THEN REPLACE(@@SERVERNAME,'\','!') ELSE @@servername + '!' + @@servername END + '_' + REPLACE(CONVERT(varchar(10),getdate(),110),'-','') + REPLACE(CONVERT(varchar(5),getdate(),114),':','') as varchar(80)) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: John Bell on 8 Jul 2010 03:54
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 |