https://drive.google.com/file/d/0B3Ios8rotFsYSVBBaHVMRFJDdWc/view?usp=sharing
Wednesday 28 October 2015
Demo Code( Send Video in Whatsapp using C#)
https://drive.google.com/file/d/0B3Ios8rotFsYSVBBaHVMRFJDdWc/view?usp=sharing
Thursday 22 October 2015
Send Mail using .Net and Sql Server
****************************** .Net *********************************************
try
{
string smtpAddress = "smtp.gmail.com";
int portNumber = 587;
bool enableSSL = true;
string emailFrom = "bhavdiptala@gmail.com";
string password = "****";
string emailTo = "bhavdip@dnkmail.in";
string subject = "Hello";
string body = "Hello, I'm just writing this to
say Hi!";
using (MailMessage mail = new MailMessage())
{
mail.From
= new MailAddress(emailFrom);
mail.To.Add(emailTo);
mail.Subject
= subject;
mail.Body
= body;
mail.IsBodyHtml
= true;
SmtpClient smtp = new SmtpClient(smtpAddress, portNumber);
smtp.Credentials
= new NetworkCredential(emailFrom, password);
smtp.EnableSsl
= enableSSL;
smtp.Send(mail);
}
}
catch (Exception ex)
{
}
****************************** Sql Server *********************************************
- Go to Object Explorer
- Management
- Right click on Database Mail and select “Configure Database Mail” as follows
- Next
- Select “Setup Database Mail by performing the
following tasks” as follows
- Next
- Enter profile name = “SQL Profile” and
description as follows
- Click on “Add” button and enter the following details. Always use your own email ID. Generally we have to use here the company email id. We have to raise a ticket to the mail server admin team to get the following details.
- Separate email id for SQL Server (This is From Email ID)
- SMTP server name
- Port number
- SSL feature should be enable or disable.
- OK
- Next
- Under Manage Profile Security option make the profile as public by selecting checkbox and default as follows
- Next
- Accept the default settings for System Parameters as follows
- Next
- Finish
- Close.
USE msdb
GO
EXEC sp_send_dbmail @profile_name='SqlProfile',
@recipients='bhavdip@dnkmail.in',
@subject='Test message',
@body='This is the body of the test message.'
Sunday 18 October 2015
Demo Code (Send Image in Whatsapp Usgin C# )
https://drive.google.com/file/d/0B3Ios8rotFsYMGZLMHpITXNtSG8/view?usp=sharing
Tuesday 13 October 2015
Fetching Excel Sheet Data in SQL using SQL Query
Fetching Excel Sheet Data in SQL using SQL Query
In order to Select, fetch or Read the Excel Sheet Data using SQL Query or Creating Table in SQL using Excel query first configure the advanced option through below scripts
step 1
sp_configure 'show advanced options', 1;
RECONFIGURE;
step 2
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 3
for MSExcel 2007 and 2010 --Database is the path of your excel sheet ,sheet1 is the name of sheet1
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=E:\book2.xlsx', 'SELECT * FROM [Sheet1$]');
Step 4 if you want to create a new table in sql based on excel data
for MSExcel 2007 and 2010 --Database is the path of your excel sheet ,sheet1 is the name of sheet1
SELECT * into test FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=E:\book2.xlsx', 'SELECT * FROM [Sheet1$]');
Case if you are using Excel 2003
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.4.0', 'Excel 4.0;Database=E:\book2.xls;HDR=YES', 'SELECT * FROM [Sheet1$]');
In case if you will get error like
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Run the below script to fix the Error
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
Note: You can use import export wizard ,SSIS also to import Data from excel sheet to sql
--Server Bit Check
SELECT SERVERPROPERTY('Edition')
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Monday 5 October 2015
Amount Digit to Word using Sql
CREATE FUNCTION [dbo].[AmountCent] ( @Money AS DECIMAL(30,2) )
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @Number AS BIGINT
SET @Number = FLOOR(@Money)
DECLARE @Below20 TABLE
(
ID INT IDENTITY(0, 1) ,
Word VARCHAR(32)
)
DECLARE @Below100 TABLE
(
ID INT IDENTITY(2, 1) ,
Word VARCHAR(32)
)
INSERT @Below20
( Word )
VALUES ( 'Zero' )
INSERT @Below20
( Word )
VALUES ( 'One' )
INSERT @Below20
( Word )
VALUES ( 'Two' )
INSERT @Below20
( Word )
VALUES ( 'Three' )
INSERT @Below20
( Word )
VALUES ( 'Four' )
INSERT @Below20
( Word )
VALUES ( 'Five' )
INSERT @Below20
( Word )
VALUES ( 'Six' )
INSERT @Below20
( Word )
VALUES ( 'Seven' )
INSERT @Below20
( Word )
VALUES ( 'Eight' )
INSERT @Below20
( Word )
VALUES ( 'Nine' )
INSERT @Below20
( Word )
VALUES ( 'Ten' )
INSERT @Below20
( Word )
VALUES ( 'Eleven' )
INSERT @Below20
( Word )
VALUES ( 'Twelve' )
INSERT @Below20
( Word )
VALUES ( 'Thirteen' )
INSERT @Below20
( Word )
VALUES ( 'Fourteen' )
INSERT @Below20
( Word )
VALUES ( 'Fifteen' )
INSERT @Below20
( Word )
VALUES ( 'Sixteen' )
INSERT @Below20
( Word )
VALUES ( 'Seventeen' )
INSERT @Below20
( Word )
VALUES ( 'Eighteen' )
INSERT @Below20
( Word )
VALUES ( 'Nineteen' )
INSERT @Below100
VALUES ( 'Twenty' )
INSERT @Below100
VALUES ( 'Thirty' )
INSERT @Below100
VALUES ( 'Forty' )
INSERT @Below100
VALUES ( 'Fifty' )
INSERT @Below100
VALUES ( 'Sixty' )
INSERT @Below100
VALUES ( 'Seventy' )
INSERT @Below100
VALUES ( 'Eighty' )
INSERT @Below100
VALUES ( 'Ninety' )
DECLARE @English VARCHAR(1024)
SET @English = ( SELECT CASE WHEN @Number = 0 THEN ''
WHEN @Number BETWEEN 1 AND 19
THEN ( SELECT Word
FROM @Below20
WHERE ID = @Number
)
WHEN @Number BETWEEN 20 AND 99
-- SQL Server recursive function
THEN ( SELECT Word
FROM @Below100
WHERE ID = @Number / 10
) + '-'
+ dbo.AmountCent(@Number % 10)
WHEN @Number BETWEEN 100 AND 999
THEN ( dbo.AmountCent(@Number / 100) )
+ ' Hundred '
+ dbo.AmountCent(@Number % 100)
WHEN @Number BETWEEN 1000 AND 999999
THEN ( dbo.AmountCent(@Number / 1000) )
+ ' Thousand '
+ dbo.AmountCent(@Number % 1000)
WHEN @Number BETWEEN 1000000 AND 999999999
THEN ( dbo.AmountCent(@Number / 1000000) )
+ ' Million '
+ dbo.AmountCent(@Number % 1000000)
ELSE ' INVALID INPUT'
END
)
SELECT @English = RTRIM(@English)
SELECT @English = RTRIM(LEFT(@English, LEN(@English) - 1))
WHERE RIGHT(@English, 1) = '-'
IF @@NestLevel = 1
BEGIN
SELECT @English = @English
END
RETURN (@English)
END
Saturday 3 October 2015
Friday 2 October 2015
Thursday 1 October 2015
Using xp_cmdshell to rename Files,Folder and Sub Folder using Sql
Exec master.dbo.xp_cmdshell 'rename C:\OldFileName.* NewFileName.*'
Exec master.dbo.xp_cmdshell 'rename C:\MasterFolderName MasterFolderNewName'
DECLARE @MasterFolder VARCHAR(15)
DECLARE @query NVARCHAR(MAX)
SET @MasterFolder = 'MasterFolder'
SET @query = 'exec master.dbo.xp_cmdshell ''rename C:\' + @MasterFolder
+ '\SubFolderName SubFolderNewName'''
PRINT @query
EXEC sp_executesql @query
Exec master.dbo.xp_cmdshell 'rename C:\MasterFolderName MasterFolderNewName'
DECLARE @MasterFolder VARCHAR(15)
DECLARE @query NVARCHAR(MAX)
SET @MasterFolder = 'MasterFolder'
SET @query = 'exec master.dbo.xp_cmdshell ''rename C:\' + @MasterFolder
+ '\SubFolderName SubFolderNewName'''
PRINT @query
EXEC sp_executesql @query
Subscribe to:
Posts (Atom)