Database Connection

hi everybody;
my questions about when database connection :
i am when connecting database latter receiveing exception error.Error is : An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
codes :
SqlCeConnection cnn=new SqlCeConnection("Datasource=\\My Documents\\Business\\LOREAL.sdf");
cnn.Open(); // Exception is here
help me..
can i doing ?

Moving it to Sql Mobile forum, when it has got better chance of being answered.

-Thanks,

Mohit

|||

Does the file exist in the path shown ?

Try to catch SqlCeException. That will show why it failed. Take a look at the VS 2005 docs on SqlCeException example.

Thank you!

Syed N. Yousuf

Microsoft Developer Support Professional

This posting is provided “AS IS” with no warranties, and confers no rights.

DataBase Connection

hi everybody;
my questions about when database connection :
i am when connecting database latter receiveing exception error.Error is : An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
codes :
SqlCeConnection cnn=new SqlCeConnection("Datasource=\\My Documents\\Business\\LOREAL.sdf");
cnn.Open(); // Exception is here
help me..
can i doing ?

Mehmet,

Try the code below instead - it fixes your connection string and provides you with a way to get more information about specific exceptions you may encounter using SQL Mobile.

-Darren

try

{

SqlCeConnection cnn = new SqlCeConnection(@."Data Source = \My Documents\Business\LOREAL.sdf");

cnn.Open();

}

catch (SqlCeException sqlex)
{
DisplaySQLCEErrors(sqlex);

}

public void DisplaySQLCEErrors(SqlCeException ex)
{
SqlCeErrorCollection errorCollection = ex.Errors;

StringBuilder bld = new StringBuilder();
Exception inner = ex.InnerException;

foreach (SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X"));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);

foreach (int numPar in err.NumericErrorParameters)
{
if ( 0 != numPar ) bld.Append( "\n Num. Par. : " + numPar );
}

foreach ( string errPar in err.ErrorParameters )
{
if ( String.Empty != errPar ) bld.Append( "\n Err. Par. : " + errPar );
}

MessageBox.Show( bld.ToString(), "SQL Server CE Error" );
bld.Remove(0, bld.Length);
}
}

|||thank you Darren for my tring
and i am writing error Message :
ErrorCode:80004005
Message : There is a file sharing violation. A different process might be using the file.[,,,,,]
Mino Error .: 25035
Source: Microsoft SQL Server 2000 Windows CE Edition
Err.Par.:\My Documents\Business\LOREAL.sdf

