I am in problem that I have delete data in my production table.
Now how to retrieve it?
I have only Yesterdays Full and Transaction Backup files.
I used the following query for deleting all data.
delete from t1;
Hello,
Thank you for reading this. I am faced with a problem where there is no storage of deleted or updated information in an OLTP DB. I am mostly concerned about retrieving the deleted data from a table when an update occurs so as to make the deleted data show as an error. Is there a way to retrieve these kind of data from a DB, if so how? Can these data be retrieved by dates? Also how much time do you have before the data can no longer be retrieved? Assuming that the data can be retrieved.
I know the good stuff about datawarehousing but unfortunately we have no DW. Any help will be greatly appreciated.
I moved my projects off of the C drive and onto a larger drive a few months back. I discovered recently, like a lot of us have unfortunatly in the past, that we are not pulling a backup of the drive I put the projects on. While going back through some old stuff on the C drive, I deleted a project inside the old Projects folder. This was a mistake because it was a project that is in use. The report still runs fine, but when I went to edit one of the reports in the project, much to my dismay, the project could not be found. I have a txt file of every SQL script that I wrote to create these reports, but am wondering if there is any way to salvage the reports from somewhere on the system so I don;t have to rebuild them?
View 7 Replies View RelatedHow can I keep a production SQL server and DR SQL server in sync? I'm not referring to data - I can use SAN replication for that. I'm referring to the many configuration settings and objects that are external to the databases..Server settings (xp_cmdshell, etc.)LoginsServer- and database-level rolesServer- and database-level permissionsObject level permissionsMaintenance plans/SSIS packagesSQL Agent jobsJob schedulesDatabase mail configurationObjects (proxies, alerts, credentials, operators)Linked serversScheduled tasksODBC connections
I realize that many of these items can be scripted out, and periodically run on the DR server; but not all of them can be. And some, although they can be scripted, may be problematic to restore on a DR server, such as SQL Agent jobs. And even with backup scripts, it seems like developing a process to refresh a DR server would be overwhelming given the number of elements and exceptions. I'm not adverse to doing the work to create a completely scripted solution, but was curious if one already exists that handles all or most of the elements in my list.
I have nine type of buttons,
EnrollAmtBTM
PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button
this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION
THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation()
{
WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass();
string SQLCONDITION = "";string RUN_SQLCONDITION = "";
SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null)
{ValueSelected = new SessionValues();
Session.Add(Session_UserSPersonalData, ValueSelected);
}
else
{
ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);
}ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false;
Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode();
SelectedNode = AgencyTree.SelectedNode;
SelectedAgency = SelectedNode.Value.ToString();
Agencytxt.Text = SelectedAgency;
Agencytxt2.Text = SelectedAgency;
Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null;
try
{CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'");
}
catch
{
}DataTable TableSet = new DataTable();
TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows)
{if (IndexingValues == 0)
{SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
else
{SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
IndexingValues += 1;
}SQLCONDITION = SQLCONDITION + ")";
ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0)
{
Dataviewlisting.ActiveViewIndex += 1;
}
else
{
Dataviewlisting.ActiveViewIndex = 0;
}
SelectedAgency = SQLCONDITION;
ValueSelected.CONDITION = SelectedAgency;
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;
try
{
CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);
EnrollAmtBTM.Text = CollectionCount.ToString();
}
catch
{
}////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.
"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"
here is the functionpublic int CollectedFinaceDataCount(String SQLStatement)
{int DataCollection;
DataCollection = 0;
try
{
SQLCommandExe = FinanceConnection.CreateCommand();
SQLCommandExe.CommandType = CommandType.Text;
SQLCommandExe.CommandText = SQLStatement;
ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar();
DisconnectToDatabase();
}catch (Exception ex)
{Console.WriteLine("Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString());
}
return DataCollection;
}
So here mu requirement request is to display only the value fronm the view i have against the agency selected
Please help ASAP
Thanks
Santosh
I've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. I could simply set up a jog to delete the older files, but that will only mask the issue.
View 3 Replies View Relatedthis is a simple problem but it's just driving me mad as it's not reading from my dB. basically, I've have reviews stored in my dB and want to display them in a textbox by clicking on a button called btnReviews. I think the problem might be that there is too much text stored per row of the table (as it is a review), but I have the datatype set as text in sql. here's the simple un-errorred code I have behind the button. any ideas where I went wrong. i've a feeling it's something small but it's just taken too long to figure out.protected void btnReviews_Click(object sender, EventArgs e) { String strConn = ConfigurationManager.ConnectionStrings["conLocalDatabase"].ConnectionString; SqlConnection dbConnection = new SqlConnection(strConn); SqlCommand dbCommand = new SqlCommand("Select [ReviewC] From [Review])", dbConnection); dbCommand.Parameters.AddWithValue("Review", txtReviewView.Text); try { dbConnection.Open(); dbCommand.ExecuteNonQuery(); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { if (dbConnection != null) { dbConnection.Close(); } } }
View 6 Replies View RelatedHi
I ve a datagrid . And Two Database table in sqlServer2005. The name of the tables are 'Property' and 'userid'.
My datagrid wants to retrive all records from Property table and one record from userid table. The Property table contains Propertycode, lastdate , departmentname.
The userid table contains so many record along with 'id' record which my datagrid wants to retrieve.
pl tel me how 2 write code for that?
Can the deleted records be recovered in Simple Recovery model database? Are the delete logs present there?
View 3 Replies View RelatedI have deleted some data from some table. I dont have any backups and the database in simple recovery Mode. How to recover that data ...
View 15 Replies View RelatedHi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC
Below is my SQL statement;
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
I am trying to insert data from staging table to production table. In the staging table I only have period or date but no primary key.
This is my staging table
Create stagingtable(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
Create Production(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
I get this every month. What can I do to make sure only unique record are loaded into production table with no duplicate from previous month.
Hi,
I need to prevent modification/update to a field.
So I created a trigger. To take the data from DELETED table then replace the field data.
However, I have problem with one field which data type is text.
MS SQL always return me this error:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
I use the following code to take the data from deleted table
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(NOTES )
FROM DELETED
I also tried simple Select statement
Select notes from deleted
What code should I use to take the deleted data
Please help.
Thanks in advance
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains
View 4 Replies View RelatedI am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedI have a table that has a primary key that is auto incremented by 1. This table's data is cleared out periodically and as data gets added the auto id primary key continues to increase in numeric value. Once the data is cleared from the table the auto id names could be used again(the eventId is not stored) Currently the eventID is at 26,581,399. I know the maximum int value is 2,147,483,647.
How should I handle this? or rebuild the table every time the data is cleared(problematically)?
I have 'codes' and 'Description' field in 'ecode' table and 'codes' in quote table values for 'Codes' in quote table is like
Quoteid Codes
0012 LB,WS
0031WDC
In 'ecode' table 'description' column contain the description for each code
for eg :
Ecode Table : -
Codes Description
Lb - Late Booking
WS - Winter Sports
WDC - Wedding Cover
How to select 'description' when retrieving data from quote table
Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..
Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?
Andy
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedHi,
I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!
Thanks in Advance
Hi I have a production table in SQL Server 2005 that has approx 500,000 records---every 6 hours this table needs to be truncated and filled
The basic SSIS package uses a script compentant and imports the data into a staging table which has the same structure as the production table. I have a final Execute SQL Task that Truncates the production table and does a Insert into production-select * from stage table.
Takes around 30 seconds to run this last Execute SQL Task--problem is there is a risk that our webservice will query this table during the Execute SQL Task and return incorrect results.
Q1: In this last Execute SQL Task if I used a BEGIN TRANSACTION and COMMIT TRANSACTION; will this be any quicker ?
Q2: In this last Execute SQL Task- would it be better to use a RENAME TABLE technique in TSQL--any code examples ??
Q3:Is there any way in TSQL I can compare EVERY FIELD in two tables ie Stage and Production which have identical data structures and figure out a way to update only the records that changed? Is SQL Server Replication the best way to do this
thanks kindly
Dave
Is there anyway to retreive deleted data (rows) of a table? Unfortunately the backups are corrupt so we can't do a restore.
Thanks
Hi,
I am currently working on a MS SQL server 2000.
I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted --- are not accessible. anyone knows why? And is there any other way to do this?
Thanks
hi there,
by mistake i deleted tables in the DB, i heard once that there is a way to retireve it after it has deleted, from the temp or something like that
so is there a way?
Thanks
Hello All.
Is there a way to check who has deleted records from my SQL tables? I asked because I notice records keep disappearing from my tables recently for no reason. The DB is only accessible by a few IT staff. Business users have no direct access to it so they can't do any harm and there is no application that update these record missing tables.
I don't need to know the exact records that have been removed. I need info on who has made a deletion activity on which table, date and time is good enough.
Thank you.
Best regards
hi
for after trigger the records stored in followig table
inserted and deleted table.
but i want to know where this tables physically stored ...i mean in which database master or some other database?
and 2nd thing tigger fired for each row or for only insert,delete,update statement?
thanx
Hi,
I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information.
For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name:
----- SQL CODE -----
SELECT DISTINCT
scContacts.ID,
scContacts.inheritCoAddr,
scContacts.fName,
scContacts.lName,
scContacts.contact,
scContacts.billToAddress1,
scContacts.billToAddress2,
scContacts.billToState,
scContacts.billToZip,
scContacts.billToCity,
scContacts.billToCounty,
scContacts.billToCountry,
scCompany.ID AS companyID,
scCompany.companyName,
scCompany.companyURL,
scCompany.billToAddress1 AS companyAddress1,
scCompany.billToAddress2 AS companyAddress2,
scCompany.billToCity AS companyCity,
scCompany.billToState AS companyState,
scCompany.billToZip AS companyZip,
scCompany.billToCounty AS companyCounty,
scCompany.billToCountry AS companyCountry,
scCompany.businessType,
scCompany.phoneExt,
scCompany.phoneNum,
scCompany.faxNum,
scCompany.minEmployees,
scCompany.maxEmployees,
scCompany.actionTypeMAX,
scCompany.actionDateMAX,
scCompany.actionTimeMAX,
scCompany.statusID,
scCompany.userID,
scCompany.repID
FROM
scCompany,
scContacts
INNER JOIN
scStatus ON
scStatus.ID = scCompany.statusID
WHERE
scgroupContacts.contactID = [insert cookie value]
AND
scgroups.ID = [insert cookie value]
AND
scCompany.ID = scContacts.companyID
AND
scCompany.groupID = scgroups.ID
----- END SQL CODE -----
As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table?
thanks,
mellamokb
bobby writes "I have a Table from which i get the ID of customers(Customers are people who have written an online exam).The date of completion of the exams are stored in the corresponding table with the name of the exam.There are more than one exams and the names of the exams are stored in an other table.Now my question is how to get the date of cpletion with the given Id from the first table?"
View 2 Replies View RelatedSubscription data are deleted...
We've got a merge replication. I have one Publisher and one Subscriber. I've made a pull subscription to a Publisher.
I've got into trouble few times occasionally 3-4 times a year. Today also I got problem with one table only one row among so many not carried out on at subscriber, even it is not find out at publisher.
This row was entered at subscriber in the morning, printed by users but in evening report it is not coming down in reports, so I had checkup and find out it is not available at any server. How?
I don’t understand, how inserted, printed data erase from subscriber but rarely. Is there any clue?
Which parameter of the subscription is responsible for it ?
Best regards.
I have a database that is 70GB big. One of the tables has over 350million rows of data. I need to delete about 1/3 of the data in thatone table.I was going to use a simple delete command to delete the unnessacaydata.Something likeDelete from tablename where columname > '100'However, it takes SOOO LONG to run, over 8 hours, if not longer....What is the best way to delete this data???One idea was to create a Temp Table, then move the needed data to thetemp table, then Truncate the Table, and move that data back.I'm not very good with SQL Query Language, so can somone give me anexample on how to do this?? Or if you have a differant idea that wouldbe faster please let me know.thanks,Sam
View 2 Replies View RelatedI have created a asp.net web api hosted on Azure. A couple of times a day all data from my database get deleted and I see these errors in the log. I have tried to reinstall microsoft sql server spatial types from nuget, but that didnt work. Could not load assembly 'D:homesitewwwrootbinSqlServerT ypesx86SqlServerSpatial110.dll'.
Error received: 'Could not load file or assembly 'file:///D:homesitewwwrootbinSqlServerTypesx86SqlServerSpatial110.dll' or one of its dependencies. The module was expected to contain an assembly manifest.'.
I want to learn that if my query deleted all data in the table by mistake, is it possible recovery and how?
View 1 Replies View Relatedhi Guys..
i don't know i m stupid or what.. but somehow i deleted my company very improtant data.. and more over i hvn't taken the backup of database..
Please help me out if any one of you guys know how can i recover the data.. i don't have a single back of this database...
Thanks .....