Hello,
is it possible to use CROSS APPLY like this:
SELECT [x]
FROM [Order] CROSS APPLY (SELECT .OrderNo + 3) [x]
i know you need [x].* but is it possible to use a function so that only [x] is needed?
thx in advance for help
MattGo
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END
and then run a sample T-SQL statement
Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
What is Cross Apply, when it will be used ?
View 2 Replies View RelatedHi,
Why am I getting a different numbers of distinct ids in those queries?
USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';
select locations.*, T.c.value('.','nvarchar(max)') from
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
) as locations cross apply @myXml.nodes('(/lol)') T(c);
select mytable.* , T.c.value('.','nvarchar(max)') from
(
select newid() as Id
union
select newid()
) as mytable cross apply @myXml.nodes('(/lol)') T(c);
Thanks,
Victor
I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
I am trying to add Extended properties to each of the columns in my table('MyTable')to the columns
DECLARE @NewTableName as nvarchar(128);
SET @NewTableName = 'MyTable'
SELECT ColumnName FROM(
SELECT c.name as ColumnName
FROM syscolumns c
INNER JOIN sysobjects o
ON o.id = c.id
WHERE o.name = @NewTableName) as T
OUTER APPLY
sp_addextendedproperty( 'Caption', ColumnName,
'user', 'dbo',
'table', @NewTableName,
'column', ColumnName)
I am getting an invalid object name 'sp_addextendedproperty' error.
Jaime
I've been trying to figure out why these two return a different amount of distinct ids...
Is that a bug in optimization?
Code Snippet
USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';
WITH locations as
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
)
select locations.*, T.c.value('.','nvarchar(max)') from locations cross apply @myXml.nodes('(/lol)') T(c);
with mytable as
(
select newid() as Id
union
select newid()
)
select mytable.* , T.c.value('.','nvarchar(max)') from mytable cross apply @myXml.nodes('(/lol)') T(c);
Good Afternoon,
I'm attempting to leverage SQL's new 'APPLY" operator and I guess I don't fully understand it proper usage.
This is a relatively simple request, first i want to count the models produced within a valid period of time. The first 'Cross Apply' gets the valid starting and ending dates and looks ups the number of models produced for the period of time. This section of code works perfectly fine.
The problem appears to be with the second "Cross Apply". What I'm attempting to accomplish is to count all models produced, regardless of time frame.
When executed the query appears to go into an loop and I end up canceling out the request.
Any ideas where I went wrong?? Any help is greatly appreciated!
select b1.model as Model
,b1.MinDate as Mfg_Str_Date
,b1.MaxDate as Mfg_End_Date
,Count(b2.Model+B2.Serial) as Mfg_Date_Valid
,Count(b3.Model+B3.Serial) as All_Units
from (select b.model, min(b.build_date) as MinDate ,max(b.build_date) as MaxDate
from etbl_models_Serial as b
group by b.model) as b1
--These are Units produced within Valid Window
cross apply (select b2.model,b2.Serial
from etbl_Production as b2
where b2.Model = b1.Model
and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2
--These are all units produced
cross apply (select b3.model,b3.Serial
from etbl_Production as b3
where b3.Model = b2.Model) as b3
Group by b1.Model, b1.MinDate, b1.MaxDate
Order by b1.Model
I can't figure out what can be causing this.
When I use this query
Select top 1000 a.EmployeeID,b.*
from #TmpActiveEmployeesWSeverance a
cross apply
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID
It runs 4 seconds
If I try to insert the results into anything It runs > 5 minutes (I have yet to let it finish)
I have tried the two following pieces of code, both with the same results
Select top 1000 a.EmployeeID,b.*
into #Tmp
from #TmpActiveEmployeesWSeverance a
cross apply
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID
--and
Insert Into TRP_ActiveEmployeesWSeverance
(EmployeeID
,PK
,BeginningBalance
,BenefitInterestRowID
,BenefitInterestID
,BenefitTypeID
,DateReceived
,InvoiceDate
,Amount
,Hours
,Fraction1
,Fraction2
,Interest
,InterestAmount
,StartDate
,EndDate
,PeriodApplied
,Offset
,Reserve
,Account
,BenefitClosedID
,PaidOut
,ClosedAccount
,ai
,ClosedDate
,StartAgain
,PartialDividend
,PartialFraction
,SameDateCount)
Select top 1000 a.EmployeeID,b.*
from #TmpActiveEmployeesWSeverance a
cross apply
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID
Any thoughts as to what can be disrupting this?
Will the order of inner joins and cross apply effect the results of a query?
Example:
FROM dbo.vw_Info v
CROSS APPLY dbo.usf_LastFee(v.StoreID, v.AgreementID, v.CustomerID, ABS(v.PrePaymentBalance), cp.ConfirmationNo) lf
INNER JOIN dbo.Customers c
[Code] ....
I want to change the position of doing "CROSS APPLY". Will it effects query results?
I have this query:
SELECT res.res_id,
sub.value('(./@id)[1]', 'char(2)') id
FROM vwResult res
CROSS APPLY report.nodes('/clue_personal_auto/report/search_dataset/subjects/*') report(sub)
It works just fine in SQL Query.After placing this into a view in SSDT November 2012 update, I get a compilation error.
Hello,
I am doing a report that uses paging and in order to optimize it, i used row_number() so i could make it return x rows per page, so, in order to compute the number of pages needed, i have to count the total number of rows, which gets very slow because i'm using a cross apply with a table-valued function. Is there any way so i can get the number of rows processed by row_number() so i dont have the need to do count?
Thanks in advance !
Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you.
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
If I Have a table like
Id(identity), PupilPersonId, EducationTypeId,VehicleTypeId,EducationDate, EducatorId,Canceled
661187 9242382 2 1 2015-07-07 00:00:00.000 O_2 False
661183 9242382 2 1 2015-07-08 00:00:00.000 O_2 False
661186 9242382 1 1 2015-07-08 00:00:00.000 O_2 False
661178 9242382 2 1 2015-07-10 00:00:00.000 O_2 False
661185 9242382 2 1 2015-07-10 00:00:00.000 O_2 False
The result I want is the unique rows from columns:
PupilPersonId, EducationTypeId,VehicleTypeId AND there MAX EducationDate
SELECT er1.* FROM EducationResult er1
INNER JOIN
(
SELECT
er.PupilPersonId, er.EducationTypeId, er.VehicleTypeId, MAX(er.EducationDate) as EducationDate
[Code] ....
I like to know is there another approach with CTE and or Cross Apply I can use instead?
Table :
ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal
Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.
SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date
The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.
I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....
CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
[Code] ....
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2
CREATE TABLE #Suppliers
(ID INT, Product VARCHAR(500)
,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500)
,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL
[Code] .....
I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.
The error is: Incorrect syntax near '.'
the query in question resembles this:
Select column1, column2
from Table1 T
cross apply function(t.column4,t.column5) F
where column3 = 'XXXX'
I made sure that the compatibility level is greater than 90 this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)
Can it be the .net framework? Although the machines had .net framework 3.52.
Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2
I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
Hi,
I have two fields in one of my database table. They are date fields, one for the start and the other for the end of the "problem solving". What I want to do is to show the concrete elapsed time between the two.
My first problem is that I'm not able to show hour AND minutes. I wrote this for instance:
SELECT DATEDIFF(hour, fld_date_debut, fld_date_fin) As elapsed_time
I tried HH:mm instead of hour, but it is not working.
My second problem is that I can have more than one start and end hour for the same "problem solving". In the database it's like:
start hour: 10:00 End hour: 11:00 Number of the problem: 1
start hour: 13:00 End hour: 16:00 Number of the problem: 1
So I would like to add these to my elapsed time. I want (11:00 - 10:00) + (16:00 - 13:00)...but how can i do this in my SQL query ?
Thanks.
Dear SQLGuru,
I came to know one of the better option in Oracle SQL for TOP-N Analysis-To find the top 5 usage accounts, top 10 users, etc. Is there any such way in SQL server ?
Is there any ways of using JOINS ( using keyword INNER & OUTER ) ? Some of the scripts that is generated automatically in Microsoft access with Join condition is not even working in MSSQL server 6.5. Is it true ? Or is there any other way that I can use these scripts in SQL 6.5.
If possible, pls. help me with some sample scripts ?
Thanks a lot in advance.
Naga
How can I do a lookup which doens't directly link two columns but uses another statement?
I tried in advanced with:
Code Snippetselect * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)
and adding 2 parameters.
Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0
So I guess that's not the way to go. Any other way to tackle this?
Can someone put me on the right track?I have a .bak file from an MSSQL 7.0 database. I want to restore thedatabase in SQL 2000 or 2005, but the restore fails with the error:"An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.Express.ConnectionInfo)"and"I/O error (bad page ID) detected during read at offset 0x000000001be000 infile 'C:Program FilesMicrosoft SQL ServerMSSQLDataCLI_JP.mdf'. (.NetSqlClient Data Provider)"So it doesn't look good. I don't really know the meaning of the error andall I have is the .bak file. Is there a way to somehow convert the file, orread it?--Alvin
View 1 Replies View RelatedI have a bunch of contacts that I've scored how well their names match to other contacts in the same business. I can programmatically figure out how to parse the results, but would like to know how to do this via SQL. My problem is for Business_fk 968976 I have 7 contacts. In the end I should have 4 contacts based on name match. For the business key listed Gerardo Lopez is in the ContactScore table twice for Contact keys 7355719 and 57028145. I then have two rows like so:
PossibleBusinessContactMatch_pk BusinessContact_fk Business_fk BusinessContactMatch_fk MatchTypeCode MatchScore MatchRank FirstName LastName Phone Email
------------------------------- ------------------ ----------- ----------------------- ------------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
1772960 57028145 968976 7355719 C 46 1 GERARDO I LOPEZ 8162214000
838834 7355719 968976 57028145 C 50 1 GERARDO
Each reference each other, and 2 is a good case, a more difficult case would have key 1 listed 10 times showing a ContactMatch_fk of 2 - 11, and then Contact_fk 2 listed 10 times with a ContactMatch_fk of 1, 3-11.I know 57028145 maps to 7355719 from the first row in the ContactScore table, so when Contact_fk of 7355719 comes up I should be able to skip it and not process that match. Hopefully that makes sense. Anyway here is the test data:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContactScore]') AND type in (N'U'))
DROP TABLE [dbo].[ContactScore];
GO
CREATE TABLE [dbo].[ContactScore]
(
[ContactScore_pk]INT NOT NULL,
[Contact_fk]INT NOT NULL,
[code]..
In 7.0, an application development change has been made to defer name checking in
stored procedures until exection time. This allows a clean store of the proc in the system catalog
regardless of objects existing or not. At execution time, the proc is compiled, and object resolution is
done. Problem - the procedure fails if object names, column names, etc are wrong or don't exist.
So, during nightly cycle's the procedures bomb out.
Is there anyway to disable this 'deferred name resolution', or am I at the mercy of the developers?
Can I make the resolution immediate?
Thanks!
Dean
Friends
I have one SQL server2000 which have Mixed Mode authentication.It is working fine in PC which server instance is situating.But I have another PC which having connectvity tools of SQL Server.When I am trying to access SQL Server from the second PC through SQL authentication access getting denied.In the same time if I am accessing any of first PC's Folder using it's administrator password.Then if try with SQL authentication I am able to connect with SQL Server in the First PC from second PC .Please note SQL server instance is not in windows authentication mode .What may be the problem.Please suggest me a resolution
Thanks in Advance
Filson
I would like to be able to turn off the deferred name resolution feature in SQL2000 when compiling stored procedures. Is this possible?
Sidney
Hi guys,
I am trying to add data to 2 columns CaseResolvDatetime and CaseResolvGroupName based on the criteria:
1. for a CallID, the last datetime where Resolution='Resolved'
2. where there is no 'Resolved', the 2nd last datetime & groupname for a particular CallID
-----------------------------------------------
DECLARE @table TABLE(CallID CHAR(8), ResolvDatetime DATETIME, GroupName VARCHAR(15), Resolution VARCHAR(15), CaseResolvDatetime datetime, CaseResolvGroupName varchar (15))
INSERT @table(CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName)
SELECT '00458176','08/1/2005 1:41:54 PM','Field Sup CoOrd','Resolved',Null,Null UNION ALL
SELECT '00458176','08/9/2005 3:35:24 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/12/2005 9:54:37 AM','BSSAppSvcs-Club','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/16/2005 10:32:40 AM','CDR SysOps','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 11:18:30 AM','CDR Server','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 2:13:15 PM','Field Sup CoOrd','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 4:01:24 PM','CDR Server','Resolved',Null,Null UNION ALL
SELECT '00458176','08/25/2005 4:29:44 PM','Field Sup CoOrd','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/26/2005 2:59:19 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00458176','09/9/2005 10:42:44 AM','Field Sup - Vic','Completed',Null,Null UNION ALL
SELECT '00461166','09/7/2005 9:49:02 AM','BSSAppSvcs-Apps','Resolved',Null,Null UNION ALL
SELECT '00461166','09/7/2005 10:26:42 AM','Service Desk','Completed',Null,Null UNION ALL
SELECT '00461166','09/9/2005 10:19:13 AM','BSSAppSvcs-Apps','Reassigned',Null,Null UNION ALL
SELECT '00461166','09/15/2005 1:15:00 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00461166','09/15/2005 1:15:11 PM','Service Desk','Resolved',Null,Null UNION ALL
SELECT '00456633','08/08/2005 1:18:32 PM','BSSAppSvcs-Club','Completed',Null,Null UNION ALL
SELECT '00456633','08/26/2005 2:02:07 PM','Service Desk','Completed',Null,Null UNION ALL
SELECT '00456633','08/26/2005 3:31:36 PM','Service Desk','Completed',Null,Null
SELECT CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName FROM @table
UPDATE @table
SET CaseResolvDatetime =
CASE WHEN Resolution = 'Completed' AND CallId = (select CallId from @table where GroupName like 'Ser%' AND Resolution = 'Completed') then ResolvDatetime
ELSE NULL
END
SELECT CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName FROM @table
-------------------------------------------------
At the moment I get the error:
Server: Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Yet when I change the line:
"where GroupName like 'Ser%'" to "where GroupName like 'CDR%'"
... I get no error.
I am merging two databases and created two different schemas to contain the objects in the new DB. I would like the objects in both schemas to be visible to one user but I can't figure out how to make this happen. I thought I could just set the Owner of the schemas to the same User but that didn't work. At the moment, the User default schema is set to dbo. I would like the default to be both schemas but this isn't an option.
Is there any way to have multiple schema objects visible to a single user? Thanks!
Hello,
I'm running into problems with my replication where I get the following error:
Violation of PRIMARY KEY constraint
I know what the error means, and I know what is causing it. In my case, a property is being added to an inventory item independently at the publisher and subscriber end. Each available property has a particular ID, each inventory item has its own unique ID, and of course the properties per inventory item are stored in a linking table. I am using UUIDs for the inventory items, to avoid collisions in that aspect, but the list of properties is fixed (currently only 15 - 20 available properties), so it doesn't make sense to me to have managed ranges, UUIDs, or other such things for the properties. Of course, I could apply a "source ID" to each added property to avoid these collisions, but I'd prefer not having to redesign the database, not to mention deal with the extraneous copies of properties.
My preference would be to simply have the server delete the copy on the server and take the subscriber row. I would have thought that using the "subscriber always wins" conflict resolver would have this effect, but it doesn't work for me. Is there a straightforward way of dealing with this problem? Am I missing something obvious? I've looked into a custom conflict resolver, but that seems like overkill for what must be a fairly common scenario.
For the record, the publisher in my case is SQL Server 2005, and the subscribers are SQL Server Mobile clients.
Any advice would be greatly appreciated!
Thanks,
Adrien.
I am using Allwayson to replicate a FCI to a remote site. (windows 2012R2) Although all is implemented, iam getting a strange error with the listener resources after ive made a failover test.
It keeps saying "Name resolution not yet available"from my understanding this is trying to update an entry that doesn't exist (since the user has permissions to do it).