|
I have a table which requires records to be deleted programmatically at time intrvals.
I have an created field which stores the time it was created and I can't figure out how to delete records older than 3 hours.
ie:
DELETE FROM temp WHERE created < now()+(3600*3)
I'm using mysql BTW, but I don't think SQL or SQL would have much discrepancies here would they?
Thanks for you time 
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
DELETE FROM temp WHERE created <= DATEADD( h, CURDATE(), -3);
This will work for SQL Server, but I'm not sure about MySQL. Give it a shot
|
|
|
|
|
this solution can work for SQL Server:
DELETE FROM temp WHERE DATEDIFF(Hour,created,getdate()) > 3
|
|
|
|
|
What is an AutoNumber field's maximum limit? What happens when the maximum limit is reached?
|
|
|
|
|
Its the limit of a long interger, roughly 2 billion. I would be interested to know what happens when it hits the max too
|
|
|
|
|
You get an error :
Server: Msg 8115, Level 16, State 1, Line 1<br />
Arithmetic overflow error converting IDENTITY to data type int.<br />
Arithmetic overflow occurred.
|
|
|
|
|
 Hi
I am trying to develop my web application
ASP.NET to query my SQL Database
but when trying to running it
i got this error message (after the application has been
succesfully compiled and running on the IIS:
Login failed for user 'PRESARIO1692\ASPNET'.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'PRESARIO1692\ASPNET'.
Line 31: sqlDataAdapter1.Fill(dataSet11);
I think i need to grant an access for user ASPNET
but i don't know how.
I am running a Microsoft SQL desktop engine
deployed with the Visual C#.net standard edition.
Thanks
marnicola90@hotmail.com
 
|
|
|
|
|
marnicola wrote:
I think i need to grant an access for user ASPNET
but i don't know how.
Yes,this is your problrm. Open enterprise manager and open Security folder in it.Write click on "Login",and then choose New Login.While windows authentication is chosen,select ASPNEt account with browse button.In the bottom of the dialog ,chosee the name of database you want to use.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Well I don't know where is it this enterprise manager but i can't find it. Where is it ? Is it of the SQL ? (I have deskttop engine version). Is it of Windows2k ? I am running on Win2k ITA . Tell me the name of the exe.
Thanks.
|
|
|
|
|
Oh,you mean you have MSDE,so you don't have it.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Yes i think i don't have the enterprise manager.
In a newsgroup i received this reply
C:\>osql -E
1> use master
2> exec sp_grantlogin 'PRESARIO1692\ASPNET'
3> go
Granted login access to 'PRESARIO1692\ASPNET'.
1> use pubs
2> exec sp_grantdbaccess 'PRESARIO1692\ASPNET'
3> go
Granted database access to 'PRESARIO1692\ASPNET'.
1> exec sp_addrolemember 'db_datareader','PRESARIO1692\ASPNET'
2> go
'PRESARIO1692\ASPNET' added to role 'db_datareader'.
1> quit
i think it's correct. What you think ?
It's may case. DOS command right because of the MSDE.?
|
|
|
|
|
Yes,this commands equal to what I told you.I've never run SQL commands from command line,Let me know if it works for you.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Well now i have try to run a scrip i have fount on the CD
that before i haven't done because i didn't know how to run scripts this is the result :
C:\Programmi\Microsoft SQL Server>osql -E sa -i installcontacts.sql
1> 2> 3> 4> Msg 3702, Level 16, State 4, Server PRESARIO1692, Line 3
Cannot drop the database 'Contacts' because it is currently in use.
1> 2> 3> 4> Msg 1801, Level 16, State 3, Server PRESARIO1692, Line 2
Database 'Contacts' already exists.
1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3>
1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 1> 2> 3
> 1> 2> 3> 1> 2> 3> 4> 5> 1> 2> 3> 4> 5> 1> 2> 3> 4> 5> 1> 2> 3> 4> 5> 1> 2> 3>
4> 5> 'ASPNET' added to role 'db_accessadmin'.
1> 2> 3> 4> 'ASPNET' added to role 'db_backupoperator'.
1> 2> 3> 4> 'ASPNET' added to role 'db_datareader'.
1> 2> 3> 4> 'ASPNET' added to role 'db_datawriter'.
1> 2> 3> 4> 'ASPNET' added to role 'db_ddladmin'.
1> 2> 3> 4> 'ASPNET' added to role 'db_denydatareader'.
1> 2> 3> 4> 'ASPNET' added to role 'db_denydatawriter'.
1> 2> 3> 4> 'ASPNET' added to role 'db_owner'.
1> 2> 3> 4> 'ASPNET' added to role 'db_securityadmin'.
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 1> 2> 3> 4> 5> 6> 7> 1> 2> 3> 4> 5> 6> 7> 8>
9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 2
9> 1> 2> 3> 4> 5> 6> 7> 1> 2> 3> 4> 5> 6> 7> 1> 2> 3> 4> 5> 6> 7> 1> 2> 3> 1> 2>
3> 1> 2> 3> 1> 2> 3> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
18> 19> 20> 21> 22> 23> 24> 25> Msg 170, Level 15, State 1, Server PRESARIO1692
, Line 15
Line 15: Incorrect syntax near 'Contact Types'.
It seems the Contacts db(my db) was already added to the master.mdf infact master in the beginning was 1 M now it's about 12M. I think Visual Studio did.
Plus the script added ASPNET user as reader writer and so on.
But still i have the same error , after the compilation was okay . I get the error when the browser open and contact both servers.
I don't know
|
|
|
|
|
The funny thing is that i try to refresh the connection after i got the login message Visual Studio Server Explore return this message :
QueryInterface for interface IOleServiceProvider failed.
and then the connection on the treeview expire
when before the compilation the connection test was
succesfull.
Plus i can see everything from the Database from visualstudio ,Tables records . Everything.
Do you think my problem is that i am running on Win2K SP2 Italian and VisualStudio is English so MSSQL (MSDE) ?????
|
|
|
|
|
Plus after the connection is expired from Server Explorer , visual studio show me an empty database, but with all tables and fields, but not data.
|
|
|
|
|
This the part of the script that grants the access to the database.
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
if not exists (select * from dbo.sysusers where name = N'ASPNET' and uid < 16382)
EXEC sp_grantdbaccess N'ASPNET', N'ASPNET'
GO
/****** Object: User dbo Script Date: 3/7/2002 1:52:45 PM ******/
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_accessadmin', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_backupoperator', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_datareader', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_datawriter', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_ddladmin', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_denydatareader', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_denydatawriter', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_owner', N'ASPNET'
GO
/****** Object: User ASPNET Script Date: 3/7/2002 1:52:45 PM ******/
exec sp_addrolemember N'db_securityadmin', N'ASPNET'
GO
|
|
|
|
|
I just finished right now to try the Runtime connecting way:
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection sqlconnectContactMgmt = new SqlConnection("server=(local);database=Contacts;Trusted_Connection=yes");
SqlDataAdapter sqladaptContactMgmt = new SqlDataAdapter("select * FROM Contacts",sqlconnectContactMgmt);
DataSet dsContacts = new DataSet();
// until now all the breakpoints are executed okay
// this generate the server error :
//Server Error in '/Ch05' Application.
//----------------------------------------------------------
//Login failed for user 'PRESARIO1692\ASPNET'.
//Description: An unhandled exception occurred during the
// execution of the current web request. Please review the
//stack trace for more information about the error and
//where it originated in the code.
//Exception Details: System.Data.SqlClient.SqlException:
//Login failed for user 'PRESARIO1692\ASPNET'.
sqladaptContactMgmt.Fill(dsContacts,"Contacts");
DataGrid1.DataBind(); // never reach this point
}
|
|
|
|
|
|
You mean This ???
initial catalog=Contacts;integrated security=SSPI;persist security info=False;user id=ASPNET;workstation id=PRESARIO1692;packet size=4096
Connection String property of the sqlConnection of my web application.
Should i use another user id ?
which one ?
I tryed but i got the exactly the same error from the same
user id even i changed it !!!!
 
