Pabitra@Microsoft.Research: June 2017

Wednesday, 28 June 2017

sql :Assign comma in sql column , sql xml path

Syntax

STUFF(string, start, length, new_string)

SELECT STUFF('01234567', 'ketenumber position', 'keteta', 'replace value');
SELECT STUFF('01234567', 4, 2, ''); ---result 012567
ie 34 is gone
3 is at 4 position

select ','+ Roll_No from Candidate where Dist_code='02' for XML path('')

result : ,1,2,3,4,5,6,7

select stuff(
(
select ','+ Roll_No from Candidate where Dist_code='02' for XML path('')
),1,1,'')

result : 1,2,3,4,5,6,7

Tuesday, 20 June 2017

sql: restore sql database using sql query

--Step: 1
---------
--Get the FILELISTONLY of backup file in disk location
RESTORE FILELISTONLY
FROM DISK ='E:\Pabitra\dbSample.BAK'
-- Find the "LogicalName" column MDFLogicalName and LDFLogicalName which is use inthe next query
--My MDFLogicalName is dbSample and LDFLogicalName is dbSample_log
-- then Run the below code
--Step : 2
---------
RESTORE DATABASE dbNewName
FROM DISK ='E:\Pabitra\dbSample.BAK'
WITH MOVE 'dbSample' TO 'E:\Pabitra\dbNewName.mdf',
MOVE 'dbSample_log' TO 'E:\Pabitra\dbNewName.ldf'

Saturday, 17 June 2017

sql: get duplicate record in sql table column

SELECT Name, COUNT(Name) FROM Mst_Student GROUP BY Name HAVING (COUNT(Name)>1)

Friday, 16 June 2017

sql: date name from any date we input

SELECT DATENAME(dw,GETDATE()) -- Friday

SELECT DATENAME(dw,convert(datetime,'1989-11-23'))


SELECT DATENAME(M,convert(datetime,'1989-11-23'))
SELECT DATENAME(YYYY,convert(datetime,'1989-11-23'))

c# : date name from any date we input

DateTime myDate = DateTime.Now;
Response.Write(myDate.DayOfWeek.ToString());// result:Friday

DateTime myDate = DateTime.Now.AddDays(-65);
Response.Write(myDate.DayOfWeek.ToString());// result:Wednesday

DateTime myDate = DateTime.Parse("28/04/1990");// dd/MM/yyyy
Response.Write(myDate.DayOfWeek.ToString());// result Saturday

DateTime myDate = DateTime.Parse("23/11/1989");// dd/MM/yyyy
Response.Write(myDate.DayOfWeek.ToString());// result Thursday

Console.WriteLine(System.Threading.Thread.CurrentThread.CurrentUICulture.DateTimeFormat.GetDayName(DateTime.Parse("15/06/2017").DayOfWeek));

sql : drop database when it is used in another process

USE master;
GO
ALTER DATABASE dbSample SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE dbSample
GO

Thursday, 15 June 2017

html : scroll page title message , title of web page scroll

put this script in the header of the page
------------------------------------------
<script type='text/javascript'>
msg = "Welcome to our new Sample Test Website";
pos = 0;
function ScrollHeaderTitle() {
document.title = msg.substring(pos, msg.length) + msg.substring(0, pos); pos++;
if (pos > msg.length) pos = 0
window.setTimeout("ScrollHeaderTitle()", 200);
}
ScrollHeaderTitle();
</script>

My details page structure is as follows .
------------------------------------------
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script type='text/javascript'>
msg = "Welcome to our new Sample Test Website";
pos = 0;
function ScrollHeaderTitle() {
document.title = msg.substring(pos, msg.length) + msg.substring(0, pos); pos++;
if (pos > msg.length) pos = 0
window.setTimeout("ScrollHeaderTitle()", 200);
}
ScrollHeaderTitle();
</script>

</head>
<body>
</body>
</html>

Tuesday, 13 June 2017

sql : create procedure using transaction and output parameter

CREATE TABLE Customers(
CustomerId INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Name VARCHAR(100) NULL,
Country VARCHAR(100) NULL,
City VARCHAR(100) NULL
);
INSERT INTO Customers(Name,Country,City) VALUES('PABITRA','INDIA','PUNE');
INSERT INTO Customers(Name,Country,City) VALUES('PRAKASH','INDIA','GOA');

CREATE PROC USP_Sample
(
@CustomerId INT=NULL,
@Name VARCHAR(50)=NULL
@MSG VARCHAR(100)=NULL OUTPUT
)
AS BEGIN
DECLARE @errorCount INT,@STATUS VARCHAR(50);
SET @errorCount=0;
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRAN _SampleTransation
SELECT CustomerId,Name,Country,City FROM Customers WHERE CustomerId=@CustomerId AND Name=@Name;

IF(@@ERROR<>0)
BEGIN
SET @MSG='An Error Occurs Please try again .';
SET @errorCount=1;
END
IF(@errorCount<>0)
ROLLBACK TRAN _SampleTransation
ELSE
COMMIT TRAN _SampleTransation

END

Friday, 9 June 2017

div to pdf using jsPDF

<html>
<head>
<title>Demo Pdf Print </title>
</head>
<body>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/0.4.1/html2canvas.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/1.0.272/jspdf.debug.js"></script>
<button id="btnPrint">generate PDF</button>
<div id="divExportToPDF">
<table style="width:100%">,
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>
<tr>
<td>Jill</td>
<td>Smith</td>
<td>50</td>
</tr>
<tr>
<td>Eve</td>
<td>Jackson</td>
<td>94</td>
</tr>
</table>
</div>
<script type="text/javascript">
$(document).ready(function () {
$('#btnPrint').click(function () {
var pdf = new jsPDF('p', 'pt', 'a4');
pdf.addHTML(document.body, function () {
pdf.save('SamplePDF.pdf');
});
});
});
</script>
</body>

</html>

SQL :no of Year back from given date or Subtract exactly a year from given date

DECLARE @FromDate date,@NoOfYearBack int;
set @FromDate= '2017-07-01'; set @NoOfYearBack=14;
select dateadd(year, -@NoOfYearBack, @FromDate)

Pabitra@Microsoft.Research

Translate