|
Try:
CREATE PROCEDURE dbo.sp_GetDowntime
@date SMALLDATETIME,
@dept NVARCHAR(3),
@equip NVARCHAR(25),
@shift NCHAR(1)
AS BEGIN
SET NOCOUNT ON
SELECT h.oee_date, h.dept, h.equip, h.oee_shift, d.dt_reason,
r.name, d.min
FROM userdata_header h
INNER JOIN userdata_downtime d
ON h.dept = d.dept
AND h.oee_date = d.oee_date
AND h.equip = d.equip
AND h.oee_shift = d.oee_shift
INNER JOIN refer_dt r
ON d.dt_reason = r.dt_reasonID
WHERE h.oee_date = @date
AND h.dept = @dept
AND h.equip = @equip
AND h.shift = @shift
RETURN (0)
END I found the following problems:- The parameters needed commas between them.
- There was a comma in the where-clause.
- The "equip" link between the two tables specified the table-names instead of the table aliases.
- I added "Set Nocount On" because many front-end programming languages by SQL-Server results otherwise.
Regards
Andy
|
|
|
|
|
Thanks, Andy. That was very helpful. Your version works perfectly, of course. I can't believe I missed the commas...where they were needed and where they weren't. I guess the error message was a misnomer. Thanks again.
|
|
|
|
|
Once I have a GB created I can create new controls as children of it, but I can't find a way to drag existing ones inside. I can place them overtop of it, but they're not bound parent-child and stay in place when the GB itself is moved on the form.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
If you have groupbox1 then I guess this piece of code will add new controls as parent-child:
private TextBox textBox1 = new TextBox();<br />
groupbox1.Controls.Add(textbox1);
By the way, you have posted in an incorrect forum. This forum for database stuff
|
|
|
|
|
I'm not asking about a form in C#, c++, etc. The access DB has a built in system to make (ugly) forms. IT uses a vb dialect for events but doesn't expose any of what would be constructor type logic in a real language to the developer.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
The same was true in the VB6 designer. You can cut the control you're trying to move into the group box, then select the group box, and select Paste, IIRC.
|
|
|
|
|
hi,
in the sql we have user_tab_coloumns to know the datatype of a field in a table by properly querying but my problem is i am using an oledbconnection in C# to connect to an access database and i want to know the datatype of a field of a table in that perticular database how to do that?
pls help me.
thanx in advance.
|
|
|
|
|
How about the DbConnection.GetSchema method?
|
|
|
|
|
how that helps?
|
|
|
|
|
It allows you to use c# to get the details of a database table, and its columns. Isn't that what you asked for?
|
|
|
|
|
Check out OleDbDataReader.GetDataTypeName(index) or OleDbDataReader.GetFieldType(index)
Never tried it myself but should work
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
hi,
i have 2 fields with varchar dates as follows
SDATE OUT_DATE
20070604 20070528
20070604 20070529
20070605 20070601
i have converted both in to date like following,
//SDATE
CONVERT(NVARCHAR,(substring(TFRTTV.SDATE,1,4)+'/'+substring(TFRTTV.SDATE,5,2)+'/'+substring(TFRTTV.SDATE,7,2)),111)
//OUT_DATE
CONVERT(NVARCHAR,(substring(TFRTTV.OUT_DATE,1,4)+'/'+substring(TFRTTV.OUT_DATE,5,2)+'/'+substring(TFRTTV.OUT_DATE,7,2)),111)
now i wanna filter records to show only
select SDATES ,OUT_DATE where OUT_DATE>SDATE-5
please tell me how to do this.
Regards
Ruwandi
rkherath
|
|
|
|
|
hai try this query
select sdates,out_date from tablename where date(out_date)>adddate( date(sdates),-5);
ok bye
|
|
|
|
|
hi ganesamoorthidhayalan,
i tried it it says
Server: Msg 195, Level 15, State 10, Line 3
'date' is not a recognized function name.
regards
Ruwandi
rkherath
|
|
|
|
|
hai friend,
tell me the either your are working in sql or mysql.
In mysql date is one of the predefine funtion.
You find out the date function,
warms and regards
ganesamoorthi dhayalan
|
|
|
|
|
try this query,
select sdates,outdate from tablename where out_date > dateadd(day,-5,sdate);
by
ganesamoorthi dhayalan
|
|
|
|
|
SELECT sdates, out_date FROM tableA
WHERE Convert(datetime, out_date) > DateAdd(dd, -5, Convert(datetime, sdate))
here in hte where clause converting both dates into datetime datatype and to sdate adding (-5) days i.e. subtracting 5 days from sdate. then checking whether out_date is greater.
Regards
KP
|
|
|
|
|
Hi..
Can u tel me abt calling batch files(.bat) from PL/SQL program?
PLz Help me..
Thanx
Gurudatta B. Shelke
|
|
|
|
|
use xp_cmdshell sp. This is extended sp and basically these are dll's. Let me know in case you need any more help!! Amit
|
|
|
|
|
Can anybody guide me for this solution:
Remote (USER-1)
.................\
..................|---> SQL SERVER
................./
Remote (USER-2 )
Problem is : User-1 and User-2 want to update a Same Table at same time.
------------------------------------------------------------------------
In this Case User-1 : is being Transation ... is Done.
In this case SQL SERVER is Locked
In this Case User-2 : Getting Error
------------------------------------------------------------------------
In Case i want to check the SQL SERVER is Locked
IF(SQL SERVER is Locked = True)
{
User-2 : Get Suit Message like "Server is Busy try after some time."
}
Give me the Concept how to do this thing using C# Programming in Pocket PC.
Pavan Pareta
|
|
|
|
|
You need to read up on transactions. Don't think in terms of locking, think in terms of what operations are permitted to occur concurrently.
The easiest, but least performant, method is to use the Serializable transaction isolation level. This prevents all updates to data you've read or updated since the beginning of a transaction until you commit the transaction. SQL Server simply blocks any client trying to make a modification, or read one of your modifications, until your transaction is committed (if using locking, rather than row versioning). There are lower isolation levels that you should read up on carefully before using to ensure that the effect is the desired one. The default level is ReadCommitted, which means that you cannot read uncommitted updates which another transaction has made (and if SQL Server is using locking rather than row versioning, blocks your connection until the changes are either committed or rolled back).
You should explicitly wrap your operations that need to have a consistent state in a BEGIN TRANSACTION/COMMIT TRANSACTION pair of statements, or use a SqlTransaction object. If an error is returned from a data-modification operation, you should roll back the transaction and abandon any further statements. At any point if you decide you need to discard your operations, you can roll back (note that everything you did in the transaction is rolled back).
Be aware that a second connection opened by your application is treated as a separate client and you can end up blocking yourself. This does not apply to SQL Server 2005 with the Multiple Active Result Sets (MARS) feature enabled.
Also be aware that if you perform operations in a different order in one transaction compared to another, you can end up with a deadlock. In this case, SQL Server will decide to kill off one of the transactions so that another complete. The transaction that is killed will be rolled back.
Sometimes you need to lock a record on a logical level for updates and still permit other users or clients to read it. Here you will have to roll your own - SQL Server does not provide support for this.
It's important for all database developers to understand transactions and isolation if the database is going to be used concurrently, as the vast majority of server-type databases will be.
|
|
|
|
|
Hi all,
I want to creat a report that displays the accNo and its subsidiary accounts how can i write an SQL Sever query to get both of the main account and its sub account in one query?
Thanx in advance
|
|
|
|
|
can please explain explain your table strecture, sample data and required output out of it
Regards
KP
|
|
|
|
|
Hi
This is a sample structure close to the structure i have
ID Name Parent
1 ddd NULL
2 sss 1
3 aaa 1
4 fff 3
i want to be able to select (1) and its children (2,3)
output like this
1 ddd
2 sss
3 aaa
3 aaa
4 fff
Thanx
|
|
|
|
|
create table a(id int, nam varchar(5), parent int)
insert into a values(1, 'ddd', null)
insert into a values(2, 'sss', 1)
insert into a values(3, 'aaa', 1)
insert into a values(4, 'fff', 3)
Query for fetching data ...
select id, nam
from (
select distinct x.* from a x inner join a y on x.id = y.parent
union all
select distinct y.* from a y inner join a x on x.id = y.parent
)b
order by id
and the output is ...
id nam
---- ------
1 ddd
2 sss
3 aaa
3 aaa
4 fff
Regards
KP
|
|
|
|