Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

March 2008 - Posts - SQL Server Knowledge Sharing Network (@sqlmaster)

March 2008 - Posts

MVP Global Summit - where all the attendees are coming from, view map geographically?

MVP Global Summit, what it meant for MVPs?

At one place, global connections with peers with a shared insight with a flavour of engagement with relevant product manager, sounds terrific isn't it!

Who can attend - as per the previous post on MVP Global Summit 2008 here, if you are a Most Valuable Professional or Regional Director then you are invited to attend this exclusive summit between April 14 - 17 2008. The venue is hosted hosted at the Washington Trade and Convention Center in Seattle and at Microsoft headquarters in Redmond, Washington, with an exclusive access to more than 400 sessions and panel discussions and a range of networking opportunities, as well as Microsoft top executives such as Steve Ballmer, chief executive officer, and Ray Ozzie, chief software architect.

So what's new with this post, fellow MVP John OBrien (Live Development) has done an excellent & another valueable job by teaming up with Summit organisers to produce a 'small world' Virtul Earth visualisation of where all the attendees of the summit are coming from. As you can see the data is a general area (city basis) to zoom and if you see larger dots that represents more MVPs on that area, pretty cool!.

This summit may be first one for the new MVPs who are awarded since last July 2007 cycle and for me it is 2nd one, its worth a trip to seattle with a deep dive to immerse into technology.



Webcast 31st March 2008: 24 Hours of SQL Server 2008: Ensuring Your Data Is Secure with a Trusted Platform

Another part of Technet webcast series on SQL Server 2008. 

In this part a focus on IT professionals and the SQL Server 2008 the Trusted Platform. In the first part of the series we spent time looking at how Contoso was using SQL Server 2008 as a productivity platform. Now we change direction from developers and look at how Contoso's IT operations embraced the new and enhanced features to make sure that SQL Server 2008 was a trusted platform. The first two parts address security, which is a main theme for Contoso as they have large amounts of critical data that needs to be protected against both internal and external threats. Either data loss or compromise would be a nightmare scenario. In this session, we look at features such as surface area configuration policies, external key management, and Network Access Protection (NAP), and Windows BitLocker Drive Encryption, which Contoso could use to protect its data.

When: Monday, March 31, 2008

Time: 11:30 A.M.–1:00 P.M. Pacific Time

Event ID    

SQL Server 2008 Express features and variation from previous version

Flood of questions fromthe users during a recent usergroup meeting.

    • What will the specification for the SQL Server 2008 Express version be? 
    • What will be the differences between it and the 2005 version, not with MSDE? 
    • How about SQL Agent attachment to the SQL Express engine? 
    • Any memory limitations and CPU limit?
I can say there isn't much difference betwen SQL Server Express edition 2005 and 2008, as usual the physical limitations for SQL Express 2008 are similar, such as:
  • 1 CPU socket
  • 1 GB memory
  • 4 GB of user data per database
  • Within Advanced Services - sqlcmd and osql utilities
  • Integration with Visual Studio
  • Replication (as a subscriber only)
  • Subset features for Reporting Services

As it became in SQL Server 2005, the FullText search is a part of SQL Server engine and similarly the SQL Server Management Studio Express, a graphical management tool that is based on SQL Server Management Studio that makes it easy to manage and administer SQL Server Express databases. On top of the Reporting Services as an integrated report creation and design environment to create reports with Full-text Search, a powerful search engine for searching text-intensive data.

Not only this now the SQL Server Express supports Service Broker, but direct communication between two SQL Server Express servers is not supported. Finally to enlighten you can now use the Dedicated Administrator Connection feature for SQL Server Express is supported with the trace flag 7806.

A list of features that are not supported (source 2008 BOL):

SQL Server features not supported in SQL Server Express SQL Server features from previous versions not supported in SQL Server Express

Database mirroring

SQL Mail

Online restore

Fail-over clustering

Database snapshot

Distributed partitioned views

Parallel index operations

VIA protocol support

Mirrored media sets

Log shipping


Parallel DBCC

Address Windowing Extensions (AWE)

Parallel Create Index

Hot-add memory

Enhanced Read Ahead and Scan

Native HTTP SOAP access

