SqlServer-QA.net

SQL Server - SSIS package transformation performance issues, follow up

SQL Master — Fri, 05 Feb 2010 13:59:00 GMT

To followup from The buffer manager failed a memory allocation call for 20485670 bytes, but was unable to swap out any buffers to relieve memory pressure blog post, here are few findings that I would like to share. When you see that SQL Server instance throws the error as per above blog post, the amount of memory that can trigger this issue varies for different computers. If the package is really handling huge number of rows such as millions of rows during import/export operations, then allocate...(read more)

Opportunity to contribute to Government of India - Unique Identification Project, a Developers initiative.

SQL Master — Fri, 05 Feb 2010 07:56:00 GMT

Government of India has started the process & project of implementing the Unique Identification (UID) project for whole of India. For those who don’t know what it is find more about this initiative and see this PDF attachment too. Another face and lead of this project is Mr. Nandan Nilekani, CEO Infosys and the project has been regularly making public their progress (and it has been good hands). In order to take this further and flexible t he UID team has come up with a unique opportunity to...(read more)

End of Support and lifecycle of Windows operating system

SQL Master — Thu, 04 Feb 2010 09:37:00 GMT

Here is the information about Windows Operating System support life cycle that you need to concentrate: With the end of support (EOS) coming for several products, moving to Windows 7 and utilizing the many features built into the product can help you achieve a more secure and compliant platform for your users, reduce your help desk costs, and increase performance and reliability for your end users. Here are some key dates to remember: March 1, 2010 – Windows 7 RC pre-expiration shut downs begin....(read more)

The buffer manager failed a memory allocation call for 20485670 bytes, but was unable to swap out any buffers to relieve memory pressure

SQL Master — Mon, 01 Feb 2010 15:38:00 GMT

A typical error occurred when a simple SSIS package has been executed to export 500 rows to another SQL instance, not once atleast 3 times! More information on error text from event viewer that has been generated by the 'service; The buffer manager failed a memory allocation call for 20485670 bytes, but was unable to swap out any buffers to relieve memory pressure. 235 buffers were considered and 195 were locked. Either not enough memory is available to the pipeline because not enough are installed,...(read more)

TSQL to get list of operations in active transaction of the current session

SQL Master — Wed, 27 Jan 2010 13:46:00 GMT

SQL Server has a wealth of Dynamic Management Views (DMVs) to get you health status of the platform. All the data will be extracted with a query, set of transactions and many more ways, in this case you had to be careful with transactions, because whenever so much data was flowing back and forth between the application server and the database server, too many things could go wrong. Here is the quick DMV that can get you list of operations in active transaction of the current session declare @xactid...(read more)

SQL Server - when to use stored procedures, a big question and think about plan cache?

SQL Master — Tue, 26 Jan 2010 15:42:00 GMT

Every now and then I see this big question from SQL Server users that when to use a stored procedure? Well there is a second part to this question as well, think about caching of such stored procedures There is no such database management software that can guarentee you flawless query execution over a period of time. Performance problems caused by misuse or mismanagement of plan cache, or inappropriate recompilation, can manifest themselves as simply a decrease in throughput or an increase in query...(read more)

Query to obtain every dependent object of a compiled plan when you pass a valid plan_handle in as a parameter

SQL Master — Tue, 26 Jan 2010 14:53:00 GMT

Every database object will have a sort of dependency between them, such as a stored procedure to run few queries against a table. Similar to this whenever a SP or query is executed then a compiled plan will be generated by the SQL query optimizer, to note about there are two main types of plans in the Object and SQL plan cache stores: compiled plans and execution plans. For every execution context for better optimization such compiled plans are considered valuable memory objects, since they can be...(read more)

SharePoint Usage Survey

SQL Master — Tue, 26 Jan 2010 08:23:00 GMT

