Reading a record without placing a lock

Dear All,
I have one server application running which continously reading and
updating a DB.
While there is a Reporting tool which generating reports.
The reports can fail, but the server application cannot. So, I need to
run a query in the Reporting tool without placing a lock on the DB
(totally transaprent to the server).
Currently this is what I am doing.
SELECT * FROM Data WITH (NOLOCK);
Question:
1. Am I doing it correctly?
2. Is there a better way of doing? Example: setting the LOCK MODE
(instead of specifying NOLOCK on every command)
Thanks in advance.<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.
Hi
You can set the transaction isolation level to read uncommitted for the
session, but then you are potentially going to have dirty reads. Other ways
to do this would be to offload the reporting database either by using log
shipping, replication, mirroring or a snapshot.
John|||Hi
> 1. Am I doing it correctly?
No. What if some user inserts/deletes the row while you are reading. You
are about to get an inconsistent data. For example
you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
so while you read page A another connection inserst the value let me say
50, but you have already read data from the page A , so it moves the all
data to a new created page so now that data looks like a)10,40 ,50,
b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
60(duplicate) from page D as well.
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
at SNAPSHOT ISOLATION LEVEL in the BOL
<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.|||Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row =A0while =A0you are reading.= You
> are about to get an inconsistent data. For example
> =A0you have tree pages with data =A0like a) 10,40,60 b) 80,100,90 =A0c)110=,70,85 ,
> so while =A0you read page A =A0another connection inserst the value let me= say
> 50, but you have already read data =A0from the page A , so it moves the al=l
> data to a new created page so now that data looks like =A0a)10,40 ,50,
> b) 80,100,90 =A0c)110,70,85 ,d)60 ... and =A0as you keep reading =A0you g=et
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint =A0or if you use SQL Server 2005 =A0take a =look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message|||<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
Hi
I can't see how unreliability and inconsistence made the user requirements
for this system!
John|||<<There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).>>
The ANSI SQL Compliant way to describe how much you want to be isolated from other users is the SET
TRANSACTION ISOLATION command. SQL Server supports this, and READ UNCOMMITTED seems to do what you
want. Apparently Informix has a non-standard command named LOCK MODE, something that SQL Server do
not have. Assuming these indeed do the same thing, I support MS for using the ANSI SQL compliant
name for the command instead of some other command name. If they do not do the same, perhaps you can
enlighten un in what way they differ?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message

Read SP source code from VB

Hi,
I'm developing an application to crypt SQL databases and i need to
read SP and Views source code from VB6, if you know how to do this
please answer me.

Greetings."Carpi" <carlos.rodriguez@.sparks-software.com> wrote in message
news:5280f320.0401080621.514fd41f@.posting.google.c om...
> Hi,
> I'm developing an application to crypt SQL databases and i need to
> read SP and Views source code from VB6, if you know how to do this
> please answer me.
> Greetings.

Probably the best place to start is the .Script method of the SQLDMO
objects, which will generate the CREATE statements for all object types.

Simon

Read only or hidden report parameters in Reporting Services

I have 3 parameters in my report page. I am setting 2 parameters in default from my aspx application for this report. So i dont want to show these 2 parameters when i execute my report. I tried rc:parameters = false in querystring which is just hiding the whole parameters. But is there a way to make the other 2 parameters readonly or hidden and show 1 parameter to take input when i execute the report.Try going to Report Manager portal, editing the report parameters, and setting visibility to false.
Alternatively, I think you can also do this if you remove the name of the parameter in the report designer.sql

Reacting to DB events

