Shrink SQL server database log

Database log increases with increasing transactions and database activity. At times it is not required to maintain the log after some period. So in such cases it becomes quite beneficial to shrink the database log file as it saves much disk space. Below query can be used to do the needful.

Output:

sometimes it is better to set the database to simple recovery before compressing log.

Consider below query in those cases:
use @databaseName;
Go 
ALTER DATABASE @databaseName SET RECOVERY SIMPLE
DBCC shrinkfile (@logFileName, 8);
ALTER DATABASE @databaseName SET RECOVERY FULL

Sending Email via SMTP client

Simple Mail Transfer Protocol commonly known as SMTP is the protocol for sending and receiving email. .NET allows to use this SMTP service via its SMTP client.

Consider the below code:

System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587);
                            smtpClient.UseDefaultCredentials = false;
                            smtpClient.Credentials = new System.Net.NetworkCredential("abc@live.com", "hr1?");
                            smtpClient.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
                            smtpClient.EnableSsl = true;
                            smtpClient.Timeout = 20000;
                            System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
                            
//Setting From , To and CC
                            mail.From = new System.Net.Mail.MailAddress("abc@live.com", "Game");
                            mail.To.Add(new System.Net.Mail.MailAddress("efg@live.com"));
                            smtpClient.Send(mail);

• In the above code first an object of smptClient is created. 
• Host server name (here smtp.gmail.com) and port number (here 587)  is provided as parameter.
• We do not want to use default credentials here so just set the respective attribute of smtpClient to false.
• Here we use the email "abc@live.com" with password "hr1?" as the sender credentials.
• Delivery method of SMTP is set to Network so that mail is send via network.
• EnableSsl is set true to make the transmission secure by SSL (secure socket layer)
• Setting timeout of mail process to 20000 milliseconds (20 seconds)
• Mail message object "mail" is instantiated.
• Mail from (here abc@live.com with name "Game") and mail to (here efg@live.com) is set
• smtpClient sends the provided mail with Send() function

Retrieve Comma Separated Values

Some times there are certain scenarios where data needs to be gathered as a collection. For instance multiple records need to be set as single record, in a such cases setting row data as comma separated column value can be quite effective.

SQL server allows to incorporate this with its built-in feature of stuff allowing row data to be merged as a single column value.

Code:

select  

