Write to text file from MS-SQL.

CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS

DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file

execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1

execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1

IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID

EXECUTE @OLEResult = sp_OADestroy @FS

SQL Server 2008 Upgrade Plan for DBAs

SQL Server 2008 Upgrade Plan for DBAs

How to remove special characters from a string in MS SQL Server (T-SQL)

If you only have to remove a few specific special characters from a string value, the REPLACE function can be used, e.g.:

select replace( replace( stringvalue, '-', ''), ',', '')

For a more general solution, the user-defined function below may be used to filter out all special characters from a string value.

---

-- Removes special characters from a string value.

-- All characters except 0-9, a-z and A-Z are removed and

-- the remaining characters are returned.

-- Author: Christian d'Heureuse, www.source-code.biz

create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)

with schemabinding

begin

if @s is null

return null

declare @s2 varchar(256)

set @s2 = ''

declare @l int

set @l = len(@s)

declare @p int

set @p = 1

while @p <= @l begin

declare @c int

set @c = ascii(substring(@s, @p, 1))

if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122

set @s2 = @s2 + char(@c)

set @p = @p + 1

end

if len(@s2) = 0

return null

return @s2

end

---

Example of how to use the function:

select dbo.RemoveSpecialChars('abc-123+ABC')

Result:

abc123ABC

How to repair SQL database when Error 8903 occurs in MS SQL Server 2000 database

In the MS SQL Server databases, the IAM (Index Allocation Map), GAM (Global Allocation Map), SGAM (Shared GAM) are different types of pages that are used to store the records on the hard disks. These pages combine together in an extent, which is used to hold the metadata of the databases. A lot of times due to the mismatching in these files, the database gets corrupt and does not work properly. In such cases, you should try to repair MDF file using appropriate measures. However, if the problem is not addressed, then there is a strong case of using a third-party SQL repair software.

Consider a situation in which you are facing the following error message while working on an MS SQL Server 2000 database:

“Extent E_ID in database ID DB_ID is allocated in both GAM GAM_P_ID and SGAM SGAM_P_ID.”

Cause:

The error has occurred because of corruption in the extents due to the irregularities in the IAM, GAM, and SGAM pages.

Resolution:

To overcome such corruption issues, you should perform the following methods ideally in the given order:

• Fix the hardware corruption issues: As is seen in such cases, the main reason for corruption is the malfunctioning of the hardware parts due to various reasons. You should try to minimize its repercussions using the following measures:

  • Check the Windows application logs and the SQL Server error logs to see whether the error is a consequence of hardware failure.

  • Run the hardware diagnostics and fix the error(s), if any.

  • You should swap the hardware components to isolate the exact error of corruption and try to repair the issue.

  • If the problem is persisting for a long time, then check if write-caching is enabled on the disks. If yes, then contact your hardware vendor to replace the disk.

  • In the end, you can try to convert to a new system be reinstalling the operating system after formatting the hard disks.

• Restore from database: If the hardware parts are perfectly in place, then you should restore database from the backup, if it is clean and updated.

• Execute DBCC CHECKDB: If nothing works out, then you should run DBCC CHECKDB with the repair clause to repair MDF file.

If the issue is still not resolved, then you should use a third-party SQL repairsoftware to repair corrupt SQL database. With the help of these MDF file repairtools, you can get all the data without inflicting any damages.

The most recommended MS SQL repair software is Stellar Phoenix SQL Data Recovery, which repairs SQL database created in MS SQL Server 2008, 2005, 2000, and 7.0. Compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000, this .MDF repair utility repairs and restores collations, NDF files, tables, stored procedures, defaults, triggers, etc.

I/O Logical Check Failure Cause SQL Server Database Corruption

Like other databases and files, Microsoft SQL Server database may also come across various corruption issues. The corruption can take place due to various reasons, including the internal database or SQL Server application issues and system related problems. One major system related problem, which can cause MDF (Master Database File) corruption, is I/O logical check failure. In such situations, the database becomes totally inaccessible and you come across severe data loss situations. In order to get your mission critical data back, you need to repair and restore the corrupt database using SQL database recovery solutions.

