Powershell Datatypes

############# Print on Console #############################

write-host 'hello world.. welcome to powershell !!!'



##################### Datatypes  ###########################

[int]$i = 10234234
[long]$l = 103243423423423
[decimal]$d = 33.42
[char]$c = "A"
[string]$s = "Kalyan"
$domains = @("hyddomain", "usdomain", "ukdomain", "chinadomain")
write-host "Integer Value is :" $i
write-host "Long Value is :" $l
write-host "Decimal Value is :" $d
write-host "Character Value is :" $c
write-host "String Value is :" $s
write-host "Array Values are :" $domains
write-host "Array Length is :" $domains.count
$l = 2 * 44 /2 *145 -33
write-host "Long value in calculation is :" $l

Trace Flags

What is a trace flag?

These are used to assign specific characterstics or switch off particular behavior temporarily or permanently.

How to assign trace flag

1. SQL Server configuration manager - SQL Server - Properties - Advanced- Startup parameters
2. Add Trace flag(s) to engine -T4199
(Or)
dbcc traceon (4199, -1)
dbcc traceoff(4199, -1)   (-1 switches trace flag globally)
(Or)

net start Mssqlserver\InstanceName  /T4199



 

The process terminated unexpectedly SQL Server Agent

SQL Server Agent could not started because of the below error
The process terminated unexpectedly

First we need to identify what the issue causing agent not to start, to do so follow the below process
Identify SQLAgent executable file location and run it from the command prompt as below
c:\mssql> sqlagent -c -v >sqlagent.out
opent the sqlagent.out file to identify the issue, I have the below issue in .out file

! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
! [298] SQLServer Error: 18456, Login failed for user 'domain\username'. [SQLSTATE 28000]
! [382] Logon to server '(local)' failed (DisableAgentXPs)
? [098] SQLServerAgent terminated (normally)
Then I realized that SQL Agent is not able to contact the installed instance,  I tried several ways to fix it finally I have added the SQL Service account to SQL Server as a windows login and provided sysadmin rights to it, later I can able to start the agent.



 

Display SQL Server startup parameters using T-SQL

DECLARE @ROOTKEY VARCHAR(30), @INSTANCENAME VARCHAR(100), @KEY VARCHAR(200), @INSTANCEVERSION VARCHAR(50), @INSTANCEVALUE VARCHAR(200)

      DECLARE @StartupParams VARCHAR(100)
      DECLARE @ver varchar(140)
      SELECT @ver = CAST(serverproperty('ProductVersion') AS varchar)
      SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
      PRINT @ver

      DECLARE @tbl_values TABLE(id INT IDENTITY(1,1), server_name VARCHAR(200) DEFAULT @@SERVERNAME, keyname VARCHAR(40), keyvalue VARCHAR(200))
      SELECT @InstanceVersion = @@serviceName
      EXEC master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
      'SOFTWARE\MICROSOFT\\Microsoft SQL Server\\Instance Names\\SQL', @InstanceVersion, @InstanceValue OUTPUT

      SELECT @InstanceName = @InstanceValue
      SET @RootKey = 'HKEY_LOCAL_MACHINE'
      SET @Key ='SOFTWARE\Microsoft\\Microsoft SQL Server\' + @InstanceName + '\MSSQLServer\Parameters'
      INSERT INTO @tbl_values (keyname, keyvalue) EXEC master..xp_regenumvalues @RootKey, @Key
      SELECT @StartupParams = Keyvalue + ',' + ISNULL(@StartupParams,'')  FROM @tbl_values WHERE id>3
      SELECT CASE WHEN @StartupParams IS NULL THEN '' ELSE left(@StartupParams,LEN(@StartupParams)-1) END

SQL Server 2008 R2 Setup Fails Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes

You may encounter the below error while performing SQL Server 2008 R2 Installation

At the end of installation you will see a report showing SQL Server database services failed etc., etc.,

