SQL Server Hints: November 2008

Tuesday, November 25, 2008

Saturday, November 22, 2008

A list of the bugs that are fixed in SQL Server 2005 Service Pack 2

913939 (http://support.microsoft.com/kb/913939/ )
FIX: The return value of the Member_Unique_Name property in SQL Server 2005 Analysis Services differs from the return value in SQL Server 2000 Analysis Services
917872 (http://support.microsoft.com/kb/917872/ )
FIX: Error message when you try to connect to SQL Server 2005 Analysis Services from Object Explorer: "System.OutOfMemoryException"
917885 (http://support.microsoft.com/kb/917885/ )
Error message when you process a data mining model in SQL Server 2005 Business Intelligence Development Studio: "Memory error: Allocation failure : Not enough storage is available to process this command"
917886 (http://support.microsoft.com/kb/917886/ )
FIX: Error message after you roll back transactions on a table in SQL Server 2005: "Error: 3315, Severity: 21, State: 1"
917888 (http://support.microsoft.com/kb/917888/ )
FIX: Error message when you use a server-side cursor to run a large complex query in SQL Server 2005: "Error: 8623, Severity: 16, State: 1 The query processor ran out of internal resources"
917905 (http://support.microsoft.com/kb/917905/ )
FIX: SQL Server 2005 performance may be slower than SQL Server 2000 performance when you use an API server cursor
917940 (http://support.microsoft.com/kb/917940/ )
FIX: The prediction performance of the Microsoft Naive Bayes algorithm may be slower than the prediction performance of other algorithms for the same mining model in SQL Server 2005 Analysis Services
917971 (http://support.microsoft.com/kb/917971/ )
FIX: You may receive more than 100,000 page faults when you try to back up a SQL Server 2005 database that contains hundreds of files and file groups
918105 (http://support.microsoft.com/kb/918105/ )
FIX: You may receive an error message when you run Database Engine Tuning Advisor by using the dta command-prompt utility in SQL Server 2005
918222 (http://support.microsoft.com/kb/918222/ )
Cumulative hotfix package (build 2153) for SQL Server 2005 is available
918276 (http://support.microsoft.com/kb/918276/ )
FIX: You notice additional random trailing character in values when you retrieve the values from a fixed-size character column or a fixed-size binary column of a table in SQL Server 2005
918529 (http://support.microsoft.com/kb/918529/ )
FIX: You cannot open a SQL Server 2005 report in some versions of Excel after you render the report to an Excel format
918555 (http://support.microsoft.com/kb/918555/ )
FIX: Error message when you use Report Manager in SQL Server 2005 Reporting Services to view a report: "The request failed with an empty response"
918644 (http://support.microsoft.com/kb/918644/ )
FIX: The SQL Server Integration Services (SSIS) service does not start when you install SQL Server 2005 SP1 on a computer that is already running the SSIS service
918735 (http://support.microsoft.com/kb/918735/ )
FIX: You may experience decreased performance when you browse a ragged hierarchy dimension that uses the HideMemberIf property in SQL Server 2005 Analysis Service
918747 (http://support.microsoft.com/kb/918747/ )
FIX: You receive an access violation error message when you try to restore a SQL Server 2005 Analysis Services database
918753 (http://support.microsoft.com/kb/918753/ )
FIX: You receive an access violation error message on a server that is running SQL Server 2005 Analysis Services when you run a MDX query that contains a user-defined function
918756 (http://support.microsoft.com/kb/918756/ )
FIX: The restore operation may fail when you try to restore a database to a new database name in SQL Server 2005 Analysis Services
918757 (http://support.microsoft.com/kb/918757/ )
FIX: The value of a cell is not updated correctly when you use multiple UPDATE CUBE statements inside a single transaction in SQL Server 2005 Analysis Services
918833 (http://support.microsoft.com/kb/918833/ )
FIX: A cell writeback operation in SQL Server 2005 Analysis Services takes a significantly longer time than a cell writeback operation in SQL Server 2000 Analysis Services
918882 (http://support.microsoft.com/kb/918882/ )
FIX: A query plan is not cached in SQL Server 2005 when the text of the hint is a large object
919144 (http://support.microsoft.com/kb/919144/ )
FIX: An access violation may occur when you enable the "lazy schema validation" option for any linked server and when you run a distributed partitioned view query in SQL Server 2005
919243 (http://support.microsoft.com/kb/919243/ )
FIX: Some rows in the Text Data column are always displayed for a trace that you create by using SQL Server Profiler in SQL Server 2005
919478 (http://support.microsoft.com/kb/919478/ )
FIX: "Select All" may not be available when you try to preview a report in Report Designer after you install SQL Server 2005 Service Pack 1
919538 (http://support.microsoft.com/kb/919538/ )
FIX: SQL Server 2005 Reporting Services cannot generate an error message in a report when the data source of the report calls a stored procedure that contains the RAISERROR statement
919556 (http://support.microsoft.com/kb/919556/ )
FIX: You cannot select the only available value from a drop-down list of a muti-value parameter in Report Manager in Microsoft SQL Server 2005 Reporting Services
919611 (http://support.microsoft.com/kb/919611/ )
FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1
919636 (http://support.microsoft.com/kb/919636/ )
FIX: Memory usage of the compiled query plan may unexpectedly increase in SQL Server 2005
919731 (http://support.microsoft.com/kb/919731/ )
FIX: SQL Server Analysis Services 2005 system performance may be slow after you use the ADOMD.NET data provider to run an MDX query that returns a large result set from a cube
919775 (http://support.microsoft.com/kb/919775/ )
FIX: The BULK INSERT statement may not return any errors when you try to import data from a text file to a table by using the BULK INSERT statement in Microsoft SQL Server 2005
919839 (http://support.microsoft.com/kb/919839/ )
FIX: The Distribution Agent may not apply transactions when you use transactional replication with a published Oracle table and the table contains a column that uses the number data type in SQL Server 2005
919905 (http://support.microsoft.com/kb/919905/ )
FIX: A query may take longer to run in SQL Server 2005 SP1 than it takes to run in the original release version of SQL Server 2005 or in SQL Server 2000
919929 (http://support.microsoft.com/kb/919929/ )
FIX: Error message when the Replication Merge Agent runs in SQL Server 2005: "Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402"
919949 (http://support.microsoft.com/kb/919949/ )
FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005
919955 (http://support.microsoft.com/kb/919955/ )
FIX: You may receive incorrect results when you run a Multidimensional Expressions (MDX) query that involves a calculated member and the autoexist functionality in SQL Server 2005 Analysis Services
919956 (http://support.microsoft.com/kb/919956/ )
FIX: Error message when you execute an MDX query that calls a VBA function in SQL Server 2005 Service Pack 1: "PFE_ASSEMBLY_NOT_SAFETOPREPARE"
919957 (http://support.microsoft.com/kb/919957/ )
FIX: Some cells return the NULL value instead of returning the actual value when you query a dimension that contains a parent/child hierarchy in a SQL Server 2005 Analysis Services cube
920203 (http://support.microsoft.com/kb/920203/ )
FIX: You receive inconsistent return values when you use Microsoft SQL Server 2005 Analysis Services to perform several queries for the same measure in a local cube
920205 (http://support.microsoft.com/kb/920205/ )
FIX: A memory leak may occur when you run SQL Server 2005 Integration Services packages that are stored in the SSIS Package store
920206 (http://support.microsoft.com/kb/920206/ )
FIX: System performance may be slow when an application submits many queries against a SQL Server 2005 database that uses simple parameterization
920340 (http://support.microsoft.com/kb/920340/ )
FIX: A query may return incorrect results when you run the query against a SQL Server 2005 Analysis Services cube
920346 (http://support.microsoft.com/kb/920346/ )
FIX: SQL Server 2005 may overestimate the cardinality of the JOIN operator when a SQL Server 2005 query contains a join predicate that is a multicolumn predicate
920347 (http://support.microsoft.com/kb/920347/ )
FIX: The SQL Server 2005 query optimizer may incorrectly estimate the cardinality for a query that has a predicate that contains an index union alternative
920794 (http://support.microsoft.com/kb/920794/ )
FIX: The size of the e-mail message is very large when you use Database Mail in SQL Server 2005 to send query results to users
920882 (http://support.microsoft.com/kb/920882/ )
FIX: You cannot deploy a SQL Server 2005 Integration Services package if you use an XML configuration file whose file name contains an uppercase letter
920974 (http://support.microsoft.com/kb/920974/ )
FIX: SQL Server 2005 treats an identity column in a view as an ordinary int column when the compatibility level of the database is set to 80
921003 (http://support.microsoft.com/kb/921003/ )
FIX: You may receive an error message when you manually define a Back Up Database task in SQL Server 2005 to back up the transaction log
921106 (http://support.microsoft.com/kb/921106/ )
FIX: You receive an error message when you try to create a differential database backup in SQL Server 2005
921292 (http://support.microsoft.com/kb/921292/ )
FIX: You may receive a "0x80004005 (E_FAIL)" error message when you use a range rowset in OLE DB for OLAP in a SQL Server 2005 Analysis Services-based application
921294 (http://support.microsoft.com/kb/921294/ )
FIX: You may receive an error message when you try to process a measure group under a low-memory condition in SQL Server 2005 Analysis Services
921395 (http://support.microsoft.com/kb/921395/ )
FIX: The color and the background image may not appear when you try to display a report in HTML format in Report Manager in SQL Server 2005 Reporting Services
921405 (http://support.microsoft.com/kb/921405/ )
FIX: An image placeholder that contains a red X appears when you run a report in SQL Server 2005 Reporting Services
921536 (http://support.microsoft.com/kb/921536/ )
FIX: A handled access violation may occur in the CValSwitch::GetDataX function when you run a complex query in SQL Server 2005
922063 (http://support.microsoft.com/kb/922063/ )
FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1
922296 (http://support.microsoft.com/kb/922296/ )
FIX: You may receive an access violation error message when you run a query against a linked server in SQL Server 2005
922438 (http://support.microsoft.com/kb/922438/ )
FIX: A query may take a long time to compile when the query contains several JOIN clauses against a SQL Server 2005 database
922527 (http://support.microsoft.com/kb/922527/ )
FIX: Error message when you schedule some SQL Server 2005 Integration Services packages to run as jobs: "Package has been cancelled"
922577 (http://support.microsoft.com/kb/922577/ )
FIX: You may receive "UnavailableException" and "LogonFailedException" error messages when you perform a stress test on the report server in SQL Server 2005 Reporting Services
922578 (http://support.microsoft.com/kb/922578/ )
FIX: In SQL Server 2005, the sp_altermessage stored procedure does not suppress system error messages that are logged in the SQL Server error log and in the Application log
922594 (http://support.microsoft.com/kb/922594/ )
FIX: Error message when you use SQL Server 2005: "High priority system task thread Operating system error Exception 0xAE encountered"
922638 (http://support.microsoft.com/kb/922638/ )
FIX: An EXCEPTION_STACK_OVERFLOW exception may occur when you try to run a Transact-SQL query in SQL Server 2005
922798 (http://support.microsoft.com/kb/922798/ )
FIX: A member property is returned in an unexpected data type when you call the "IMDDataset::GetAxisRowset" interface in SQL Server 2005 Analysis Services
922804 (http://support.microsoft.com/kb/922804/ )
FIX: After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database
922853 (http://support.microsoft.com/kb/922853/ )
FIX: You may receive an error message when you try to use the Aggregation Design Wizard to design aggregations in SQL Server 2005 Analysis Services
923008 (http://support.microsoft.com/kb/923008/ )
FIX: Error message when you use the Bcp.exe utility to import data into a table from a text file in SQL Server 2005: "The memory could not be written"
923296 (http://support.microsoft.com/kb/923296/ )
FIX: Log Reader Agent fails, and an assertion error message is logged when you use transactional replication in SQL Server 2005
923300 (http://support.microsoft.com/kb/923300/ )
FIX: The calculated members are not returned in subsequence levels when you run an XML for Analysis (XMLA) script to retrieve child members at a level that is in a hierarchy in SQL Server 2005 Analysis Services
923301 (http://support.microsoft.com/kb/923301/ )
FIX: SQL Server 2005 Analysis Services continually generates minidump files when you try to deploy a new cube
923304 (http://support.microsoft.com/kb/923304/ )
FIX: Error message when you run a MDX query after you install the cumulative hotfix package for SQL Server 2005 Analysis Services for Itanium-based operating systems (build 9.0.2153): "Internal error: An unexpected exception occured"
923475 (http://support.microsoft.com/kb/923475/ )
FIX: The cells may be incorrectly updated when you execute multiple UPDATE CUBE statements in a single transaction in SQL Server 2005 Analysis Services
923476 (http://support.microsoft.com/kb/923476/ )
FIX: The query may never finish when you run a Multidimensional Expressions (MDX) query against a parent/child dimension in SQL Server 2005 Analysis Services
923477 (http://support.microsoft.com/kb/923477/ )
FIX: Unfiltered results may be generated when you use a drillthrough action on a dimension that is stored in the ROLAP storage mode in SQL Server 2005 Analysis Services
923478 (http://support.microsoft.com/kb/923478/ )
FIX: You receive an error message when you deploy a cube dimension that has a dimension description in Business Intelligence Development Studio
923605 (http://support.microsoft.com/kb/923605/ )
FIX: A deadlock occurs and a query never finishes when you run the query on a computer that is running SQL Server 2005 and has multiple processors
923623 (http://support.microsoft.com/kb/923623/ )
FIX: You receive an error message, when you use Report Builder after you install SQL Server 2005 Reporting Services SP1: “Short time pattern does not include hours and minutes”
923624 (http://support.microsoft.com/kb/923624/ )
FIX: Error message when you run an application against SQL Server 2005 that uses many unique user logins or performs many user login impersonations: "insufficient system memory to run this query"
923849 (http://support.microsoft.com/kb/923849/ )
FIX: When you run a query that references a partitioned table in SQL Server 2005, query performance may decrease
924012 (http://support.microsoft.com/kb/924012/ )
FIX: Error message when you try to process a cube that has a relationship between two tables in a data source view that contains more than four keys in SQL Server 2005 Analysis Services: "000042ac"
924264 (http://support.microsoft.com/kb/924264/ )
FIX: The metadata of the Description object of a Key Performance Indicator appears in the default language after you define a translation for the Description object in SQL Server 2005 Business Intelligence Development Studio
924291 (http://support.microsoft.com/kb/924291/ )
FIX: Error message when you execute a user-defined function in SQL Server 2005: "Invalid length parameter passed to the SUBSTRING function" or "A transport-level error has occurred when receiving results from the server"
924344 (http://support.microsoft.com/kb/924344/ )
FIX: You may receive incorrect results when you use the bulk copy program command-line utility to import data in SQL Server 2005
924345 (http://support.microsoft.com/kb/924345/ )
FIX: Error message when you try to open a file attachment in a message that is generated by the sp_send_dbmail procedure in SQL Server 2005: "This file is not in a recognizable format"
924346 (http://support.microsoft.com/kb/924346/ )
FIX: An access violation may intermittently occur when you run a SQL Server Integration Services package in SQL Server 2005
924601 (http://support.microsoft.com/kb/924601/ )
FIX: A partitioned table may generate an inefficient query plan when you run a Transact-SQL query that uses the Top n clause on a partitioned table in SQL Server 2005
924807 (http://support.microsoft.com/kb/924807/ )
FIX: The restore operation may take a long time to finish when you restore a database in SQL Server 2005
924808 (http://support.microsoft.com/kb/924808/ )
FIX: You receive an error message, or you obtain an incorrect result when you query data in a partitioned table that does not have a clustered index in SQL Server 2005
924939 (http://support.microsoft.com/kb/924939/ )
An update is available for SQL Server 2005 Analysis Services that adds partition information to the existing schema that is returned by the DISCOVER_PARTITION_INFO rowset
924954 (http://support.microsoft.com/kb/924954/ )
FIX: Error message when you use a table-valued function (TVF) together with the CROSS APPLY operator in a query in SQL Server 2005: "There is insufficient system memory to run this query"
925057 (http://support.microsoft.com/kb/925057/ )
FIX: Color images in a report appear in grayscale after you export the report to a PDF file in SQL Server 2005 Reporting Services
925058 (http://support.microsoft.com/kb/925058/ )
FIX: Error message when you create a new subscription to a report in SQL Server 2005 Reporting Services: "Error: Object expected"
925135 (http://support.microsoft.com/kb/925135/ )
FIX: An empty string is replicated as a NULL value when you synchronize a table to a SQL Server 2005 Compact Edition subscriber
925153 (http://support.microsoft.com/kb/925153/ )
FIX: You may receive different date values for each row when you use the getdate function within a case statement in SQL Server 2005
925227 (http://support.microsoft.com/kb/925227/ )
FIX: Error message when you call the SQLTables function against an instance of SQL Server 2005: "Invalid cursor state (0)"
925277 (http://support.microsoft.com/kb/925277/ )
FIX: You may experience very large growth increments of a principal database after you manually fail over a database mirroring session in SQL Server 2005
925278 (http://support.microsoft.com/kb/925278/ )
FIX: A query may crash, and you may receive an error message when an MDX query uses the Filter function at the start to slice a partition in SQL Server 2005 Analysis Services: "An unexpected exception occurred"
925335 (http://support.microsoft.com/kb/925335/ )
FIX: Error message when you use a label after a Transact-SQL query in SQL Server 2005: "Incorrect syntax near 'X'"
925387 (http://support.microsoft.com/kb/925387/ )
FIX: Error message when you use a unary operator in a parent/child dimension in a SQL Server 2005 Analysis Services cube: "Record ID is not valid"
925566 (http://support.microsoft.com/kb/925566/ )
FIX: Error message when you run a query against a cube that contains linked objects in SQL Server 2005 Analysis Services: "Internal error An unexpected error occurred"
925744 (http://support.microsoft.com/kb/925744/ )
FIX: Error message when you try to use a SQL Server authenticated login to log on to an instance of SQL Server 2005: "Logon error: 18456"
925754 (http://support.microsoft.com/kb/925754/ )
FIX: You may experience stack corruption and SQL Server 2005 Analysis Services may stop responding when you run SQL Server 2005 Analysis Services on a Dual-Core Intel Itanium 2 Processor 9000 Series-based computer
925843 (http://support.microsoft.com/kb/925843/ )
FIX: Error message when you use the SQL Server Native Client provider to run queries between two linked servers in SQL Server 2005 Service Pack 1: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"
926106 (http://support.microsoft.com/kb/926106/ )
FIX: You receive an error message when you use the Print Preview option on a large report in SQL Server 2005 Reporting Services
926235 (http://support.microsoft.com/kb/926235/ )
FIX: In SQL Server 2005 Analysis Services, the query may not return all the expected rows when you use the TopCount function in an MDX query
926240 (http://support.microsoft.com/kb/926240/ )
FIX: SQL Server 2005 may stop responding when you use the SqlBulkCopy class to import data from another data source
926285 (http://support.microsoft.com/kb/926285/ )
Fix: Error message when you convert a column from the varbinary(max) data type to the XML data type in SQL Server 2005: "Msg 6322, Level 16, State 1, Line 2 Too many attributes or namespace definitions"
926335 (http://support.microsoft.com/kb/926335/ )
FIX: Error message when you trace the Audit Database Management event and you try to bring a database online in SQL Server 2005: “Msg 942, Level 14, State 4, Line 1”
926422 (http://support.microsoft.com/kb/926422/ )
FIX: You receive error messages when you use SQL Server Management Studio or SQL Server Business Intelligence Development Studio after you install Microsoft Office Excel 2007 on a computer that has SQL Server 2005 Analysis Services installed
926493 (http://support.microsoft.com/kb/926493/ )
FIX: Error message when you restore a transaction-log backup that is generated in SQL Server 2000 SP4 to an instance of SQL Server 2005: "Msg 3456, Level 16, State 1, Line 1. Could not redo log record"
926515 (http://support.microsoft.com/kb/926515/ )
FIX: You may receive unexpected results when you run an MDX query in SQL Server 2005 Analysis Services
926611 (http://support.microsoft.com/kb/926611/ )
FIX: SQL Server 2005 may not send a message notification that is based on the specific string in the forwarded event when a computer that is running SQL Server 2000 forwards an event to a computer that is running SQL Server 2005
926612 (http://support.microsoft.com/kb/926612/ )
FIX: SQL Server Agent does not send an alert quickly or does not send an alert when you use an alert of the SQL Server event alert type in SQL Server 2005
926613 (http://support.microsoft.com/kb/926613/ )
FIX: You may receive incorrect results when you query a table that is published in a transactional replication in SQL Server 2005
926773 (http://support.microsoft.com/kb/926773/ )
FIX: Error message when you run a query that uses a fast forward-only cursor in SQL Server 2005: "Query processor could not produce a query plan because of the hints defined in this query"
926774 (http://support.microsoft.com/kb/926774/ )
FIX: You may receive incorrect results when you try to aggregate a non-additive measure on the parent-child dimension in SQL Server 2005 Analysis Services
926775 (http://support.microsoft.com/kb/926775/ )
FIX: The sum total of a column contains incorrect data when you browse or query a cube in SQL Server 2005 Analysis Services
926962 (http://support.microsoft.com/kb/926962/ )
FIX: The user can see all the data in a cube, and SQL Server 2005 Analysis Services may crash
926967 (http://support.microsoft.com/kb/926967/ )
FIX: You obtain an incorrect result when you run a Multidimensional Expressions (MDX) query in SQL Server 2005 Service Pack 1
927109 (http://support.microsoft.com/kb/927109/ )
FIX: Many query notifications are sent to the data warehouse when proactive caching is enabled in SQL Server 2005 Analysis Services
927179 (http://support.microsoft.com/kb/927179/ )
FIX: Processing is never completed and cannot be stopped when you process a cube in SQL Server 2005 Analysis Services
927289 (http://support.microsoft.com/kb/927289/ )
FIX: Updates to the SQL Server Mobile subscriber may not be reflected in the SQL Server 2005 merge publication
927290 (http://support.microsoft.com/kb/927290/ )
FIX: Error message when you open a report in Report Manager in SQL Server 2005 Reporting Services: "An error has occurred during report processing. There is already an open DataReader associated with this Command which must be closed first"
927455 (http://support.microsoft.com/kb/927455/ )
FIX: The query runs much slower than you expect when you use the InStr function in a calculated member of an MDX query in SQL Server 2005 Analysis Services
927643 (http://support.microsoft.com/kb/927643/ )
FIX: Some search results are missing when you perform a full-text search operation on a Windows SharePoint Services 2.0 site after you upgrade to SQL Server 2005
927859 (http://support.microsoft.com/kb/927859/ )
FIX: You may experience decreased performance when you run a query that contains the CREATE SET statement against a ragged dimension in SQL Server 2005 Analysis Services
928083 (http://support.microsoft.com/kb/928083/ )
FIX: You may receive an error message when you run a CLR stored procedure or CLR function that uses a context connection in SQL Server 2005
928243 (http://support.microsoft.com/kb/928243/ )
FIX: When you run a SQL Server 2005 Integration Services package, the package may stop responding
928372 (http://support.microsoft.com/kb/928372/ )
FIX: Error message when you use a synonym for a stored procedure in SQL Server 2005: "A severe error occurred on the current command"
928394 (http://support.microsoft.com/kb/928394/ )
FIX: The changes are not reflected in the publication database after you reinitialize the subscriptions in SQL Server 2005
928537 (http://support.microsoft.com/kb/928537/ )
FIX: The full-text index population for the indexed view is very slow in SQL Server 2005
928538 (http://support.microsoft.com/kb/928538/ )
FIX: The Report Server Windows service may stop responding, and you may receive the following error message in SQL Server 2005 Reporting Services: "An internal error occurred on the report server"
928539 (http://support.microsoft.com/kb/928539/ )
FIX: An access violation is logged in the SQL Server Errorlog file when you run a query that uses a plan guide in SQL Server 2005
928789 (http://support.microsoft.com/kb/928789/ )
FIX: Error message in the database mail log when you try to use the sp_send_dbmail stored procedure to send an e-mail in SQL Server 2005: "Invalid XML message format received on the ExternalMailQueue"
928792 (http://support.microsoft.com/kb/928792/ )
FIX: The output rows of the Aggregate transformation are divided into multiple rows in SQL Server 2005 Integration Services
929179 (http://support.microsoft.com/kb/929179/ )
FIX: A memory leak may occur every time that you synchronize a SQL Server Mobile subscriber in SQL Server 2005
929240 (http://support.microsoft.com/kb/929240/ )
FIX: I/O requests that are generated by the checkpoint process may cause I/O bottlenecks if the I/O subsystem is not fast enough to sustain the IO requests in SQL Server 2005
929278 (http://support.microsoft.com/kb/929278/ )
FIX: SQL Server 2005 may not perform histogram amendments when you use trace flags 2389 and 2390
930283 (http://support.microsoft.com/kb/930283/ )
FIX: You receive error 1456 when you add a witness to a database mirroring session and the database name is the same as an existing database mirroring session in SQL Server 2005
930284 (http://support.microsoft.com/kb/930284/ )
FIX: You receive error 1456 when you try to add a witness to a DBM session in SQL Server 2005
930409 (http://support.microsoft.com/kb/930409/ )
FIX: Error message when you run a Multidimensional Expressions (MDX) query that uses a calculated member in SQL Server 2005 Analysis Services: "Internal error An unexpected exception occurred"
930410 (http://support.microsoft.com/kb/930410/ )
You may unexpectedly receive a NULL value when you run a Multidimensional Expressions (MDX) query in SQL Server 2005 Analysis Services
929376 (http://support.microsoft.com/kb/929376/ )
FIX: A "17187" error message may be logged in the Errorlog file when an instance of SQL Server 2005 is under a heavy load
922852 (http://support.microsoft.com/kb/922852/ )
FIX: You may experience high CPU utilization across multiple processors when you run a SQL Server 2005 Analysis Services query against a large partition or lots of partitions
917035 (http://support.microsoft.com/kb/917035/ )
FIX: Query performance may decrease after SQL Server 2005 has been running for a while
918832 (http://support.microsoft.com/kb/918832/ )
FIX: An inefficient or incorrect SQL query is generated when you try to use SQL Server 2005 to browse a ROLAP dimension
919193 (http://support.microsoft.com/kb/919193/ )
FIX: A forward-only cursor may be implicitly converted to a keyset cursor in SQL Server 2005
921293 (http://support.microsoft.com/kb/921293/ )
FIX: The description for the Dimension field is not set in the local cube file when you use the CREATE LOCAL CUBE statement in SQL Server 2005
921295 (http://support.microsoft.com/kb/921295/ )
FIX: You may receive an incorrect result when you try to run a Multidimensional Expressions (MDX) query by using SQL Server 2005
922579 (http://support.microsoft.com/kb/922579/ )
FIX: The operation may take longer than you expect when you run a warm query to obtain information from the Microsoft Search service in SQL Server 2005
923473 (http://support.microsoft.com/kb/923473/ )
FIX: SQL Server 2005 Analysis Services may stop responding when you try to process a ROLAP dimension two times
924686 (http://support.microsoft.com/kb/924686/ )
FIX: The database mirroring session may remain in the synchronizing state and may stop responding when a database failover occurs in SQL Server 2005
926024 (http://support.microsoft.com/kb/926024/ )
FIX: The query performance is very slow when you use a fast forward-only cursor to run a query in SQL Server 2005
929404 (http://support.microsoft.com/kb/929404/ )
FIX: Error message when you perform a transaction log backup operation and another data backup operation in parallel in SQL Server 2005: "Error 3633"

-------------------------------------------------------
http://support.microsoft.com/?kbid=921896

Tuesday, November 18, 2008

SQL Server Schema Binding and Indexed Views

http://www.mssqltips.com/tip.asp?tip=1610

Temporary tables

Temporary Tables

CREATE TABLE #Yaks
(YakID int,
YakName char(30)
)

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are created in tempdb

select name from tempdb..sysobjects where name like '#yak%'

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:

DECLARE @TibetanYaks TABLE
(YakID int,YakName char(30)
)

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakNameFROM dbo.Yaks WHERE YakType = 'Tibetan'

Table variables don't need to be dropped when you are done with them.

Which to Use
- If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.

- If you need to create indexes on it then you must use a temporary table.

- When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

----------------------------------------------------------------------------------
http://www.sqlteam.com/article/temporary-tables

Monday, November 17, 2008

Designing INSTEAD OF Triggers

CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)

---------------------------------------------------

CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

---------------------------------------------------

CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)

----------------------------------------------------

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END

Sunday, November 16, 2008

Considerations for All Types of Replication

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

NOT FOR REPLICATION - SQL 2005

In some cases, it is desirable for user activity in a replication topology to be treated differently from agent activity. For example, if a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table, it might not be required to enforce the same constraint when the row is inserted by a replication agent at the Subscriber. The NOT FOR REPLICATION option allows you to specify that the following database objects are treated differently when a replication agent performs an operation:

Foreign key constraints
The foreign key constraint is not enforced when a replication agent performs an insert, update, or delete operation.

Check constraints
The check constraint is not enforced when a replication agent performs an insert, update, or delete operation.

Identity columns
The identity column value is not incremented when a replication agent performs an insert operation.

Triggers
The trigger is not executed when a replication agent performs an insert, update, or delete operation.

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

hints about SQL server 2005 from pdfslibrary.com

create SNAPSHOT database

CREATE DATABASE andrew_test_snapshot_250206_1800 ON ( NAME = N'andrew_test_datafile', FILENAME = N'E:\DB_Snapshots\andrew_ test_snapshot_2502061800.snap' ) AS SNAPSHOT OF andrew_test;

------------------------------------------------------

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/

Wednesday, November 12, 2008

using xml (SQL 2005)

-- RAW mode

USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW
-- ELEMENTS with RAW mode
USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW, ELEMENTS

-- Named element in RAW mode

SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW('Product')
-- root element
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW, ROOT('Products')

-- Join in RAW mode

SELECT Category.Name CategoryName, Product.ProductID, Product.Name
FROM Production.ProductSubCategory Category
JOIN Production.Product Product
ON Product.ProductSubCategoryID = Category.ProductSubCategoryID
ORDER BY Category.Name, Product.ProductID
FOR XML RAW

-- AUTO Mode

SELECT ProductID, Name, ListPrice
FROM Production.Product Product
FOR XML AUTO

-- Join in AUTO mode

SELECT Category.Name CategoryName, Product.ProductID, Product.Name
FROM Production.ProductSubCategory Category
JOIN Production.Product Product
ON Product.ProductSubCategoryID = Category.ProductSubCategoryID
ORDER BY Category.Name, Product.ProductID
FOR XML AUTO

-- Nested XML with TYPE

SELECT ProductID,
Name, ListPrice,
(SELECT ReviewerName, Comments
FROM Production.ProductReview ProductReview
WHERE ProductReview.ProductID = Product.ProductID
FOR XML AUTO, ELEMENTS, TYPE)
FROM Production.Product Product
WHERE ProductID > 795
FOR XML AUTO

-- EXPLICIT Mode

SELECT 1 AS TAG,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1]
FROM Production.Product
FOR XML EXPLICIT

-- Nesting in EXPLICIT Mode

SELECT 1 AS TAG,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1!ProductName!Element],
NULL AS [Review!2!Reviewer],
NULL AS [Review!2]
FROM Production.Product
WHERE ProductID > 795
UNION ALL
SELECT 2,
1,
p.ProductID,
NULL,
r.ReviewerName AS [Review!2!Reviewer],
r.Comments AS [Review!2]
FROM Production.ProductReview r
JOIN Production.Product p
ON p.ProductID = r.ProductID
WHERE p.ProductID > 795
ORDER BY [Product!1!ProductID]
FOR XML EXPLICIT

-- PATH mode

SELECT ProductID AS "@ProductID",
Name AS "*",
Size AS "Description/@Size",
Color AS "Description/text()"
FROM Production.Product
ORDER BY Name
FOR XML PATH('Product')

Tuesday, November 11, 2008

User-Defined Table Types (SQL 2008)

In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.

To create a user-defined table type, use the CREATE TYPE statement. To ensure that the data in a user-defined table type meets specific requirements, you can create unique constraints and primary keys on the user-defined table type.

Restrictions
User-defined table types have the following restrictions:

- A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

- Alias types based on a user-defined table type The [NOT FOR REPLICATION] option is not allowed.

- CHECK constraints require a computed column to be persisted.

- The primary key on computed columns must be PERSISTED and NOT NULL.

- A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type. (SQL Server enforces any UNIQUE or PRIMARY KEY constraint by using an index.)

- A DEFAULT value cannot be specified in the definition of a user-defined table type.

- The user-defined table type definition cannot be modified after it is created.

- User-defined functions cannot be called within the definition of computed columns of a user-defined table type.

Creating a user-defined table type
USE AdventureWorks;GO
/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE
(
LocationName VARCHAR(50), CostRate INT
);GO

Creating a User-Defined Type (SQL 2005)

To create a user-defined type (UDT) capable of being installed in Microsoft SQL Server 2005, you must first create a class in one of the supported Microsoft .NET Framework programming languages, such as Microsoft Visual C# or Microsoft Visual Basic, which conforms to the specifications for creating UDTs. The class can then be compiled as a dynamic-link library (DLL), which can be loaded in SQL Server 2005. You can also create and deploy UDTs using Microsoft Visual Studio. The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server 2005. The CLR can be enabled by using the sp_configure system stored procedure, as shown in the following Transact-SQL statements:

sp_configure 'clr enabled', 1
Reconfigure

The following code listing defines the Point UDT

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
IsByteOrdered=true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
private bool is_Null;
private Int32 _x;
private Int32 _y;
public bool IsNull
{
get
{
return (is_Null);
}
}
public static Point Null
{
get
{
Point pt = new Point();
pt.is_Null = true;
return pt;
}
}
// Use StringBuilder to provide string representation of UDT.
public override string ToString()
{
// Since InvokeIfReceiverIsNull defaults to 'true'
// this test is unneccesary if Point is only being called
// from SQL.
if (this.IsNull)
return "NULL";
else
{
StringBuilder builder = new StringBuilder();
builder.Append(_x);
builder.Append(",");
builder.Append(_y);
return builder.ToString();
}
}
[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
// With OnNullCall=false, this check is unnecessary if
// Point only called from SQL.
if (s.IsNull)
return Null;
// Parse input string to separate out points.
Point pt = new Point();
string[] xy = s.Value.Split(",".ToCharArray());
pt.X = Int32.Parse(xy[0]);
pt.Y = Int32.Parse(xy[1]);
// Call ValidatePoint to enforce validation
// for string conversions.
if (!pt.ValidatePoint())
throw new ArgumentException("Invalid XY coordinate values.");
return pt;
}
// X and Y coordinates exposed as properties.
public Int32 X
{
get
{
return this._x;
}
// Call ValidatePoint to ensure valid range of Point values.
set
{
Int32 temp = _x;
_x = value;
if (!ValidatePoint())
{
_x = temp;
throw new ArgumentException("Invalid X coordinate value.");
}
}
}
public Int32 Y
{
get
{
return this._y;
}
set
{
Int32 temp = _y;
_y = value;
if (!ValidatePoint())
{
_y = temp;
throw new ArgumentException("Invalid Y coordinate value.");
}
}
}
// Validation method to enforce valid X and Y values.
private bool ValidatePoint()
{
// Allow only zero or positive integers for X and Y coordinates.
if ((_x >= 0) && (_y >= 0))
{
return true;
}
else
{
return false;
}
}
// Distance from 0 to Point method.
[SqlMethod(OnNullCall = false)]
public Double Distance()
{
return DistanceFromXY(0, 0);
}
// Distance from Point to the specified point method.
[SqlMethod(OnNullCall = false)]
public Double DistanceFrom(Point pFrom)
{
return DistanceFromXY(pFrom.X, pFrom.Y);
}
// Distance from Point to the specified x and y values method.
[SqlMethod(OnNullCall = false)]
public Double DistanceFromXY(Int32 iX, Int32 iY)
{
return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
}
}

CREATE RULE (SQL 2005)

Creates an object called a rule. When bound to a column or an alias data type, a rule specifies the acceptable values that can be inserted into that column.

1-Creating a rule with a range

The following example creates a rule that restricts the range of integers inserted into the column or columns to which this rule is bound.

CREATE RULE range_rule
AS
@range>= $1000 AND @range <$20000;

----------------------------------------------

2- Creating a rule with a list

The following example creates a rule that restricts the actual values entered into the column or columns (to which this rule is bound) to only those listed in the rule.

CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877');

----------------------------------------------

3- Creating a rule with a pattern

The following example creates a rule to follow a pattern of any two characters followed by a hyphen (-), any number of characters or no characters, and ending with an integer from 0 through 9.

CREATE RULE pattern_rule
AS
@value LIKE '__-%[0-9]'

Sunday, November 9, 2008

SET ANSI_NULLS (SQL 2005)

-- Create table t1 and insert values.

CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO

-- Print message and perform SELECT statements.

PRINT 'Testing default setting'
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- SET ANSI_NULLS to ON and test.

PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- SET ANSI_NULLS to OFF and test.

PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- Drop table t1.

DROP TABLE t1

SQL Server Hints