Appendix: Table and Code Images

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view table as text

Click here to view table as text

Click here to view table as text

Click here to view table as text

Click here to view table as text

Click here to view table as text

Click here to view table as text

Click here to view table as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view table as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view table as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view table as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view table as text

Click here to view table as text

Click here to view code as text

Click here to view code as text

Click here to view table as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Click here to view table as text

Click here to view code as text

Click here to view code as text

Click here to view code as text

Why did a T-SQL script fail to run a job when it could successfully run in SQL Server Management Studio ?

Answer

This issue is usually caused by the fact that the execution security context of the job is different from the security context when you manually run the script in SSMS. The commands in SQL Server Management Studio are executed as the current login. However by default every job step runs in the security context of SQL Server Agent Service account. If the SQL Server Agent Service account is different from the login account and if it does not have enough permission to execute the query, the job will fail.

Though you can simply give the SQL Server Agent Service account permissions to execute the query, this is not the best way from a security perspective. For SQL Server Agent Service account, it is recommended to give it the least amount of privileges. You cannot create a SQL Server Agent proxy to execute the job because for T-SQL job step it will not use any SQL Server Agent proxy.

For a Transact-SQL job step, by default it runs under the security context of the job owner and if the owner of the job is a member of the sysadmin fixed server role, it runs under the SQL Server Agent service account.

You can use one of the following three methods to resolve this issue:

1. The first method to resolve this issue is to change the job owner to a login who has enough permission to execute the T-SQL script but the job owner should not be a sysadmin.

2. The second method is specifying the "Run as User" option for the Transact-SQL job step in case you have several job steps and you do not want to change the job owner so as not to impact other job steps execution. You can go to the Advanced options of the job step, and input an user to the "Run as User" field. Ensure you are a sysadmin to edit this option.

3. The third method is that you can add an EXECUTE AS statement ahead of your T-SQL script.

For example:

EXECUTE AS USER='user_test';
--The statement will be executed under the user_test security context.
SELECT * FROM MyTable;
--After execute the REVERT statement, the security context will be
reset to the default security context.
REVERT;

Click here to view code as image

You can run SQL Server Profiler to monitor which security context is being used by your SQL job.

References

How to: Define Transact-SQL Job Step Options (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms190223.aspx

SQL Agent Subsystems

http://msdn.microsoft.com/en-us/library/ms187100.aspx

EXECUTE AS (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms181362.aspx

How can I degrade a SQL Server database from a higher version to a lower one?

Answer

Yes, degrading a SQL Server database is not supported, but you can use a flexible workaround to resolve this issue. You can use SQL Server Management Studio to first generate scripts for a lower version and then use Data Import/Export Wizard to transfer data. For example, if you want to degrade a SQL Server 2008 database to SQL Server 2005, you can follow the steps below:

1. Open the SQL Server 2008 database in SQL Server Management Studio 2008.

2. Right click the database name in the Object Explorer and select "Tasks Generate Scripts…"

3. In the Script Wizard dialog box, select the database name and check "Script all objects in the select database". Then click "Next".

4. Set the "Script for server version" option as SQL Server 2005.

5. Run the script in SQL Server 2005.

6. Import data from SQL Server 2008 to SQL Server 2005 by using the SQL Server Import and Export Wizard.

Similarly, you can do this in SQL Server 2005 Management Studio if you want to degrade a SQL Server 2005 database to a SQL Server 2000.

How can I create a linked server to the Access or Excel data source from the 64-bit version of SQL Server 2005/2008?

Answer

Before Microsoft releases the 64-bit ACE 12.0, you can only use the 32-bit Microsoft.Jet.OLEDB.4.0 to access Microsoft Access and Excel files. The method requires that you first install a 32-bit SQL Server instance on your 64-bit environment as a medium, then create a linked server with Microsoft.Jet.OLEDB.4.0 provider to your Excel/Access file on your 32-bit SQL Server instance, and then create a linked server to your SQL Server Express instance on your 64-bit SQL Server instance.

Now you can use the 64-bit ACE driver for the data access. Here are the detailed steps:

1. Download and install the 64-bit ACE driver.

2. Open SQL Server Management Studio (SSMS), connect to your 64-bit SQL Server instance and create a linked server to your Access database with the following T-SQL statements:

EXEC sp_addlinkedserver
@server=N'MYACCESS',
@srvproduct=N'Access',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'D:\AccessData\test.mdb'
go
EXEC sp_addlinkedsrvlogin
@rmtsrvname=N'MYACCESS',
@useself=N'True'
GO

Click here to view code as image

If you are accessing an Excel file, you need to additionally specify the @provstr parameter:

EXEC sp_addlinkedserver
@server=N'MYEXCEL',
@srvproduct=N'EXCEL',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'D:\AccessData\test.xlsx',
@provstr=N'Excel 12.0'
go
EXEC sp_addlinkedsrvlogin
@rmtsrvname=N'MYEXCEL',
@useself=N'True'
GO

Click here to view code as image

3. Then you can query with the linked server.

--For query to Access
SELECT * FROM MYACCESS...Tasks
--For query to Excel
SELECT * FROM MYEXCEL...[Books$]

Click here to view code as image

References

The Microsoft OLE DB Provider for Jet and the Microsoft Access ODBC driver are available in 32-bit versions only

http://support.microsoft.com/default.aspx/kb/957570

sp_addlinkedserver (Transact-SQL)

http://technet.microsoft.com/en-us/library/ms190479.aspx

Microsoft Access Database Engine 2010 Redistributable

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en

Why is all of the memory allocated for SQL Server not shown in Windows Task Manager?

Answer

In Windows Server 2000/2003 Task Manager, the" Mem Usage" column shows the amount of memory for the working se t. The working set of a process is the set of pages in the virtual address space of the process that are currently resident in physical memory. Memory allocations that are nonpageable such as Address Windowing Extensions (AWE) or large page allocations are not included in the working set. Therefore, if the AWE mechanism is enabled, the Task Manager will show the memory usage incorrectly. To find out how much memory SQL Server has allocated through the AWE mechanism, you can use the sys.dm_os_memory_clerks DMV as follows:

SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM
sys.dm_os_memory_clerks

Click here to view code as image

For Windows Server 2008, this column is called "Memory (Private Working Set)" in Task Manager. The private working set is a subset of the working set that specifically indicates the total amount of memory allocated by a process, not including memory shard with other processes. You can roughly estimate the amount of memory that is paged out per process by subtracting the value of Process: Working Set from the Process: Private Bytes counters by using the System Monitor.

1. To monitor overall memory usage for SQL Server, you can run the DBCC MEMORYSTATUS command. The DBCC MEMORYSTATUS command provides a snapshot of the current memory status of Microsoft SQL Server. In the result of the DBCC MEMORYSTATUS command, the Memory Manager section shows overall memory consumption by SQL Server. The elements in this section are the following:

a) VM Reserved: This value shows the overall amount of virtual address space (VAS) that SQL Server has reserved.

b) VM Committed: This value shows the overall amount of VAS that SQL Server has committed. VAS that is committed has been associated with physical memory.

c) AWE Allocated: This value shows the overall amount of memory that is allocated through the AWE mechanism on the 32-bit version of SQL Server. Or, this value shows the overall amount of memory that locked pages consume on the 64-bit version of the product.

d) Reserved Memory: This value shows the memory that is reserved for the dedicated administrator connection (DAC).

e) Reserved Memory In Use: This value shows the reserved memory that is being used.

The following example is the result of the Memory Manager section:

Memory Manager KB
------------------------------ --------------------
VM Reserved 1761400
VM Committed 1663556
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

Click here to view code as image

2. In SQL Server 2008, you can obtain this information simply by querying the sys.dm_os_process_memory DMV. The physical_memory_in_use column indicates total memory usage of the server including allocation through large page and AWE APIs. The large_page_allocations_kb and locked_pages_allocations_kb columns show the amount of memory allocated using large page and AWE APIs, respectively.

References

How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

http://support.microsoft.com/kb/907877

sys.dm_os_process_memory (Transact-SQL)

http://technet.microsoft.com/en-us/library/bb510747.aspx

Working Set

http://msdn.microsoft.com/en-us/library/cc441804(VS.85).aspx

Monitoring Memory Usage

http://technet.microsoft.com/en-us/library/ms176018.aspx

How do I configure SQL Server to use larger memory space over 2GB on a 32-bit server?

Answer

There are many ways to have your SQL Server occupy more than 2GB memory on your server.

/3GB

A 32-bit machine can directly address only 4 GB of memory, and by default, Windows itself reserves the top 2 GB of address space for its own use, which leaves only 2 GB as the maximum size of the VAS (virtual address space) for any application, such as SQL Server. You can increase this by enabling a /3GB flag in the system's Boot.ini file, which allows applications to have a VAS of up to 3 GB. Note that VAS is just the set of all possible addresses rather than the physical memory.

PAE (Physical Address Extension)

PAE is the added ability of the IA32 processor to address more than 4GB of physical memory. To enable PAE, use the /PAE switch in your system's boot.ini file.

AWE (Address Windowing Extensions)

AWE extends the capabilities of applications running on 32-bit operating systems by allowing access to available physical memory in excess of the limits set on their configured virtual memory address space. The advantage is that SQL Server 2005 supports dynamic allocation of AWE mapped memory. If fewer resources are required, SQL Server can return AWE mapped memory to the operating system for use by other processes or applications. Also, dynamic AWE mapped memory is supported on servers with less physical memory than the configured limit on virtual memory address space. However, on a 64-bit platform, the AWE Enabled configuration option is present, but its setting is ignored.

Note

1. Enabling PAE is a requirement for AWE memory access above 4 GB. Without /PAE switch, AWE cannot reserve memory in excess of 4 GB.

2. When the /3GB switch is used in conjunction with the /PAE switch, the OS does not use any memory in excess of 16GB. This is caused by kernel virtual memory space considerations. If the system restarts with the /3GB switch in boot.ini, and the system has more than 16GB available (physical memory), the additional RAM is not used by the OS. Restarting without the /3GB switch enables use of all the physical memory.

You can refer to the following articles for detail information to configure your SQL Server to use more than 2GB physical memory.

Reference

How to configure SQL Server to use more than 2 GB of physical memory

http://support.microsoft.com/default.aspx/kb/274750

Using AWE

http://msdn.microsoft.com/en-us/library/ms175581(SQL.90).aspx

Large memory support is available in Windows Server 2003 and in Windows 2000

http://support.microsoft.com/kb/283037/

How do I rename my SQL Server instance?

Answer

SQL Server doesn't support renaming the instance name directly. To work around the issue, you could use one of the following workarounds:

a) Uninstall the existed instance and install it with the new name.

b) Change the computer name and rename the part of the instance name that corresponds to the computer name.
For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance part of the name, Instance1, will remain unchanged. In this example, the \\ComputerName\InstanceName would be changed from \\MB1\Instance1 to \\MB2\Instance1.

For a renamed default instance, run the following procedures:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

Click here to view code as image

For a renamed named instance, run the following procedures:

sp_dropserver <old_name\instancename>
GO
sp_addserver <new_name\instancename>, local
GO

Click here to view code as image

References

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server
http://technet.microsoft.com/en-us/library/ms143799.aspx

How do I store data in multiple languages within one database?

Answer

The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and nvarchar(max) data types, instead of their non-Unicode equivalents, char, varchar, and text. Unicode is a standard for mapping code points to characters. Since it is designed to cover all the characters of all the languages of the world, there is no need for different code pages to handle different sets of characters.

References

Unicode Basics

http://technet.microsoft.com/en-us/library/ms187828.aspx

Using Unicode Data

http://technet.microsoft.com/en-us/library/ms191200.aspx

How do I calculate the space a nullable column takes in SQL Server 2008?

Answer

Scenario 1: The table without SPARSE column(s)

a) Fixed length column needs the full apace of data type even if the value of the column is NULL or empty.

b) Variable length column needs 2 bytes overhead additional for storing the data even for NULL or empty.

Scenario 2: The table with SPARSE column(s)

This scenario, is different from the one above. The values of all SPARSE columns will store together into a complex structure, which is called "Sparse Vector". It will take

4 byte header + a list of columns which values are not null + a list of column offset + real data

For each non NULL value in a row, there will be 2 bytes for the id of the column in the "column list" and 2 bytes for the "offset" of that column in the real data. This means, the non null value for a SPARSE column will take 4 + data size to store, and the NULL value for a SPARSE column will take 0 bytes to store. If all the columns in a row are NULL, the "SPARSE Vector" will be empty, and it will take 0 bytes.

For other nullable column(s), please refer to scenario 1.

For those non-sparse column, SQL Server uses NULL bitmap to indicate whether the value of a column is NULL. One NULL value of a column will take 1 bit in the NULL bitmap, so the size of the bitmap is (the number of column)/8, not including SPARSE column(s).

Why can't I view the list of databases, tables, views and etc. in the SQL ServerManagement Studio Object Explorer?

Answer

This issue is due to not having VIEW DEFINITION permission on the specific object (database, table, view etc.)

To resolve this issue, you need to grant VIEW DEFINITION permission on the object to the user which is mapped to the login you used. You can perform this by running the following Transact-SQL statement:

USE database_name;
GO
GRANT VIEW DEFINITION ON [object_name] TO
[database_user/database_role]

Click here to view code as image

More information:

VIEW DEFINITION Permission

http://technet.microsoft.com/en-us/library/ms175808.aspx

Why can't I by pass the "Restart computer" rule when installing SQL Server 2008?

Answer

This issue is usually caused by another application installation not correctly renaming or deleting the registry entry PendingFileRenameOperations. You can work around this issue by following these steps:

1. Open Registry Editor. Click Start --> Run, input "regedit" and press Enter.

2. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager, and delete any value in "PendingFileRenameOperations" key.

How do I use parameters in an SSIS ADO NET source?

Answer

You can use variables together with Expression to pass the parameters:

1. Switch to Control Flow, click the Data Flow Task.

2. In the Properties window, notice a property called Expression and a small button next to it. Click the button to open the Expression Editor.

3. In the Property list, select [The name of ADO NET Source].[SqlComamnd] and click the button under expression column to open Expression Builder.

4. Write the query with variable names and click the Evaluate Expression button to test the expression. For example:
"SELECT * FROM EMPLOYEES WHERE FNAME='" + @[User::FName] + "'"

Note: The Integration Services expression syntax is similar to the syntax that the C and C# languages use. Expressions include elements such as identifiers (columns and variables), literals, operators, and functions. For more information, you can refer to:
http://msdn.microsoft.com/en-us/library/ms140206.aspx

How do I refresh the layout information of a SQL Server Integration Services (SSIS) package that is modified programmatically?

Answer

The problem is due to the fact that when the package was first made in Business Intelligence Development Studio (BIDS), the layout information was saved within a xml tag called <packagevariable>, and all elements have their layouts written in encoded xml in the xml file.

When you modify the package through .NET and add new elements, the layout information is not updated. So when the package is loaded within the BIDS for modification, the BIDS recognizes that some layout information is available, so it will use this information to layout the ENTIRE PACKAGE. This causes the problem. To solve the issue, you can remove the layout information programmatically, then the package will layout the package in a default manner. The code below is for your reference:

Application app = new Application();
string path = @"D:\Libraries\Documents\Visual Studio
2008\Projects\WCFSSIS\WCFSSIS\FEL.dtsx";
Microsoft.SqlServer.Dts.Runtime.Package pkg = app.LoadPackage(path,
null);
Executable exec = pkg.Executables["FLC"];
(exec as IDTSSequence).Executables.Add("STOCK:SEQUENCE");
string strXML = null;
pkg.SaveToXML(out strXML, null);
//Remove the layout information.
strXML = strXML.Remove(strXML.IndexOf("<DTS:PackageVariable>"),
strXML.IndexOf("</DTS:PackageVariable>") -
strXML.IndexOf("<DTS:PackageVariable>") + 22);
strXML = strXML.Remove(strXML.IndexOf("<DTS:PackageVariable>"),
strXML.IndexOf("</DTS:PackageVariable>") -
strXML.IndexOf("<DTS:PackageVariable>") + 22);
Package pkg2 = new Package();
pkg2.LoadFromXML(strXML, null);
app.SaveToXml(@"D:\package.dtsx", pkg2, null);

Click here to view code as image

Applies to

SQL Server Integration Services 2005
SQL Server Integration Services 2008
SQL Server Integration Services 2008 R2

Can SQL Server Enterprise Edition be installed on client operating systems?

Answer

In previous releases of SQL Server, such as SQL Server 2005 or earlier versions, the setup would be blocked on client operating systems. It is no longer blocked since the SQL Server 2008 release, but it is still not a support configuration.

I recommend that you install SQL Server Developer Edition which has identical functionalities as Enterprise Edition on your client OS.

Reference

Hardware and Software Requirements for Installing SQL Server 2008:

http://msdn.microsoft.com/en-us/library/ms143506.aspx

How do I troubleshoot SQL Server connectivity issues?

Answer

You may encounter the problem that you cannot connect to your SQL Server instance remotely. You can refer to the following common practices to troubleshoot this issue:

1. Check your SQL Server Error log to make sure that it enables TCP/IP protocol and establish the listening on a specific TCP port.
Normally, if SQL Server is configured to enable TCP/IP protocol, when it starts it will log a message similar to the following error log:
2010-01-04 12:30:40.47 Server Server is listening on [ 'any' <ipv4> 1433]. This means that the SQL Server instance is using TCP/IP protocol and listening on the TCP port 1433.

2. Enable TCP/IP protocol if you could not find the message in the Error Log. You can enable protocols via SQL Server Configuration Manager. Under SQL Server Network Configuration->Protocols for <instance name>, enable TCP/IP protocol.

3. Check Network issue after TCP/IP enabled.
To check if your network is healthy, you can use ping and telnet orders to check.

• Run "ping -t" for 1-2 minutes to check if there any timeout response ping -t <the remote server IP address>
If there are timeout responses, contact your network administrator to check the network issue.

• Run "telnet" command directly to the TCP port on the server to check if you can access the TCP port.
Telnet <servername> <listening tcp port>

4. Check firewall if you cannot telnet to the specific TCP port.
Ensure that the TCP port on which SQL Server is listening has been put into the exceptions of your firewall. If you connect to a named instance, it is also recommended that you put the UDP port 1434 for SQL Server Browser service into your firewall exception.

The connection issues may be due to other causes like client drivers, application configuration, authentication and ect.

Reference

Turn on Telnet Client feature on Windows Vista or later versions and other telnet frequently asked questions:

http://windows.microsoft.com/en-us/windows-vista/Telnet-frequently-asked-questions

Troubleshooting SQL connectivity issues from Microsoft Protocols Team blogs.

http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

Why can’t I track data changes even though I already enabled the database audit on my database?

Answer

Database Audit does not track data changes. To track the data changes, you need to use another feature named "Change Data Capture" (CDC).

SQL Server Audit

SQL Server Audit is used to track and log events (not data change) occurring on the SQL Server. SQL Server uses extended events to help create an audit. The audit can be server level or database level. You can refer to the following articles to create the server and database level audit:

Creating and Managing Audits with SQL Server Management Studio

http://msdn.microsoft.com/en-us/library/cc280500.aspx

Creating and Managing Audits with Transact-SQL

http://msdn.microsoft.com/en-us/library/cc280665.aspx

If you create an audit on a database table, SQL Server will only track the events that trigger the certain operations (SELECT/INSERT/UPDATE/DELETE) defined in the database audit specification. It also checks the principles defined in the database audit specification. If the operations are not executed by the defined principles, it will not log the audits.

You can read the Windows event logs by using the Event Viewer utility in Windows. For file targets, you can use either the Log File Viewer in SQL Server Management Studio or the fn_get_audit_file function to read the target file.

Change Data Capture

Change Data Capture (CDC) is used to track the changed data caused by common DML operations (INSERT/UPDATE/DEKLETE). The capture process reads the transaction log to pull out the data change information and then load the information into the associated change table. You can use the function fn_cdc_get_all_changes_<capture instance name> to query the change data information.

Reference

Change Data Capture

http://msdn.microsoft.com/en-us/library/bb522489.aspx

How do I use Powershell script to read the information of any database on a server?

Answer

To read the information of a database, you can first grant the VIEW ANY DEFINITION permission to the current login.

VIEW ANY DEFINITION is at server scope. For example:

GRANT VIEW ANY DEFINITION TO test
Go

Click here to view code as image

Then you can read a database information with the Powershell script like the following:

$srvname="localhost"
$dbname="Test"
$mySrvConn = new-object
Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$srvname
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = "test"
$mySrvConn.Password = "xxxxxx"
$srv = new-object
Microsoft.SqlServer.Management.SMO.Server($mySrvConn)
$db = $srv.Databases[$dbname]
$db.logfiles|measure-object -property size -sum
$logsize =($db.logfiles|measure-object -property Size -sum).Sum/1024
$datasize = $db.Size - $logsize
write-host "log size:" $logsize
write-host "data size:" $datasize
$srv.ConnectionContext.Disconnect()

Click here to view code as image

Reference

SQL Server Powershell Overview

http://msdn.microsoft.com/en-us/library/cc281954.aspx

Advanced Microsoft SQL Server Powershell Tips and Tricks

http://www.microsoft.com/emea/spotlight/sessionh.aspx?videoid=984

Why can't I log on to SQL Server using Windows Authentication in SQL Server Management Studio?

Answer

If you want to log on to the server using Windows Authentication, ensure that the current Windows account has been added to server logins. To check whether the current Windows account has been added, please run the following Transact-SQL statement:

SELECT loginname FROM sys.syslogins

Click here to view code as image

If the current Windows account is not listed, you need to add the current Windows account to server logins. You can log on to SQL Server instance using a system administrator account and then run the following Transact-SQL statement:

CREATE LOGIN [Computer_Name\Local_Windows_Account_Name]
FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Click here to view code as image

You can also perform this in GUI via Management Studio, in Object Explorer, server node --> Security --> Logins, right click Logins, select New Login.

Reference

CREATE LOGIN:
http://technet.microsoft.com/en-us/library/ms189751.aspx
If you are running SSMS on Windows Vista/7, you may have to use "run as
administrator" so as to elivate the user permission.
How to: Connect to SQL Server from Windows Vista
http://technet.microsoft.com/en-us/library/bb326612.aspx

What permission(s) do I need to execute a stored procedure (sp)?

Answer

I already have EXECUTE permission on a stored procedure, but sometimes when executing the stored procedure, it fails with a permission error. Look at the following test environment:

Database:
TestDatbase
Users:
user1 (mapped to the login "login1" );
user2 (mapped to the login "login2");
Schemas:
user1 (owned by user1);
user2 (owned by user2);
Tables:
User1.Table1;
User2.Table2;
Procedures:

CREATE PROCEDURE user1.uspGetTable1
AS
SELECT * FROM user1.Table1;
CREATE PROCEDURE user1.uspGetTable2
AS
SELECT * FROM user2.Table2;

Click here to view code as image

Log on to the server with 'login1', run the procedures, you can successfully run the user1.uspGetUsers1 procedure, but when you run the user1.uspGetUsers2 procedure, you get the following error message:

Msg 229, Level 14, State 5, Procedure uspGetUsers2, Line 3
The SELECT permission was denied on the object 'Table2', database
'TestDatabase', schema 'user2'.

This issue is related to ownership chains. Since the owner of the stored procedure is different from the owner of the table user2.Table2, SQL Server will check the permission when the SELECT statement is executed on the table user2.Table2. For the stored procedure user1.uspGetTable1, since its owner is same as user1.Table1, SQL Server will not perform permission check when the SELECT statement is executed on the table.
So in this case, to execute the stored procedure user1.uspGetTable2, we not only need the EXECUTE permission on the stored procedure but also needs SELECT permission on the table user2.Table2.

Reference

Ownership Chains
http://msdn.microsoft.com/en-us/library/ms188676.aspx
Basic SQL Server Security concepts - ownership chaining: good and evil; schemas;
https://blogs.msdn.com/lcris/archive/2007/09/13/basic-sql-server-security-concepts-ownership-chaining-good-and-evil-schemas.aspx
Using a digital signature as a secondary identity to replace Cross database ownership chaining
https://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

How do I troubleshoot SQL Server 2008 installation issues?

Answer

Here are basic steps to troubleshoot SQL Server 2008 installation issues.

1. Check Windows System and Application event logs for error information according to the time of occurrence. If you are in a cluster environment, you may also need to collect the cluster log for view (By default the cluster log is located at C:\WinNT\Cluster\Cluster.log).

2. Check the SQL Server setup logs, starting from the Summary.txt file. Get it from %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log by default. Look up the error message in Summary.txt.

3. If there is not enough information in the Summary.txt, then drill down to the detailed log file that it mentioned. Check the error code and error message for detailed information. If you cannot understand the error code and error message, you can research the error code from internet.

4. If it is difficult to identify the root cause of the issue, you can post a new thread in this forum or contact Microsoft Customer Support Services (CSS).

Besides if you encounter the common asked questions regarding "Unable to install Windows Installer MSP file.", you can refer to the following two blogs for troubleshooting the issues:

SQL Server 2005 Patch Fails to install with an error "Unable to install Windows Installer MSP file"
http://blogs.msdn.com/sqlserverfaq/archive/2009/01/30/part-1-sql-server-2005-patch-fails-to-install-with-an-error-unable-to-install-windows-installer-msp-file.aspx
SQL Server 2005 Patch Fails to install with an error "Unable to install Windows Installer MSP file"
http://blogs.msdn.com/sqlserverfaq/archive/2009/02/27/part-2-sql-server-2005-patch-fails-to-install-with-an-error-unable-to-install-windows-installer-msp-file.aspx

How do I configure SQL Server to enable distributed transactions via Linked Server?

Answer

To enable distributed transactions across different servers, you need to configure MS DTC on both servers and some of your SQL Server server options. The following is a list you need to check:

1. DTC service is running please run "services.msc" from windows run dialog to check if it is running. If it is stopped, please start it by right-clicking on it and choose "Start".

2. Network DTC access is enabled
if you are using Windows Server 2003, please refer to this link:
http://support.microsoft.com/kb/817064
if you are using Windows Vista or higher version of Windows, please refer to this link:
http://technet.microsoft.com/en-us/library/cc753510%28WS.10%29.aspx

3. DTC service is configured to be allowed to access network in firewall in the linking server, please make sure this service could access the network and could receive connection from other server. In the linked server, please make sure this service could receive connection from other server.

4. XACT_ABORT is enabled
we could enable XACT_ABORT with the following statement:
SET XACT_ABORT ON

5. "Ad Hoc Distributed Queries" is enabled

please execute the status of "Ad Hoc Distributed Queries":

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries'
if the value under run_value is 0, please execute the following script
to enable it:
sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Click here to view code as image

How do I find the correct "server" or "data source" value for an SQL Server instance in a connection string?

Answer

To get the server value of a SQL Server instance, the first thing you need to do is to find the instance name. To find the instance name of a SQL Server instance, please follow the steps below:

1. Open SQL Server Configuration Manager (SSCM) from Start - > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools;

2. In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), you could find list times like SQL Server (<InstanceName>), the <InstanceName> is exactly what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (<InstanceName>) list times there.

After you find out the instance name, you can determine whether it is a default instance or named instance. If the instance name is MSSQLSERVER, it is a default instance. Otherwise, it should be a named instance such SQLEXPRESS, SQL2008 and so on. You can install multiple instances on a server but only one default instance.

After you get the instance name and know instance type (default or named instance), you will know the value of server or data source:
For default instance, use <ComputerName> as the server name; and for named instance, use <ComputerName>\<InstanceName> as the server or data source.

Note: If the SQL Server instance is installed on the local server, you can also use localhost, (local),. (dot), 127.0.0.1 (the IP address of localhost), and local IP address and <ComputerName>. If the SQL Server instance is installed on the different trusted domain, you may need to use full computer name such as ComputerName.domain.corp.company.com instead of <ComputerName>. If the SQL Server installed is public network environment, you may need to use IP address instead of <ComputerName>. If you just know the IP address/computer name and the TCP port for a SQL Server named instance, you can use "tcp:<server ip address/computer name>,<TCP port>" as the value of server or data source.

Once we get the server value, we can make connections to the SQL Server instance with the correct connection string as follows: Server=WinSrv1\SQL2008;Database=myDataBase;UID=myUsername;PWD=myPassword;
orData Source=WinSrv1\SQL2008;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

How do I configure Windows Firewall to allow remote connections using TCP/IP protocol for SQL Server?

Answer

On the server on which SQL Server runs, we need to find out which ports that SQL Server is listening and add which to Windows Firewall Inbound Exceptions. Firstly, we need to add TCP port on which SQL Server service is listening to the Windows Firewall Inbound Exceptions so that the client can make connections to SQL Server over Windows Firewall.

Secondly, if we do not specify TCP port in the connection string and SQL Server is not listening on the TCP 1433 port, we need to add UDP 1434 port on which SQL Server Browser Services is listening to the Windows Firewall Inbound Exceptions. It is because:

a) If TCP port is specified in the connection string, the connection provider such as SQL Native Client will use this port to connect to SQL Server, so that the connection provider does not need SQL Server Browser Services to find the corresponding on which SQL Server is listening;

b) If TCP port is not specified in the connection string, the connection provider will send a UDP package to 1434 port to the server. So, if UDP 1434 port is not open in the Windows Firewall therefore the connection provider will not get the corresponding TCP port on which SQL Server is listening. As a result, the connection provider will use TCP 1433 port to connect to SQL Server, which may be failed if the SQL Server does not listen on TCP 1433 port. So as to make connection to SQL Server in this case, we need to add UDP 1434 port to Windows Firewall Inbound Exceptions.

To find out the TCP port on which SQL Server is listening, please follow the steps below:

1. Open SQL Server Configuration Manager from Start -> All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools;

2. Click Protocols for <SQLInstanceName> under SQL Server Network Configuration node in the left panel of SQL Server Configuration Manager window; Double-click TCP/IP in the right panel, switch to IP Address tab and you could find out all ports that SQL Server is listening for each IP address or a specify port for all IP addresses.

Please note that if SQL Server is listening on dynamic ports, its ports may be changed after restarting each time. As a result, you need to find its current listening TCP port(s) and add all of them to Windows Firewall Inbound Exceptions.

References

SQL Server Browser Services
http://msdn.microsoft.com/en-us/library/ms181087.aspx
How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager):
http://msdn.microsoft.com/en-us/library/ms177440.aspx

Applies to

Microsoft SQL Server 2005 all editions;
Microsoft SQL Server 2008 all editions;
Microsoft SQL Server 2008 R2 all editions

Could SQL Server 2008 Express edition and SQL Server 2008 R2 Express edition be installed side by side with SQL Server 2005 Express?

Answer

Yes, but we need pay attention to the following points for installing SQL Server 2008 Express and SQL Server 2008 R2 Express side by side with SQL Server 2005 Express:

1. If there is no SQL Server 2005 Express Management Tools installed on the computer, then the three editions of SQL Server 2008 Express and SQL Server 2008 R2 Express(SQL Server Express, SQL Server Express with Tools and SQL Server Express with Advanced Services) could be full installed side by side with SQL Server 2005 Express.

2. If the SQL Server 2005 Express Management Tools or the alone SQL Server Management Studio Express are installed, then the Management Tools of SQL Server 2008 Express and SQL Server 2008 R2 could not be installed since the tools are installed into a shared directory, we will get the following error message:

Rule "SQL Server 2005 Express tools failed".

The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.

To solve this issue, we need to uninstall Management Tools of SQL Server 2005 Express from Add/Remove programs.

Installing SQL Server 2008 Express and SQL Server 2008 R2 Express is same with the other editions, please refer the below links:

Reference

How to: Install SQL Server 2008 R2 (Setup)
http://msdn.microsoft.com/en-us/library/ms143219.aspx
How to: Install SQL Server 2008(Setup)
http://msdn.microsoft.com/en-us/library/ms143219(SQL.100).aspx
Installing SQL Server 2008 Express Guidance
http://msdn.microsoft.com/en-us/library/dd981045(SQL.100).aspx

Applies to

Microsoft SQL Server 2005 Express
Microsoft SQL Server 2008 Express
Microsoft SQL Server 2008 R2 Express

How do I apply a service pack for an instance of SQL Server 2005 Express Edition or SQL Server 2008 Express Edition?

Answer

The behavior to apply a service pack for an instance of SQL Server Express Edition has been changed since SQL Server 2008 than prior SQL Server 2005.

In SQL Server 2005, to install a service pack for an instance of SQL Server 2005 Express Edition, we cannot use SQL Server 2005 Service Pack which is used to upgrade non-Express Editions but need to use SQL Server 2005 Express Edition Service Pack. Also, SQL Server 2005 Express Edition can also be used for new installations. Here are details:

a) To install a service pack for an instance of SQL Server 2005 Express Edition, please use SQL Server 2005 Express Edition Service Pack.

b) To install a service pack for an instance of SQL Server 2005 Express Edition with Advanced Services, please use SQL Server 2005 Express Edition with Advanced Services Service Pack.

However, the behavior was changed since SQL Server 2008. SQL Server 2008 Express Edition Service Pack is only used for new installation (with service pack patched); and to upgrade an instance of SQL Server 2008 Express Edition, please use SQL Server 2008 Service Pack instead which can upgrade all editions of SQL Server 2008.

Applies to

Microsoft SQL Server 2005
Microsoft SQL Server 2008

How do I resolve ‘SQL Server Setup cannot valid the service accounts’ error message during the setup?

Answer

The error message typically happens when you use domain accounts as service account. If the Activity Directory Service (ADS) is not available currently, so the setup cannot valid the domain accounts you specify.

As a work around, you can temporarily choose built-in system accounts from dropdown list during the installation which should work to complete the setup. However, a built-in system account may have addition privileges than SQL Server service actually requires. Therefore, we recommend reconfiguring each service of SQL Server to use an individual domain account or local Windows account.

Applies to

Microsoft SQL Server 2005
Microsoft SQL Server 2008
Microsoft SQL Server 2008 R2

Why do I get ‘Rule "Existing clustered or clustered-prepared instance" failed’ error while adding new features to an existing instance of SQL Server Failover Cluster?

Symptom

After SQL Server 2008 or 2008 R2 cluster setup, you may want to add new features that were not installed during the initial installation. Therefore, you try to add addition features to this cluster. For instance, if you perform this operation using GUI, you may use the following steps:

1. Start SQL Server setup, and ‘select New installation or add features to an existing installation’ wizard.

2. Choose the cluster instance which you want to add features to in the Installation Type page.

3. Select features you want to add and following the wizard.

However, in the ‘Installation Configuration Rules’ page, you get the following error:

Rule "Existing clustered or cluster-prepared instance" failed. The instance selected for installation is already installed and clustered on computer xxx. To continue, select a different instance to cluster.

Answer

The cause to this issue is that SQL Server 2008 or 2008 R2 does not support adding new features to an existing failover cluster instance. However, the installation is not be blocked until you get the ‘Installation Configuration Rules’ error.

To work around this issue, you could:

a) If you want to install cluster-aware feature such as SSAS or SSRS with failover cluster, you need to select to install during the initial installation of SQL Server failover cluster, or install these features as a new installation/instance after initial installation of SQL Server failover cluster.

b) If you want to install non-cluster features/shared components such as SSMS, SSIS and etc., you can perform a new installation and select these features during the installation which will not install a new instance of SQL Server but only install all those shared components.

Reference

SQL Server 2008 Failover Clustering Whitepaper
http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx

Applies to

Microsoft SQL Server 2008
Microsoft SQL Server 2008 R2

How do I clear the warm cache of a cube?

Answer

Sometimes you need to clear the warm cache of a cube so as to compare the query performance between using the cache and not. In this case, you can run the following XMLA script to clear it.

<Batch
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <ClearCache>
      <Object>
         <DatabaseID>Adventureworks</DatabaseID>
         <CubeID>Sales</CubeID>
      </Object>
   </ClearCache>
</Batch>

Click here to view code as image

Note: Please change the DatabaseID and CubeID to your DatabaseID and CubeID accordingly.

You can execute it in SQL Server Management Studio (SSMS). Open SSMS, connect to the Analysis Services instance, click File->New->Analysis Services XMLA Query, and input the XMLA script as followsand execute it.

After you clear the cache, it is recommended that you execute the following MDX query to reload the MDX script without loading any data in the cache:
SELECT {} ON 0
FROM [cube name]

How do I exclude data members or create non-aggregatable values in parent-child dimensions?

Answer

In Analysis Services 2005 and later versions, data members cannot be excluded from parent-child hierarchies. For example: the one below is the default parent-child dimension in cube browser:

Amy E. Alberts | Jae B. Pak | Total | $8,503,338.65
| Rachel B. Valdez | Total | $1,790,640.23
| Ranjit R. VarkeyChudukatil | Total | $4,509,888.93
| Total |  | $15,535,946.26

Click here to view table as image

The value of Amy is 15,535,946.26, which includes the data member of Amy (732,078.44) and the subtotals of children (14,803,867.81). But for some reason, if you want to exclude the data member of Amy, then you should use an MDX Script assignment to overwrite the value of Amy:

SCOPE([Measures].[Reseller Sales Amount]);
THIS=IIF(
IsLeaf([Employee].[Employees].CurrentMember)
,[Measures].[Reseller Sales Amount]
,Sum
([Employee].[Employees].CurrentMember.Children
,[Measures].[Reseller Sales Amount])
);
ENDSCOPE;

Then you will get the result like this:

Amy E. Alberts | Jae B. Pak | Total | $8,503,338.65
| Rachel B. Valdez | Total | $1,790,640.23
| Ranjit R. Varkey Chudukatil | Total | $4,509,888.93
| Total |  | $14,803,867.81

Click here to view table as image

If you want to display data members for a parent-child hierarchy, then you can use an MDX Script assignment to overwrite the value of Amy:
SCOPE([Measures].[Reseller Sales Amount]);
THIS=([Employee].[Employees].CURRENTMEMBER.DATAMEMBER,
[Measures].[Reseller Sales Amount]);
ENDSCOPE;
Then you will get the result like this:

Amy E. Alberts | Jae B. Pak | Total | $8,503,338.65
| Rachel B. Valdez | Total | $1,790,640.23
| Ranjit R. VarkeyChudukatil | Total | $4,509,888.93
| Total |  | $732,078.44

Click here to view table as image

You can also create a calculated member to avoidmodifying the default measure values. But using Script is better for performance.

How do I specify a calculated member as the default member of an attribute?

Answer

If you specify a calculated member as the default member in the properties window, then you will receive an error message. This is because you create the calculated member in MDX Script but process the default member specified in UI before the MDX Script is executed. So you will see the error because no calculated member was found.

You can use ALTER CUBE statement at the end of MDX Script, or just execute the Script in SSMS:

AlterCube
[Adventure Works]
UpdateDimension [Destination Currency].[Destination Currency],
Default_Member = [Destination Currency].[Destination Currency].[US Dollar];

Reference

http://msdn.microsoft.com/en-us/library/ms144822.aspx

How do I create AVG measure in my cube?

Answer

The build-in aggregation AverageOfChildren is a semi-additive aggregation. It behaves the same as measures with aggregation type Sum on all dimensions except Time dimensions. If you want to create a measure to calculate the average sales amount of customers for all dimensions, then you can try this:

1. Create a measure [Customer Count] withaggregation DistinctCount for the customers.

2. Create a measure [Sales Amount] withaggregation Sum for the sales amount.

3. Create a calculated measure with expression: [Sales Amount]/ [Customer Count], and then set the additional properties.

How do I implement dynamic security for the users?

Answer

There is a dimension Reseller in our cube, and we can create roles to grant each user (Reseller) access to the cube to let them see their own sales data. However, if there is a large number of users, then we need to create lots of roles. Even if you can create so many roles, it’s still hard for maintenance. But, we can implement dynamic dimension security for each reseller. You can try the steps below:

1. Create a Users table containing the users in the underlying database. Create a dimension named User from this table.

2. Create a Factless table to maintain the relationship between the users and the Resellers:
users | Resellers
user1 ResellerA
user1 ResellerB
user2 ResellerB
After that, create a measure group base on this table.

3. The relationship in DSV is similar like this:
User <- Factless -> Reseller <- fact table
So, create regular relationship between the two dimensions and the Factless measure group.

4. Create a role for the users, in Dimension Data tab, select the attribute Reseller in dimension Reseller, switch to Advanced tab, the allowed member set should be:
NonEmpty( Reseller.Reseller.Members,
( StrToMember("[Users].[User].["+UserName()+"]"),
[Measures].[Bridge Reseller Users Count] ))
Test the role in your cube browser, if it works then deny access to the dimension user and the factless measure group.

What is the common usage of Unknown member?

Answer

Unknown member is useful for referential integrity issues and the NULL values issue. There are two scenarios:

Referential Integrity

You may encounter this error message:
"No record (key) found in dimension"
If some records in your fact table are not found to have matched keys in your dimension table. For example:
Fact table:

ProductKey | Value
1 | 1
2 | 2
3 | 3
100 | 100

Click here to view table as image

Dimension table:

ProductKey | Family
1 | Food
2 | Food
3 | Beverages

Click here to view table as image

To overcome this error message, you can configure the error message setting and the unknown member setting through the link below
http://msdn.microsoft.com/en-us/library/ms345138(SQL.90).aspx

Then you will see the results below in Browser:

Key | Value
1 | 1
2 | 2
3 | 3
Unk | 100

Click here to view table as image

NULL Values

Same fact table, but for the dimension table, we add one row:

ProductKey | Family
1 | Food
2 | Food
3 | Beverages
4 | NULL

Click here to view table as image

Create a natural hierarchy Family -> ProductKey, set the attribute relationship ProductKey <- Family. After that, modify the keycolumn setting of the attribute Family, convert NULL to unknown member (Unk). Remember to reprocess the dimension then you will see the results below in Browser:

Food | Value
1 | 1
2 | 2
Beverages |
3 | 3
Unk |
4 |
UNK | 100

Click here to view table as image

How do I achieve Cumulative Sums for a set in MDX?

Answer

Suppose there is a named set [SortedCus], if you want to get the cumulative sums for each member, then you can try the sample below against the sample database [Adventure Works]:

WITH
SET [SortedProduct] AS
Order
(
[Product].[Product].[Product]
,[Measures].[Internet Sales Amount]
,BDESC
)
MEMBER [Measures].[Cumulative Sums] AS
Sum
(
Head
(
[SortedProduct]
,Rank
(
[Product].[Product].CurrentMember
,[SortedProduct]
)
)
,[Measures].[Internet Sales Amount]
)
SELECT
{
[Measures].[Internet Sales Amount]
,[Measures].[Cumulative Sums]
} ON 0
,[SortedProduct] ON 1
FROM [Adventure Works];

Click here to view code as image

How do I use wildcards in MDX?

Answer

There is no build-in function for this. Generally, we can achieve that through the approaches below:

1. Using VBA function instr() in Filter(), for example:

SELECT
{} ON 0
,Filter
(
{[Employee].[Employees].MEMBERS}
,
VBA!Instr([Employee].[Employees].CurrentMember.Name,"RA") > 0
) ON 1
FROM [Adventure Works];

Click here to view code as image

For more information about the Instr(), you can refer to:
http://msdn.microsoft.com/en-us/library/8460tsh1(VS.80).aspx

2. Using Stored Procedures. You can create your own Stored Procedures to achieve this, you can also download Analysis Services Stored Procedures project from:
http://www.codeplex.com/wikipage?ProjectName=ASStoredProcedures
After that, you can use the code like this:

SELECT
{} ON 0
,ASSP.Like([Employee].[Employees].Members
,"%RA%"
,[Employee].[Employees].CurrentMember.Name)
ON 1
FROM [Adventure Works];

Click here to view code as image

How do I query multi-dimensional data in a relational database with T-SQL?

Answer

You can use OpenQuery and OpenRowSet to execute pass-through queries against a cube. You can try the steps blow against sample database:

1. In SSMS (SQL Server Management Studiu), connect to the database engine, expand the folder ‘providers’ below the folder ‘linked server’, double click the provider ‘MSOLAP’, and then ensure option ‘Allow InProcess’ has been selected.

2. create a linked server:

EXEC sp_addlinkedserver
@server='TestForOpenQuery', -- the given name to the linked server @srvproduct='',
@provider='MSOLAP',
@datasrc='localhost',--the instanse name of SSAS(machine/instanse)
@catalog='Adventure Works DW 2008'

3. Execute the query:

SELECT "[Product].[Category].[Category].[MEMBER_CAPTION]" AS Category,
ROUND(CONVERT(float,"[Measures].[Internet Sales Amount]"),2) AS Amount
FROM OpenQuery(TestForOpenQuery, 'select {[Measures].[Internet Sales
Amount]} on 0, [Product].[Category].members on 1 from [Adventure
Works]')

