finished updates are not visible immediately (needed time about 2-5 secs), SQL Server, web applicat

[Guest]

I have C#, web application, SQL Server appliacation. There is a webservice, which runs stored procedure, which deletes some row and add one new row in the same table. Although the changes are finished, another webservice cant see the changes immediately. The table is nearly empty (10 rows).

1. webservice: sql command runs a stored procedure by execute non query metho
2. webservice: sql command runs another stored procedury by execute reader metho

Changes are not visible although the second webservice is started when the first is finished..

thank you very muc

Tom

 

[William Ryan eMVP]

Tom:

I'd need to see the code and where/when the second web service is being
called. However, if you fire an update query and once it's definitely
completed fire a select query, you'll see the new changes. I'm just
wondering if the select query is working off of a DataTable/DataSet that
hasn't been refreshed, or is returning the same or it the first query isn't
really finished yet. Something suspicious is going on b/c if the first
query is completed, the old records don't exist except in the transaction
log...so there'd be no way for them to be returned.

can you post the code sequence and the routines to call the procs?

I have C#, web application, SQL Server appliacation. There is a

webservice, which runs stored procedure, which deletes some row and add one
new row in the same table. Although the changes are finished, another
webservice cant see the changes immediately. The table is nearly empty (10
rows).

 

[Guest]

Both webservices are activated by user. When the first one is finished, script enables button for the calling of the second webservice.

Code of the first webservice is here

[WebMethod(EnableSession = true)
...

this.com.CommandText = "cb_admin.HierarchyAssignPropertyToNugget"
com.Parameters.Add("@pro_id",SqlDbType.BigInt).Value = propertyId
com.Parameters.Add("@nug_id",SqlDbType.BigInt).Value = nuggetId
com.Parameters.Add("@hie_id",SqlDbType.BigInt).Value = hierarchyId
if (forWhom == 0
com.Parameters.Add("@ent_id",SqlDbType.BigInt).Value = System.DBNull.Value
els
com.Parameters.Add("@ent_id",SqlDbType.BigInt).Value = forWhom
com.Parameters.Add("@type",SqlDbType.TinyInt).Value = type
com.Parameters.Add("@comment",SqlDbType.NVarChar).Value = comment
tr

com.ExecuteNonQuery()

catch(SqlException se

if (se.Number == 2627) // snaha o duplicitni prideleni vlastnost

cbe = new CBException("e1",se.Source)

els

this.cbe = new CBException(se)

throw (cbe);


code for the second webservic

[WebMethod(EnableSession = true)
...

'something similiar but I was wrong: the second webservice use comandType.Text and returns dataSet not SqlDataReader.

I think that the problem is in sql server datetime and iis server datetime becouse sql guery contains something like thi

select ... many columns and tables ... where date_from >= getDate()

- the first webservice uses sql server datetime but the second constructs all query text on iis computer and therefore the query text looks something like that

select ... many columns and tables ... where date_from >= '1.1.1980 10:00:00

- it uses iis server date and time (another computer

Toma

 

[William Ryan eMVP]

Tomas:

Your theory about the different times is the most likely culprit. If
possible you'll want to synchronize the two times but I'm not telling you
something you don't already now. How about adding in an identity field and
then using the RowIndex (Identity) for the query. This will allow it to
work regardless of the system times.
If Server 2 was off by just 10 seconds or so, the query would not return
those values so I think a 'record number' is a more robust way to go unless
you can definitively ensure that both times will always be the same (which
is going to be difficult to guarantee).

Both webservices are activated by user. When the first one is finished,

script enables button for the calling of the second webservice.

Code of the first webservice is here:

[WebMethod(EnableSession = true)]
...

this.com.CommandText = "cb_admin.HierarchyAssignPropertyToNugget";
com.Parameters.Add("@pro_id",SqlDbType.BigInt).Value = propertyId;
com.Parameters.Add("@nug_id",SqlDbType.BigInt).Value = nuggetId;
com.Parameters.Add("@hie_id",SqlDbType.BigInt).Value = hierarchyId;
if (forWhom == 0)
com.Parameters.Add("@ent_id",SqlDbType.BigInt).Value = System.DBNull.Value;
else
com.Parameters.Add("@ent_id",SqlDbType.BigInt).Value = forWhom;
com.Parameters.Add("@type",SqlDbType.TinyInt).Value = type;
com.Parameters.Add("@comment",SqlDbType.NVarChar).Value = comment;
try
{
com.ExecuteNonQuery();
}
catch(SqlException se)
{
if (se.Number == 2627) // snaha o duplicitni prideleni vlastnosti
{
cbe = new CBException("e1",se.Source);
}
else
{
this.cbe = new CBException(se);
}
throw (cbe);
}

code for the second webservice

[WebMethod(EnableSession = true)]
...

'something similiar but I was wrong: the second webservice use

comandType.Text and returns dataSet not SqlDataReader.

I think that the problem is in sql server datetime and iis server datetime

becouse sql guery contains something like this

select ... many columns and tables ... where date_from >= getDate()

- the first webservice uses sql server datetime but the second constructs

all query text on iis computer and therefore the query text looks something
like that: