Sql+(Plus)

Duplicate Record Count

SELECT admissionno, COUNT(*) TotalCount FROM tbstudentmaster

GROUP BY admissionno

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC

Remote Desktop Not Working

You can disable blank password restrictions by using a policy. To locate and change this policy:

  • Click Start, point to Run, type gpedit.msc, and then click OK to start the Group Policy Editor.
  • Open Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options\Accounts:
  • Limit local account use of blank passwords to console logon only.
  • Double-click Limit local account use of blank passwords to consol logon only.
  • Click Disabled, and then click OK.
  • Quit Group Policy Editor.

Age Calculator (jQuery)

 

function getAge(birthday) {

var today = new Date();

var thisYear = 0;

if (today.getMonth() < birthday.getMonth()) {

thisYear = 1;

} else if ((today.getMonth() == birthday.getMonth()) && today.getDate() < birthday.getDate()) {

thisYear = 1;

}

var age = today.getFullYear() – birthday.getFullYear() – thisYear;

return age;

}

Linked Server with MS-Access 2007

 

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver

@server = N’Dentist’,

@srvproduct=N’ACCESS 2007′,

@provider=N’Microsoft.ACE.OLEDB.12.0′,

@datasrc=N’C:\Documents and Settings\Administrator\Desktop\DATA20708.mdb’

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’Dentist’,

@locallogin = NULL ,

@useself = N’False’

Auto Create Table Procedures in SQL Server (Insert,Update,Delete,Select)

 

Create procedure sp_AutomaticInsertUpdateDeleteProc(@TableName as varchar(250),@Cols as varchar(8000),@IDCol as varchar(max))  —                   sp_AutomaticInsertUpdateDeleteProc ‘Tbdepartments’,”,’Deptid’

as begin

Declare @prc table (txt varchar(max))

declare @sql varchar(8000),@Para varchar(max)

declare @idcol1 as varchar(max)

Set @idcol1=@idcol

if @Cols=” begin

 

SELECT @Cols = COALESCE(@Cols+’,~’ ,”) +name+ ‘ ‘ +case is_ansi_padded when 1 then type_name(system_Type_Id)+'(‘+

case max_Length

when -1 then ‘max’

else cast(max_length as varchar)

end +

‘)’ else

type_name(system_Type_Id)+

case when Precision>0 and Scale>0 and  type_name(system_Type_Id)<> ‘datetime’ and  type_name(system_Type_Id)<> ‘money’

and  type_name(system_Type_Id)<> ‘smallmoney’ then ‘(‘+ cast(Precision as varchar)+’,’+cast(Scale as varchar)+’)’ else ” end

end

from sys.columns where object_name(object_Id)=@TableName  and name<>@IDCol

and type_name(system_Type_Id)<> ‘timestamp’ order by name

SELECT @para = COALESCE(@para+’,’ ,”) +name from sys.columns where object_name(object_Id)=@TableName  and name<>@IDCol and type_name(system_Type_Id)<> ‘timestamp’ order by name

SELECT @IDCol =’@’+ @IDCol + ‘ ‘ +case is_ansi_padded when 1 then type_name(system_Type_Id)+'(‘+ cast(max_Length as varchar)+’)’ else type_name(system_Type_Id) end  from sys.columns where object_name(object_Id)=@TableName  and name=@IDCol

set @Cols=substring(@Cols,2,len(@Cols)-1)

end

print @Cols

print @para

print @IDCol

—Insert Procedure

insert into @prc values( ‘–                   Insert Procedure ‘)

–insert into @prc values( ‘Go’)

set @para=substring (@IDCol,2,charindex(‘ ‘,@IDCol,1)-1)+’,’+@para

set @Cols=@IDCol+’,’+@Cols

set @sql=’Create Procedure prcInsert_’+ rtrim(ltrim(@tableName)) + ‘ (‘

insert into @prc values( @sql)

set @sql=+replace( @Cols ,’,~’,’,@’)+ ‘) as begin ‘

insert into @prc values( @sql)

set @sql=’insert into ‘  +rtrim(ltrim(@tableName)) + ‘ ( ‘ +@para + ‘ ) values ( ‘+’@’+replace( @para ,’,’,’,@’)+ ‘ ) End ‘

insert into @prc values( @sql)

insert into @prc values( ‘Go’)

insert into @prc values( ”)

insert into @prc values( ”)

—Split Columns in Table

Declare @index int

Declare @index1 int

Declare @Char int

Declare @Char1 int

Declare @exp varchar(max)

Declare @exp1 varchar(max)

set @exp=replace( @Cols ,’,’,’,@’)

set @exp1=replace( ‘@’+@para ,’,’,’,@’)

set @index =0

set @index1 =0

set @Char =0

set @Char1 =0

print @exp

print @exp1

print @para

create table #temp (data varchar(max),data1 varchar(max))

while @exp> ” begin

Select @Char=charindex(‘,’,@exp,@index)

Select @Char1=charindex(‘,’,@exp1,@index1)

if @char>0 begin

insert into #temp select substring(@exp1,@index1,@CHAR1),substring(@exp,@index,@CHAR)

SET @Exp=substring(@exp,@CHAR+1,len(@exp))

SET @Exp1=substring(@exp1,@CHAR1+1,len(@exp1))

end

else begin

insert into #temp select @exp1,@exp

SET @Exp=”

end

end

—Update Procedure

insert into @prc values( ‘–                   Update Procedure ‘)

set @sql=’Create Procedure prcUpdate_’+ rtrim(ltrim(@tableName)) + ‘ (‘+replace( @Cols ,’,~’,’,@’)+’)  as begin

update ‘ + @tableName + ‘ set ‘

Declare @p1 varchar(max),@p2 varchar(max)

Declare cur  cursor for select * from #temp where data<>@IdCol–into @p1,@p2

open cur

FETCH NEXT FROM cur into @p1,@p2

while @@fetch_status =0 begin

set @sql=@sql + right(@p1,len(@p1)-1) + ‘=’ +  @p1

FETCH NEXT FROM cur into @p1,@p2

if  @@fetch_status =0 begin

set @sql=@sql +’, ‘

end

end

close cur

deallocate  cur

set @sql=@sql + ‘ where ‘+ @idcol1 + ‘=@’+@idcol1 + ‘  End ‘

insert into @prc values( @sql)

insert into @prc values( ‘Go’)

—   Delete Proc

insert into @prc values( ‘–                   Delete Procedure ‘)

set @sql=’Create Procedure prcDelete_’+ rtrim(ltrim(@tableName)) + ‘ (‘+ @idCol+’)  as begin

delete from ‘+ rtrim(ltrim(@tableName)) +’ where ‘+ @idcol1 + ‘=@’+@idcol1 + ‘  End ‘

insert into @prc values( @sql)

select * from @prc

–Select * from #temp

drop table #temp

insert into @prc values( ‘Go’)

end

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s