Column Name Conflicts With T-SQL Clause Keyword
Jan 14, 2008
I am busy extending a VB6 app to talk to SQL Server Express 2005 and have come across a naming conflict. Some of the columns in the application's Access 97 tables is "index" which obviously exists as a T-SQL keyword and therefore any queries I perform including this column throws a syntax error. I tried prefixing the columns with their table names as in TableName.Index, but this still throws up the syntax error which I thought was a bit odd. I preferably want a fix that will be Access as well as T-SQL compliant but if that's not possible I will just write a string converter that does the job based on anyone's suggestions. TIA
View 4 Replies
ADVERTISEMENT
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
Jul 31, 2015
I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;
Error:Â The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
View 6 Replies
View Related
Jul 20, 2005
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies
View Related
Mar 31, 2004
hi
I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.
in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"
is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)
CREATE PROCEDURE topcat.getTransHistory
(
@contact_id numeric(9)
)
AS
BEGIN
DECLARE @phone_no varchar(255)
set @phone_no = (select top 1 phone_num from topcat.class_contact where _id = @contact_id)
select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @phone_no
END
GO
Cheers
James :)
View 4 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
Dec 28, 2006
Hi i want to join two tables basing on like condition of the column values of two tables.
My query is like this:
select
pc_assign_worklist.pxRefObjectInsName AS "pxRefObjectInsName",
pc_assign_worklist.pxUrgencyAssign AS "pxUrgencyAssign",
pc_assign_worklist.pyLabel AS "pyLabel",
pc_assign_worklist.pyAssignmentStatus AS "pyAssignmentStatus",
pc_assign_worklist.pxAssignedOperatorID AS "pxAssignedOperatorID" ,
pc_assign_worklist.pxCreateDateTime AS "pxCreateDateTime" ,
pc_assign_worklist.pxCreateOpName AS "pxCreateOpName",
pc_index_workparty.MemberIdentifier AS "MemberIdentifier",
pc_index_workparty.LastName AS "Last Name",
pc_index_workparty.FirstName AS "First Name",
pc_index_workparty.pxInsName AS "pxInsName"
from
dbo.pc_assign_worklist, dbo.pc_index_workparty
where
pxAssignedOperatorID ='dasxkx1'
AND pc_index_workparty.pzInsKey Like '%'+pc_assign_worklist.pxRefObjectInsName+'%' ORDER BY pxUrgencyAssign DESC
-----------
i want to compare the two columns of two tables using like or contains clause as column1 in table a has value like "hi i am" where as column2 in table2 has value "hi". I need help on how to accomplish this.
View 3 Replies
View Related
Apr 8, 2004
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits
e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
SELECT af.AccomType, af.AccomRef, af.AccomName,af.address1, af.address2, bf1.RoomCode,
Convert(Int,Replace(Replace(Replace(Replace(REPLAC E(Replace(MAX(bf1.EndBook),'°','200'),'´','204'),' 99','1999'),'97','1997'),'47',1947),'98','1998')) AS max_date,
...............
WHERE
af.Resort=@strResort
AND
(af.AccomType = 'H' OR af.AccomType = 'O')
AND
max_date>20040721
order by max_date.
Problem is I get an error saying invalid column max_date. It works in the order by clause when I get rid of the
'max_date>20040721 '.
Thanks
View 2 Replies
View Related
Sep 20, 2014
In a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?
So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?
I do not want to use dynamic SQL string concatenation...
View 5 Replies
View Related
Jul 23, 2005
Hi :From a crystal report i get a list of employee firstnames as a stringinto my store procedure. Why is it comming this way ? hmmmmmm it's aquestion for me too.ex: "e1,e2,e3"here are my tablestblProjectsProjectId123tblEmployeeemployeeId FirstName1 e12 e23 e3tblProjectsToEmployeeProjectId employeeId1 11 21 32 12 23 13 23 34 14 3i need to find out the project ids all 3 of these employees worked on.so the out put i need isprojectId13How can i get it ????????????now i can use replace command to format it to a OR clause or ANDclauseSET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' ORemployeeId = ''') + ''''some thing like this.OR clause will give me all 4 projects.in('e1','e2','e3') will give me all 4 projects.of cause AND command will not give me any.other method i tried was adding the employee table 3 times into thesame SQL string and doing some thing likeWHERE (empTable1.Firstname ='e1' AND (empTable1.Firstnamein('e2','e3'))AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))AND ...and goes alone. this gives me some what i needed. but it's a verymessy way of doing it, because i get a comma seperated stringparameter i have to construct the sql string on the fly.any help or direction on this matter would greatly appreciated.thankseric
View 3 Replies
View Related
Nov 1, 2007
Hi,
I have a [TestTable] table with three rows. The pair of columns [Test1] and [Test2] are id, the [Test3] is a data column. First, I get a table variable with list of id pairs. Next, I would like to update the rows of that ids. However, I have not found the elegant way how to do it. For one column it is simple, just IN clause, which does not work (or I could not find how) for multi-columns. Does someone have a hint?
Thanks,
Martin
Note: The example bellow is dummy; on the other hand, I hope it shows the important points. Please, do not beat me on syntax errors.
Code Block
DECLARE @MyTableVar table(
Test1 int NOT NULL,
Test2 int NOT NULL
);
SELECT [Test1],[Test2] INTO @MyTableVar FROM [TestTable] WHERE [Test3] = '%dd%';
UPDATE [TestTable] SET [Test3] = [Test3] + 'ds'
WHERE ([Test1], [Test2]) IN (SELECT [Test1], [Test2] FROM @MyTableVar);
View 3 Replies
View Related
Apr 29, 2007
Hi
I need suggestion for a query. Consider following 2 tables.
Table-1 "T1"
-----------
|ID|Name
|1 |abc
|2 |def
|3 |erw
|4 |rwg
|5 |her
Table-2 "T2"
----------
|ID|Qty
|1 |12
|1 |2
|2 |22
|3 |10
|2 |14
I want a query which displays ID, Name and MAX(Qty) for each item where Max(Qty)>=10 i.e. result should be
Result
----------
|ID|Name|Qty
|1 |abc |12
|2 |def |22
|3 |erw |10
I tried:
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE MaxQty>=10
But it fails as computed or inline query columns can not be added in where clause.
However following works:
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE (Select Max(Qty) From T2 where ID=t1.ID) >=10
BUT IS IT OPTIMIZED?
Please suggest an optimized way to handle such scenarios.
View 1 Replies
View Related
Mar 25, 2004
Hello, I am using the JDCB-ODBC driver for my app, but one of the columns in my WHERE clause is 255 characters long. Because of this no rows are returned even though the statement should return 1 or 2 rows. I've tried other JDBC-ODBC drivers too and they have the same problem. Additionally, I've tried using RTrim(), Substring(), etc and it still will not work....any ideas? Is it a driver bug? Anyone know of a driver that will work?
Sample code....
ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ;
if (rs.next()) {
// Never gets here
}
Thanx!
View 4 Replies
View Related
Jun 4, 2015
I have a table Customer with column name "SerNo" the value of SerNo column is like below.
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7
Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
[code]...
I want to join it with nother table "Order"Â which has a SerNo column but does not have first part of SerNo.
SerNo.
9ad88929-f32d-459e-96c4-8d6c3d61e9d9
0a1d8b8f-7f5f-4a01-8e63-64579213afef
9342f8d7-dfdd-4535-8f01-5301bde669aa
[code]...
So basically i want to join these two tables and ignore the first part before "-" from SerNo column in customer table.
View 5 Replies
View Related
Jul 30, 2007
Hi all,
I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.
Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.
DECLARE @strSearchType varchar(2)
DECLARE @strSearchValue varchar(15)
DECLARE @SearchField varchar(15)
set @strSearchType = 'PN'
set @strSearchValue = '1234567'
IF @strSearchType = 'PN'
begin
set @SearchField = 'PART_NUMBER'
end
ELSE
begin
set @SearchField = 'INTERNAL_SKU'
end
SELECT
SKU as 'SKU',
PART_NUMBER as 'PartNumber',
DESCRIPTION as 'Description'
FROM SKUs
WHERE @SearchField = @strSearchValue
FOR XML PATH('SKU'), ROOT('Response')
Thanks,
ST
View 4 Replies
View Related
Jun 25, 2004
how does one specify multiple values for a single column in a where clause?
example:
SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')
if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:
View 2 Replies
View Related
Dec 30, 2003
Hello DBAs:
I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.
1. Col2,3,4 are NULL
WHERE Col1 = condition
2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL
where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.
How do I construct this logic. Please help
View 6 Replies
View Related
Jan 19, 2015
My SQL query is getting an error:
#1054 - Unknown column 'teacher_invoices' in 'on clause'
[code]
SELECT
users.user_id,
users.type,
users.email,
users.firstname,
users.surname,
teacher_invoices.invoice_date,
teacher_invoices.teacher_id
FROM users
INNER JOIN teacher_invoices
ON users.user_id=teacher_invoices=teacher_id
WHERE type = 'teacher' AND teacher_invoices.invoice_date >= '2013-01-01'
[code]
View 2 Replies
View Related
Jul 16, 2015
We're encrypting a column of Social Security numbers. Developer needs to execute a query -->similar<-- to:
Select <bunch of columns>
From dbo.People p
Where p.SSN = @Input_SSN_Param --I know this won't work.
If we write the where clause like
Where DecryptByKey(p.SSN) = @Input_SSN_Param
Won't every SSN in the table be decrypted? Seems like a bad idea, performance-wise.
Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?
View 3 Replies
View Related
Jul 20, 2005
I'm trying to concatenate fields in SQL stored proc for use in textfield in asp.net dropdownlist. I'm running into a problem when I tryto use a DateTime field, but can't find the answer (so far) on theInternet. Was hoping someone here would know?My sql stored proc:SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As'SelectInfo'FROM tblAnomalyWHERE WorkOrder=@varWO AND Signoff=NullORDER BY DateEnteredbut I get the error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I'm not the brightest bulb on the block, so any clues greatlyappreciated!Thanks, Kathy
View 3 Replies
View Related
Aug 3, 2015
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
View 9 Replies
View Related
May 5, 2015
I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?
[Project1].[Id] AS [Id],Â
[Project1].[DirectoryId] AS [DirectoryId],Â
[Project1].[SPI] AS [SPI],Â
[Project1].[FirstName] AS [FirstName],Â
[Project1].[LastName] AS [LastName],Â
[Project1].[NPI] AS [NPI],Â
[Project1].[AddressLine1] AS [AddressLine1],Â
[Project1].[AddressLine2] AS [AddressLine2],Â
[code]...
View 5 Replies
View Related
May 15, 2008
Greetings,
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM
...
...
ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]
END
Thanks
View 14 Replies
View Related
Sep 24, 2015
For Below example when @x=1 to retrieve col>0 rows or all rows.
With out another if else blocks or Dynamic sql to solve only in where clause.
select 0 col into #x
union
select 1 col
union
select 2 col
declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END
--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter
View 5 Replies
View Related
Jul 27, 2014
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26
What I have tried so far:
Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
View 2 Replies
View Related
Mar 28, 2007
What are conflicts from MERGE replication point of view?
------------------------
I think, therefore I am - Rene Descartes
View 1 Replies
View Related
Jul 10, 2006
I have two tables that are getting conflicts between the subscriber and the publisher, however I am pretty sure we only update these tables at the subscriber.
I have column level tracking turned on.
The tables both have Nonoverlapping, single subscription (3) set for partition options. I wonder does this do any maintenance to the tables?
I have put in place some triggers to audit what makes changes to the data, but won't know until tomorrow, so if there is something that anyone knows about that might help, please let me know.
Thanks
View 6 Replies
View Related
Oct 1, 2007
I have a database on SQL Server 2005 and am using Access 2003 for my front-end. Today, I started having problems with ONE of the tables. I can't make changes - I get the "another user has made changes" and do not have the Save Changes option - only the Copy to Clipboard or Drop Changes options. I can make changes directly to the table using the Management Studio.
I don't know if it's relative, but it happened as I was trying to add an "After Update" event to set a field equal to currentuser().
Do you have any idea what may be happening?
Thanks
View 7 Replies
View Related
Jun 14, 2006
Can someone who has had direct experience with this tell me exactly what happens when a conflict (updating same record on two nodes at the same time) occurs in a P2P replication topology? Does the Dist. Agent throw an error? More importantly does the replication set continue to replicate the articles after any error occurs?
Thanks,
Derek
View 4 Replies
View Related
Feb 19, 2007
Can someone answer/point me to an article to handle update conflicts when replicating?
Say for example, you pull down data into your sql everywhere database and start making changes. Before you replicate, changes are made in the master database that conlift with changes you've made. For example, when you pull down your data there was a product with 4 units left. So maybe you place an order for that product for 4 units while offline. However, while you were offline someone else has taken all 4 of those units and when you replicate, you need to throw an error. How do you handle this situation?
View 1 Replies
View Related
Mar 6, 2004
Hi, I need to set an alert when a replication-conflict occurs rather that to check for conflicts manually. How can i accomplish this? I couldn't find the particular error-message to trap in an alert.
Regards!!
View 1 Replies
View Related
Mar 25, 2008
I have set up merge replication between 1 publisher and 9 subscribers (all push subscriptions). Distributor and publisher are located on the same machine.
Although everything seems to work fine from the outside (most of the time), there are a lot of conflicts in the conflict table for the replication. They appear all the time. There are a lot of "download insert failed" conflicts. They always look like in the following three screenshots:
http://www.tronk.be/conflicts/conflict1.JPG
http://www.tronk.be/conflicts/conflict2.JPG
http://www.tronk.be/conflicts/conflict3.JPG
In the same way, there are also many "upload insert failed" conflicts.
In addition to this, there are some "update conflict"s (but a lot less than the other conflicts). Some of them show the same row at both sides:
http://www.tronk.be/conflicts/conflict4.JPG
Others show a different row at the both sides:
http://www.tronk.be/conflicts/conflict5.JPG
The only thing that causes a real problem is the last screenshot, although I don't understand why the other conflicts are there (the inserst statements actually seem to happen anyway, even though there are conflicts). In case of the last screenshot, I can't find any place where an UPDATE actually happens at APP-STB, while I can clearly pinpoint the UPDATE at the other side (which is what actually comes from our program).
One more thing, the system is running on its limits, but all replication seems to be working fine.
I would appriacte any help or comments very much.
View 1 Replies
View Related
Sep 11, 2007
I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:
3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.
There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?
And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.
Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?
My users are losing faith in the replication system and so am I.
Thanks for your time,
-chiraj.
View 3 Replies
View Related