The I/O logical check failure can be indicated by various error messages, which includes the following one:

“Error 823 I/O error detected during at offset in file ''”

The above error may also occur when SQL Server come across other system errors. After this behavior, SQL Server database become totally unusable. In order to sort out this problem, you need to fix it through SQL recovery methods.

Cause

If the read Windows API (Application Programing Interface) call or writer API call for SQL Server database is successful, but some particular logical checks are unsuccessful. In such cases, error 823 occurs, which resembles the following:

“2003-09-05 16:51:18.90 spid17 Error: 823, Severity: 24, State: 2 2003-09-05 16:51:18.90 spid17 I/O error (torn page) detected during read at offset 0x00000094004000 in file 'F:\SQLData\mydb.MDF'..”

Resolution

In order to work around this behavior of Microsoft SQL Server database and perform SQL Server recovery, you should first run DBCC CHECKDB utility on the affected or damaged MDF file. Before you try to fix any system related issue with MS SQL Server, you should first check the database and sort out the problems. If DBCC CHECKDB statement fails to detect or fix any error, please check the Application Event Log of Microsoft SQL Server.

If the above method fails to find and fix any errors in Microsoft SQL Server, you need to restore the database from a valid and recent backup to get your valuable data back. In case backup is not available, you have to opt for third-party applications to repair and restore the corrupted MDF file.

The MS SQL recovery applications are particularly designed to methodically scan entire database and extract all inaccessible data from it. The software have read-only approach and simple graphical user interface to ensure safe and easy recovery.

Rebuilding MSDB in SQL Server 2005

http://rip747.wordpress.com/2008/05/26/rebulding-msdb-in-sql-server-2005/

Damaged Filegroups Cause Database Corruption in MS SQL Server

Microsoft SQL Server stores all your valuable data in MDF (Master Database File) in the form of tables. The data is organized using database index, which makes it easy to locate and access stored data. Sometimes, the database grows unexpectedly and performance of the database gets decreased dramatically. To control the database growth, you can use native options of Microsoft SQL Server, such as 'Filegroup'. If you do not take any such actions, the database may crash and get damaged. This behavior causes serious data loss and requiresSQL database recovery to be sorted out, if there is no backup in place.

Microsoft SQL Server offers various methods to help supporting the database growth. From the database planning point of view, one of the most important topic that must be covered is the 'Filegroup'. It can be considered as a logical data storage unit to hold the SQL Server database objects, which maps to multiple files or file system file.

Every Microsoft SQL Server database contains a filegroup by default, which is known as primary filegroup. It is used to support the user and system objects of the MDF database. You can allocate additional mass storage devices to Microsoft SQL Server to support the assumed performance and needed growth. You can create new files and filegroups on new hard drive. The database objects can be transferred to newly created filegroups for spreading I/O (Input/Output) over the additional hard drives.

Under some circumstances, the filegroups may get damaged and stored database files become inaccessible. In a practical scenario of this problem, you may come across the below error message when you try to access a Microsoft SQL Server database:

“Unable to process index I_NAME of table O_NAME because filegroup F_NAME is invalid.”

After the above problem, you can not gain access of MS SQL Server database and stored data. It leads to critical data loss situations and you need to recover SQL database to get your mission-critical data back.

A complete backup is the greatest help in such cases. However, if backup is not valid or available, SQL Server recovery software can help you to extract data from the database. The applications are particularly designed to scan whole database effectively and restore it in all corruption scenarios. They offer safe SQL recoverywith read-only and non-destructive conduct.

Getting Started with Data Bars in SQL Server 2008 R2 Reporting Services

Getting Started with Data Bars in SQL Server 2008 R2 Reporting Services

Reading XML documents using LINQ to XML

Reading XML documents using LINQ to XML

Collect and store historical performance counter data using SQL Server

Collect and store historical performance counter data using SQL Server

Helping people kick bad SQL Server habits

Helping people kick bad SQL Server habits

How to Protect Your Microsoft SQL Server Database

