Connect to SSIS Service on machine failed | Error loading type library/DLL

Posted on September 1, 2010 by PradeepAdiga

Today one of the members of the Project DBA Team installed SQL Server Integration Services (SSIS) 2005 on one of the production servers. The installation was successful and the first thing anyone would do to confirm is connect to SSIS through SSMS. Surprise!

The error message reads “Connect to SSIS Service on machine “ServerName” failed:  Error loading type library/DLL.” and there is no mention of the name of the DLL which is having issues.

The next option is to check if we are able to connect to the SSIS from a remote server. Yes, we were able to connect to this instance of SSIS from some other server. Now it was confirmed that the problem did not exist with the SSIS installation as such. It had to do something with the DLLs located on the same box.

Since SSIS is having issues, the first DLL that would come to mind is the DTS.dll. This file is usually located in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn folder. While I was navigating to this folder, I observed that a folder named 100 was also residing in the same location as that of 90 folder. This hinted that someone had tried some of the components of SQL Server 2008 and it could have possibly messed up some of the files related to SQL Server 2005.

Hence I planned to register the DTS.dll again. Just opened the DTS.dll using C:\WINDOWS\SYSTEM32\regsvr32.exe in Windows Explorer and it said “Registered successfully”. This can also be done using the command line regsvr32.exe.

Again opened up SSMS and as predicted we were able to connect to the Integration Services.

Posted in SQLServerPedia Syndication, Troubleshoot | Tagged , , | 1 Comment

Change ‘Edit Top 200 rows’ setting in SSMS 2008

Posted on August 31, 2010 by PradeepAdiga

SQL Server Management Studio (SSMS) is a good feature rich tool. While many choose to do data manipulation using T-SQL queries, there are a lot of users who make use of SSMS for manipulating data.

Until SQL Server 2008, right clicking on the table would provide options to SELECT or EDIT “all” rows in the table.

But in SQL Server 2008, SSMS allows only Top 1000 rows to be selected and Top 200 rows to be edited.

This restriction may not be useful especially when one wants to see/edit more number of rows in SSMS. Is there a way to change this behavior? Yes, SSMS has that option under Tools –> Options.

The other feature is that if a value of 0 is entered in any of the above fields, SSMS will list all the rows from the table (Not a good idea to do a SELECT * on a big table)

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment