Search This Blog

Sunday, July 31, 2011

Creating a linked Server on SQL Server

EXEC sp_addlinkedserver @server='GM', @srvproduct='',
@provider='SQLOLEDB', @datasrc='databaseIP,Port',
@catalog ='DBName'
EXEC sp_addlinkedsrvlogin 'GM', 'false', NULL, 'DBUser', 'DBPassword'

--The Query, Using the Server Database you are already in and your new linked server: GM--
SELECT
NUMBER,
DT_CREATED AS DATE_CREATED,
PRIORITY,
(SELECT COMPANY FROM GM.myDatabase.dbo.myTable WHERE ACCOUNTNO IN (SELECT TOP 1 ACCOUNTNO FROM GM.myDatabase.dbo.myTable WHERE Field1=(SELECT CUSTOMER_NUMBER FROM CUSTOMERS WHERE ID=ID_CUSTOMER))) AS COMPANY FROM MAIN_TABLE

Thursday, July 28, 2011

Another way to Pass list of parameters to SQL Server Stored Procedures


The following are the ways you could have solved

1) Passing inputs (all Ids at once) as a comma separated string
2) Since SQL Server 2005 is CLR compliant, you could have written a dll which could have performed the above task at a better performance rate

3) But third option is passing all the inputs as XML. And XML parsing may be complex but this approach will be far more efficient. Have a look at the code below

CREATE   PROCEDURE  SP_EMPLOYEES @ids xml   AS
    SELECT  E . EMPLOYEEID ,  E . MANAGERID ,  E . NAME
    FROM    TBLEMP E
    JOIN    @ids . nodes ( '/Root/Employee' )   AS  T ( Item )
      ON    E . EMPLOYEEID =  T . Item . value ( '@num' ,   'int' )
go
EXEC  SP_EMPLOYEES N '<Root><Employee num="1"/><Employee num="2"/>
                             <Employee num="3"/><Employee num="4"/></Root>'

Tuesday, July 12, 2011

Opening DTS packages on SQL Server 2005 Management Studio

For opening DTS packages on SQL Server Management for 2005 and above, you will have to install the following components
1. DTS component for Sql Server 2005 Client
2. Backward Compatibility
from the following link

http://www.microsoft.com/download/en/details.aspx?id=15748

The names of the MSIs will be something like this SQLSERVER2005_DTS.msi anf SQLSERVER2005_BC.msi.

When done with the above installation, please make sure that if you have multiple installations of SQL Server, in Environment variable->System Variables, the entry of SQL Server 2000 Tools should be before the latest version like below

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;


Sharepoint 2010 on Windows 7

This url has the full instructions on how to go about this. http://sharepoint-2010-world.blogspot.com/2011/02/install-sharepoint-foundation-2010-on.html

Will try this once I am done with Windows 7 upgrade on my desktop. Also this site is helpfull
http://mkdot.net/blogs/zzl/archive/2008/06/18/install-vs-2005-2008-extensions-for-sharepoint-on-windows-xp-vista-os.aspx

Sunday, July 3, 2011

Connecting Oracle 10g Developers Suite Forms to Oracle 10G Express

Hi, today I was trying to do a hands-on on Oracle Forms builder. I found a very nice tutorial at 

You can download the Oracle 10g Developers Suite for Free at the Oracle Site (http://www.oracle.com/technetwork/developer-tools/developer-suite/downloads/index.html) and also Oracle 10g Express at http://www.oracle.com/technetwork/database/express-edition/downloads/index.html . The installations are pretty straight forward and its not where you will see a road block, but its when you try to connect from Form's Developer or SQL plus to the Express Instance you will have some weird issues if unknowingly your machine has some old versions of Oracle tnsnames.ora apart from the places where you have installed the new express editions and believe me its a real pain in the butt.

So you will get an error like "ORA-12154 TNS:Could not resolve the connect identifier specified" and here the things that you need to check first

1. Do a global search on your local machine for the file names "tnsnames.ora" and if you see multiple occurrences, you can be sure that is the problem. In my case, I was sure that I will not need the other tnasnames.ora files apart from the one in the location of the 10g express so I simply renamed those tnsnames.ora files to tnsnames.ora1 and sqlnet.ora (if there was one in those locations) to sqlnet.ora1.

2. In the drive where you have installed your express database ( In my case its D:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN ), you will find that by default your tnsnames.ora will have an entry like below

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CHIRES35510)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Now, please change the SERVICE_NAME = XE to SID = XE like below

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CHIRES35510)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = XE)
    )
  )
