Hi,
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!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end of it a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Mnay thanks for your help!
av
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Thanks in advance!
I have a query which is quite complex. It's based on a set of data ina complex view which takes the data from several tables.In this complex query, if I allow the various parts of the query towork on the results of the view (MISView), it can take 15 minutes torun (eek !), however, if I create a temporary table with the data fromthe view and then use that for the remainder of the query, it runs inapprox 20 seconds.Now, I have examined the execution plan (my new favourite toy) andthere is a difference (as expected). However when looking at the partof the query that takes up most of the time, it shows that it bringsback 109,645,866 records from a table (Credit) that contains 13,002records. This table is one that is referenced in the view (MISView)which contains 13,653 records and does get some of it's data from thetable which is scanned (Credit).For the record, we don't have any tables with over 100,000 records in,so 109 million rows is going some for us. The part of the query thatruns slow does reference another copy of itself but this is necessaryfor the equation that is being run.Now I'm OK with why it's doing the table scan, but why does it bringback substantially more data than is in the table ? Is it somemultiple of the number of records that it's trying to work out. Iassume it tries to run a seperate plan for the view as part of it'sprocess.Ideally, I'm still going to go down the route of the temporary table,but I would like to understand more about what it does first as Idon't like leaving things unanswered.Any help would be appreciated.
View 6 Replies View RelatedHi!
What is the difference in performance if I use a Temp-table or a local-table variable in a storedprocedure?
Why?
//Daniel
Hi, all experts here,
Thank you very much for your kind attention.
I am wondering if we could back up the databases to any place outside of the local server system? As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient.
Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi,
I'm trying to copy all the data from a single table across to a remote linked server and I'm having all sorts of problems.
The SQL query I'm attampting is this:-
Code:
EXEC sp_addlinkedserver 'GODZILLA', N'SQL Server'
EXEC sp_addlinkedsrvlogin 'GODZILLA', 'False', 'rmtUser', 'lclUser', 'password'
Go
--DELETE TABLE
DROP TABLE GODZILLA.Racing.dbo.horse
--SELECT INTO
SELECT horse_id, horse_filename, horse_time, horse_new, horse_driveLetter
INTO GODZILLA.Racing.dbo.horse
FROM Racing.dbo.horse
EXEC sp_droplinkedsrvlogin 'GODZILLA', 'lclUser'
EXEC sp_dropserver 'GODZILLA'
Basically - this doesn't work as an error is returned:-
Code:
The object name 'GODZILLA.Racing.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Now - it has no problem running a SELECT (ie read-only) query from the remote DB but why does it have a problem with writing? And if thats the issue, why doesn't it say so. Am I going about this the right way???
Please help!
Greetings -I'm using an Access front end to a SQL Server (2000) databas*e. Viaseveral steps, I create a temp table and manipulate the data* in it.Iwant to update the backend with this new data but my UPDATE *queryfailsas my temp table is local and the SQL database doesn't know *about it.There are no linked tables in the FE database.I have the following (DAO):Set Db = CurrentDbSet Qdf = Db.CreateQueryDef(TMP_QUERY_NAME)Qdf.connect = ConnectString()sqlString = "UPDATE tblRemote " & _"SET " & _"tblRemote.Some_Foo = tblLocal.Foo, " & _"FROM tblRemote INNER JOIN tblLocal " & _"ON tblRemote.Some_ID = tblLocal.Some_ID;"Qdf.sql = sqlStringQdf.ReturnsRecords = FalseQdf.Execute dbFailOnErrorIs there any way of doing this without adding a linked table*?Thanks, chris
View 2 Replies View RelatedHi
I have an sqlserver setup locally to chew thru pricefiles from our suppliers. The pricefiles comes in various formats mostly txt-files and are inserted via bulk-inserts and separeted by GO-statements, in the end I end up with a table containing unique partnumbers, the average price for the part, and number in stock. What i would like is to have a transact- statement in the end of all thoose sql-statements that copies the final table to a remote sqlserver that contains the DB used on my website. How do i connect to a remote server thru a sql-statement? is it possible?
If i just get the connection part explained, i can figure out the rest by myself.
Thanks
/Jonas
I have a SQL CE database that is synching up with a SQL Express database (therefore no replication).
I do a pull down (with RDA) Table XYZ with no rows.
I fill out the rows on the PDA
I push the changes back to SQL Express
What I would like to do now is selectively delete some rows from the CE Database. BUT, I don't want those rows to delete from the SQL Express master database.
How can I do this?
Hi,
I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large.
If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that.
I appreciate any advice / guidance anyone can offer me!
I am not sure if this is possible or not, but I have to at least ask.
I have a SQL Reporting Services project that has a QA server version and then a Production server version. So when requested changes are made to the reports, I make the changes in Visual Studio and then push to QA. After the changes have been tested and approved, I then push the changes to the Production server.
I have come across an issue, where requested changes were made in Visual Studio (a lot of changes), and pushed to QA. The users now have changed their minds and they want instead to stay with what is in production. So, I am looking for a way to recover the report file on the production web server, bring it back into my visual studio project to replace the report that I had changed.
Is this possible, or will I have to start over and step one and reverse the changes in Visual Studio.
Let me know.
Thank you,
T.J.
After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help
View 1 Replies View RelatedHi,
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 all
I would like to know if its possible to "Save" records when they get deleted.
For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc...
In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view.
All this should go to a seperate database or seperate table, it doesnt really matter.
My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
It happened accidently that we deleted records from a table in SQL server 2005 DB. We never took a full backup of the DB till then. Is there any way that we can recover the deleted records. Logs files are still present. ( *mdf and *ldf ).
View 3 Replies View Relatedhello forum
Is there way to undelete deleted records from a table? - For security reasons I need to delete records without removing them out of the database.
In other words: target is to design a procedure to delete records only logical.
I had the idea to use a field, saying record is deleted or not.
This field would control if data is hidden (logical deleted) or not. To undelete a record, only this field would change its state.
But it becomes difficult if you have many FK to logical deleted data.
Is there a better or easier way to do this?
Thanks for you help !!
I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.
View 3 Replies View RelatedHello,
Can any one tell me ..is there any restriction on the total number of records that can be added to a table ( SQL Server 2005) .Because .. i added 100 records to a table through my program..but i was able to c ..only the last 20 records which is being inserted..can any one tellme any way to increase the total number of records..
pls..help me..solution is required immediately.........
Regards,
Sweety
Hi,
i have deleted 5 to 10 records in a table. is there any way to retrieve the deleted records?
please help me in this regards.
Thanks in Advance.
M.ArulMani
I want to delete some records from dbo.ABC table using where clause.
And would like to restore the deleted records back into the same table.
Hello,
I have a stored procedure that's running a little slower than I would like. I've executed the stored proc in QA and looked at the execution plan and it looks like the problem is in a trigger on one of the updated tables. The update on this table is affecting one row (I've specified the entire unique primary key, so I know this to be the case). Within my trigger there is some code to save an audit trail of the data. One of these statements does an update of the history table based on the inserted and deleted tables. For some reason this is taking 11.89% of the batch cost (MUCH more than any other statement) and within this statement 50% of the cost is for a table scan on inserted and 50% is for a table scan on deleted. These pseudo-tables should only contain one record each though.
Any ideas why this would be causing such a problem? I've included a simplified version of the update below. The "or" statements actually continue for all columns in the table. The same trigger template is used for all tables in the database and none of the others seem to exhibit this behavior as far as I can tell.
Thanks for any help!
-Tom.
UPDATE H_MyTable
SET HIST_END_DT = @tran_date
FROM H_MyTable his
INNER JOIN deleted del ON (his.PrimaryKey1 = del.PrimaryKey1) and
(his.PrimaryKey2 = del.PrimaryKey2)
INNER JOIN inserted ins ON (his.PrimaryKey1 = ins.PrimaryKey1) and
(his.PrimaryKey2 = ins.PrimaryKey2)
WHERE (his.HIST_END_DT is null)
and ((IsNull(del.PrimaryKey1, -918273645) <>
IsNull(ins.PrimaryKey1, -918273645)) or
(IsNull(del.PrimaryKey2, -918273645) <>
IsNull(ins.PrimaryKey2, -918273645)) or
(IsNull(del.Column3, -918273645) <>
IsNull(ins.Column3, -918273645)))
hi all
i deleted 8 fields from a table and i need to retrieve them back, is there a way to do that.
please help it is very urgent
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
Hi there,Could somebody post some simple example how one can refer toa column in the 'deleted' temporary table within a trigger definition ?Should the 'deleted' and 'inserted' temp tables be declared, or they are'implied' ?I keep having this msg when trying to create a trigger"The column prefix 'deleted' does not match with a table name or alias nameused in the query"Thanks for your inputs,Eddy.
View 2 Replies View RelatedFor some reason in Enterprise Manager for SQL Server 2000, I cannotput the following line into a trigger:select * into #deleted from deletedWhen I hit the Apply button I get the following error:Cannot use text, ntext, or image columns in the 'inserted' or'deleted' tablesThis seems like a weird error, since I am not actually doing anythingto the inserted or deleted tables, I am just trying to make a tempcopy.I have another workaround but I am just curious why this happens.Thanks,Rebecca
View 3 Replies View RelatedIs there a configuration or a trick to improve the speed of the access to inserted and deleted tables whithin a trigger? Whenever a trigger is called, the access to inserted or deleted constitute approximatly 95% of the execution time.
Is there a way to have access to inserted and to deleted improved other than copying the data to another table?
I 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 RelatedWhat is the best way to detect that a sqlserver table row has foreign keys and cannot be deleted?Thanks,Keith
View 1 Replies View Related