While Loop to Create Login and Execute

Create Login if not exists.

drop table #temp

go

Create table #temp (id int identity(1,1),loginname varchar(50), [status] bit default 0)

go

insert into #temp (loginname) select 'domainname\aarthi'

insert into #temp (loginname) select 'domainname\AANTONIOU'

insert into #temp (loginname) select 'domainname\ARULM'

insert into #temp (loginname) select 'domainname\ahull'

insert into #temp (loginname) select 'domainname\atesfay'

insert into #temp (loginname) select 'domainname\BXMARQUEZ'

insert into #temp (loginname) select 'domainname\camodei'

insert into #temp (loginname) select 'domainname\rnicol'

insert into #temp (loginname) select 'domainname\rramasubramanian'

insert into #temp (loginname) select 'domainname\salderidge'

insert into #temp (loginname) select 'domainname\SBOOGICH'

insert into #temp (loginname) select 'domainname\SGENYERY'

insert into #temp (loginname) select 'domainname\shaJsan'

insert into #temp (loginname) select 'domainname\skhHanom'

insert into #temp (loginname) select 'domainname\SMORJAN'

insert into #temp (loginname) select 'domainname\SROBUERTS'

insert into #temp (loginname) select 'domainname\STAON'

insert into #temp (loginname) select 'domainname\SWERLSH'

insert into #temp (loginname) select 'domainname\sxpkearce'

insert into #temp (loginname) select 'domainname\vxgatrdner'

go

set nocount on;

while (select count(*) from #temp where [status] = 0) > 0

begin

    declare @id int = (select top 1 id from #temp where [status] = 0 order by id asc)

       declare @SqlStatement sysname

       declare @loginname varchar(50)

       select @loginname = loginname from #temp where id = @id

    select @id as idvalue, @loginname as loginname

       If not Exists (select loginname from master.dbo.syslogins

    where name = @loginName and dbname = 'master')

    Select @SqlStatement = 'CREATE LOGIN ' + QUOTENAME(@loginName) + '

    FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english])'

print @SqlStatement

    --EXEC sp_executesql @SqlStatement

update #temp set [status] = 1  where id = @id

end

set nocount off;

go

select * from #temp

SSRS Date Format using Expression

Any column display with value of 1/1/1900 12:00:00 AM even after the SQL Server Query output is correct, you need to follow the below IIF statement in expression of SSRS

SELECT [ReviewDate] AS  [ReviewDate] from Tablename.

Review Date is datetime.

Solution to SSRS is: Editing the value like below. If the value is Null or Empty, the below one is a good one for that issue

=IIF(Fields!ReviewDate.Value="1/1/1900 12:00:00 AM","",Format(Fields!ReviewDate.Value,"dd-MM-yyyy"))

In SQL Server, you can use the query

SELECT isnull(Convert(Varchar(10),[ReviewDate],105),'') AS  [ReviewDate] from TableName

However, although the above SQL display is correct, it won't help in SSRS. Therefore, one should follow the above IIF statments, that is

=IIF(Fields!ReviewDate.Value="1/1/1900 12:00:00 AM","",Format(Fields!ReviewDate.Value,"dd-MM-yyyy"))

Normal Date Format formula is as per below:

=Format(CDate(Fields!ReviewDate.Value), "dd-MM-yyyy") -- Works well

Find CLR enabled or not in SQL Server

Objective:

To find the CLR status in SQL Server.

Query:

SELECT @@servername as Servername, * FROM sys.configurations

WHERE name = 'clr enabled'

go

select * from sys.dm_clr_properties