When the server or database system of MSSQL Server2008 or 2005crash, it’s very difficult to recover the original data backup with MS SQL code, but its not impossible. MS SQL Server database unlike other databases such as FoxPro can copy data files; the former must have a backup strategy in order to secure data backup recovery. So take an appropriate backup & recovery operation to recover corrupted data, the data of MS SQL Server 2008 and 2005 can be safely protected.

Restore vs. Recovery

Restore and recovery are two different concepts, but in the database recovery process is also closely linked together. The difference between restore and recovery is given below:

Restore is equivalent to rebuild the part or whole of the database from a backup. It can not change the state of database, such as offline and online.

Recovery of database means it restore the database from offline to online state for users to use.

In Microsoft SQL Server database “Recovery” the database will no longer be able to operate the “Restore”.

A good backup and recovery plan for Microsoft SQL Server should cover the following steps:

1. Need data protection.

2. Daily / weekly / monthly backup plan.

3. Documented verify and the results of the test.

4. Documented the recovery process.

From Microsoft SQL Server 2005, MS SQL Server provides a lot of smart backup methods to recover damaged SQL server database. The main purpose of backup SQL Server database is to reduce the data loss, which asking for zero data loss, and need shorter interval of time to backup the data.

If you are facing data loss problem of SQL server database due to any reasons and not maintain your backup then you have to need smart third party SQL server recovery software to recover SQL database.

Using MSDTC As a Non-clustered Resource Cause SQL Server Database Corruption

In Microsoft SQL Server, MSDTC (Microsoft Distributed Transaction Coordinator) allows the client applications to include various data sources in a single transaction. These transaction coordinates committing distributed transactions throughout all servers, which are listed in transaction. MSDTC is a transaction manager that is actually a Windows process. Under some situations, this transaction manager may cause various critical problems in SQL Server, if it is improperly configured, which cause database corruption. In such cases, you need to go for SQL data recovery solutions to get your mission-critical data back.

Microsoft supports running the MSDTC services only on the cluster nodes as clustered resource. You are not recommended to run the MSDTC in a stand-alone mode on cluster. It is not even supported in most of the cases. Using the MSDTC as non-clustered resource on Microsoft Windows cluster can cause several problems. Such configuration is quite problematic as the transaction can not be orphaned and you might come across database corruption in any cluster failover takes place.

Rebuild MSDTC on MS SQL Server failover clustered server:

In order to rebuild the failed MSDTC and perform SQL server recovery, go through the underwritten steps:

• Switch all the SQL Server services to the Manual mode.

• Close the Control Panel and reboot server. It clears memory of the DLLs, which are loaded by SQL Server services. Else, such services remain in server memory and hold the locks on Server Server system resources.

• Remove the following registry keys: HKEY_CLASSES_ROOT\CID HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet0\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet1\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet2\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet3\Services\MSDTC

• Delete Z:\MSDTCLog directory.

• Remove all DLL files.

• Run Dtcsetup.exe on first node.

• Run Dtcsetup.exe on second node, if prompted.

• After clicking OK button on second node, you can click OK button on first node, and reboot your system.

The above method can rebuild MSDTC in most of the cases and fix related problems. However, if the problem is caused by database corruption due to use of MSDTC on a non-clustered resource, it does not work. In such critical situations, an updated backup proves great help. However, if backup is not current, MS SQL repair becomes need of hour.

SQL recovery is potential through powerful and advanced third-party applications. Such tools are powerful enough to methodically scan entire database and extract all inaccessible data from it. They are quite safe and easy to use.

How to Setup Row-Level Security for SQL Server

How to Setup Row-Level Security for SQL Server

Fixing Error 2570 Post MDF File Corruption

Software and hardware malfunctions are the primary cause for any file – including MDF – damage. Few common corruption scenarios for a MDF file damage are improper system shutdown, virus infection, simultaneous modifications, header corruption, application malfunction, flood, fire, and more. To overcome either of the situations, the database user can restore the data from an updated backup. However, in case of no backup or backup falling short of restore the required MDF file, the user needs to perform MDF database repair. While MDF file corrupted due to physical damages need assistance of rich-experienced database repair technicians, logically damaged MDF files can be repaired using an effective third-party database repair application.

