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 Access across servers or Firewall Issue

Hi All,

I am facing a strange issue with accessing data from SQL server 2000. We are using SQL Server authentication for connecting to SQL Server 2000 and ADO .Net SQL client in the application. The problem here is, we have databases installed in one of the servers in some other office location that is in different state but on same domain. We do development from two locations that is one from Virtual Machines hosted on servers where Database Servers are located and on our local desktops in India.

Now when I developed a test client in Virtual Machines which will call a web service to get datasetof some data and show them in DataGrid. When I run the test client in debug mode from Visual Studio 2003, then I am getting data in the DataGrid. Even when I just run the exe from Virtual Machine still I am getting data.

But when I copy debug folder of the test client to the local desktop in India and when I run the Test client I am not getting any data loaded on to the DataGrid. I am getting an empty dataset when I am running from the local desktop.

Also I have converted the VS2003 project to VS2005 and when I run the application from Local Desktop then I am getting the data loaded fine. I could not understand what is the problem, what is the difference between accessing data from VS2003 and VS2005 using webservice.

Is this a firewall issue or I am missing any thing here. The webservice method I am calling to get data is actually returning dataset when I am calling this method directly from the browser also and even through application in virtual machine.

I am still not able to come to terms with this problem. This is severely affecting our deployment. Please help us resolve this problem. Please let me know if you need more information.

Thanks,

Venkatesh

Hi Venkatesh,

From what I understand, you have 3 components in your application system. There's the SQL Server, some client code, and a web service.

The SQL Server is in the same domain as the Virtual Machines that you are developing in. Your local desktops in India are in the same domain or a different domain than the SQL Server machine?

Since you are using SQL Server user authentication, the cross domain connectivity should not be an issue. You can verify this by running osql.exe or Query Analyzer tool on your local desktop connecting to the SQL Server machine using the same SQL Server user credentials as your client application.

As for the test client not seeing any data from the web service, I think it would help if you can mention how the web service is connecting to SQL Server and how the ADO.Net application is retrieving data from the web service. I can see the web service using ADO.Net (SqlClient) to connect to the backend SQL Server. Then, some front end client application built using the web service's WSDL document.

I think it will help people understand your application setup and provide possible solutions, if you can specify:

What is the web service using to connect to SQL Server (SqlClient)? Is the web service running on the same machine as SQL Server? If the web service is running on a different machine, is there a connectivity issue between the web service and SQL Server? Is the web service connected to SQL Server using SQL User credentials or Integrated authentication? Does the client application require to send user credentials to connect to the web service?|||

Sorry for the delay in response,

1. Web Service has Business Logic DLL reference which uses SQLClient ADO .Net commands to connect to database.

2. Web Service and SQL Server are on different servers but on the same domain.

3. There is no connectivity issue between the two machines because when I access same WebService through Web Browser, it works fine.

4. Web Service is connecting to SQL Server using SQL User credentials.

5. The User credentials are configured through WebService's Web.Config file.

I really have no clue about this issue. The issue just lies between the Windows Client and Web Service. Because when I access WebService directly through Web Browser then the Web Method is returning data, that means Web Service is getting the data fine but it is not able to give it to the Client.

FYI this issue occurs only on Windows 2003 Server if I install the same service on Windows 2000 server it works fine.

Anyhelp will be greatly appreciated.

Database Access across servers or Firewall Issue

Hi All,

I am facing a strange issue with accessing data from SQL server 2000. We are using SQL Server authentication for connecting to SQL Server 2000 and ADO .Net SQL client in the application. The problem here is, we have databases installed in one of the servers in some other office location that is in different state but on same domain. We do development from two locations that is one from Virtual Machines hosted on servers where Database Servers are located and on our local desktops in India.

Now when I developed a test client in Virtual Machines which will call a web service to get datasetof some data and show them in DataGrid. When I run the test client in debug mode from Visual Studio 2003, then I am getting data in the DataGrid. Even when I just run the exe from Virtual Machine still I am getting data.

