Native OLE DB\SQL Server Native Client 10.0 does not list out the available databases

This held our work for few hours. When we tried to make a connection to SQL Server 2008 server with “Native OLE DB\SQL Server Native Client 10.0” provider from a developer’s machine through SSIS, it did not list out available databases, and did not allow to connect to a database even the name of the database is just typed in the “Select or enter a database name” input box. We doubt that the problem was related to some network problem or to some installed components but, since it allowed to connect with “Native OLE DB\Microsoft OLE DB Provider for SQL Server”, thought that it is related to “SQL Server Native Client 10.0”. We spent hours to find the reason but could not find any issue related to SQL Server components. Though the connection can be established with “Microsoft OLE DB Provider for SQL Server”, since it does not allow to run some of the new TSQLs and specially it does not recognize “Date” data type properly, we had to dig deeper…. Finally, my colleague Buddhika found the issue. Windows Firewall Settings was blocking the Native Client 10.0 requests. Once it is turned off, connection could be established. Again, turning off the settings is not a good practice, we tried to find a way to make a connection while Firewall Settings is on, and we found. Simply, the TCP port 1433 added as an exception for the Firewall Settings. It worked. So, in shorter form, if you cannot make a connection to SQL Server 2008 with Native OLE DB\SQL Server Native Client 10.0” provider, make sure that the SQL Server listening port is added as an exception with Windows Firewall Settings. I am sure that we will be getting the same with Analysis Services too, and adding TCP port 2383 as an exception might solve the issue.

Slowly Changing Dimension: Char for Type II Attributes

I used to use Char data type for character data types of variable length columns, if the maximum number of characters that will set for the attribute is less than 10. This is what most DBAs/DBEs do. Not only the maximum length, there are few more considerations for using Char instead of Varchar for character data types of variable length. One would be, when the length of all data values is approximately same. I made a mistake; this cannot be applied in everywhere, especially in Relational DW with Type II attributes. For example, if I have to make a dimension table with Marital Status that contains either Single or Married, I can make the column as either varchar(8) or char(8), so I made it as char(8) because I prefer to make my design according to experts suggestions. Unfortunately this is a Type II attribute which is designed to maintain history, hence when the record contains “Single”, even though the same value comes with a new data set, it considers as a changed (because of the length) and inserts a new record, making the old record as a historical record and it continues with next data set... It unnecessary adds new records to the table making the old ones as historical records. So, I made the all the Type II columns as varchar columns that were set as char to solve the issue, what do you think, any suggestions, any thoughts on this?

Another SQL Server new course.

Last week, two batches, one for 2780B (SQL 2005) and another for 6231A (SQL 2008) were successfully completed and will be starting my next class, most probably within next two weeks, for 2778: Writing Queries with Microsoft SQL Server 2008, at NetAssist. If you interest to join my class, you are welcome. Although I started my conducting classes on.NET, last two years I have been doing classes, workshops only for SQL Server and Business Intelligence. Just thought to focus on two more subjects. Working closely with one of the reputed institutes regarding these two subjects, hope will be able to start them by January, 2010.

USER_NAME(), SUSER_NAME(), ORIGINAL_LOGIN()

Sometimes, we switch the execution context to different account when required. One of requirements when connected in such a manner may be, finding out the original account. This can be retrieved from ORIGINAL_LOGIN function. Not only this, the other functions such as USER_NAME, SUSER_NAME are useful too, if you need to info return from them. Thought to put down a small code, just to show the different between these functions; Login as "sa" and execute... -- Create a login and a user USE master GO CREATE LOGIN TestLogin WITH PASSWORD = '123', CHECK_POLICY = OFF GO USE AdventureWorks GO CREATE USER TestUser FROM LOGIN TestLogin -- Test the functions -- Returns dbo SELECT USER_NAME() -- Returns sa SELECT SUSER_NAME() -- Switch the execution context EXECUTE AS LOGIN = 'TestLogin' -- Returns TestUser SELECT USER_NAME() -- Returns TestLogin SELECT SUSER_NAME() -- Returns sa SELECT ORIGINAL_LOGIN() REVERT; USER_NAME: Returns the current user in the current context. If the user_id is submitted, returns the name of the given id. SUSER_NAME: Returns the current login in the current context. If server_user_id is submitted, returns the name of given id. ORIGINAL_LOGIN: Returns the original login in the session in which there are many implicit or explicit context switches.