Then you will get this:

Category | Amount
NULL | 29358677.22
Accessories | 700759.96
Bikes | 28318144.65
Clothing | 339772.61
Components | NULL

Click here to view table as image

How do I resolve the error "File system error: A FileStore error from WriteFile occurred. Physical file: ...." when I process a dimension by using the Process Update option?

Answer

When you process a dimension by using the Process Update processing option or the Process Default processing option, the string file store (.asstore) is appended instead of being overwritten. This behavior reduces the process time even if no change is made on the string attribute. If the dimension is frequently processed that way, the string file store will become very large. When the string file store reaches 4 GB, you can no longer process the dimension by using the Process Update processing option or the Process Default processing option. You will receive an error message:
"File system error: A FileStore error from WriteFile occurred. Physical file: ...."

If you are doing ProcessUpdate on a regular basis, I would suggest doing processFull now and then. When you process a dimension by using the Process Full processing option, all the data of the dimension will be dropped and reconstructed. The string file store will be smaller.

How do I connect to a SSAS cube through Internet?

Answer

Sometimes your company has security policy that restricts you to access the SQL Server Analysis Services through TCP/IP protocol since most TCP ports are not allowed by firewall. In this case, you can set up HTTP access to your Analysis Services instance.

The following articles are helpful for you to configure HTTP Access to your SSAS on different OS:
Configuring HTTP Access to Analysis Services on Microsoft Windows XP
Configuring HTTP Access to Analysis Services on Microsoft Windows Server 2003

There is a third party blog which extends the description on configuring this on Windows Vista. We post the link here just for your reference:
Connecting HTTP Access to Analysis Services on Microsoft Windows Vista (IIS 7)

What will happen if I create two roles for a user?

Answer

Permissions across multiple roles are additive. If a user belongs to a role which has access to all dimensions, then he/she will always have this permission even if we have created another role to restrict this user. So, you may need to check the roles you belong to. You can try the statement below:
SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

The Roles column will have a comma-separated list of the roles that users belong to for that database. However, it only returns the current users’ role. So, if you are an administrator, it will return all roles.

You can choose which roles you would like to use to connect to a server by specifying Roles parameter in your connection string. For example:

Data Source=myASserver;Initial Catalog="Adventure Works
DW";Roles=Operation Manager, Sales Manager;

Click here to view code as image

How do I connect to SSAS instance without SQL Browser services started?

Answer

By default SSAS uses the TCP port 2383 (the configured port is 0). SQL Server Browser Services uses the TCP port 2382 for redirecting client requests to the correct Analysis Services instance. However if SQL Server Browser Services is not started or not allowed to start by company security policy when a client tries to establish a connection, it cannot locate the SSAS instance. To resolve this issue, you need to explicitly specify the TCP port in your connection string in your client.

For example:

AdomdConnection cn = new AdomdConnection("Data
Source=myASserver:2383;Initial Catalog=ASTest;Integrated
Security=SSPI;");
     try
       {
       cn.Open();
       MessageBox.Show("Succeed!");
     }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
      }
      finally
      {
          cn.Close();
      }

Click here to view code as image

How do I keep on accessing my cube in MOLAP storage mode when it is processed?

Answer

Generally there are two solutions:

1. Use proactive caching

2. Incorporate a staging server

If there are only data changes in the data source, you can use proactive caching for transparent processing. You do not have to manually process the cube and it can provide minimum latency.

The second method of using a staging server is useful when there are structure changes in your cube definition. In this case, a whole cube process may be required. By incorporating a staging server, you can perform cube processing jobs on the staging server. After that, use the Synchronize Database Wizard to synchronize the Analysis Services database between your staging server and production server.

Reference

Proactive Caching (Partitions)
http://msdn.microsoft.com/en-us/library/ms174769.aspx
Synchronizing Analysis Services Databases
http://msdn.microsoft.com/en-us/library/ms174928.aspx

How do I have the members of a dimension displayed in my custom order?

Answer

This can be implemented by introducing an additional attribute column for display order and setting OrderByAttribute to the new attribute column. Let us say that your dimension attribute name is Type.

Then you can follow these steps to define the display order of the Type members:

1. Create a Named Calculation for the dimension
In your data source view (DSV), you can add a named calculation "DisplayOrder" to your dimension. The Expression is:

CASE
WHEN Type='A' THEN 3
WHEN Type='B' THEN 1
WHEN Type='C' THEN 2
WHEN Type='D' THEN 4
ELSE 5
END

Click here to view code as image

2. Add the DisplayOrder to the dimension attribute
Open the dimension, under Dimension Structure, right-click the DisplayOrder in the Data Source View panel, click "New Attribute from Column".

3. Set the attribute properties
Select "Display Order", set the attribute "Attribute Hierarchy Enabled" to False, set"AttributeHierarchyOptimizedState" to "NotOptimized" , set AttributeHierarchyOrdered to False, set "AttributeHierarchyVisible" to False.

4. Define Attribute Relationship.
Select Attribute Relationship tab, right click Type, click New Attribute Relationship, choose Type as the Source Attribute and Display Order as the Related Attribute.

5. Switch to the Dimension Structure tab, select the Type attribute, set its property "Order By" to "AttributeName", and set "OrderByAttribute" to "Display Order".

6. Save the changes and process your dimension.

Reference

Sorting Attribute Members Based on a Secondary Attribute
http://technet.microsoft.com/en-us/library/ms166763.aspx

How do I run MDX query in Excel 2007/2010?

Answer

Currently in Excel 2007, it is a limitation that you cannot directly execute MDX via UI input. The following three methods are common workarounds:

1. Modify the connection file (.odc).

When you create a SSAS connection, there will be a connection file (.odc) generated in the folder C:\Users\<username>\My Documents\My Data Sources. Open the connection file with note pad and you will find a piece of XML code in it like the following:

< xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
    <o:Name>. PueStores</o:Name>
</o:DocumentProperties>
< /xml><xml id="msodc">
   <odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
       <odc:Connection odc:Type="OLEDB">
          <odc:ConnectionString>Provider=MSOLAP.4;Integrated
Security=SSPI;Persist Security Info=True;Data Source=.;Initial
Catalog=PueStores</odc:ConnectionString>
          <odc:CommandType>Cube</odc:CommandType>
          <odc:CommandText>PueStores</odc:CommandText>
       </odc:Connection>
    </odc:OfficeDataConnection>
    < /xml>

Click here to view code as image

Now you just need to change two places. Change the <odc:CommandType> value to "MDX" and <odc:CommandText> value to your MDX statement. For example:

< xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
   <o:Name>. PueStores</o:Name>
</o:DocumentProperties>
< /xml><xml id="msodc">
   <odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
       <odc:Connection odc:Type="OLEDB">
          <odc:ConnectionString>Provider=MSOLAP.4;Integrated
Security=SSPI;Persist Security Info=True;Data Source=.;Initial
Catalog=PueStores</odc:ConnectionString>
          <odc:CommandType>MDX</odc:CommandType>
          <odc:CommandText>
             SELECT NON
EMPTY{([Store].[Store].AllMembers,[Measures].[Sales Amount])} on 0,
             NON EMPTY [Date].[YTD].AllMembers ON 1
             FROM [PueStores]
             < /odc:CommandText>
       </odc:Connection>
   </odc:OfficeDataConnection>
   < /xml>

Click here to view code as image

Save the file, and then in Excel, click Data->Existing Connections, select the connection under "Connection files on this computer" and click Open. This can be a workaround but it is not very convenient if you need to change the MDX statement.

2. Execute MDX via a SQL Server stored procedure

To do this, you need to first create a stored procedure in a database. For easy use, I create it in a master database:

create procedure myMDX(@mdx varchar(max))
as
DECLARE @strSQL nvarchar(max)
SET @strSQL=N'SELECTa.* FROM OpenRowset(''MSOLAP.4'',''DATASOURCE=
localhost ; Initial Catalog=PueStores;'','''
+ @mdx +''') as a'
EXEC sp_executeSQL @strSQL

Click here to view code as image

Then in Excel 2007, select Data->From Other Sources->From Microsoft Query, create a new data source for your SQL Server instance with "SQL Server Native Client" driver, choose the default database to master. After you create the data source, click OK, in the pop-up designer window, click Close to close the inner query editor window, and then click File->Execute SQL…, choose the data source and input the following SQL:

exec myMDX 'SELECT NON
EMPTY{([Store].[Store].AllMembers,[Measures].[Sales Amount])} on 0,
NON EMPTY [Date].[YTD].AllMembers ON 1
FROM [PueStores]'

Click here to view code as image

Then close the window, you can choose Table or PivotTable to display the output. The advantage of this method is that you can directly change the MDX statement in the connection properties and the data will be refreshed. The disadvantage is that you are introducing an intermediate SQL Server instance for query.

3. Install an add-in for Excel like OLAP PivotTable Extensions.

There are some third-party add-ins for Excel like OLAP PivotTable Extensions, however since this is a third-party software, before you make the decision to use it, please first test it and make sure that it applies to your corporate policy.

How do I automatically deploy my Analysis Services 2005/2008 project?

Answer

The full deployment process of a SSAS project includes building, producing a deployment script file and deploying SSAS database with the script file. You can create a batch file to execute these steps: --Build the project

1. devenv.exe /Build <myprojectfile>
-- Producing the deployment script

2."C:\ProgramFiles\Microsoft SQL
Server\100\Toos\Binn\VSShell\Common7\IDE\
Microsoft.AnalysisServices.Deployment.exe"<filename>.asdatabase /s /o:
"<scriptfile>.xmla"
-- Deploy the SSAS database according to the script file

3. ascmd.exe -S <servername> -i <scriptfile>.xmla

Reference

devenv command line switch
http://msdn.microsoft.com/en-us/library/xee0c8y7.aspx
Microsoft.AnalysisServices.Deployment Utility
http://msdn.microsoft.com/en-us/library/ms162758(SQL.90).aspx
Readme For Ascmd Command-line Utility Sample

How do I process a cube via T-SQL?

Answer

There are some methods like starting a SQL job to execute Analysis Services command or using xp_cmdshell to execute ascmd to process a cube. However these methods require additional RPC calls and do not look graceful. A better way is to create a CLR stored procedure to process the cube.

1. Alter your database to set it to Trustworthy

ALTER DATABASE Test SET TRUSTWORTHY ON

Click here to view code as image

2. Run "CREATE ASSEMBLY" to load the ADOMD.NET assembly

CREATE ASSEMBLY [AdomdClient]
AUTHORIZATION [dbo]
FROM 'C:\Program
Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.Ad
omdClient.dll'
WITH PERMISSION_SET = UNSAFE;

Click here to view code as image

3. Create a SQL Server Database project, add the reference AdomdClient under the SQL Server tab.