(STUFF((SELECT ',' + CAST(a.username AS VARCHAR(10))+''' ' [text()]

FROM [users] a

FOR XML PATH(''), TYPE)

.value('.','NVARCHAR(MAX)'),1,2,' '))

as AllNames 

Output:

Reading data from local resource - .NET WCF Service

Hello. We can read data from local resource files in WCF service as follows:

The below code snippet is a function "translations" which returns a JavaScript object of all the text read from "default.resx" resource file

//Reading data from resource file
        public string translations()
        {

//1- Assign variables for key and values and xml document and node which will identify a text from resource node

            string key = "", value = "";
            XmlDocument loResource;
            XmlNodeList elemList;

            //Reading data from ResourceFiles
            #region translations
            loResource = new XmlDocument();
            loResource.Load(HostingEnvironment.MapPath("~") + "App_LocalResources\\default.resx");
            elemList = loResource.GetElementsByTagName("data");
         
//2- Read Data from each data node
            nameDictionary = new Dictionary<string, string>();
            for (int i = 0; i < elemList.Count; i++)
            {
                key = elemList[i].Attributes["name"].InnerText.Trim();
                value = elemList[i].InnerText.Trim();
                nameDictionary.Add(key, value);
            }
            #endregion
            //----End Translations

//3- Return serialized JSON object of key/value pair dictionary
            return serializer.Serialize(nameDictionary);
        }

Sample Output:

{"btnBegin":"Begin","btnFilter":"Filter","btnSave":"Save","btnStartIssue":"Report Issue","btnStartTask":"Start Task","ltrAddProject":"Add Project","ltrAddResource":"Add Resource","ltrCloseIssue":"Close Issue","ltrDetail":"Task Detail","ltrDuration":"Duration In","ltrEndTask":"End Task","ltrIssue":"Issue Title" ,"ltrLinkage":"Linkage", "ltrPassword":"Password","ltrPowererdBy":"Powered by Wavetec","ltrPRLinkage":"Project-Resource Linkage" ,"ltrProject":"Project","ltrProjectName":"ProjectName","ltrResource":"Resource","ltrResourceName":"ResourceName", "ltrResourceType":"ResourceType","ltrStartDate":"StartDate","ltrTask":"Task Title","ltrTaskMng":"Task Management","ltrUserName":"UserName"}

User-Defined Functions in SQL Server

In SQL Server user can define function to return a table or some value. Following are two ways of creating function:

1- Returning Table

-----------------Returing table

create function printTwo

(@projectId varchar, @resourceId varchar)

returns table as

return

(

select @projectId as [proj],@resourceId as [resc]

);

2- Returning Scalar(single) value

-----------------Returing scalar

create function printOne

(

@value int

)

returns int

WITH EXECUTE AS CALLER 

as

begin

return @value

end 

3- Checking the output by calling in different ways 

select dbo.printOne(1) 'Result';

select * from dbo.printTwo('1','2');

select dbo.printOne(1) 'Result', * from dbo.printTwo('a','b');

Export sql server query output to CSV - via BCP utility

To export SQL query data to a comma separated value (CSV) file we can use Bulk Copy Program (bcp) utility. Follow below steps to get the desired CSV file.

1. Enable xp_cmdshell property to use the bcp utility 
   
2. Write the desired query and passed it in the xp_cmdshell command 
   

    3. Output CSV

HTTP Request Via SQL Server Query

Hello again, in this post we will see how to perform an Hypertext transfer protocol (HTTP) request through SQL server query. However remember that this action is not a general practice, it should be performed for specific cases like for instances:

• Data to be posted to some system at day end or specific time interval
• XML or JSON based data needs to be retrieved from integrated systems
• In a case where security is not a major concern (like internal systems)

This method is supposed to be less efficient in terms of execution time as compared to the traditional web request done by the web applications. So I would recommend to use this only if you have a specific requirement like ones mentioned above or if you do not have any other way then this.

Following are steps to create HTTP request via SQL query:

1. First check if  OLE (Object Linking and Embedding) Automation Procedures are enabled from the following built-in stored procedure:

           EXEC sp_configure 'Ole Automation Procedures'; 

           The config_value and run_value should be 1

     2. If not enabled already then execute another built-in SQL SP of sp_configure:

           exec sp_configure 'show advanced options', 1;  

           GO  

           RECONFIGURE;  

           GO  

           exec sp_configure 'Ole Automation Procedures', 1;  

           GO  

           RECONFIGURE;  

           GO  

    3. Now to perform a simple request consider the below query where data is retrieved in JSON format:

DECLARE @obj int,

        @url VarChar(MAX),

        @response VarChar(8000),

        @requestHeader VarChar(MAX),

        @requestBody VarChar(MAX),

@status int

SET @url = 'http://65.181.112.199:88/default.aspx?source=CategoryCombo'

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @url, 'false'

Exec sp_OAMethod @obj, 'Send'

EXEC sp_OAGetProperty @obj, 'responseText', @response OUT

Exec sp_OAMethod @obj, 'status', @status OUTPUT

EXEC sp_OADestroy @obj

SELECT @response [RESPONSE], @status [Status]

   4. Result:

For more information visit stack overflow:

http://stackoverflow.com/questions/19307704/how-read-json-data-from-url-in-sql-server

SQL Server Stored Procedure with input parameter

Hello. Hope you are doing good. In this post we will learn how to create an SQL stored procedure with input parameter.

Consider following steps:

1. Use create procedure command to create procedure like "create procedure abc"
2. Define input parameter with @ to denote parameter values like '@Name varchar(15)', note that @Name is a parameter of type varchar with 15 character length. You can add multiple parameters by separating each with a comma (,)
3. Write the SQL command you want the procedure to execute. For this example we will print name with today's date:

     create procedure showDateWithName 

     @Name varchar(15)

     as

     begin

     select 'Hello '+@Name +'! today is '+ convert(varchar,GETDATE(),106);

     end

• To use the stored procedure run the execute command:

       execute showDateWithName 'Hasan'

Procedure Result