** In the first case for people who need to keep their old tnsnames.ora, please create an environment variable "TNS_ADMIN" and in my case it points to "D:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN".

Once done, please save and restart the following 2 services on windows.
1.OracleServiceXE
2.OracleXETNSListener

PS: This blog helped me as it had similar problems discussed and I consolidated it for future references
http://dbaspot.com/oracle-server/71759-tns-listener-could-not-resolve-service_name-given-connect-descriptor-3.html

Saturday, January 1, 2011

Custom Error Pages in ASP.NET

The default error page is a catchall for any type of error that may occur for resources managed by ASP.NET, so it has to be relatively generic. In some cases we might need to create custom error pages as the generic pages will not tell the whole story. For example, if a user attempts to access a nonexistent resource managed by ASP.NET, then the user receives a 404
error. If, however, you have only specified a default error page, then ASP.NET displays that error page without ever mentioning that the resource was not found. This makes it appear as though the resource exists and is having errors.


To avoid this we could create a 404 error page that informs the user that the page they are trying to access does not exist so they know they entered the wrong URL. We could even go as far as displaying a site map, links, or a search box to allow the user to more easily locate their desired content. Defining custom error pages requires adding an <error> inner element to the
<customErrors> element in Web.config. The following lines show an example that defines a custom error page for the 404 error.


<config>
...
<system.web>
...
<customErrors mode="On" defaultRedirect="~/ErrorPages/GenericError.html">
<error statusCode="404" redirect="~/ErrorPages/Error404.html"/>
</customErrors>
</system.web>
</config>


The statusCode parameter of the <error> element defines the server status code for which the error page should be returned. The redirect parameter defines the actual error page location. In this example, if a 404 error occurs, ASP.NET redirects the user to the Error404.html page. Because the 404 error is the only custom error defined, any other error will cause the user to be redirected to the GenericError.html page.

Tuesday, December 21, 2010

Custom Configuration Handler

I was going through a book about a different ways of reading configuration file and then I came across a scenario where it requires to get icon image url based on extensions of File. Of course there may be several ways of solving this issue but to me the most interesting one was to creating a section in the web.config for the extension's Image Url and a custom configuration section handler to read the same when used in an application. The code snippets are taken from the book but I made a solution to demonstrate its use in an actual scenario.


1) Lets say we plan to create the following mapping in xml to map a particular extension of file to Image Url.



               <unknownIcon imageUrl="Icons/unknown.gif" description="Unknown File Type"/>
<icon imageUrl="Icons/word.gif" description="Word Processing Document">
<ext>.DOC</ext>
<ext>.RTF</ext>
<ext>.TXT</ext>
</icon>
<icon imageUrl="Icons/excel.gif" description="Excel Document">
<ext>.XLS</ext>
<ext>.CSV</ext>
</icon>
<icon imageUrl="Icons/image.gif" description="Picture/Image">
<ext>.GIF</ext>
<ext>.TIFF</ext>
<ext>.JPG</ext>
<ext>.BMP</ext>
<ext>.PNG</ext>
</icon>
2) Let us create the Model class which will hold this data during run time, so I create a Class Library Project called "IconConfiguration" and name the newly added class as "IconConfigurationItem".



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace IconConfiguration
{
    public class IconConfigurationItem
    {
        private string _image;
        private string _description;
        private string _extension;


        public IconConfigurationItem(string image, string description, string extension)
        {
            this._image = image;
            this._description = description;
            this._extension = extension;
        }


        public string Image 
        { 
            get
            {
                return this._image;
            }
            set
            {
                this._image = value;
            }
        }
        public string Description 
        {
            get
            {
                return this._description;
            }
            set
            {
                this._description = value;
            }
        }
        public string Extension
        {
            get
            {
                return this._extension;
            }
            set
            {
                this._extension = value;
            }
        }
    }
}

3) The next step is to create a collection class which will hold each of these IconConfigurationItem. So I added another class to my Class Library Project and named it "IconConfigurationCollection". 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace IconConfiguration
{
    public class IconConfigurationCollection : CollectionBase
    {
        public IconConfigurationItem unknownIconInfo;
        private int getExtensionIndex(string extension)
        {
            int index = 0;
            for (index = 0; index < this.List.Count; index++)
            {
                IconConfigurationItem item = (IconConfigurationItem)this.List[index];
                if (item.Extension.Equals(extension.ToUpper().Trim()))
                    return index;
            }
            return -1;
        }
        public IconConfigurationItem getIconInfo(string extension)
        {
            int index = getExtensionIndex(extension);
            if (index == -1)
                return unknownIconInfo;
            else
                return (IconConfigurationItem)(this.List[index]);
        }
        public void Add(IconConfigurationItem obj)
        {
            this.List.Add(obj);
        }
    }
}

4) The very next step will be to create the Configuration Handler which will read the particular XML from my Web.config. It must inherit from IConfigurationSectionHandler and when you do that the intelligence will also write the method Create which needs to be implemented. My Configuration Handler class name is "IconConfigurationHandler".

using System.Configuration;
using System.Xml;
//<unknownIcon imageUrl="Icons/unknown.gif" description="Unknown File Type"/>
//<icon imageUrl="Icons/word.gif" description="Word Processing Document">
//    <ext>.DOC</ext>
//    <ext>.RTF</ext>
//    <ext>.TXT</ext>
//</icon>
//<icon imageUrl="Icons/excel.gif" description="Excel Document">
//    <ext>.XLS</ext>
//    <ext>.CSV</ext>
//</icon>
//<icon imageUrl="Icons/image.gif" description="Picture/Image">
//    <ext>.GIF</ext>
//    <ext>.TIFF</ext>
//    <ext>.JPG</ext>
//    <ext>.BMP</ext>
//    <ext>.PNG</ext>
//</icon>
namespace IconConfiguration
{
    class IconConfigurationHandler : IConfigurationSectionHandler
    {
        #region IConfigurationSectionHandler Members

        public object Create(object parent, object configContext, System.Xml.XmlNode section)
        {
            IconConfigurationCollection returnObject = new IconConfigurationCollection();
            IconConfigurationItem configItem;

            XmlNodeList iconNodelist = section.SelectNodes("icon");
            XmlNodeList extensionNodeList;
            //XmlNode iconNode;
            //XmlNode extensionNode;

            foreach(XmlNode iconNode in iconNodelist) 
            {
                extensionNodeList = iconNode.SelectNodes("ext");
                foreach (XmlNode extensionNode in extensionNodeList)
                {
                    configItem = new IconConfigurationItem(
                        iconNode.Attributes.GetNamedItem("imageUrl").Value,
                        iconNode.Attributes.GetNamedItem("description").Value,
                        extensionNode.InnerText);
                    returnObject.Add(configItem);
                }
            }
            //Acquire and Process the Unknown Icon Node
            XmlNode unknownIconNode = section.SelectSingleNode("unknownIcon");
            if (unknownIconNode != null)
            {
                returnObject.unknownIconInfo = new IconConfigurationItem(
                    unknownIconNode.Attributes.GetNamedItem("imageUrl").Value,
                    unknownIconNode.Attributes.GetNamedItem("description").Value,
                  string.Empty);
            }
            return returnObject;
        }

        #endregion
    }
}

All it does is select icon and unknownIcon nodes from the custom section that we will add in the web.config and makes an object of type IconConfigurationItem and adds it to the Collection IconConfugurationCollection and returns the collection object.

5) We are almost done with the Library class, only the last thing which the Config.cs file/class which will read the web.config from the web project. But before writing this part, we can actually add a website to our solution and also add the custom section in the web.config, so we have a better idea of which section we will read before writing the config.cs file.

So I crated a Website and added a Page called "IconConfigHandler.aspx" and in the web.config, I added a section as

<!--Section for Custom Configuration Handler to pick up-->
<section name="IconConfig" type="IconConfiguration.IconConfigurationHandler, IconConfiguration"/> 

Note : The <configSections> element must be the first item defined in the <configuration> section. If you place any other elements in the <configuration> section before the <configSections> element, you will receive a compilation error.

So, This must be under configSections tag and the type is having a syntax like 
type="Namespace.Type, assembly". Now that we have added the new section name so we can added our mapping xml as in point (1) under the new section name i.e. "iconConfig", so it will read like the following

       <IconConfig>
<unknownIcon imageUrl="Icons/unknown.gif" description="Unknown File Type"/>
<icon imageUrl="Icons/word.gif" description="Word Processing Document">
<ext>.DOC</ext>
<ext>.RTF</ext>
<ext>.TXT</ext>
</icon>
<icon imageUrl="Icons/excel.gif" description="Excel Document">
<ext>.XLS</ext>
<ext>.CSV</ext>
</icon>
<icon imageUrl="Icons/image.gif" description="Picture/Image">
<ext>.GIF</ext>
<ext>.TIFF</ext>
<ext>.JPG</ext>
<ext>.BMP</ext>
<ext>.PNG</ext>
</icon>
</IconConfig>

This must be placed outside the <configSections> tag.

6) So we now write the Config class to read the above config

using System;
using System.Web;
using System.Collections.Generic;
using System.Configuration;

namespace IconConfiguration
{
    public static class Config
    {
        private const string sectionName = "IconConfig";
        private const string normalText = "normalRead";
        private static IconConfigurationCollection _iconData;
        public static IconConfigurationCollection IconData 
        { 
            get
            {
                if (_iconData == null)
                {
                    _iconData = (IconConfigurationCollection)ConfigurationManager.GetSection(sectionName);
                }
                return _iconData;
            }
        }
        public static string AppConfigData
        {
            get
            {
                return ConfigurationManager.AppSettings[normalText].ToString();
            }
        }
    }

}

*I have added a property called "AppConfigData" just to make sure that if I want to read without the custom section handler things are ok or not and that we will see shortly.

7) Next in the Webpage of the website I added a Textbox for entering the extension and a button, on click of which it will get the appropriate Image Url and also 2 lables, one to display the fetched url of the extension and the other one to read from App Settings without using the custom section handler. The code behind reads something like this as follows

protected void btnShowIcon_Click(object sender, EventArgs e)
    {
        string extension = txtEntension.Text.Trim();

        lblResult.Text = ((IconConfigurationItem)Config.IconData.getIconInfo(extension)).Image;
        lblRes.Text = Config.AppConfigData;
    }

Oh Boy ! It works just as expected.

Wednesday, November 24, 2010

Remote Desktop Connection as an Admin

You might have come across situations where you need to connect to a remote desktop and some other users are holding sessions and the total number has exceeded and the mstsc command will not allow further sessions to the remote desktop of the server.

One easy way to kick one user out will be to use the following command to login

%SystemRoot%\system32\mstsc.exe /admin

and then type ur username and password to get in.

Friday, November 19, 2010

Try Catch block in a Transaction

We can use TRY catch blocks from SQL Server 2005 onwards and can rollback a transaction if there were any errors. Please see the example below


BEGIN TRY
 SET XACT_ABORT ON -- Will rollback on any errors
 BEGIN TRANSACTION    -- Start the transaction
   DELETE FROM Mytable
WHERE id = 'something'
   -- If we reach here, success!
   COMMIT
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Friday, November 12, 2010

Checking data in database before going for any inserts

We commonly encounter a scenario where we have to check if a data is already there in table before going for any insertions. So here is a quick query for this purpose

please note that i wrote this query keeping Sql Server in mind.

IF NOT EXISTS ( SELECT TOP 1 [NAME] FROM myTable WHERE [Name] = 'check_name' )
BEGIN

INSERT INTO myTable
           ([Name]
           ,[Description]
           )
     VALUES
           ('check_name'
           ,'test'
           )
END