4. Create a CLR stored procedure as following:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;
public partial class StoredProcedures
{
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void FullProcessCube(SqlString strServer, SqlString
strDbName, SqlString strCubeName)
   {
      // Put your code here
      //Open a connection to the local server
      AdomdConnection conn = new AdomdConnection(String.Format("Data
Source={0}", strServer));
      conn.Open();
      //Create a command, and assign it an XMLA command to process
the cube.
      AdomdCommand cmd = conn.CreateCommand();
      cmd.CommandText = String.Format("<Process
xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\
">\r\n" +
       @"<Object>
<DatabaseID>{0}</DatabaseID>
<CubeID>{1}</CubeID>
</Object>
<Type>ProcessFull</Type>
< /Process>", strDbName, strCubeName);
       //Execute the command
       int result = cmd.ExecuteNonQuery();
       //Close the connection
       conn.Close();
    }
};

Click here to view code as image

5. Set the Project property "Permission Level" to Unsafe.

6. Build the project and deploy it to your SQL Server database.

7. Enable clr on your SQL Server by using sp_configuration

sp_configure 'clr enabled',1
go
reconfigure with override
go

Click here to view code as image

8. Execute the stored procedure.

exec dbo.FullProcessCube'localhost','ASDemo','DemoAdventure'

Click here to view code as image

Reference

Overview of CLR Integration
http://msdn.microsoft.com/en-us/library/ms131045.aspx
CREATE ASSEMBLY (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189524.aspx
ADOMD.NET Client Programming
http://msdn.microsoft.com/en-us/library/ms123477.aspx
XML for analysis (XMLA)
http://msdn.microsoft.com/en-us/library/ms187178(SQL.90).aspx

Can I perform writeback operations in Excel 2007?

Answer

No. Excel 2007 does not support writeback to SSAS database. You have to manually run "UPDATE CUBE" statement for writeback. However writeback is supported in Excel 2010.

Reference

Using Cube Writebacks (MDX)
http://msdn.microsoft.com/en-us/library/ms145568.aspx
Excel 2010 PivotTable What-If Analysis (Writeback)
http://blogs.msdn.com/b/excel/archive/2009/10/12/excel-2010-pivottable-what-if-analysis.aspx

How do I troubleshoot the slow MDX query performance?

Answer

For MDX query performance, it is usually caused by the following factors:

1. Bottlenecks from SSAS Formula engines.

2. Bottlenecks from SSAS Storage engines.

3. Bottlenecks from system resources.

You can use SQL Server Profiler and System Performance Monitor to track the cause of the issue. For formula engines, you need to check the following events:
Query SubCube
Query Begin
Query End

You can determine how much time the storage engine consumes by checking the Query SubCube event's Duration value. Compare it with the Duration value in Query End event. If it is larger than 30%*(Duration in Query End event), your SSAS storage engine may have performance bottlenecks. You can add Query SubCube Verbos event for more detailed information on the TextData column. When this issue happens, you can consider refreshing your cache or using partitions to improve performance.

If the subtraction between the total time of query and the time spent on Query SubCube is the query cost spent on formula engine. If it is larger than 30%*(Duration in Query End event), your SSAS formula engine may have performance bottlenecks. You need to narrow down the cost of the query by adding more events under Query Processing.

For bottlenecks from system resources, you need to use System Performance Monitor to monitor your server and Analysis services instance to check if there is any memory, I/O, CPU, and network bottlenecks.

You can read the following articles for detailed information and MDX optimization:
SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
http://www.microsoft.com/downloads/details.aspx?FamilyId=975C5BB2-8207-4B4E-BE7C-06AC86E24C13&displaylang=en
SQL Server 2008 White Paper: Analysis Services Performance Guide
http://www.microsoft.com/downloads/details.aspx?FamilyID=3be0488d-e7aa-4078-a050-ae39912d2e43&displaylang=en
Best practices for SQL Server 2005/2008 OLAP cube design and MDX querying
http://technet.microsoft.com/en-us/library/dd285464.aspx

How do I hide the toggle image (+) if there is no row when using drill-down?

Answer

You can solve this issue by creating an additional independent column in SQL Server Reporting Services 2005/2008.

1. Right click the parent column, and insert a new column to the left.

2. Right click the child column’s field, click Edit Group…, and switch to the Visibility tab. Select "Visibility can be toggled by another report item" and set Report Item to the name of a textbox in the new added column.

3. Click the textbox you choose in the 2<sup>nd</sup> step, in the Properties window, locate the Visibility property. Set the Hidden value to "=iif(Fields!details.Value is nothing,true,false)".

4. You may need to modify the background color of the data region (matrix or table) to avoid displaying a blank cell when the toggle image (+) is hidden.

How do I achieve nested aggregation?

Answer

To avoid using Expression with nested aggregations "Sum(Max(Data))", you can change the dataset. For example: add a new column in your dataset to calculate the Max value for the group and then use the Sum(Max(…)) in your report.

Or you can try the workaround below. Imagine that group1 contains group2 and group2 contains details, and you want to use sum(max(data)):

1. Add a "fake" in dataset like this

UNION
SELECT '_fake' AS Group1, '_fake' AS Group2, '_fake' AS
Detail, … …

Click here to view code as image

2. Sort the data by descending, so the fake data will show at the last group because of the character ‘_’.

3. Copy the following code to the custom code area:

Dim Aggregation as Decimal

Function AddValue(ByVal NewValue As Decimal) as Decimal

Aggregation = Aggregation + NewValue

Return Aggregation

End Function

Function GetAggregation() as object

GetAggregation = Aggregation

Aggregation = 0 'Reset the group

Return GetAggregation

End Function

Click here to view code as image

4. Delete the footer of group1, then right-click the handle of group1’s header and insert a row above. Below is the structure:
Group 1 new Header
Group 1 Header
Group 2 Header
details
Group 2 Footer

handles | (Tableheader )
1 | =Previous(Fields!group1.Value) | =code.GetAggregation ()
1 | = (Fields!group1.Value) |
2 | =Fields! Group2.Value | =code.AddValue(max( data))
== | Details (Table footer) | details

Click here to view table as image

5. Right-click the handle of the new header, click the properties (not edit group). In Properties window, set the visibility to be
"=iif(Previous(Fields!group1.Value) is nothing, true,false)".

6. Right click the handle of each one of the other rows, click the properties (not edit group). In Properties window, set the visibility to be
"=iif(Fields!group1.Value = "_fake", true,false)".

How do I achieve column break in a matrix?

Answer

Here are some workarounds, available forboth Reporting Services 2005 and2008:

Workaround 1

Spread the columns from one matrix into several matrixes. You can first copy one matrix and then paste it into several ones you want. Then set the filter for each column group to make sure that the total columns’ length in one matrix just fit a page’s width.

Workaround 2

The other method is to use a custom code.

1. Please copy the following code to the custom code area:

Dim FlagTable As System.Collections.Hashtable
Dim Flag AS Integer
Function MyFunc(ByVal NewValue As Object) As Integer
If (FlagTable Is Nothing) Then
FlagTable = New System.Collections.Hashtable
End If
If (NewValue Is Nothing) Then
NewValue = "-"
End If
If (Not FlagTable .Contains(NewValue )) Then
Flag =Flag + 1
FlagTable.Add(NewValue, nothing)
End If
MyFunc = Flag
End Function

Click here to view code as image

2. Create a list in your report.
Imagine that the column group of a matrix is grouped bythe field ‘Column_Group’, then set the detail group of list withthe expression like this: =Ceiling(Code.MyFunc(Fields!Column_Group.Value)/5)
Note: This means the Max number of column in matrix will be five after you follow step C.

3. Sort the dataset by column group field, and then drag the matrix into the list. Click Preview.

Workaround 3

Similar to the second method, you need to modify the dataset.

1. Create an ID column for the column group in your dataset.
For example,there isa datasetwith the following query:

SELECT * FROM Table

Click here to view code as image

The column group is grouped on the field "Group1".Then, modify the query like this:

SELECT *, Dense_Rank()OVER(order by Group1) AS ID FROM Table

Click here to view code as image

2. Create a list in your report, set the detail group of the list with the Expression like this:
=Ceiling(Fields!ID.Value/5)
Note: This meansthat the Max number of column in matrix will be five after you followthe step C.

3. Sort the dataset bythe column group and then drag the matrix into the list. Click Preview.

How do I use Parent-Child Hierarchies and formatted value in report with Analysis Services?

Answer

To resolve this issue, you can use the Extended Field Properties which is supported in SSRS.

To retrieve the extended properties, for example Formatted Value, you need to add the properties to the MDX query:

VALUE,BACK_COLOR,FORE_COLOR,FORMATTED_VALUE
,FORMAT_STRING,FONT_NAME,FONT_SIZE,FONT_FLAGS

Click here to view code as image

Afterwards, use the extended property in your cube:
=Fields!Employees.FormattedValue

For more information, reference:
http://msdn.microsoft.com/en-us/library/ms156477.aspx
Imagine that Employees is the Parent-Child Hierarchy, so the group expression in report could be:
=Fields!Employees.UniqueName
Father group expression could be:
=Fields!Employees.ParentUniqueName
Value expression could be:
=Fields!Measure.FormattedValue

Afterwards, set the visibility property, padding property for the report as above documents described.

Next, you may want to filter the data in your report. For example, just display memberA and the descendant and ancestor of memberA. Simply creating the filter in report may not work, because you may destroy the parent-child relationship. It can be achieved in query designer. Drag the hierarchy to the filter pane, select the option ‘Parameters’. Or try the query below to display descendant and itself:

SELECT
NON EMPTY
{[Measures].[Reseller Sales Amount]} ON COLUMNS
,NON EMPTY
{Descendants(StrToSet(@EmployeeEmployees,CONSTRAINED))}
DIMENSION PROPERTIES MEMBER_CAPTION,MEMBER_UNIQUE_NAME
,PARENT_UNIQUE_NAME ,LEVEL_NUMBER
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES
VALUE,BACK_COLOR,FORE_COLOR ,FORMATTED_VALUE
,FORMAT_STRING,FONT_NAME,FONT_SIZE,FONT_FLAGS;

Click here to view code as image

Note: For relational databases, if you want to filter the members, you need to create a dataset with Common Table Expressions (CTE). For example:

WITH CTE AS
(
SELECT * FROM TABLE WHERE ID= @ID
UNINON ALL
SELECT T1.* FROM TABLE T1, CET T2
WHERE T1.ParentID = T2.id
)
SELECT * FROM CTE

Click here to view code as image

Note: The test is based on the sample databases and projects here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=SqlServerSamples

How do I set interactive sort across multiple groups in SSRS 2005?

Answer

One possible workaround is using drill-through action to create a fake ‘interactive sort’:

1. Copy and paste the table1 to create a table2.

2. Select the table1, click and edit each group, switch to the Sorting tab, select a proper column with Ascending/Descending direction for sorting. Select the table2, click and edit each group, sort the records with a different direction.

3. Click the cell which contains the interactive_sort button in table1, add a new column to the left. Merge the cells in these two columns row by row except the table header. After that remove the interactive sort from the table1 by unchecking the option "Add an interactive sort action to this textbox". Repeat this step for table2.

4. Create a hidden parameter for the report named Sorting. Set the default value for this parameter to be "Ascending".

5. Right click the new added cell in the header of the table1, click properties, and switch to Navigation tab. Select the "Jump to report", choose the current report, set the parameter value of @Sorting to be: "Descending". After that, select table1, click properties, switch to Visibility tab, set the visibility with expression: ‘=iif(Parameters!Sorting.Value="Ascending",false,true)".

6. Right click the new cell in table2, click properties, and switch to Navigation tab. Select the "Jump to report", choose the current report, set the parameter value of @Sorting to be
"Ascending". After that, select table2, click properties, switch to Visibility tab, set the visibility with expression: ‘=iif(Parameters!Sorting.Value=" Descending",false,true)’.

7. You can merge the cells in these two columns except the cells in header. You can also create two pictures for those two cells, one for Descending and one for Ascending. After that, set drill through action on these two pictures.

For matrix, there are some differences.

a) Since the sorting button must be a static cell and you cannot split the matrix header in SSRS 2005, you can only set interactive sort on matrix header (left top corner) in SSRS 2005. But in SSRS 2008, you have multiple choices since it has multiple matrix headers.

b) You cannot sort the detail data in matrix since there is only one detail cell in one column group and one row group for a matrix. For example, look at the below matrix

Matrix Header | A | B
C | 1 | 2
D | 3 | 4

Click here to view table as image

There is only one value 1 for (A, C), so sorting that value is no meaningful. And even if you can sort the values (1 and 3) on the column A, you will find that the sort will also impact on the column B, that means 2 and 4 will be also sorted. So you can only sort on groups or subtotals – (sort X-axis or Y-axis).

For sorting row/column group, right click the matrix header, click properties, and then switch to the Interactive Sort tab, and set the sorting group region and sorting expression. In Reporting Services 2005, if the row/column group is not in the drop down list of ‘evaluate sort expression’, you can type the row/column group name in that textbox.

After that, if you want to set the interactive sort for row group and column group at one time, you can try the above work around (for table) – using drill through action. Create two matrixes, right click the group, click edit group, sort the groups in sort tab of, and then create drill through with parameter.

How do I get distinct values of SharePoint column using SQL Server Reporting Services?

Answer

You can use custom code to get distinct records.

Here are the detailed steps:

1. Create a new dataset same as the main dataset, and then create a hidden parameter that gets all the records in one field of the new dataset.

2. Create a function that used to remove the duplicate records. Here is the code:

Public Shared Function RemoveDups(ByVal items As String) As String
Dim noDups As New System.Collections.ArrayList()
Dim SpStr
SpStr = Split(items ,",")
For i As Integer=0 To Ubound(Spstr)
If Not noDups.Contains(SpStr(i).Trim()) Then
noDups.Add(SpStr(i).Trim())
End If
Next
Dim uniqueItems As String() = New String(noDups.Count-1){}
noDups.CopyTo(uniqueItems)
Return String.Join(",", uniqueItems)
End Function

Click here to view code as image

3. Create another parameter that will be used for filtering the main data.
Please set the available value to be
=Split(Code.RemoveDups(JOIN(Parameters!ISSUE_STATUS_TEMP.Value, ",")),
",")
And the default value to be the value you wanted, such as the first value:
=Split(Code.RemoveDups(JOIN(Parameters!ISSUE_STATUS_TEMP.Value, ",")),
",").(0)

4. Select the main dataset and open the property window of this dataset.

5. In the "Filters" tab, set the filter to be:

Expression: <The field to be filter>
Operator: =
Value: =Parameters!Region.Value

Click here to view code as image

The parameter "Region" should be the parameter we created in the step3. Now, we could get the distinct values of SharePoint columns.

How do I resolve the printer problem "Unable to load client print control"?

Answer

The issue could be caused if the RSClientPrint control has been updated after the Report Server got updated.

The Reporting Services uses the CLSID of the RSClientPrint control and a function in the control to check if the RSClientPrint control has been installed in the client. If the CLSID is not the one the server required, the server will throw the exception "Unable to load client print control".

For example, the old CLSID is "{FA91DF8D-53AB-455D-AB20-F2F023E498D3}". After applying SQL Server 2005 Service Pack2 or later, the CLSID should be "{41861299-EAB2-4DCC-986C-802AE12AC499}". If the client has the control with the ID "{FA91DF8D-53AB-455D-AB20-F2F023E498D3}", and the server has been updated, then we will encounter the error.

I would suggest using the following steps to solve the issue:

1. Install Windows Resource Kits.

2. Use the "oleview.exe" to check if the RSClientPrint 2005 Class (Object Class -- > Grouped by Component Category -- > Controls) has been installed.

3. If the control has been installed in the client, please remember its CLSID that is displayed in the right window of OLEVIEW.

4. Open Internet Explorer

5. Click "Tools" -- > "Manage Add-ons" to manage add-ons.

6. Select "All add-ons", and then select "RSClientPrint 2005 Class"

7. Click "Delete" to delete the control. Or "Disable" to disable the control.

8. Re-run a report, and click Print. Now the RSClientPrint control will work fine.

9. Re-open the OLEVIEW to check the newer CLSID.

If the new CLSID is different from the old one, the issue is the one we have described above.

Reference

Window Resource Kits:
http://www.microsoft.com/downloads/details.aspx?familyid=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en
"Unable to load client print control" after installing a Service Pack or Cumulative Update of SQL Server 2005:
http://blogs.msdn.com/mariae/archive/2008/12/11/unable-to-load-client-print-control-after-install-a-service-pack-or-cumulative-update-of-sql-server-2005.aspx

How do I allow Enter key to act like the "view report" button?

Answer

While clicking the "View Report" button, the report sever uses JavaScript to verify the parameters and submit the action. There, the report manager uses the "Report.aspx" to display the parameter panel, and the report server uses the "ReportViewer.aspx" to display the parameter panel. We can implement a custom function using JavaScript that allows us to view the report by Enter key.

Please follow these steps to allow Enter key to act like "View Report" button:

1. Open the "Report.aspx" using notepad.
By default, the file located at:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ ReportManager\Pages

2. At the end of the file, embed the following code.

<script language = "Javascript">
   function GetViewReportBtn() {
      dueTime += 50;
      if (dueTime > 30000) {
         clearTimeout(timer);
         return;
      }
      var obj = document.getElementsByTagName("INPUT");
      for (var i = 0; i < obj.length; i++) {
         if (obj[i].type == "submit" && obj[i].value == "View Report")
{
             clearTimeout(timer);
             return obj[i];
          }
       }
       timer = setTimeout(GetViewReportBtn, 50);
   }
   var dueTime = 0
   var timer;
   timer = setTimeout(GetViewReportBtn, 50);
   function document.onkeydown() {
      var e = event.srcElement;
      if (event.keyCode == 13) {
         var btnViewReport;
         btnViewReport = GetViewReportBtn();
         if (btnViewReport)
            btnViewReport.click();
      }
   }
</script>

Click here to view code as image

3. Save the file.

4. Open the "ReportViewer.aspx" using notepad.
By default, the file is located at:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\Pages

5. At the end of the file, embed the sample code we have used in step2.

6. Save the file.

Now, you can view the report by keying Enter.

How do I avoid prompting Login dialog while deploying report from Microsoft Business Intelligence Development Studio?

Answer

If you installed Business Intelligence Development Studio on Windows Vista or Windows Server 2008, you will encounter compatibility issues with the Visual Studio environment when you use the design tools. If you want to open an existing project or solution, or publish items from Report Designer or Model Designer to a report server, you must open Business Intelligence Development Studio with elevated permissions:

1. From the Start menu, click All Programs, click SQL Server 2008, right-click Business Intelligence Development Studio, and then click Run as administrator.

2. Click Continue.

3. Click Run Program.

You should now be able to deploy reports and other items to a report server. If you do not have sufficient permissions, you will encounter issues such as the following:

1. Access Denied errors when you try to open an existing project.

2. Login failure errors when you configure a data source connection to a shared data source.

3. When using the Report Server Wizard to create a report, you will get a login failure error when the Wizard attempts to connect to the external data source that provides data to the report.

SQL Server Management Studio users must use Run as administrator permissions to create logins for users who administer databases on the server. For more information about elevated permission requirements for Management Studio, see How to: Connect to SQL Server from Windows Vista.

More information, please see:

How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008

Another possible issue should be Kerberos. If we use a domain account to run the SQL Server Reporting Services, and we have not configured the Kerberos in the domain environment correctly, we will get the login dialog.

To solve the issue, we can configure the Kerberos or change the services account to a local services account.

Please follow these steps to change the account to a local services account:

1. Run command "inetmgr" to open Internet Information Server manager.

2. Select the application pool that the Reporting Services is running under.

3. Right-click the application pool, and then click "Properties"

4. Go to "Identity" tab.

5. Check item "Predefined", and select "Network Service".

6. Click "OK" to apply.

For more information about how to configure Kerberos for SQL Server Reporting Services, please see section 3.2.1 in the following Knowledge Base article: http://support.microsoft.com/kb/958998

How do I resolve the error "No report servers were found on the specified machine"?

Answer

The issue is caused by the namespace of SQL Server Reporting Services 2008 WMI provider is different from the namespace of SQL Server Reporting Services 2005 WMI provider.

By default, the namespace of SQL Server Reporting Services 2005 WMI provider is:
\\ComputerName\root\Microsoft\SqlServer\ReportServer\v9\Admin
The namespace of SQL Server Reporting Services 2008 WMI provider is:
\\ComputerName\root\Microsoft\SqlServer\ReportServer\rs_<instance name>\v10\admin

To solve the issue, please use Reporting Services Configuration Manager 2008 or SQL Server Manager Studio 2008 to connect to the report servers.

How do I get the subtotals of each page and previous pages at the bottom of the report?

Answer

To get the subtotals of each page and previous pages at the bottom of a report whatever specified number of rows grouped and printing target papers to be used, you can follow these steps;

1. Right-click the blank area in the Document window to the left of the report body, and then click Page Footer.

2. For the subtotals of each page, add one TextBox control into the section of the page footer and enter this expression in it:
="SubTotal of Current Page: " +
Sum(ReportItems!theValueField.Value).ToString()

3. For the subtotals of previous pages, insert one additional column for the table and type the expression
=Runningvalue(Fields!ColVal.Value,Sum,"YourTableName") in this column detail cell.

4. Select this additional column and set its Hidden property to true in its Properties Window.

5. Add one TextBox control into the section of the page Footer and enter this expression in it to get the subtotal of the previous pages:
="SubTotal of Prev Pages: "
+Last(ReportItems!theAdditionalColumnDetailCell.Value).ToString()

How do I anonymously access the Report Manager?

Answer

The following steps describe how to anonymously access Report Manager:

1. Add the anonymous user to the System User role.

2. Create an Item-level role with all of the tasks you want this user to be able to use, and then assign the anonymous user to that role at the root level. ( Step 1 and Step 2 could be accomplished by using Report Manager only if Anonymous access is disabled and the logged-on user is a member of the System Administrator role.

3. Check permissions for the anonymous user on folders that Reporting Services use:
- C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services (and subfolders)
- C:\<windows>\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files
-Temp folder for the anonymous user

Note: We don’t suggest using Anonymous access to Report Manager. When using Anonymous access, every person hitting that page will be under the Anonymous user context, and no one will have rights to administer Reporting Services through Report Manager.

How do I deploy a Report Server for Internet Access?

Answer

Some settings in the rswebapplication.config file are used to direct requests from Report Manager to a report server. Report Manager uses the ReportServerUrl settings in the configuration file to find the report server. In an intranet deployment model, the Report Manager connects to a report server using the network name like this (for example,http://server01/reportserver).

However to access a report server on the Internet, you need to configure the ReportServerUrl that specifies the fully qualified domain name of the report server. The external client uses this value to access a report server. In this scenario, the browser that is hosting Report Manager must send a fully qualified domain name when making requests on behalf of Report Manager to the report server. The following example illustrates the syntax for ReportServerURL: <ReportServerURL>http(s)://<server.domain.com>/reportserver</ReportServerU RL>

If you use SharePoint 2.0 Web Parts for Reporting Services, you need to configure this setting in <ReportServerExternalURL> section: <ReportServerExternalURL>http(s)://<server.domain.com>/reportserver</ReportServerExternalURL>

Note: This element is not included in the RSReportServer.config file, to use it, you need to add it yourself.

For more information about configuring internet access for Reporting Services, please see:
For SQL Server Reporting Services 2005:
http://msdn.microsoft.com/en-us/library/ms159272(SQL.90).aspx
For SQL Server Reporting Services 2008:
http://msdn.microsoft.com/en-us/library/ms159272.aspx

How do I resolve the Reporting Server Error 1053: The service did not respond to the start or control request in a timely fashion?

Answer

The problem is usually caused by an incorrect timeout setting. To work around the issue, try the following steps:

1. Click Start, click Run, type regedit, and then click OK.

2. Locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

3. In the right pane, locate the ServicesPipeTimeout entry.Note If the ServicesPipeTimeout entry does not exist, you must create it. To do this, follow these steps:

i. On the Edit menu, point to New, and then click DWORD Value.

ii. Type ServicesPipeTimeout, and then press ENTER.

4. Right-click ServicesPipeTimeout, and then click Modify.

5. Click Decimal, type 60000, and then click OK. This value represents the time in milliseconds before a service times out.

6. Restart the computer.

How do I dynamically load an external image in a report?

Answer

Generally there are two possible reasons for the issue:

1. If you want to use a directory string, and the images are stored in the file folder which is not in the root folder of the Website populate SQL Server Reporting Service, there will be a permissions error. If using IIS6.0 as the web server, the account "Network Service" ("ASPNET" for IIS5.0) must have the permission "Read" in the folder.

2. If you want to use an URL, and the images are stored in a website which is not the same as the populate SQL Server Reporting Service Website. Therefore an execution account error will occur.

To solve the issue, we could use either of the following two solutions below: Use the folder of images "images" as a sample.

Solution1: Using Directory string.

1. Store the images in any folder on the SQL Server Reporting Server.

2. Set the account "Network Service"(or "ASPNET") "Read" permission.

3. Store the Directory string in the "Pointer" field in the table. The Directory string must be full, and with prefixfile://. Do not use quotes. For example: file://D:/images/ XXX.jpg (".png", ".gif", and so on).

4. Design the report, and set the property "Value" of image control as "=Fields! Pointer.Value".

Solution2: Using URL

1. Store the images in the file system in one of the websites on the server.

2. Open Reporting Service Configuration Manager, navigate to "Execution Account".

3. Specify an account.

4. Store the URL in the "Pointer" field in table, and the URL must be full. Do not use quotes. For example: http ://< website >/images/XXX.jpg (".png", ".gif", and so on).

5. Design the report, and set the property "Value" of image control as "=Fields! Pointer.Value".

How do I call the public methods of Reporting Service Web service in XML Datasource to query report server metadata?

Answer

You can access
http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005_members(SQL.90).aspx for more methods of reporting service web service. Take the ListReportHistory Method for an example;

1. Create a data source, select XML type for the data source and enter the following connection string:
http://YourServer/ReportServer/ReportService2005.asmx

2. Create a dataset and reference the above data source and enter the following xml as the query string:

<Query>
<Method Name="ListReportHistory"
Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reportin
g/reportingservices">
<Parameters>
<Parameter Name="Report">
<DefaultValue>/Report1/images</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

Click here to view code as image

Notes: the method is ListReortHistory, and its parameter is Report and the parameter value is /Report1/images. The method and parameter name is case sensitive.

3. If you want to parameterize the "Report" value, you can open the dataset properties dialog window and go to the parameter tab, the parameter name is Report and the parameter value is from the parameter you have created in the report parameter page. DON’T put the report parameter in <DefaultValue>, i.e. <DefaultValue>@YourParameter</DefaultValue>.

4. Then you can reference these fields returned on your report.

How do I get group total in Group Footer with PageBreak in SQL Server Reporting Services?

Answer

I want to get a total of certain group within a group footer of the Tablix, which has another group for breaking the records on certain number of records. For example,There is a table with a amount of data and its columns include "CustomerID", "CustomerType" and "Totaloutstanding". Now, run SSRS 2005 to report this table data. On this report,

1. In the report dataset, these data are ordered by "CustomerID", one CustomerType has one CustomerID, the CustomerID values like 1,2,3......

2. Insert a Table control to host these data,

3. The data on every page of this report has only less than or equal 1000 rows. To achieve this, insert a group for the detail rows with group-on expression =(RowNumber(Nothing)-1)\1000. Assuming this group is Group1.

4. The data on every page of this report need to be grouped by "CustomerType". To achieve this, insert a group under the Group1 for the detail rows with group-on expression =Fields!CustomerType.value, and tick these options "Include group header","Include group footer" and "Repeat group header". Assuming this group is Group2.

5. (?). How to display the subtotal based on CustomerType at the end of each CustomerType series? Note that one CustomerType might be span multiple pages by Group1, and one page(Group1 instance) might have multiple Group2 instance.

There are 2 main steps, one is how to calculate the subtotal based on the "CustomerType", and the other is how to hide the Group2 footer rows if they are not at the end of each CustomerType series.

1. The possible alternatives for how to calculate the subtotal based on the "CustomerType", there are 2 options you can follow:
Use semi hard-code: In the Group2 footer cell, type this expression =Switch(Fields!Customer.Value="Person",SUM(IIF(Fields!Customer.Value="Person",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Company",SUM(IIF(Fields!Customer.Value="Company",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Electronic",SUM(IIF(Fields!Customer.Value="Electronic",Fields!Totaloutstanding.Value,0),"StatisticTbl")) . Note that "StatisticTbl" is the report dataset name, and "Person","Company" and "Electronic" are the "CustomerType" examples.

Use the subreport to do this:Create a new report, dataset:

select CustomerType,sum(Totaloutstanding) as subtotal
from StatisticTbl
where CustomerType= @cust
group by customerType

Click here to view code as image

report parameter:name->cust, datatype->string
report body: drag a table control and only one detail cell via deleting other cells, and put the =Fields!subtotal in this cell. Adjust this report page size to fit on one cell of the main report Group2 footer. On the main report, in the cell displaying the subtotal of "CustomerType" of Group2 footer, insert a Subreport control to reference this newly created report and set this subreport parameter "cust" point to =Fields!CustomerType.value.

2. After implementing any option of the first step, the subtotal based on the "CustomerType" will be displayed in each Group2 footer. Now, how to hide these footer rows not at the end of the "CustomerType" series? Click the handle of the Group2 footer row to select the entire row, and locate its Hidden property under Visibility note in the Properties box, then type the following expression to hide the footers not at the end of each "CustomerType": =IIF(Runningvalue(Fields!CustomerType.Value,countdistinct,"Group1")=1,IIF(Count(Fields!CustomerType.Value,"Group2")=1000,true,false),false)

How do I create cascading parameters when using cube database in Reporting Services?

Answer

If the dataset MDX query is generated by dropping and dragging attributes and measures in Design Mode of the Query Designer, then you can define the cascading parameters in the Filter pane. In the pane, select a dimension and its hierarchy and tick the parameter checkbox to create the first parameter, on the next filter row, select another dimension and tick the Parameter checkbox to produce the second parameter, then, the 2 parameters will be cascaded automatically.

If the dataset MDX query is written manually, in the dataset Query Designer,

1. Click the Parameter button to open Query Parameter box. In the box, define your first parameter following the instruction and used in the dataset query.

2. Click ok Buttons 2 times to close the report dataset properties.

3. In the Report Data pane, right click Datasets folder and select "Show Hidden Datasets".

4. Then the first parameter dataset will show under the Datasets folder.

5. Double click the parameter dataset to open its Query Designer.

6. Click the parameter button to open the Query Parameters box, and define the second parameter as the instruction. Click ok to quit the box.

7. Modify the first states parameter query like this:

WITH MEMBER [Measures].[ParameterCaption] AS
[Geography].[State-Province].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS
[Geography].[State-Province].CURRENTMEMBER.UNIQUENAME MEMBER
[Measures].[ParameterLevel] AS
[Geography].[State-Province].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON
COLUMNS , [Geography].[State-Province].ALLMEMBERS ON ROWS
FROM (select StrToSet(@Countries,Constrained) on columns from
[Adventure Works])

Click here to view code as image

8. Click ok 2 times to close the dataset properties.

Then, the states and counties parameters are cascaded.

Why does the "Attempted to divide by zero" error still happen?

Answer

I have 2 decimal data fields (Field1 and Field2) in a dataset, and use the expression with IIf function below to calculate the quotient. If the Field1 is not zero, the divide expression within the true part will be executed. Otherwise, just 0 will be returned.
=IIf(Fields!Field1.Value<>0, Fields!Field2.Value/Fields!Field1.Value, 0)
However, why I am still getting "#Error" when Field1 is zero?
The error message is as follows:
[rsRuntimeErrorInExpression] The Value expression for the textrun
'Textbox6.Paragraphs[0].TextRuns[0].TextRuns[0]' contains an error: Attempted to divide by zero

The cause of this error is that the IIf function always evaluates both the true part and the false part, even though it returns only one of them. This means both the true part and the false part need to be evaluated.

In order to resolve this issue, you need to use a nested IIf function to avoid the zero-divisor in any rate.
=IIf(Fields!Field1.Value=0, 0, Fields!Field 2.Value/IIf(Fields! Field 1.Value=0, 1, Fields! Field 1.Value))

After that, you will get the correct result whether Field1 is 0 or not.

Workaround

You can also use custom code to prevent errors caused by dividing by zero. Select click Report Properties on the Report menu. Click the Code tab and enter the following code in the Custom Code box:

Function Divide(Numerator as Decimal, Denominator as Decimal)
If Denominator = 0 Then
Return 0
Else
Return Numerator/Denominator
End If
End Function

Click here to view code as image

After that, set the expression to be:
=Code.Divide(Fields!Field2.Value, Fields!Field1.value)

Reference

IIf Function:
http://msdn.microsoft.com/en-us/library/27ydhh0d(v=VS.90).aspx

Applies to

Reporting Services 2005
Reporting Services 2008
Reporting Services 2008 R2

How do I solve the issue - The configuration file contains an element that is not valid?

Answer

The issue is probably caused by the rsreportserver.config configuration file unexpectedly modified or some settings misconfigured. In this case the detailed error message will indicate which element cause the problem. You can check the detailed error message in the SSRS execution log. For example, there is the error below:

appdomainmanager!DefaultDomain!1004!<DateTime>:: i INFO: Entered managed
ServiceMain in DefaultDomain.

configmanager!DefaultDomain!734!< DateTime>:: e ERROR: Error loading
configuration file: The configuration file contains an element that is not
valid. The Configuration element is not a configuration file element.

library!DefaultDomain!734!< DateTime>:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErro
rException: ,
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErro
rException: The report server has encountered a configuration error. --->
System.Exception: The configuration file contains an element that is not valid.
The Configuration element is not a configuration file element.

at
Microsoft.ReportingServices.Diagnostics.ExtensionsConfiguration.ParseExte
nsionElement(XmlNode child, Extension& extension, String childName)

at
Microsoft.ReportingServices.Diagnostics.ExtensionsConfiguration.ParseRend
erExtensions(XmlNode child, ExtensionArray array)

at
Microsoft.ReportingServices.Diagnostics.ExtensionsConfiguration.ParseExte
nsions(XmlNode child, ExtensionArray array)

at
Microsoft.ReportingServices.Diagnostics.ExtensionsConfiguration.ParseXML(
XmlNode node)

at
Microsoft.ReportingServices.Diagnostics.RSConfigurationFileManager.ParseD
ocument(XmlDocument xmlConfiguration)

at
Microsoft.ReportingServices.Diagnostics.RSConfigurationFileManager.LoadDo
cument()

at
Microsoft.ReportingServices.Diagnostics.RSConfigurationFileManager.LoadCo
nfiguration()

Click here to view code as image

From the error message, there is a <Configuration> element that is invalid.

In order to solve the issue, please follow these steps:

1. Open the rsreportserver.config file with text editor

2. Search for each <Configuration> setting

3. Correct the element if it is invalid.

Or, you can replace each <Configuration> element with the same setting from another SSRS(which is running file) rsreportserver.config file.

Applies to

SQL Server Reporting Services 2005
SQL Server Reporting Services 2008
SQL Server Reporting Services 2008 R2

How do I pass a multi-value parameter within a URL?

Answer

For multi-value parameter, it is simple that you can use the Join function. For example:

="http://servername/reportserver?/ReportFolder/ReportName&rs:Command=R ender&ParameterName=" & JOIN(Parameters!ParameterName.Value, "&ParameterName=")

Applies to

SQL Server Reporting Service2005
SQL Server Reporting Service2008
SQL Server Reporting Service2008 R2

How do I keep only one vertical scroll bar in local report?

Answer

I have a local report with a long height; The problem is that there are two vertical scroll bars on right hand like below. Specially, the scroll wheel does not work on the inner scroll bar. Is there a work around for this?

You can achieve this requirement in local report. Just type in below code in code-behind:

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
.........//other code
this.ReportViewer1.AsyncRendering = false;
this.ReportViewer1.SizeToReportContent = true;
this.ReportViewer1.ZoomMode = ZoomMode.FullPage;
.........//other code
}
}

Click here to view code as image

Then you will get the report like below, you can see that there is only one vertical scroll bar now, and it will never occur the scenario that the scroll wheel does not work.

Applies to

Sql Server Reporting Service2005
Sql Server Reporting Service2008
Sql Server Reporting Service2008 R2

How do I Hide Export Options with Local Report?

Answer

Currently, this is a product limitation for customizing the export option directly of the ReportViewer control.

However, as a workaround, we can use JavaScript to remove the Word and Excel export options. Please refer to the steps below.

For ReportViewer 2008:

1. Preview the web page which contains the ReportViewer control.

2. Check the source code of page. Find the id of the <select> node which contains the export options. e.g. It is " ReportViewer1_ctl01_ctl05_ctl00" in my page.

3. In the ASPX file, add the JavaScript below:

<script type="text/javascript">
   window.onload = function () {
      var formatDropDown =
document.getElementById('ReportViewer1_ctl01_ctl05_ctl00');
       if (formatDropDown != null) {
          formatDropDown.remove(1);
      }
   }
</script>

Click here to view code as image

For ReportViewer 2010:

1. Preview the web page which contains the ReportViewer control.

2. Check the source code of page. Find the id of the <div> node which contains the export options.
e.g. It is "ReportViewer1_ctl06_ctl04_ctl00_Menu" in my page.

3. In the ASPX file, add the JavaScript below:

<script type="text/javascript">
   window.onload = function () {
      var formatDropDown =
document.getElementById('ReportViewer1_ctl06_ctl04_ctl00_Menu');
       var formats = formatDropDown.childNodes;
       if (formatDropDown != null) {
          formatDropDown.removeChild(formats[0]);
          formatDropDown.removeChild(formats[1]);
      }
   }
</script>

Click here to view code as image

Please correct the element id based on your web page. As a result, only the PDF export option still exists.

How do I get the same page number in web page and PDF?

Answer

Reporting Services is designed to use 2 different rules in pagination:

1. Physical pagination is used to the print control and the TIFF and PDF rendering extensions. It is controlled by the PageSize properties

2. Logical pagination is used to the HTML and GDI rendering extensions. It is controlled by the InteractiveSize properties

However, if just specify the same size to PageSize and InteractiveSize, we will get more pages in PDF or other physical formats due to the Margin properties of Report are only applied in the physical pagination.

For example, if the InteractiveSize and the PageSize are both set to 8.5in x 11in and the Margins are 1in, then the actual size of printing area in physical page is 6.5(8.5-1-1)in x 9(11-1-1)in. This leads to more pages are generated when render in physical pagination.

In order to make the page numbers matches, we need to ensure the acutrul size of printing area and the interactive size are the same. Please refer to the equalities below:

1. Width(InteractiveSize) + Margin(Left) + Margin(Right) = Width(PageSize)

2. Height(InteractiveSize) + Margin(Top) + Margin(Bottom) = Height(PageSize)

Since sometimes the PageSize is specified corresponding to a specific paper size like A4 for printing, we suggest you to keep the PageSize to 8.5in x 11in and adjust the InteractiveSize and Margins based on the equalities.

Reference

Logical and Physical Pagination Rules:
http://blogs.msdn.com/b/bwelcker/archive/2005/08/19/454043.aspx

Applies to

Reporting Services 2005
Reporting Services 2008
Reporting Services 2008R2

How do I implement OR logic or complicated logics for filters in a SSRS report?

Answer

When using filters in a report, you will find the limitation of the condition "and/or" in the filter pane as the following picture showed, you cannot change the condition "and" to "or" or any other logical operations:

(Reporting Services 2005)

(Reporting Services 2008&R2)

To resolve this issue, for example, if you want to create a filter as the following form:
(Year = '2003') Or (Category = 'Bikes')

You can create a filter with an expression as below:

Expression:

=(Fields!Year.Value = "2003") or (Fields!Category.Value = "Bikes")

Operator:

=

Value:

= TRUE

Applies to

SQL Server Reporting Services 2005
SQL Server Reporting Services 2008
SQL Server Reporting Services 2008 R2

How do I work Parameters with a local report?

Answer

Currently this is a product limitation for reportviewer displaying local report. You have to create the parameter prompts on your web page and submit the parameters with ReportViewer.LocalReport.SetParameters as follows:

ReportParameter p = new ReportParameter();
p.Name = "EmployeeID";
p.Values.Add(this.drpEmpList.SelectedValue);
this.ReportViewer1.LocalReport.SetParameters(p);
this.ReportViewer1.LocalReport.Refresh();

Click here to view code as image

Applies to

Visual Studio 2008
Visual Studio 2010

How do I pass a multi-value parameter into a report with SQL Server Reporting Services (SSRS) Web Services API?

Answer

In Microsoft SQL Server Reporting Services (SSRS), in order to pass a multi-value parameter through the Reporting Services Web services, you need to define the same numbers of ParameterValue objects as the number of the values of the multi-value parameter being past into the report. The Name property of these ParameterValue objects must be specified same to the parameter name.

For example, you have a multi-value parameter named "ProductSubcategory" in a report, and now you want to pass the values 1, 2 and 3 to the parameter, you need to create a ParameterValue array which length is 3 and specify each one with the same Name property value.

Below is a complete sample for your reference:

SRReportExecution2005.ReportExecutionServiceSoapClient rs = new
SRReportExecution2005.ReportExecutionServiceSoapClient();
rs.ClientCredentials.Windows.AllowedImpersonationLevel =
System.Security.Principal.TokenImpersonationLevel.Impersonation;
// Render arguments
byte[] result = null;
string reportPath = "/AdventureWorks 2008 Sample Reports/Product Line
Sales 2008";
string format = "MHTML";
string historyID = null;
string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3];
parameters[0].Name = "ProductCategory";
parameters[0].Value = "1";
parameters[1].Name = "ProductSubcategory";
parameters[1].Value = "2"; // June
parameters[2].Name = "ProductSubcategory";
parameters[2].Value = "3";
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
ExecutionInfo2 execInfo2 = new ExecutionInfo2();
ExecutionHeader execHeader = new ExecutionHeader();
ServerInfoHeader svrinfoHeader;
execHeader = rs.LoadReport2(null, reportPath, historyID, out
svrinfoHeader, out execInfo2);
rs.SetExecutionParameters2(execHeader, null, parameters, "en-us", out
execInfo2);
String SessionId = execInfo2.ExecutionID.ToString();
try
{
rs.Render(execHeader, null, format, devInfo, out result, out extension,
out mimeType, out encoding, out warnings, out streamIDs);
}
catch (Exception exp)
{
//Throw the exception.
throw (exp);
}

Click here to view code as image

Reference

ReportExecution2005 Namespace:
http://msdn.microsoft.com/en-us/library/reportexecution2005.aspx

Applies to

SQL Server Reporting Services 2005
SQL Server Reporting Services 2008
SQL Server Reporting Services 2008 R2

How do I pass a multi-value parameter to a dataset while using LIKE operator in the WHERE clause?

Answer

1. Run the following code in your database to create a table valued function which will be used to split a string into a table.

CREATE FUNCTION [dbo].[fn_String_To_Table](
@String VARCHAR(max),
@Delimeter char(1),
@TrimSpace bit )
RETURNS @Table TABLE ( [Val] VARCHAR(4000) )
AS
BEGIN
DECLARE @Val VARCHAR(4000)
WHILE LEN(@String) > 0
BEGIN
SET @Val = LEFT(@String,
ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),
LEN(@String)))
SET @String = SUBSTRING(@String,
ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),
LEN(@String)) + 1, LEN(@String))
IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))
INSERT INTO @Table( [Val] )
VALUES ( @Val )
END
RETURN
END