To illustrate the above case, consider a practical situation where you have a MDF file that contains your MS SQL Server 2005 database. The database was mounting systematically, until you encountered the below error message:

“Warning: Page P_ID, slot S_ID in Object O_ID Index I_ID Column COLUMN_NAME value COLUMN_VALUE is out of range for data type "DATATYPE". Update column to a legal value.”

After the above error message appeared, the database became unmountable and its records unreadable. In addition, the same error message appeared every time you try to access the MDF file. This is a severity level 16 error message.

Cause:

The above error message occurs after MDF file corruption due to logical or physical reason.

Resolution:

To get rid of the above MDF file corruption error message and to access the database saved in the MDF files, you will need to follow the below steps:

• Try and perform restoration from an updated backup.

• In case of no database backup, you will need to take below actions:

1. In case of any physical damage, you will need to change the physically corrupted system component. In order to identify the corrupted component, you will need to check the application and system logs.

2. In case of logical damage of MDF file, you will need to use a third-party MDF File Repair application. These MDF recovery applications use effective repair techniques to repair corrupted MDF files after any logical damage. The original file, however, remains intact during the repair process.

Removing Transaction Log Files in SQL Server Suspends Database Mirroring

In Microsoft SQL Server 2008, you can configure the database mirroring between two database. It improves database reliability and prevent data loss situations, if one database gets damaged due to any reason. In case of failure, you can restore the copy of your database from mirror database. However, if you unintentionally remove the transaction log files on SQL Server database, database mirroring session is broken by principle server. After this behavior, you can not restore the data from mirror server if principle server fails. It causes database corruption and require SQL Database Recovery to be fixed, if backup is not available.

As a practical instance of this problem, consider the underwritten scenario:

In Microsoft SQL Server 2008 SP1 (Service Pack 1) or Microsoft SQL Server 2008, you configure mirroring between two databases.

You run ALTER DATABASE command along with REMOVE FILE clause for removing the transaction log files of SQL Server database on principal server.

In the above scenario, database mirroring session is aborted and you can not resume it. At the same time, you get the below assertion:

“Location: "logmgr.cpp":3350 Expression: headLFCB->lfcb_startOffset <>GetSize () * (ULONGLONG)PAGESIZE”

Furthermore, the below error message is logged in SQL Server Event Log:

“ spid23s A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.”

Cause

The problem takes place due to wrong log space tracking on mirror database. It can also occur due to Microsoft SQL Server database (MDF file) corruption.

Resolution

If the problem is caused by wrong log space tracking, download latest update on SQL Server and install it. On the other hand, if MDF corruption is causing the problem, you need to go for SQL Server Recovery to sort out the problem.

SQL Recovery is potential through advanced and effective third-party tools, known as MS SQL Recovery software. The applications employ advanced scanning techniques to ensure absolute and quick recovery in all cases of SQL Serverdatabase corruption.

Rectifying Error 2522 in SQL Server

SQL Server 2005 provides various options to control the growing size of the database. One such option is to create a filegroup. TA filegroup is a logical storage unit which is used to contain the database objects that reference to a file system file or multiple files. In a filegroup, additional disk drives are allocated to support the estimated growth of the database. Then, the additional files are shifted to these filegroups to spread the I/O over additional disks. Having said that, these filegroups-supported databases can get corrupt due to various reasons. In such cases, you should consider taking appropriate corrective measures to resolve the issue. However, if the measures are not sufficient then you should use a third-party SQL repair software to repair the corrupted database. Consider a scenario wherein you have received the following error message when you open the SQL Server 2005 database: “Unable to process index I_NAME of table O_NAME because filegroup F_NAME is invalid.” This message suggests that the index cannot be checked as one of the filegroup IDs does not exist, which is stored in the metadata of the index. Cause:

The root cause of this error message is that the filegroup IDs stored in the metadata for an index do not exist so that index can not be checked. It may be possible that the database is corrupt. Resolution:

