|
We're not going to do a Google search/buy a book so you don't have to do the work. Please read the forum guidelines.
|
|
|
|
|
Hi All,
I have asked a similar question before on another forum, and although the answer that I received pointed me in the right direction, it did not help solve my problem. What I would like to do is to schedule the deletion of a row of data after X number of days have passed. For instance I want to delete the information of users from my database a week after their first
login. I was told that I can use SQL 2005 scheduler. I checked out the link that I was given by the person who responded to my post, and was unsatisfied by what I've read. I did not see helpful examples that show how to set up a schedule to delete info after sometime has passed. Please explain how to use SQL 2005 scheduler or point me to a site that is helpful. Thanks for your response.
modified on Monday, May 5, 2008 9:31 AM
|
|
|
|
|
1. Create a stored proc (delete from User where created < dateadd(d,-7,getdate()) will do
2. Create an SSIS/DTS package that executes the stored proc (optional but a good learning curve)
3. Create a Job that runs periodically and executes the package (I think you can use the Job schedulter to execute the procedure without the SSIS)
If none of this makes sense have a rat around the Enterprise Manager for SQL Server Agent
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
" 3. Create a Job that runs periodically and executes the package "
Why would I have to create a job to run execute the stored proc if the scheduler can do that? Can I just create a stored proc without a job to execute it? Thank you so much for your reply
|
|
|
|
|
Scheduler = Job, in SQL Server the scheduler is the job manager, it's how you schedule work to be done.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Since we are the procedures companies use the original powerbuilder 7.0 development.
powerbuilder 7.0 DataWindow for the use of multi-table connection, generated SQL statement,
"*=" Equals "left join", "=*" equals "right join".
But when I put the database to upgrade to SQL 2005, and compatible with the database-level adjustment to 9.0, the application error, suggesting that SQL 2005 does not support "*=" only support "left join"
I can only adjust to the compatibility of 8.0 (which did not upgrade and What is the difference between)
I should be how to solve it ?
|
|
|
|
|
"Powerbuilder 7.0"-generated statements,
SELECT ctn_in_out.ctn_no,
ctn_in_out.vessel_code,
ctn_in_out.voyage,
FROM ctn_in_out,
goen_action
WHERE ( ctn_in_out.vessel_code =* goen_action.vessel_code)
SQL standard statements
select * from ctn_in_out right join goen_action on
ctn_in_out.vessel_code = goen_action.vessel_code
|
|
|
|
|
I need to pass to a trigger two dynamic text parameters stored into a
table (I can obtain them via a simple select). How can I obtain such a
thing?
|
|
|
|
|
Member 4008492 wrote: I need to pass to a trigger two dynamic text parameters stored into a
table (I can obtain them via a simple select). How can I obtain such a
thing?
Do the select inside your trigger. You're not limited to just selecting from the insert/delete tables.
|
|
|
|
|
hi 
i am working in vb program and ther is error in my sql
<br />
SELECT * From ClassRoom <br />
Where Capacity >= " & SumCapacity & " <br />
and RoomID not in(SELECT RoomID from [Slot] where SlotID= " & Slots4Sec.Tables("Slot").Rows(0).Item(0) & " or SlotID= " & Slots4Sec.Tables("Slot").Rows(1).Item(0) & " or SlotID =" & Slots4Sec.Tables("Slot").Rows(2).Item(0) & " or SlotID =" & Slots4Sec.Tables("Slot").Rows(3).Item(0) & ")" <br />
& "ORDER BY Capacity asc"<br />
<br />
<br />
can any body find the error please i am Sure about table names and type and iam using access db 
|
|
|
|
|
What error message do you get?
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
hi Blue_Boy
when i fill may data set it gives error (Data type mismatch in criteria expression)
|
|
|
|
|
Parameter values which you add include inside single quotes.
" SELECT * From ClassRoom <br />
Where Capacity >= '" & SumCapacity & "' <br />
and RoomID not in(SELECT RoomID from [Slot] where SlotID= '" & Slots4Sec.Tables("Slot").Rows(0).Item(0) & "' or SlotID= '" & Slots4Sec.Tables("Slot").Rows(1).Item(0) & "' or SlotID ='" & Slots4Sec.Tables("Slot").Rows(2).Item(0) & "' or SlotID ='" & Slots4Sec.Tables("Slot").Rows(3).Item(0) & "') " <br />
& " ORDER BY Capacity asc"
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
thanks 
i found the error it was that the type of roomid is different in the to table
thanks so much
|
|
|
|
|
You are welcome...
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
hi friends,
i have a report using matrix table something like..
Jan | Feb | Mar
-----------------------------------------------
sale | s_qty | sale | s_qty | sale | s_qty
-----------------------------------------------
Loc_A 10 | 20 | 10 | 15 | 20 | 50
11 | 16 | 30 | 15 | 20 | 20
10 | 20 | 05 | 10 | 50 | 10 **
Loc_B 15 | 30 | 40 | 45 | 10 | 60
14 | 16 | 30 | 15 | 20 | 20
19 | 20 | 05 | 10 | 50 | 10 **
Loc_C 10 | 20 | 10 | 15 | 20 | 50
11 | 16 | 30 | 15 | 20 | 20
10 | 20 | 05 | 10 | 50 | 10
Now whats my problem is i need the summary of each group.(showed in '**').
i used Matrix table , and now showing the grand Total also. but i need group summary.
Can any one help me..
Thanks in advance...
|
|
|
|
|
You need to group by Loc_A,Loc_B and Loc_C ?
Write result which you want to get.
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
i used stored procdure for tat & i grouped in the report. the current result i'm getting is what i showed. but i need the group wise total...
|
|
|
|
|
Ok, now you need to get sum of columns sale,s_qty grouped by Loc_A,Loc_b and Loc_c?
Are you usng Crystal Report to show data report?
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
No, i'm using SQL Reporting, & i used Matrix Table
|
|
|
|
|
Hello everybody,
I try to show you the output but when I look from the preview part, the design is get out of order. I want to ask you how can I make the "null" parts gone? There are now empty spaces instead of nulls. But I want namesurname one after another without no spaces.
The output of this code is ;
MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
B(+) NIGHT BIRCAN AR
B(+) NIGHT BIRCAN AR
B(+) NIGHT HASAN DEM HASAN DEM
B(+) NIGHT HAVVA SUK HAVVA SUK
B(+) NIGHT HAVVA SUK
B(+) NIGHT MEHDIYE GÖR
B(+) NIGHT KEREM GOK
Here is my second table's view as I want.
What should I do to get this output?
MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
B(+) NIGHT BIRCAN AR BIRCAN AR HAVVA SUK HAVVA SUK
B(+) NIGHT HASAN DEM HASAN DEM MEHDIYE GÖR
B(+) NIGHT HAVVA SUK KEREM GOK
This my code;
select ISNULL(MAX(MachineType),''),
ISNULL(MAX(DAYS),''),
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END),'') AS MONDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END),'') AS TUESDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END),'') AS WEDNESDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END),'') AS THURSDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END),'') AS FRIDAY,
ISNULL(MAX(CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END),'') AS SATURDAY
from
(SELECT SICK.MACHINETYPE,SICK.NAMESURNAME ,
CASE WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS,
CASE WHEN SICK.MACHINETYPE= 1 THEN 'Negative-'
WHEN SICK.MACHINETYPE= 2 THEN 'B(+)'
WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)'
WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType,
CASE WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY
FROM SEANCE,SICK
WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N'
and SUBSTRING(SICK.EUCLID,1,5)= '18016'
AND SEANCEDATE BETWEEN '20080301' AND '20080307'
AND SICK.EUCLID=SEANCE.EUCLID )AS T
GROUP BY MachineType,
DAYS,
CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END,
CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END
ORDER BY MachineType
|
|
|
|
|
Ever heard of CAST and TRIM?
If not then google it, and use it!
HTH.
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hi,
By default when i install SQL Express Edition i don't havethe connexion named NT AUTHORITY\NetworkService. My problem is than i use integrated security in my connection string to allow instance user.
Integrated security need NT AUTHORITY\NetworkService to allow connexion to database.
How to create this account?
Who have a idea?
Thanks
|
|
|
|
|
Which OS you are using?
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Windows Server 2003 Standard edition.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