Click here to view code as image

2. In your repot, define a single valued and internal parameter called MPjoin, then must set its default value using the expression
=Join(Parameters!rptParameter.Value,",").

3. Change your dataset query string like this:

SELECT T1.Col1,T1.Col2,T1.Title
FROM myTable T1 INNER JOIN fn_String_To_Table(@MPjoin,',',1) T2
ON T1.Title LIKE T2.Val+ '%'

Click here to view code as image

4. Open the Dataset Properties, in the windows box, click Parameters to go to the dataset Parameters page and make sure the @MPjoin value is from @MPJoin or the expression =Join(Parameters!rptParameter.Value,",").

How do I preview a Subreport which is in another SSRS project?

Answer

To preview the subreport, you have to first deploy your report project which contains the subreport to your report server. If you choose "Go to Report" for the subreport, you also need to deploy your main SSRS project to the report server and then specify the relative path. In this scenario, the subreport's name will not be displayed in the drop-down list of Select a report from the list when you design the main report. You have to manually type in the relative subreport path in the drop-down list. For example, your two SSRS projects are deployed to the report server as following structure:

Then you can configure the subreport settings as follows:

1. On your main report, right-click the textbox you want to add an action, and then select Text box Properties...button.

2. Switch to the Action tab, type in the relative report path in the drop-down list of Select a report from the list, such as ../DemoTest2/Product Detail:

3. If the subreport has parameters, you can click the Add button to add parameters into the parameter grid.

4. You need to hard code the Parameter Name in the Name drop-down list, as these parameters are not contained in the name drop-down list.

5. In the Value drop-down list, select a value to pass to the subreport.

6. Repeat steps 3-5 to specify a value for each subreport parameter.

7. Click OK.

8. Deploy the report project and preview it.

This method applies to Sql Server Reporting Service2005, Sql Server Reporting Service2008 and Sql Server Reporting Service2008 R2.

Walkthrough: How to list models from SQL Server Master Data Services in a SQL Server Reporting Services (SSRS)

Answer

SQL Server Master Data Services (MDS) is a new feature in SQL Server 2008 R2. It is the source of master data for an organization. To learn more information about it, you can read "Master Data Services". This article walks through the steps of how to access the models of SQL MDS in a SSRS report. The points are exposing the MDS web services and using XML data source type to access the data from the web services.

Expose the MDS WCF services to using BasicHttpBinding

1. In order to consume MDS web services, you need to enable web services for MDS first. For detailed steps, please refer to How to: Enable Web Services (Master Data Services).

2. Open the web.config configuration file for the MDS web site with text editor. By default, the file is host in C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication

3. Add the BasicHttpBinding configurations to the bindings section of the web.config file as follows:

<bindings>
<wsHttpBinding>
<binding name="mdsWsHttpBinding" maxBufferPoolSize="2147483647"
maxReceivedMessageSize="2147483647">
<readerQuotas maxArrayLength="2147483647"
maxBytesPerRead="2147483647" maxDepth="2147483647"
maxNameTableCharCount="2147483647"
maxStringContentLength="2147483647"/>
<!-- Non-SSL implementations.-->
<security mode="Message">
<message clientCredentialType="Windows"/>
</security>
<!-- SSL implementations -->
<!--<security mode="Transport">-->
<!--<message clientCredentialType="Windows" />-->
<!--</security>-->
</binding>
</wsHttpBinding>
<basicHttpBinding>
<binding name="mdsBasicHttpBinding" maxBufferPoolSize="2147483647"
maxReceivedMessageSize="2147483647">
<readerQuotas maxArrayLength="2147483647"
maxBytesPerRead="2147483647" maxDepth="2147483647"
maxNameTableCharCount="2147483647"
maxStringContentLength="2147483647"/>
<!-- Non-SSL implementations.-->
<!--<security mode="Message">
<message clientCredentialType="Windows"/>
</security>-->
<!-- SSL implementations -->
<!--<security mode="Transport">-->
<!--<message clientCredentialType="Windows" />-->
<!--</security>-->
</binding>
</basicHttpBinding>
</bindings>

Click here to view code as image

4. Configure the service to use BasicHttpBinding

<service behaviorConfiguration="mdsWsHttpBehavior"
name="Microsoft.MasterDataServices.Services.Service">
<!--<endpoint binding="wsHttpBinding"
bindingConfiguration="mdsWsHttpBinding"
bindingNamespace="http://schemas.microsoft.com/sqlserver/masterda
taservices/2009/09"
contract="Microsoft.MasterDataServices.Services.ServiceContracts.
IService" /> -->
<endpoint binding="basicHttpBinding"
bindingConfiguration="mdsBasicHttpBinding"
bindingNamespace="http://schemas.microsoft.com/sqlserver/masterda
taservices/2009/09"
contract="Microsoft.MasterDataServices.Services.ServiceContracts.
IService" />
</service>

Click here to view code as image

Create a Shared Data Source or embedded Data Source that is using XML data source type

Create a Shared Data Source

1. In the Business Intelligence Development Studio (BIDS), in the Solution Explorer, right-click Shared Data Sources.

2. Then click Add New Data Source.

3. In the Shared Data Source Properties dialog, type in a name for the data source, select type XML

4. Set the connecting string to be the URL address of the MDS WCF service. E.g.http://servername:10001/Service/Service.svc

5. Go to Credentials tab, check Use Windows Authentication(Integrated Security)

Create an Embedded Data Source

1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Data Sources.

2. Then click Add Data Source.

3. In the Shared Data Source Properties dialog, type a name for the data source, select type XML

4. Set the connecting string to be the URL address of the MDS WCF service. E.g. http://servername:10001/Service/Service.svc

5. Go to Credentials tab, check Use Windows Authentication(Integrated Security)

List all models in a SSRS report

1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Datasets.

2. Then click Add Dataset….

3. In the Dataset Properties dialog, in the Query tab, type a name for the dataset

4. Choose the data source created before for this dataset.

5. In the Query textbox, type the following query:

<Query>
<Method
Namespace="http://schemas.microsoft.com/sqlserver/masterdataservic
es/2009/09" Name="MetadataGetRequest">
<Parameters>
<Parameter Name="ResultOptions" Type="XML">
<DefaultValue>
<Models>Details</Models>
</DefaultValue>
</Parameter>
<Parameter Name="SearchCriteria" Type="XML">
<DefaultValue>
</DefaultValue>
</Parameter>
</Parameters>
</Method>
<SoapAction>http://schemas.microsoft.com/sqlserver/masterdataservi
ces/2009/09/IService/MetadataGet</SoapAction>
<ElementPath
IgnoreNamespaces="True">MetadataGetResponse{}/Metadata{}/Models{}/
Model{}/Identifier{Id, Name, InternalId}</ElementPath>
</Query>

Click here to view code as image

Pass user defined search criteria to the web service call

1. In the Business Intelligence Development Studio (BIDS), in the Report Data, double-click the dataset for the web service call.

2. In the Dataset Properties dialog, go to Parameters tab

3. Click Add to add the first parameter.
Note:

• The number of the parameters must be same with the number of the parameters in the web service call.

• The sequence of the parameters must be same with the sequence in the web service call.

• The name of each parameter must be same with the parameter in the web service call.

4. Set the parameter name to be ResultOptions, and the value to be:

<Models>Details</Models>

Click here to view code as image

5. Click Add again to and another parameter SearchCriteria.

6. Set the value of the parameter to be:

="<Models><Identifier><Name>" & Parameters!modelName.Value &
"</Name></Identifier></Models>"

Click here to view code as image

modelName is a report parameter.

More Information

a. The ResultOptions parameter is a MetadataResultOptions object.

b. The SearchCriteria parameter is a MetadataSearchCriteria object.

c. In order to construct the SOAP message, you can follow these steps:

1. Create a .NET application to invoke the MDS WCF services.

2. Call the MetaDataGet method in the application.

3. Debug the application.

4. Use a tool named Fiddler to get the constructed SOAP message.

d. Another samples for ResultOptions:

<Parameter Name="ResultOptions" Type="XML">
<DefaultValue>
<AttributeGroups>Details</AttributeGroups>
<Attributes>Details</Attributes>
<DerivedHierarchies>Details</DerivedHierarchies>
<DerivedHierarchyLevels>Details</DerivedHierarchyLevels>
<Entities>Details</Entities>
<ExplicitHierarchies>Details</ExplicitHierarchies>
<MemberTypes>Details</MemberTypes>
<Models>Details</Models>
<VersionFlags>Details</VersionFlags>
<Versions>Details</Versions>
</DefaultValue>
</Parameter>

Click here to view code as image

e. Another sample for SearchCriteria:

<Parameter Name="SearchCriteria" Type="XML">
<DefaultValue>
<Models><Identifier><Name>modelName</Name></Identifier></Models>><Ent
ities><Identifier><Name>entityName</Name></Identifier></Entities>
</DefaultValue>
</Parameter>

Click here to view code as image

Reference

XML Connection Type (SSRS)

http://msdn.microsoft.com/en-us/library/dd220468.aspx

Element Path Syntax for Specifying XML Report Data (SSRS)

http://msdn.microsoft.com/en-us/library/ms365158.aspx

ServiceClient.MetadataGet Method

http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.serviceclient.metadataget.aspx

MetadataResultOptions Class

http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.metadataresultoptions.aspx

MetadataSearchCriteria Class

http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.services.datacontracts.metadatasearchcriteria.aspx

[Download sample]

Walkthrough: Control Dataset Query Dynamically based on SSRS Report Parameters

Answer

Generally, using a parameterized query can filter data for a report dataset at the source. When include query parameters in a query, Reporting Services automatically creates report parameters that are connected to the query parameters. This enables users to select the data based on a particular criterion, such as values in a data field need to be greater than a specific value.

However, sometimes the criterion of the filter also depends on parameters dynamically. For example, users can choose the comparison operator like > (greater than) or < (less than) as report parameters to build the filter. This article will walk you through the processes of using a dynamic query based on parameters in a report.

The sample in this article uses the sample AdventureWorksDW2008 database. The report displays the Product list containing Product Name, Standard Cost, Dealer Price and List Price columns.

Add Data Source and Dataset

1. Create a new Report Server project and then add a blank report.

2. Add a new Data Source DataSource1 to connect the AdventureWorksDW2008 database with the connection string like
Data Source=.; Initial Catalog=AdventureWorksDW2008

3. Add a new Dataset Dataset1. Specify the Data source to DataSource1 and type in the following query

SELECT EnglishProductName, ListPrice, StandardCost, DealerPrice
   FROM DimProduct

Click here to view code as image

Add a Table

1. Add a table to the report body.

2. Add each data field in Dataset1 to the detail row of the table. It looks like

Add Parameters

1. Add a parameter FieldName with the Available Values below

Label | Value
StandardCost | StandardCost
DealerPrice | DealerPrice
ListPrice | ListPrice

Click here to view table as image

2. Add a parameter Operator with the Available Values below

Label | Value
> | >
>= | >=
="=" | ="="
<= | <=
< | <

Click here to view table as image

Please be aware that we need to specify an expression for the equal to item rather than only use the equal sign.

3. Add a parameter Value and set its Data type to Float.

Modify the Dataset Query

1. Right click DataSet1 in the Report Data pane and select Dataset Properties.

2. In the Dataset Properties window, click the fx button beside the Query text area.

3. Replace the query text to the expression below

="SELECT EnglishProductName, ListPrice, StandardCost, DealerPrice FROM DimProduct WHERE "& Parameters!FieldName.Value & Parameters!Operation.Value & CStr(Parameters!Value.Value)

Preview the report. Then, the Field Name, Operator and Value can be specified as parameters to filter the query data dynamically.

By using expression, the dataset query can be built dynamically based on various requirements.

[Download sample]

Walkthrough: How to change XML data for embedded XML document in a SQL Server Reporting Services (SSRS) report

Answer

In Microsoft SQL Server Reporting Services (SSRS), you can use XML data source type to connect to and retrieve data from XML documents, Web services, or embedded XML snippets in queries. For embedded XML snippets, people often ask how to change the XML attributes or elements values dynamically under a specified XML schema. This article will show you the steps of the resolution.

Note: This walkthrough is only available in SSRS 2008 or 2008 R2. In SSRS 2005, you may encounter the error :

An error occurred while setting the Command Text property of the data extension command.
The XmlDP query is invalid. Syntax error at line <line number>, character <number> of the ElementPath

However you can ignore the error, and continue to design and preview the report.

Create a Shared Data Source or embedded Data Source that is using XML data source type.

Shared Data Source:

1. In the Business Intelligence Development Studio (BIDS), in the Solution Explorer, right-click Shared Data Sources.

2. Then click Add New Data Source.

3. In the Shared Data Source Properties dialog, type a name for the data source, select type XML, and leave the connection string blank.

Embedded Data Source:

1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Data Sources.

2. Then click Add Data Source.

3. In the Shared Data Source Properties dialog, type a name for the data source, select type XML, and leave the connection string blank.

4. Go to Credentials tab, check Use Windows Authentication(Integrated Security)

Create a report parameter in the report.

