Is there a delay writing to an SQL Database

---

Can someone advise if there is a delay in data being written to the database following a tableadapter.update(datatable) command?

I save transactions which are subjected to the above and then a listview is updated to reflect them.

As I work through all is OK and the transactions appear in view.

I then run a backup through my app using a backup object to do this and this reports all OK

I then close the app and re-open and as as I am in debug the database is empty.

I perform a restore through my app using a restore object and selecting the backup file I created previoulsy which reports all OK

The retore procedure calls application.restart to allow the app to initialise to the restored data.

The problem is quite a bit of my data in missing from the restore as if the last block I did prior to backup never actaully made it to the database?

I also rememeber noting that at times when the update method is performed the actual timestamp on the physical databse is not updated....until I close the app and return to the designer?

So does this mean then prior to performing a backup I have to somehow force the app to ensure it has written all changes to the databse?

Thanks

hi,

very loosely speaking, "updates" (as long as all operations) are always written to both data and log files..

log entries are immediately written in syncronous way, so that transactions can be later committed/rolled back even in case of a system crash... on the other side, physical flush to the database "data" files is performed at "scheduled" time (in async mode to boost underlying OS I/O activity and not to make the database service wait for completation aknowledge), that's to say at checkpoint occurrances...

but even if a crash occurs before the physical data flush to the data files, at next SQL Server start up the data will be recovered/rolled back (depending on completation and commit status of the relative transactions) when every and each database is started up, entering the "redo" and "undo" phases to check what still need to be written to data file(s) (to the actual tables) from the log file(s) analysing all active LSNs (log sequence number) in order to find what need to be serialized.. so, usually, you are not concerned with such a deal in your application if not in case of real disaster and database corruption as, again, even in case of a system crash the database, at next start up, must (and actually "is", if no corruption is on the air) recovered to a consistent state...

so, I'm guessing you are using the User Instance feature provided by SQLExpress..

I'm also guessing you set the "Copy to Ouput directory" property of your database file(s) to "Always" and that you took a backup of your "original" database and not the one you are interested in, that's to say the one you were working with at debug/run time..

is this the case?

regards

|||Hi, thanks for your explanation, this will prove useful.

I found out from another forum that when performing a backup/restore it is better to do it using the release version of the application rather than testing in the IDE!

Once I did this all worked well.

Is there a correct syntax for writing a query?