But when I copy debug folder of the test client to the local desktop in India and when I run the Test client I am not getting any data loaded on to the DataGrid. I am getting an empty dataset when I am running from the local desktop.

Also I have converted the VS2003 project to VS2005 and when I run the application from Local Desktop then I am getting the data loaded fine. I could not understand what is the problem, what is the difference between accessing data from VS2003 and VS2005 using webservice.

Is this a firewall issue or I am missing any thing here. The webservice method I am calling to get data is actually returning dataset when I am calling this method directly from the browser also and even through application in virtual machine.

I am still not able to come to terms with this problem. This is severely affecting our deployment. Please help us resolve this problem. Please let me know if you need more information.

Thanks,

Venkatesh

Hi Venkatesh,

From what I understand, you have 3 components in your application system. There's the SQL Server, some client code, and a web service.

The SQL Server is in the same domain as the Virtual Machines that you are developing in. Your local desktops in India are in the same domain or a different domain than the SQL Server machine?

Since you are using SQL Server user authentication, the cross domain connectivity should not be an issue. You can verify this by running osql.exe or Query Analyzer tool on your local desktop connecting to the SQL Server machine using the same SQL Server user credentials as your client application.

As for the test client not seeing any data from the web service, I think it would help if you can mention how the web service is connecting to SQL Server and how the ADO.Net application is retrieving data from the web service. I can see the web service using ADO.Net (SqlClient) to connect to the backend SQL Server. Then, some front end client application built using the web service's WSDL document.

I think it will help people understand your application setup and provide possible solutions, if you can specify:

What is the web service using to connect to SQL Server (SqlClient)? Is the web service running on the same machine as SQL Server? If the web service is running on a different machine, is there a connectivity issue between the web service and SQL Server? Is the web service connected to SQL Server using SQL User credentials or Integrated authentication? Does the client application require to send user credentials to connect to the web service?|||

Sorry for the delay in response,

1. Web Service has Business Logic DLL reference which uses SQLClient ADO .Net commands to connect to database.

2. Web Service and SQL Server are on different servers but on the same domain.

3. There is no connectivity issue between the two machines because when I access same WebService through Web Browser, it works fine.

4. Web Service is connecting to SQL Server using SQL User credentials.

5. The User credentials are configured through WebService's Web.Config file.

I really have no clue about this issue. The issue just lies between the Windows Client and Web Service. Because when I access WebService directly through Web Browser then the Web Method is returning data, that means Web Service is getting the data fine but it is not able to give it to the Client.

FYI this issue occurs only on Windows 2003 Server if I install the same service on Windows 2000 server it works fine.

Anyhelp will be greatly appreciated.

Data Transformation Services.Linked server Error while connecting to Oracle

Hi,

I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.

But when I give import data and give the parameters to hook up the Oracle Server its

throwing a "Linked Server Error and says insufficient previleges"...

Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?

Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error

Thanks in Advance

Kings Indian

Have you set a username and password on the DTS Package?
|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||.|||

Kings Indian wrote:

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

Hello again Kings Indian, my apologise for the time it took to getting back to you.

Ok the best thing to do is go into Design mode of the DTS package.

Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.

I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791

You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.

Anyway I hope this works for you, if not come back and we shall see if we can progress further.

hth

Pace

Data Transformation Services.Linked server Error while connecting to Oracle

Hi,

I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.

But when I give import data and give the parameters to hook up the Oracle Server its

throwing a "Linked Server Error and says insufficient previleges"...

Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?

Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error

Thanks in Advance

Kings Indian

Have you set a username and password on the DTS Package?
|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||.|||

Kings Indian wrote:

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

Hello again Kings Indian, my apologise for the time it took to getting back to you.

Ok the best thing to do is go into Design mode of the DTS package.

Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.

I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791

You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.

Anyway I hope this works for you, if not come back and we shall see if we can progress further.

hth

Pace