I have a database that has a table with tons of data in it and it is getting close to the 999 days before it starts deleting the oldest records. Is there a way to allow the table to keep all of the information? I don't think there is, so here is my next question. Is there a way that I can move the entire table into another database to save all the information and start over with the 999 days? My main goal here is to not lose ANY information from any table from the database.
Also, I was thinking that when an entry is added into the main table, if it can also be added into the other table where I move the old data? I think a trigger can be used here, but I'm not sure how to do one in 2005. Thank you very much in advance for your help!!!
Hello! I'm new DBA. I have triggers on my table in the database have been created one individual a long time ago. Sp_helptrigger stored procedure gave me just a little info about triggers. I would like to see the real code for each trigger, but I don't have idea where to look for it. Please help me to find it.
I am trying to change values of two tables in my sql server 2000 database. When one of the tables is modified in some way like adding/updating a record, I need a trigger procedure to copy this new data from the first table to the second. Problem is, how do I get the newly inserted or updated data from the first table? How do I specify that I only want the data which caused the trigger to execute? Anyone know?
We recently had a problem were a webserver lost connection to sql 2000 database after we put in a new router. When we failed back to old router it was able to connect to database again. It is using a non standard port 2125 to connect to sql database. Why would a new router cause connection problems to a database.
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF', MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF', REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
The situation is I need info from a certain SharePoint site that is in a sql database. The problem is The SharePoint site currently contains data from 9 different projects, all using the same database. The database is a complex setup and it would not be an easy operation to extract the special information alone. The database is run with Microsoft SQL and is comprised of 4 separate databases. 3 databases organize the data (500+mb of data total) and 1 stores the data for all the sites (12.5+GB and increasing at a rate of 100-300mb per day).
How can I get the special data I need from this setup???
I'm using SQL Server 2005 as my backend application. I had strange experience on it, sometime some of my records suddenly disappear without any deletion action.I've check the log files and I've already set a trigger on deletion to copy the deleted record to a history table, but I found nothing. Are there anybody that have same experience like me ?
We are running SQL 7 with a front end that links to the tables through ODBC.In our main table, the user has no way to delete a record through theinterface, though it is possible to delete it by opening the ODBC link.Users would have no reason to delete a record, but one of our records turnedup missing.Now, it's possible that a user may have accidentally deleted the record.But, since users don't have any reason to delete records, and since theydon't access the ODBC links, it seems unlikely (though possible).I was wondering if anyone had every heard of SQL Server ever "losing" arecord that had previously been saved. I checked the nightly backup from thenight after it was added, and the record was there. So either a user deletedit, or somehow it got lost in SQL Server. I have no code that deletesrecords in this table in any way, shape or form, so it couldn't have beenmalfunctioning code.So, while I have a hard time believing that SQL Server would just "lose" arecord, I also know that anything's possible, so I thought I'd ask if anyonehad ever heard of such a thing.Thanks!Neil
Hi all, I have some problem : I use sql server 2000,I have about 20000 records ! I delete all data unfortunately ! So , i need to restore all . Are there way to do this ? I don't setup automatic backup ! My data is very important !
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V
Database Mail only sends SMTP email messages and logs problemsconnecting to the mail server. However, I also need to log bouncedemails (bad address, etc).Are there any clean ways to do this with Database Mail and Exchange?Or will I have to mix Database Mail with SQL Mail to read bounces outof the inbox? Or, have a .NET windows service that talks to theExchange API?
i've a store procedure has a strange behavior, As soon as created has a good performance , but after some times (indeterminated) it takes more time to be execute.... (up to 70s!!!)
The thing that i've not understood was if i take the query inside to the store i execute it separtely I get result immediately... :eek:
Dropping and re-creating procedure,it become newly fast... I've just scheduled a maintenance plan with index optimization and integrity check, but this seems doesn't work ...
I've got a project with 12 packages. One of them runnning and at the same time when I try to alter another one or even add a new task I see that all the tasks on toolbox are disabled.
Why?
I would like to hear a good reason for this behaviour, afaik I don't get the point...
Creating custom Register form That inserts user info in to database via storeprocgetting following errorCompilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30456: 'ValidateInput' is not a member of 'System.Web.UI.WebControls.TextBox'.
Source Error:
Line 3496: Me.__BuildControlTree(Me) Line 3497: Me.AddWrappedFileDependencies(Global.ASP.partfrm_aspx.__fileDependencies) Line 3498: Me.Request.ValidateInput Line 3499: End Sub Line 3500:
<tr> <td align="Left" colspan="2"> <h3> PARTICIPANT INFORMATION: </h3><hr /> Please enter information for the attendee and click 'continue'.</td>
<p> Your account has been successfully created. </p> <asp:Button ID="ContinueButton" runat="server" CommandName="Continue" Text="Continue" />
</asp:WizardStep> </WizardSteps> </asp:Wizard> > CODE-BEHIND 'This event handler fires when the user clicks Finish. We need to insert the new participant record into the database Protected Sub AddParticipant_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles AddParticipantWizard.FinishButtonClick 'Insert the Participant into the database InsertPartDataSource.Insert()
'Send the user back to the homepage Response.Redirect("~/PartReg.aspx") End Sub
I use detach/attach method to move database to a new SQL Server 7 on MSCS. The security information was lost in the new server. Database Access permission was gone and I failed to select it again because message showed the database role is already exist. The default databases were also posted incorrectly. Would you please to tell me how to fix this type of peoblem or let me know how to move the login info such as login id, password, database permisson to the new server which locates in a different machine.
Hi, all, I found that the SQL2000 EM does not show database space allcoated information, as well as tables and indexes size while SQL 7.0 does. Someitmes these information are fairly handy. is there any other easy ways to find out the same info from SQL2000 through Em, or elsewhere ?? Thanks Anthony
I have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator.
/* Original by: Bill Graziano (SQLTeam.com) Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40) */
declare @low int
select @low = low from master.dbo.spt_values where number = 1 and type = 'E'
select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved, (d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp, ((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unused from sysobjects o
inner join (select distinct id, rowcnt from sysindexes where keys is not null and first != 0) ro on o.id = ro.id
inner join (select id, sum(reserved) reserved from sysindexes where indid in (0, 1, 255) group by id) r on o.id = r.id
inner join (select c.id, dpages+isnull(used, 0) data from (select id, sum(dpages) dpages from sysindexes where indid < 2 group by id) c full outer join (select id, isnull(sum(used), 0) used from sysindexes where indid = 255 group by id) t on c.id = t.id) d on r.id = d.id
inner join (select id, sum(used) used from sysindexes where indid in (0, 1, 255) group by id) i on d.id = i.id
Please help, i am really really struggling for a logic to handle 100's of reports we have via button click from asp.net webform. in the button click i am constructing the url : ************************************************************************************************************** http://localhost/reportserver?/MyReports/StatusReport&UserID=1&ContractID=1&subcode=null *************************************************************************************************************
I have a table would like to maintain the parameters required for the chosen report: when the user chooses from list box on the webform for StatusReport, immedeately it fetches the parameters related to Statusreport and gets everything which is stored with a space in between for each parameter, for this report i have 3 parameters: UserID ContractID subcode
now how can i construct the string based on the above parameters , i am using vb.net as code behind for my webform(asp.net)
please any ideas will help me achieve the logic. please help thank you all very much.
I used the URL approach to call the reporting service. In the ASP page, I use javascript to open a new window to view the report. But when I close the new window and work on the original window. It seems that the DB connection to the SQL server is not working.
We run std 2008 r2. On my col heading text boxes and data text boxes i'm purposely picking only borders above and left in an attempt to get rid of what looks like a double border between text boxes.
The only places I see what appears to be a single thickness is the left of the 1st column hdg and data, and above any col hdg text box I set this way.
Also, when I pick .5 instead of 1 pt thickness, ssrs always puts it back to 1 pt.
How can I get the appearance of single thickness borders instead double?
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
I have several reports that are set to landscape but then lose this layout when a linked report is created from the original report. I found the link below describing Brian Welcker's workaround and my question is where do you place the vb.net code he provided?
This is sort of a DR issue since it involves reboots and power failures.
We have MSSQL 2005 SP1 and unpatched systems, but in particular we have this problem still with the SP1 server (in addition to the unpatched systems). Running on Win 2k3.
It seems that upon reboot, our stored SSIS jobs lose their pemissions and/or ownership for execution. It doesn't happen at every reboot or power failure, but seemingly more than half the time.
Here is the error reported: The job failed. The owner () of job Job_1 does not have server access.
But checking the properties of the job and it shows an valid owner in the Owner field. I believe we are using Active Directory for authentication and the user listed is valid in that respect.
We have "fixed" this problem before by removing the job completely and re-installing it, with the same owner. No issues until the next system outage.
Has anyone else seen this before? Is there a fix or is this some kind of bug?
I wanted to upgrade pre-release version of SQL Express to the latest version, and wanted to make sure that none of the database information would be lost. The thing that concerns me is that it states to uninstall previous versions of SQL Server 2005 Express before installing the latest version. I threw the database tables into a backup directory, and I think this should be fine, but I want to be sure. Let me know. Thanks!
