Is it a good idea to use SQL2005 MDF for audit, exception tracking

I am writing a web application that uses a Teradata database as the primary data source. While Teradata is great as a data warehouse and managing Terabytes of information it doesn't do as well when update or inserting. I was thinking of using a local SQL2005 MDF file to hold a few reference tables and an audit table to collect usage information and exception database to capture any errors.

There could be a few thousand users of the web application but no more than a couple hundred at a time.

I just trying to get some opinions on these technique. I am open to all comments and suggestions.

Thank You

Hi John,

If you're using the SQL Server as the auditing database and exception tracking, I think it will be fine.

Although there will be hundreds of connections to the main app, the exception will not be much and audit data size will not be huge then. Just connect the audit and exception handling module to your SQL Server database, and it will be OK.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Kevin

Thank you. I'm wondering if it is acceptable practice to run SQL Server 2005 express rather than installing a full SQL Server 2005 on the Advanced Server for doing the Audit tracking and exception reporting. The MDF file should never reach the 4GB limit of Express. Thank you again for your answer.

is it a bug in SSCE OLEDB ?

i'm use this code ,in SQL2005 std and ACCESS database, it work

but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()

Exception : OleDbException

0x80040E30L

DB_E_BADTYPENAME

Code Snippet

OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");

od.Open();

OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@.billno,@.checkouttime,@.finalprice,@.handle,@.ischeckout,@.memo,@.paymode,@.trick)", od);

og.Parameters.Add("@.billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@.checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@.finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@.handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@.ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@.memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.trick", OleDbType.VarWChar).Value = "";

og.ExecuteScalar();

od.Close();

why the same code is not work? i'm find all MSDN ,but there is no answer

Who can help me,Thanks

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

|||Have you tried using ExecuteNonQuery in stead of ExecuteScalar

|||

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

|||

Jo?o Paulo Figueira wrote:

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

yes,it's works

but,i want to use the same code on SSEE SSCE ACCESS

so i use OLEDB parameter ,but it's error

|||

Rajagopal R V wrote:

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

the SSCE managed provider is sqlceconnection ,the SSEE managed provider is sqlconnection

if i use this provider ,i will write 3 different query on SSEE SSCE ACCESS

don't have an easy Method ?

thanks

|||

You could use database independent base classes in your code (e.g. DbConnection) and specific providers for each database:

DbConnection connection = new SqlConnection(...); // Or SqlCeConnection or whatever else.

connection.Open();

DbCommand command = connection.CreateCommand();

command.CommandText = "Select whatever from somewhere where some = ?";

command.Parameter.Add(...);

Usually it makes little sense because SQL dialects are slightly different and query for one database won’t work on another (unless it’s really basic).

is it a bug in SSCE OLEDB ?

i'm use this code ,in SQL2005 std and ACCESS database, it work

but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()

Exception : OleDbException

0x80040E30L

DB_E_BADTYPENAME

Code Snippet

OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");

od.Open();

OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@.billno,@.checkouttime,@.finalprice,@.handle,@.ischeckout,@.memo,@.paymode,@.trick)", od);

og.Parameters.Add("@.billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@.checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@.finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@.handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@.ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@.memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.trick", OleDbType.VarWChar).Value = "";

og.ExecuteScalar();

od.Close();

why the same code is not work? i'm find all MSDN ,but there is no answer

Who can help me,Thanks

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

|||Have you tried using ExecuteNonQuery in stead of ExecuteScalar

|||

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

|||

Jo?o Paulo Figueira wrote:

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

yes,it's works

but,i want to use the same code on SSEE SSCE ACCESS

so i use OLEDB parameter ,but it's error

|||

Rajagopal R V wrote:

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

the SSCE managed provider is sqlceconnection ,the SSEE managed provider is sqlconnection

if i use this provider ,i will write 3 different query on SSEE SSCE ACCESS

don't have an easy Method ?

thanks

|||

You could use database independent base classes in your code (e.g. DbConnection) and specific providers for each database:

DbConnection connection = new SqlConnection(...); // Or SqlCeConnection or whatever else.

connection.Open();

DbCommand command = connection.CreateCommand();

command.CommandText = "Select whatever from somewhere where some = ?";

command.Parameter.Add(...);

Usually it makes little sense because SQL dialects are slightly different and query for one database won’t work on another (unless it’s really basic).