I thought I could force data to be sent on the next merge by doing a simple
update statement on the rows I need sent.
Ran update statement at the subscriber
Update MyTable
SET A_Column = A_Column
This doesn't seem to work for rows that don't already exist on the
publisher.
Is there a system stored procedure that I can use at the subscriber to force
some data to the publisher?
Tina,
sp_mergedummyupdate should do it for you.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||The system stored procedure doesn't seem to force the rows to be sent
either.
I wrote the following since I had several hundred rows to force. I ran
the agent afer running the script but still no rows were inserted.
DECLARE @.guid uniqueidentifier
DECLARE @.SQL nvarchar(200)
DECLARE list_procs CURSOR FOR
SELECT row_guid FROM Ticket
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.guid
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'sp_mergedummyupdate @.source_object = ''Ticket''' +
',@.rowguid=''{' + CONVERT( varchar(36), @.guid ) + '}'''
--Print @.SQL
EXEC sp_executesql @.sql
FETCH NEXT FROM list_procs INTO @.guid
END
CLOSE list_procs
DEALLOCATE list_procs
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u$GZGwisEHA.904@.TK2MSFTNGP11.phx.gbl...
> Tina,
> sp_mergedummyupdate should do it for you.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Tina,
can you check (based on rowguid) to see if the records have entered
MSmerge_contents. Also, I don't want to state the obvious, but did you run
the merge agent afterwards?
Rgds,
Paul ibison (SQL Server MVP)
[vbcol=seagreen]
|||I figured out my problem. I found that the rowguids were in my
msmerge_tombstone table. Since I was using a push, the publisher could
never delete them from the subscriber but replication wasn't letting the
subscriber send these as new rows either.
Thanks for your time.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oy$1XUFtEHA.3052@.tk2msftngp13.phx.gbl...
> Tina,
> can you check (based on rowguid) to see if the records have entered
> MSmerge_contents. Also, I don't want to state the obvious, but did you run
> the merge agent afterwards?
> Rgds,
> Paul ibison (SQL Server MVP)
>
>
Showing posts with label ran. Show all posts
Showing posts with label ran. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
For xml auto question:
Hi,
I ran the two queries below on SQL server 2005 enterprise 9.00.1399.06 and 9.00.2047.00. The first ("Top" paginated) query ran fine (from my C# code based dataset) on the older version, but returns results like those below (See Result1) from the newer version. I need to get my data back with the xml parent child nesting intact and table handles as they are designated in the main query text. Perhaps there is another way to do a paginated query that will deliver xml nested as shown in Result2. If so I would like to know how to code it.
If you have any suggestions, I would appreciate any help you can give. Thanks, Dave
Query 1 (Paginated using Top):
select niin, item_name, cage, partno, vendorname, ui, price from (select top 10 niin, item_name, cage, partno, vendorname, ui, price from (select top 30 flis_a.niin,flis_a.item_name, flis_cage.cage,flis_cage.partno,flis_cage.vendorname, pricing.ui,pricing.price from dbFlisCurPlusHist..niindata AS flis_a left outer join dbFlisCurPlusHist..mcrldata AS flis_cage on flis_a.niin = flis_cage.niin left outer join dbFlisCurPlusHist..mlcdata AS pricing on flis_a.niin = pricing.niin where flis_a.niin like '0000%' and flis_a.item_name like '%BOLT%' and flis_cage.cage like '%205%' and pricing.sa ='DN' order by flis_a.niin, flis_cage.niin, pricing.niin) as newtbl order by niin desc) as newtbl2 order by niin asc for xml auto
Query 2:
select flis_a.niin,flis_a.item_name, flis_cage.cage,flis_cage.partno,flis_cage.vendorname, pricing.ui,pricing.price from dbFlisCurPlusHist..niindata AS flis_a left outer join dbFlisCurPlusHist..mcrldata AS flis_cage on flis_a.niin = flis_cage.niin left outer join dbFlisCurPlusHist..mlcdata AS pricing on flis_a.niin = pricing.niin where flis_a.niin like '0000%' and flis_a.item_name like '%BOLT%' and flis_cage.cage like '%205%' and pricing.sa ='DN' order by flis_a.niin, flis_cage.niin, pricing.niin asc for xml auto
Result 1: (Results not nested as they need to be. This ran fine on older version from c# dataset but now fails on the newer sql server)
<newtbl2 niin="000041534" item_name="BOLT,MACHINE" cage="80205" partno="AN7-24A" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.68" />
<newtbl2 niin="000041535" item_name="BOLT,SHEAR" cage="80205" partno="NAS1303-12" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.23" />
<newtbl2 niin="000045155" item_name="BOLT,MACHINE" cage="80205" partno="AN17-40A" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000009.81" />
<newtbl2 niin="000050435" item_name="BOLT,SHEAR" cage="80205" partno="NAS627H14" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000009.03" />
<newtbl2 niin="000050546" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-5-35" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000012.94" />
<newtbl2 niin="000050549" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-6-19" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000015.52" />
<newtbl2 niin="000050550" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-6-23" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000020.73" />
<newtbl2 niin="000056093" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-4-27" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000011.12" />
<newtbl2 niin="000061454" item_name="BOLT,INTERNAL WRENCHING" cage="80205" partno="NAS1351-4H12" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.25" />
<newtbl2 niin="000062269" item_name="BOLT,SQUARE NECK" cage="80205" partno="MS35751-53" vendorname="NATIONAL AEROSPACE STANDARDS" ui="BX" price="000000005.61" />
-
The above response should look like this with child elements nested, etc and the flis_a table handle intact. The response below ran fine on SQL Server 2005 Enterprise version 9.00.1399.06.
<flis_a niin="000041534" item_name="BOLT,MACHINE"><flis_cage cage="80205" partno="AN7-24A" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000000.68"/></flis_cage></flis_a><flis_a niin="000041535" item_name="BOLT,SHEAR"><flis_cage cage="80205" partno="NAS1303-12" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000000.23"/></flis_cage></flis_a><flis_a niin="000045155" item_name="BOLT,MACHINE"><flis_cage cage="80205" partno="AN17-40A" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000009.81"/></flis_cage></flis_a><flis_a niin="000050435" item_name="BOLT,SHEAR"><flis_cage cage="80205" partno="NAS627H14" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000009.03"/></flis_cage></flis_a><flis_a niin="000050546" item_name="BOLT,CLOSE TOLERANCE"><flis_cag ...
--
Result 2: (Non paginated and works fine on both versions)
<flis_a niin="000011989" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS1308-29" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000008.65" />
</flis_cage>
</flis_a>
<flis_a niin="000011993" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS464P8-79" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000030.46" />
</flis_cage>
</flis_a>
<flis_a niin="000014780" item_name="BOLT,INTERNAL WRENCHING">
<flis_cage cage="80205" partno="NAS1352-06LE8" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000003.72" />
</flis_cage>
</flis_a>
<flis_a niin="000014807" item_name="BOLT,CLOSE TOLERANCE">
<flis_cage cage="80205" partno="MS27576-5-22" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000013.93" />
</flis_cage>
</flis_a>
<flis_a niin="000014847" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="MS21250-03020" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000001.02" />
</flis_cage>
</flis_a>
<flis_a niin="000014899" item_name="BOLT,MACHINE">
<flis_cage cage="80205" partno="NAS428-3-15" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000000.86" />
</flis_cage>
</flis_a>
<flis_a niin="000016674" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS464P5LA33" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000002.86" />
</flis_cage>
</flis_a>
.
.
.
Subqueries in the from clause are now treated like views (as they should be) and become opaque for auto mode queries.
You may want to run the queries under compat level 80 (sp_dbcmptlevel 'dbname', 80) if you want the SQL Server 2000 behaviour or rewrite your queries using FOR XML PATH.
Also, it would help if you could provide a schema definition and some sample data to repro the behaviour.
Best regards
Michael
For xml auto question:
Hi,
I ran the two queries below on SQL server 2005 enterprise 9.00.1399.06 and 9.00.2047.00. The first ("Top" paginated) query ran fine (from my C# code based dataset) on the older version, but returns results like those below (See Result1) from the newer version. I need to get my data back with the xml parent child nesting intact and table handles as they are designated in the main query text. Perhaps there is another way to do a paginated query that will deliver xml nested as shown in Result2. If so I would like to know how to code it.
If you have any suggestions, I would appreciate any help you can give. Thanks, Dave
Query 1 (Paginated using Top):
select niin, item_name, cage, partno, vendorname, ui, price from (select top 10 niin, item_name, cage, partno, vendorname, ui, price from (select top 30 flis_a.niin,flis_a.item_name, flis_cage.cage,flis_cage.partno,flis_cage.vendorname, pricing.ui,pricing.price from dbFlisCurPlusHist..niindata AS flis_a left outer join dbFlisCurPlusHist..mcrldata AS flis_cage on flis_a.niin = flis_cage.niin left outer join dbFlisCurPlusHist..mlcdata AS pricing on flis_a.niin = pricing.niin where flis_a.niin like '0000%' and flis_a.item_name like '%BOLT%' and flis_cage.cage like '%205%' and pricing.sa ='DN' order by flis_a.niin, flis_cage.niin, pricing.niin) as newtbl order by niin desc) as newtbl2 order by niin asc for xml auto
Query 2:
select flis_a.niin,flis_a.item_name, flis_cage.cage,flis_cage.partno,flis_cage.vendorname, pricing.ui,pricing.price from dbFlisCurPlusHist..niindata AS flis_a left outer join dbFlisCurPlusHist..mcrldata AS flis_cage on flis_a.niin = flis_cage.niin left outer join dbFlisCurPlusHist..mlcdata AS pricing on flis_a.niin = pricing.niin where flis_a.niin like '0000%' and flis_a.item_name like '%BOLT%' and flis_cage.cage like '%205%' and pricing.sa ='DN' order by flis_a.niin, flis_cage.niin, pricing.niin asc for xml auto
Result 1: (Results not nested as they need to be. This ran fine on older version from c# dataset but now fails on the newer sql server)
<newtbl2 niin="000041534" item_name="BOLT,MACHINE" cage="80205" partno="AN7-24A" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.68" />
<newtbl2 niin="000041535" item_name="BOLT,SHEAR" cage="80205" partno="NAS1303-12" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.23" />
<newtbl2 niin="000045155" item_name="BOLT,MACHINE" cage="80205" partno="AN17-40A" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000009.81" />
<newtbl2 niin="000050435" item_name="BOLT,SHEAR" cage="80205" partno="NAS627H14" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000009.03" />
<newtbl2 niin="000050546" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-5-35" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000012.94" />
<newtbl2 niin="000050549" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-6-19" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000015.52" />
<newtbl2 niin="000050550" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-6-23" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000020.73" />
<newtbl2 niin="000056093" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-4-27" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000011.12" />
<newtbl2 niin="000061454" item_name="BOLT,INTERNAL WRENCHING" cage="80205" partno="NAS1351-4H12" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.25" />
<newtbl2 niin="000062269" item_name="BOLT,SQUARE NECK" cage="80205" partno="MS35751-53" vendorname="NATIONAL AEROSPACE STANDARDS" ui="BX" price="000000005.61" />
-
The above response should look like this with child elements nested, etc and the flis_a table handle intact. The response below ran fine on SQL Server 2005 Enterprise version 9.00.1399.06.
<flis_a niin="000041534" item_name="BOLT,MACHINE"><flis_cage cage="80205" partno="AN7-24A" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000000.68"/></flis_cage></flis_a><flis_a niin="000041535" item_name="BOLT,SHEAR"><flis_cage cage="80205" partno="NAS1303-12" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000000.23"/></flis_cage></flis_a><flis_a niin="000045155" item_name="BOLT,MACHINE"><flis_cage cage="80205" partno="AN17-40A" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000009.81"/></flis_cage></flis_a><flis_a niin="000050435" item_name="BOLT,SHEAR"><flis_cage cage="80205" partno="NAS627H14" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000009.03"/></flis_cage></flis_a><flis_a niin="000050546" item_name="BOLT,CLOSE TOLERANCE"><flis_cag ...
--
Result 2: (Non paginated and works fine on both versions)
<flis_a niin="000011989" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS1308-29" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000008.65" />
</flis_cage>
</flis_a>
<flis_a niin="000011993" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS464P8-79" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000030.46" />
</flis_cage>
</flis_a>
<flis_a niin="000014780" item_name="BOLT,INTERNAL WRENCHING">
<flis_cage cage="80205" partno="NAS1352-06LE8" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000003.72" />
</flis_cage>
</flis_a>
<flis_a niin="000014807" item_name="BOLT,CLOSE TOLERANCE">
<flis_cage cage="80205" partno="MS27576-5-22" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000013.93" />
</flis_cage>
</flis_a>
<flis_a niin="000014847" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="MS21250-03020" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000001.02" />
</flis_cage>
</flis_a>
<flis_a niin="000014899" item_name="BOLT,MACHINE">
<flis_cage cage="80205" partno="NAS428-3-15" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000000.86" />
</flis_cage>
</flis_a>
<flis_a niin="000016674" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS464P5LA33" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000002.86" />
</flis_cage>
</flis_a>
.
.
.
Subqueries in the from clause are now treated like views (as they should be) and become opaque for auto mode queries.
You may want to run the queries under compat level 80 (sp_dbcmptlevel 'dbname', 80) if you want the SQL Server 2000 behaviour or rewrite your queries using FOR XML PATH.
Also, it would help if you could provide a schema definition and some sample data to repro the behaviour.
Best regards
Michael
Subscribe to:
Posts (Atom)