|
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
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
|
|
|
|
|
|
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?
(no code needed)
thanks
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.
|
|
|
|
|
Hello,
You can use excel sheet as an Database, and you can also query it like regular Access or Sql Database.you can use oledb connection to excel file from your vb.net or c# application.
Use the connection string below,
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=XLFile.XLS; Extended Properties="Excel 8.0; HDR=No;"
|
|
|
|