Forcing resynchronization

I am doing testing on a database between a SQL Server and a laptop
usingmerge replication. I can see that each system is missing a record
in each system. When i run the validation process the system
identifies that the table are not in sync. Regardless of the
resynchronization method I use the two tables do not synchronize. What
am I doing wrong
This sort of thing can occur if the merge trigger hasn't fired. EG
(1) If you bulk insert the rows and choose the defaults, then FIRE_TRIGGERS
is false and consequently the rows are not added to MSmerge_contents.
(2) If you do a fast-load using the Transform Data task in DTS.
In these cases case, you need to run sp_addtabletocontents to include the
rows then resynchronise. Alternatively you can use sp_mergedummyupdate for a
single row. For the fast load case, in future if you deselect the check box
the triggers will fire.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

force close connections

hello!
is there any way to force the server to close all existing USER connections?
i mean, not the system connections or the sql server agent connections, only
the user ones.
Thanks!!!!Not directly. Is this for a certain database? If so, perhaps setting database option restricted user
might help. Also, when you set a database option, you have a rollback option, which will kick out
existing users.
If above doesn't suit you, you could always write a cursor which loops something like sysprocesses
and uses dynamic SQL to KILL the spids you don't want to keep.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:EAF6BCAC-A658-43B9-AEF7-C16423C43210@.microsoft.com...
> hello!
> is there any way to force the server to close all existing USER connections?
> i mean, not the system connections or the sql server agent connections, only
> the user ones.
> Thanks!!!!|||On Apr 16, 7:03=A0am, DarthSidious
<DarthSidi...@.discussions.microsoft.com> wrote:
> hello!
> is there any way to force the server to close all existing USER connection=s?
> i mean, not the system connections or the sql server agent connections, on=ly
> the user ones.
> Thanks!!!!
Here is what I use - just pass the database name and run in master:
CREATE PROCEDURE [dbo].[utl_KillUsers] @.dbname varchar(50) as
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30),
request_id int )
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname =3D @.dbname
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL =3D 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers|||In sql 2005 you can also set a database to single user mode and use the WITH
ROLLBACK IMMEDIATE flag. See ALTER DATABASE in BOL. If you wanted to
disconnect ALL user connections you would need to do this for every user
database.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:EAF6BCAC-A658-43B9-AEF7-C16423C43210@.microsoft.com...
> hello!
> is there any way to force the server to close all existing USER
> connections?
> i mean, not the system connections or the sql server agent connections,
> only
> the user ones.
> Thanks!!!!

force close connections

hello!
is there any way to force the server to close all existing USER connections?
i mean, not the system connections or the sql server agent connections, only
the user ones.
Thanks!!!!
Not directly. Is this for a certain database? If so, perhaps setting database option restricted user
might help. Also, when you set a database option, you have a rollback option, which will kick out
existing users.
If above doesn't suit you, you could always write a cursor which loops something like sysprocesses
and uses dynamic SQL to KILL the spids you don't want to keep.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:EAF6BCAC-A658-43B9-AEF7-C16423C43210@.microsoft.com...
> hello!
> is there any way to force the server to close all existing USER connections?
> i mean, not the system connections or the sql server agent connections, only
> the user ones.
> Thanks!!!!
|||On Apr 16, 7:03Xam, DarthSidious
<DarthSidi...@.discussions.microsoft.com> wrote:
> hello!
> is there any way to force the server to close all existing USER connections?
> i mean, not the system connections or the sql server agent connections, only
> the user ones.
> Thanks!!!!
Here is what I use - just pass the database name and run in master:
CREATE PROCEDURE [dbo].[utl_KillUsers] @.dbname varchar(50) as
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30),
request_id int )
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @.dbname
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
|||In sql 2005 you can also set a database to single user mode and use the WITH
ROLLBACK IMMEDIATE flag. See ALTER DATABASE in BOL. If you wanted to
disconnect ALL user connections you would need to do this for every user
database.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:EAF6BCAC-A658-43B9-AEF7-C16423C43210@.microsoft.com...
> hello!
> is there any way to force the server to close all existing USER
> connections?
> i mean, not the system connections or the sql server agent connections,
> only
> the user ones.
> Thanks!!!!

Force a round() when it's below a 5?

I have a sales tax function in my antiquated system. Someone decided to
play it safe and over collect on sales tax.
If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
system shows 10.42
How do I force the round up for reporting to Auditors when they want to
query random samples of data?
TIA
__StephenTry:
select ceiling (10.4115 * 100) / 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>I have a sales tax function in my antiquated system. Someone decided to
>play it safe and over collect on sales tax.
> If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
> system shows 10.42
> How do I force the round up for reporting to Auditors when they want to
> query random samples of data?
> TIA
> __Stephen
>|||Lookup CEILING in Books Online.
ML
http://milambda.blogspot.com/|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Try:
> select ceiling (10.4115 * 100) / 100
Thanks. With your credentials I'll give it a whirl. :)
Will this work within a SUM() when I'm grouping by State, Client, City.
Some clients have different contracts with us and we compute tax on where
the HQ of company is that signed the contract, and not where the recipient
is located.
I'm processing 250,000 + detail rows a month creating a final result of 110
rows summarized today.|||Sure. I assume that this round-up has to happen on each line item before it
is summed. You can make it conditional, too. For example:
select
sum (case when State in ('CA', 'TX') then ceiling (Tax * 100) / 100 else
Tax end)
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:OaaHuYn%23FHA.208@.tk2msftngp13.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Thanks. With your credentials I'll give it a whirl. :)
> Will this work within a SUM() when I'm grouping by State, Client, City.
> Some clients have different contracts with us and we compute tax on where
> the HQ of company is that signed the contract, and not where the recipient
> is located.
> I'm processing 250,000 + detail rows a month creating a final result of
> 110 rows summarized today.
>
>|||Back in the dark ages, we rounded by adding and truncating. For
example, to always round up with 2 decimal places,
SELECT cast((9.53 * 1.0925 + .009999) * 100 as integer) / 100.
Not sure its any simpler.
Good luck.
Payson
Tom Moreau wrote:
> Try:
> select ceiling (10.4115 * 100) / 100
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...