I'd like to build an application that will react to specific changes to
data in a set of tables in a database. The application would replicate
these data changes to another database. The target database won't be
SQLServer. Neither is this simple replication, at times the application
will need to get extra data from the source database before the target
is updated.
In other DBMS systems I am involved with the DBMS has the facilty to
write to an application message queue so that the monitoring application
only has to monitor the queue rather than a database. What I'd like to
do is something like this:
1. Some application changes data in a table.
2. A trigger reacts to the change and writes a message to an application
queue.
3. A windows service/process monitors the queue and picks up the
message. It then carries out whatever replication/DB actions are necessary.
This would mean defining a number of new triggers on existing tables and
developing the windows service/process. Existing applications and the
existing tables in the database would remain unchanged. I'm not a
windows programmer but I have someone in my team who is and who will
build the windows service/process.
The bit I'm unsure about is how a trigger can write to an application
queue or communicate with the windows service/process. I may be using
the wrong terminology as I have more knowledge of Unix than Windows.
Could anyone help with how I can do this or suggest any alternative
strategies.
Thanks In Advance.
LaurenceYou can get all the queuing infrastructure for free. SQL Server 2005 include
s a functionality called
"Service Broker", where your trigger does SEND to write to the queue and the
service uses RECEIVE to
read off of the queue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message news:43A2EAFC.5020500@.open.ac.uk
..
> I'd like to build an application that will react to specific changes to da
ta in a set of tables in
> a database. The application would replicate these data changes to another
database. The target
> database won't be SQLServer. Neither is this simple replication, at times
the application will
> need to get extra data from the source database before the target is updat
ed.
> In other DBMS systems I am involved with the DBMS has the facilty to write
to an application
> message queue so that the monitoring application only has to monitor the q
ueue rather than a
> database. What I'd like to do is something like this:
> 1. Some application changes data in a table.
> 2. A trigger reacts to the change and writes a message to an application q
ueue.
> 3. A windows service/process monitors the queue and picks up the message.
It then carries out
> whatever replication/DB actions are necessary.
> This would mean defining a number of new triggers on existing tables and d
eveloping the windows
> service/process. Existing applications and the existing tables in the dat
abase would remain
> unchanged. I'm not a windows programmer but I have someone in my team who
is and who will build
> the windows service/process.
> The bit I'm unsure about is how a trigger can write to an application queu
e or communicate with
> the windows service/process. I may be using the wrong terminology as I ha
ve more knowledge of
> Unix than Windows.
> Could anyone help with how I can do this or suggest any alternative strate
gies.
> Thanks In Advance.
> Laurence
>|||Thanks Tibor,
We currently run SQL Server 2000 but will upgrade to 2005 in time. If
there's no way to do this in 2000 I'll have to wait a bit.
Thanks Again.
Laurence
Tibor Karaszi wrote:
> You can get all the queuing infrastructure for free. SQL Server 2005
> includes a functionality called "Service Broker", where your trigger
> does SEND to write to the queue and the service uses RECEIVE to read off
> of the queue.
>|||There is nothing that stop you from doing this now. Just have the trigger wr
ite to a table and have
the app polling the table, reading off of it and removing the rows that has
been handled. But, I
think you should look into Service Broker as it provide you with so much inf
rastructure code. At
least grab a book and read about SB (or play with it), so you can make the r
ight decision. I
wouldn't be surprised if you in the end decide to wait until 2005 is impleme
nted to cut down on the
dev time for this (thanks to Service Broker).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message news:43A7CA1D.6060804@.open.ac.uk
..
> Thanks Tibor,
> We currently run SQL Server 2000 but will upgrade to 2005 in time. If the
re's no way to do this
> in 2000 I'll have to wait a bit.
> Thanks Again.
> Laurence
>
> Tibor Karaszi wrote:
>

Re : Enabling / Disabling Data Flow Tasks

Hello,

I have created around 10 seperate packages for our application data load. Now I am planning to create a master package (or a wrapper package) which will execute all the 10 packages (thru execute package task). Then I have a job which executes the master package at a given date and time.

Question : How can I enable / disable execution of each package within the master package depending upon a flag variable. The reason why I need this mechanism is if the flag = 0 then I don't want all 10 packages within master package to execute and if flag = 1 then master package execution should begin and subsequently execute all packages within that master package.

Thank you

