SQL Server Q&A

The computer has become everyone’s go-to for storing any kind of data. While various kinds of virus scans are used to keep the computer free of viruses, you have to accept the fact that the computer can go down due to many other reasons. Keeping a backup of the data is very important. The reasons behind why this is important are that the computer can suddenly stop working at times or that the memory of the computer becomes full eventually and you will need to free it. In fact, a full memory drive slows down the computer considerably and hence, you should try to keep the computer free with Home Backup Solutions.

?Before using the Home Backup Solutions:

Go through all the files on your computer and know which files are important to you and need immediate backup. Keep the important files in one single drive so it’s not all scattered when you perform the actual backing up process.
Segregate small files like text documents, excel sheets, presentations etc and larger files like videos, audio files, digital photos etc.
Know about the many online Home Backup Solutions which are available so you can store your files in an offsite location.
Make sure your internet connection is fast.

Symptoms
If you use Bcp.exe to export a table to a flat file, the process may fail with an exception error if you use a format file to change the order of the columns during the export. The exception error occurs within the Bcp.exe file and the following error message appears:

The instruction at “memory_address” referenced memory at “memory_address”.
The memory could not be “written”.The output file is created but no rows are exported.
Resolution
Use a view to change the order of the columns to export. Then, use the view when you perform the bulk copy program and have the format file match the order of the columns in the view.

Symptoms
A standalone computer (without any network protocols installed) fails to connect locally to the Microsoft SQL Server 7.0 Desktop Edition. If you attempt to connect by using either Query Analyzer or SQL Enterprise Manager, the following error message occurs:

Unable to connect to server \\SRVNAME:
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied. Attempting to connect by using ODBCPING causes this error message to occur:

