I have a long query which I have set off and would like to stop, and
rename one of the tables used.
My query is due to my lack of understanding of the underlying
structure of MSSQL-Server...
So say and update updates TABLE_A and I stop it, whilst this
transaction is rolling back I attempt to rename TABLE_A to TABLE_A_OLD
and rename a different table to become TABLE_A. I am assuming that
the rollback actions will use the object reference of TABLE_A_OLD and
continue to rollback the effects on the correct table and not corrupt
'new' TABLE_A... or will it not allow me to rename TABLE_A until the
rollback is complete?
WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:
Begin Transaction
Lock Table XTable
Drop XTable
Alter Table XTable_temp rename to XTable
Release Lock XTable
End Transaction
Create XTable_temp
I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.
HiI'm expecting the answer no here but its worth asking.If I wanted to test an arbitary program could I somehowsay in query analyser, start a global transaction on thisdatabase, run my tests, then at the end roll it back from withquery analyser?I know I can use backup and restore but this would beeasier, still, again, I don't think you can do this.TaF
How to prevent a stored procedure or trigger from being invalidated after renaming a table. I used sp_rename but it does not alter the stored procs and triggers using the table ,is there any way to change all the dependent objects without manually generating the script and then changing it
Hi, I have written a service to rename table, how to check is the table is locked by another processes? There are some processes will execute select statement on it. The rename transaction must wait until all the select transactions have been finished.
Does there has a way to rename a table name in sqlce 3.5 database ? It looks like use sql alter command can not do this task . Any one could give me advice are welcome. Thanks.
This morning I needed to update some bad address's in our production DB. I ran this query in ISQLW:
UPDATE mytable SET city = 'blah' WHERE city <> 'blah' GO
it should have been: UPDATE mytable SET city = 'blah' WHERE city <> 'blah' AND name = 'blah' GO
So I basically started updating my entire DB rather than just a handful of records. The query seemed to be running too long (30seconds) then I noticed and clicked the Cancel button. Does this transaction get rolled back when you click cancel? Im not sure where to look to see if the rollback occurred.?
I do have a way to re-update all the address's and a backup but Ive never restored and Im not sure I even need to. I could also run a query to group all the address's but it will be very difficult to tell what got updated. Where talking about 500k records.
i have a table called images it has over 200,00 rows - i need to add 2 new fields and could not do it. i created a new table and moved over the records but i can't delete or truncate or rename the table either
EXEC sp_rename 'images', 'imagesold' is just taking forever
begin transaction truncate table Acounts select * from Accounts rollback select * from Accounts
The first select is returning 0 rows as expected. But after doing the rollback I expected to yet see 0 rows as truncate is not logged so cannot be rolled back. But to my suprise I see that there are rows.
So the rollback is rolling back the truncate table statement. Can someone please explain this????
I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.
When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.
I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.
i've SQL EXPRESS and want to rename the column/field of a table but it's not running. here is the query i'm using: ALTER TABLE tablename RENAME COLUMN OldName TO NewNameand it shows me the below error:Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'column'. pls tell me how can i rename a field of a table.
i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05
my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one
1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.
DROP TABLE aMgmt.Employee GO EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT' GO
I need to rename a replicated table columns.will this affect my current replication (Transactional replication)? Do i have to create a new snapshot and restart the subscriber?
I am using the compact edition on a desktop using VS2005 as well as SQL Server Management Studio. None of those tools allow me to rename a column or rename a table. Can someone point me a tool the runs on the desktop (as opposed to running on a CE device) that allows me to do the renaming?
I have a client who accidentally deleted data from a table this morning around 10:15 AM. Classic case of accidentally did not highlight the WHERE condition in the DELETE statement and ran it.
1. Database is in Full Recovery mode. 2. Unable to locate the last backup file. 3. Transaction Log file is around 128GB while the mdf is around 212 GB.
Is there a way we could restore the database to a new name and take it back to 10AM, get the table data back and re-insert into the production database?
I renamed a database table, and then try to create a view with that table. However, the renamed table does not appear in view designer. I have to close and re-open SQL Server Management studio 2014.
I'm trying to rename a table with date suffix at the end of the table name, and drop the date suffix table which is greater than 7 days. for that I have the below sql, I have not included the drop syntax in this.
I'm not able to rename with the date suffix in the below sql, syntax error at '+'
DECLARE @TPartitionDate date IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'IIS_4') BEGIN SELECT @TPartitionDate = MAX(PartitionDate) FROM PartitionLog (NOLOCK) EXEC sp_rename 'IIS_4','IIS_4_'+ @TPartitionDate END
If you drop / rename a table and then recreate the table with the SAME NAME, what impact does it have on stored procedures that use these tables? From a system perspective, do you have to rebuild / recompile ALL the stored procedures that use this table?
I had a discussion with someone that said that this is a good idea, since the IDs of the tables change in sysobjects and from a SQL SERVER query plan perspective, this needs to be done...
Question 2
If you Truncate a Table as part of a BEGIN TRANSACTION, what happens if an error occurs? Will it Rollback? The theory is that it won't because Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?
SQL 7.0 SP1 - NT 4.00 EE SP6a - Cluster - Multiprocessor(2)-VB 6.0 SP4. Maybe a very stupid question,but I need to know if it is possible to assign a low priority to a VB exe that query for long time eating all the CPU (100% in task manager). Thank you. Franco
I'm working on creating a new version of an existing table. I want to keep the old table around, only with a different name. In looking this up I found there's a system routine named sp_rename, which looks like it will work fine.However in thinking about this I realized that the foreign key constraints defined on the table (there's 3 of them) are likely to not be renamed, correct?
If I'm correct, then I suppose I could rename the table, then drop the 3 foreign key constraints, and create them new using different names for those foreign key constraints.
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table. When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds. When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help!
HelloI am using an allready Full database MS SQL 2000my 3 tables -->Report :ReportID (PK)RNameRValueProduct :PNameCategoryReportID (FK)Infos :ICommentsIVaLuemy query (to get a new table with only columns, or a .NETcollection) -->SELECT Report.ReportID AS RID, Report.RName AS RN, Report.RValue AS RV, Infos.Commentar AS IC,MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50, MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54, MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78, MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540, MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421FROM Report INNER JOIN Product ON Report.ReportID = Product.ReportID LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue WHERE (Report.ReportID = 10)GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.ICommentsReport.ReportID = Product.ReportID --> Primary Key to Foreign KeyReport.RValue = Infos.IValue --> only on full text (100 char)they are not indexedin Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infosit can be very longhow can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)thank you
