when i run i have an error with some tables constraints.
As you know If we try to TRUNCATE a table, and the table is have a relationship with other table like for exemple Employees_Salary with the constraint of SQL Foreign Key then we can't TRUNCATE the table with SQL Truncate Table command, we need to TRUNCATE the Employees_Salary table first because Employees_Salary is depending on it and that is having some records. That's why we need to TRUNCATE both tables and the sequence is to empty that table which is based on SQL Foreign Key or remove this constraint first.
What i need is a list of all database tables ordered by the right sequence considering the constraints
Anyone can help me?
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table DepartmentDetail table references Department table Department table references Employee table
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it? Newbie
I've been handed a database with over 100 tables and told to find everywhere a cascade delete constraint exists. I could just go through every table by hand and check, but I think there must be an easier way, perhaps an sql query on the master db. Any thoughts?
Hi all, I am trying to delete all of my table constraints and I generated this script:
declare @table_name sysname declare @alter_table_statement varchar(256) declare @const_id integer declare @prt_obj integer declare @const_name varchar(256) declare @parent_name varchar(256) -- definindo o cursor... declare constraint_id cursor local fast_forward for select id from sysobjects where XTYPE='PK' OR XTYPE='F' order by parent_obj
declare parent_obj cursor local fast_forward for SELECT PARENT_OBJ FROM SYSOBJECTS WHERE XTYPE='PK' OR XTYPE='F' order by parent_obj -- definindo o cursor...
-- apagando as constraints... open constraint_id open parent_obj fetch next from parent_obj into @prt_obj fetch next from constraint_id into @const_id set @parent_name = (select name from sysobjects where id=@prt_obj ) set @const_name = (select name from sysobjects where id=@const_id) select @alter_table_statement = 'alter table '+ ltrim(rtrim(@parent_name)) + ' drop constraint ' + ltrim(rtrim(@const_name)) exec(@alter_table_statement) while @@Fetch_Status = 0 begin fetch next from parent_obj into @prt_obj fetch next from constraint_id into @const_id set @parent_name = (select name from sysobjects where id=@prt_obj ) set @const_name = (select name from sysobjects where id=@const_id) select @alter_table_statement = ('alter table '+ ltrim(rtrim(@parent_name)) + ' drop constraint ' + ltrim(rtrim(@const_name))) exec(@alter_table_statement) end close constraint_id close parent_obj
-- desalocando o cursor... deallocate table_name_cursor deallocate constraint_id deallocate parent_obj
The problem is that this script doesn't complete it's action because of constraint reference problem. The constraint 'PK__justification__0FEC5ADD' is being referenced by table 'justification', foreign key constraint 'FK6F298AF2E0E77479'. Can I turn off this constraint verification? Or there is another way to delete this constraints?
Is there any easy way to truncate a table which has a foreign key restraint? I want to override the default behavior which is to not allow truncate of parent tables. I want to be able to temperarily remove the contraint so I can truncate the temple, how do you do this?
I have an issue with Delete statement.In the code given below (its a part of actual proc),if we use TRUNCATE to clean the temp tables, everything goes fine.But if I use DELETE in place of truncate, system skips the IF loop 'if (@script_type = 1 OR @script_type = 2)'I am not able to understand this behavioral difference between DELETE and TRUNCATE.Recently the database is being used for replication, but that should not be a reason.
SELECT @max_rows = COUNT('X') FROM #temp_table1 SET@row_cnt = 1 WHILE @row_cnt <= @max_rows BEGIN
I have to truncate access table before I insert records to access database table.
I tried using Delete From Table_name or Truncate table Table_NAME and I have used Microsoft Jet 4.0 OLE DB Provider. It does not seem to work. I read some post on forums. None of them seem to work while truncating or deleting records from one of the access database table.
Is there any easy way to truncate access database without using script component and VB scripts. I do not know how to Write VB scripts so trying to find alternatives.
Hi, We have a DB with a heirarchy of tables each connected via Guids and controlled for Delete Operations by Foreign Key Constraints. However, under certain privileged conditions we would like CERTAIN users to be able to perform a Cascade Delete all the way down. Thus by example we have tables A,B,C,D - Typically once a record is inserted into Table A then we can add multiple records in Table B referencing that new record in Table A...and so on through records in Table C referencing Table B...records in Table D referencing table C. Now when we try and delete that ONE Record in Table A we 'could' implement CASCADE DELETES which would delete all those records in Table B,C,D. We decided that was too dangerous to implement as Whooosh....all records could be deleted by a non-privileged user deleteing that ONE records in Table A, so we changed the Cascade DELETE to NO ACTION. However, there are times (hah...particularly in Testing !) when we would like a privilegd user to be able to DO that task...i.e. perform a CASCADE delete. Has anybody got any suggestions on how this would be best, easily and securely implemented. I have had a brief look at INSTEAD OF Triggers but am not sure about the pitfalls of using this... ANy help appreciated, Cheers, Desmond.
How to delete records from multiple tables if main table’s entity is deleted as constraints is applied on all..There is this main table called Organization or TblOrganization.and this organization have branches which are in Brach table called tblBranch and this branch have multiple applications let say tblApplication and these application are used by multiple users called tblUsers.What I want is: when I delete the Organization All branches, application and users related to it must be deleted also.How I can apply that on a button click in asp.net web forms..Right now this is my delete function which is very simple
Public void Delete(int? id){ var str=”DELETE FROM tblOrganization WHERE organizationId=”+ id ; } And My tables LOOK LIKE this CREATE TABLE tblOrganization ( OrganizationId int, OrganizationName varchar(255)
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this? Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Hi, I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error. e.g MOB 401.908.804 - Fails 0401.907.324 - okay 8239 9082 (pager) - fails Anyone got an idea on how to fix this???? Regards.. Peter.
With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).
Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:
1. Log backup 2. Log backup no truncate
From what I understand and have read, the "no truncate" backup method keeps the entire transaction log indefinitely. Using the truncation method, the transaction log is either 1) compressed or 2) cleaned up so that any completed transactions are removed from the log. Which one of these is true?
And, for the big question: is it better to run a backup of the transaction log with truncation or not? Our current backup scheme is similar to the following:
Full backup every 24 hours transaction log backup every hour with no truncation
Should we insert a truncation backup somewhere in here? What is the danger of removing (or compressing) parts of the transaction log? Will this affect the restore process?
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done?
The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A / Table B Table C / Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
this is my Delete Query NO 1 alter table ZT_Master disable trigger All Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) alter table ZT_Master enable trigger All
I have troble in Delete Query No 2 here is a select statemnt , I need to delete them select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) I tried modified it as below delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) but this doesn't works..
can you please help? and can I combine these 2 SQL Query into one Sql Query? thank you
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETE AS
[code]...
When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products WHERE vendor='Foo' AND productId NOT IN ( SELECT productId FROM sql2000.staging.dbo.fooProductList )
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?
I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.
i read that i can Truncate a table even if child table has no records so i tries to disable constraints but still can't get it to workCannot truncate table 'InventoryPC' because it is being referenced by a FOREIGN KEY constraint. disabling code on lines 9-13 and enabling codes on 36-40 1 ALTER PROCEDURE dbo.RevertDB 2 3 /* Reverts Database to original "Clean" State */ 4 AS 5 SET NOCOUNT OFF 6 DECLARE @Log AS varchar(MAX), @RowsInDB AS int 7 SET @Log = 'RevertDB Started at ' + CAST(GETDATE() AS varchar(50)) + '' 8 9 /* *** Disable Constraints *** */ 10 ALTER TABLE Booking NOCHECK CONSTRAINT ALL 11 ALTER TABLE InventoryPC NOCHECK CONSTRAINT ALL 12 ALTER TABLE PC NOCHECK CONSTRAINT ALL 13 ALTER TABLE Platform NOCHECK CONSTRAINT ALL 14 15 /* *** Start Truncates *** */ 16 TRUNCATE TABLE Booking 17 SET @Log = @Log + 'Trucate Table Booking - Done' + '' 18 SET @RowsInDB = (SELECT COUNT(BookingID) FROM Booking) 19 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '' 20 21 TRUNCATE TABLE InventoryPC 22 SET @Log = @Log + 'Trucate Table InventoryPC - Done' + '' 23 SET @RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC) 24 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '' 25 26 TRUNCATE TABLE PC 27 SET @Log = @Log + 'Trucate Table PC - Done' + '' 28 SET @RowsInDB = (SELECT COUNT(PCID) FROM PC) 29 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '' 30 31 TRUNCATE TABLE Platform 32 SET @Log = @Log + 'Trucate Table Platform - Done' + '' 33 SET @RowsInDB = (SELECT COUNT(PlatformID) FROM Platform) 34 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '' 35 36 /* *** Enable Constraints *** */ 37 ALTER TABLE Booking WITH CHECK CHECK CONSTRAINT ALL 38 ALTER TABLE InventoryPC WITH CHECK CHECK CONSTRAINT ALL 39 ALTER TABLE PC WITH CHECK CHECK CONSTRAINT ALL 40 ALTER TABLE Platform WITH CHECK CHECK CONSTRAINT ALL 41 42 SET @Log = @Log + '*** End Truncates ***' + '' 43 /* *** End Truncates *** */ 44 45 /* *** Start Insert Platform *** */ 46 SET @Log = @Log + 'Start Insert Platform' + '' 47 48 EXEC dbo.InsertPlatform 'Windows XP SP2 Professional Edition', 'Some description for Windows XP SP2 Professional Edition over here …' 49 EXEC dbo.InsertPlatform 'Windows Vista Ultimate', 'See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance.Website: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx' 50 EXEC dbo.InsertPlatform 'Apple Mac OS X Tiger', 'Some description for Apple Mac OS X Tiger over here …' 51 EXEC dbo.InsertPlatform 'Apple Mac OS X Leopard', 'Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace.Finder: Browse your files like you browse your music with Cover Flow.Time Machine: See how your system looked on any given day and restore files with aWebsite: http://www.apple.com/macosx/leopard/features/' 52 EXEC dbo.InsertPlatform 'Red Hat Linux', 'Some description for Red Hat Linux over here …' 53 54 SET @Log = @Log + 'Rows In Platform: ' + (SELECT COUNT(PlatformID) FROM Platform) + '' 55 /* *** Start Insert PC *** */ 56 SET @Log = @Log + 'Start Insert PC' + '' 57 58 DECLARE @WinXP int, @WinVista int, @OSXTiger int, @OSXLeopard int, @RedHat int 59 SET @WinXP = (SELECT PlatformID FROM Platform WHERE Title = 'Windows XP SP2 Professional Edition') 60 SET @WinVista = (SELECT PlatformID FROM Platform WHERE Title = 'Windows Vista Ultimate') 61 SET @OSXTiger = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Tiger') 62 SET @OSXLeopard = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Leopard') 63 SET @RedHat = (SELECT PlatformID FROM Platform WHERE Title = 'Red Hat Linux') 64 65 EXEC dbo.InsertPC 'Fusion PC One', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '1GB Dual Channel DDR2 667 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @WinXP 66 EXEC dbo.InsertPC 'Fusion PC Two', 'Description here ...', 'Intel Core2 Duo E6850 3 GHz 1333MHz', '2GB Dual Channel DDR2 800 SDRAM', '240GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '24" 3000:1 Wide Screen LCD', @WinVista 67 EXEC dbo.InsertPC 'Fusion PC Three', 'Description here ...', 'AMD Athlon 64 X2 Dual Core 6000+ 3 GHz', '2GB Dual Channel DDR2 667 SDRAM', '240GB SATA2 NCQ HDD', 'ATI Radeon Cross Fire 2900 256MB GDDR3', '24" 3000:1 Wide Screen LCD', @WinVista 68 EXEC dbo.InsertPC 'Fusion X1', 'Description here ...', 'Intel Core2 Extreme Q6850 3 GHz 1333MHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '30" 3000:1 Wide Screen LCD', @OSXLeopard 69 EXEC dbo.InsertPC 'Fusion X2', 'Description here ...', 'AMD Athlon 64 FX 74 3 GHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3', '30" 3000:1 Wide Screen LCD', @WinVista 70 EXEC dbo.InsertPC 'Fusion Tiger 1', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '2GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3 SLI', '22" 3000:1 Wide Screen LCD', @OSXTiger 71 EXEC dbo.InsertPC 'Fusion Linux 1', 'Description here ...', 'AMD Athlon 64 X2 6000+ 3 GHz', '1GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @RedHat 72 73 SET @Log = @Log + 'Rows In PC: ' + (SELECT COUNT(PCID) FROM PC) + '' 74 75 /* *** Start Insert Inventory *** */ 76 SET @Log = @Log + 'Start Insert Inventory' + '' 77 78 DECLARE @F1 int, @F2 int, @F3 int, @FX1 int, @FX2 int, @FT1 int, @FR1 int 79 SET @F1 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC One') 80 SET @F2 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Two') 81 SET @F3 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Three') 82 SET @FX1 = (SELECT PCID FROM PC WHERE Title = 'Fusion X1') 83 SET @FX2 = (SELECT PCID FROM PC WHERE Title = 'Fusion X2') 84 SET @FT1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Tiger One') 85 SET @FR1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Linux One') 86 87 EXEC dbo.InsertInventory 10, @F1, 2.5, 'iCluster Fusion One' 88 EXEC dbo.InsertInventory 10, @F2, 2.5, 'iCluster Fusion Two' 89 EXEC dbo.InsertInventory 10, @F3, 2.5, 'iCluster Fusion Three' 90 EXEC dbo.InsertInventory 6, @FX1, 6, 'iCluster Fusion X1' 91 EXEC dbo.InsertInventory 6, @FX2, 6, 'iCluster Fusion X2' 92 EXEC dbo.InsertInventory 10, @FT1, 3, 'iCluster Fusion Tiger One' 93 EXEC dbo.InsertInventory 30, @FR1, 2, 'iCluster Fusion Linux One' 94 95 SET @Log = @Log + 'Rows In Inventory: ' + (SELECT COUNT(InventoryID) FROM InventoryPC) + '' 96 97 RETURN @Log 98
I have two tables in my SQL CE database. tblTempData and tblBatchData. When the user wants to move the data from 'temp' into 'batch' I would like to truncate the 'temp' table. When I do this I get the unable to parse query error.
I have a simple stored procedure that will truncate the log file but I now need to make this dynamic so that this proc can be run with any DB name. I know you can look up the logical filename in sysfiles but I'm not too sure how to write this procedure so that it will simply truncate the log file of the currently selected DB.
This is what I have so far which as you can see is hardcoded:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
CREATE PROCEDURE TruncateLog AS
BACKUP LOG [FICaches] with TRUNCATE_ONLY
DBCC SHRINKFILE (FICaches_log, 50)
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