Jatin Shah

I am sure you could find a way to do this using the enable/disable method, however you may find it easier to use precedence constraints. Create a dummy (null-op) task in the parent package that is the first object to run, and connect it to all 10 sub-package tasks using precedence contraints. Double-click each precedence constraint, set the option to 'success and expression' and set the expression to "@.var == 1" (or 0 as the case may be)

Pretty much the same as what you are proposing, except the precedence constraint is being 'disabled' through a supported mechanism, instead of the tasks itself.

|||

Hello Grant,

Thank You for suggesting a way out.

I tried doing what you have explained, but when I try to set the expression as below

"@.flg == 1" it raises an error.

Thank You

Jatin Shah

|||

What's the error message?

You should check this out: http://www.sqlis.com/default.aspx?306

-Jamie

RDO 2.0 and Visual Basic 5 application stopped working with SQL Server 2005

We have a legacy application that is still in wide use. It is Visual Basic 5 and RDO 2.0 using ODBC to connect to SQL Server database.

When trying to connect to SQL Server 2005, it can no longer update or insert data. It reads data fine. The error message it gives is 16933, "The cursor does not include the table being modified or the table is not updatable through the cursor."

Has anybody else seen this problem? Any help would be appreciated.

Yes, we've seen this. It's hinted at in the documentation, although none of our examples seemed to exactly match what's documented. In several cases we were able to fix the problem by swapping the "from" table with a join table, but in one case we had to give up on the cursor.

RDLC and Excel - No Grid Lines

Using ASP.NET 2, C#, Web application, we have an rdlc report which will be primarily used to export to an Excel spreadsheet. When we run thw report, then export it, we wind up with a spreadsheet with no gridlines, eg a spreadsheet with invisible cell borders.

Is the a setting or property somewhere which can be changed so that the spreadsheet which opens after the export looks like a standard spreadsheet, that is, with visible cell borders?

Many thanks
Mike Thomas

In the test I just did, setting the BorderStyle to Single on the text boxes I wanted to have borders worked. You should also take a look at http://msdn2.microsoft.com/en-us/library/aa178951(SQL.80).aspx to see how cells are converted. Short and sweet: best, use a table; next best, make sure to align everything.

Larry

|||

Sorry, it's late. I should have also mentioned that every cell in a table and a matrix are textboxes. The BorderStyle is 'none' by default and the BorderColor is 'Black' by default. The properties I mean are those in Visual Studio, not those found through the context menu.

Larry

RDL vs RDLC

My application is a large windows form app that connects to databases via webservice. The client has no ability to get to the SQL Server. Do I even have an option of using Reporting Services, or do I have to use client deployed reports in RDLC files and pass it a dataset? If so, what are the advantages of using SSRS?

I don't have a problem with this method, since it is pretty much identical to what we do with Crystal Reports, but I want to make sure I understand the alternatives.

Thanks

