Merge Replication Corruption (system Triggers And Views)
Sep 21, 2006
All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:
Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?
-mike
View 5 Replies
ADVERTISEMENT
May 4, 2006
We have merge replication running with anamous subscribers
We have generested lots of views tables and stored procedures like
sp_ins_C435D35DDEC04FE2517CCD52A9024EC4
ctsv_07BA7383A12B4654B4D3A69B3053B227
aonflict_DH_tblReplicationRegion
How do we get rud of these I am concerned it will fill up the publisher database
Can any one advise
View 1 Replies
View Related
Aug 12, 1999
Hello there,
A Publication has been installed between 2 server (SQL 6.5, sce pack 5a).
The task Repl_Subs_Distribution fails with the following message:
"The last distributor job id and the last subscriber job id do not match. No jobs were available with a job id > 80"
1) with a Push Subscription, the error "no subscriber has been defined" came up, so I also did a pull subscritption for
the remote server = could it be the cause of the job id not matching?
2) although I found a document on a "sp_Mskill_job" replication stored procedure, it is not present in my DB and I cannot
use it. Is there a way to create it?
Thanks for your precious help.
Best regards,
Rachel
View 2 Replies
View Related
Jun 28, 2004
Hi All,
I have a strange problem, when I define an after update trigger on a view I have a message saying:
"Server: Msg 208, Level 16, State 4, Procedure after_update_tblpcl_AnalogValue_Minutely, Line 1"
although I am the owner of the view and its in my DB, but when I make the trigger as "Instead of Update" it works fine.
thanx for help.
PEACE!
View 2 Replies
View Related
Oct 15, 2007
hi all,
i had a view in my project, i am inserting a record in to that view(View contains a identity key for a column), with in stored procedure i am inserting a record i am not passing the identity key value to the insert statement. The record is getting inserted,Based on the identity key(i am getting the identity key value with Scope_Identity()) and with that value i am inserting records into another two tables.In this scenario every thing is working fine.
but now i am trying to place a trigger(instead of insert trigger) on the view, when i placed,it is not inserting record into first table,so i am not able to get the identity value of that record and process failed.
how can this achived, let me know.
View 2 Replies
View Related
Dec 18, 1998
Is there an automated way of regenerating the stored procedures, views, and triggers? Currently I drop and recreate them periodically to ensure that the dependancies are all correct and SQL uses the best plan. Is there a way to have SQL Server do this for me???
Thanks for any help with this issue...
View 1 Replies
View Related
Jan 22, 2007
Hello..
I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?
1st Design
PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..
SALES TABLE
SalesID
Date
...etc...
SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
PURCHASE TABLE
PurchaseID
Date
...etc...
PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...
and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE
2nd Design
PRODUCT TABLE
ItemID
ItemName
Price
...etc...
INVENTORY TABLE
ItemID
QtyBegin
...etc...
SALES TABLE
SalesID
Date
...etc...
SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...
and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:
QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ........
And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.
According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?
Thanks a lot.
View 3 Replies
View Related
Apr 20, 2015
I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements. some objects are still scripted out as Create. Reason is it has some extra space in between
CREATE TABLE #test1(
[NAME] [nvarchar](128) NOT NULL,
[DEFINITION] [nvarchar](max) NULL,
[DEFINITION_bk] [nvarchar](max) NULL,
[type] [char](2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
View 5 Replies
View Related
May 9, 2006
Hi,
I would like to use the view of the company data in the following stored procedures
without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.
Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.
INSERT INTO State(StateName)
VALUES(@StateName)
Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.
SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber
FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID
Here is code for the insert trigger on the view
CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN
DECLARE @CompanyName VARCHAR(128)
DECLARE @AddressID INT
DECLARE @Street VARCHAR(256)
DECLARE @City VARCHAR(128)
DECLARE @StateID INT
DECLARE @StateName VARCHAR(128)
DECLARE @ZipCode INT
DECLARE @Country VARCHAR(128)
DECLARE @PhoneID INT
DECLARE @PhoneNumber VARCHAR(32)
--DECLARE @CompanyID INT
SELECT
--@CompanyID = CompanyID,
@CompanyName = CompanyName,
@Street = Street,
@City = City,
@StateName = StateName,
@ZipCode = ZipCode,
@Country = Country,
@PhoneNumber = PhoneNumber
FROM INSERTED
INSERT INTO State(StateName)
VALUES(@StateName)
SET @StateID = @@IDENTITY
INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@Street, @City, @StateID, @ZipCode, @Country)
SET @AddressID = SCOPE_IDENTITY()
INSERT INTO Phone(PhoneNumber)
VALUES(@PhoneNumber)
SET @PhoneID = SCOPE_IDENTITY()
INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@CompanyName, @AddressID, @PhoneID)
END
The stored procedures are here.
CREATE PROCEDURE GetCompany
(
@CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @CompanyID
END
GO
CREATE PROCEDURE AddCompany
(
@CompanyName VARCHAR(128),
@Street VARCHAR(256),
@City VARCHAR(128),
@StateName VARCHAR(128),
@ZipCode VARCHAR(128),
@Country VARCHAR(128),
@PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@CompanyName, @Street, @City, @StateName,
@ZipCode, @Country, @PhoneNumber)
END
GO
View 3 Replies
View Related
Jul 20, 2005
I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}
View 1 Replies
View Related
Aug 10, 2001
I would like to put an insert trigger on sysdatabases. Is this possible?
Thanks!
View 1 Replies
View Related
Apr 20, 1999
I'm thinking of building a trigger against a system table(sysobjects) in
database(a) on server(a) that will assist me in updating a table in
database(b)on server(b). What I need to know from table(b)is if a new table
has been added and removed from database(a) on server(a). I want to use a
store procedure to query the table(b). I was thinking of building a trigger
against my sysobjects table that would update table(b) whenever any tables
are added or removed from database(a). Has anyone built triggers against
system tables? I am running SQL 7.
View 2 Replies
View Related
Jul 24, 2006
Hi, I have several independent, system components that write auditevents to a database locally via ODBC and remotely via TCP, and I havea requirement to audit when the database is available for modification.First, does SQL Server have a concept of starting/stopping specificdatabase instances? Or does my database instance "stop" only when theNT service MSSQLSERVER stops?Second, Oracle has system event triggers, e.g. AFTER STARTUP, but Ican't find an equivalent in SQL Server 2000. What alternatives arethere?Note that I can't rely on the MSSQLSERVER start/stop events in the NTApplication Event log, as the events need to be inserted "in-band" intomy database's event table.TIA,Josh
View 1 Replies
View Related
Apr 10, 2007
I was looking in sys.syslogins and sys.sql_logins for a property to tell me if the login is locked but all i see are properties for if the account is enabled or if the account has access.
Is there a system view I can query to tell if if the account is locked ?
Thanks,
View 3 Replies
View Related
Apr 15, 2008
How can we write triggers on system table dbo.sysobjects,
when i tries to write a trigger, it is giving an error that permission is denied.
I even give a permission of "allow modifications to system catalogs" in Enterprise manager.
still it is not giving permission.
How can i create a trigger on dbo.sysobjects table?
View 9 Replies
View Related
Feb 27, 2002
Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.
View 2 Replies
View Related
Mar 1, 2002
Hi,
Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.
I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??
Thanks
Rob
View 1 Replies
View Related
Apr 24, 2008
Hello,
I am trying to create a stored procedure that will disable triggers on any given table. Basically I want to pass in a table name as a parameter and query the master table for all triggers that belong to that table...then disable them. I just don't know how to get a list of triggers that belong to a table?
Thanks,
Andrew
View 2 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Jan 31, 2006
Hello,
can someone tell me where I can look to find a sql command that
is being executed by an application against my database?
In Oracle, I look in the v$sql_text view - is there something
similar in SQL Server 2000 ?
Many thanks in advance
View 2 Replies
View Related
Nov 26, 2007
I'm trying to access a System Catalog View (sys.database_permissions, and others) from a table valued function. I need to be able to pass the database name into the function so that I can operate on any database. Typically I'd use dynamic SQL to do something like
INSERT INTO #tempTable
EXEC ('SELECT * FROM ' + @DBName + '.sys.database_permissions')
But of course I can't use dynamic SQL inside of a UDF. I know I could do this using a stored procedure, but I'd need the output to be a recordset that I can query.
Has anyone done anything like this? I think I'm stuck.
View 5 Replies
View Related
Apr 17, 2007
Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Jun 4, 2008
Hi all, Is there any easy way to not allow a user to see system views? I have set up 1 view for a login, and I have to use an ODBC connection to access it. However, the 3rd party application I'm using is apparently timing out because of the number of tables/views that are returned. TIA! - Mark
View 2 Replies
View Related
Mar 5, 2006
I've had a couple problems with SMO and I wonder if anyone else has. Specifically, when I run through stored procedures (just like your example) it is painfully slow. Maybe 5 a second. Another problem is filtering on nonsystem objects with either "SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); or View.isSystemObject.
Both cases crash my app. Any ideas?
Below are two ways that both crash or hang.
SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{}
OR
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{
if (!view.IsSystemObject)
{}
}
View 1 Replies
View Related
Apr 30, 2007
Is there anyway to access system views on/from a linked server?
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTDss2005].master.[information_schema.colums]
Thanks
View 6 Replies
View Related
May 11, 2015
I got a script, which ran on SQL Server 7. After compiling on SQL Server 2012, I came across that the previous script used a system ViewsTables columns named "suid", which no longer operate in SQL Server 2012. After modifying the script with 2012 and replaced suid by SID, it works but the suid column is not appearing in System Views. What if we want to get that column back in SQL Server 2012? is there any possible way?
View 3 Replies
View Related
Mar 15, 2008
Please, could anyone tell me how to get information from which attribute of which table is the attribute from the view derived(it could also be a complex expression, not just attribute)through querying system tables or views(INFORMATION_SCHEMA, preferably if possible, because it's standard)
i'm using MSSQL2005.
Thank you!
View 4 Replies
View Related
Feb 4, 2008
As im diving into my new DBA role and reading as much as I can, I am a little confused on DMV's and system tables.
I've been reading through the book "SQL Server 2005 Bible", which has been very helpful. I tend to use it in conjunction with BOL whenever I come across something I want to learn more.
Last Friday, I tinkered around with DMV's, which was really cool, but I ran into something today that confused me.
Basically, it was finding out the recovery model for all the DB's on the server. The code in the book was:
SELECT [name], recovery_model_desc
FROM sys.databases;
Which turned exactly what it says it will.
however, I am confused.
I initially thought I needed to specify something in the "[name]" section, but realized, that is not the case.
My question is, why is that?
How do I know when to use [] around something?
I found this article:
http://www.databasejournal.com/features/mssql/article.php/3587906
I have been reading it.
I guess I am just really young and raw to T-SQL to know when to use the language corectly.
Is it due to the fact that the rules or syntax is a little different when using system tables?
Hope that makes sense.
Thanks.
TCG
View 5 Replies
View Related
Oct 8, 2007
I'm recreating many of my DBA scripts that no longer work in 2005 due to the rework of system tables. It's a risk I lived with knowing that someday the system tables would change. I'm now encountering collation problems, which I do not understand. I know how to fix the problem, but I don't know why the collation issues exist in the first place.
Run the following command.
Select * From sys.all_objects a JOIN master..spt_values b on a.type = b.type
You will receive the following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
Now run sp_help 'sys.all_objects' and look at the collation defintion for columns "type" and "type_desc". In my environment they have a collation of Latin1_General_CI_AS_KS_WS. This is different then the overall default collation of SQL_Latin1_General_CP1_CI_AS, thus causing the error.
My question is why did Microsoft need to make this collation different for these columns?
Any ideas?
Thanks, Dave
View 3 Replies
View Related
Sep 12, 2006
We are in the process of supporting two databases (Oracle 10g, SQL Server 2005) for our application.
I want to know what is the equivalent Tables/Views in SQL Server for the Oracle System tables dba_tab_comments, dba_tab_cols
Thanks in advance
View 4 Replies
View Related
Apr 25, 2008
Is this possible?
here is the situation. I have a DB on one system. I back it up and then restore it to a second system. This second system I run reports off of and I want to create custom views that do not exist on the original system. Can I restore the
backup DB from the remote system without wiping out the custom views on the local system?
I have to do this this way as they won't let us create the views we want on the remote system so the only way we have access to run the reports is by restoring the backup locally.
TIA!
View 2 Replies
View Related
Aug 25, 2000
Is there a way to not initiate triggers during replication? We have a few triggers that we do not want to use while replicating. They cause data in other tables to be deleted.
View 2 Replies
View Related