To resolve this error message, you should perform the following methods preferably in the given order: Check for hardware issue: You should run hardware diagnostics and check the error logs to ascertain whether this error has resulted because of any hardware issues. Restore from backup: You should check if the backup of the database is updated and clean. If yes, then restore the database from its backup. Run DBCC CHECKDB: You should run DBCC CHECKDB firstly without using the repair clause and then with the repair clause after determining the extent of the corruption. The aforementioned methods will be able to resolve the issue. However, if the error is not solved then you should use a third-party MS SQL repair software to repair SQL database. Such tools are able to perform repair SQL database without overwriting the original content. Stellar Phoenix SQL Recovery software is an ms sql recovery software that enables you to perform SQL repair of the databases created in MS SQL Server 2000, 2005, and 2008. It is able to repair various components of corrupted SQL databases such as rules, defaults, stored procedures, triggers, etc. This SQL repair tool also recovers database constraints such as primary key, unique key, foreign key, etc. It is compatible with Windows 7, Vista, Server 2003, XP, and 2000.

How to find the length of a string in SQL Server

Determining a string's length is extremely useful in SQL Server! The best way to determine a length of a string in SQL Server's transact SQL is to use the built in LEN function. This function returns a string's length. The LEN function may be used in SELECT queries, for example to find rows where colums are a particular length: SELECT * FROM t_Books WHERE LEN(t_Books.BookTitle) BETWEEN 10 AND 50 Note that LEN only works with string datatypes, and it exludes any trailing blank spaces that may be present in a field. For columns containing byte data, consider using the DATALENGTH function.

How to select left and right substrings in SQL Server

Do you need to select part of a substring in SQL Server's Transact SQL? There are three useful functions for doing this - Left, Right andSubstring. The Transact SQL Left and Right functions are used to truncate strings after a specified number of characters from either the left side or the right side of the string, respectively. Both the Left and Right functions need two arguments to be supplied - the string to be truncated, and the length of the string that should be retained. To show an example of the Left function, the SQL statement below will return "sq": SELECT Left('sql server', 2)

The example below uses the Left function to create a four character DataCode column from the first four characters of the DataDescription column of the FinancialData database table: SELECT DataID, DataDescription,Left(DataDescription, 4) AS DataCode FROM FinancialData ORDER BY DataID By contrast, the Right Transact SQL function truncates a string from the right side of that string. This example creates a DataCode column that is based on the final three characters of the DataDescription column: SELECT DataID, DataDescription, Right(DataDescription, 3) AS DataCode FROM FinancialData ORDER BY DataID To return part of a string, use the Substring function.

• SQL Server Documentation Tool Automatically document all of your SQL Server databases with this handy utility

Rectifying the Error 8999 in SQL Server

The tempdb database is one of the system databases in SQL Server. It is used to store the temporary tables, table variables, etc. The tempdb is recreated every time SQL Server is started. This way, the system always starts with a fresh copy of the tempdb database. However, sometimes the tempdb gets corrupt because of various issues such as power outages, virus infections, human errors, abrupt system shutdown, etc. In such cases, you should take appropriate corrective measures to resolve the issue. However, if you are not able to do so then you should use a third-party MS SQL repair tool to repair SQL database. Consider a scenario wherein you are performing allocation checks on the tempdb database. But the process terminates abruptly and an error message is displayed, that is: “Database tempdb allocation errors prevent further CHECKNAME processing.” Cause:

The most possible reason for such behavior is that there were allocation errors in the tempdb database. And as the tempdb database cannot be taken offline, the operation is cancelled and, hence, the error message is displayed. Resolution:

To resolve this error message, there are various things that you can do. They are:

Look for hardware failure: Run hardware diagnostics and check whether the error is because of any hardware issue. You can also check the error logs or swap the hardware components to ascertain this point.

Restore from backup: Replace the corrupted database with its clean and updated backup.

