How to Read Excel Cells from SQL Server Integration Services

We can get data in all sorts of formats in the real world. In this new article from me, learn how to use scripting to import data from individual Excel cells.

Read the article at http://www.sqlservercentral.com/links/66237/151372

SQL Server 2008 R2 has RTMed today

You can get more information, resources and download a free trial here: http://www.sqlserverlaunch.com. MSDN and TechNet subscribers can expect to download it soon.
Some more information here: Data Platform Insider blog

How to change server name when replication is enabled

Changing a server name is not difficult but Microsoft does not recommen to change the server name when the replication is configured. These are the steps you need to follow.

http://www.sql-server-performance.com/faq/change_server_name_replication_enabled_p1.aspx

Could not find stored procedure 'dbo.sp_MSins_dboTest'

This is a frequent error you get in transactional replication. See how to resolve this at http://www.sql-server-performance.com/faq/could_not_find_dbssp_msins_dbotest_p1.aspx

Queries which include DMFs return a syntax error

When you execute queries which include DMFs the queries return a syntax error. See the full FAQ at http://www.sql-server-performance.com/faq/dmf_syntax_error_p1.aspx

SET NOCOUNT ON

What is this setting. Read the new blog post at beyond relational

BeyondRelational.Com

I am invited to write blog posts targeting noive DBAs / database developers at www.beyondrelational.com . Since I can’t duplicate those post here I will include the shortcut here to the beyondrelational.com .Those will be tagged as BeyondRelational.com

Microsoft SQL Server 2008: What's New with R2?

Are you ready for Microsoft SQL Server 2008 R2? This white paper highlights some of the major features included in SQL Server 2008 R2 due to be released in the first half of 2010.

Microsoft SQL Server 2008- What's New with R2-

Windows Authentication Users Domain Will not Change when Server Name Changed

in practice, you need to change the server name due to many reasons. There are few challenges you need to face as Database administrator. This is one of them.

After changing the server name, you need to run following stored procedures.

sp_dropserver SERVER2

GO

sp_addserver SERVER3, local

GO

SERVER2 being the old server name and SERVER3 is the new server name.

After this if you can login to the server with SERVER3\Administrator if you have configured SERVER2\Administrator user before. However, if you closely look at there is no user called SERVER3\Administrator instead user is SERVER2\Administrator.

When server name is changed, I believe these windows authentication users also changed. Now you need to run following script to change user names accordingly.

/****** Object: Login [SERVER2\Administrator] Script Date: 04/06/2010 14:51:51 ******/

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SERVER2\Administrator')

DROP LOGIN [SERVER2\Administrator]

GO

/****** Object: Login [SERVER3\Administrator] Script Date: 04/06/2010 14:51:51 ******/

CREATE LOGIN [SERVER3\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

Changing Data Type for Derived Columns?

If you want to add a column to the existing data flow in SSIS, in normal circumstances you are using Derived Column Transformation to accomplish this task.

If you drag and drop the Derived Column Data Flow Transformation and following configuration dialog box will appear.

Unlike in SQL Server 2005, you don’t have an option of selecting the data type you need. It will select default data type for expression you type.

For example, for text data type it will be Unicode string [DT_WSTR], for positive integer’s four-byte signed integer [DT_I4], for float numeric [DT_NUMERIC].

There is a workaround for this. Go to the advanced Editor; navigate to the option shown in following image.

You can select any data type from the available combo box here and if you go back again to the previous configuration, you will see the newly selected data type there as well.

However, I feel this is a bug to be fixed.