1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Parameters.

2. Then click Add Parameter….

3. In the Report Parameter Properties dialog, type a name and a prompt for the parameter(e.g. XMLData)

4. Go to Default Values

5. Check Specify Values, and then click Add to add a value. The value is the XML document.

Change a dataset in the report.

1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Datasets.

2. Then click Add Dataset….

3. In the Dataset Properties dialog, in the Query tab, type a name for the dataset

4. Choose the data source created before for this dataset.

5. In the Query textbox, type in the following query: ="<Query><XmlData>" &Parameters!XMLData.Value &"</XmlData></Query>"

6. Go to Fields tab

7. Add Query Field for this dataset. Please note, you need to know the element path before using it in the step. You can get all the fields first using full embedded query directly.

Now, you can change the XML data from the parameter to change the XML data for the report.

Reference

XML Connection Type (SSRS)
http://msdn.microsoft.com/en-us/library/dd220468.aspx

[download sample]

Walkthrough: How to get distinct values of a column of a SharePoint list using SQL Server Reporting Services

Answer

You can connect to a Windows SharePoint Services Lists Web Services, and use GetListItems method retrieve data from a SharePoint list by using the XML data source type in SSRS. However, the GetListItems returns records for a column of a SharePoint list may include duplicate records.

Unfortunately there is no direct way to remove the duplicate records using the Windows SharePoint Services Lists Web Services.This article will show you how to retrieve a column of a Sharepoint list and how to use Custom Code in SSRS to remove the duplicate records.

Create a Report Server Project in Business Intelligence Development Studio

Open Business Intelligence Development Studio (BIDS) and create a new Report Server Project.

Add a report to the project

1. Right click the Report folder, select Add, click New Item....

2. Select Report template, input a report name "Remove Duplicate Records.rdl"….

Create a XML data source

1. From the View menu, select Report Data.

2. In the Report Data panel, right-click Data Sources, and then click Add Data Sources….

3. In the Data Source Properties dialog, configure the connection:

a. Type a name for the data source in the Name textbox

b. Check Embedded connection

c. Select type XML

d. Type the connection string in the Connection string textbox

The connection string is the URL of the Windows SharePoint Services Lists Web Services. For example:
http://sharepointasia/sites/IRSIS/_vti_bin/lists.asmx

4. In the Data Source Properties dialog, configure to the Credentials tab, check the item Use Windows Authentication(integrated security)

Create a Dataset based on the XML data source type

1. In the Report Data panel, right-click Datasets, and then click Add Dataset….

2. In the Dataset Properties dialog, type the name for the Dataset.

3. Select the data source we created in the last step in the Data source dropdown list.

4. Select Text for Query type.

5. In the query textbox, type the following statement:

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListIt
ems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/"
Name="GetListItems">
<Parameters>
<Parameter Name="listName">
<DefaultValue>{5D7A343B-CFE2-4B53-8C51-EEEB87591A12}</DefaultValue
>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

Click here to view code as image

Note: You need to replace the value for listName parameter with actual value in your environment.

Add a dummy parameter to the report

1. In the Report Data panel, right-click Parameters, and then click Add Parameters….

2. In the Report Parameter Properties dialog, type a name for the parameter.

3. Check the item Hidden and Allow multiple values

4. Go to Available Values tab, set the parameter to get values from the Dataset we set before.

5. Go the Default Values tab, set the parameter to get values from the Dataset we set before.

Embed code to the report

The code is used to remove duplicate records from the dummy parameter, and then return a unique array.

1. Click the report body.

2. Click the Report menu, and select Report Properties…

3. Go to Code tab.

4. Embed the following code:

Public Shared Function RemoveDuplicates(parameter As Parameter) As
String()
 Dim items As Object() = parameter.Value
 System.Array.Sort(items)
 Dim k As Integer = 0
 For i As Integer = 0 To items.Length - 1
    If i > 0 AndAlso items(i).Equals(items(i - 1)) Then
        Continue For
    End If
    items(k) = items(i)
    k += 1
 Next
 Dim unique As [String]() = New [String](k - 1) {}
 System.Array.Copy(items, 0, unique, 0, k)
 Return unique
End Function

Click here to view code as image

Create another parameter

This parameter is used for user interaction. This parameter get the distinct value from the custom code function RemoveDuplicates.

1. In the Report Data panel, right-click Parameters, and then click Add Parameters….

2. In the Report Parameter Properties dialog, type a name for the parameter.

3. Go to Available Values tab, check item Specify values

4. Click Add to add a value, type the following expression in both Label and Value textbox.
=Code.RemoveDuplicates(Parameters!DummyParameter)
Note: the DummyParameter is name of the dummy parameter.

5. Go to Default Values tab, check item Specify values

6. Click Add to add a value, type the following expression in the Value textbox. If Allow multiple values is checked, please use the following expression
=Code.RemoveDuplicates(Parameters!DummyParameter) Otherwise, you can use:
=Code.RemoveDuplicates(Parameters!DummyParameter)(0)

Note: the DummyParameter is name of the dummy parameter.

Additionally, if you use cascading parameter, and the child parameter includes duplicate records, please use the custom code function to remove the duplicate records.

Reference

Custom Code and Assembly References in Expressions in Report Designer (SSRS)
http://msdn.microsoft.com/en-us/library/ms159238.aspx
XML Connection Type (SSRS)
http://msdn.microsoft.com/en-us/library/dd220468.aspx
Lists.GetListItems Method
http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

[download sample]

Walkthrough: Assign Dataset Dynamically Created in Code to your local report with ReportViewer

Answer

The Walkthrough: Creating a ReportViewer Reportshows how to create a simple table report in a Visual Studio Windows Forms application project based on a predefined dataset and data table in the project. However, sometimes the dataset need to be displayed is not the one you predefined, so the dataset structure cannot be retrieved when you design the report. This article will walk you through the processes of designing a local report and binding it to a dynamically created dataset in code.

The sample in this article uses the sample AdventureWorksDW2008R2 database. The report displays the Product Category list containing both the category key and category name columns.

Create a new Windows Forms application project

1. Open Visual Studio. On the File menu, point to New, and then select Project.

2. In the Installed Templates pane, select Visual C#.

3. Choose Windows Forms Application.

4. In the Name box, type SimpleReport.

5. In the Location box, enter the directory in which you want to save your project, or click Browse to navigate to it.

6. Click OK.

Define a dataset for report designing

1. In the Project menu, select Add New Item.

2. In the Add New Item dialog, click DataSet. Type a name for the dataset and click Add. The default name is DataSet1.xsd. This adds a new XSD file to the project and opens the Dataset Designer.

3. From the Toolbox in the Dataset Designer, drag a DataTable control onto the design surface.

4. Right click the DataTable and select Add -> Column.

5. Type in ProductCategoryKey as the Data Column name.

6. Add another column set the name to EnglishProductCategoryName

7. Save the DataSet1 file.

Add a new report definition file using the Report Wizard

1. From the Project menu, select AddNew Item.

2. In the Add New Item dialog box, choose Report Wizard.

3. In Name, type Product Category.rdlc and then click Add. A graphical design surface opens behind the dialog box.

4. In the Dataset Properties page, in the Data source drop-down list, select DataSet1 you created. The Available datasets box is automatically updated with DataTable1 you created.

5. Click Next.

6. In the Arrange Fields page, drag both Category and Sales from Available fields to the Values box.

7. Click Next twice, then click Finish.

This creates the .rdlc file and opens it in Report Designer. The tablix you designed is now displayed in the design surface like this:

Product Category Key | English Product Category Name
[ProductCategoryKey] | [EnglishProductCategoryName]

Click here to view table as image

Add the ReportViewer control to your form

1. Click Form1.cs in Solution Explorer.

2. From the View menu, choose Designer.

3. From the Reporting section of the Toolbox, drag the ReportViewer control to the form.

4. Open the smart tags panel of the ReportViewer1 control by clicking the smart-tag glyph on the top right corner. Click the Choose Report drop-down list and select SimpleReport.Sales Orders.rdlc.
Notice that a BindingSource is automatically created corresponding to which point to DataSet1 as DataSource.

5. From the smart tags panel, click Dock in parent container.

Specify the dataset in code to the ReportViewer

1. Click Form1.cs in Solution Explorer and select View Code.

2. Add the following code to the Form_Load event. Since we need access SQL Server Database, please also add using System.Data.SqlClient in the namespace.

private void Form1_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=.; Initial
Catalog=AdventureWorksDW2008R2; Integrated Security=True");
SqlDataAdapter    da     =    new     SqlDataAdapter("SELECT
ProductCategoryKey,      EnglishProductCategoryName     FROM
DimProductCategory", con);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].TableName = "DataTable1";
this.DataTable1BindingSource.DataSource = ds;
this.reportViewer1.RefreshReport();
}

Click here to view code as image

In the code, we change the BindingSource to point the dataset created in the code.

3. Build the project and run it. Then we can get the result like this:

Besides dataset, you can also bind the datasource with a Linq result set but just remember to keep the same data scheme as that defined in your report.

Reference

Samples and Workthroughs
[download sample]

Walkthrough: Restrict the users to see only their folders via Reporting Services web services

Answer

In reporting Services security management, we often want the users to see only their folders. If there are 3 or 10 folders under report manager, it is fine thought the steps described at http://msdn.microsoft.com/en-us/library/aa337491.aspx. However, the operations mentioned in the above link make the current folder inherit security settings from its parent, it really painful job because we need to delete some users from each folder. This article introduces how to grant an user proper permissions under a specified folder and the user only can see his folders.

Create a visual basic project

1. Createa new console application in visual studio using Visual Basic language.

2. In Solution Explorer, right-click the Project folder, and then click Add Web Reference.

3. In the URL Textbox, type
http://ServerName/ReportServer/ReportService2005.asmx?wsdl, and then click Go. One Web service -ReportService is found at the URL.

4. In the Web Reference Name text box, replace the default value with ReportService.

The Add Web Reference dialog box looks like this:

5. Click Add Reference. The Web reference is now added to the solution.

6. Double-click the SetPoliciesprgm.vb file in the Solution Explorer, and write your code to set the scenario.

The sample code looks like this:

Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Prompt reporting services admin to enter itempath,usergroup and roles.
Console.WriteLine("You need to provide the following information.")
Console.Write("The full path of the item:")
Dim inputitempath As String = Console.ReadLine()
Console.Write("The name of a group or user:")
Dim GroupUserName As String = Console.ReadLine()
Console.Write("The roles that are associated with the group or user:")
Dim inputRolestr As String = Console.ReadLine()
Console.WriteLine("Please waiting......Processing......")
'system process the setpolicies request.
Dim mypolicyarr As Policy() = New Policy(0) {}
'1)The roles that are associated with the group or user
Dim rolearr As String() = Nothing
Dim count As Integer = 0
Dim splitchart As Char() = {","c}
rolearr = inputRolestr.Split(splitchart)
Dim mypolicy As New Policy()
Dim myroles As Role() = New Role(rolearr.Length - 1) {}
For count = 0 To rolearr.Length - 1
Dim myrole As New Role()
myrole.Name = rolearr(count)
myroles(count) = myrole
Next
mypolicy.Roles = myroles
'2)The name of a group or user
mypolicy.GroupUserName = GroupUserName
mypolicyarr(0) = mypolicy
'3)Set the policies with the above information
If inputitempath <> "" Then
Dim itemarr As String() = Nothing
Dim i As Integer = 0
Dim itempath As String = Nothing
Dim splitchart1 As Char() = {"/"c}
itemarr = inputitempath.Split(splitchart1)
For i = 0 To itemarr.Length - 1
itempath = itempath & itemarr(i) & "/"
If i = 0 Then
rs.SetPolicies(itempath, mypolicyarr)
Else
rs.SetPolicies(itempath.Substring(0, itempath.Length - 1),
mypolicyarr)
End If
Next
End If
Console.WriteLine("Permissions granted successfully.")
Console.ReadLine()
End Sub 'Main

Click here to view code as image

Save the code as sample script with rss extension

1. Open notepad and create a new a text file.

2. Copy and paste the above code into it, and save it as SetPolicies.rss extension.

3. Copy the rss file onto somewhere, for example, it is placed on E:\RS.

Run the script using rs utility

1. Open a command prompt: On the Start menu, click Run, type cmd in the text box, and then click OK.

2. Navigate to the directory that contains the rs utility (By default the rs.exe is located at \Program Files\Microsoft SQL Server\100\Tools\Binn). Your screen looks like this:

3. At the command prompt, type the appropriate command to run the sample script file. For example, type the following command in it:

rs -i E:\RS\SetPolicies.rss -s http://ServerName/ReportServer

Click here to view code as image

4. Press Enter to execute the command

5. Type the full path of the item you want to specify for your user, and then press Enter to continue. The screen looks like this:

6. Type the name of a group or user as requested, and then press Enter to continue. The screen looks like this:

7. Please type the roles that are associated with the user or group, for example, Browsers, Report Builder. The screen looks like this:

8. Press Enter to run. After a while, the console will tell the result with a message, for example, "Permission granted successfully" indicates it completes successfully.

9. Now, the user "fareast\v-jernee" only can see the Folder3 folder under Home page in report manager.

See http://msdn.microsoft.com/en-us/library/ms162839(v=SQL.100).aspx for more details about using RS utility in reporting services.

Walkthrough: Add a Subreport in local report in ReportViewer

You coulddisplay a Subreport in Server report easily; however, you will get error message: Sub report could not be shown if you just follow above article to add a subreport in a local report. This article describes how to add a subreport in a local report.

The sample in this article uses the NorthWind database, the report displays the Customers list and Customer's detailed Order information of every Customer.

Create a web application

1. Start Microsoft Visual Studio 2008.

2. Click File menu, move to New, and then click Project....

3. Drill-down Visual C# language in the Project types panel, and then click Web tab.

4. Select ASP.NET Web Application in the Templates panel

5. Type in a name for the project, and then click OK.

Create a Main report using Report Wizard

1. Right-click the project name in the Solution Explorer window, then click New Item

2. Select Report Wizard in the Templates panel and type in "CustomerInfo" for report name.

3. Type in Your Server Name and select the NorthWind database to connect.

4. Select the table Customers to create a dataset named "CustomersDataSet"

5. Choose Tabular as report type to display CustomerID, Company, Contact, Phone, Fax information in the detail panel.

Create a Sub report using report Wizard

1. Right-click the project name again to select Report Wizard template, and then type in "OrderInfo" for report name.

2. Select table Orders to create a dataset named "OrdersDataSet"

3. Choose Tabular as report type to display OrderID, RequiredDate, ShipCountry, Address information in the detail panel.

Add a parameter CustomerID for Subreport to filter the Subreport's DataSet

1. Click the Report menu, select Report Parameters and then add a parameter named "CustomerID".

2. Click the Report menu again and select DataSource..., then click the Properties button of subreport datasource

3. Switch to the Filters tab, select Fields!CustomerID.value in Expression drop-down list; Operator =; Value Parameters!CustomerID.value, and then Click OK

Drag a Subreport control into Main report

1. Right-click the detail row on the main report, and then select Insert Row Below

2. Merge this detail row just created, and then drag a Subreport control into this row.

3. Right-click the Subreport control and select Properties, then select OrderInfo in the Subreport drop-down list.

4. Switch to the Parameters tab to select CustomerID for Parameter Name, =Fileds!CustomerID.value for Parameter Value, click OK.

5. Click the Report menu and select DataSource...

6. In the Project data source drop-down list, select Subreport's datasource and add it into main report by clicking the button Add to Report, and Click OK.

Add a ReportViewer control into your Web Form

1. Drag a ReportViewer control to the Web Form.

2. Open the smart tags panel by clicking the triangle on ReporViewer's top right corner. Click the Choose Report drop-down list and select CustomerInfo.rdlc report and you will find that two datasources are automatically created to web page.

3. Add below code to display Subreport in Main report with ReportViewer control

protected void Page_Load(object sender, EventArgs e)
{
ReportViewer1.LocalReport.SubreportProcessing += new
SubreportProcessingEventHandler(SetSubDataSource);
this.ReportViewer1.LocalReport.Refresh();
}
public void SetSubDataSource(object sender,
SubreportProcessingEventArgs e)
{
e.DataSources.Add(new ReportDataSource("OrdersDataSet__Orders",
"ObjectDataSource2"));
}

Click here to view code as image

Add a drill-down feature to the main report

1. Right-click the whole second detail row you created in Step5, and press F4 button to open Properties grid window in your right side.

2. Find out Hide property in Visibility node, set its value to True, select CustomerID as ToggleItem property's value.

Run the project, you will get the report like picture one, which display all customers' information, drill-down the + sign, you will get all the orders' detail information of every customer, as picture two's layout.

Reference

Configuring Subreports and Drillthrough Reports
LocalReport.SubreportProcessing Event

[Download the sample]

Walkthrough: Working ReportViewer with a complex Business Object with a nested generic list type property as data source for local reports

The MSDN document http://msdn.microsoft.com/en-us/library/ms251784(v=VS.100).aspxshows a good demo on how to use a business object with a ReportViewer control for local processing. This is a simple scenario, however sometimes the situation becomes complex when there is a nested generic List type property defined in the Product class. This article will dig further on handling this complex business object scenario.

Perform the following steps to create a complex business object, design a local report, and display it a ReportViewer control in your .NET Windows Form or Web Form application.

Create a business object as a data source

1. Create a Windows Form project in Visual Studio and then create a new class with the file name BusinessObject.cs.

2. Replace the default code with the code:

public class Product
{
   private string m_name;
   private int m_price;
   private List<int> m_styles;
   public Product(string name, int price, List<int> styles)
   {
      m_name = name;
      m_price = price;
      m_styles = styles;
   }
   public string Name
   {
      get
      {
         return m_name;
      }
   }
   public int Price
   {
      get
      {
         return m_price;
      }
   }
   public List<int> Styles
   {
      get
      {
         return m_styles;
      }
   }
}
public class Merchant
{
   private List<Product> products;
   public Merchant()
   {
      products = new List<Product>();
   }
   public List<Product> ProductList
   {
      get
      {
         return products;
      }
   }
   public void AddProduct(Product product)
   {
      products.Add(product);
   }
}

Click here to view code as image

3. Build your project. This creates an assembly for the object, which you will later use as a data source for the report.

Add a report to the project

1. From the Project menu, select Add New Item.

2. In the Add New Item dialog, select Report. Type a name for the report and click Add. This launches the Report Wizard with the Data Source Configuration Wizard.

3. In the Choose a Data Source Type page, select Object and click Next.

4. In the Select the Data Objects page, expand the class hierarchy under BusinessObject until you see Product in the list. Select Product and click Finish. You now return to the Report Wizard. Notice that the new data source object is added to your project in Solution Explorer.

5. Click Next, and then click Finish to quickly finish the report composing. Now you can see your local report generated like this.

Add a ReportViewer control to your Windows Form

1. Drag a ReportViewer control to your Windows form or Web form.

2. Select the ReportViewer control, and open the smart tags panel by clicking the triangle on the top right corner. Click the Choose Report drop-down list and select the report you just designed. For example:

Notice that a BindingSource is automatically created corresponding to each object data source used in the report.

Add code to display your report with ReportViewer control

1. Add the following code to the Form_Load event:

Merchant merchant = new Merchant();
merchant.AddProduct(new Product("Bike", 20, new List<int>() { 1,
2, 3 }));
merchant.AddProduct(new Product("Towel", 10, new List<int>()
{ 90, 60, 75 }));
this.BindingSource.DataSource = merchant.ProductList;
this.reportViewer1.RefreshReport();

Click here to view code as image

2. Build your project and run it.

3. After you run your application, you will find that the Styles field could not be correctly displayed:

WHAT IS WRONG?

If you look at the output information in Visual Studio, you can find the following warning:

Warning: The Value expression used in textbox 'Styles'returned a data type that is not valid. (rsInvalidExpressionDataType)

The error is caused by the fact that the Fields!Styles.Value is actually a List<int> type object which cannot be directly displayed in the report.

Modify your report for correct display

The following steps will correct the problem and display the styles into a string with the format like "1, 2, 3":

1. Double click your report to open it in designer view in Visual Studio.

2. Click the top Report menu

3. Click Report Properties

4. Switch to the Code tab and input the following code:

Public Function GetListItems(intList As
System.Collections.Generic.List(Of Integer)) As String()
   Dim intArrayStr As String() = Array.ConvertAll(Of Integer,
   String)(intList.ToArray(), New Converter(Of Integer,
   String)(AddressOf Convert.ToString))
   Return intArrayStr
End Function

Click here to view code as image

5. Click OK to return to the report

6. Replace the Styles field value =Fields!Styles.Value by the expression =Join(Code.GetListItems(Fields!Styles.Value),",").

7. Build and run your project again. Then you can get the result like this:

Reference

Samples and Workthroughs

[Download the sample]

How do I set a flat file name for flat file connection manager dynamically?

Answer

You can implement this requirement with these steps:

1. Create a package scope variable"FileName", and set its default value to "test.txt".

2. In your SSIS package, locate your existing connection manager. For example, "TestFlatConnectionManager", and add the following expression for the ConnectionString property:
"C:\\" + @[User::FileName]
Save the package, and the TestFlatConnectionManager will get the file name dynamically.

How do I use dynamic T-SQL in Lookup?

Answer

1. Create a package scope variable "SQLVariable", change its type to string.

2. Open the Lookup transformation, type a default SQL clause for it.

3. Select the data flow task that includes the Lookup and go to its properties.
Click on the ellipsis (…) against Expressions, use the expression "@[User::SQLVariable]" for [LookupName].[SqlCommand] property.

Thenyou can dynamically change the SQL clause for the Lookup by modifying the value of the variable "SQLVariable".

How do I use lookup transformation to load data incrementally?

Answer

The Lookup transformation component will help for this scenario. You can refer to the following steps:

1. Create a test table and add some test data.

CREATE TABLE dbo.SourceTable
(ProductID int NOT NULL
,Name varchar(10) NULL)
Go
CREATE TABLE dbo.DesTable
(ProductID int NOT NULL
,Name varchar(10) NULL)
Go
Insert into SourceTable(ProductID,Name) values(1,'a')
Insert into SourceTable(ProductID,Name) values(2,'e')
Insert into SourceTable(ProductID,Name) values(3,'c')
Insert into DesTable(ProductID,Name) values(1,'a')
Insert into DesTable(ProductID,Name) values(2,'b')

Click here to view code as image

2. Drag a Data Flow task onto the control panel, and double-clickthe "Data Flow Task".

