473,289 Members | 1,923 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

low cost method to check database before inserting data

I developed a console application that will continually check a message
queue to watch for any incoming data that needs to be inserted into MS
SQL database.

What would be a low-cost method I could use inside this console
application to make sure the MS SQL database is operational before I
perform the insert?

Jul 23 '05 #1
7 3380
You can use DATABASEPROPERTYEX() in TSQL or the Database.Status
property in SQLDMO to see if a particular database is available.

If that doesn't help, you'll probably need to give some more
information about how your console app will connect to MSSQL (ODBC, ADO
etc.).

Simon

Jul 23 '05 #2
I don't think I could use any TSQL code since that would live on the
database and if the database server was down, I would not be able to
access the code.

What I think I need is a way to fast and cheap way to "ping" the
database server to ensure that it's up and accepting connections before
the console application attempts to connect and use a stored procedure
to insert the data.

If the "ping" failed, the console application would wait a few minutes
before trying again to insert data.

Maybe in terms of greater scalability, a better way would be to go
ahead and insert the data and if the database was down for some reasons
and it returned a "insert failed. - no such database." status. The
console application could go into a "waiting" mode to wait a few
minutes before attempting to insert the data again. Once the database
is back online, the console application would resume "normal" mode to
keep on inserting data as it's found on the MSMQ Queue.

Jul 23 '05 #3

"Jared Evans" <jn*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I don't think I could use any TSQL code since that would live on the
database and if the database server was down, I would not be able to
access the code.

What I think I need is a way to fast and cheap way to "ping" the
database server to ensure that it's up and accepting connections before
the console application attempts to connect and use a stored procedure
to insert the data.

If the "ping" failed, the console application would wait a few minutes
before trying again to insert data.

Maybe in terms of greater scalability, a better way would be to go
ahead and insert the data and if the database was down for some reasons
and it returned a "insert failed. - no such database." status. The
console application could go into a "waiting" mode to wait a few
minutes before attempting to insert the data again. Once the database
is back online, the console application would resume "normal" mode to
keep on inserting data as it's found on the MSMQ Queue.


You seem to be mixing the terms "server" and "database" a bit - the server
can be up and accepting connections, but one individual database may be
unavailable. So you'll have to decide what level of checking you want to do
before trying the INSERT - check the Windows service is running, check you
can login to MSSQL, check the database is available, check you can execute
the proc etc. At one end of the scale, if you get very few messages then
just try connecting and inserting; at the other, with very large numbers of
messages, you might find it's worth keeping an open connection. It's
probably a case of just experimenting to find the right balance for your
application.

Simon
Jul 23 '05 #4
The SQL Agent alert engine pings the server periodically with a TSQL
statement, you could just take a leaf out of their book.

USE master
GO
SELECT 'Testing Connection...'

Mr Tea

"Jared Evans" <jn*****@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
I developed a console application that will continually check a message
queue to watch for any incoming data that needs to be inserted into MS
SQL database.

What would be a low-cost method I could use inside this console
application to make sure the MS SQL database is operational before I
perform the insert?

Jul 23 '05 #5
I would hate to have to poll the database every time a data insert was
attempted. Something about this concept doesn't sit right with me. I
am expecting periods of high volume of data inserts but I'm also
expecting the database and the server to be up the vast majority of
time. The database would only be taken offline for upgrade reasons or
unexpected hardware failure.

While double-checking with the database prior to any data insert sounds
like it would be more reliable, it would also take away performance
that could be applied elsewhere. I think it would be more
architecturally sound if the console application was able to revert to
a "waiting" mode once it detected that the database was not available
regardless of if the database server was accepting connections or not.

Jul 23 '05 #6
By detection when the database is not available, I meant that when the
insert attempt returns an error message or exception.

Jul 23 '05 #7
Jared Evans (jn*****@gmail.com) writes:
I don't think I could use any TSQL code since that would live on the
database and if the database server was down, I would not be able to
access the code. What I think I need is a way to fast and cheap way to "ping" the
database server to ensure that it's up and accepting connections before
the console application attempts to connect and use a stored procedure
to insert the data.


The simple-minded way of doing it would simply be to try to connect. If
the connect fails, the server is not available. If the connect succeeds,
submit your query. You can reduce the connection timeout, if you don't
want wait 15 seconds when the server is down.

It could also be worth considering turning off connection pooling, so
that you really get a connection attempt each time. Then again, that
requires you to stay connected to complete all inserts once your
are connected.

Note that if the server is up, but the database is unavailable for
some reason, and you connection string specifies the datbase to
connect to, your connection will fail.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
by: el_roachmeister | last post by:
Is there a way to make a text link post to a form without passing all the parameters in the url? The urls tend to get very long and messy. I often wonder if there is a limit to how long they can...
3
by: Dan | last post by:
I am a relatively new user on Oracle 9.2.0.1 and I am having trouble performance tuning this production database. I am running a large query that joins two tables, document(3 mil) and entity(9...
41
by: Matt Alanzo | last post by:
Our SOHO 2 person compay sells furniture (not programmers). In '98 we paid $,$$$ for a VBA -Access '97 accounting application, including VBA source code .... an huge investment for us then (and...
5
by: Vicky via DotNetMonster.com | last post by:
Hi, I need help with "An object reference is required for the nonstatic field, method, or property 'dataReader.Class1.data'" Before I put folowing variable in class level, it works fine....
2
by: altergothen | last post by:
Hi there I am a newbie to ASP.Net - Please Help! I am trying to insert the values of my variables into a database. If I try the following it works perfectly: string insertQuery = "INSERT into...
2
by: Fred Flintstone | last post by:
What's the difference between these two methods? 1 - Parameterrized SQL queries: Dim CommandObject As New Data.SqlClient.SqlCommand With CommandObject .Connection = myConnection...
0
by: dba123 | last post by:
I have a requirement/standard that we must use the Enterprise Library Data class to implement our Database transactions for our public website such as inserting data, etc. So, with that, I have...
5
by: Alan Little | last post by:
I have affiliates submitting batches of anywhere from 10 to several hundred orders. Each order in the batch must include an order ID, originated by the affiliate, which must be unique across all...
0
MrMancunian
by: MrMancunian | last post by:
How to create a database connection without using wizards Introduction I've seen a lot of questions on the net about getting data from, and saving data to databases. Here's a little insight how...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.