|
select CallId,
max(case when status like 'dialing%' then dtime end) as startcall,
max(case when status like 'line%' then dtime end) as endcall,
max(case when status like 'line%' then trunk end) as trunk
from Table1
group by CallId
order by CallId
This assumes that all of the records for a single phone call share a unique identifier named "CallId".
|
|
|
|
|
I have a sqlconnection to a sql server 2005.
This server is in one server and my application is in another server.
I create a userid and password but when i try to connect to my database an error saying
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
appears. I do not know what is the problem for this.
Can somebody help me
Thanks
Javierarka
|
|
|
|
|
Your connection string requests a trusted connection (this overrides any user name and password) and it can't create one for some reason or another. Possibly because the SQL Server is on another machine and you are connecting with a machine account rather than a domain account.
|
|
|
|
|
In VS2005 you could go to Server Explorer and set up a successful connection with your target database. Once you have established a successful connection you can then click on the server and the properties dialog will show the ConnectionString . You can put this in your code to validate that the connection works and then use the supplied string to target where your error is at.
|
|
|
|
|
Hi all,
I have a stored procedure which runs on Sql Server 2005 which returns 5 rows representing exchange rates. If I call this procedure in Sql Server management studio it responds in about a second. If however I do this using ADO.NET, it takes many times longer and sometimes times out.
Incidentally, there is a table which contains over 100 million rows used in a join in this proc which could point to performance issues, but as I say it works ok within Management studio.
Any ideas?
ADO code below...
using (SqlConnection conn = new SqlConnection(Utils.ConnectionString))
{
conn.Open();
SqlCommand command = new SqlCommand("FxGetRates", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@date", SqlDbType.SmallDateTime).Value = valueDate;
List<FxRate> results = new List<FxRate>();
using (SqlDataReader reader = command.ExecuteReader())
{
int ord_assetId = reader.GetOrdinal("AssetId");
int ord_domesticcurrencyid = reader.GetOrdinal("DomesticCurrencyId");
int ord_foreigncurrencyid = reader.GetOrdinal("ForeignCurrencyId");
int ord_marketdatasourceid = reader.GetOrdinal("MarketDataSourceId");
int ord_rate = reader.GetOrdinal("Rate");
while (reader.Read())
{
FxRate result = new FxRate();
result.AssetId = reader.GetInt32(ord_assetId);
result.DomesticCurrencyId = reader.GetInt32(ord_domesticcurrencyid);
result.ForeignCurrencyId = reader.GetInt32(ord_foreigncurrencyid);
result.MarketDataSourceId = reader.GetInt32(ord_marketdatasourceid);
result.Rate = reader.GetDouble(ord_rate);
results.Add(result);
}
}
return results.ToArray();
Regards,
Rob Philpott.
|
|
|
|
|
Roughly how many rows would you expect this to return? It looks as though the problem is in the creation of the FxRate rows and the addition of it to the List.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks for the reply.
Just five. And when debugging its apparant that the time is spent on the ExecuteReader statement, not in getting the values out from it later on.
Regards,
Rob Philpott.
|
|
|
|
|
OK - this is where I do my shameless plug and point you towards your new best friend;) - SQL Profiler. This article Improving the performance of queries with SQL Server Part 1[^] should be able to help you (or at least point you in the right direction).
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Have you tried single-stepping using the debugger? If the performance problem is not your stored procedure then perhaps opening the connection that is slow.
|
|
|
|
|
what is the query for removing table space in oracle
Warm Regards,
Mushq
|
|
|
|
|
|
hi ,
i want to send email thru sql server, how can i do this
sdfsdfsdfs
|
|
|
|
|
hi
This is the syntax for sending email in sql server 2005
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
regards
dharani
|
|
|
|
|
How to get different result/row of following select query :
Select * from Countries where country='India'
or
Select * from Countries where country='INDIA'
means Case-Sensitive searching in Character column.
Thanks in Advance
Regards
Girish Kumar Sharma
|
|
|
|
|
User UPPER/LOWER as below:
select upper('AsGas'),lower('AsGas')
if it doesn't solve your problem explain more...
Human knowlege belongs to the world
|
|
|
|
|
i mean; if there are two rows named "India" and "INDIA"; and if i want to get "India" then "INDIA" should not be there; and if i want "INDIA" then "India" should not be there.
Only given string should be in selection criteria please.
Data is in MS-Access.
Thanks in Advance.
Girish Kumar Sharma
-- modified at 6:34 Tuesday 16th January, 2007
|
|
|
|
|
You could cast is as a VARBINARY and compare the binary versions. I've never tried it, but you could give it a go:
SELECT *
FROM MyTable
WHERE CAST(MyColumn AS VARBINARY(100)) = CAST('SomeValue' AS VARBINARY(100));
|
|
|
|
|
Colin Angus Mackay wrote: SELECT * FROM MyTable WHERE CAST(MyColumn AS VARBINARY(100)) = CAST('SomeValue' AS VARBINARY(100));
Hi
Just curious to know if cast works on MS Access database?
Thanks
Chandra
|
|
|
|
|
ChandraRam wrote: Just curious to know if cast works on MS Access database?
No idea.
|
|
|
|
|
Oh, ok... the OP had specified Access and I was wondering... will try it at home tonight and post result here, anyway
|
|
|
|
|
Nope - there is no CAST function in MS Access... there are a few type conversion functions available, but will probably not help the OP.
|
|
|
|
|
Then, how to solve this string comparison problem in MS-Access; bcoz there is no such ready-made function available; by which user can match/compare two strings.
|
|
|
|
|
Hi all,
I want to install my program on multiple computers on a network, and my database on just one of them, then all the different computers run from the one database.
I'm using SQL Server 2005 Express, do I need to install the instance on each of the machines with the Disable Network Protocols set to 0, or do I just need to install the instance on the computer hosting the database, because I will point the connection of my program to that computer\instance, that's why I wonder if I will need to install the instance on every computer?
Is there maybe someway that I can install the instances on each machine, then point the current machine instance to the hosting machine instance, so that sql server can handle the transactions on the database? Hope it makes sense what I'm asking
He who laughs last is a bit on the slow side
|
|
|
|
|
NeroToxic wrote: do I need to install the instance on each of the machines with the Disable Network Protocols set to 0, or do I just need to install the instance on the computer hosting the database, because I will point the connection of my program to that computer\instance, that's why I wonder if I will need to install the instance on every computer?
The SQL Server must be on the same machine as the database. The applications must then connect to the SQL Server.
NeroToxic wrote: Is there maybe someway that I can install the instances on each machine, then point the current machine instance to the hosting machine instance, so that sql server can handle the transactions on the database? Hope it makes sense what I'm asking
Not really. How would installing SQL Server on each individual machine help SQL Server manage the transactions.
Installing SQL Server on one central machine helps transactions because all clients connect through the SQL Server process. The process then manages the transactions between all clients.
If this doesn't help, then it might be better if you just stated the outcome you wanted and asked how to get there.
|
|
|
|
|
Thank you for the reply.
Colin Angus Mackay wrote: Not really. How would installing SQL Server on each individual machine help SQL Server manage the transactions.
I had problems with SQL Server 2000, when multiple clients tried to access the same table simultaneously it threw an exception stating that the table is currently in use, was just a thought that I had.
Thanks again, organized a machine that I can install and test remote connections on to the database, will see if it works properly, this wasn't really intended for our application but recently got the request from one of our clients to run from multiple machines to one database.
He who laughs last is a bit on the slow side
|
|
|
|