34 posts from: Microsoft SQL Server Support Blog
How to fix TDS parser to display TDS frames when SQLServer is listening on port other than default 1433
(Indexed 2010-06-30):
If you try to view a netmon trace in Netmon 3.4, you will see TDS traffics are nicely parsed for you. You will see a display similar to this: [Image] The parsing works nicely because SQLServer is listening on default tcp port 1433. But, if your SQLServer is listening on a different port (other than 1433), then these TDS parsing won't work by default. You will see a display similar to this: [Image] To enable the parser to parse TDS traffic for ports other than 1433 (in this case, 63959), we n...(truncated)...
Commonly used 32-bit CPU registers and their purpose
(Indexed 2010-06-30):
While debugging a dump, we commonly see various CPU registers. Each register has different purpose. I am trying to put them together in one place for your easy reference. You can list all the registers in windbg with r command: [Image] Please note that a given register may be used for general purpose at any time. EAX: Arithmetic operations, I/O port access and interrupt call EDX: Arithmetic operations, I/O port access and interrupt call. If you have a multiplication...(truncated)...
How to enable TDS parser to display TDS frames when SQLServer is listening on port other than default 1433
(Indexed 2010-06-30):
If you try to view a netmon trace in Netmon 3.4, you will see TDS traffic is nicely parsed for you. You will see a display similar to this: [Image] The parsing works nicely because SQLServer is listening on default tcp port 1433. But, if your SQLServer is listening on a different port (other than 1433), then these TDS parsing won't work by default. You will see a display similar to this (no frames resolving to TDS): [Image] To enable the parser to parse TDS traffic for ports other than 1433 ...(truncated)...
Step by step N-tier configuration of Sync services for ADO.NET 2.0
(Indexed 2010-04-19):
Recently I have worked on couple of cases where customers were trying to use N-tier configuration of Sync services for ADO.NET on IIS. In this blog we will use IIS and setup N-tier configuration of sync service for ADO.NET version 2.0 which comes as part of Microsoft Sync Framework 1.0. Preparing Environment: We need a development machine that we will use to develop the application, a middle tier server where IIS is installed and configured for WCF services and database server. If y...(truncated)...
How to install .NET Framework 3.5 SP1 on Windows Server 2008 R2 environments
(Indexed 2010-01-09):
The .NET Framework 3.5 SP1 (also referred to as .NET Framework 3.5.1) is a prerequisite for SQL Server 2008. SQL Server 2008 Setup for standalone instance will install the .NET Framework 3.5 SP1 if it is not already installed. However, for Failover Clustering, the .NET Framework 3.5.1 must be installed before the SQL Server 2008 Failover Cluster installation. In Windows Server 2008 R2, the .NET Framework is a feature and installing it is different as compared to other previous operating system. ...(truncated)...
How to install/enable .Net 3.5 SP1 on Windows Server 2008 R2 for SQL Server 2008 and SQL Server 2008 R2
(Indexed 2010-01-09):
The .NET Framework 3.5 SP1 (also referred to as .NET Framework 3.5.1) is a prerequisite for SQL Server 2008. SQL Server 2008 Setup for standalone instance will install the .NET Framework 3.5 SP1 if it is not already installed. In Windows Server 2008 R2, the .NET Framework is a feature and installing it is different as compared to other previous operating system. In previous operating system, one could either download the .NET Framework 3.5.1 install binaries from Microsoft download site or use t...(truncated)...
Creating HTTP endpoint fails with 7850 error.
(Indexed 2010-01-07):
Creating a HTTP endpoint in SQL Server 2005 or SQL Server 2008 may fail with the following error messages:Msg 7850, Level 16, State 1, Line 1The user 'domainmyuser' does not have permission to register endpoint 'training_sql_endpoint' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.Msg 7807, Level 16, State 1, Line 1An error ('0x80070005') occurred while attempting to register the endpoint 'training_sql_endpoint'. The error message is actu...(truncated)...
How to connect to file-based data sources (Microsoft Access , Microsoft Excel and Text files ) from a 64 bit application
(Indexed 2009-12-29):
The Issue:A 64-bit process can load only 64-bit components in it's process boundary. Same is true for a 32-bit process also. So, if your application is 64 bit, you will need a 64-bit provider or driver to connect to Microsoft Access (mdb, accdb) or Microsoft Excel 2010 (xls, xlsx, and xlsb) or text files. Bad news is that there is no 64-bit provider or driver available "yet" to connect to these file-based data sources. Good news is that a 64-bit Provider is heading your way which is currentl...(truncated)...
SQL Server 2005 setup fails when MSXML Core Services 6.0 Service Pack 2 has already been installed
(Indexed 2009-08-28):
There is a known issue with SQL Server setup when MSXML6 component update has been installed on the system. The problem described in KB 968749 http://support.microsoft.com/kb/968749 has raised a lot of concerns by customers. The concerns are related to the fact that the solution is manual and not usable in a Large Enterprise environments. In order to automate MSXML6 component un-install we have created the automatic solution for this issue. However the solution needs to be implemented ...(truncated)...
How to configure SQL server to listen on different ports on different IP addresses?
(Indexed 2009-07-17):
Technorati Tags: SQL PortThe following post describes how you can configure your SQL Server to listen on a different port(s) on different IP addresses that are available on your system. This procedure applies to both SQL server 2005 and SQL server 2008.Case 1: SQL Server is installed in an environment where the IP addresses had not changed since the time it is originally installed.1) Open SQL Server configuration manager.2) Ensure that TCP/IP protocol is enabled[Image] By default, all the IP add...(truncated)...
Dealing with very large SQL Compact database files
(Indexed 2009-07-14):
Working with very large SQL Compact files, you may run into two issues: 1. Manipulating a large SQL Compact database in VS 2008 IDE requires a hotfix http://support.microsoft.com/kb/968436 Error message when you use the Visual Studio 2008 IDE to manipulate a SQL Server Compact 3.5 Database file which is larger than 128 Megabytes: "The database file that is larger than the configured maximum database size" 2. Upgrading a very large database from SQL Compact 3.1 to 3.5 requires another ho...(truncated)...
Performance: Tips for faster download of a large SQL Compact 3.5 database via Replication
(Indexed 2009-06-24):
SQL Compact 3.x Replication initial subscription is resource-intensive and may take significant time on devices. Read the introduction to the SQL Compact Blog for this problem. Introduction (from SQL Compact Blog) The initial subscription is going to bring down lot of data from server to client in merge replication. It means that the requirement for bandwidth, memory, processing power is going to be high for the creation of subscription. However, for devices all these requirements are not ea...(truncated)...
SQL Compact Performance: PostSyncCleanup to turn off UpdateStatistics during initial download of SQL Compact Replication
(Indexed 2009-04-15):
SQL Compact Replication initial subscription may take significant time on devices. The UpdateStatistics operation adds additional overhead to the initial download. Indicators:The Client Agent log may show the start and end times of UpdateStatistics operation. For example:SQLCECA: 04/09/2009-12:21:26 UpdateStatistics Start app=.exeSQLCECA: 04/09/2009-12:27:59 UpdateStatistics End hr=0 app=.exe Workaround: Recent hotfixes for SQL Compact 3.5 and 3.1 (KB article URLs given below) expose a new ...(truncated)...
Class not registered error after restoring an Analysis Services database that uses the SQL Server 2005 SQL Native Client.
(Indexed 2009-04-15):
After restoring an Analysis Services database that uses the SQLNCLI.1 SQL Native Client (SNAC) on a server which has never had SQL Server 2005 installed, an error similar to the following occurs during processing operations:Internal error: The operation terminated unsuccessfully.Internal error: The operation terminated unsuccessfully.OLE DB error: OLE DB or ODBC error: Class not registered.Errors in the high-level relational engine. A connection could not be made to the data source with the Data...(truncated)...
Plan guides (plan freezing) in SQL Server 2005/2008
(Indexed 2009-02-20):
SQL Server 2005 The plan guides were first introduced in SQL Server 2005.You may find detailed information about the plan guides in SQL Server 2005 here: General information http://msdn.microsoft.com/en-us/library/ms187032(SQL.90).aspx Description of sp_create_plan_guide http://msdn.microsoft.com/en-us/library/ms179880(SQL.90).aspx ...(truncated)...
Reporting Services Staaaarrrrrtttt Up
(Indexed 2007-11-09):
I hear the complaint a lot that after a time period of no calls to Reporting Services, the first call to the Reporting Services instance is very slow. This is totally expected because the application pool generally gets spun down and we need to restart everything.If you wire up some basic web service calls, you can see that (at least in my testing) the RS2005 web service takes approximately 30 seconds to start up. The only database work done here is the bare minimum necessary to retrieve the e...(truncated)...
Working around the 4.2 billion tuple calculation limit in Analysis Services 2005 is possible in some cases...
(Indexed 2007-11-06):
An Analysis Services 2005 query against a calculation on a cell may report the error: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." This may be because there are simply more underlying cells in the query than the supported maximum, but in some cases, the query can be rewritten to avoid the error by breaking out parts of a clause into other clauses. Specifically, an IIF() statement apparently considers the total number of cells for calcu...(truncated)...
General network errors (GNEs), Timeout expired, SQL Server not found or Access Denied
(Indexed 2007-09-06):
Many times, the key to solving these errors is a network packet trace. The problem is that these errors are often thrown intermittently. This can obviously make it problematic to capture a network trace. Network Monitor 3 (AKA Netmon3) solves this problem quite nicely. Netmon3 has the built-in ability to capture rolling traces. This allows you to start the trace and then walk away and be sure that you will have captured the error. Here are my steps for configuring Netmon3 for a rolling tra...(truncated)...
Tools of the Trade: Part IV WinDbg & AdPlus (I)
(Indexed 2007-09-04):
In this series of posts, were going to take a look at some of the tools we at Microsoft Developer Support -- Database commonly use, how to use them, and the types of issues they can be used to resolve. In this article, Enamul Khaleque starts down the road of using WinDbg and AdPlus. Future articles will move beyond identification, and discuss step-by-step approach of using WinDbg to solve wide array of issue including but not limited to: memory leaks, high & low CPU hangs, heap corruption, rac...(truncated)...
SQL Server Support FAQ
(Indexed 2007-08-16):
How often should I expect to hear from my Support Engineer?This will vary based on the issue and its impact to your business. If you have specific preferences for frequency of contact, time of day, or method (prefer phone vs. e-mail), please discuss this with your support engineer. My Support Engineer is not available and I need help now.If you need immediate assistance and your engineer is unavailable, please contact the engineers manager. Manager contact information is in the engineers sign...(truncated)...
Tools of the Trade: Part III Kerberos (I)
(Indexed 2007-04-16):
In this series of posts, were going to take a look at some of the tools we at Microsoft Developer Support -- Database commonly use, how to use them, and the types of issues they can be used to resolve. In this article, Adam Saxton starts down the long road of double-hop troubleshooting by providing a gentle introduction to Kerberos, and how to recognize Kerberos+SQL related issues. Future articles will move beyond identification, and discuss the tools and processes we use to troubleshoot such...(truncated)...
Tools of the Trade: Part II The Repro
(Indexed 2007-04-16):
In this series of posts, were going to take a look at some of the tools we at Microsoft Developer Support -- Database commonly use, how to use them, and the types of issues they can be used to resolve. This post might seem like a bit of a stretch. For one thing, its not about a SQL related tool. Thats not such a big deal there are plenty of tools that we use that have applications beyond SQL. You can expect more posts in this series about tools that have applications far beyond troubleshooti...(truncated)...
WHITEPAPER - Online Indexing Operations in SQL Server 2005
(Indexed 2007-03-27):
I wanted to post a quick blog about a new whitepaper from Microsoft.Introduced in Microsoft SQL Server 2005 Enterprise Edition, the online index feature provides a powerful way to perform maintenance operations such as rebuilding or creating indexes in a production system without sacrificing DML concurrency. Users are not blocked from querying and updating the underlying table during the index operation. This white paper is targeted to database administrators who want to use the online index fea...(truncated)...
SQL 2005 SP2 is available now!
(Indexed 2007-02-19):
Finally, after rounds of CTPs (Community Technology Previews) and great feedback from customers, we have officially released Service Pack 2 (SP2) for SQL 2005.Download SP2You can download it by visiting the Microsoft website here.Enhancements in SQL 2005 SP2SP2 provides several new features and improvements. You can learn about them here.Download SQL 2005 SP2 Sample databasesYou can download the sample databases and learn about their enhancements by visiting here.Download Books Online refresh fo...(truncated)...
PART II - Data security enhancements in SQL Server 2005
(Indexed 2007-02-16):
In the previous edition (Data security enhancements in SQL Server 2005) , we talked about data encryption/decryption in general. In this installment, lets talk about key maintenance. It is important to ensure that we can decrypt our data, even in case of user mistake or corruption. For this, we need to protect our keys in our database. We have couple of options to do this. Master key and certificateYou can use the following steps to backup Master key and Certificates Backing Master key...(truncated)...
Tools of the Trade: Part I Universal Data Link Files
(Indexed 2007-02-16):
The other day I was working on an issue with one of our customers. I had been having him run several different tests using a few of the tools that we use on the SQL Developer team on a daily basis. After about the third or fourth diagnostic we ran, he asked me something along the lines of: how are we supposed to know about all these different tools? I didnt have a good answer. Although many of the tools we use are well known, unless a person spends their days troubleshooting, they are unlike...(truncated)...
SQL Server 2000/2005 and DST changes
(Indexed 2007-01-29):
Background In August of 2005 the United States Congress passed the Energy Policy Act, which changes the dates of both the start and end of daylight saving time (DST). When this law goes into effect in 2007, DST will start three weeks earlier (2:00 A.M. on the second Sunday in March) and will end one week later (2:00 A.M. on the first Sunday in November) than what had traditionally occurred. MS products impacted by this changeYou can view a list of all Microsoft products that are impacted by thi...(truncated)...
How can I limit the number of reboots when applying hotfixes and the like to SQL Server and OS?
(Indexed 2006-12-06):
Avoiding reboots after applying a Service Pack or a hotfix to SQL is a big concern to our customers. In SQL 2005, we have made several enhancements to help you avoid a reboot after applying Service Packs and hotfixes. To learn about these enhancements, read our MSDN article here. Happy reading! Posted By: Sanchan Saxena [Image]
We upgraded our servers to Windows 2003 SP1, and now our data-driven application fails with strange errors!
(Indexed 2006-11-15):
Problem Description After upgrading an application server or database server to Windows 2003 SP1, the application encounters errors originating from data-access components. Typically we'll see this when one server in the application server/database server scenario is updated and the second server is left on a previous version of the OS (particularly Windows 2000 Server). There can be a variety of errors involved, the most obvious is along the lines of: [Microsoft][ODBC SQL Server Driver][SQL Se...(truncated)...
Tracking License Information in SQL 2005
(Indexed 2006-11-10):
SQL 2005 no longer tracks licensing (per seat or per processor) via registry entries. SQL 2005 still reads the registry for this information, but the SQL 2005 setup doesnt put licensing information in the registry during setup as in SQL 2000. This is by-design. Hence, when ServerProperty(LicenseType) is run on a SQL 2005 installation, DISABLED is always returned. This could be a problem for large companies who would like a programmatic way to track licensing rather than just having paper licen...(truncated)...
Changing the location of Log files for SQL Server Reporting Services
(Indexed 2006-11-03):
I have found a few occasions where I have needed to change the location of my Reporting Services Logfiles and wanted to share it with you.There are three files you need to modify to change the location of the Reporting Services Logfiles. These files are: c:Program FilesMicrosoft SQL ServerReporting ServicesReportManagerweb.config c:Program FilesMicrosoft SQL ServerReporting ServicesReportServerweb.config c:Program FilesMicrosoft SQL ServerReporting ServicesReportServerBinReportingService...(truncated)...
PART I - Data security enhancements in SQL Server 2005
(Indexed 2006-11-03):
In this two part series, we will talk about data encryption and its implementation in SQL Server 2005. You may encrypt sensitive data like credit card numbers, client information etc. using SQL Server data encryption functionality. Lets take an example to demonstrate the different steps involved in encrypting and decrypting your data. STEP I Setting the stage for encryption. Steps below are needed to be done only once --- Create test databaseCREATE DATABASE encryption_test;GOUSE encryption_tes...(truncated)...
Why is the Initialization checkmark sometimes grayed out in Reporting Services 2005 Configuration Manager?
(Indexed 2006-10-23):
Just a quick post for Reporting Services. If you are using the Standard edition of Reporting Services, the initialization pane is grayed out in Reporting Services Configuration Manager. You should still see the checkmark. This is grayed out because scale out to a web farm is not available in this edition. If the Report Server Windows service is running, it is already initialized. POSTED BY : Cary Russell[Image]
401.1 when trying to access reports from client machines
(Indexed 2006-10-17):
A lot of times, we will see this issue manifest itself where reports run fine from the web server directly, but client machines get three authentication dialog boxes, then fail with a 401.1 error message. The problem here is that Windows XP SP2 and Windows 2003 SP1 implemented a security check that compares the local computer name to the server name in the IIS request. This was done to prevent reflection attacks. If the fully-qualified domain name (FQDN) or host header in the request does no...(truncated)...