3. From the toolbox, drag an OLE DB Source and connect to the table "SourceTable".

4. Drag a Lookup transformation on to the data flow panel, and create a connection from the OLE DB Source to it. Double click the Lookup component to open the Property editor, choose "Ignore failures" for "specify how to handle rows with no matching entries". In the Connection tab, choose the connection manager and select the table "DesTable". Then click the Columns tab, map the columns "ProductID" between the SourceTable and the DesTable, and check all the checkboxes in the destination.

5. Modify the output alias like this:ProductID->De_ProductID andName->De_Name. Click the"Error Output" tab,andselect the"Ignore failure item" for error column.

6. Drag a Conditional Split, and connect the Lookup to it with matched output. Edit the Conditional Split, and add the following two outputs.
NewRow: ISNULL(De_ProductID)
Changes: (ISNULL(De_ProductID)==False) && (Name != De_Name)
Note: For those records not found in the destination table, the column value of De_ProductID in the output table is NULL, which also means that these records in the source table are newly added. Else if the Name values in the source table do not match corresponding values in the destination table, it means these records in the source table are changed. If you have multiple columns, you may consider adding a timestamp column to save the update time automatically.

7. Now you can incrementally load data by inserting the new rows into your target table and update changed records also from the two outputs from the Conditional Split component. Drag a SQL Server Destination for inserting new data and an OLE DB Command for updating records to the panel.

a. Connect the Conditional Split to the SQL Server Destination with"NewRow"output. Then connect the Conditional Split to the OLE DB Command with "Changes" output.

b. Edit the SQL Server Destination, connect it to the table "DesTable".

c. Double click the OLE DB Command, set the Connection Manager column to your destination connection manager.

d. Switch to the "Component Properties" tab, and input the following T-SQL clause to the SQLCommand field:
UPDATE dbo.DesTable
SET Name = ?
WHERE ProductID = ?

e. Click the Column Mappings tab, and you can find two columns automatically generated in the Available Destination Columns, Param_0 and Param_1. According to the parameters sequence showed in the T-SQL, you can connect Name to Param_0 and ProductID to Param_1.

8. Save and execute the package.

How do I create a folder for SSIS package in MSDB with T-SQL?

Answer

Yes, you can directly insert a record into the user table dbo.sysssispackagefolders in your msdb database with the T-SQL statement as follows:

INSERT INTO dbo. sysssispackagefolders (
folderid
,parentfolderid
,foldername)
VALUES (NEWID(), '<Parent folder GUID>', '<Folder name>')
For example, if you want to create a folder named "Packages" under the
MSDB folder, you can run the following T-SQL:
INSERT INTO dbo. sysssispackagefolders (
folderid
,parentfolderid
,foldername)
VALUES (NEWID(), '00000000-0000-0000-0000-000000000000','Packages')

Click here to view code as image

How do I resolve the error "Connection is busy with results for another command"?

Answer

The error message usually indicates that there are multiple parallel running SSIS tasks sharing a same connection and that you set your connection manager’sRetainSameConnection property to True. If one task has not finished, but the others are sending requests on the same connection, this issue may occur.

To resolve this issue, you can simply change the property value to False. If you want some tasks to share a same connection, it is recommended that you have these tasks executed in sequence. For parallel tasks with the same data source, it is recommended that you set it to False or create separate connection managers for them.

How do I programmatically create a Transfer SQL Server Objects task?

Answer

You can use a Script Task to add a Transfer SQL Server Object Task and set the TableList property with StringCollection.

1. Create a package scope variable "TableName" to hold table names, change its type into Object.

2. From the toolbox, drag an Execute SQL Task to the Control Flow panel.

3. Double-click that task to edit it, change the "Resultset" value to "Full Result Set" and input the following T-SQL clause to get the table names.

Select [table_name] from INFORMATION_SCHEMA.TABLES

Click here to view code as image

4. Click the "Result set" tab, click Add, input 0 for the Result Name and select "User::TableName" for the Variable Name.

5. Drag a Script Task to the Control Flow panel, connect the Execute SQL Task to it. Open the Property Editor by double clicking the Script Task, input "User::TableName" into the ReadWritevariables field and use VB.NET as the script language.

6. Click the "Edit script" button, in the Visual Studio, write your custom code by referring to the following code to copy data with Transfer SQL Server Objects Task.

   ' Create a package to execute TransferSqlServerObjectsTask
   Dim pkg As New Package()
   Dim MoveTable As Executable =
   pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask")
   Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

   Dim aa As ConnectionManager = pkg.Connections.Add("SMOServer")
   aa.ConnectionString =
   "SqlServerName=.;UseWindowsAuthentication=True;UserName=;"
   aa.Name = "TestConn"

   MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)
   MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask,
   True)

   MoveTableTask.Properties("ExistingData").SetValue(MoveTableTask,
   Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.Existin
   gData.Replace)
   MoveTableTask.Properties("CopyAllTables").SetValue(MoveTableTask,
   False)

   'Read the records from Object variable and put them into
   StringCollection

   Dim Tables As StringCollection = New StringCollection()

   Dim oleDA As New OleDbDataAdapter
   Dim dt As New DataTable

   Dim row As DataRow
   oleDA.Fill(dt, Dts.Variables("TableName").Value)
   For Each row In dt.Rows
   Tables.Add(row(0).ToString())
   Next

   MoveTableTask.Properties("TablesList").SetValue(MoveTableTask,
   Tables)
   MoveTableTask.Properties("SourceConnection").SetValue(MoveTableTas
   k, aa.Name)
   MoveTableTask.Properties("SourceDatabase").SetValue(MoveTableTask,
   "Test")
   MoveTableTask.Properties("DestinationConnection").SetValue(MoveTab
   leTask, aa.Name)
   MoveTableTask.Properties("DestinationDatabase").SetValue(MoveTable
   Task, "Test1")

   ' Execute package and dispose
   pkg.Execute()

   Dts.TaskResult = ScriptResults.Success

Click here to view code as image

How do I execute a stored procedure with a return value and an output parameter in an Execute SQL Task?

Answer

The parameters settings for a stored procedure in an Execute SQL Task depend on your connection type. For OLE DB connection and ADO.NET connection, the parameters settings are different. Assume that you have stored procedure like this:

CREATE PROCEDURE AddDictionary
(
@name nvarchar(50),
@id uniqueidentifier output
)
AS
DECLARE @retval int
SET @retval = 0
IF EXISTS(SELECT * FROM DICTIONARY WHERE [NAME]=@name)
BEGIN
SELECT @retval = -1
END
ELSE
BEGIN
SELECT @id=newid()
INSERT INTO DICTIONARY VALUES(@id,@name)
END
RETURN @retval

Click here to view code as image

Now in your SSIS package, first create three variables in your SSIS package. Click the SSIS menu, click Variables, and create the variables User::ItemName with String type and a default value "Test", User::ItemId with Object type, and User::ReturnValue with Int32 type and a default value 0.

If your connection type is OLE DB connection, you can refer to the following steps to configure your Execute SQL Task:

1. Double click your Execute SQL Task, input the following statement to the SQLStatement field:
exec ? = AddDictionary ?, ? output

2. Choose ResultSet to None and SQLSourceType to Direct Input.

3. Switch to the Parameter Mapping panel, click Add to add the three variables created above to map the parameters in the stored procedure.

4. You can configure the settings as the following table:

   Variable Mapping     Direction       Data        Parameter   Parameter

                                        Type        Name        Size

   User::ItemName       Input           NVARCHAR    1           50

   User::ItemId         Output          GUID        2           -1

   User::ReturnValue    ReturnValue     Int32       0           -1

Click here to view code as image

Note that the ParameterName values are numeric and zero based. You need to set the name starting from 0 according to the parameters occurrence sequence in the SQLStatement.

If you are using ADO.NET connection, the steps are different:

1. Double click your Execute SQL Task, input the following statement to the SQLStatement field:

declare @retval int
exec @retval = AddDictionary@itemname, @itemid output

Click here to view code as image

2. Choose ResultSet to None and SQLSourceType to Direct Input.
Switch to the Parameter Mapping panel, click Add to add the three variables created above to map the parameters in the stored procedure as follows:

Variable Mapping      Direction        Data Type    Parameter     Parameter

                                                    Name          Size

User::ItemName        Input            NVARCHAR     @itemname     50

User::ItemId          Output           GUID         @itemid       -1

User::ReturnValue     ReturnValue      Int32        @retval       -1

Click here to view code as image

Note that the ParameterName values are same as the parameter names specified in your SQLStatement. But you need to explicitly declare the parameter for the return value.

After you are clear about these steps, you can successfully execute your stored procedure with an Execute SQL Task.

How do I use the Fuzzy Grouping transformation?

Answer

The Fuzzy Grouping transformation indeed can look through a list of similar text and group them together.
For example:
EMPID,TITLE,LNAME
1,CEO,Jack
2,Ceo,Jack
3,CIO,Jimmy

We can see that the meanings of the "CEO" and "Ceo" are the same except for the case sensitivity. So we need to group and clean those two rows.

You can do this by referring to the steps below:

1. Create a flat file named "FuzzySampleData.txt", and copy the above data into it.

2. Create a Flat File Connection under the Connection Manager panel. Name it "FuzzyGroupConn" and set the filename to the path of the file "FuzzySampleData.txt".

3. Drag a Flat File Source to the Data Flow panel and configure it to use the FuzzyGroupConn connection.

4. Drag a Data Conversion component to the panel, connect the Flat File Source to it and convert the data type of the TITLE column to DT_WSTR, and type the output alias "TITLENEW".

5. Drag a Fuzzy Grouping component to the Data Flow panel. Connect the output of the Data Conversion to the Fuzzy Grouping. Go to the Fuzzy Grouping’s property editor, select the Column tab, choose "TITLENew" as input column. Then go to the "Advanced" tab, and set the Similarity threshold to .50.

6. Drag an OLE DB Destination to the Data Flow Panel, connect the Fuzzy Grouping component to it, right click the green connection line, click Data Viewers…, and click the "Add…" button to add a data viewer. The results look like this:

_key_in | _key_out | _score | EMPID | TITLE | NAME | TITLENEW | TITLE_CLEAN | _Similarity_TITLENEW
2 | 1 | 1 | 2 | Ceo | Jack | Ceo | CEO | 1
1 | 1 | 1 | 1 | CEO | Jack | CEO | CEO | 1
3 | 3 | 1 | 3 | CIO | Jimmy | CIO | CIO | 1

Click here to view table as image

From the _key_out and TITLE_clean columns, you can find that the data is cleaned and grouped by similar title values.

Note: If you could not successfully execute the package due to a code page issue, you can change the property "AlwaysUseDefaultCodepage" of your SQL Server Destination component to True.

Reference

Fuzzy Grouping Transformation
http://msdn.microsoft.com/en-us/library/ms141764.aspx

How do I use Integration Services to achieve Push-mode processing for Analysis Services?

Answer

Usually when you process a cube, the SSAS service will send SQL queries to the underlying data source so as to load data into the cube. This is a typical pull-mode processing. But in some cases, the data sources are non-regular databases or plain text files, which will cause difficulties to use pull-mode processing since you could not use any type of SQL. In this case, you may want to use a push-mode processing.

Microsoft SSIS provides this function. It can fetch data from underlying data sources and pushes the data into SSAS cubes directly.

For example, the internet order quantity of the calendar year 2001 is: 1031. But for some reason, you need to add a new record from txt file to the cube: ProductKey OrderDateKey DueDateKey ShipDateKey CustomerKey PromotionKey CurrencyKey SalesTerritoryKey SalesOrderNumber SalesOrderLineNumber RevisionNumber OrderQuantity UnitPrice ExtendedAmount UnitPriceDiscountPct DiscountAmount ProductStandardCost TotalProductCost SalesAmount TaxAmt
TaxAmt Freight CarrierTrackingNumber
CustomerPONumber 310 20010701 20010713 20010708 21768 1 19 6 SO75124 1 1 1 3578.27 3578.27
0 0 2171.2942 2171.2942 3578.27 286.2616 89.4568 NULL NULL

1. Create a package, drag and drop a "Data Flow Task" on to the Control Flow tab. Copy and paste above record to a txt file.

2. Switch to Data Flow, create a "Flat File Source" pointing to the txt file. Select the option "Column names in the first data row", keep the other default setting and then switch to the columns tab, review the data to ensure you can access to that file without any problem.

3. In Flat File Source Editor, select the option "Retain null values from the source as null values in the data flow".

4. Drag and drop a "Partition Processing Destination" on to the Control Flow tab, connect "OLE DB Source" to "Partition Processing Destination", double click the "Partition Processing Destination", and then create a data Source pointing to the Adventure Works DW 2008 multidimensional database.

5. Select partition Internet_Sales_2001 as target object, select ‘Add’ as processing method for testing only.

6. Map the input columns with the partition attributes. You should map every attribute to the input columns, for example:

7. Run the package, then check the internet order quantity of calendar year 2001, and it should be 1,014 now.

How do read data from Excel 2007 with Integration Services 2005?

Answer

To read data from Excel 2007, you can try the following steps:

1. Ensure you have installed the provider "Microsoft Office 12.0 Access Database Engine OLE DB Provider"

2. Create an OLE DB source, double click on the OLE DB Source and then create an OLE DB connection manager.

3. Select "Microsoft Office 12.0 Access Database Engine OLE DB Provider" as the provider, and enter the full path of the file for "Server or file name".

4. Switch to the "All" tab, specify the extended properties, and then enter "excel 12.0" for the property "Extended properties". Click the button "Test Connection", ensure you can access to the Excel file.

5. After create the Connection Manger, in OLE DB Source Editor, select "Table or view" as the access mode, and then select the Excel sheet in the list "Name of the table or the view". Then Click the "OK" button.

How do I upgrade my DTS packages to SSIS 2008 packages?

Answer

SSIS is a revolutionary change to DTS. A lot of things have been changed. Currently there is no way to directly upgrade a complex DTS package to a SSIS package, instead you can only migrate a DTS package to a SSIS package by using DTS Package Migration Wizard, but there are still many known issues for the migration process. It may be fine to migrate some simple DTS packages, but for complex ones, the migration process may have problems.

If you decide to use DTS Package Migration Wizard to migrate the DTS packages, after the migration you still have a lot of work to do, recreating some components, reconfiguring some settings and performing tests.

If you have many DTS packages, the way I recommend is you keep on using your old DTS packages but just wrap each DTS package with a SSIS package, which means you can use the task "Execute DTS 2000 Package Task" in SSIS package to execute your existing DTS package.

If the number of DTS packages is low and the packages are not very complex, you may consider creating new SSIS packages to replace them.

Please refer to this article for more detailed information: Migrating Data Transformation Services Packages http://msdn.microsoft.com/en-us/library/ms143501.aspx

If you want to know of some third-party tools for the migration, you may try this one, DTS xChange developed by the MVP Brain Knight. Please note that for third party tools, Microsoft has no warranties of them. We post the information here just for your reference. If you have any questions on third party tools, please contact the vendors.

How do I troubleshoot SSIS packages failed execution in a SQL Agent job?

Answer

When you see a SSIS package fails running in a SQL Agent job, you need to first consider the following conditions:

1. The user account that is used to run the package under SQL Server Agent differs from the original package author.

2. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.
You can find the detailed information in this KB article:
An SSIS package does not run when you call the SSIS package from a SQL Server
Agent job step
http://support.microsoft.com/kb/918760

You can check SQL Server Agent’s activity logs, Windows Event logs and SSIS logs to get more clues. Also the tool Process Monitor is helpful to track the cause of registry or file access related issues.

The following 4 issues are common encountered in the SSIS forum. We also would like to list them here for your reference:

1. The package's Protection Level is set to EncryptSensitiveWithUserKey but your SQL Server Agent service account is different from the SSIS package creator.

2. Data source connection issue.

3. File or registry access permission issue.

4. No 64-bit driver issue.

Package Protection Level issue

For the 1st issue, you can follow the following steps to troubleshoot this issue:

1. Check what the Protection Level is in your SSIS package.

2. If the Protection Level is set to EncryptSensitiveWithUserKey, check the Creator in your SSIS package and compare it with the SQL Server Agent Service account.

3. If the Creator is different from the SQL Server Agent Service account, then the sensitive data of the SSIS package could not be correctly decrypted, which will lead to the failure.

A common solution to this issue is that you create a proxy account for SSIS in SQL Server Agent and then specify the proxy account as the "Run as" account in the job step. The proxy account must be the same as the SSIS package creator. You can refer to this article for how to create a proxy account:

How to create a proxy account
http://msdn.microsoft.com/en-us/library/ms190698.aspx
You can also change the SSIS package protection level to EncryptSensitiveWithPassword and specify the password (/P XXXX) in the command line in the job step. For the command parameters, you can refer to this article:
Dtexec utility
http://msdn.microsoft.com/en-us/library/ms162810.aspx

Besides you can import your SSIS package into your SQL Server MSDB database via SQL Server Management Studio (connecting to Integration Services). When you import the package to MSDB, you need to set the ProtectionLevel to "Rely on server storage and roles for access control". After that, add your SQL Server Agent Services account to be a user of MSDB with the db_ssisadmin role. Then connect to your SSIS in SSMS again, right click your SSIS package, click Package Roles, and set both the ReaderRole and Writer Role to db_ssisadmin.

Data Source Connection Issue

For the 2nd data source connection issue, it usually happens when you are using Windows Authentication for your data source. In this case, you need to make sure that the SQL Server Agent Services service account or your Proxy account has the permission to access your database.

File or Registry Key Access Issue

For the 3rd file or registry access permission issue, if you know which file or folder your SSIS package need to make sure that your SQL Server Agent Services service account or your Proxy account has the permission to access the file or folder. If you are not very sure of this, you can run SQL Server Process Monitor to monitor your SQL Server Agent Services process and dtexec process when you start running the job. You can analyze the trace to check if there is any ACCESS DENIED message. You can download Process Monitor here:
Process Monitor
http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

64-Bit Driver Issue

For the 4th issue, this usually happens on a 64-bit server since there is no corresponding 64-bit driver in a 64-bit process. Think about that when your SSIS package is accessing Excel or Access database file while there is no related 64-bit Jet driver, the job of executing your SSIS package will fail. To resolve this issue, before SQL Server 2008, you need to use 32-bit DTEXEC utility in your SQL Server Agent job to execute the SSIS package. In SQL Server 2008, you have a better choice, using the option "Use 32-bit runtime" under the tab "Execution options" of your job step with the "SQL Server Integration Services Package" type.

How do I track the error message when my SSIS package failed execution?

Answer

Generally if you do not apply any logging functions to your SSIS package, you may only find very few error messages via Windows Event logs or console output. But if your SSIS package has logging functions enabled, that is a different story. Commonly there are five places you can work with:

1. Windows Event Log & Job History

2. Logs from SSIS Logging Audit

3. Logs from SSIS Event Handler

4. Logs from the SSIS components

5. Logs from underlying data sources

Windows Event Log & Job History

If your SSIS package runs with a SQL Agent job, you can first track the error message in Windows Event Logs and SQL Server Agent job history.

Logs from SSIS Logging Audit

The first method usually displays general error messages. If you need to probe more detailed information, you can enable SSIS logging audit to output the error messages to a text file, Windows Event log, SQL Server Profiler trace, XML file or the SQL Server database table sysssislog. In SSIS package, you can do this by configuring SSIS log providers.

Integration Services Log Providers
http://msdn.microsoft.com/en-us/library/ms140246.aspx

Logs from SSIS Event Handler

You can add more custom error handling logics under the Event Handler panel. You can define the error event handler at both the package level and the control flow task level. It is a powerful feature and easy for you to create custom event handling logic for different events.
Integration Services Event Handlers
http://msdn.microsoft.com/en-us/library/ms140223.aspx

Logs from SSIS components

You can also implement your custom logging logic in a SSIS Script Task or a Script Component. For example, write some variable values or data into a text file during the SSIS package execution.

Logs from underlying data sources

If from your SSIS logs or Windows application logs, you find some overall error messages related to your underlying data sources, you may need to drill into the details by observing the error logs of your data source. In SQL Server, this is SQL Server error logs which are by default located in Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

How do I troubleshoot a connection error regarding DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER ?

Answer

Many issues may cause this error message. When this issue happens, it usually lacks of adequate information for troubleshooting. The following summarizes most common scenarios and resolutions on this issue so that you can have the general perspectives to troubleshoot this issue.

1. Incorrect provider for connection

This issue usually happens when you choose a provider which is not mapped to the correct version that the process needed. There are two typical scenarios:

A.Lack of 64-bit provider

B.Lack of client binary installed

For A, a common issue to see is that there is no 64-bit Jet provider, though you can choose the provider at the design time, but when you run it under 64-bit process, the execution will fail.

To resolve this issue, you need to use 32-bit DTEXEC to run your SSIS package. If you are in a job, set the job step to run the 32-bit dtexec to execute your SSIS package. If you are in BIDS, you need to change the SSIS Project property (under Debugging) "Run64BitRuntime" to False. But please aware that this setting is only for debugging purpose in BIDS.

For B, it often happens for third party data sources like Oracle, AS/400 DB2 etc. When you deploy your SSIS package to a target server, please remember to install and configure the client binaries.

2. Incorrect connection parameters settings

This issue often happens when you specify wrong connection parameters or explicitly specify a password in the connection string in your SSIS package. You need to first make sure that every parameter you set in the connection string does not have any typo error. For the password issue, it is caused by the fact that your SSIS package does not save the password in the package.

If you are using a connection to SQL Server, there is an option "Save my password" under "Use SQL Authentication" on the connection property window. But for other types of data source connections, there might be no such option. You may use a configuration file to save your password. However there is a security risk since the password in a configuration file is unencrypted. A safer way is to save the sensitive information in your SQL Server database so that you can provide more restrictions to access the password information, or you can change the package’s protection level to EncryptSensitiveWithPassword or EncryptSensitiveWithUserKey.

3. Sensitive data for the connection could not be correctly decrypted

This issue usually happens when you execute your SSIS package in a SQL Server Agent job. By default, your SSIS package chooses EncryptSensitiveWithUserKey as the ProtectionLevel. If your SQL Server Agent Services service account is different from the SSIS package creator, it cannot decrypt the sensitive data correctly and this will fail the connection. You can refer to the FAQ "How do I troubleshoot SSIS packages failed execution in a SQL Agent job?" for more information.

4. Oracle data provider limitation