Do you work with or use SharePoint © ? If so, we would love to hear how you use it! Your valuable input will help make the next version of SharePoint. Also, by completing the survey you will be entered into a sweepstakes giveaway for a Zune 8 GB (choice of colors) or $100 Visa gift card . This survey will take roughly 20 minutes . (No purchase necessary . All gratuity recipients must be U.S. Citizens or U.S. Permanent Residents with a valid social security number. Sweepstakes ends Feb 28 th 2010...(read more)

A Java application crashes when it connects to SQL Server 2008 by using Java Database Connectivity Driver 2.0

SQL Master — Tue, 26 Jan 2010 08:13:00 GMT

Product: Microsoft SQL Server 2008 Standard & Microsoft SQL Server 2008 Enterprise Editions Notification Contents: New and Major Modifications A Java application crashes when it connects to SQL Server 2008 by using Java Database Connectivity Driver 2.0 http://support.microsoft.com/kb/977305/en-US...(read more)

Customer Ready Guidelines for Security Advisory 979352 Updated (IE6 Vulnerability) - Get your IE6 browsers to upgrade to IE8

SQL Master — Thu, 21 Jan 2010 09:38:00 GMT

Here is the advisory guidelines email from Microsoft Security that I would like to share: Microsoft is aware of public exploit code released that impacts customers using Internet Explorer 6. Customers using Internet Explorer 8 are not affected by currently known attacks and exploits due to the improved security protections in IE8. To help protect our customers, we recommend that all customers immediately upgrade to Internet Explorer 8 . Customers should also consider applying the workarounds and...(read more)

SQL Server 2008 R2 gets official release date!

SQL Master — Wed, 20 Jan 2010 09:19:00 GMT

Here is an exciting news for SQL Server 2008 R2 CTP users, DataPlatform-Insider blog confirms that .. will be listed on Microsoft’s May price list, and will be available by May 2010 . Before we conclude on few points I believe there is a need to get another instalment of 2008 R2 CTP release, inline the release with Visual Studio 2010 and make sure that features of Office 2010 can be utilized. The excitement can become a vapour if there hasn't been a planning from Organizations and Microsoft to fill...(read more)

List of SQL Server 2008 Enterprise Edition Knowledge Base Notification update

SQL Master — Tue, 19 Jan 2010 10:15:00 GMT

The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2008 Enterprise Notification Contents: New and Major Modifications FIX: The LogReader agent unexpectedly consumes all CPU resources when the agent handles a failed connection in a SQL Server 2008 transactional replication http://support.microsoft.com/kb/976991/en-US FIX: Only the first 2000 characters are returned by the fn_get_audit_file() function when you use it to obtain the information of an audit...(read more)

List of SQL Server 2008 Standard Edition Knowledge Base Notification updates

SQL Master — Tue, 19 Jan 2010 10:09:00 GMT

Source: Knowledge Base Product: Microsoft SQL Server 2008 Standard Notification Contents: New and Major Modifications FIX: Connector 1.0 for SAP BI throws an exception when data is being loaded in parallel in a SSIS 2008 package http://support.microsoft.com/kb/975058/en-US FIX: The check phase of the WMI service fails when you set up a SQL Server 2008 cluster http://support.microsoft.com/kb/975374/en-US FIX: You receive error message 8672 when you run a MERGE statement in SQL Server 2008 http://support.microsoft.com/kb/976316/en-US...(read more)

SQL Server 2005 Service Pack 4 - whats your choice?

SQL Master — Thu, 14 Jan 2010 17:43:00 GMT

I believe most of the Enterprises are interested to deploy a software when the service packs are associated with it to fix most of the bugs from RTM release, SQL Server is no excuse for that reason. Its an interesting campaign to see and now it is time to get a release on Service Pack (I guess SQL-ReleaseServices policy states), if you are interested to cast your vote then visit the SQL Server 2005 Service Pack 4 page on Connect site. More the merrier!...(read more)

SQL Server 2008 Service Pack 2 - whats your choice?

SQL Master — Thu, 14 Jan 2010 17:31:00 GMT

I believe most of the Enterprises are interested to deploy a software when the service packs are associated with it to fix most of the bugs from RTM release, SQL Server is no excuse for that reason. Its an interesting campaign to see and now it is time to get a release on Service Pack (I guess SQL-ReleaseServices policy states), if you are interested to cast your vote then visit the SQL Server 2008 Service Pack 2 page on Connect site. More the merrier!...(read more)

SQL Server Management Studio - Obtain Misisng Indexes information and its limitations!

SQL Master — Thu, 14 Jan 2010 07:14:00 GMT

It is a common criteria that using Dynamic Management Views (DMVs) you can easily obtain the system & database state on a SQL instance. It may not be easy for a starter DBA to ensure what kind of DMV is used to obtain information on Recent Expensive Queries, if so for you Activity Monitor is the tool to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server. [This topic is pre-release documentation and is subject to change in future releases....(read more)

Find alternate week day

Madhivanan — Tue, 12 Jan 2010 14:24:00 GMT

My co-worker had a task of finding minimum alternate week day as of today for a given date and day Consider these data Date : 2009-12-22 day : 5 (it denotes Thursday) It means the event was scheduled on 2009-12-22 which should run on every alternate Thursday starting from 2009-12-24 (The first Thursday on or after the scheduled date). The task is finding the minimum of current available day Here is my solution declare @date datetime , @day int select @date = '2009-12-22' , @day = 5 select min ( dateadd...(read more)

Support for SQL Server 2008 RTM ends soon!

SQL Master — Tue, 12 Jan 2010 14:19:00 GMT

Support for SQL Server 2005 SP2 and SQL Server 2008 RTM ends soon!

SQL Master — Tue, 12 Jan 2010 14:10:00 GMT

Its the time again that you should re-visit your database platform to ensure none of the SQL instances fall within SQL Server 2005 Service Pack2 or SQL Server 2008 RTM editions! It is quite common scenario that few of Enterprises may not have gone to upto date service pack on SQL Server 2005 which is SP3 & SQL Server 2008 which is SP1. So here is the reminder for you to ensure to upgrade the 2005 & 2008 instances to upto date service packs, by testing them thoroughly which is a best practice....(read more)

SQL Server Migration Assistant for MySQL v1.0 - CTP1

SQL Master — Tue, 12 Jan 2010 09:45:00 GMT

SQL Server Migration Assistant is one of best used tool when there is a huge effort required to migrate the database from other data sources such as MS Access, Oracle & MySQL. As a part of Microsoft Partner network communication I have received this exciting news about SSMA for MySQL which is available for download freely from SSMA2008-MySQLv1.0_CTP1 which is for SQL Server 2008 version and SSMA2005-MySQLv1.0_CTP1 for SQL Server 2005 version. SSMA for SQL 2008 can be used to migrate data from...(read more)

Unable to cast COM object of type 'Microsoft.Office.Interop.Access.Dao.DBEngineClass' to interface type 'Microsoft.Office.Interop.Access.Dao._DBEngine'

SQL Master — Tue, 12 Jan 2010 08:25:00 GMT

No doubt that the error on subject line will get no where! This is presented when trying to open a Database Properties using SQL Server Managment Studio, going back to history on the SQL instance where this is presented is an upgraded database instance having upto date service packs (SQL Server 2008 SP1) with no cumulative update packages. Further information on full error text is below: Access Object Collector error: Database Unable to cast COM object of type 'Microsoft.Office.Interop.Access.Dao.DBEngineClass'...(read more)

Replace data of one table with data of other table

Madhivanan — Fri, 08 Jan 2010 11:22:00 GMT

One of the questions asked in a forum was about replacing data of one table by the data of another table The solution that the questioner used was having cursor by looping through the source table and replace particular words by other words from another table by matching the words Here is my alternate solution declare @Datatable table ( Field1 nvarchar ( 50 )) insert into @Datatable ( Field1 ) select 'testing for this' union all select 'testing for this to' declare @MatchTable table ( LookFor nvarchar...(read more)

Oracle's acquisition SUN brings cold shoulder to MySQL!

SQL Master — Fri, 01 Jan 2010 11:32:00 GMT

Its a old story that Oracle agreed to acquire Sun Microsystems, which means popular Open Source database application will be affected aka MySQL. As Sun already acquired MySQL in the previous year and a recent marriage of closed source database would get to own MySQL that causes a cold shoulder and database customers will pay higher prices! It is an interesting fact that I found from HelpMySQL site that : If Oracle acquired MySQL on that basis, it would have as much control over MySQL as money can...(read more)

Happy Holidays

Madhivanan — Thu, 31 Dec 2009 09:58:00 GMT

In the Query Analyser, set the Result mode to Text (Press CTRL+T) and run the following code set nocount on select space ( 17 - len ( replicate ( char ( 94 ), no )))+ replicate ( char ( 94 ), no * 2 - 1 ) from ( select top 10 row_number () over ( order by name ) as no from sysobjects ) as t union all select space ( 14 )+ replicate ( char ( 124 ), 5 ) union all select space ( 10 )+ cast ( 0x486170707920486F6C6964617973 as varchar ( 100 ))...(read more)