Re: Thread problem in multithreaded database applications - Database Discussion Boards

Hi,

Our requirement is to have an automated email sent to particular addresses every night by reading records from a table meeting particular condition , generating report from those records .

How to achieve this in case of sql server 2000?

or is it that , i need to write an service in dot net and let it run on the system at at particular time it will read the table and generate report and send it to particular addresses?

Please give me the proven aproach which I can take?

regards

Vijay

Assuming you can get sp_smtp_sendmail or xp_smtp_sendmail working, you can write T-SQL statements to dynamically build your recipients, subject, message etc. from your tables and send it that way.

I haven't done this directly, so please don't ask me to send the code... work with it, and, when you have a solution, share it with us.

Tim

A little bit more info:

xp_smtp_sendmail is not native SQL Server procedure but a third party DLL. The advantage is that it can operate without an email client (although the DLL must be installed).

xp_sendmail is a native procedure but it requires a MAPI client installed on the server.

If the query doesn't need bind variables, you can embed it to xp_sendmail-call as a parameter. In this case the query is run by xp_sendmail and the output of the query is sent to the recipients defined by parameters.

Mika

Hello,
we developed a windows application in visual c# using sql server 2005 standart edition. This application is multi-threaded. There are threads that transmits data packages to database.

After some time, we got an error. We wrote this error to the log:

"A new connection was rejected because the maximum number of connections on session ID 51 has been reached. Close an existing connection on this session and retry. [CLIENT: 192.168.1.116]"

How can this error can be fixed?

And also in multithreaded applications, is sql connection brokens possible? Is there a limit to connections that threads are running in sql server?

Well, you should follow the principle of open late and close early (remember to dispose of the connection), not keeping your connection open all the time. You should also look at connection pooling as this could help too.

Bob
Ashfield Consultants Ltd

We need DB connection all the time because threads are using the DB all time. So why open and close it every time? Is not opening and closing a connection expensive?

While our windows program runs, the connection is strangely not null and its state is open. But after some time we take the error : "A new connection was rejected because the maximum number of connections on session ID 51 has been reached. Close an existing connection on this session and retry. [CLIENT: 192.168.1.116].

ahmeday wrote:
Is not opening and closing a connection expensive?

Check out my suggestion about connection pooling - this basically means when you close the connection, in the pool it is kept open for a while and if another connection using EXACTLY the same connection string is requested it is just allocated the already open one from the pool. There are more details, but that's it in a nutshell.

What are you runnning on each thread? From the error it sounds like you are trying to establish a new connection anyway.

Can you post the code?

Bob
Ashfield Consultants Ltd

The application is three-tied so it is hard to post the code here Smile | :)

Exactly the error message is related to the TdsParser. In Ado.net, the connection breaks after some time for example 10-20 hours. And our idea to solve this is when the connection disconnects, we create a new connection and send the old one to the pool. What you suggest about this? Is it reasonable. And our program is windows service. It runs all the time on background. If network connection breaks from ethernet, out sql server connection may be disconnect. Our server and our program are in diffent machines. there are many reasons.

This is error that was written to the log. It is in event viewer:

"Message : Object reference not set to an instance of an object.
Source : System.Data
Stack : at System.Data.SqlClient.TdsParser.AddSqlErrorToCollection(SqlErrorCollection& temp, SqlErrorCollection& InputCollection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at EnmosService.Data.PackageLog.dsPackageLogTableAdapters.PrcDailyPackagesStatusTableAdapter.PrcDailyPackages(Nullable`1 DICId, Nullable`1 Sent, Nullable`1 Receive, Nullable`1 Error)
at EnmosService.Data.PackageLog.DailyPackagesStatus.SetDailyPack "

Thanks a lot.

if have two table like the following:
name course
------- ---------
mahmoud c++
osam java

and the second column as the following:
name course1
--------- ---------
mahmoud db
osama c#
najeeb vb.net
yahya xml

please help me to make the following result
name course course1
--------- ---------- ------------
mahmoud c++ null
osama java null
mahmoud null db
osama null c#
najeeb null vb.net
yahya null xml

thank you

select t1.name, t1.course, t2.course
from table1 t1
left outer join table2 t2 on t2.name = t1.name

(this assumes everyone in table2 is in table)

Bob
Ashfield Consultants Ltd

the query you sent not return the data as i explained in the result

thank you

Well, I did say it assumed all the names in table 2 were in table 1!

To be honest, its a rubbish design in the first place, and whoever designed it should be sacked. However, I expect in reality I am doing your homework for you, so here you are:

select t1.name, t1.course, t2.course
from table_1 t1
left outer join table_2 t2 on t2.name = t1.name
union
select t2.name, t1.course, t2.course
from table_2 t2
left outer join table_1 t1 on t1.name = t2.name
where t1.name is null

Bob
Ashfield Consultants Ltd

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.name = table2.name

"We can't solve problems by using the same kind of thinking we used when we created them"

the solution is not correct

SELECT b.n, a.c, b.c AS c1
FROM a RIGHT OUTER JOIN
b ON a.n = b.n
UNION ALL
SELECT a.n, a.c, b.c AS Expr1
FROM a LEFT OUTER JOIN
b ON a.n = b.n

Venky

Hello

I want to fetch record in a pair of 100. First time I want Records from 1 to 100 then 101 to 200 and so on.

Is there any way to solve this problem.

Thanks

Syed Shahid Hussain

You can use Ranking Functions (Transact-SQL)[^] in order to paginate your data.

Giorgi Dalakishvili

#region signature
my articles
#endregion

When i installed i got this error.so what should i do
The error i found:
sql server failed to start sqlserver books online:"how to view sql server 2005 setup Logfiles and starting sql server manually

To get back your sql 2005 help / documentation, you need to re-install the sql server client tools

Venky

When display MA lines, does it need to save the MA values into the database after the first calculation of these values?
I'm not sure whether the performance is a problem. If not, the software will be more agile by not saving the intermediate data.

system

Do you think anyone knows what you are talking about? What is MA?

Bob
Ashfield Consultants Ltd

MA = Moving Average

For example, I have 5 numbers as below
1 2 3 4 5
now MA2 means the average of every 2 numbers, it all like this

original: 1 2 3 4 5 6
ma2: - 1.5 2.5 3.5 4.5 5.5

MA2(n) = (ORG(n)+ORG(n-1))/2;

system

do you need to retreive the interim values from the database EVER. This is your answer.

Never underestimate the power of human stupidity
RAH

Hi,

can i use excel "almost" like a database?
i thinks on using a sheet like table and workbook like database,validations fine (don´t need key: i don´t get many errors from there)

My problem is the sql statements, can i query it(excel data)?
i don´t know how, i think also sql statement in c# don´t work in this case.
must i use xml?

Cry | :((

(no code needed)

thanks Big Grin | :-D

nelsonpaixao@yahoo.com.br

trying to help & get help

You can use VB6 which lets you use SQL statements. I don't think you can use XMl unless it's 2003 or above. No C#.

Blog link to be reinstated at a later date.