Use DBCC CHECKDB: Use the DBCC CHECKDB command without any repair clause to know the extent of corruption. After determining it, you should run DBCC CHECKDB again with the recommended repair clause. However, if you are unable to sort the issue even after using the aforementioned methods then you should use a third-party SQL repair software to repair SQL database. Such read-only tools are able to repair the corrupted databases without overwriting the original data. SQL Database Recovery Software is an MS SQL repair tool that enables you to repair SQL database. It is capable of performing MDF repair of corrupted SQL databases created in SQL Server 2000, 2005, and 2008. This tool is capable of repairing various database components such as tables, queries, reports, triggers, views, stored procedures, views, rules, etc. It is also able to preserve the database relationships such as primary key, unique key, checks, etc. This MS SQL repair tool is compatible with Windows 7, Vista, Server 2003, XP, and 2000.

DBCC CHECKDB Displays 8929 Error Message

MS SQL Server database users use DBCC CHECKDB command to repair their logically damaged SQL databases. This command can be used with different 'repair' parameters, such as, repair_allow_data_loss, repair_fast, and repair_rebuild. While the command used with the above parameters performs maximum repair in most database corruption cases, there might be few instances, wherein the command fails to resolve the corruption issues. In such cases, the output of the command is an error message, which states the exact cause for failure of DBCC CHECKDB command. After the occurrence of DBCC CHECKDB command failure error message, the database remains unrepairable, further resulting in inaccessibility of saved records. To overcome such SQL database corruption scenarios and successfully access the records saved in the database (in case no backup is available), the user needs to opt for a commercial SQL Database Repair application. Below is one such error message that appears when the user DBCC CHECKDB command fails to repair a corrupted MS SQL database: “Server: Msg 8929, Level 16, State 1, Line 1 Object ID 2: Errors found in text ID 852426752 owned by data record identified by RID = (1:110:19) id = 1925581898 and indid = 2. Server: Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 2. The text, ntext, or image node at page (1:438), slot 3, text ID 852426752 is referenced by page (1:71), slot 4, but was not seen in the scan. DBCC results for 'PDP'. DBCC results for 'sysobjects'. There are 652 rows in 11 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 480 rows in 18 pages for object 'sysindexes'. CHECKDB found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2). DBCC results for 'syscolumns'...” After the above severity level 16 error message appears, the database records become inaccessible. Cause: The primary reason for the occurrence of above error message is severe corruption in MS SQL database. Resolution: To resolve the above error and mount your database, you will need to repair the database using a commercial SQL Database Repair software. A MS SQL Repair utility assures maximum repair of SQL database, irrespective of its level of corruption. SQL Recovery Software is a complete solution for repairing logically damaged MS SQL Server 2008, 2005, and 2000 database. It is competent enough to recover almost all database components after repairing. The read-only tool SQL Repair does not make any change in the original database. It can be installed on Windows 7, Vista, 2008, XP, 2003, and 2000 operating systems.

Fixing “Errors found in text ID TEXT_ID...” Error Message

DBCC CHECKDB is a repair command used by MS SQL Server users to detect and repair their corrupt databases. The command can be used with various parameters, such as, repair_rebuild and repair_allow_data_loss. Although the command proves helpful in resolving all kinds of logical corruption issues, there are certain problems that persists even after using the above command. Such situations occur when the database is badly corrupted. An easy way to access data in case of severe corruption is by restoring the database from an updated backup. However, in case of no backup available or backup falling short of restore the required database, you will need to use a powerful third-party MDF File Repair application. Consider a real-time situation to describe the above problem. You attempt to mount your SQL database and see some consistency issues. To resolve these issues, when you run DBCC CHECKDB command, you encounter the below error message: “Server: Msg 8929, Level 16, State 1, Line 1 Object ID 1979154096: Errors found in text ID 379493089280 owned by data record identified by RID = (1:647611:14) PK1 = 1300583. Server: Msg 8929, Level 16, State 1, Line 1 Object ID 1979154096: Errors found in text ID 379544338432 owned by data record identified by RID = (1:648136:32) PK1 = 1300903. … Table error: Object ID 1979154096, index ID 255, page (1:558593). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1. Server: Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 0, text ID 552407334912 is referenced by page (1:1387313), slot 46, but was not seen in the scan. Server: Msg 8965, Level 16, State 1, Line 1 … CHECKDB found 0 allocation errors and 33 consistency errors in table 'QTI_RESULT_DATA' (object ID 1979154096). CHECKDB found 0 allocation errors and 33 consistency errors in database 'bb_bb60'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (bb_bb60 ).” After the above error message gets displayed on the screen, the database records become inaccessible. Cause: The root cause for DBCC CHECKDB command to display the above error message is severe corruption of MS SQL database. Resolution: Below are some guidelines that help you to overcome the above error message: Check if any system component is physically damaged. To do so, check the system, application, and SQL Server logs. Identify the component in the logs and swap it with a new component.