Is there a best syntax for writing a query (specifically in sql server with
t-sql)? I've written a few with the JOIN keywords but I mostly write them
using '=', '*=', etc... Is there a more 'sql-compliant' way or is it just a
matter of preference?
THanks.*= and =* are old syntaxes and might lead to ambiguous queries.
SQL Server 2005 does not support this syntax.
Use the ansi-92 syntax of left outer and right outer join.
You can refer to BOL for more information on this.
Hope this helps.|||Use the Join keyword.
JOIN is part of the ANSI syntax.
BOL 2005 says:
The outer join operators (*= and =*) are not supported when the
compatibility level of the database is set to 90.
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"VMI" <VMI@.discussions.microsoft.com> wrote in message
news:243B3E9F-5935-46BE-A6A6-A1C51DF6B1F7@.microsoft.com...
> Is there a best syntax for writing a query (specifically in sql server
> with
> t-sql)? I've written a few with the JOIN keywords but I mostly write them
> using '=', '*=', etc... Is there a more 'sql-compliant' way or is it just
> a
> matter of preference?
> THanks.|||> Is there a best syntax for writing a query (specifically in sql server with
> t-sql)? I've written a few with the JOIN keywords but I mostly write them
> using '=', '*=', etc... Is there a more 'sql-compliant' way or is it just
a
> matter of preference?
>
There are situations where *= can actually give you bad results. Stay away
from it.|||Can you give an example. Say you have the same value repeated in 3 rows for
a
column, if you want to remove duplicates and still get 3 rows. Then what
value do you want to have in that column?|||Oops.. wrong post :(

Is my SProc written correctly?

I am very new to SQL server and I'm using stored procedures for my program. So far I wrote one tonight, that works just fine.

I haven't really written one before, but its kind of similar syntax since I know C++/C# and VB.

My question is, even though this works for what I need it do, is it written correctly? Can you see any problems with it, or would you have done it differently?

I want to make sure its done correctly, and it runs as fast as possible.

Thanks!

[pre]

CREATE PROCEDURE CreateNewUser

@.UserID int out,
@.LoginID nvarchar(30),
@.Password nvarchar(30),
@.RegisterDate smalldatetime,
@.LoginIDExists nvarchar(30)
AS
/* Check to see if the loginID is already in use before attempting to save it.
We MUST have a unique loginID for each user */
SELECT @.LoginIDExists = loginID FROM users WHERE loginID = @.LoginID

/* If we pulled a value from the database, then the loginID already exists, return with error code 1 */
IF (@.LoginIDExists = @.LoginID)

BEGIN
SELECT 1
RETURN
END

ELSE BEGIN
/* The loginID does not already exist, attemp to add the new user to the database. */
INSERT INTO users (
loginID,
loginpassword,
registerDate )
VALUES (
@.LoginID,
@.Password,
@.RegisterDate )

/* Grab the UserID for the new user. */
SELECT @.UserID = @.@.identity

/* return with error code 0 */
SELECT 0

RETURN

END
GO

[/pre]

I think it's ok, only some suggestions:

Replace the @.@.IDENTITY toSCOPE_IDENTITY()

Instead of using the SELECT statement to return the error code, you can use the RETURN statement. If you use the RETURN statement, you need to add a parameter and set the ParameterDirection to ReturnValue to the ADO.Net Command object.

|||Personally, I'd simplify it a bit more by removing the @.LoginExistsparameter. I'd take Fredrik's suggestions, plus I'd do the RETURNvalues slightly differently, and I'd do an @.@.ERROR check:

CREATE PROCEDURE CreateNewUser
@.UserID int OUTPUT,
@.LoginID nvarchar(30),
@.Password nvarchar(30),
@.RegisterDate smalldatetime
AS
DECLARE @.Result INT

/* Check to see if the loginID is already in use before attempting to save it.
We MUST have a unique loginID for each user */
IF EXISTS(SELECT loginID FROM users WHERE loginID = @.LoginID)

/* If we pulled a value from the database, then the loginID already exists, return with error code -1 */
BEGIN
SELECT @.RESULT = -1
END

ELSE
BEGIN
/* The loginID does not already exist, attempt to add the new user to the database. */
INSERT INTO users (
loginID,
loginpassword,
registerDate )
VALUES (
@.LoginID,
@.Password,
@.RegisterDate )

/* Grab the @.@.Error and the UserID for the new user. */

SELECT @.Result = @.@.Error, @.UserID = Scope_Identity()

END

/* @.Result = -1 if the user was already on file, 0 if the user was added, and something else if the
user was attempted to be inserted and the INSERT failed */

RETURN @.Result

|||What I just posted allows a gap between the database check and thedatabase insert where someone else could insert a record with thatloginID (your original code also allowed this). Below I've addeda TRASNACTION and an UPDLOCK which will prevent that from happening:

CREATE PROCEDURE CreateNewUser
@.UserID int OUTPUT,
@.LoginID nvarchar(30),
@.Password nvarchar(30),
@.RegisterDate smalldatetime
AS
DECLARE @.Result INT

/* Check to see if the loginID is already in use before attempting to save it.
We MUST have a unique loginID for each user */

BEGIN TRANSACTION
IF EXISTS(SELECT loginID FROM usersWITH (UPDLOCK) WHERE loginID = @.LoginID)

/* If we pulled a value from the database, then the loginID already exists, return with error code -1 */
BEGIN
SELECT @.RESULT = -1
END

ELSE
BEGIN
/* The loginID does not already exist, attempt to add the new user to the database. */
INSERT INTO users (
loginID,
loginpassword,
registerDate )
VALUES (
@.LoginID,
@.Password,
@.RegisterDate )

/* Grab the @.@.Error and the UserID for the new user. */

SELECT @.Result = @.@.Error, @.UserID = Scope_Identity()

END

/* @.Result = -1 if the user was already on file, 0 if the user was added, and something else if the
user was attempted to be inserted and the INSERT failed . COMMIT the transaction if there
were no errors, otherwise, ROLLBACK */

IF @.Result = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
RETURN @.Result|||

Thank's that's pretty cool. I need to get a book on SQL Server so I can learn to do all this stuff better.

I copied the code so I can modify my procedure later. I was going to make one procedure for each function, like CreateUser, EditUser, DeleteUser, ChangePassword, etc...

Do you think this is a good idea or should I include them all in one procedure.

I wanted to keep them small and simple. I didn't want to end up with a 100+ line procedure. I figured it would be better to do everything seperatly, and there's less code to run on each procedure so maybe they will be a little faster.

Thanks.

|||

Bluebarry wrote:

I copied the code so I can modify my procedure later. I was going tomake one procedure for each function, like CreateUser, EditUser,DeleteUser, ChangePassword, etc...

Personally, I like separate procedures. I think each storedprocedure should have one function. But if you have a lotof tables, that could mean a lot of lot of stored procedures.|||

tmorton wrote:

Bluebarry wrote:

I copied the code so I can modify my procedure later. I was going to make one procedure for each function, like CreateUser, EditUser, DeleteUser, ChangePassword, etc...

Personally, I like separate procedures. I think each stored procedure should have one function. But if you have a lot of tables, that could mean a lot of lot of stored procedures.

I dont care too much how many procedures i have unless its going to effect performance. i think having one large procedure would hurt performance more then several smaller procedures. That single procedure has to run more lines of code and do more calculations, like (If we are inserting to this, otherwise do this)... I write my programs in the same way I create many small simple methods rather then a few large methods. It makes it easier to keep bugs out of the program too.