Hi,
I've a SQL2000 server with an instance.
I wanna replicate this istance in another server on my lan.
How can I do this?
Thanks
We'd need to know a lot more to fully understand the requirements, but for
creating and maintaining a copy of user databases, have a look in BOL for log
shipping.
Cheers,
Paul Ibison
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Monday, March 19, 2012
Friday, February 17, 2012
create db with script
I have an .sql script that I am using to create my db in sql2000. I am creating it as the sa user. In this script, I am also creating a user and putting them in the db_owner role. Upon load of data, I am the user I made.
I keep getting an error :
file:C:/Projects/CanidateManager/scripts/ant/targets-sql.xml:32: java.sql.SQLException: Lo
gon failed. Msg 18456, Severity 14, State 1, Login failed for user 'sa'., Server , Proced
ure , Line 0
at org.apache.tools.ant.taskdefs.JDBCTask.getConnecti on(JDBCTask.java:352)
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLE xec.java:358)
at org.apache.tools.ant.Task.perform(Task.java:341)
at org.apache.tools.ant.Target.execute(Target.java:30 9)
at org.apache.tools.ant.Target.performTasks(Target.ja va:336)
at org.apache.tools.ant.Project.executeTarget(Project .java:1339)
at org.apache.tools.ant.Project.executeTargets(Projec t.java:1255)
at org.apache.tools.ant.Main.runBuild(Main.java:609)
at org.apache.tools.ant.Main.start(Main.java:196)
at org.apache.tools.ant.Main.main(Main.java:235)
What am I doing wrong? I am not a database person persay so I have exhausted all my resources.check the security mode of your sql box.|||You don't show how you're executing it...why not use osql?
Where are you executing this from as well
Why do you need to have a database built by a front end (just a guess)|||It is not from a front end but part of the installation when we send the software out to a new environment. I need to build the database and load it with needed data. I have an sa in the security folder with a password of sa with public and db_owner. I am calling this script with an ant build target.|||Whats an ant build target?
I just use osql.exe|||is any gui involved in your installation? or you've hard-coded sa pwd into a script?|||When I installed the sql server on my box, I created the sa user with password. So no, there is no gui to do this. Maybe I am missing something.|||wait a minute, you've "created" sa user???|||I do have a properties file that has the sa and password info in it so the script does not have any actual hard coding just a token. I'm using these just to assign who the user is when creating.|||When I installed the sql it created the sa. I just made the password sa as well.|||oh, that's reassuring :)
make sure you have either sql standard or mixed security mode enabled on the server.|||How do I check that from Enterprise manager?|||Right click on the server, Select properties and look at the security tab|||It is set to both sql and windows authentication.|||So if that is set correctly, any other suggestions as to my issue?|||Originally posted by rjkimme
When I installed the sql it created the sa. I just made the password sa as well.
Not a good idea...
You should create another user and grant it sa...
Then take sa and strip it of all rights...
and you shouldn't tell us what the password is...
esp on a public board...
(What's the ip? just kidding)
Maybe if you post the command your executing...|||Another issue because I may have solved my previous problem. Is there a programmatic way to create a logon for a database?|||Look up sp_addlogin sp_adduser in bol|||Instead of worrying over the password, would it be possible to assume an administrator is doing the install, and use a trusted connection with his rights? Not sure if that works in most/all environments, but it sounds like a safe bet. You just ahve to make sure that you are dropping tables into the correct database. I had to help a guy get his application out of the master database a few years back, and it was not pretty.|||Miracles do happen. It works. Thanks so much for your help and time.|||Originally posted by rjkimme
Miracles do happen.
uhhh...no
But that's great!
Way to stick with it!
And now...its almost time..
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Nov 14 2003 17:00:00'
SELECT DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margaritaville|||Yes, a nice way to head off for the weekend.
I keep getting an error :
file:C:/Projects/CanidateManager/scripts/ant/targets-sql.xml:32: java.sql.SQLException: Lo
gon failed. Msg 18456, Severity 14, State 1, Login failed for user 'sa'., Server , Proced
ure , Line 0
at org.apache.tools.ant.taskdefs.JDBCTask.getConnecti on(JDBCTask.java:352)
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLE xec.java:358)
at org.apache.tools.ant.Task.perform(Task.java:341)
at org.apache.tools.ant.Target.execute(Target.java:30 9)
at org.apache.tools.ant.Target.performTasks(Target.ja va:336)
at org.apache.tools.ant.Project.executeTarget(Project .java:1339)
at org.apache.tools.ant.Project.executeTargets(Projec t.java:1255)
at org.apache.tools.ant.Main.runBuild(Main.java:609)
at org.apache.tools.ant.Main.start(Main.java:196)
at org.apache.tools.ant.Main.main(Main.java:235)
What am I doing wrong? I am not a database person persay so I have exhausted all my resources.check the security mode of your sql box.|||You don't show how you're executing it...why not use osql?
Where are you executing this from as well
Why do you need to have a database built by a front end (just a guess)|||It is not from a front end but part of the installation when we send the software out to a new environment. I need to build the database and load it with needed data. I have an sa in the security folder with a password of sa with public and db_owner. I am calling this script with an ant build target.|||Whats an ant build target?
I just use osql.exe|||is any gui involved in your installation? or you've hard-coded sa pwd into a script?|||When I installed the sql server on my box, I created the sa user with password. So no, there is no gui to do this. Maybe I am missing something.|||wait a minute, you've "created" sa user???|||I do have a properties file that has the sa and password info in it so the script does not have any actual hard coding just a token. I'm using these just to assign who the user is when creating.|||When I installed the sql it created the sa. I just made the password sa as well.|||oh, that's reassuring :)
make sure you have either sql standard or mixed security mode enabled on the server.|||How do I check that from Enterprise manager?|||Right click on the server, Select properties and look at the security tab|||It is set to both sql and windows authentication.|||So if that is set correctly, any other suggestions as to my issue?|||Originally posted by rjkimme
When I installed the sql it created the sa. I just made the password sa as well.
Not a good idea...
You should create another user and grant it sa...
Then take sa and strip it of all rights...
and you shouldn't tell us what the password is...
esp on a public board...
(What's the ip? just kidding)
Maybe if you post the command your executing...|||Another issue because I may have solved my previous problem. Is there a programmatic way to create a logon for a database?|||Look up sp_addlogin sp_adduser in bol|||Instead of worrying over the password, would it be possible to assume an administrator is doing the install, and use a trusted connection with his rights? Not sure if that works in most/all environments, but it sounds like a safe bet. You just ahve to make sure that you are dropping tables into the correct database. I had to help a guy get his application out of the master database a few years back, and it was not pretty.|||Miracles do happen. It works. Thanks so much for your help and time.|||Originally posted by rjkimme
Miracles do happen.
uhhh...no
But that's great!
Way to stick with it!
And now...its almost time..
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Nov 14 2003 17:00:00'
SELECT DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margaritaville|||Yes, a nice way to head off for the weekend.
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
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
Subscribe to:
Posts (Atom)