We have an issue where we are trying to replicate a DB using SQL replication, the Replication Monitor reports an error:
Cannot TRUNCATE TABLE 'dbo.xxx' because it is being referenced by object 'View_xxx'.
Although I am the DBA, I do not have an inside knowledge of this particular DB.
I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:
Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture
Is my only option to change the TRUNCATE to DELETE?
[URL]
SQL 2012 Ent SP2
Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.
I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.
The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.
I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).
I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.
I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.
Do I have to remove replication completely to get round this? Surely not.
I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?
I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.
Hi
We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS.
I set my Transfer Sql Transfer Objects Task to use the following settings:
CopyData = true
ObjectsTocopy = All Tables
As soon as it hits a table that has a foreign key, I get the following error:
"Truncate data failed for Table nnn"
I've tried setting DropObjectsFirst = True but this fails too with foreign key violations.
First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations?
Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups?
Kind regards
Ben
HI,
I am trying to find a replication solution. It does not have to be real-time, but snapshot will not work since the database is too big. I was trying to configure Transactional replication. The replication itself worked good, but the Truncate table is not allowed in the transactional replication and merge replication. We have to use "Truncate table" in another processes during replication. Is there any other option or third party application I can use to do the replication with truncate table working. I tried Replication Exec, but it does not support 64bit system, which we have.
I am trying to find a replication solution. It does not have to be real-time, but snapshot will not work since the database is too big. I was trying to configure Transactional replication. The replication itself worked good, but the Truncate table is not allowed in the transactional replication and merge replication. We have to use "Truncate table" in another processes during replication. Is there any other option or third party application I can use to do the replication with truncate table working. I tried Replication Exec, but it does not support 64bit system, which we have.
Thanks
In my ETL job I would like to truncate stg table but before truncating stging table, I want to make sure that all the records are inserted in the data model. The sample is as below
create table #stg (
CreateID int,
Name nvarchar(10)
)
insert into #stg
select 1, 'a' union all
select 2, 'b' union all
[Code] ....
How can I check among these tables and make sure that all values are loaded into the data model and the staging table can be truncated.
Hey,
I have SQL Server 2000 DB that was converted via DTS from Acccess 97. Replication fails for any table with a single hypen in the table name. If I copy one of these tables to a name using an underscore rather than a hyphen, then it replicates successfully.
Below are errror messags from agent with -Outputverboselevel set to 2. I have also seen 'Agent error code 20082', in which case the replication command is incomplete.
Any ideas?
Thanks,
Bill Coulter
[12/7/2006 1:22:40 PM]172.23.150.51.engdata: {CALL sp_MSupd_br1-16 (NULL,?,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,0x0200020000)}
Parameterized values for above command(s): {{N'00000000001010000100', N'000000000000000000', N'730535L1205C'}}
Agent message code 20046. Syntax error or access violation
[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_distribution_history(57, 6, ?, ?, 27, 27, 2.32, 0x01, 1, ?, 1, 0x00, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 18,
Transaction Seqno = 0000108b000028f2000400000000, Command ID = 1
Message: Replication-Replication Distribution Subsystem: agent OUSBID02-engdata-172.23.150.51-57 failed. Syntax error or access violation[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_repl_alert(3, 57, 18, 14151, ?, 1, N'OUSBID02', N'engdata', N'172.23.150.51', N'engdata', ?)}
ErrorId = 18, SourceTypeId = 1
ErrorCode = ''
ErrorText = '{CALL sp_MSupd_br1-16 (NULL,N'00000000001010000100',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'000000000000000000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'730535L1205C',0x0200020000)} '
[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_repl_error(18, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: {CALL sp_MSupd_br1-16 (NULL,N'00000000001010000100',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'000000000000000000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'730535L1205C',0x0200020000)}
ErrorId = 18, SourceTypeId = 4
ErrorCode = '37000'
ErrorText = 'Syntax error or access violation'
[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_repl_error(18, 0, 4, ?, N'37000', ?)}
Category:ODBC
Source: xxx.xx.xx.xx
Number: 37000
Message: Syntax error or access violation
The error message is "cannot drop the table because it is being used for replication"
my sql version is in sql server 2012 and Im ussing merge replication.
I want to drop only one table.
I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?
Thanks in advance.
Is there any method to setup table replication between you sql server express?
View 3 Replies View RelatedI was just looking at an SSIS package someone else set up and I went into one of the execute SQL tasks and it is calling a stored procedure to truncate a table. There are a lot of places that tables are truncated within this SSIS package. But each one the 'database', 'owner', and 'table' name are hard coded.
Like this: exec dbo.uspTruncateTable 'dbname', 'dbo', 'tblname'
Of course there are different names, just changed for an example.
Here is the code in the Stored Procedure.
CREATE procedure [dbo].[uspTruncateTable]
@nDatabase varchar(255),
@nOwner varchar(255),
@ntable varchar(255)
as
declare @sqlString nvarchar(max)
set @sqlString = 'Truncate Table ' + @nDatabase + '.' + @nOwner + '.' + @ntable
exec (@sqlString)
This just seems like an unneeded step, just code the 'truncate table dbname.dbo.tblname'.
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
[code],...
Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
i did a Stored Procedure to truncate a table. it contains lots of crap from testing the site. just a simple 1 ALTER PROCEDURE dbo.TruncateUploads
AS
TRUNCATE TABLE Uploads but it says "Cannot truncate table 'Uploads' because it is being referenced by a FOREIGN KEY constraint." then i make NULL all foreign keys and try still cannot. then i did it manually deleting all records then can! but why can't i truncate it? isn't it the same as removing all values plus having the primary keys to start from 0?
How can i TRUNCATE my table (removes rows that were produced at testing), so that in the actual running the automated IDNumber for my table start with 1.
View 6 Replies View Relatedhi, I have two tables members table and orders table.
I have 2 records in members table and zero records in order table.
When I try to truncate members table I get an error message.
cannot truncate table members,because it is being referenced by a FOREIGN KEY constraint.
this what blows my head, There is no records in orders table. so why would the constraint enforced when there is no data in the order table. I created same table in access and I was able to delete a member from the members table. any comments.
thanks
Ahmed
I have several very large tables and sometimes I need to clean them.
It's known that TRUNCATE TABLE works much faster than DELETE, but impossible to use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint... Is it possible temporary disable (not delete) constraints and after complition of TRUNCATE enable them ?
Hi,
(From Bol)
"You Cannot Use Truncate Table On A Table Referenced By A Foreign Key
Constraint;"
1) States
Statecode TinyintPrimary Key
Statename Varchar(25)
2) Rivers
RivercodeSmallint Primary Key
RivernameVarchar(40)
3) Riverinstates
StatecodeTinyint,
RivercodeSmallint,
Constraint Pkriverinstates Primary Key(Statecode,Rivercode),
Constraint Fkriverinstates
Foreign Key (Statecode) References States(Statecode),
Foreign Key (Rivercode) References Rivers(Rivercode)
after removing all rows from RiverInStates Table using TRUNCATE and the command completed successfully.
when i tried to remove all rows from Rivers table , i got the following error.
"Server: Msg 4712, Level 16, State 1, Line 76
Cannot truncate table 'rivers' because it is being referenced by a FOREIGN
KEY constraint."
i thought the error may be due to the "Fkriverinstates" FOREIGN KEY CONSTRAINT. so i DROPped the constraint with
ALTER TABLE RiverinStates DROP CONSTRAINT Fkriverinstates
even after DROPping the constraint, im getting the same error
can any one point out where the problem is?
thanks in advance
what is the best way to truncate/delete records from a table at a certain time of the day...... i'm sure this is a simple ?, but i'm still learning sql.
thanks,
eddie
any idea why this is not wroking ?
------------------------------
declare
@TableName varchar
DECLARE
EmptyTables CURSOR FOR
Select Table_Name From Information_Schema.Tables
Where Table_Type = 'Base Table' Order By Table_Type
open EmptyTables
FETCH NEXT FROM
EmptyTables
into
@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
truncate table @TableName
FETCH NEXT FROM
EmptyTables
into
@TableName
END
close EmptyTables
deallocate EmptyTables
I have a database of 25 GB. I truncated a table which holds around 5 GB of data.
Even after truncation, I see my database size as 25 GB...
Should I run a shrink database after the truncation?
Thanks
Santhosh
Try to do a snapshot replication. The field type on Publishier is char(1) and on Subscriber is varchar(1). The replication fails.
Getting the following error:
Error messages:
The process could not bulk copy into table '"GRS"."GRS_IMG_NAMES"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Data conversion failed
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "OneACS"."GRS"."GRS_IMG_NAMES" in "D:Microsoft SQL ServerMSSQLReplDatauncUSER-001_MA_HAMPSHIRE_MA_HAMPSHIRE20070829173640GRS_IMG_NAMES_2#1.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SUSER-001 -T -w (Source: MSSQLServer, Error number: 20253)
Does anybody know if I can do that or structures have to be same?
Thanks in advance
TH
I have somme users who need to truncate some tables owned by DBO. I know that only table owner or DBO can execute TRUNCATE TABLE but I don't want grant DBO permission to those users. Do you have any suggestion ? Thanks a lot.
View 4 Replies View RelatedIf I have a truncate table statement in a stored proc, will my log backups be compromised due to the nonlogged operations. If so, what are my alternatives in case that I need to restore? Differential backups?
View 1 Replies View RelatedI am doing the following-
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 know that only the owner of a table can truncate it.
I want a user to run a procedure that truncates a table. Both the procedure and the table are owned by dbo, however the system says that the user can't truncate the tables because she doesn't have permission. The user has dataread and datawrite global permissions, and I do not want to give her dbo perimssions.
Is there a solution to this?
Hi all,
Is it possible to TRUNCATE a table and BCP data into the same table in one
TRANSACTION?
My problem is that I want to refresh(delete and via BCP append new data) a
table without disturbing running applications. Can I run BCP from a
SQL-script or a stored procedure?
Thank`s
Jonas Dahlqvist
Alfa Laval Automation AB
Does anybody know of a way to allow non-administrators to execute the truncate table statement?
I have developers that from time to time need to move data between their databases usinge the DTS wizard. Most of their tables have identity columns and in order retain the identity seed, they need to click on the option to enable identity insert. This option isn't available to non administrators.
Error Message "Cannot TRUNCATE TABLE 'HR.dbo.Employee' ".
All of a sudden (!) I began to get this error whle attempting to run a multi-step job: This is during a very simple T-SQL statement.
Executed as user: EPRODSQLAdmin. Cannot TRUNCATE TABLE 'HR.dbo.Employee' because it is being referenced by object 'v_apps_Employee'. [SQLSTATE 42000] (Error 3729). The step failed.
Tables are being referenced by views all the time... Why would I begin to get this message?
The only thing I can think of is that I had just made my first "indexed view" ever, and it is in this database. Could that be related to the new error message?
hi
Can We Use Truncate Command on a table which is Refernced by Foreign Key?
Hi group,In one of the books 'Gurus Guide to Transact SQL' i found this info:------------------------------------------------------------TRUNCATE TABLE empties a table without logging row deletions in thetransaction log. It can't be used withtables referenced by FOREIGN KEY constraints, and it invalidates thetransaction log for the entire database.Once the transaction log has been invalidated, it can't be backed upuntil the next full database backup.------------------------------------------------------------Does it mean that the log backup taken after table truncation is aninvalid backup?Help me! i'm lost....Thanks in advanceRonin*** Sent via Developersdex http://www.developersdex.com ***
View 5 Replies View RelatedHi,I am trying to create a script that deletes transaction tables and leavesmaster data like customer, vendors, inventory items, etc. How can I useTRUNCATE TABLE with an Exists? My problem is I have 200+ tables, if Isimply use a list like:truncate table01truncate table02truncate table03....I get errors if the table does not exist and have to manually run thetruncate statements. Some tables may not exist if that part of the app isnever used. I'm trying to make a list of all tables that could existwithout it erroring out 50+ times.Thanks in advance.
View 2 Replies View Related