Indexed views (materialized views)

SQL Mail and Database Mail

Partitioned views

Online Index Operations


SQL Server Agent and SQL Server Agent Service

How to get rowcount efficiently within a SSIS package?

I would like to call this as how efficiently you can obtain counting rows in SSIS package. Usual thought would come is @@rowcount or  COUNT_BIG functions, but this is not that efficient way to obtain the results.

SSIS provides the snippet within its components such as using Row Count component, for this you have to create a variable of integer typeat a scope where you can see it from your Data Flow task. Then add this RowCount component to the Data Flow task place where you want to get results. Not yet finished once it is specified in that task you have to edit the Row Count component and set its VariableName property to the name of the variable you created.

The downside of this method is this component is not updated quickly unless the complete DataFlow task is completed, may be we can use VBScript component to get upto date count values and again this is not possible always to embed within the package.

So going deep into the requirement such getting rowcount values on all sorts of level such as, how many rows were inserted to a destination? As usual you can achieve this by using RowCount component just before the destination component. A little glitch here is missing of failed rows during that insert operation, because it would not take into account rows which failed to be inserted. So there are two patterns you could use for OLEDB destinations:

  • Use an ExecuteSQL task to count rows before and after the Data Flow has executed, and compare;
  • Add a RowCount component before the destination, and one on the error output of the destination, and compare the values of the two variables after the Data Flow has completed.


SQL Server 2008 Spatial data - geography & geometry, all in one

Geography & Geometry has much significance in day to day life, within that as a data now you can manage within your Database using SQL Server 2008 new data type - spatial, this  represents information about the physical location and shape of geometric objects.

More from here - SQLServer-spatial-demos

Posted by ssqa.net | with no comments

SQL Server Integration Services and Clustering - confguration gotcha to ensure SSIS works with failover of cluster!

A newbie or FAQ within Forums and Newsgroups that having multiple instances and how best to set the resources on the server such as memory, CPU etc.

Read more from here.

Pivot table connectivity to SQL Server Analysis Services - security gotchas

Security permissions in OLAP is similar to normal OLTP based environment. Say if you are using Pivot Table object in MS Office Excel to display the reports using OLAP objects like Cubes. To achieve this it should connect to an OLAP Server and database to fetch the data. As per the configuration when it tries to refresh data in the pivot table, it internally checks whether the windows user from the current system has required access rights.

If the login that used in Excel do not have proper permission on the Cube then it gives error "Either the user, Domain\User, does not have access to the OLAP Database, or the database does not exist".

In case of having different SQL Server groups you should assign proper privileges to the user and put them in appropriate user group. Security credentials are essential once your OLAP data source becomes accessible over HTTPS because your data source is now potentially available to anyone who can browse your Web site. For example, a remote user can open Excel and connect a PivotTable to your OLAP data source simply by providing the URL for the Analysis Server and the name of the Initial Catalog.

Analysis Services implements cube security roles based on Windows user accounts. Basically, you create one or more local Windows NT user accounts on the server and assign them to a SQL Server database role. You then assign the database role to a cube role and assign it to the specific cube. The cube role essentially inherits the Windows NT user ID and password credentials. You can then add the user ID and password credentials to the OLAP data source connection string.

Also one OLAP data source may contain multiple cubes. You can connect to a specific cube by setting the OWC PivotTable control's <DataMember> XML tag value to a valid cube name (explained in the next section). This cube must be assigned to a role that corresponds to the credentials in the OLAP data source connection string.

Cluster resource 'SQL Server (<SQL Server instance name>)' in Resource Group '<Cluster group name>' failed.

A common mistake by DBAs for missing to check whether all the functionality of tools are working when a SQL Server Cluster environment is setup, though you may not catch hold of few issues with basic failover/failback testing initially but when the application is initiated and data is stored with relevant scheduled jobs and processes the nodes funcationality may be different to each other. So in this case it is a best practice to document and perform a failover once the application usage is in peak within the DBMS environment.

Recently we have had such problems where we missed 2 cycles of failover tests between the nodes, so during this time when we had issue with active node the SQL has been failed over to other node and thats where when tried to start SQL Server agent it hangs, and goes back to 'Failed' status. As usual I was going through the SQLAgent logs and Event Viewer logs for any information on SQL Server services account privileges, nothing was wrong or no such warnings or error messages on the log.