COULD NOT CONNECT TO SQL SERVER
SQLState: 08001 Native Error: 17
Info. Message: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does not exist or access denied.
SQLState: 01000 Native Error: 53
Info. Message: [Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionOpen(Connect()). ISQL can still connect, when you specify the SQL Server machine name. ISQL makes a DB Library connection and uses shared memory.
Resolution
The inability to connect locally, by using the machine name, occurs after Microsoft Data Access Components (MDAC) 2.6 has been applied to the desktop computer and the connection attempts to make the connection by using the Shared Memory protocol. Shared Memory is used when you specify the machine name to connect and no network protocols are installed on the desktop computer.

Symptoms
An access violation (AV) exception may occur when SQL Server tries to free the relevant memory block. The following is a sample short stack dump that you may see in the error log:

* Short Stack Dump* 0040A829 Module(sqlservr+0000A829) (CVariableInfo::CVarBlock::PvbJoin+00000035)* 0040A782 Module(sqlservr+0000A782) (CVariableInfo::PviRelease+00000056)* 0040A72C Module(sqlservr+0000A72C) (CVarPageMgr::Release+00000014)* 00401B52 Module(sqlservr+00001B52) (CMemObj::Free+0000001E)* 00401AFD Module(sqlservr+00001AFD) (CMemThread::Free+00000044)* 00401B26 Module(sqlservr+00001B26) (commondelete+0000001B)* 00560988 Module(sqlservr+00160988) (CSql::~CSql+00000021)* 0053E4B9 Module(sqlservr+0013E4B9) (CSqlMgr::DerefSql+00000065)* 0053EAC8 Module(sqlservr+0013EAC8) (CCompPlan::~CCompPlan+00000051)* 0053EA4B Module(sqlservr+0013EA4B) (CCompPlan::`vector deleting destructor’+0000000B)* 00440B4F Module(sqlservr+00040B4F) (CCacheObject::Release+000000D8)* 005D2F4F Module(sqlservr+001D2F4F) (CCache::FRemoveOne+00000316)* 0081AB31 Module(sqlservr+0041AB31) (BPool::ReplenishFreeList+00000144)* 0040AFAB Module(sqlservr+0000AFAB) (BPool::HelpLazyWriter+00000037)* 004306F5 Module(sqlservr+000306F5) (BPool::ReadAhead+00000074)* 00431A6D Module(sqlservr+00031A6D) (SDES::ReadAhead+0000006D)* 00521D63 Module(sqlservr+00121D63) (UnorderedPageSupplier::AddToQueue+00000546)* 00521938 Module(sqlservr+00121938) (UnorderedPageSupplier::GetNextPage+0000006C)* 00405810 Module(sqlservr+00005810) (SDES::GetBiDi+0000019C)* 0043362A Module(sqlservr+0003362A) (RowsetSS::FetchNextRow+000000B0)* 00433506 Module(sqlservr+00033506) (CQScanRowset::GetRowWithPrefetch+00000040)* 00521769 Module(sqlservr+00121769) (CQScanTableScan::GetRow+0000005F)* 0042C876 Module(sqlservr+0002C876) (CRowPrefetchDelayMgr::PopulateBuffer+0000001F)* 0042C334 Module(sqlservr+0002C334) (CRowPrefetchDelayMgr::GetRowUnordered+0000001E)* 0042C247 Module(sqlservr+0002C247) (CRowPrefetchDelayMgr::GetRow+0000001B)* 0042C22B Module(sqlservr+0002C22B) (CQScanRangePrefetchDelay::GetRow+00000014)* 0042D02F Module(sqlservr+0002D02F) (CQScanNLJoin::GetRow+0000010B)* 006D58CB Module(sqlservr+002D58CB) (CQScanConcat::GetRow+0000001C)* 005285B0 Module(sqlservr+001285B0) (CQScanSort::BuildSortTable+00000047)* 00528560 Module(sqlservr+00128560) (CQScanSort::Open+00000032)* 0041D92F Module(sqlservr+0001D92F) (CQueryScan::Startup+0000010D)* 0041925F Module(sqlservr+0001925F) (CStmtQuery::ErsqExecuteQuery+0000026B)* 00432F55 Module(sqlservr+00032F55) (CStmtSelect::XretExecute+00000229)* 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)* 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)* 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)* 005F74B6 Module(sqlservr+001F74B6) (CStmtExecStr::XretExecute+0000032E)* 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)* 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)* 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)* 00429DD3 Module(sqlservr+00029DD3) (execrpc+000004CB)* 0042904D Module(sqlservr+0002904D) (execute_rpc+00000019)* 00410FCE Module(sqlservr+00010FCE) (process_commands+00000210)* 41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)* 41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)* 7800A27B Module(MSVCRT+0000A27B) (beginthread+000000CE)* 77E5758A Module(KERNEL32+0000758A) (SetFilePointer+0000018A)
Resolution
SQL Server works with double-byte Unicode data internally; therefore, SQL Manager assumes an even length buffer.However, under some unexpected situations the buffer length can become an odd number. For example, this behavior might occur with an invalid Tabular Data Stream (TDS) language event. When the buffer length becomes an odd number, SQL Manager cannot allocate a sufficient memory block. This leads to a potential memory scratch and can cause the access violation that is described in the “Symptoms” section when the relevant memory block is freed later.

Symptoms
If you use Bcp.exe to export a table to a flat file, the process may fail with an exception error if you use a format file to change the order of the columns during the export. The exception error occurs within the Bcp.exe file and the following error message appears:

The instruction at “memory_address” referenced memory at “memory_address”.
The memory could not be “written”.The output file is created but no rows are exported.
Resolution
Use a view to change the order of the columns to export. Then, use the view when you perform the bulk copy program and have the format file match the order of the columns in the view.

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by the hotfix packageThe prerequisites for installing the hotfix packageWhether you must restart the computer after you install the hotfix packageWhether the hotfix package is replaced by any other hotfix packageWhether you must make any registry changesThe files that are contained in the hotfix package
Resolution
In Microsoft SQL Server 2005, when you call a method of a component object model (COM) object by using the sp_OAMethod stored procedure or the sp_OAGetProperty stored procedure, a memory leak occurs. For the sp_OAMethod stored procedure, this problem occurs if the method returns the result in the output parameter of the method. For the sp_OAGetProperty stored procedure, this problem occurs if the stored procedure returns a property value in the OUTPUT parameter.
Additionally, if the memory leaks exhausts the non-buffer pool memory region, the following error message is logged in the SQL Server error log file:

2008-05-01 08:35:07.80 spid535 Downgrading backup log buffers from 1024K to 64K
2008-05-06 09:35:22.55 spid77 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576
2008-05-06 10:48:04.85 spid593 Error: 701, Severity: 17, State: 123.
2008-05-06 10:48:04.85 spid593 There is insufficient system memory to run this query.