The FAQ section about the ReportViewer controls may be helpful (e.g. #8): http://www.gotreportviewer.com/

-- Robert

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

RDA Question

Hi All

Wondering if someone can answer a couple of quick questions.

I have a vb 2005 winforms application that uses SQL CE database and uses RDA to pull data from a SQL 2005 database and also to push back the changes. I would like to be able to advise the user if the connection to the SQL 2005 database is available and make either the push button or the pull button available depending on the state of the local or remote database.

Any suggestions on how I can do this. I have tried the following which works sort of ok but the timeout when it can't open the connection is too long.

PrivateSub checkserver()

Dim connectionstatus AsNew SqlConnection(My.Settings.MyRemoteConnectionString)

Try

connectionstatus.Open()

label2.text = "Connected"

Catch ex As Exception

Label2.Text = "Disconnected"

Button5.Enabled = False

Button6.Enabled = False

Finally

connectionstatus.Close()

EndTry

EndSub

Also I'm not sure of a way to determine which database is the most recent to enable either the push or pull buttons.

Appreciate any assistance.

Regards

Ian

You could add "Connection Timeout=5" to MyRemoteConnectionString (5 seconds instead of 15)

|||

Thanks, much better.

Any ideas about how I can determine the last time either database was edited/updated?

Ian

|||If you are using RDA, you must implement a scheme to track these changes yourself, as no information about server state is available when using RDA. I you are tracking changes locally, you might be able to get some information from the replication system tables in the SQL CE database. Maybe you should consider Merge replication, which allows you to track changes both server side and client side.

RDA Question

Hi All

Wondering if someone can answer a couple of quick questions.

I have a vb 2005 winforms application that uses SQL CE database and uses RDA to pull data from a SQL 2005 database and also to push back the changes. I would like to be able to advise the user if the connection to the SQL 2005 database is available and make either the push button or the pull button available depending on the state of the local or remote database.

Any suggestions on how I can do this. I have tried the following which works sort of ok but the timeout when it can't open the connection is too long.

Private Sub checkserver()

Dim connectionstatus As New SqlConnection(My.Settings.MyRemoteConnectionString)

Try

connectionstatus.Open()

label2.text = "Connected"

Catch ex As Exception

Label2.Text = "Disconnected"

Button5.Enabled = False

Button6.Enabled = False

Finally

connectionstatus.Close()

End Try

End Sub

Also I'm not sure of a way to determine which database is the most recent to enable either the push or pull buttons.

Appreciate any assistance.

Regards

Ian

You could add "Connection Timeout=5" to MyRemoteConnectionString (5 seconds instead of 15)

|||

Thanks, much better.

Any ideas about how I can determine the last time either database was edited/updated?

Ian

|||If you are using RDA, you must implement a scheme to track these changes yourself, as no information about server state is available when using RDA. I you are tracking changes locally, you might be able to get some information from the replication system tables in the SQL CE database. Maybe you should consider Merge replication, which allows you to track changes both server side and client side.

RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:

Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}

Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:

Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456

The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.

So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?

Please help.

Thank you.

Fipil.

Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||

I'm not using Replication, but RDA. So, there are no a publication created.

|||Moving to CE forum.|||

I solved the problem.

The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).

|||

Hi,Fipil

I met the same problem with u.

Glad that you have slove the problem :-)

But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?

Thanks & Best Regards,

Hua wen gui

|||

Hi,

I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...

|||

So, you did not connect the DB with Login "test" in your PDA program, right?

My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().

meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.

It's a strange problem, any suggestion?

Thanks

|||

Yes, you are right. I changed original name of user by 'test' while writting post here.

Did you mean DB on server or db on PDA?

|||

The DB on server.

I'd like to pull data from DB Server to PDA with RDA.pull.

But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)

I can not find out the problem.

I'm not sure If the problem on IIS or SQL Server?

Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.

RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:

Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}

Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:

Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456

The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.

So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?

Please help.

Thank you.

Fipil.

Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||

I'm not using Replication, but RDA. So, there are no a publication created.

|||Moving to CE forum.|||

I solved the problem.

The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).

|||

Hi,Fipil

I met the same problem with u.

Glad that you have slove the problem :-)

But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?

Thanks & Best Regards,

Hua wen gui

|||

Hi,

I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...

|||

So, you did not connect the DB with Login "test" in your PDA program, right?

My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().

meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.

It's a strange problem, any suggestion?

Thanks

|||

Yes, you are right. I changed original name of user by 'test' while writting post here.

Did you mean DB on server or db on PDA?

|||

The DB on server.

I'd like to pull data from DB Server to PDA with RDA.pull.

But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)

I can not find out the problem.

I'm not sure If the problem on IIS or SQL Server?

Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.

RDA pull problem

Hi,

I am new to SQL Mobile programming. I am using SQL Server 2005 Mobile and SQL Express. I have a mobile windows application + .sdf file in the PDA. When i try to pull the table the first time, the table is successfully retrieved to PDA. When i try to pull the table again, i get the following error:

"An unexpected error has occured in TestDb.exe. Select quit and then restart this program, or sleect details for more information.

A duplicate value cannot be inserted into aunique index. [table Name=_sysRDASubscriptions, constraint name=c_LocalTableName] "

The above message indicates, that the table is existing in "_sysRDASubscriptions" table.

I tried dropping the table using SQL Explorer in PDA, but it didn't work out.

I am not sure of how to drop the existing table from the database programatically, and if I drop the table from the database, will this be cleared.

Please help me in resolving this issue. I am in desperate need of an urgent solution for this.

Thanks

Prasanna

You will need to drop the existing table before the next call to RDA.Pull. An error occurs if the table already exists.

Run the following command in SQL Mobile Query Analyzer

DROP TABLE < table_name >

Take a look at SQL Server 2005 Mobile Edition Books Online topic:

"Executing SQL Statements on the SQL Tab"

Thank you!
Syed N. Yousuf
Microsoft Developer Support Professional 
 
This posting is provided “AS IS” with no warranties, and confers no rights. 

|||

Hi Syed,

I have already tried dropping the table from SQL Mobile Query Analyzer, and i think even I have already mentioned in the message that i posted previously. So, it will be better if you can read the previous post in detail.

By the way regarding the issue, even after dropping the table using the

"DROP TABLE <TABLENAME>" Query using SQL Mobile Query Analyzer, I didn't succeed. And when i did the same programatically, i don't understand why i get a "Null Reference Exception". I queried the Information_Schema.tables and i found that after i execute the DROP command, the information_schema.tables doesn't have any entries regarding the deleted table. Even after this i get the error. I am not sure how to delete the entries from the _sysRDASubscriptions table. Is it possible to execute queries against this table, either programmatically or manually. Suggest, a method to solve this issue.

Thanks

Prasanna.

|||

Are you really using SQL Express as the source of the tables you are trying to pull? If so, RDA from SQL Mobile to SQL Express is not a licensed or supported scenario.

-Darren Shaffer

|||

Hi Darren,

Our applications have both trial and paid versions. For paid versions, we will be not be using SQL Express, but for trial versions, we need to have SQL Express. So, I need a solution for both SQL Express and other editions as well.

Thanks

Prasanna

|||

About the only way to synchronize data from SQL Mobile to SQL Express is going to be to expose some simple web services that get and put data from/to the SQL Express database. Unlike MSDE, SQL Express has no SQL Agent and hence cannot support merge replication or RDA.

-Darren

RDA pull problem

Hi,

I am new to SQL Mobile programming. I am using SQL Server 2005 Mobile and SQL Express. I have a mobile windows application + .sdf file in the PDA. When i try to pull the table the first time, the table is successfully retrieved to PDA. When i try to pull the table again, i get the following error:

"An unexpected error has occured in TestDb.exe. Select quit and then restart this program, or sleect details for more information.

A duplicate value cannot be inserted into aunique index. [table Name=_sysRDASubscriptions, constraint name=c_LocalTableName] "

The above message indicates, that the table is existing in "_sysRDASubscriptions" table.

I tried dropping the table using SQL Explorer in PDA, but it didn't work out.

I am not sure of how to drop the existing table from the database programatically, and if I drop the table from the database, will this be cleared.

Please help me in resolving this issue. I am in desperate need of an urgent solution for this.

Thanks

Prasanna

You will need to drop the existing table before the next call to RDA.Pull. An error occurs if the table already exists.

Run the following command in SQL Mobile Query Analyzer

DROP TABLE < table_name >

Take a look at SQL Server 2005 Mobile Edition Books Online topic:

"Executing SQL Statements on the SQL Tab"

Thank you!
Syed N. Yousuf
Microsoft Developer Support Professional 
 
This posting is provided “AS IS” with no warranties, and confers no rights. 

|||

Hi Syed,

I have already tried dropping the table from SQL Mobile Query Analyzer, and i think even I have already mentioned in the message that i posted previously. So, it will be better if you can read the previous post in detail.

