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