Run DBCC CHECKDB command with repair_allow_data_loss parameter. However, doing so might erase some of the critical data saved in your database.

DBCC CHECKTABLE Displays 8966 and 8944 Error Messages

Records stored in a MS SQL Server table become inaccessible when it gets corrupted due to record header corruption, metadata structure damage, application malfunction, and concurrent modification. To surmount table corruption, MS SQL Server provides an inbuilt table repair command known as 'DBCC CHECKTABLE'. The command checks the logical and physical integrity of the corrupted table and the indexed view. While in most cases, the command is successful in repairing corrupted tables, there are a few instances where the command falls short in repairing the MS SQL Server table. In such situations, the command displays an error message, exhibiting the exact reason for its failure. The table remains corrupt after DBCC CHECKTABLE failure message. To overcome such table corruption, an ideal option is to restore the table from an updated backup. However, in the absence of or in case of deficient table backup, the user needs to use a powerful third-party MDF Repair utility.

To explain the above scenario in detail, consider a practical case where you encounter the below error message while attempting to repair your corrupt table using DBCC CHECKTABLE command:

“Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:24) with latch type SH. sysindexes failed. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 0, page (1:24), row 11. Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. Values are 95 and 193. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 0, page (1:24), row 11. Test (GetRecType (pRec) >= 0 && GetRecType (pRec) <= LAST_REC_TYPE) failed. Values are 14 and 12. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2). DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

The error message appears when the repair command is unable to repair the database table.

Cause:

Severe corruption in table due to record header damage.

Resolution:

To repair your table when DBCC CHECKDB fails, you will need to opt for a commercial MDF File Repairapplication. Such MDF Recovery tools use advanced repair solutions to repair the table and restore it at user-specified location.

Network communications mechanisms for SQL Server

Network communications mechanisms for SQL Server

Fixing “cannot be placed on a page of type PAGE_TYPE” Error Message

A table in MS SQL contains numerous rows, each containing a different set of information. These rows are stored in multiple partitions, which are structured in heap or B-tree format. The actual information saved in these rows is stored in the leaf nodes (text data node). Each leaf node has its parent node that keeps critical information related to that node. However, there are some situations when the text data node points to a wrong parent node or the parent node does not even exist. These situations primarily occur due to corruption of SQL database. To overcome such corruption cases, the user will need to restore the data from an updated backup. However, if no backup is available ,then s/he can opt for effective SQL Database Repair tool to repair the corrupted database.

The below error message might pop up while accessing the database table:

“Table error: The text, ntext, or image node at page P_ID, slot S_ID, text ID TEXT_ID has type NODE_TYPE. It cannot be placed on a page of type PAGE_TYPE.”

The error message has severity level 23.

Cause

The error message occur because:

The parent node could not be found.
The text node is pointing to a wrong parent node.

This occurs when the SQL Server database is damaged either physically or logically.

Resolution

Underneath are some guidelines that help you to surmount the above error message:

For all kinds of possible hardware problems, you should first check system logs, application logs, and SQL Server logs. In case you find any physically damaged system component, then you need to swap it with a new system component.
Restore the data from an updated backup.
For all kinds of logical crash scenarios, you should execute DBCC CHECKDB command with correct repair clause.
While the above command repairs a logically corrupted database, it may also result in loss of data. To avoid such situations, you should use effective third-partysql database repair application to resolve logical corruption. These SQL Repair tools employ powerful, yet safe repair techniques to repair the database.

Moving your database to the cloud with SQL Azure - Part 1