|
|
|
|
|
MSDE be default uses Integrated Security, thus it will use windows security. No need to give user/pass.
Here is mine for a default MSDE (the default one included with VS.NET i should rather say):
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=LOCALHOST\VSdotNET;Packet Size=4096;Workstation ID=LOCALHOST;
DAMN emoticons !!!!!
|
|
|
|
|
I have little experience with arrays and would like some help. I am querying an LDAP directory via a ActiveX component called AdvLDAP from Aspfusion.net. The code is below. What I want to do is be able to perform IF THEN statements on the variables in the array and insert some into a table. You can see from the LDAP.Attributes line what attributes I am returning. For instance, I want to return the attribute and value of "User-Suspend" so that I can perform something like "IF User-Suspend = "Y" THEN"...any help is appreciated.
===========================================
dim LDAP
set LDAP = Server.CreateObject("AdvLDAP.LDAP")
LDAP.ServerName = "10.1.58.25"
LDAp.Port = "389"
LDAP.UserName = "tssacid=dsldap"
LDAP.Password = "*******"
LDAP.DN "c","us"
LDAP.DN "o","upb"
LDAP.DN "host","CAICCI"
LDAP.DN "tssadmingrp","Acids"
LDAP.DN "tssacid",""&userID&""
LDAP.Attributes = "Name;user-type;user-suspend"
LDAP.Filter = "objectclass=*"
LDAP.Scope = "Base"
Set Result = LDAP.Query()
if (LDAP.IsError = 1) then
Response.Write LDAP.ErrorReason & "<br>"
else
%>
<TABLE ALIGN=center BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR bgcolor="black">
<%
Dim cols,rows,Arr()
cols = 0
rows = 0
for each Member in Result%>
<TD bgcolor="black" width="100%">
<font color="white"><%=Member.Attributes%>
</font></b></TD>
<%cols = cols+1
if (cols = 1) then
set Links = Member.ValuesList()
for each item in Links
rows = rows + 1
next
set Links = Nothing
end if
next
%>
</TR>
<%
ReDim Arr(cols, rows)
c1 = 0
for each Member in Result
r1 = 0
set Lnks = Member.ValuesList()
for each Item in Lnks
val = ""
itm = Item.Value
for i = 0 to ubound (itm)
val = val & itm(i)
if (i <> ubound (itm))then
val = val & ", "
end if
next
Arr(c1,r1) = val
r1 = r1 + 1
next
set Lnks = Nothing
c1 = c1+1
next
for rw = 0 to rows-1%>
<TR>
<%for cl = 0 to cols-1%>
<TD><%
if (Arr(cl,rw) <> "")then
Response.Write Arr(cl,rw)
else
Response.Write "-"
end if%></TD>
<%next%>
</TR>
<%next%>
</TABLE>
<%
End If
%>
|
|
|
|
|
Hi I have Visual Studio.NET Beta 2 on my computer.
I dowloaded Project from Code Project that might be very usfull for me as a beginner such as:
http://www.codeproject.com/useritems/spgen.asp
Trying to open the sources I get "invalid RESX input' error double clicking the frmMain.cs and "SQLDMO could not be found" message (I have registarted SQLDMO.dll).
What should I do?
Please help
Thanks
|
|
|
|
|
|
I wanted my application to start soon after the user logs onto the PC and therefore created a key iunder
HKM\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
the application does start up thats ok
the problem is now soon as
-the user logs in the aplication starts up,so far so good
-there is a ado connection that is done in the app class ...
[this works fine during normal execution]
-however when this is done soon after OS startup a run time error is thrown...
is it because ADO drivers are not initialised yet or something like that
how do i overcome this.
any advice would be a blessing
fordge
|
|
|
|
|
I want to try to help you, but I have a couple of questions that would help me to provide an answer:
1. What programming language is your app written in?
2. Does your app run fine when you start it post starup--that is, once you have logged in and all services are started and then you go and start it manually, does it work OK?
3. If the answer to question 2 is no, then have you steped through it to the point of failure with the debugger? And if so, what does it say?
I've never run an application at login that uses ADO, but I do believe that all applications listed under "Run" are run after all system services have started. I could be wrong. Let me know your answers and we'll go from there.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
