MS SQL Server Model not showing any objects in ODI

we had a requirement to connect to the MS SQL server 2008 as our source system and once issue we noticed that we were succesfull in connecting to the database while in the model view we were not able to see any objects.

The procedure to connect to the database is standard but there is one trick which we should understand while connecting to the SQL server database.

Please find below the screen shot when u click for new data server Go to Topology - > Physical Architecture - > Technology -> MS Sql Server ->  New Data Server

As below choose the mentioned JDBC drivers for SQL Server and give the IP and port. Plase make sure that remote access is anabled and your user has access on the required database.

Next step is to create a physical schema from this data server here you must keep in mind that in SQL Server database structure is different e.g In our case the database name is "dqaa" and we wish to extract some tables from it. The owner of schema is "DBO" and hence we need to set the setting as below because the property "Local Object Mask" will use that information to extract tables /views/objects from SQL server metadata . This was the main reason why we were not able to see any objects in the Model view after reverse engineering.

Hope that helps.

Update using Join and where clause

Its more often required to update a table which value comes from another table and you want to update using joins and specifying the where clause instead of using sub queries.

So here the systax for doing it in SQL server 2008.

update a
set a.[ColName] = b.[ColName]
from [FirstTableName] a inner join [SecondTableName] b
on b.[colName] = a.[ColName]
where a.[colName] > 287


I suppose that a.[colName] is any integer column.

So thats the way update works using joins.

SQL server Connection Issue

We have our server and our server was running two instances of the SQL Server.I did'nt notice a minor thing that was causing me the following error


"Named Pipes Provider, error: 40 - Could not open a connection to SQL Server".

so in my case the solution was to give full name in the connection string.

Like before i was trying giving 10.x.x.98

But later i tried using 10.x.x.98\instancename and then it worked.

Bcoz what was happening SQL was SQL server was redirecting me to the default instance and that why i was having this problem.

SQL server Prevent save changes Problem

Save not permitted in SQL Server 2008 - Management Studio

For those who tested the new SQL Server 2008, you probably got this message, when trying to change the columns in some tables, add columns or change nullity conditions. "Save is not permitted"... and something like tables have to be dropped and re-created. The only choice you have is to click cancel, or to choose to save the message to a text file, which is, i think, not very useful :). The solution i found today is in SQL Books Online, and it is pretty easy:

Tools -> Options -> Designers, and uncheck the option "Prevent saving changes that require table re-creation".