Create database using dmo and sql2005

The following code which worked just fine with sql2000 sp3 no longer
works with sql 2005. The error I receive says: "The file filename.mdf
is compressed but does not reside in a read only database or filegroup.
The file must be decompressed".
This happens on the line of code where I wrote <--PROBLEM.
Can anyone help?
HRESULT CSQLWrapperApp::CreateDataBase(CString dataBaseName,CString
databaseDirectoryLocation,CString& error)
{
HRESULT hr = NULL;
LPSQLDMODBFILE pMDFFile = NULL;
LPSQLDMOLOGFILE pLDFFile = NULL; /*IWSQLDMOLogFile * */
LPSQLDMODATABASE pDatabase = NULL;
LPSQLDMOFILEGROUPS pFileGroups = NULL;
LPSQLDMOFILEGROUP pFileGroup = NULL;
LPSQLDMODBFILES pDBFiles = NULL;
LPSQLDMOTRANSACTIONLOG pTransactionLog = NULL;
LPSQLDMOLOGFILES pLogFiles = NULL;
LPSQLDMODATABASES pDatabases = NULL;
CString fileName = dataBaseName + _T(".mdf");
error = _T("");
hr = createDBFile(fileName,databaseDirectoryL
ocation,pMDFFile, error);
if (hr == S_OK)
{
fileName = dataBaseName + _T(".ldf");
hr =
createLogFile(fileName,databaseDirectory
Location,pLDFFile,error);
if (hr == S_OK)
{
hr = CoCreateInstance(CLSID_SQLDMODatabase, NULL,
CLSCTX_INPROC_SERVER, IID_ISQLDMODatabase, (LPVOID*)&pDatabase);
if (hr == S_OK)
{
pDatabase->SetName(dataBaseName);
// add DBFile to DBFiles collection, in PRIMARY FileGroup
hr = pDatabase->GetFileGroups(&pFileGroups);
hr = pFileGroups->GetItemByName(TEXT("PRIMARY"), &pFileGroup);
hr = pFileGroup->GetDBFiles(&pDBFiles);
if FAILED( hr = pDBFiles->Add(pMDFFile) )
{
error = getDMOError(hr);
}
else
{
hr =
pDatabase->GetTransactionLog(&pTransactionLog);
hr = pTransactionLog->GetLogFiles(&pLogFiles);
if FAILED( hr = pLogFiles->Add(pLDFFile) )
{
error = getDMOError(hr);
}
else
{
// add Database to Database collection, this creates the data and
log
//devices and the actual new database
hr = pSQLServer->GetDatabases(&pDatabases);
if FAILED( hr = pDatabases->Add(pDatabase) ) <-- PROBLEM
{
error = getDMOError(hr);
}
}
}
}
else
{
error = _T("CoCreateInstance failed in CreateDataBase.");
hr = S_FALSE;
}
}
else
{
hr = S_FALSE;
}
}
else
{
hr = S_FALSE;
}
// release all objects
if (pFileGroups) pFileGroups->Release();
if (pFileGroup)pFileGroup->Release();
if (pDBFiles) pDBFiles->Release();
if (pMDFFile) pMDFFile->Release();
if (pTransactionLog) pTransactionLog->Release();
if (pLogFiles) pLogFiles->Release();
if (pLDFFile) pLDFFile->Release();
if (pDatabases) pDatabases->Release();
if (pDatabase) pDatabase->Release();
return hr;
}
////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////
HRESULT CSQLWrapperApp::createDBFile(CString fileName, CString
directoryName,LPSQLDMODBFILE & pDBFile, CString& error)
{
HRESULT hr;
error = _T("");
try
{
hr = CoCreateInstance(CLSID_SQLDMODBFile, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLDMODBFile, (LPVOID*)&pDBFile);
if (hr == S_OK)
{
pDBFile->SetName(fileName);
pDBFile->SetPhysicalName(directoryName + _T("\\") + fileName );
pDBFile->SetSize(10); //MB
pDBFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
pDBFile->SetFileGrowth(10);
pDBFile->SetMaximumSize(100);
pDBFile->SetPrimaryFile(TRUE);
}
else
{
error = _T("CoCreateInstance failed in createDBFile");
}
}
catch (...)
{
}
return hr;
}
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////
HRESULT CSQLWrapperApp::createLogFile(CString logFileName, CString
directoryName, LPSQLDMOLOGFILE& pLDFFile,CString& error)
{
error = _T("");
HRESULT hr;
try
{
hr = CoCreateInstance(CLSID_SQLDMOLogFile, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLDMOLogFile, (LPVOID*)&pLDFFile);
if(hr == S_OK)
{
pLDFFile->SetName(logFileName);
pLDFFile->SetPhysicalName(directoryName + _T("\\") + logFileName );
pLDFFile->SetSize(5);
pLDFFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
pLDFFile->SetFileGrowth(10);
pLDFFile->SetMaximumSize(100);
}
else
{
error = _T("CoCreateInstance Failed in createLogFile");
}
}
catch (...)
{
}
return hr;
}Is the drve or folder compressed? Perhaps 2005 does a check against this whe
re 2000 did not?
(Storing database files as compressed files has never been supported.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<roberta.coffman@.emersonprocess.com> wrote in message
news:1137444250.800043.163510@.g14g2000cwa.googlegroups.com...
> The following code which worked just fine with sql2000 sp3 no longer
> works with sql 2005. The error I receive says: "The file filename.mdf
> is compressed but does not reside in a read only database or filegroup.
> The file must be decompressed".
> This happens on the line of code where I wrote <--PROBLEM.
> Can anyone help?
> HRESULT CSQLWrapperApp::CreateDataBase(CString dataBaseName,CString
> databaseDirectoryLocation,CString& error)
> {
> HRESULT hr = NULL;
> LPSQLDMODBFILE pMDFFile = NULL;
> LPSQLDMOLOGFILE pLDFFile = NULL; /*IWSQLDMOLogFile * */
> LPSQLDMODATABASE pDatabase = NULL;
> LPSQLDMOFILEGROUPS pFileGroups = NULL;
> LPSQLDMOFILEGROUP pFileGroup = NULL;
> LPSQLDMODBFILES pDBFiles = NULL;
> LPSQLDMOTRANSACTIONLOG pTransactionLog = NULL;
> LPSQLDMOLOGFILES pLogFiles = NULL;
> LPSQLDMODATABASES pDatabases = NULL;
>
> CString fileName = dataBaseName + _T(".mdf");
> error = _T("");
> hr = createDBFile(fileName,databaseDirectoryL
ocation,pMDFFile, error);
> if (hr == S_OK)
> {
> fileName = dataBaseName + _T(".ldf");
> hr =
> createLogFile(fileName,databaseDirectory
Location,pLDFFile,error);
> if (hr == S_OK)
> {
> hr = CoCreateInstance(CLSID_SQLDMODatabase, NULL,
> CLSCTX_INPROC_SERVER, IID_ISQLDMODatabase, (LPVOID*)&pDatabase);
> if (hr == S_OK)
> {
> pDatabase->SetName(dataBaseName);
> // add DBFile to DBFiles collection, in PRIMARY FileGroup
> hr = pDatabase->GetFileGroups(&pFileGroups);
> hr = pFileGroups->GetItemByName(TEXT("PRIMARY"), &pFileGroup);
> hr = pFileGroup->GetDBFiles(&pDBFiles);
> if FAILED( hr = pDBFiles->Add(pMDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> hr =
> pDatabase->GetTransactionLog(&pTransactionLog);
> hr = pTransactionLog->GetLogFiles(&pLogFiles);
> if FAILED( hr = pLogFiles->Add(pLDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> // add Database to Database collection, this creates the data and
> log
> //devices and the actual new database
> hr = pSQLServer->GetDatabases(&pDatabases);
> if FAILED( hr = pDatabases->Add(pDatabase) ) <-- PROBLEM
> {
> error = getDMOError(hr);
> }
> }
> }
> }
> else
> {
> error = _T("CoCreateInstance failed in CreateDataBase.");
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> // release all objects
> if (pFileGroups) pFileGroups->Release();
> if (pFileGroup)pFileGroup->Release();
> if (pDBFiles) pDBFiles->Release();
> if (pMDFFile) pMDFFile->Release();
> if (pTransactionLog) pTransactionLog->Release();
> if (pLogFiles) pLogFiles->Release();
> if (pLDFFile) pLDFFile->Release();
> if (pDatabases) pDatabases->Release();
> if (pDatabase) pDatabase->Release();
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////
> HRESULT CSQLWrapperApp::createDBFile(CString fileName, CString
> directoryName,LPSQLDMODBFILE & pDBFile, CString& error)
> {
> HRESULT hr;
> error = _T("");
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMODBFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMODBFile, (LPVOID*)&pDBFile);
> if (hr == S_OK)
> {
> pDBFile->SetName(fileName);
> pDBFile->SetPhysicalName(directoryName + _T("\\") + fileName );
> pDBFile->SetSize(10); //MB
> pDBFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pDBFile->SetFileGrowth(10);
> pDBFile->SetMaximumSize(100);
> pDBFile->SetPrimaryFile(TRUE);
> }
> else
> {
> error = _T("CoCreateInstance failed in createDBFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////
> HRESULT CSQLWrapperApp::createLogFile(CString logFileName, CString
> directoryName, LPSQLDMOLOGFILE& pLDFFile,CString& error)
> {
> error = _T("");
> HRESULT hr;
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMOLogFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMOLogFile, (LPVOID*)&pLDFFile);
> if(hr == S_OK)
> {
> pLDFFile->SetName(logFileName);
> pLDFFile->SetPhysicalName(directoryName + _T("\\") + logFileName );
> pLDFFile->SetSize(5);
> pLDFFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pLDFFile->SetFileGrowth(10);
> pLDFFile->SetMaximumSize(100);
> }
> else
> {
> error = _T("CoCreateInstance Failed in createLogFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
>|||Databases files were never supported on a compressed drive with 2000 but I
don't think it ever checked for it. It looks like 2005 makes some kind of
check. This is from BOL 2000:
'os_file_name'
Is the path and file name used by the operating system when it creates the
physical file defined by the <filespec>. The path in os_file_name must
specify a directory on an instance of SQL Server. os_file_name cannot
specify a directory in a compressed file system.
Andrew J. Kelly SQL MVP
<roberta.coffman@.emersonprocess.com> wrote in message
news:1137444250.800043.163510@.g14g2000cwa.googlegroups.com...
> The following code which worked just fine with sql2000 sp3 no longer
> works with sql 2005. The error I receive says: "The file filename.mdf
> is compressed but does not reside in a read only database or filegroup.
> The file must be decompressed".
> This happens on the line of code where I wrote <--PROBLEM.
> Can anyone help?
> HRESULT CSQLWrapperApp::CreateDataBase(CString dataBaseName,CString
> databaseDirectoryLocation,CString& error)
> {
> HRESULT hr = NULL;
> LPSQLDMODBFILE pMDFFile = NULL;
> LPSQLDMOLOGFILE pLDFFile = NULL; /*IWSQLDMOLogFile * */
> LPSQLDMODATABASE pDatabase = NULL;
> LPSQLDMOFILEGROUPS pFileGroups = NULL;
> LPSQLDMOFILEGROUP pFileGroup = NULL;
> LPSQLDMODBFILES pDBFiles = NULL;
> LPSQLDMOTRANSACTIONLOG pTransactionLog = NULL;
> LPSQLDMOLOGFILES pLogFiles = NULL;
> LPSQLDMODATABASES pDatabases = NULL;
>
> CString fileName = dataBaseName + _T(".mdf");
> error = _T("");
> hr = createDBFile(fileName,databaseDirectoryL
ocation,pMDFFile, error);
> if (hr == S_OK)
> {
> fileName = dataBaseName + _T(".ldf");
> hr =
> createLogFile(fileName,databaseDirectory
Location,pLDFFile,error);
> if (hr == S_OK)
> {
> hr = CoCreateInstance(CLSID_SQLDMODatabase, NULL,
> CLSCTX_INPROC_SERVER, IID_ISQLDMODatabase, (LPVOID*)&pDatabase);
> if (hr == S_OK)
> {
> pDatabase->SetName(dataBaseName);
> // add DBFile to DBFiles collection, in PRIMARY FileGroup
> hr = pDatabase->GetFileGroups(&pFileGroups);
> hr = pFileGroups->GetItemByName(TEXT("PRIMARY"), &pFileGroup);
> hr = pFileGroup->GetDBFiles(&pDBFiles);
> if FAILED( hr = pDBFiles->Add(pMDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> hr =
> pDatabase->GetTransactionLog(&pTransactionLog);
> hr = pTransactionLog->GetLogFiles(&pLogFiles);
> if FAILED( hr = pLogFiles->Add(pLDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> // add Database to Database collection, this creates the data and
> log
> //devices and the actual new database
> hr = pSQLServer->GetDatabases(&pDatabases);
> if FAILED( hr = pDatabases->Add(pDatabase) ) <-- PROBLEM
> {
> error = getDMOError(hr);
> }
> }
> }
> }
> else
> {
> error = _T("CoCreateInstance failed in CreateDataBase.");
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> // release all objects
> if (pFileGroups) pFileGroups->Release();
> if (pFileGroup)pFileGroup->Release();
> if (pDBFiles) pDBFiles->Release();
> if (pMDFFile) pMDFFile->Release();
> if (pTransactionLog) pTransactionLog->Release();
> if (pLogFiles) pLogFiles->Release();
> if (pLDFFile) pLDFFile->Release();
> if (pDatabases) pDatabases->Release();
> if (pDatabase) pDatabase->Release();
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////
> HRESULT CSQLWrapperApp::createDBFile(CString fileName, CString
> directoryName,LPSQLDMODBFILE & pDBFile, CString& error)
> {
> HRESULT hr;
> error = _T("");
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMODBFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMODBFile, (LPVOID*)&pDBFile);
> if (hr == S_OK)
> {
> pDBFile->SetName(fileName);
> pDBFile->SetPhysicalName(directoryName + _T("\\") + fileName );
> pDBFile->SetSize(10); //MB
> pDBFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pDBFile->SetFileGrowth(10);
> pDBFile->SetMaximumSize(100);
> pDBFile->SetPrimaryFile(TRUE);
> }
> else
> {
> error = _T("CoCreateInstance failed in createDBFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////
> HRESULT CSQLWrapperApp::createLogFile(CString logFileName, CString
> directoryName, LPSQLDMOLOGFILE& pLDFFile,CString& error)
> {
> error = _T("");
> HRESULT hr;
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMOLogFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMOLogFile, (LPVOID*)&pLDFFile);
> if(hr == S_OK)
> {
> pLDFFile->SetName(logFileName);
> pLDFFile->SetPhysicalName(directoryName + _T("\\") + logFileName );
> pLDFFile->SetSize(5);
> pLDFFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pLDFFile->SetFileGrowth(10);
> pLDFFile->SetMaximumSize(100);
> }
> else
> {
> error = _T("CoCreateInstance Failed in createLogFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
>|||Roberta, Tibor and Andrew,
Yes, SQL 2005 checks whether the newly created file is compressed.
The folder where the data files reside must not be compressed.
ML
http://milambda.blogspot.com