By the way regarding the issue, even after dropping the table using the

"DROP TABLE <TABLENAME>" Query using SQL Mobile Query Analyzer, I didn't succeed. And when i did the same programatically, i don't understand why i get a "Null Reference Exception". I queried the Information_Schema.tables and i found that after i execute the DROP command, the information_schema.tables doesn't have any entries regarding the deleted table. Even after this i get the error. I am not sure how to delete the entries from the _sysRDASubscriptions table. Is it possible to execute queries against this table, either programmatically or manually. Suggest, a method to solve this issue.

Thanks

Prasanna.

|||

Are you really using SQL Express as the source of the tables you are trying to pull? If so, RDA from SQL Mobile to SQL Express is not a licensed or supported scenario.

-Darren Shaffer

|||

Hi Darren,

Our applications have both trial and paid versions. For paid versions, we will be not be using SQL Express, but for trial versions, we need to have SQL Express. So, I need a solution for both SQL Express and other editions as well.

Thanks

Prasanna

|||

About the only way to synchronize data from SQL Mobile to SQL Express is going to be to expose some simple web services that get and put data from/to the SQL Express database. Unlike MSDE, SQL Express has no SQL Agent and hence cannot support merge replication or RDA.

-Darren

RDA Pull from Windows Server 2003 gives Native Error 28035

I currently have a Vs.Net 2003 Pocket PC application that uses sql server ce 2.0 to push and pull data from Sql Server 2000 to the Pocket PC. The sscesa20.dll isapi agent is installed on a Windows 2000 server and points to another server that hosts the SQL database. In this configuration everything works like a charm. I am in the process of upgrading my application server to Windows 2003 Server but the RDA PULL process does not work, giving an Native Error of 28035 EVERY time. I use the exact same code in the app but when I point to the isapi agent on the 2003 box it does not work. When I connect to the the sscesa20.dll via the Pocket IE it returns the "Sql Server CE Server Agent" message. When I do an RDA.Submit it works and the SQL table is updated as expected.

I have configured IIS 6 with the proper web extensions
I have turned off the firewall and antivirus on the server.
I have reinstalled the server agent several times.
I set up the sscesa20.dll manually.
I removed all file level security.
I opened up the virtual directory.
I verified the ddl versions match.

I'm dead in the water right now. I'm not sure what else to try.

Any help would be greatly appreciated.

Error 28035 indicated that the header information that the server tools are using to broker the RDA is missing or corrupted. The typical cause of this is that you have a security product somewhere in the path from your mobile device to the publisher (SQL Server) that is stripping off the content lengths on the replication HTTP dialogue. You need to completely disable any internet security products like Symantec or McAffee on both your IIS and your SQL Servers.

Darren

|||

Darren,

Thanks for the info. I'm sure it's not the SQL Server because I don't get any errors when I RDA pull using the Windows 2000 Server implimentation of the ISAPI agent. I've turned off all the firewalls but my provider is a little hesitant to turn off the anti-virus products. I'm perplexed because everything works fine with my Windows 2000 Server. Everything points to this being a Windows Server 2003 specific problem. I'm going to work with the ASP to get the anti virus out of the equation too.

|||

Hi Tstay,

Did you resolve the problem?

What is the behavior when you turned of Anti virus?

This error also occurs in case of SQL Mobile 3.0 Client Agent is trying to contact SQL CE 2.0 Server Agent (OR) SQL CE 2.0 Client Agent is trying to contact SQL Mobile 3.0 Server Agent. Make sure that you are using same version of SQL Mobile/CE both on device and as well as on IIS.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

|||

We turned off the anti-virus and the firewall and I'm still getting the error. The Sql CE version on the device and the server match. We have come to the conclusion that it's a 2003 or IIS 6 issue, so we have no choice to roll back the OS to 2000 until we have more to go on with 2003. Any advice would be appreciated.

thanks