Ask The DBAs

Welcome to AskTheDBAs.com.  This is a community site where the SQL Server DBA team at RockSolid SQL will attempt to answer questions posed to them by the community.

Terms of use:  Questions may be rephrased or rewritten to be more general or to pose the question more clearly.  Not all questions will be answered.  Answers provided are general advice not nessecarily related to your original situation and are subject to our disclaimer.  Professional assistance should be sort to determine if the general advice provided is relevant to your specific situation.

NOTE: We are SQL Server DBAs so only questions relating to SQL Server will be answered!

To ask a question please email askthedbas@rocksolidsql.com or tweet the question to @askthedbas. 

Kind Regards

The RockSolid SQL DBA Team

Warm standby on SQL Server 2008 with log shipping from SQL Server 2005?

Question

Can you create a warm standby database on SQL Server 2008 with log shipping from a SQL Server 2005 database?

Answer

Each version of SQL Server has a database version number; for SQL 2000 it is 8, SQL 2005: 9 and SQL 2008: 10. When a database backup that came from a lower SQL Server version is restored, SQL Server will run an upgrade process to bring that older database version up to the database version supported by the new instance. This process is run automatically as part of SQL Server’s recovery process.  The recovery process itself is certain operations that need to happen to ensure the database is in a consistent state for user access (roll-forwards and roll-backs etc).

Placing a database in “Standby/Read Only” mode instructs SQL Server to recover the database after each restore, but also create a special standby file that contains information which will allow this recovery to be undone when it is time to do additional transaction logs restores. 

However unfortunately, once a database has been upgraded it cannot be undone.  This is why if you try and use the “Standby/Read Only” option when restoring a database of an older version SQL Server fails with:

 Msg 3180, Level 16, State 1, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This error message essentially says you cannot bring an older database version online in “Standby/Read Only” mode.

But you can restore transaction logs and log shipping from an older database version to a newer database version when you keep the database offline.  This is because the recovery (and upgrade process) for the database is deferred until the point you do actually bring the database online.  This means you can log ship from an older version of SQL Server to a newer version until the point you bring that database online for user access.

Hope this helps.  All Answers provided are subject to our standard Answers Disclaimer.

Q) How do I add a root node and return columns as elements with FOR XML?

Question

How do I add a root node and return columns as elements with FOR XML?

Answer

FOR XML is a TSQL option which can be added to the SELECT statement to return data in XML format.   A simple example of this is:

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML AUTO

When combined with JOINs FOR XML can be used to return structured XML hierarchies, however we won’t go into those details here.  Instead we will focus on the question.  To add a root node simply add the ROOT(rootnodename) modifier to the FOR XML clause.  For example:

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML AUTO,ROOT('Tables')

To change the Element tag name for each row you can use a table alias or change to using RAW or PATH mode.  For example:

SELECT * FROM INFORMATION_SCHEMA.Tables as [Table] FOR XML AUTO, ROOT('Tables')

OR

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML RAW ('Table'), ROOT('Tables')

And finally, if you want each column to be translated into as an element instead of being an attribute you add the ELEMENTS modifier.  This is useful, for example, if you are outputting XML to be loaded into a DataSet via the DataSets ReadXML method.  For example:

SELECT * FROM INFORMATION_SCHEMA.Tables as [Table] FOR XML AUTO, ELEMENTS, ROOT('Tables')

OR

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML RAW ('Table'), ELEMENTS, ROOT('Tables')

Hope this helps.  All Answers provided are subject to our standard Answers Disclaimer.

Q) Query that returns rows in date range or overlapping date range

Question

I need a query returns all rows between two dates or rows that overlap the date range in any way.

Answer

SELECT *
FROM MyTable
WHERE
(
/*When the start date is before the range but the end date falls in or after the range*/
    (StartDate < @StartDate AND EndDate >= @StartDate)
/*When the StartDate falls in the range.  The end date can be in or out of the range*/
    OR (StartDate >= @StartDate AND StartDate < @EndDate)

)

Hope this helps.  All Answers provided are subject to our standard Answers Disclaimer.

Q) How in SQL Server can I get a date value without any time value?

Question

How in SQL Server can I get a date value without any time value?

Answer

In SQL Server 2008 Microsoft introduced the DATE data type so you can simply do the following:

SELECT CAST(GETDATE() as DATE)

In SQL Server 2005 and below the DATETIME data type always retains a time component.  If you are not interested in time then commonly you set this to 00:00:000.  This will ensure sorting and aggregations on the “date” component are consistent.  A quick and easy way to convert a datetime to a standardized date is to do the following:

SELECT CAST(CONVERT(VARCHAR(50),GETDATE(),112) as DATETIME)

This has the benefit in that it results in a proper date time value (rather than a string) and is also deterministic so can be used in indexed views etc.

All Answers provided are subject to our standard Answers Disclaimer.

Q) When running a Reporting Services report with a Chart in it I get a “Method not found” error. What is the cause of this?

Question

When running a Reporting Services Report I get the following error:

An error has occurred during rendering of chart chart1. Details: Method not found: 'Boolean Dundas.Charting.WebControl.Series.get_PlotAsLine()'. (rsErrorDuringChartRendering)Method not found: 'Boolean Dundas.Charting.WebControl.Series.get_PlotAsLine()'.

What is the cause and fix of this?

Answer

This is likely to have been caused by corruption of the DunasWebChart.dll file located in the ReportServ\bin folder.  What caused this corruption is unclear, it may have resulted from manually copying on older/newer version of the DLL overtop of the correct DLL.To resolve this issue requires replacing the DLL with the correct version.  Reapplying the current SQL Server service pack does not appear to replace this file in our testing.  Instead you may need to backup and then reinstall SQL Server Reporting Services.

NOTE: Reinstalling Reporting Services may cause you to lose current configuration/and or data.  The best method of reinstalling Reporting Services without loss is outside the scope of this answer and further advice should be sought from a qualified professional.

All Answers provided are subject to our standard Answers Disclaimer.

Disclaimer

All answers given are general answers and may or may not apply to your own specific situation.  We recommend that you seek your own professional advice before deciding on the suitability of any answers provided on this site.  If you choose to follow any answers on this site you do so at your own risk.  While all care is taken to ensure answers are accurate, we do not make any guarantees about the suitability of advice and advice given may contain technical inaccuracies or topographical errors.  The answers provided are our own opinions and may differ from advice provided by Microsoft and/or other SQL Server professionals.

We always recommend that testing of an answer first be carried out in a suitable test environment before applying to any production environment.  We also recommend that you have full and complete backups of any database system before carrying out any type of change or modification.  In no event shall we be liable for any special, incidental, indirect, economic or consequential damages or for loss of profit, revenue or data howsoever caused, regardless of whether we could foresee or was advised of the possibility or likelihood of such loss or damage.