|
|
|
SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes FROM TableName start, TableName end WHERE start.ID = end.ID AND start.Status = 'Ready' AND end.Status = 'Completed' and (Date_Timeworked between '2008-09-10' and '2008-09-10' and start.Status = 'Ready' and start.Status = 'Rejected')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
use getdatetime() to get now time.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
Whats the best way for importing a fixed length file to SQL Server 2005. Currently I am using DTS. Are there any better ways to achieve this??
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
It's hard to say what's a better way since you don't specify what is it that you are not satisfied with in DTS.
However, another alternative could be using bcp (Bulk Copy Program) from command line or SqlBulkCopy class from C#.
Mika
|
|
|
|
|
Is it possible to count rows with a multi-selected query? I have no problems counting in one table however, my real goal is to count rows of mutiple tables.
Here's a sample. This counts for one table
SELECT COUNT(*) FROM table_1 WHERE condition
And what I want is to count this multiselected one:
SELECT table_1.column1,table_2.column4,table_3.column7 FROM table_1,table_2,table_3 WHERE condition
Do you know how to count the rows of this one? Is it possible? Thanks!
|
|
|
|
|
SELECT table_1.column1,<br />
(select count(*) from table1 as t1 where t1.col = table_1.col) as CountTable1,<br />
<br />
table_2.column4,<br />
(select count(*) from table2 as t2 where t2.col = table_2.col) as CountTable2,<br />
<br />
table_3.column7<br />
(select count(*) from table3 as t3 where t3.col = table_3.col) as CountTable3<br />
<br />
FROM table_1,table_2,table_3 WHERE condition
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I GOT IT! Thanks! 
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I've spent half of the day trying to install SQL Server 2008, but it wouldn't install. At the very end it shows me a table saying that "Database Engine Services Failed", and "Replication Failed". Evidently, because of this failures, the service in the services (control panel) will not start
|
|
|
|
|
The only thing that comes in mind is that this could be a result if the computer is a domain controller.
|
|
|
|
|
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 
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"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
