473,554 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3418
You can use DATABASEPROPERT YEX() 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.goo glegroups.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******** *************@o 13g2000cwo.goog legroups.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****@sommarsk og.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
2836
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 get?
3
3569
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 mil). I have reorganized my tablespaces so that the two tables are on different tablespaces, different disks. They both have their indexes stored...
41
3057
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 now!). The application publisher went belly up years ago. Over time we've made a number of VBA code changes (< 500 lines total). Now our CPA is urging...
5
13645
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. ------------------ string data; string valTicker = ""; string valPeriod = ""; --------------------- but after put them into class level I got above...
2
2560
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 test(name,surname,email) VALUES('Bob', 'Sly', 'bobsly@yahoo.com')"; but instead of inputing the values directly, I want to insert them as...
2
1499
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 .Parameters.Clear() .Parameters.Add("@TextField", SqlDbType.NVarChar, 50).Value = TextField
0
951
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 a method below which uses some methods from Enterprise.Data. I need to call and run this method asynchronously so that it doesn't bog down our...
5
2573
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 orders in all batches ever submitted by that affiliate. I'm trying to figure out the most efficient way to check the uniqueness of the order ID. ...
0
4739
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 to do that using .NET code. I prefer using code instead of wizards, because you are completely in charge of connections. This article is written for...
0
7578
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7780
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7530
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7862
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6119
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3539
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3530
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1111
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
812
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.