help me (:|||

Something else on your device has a lock on the .SDF file. Try soft-resetting the device and run your code again. If that doesn't work, you may not have a clean install of SQL Mobile on the device.

Regards,

Darren

|||thank you Darren very very much
i am tring now and when being error message i will write is here (:|||but not do Darren..
i am reset machine and later i was set to program and i was install my application. but not did.
again same error...
can i doing ?
please help....|||

Mehmet,

Can you open the .sdf file using Query Analyzer 3.0 on your device? If that works and your application cannot create a connection to the database, there is some problem in your code and I'd ask you to post more of your code. If you cannot open the database with Query Analyzer 3.0, I'd be interested to know what error is reported. The error you are getting indicates that the .sdf fileis locked, but SQL Mobile is a multi-user database and can accept multiple connections. The only thing that I know of that would lock the entire database would be that you are running a Compact, AutoFlush, or merge repl with AddOption.CreateDatabase while you have code that is trying to connect to it. And it doesn't sound like that is the case.

Are you sure you have a good install of SQL Mobile and that the .sdf file is valid? How did you create your .sdf file?

Darren

|||hi again...
i am using SQL CE 2.0 in dotnet 2003 .
and my database running in PDA. and it s running on QueryAnalyzer.
but i am not connection database on codebehind in .net. and i sent code to you.
or i did writing code for create database on codebehind. Then creating database on PDA but again i am not connected database ):
and when i not connection open database later i am not running ExecuteReader or ExecuteNonQuery on PDA.
but no problem PDA.|||

SQL CE 2.0 allows only one connection to database, QA connection counts. Make sure to exit QA or you won't be able to open this database.

|||but my code is correct and my code is running on Windows Application.but not running on PDA application.
i reseted pda , i installed QA and i installed my program.
but again not running.
usually error is : An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
connection.Open(); /// exception is here
or not running code is : SqlDataAdapter.Fill(DataTable); // not running code..|||

I’m not quite sure how your code can run on Windows application because SQL Server CE 2.0 is not available on desktop. Even if it does, it does not mean it would run unchanged on device.

This exception you're getting means something wrong with your code, data and/or environment. Error messages in the exception would tell you what it is, so you can fix your application code, data or environment.

If that's the same message as before, please make sure you're not running another copy of your application, you do not open more than one connection to the same DB from your application and you do not have Query Analyzer running at the same time as your application.

|||

pls help me as soon as possible.I am getting an error as

An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

|||first only not running your database (sdf) on query analyzer..
usually your code first : connection.open()
command.ExecuteNonQuery();
connection.Close();
for example.....
if you opened connection and end your process later close connection...|||Hi, Darren;
I am facing this same problem.
I am quite sure that no other process is trying to connect my .sdf file.
My code is:
....
conn.Close();
conn.Open();//open connection just after close; throw exception.

The reason that close connection and open again is that if I still use this open connection (passed in as parameter), I got exception sth like "...connection is Fetching...". So I close and then open it. It works most of the time, but it throws that exception. I looks like connection hasn't been closed but try to open it again. I add code:
...
conn.Close();
int count = 0;
while (conn.State != ConnectionState.Closed)
{
Thread.Sleep(250);
if(count>4)
break;
count++;
}
conn.Open();
...
it still throws that exception.
How can I fix that problem?

Thank u very much.

|||

Hi everbody,

I have a problem like this.There is a programme on PDA and it has a (.sdf) database .When I trying to start program,I got this error : "not access to user table" and then "it will be upgrade" and then "25035: There is a file sharing violation.A different process might be using the file"

and when I look at the database,I saw three tables: MSysConstraints,MSysObjects,MSysRDATombstone.

I supposed that the database havent a "user table" ,but I dont know what serve the three tables on the database.

thanks much..

DataBase Connection

hi everybody;
my questions about when database connection :
i am when connecting database latter receiveing exception error.Error is : An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
codes :
SqlCeConnection cnn=new SqlCeConnection("Datasource=\\My Documents\\Business\\LOREAL.sdf");
cnn.Open(); // Exception is here
help me..
can i doing ?

Mehmet,

Try the code below instead - it fixes your connection string and provides you with a way to get more information about specific exceptions you may encounter using SQL Mobile.

-Darren

try

{

SqlCeConnection cnn = new SqlCeConnection(@."Data Source = \My Documents\Business\LOREAL.sdf");

cnn.Open();

}

catch (SqlCeException sqlex)
{
DisplaySQLCEErrors(sqlex);

}

public void DisplaySQLCEErrors(SqlCeException ex)
{
SqlCeErrorCollection errorCollection = ex.Errors;

StringBuilder bld = new StringBuilder();
Exception inner = ex.InnerException;

foreach (SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X"));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);

foreach (int numPar in err.NumericErrorParameters)
{
if ( 0 != numPar ) bld.Append( "\n Num. Par. : " + numPar );
}

foreach ( string errPar in err.ErrorParameters )
{
if ( String.Empty != errPar ) bld.Append( "\n Err. Par. : " + errPar );
}

MessageBox.Show( bld.ToString(), "SQL Server CE Error" );
bld.Remove(0, bld.Length);
}
}

|||thank you Darren for my tring
and i am writing error Message :
ErrorCode:80004005
Message : There is a file sharing violation. A different process might be using the file.[,,,,,]
Mino Error .: 25035
Source: Microsoft SQL Server 2000 Windows CE Edition
Err.Par.:\My Documents\Business\LOREAL.sdf

help me (:|||

Something else on your device has a lock on the .SDF file. Try soft-resetting the device and run your code again. If that doesn't work, you may not have a clean install of SQL Mobile on the device.

Regards,

Darren

|||thank you Darren very very much
i am tring now and when being error message i will write is here (:|||but not do Darren..
i am reset machine and later i was set to program and i was install my application. but not did.
again same error...
can i doing ?
please help....|||

Mehmet,

Can you open the .sdf file using Query Analyzer 3.0 on your device? If that works and your application cannot create a connection to the database, there is some problem in your code and I'd ask you to post more of your code. If you cannot open the database with Query Analyzer 3.0, I'd be interested to know what error is reported. The error you are getting indicates that the .sdf fileis locked, but SQL Mobile is a multi-user database and can accept multiple connections. The only thing that I know of that would lock the entire database would be that you are running a Compact, AutoFlush, or merge repl with AddOption.CreateDatabase while you have code that is trying to connect to it. And it doesn't sound like that is the case.

Are you sure you have a good install of SQL Mobile and that the .sdf file is valid? How did you create your .sdf file?

Darren

|||hi again...
i am using SQL CE 2.0 in dotnet 2003 .
and my database running in PDA. and it s running on QueryAnalyzer.
but i am not connection database on codebehind in .net. and i sent code to you.
or i did writing code for create database on codebehind. Then creating database on PDA but again i am not connected database ):
and when i not connection open database later i am not running ExecuteReader or ExecuteNonQuery on PDA.
but no problem PDA.|||

SQL CE 2.0 allows only one connection to database, QA connection counts. Make sure to exit QA or you won't be able to open this database.

|||but my code is correct and my code is running on Windows Application.but not running on PDA application.
i reseted pda , i installed QA and i installed my program.
but again not running.
usually error is : An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
connection.Open(); /// exception is here
or not running code is : SqlDataAdapter.Fill(DataTable); // not running code..|||

I’m not quite sure how your code can run on Windows application because SQL Server CE 2.0 is not available on desktop. Even if it does, it does not mean it would run unchanged on device.

This exception you're getting means something wrong with your code, data and/or environment. Error messages in the exception would tell you what it is, so you can fix your application code, data or environment.

If that's the same message as before, please make sure you're not running another copy of your application, you do not open more than one connection to the same DB from your application and you do not have Query Analyzer running at the same time as your application.

|||

pls help me as soon as possible.I am getting an error as

An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll|||first only not running your database (sdf) on query analyzer..
usually your code first : connection.open()
command.ExecuteNonQuery();
connection.Close();
for example.....
if you opened connection and end your process later close connection...|||Hi, Darren;
I am facing this same problem.
I am quite sure that no other process is trying to connect my .sdf file.
My code is:
....
conn.Close();
conn.Open();//open connection just after close; throw exception.

The reason that close connection and open again is that if I still use this open connection (passed in as parameter), I got exception sth like "...connection is Fetching...". So I close and then open it. It works most of the time, but it throws that exception. I looks like connection hasn't been closed but try to open it again. I add code:
...
conn.Close();
int count = 0;
while (conn.State != ConnectionState.Closed)
{
Thread.Sleep(250);
if(count>4)
break;
count++;
}
conn.Open();
...
it still throws that exception.
How can I fix that problem?

Thank u very much.

|||

Hi everbody,

I have a problem like this.There is a programme on PDA and it has a (.sdf) database .When I trying to start program,I got this error : "not access to user table" and then "it will be upgrade" and then "25035: There is a file sharing violation.A different process might be using the file"

and when I look at the database,I saw three tables: MSysConstraints,MSysObjects,MSysRDATombstone.

I supposed that the database havent a "user table" ,but I dont know what serve the three tables on the database.

thanks much..

sql

Database conn

I have downloaded some code on how to upload images to a sql database and I am getting
this error message

"SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied."

I have opened Enterprise Manager and made sure I was a "user" and everything seems to be ok there.
The connection string looks like this
"server=localhost;uid=sa;pwd=;database=ImageUpload"
Could anyone give me an idea on what to check or try next

Thanksu can try replacing 'localhost' with the ip address
use ipconfig command to know abt ip addr|||What kind of authentication you used? SQL Server authentication or windows integrated?

Try windows integrated authentication.

Database Comparison

I had an application go down yesterday...it was giving a Type Mismatch error and the support team couldn't figure it out.

My suspicion is that a field in the database got populated with a character the vb app didn't like when it renders the information.

I'd like to prove that theory...but the database is large (4gb) and I'm not sure how to do a database comparison to see where the records differ.

Anybody have a script to get me started OR some experience to share?

Thanks,

Alex8675Anybody have some real, useful, practical information that could help us figure out what your problem is?

Exact text of error?
DDL of table?
Datatype of column?

We are not mentalists, here. We don't play guessing games.

Database Backups

I’m trying to backup all my database in SQL server 2005, but I keep getting
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
Chris Holden
Chris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> Im trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden
sql

Database Backups

I’m trying to backup all my database in SQL server 2005, but I keep gettin
g
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
Chris HoldenChris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> Im trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden

Database Backups

Iâ'm trying to backup all my database in SQL server 2005, but I keep getting
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
--
Chris HoldenChris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> I?m trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden

database backup restore problem!

Attempting to do a data base recovery from backup and keep getting the following error: " The media set for database "isis" has 2 family members but only 1 are provided. All members must be provided."
I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a system problem now the sql 2000 server us up and when i try to restore the database it gives me the above error. I hope some one out there can help.
Ray
You striped the backup over two backup files, and now you try to restore from only one of those
backups files. You need to find that other backup file before you can restore.
(This is similar to having two disks in a raid 0, take one of those to another machine and expect to
get to the data on that machine... :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond C Prys" <Raymond C Prys@.discussions.microsoft.com> wrote in message
news:E9DA9319-A918-4DF5-94BB-C1E0F9B7B2B9@.microsoft.com...
> Attempting to do a data base recovery from backup and keep getting the following error: " The
media set for database "isis" has 2 family members but only 1 are provided. All members must be
provided."
> I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a
system problem now the sql 2000 server us up and when i try to restore the database it gives me the
above error. I hope some one out there can help.
> Ray
>

database backup restore problem!

Attempting to do a data base recovery from backup and keep getting the following error: " The media set for database "isis" has 2 family members but only 1 are provided. All members must be provided."
I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a system problem now the sql 2000 server us up and when i try to restore the database it gives me the above error. I hope some one out there can help.
RayYou striped the backup over two backup files, and now you try to restore from only one of those
backups files. You need to find that other backup file before you can restore.
(This is similar to having two disks in a raid 0, take one of those to another machine and expect to
get to the data on that machine... :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond C Prys" <Raymond C Prys@.discussions.microsoft.com> wrote in message
news:E9DA9319-A918-4DF5-94BB-C1E0F9B7B2B9@.microsoft.com...
> Attempting to do a data base recovery from backup and keep getting the following error: " The
media set for database "isis" has 2 family members but only 1 are provided. All members must be
provided."
> I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a
system problem now the sql 2000 server us up and when i try to restore the database it gives me the
above error. I hope some one out there can help.
> Ray
>

database backup restore problem!

Attempting to do a data base recovery from backup and keep getting the follo
wing error: " The media set for database "isis" has 2 family members but on
ly 1 are provided. All members must be provided."
I backed up the database in sql 2000 server and had to reinstall the sql 200
0 server due to a system problem now the sql 2000 server us up and when i tr
y to restore the database it gives me the above error. I hope some one out
there can help.
RayYou striped the backup over two backup files, and now you try to restore fro
m only one of those
backups files. You need to find that other backup file before you can restor
e.
(This is similar to having two disks in a raid 0, take one of those to anoth
er machine and expect to
get to the data on that machine... :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond C Prys" <Raymond C Prys@.discussions.microsoft.com> wrote in message
news:E9DA9319-A918-4DF5-94BB-C1E0F9B7B2B9@.microsoft.com...
> Attempting to do a data base recovery from backup and keep getting the following e
rror: " The
media set for database "isis" has 2 family members but only 1 are provided.
All members must be
provided."
> I backed up the database in sql 2000 server and had to reinstall the sql 2000 serv
er due to a
system problem now the sql 2000 server us up and when i try to restore the d
atabase it gives me the
above error. I hope some one out there can help.
> Ray
>

database backup problem

I got error message, Error: 9002, Severity: 17, State: 6 The log file for database 'test' is full. Anybody can help me? why happen, how to fix, thanks in advance.I got that yesterday when trying to change a column width! Turned out the log file was set to a fixed size. As soon as I changed it to automatically grow the problem went away.|||i don't know if this'll work, but if the database is still operational, i think you could expand the database size. this should cancel out the "Full" error message. It allows you to do it 6.5 and 7, but i can't remember off the top of my head how to do it in 2000 (if that's what you're running)|||Grahamt: can you describe the step of how to I changed it to automatically grow . I appreciate it. Thanks|||Cruickshanks: i am running on sql server 2000|||Right-click the DB in Enterprise Mangler
Click the Transaction Log Tab at the top
Make sure the "Automatically Grow file" and "Unrestricted file growth" are both checked.

See if that works. :)|||I check the transaction log, "Automatically Grow file" and "Unrestricted file growth" are both checked. What happen?|||Disk full?|||If the disk is full...just try truncating the log . in query analyzer execute

use test
go

back up log test with truncate_only .

This should resolve your problem. Also try to create a maintenance plan .|||Praveenvc: Why need to trunck the log file, is the log file locate in the management\SQL Sever Agent\SQL Sever Log, but that is not two big, only have 7 logfile there. Another thing, after you trunck the log file, how to do mantainance plan, can you give me more detail as i am a new with sql server . Thanks again!

Database Backup Failed with Timeout Expired from Enterprise Manager

Hi,

I have SQL Server 2000 with SP4. When we take backup from Enterprise manager its fails with error "timeout expired". But when we take backup from query analyzer it works.

It seems that through enterprise manager 30 sec is the non configurable timeout parameter set. Checked sysprocesses table during backup but there was no blocking during that time at least for that spid.

Is there a way to find out which process is blocking the database backup? Also, what are the possible scenarios where database backup can be blocked.

Thanks,

Ramesh

Hi rksingh,

When you run a backup or restore from the Enterprise manager, the querytimeout does not apply.

It timesout because the process is being blocked by another process.

It could be blocked by BACKUP LOG or ALTER DATABASE commands.

Open another QA session and use SP_WHO2 ACTIVE to check for blocking.

regards

Jag

|||

Check SQL error log what error it is recorded...all backup info recorded in sql error log.

|||

Problem is resolved with the help of Microsoft PSS.

There is query time out setting for SQL Server 2000 Enterprise Manager which was set to 100 in my case whereas set to 0 on other servers.

To change settings

1. Open enterprise Manager.

2. Click on Tools - > Options

3. Goto Advanced Tab

3. Under connection setting change the Query time out value.

Thanks,

Ramesh

Database Backup Failed with Timeout Expired from Enterprise Manager

Hi,

I have SQL Server 2000 with SP4. When we take backup from Enterprise manager its fails with error "timeout expired". But when we take backup from query analyzer it works.

It seems that through enterprise manager 30 sec is the non configurable timeout parameter set. Checked sysprocesses table during backup but there was no blocking during that time at least for that spid.

Is there a way to find out which process is blocking the database backup? Also, what are the possible scenarios where database backup can be blocked.

Thanks,

Ramesh

Hi rksingh,

When you run a backup or restore from the Enterprise manager, the querytimeout does not apply.

It timesout because the process is being blocked by another process.

It could be blocked by BACKUP LOG or ALTER DATABASE commands.

Open another QA session and use SP_WHO2 ACTIVE to check for blocking.

regards

Jag

|||

Check SQL error log what error it is recorded...all backup info recorded in sql error log.

|||

Problem is resolved with the help of Microsoft PSS.

There is query time out setting for SQL Server 2000 Enterprise Manager which was set to 100 in my case whereas set to 0 on other servers.

To change settings

1. Open enterprise Manager.

2. Click on Tools - > Options

3. Goto Advanced Tab

3. Under connection setting change the Query time out value.

Thanks,

Ramesh

sql

Database Authentication Error in FrontPage

Hi,
I am trying to add a Data view to a sharepoint site. When I try to create a
connection to the SQL database I get an Authentication error.
I have tried both the Windows Authentication and a Username. I am a DB
administrator and have full access to the database and its server.
Thanks!Please see the reply in
microsoft.public.sqlserver.connect
-Sue
On Fri, 14 Oct 2005 13:04:04 -0700, "Slim"
<Slim@.discussions.microsoft.com> wrote:

>Hi,
>I am trying to add a Data view to a sharepoint site. When I try to create a
>connection to the SQL database I get an Authentication error.
>I have tried both the Windows Authentication and a Username. I am a DB
>administrator and have full access to the database and its server.
>Thanks!

Database Authentication Error in FP

Hi,
I am trying to add a Data view to a sharepoint site. When I try to create a
connection to the SQL database I get an Authentication error.
I have tried both the Windows Authentication and a Username. I am a DB
administrator and have full access to the database and its server.
Thanks!
It's hard to say without detailed error information. But try
turning on failure audits in SQL Server and check the SQL
Server log to see what account is failing. That should help
you track it down. You also want the entire error message -
there are different types of authentication errors that can
point to different problems.
-Sue
On Fri, 14 Oct 2005 13:04:02 -0700, "Slim"
<Slim@.discussions.microsoft.com> wrote:

>Hi,
>I am trying to add a Data view to a sharepoint site. When I try to create a
>connection to the SQL database I get an Authentication error.
>I have tried both the Windows Authentication and a Username. I am a DB
>administrator and have full access to the database and its server.
>Thanks!

Database Authentication Error in FP

Hi,
I am trying to add a Data view to a sharepoint site. When I try to create a
connection to the SQL database I get an Authentication error.
I have tried both the Windows Authentication and a Username. I am a DB
administrator and have full access to the database and its server.
Thanks!It's hard to say without detailed error information. But try
turning on failure audits in SQL Server and check the SQL
Server log to see what account is failing. That should help
you track it down. You also want the entire error message -
there are different types of authentication errors that can
point to different problems.
-Sue
On Fri, 14 Oct 2005 13:04:02 -0700, "Slim"
<Slim@.discussions.microsoft.com> wrote:

>Hi,
>I am trying to add a Data view to a sharepoint site. When I try to create a
>connection to the SQL database I get an Authentication error.
>I have tried both the Windows Authentication and a Username. I am a DB
>administrator and have full access to the database and its server.
>Thanks!

Database Attach error encountered

Would appreciate any inputs.

I am running SQL2005. I am trying to attach to a database located on an external hard drive that is connected to my laptop via USB connection. The database files are a copy of the database files from a server running SQL2005.

After making the copy of the database, we confirmed that we could attach to the database on the external drive from the server and were successful attaching, opening and accessing the copy. Brought the HD home to work with the database and am getting the following error when I try to attach:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.GetOriginalDatabaseName(String primaryFilePath)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.PopulatePrimaryFileData(String primaryFilePath)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile..ctor(SqlManagementUserControl parent, CDataContainer dc, String fullPath, String databaseOwner, ServerConnection connectionInfo)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.IsSelectedFileValid(BrowseFolder dlg)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.PrimaryFile.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

===================================

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\Navision SqlData\ForentaSQL_Data.mdf'. (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3159&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

Server Name: twdlaptop1
Error Number: 5123
Severity: 16
State: 20
Line Number: 1

Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

It looks as though your problem is a permissions problem. When you're at home you're using a different login context than you are when you're at work. The external HD allows the work login access, but not the home login. I'd look at the security tab under the properties menu item for the directory where the database files are stored and see if you can add your home login to the list of allowed users.

Database attach error 3624

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

Database attach error 3624

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.