Error restoring a mssql database
in [Programming SQL]
|
From: Usman on 28 Jul 2008 12:49 Hi I'm having this error when I restore a backup on a Windows 2008 64-bit machine that has MsSqlserver 2005 installed on it. "This cache contains no result sets, or the current result set contains no rows" I get this error on the statement pQuryResult->GetColumnString(1,1); The code works perfectly on all the machines except for this particular machine. Please help me with any suggestion. I've googled it but have'nt found any solution to it. Regards Usman
From: Tom Moreau on 28 Jul 2008 11:53 What happens when you run the actual T-SQL in an SSMS session? -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Usman" <usman(a)hostingcontroller.com> wrote in message news:%23HfeSmM8IHA.2548(a)TK2MSFTNGP05.phx.gbl... Hi I'm having this error when I restore a backup on a Windows 2008 64-bit machine that has MsSqlserver 2005 installed on it. "This cache contains no result sets, or the current result set contains no rows" I get this error on the statement pQuryResult->GetColumnString(1,1); The code works perfectly on all the machines except for this particular machine. Please help me with any suggestion. I've googled it but have'nt found any solution to it. Regards Usman
From: Erland Sommarskog on 28 Jul 2008 18:19 Usman (usman(a)hostingcontroller.com) writes: > I'm having this error when I restore a backup on a Windows 2008 64-bit > machine that has MsSqlserver 2005 installed on it. > > "This cache contains no result sets, or the current result set contains no > rows" > > I get this error on the statement > > pQuryResult->GetColumnString(1,1); > > The code works perfectly on all the machines except for this particular > machine. Please help me with any suggestion. I've googled it but have'nt > found any solution to it. The only piece of code you post, does not look like a RESTORE statement to me. Maybe you could post more of your code, so get some more context? Maybe you could even tell us which API this is? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Usman on 29 Jul 2008 05:21 Here is the function that I'm using for it. Consider bSetDefaults and bConnect function work properly and CHelperFunction::Format is an alternative to _vsnprinf function. Please suggest what could be the reason that this error is coming on a particular 64 bit windows 2008 machine. Regards Usman ----------------------------------------------------------------------------------------- short CMSSQLDB::InternalRestoreDatabase(string DatabaseName, string TargetResourcePath) { USES_CONVERSION; short sResult; sResult = 0; logData("step1"); try { _SQLServerPtr spSQLServer; if (SUCCEEDED(spSQLServer.CreateInstance(__uuidof(SQLServer)))) { try { if (TRUE == bSetDefaults(spSQLServer)) { if (TRUE == bConnect(spSQLServer)) { DatabasesPtr spDatabases; _DatabasePtr spDatabase; TransactionLogPtr spTransactionLog; LogFilesPtr spLogFiles; _LogFilePtr spLogFile; CComBSTR bstrDatabase,bstrFileName,bstrPhysicalFileName,bstrLogPhysicalFileName,pRelocateFileList; CComBSTR bstrRealFileName,bstrRealLogFileName; TargetResourcePath = GetShortPath(TargetResourcePath); logData("step2"); logData(TargetResourcePath); string tempTargetResourcePath; string strRelocateFile,strRealLogFileName; string strPhysicalFileName,strLogPhysicalFileName; string strRealName; long cnt; TargetResourcePath = CHelperFunction::TrimAll(TargetResourcePath); int idx1,len; if (TargetResourcePath.find(" ") != -1) { len = TargetResourcePath.length(); idx1 = TargetResourcePath.rfind('\\'); tempTargetResourcePath = TargetResourcePath.substr(idx1 + 1, len - (idx1+1)); } else { if (GetFileAttributes(TargetResourcePath.c_str()) == -1) { len = TargetResourcePath.length(); idx1 = TargetResourcePath.rfind('\\'); tempTargetResourcePath = TargetResourcePath.substr(idx1+1, len - (idx1+1)); } else tempTargetResourcePath = TargetResourcePath; } logData("step3"); bstrDatabase.Append(A2BSTR(DatabaseName.c_str())); bstrFileName.Append("\""); bstrFileName.Append(A2BSTR(TargetResourcePath.c_str())); bstrFileName.Append("\""); CComVariant vtDatabase; vtDatabase.vt = VT_BSTR; vtDatabase.bstrVal = bstrDatabase.Copy(); //get the name of the database file and use that name to restore from //the backup file spDatabases = spSQLServer->Databases; spDatabase = spDatabases->Item(vtDatabase); logData("step4"); //************************************************************ //get the real and physical file name of the database FileGroupsPtr pspFileGroups; _FileGroupPtr ptrFileGroup; logData("step5"); spDatabase->get_FileGroups(&pspFileGroups); CComBSTR bstrFileGroupName("PRIMARY"); CComVariant vtItem; vtItem.vt = VT_BSTR; vtItem.bstrVal = bstrFileGroupName.Copy(); ptrFileGroup = pspFileGroups->Item(vtItem); logData("step6"); DBFilesPtr ptrDBfiles; _DBFilePtr spDBFile; _DBFilePtr spDBFile2; ptrFileGroup->get_DBFiles(&ptrDBfiles); ptrDBfiles->get_Count(&cnt); spDBFile = ptrDBfiles->ItemByID(1); spDBFile->get_PhysicalName(&bstrPhysicalFileName); //************************************************************** //get the transaction log object and the info for the file name and physical filename logData("step7"); spTransactionLog = spDatabase->TransactionLog; spTransactionLog->get_LogFiles(&spLogFiles); spLogFiles->get_Count(&cnt); spLogFile = spLogFiles->Item(cnt); logData("step8"); spLogFile->get_PhysicalName(&bstrLogPhysicalFileName); strLogPhysicalFileName = OLE2A(bstrLogPhysicalFileName); strLogPhysicalFileName = CHelperFunction::TrimAll(strLogPhysicalFileName); string strTempPhysicalFileName = OLE2A(bstrPhysicalFileName); strTempPhysicalFileName = CHelperFunction::TrimAll(strTempPhysicalFileName); strPhysicalFileName = strTempPhysicalFileName; logData("step9"); _RestorePtr spRestore; if (SUCCEEDED(spRestore.CreateInstance(__uuidof(Restore)))) { CComBSTR pRetVal; BSTR bstrVal; string strRetVal; //Set a media property, naming the source device(s) spRestore->put_Files(A2BSTR(tempTargetResourcePath.c_str())); logData(tempTargetResourcePath); Sleep(5000); //get the logical file names of the backup media whose //physical files are to be removed QueryResults *pQuryResult; spRestore->raw_ReadFileList(spSQLServer,&pQuryResult); logData("step11"); Sleep(5000); _bstr_t bVal = pQuryResult->GetColumnString(1,1); logData("step11-1"); strRealName = OLE2A(bVal.copy()); strRealName = CHelperFunction::TrimAll(strRealName); bVal = pQuryResult->GetColumnString(2,1); logData("step11-2"); strRealLogFileName = OLE2A(bVal.copy()); strRealLogFileName = CHelperFunction::TrimAll(strRealLogFileName); //now create the complete string with logical and physical file name altered there string strtemp; strtemp = CHelperFunction::Format("[%s]", strRealName.c_str()); string strtempldf = CHelperFunction::Format("[%s]", strRealLogFileName.c_str()); strPhysicalFileName = CHelperFunction::Format("%s,[%s],%s,[%s]", strtemp.c_str(), strPhysicalFileName.c_str(), strtempldf.c_str(), strLogPhysicalFileName.c_str()); logData("step12"); spRestore->put_RelocateFiles(A2BSTR(strPhysicalFileName.c_str())); spRestore->put_Action(SQLDMORestore_Database); spRestore->SQLVerify(spSQLServer); logData("step13"); //Set the Database property, naming the database backed up spRestore->put_Database(bstrDatabase.Copy()); logData("step14"); //If necessary, set the ReplaceDatabase property to force database creation spRestore->ReplaceDatabase = TRUE; logData("step15"); //Call the SQLRestore method spRestore->SQLRestore(spSQLServer); logData("step16"); } sResult = 1; m_szErrorReason = "Success"; } else { sResult = -4; m_szErrorReason = "unable to connect to database"; } } vDisconnect(spSQLServer); spSQLServer.Release(); } catch(_com_error pCE) { sResult = -3; vDisplayError(pCE); vDisconnect(spSQLServer); spSQLServer.Release(); } } else { sResult = -2; m_szErrorReason = "Unable to create the SQLServer object."; } } catch(_com_error pCE) { sResult = -1; vDisplayError(pCE); } return sResult; } ----------------------------------------------------------------------------------------- "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9AEA352C3925Yazorman(a)127.0.0.1... > Usman (usman(a)hostingcontroller.com) writes: >> I'm having this error when I restore a backup on a Windows 2008 64-bit >> machine that has MsSqlserver 2005 installed on it. >> >> "This cache contains no result sets, or the current result set contains >> no >> rows" >> >> I get this error on the statement >> >> pQuryResult->GetColumnString(1,1); >> >> The code works perfectly on all the machines except for this particular >> machine. Please help me with any suggestion. I've googled it but have'nt >> found any solution to it. > > The only piece of code you post, does not look like a RESTORE statement > to me. > > Maybe you could post more of your code, so get some more context? Maybe > you could even tell us which API this is? > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Usman on 29 Jul 2008 05:23
Dont know how to do that. Please see my reply to the other person in the same thread to see what API I am using to restore the database. Regards Usman "Tom Moreau" <tom(a)dont.spam.me.cips.ca> wrote in message news:%23f%23EDpM8IHA.616(a)TK2MSFTNGP02.phx.gbl... > What happens when you run the actual T-SQL in an SSMS session? > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Usman" <usman(a)hostingcontroller.com> wrote in message > news:%23HfeSmM8IHA.2548(a)TK2MSFTNGP05.phx.gbl... > Hi > > I'm having this error when I restore a backup on a Windows 2008 64-bit > machine that has MsSqlserver 2005 installed on it. > > "This cache contains no result sets, or the current result set contains no > rows" > > I get this error on the statement > > pQuryResult->GetColumnString(1,1); > > The code works perfectly on all the machines except for this particular > machine. Please help me with any suggestion. I've googled it but have'nt > found any solution to it. > > Regards > > Usman > > |