[Informatica][Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket

Problem : Install informatica 8.1 on SQL Server 2000
Cause : SQL Server 2000 need service pack 4 and firewall need to switch off

SQL Server JDBC Connection Errors: Error establishing socket, connection refused...

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
       at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
       at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
       at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
       at com.microsoft.jdbc.sqlserver.tds.TDSConnection.(Unknown Source)
       at com.microsoft.jdbc.sqlserver.SQLServerImplConnection.open(Unknown Source)
       at com.microsoft.jdbc.base.BaseConnection.getNewImplConnection(Unknown Source)
       at com.microsoft.jdbc.base.BaseConnection.open(Unknown Source)
       at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)


SQL server 2005 JDBC Driver:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed.
                                    java.net.ConnectionException: Connection refused: connect
       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerException.connect(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

For this kind of database connection problem, there are always two distinct issues:

1. The SQL server itself is not running or TCP/IP is disabled. That can be confirmed by run

   netstat

   and see it is listed there. And then run

   telnet localhost 1433

   and see it can connect (or whatever port number).

2. Even if the server is running, a client program such like jdbc may still fail to connect to it because of a

   firewall

   For testing, you can turn off the XP firewall. And if you have other firewall running, check the firewall log and see any activities that might be related.

You can also verify whether or not your server is running from the

SQL Server Network Utility

on the server, or from another client utility such as

SQL Query Analyzer

or a command line utility such as

osql

To check the enabled protocols for a server, follow these steps:

1. In SQL Server 2000, start the SQL Server Network Utility (svrnetcn.exe).
2. On the General tab, select the instance of Microsoft SQL Server on which to load an installed server network library.
3. Make sure that TCP/IP appears in the Enabled Protocols list.
4. To find the port number, in the Enabled Protocols list, click TCP/IP, and then click Properties. The Properties dialog box displays the port number.

There is an known bug: SQL Server May Not Listen on TCP/IP Sockets When TCP/IP is the Only Protocol

In a SQL Server 2000 custom installation, if TCP/IP is the only selected protocol and all other protocols are disabled, SQL Server may not initialize and listen on TCP/IP sockets. The Server Network Utility shows that it is listening only on TCP/IP port 1433 even though it is not. To identify if SQL Server is not listening on TCP/IP, check to see if the value for TcpPort in the following registry key is incorrectly set to blank or empty:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ [Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\REG_SZ TcpPort=

To work around this problem, follow these steps:

1. Start Registry Editor (Regedt32.exe).
2. Locate the TcpPort value in the following key in the registry:

   Named instance:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[InstanceName]\MSSQLServer\SuperSocketNetLib\Tcp\TcpPort

   Default instance:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
   \MSSQLServer\SuperSocketNetLib\TCP\TcpPort

3. . On the Edit menu, click String. Enter either of the following values:
   a. Type the port number you want. -or-
   b. Enter a value of 0 to have SQL Server dynamically detect and assign a port the next time SQL Server starts.

4. Click OK.
5. Quit Registry Editor.

SQL Server 2000 Service Pack 4 (SP4):

You are running a version of Microsoft SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine (also called MSDE) that has known security vulnerabilities when used in conjunction with the Microsoft Windows Server 2003 family. To reduce your computer's vulnerability to certain virus attacks, the TCP/IP and UDP network ports of Microsoft SQL Server 2000, MSDE, or both have been disabled. To enable these ports, you must install SQL Server 2000 Service Pack 3a, or the most recent service pack for Microsoft SQL Server 2000 or MSDE from http://www.microsoft.com/sql/downloads/2000/sp4.mspx.

Oracle : Truncate Table in Procedure

DDL and commands like TRUNCATE are not allowed in Oracle PL/SQL blocks. However, there is a workaround. Native dynamic SQL with the EXECUTE IMMEDIATE command will do the trick. Here is an example:

create procedure MyProc
as
begin
 execute immediate 'truncate table MyTable';
end;

The SQL is executed as if from a host application. Pretty much anything you can run from a SQL prompt can be run from inside a PL/SQL block this way. Because running dynamic native SQL executes a string containing SQL, PL/SQL suddenly becomes very flexible. Dynamic native SQL in Oracle allows you to combine the benefits of a host language with the benefits of stored procedures. Here's an example of the previous procedure changed to include a parameter for the name of the table name to be truncated:

create procedure MyProc (pTable in varchar2)
as
begin
 execute immediate 'truncate table ' || pTable;
end;

Connected v/s Unconnected Transformation

Tips :

want to know the use of unconnected transformation.

Why shd we use unconnected transformation, if we can achieve same with connected one?

Which gives better performance, connected or unconnected and why?

Answers 1 :
Depending upon the scenario we use the conn and unconn tr..

for e.g
In u r mapping u require same lkp multiple times,creating lkp multiple times put it in flow is not a good idea..which occupies more cache ...
Instead of that create one unconnectes lkp use multiple times where ever u want ..which very fast..

Answers 2:
u can use conn lookup but if u need 1 row so go for unconn lookup coz u can
use it multiple time in same mapping from multiple expression trn.
i hope it will help to u

Benefit :
The benefit of an unconnected lookup is that you can do conditional lookups (using IIF,DECODE, etc.). In other words, you can control when the lookup is used with in unconnected lookup.

unconnected lookup gives better performance because it's not connectd to mapping pipeline.
so when data transform is very fast compare to connected transformation.

unconnected look up can be used when you need only one port to be
returned from the look up table or if the same is to be used
many times in the mapping. and moreover unconnected lookup gives
better performance as it just acts as a function call. but if
more ports has to be returned from look up table we must use a
connected look up

Distinct & duplicate records to 2 different targets

Problem :
source is flat file which contains duplicates,we have 2 targets in the mapping,we need to pass unique records to one target & records which is repeating(duplicates) to another target.
can anyone please tell how we can achieve this.

Solutions 1:
Sort the data. Create a field in an Expression that counts the duplicate rows by comparing it to the previous row. Use a Router with two groups: where count=1 and where count >1.

Solution 2 :
put an aggregator transformation and do group by all ports and add a count(*) function in another output port and based on count(*)>1 or =1 you can use router to divert the distinct and duplicate records.

Solutions 3:
u can also pass all duplicate records.
by following these steps:

1. take sql qualifier with sorted input checked on.
2.pass all rows to aggregator , n do group by all
ports, and take on port (count(*))
3. take another sql qualifier ,
4. use joiner with full outer join n join 2 & 3. with
condition (port matching)
5. take 2 filters to separate duplicate row & distinct
rows.. based on condn: count(*)>1 n count(*)=1 and
pass it to respective targets.

Data Mart Migration from sql server to oracle Db

Tips

If you have foreign key constraints on your fact table pointing to your dimension tables, you'll need to load the dimesnion tables first, then load the facts.

bind: (WSAEADDRINUSE)Address already in use

bind: (WSAEADDRINUSE)Address already in use

Problem :
when i initialize the debugger for a session the debugger log is giving the following message :
"bind: (WSAEADDRINUSE)Address already in use"

Solve : Change the PORT No
click--->Tools-->ooptions-->click on Debug tab and below TCP/IP check pick a port number between Min and Max automatically
Min: 5001 Max:32000

Are y using any kinds of chatting services like Yahoo messenger. Close them.
This means that ur port number is already in use by
another application.
try using ps -ef in the server and kill any application that is using that port.
Then try running the application. If it still does not run
then just log off and log in the repository again. It
should work.