Another common scenario happens when you use Microsoft OLE DB Provider for Oracle or Microsoft ODBC Driver for Oracle to connect to Oracle9i or later version database. There are many limitations which can be found in this KB article,http://support.microsoft.com/kb/244661. It is recommended that you use Oracle OLE DB Provider for the Oracle 9i or later versions.

But you still need to be aware of this known issue on the connection parameter "Initial Catalog":
You get "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER" error message when using Oracle connection manager in SSIS
http://support.microsoft.com/kb/2009312

How do I troubleshoot SSIS package hanged for running?

Answer

There might be various reasons to cause a SSIS package hanged for running and generally for a hang issue, there is no log information helpful for diagnostics. These are the difficulties of troubleshooting hang issues. In this FAQ, we would like to give you three common aspects to help you perform self-troubleshooting:

1. Resource bottleneck

2. Blocking or Deadlock

3. Poor performance query

Resource Bottleneck

Generally resource bottleneck includes bottlenecks of memory, processor, physical disk or network interface. You can run Performance Monitor (perfmon.exe) to collect the trace with the following counters:

Processor: %Processor Time

Processor: %DPC Time

Processor: %Privileged Time

Processor: Interrupts/sec

Memory: Available Bytes

Memory: Pages/sec

Memory: Page Faults/sec

Memory: Committed Bytes

PhysicalDisk: Avg. Disk Queue Length

PhysicalDisk: Avg. Disk Read Queue Length

PhysicalDisk: Avg. Disk Write Queue Length

PhysicalDisk: Disk Reads/sec

PhysicalDisk: Disk Writes/sec

PhysicalDisk: Disk Transfers/sec

Network Interface: Bytes Total/sec

Network Interface: Bytes Sent/sec

Network Interface: Bytes Received/sec

Network Interface: Current Bandwidth

Process: *

System: Processor Queue Length

System: Threads

(*) means all the related performance counters

Memory pressure

When you observe that there is not much available memory and the Pages/sec is over 20, it indicates that you are probably facing a memory bottleneck. You can check or try the following methods to reduce memory usage:

• Check if your SSIS package is trying to load a large amount of data with a Sort transformation that is memory intensive, if there is a sort transformation in your SSIS package, consider removing it and put the sort operation to your database level.

• Avoid including unnecessary columns from your source component

• Stop all unnecessary applications or services to release some memory;

• Reduce the values of DefaultBufferMaxRows and DefaultBufferSize.

• If your SSIS package uses SQL Server data source, try configuring "min server memory" and "max server memory" on your SQL Server instance so as to restrict the memory usage by SQL Server;

• Split your SSIS package into multiple packages with each one only transferring small amount of data.

• If your server is 32-bit with 4GB memory, you can enable /3GB switch in your system boot file so as to assign 1GB more memory to applications running in user mode.

If these steps still do not help, consider adding physical memory to your machine.

High CPU

If you encounter high CPU issues during the package execution, you can identify which process is intensively using the CPU by observing the Process: %Processor Time counter or simply using Task Manager to sort the process by CPU. If the high CPU is for dtexec process, you need to check if there are too many concurrent threads for SSIS tasks. If so, try reducing the package property MaxConcurrentExecutables value or combining parallel tasks in sequence to see if this situation will be improved.

If your SSIS package is accessing a local SQL Server instance, you need to check if the high CPU is caused by a nonoptimal query plan. You can refer to this article on further
troubleshooting,http://msdn.microsoft.com/en-us/library/cc966540.aspx.

If the high CPU is caused by another process, you need to observe the relationship between the process and your SSIS package, and evaluate if you can temporarily stop the process or schedule to run your SSIS package in a different time.

Disk bottleneck

If you see PhysicalDisk: Avg. Disk Queue Length >2, it usually indicates that you are probably facing a disk I/O bottleneck issue. You need to contact your hardware vendor to perform I/O stress test and diagnostics.

Network bottleneck

For NIC, you can check the network utilization by observing the counters Network Interface: Bytes Total/sec and Network Interface: Current Bandwidth. If the average value of (bytes total/sec) / current bandwidth is over 70% during your SSIS package execution, it means that your NIC may encounter a bottleneck and you may consider replacing it with a faster NIC.

In addition, you can run Network Monitor (netmon.exe) to monitor the network traffic on your NIC, if you see a significantly large number of TCP re-transmissions, it could indicate that your network condition is not healthy and it may cause intermittent connection issue.
You can also refer to this article for using perfmon to diagnose performance issues:

http://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx

Blocking & Deadlock

If you do not encounter resource bottleneck problems, you can check if there are blocking/deadlock issue. Blocking issues may happen in the underlying data sources, files or script tasks.

For SQL Server, there are a lot of methods to monitor blocking and deadlock issues. A common way is to use SQL Server Profiler to capture the trace of Locking events. You can also refer to this KB article to monitor blocking issue:
http://support.microsoft.com/kb/271509

You can also enable the trace flags 1204 and 1222 on your SQL Server to check if there is any deadlock happening:
DBCC TRACEON(1204, 1222, -1)
Enabling these trace flags will output deadlock information to your SQL Server error logs and you can find the detailed information from your SQL Server Error log. This article describe more information about deadlock:
http://msdn.microsoft.com/en-us/library/ms178104.aspx

When you identify blockers, you can kill them with the KILL command. Blocking and deadlock are very common for concurrent access and sometimes it is by design and cannot be avoided, but you can refer to the following article to minimize the blocking opportunity:
http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx

For file access, avoiding concurrent access usually can resolve blocking issues. For Script task, a common blocking issue happens on concurrently reading/writing variables. You need to use VariableDispenser.LockForRead or VariableDispenser.LockForWrite for read or write variables. Remember to explicitly call Variables.Unlock() to release the locks once you finish handling the variables. More information can be found here:
Variables.Unlock Method
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.variables.unlock.aspx

Poor Performance Query

If through your observation, you find that your SSIS package is hanging at the step of an Execute SQL Task or other tasks running some queries to your SQL Server database, in this case, you can use SQL Server Profiler to capture the trace of the queries and identify the cause of the issue by analyzing the profiler trace file. Then you can use Database Engine Tuning Advisor (DTA) to analyze the queries and get some suggestions from DTA on how to improve the query performance such as creating missing indexes. You can refer to this article for more information:
http://msdn.microsoft.com/en-us/library/ms166575.aspx

Using DTA is a simple way to help you perform quick tuning on your queries. Besides if your database is a large database with busy CRUD operations, you may need to pay special attentions on your database statistics and fragmentations. If database statistics are not updated in-time, the cached query execution plan may be out and thus is not the best optimized one for the current query, it is recommended that you schedule a job to timely update statistics on those heavy tables.

Also huge fragmentations also degrade query performance, you can run the DMF sys.dm_db_index_physical_stats on your table indexes to check the value of avg_fragmentation_in_percent, if it is between 5% and 30%, try re-organizing your indexes, if it is larger than 30%, try rebuilding indexes.

If you want to know more detailed information of troubleshooting SQL Server performance issue, you can refer to the following two articles:
Troubleshooting Performance Problems in SQL Server 2005
http://msdn.microsoft.com/en-us/library/cc966540.aspx
Troubleshooting Performance Problems in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd672789.aspx
If you have a large amount of data to be transferred, it is recommended that you enable the FastLoad option which requires a TABLE LOCK on the destination table. If your destination is SQL Server, please use SQL Server Destination for the best performance.

Other

Some problems are caused by known issues which are addressed in the latest SQL Server Service Packs. It is recommended that you apply the latest service pack before you perform in-depth troubleshooting.

If you still cannot resolve this issue after taking the above jobs, please collect a hang dump file with ADPlus and contact Microsoft Customer Support Services for further assistance.

How to use ADPlus to troubleshoot "hangs" and "crashes"
http://support.microsoft.com/kb/286350

How do I use SSIS to completely transfer my database including logins, users, and permissions from one server to another server?

Answer

This is a very common asked question, but there is indeed no easy way for a fully automatic process. Though SSIS provides some methods like Transfer SQL Server Login Task, Transfer SQL Server Database Task and Transfer SQL Server Database Object Task, when you perform a real transfer, you will find that transferring logins, users and user permissions is always a head-ache due to the security design.

Many people usually need to manually reset the login passwords, recreate logins and assign database roles to users after they create a SSIS package with those aforementioned tasks for transfer. Here are the steps:

1. In SSIS, drag Transfer SQL Server Login Task to the Control Flow. Double click on it to open the property editor, set the connections, choose the logins you want to transfer and set the options "IfObjectExists" and "CopySids" per your favorite.

Note: You can choose "IfObjectExists" to Skip if there are already duplicated logins existing on the target server. In this case, choosing Overwrite may cause errors during execution.

Setting CopySids to True is helpful for transferring Windows domain user accounts when the two servers are in the same domain. If they are in different domains, you need to re-create the logins later.

2. Drag a Transfer Database Task to the Control Flow panel. Double click it to open the property editor, choose the Method of Source Database to "DatabaseOnline", and set the connections and destination database.

3. Link the two Tasks together from Transfer Login Task to Transfer Database Task.

4. Save and Execute the SSIS package.

5. After that, connect to the target server, manually change the login password for those transferred SQL logins since for SQL logins, after the transfer, their passwords have been reset with random values.

6. As step 1 mentioned, if the Windows login account is in different domain, you need to drop and recreate the logins.

7. Manually assign database roles for users.

To automate the whole process within a SSIS package, you need to consider integrating the steps 5 to 7 to your SSIS package. Before you transfer your database, you can first script out the logins/passwords, login permissions to a logins.sql script file and user role permissions to another users.sql file, then you can implement an automatic transfer with one Execute SQL Task (executing the logins.sql script file) for creating logins/passwords on the target server, one Transfer Database Task for transferring database objects to the target server as the step 2, and one Execute SQL Task (executing the users.sql script file) to assign users’ permissions.

Reference

Transfer Login Task
http://technet.microsoft.com/en-us/library/ms137870.aspx
Transfer Database Task
http://technet.microsoft.com/en-us/library/ms141204.aspx
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546
How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008
http://support.microsoft.com/kb/918992/

How do I create and deploy a SSIS custom task?

Answer

Regarding the creation of a SSIS custom task, you can first start with the following article and its related articles:
Create a Custom Task
http://msdn.microsoft.com/en-us/library/ms345156.aspx

There is also a good example "HelloWorldTask" in SSIS team blog which you can reference:
http://blogs.msdn.com/mattm/archive/2008/07/18/creating-a-custom-task-with-a-default-ui.aspx.

To easily deploy your SSIS custom task and use it immediately in your BIDS, you can add the following script to your project's PostBuild event:
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\gacutil.exe" /if
"$(TargetPath)"
copy $(TargetFileName) "C:\Program Files (x86)\Microsoft SQL
Server\100\DTS\Tasks"

In this way, once you build your project, your custom task will be automatically deployed. You can right click the Toolbox in BIDS, click "Choose Items" and select the task at "SSIS Control Flow Items". This article depicts the detailed information: Building, Deploying, and Debugging Custom Objects
http://msdn.microsoft.com/en-us/library/ms403356.aspx

The above articles are for basic knowledge of how to create a SSIS custom task. Sometimes there are some requirements which require your in-depth understanding of SSIS development and .NET Windows Forms control programming. Another commonly asked question for SSIS custom task is how you can create a property for your custom task, but the property value need to be selected from a dropdown list. The above examples do not show you how to do this. If you do not know about Windows Forms control programming, this may take a lot of time working on this. But if you know it, it will not be a difficult job. Let us take the HelloWorldTask project for example. To implement this requirement. You need to:

1. Create a custom type converter class inherited from an existing type converter like StringConverter and override the existing functions. For example:

class DateFormatConverter:StringConverter
{
public override bool
GetStandardValuesSupported(ITypeDescriptorContext context)

{
return true;
}
public override TypeConverter.StandardValuesCollection
GetStandardValues(ITypeDescriptorContext context)
{
return new StandardValuesCollection(new string[]
{ "yyyy-MM-dd","M/d/yyyy","d/M/yy"});
}

public override bool
GetStandardValuesExclusive(ITypeDescriptorContext context)
{
return false;
}
}

Click here to view code as image

2. Define the attribute in the GeneralViewNode class as follows:

[TypeConverter(typeof(DateFormatConverter)),
DisplayName("Format"), Description("Display Date Format")]
public string Format
{
get
{
return format;
}
set
{
format = value;
}
}

Click here to view code as image

3. Rebuild the project and deploy it. You may need to close your Visual Studio and re-open it so as to use the latest version custom task.

You can read this great article for developing advanced functions for custom task properties:

Getting the Most Out of the .NET Framework PropertyGrid Control
http://msdn.microsoft.com/en-us/library/aa302326.aspx

How do I improve package loading performance in BIDS when I open a package which is imported from another place?

Answer

This issue is usually caused by the fact that your SSIS package is complex with many tasks including many connection managers. When you open the SSIS package in BIDS, by default the validation process on each task will start. Since your SSIS package is copied from another place, the connections may be invalid. In this case, the validation process will spend a lot of time and cause the BIDS not working during this time.

To resolve this issue, you can do as follows:

1. Before you add the SSIS package, select your SSIS project first, then click the SSIS menu, and change your SSIS project to Offline mode by checking "Work Offline".

2. Add your SSIS package to the project and change each Task's DelayValidation property to True.

3. After these steps, you can modify the connections to be valid based on your local test environment.

Contents

Part I Database Administration

      1. Why did a T-SQL script fail to run a job when it could successfully run in SQL Server Management Studio ?

      2. How can I degrade a SQL Server database from a higher version to a lower one?

      3. How can I create a linked server to the Access or Excel data source from the 64-bit version of SQL Server 2005/2008?

      4. Why is all of the memory allocated for SQL Server not shown in Windows Task Manager?

      5. How do I configure SQL Server to use larger memory space over 2GB on a 32-bit server?

      6. How do I rename my SQL Server instance?

      7. How do I store data in multiple languages within one database?

      8. How do I calculate the space a nullable column takes in SQL Server 2008?

      9. Why can't I view the list of databases, tables, views and etc. in the SQL ServerManagement Studio Object Explorer?

    10. Why can't I by pass the "Restart computer" rule when installing SQL Server 2008?

    11. Can SQL Server Enterprise Edition be installed on client operating systems?

    12. How do I troubleshoot SQL Server connectivity issues?

    13. Why can’t I track data changes even though I already enabled the database audit on my database?

    14. How do I use Powershell script to read the information of any database on a server?

    15. Why can't I log on to SQL Server using Windows Authentication in SQL Server Management Studio?

    16. What permission(s) do I need to execute a stored procedure (sp)?

    17. How do I troubleshoot SQL Server 2008 installation issues?

    18. How do I configure SQL Server to enable distributed transactions via Linked Server?

    19. How do I find the correct "server" or "data source" value for an SQL Server instance in a connection string?

    20. How do I configure Windows Firewall to allow remote connections using TCP/IP protocol for SQL Server?

    21. Could SQL Server 2008 Express edition and SQL Server 2008 R2 Express edition be installed side by side with SQL Server 2005 Express?

    22. How do I apply a service pack for an instance of SQL Server 2005 Express Edition or SQL Server 2008 Express Edition?

    23. How do I resolve ‘SQL Server Setup cannot valid the service accounts’ error message during the setup?

    24. Why do I get ‘Rule "Existing clustered or clustered-prepared instance" failed’ error while adding new features to an existing instance of SQL Server Failover Cluster?

Part II Analysis Services

    25. How do I clear the warm cache of a cube?

    26. How do I exclude data members or create non-aggregatable values in parent-child dimensions?

    27. How do I specify a calculated member as the default member of an attribute?

    28. How do I create AVG measure in my cube?

    29. How do I implement dynamic security for the users?

    30. What is the common usage of Unknown member?

    31. How do I achieve Cumulative Sums for a set in MDX?

    32. How do I use wildcards in MDX?

    33. How do I query multi-dimensional data in a relational database with T-SQL?

    34. How do I resolve the error "File system error: A FileStore error from WriteFile occurred. Physical file: ...." when I process a dimension by using the Process Update option?

    35. How do I connect to a SSAS cube through Internet?

    36. What will happen if I create two roles for a user?

    37. How do I connect to SSAS instance without SQL Browser services started?

    38. How do I keep on accessing my cube in MOLAP storage mode when it is processed?

    39. How do I have the members of a dimension displayed in my custom order?

    40. How do I run MDX query in Excel 2007/2010?

    41. How do I automatically deploy my Analysis Services 2005/2008 project?

    42. How do I process a cube via T-SQL?

    43. Can I perform writeback operations in Excel 2007?

    44. How do I troubleshoot the slow MDX query performance?

Part III Reporting Services

    45. How do I hide the toggle image (+) if there is no row when using drill-down?

    46. How do I achieve nested aggregation?

    47. How do I achieve column break in a matrix?

    48. How do I use Parent-Child Hierarchies and formatted value in report with Analysis Services?

    49. How do I set interactive sort across multiple groups in SSRS 2005?

    50. How do I get distinct values of SharePoint column using SQL Server Reporting Services?

    51. How do I resolve the printer problem "Unable to load client print control"?

    52. How do I allow Enter key to act like the "view report" button?

    53. How do I avoid prompting Login dialog while deploying report from Microsoft Business Intelligence Development Studio?

    54. How do I resolve the error "No report servers were found on the specified machine"?

    55. How do I get the subtotals of each page and previous pages at the bottom of the report?

    56. How do I anonymously access the Report Manager?

    57. How do I deploy a Report Server for Internet Access?

    58. How do I resolve the Reporting Server Error 1053: The service did not respond to the start or control request in a timely fashion?

    59. How do I dynamically load an external image in a report?

    60. How do I call the public methods of Reporting Service Web service in XML Datasource to query report server metadata?

    61. How do I get group total in Group Footer with PageBreak in SQL Server Reporting Services?

    62. How do I create cascading parameters when using cube database in Reporting Services?

    63. Why does the "Attempted to divide by zero" error still happen?

    64. How do I solve the issue - The configuration file contains an element that is not valid?

    65. How do I pass a multi-value parameter within a URL?

    66. How do I keep only one vertical scroll bar in local report?

    67. How do I Hide Export Options with Local Report?

    68. How do I get the same page number in web page and PDF?

    69. How do I implement OR logic or complicated logics for filters in a SSRS report?

    70. How do I work Parameters with a local report?

    71. How do I pass a multi-value parameter into a report with SQL Server Reporting Services (SSRS) Web Services API?

    72. How do I pass a multi-value parameter to a dataset while using LIKE operator in the WHERE clause?

    73. How do I preview a Subreport which is in another SSRS project?

    74. Walkthrough: How to list models from SQL Server Master Data Services in a SQL Server Reporting Services (SSRS)

    75. Walkthrough: Control Dataset Query Dynamically based on SSRS Report Parameters

    76. Walkthrough: How to change XML data for embedded XML document in a SQL Server Reporting Services (SSRS) report

    77. Walkthrough: How to get distinct values of a column of a SharePoint list using SQL Server Reporting Services

    78. Walkthrough: Assign Dataset Dynamically Created in Code to your local report with ReportViewer

    79. Walkthrough: Restrict the users to see only their folders via Reporting Services web services

    80. Walkthrough: Add a Subreport in local report in ReportViewer

    81. Walkthrough: Working ReportViewer with a complex Business Object with a nested generic list type property as data source for local reports

Part IV Integration Services

    82. How do I set a flat file name for flat file connection manager dynamically?

    83. How do I use dynamic T-SQL in Lookup?

    84. How do I use lookup transformation to load data incrementally?

    85. How do I create a folder for SSIS package in MSDB with T-SQL?

    86. How do I resolve the error "Connection is busy with results for another command"?

    87. How do I programmatically create a Transfer SQL Server Objects task?

    88. How do I execute a stored procedure with a return value and an output parameter in an Execute SQL Task?

    89. How do I use the Fuzzy Grouping transformation?

    90. How do I use Integration Services to achieve Push-mode processing for Analysis Services?

    91. How do read data from Excel 2007 with Integration Services 2005?

    92. How do I upgrade my DTS packages to SSIS 2008 packages?

    93. How do I troubleshoot SSIS packages failed execution in a SQL Agent job?

    94. How do I track the error message when my SSIS package failed execution?

    95. How do I troubleshoot a connection error regarding DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER ?

    96. How do I troubleshoot SSIS package hanged for running?

    97. How do I use SSIS to completely transfer my database including logins, users, and permissions from one server to another server?

    98. How do I create and deploy a SSIS custom task?

    99. How do I improve package loading performance in BIDS when I open a package which is imported from another place?

  100. How do I use parameters in an SSIS ADO NET source?

  101. How do I refresh the layout information of a SQL Server Integration Services (SSIS) package that is modified programmatically?

Copyright © 2012 by Microsoft Corporation

All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher.

Microsoft and the trademarks listed at
http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective owners.

The example companies, organizations, products, domain names, email addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, email address, logo, person, place, or event is intended or should be inferred.

This book expresses the author's views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.

Part I
Database Administration

The collected FAQs for database administration attempts to answer the most frequently asked questions related to the database maintenance, security, setup and upgrade, tools and performance etc. It serves as a repository of consolidated answers to these most common questions. If there is any feedback, please send to sqltnsp@microsoft.com.

Part II
Analysis Services

The collected FAQs for analysis services attempts to answer the most frequently asked questions related to the analysis services maintenance, security, MDX query configuration and performance etc. It serves as a repository of consolidated answers to these most common questions. If there is any feedback, please send to sqltnsp@microsoft.com.

Part III
Reporting Services

The collected FAQs and walkthrough articles for reporting services attempts to answer the most frequently asked questions related to the report authoring, security, configuration, development and error trouble-shootings etc. It serves as a repository of consolidated answers to these most common questions. If there is any feedback, please send to sqltnsp@microsoft.com.

Part IV
Integration Services

The collected FAQs for integration services attempts to answer the most frequently asked questions related to the package design, security, development, error troubleshooting and performance etc. It serves as a repository of consolidated answers to these most common questions. If there is any feedback, please send to sqltnsp@microsoft.com.

SQL Server Community FAQs Manual

SQL Server TechNet Forum Support Team

Summary: The SQL Server Forum Support Team authored, collected and consolidated commonly asked questions in SQL Server MSDN and TechNet forums into this book so as to provide an offline reading and learning experience for IT professionals and people who are interested in SQL Server. The book includes about 101 items related to common problems with database administration, SSAS, SSIS and SSRS. (The book was originally published in June 2011.)

Category: Reference
Applies to: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2
Source: MSDN Blogs (link to source content)
E-book publication date: January 2013