After a strolling through the MSDN and KBA (Knowledge Base Articles on Cluster) I was able to pick the root cause of this error, rather its a bug fix: The resource-specific registry keys that correspond to the SQL Server cluster resource that you are trying to bring online are missing. Not only this it will occur if the correspdoing value of Virtual server name is missing/not correct on resource-specific registry keys.

KBA 883732 talks the resolution as follows:

To resolve this problem, you must manually re-create the resource-specific registry keys that correspond to the SQL Server cluster resource. To do this, follow these steps:

1. Click Start, click Run, type Regedit, and then click OK.
2. In Registry Editor, locate and select the following registry key:

3. Create the following registry values in the Parameters registry key:

For a default instance of SQL Server:

Value Name: InstanceName
Value Type: REG_SZ

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data: <Name of the virtual SQL server>
For a named instance of SQL Server:

Value Name: InstanceName
Value Type: REG_SZ
Value Data: <SQL Server instance name corresponding to the virtual server>

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data: <Name of the virtual SQL server>
4. Quit Registry Editor.

After you create the resource-specific registry keys, you can bring the SQL Server cluster resource online successfully.

If you notice that a SQL Server Agent cluster resource cannot be brought online, you must create the same set of resource-specific keys that correspond to the SQL Server Agent cluster resource.

This is where I have got root cause for above error and for more  information review 810056 - You must use resouce-specific registry keys for SQL Server cluster resouces

TSQL to change collation of database, whats new in SQL 2008 then?

How can you change collation of a database?

How do you check the existing collation setup on a database & tables?

By using the below TSQL:

--For database 

select databasepropertyex(db_name(),'collation') as collation_name


select column_name, collation_name from INFORMATION_SCHEMA.COLUMNS
where table_name = 'TableName'

The generic answer you get is use ALTER DATABASE <DBName> COLLATE CollationName and so on. TO this affect only the inserted data after this statement execution will affect the data and not with existing data, in order to achieve that is to generate the database creation script using ENterprise Manager or Management Studio by replacing the existing collation  with the new name. Then export the tables database using BCP or DTS then drop the database (don't forget to take full database backup before), use the changed script to create the database and then use BCP or BULK INSERT to import the data. ALso this KBA325335 talks much about details into it.

Also you may be aware that you can have multiple collation set of columns within a table and I have referred the following TSQL script to change the COLLATE that is different to existing collation, the script it as follows:

declare  @toCollation sysname
SET    @toCollation = 'SQL_Latin1_General_CP1_CI_AS' --  Database default collate

       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
            WHEN DATA_TYPE in ('text','ntext') then ''
       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
                                           WHEN 'YES' THEN 'NULL'
                                           WHEN 'No' THEN 'NOT NULL'

WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
 and COLLATION_NAME <> @toCollation

Also be aware that it will work on default data types and not on user-defined database, in this case you have to drop and re-define them again, also ensure to drop the associated indexes & constraints that are stick to installation default such as case-sensitive.

So what's new within SQL Server 2008 version then, usual support for new collations that are associated with Windows 2008 and Windows VIsta. There are 84 new collations within the existing Windows collations within SQL Server 2005, with this they have added East Asian collations to support supplementary characters.

Also BOL defines that support for supplementary character string comparison based on linguistic sorting with new collations in SQL Server. The deprecated collations are Korean, Hindi, Macedonian, and Lithuanian_Classic Windows collations and deprecation of the SQL_ALTDiction_CP1253_CS_AS SQL collation. These collations were supported SQL Server 2005, and are supported in SQL Server 2008, but will not be displayed in the Setup collations list, and will not appear when the system function ::fn_helpcollations() is used to query the list of supported collations in SQL Server 2008.


Get to know about SQL Server 2008 February CTP by participating in webchat

Join the Microsoft SQL Server development team to learn more and ask technical questions about CTP release of SQL Server 2008.

Add-to-Calendar and when it is:

March 13, 2008 11:00 A.M. Pacific Time and refer here for Additional Time Zones

Posted by ssqa.net | with no comments