Creating Linked Server In Microsoft SQL Server with Oracle

Hello folks. i recently experienced scenario where the two environments have to be linked up for direct communication. so i found the following process to link up MS SQL to Oracle.

Note: The following technologies are considered in this tutorial:
1- Oracle 11 g (IDE: SQL Developer)
2- Microsoft SQL Server 2012

1) In windows first open sqlnet.ora file under the folder Program Files (X86)-> Oracle Developer Tools-> network ->admin

2) Change SQLNET.Authentication_Services = (NTS) to SQLNET.Authentication_Services = (NONE)

3) Now open SQL Server

4) In server objects goto Linked Server-> Providers and on OraOLEDB.Oracle right click.

Set the following properties:

Enable:  a)Dynamic Parameter b)Nested queries c)Allow Inprocess  d)Supports ‘Like’ operator

5) Now rightclick on LinkedServer and select “New Linked Server” option

6) For "New Linked Server

• Give a name to the server.
• Select Other data source
• In provider select OLE DB Provider for Microsoft Directory Services
• In Product Name enter the name of the provider of Oracle (under LinkedServer->Providers) . Here OraOLEDB.Oracle.
• In DataSource enter the Oracle data source. Here “localhost:1521/xe”
• In Provider string enter the entire connection string for Oracle:          
  Here “User ID=rizvi; Password=123; Data Source=localhost:1521/xe; Pooling=false;”

7) In Security page:

     Select Option “Be made using the login’s current security context

8) Data Fetching:

    a.Check the table data in oracle

    b.Check the same data in SQL server (In MSSQL query oracle data with Adhoc query option of OpenRowSet

Example MSSQL query for Oracle:

select * from

openrowset('OraOLEDB.Oracle','xe';'rizvi';'123',

'select * from hmis_tri_counter');

That is all folks for creating a linked server. Happy querying.

Take care.

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