FOR XML EXPLICIT

Hello.
I'm trying to list two columns and count one from it.
Here's my code:
SELECT Caption0 as 'Operating System', CSDVersion0 , COUNT(CSDVersion0) as
'Number'
FROM v_GS_OPERATING_SYSTEM
GROUP BY Caption0, CSDVersion0
ORDER BY Caption0, CSDVersion0
FOR XML EXPLICIT
I have a message, that I FOR XML EXPLICIT requiers the first column to
hold positive integers that represent XML tags IDs.
I know what it means, but i'm nOOb. What i must do? I think I looked by
google everywhere...
Legrooch
You may want to look at the articles linked from
http://blogs.msdn.com/mrys/archive/2...09/151523.aspx
While they talk about the new PATH mode, they contain several FOR XML
EXPLICIT examples.
Feel free to come back if that is not enough information.
Best regards
Michael
"Legrooch" <leszek.gruszka@.kana.com.pl> wrote in message
news:opsavdy3l2732xkw@.k3-xp-lg.k3.win...
> Hello.
> I'm trying to list two columns and count one from it.
> Here's my code:
> SELECT Caption0 as 'Operating System', CSDVersion0 , COUNT(CSDVersion0) as
> 'Number'
> FROM v_GS_OPERATING_SYSTEM
> GROUP BY Caption0, CSDVersion0
> ORDER BY Caption0, CSDVersion0
> FOR XML EXPLICIT
> I have a message, that I FOR XML EXPLICIT requiers the first column to
> hold positive integers that represent XML tags IDs.
> I know what it means, but i'm nOOb. What i must do? I think I looked by
> google everywhere...
> --
> Legrooch

FOR XML AUTO returns incomplete xml

I have two SQL tables that are populated based on data in SQL system tables.
When I run the FOR XML AUTO select statement on these tables, certain fields
will be missing end tags AND the data will not all be returned. In some
cases the end tags are there but the data is incomplete. When running the
query against the actual system table I'll get similar results but not
exactly the same. Any suggestions?
Using sql 2000 sp3. SQLXML 3 sp2
--master..sysaltfiles table data. Return only a few db's and an incomplete
select rtrim(filename) filename from mridiag..tbldbfiles for xml auto
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Data.MDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Log.LDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\master.mdf
NT\data\pubs_log.ldf
(24 row(s) affected)
--master..sysprocesses table. Only returns 1 incomplete record
select LASTWAITTYPE test from master..sysprocesses for xml auto
<master..sysprocesses test="SLEEP
(15 row(s) affected)
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
>I have two SQL tables that are populated based on data in SQL system
>tables.
> When I run the FOR XML AUTO select statement on these tables, certain
> fields
> will be missing end tags AND the data will not all be returned. In some
> cases the end tags are there but the data is incomplete. When running the
> query against the actual system table I'll get similar results but not
> exactly the same. Any suggestions?
What client are you using to retrieve the results?
You might also check this FAQ:
http://sqlxml.org/faqs.aspx?faq=76
Bryant
|||Thanks for the response.
I reviewed the link to the FAQ and compared it to how I'm doing it. First,
the information I posted was using Query Analyzer but I get the same results
when executing it from my vb script.
I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
link I based my code on is below. It's basically the same as the "VB
Example" on the faq you pointed me to but the version of XML on the FAQ is
3.0 and the version used in my script is 4.0. Other than that, I can't see
any differences.
Any more suggestions or questions? It really has me puzzled.
Thanks again.
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||Here is the link I mentioned.
http://www.sqlxml.org/faqs.aspx?faq=10
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||The query analyzer is using ODBC and not the OLEDB stream object and thus
only get junked XML back. Also, unless you increase the number of bytes
displayed per line, it does drop information.
If you are using the SQLOLEDB stream interface, you should get the XML back.
Can you try it with the SQLXML HTTP component to see if the XML is correctly
generated by the FOR XML query?
Thanks
Michael
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:9679959F-BC15-48CC-B4F9-7B521D883DCC@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> I reviewed the link to the FAQ and compared it to how I'm doing it.
> First,
> the information I posted was using Query Analyzer but I get the same
> results
> when executing it from my vb script.
> I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
> link I based my code on is below. It's basically the same as the "VB
> Example" on the faq you pointed me to but the version of XML on the FAQ is
> 3.0 and the version used in my script is 4.0. Other than that, I can't
> see
> any differences.
> Any more suggestions or questions? It really has me puzzled.
> Thanks again.
> "Bryant Likes" wrote: