SQL:Finding occurance of chars/words in String

The following is a very simple logic to check the ocuurance of chars/word in a larger string. This thing is particularly important when you want to just check if the string that you are searching for occurs as a sub-string of a value in a larger string which may be a value of a column in a table.

One practicle example would be say for example you want to check if a role is there in a column of user_roles which has a comma separated value of all roles for a particular user, so here you know what you have to look for in a column of all roles.

DECLARE @LongSentence VARCHAR(MAX)

DECLARE @FindSubString VARCHAR(MAX)SET @LongSentence = 'My Super Long string with long words'SET @FindSubString = 'long'SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,(
LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars

In simple terms all it does is

Length of Long Seq (36) minus Length of Long Sentence with all 'long' replaced with blank (24) = 8 so we have 8 ocurrences of the characters

The above divided by the Length of 'long' will give you the occurrences of the word 'long' in the Long Sentence i.e 2

or 8/4 = 2


**I got the above T-SQL from this source http://blog.sqlauthority.com/2010/08/16/sql-server-finding-the-occurrence-of-character-in-string/

Adding System.ServiceModel.Web.dll in v4.0

While creating a WCF service library on Visual Studio 2010, I am sure many of you have faced an issue where you are not able to find the System.ServiceModel.Web.dll.

The reason being that Visual Studio by default targets the 4.0 Client Profile and System.ServiceModel.Web.dll is not present in that list of libraries to be referenced. The work around is to change the target framework to v.4.0 instead of the 4.0 Client profile.

* The same problem will not arise if you have created a WCF Service Application.

WCF Base Address error on Windows Vista

I am sure its not only me but many of you have encountered an unusual error when you first started an instance of a WCF Host application through Visual Studio on Windows Vista, the error will look something like this below

{"HTTP could not register URL http://+:49779/WcfServiceLibraryNorthwind/NorthwindService/. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details)."}

Since vista's security is not exactly like old windows, so the owner of an HTTP namespace (in this case the administrator) will have to delegate that ownership to another user, so the command would be something like this below

"netsh http add urlacl url=http://+:portNumber/ user=MYMACHINE\UserName"

i.e as below in my case
netsh http add urlacl url=http://+: 49779/ user=KAUSHIK-PC\Kaushik

*On success, you will get a message like below

URL reservation successfully added

** please open the command shell "As Administrator", otherwise you will get an error as below 

Url reservation add failed, Error: 5

The requested operation requires elevation.

For further reading "http://blogs.msdn.com/b/paulwh/archive/2007/05/04/addressaccessdeniedexception-http-could-not-register-url-http-8080.aspx"

WebGrid Control Issues

If you are using web grid third party control in any of your applications please make sure to use it for the exact version of ASP.NET for which it was built for. Suppose if say you have a Webgrid to be used for an ASP.NET 1.1 application and if my mistake your default Website or your virtual directory has a version of ASP.NET as 2.0, then the webgrid will throw error like the datasource or memory used to access data from grid is corrupt and that is basically because the other version wp might not have access to the webgrid datasource.

Android app on windows using Eclipse

First all please go through this blog which guides developers like me who are basically .NET developers but having an interest on building Android application using windows platform.


http://www.caffeinedi.com/2010/07/03/beginning-google-android-development-for-net-developers-part-1/

The blog post will guide anyone on how to setup the environment and run your first app, but I want to highlight some of the issues I faced and which are quiet common and resolutions/work around those issues. First of all the 4 things that needs to be setup are


1. Java Development Kit (JDK) 6 – self-explanatory; the Java runtime and development libraries.


2. Eclipse – an IDE (integrated development environment) for Java and other languages.   If you don’t have it installed, grab the Eclipse IDE for Java Developers version, which will suit the needs of this tutorial.  The Windows download doesn’t contain an installer, so extract the ZIP in an accessible location, such as D:\eclipse.


3.Android Software Development Kit (SDK) – The heart of Android development.  This provides all of the necessary tools and libraries required to build applications for the Android platform.  Extract it to an accessible location on your hard drive and run the setup executable, which will download and install a set of components.


4.Android Development Tools (ADT) Eclipse Plugin – This plugin adds Android-specific development tools and project templates to Eclipse.  Instead of a separate download, it installs within Eclipse – you can follow the instructions here to get it installed.


ISSUE 1 At step 3, when you try to run the executable to install Android SDK, then that might give errors because it might not be able to detect the JDK. Instead of cancelling the installation, just click "Previous" button and go the previous screen and then "Next" button ,it will detect it this time for sure.


ISSUE 2 To install the ADT as in Eclipse plugin, follow the steps below to first download it as a plugin
1. Start Eclipse, then select Help > Install New Software....
2. Click Add, in the top-right corner.
3.  In the Add Repository dialog that appears, enter "ADT Plugin" for the Name and the following   4. URL for the Location:

    https://dl-ssl.google.com/android/eclipse/

    Click OK

    Note: If you have trouble acquiring the plugin, try using "http" in the Location URL, instead of    "https" (https is preferred for security reasons).
    In the Available Software dialog, select the checkbox next to Developer Tools and click Next.
    In the next window, you'll see a list of the tools to be downloaded. Click Next.
    Read and accept the license agreements, then click Finish.

    Note: If you get a security warning saying that the authenticity or validity of the software can't be established, click OK.
    When the installation completes, restart Eclipse.


ISSUE 3 After creating your first Android virtual device, when you try to run the application, you can get an error like 
Emulator] PANIC: Could not open: C:\Documents and Settings\UserNAme\.android/avd/Android2.2.ini
This happens because if your Android SDK location is in any other location than C drive, the eclipse tool will not be able to locate that folder .android, so the solution would to look for the folder *.android and copy it over to the location sorted by the eclipse tool. In my case, I copied the folder .android from a location in my D: drive to C:\Documents and Settings\UserNAme\.android.
ISSUE4 While launching the new emulator, you might again get an error like
 Emulator] invalid command-line parameter: Files\Android\android-sdk\tools/emulator-arm.exe.
 Emulator] Hint: use '@foo' to launch a virtual device named 'foo'.
 Emulator] please use -help for more information


To get around this issue change the SDK location path in eclipse to D:\PROGRA~1\Android\android-sdk for a 32 bit machine or change to D:\PROGRA~2\Android\android-sdkfor a 64 bit machine if your SDK installation is on D: drive or else the drive accordingly. 


This issue is because of the fact that eclipse will not be able to SDK location if SDK location contains any spaces.

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

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>'

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

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 

http://cisnet.baruch.cuny.edu/holowczak/oracle/dev2k/9ids/#introduction

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