|
I normally test these things by opening two sessions in Query Analyzer. However if you want to test this using a stored procedure then use the "WaitFor" T-SQL command to wait for a period of time.
|
|
|
|
|
hi,
i want to use TABLOCKX lock on my table. i have used same query given in above post.
but problem is lock does not get released after completion of transaction holding lock on table. hence another transaction keeps waiting.
following are my 2 query i tried in query analyzer of sql server.
Please help me...
Query 1:
BEGIN TRAN A
SELECT * FROM alerts with (TABLOCKX, HOLDLOCK)
declare @cnt bigint
set @cnt = 1000000
print 'start time = '+cast(getdate() as varchar)
while @cnt > 0
begin
print 'Cnt = ' + cast(@cnt as varchar)
set @cnt = @cnt - 1
end
print 'end time = '+cast(getdate() as varchar)
COMMIT TRAN A
-----------------------------------------------------------------------------------
Query 2:
BEGIN TRAN B
print 'start time = '+cast(getdate() as varchar)
SELECT * FROM alerts --with (TABLOCKX)
print 'end time = '+cast(getdate() as varchar)
COMMIT TRAN B
Please let me know if i'm doing anything wrong.
Thank in advance...
Rajesh.
|
|
|
|
|
you can create trigger for dml staements such as select,update ,delete..So create a trigger while selecting the table.
PPK
|
|
|
|
|
Hi,
I work on a project which is connected to mysql.
But when I connect to mysql I connect with "localhost",
bu I can't connect with my IP.
Please help.
Thanks.
|
|
|
|
|
Can you connect using 127.0.0.1?
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
Hi,
Thanks for your reply but I can't connect with 127.0.0.1.
|
|
|
|
|
Hi, I don't really know MySQL but try:
Turning off any firewalls that might be running.
Making sure the service is started properly on your computer.
If thats not the problem perhaps try reinstalling MySQL.
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
how to programmatically add the values of a apecific field in a .mdb file.(only that field is blank, the other fields are filled)
thanks
|
|
|
|
|
Send the appropriate UPDATE SQL Command to the database.
|
|
|
|
|
Between Stored procedure and user defined function which is faster and why?
|
|
|
|
|
They solve different problems. A speed comparison is invalid.
|
|
|
|
|
Stored procedure is faster then user defined function because stored procedure is precompiled and user define function is compiled at the of call
So it should take little more time to respond.
I hope this will help you
Puneet Srivastava
|
|
|
|
|
Not true in SQL Server. SQL Server compiles the query plan of any query, including a stored procedure, the first time it is used. It then caches that query plan, giving a higher weighting to the plan for a stored procedure over an ad-hoc query with parameters, over an auto-parameterized ad-hoc query, over a query that couldn't be auto-parameterized.
If you want to force a stored procedure to be recompiled, use WITH RECOMPILE on the EXECUTE statement. To force a recompile every time, use WITH RECOMPILE on the CREATE PROCEDURE statement.
I believe the text of a user-defined function is effectively incorporated into the text of the query that called it, it does not get a separate query plan of its own. This may depend on whether it's a scalar function, an in-line table-valued function or a multi-statement table-valued function.
|
|
|
|
|
|
hi,
i have an sql table which has no primary key. i want to find out wether the same row is repeated. how can i see it.
regards
Ruwandi
rkherath
|
|
|
|
|
select field_name count(*) from tablename where group by field_name order by 1;
This query to find out the number of duplicate value.
|
|
|
|
|
its also the best query
SELECT *
FROM BB1
WHERE (DATE_STAMP,DB_NAME,TABLE_NAME) IN (SELECT DATE_STAMP,
DB_NAME,
TABLE_NAME
FROM BB1
GROUP BY DATE_STAMP,DB_NAME,TABLE_NAME
HAVING COUNT(* ) > 1);
|
|
|
|
|
hi ganesamoorthidhayalan,
it works. thanks a lot.
Regards
Ruwandi
rkherath
|
|
|
|
|
I'm using SQL to output some data from the 3 table below (Sales, Product,& Staff).
How if i want to display a table as this...Have any ideas??
ProductId TotalSale Sale_of_team1 sale_of_team2 sale_of_team3
P012 3 0 3 0
P016 27 7 0 20
P017 4 4 0 0
P019 2 0 2 0
P022 2 0 2 0
>>Sales
ProductId Date Week SalesUnit StaffId
P012 20070514 20 3 S002
P016 20070531 22 7 S001
P016 20070531 22 20 S003
P017 20070531 22 2 S001
P017 20070523 21 2 S001
P019 20070530 22 2 S002
P022 20070502 18 2 S002
>>Product
ProductId ProductName Category Cost
P012 ADSL 512M/256K Streamyx 77
P016 LT 38 Lets Talk 38
P017 PLAN A Lets Talk 68
P019 PLAN C Lets Talk 198
P022 Italk Mobile-50 Italk Prepaid Cards 50
>>Staff
StaffId StaffName Team Level
S001 Jass 1 1
S002 kwee 2 2
S003 Rain 3 2
Thank for helping...arigatou gozaimasu!!!
|
|
|
|
|
The following should do the trick:
SELECT Sales.ProductId,
SUM(Sales.SalesUnit) AS TotalSale,
SUM(CASE Staff.Team = 1 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team1,
SUM(CASE Staff.Team = 2 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team2,
SUM(CASE Staff.Team = 3 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team3
FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
GROUP BY Sales.ProductId The "case" clauses allow you to conditionally summate a column.
Or
SELECT Product.ProductId,
(SELECT SUM(SalesUnit) FROM Sales) AS TotalSale,
(SELECT SUM(SalesUnit) FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
AND Staff.Team = 1
WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team1,
(SELECT SUM(SalesUnit) FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
AND Staff.Team = 2
WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team2,
(SELECT SUM(SalesUnit) FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
AND Staff.Team = 3
WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team3
FROM ProductsThis uses correlated sub-queries to do the work.
Regards
Andy
|
|
|
|
|
hi All,
i have a dates field which contains dates as varchar type
Dates
(yyyymmdd)
20071231
20070312
20071123
i wanna convert this in to
yyyy/mm/dd
2007/12/31
2007/03/12
2007/11/23
how can i do this please tell me
convert(varchar,dates,101)
didnt include the '/'
regards
Ruwandi
rkherath
|
|
|
|
|
to get the dates with "/" seperated you can use
substring(dates, 1, 4) + '/' + substring(dates, 5, 2) + '/' + substring(dates, 7, 2)
But the dates remains varchar
convert function actually converts to datetime data type.
Regards
KP
|
|
|
|
|
hi,
thank. i got it .
thx a lot.
regards
Ruwandi
rkherath
|
|
|
|
|
I need to change the display from (123.98) to -123.98 in SSRS.
I tried couple of fromat option on text box and still no good results...
Any Help!!
Amit
|
|
|
|
|
Hi,
I have one procedure in Oracle which is returning the REF Cursor I am accessing it by using the Data access application block DAAB like the following:
OracleParameter param = new OracleParameter();
param.ParameterName = "Emp_Cursor";
param.OracleType = OracleType.Cursor;
param.Direction = ParameterDirection.Output;
param.Size = 3000;
Database db = DatabaseFactory.CreateDatabase("DBConnection");
//DbCommand cmd = db.GetSqlStringCommand("Select * from emp");
DbCommand cmd = db.GetStoredProcCommand("emppackage.select_employee");
db.AddOutParameter(cmd, param.ParameterName, param.DbType, param.Size);
DataSet ds = new DataSet();
ds = db.ExecuteDataSet(cmd);
GridView1.DataSource = ds;
GridView1.DataBind();
But iam getting this error:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_EMPLOYEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
How to call Ref Cursors in DAAB.
Thanks in advance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
