MS SQL Logging Util

[Mauldor]

I have a problem with a program which uses MSSQL as the database. Now in order to work out why it is crashing, I need to somehow see what it is trying to do in SQL and any errors - for example trying to write to a database with the wrong usename or fields misisng etc..

Anybody know of a program or way to achive this?

Originally Posted by Mauldor

I have a problem with a program which uses MSSQL as the database. Now in order to work out why it is crashing, I need to somehow see what it is trying to do in SQL and any errors - for example trying to write to a database with the wrong usename or fields misisng etc..

Anybody know of a program or way to achive this?

MSSQL Trace? (Haven't got it on this PC, but I think it's called SQL Performance Analyzer, if you've got the SQL management tools installed).

...or you can get to it from Enterprise Manager/Tools

[grandmaster]

you can use sql enterprise manager to view the last action of the user that processed the transaction.

(as long as (s) he does'nt move on and process anything else..

I used it to track down an error in a field where a user was using an ' in an address and SQL was whinging

[Caspar]

Originally Posted by Mauldor

I have a problem with a program which uses MSSQL as the database. Now in order to work out why it is crashing, I need to somehow see what it is trying to do in SQL and any errors - for example trying to write to a database with the wrong usename or fields misisng etc..

Anybody know of a program or way to achive this?

Have you tried the MSSQL tool Query Analyser or even Enterprise Manager.

[Mauldor]

Originally Posted by Caspar

Have you tried the MSSQL tool Query Analyser or even Enterprise Manager.

Indeed I have, I can see what Transactions its doing, tried the very same thing in Eneterprise Manager and they work fine, though the program is not updating this database. Maybe I never put enough Fields on Profiler (only think I have tried so far) to show Errors - I am trying to see what its doing and any errors and why sort of thing.. Oh well Ill keep looking

Profiler - that's what I was thinking of. Set up a trace, and you should see all the SQL statements submitted. You can then cut and paste iinto Query Analyser to check for errors.

[Mauldor]

Right he ho - finally got somewhere (though no closer to the answer like)..

I selected all the option on Profiler and ran this just before I knew where there would be a error and stopped it after the error, this gave me a better chance to see what the problem was.

Now to explain (as this might help) what it is I am doing, the program (a game server written in Chinese) Is running on a XP Box with everything set to Chinese PRC (so i can see if nothing else the txt files in chinese to aid me translating it). The trace was rouhgly like thus:

INSERT INTO Storage (First, Late, VipEndDate) VALUES ('Peter','Pan','20/07/2005 05:27:14')

Now if I run this on Enterprise Manager on the said database, I get the Error :

The Convertion of char data type to a DateTime DataType resulted in a out-of-range datetime value

VipEndDate by the way is defined as DateTime. I will go Google this to death and see what comes from it - cos I can type the date in no problems, just on a Update that it failes

Dates in computers are the bane of my life! If you can fix the code so that the date is formatted as 20 Jul 2005, before submitting the SQL, you'll be alright! (That's the easiest - I think there are ways of getting it to accept the data in dd/mm/yyyy format, but these are more complex and involve editing the registry, etc - even more complicated if the box is set to Chinese - what is the Chinese date format in Windows?).

[Richard M]

Originally Posted by Mauldor

Right he ho - finally got somewhere (though no closer to the answer like)..

I selected all the option on Profiler and ran this just before I knew where there would be a error and stopped it after the error, this gave me a better chance to see what the problem was.

Now to explain (as this might help) what it is I am doing, the program (a game server written in Chinese) Is running on a XP Box with everything set to Chinese PRC (so i can see if nothing else the txt files in chinese to aid me translating it). The trace was rouhgly like thus:

INSERT INTO Storage (First, Late, VipEndDate) VALUES ('Peter','Pan','20/07/2005 05:27:14')

Now if I run this on Enterprise Manager on the said database, I get the Error :

The Convertion of char data type to a DateTime DataType resulted in a out-of-range datetime value

VipEndDate by the way is defined as DateTime. I will go Google this to death and see what comes from it - cos I can type the date in no problems, just on a Update that it failes

Use:

INSERT INTO Storage (First, Late, VipEndDate) VALUES ('Peter','Pan','07/20/2005 05:27:14')

[Mauldor]

I changed my Regional Lang to USA and it worked, how i fathered this out was playing about with that Update Statement till I worked out what format it was hoping to get. Google showed LOTS of these Problems (for Coders) and MS-SQL - so its not Uniquie Problem. Many Thanks to everybody anyhow for the Help and it was Indeed Profiler - I must play with this Utility so more to see what the good triggers are to set !!

[Caspar]

Glad you sorted it ..and I agree with JohnHorb Dates are a real nightmare when developing