"None can stop the rising sun, clouds can hide for a while........" -Ravi

Wednesday, July 29, 2009

Use nvarchar(max) for ntext in MS SQL 2005 and later versions

Error:
The ntext data type cannot be selected as DISTINCT because it is not comparable

Prob: Union fails, If a table contains nText datatype field

Answer: Use nVarChar(MAX)(This works only SQL 2005 and Above versions)

Similarly, use VarChar(MAX) for TEXT, varbinary(max) for image

Tuesday, July 14, 2009

Conenction in tns ORA file for conencting to Oracle data base

For conencting to Oracle database, First you need to create connection string in TNS ORA file.

1) go to C:\ORANT\NETWORK\ADMIN
2) Open tns names.ora file
3) Modify your variable in the following string
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PLSExtProc)
)
)

Connecting to Databases from SSIS COnnection manager

Couple of guys asked me about conencting to ACCESS from SSIS. I thought, It's betetr to put in my blog..

1) Right click in Connection manager window(Which is appear at the bottom of the visual studio)
2) Select New OLEDB Connection
3) You will get "Configure OLEDB Connection Manger:" window. Click on NEW button
4) Select Provider, What ever database you want to connect
Ex: use OLEDB\SQL Native Client for connecting to SQL, Native OLEDB\Microsoft JEt4.0 OLEDB Provider for connecting to ACCESS, Microsoft OLEDB Provider for Oracle for Oracle e.t.c..
5)Select the file if data base is Access, Eneter server name if you want to connect to SQL, For conencting the Oracle data base you need to use Server variable which is used in tns ORA file(For creating connection string in tns ora file Click here).
6) Click on Test Conenction to amke sure whether it's conencted or not.
7) Click Ok