Problem
There has been lots of buzz about cloud computing lately and looking at the benefits it provides (in terms of cost savings, high availability, scalability (scale up/down) etc.) it is now evident that cloud computing is the future for next generation applications. Many of tomorrow's applications will be designed and hosted in the cloud. Microsoft realizes this potential and provides a cloud computing solution with Windows Azure. Windows Azure platform, which is hosted inside Microsoft data centers, offers several services which you can leverage while developing your application if you target them for the cloud. One of them is Microsoft SQL Azure, it's a cloud based relational database service built on Microsoft SQL Server technologies. In this tip series, I am going to show how you can start creating databases and database objects on the cloud with SQL Azure.

Solution
Recently Microsoft launched Microsoft SQL Azure (formerly called SQL Data Services (SDS)) Oct CTP, which provides relational database capabilities on the cloud and is based on SQL Server technologies. You use familiar T-SQL commands to work with your database though not all T-SQL statements are supported with SQL Azure. You can find a complete list of supported/unsupported T-SQL statements here.

SQL Azures enables you to store structured, semi-structured, and unstructured data in your cloud database. Though the current SQL Azure Oct CTP version offers relational database service only, some of the features are not supported yet, but would likely be part of future CTPs or RTM, (refer to the SQL Azure site on MSDN for the latest updates). In the future it’s going to support more services for example Reporting Services, Analysis Services etc.

Turning the wheel...
Currently Windows Azure and SQL Azure are in its Community Technology Preview (CTP) version (this example is based on the October CTP). So to work on this, first you need to login with your hotmail or Live ID and you need to register on http://go.microsoft.com/fwlink/?LinkID=149681&clcid=0x09; you will get a form similar to the one shown below.

On submitting the above form you will get an email from Microsoft with an invitation code. Then you need to go to https://sql.azure.com/, login with your hotmail or Live ID and enter your invitation code in the form as shown below:

Once you enter correct invitation code, you will be prompted to accept terms of usage, you can accept by clicking on the "I Accept" button after reading it.

Then you will see the next screen which will show you a default project created for you. You need to click on the Project Name (in current case it is "SDS-Only CTP Project") in the grid to move to the next step..

Now that default project is already created, you need to create a server (with Oct CTP you can create one server and five databases in it including the default master database). For that you need to specify the Administrator user name and password. Please keep the Administrator user name and password safe as it will be required when you want to connect to the server from SQL Server Management Studio (SSMS) as an administrator. The server name will be automatically generated and it will be shown to you on the next screen.

Here you can see a server has been created for you and the user name you specified in last screen is the administrator on the server. By default you will notice a master database is created. On the bottom right you can see there are two options ("Create Database" and "Drop Database"). Click on "Create Database", specify the name of the database and its maximum size.

With the current CTP, two editions of databases can be created, first Web edition which has maximum 1 GB size limit and second Business edition which has maximum 10 GB size limit. More details on these different editions and pricing can be found here.

With the "Create Database" option, I created a database called AdventuresWorksOnCloud which you can see here. To get the connection string, which you will need to connect from your applications, you can select the radio button before the database and then click on "Connection Strings" option on the bottom left.

Okay so far we created a server on the cloud and a database in it. Now I am going to show how you can connect to your newly created database.

Launch SSMS by going to Start -> SQL Server 2008 -> SQL Server Management Studio. You will prompted to enter your connection details, click cancel on it because the current CTP does not allow you to connect from SSMS Object Explorer, if you try connecting from there you will get an exception error like this "Invalid object name 'sys.configurations'. (Microsoft SQL Server, Error: 208)". So you need to click the "New Query" button and then specify the server name (which was created above), the user name and password as shown below.

Further as the current CTP does not allow using the USE statement to switch to different database you need to specify which database you want to connect to. For that click on the "Options>>" button and in the "Connect to database" combo-box specify the database name.

Now click on the "Connect" button and you will get an exception error similar to this.

This is because, for security reason, by default all hosts are denied access. So you must specify the IP address ranges of the hosts which will be accessing your server and databases by going to the SQL Azure web portal as shown below:

Once the Firewall Settings are set you should be able to connect and use familiar T-SQL commands.

Ref: http://www.mssqltips.com