workaround
1. Open SQL Server Configuration manager and try to change the service account name to any local admin account.
2. You can start the service though you could not login into SQL Server using the current credentials
3. Stop SQL Server from Configuration manager
4. Start SQL Server from command prompt using below command
net start MSSQLServer /c /m /T3608
5. Try login to server from SSMS and add your login as sysadmin, If not use the below command to login into SQL Server using DAC from command prompt
6. sqlcmd Admin:SQLServername\InstanceName
7. sp_addsrvrolemember 'domain\username', 'sysadmin'
8. stop and start sql server using configuration manager
9. It helps to login into SQL Server then you can add logins whatever are required.

 

How to convert LITESPEED backup file to SQL Server Native backup

Convert LITESPEED backup file to SQL Server Native backup

Copy Extractor.exe from the following path of LITESPEED Installed machine
%PROGRAM_FILES%\LiteSpeed\SQL Server

(OR)

Download and Install trail version of LITESPEED and copy Extractor.exe to target machine

1. Copy LITESPEED backup file to target server

2. Convert LITESPEED backup to SQL Server native backup using the below command

extractor.exe -F "Drive:\BackupFilename" -E "Drive:\BackupFilename_native.bak" -N 1

-F – Path of Litespeed backup file name
-E –Path to convert litespeed backup to native backup
-N – Number of files to extract from backup file

After extraction the backup native backup files will extracted to specified path

3. Restore native backup using the following syntax on source server

RESTORE DATABASE [OVM_Stg] FROM 
DISK = N'Drive\FileName_FULL_native.bak0',
DISK = N'Drive\FileName_FULL_native.bak1',
DISK = N'Drive\FileName_FULL_native.bak2',
DISK = N'Drive\FileName_FULL_native.bak3',
DISK = N'Drive\FileName_FULL_native.bak4',
DISK = N'Drive\FileName_FULL_native.bak5'
WITH  RECOVERY, 
MOVE N'Filename' TO N'C:\mdfs\dbname.Mdf', 
MOVE N'Filename_log' TO N'C:\Ldfs\dbname_log.ldf',
REPLACE,  STATS = 10
GO

 

Export to excel with column headings using SQL Server

Procedure to export data into excel with columns

create procedure sp_generate_excel_with_columns
(
@server_name varchar(100),
@db_name varchar(255),
@table_name varchar(100),
@file_name varchar(100)
)
as
declare @del_command varchar(200)
declare @file_exists INT
declare @file_path varchar(200)
select @file_path = substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))
--select @file_path
EXEC xp_fileexist @file_name, @File_Exists OUT
If @file_exists = 1
set @del_command = 'exec master..xp_cmdshell ''del ' +  @file_name +  ''''
exec(@del_command)
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(200)
select @columns=coalesce(@columns+',','')+column_name+' as '+column_name
from  information_schema.columns  where  table_name =@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--print @columns
--Dummy file to storate actual data from table
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
--Create EXCEL file with column names
set @sql='exec master..xp_cmdshell ''bcp "select * from (select '+@columns +') as t" queryout "'+ @file_name + '" -S' + @server_name + ' -d' +  @db_name +' -T -c'''
exec (@sql)
--Populate dummy file with data
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -S' + @server_name + ' -d' +  @db_name +' -T -c'''
exec (@sql)
--Contact dummy file with data to original file with columns
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec (@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec (@sql)

---- Execute Syntax
exec sp_generate_excel_with_columns @@servername,'Database_Name','Table_Name','c:\Temp\File_Name.xls'
 

T-SQL to verify file exists

T-SQL to verify file exists

declare @file_path varchar(100)
declare @file_exists int

set @file_path = 'C:\Temp\RESOURCES_BKP_OUTPUT.xls'

exec master.dbo.xp_fileexist @file_path, @file_exists output

if @file_exists = 1
 Print 'File Exists'
else
 Print 'File not found'