.:: Dinesh Asanka ::.

Following are the steps you need to follow to accomplish above task.

• Open the Microsoft Visual Studio 2005.
• Select Server Explorer option from the View Menu.
• From the Data Connections node, right click and select, 'Add connection'. Fill in the details to connect to the intended SQL Server and the database using the login, which has a fixed server role, SysAdmin. Click on Test connection. Once the connection succeeds, the overall screen should look like the following.
• Expand the data connection just added, and navigate to the Stored Procedures node.
• Expand the Stored Procedures node and select the intended SP to be debugged.
• Right click and select open to view the source code of the selected stored procedure.
• Place a break point on the intended line of code where debugging needs to be started it’s usually the way .NET Developers perform.
• After performing the above steps the screen shot should look like the following.

• Right click on the stored procedure from the 'Server Explorer' and select 'Step-Into Stored Procedure' which will bring following dialog box. This dialog box allows you to enter stored procedure parameter values.

• Next, you have the options of debugging your stored procedure using normal function keys. You have the option of checking the variable values too.

SQL Server Analysis Service (SSAS) Databases are another type of database which stores cubes, dimensions, etc. Often, people do not get backups of these databases. This article shows you how to get those backups.

After a server reboots the SQL Server Reporting Server (SSRS) service is failing to start giving following two errors in the event log.

1. The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:

The service did not respond to the start or control request in a timely fashion.

2. Timeout (30000 milliseconds) waiting for the SQL Server Reporting Services (MSSQLSERVER) service to connect.

This issue may occur if the service times out before it starts successfully. This issue is more likely to occur if your computer is heavily loaded.

To resolve this issue, increase the time-out value for service startup process. When you increase this value, the Microsoft ISA Server Storage service has more time to load when the computer starts. To increase the service startup time, create the following registry entry:

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

Name: ServicesPipeTimeout

Type: REG_DWORD

Data: The number of milliseconds that you want to give the services to start in

Typically, a data value of 35,000 is sufficient to keep the service from timing out. However, you can reduce or increase this value according to your specific startup requirements. For example, to use a time-out value of 60 seconds, assign a data value of 60,000 to the ServicesPipeTimeout registry entry. A larger data value does not decrease your computer's performance. To create this registry entry, follow these 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. Right-click Control, point to New, and then click DWORD Value.

4. In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.

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

6. Click Decimal, type the number of milliseconds that you want to wait until the service times out, and then click OK.

For example, to wait 60 seconds before the service times out, type 60000.

7. Quit Registry Editor, and then restart the computer.

At last, Microsoft SQL Server 2008 going to be released in August.

See full details here.

Importing Excel files to SQL Server is a frequent task you need to carry out in your day to day life. Most probably you are using SSIS to import Excel files to SQL Server.

If you are asked to import an Excel file, you can use Excel Source from the Data Flow Sources SQL Server Integration Services (SSIS) and select correct version from the available list.

You can see that you can only import Excel files up to Microsoft Excel 97-2005 version, which means that you are not allow to import Excel 2007 files from the above control.

However, if you follow below steps, you can import Excel 2007 files into the SQL Server.

1. Drag and drop OLE DB Source data flow source to the data flow task.

2. Double click the OLE DB Source and click New button for OLE DB Connection Manager.

3. Click New button in the in Configure OLE DB Connection Manager screen.

4. Select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider from the OLE DB Provider list.

5. Select All option and at the Extended Properties enter Excel 12.0. After this you will see a screen like following image.

You can see the selected provider at the top of the screen.

6. Enter the file name with full path and make sure you have the extension xlsx.

7. After clicking OK button, you will be taken to the initial screen, in which you have to select the worksheet you want.

8. After this, you are ready to import data from Excel 2007 to SQL Server. It is just a matter of inserting correct destination you need.

When try to start SQL Server service it failed and following errors are logged in Application log in Event Viewer.

TDSSNIClient initialization failed with error 0x7e, status code 0x1.

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Answer

Use SQL Server Configuration Manager and check the Server Network Configuration to see which protocols are enabled.

Try checking what network protocols are enabled for the SQL Server instance in the SQL Computer Manager. If the VIA protocol is enabled disable it and then try starting the SQL Server service again.

Users are able to connect to report manager but not able to connect to report server.

You need to register IIS which is happening at the installation of SQL Server Reporting Services. However, there can be situation where you need to re-register it.

1. Open Command Prompt.

2. Change directory as follows:
For 32-bit machines change to:
<WindowsDir>\Microsoft.NET\Framework\<version number>\
For 64-bit machines change to:
<WindowsDir\Microsoft.NET\Framework64\<version number>\

3. Run the command ‘aspnet_regiis.exe -i’ and press enter.

Last Monday evening (2008-June-23) I became a father of a lovely daughter. Bit busy with the little princesses these days. For the older one, it is a huge experience for her. She is helping us to keep her sister happy.

This is my latest article on Dundas reporting. Dundas is an third party tool which you can used in SQL Server Reporting Services.

Read the full article on www.sql-server-performance.com

When SQL Server Snapshot replication is running following errors are occurring

Cannot DROP TABLE <Table Name> it is being referenced by object <View Name>.

Cannot TRUNCATE TABLE <Table Name>because it is being referenced by object <View Name>.

Data changes are not tracked for snapshot replication, each time a snapshot is applied, it completely overwrites the existing data. In the Snapshot replication, it will truncate the tables before applying the data.

If the view which error is referring to , is created with SCHEMA BINDING.

CREATE VIEW statement called SCHEMA BINDING tells SQL Server to check for dependencies and disallow any modifications that would violate them. Therefore it does not allow you to Drop / Truncate tables.

There are two ways to over the issue.

1. Remove the SCHEMA BINDING option from the view. However, for the indexed Views you will not be able to drop the SCHEMA BINDING option.

2. Drop the view before replication and do the replication. After the replication recreate the views.