|
|
thanks hasan
|
|
|
|
|
Hello,
I have the following sql which will be stored a string, to be executed.
However, it gives me a syntax error -> Incorrect syntax near the keyword 'not'.
This is because sql server thinks its a keyword, how can I format my sql so that the arguments can be passed correctly.
<br />
BEGIN<br />
SET @SQL = 'CREATE PROCEDURE [dbo].[incident_selAllFilterRows]<br />
AS<br />
SELECT incidentID, company, subject, contact, phoneNo, assignedTo, priority<br />
FROM incident<br />
WHERE [status] = 'not started' AND [status] = 'in progress''<br />
EXEC sp_executeSQL @SQL END<br />
Many thanks,
Steve
|
|
|
|
|
steve_rm wrote: SET @SQL = 'CREATE PROCEDURE [dbo].[incident_selAllFilterRows]
AS
SELECT incidentID, company, subject, contact, phoneNo, assignedTo, priority
FROM incident
WHERE [status] = 'not started' AND [status] = 'in progress''
You have nested quotes (')... basically, the statement is terminated at the ' before the not and the rest of the statement is ignored / not recognised.
HTH
|
|
|
|
|
In addition to the previous respose. You might also want to check your WHERE clause for logical correctness.
WHERE [status] = 'not started' AND [status] = 'in progress'
What you are asking above is that the status column must be equal to "not started" AND "in progress" at the same time. Did you mean OR?
|
|
|
|
|
Missed that one...:->
Teaches me to read until the end
|
|
|
|
|
Thanks,
Single quotes should be used.
Steve
|
|
|
|
|
Hi,
I would like to know which is the most latest and good way of writing SQL Joins. Is it by using *=, =* etc.
eg:
select distinct * from employee,department
where employee.DepartmentID *=department.DepartmentID
( or)is it ANSI Standard?
Eg: SELECT distinct *
FROM employee
LEFT OUTER JOIN
department
ON employee.DepartmentID = department.DepartmentID
Please help.
rmr
|
|
|
|
|
shivarishxxxx wrote: select distinct * from employee,department
where employee.DepartmentID *=department.DepartmentID
This type of join is slowly being phased out. The Inner/Outer join is the way to go for you.
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 your prompt reply.
rmr
|
|
|
|
|
Does anybody know how to get the sql log information?
Thanks,
Rob
http://www.vwsolutions.com
|
|
|
|
|
Hello,
I have the following query; the results I need are returned however so are null values.
I need to exclude any results that are not defined in the case function.
select
(case when status like 'dialing%' then dtime end) as startcall
, (case when status like 'line%' then dtime end) as endcall
, (case when status like 'line%' then trunk end) as trunk
from
Table1
All of my references mention boolean logic, however; I am not sure how to apply it or if that's the right approach.
Any help or information would be greatly appreciated.
Regards,
Hulicat
|
|
|
|
|
Hulicat wrote: I need to exclude any results that are not defined in the case function
Then shouldn't it go in a WHERE clause?
Since I don't actually know what you are trying to achieve I cannot say further what the WHERE clause should look like.
|
|
|
|
|
I am trying to obtain only the results defined in the where clauses without any nulls
Sample of Current Results:
StartCall……………...........EndCall………………………………..........Trunk
NULL……... ..................12/4/2006 3:09:48 PM………..………. 8
NULL…………… .............NULL……………………………. ............NULL
NULL……………….. ...............NULL……………………………. ............NULL
NULL……………. ............NULL……………………………. ............NULL
NULL…………….. ............NULL……………………………. ............NULL
NULL…………..................NULL……………………………. ............NULL
NULL………………. ..........NULL……………………………. ............NULL
NULL…….......…............NULL……………………………. ............NULL
12/4/2006 3:27:57 PM….....NULL……………………………. ............NULL
NULL…….. ………...........NULL……………………………. ............NULL
NULL…….. ………...........NULL…………………………….. ............NULL
Desired output:
Startcall……………………….........EndCall…………………………...........Trunk
12/4/2006 3:27:57 PM……....…12/4/2006 3:09:48 PM……....…….8
Regards,
Hulicat
|
|
|
|
|
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
|
|
|
|
|