hi
well I am working on a project that our database has 2, 8000 record table that they have triggers on the update of their feilds
our application must update these tables but it takes a long time to do this
I know there is something named bulk insert but I couldn't find sth similar to this command for update
so would you please help me to find a faster way to update these tables?
thanks for your attention
Best Regards
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
hi,
netman Mo wrote:
> hi
> well I am working on a project that our database has 2, 8000 record
> table that they have triggers on the update of their feilds our
> application must update these tables but it takes a long time to do
> this
> I know there is something named bulk insert but I couldn't find sth
> similar to this command for update
> so would you please help me to find a faster way to update these
> tables?
nope.. update syntax is not overloaded with bulk operators..
if the cause of your delay is dependent on the trigger fired by the update
statement, you should perhaps check it's code... or... if you are sure the
updates you are performing do not involve the trigger check, you can disable
it before executing the statements..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Tuesday, March 27, 2012
A difficult Combining Rows problem
Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in one hour chunks, like below:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
This involves several things:
- Recognizing that there are variable # of rows (maybe we only have 3
15 minute entries instead of 4)
- Getting a min of those row's min column
- Getting a max of those row's max column
- Getting a total for those row's points column
- Input to any view or whatver would be based on the startTime and
endTime and would always be in whole hours.
I have a feeling that I am going to be doing this all in the C# .NET
end of things, but it's at least worth a shot asking all of you SQL
experts. What I am basically interested in knowing is, do you all
think that this is possible using views or stored procedures or
something else I don't know about. I didn't even know about views
until i started researching how to do this.
Any ideas? Is this possible? Should I just give up and do it on the
C# end of things? Seems to me that it might be possible to do in a
stored procedure, but possible not worth my time. I aprpeciate any
help or suggestions.
JasonTry this:
SELECT groupid,
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'20050101')),
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'2005-01-01T00:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(HH,'20050101',starttime) ;
David Portas
SQL Server MVP
--|||Hi
Check out the dateadd/datepart functions in Books Online for rounding times.
Try:
SELECT GROUPID, DATEADD(mi,-DATEPART(mi,Starttime),Starttime) AS StartTime,
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(mi,-DATEPART(mi,Starttime),Starttime),
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
John
"Factor" wrote:
> Greetings,
> I'm working to combine rows based on a time window and I am hoping to
> be able to write a stored procedure to do this for me, rather than have
> parse through all this data in my program. I'm not very well versed
> with T-SQL syntax.. just enough to get by selecting using inner joins,
> updating and inserting... thats about it. (Hence why I am here.)
> The raw data I have below looks like this:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
> 1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
> 1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
> 1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
> 1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
> 1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
> 1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
> 1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
> Pretty straight forward; you can see each entry is a 15 minute time
> interval. What I want to be able to do is to use a view or a stored
> procedure to view this in one hour chunks, like below:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
> 1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
> This involves several things:
> - Recognizing that there are variable # of rows (maybe we only have 3
> 15 minute entries instead of 4)
> - Getting a min of those row's min column
> - Getting a max of those row's max column
> - Getting a total for those row's points column
> - Input to any view or whatver would be based on the startTime and
> endTime and would always be in whole hours.
> I have a feeling that I am going to be doing this all in the C# .NET
> end of things, but it's at least worth a shot asking all of you SQL
> experts. What I am basically interested in knowing is, do you all
> think that this is possible using views or stored procedures or
> something else I don't know about. I didn't even know about views
> until i started researching how to do this.
> Any ideas? Is this possible? Should I just give up and do it on the
> C# end of things? Seems to me that it might be possible to do in a
> stored procedure, but possible not worth my time. I aprpeciate any
> help or suggestions.
> Jason
>|||John Bell and David Portas,
I will have to read up on these Dateadd/DatePart parameters an actually
interpret what is going on within these statements, but just from what
you gave me here it looks like this will work out very well, and I
really appreciate the insight. This will allow me to vary that time
window fairly easily I do believe, all on a SQL call (that's much
better than bringing back all the data and parsing through it all it.
Thanks again,
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still
as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||On 10 Nov 2005 11:41:54 -0800, Factor wrote:
>John
>I have read over those functions and I now understand what they do and
>how to use them, but I am still as to why the min / max /
>total fields actually work. I assume it has something to do with the
>GROUP BY statements, but again, I don't know why.
Hi Jason,
Correct. The GROUP BY tells SQL Server to combine the data from several
rows into one row. This is normally used to report totals, minimum,
maximum per project, per section, etc. But with the appropriate
expression, it cal also be used to combine rows that fit in the same
"period" into one group.
Though John's and David's versions both work, I suggest you go with
Davids version, as this is more flexible. (And, once you get your head
around it, easier to understand as well).
Basically, John's version works by taking each of the date parts you
want to disregard (milliseconds, seconds, minutes), then subtracting
that amount of time from the Starttime. The end result will of course be
the last full hour equal to or before Starttime.
David's version works the other way around - it calculates the number of
full hours that have elapsed since a chosen anchor date, then adds that
number to the chosen anchor date. The result will be the same as John's
expression.
(Note: David chose to just use the number of hours for the group by, and
add it back to the anchor date in the SELECT clause only)
>Assuming black magic happens and thats just how it works, I should just
>be able to change those hh,1 to hh,4 and get 4 hour increments instead.
No. I'll give you two examples how to modify David's query to report on
4-hour intervals and to report on 1/2-hour intervals.
For 4-hour intervals, again calculate the number of hours since an
anchor date. Divide by 4 and truncate, then multiply by 4 again. Add
this number of hours to the anchor date. There you have the start of the
last 4-hour interval
SELECT groupid,
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'20050101')),
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'2005-01-01T03:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
For 1/2-hour intervals, we can't divide the number of hours sice the
anchor date by 0.5, as that won't give us back the precision we already
lost. Instead, we'll have to calculate minutes and divide by 30:
SELECT groupid,
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'20050101')),
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'2005-01-01T00:29:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
In both cases, don't forget to change the shifted anchor value in the
expression for the end point of the interval. Instead of using the same
anchor date, then adding 30 minunte or 4 hours minus one second, the
anchor date is shifted by 30 minutes or 4 hours minus one second.
Now, the above code can still be simplified further. If your table
always has the complete data (as your sample roiws indicate), then you
could change the above queries to:
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
-- or: GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
Note that this might show "holes" in the periods if your real data is
not as complete as the sample you posted indicates. But the advantage is
that you get rid of the "shifted" anchor date for calculating end time.
Final step would be to put it in a stored procedure and use a parameter
for the interval length (in minutes):
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) / @.Interval;
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jason,
What David Provided is an Excellent query .
Let us see if this query can help you.
Select GID , Min(STime) , Max(ETime) ,
Min(Minimum),Max(Maximum),Sum(Points)[co
lor=darkred]
>From yourTableName Group By[/color]
GID,Convert(varchar,STime,112),DatePart(
hh,STime)
Having same name as Functions/ Keyword sound confusing to me so I
changed them.
With Warm Regards
Jatinder Singh|||Hi
This is easier with David's method (see Hugo's reply for an explanation).
Dividing the number of hours by 4 and dropping the remainder will give you 4
hour chunks when they are multiplied back up. You also need to change the en
d
time to give a 4 hour gap.
SELECT groupid,
MIN(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'20050101')
) AS Starttime,
MAX(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'2005-01-01T03:59:59')
) AS Endtime,
MIN(min) AS [Min],
MAX(max) AS [Max],
SUM(points) AS [Total Points]
FROM Readings
GROUP BY groupid,
4*(DATEDIFF(HH,'20050101',starttime)/4)
John
"Factor" wrote:
> John
> I have read over those functions and I now understand what they do and
> how to use them, but I am still as to why the min / max /
> total fields actually work. I assume it has something to do with the
> GROUP BY statements, but again, I don't know why.
> Assuming black magic happens and thats just how it works, I should just
> be able to change those hh,1 to hh,4 and get 4 hour increments instead.
> When I do that, the Starttime and Endtime values do return correctly
> (although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
> the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
> that black magic that is limiting the MIN/MAX/SUM to one hour is still
> limiting them to one hour even with the altered start and end times.
> I'm unsure how to fix or get around this because I don't yet understand
> what is limiting that max to an hour in the first place. How does this
> work? I've been tripped up GROUP BY things before, it's my kryptonite
> for some reason.
> Hope that is not too confusing, I'm all jumbled in my head.
> I really apprecaite the help with this so far, you've all been
> wonderful.
> Jason
>|||Wondeful! Lots ot take in, I thank everyone for their help. I've made
a lot of progress and I've learned a TON about SQL int he past two
days.
I hope I can help you all in the future with something!
Thanks again,
Jason
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in one hour chunks, like below:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
This involves several things:
- Recognizing that there are variable # of rows (maybe we only have 3
15 minute entries instead of 4)
- Getting a min of those row's min column
- Getting a max of those row's max column
- Getting a total for those row's points column
- Input to any view or whatver would be based on the startTime and
endTime and would always be in whole hours.
I have a feeling that I am going to be doing this all in the C# .NET
end of things, but it's at least worth a shot asking all of you SQL
experts. What I am basically interested in knowing is, do you all
think that this is possible using views or stored procedures or
something else I don't know about. I didn't even know about views
until i started researching how to do this.
Any ideas? Is this possible? Should I just give up and do it on the
C# end of things? Seems to me that it might be possible to do in a
stored procedure, but possible not worth my time. I aprpeciate any
help or suggestions.
JasonTry this:
SELECT groupid,
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'20050101')),
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'2005-01-01T00:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(HH,'20050101',starttime) ;
David Portas
SQL Server MVP
--|||Hi
Check out the dateadd/datepart functions in Books Online for rounding times.
Try:
SELECT GROUPID, DATEADD(mi,-DATEPART(mi,Starttime),Starttime) AS StartTime,
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(mi,-DATEPART(mi,Starttime),Starttime),
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
John
"Factor" wrote:
> Greetings,
> I'm working to combine rows based on a time window and I am hoping to
> be able to write a stored procedure to do this for me, rather than have
> parse through all this data in my program. I'm not very well versed
> with T-SQL syntax.. just enough to get by selecting using inner joins,
> updating and inserting... thats about it. (Hence why I am here.)
> The raw data I have below looks like this:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
> 1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
> 1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
> 1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
> 1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
> 1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
> 1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
> 1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
> Pretty straight forward; you can see each entry is a 15 minute time
> interval. What I want to be able to do is to use a view or a stored
> procedure to view this in one hour chunks, like below:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
> 1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
> This involves several things:
> - Recognizing that there are variable # of rows (maybe we only have 3
> 15 minute entries instead of 4)
> - Getting a min of those row's min column
> - Getting a max of those row's max column
> - Getting a total for those row's points column
> - Input to any view or whatver would be based on the startTime and
> endTime and would always be in whole hours.
> I have a feeling that I am going to be doing this all in the C# .NET
> end of things, but it's at least worth a shot asking all of you SQL
> experts. What I am basically interested in knowing is, do you all
> think that this is possible using views or stored procedures or
> something else I don't know about. I didn't even know about views
> until i started researching how to do this.
> Any ideas? Is this possible? Should I just give up and do it on the
> C# end of things? Seems to me that it might be possible to do in a
> stored procedure, but possible not worth my time. I aprpeciate any
> help or suggestions.
> Jason
>|||John Bell and David Portas,
I will have to read up on these Dateadd/DatePart parameters an actually
interpret what is going on within these statements, but just from what
you gave me here it looks like this will work out very well, and I
really appreciate the insight. This will allow me to vary that time
window fairly easily I do believe, all on a SQL call (that's much
better than bringing back all the data and parsing through it all it.
Thanks again,
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still
as to why the min / max /total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still
as to why the min / max /total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||On 10 Nov 2005 11:41:54 -0800, Factor wrote:
>John
>I have read over those functions and I now understand what they do and
>how to use them, but I am still
as to why the min / max />total fields actually work. I assume it has something to do with the
>GROUP BY statements, but again, I don't know why.
Hi Jason,
Correct. The GROUP BY tells SQL Server to combine the data from several
rows into one row. This is normally used to report totals, minimum,
maximum per project, per section, etc. But with the appropriate
expression, it cal also be used to combine rows that fit in the same
"period" into one group.
Though John's and David's versions both work, I suggest you go with
Davids version, as this is more flexible. (And, once you get your head
around it, easier to understand as well).
Basically, John's version works by taking each of the date parts you
want to disregard (milliseconds, seconds, minutes), then subtracting
that amount of time from the Starttime. The end result will of course be
the last full hour equal to or before Starttime.
David's version works the other way around - it calculates the number of
full hours that have elapsed since a chosen anchor date, then adds that
number to the chosen anchor date. The result will be the same as John's
expression.
(Note: David chose to just use the number of hours for the group by, and
add it back to the anchor date in the SELECT clause only)
>Assuming black magic happens and thats just how it works, I should just
>be able to change those hh,1 to hh,4 and get 4 hour increments instead.
No. I'll give you two examples how to modify David's query to report on
4-hour intervals and to report on 1/2-hour intervals.
For 4-hour intervals, again calculate the number of hours since an
anchor date. Divide by 4 and truncate, then multiply by 4 again. Add
this number of hours to the anchor date. There you have the start of the
last 4-hour interval
SELECT groupid,
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'20050101')),
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'2005-01-01T03:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
For 1/2-hour intervals, we can't divide the number of hours sice the
anchor date by 0.5, as that won't give us back the precision we already
lost. Instead, we'll have to calculate minutes and divide by 30:
SELECT groupid,
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'20050101')),
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'2005-01-01T00:29:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
In both cases, don't forget to change the shifted anchor value in the
expression for the end point of the interval. Instead of using the same
anchor date, then adding 30 minunte or 4 hours minus one second, the
anchor date is shifted by 30 minutes or 4 hours minus one second.
Now, the above code can still be simplified further. If your table
always has the complete data (as your sample roiws indicate), then you
could change the above queries to:
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
-- or: GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
Note that this might show "holes" in the periods if your real data is
not as complete as the sample you posted indicates. But the advantage is
that you get rid of the "shifted" anchor date for calculating end time.
Final step would be to put it in a stored procedure and use a parameter
for the interval length (in minutes):
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) / @.Interval;
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jason,
What David Provided is an Excellent query .
Let us see if this query can help you.
Select GID , Min(STime) , Max(ETime) ,
Min(Minimum),Max(Maximum),Sum(Points)[co
lor=darkred]
>From yourTableName Group By[/color]
GID,Convert(varchar,STime,112),DatePart(
hh,STime)
Having same name as Functions/ Keyword sound confusing to me so I
changed them.
With Warm Regards
Jatinder Singh|||Hi
This is easier with David's method (see Hugo's reply for an explanation).
Dividing the number of hours by 4 and dropping the remainder will give you 4
hour chunks when they are multiplied back up. You also need to change the en
d
time to give a 4 hour gap.
SELECT groupid,
MIN(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'20050101')
) AS Starttime,
MAX(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'2005-01-01T03:59:59')
) AS Endtime,
MIN(min) AS [Min],
MAX(max) AS [Max],
SUM(points) AS [Total Points]
FROM Readings
GROUP BY groupid,
4*(DATEDIFF(HH,'20050101',starttime)/4)
John
"Factor" wrote:
> John
> I have read over those functions and I now understand what they do and
> how to use them, but I am still
as to why the min / max /> total fields actually work. I assume it has something to do with the
> GROUP BY statements, but again, I don't know why.
> Assuming black magic happens and thats just how it works, I should just
> be able to change those hh,1 to hh,4 and get 4 hour increments instead.
> When I do that, the Starttime and Endtime values do return correctly
> (although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
> the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
> that black magic that is limiting the MIN/MAX/SUM to one hour is still
> limiting them to one hour even with the altered start and end times.
> I'm unsure how to fix or get around this because I don't yet understand
> what is limiting that max to an hour in the first place. How does this
> work? I've been tripped up GROUP BY things before, it's my kryptonite
> for some reason.
> Hope that is not too confusing, I'm all jumbled in my head.
> I really apprecaite the help with this so far, you've all been
> wonderful.
> Jason
>|||Wondeful! Lots ot take in, I thank everyone for their help. I've made
a lot of progress and I've learned a TON about SQL int he past two
days.
I hope I can help you all in the future with something!
Thanks again,
Jason
A DB with the same name om two nodes
I'm not completly sure how to phrase this question.
In the 2 node cluster I'm working with, I noticed that there is a database
named "Utility" on both nodes of the cluster, which confuses me. I thought an
instance was clustered, not a database, so finding the duplicate DB doesn't
make sense.
Now I understand that master, model, msdb & tempdb will be duplicated across
nodes, but these are system DBs and (I would have assumed) special. The
Utility DB is serving the function for local stuff similar to a combination
of msdb & master, but for local admin applications.
I was of the impression that an instance was clustered, not a database. So,
I don't understand how the duplicate could (or should) be there.
OK, it is clear that you "can", but what happens during a failover? It would
seem that the copy on the failed instance would be unavailable and could
cause issues.
Also, does that mean that you can have a db on a clustered instance that is
not available after a failover?
"Edwin vMierlo [MVP]" wrote:
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:9467D8C2-0970-485E-9ADF-D3DA2E4E314A@.microsoft.com...
> an
> doesn't
> across
> combination
> So,
> Yes you can,
> so "Instance1" has a database called "data1" in a cluster group called
> "Group1"
> and "Instance2" has a database called "data1" in a cluster group called
> "Group2"
> Maybe I misunderstand your post, but I do not see a problem
> rgds,
> Edwin.
>
>
|||Two different instances on the same cluster act completely independently of
each other, so the database names can all be the same or different.
No different really than if you installed two instances on a stand alone and
created a database called "myDB" on each, other than the fact that in your
cluster, each instance has its own drive resources.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:12B664B7-DF3F-4B5D-8B0F-4AA9F1B2B4E3@.microsoft.com...[vbcol=seagreen]
> OK, it is clear that you "can", but what happens during a failover? It
> would
> seem that the copy on the failed instance would be unavailable and could
> cause issues.
> Also, does that mean that you can have a db on a clustered instance that
> is
> not available after a failover?
> "Edwin vMierlo [MVP]" wrote:
|||How many instances do you have installed?
There should only be one set of system databases per instance. And, they
should be located on a "shared" physical disk. Only 1 node should have
ownership of this disk at a time; therefore, you shouldn't have multiple
copies unless you had multiple instances.
Sincerely,
Anthony Thomas
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9467D8C2-0970-485E-9ADF-D3DA2E4E314A@.microsoft.com...
> I'm not completly sure how to phrase this question.
> In the 2 node cluster I'm working with, I noticed that there is a database
> named "Utility" on both nodes of the cluster, which confuses me. I thought
an
> instance was clustered, not a database, so finding the duplicate DB
doesn't
> make sense.
> Now I understand that master, model, msdb & tempdb will be duplicated
across
> nodes, but these are system DBs and (I would have assumed) special. The
> Utility DB is serving the function for local stuff similar to a
combination
> of msdb & master, but for local admin applications.
> I was of the impression that an instance was clustered, not a database.
So,
> I don't understand how the duplicate could (or should) be there.
|||It's less of a concern, than a comprension issue.
So, if I have one database per instance (what I would have expected) during
a failover, that DB will be seen by the other instance. However, if I have
the same database name on both instances, then during a failover, the local
copy will be the only visable one?
Like I sad, a compresion issue.
"Edwin vMierlo [MVP]" wrote:
> As Kevin already mentioned, after failover, there should be no difference,
> other than the two instances are online on the same physical node.
> An instance "lives" in a cluster group. The cluster group "acts" like an
> completely independent server, with it own Network Name, Ip address, disks,
> databases.
> Hope this helps to take your concerns away,
> Best Regards,
> Edwin.
> MVP - Windows Server - Clustering
>
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:12B664B7-DF3F-4B5D-8B0F-4AA9F1B2B4E3@.microsoft.com...
> would
> is
> database
> thought
> The
> database.
>
>
|||SQL Server instances failover, not databases. It is no difference than
running multiple instances on a stand-alone server, except instances in a
clustered configuration also differ by virtual server network name, but they
are still independent, ISOLATED binaries and databases. Nothing is "shared"
between the instances except for the cluster nodes that can potentially host
the resources.
The databases failover from one node to the other because the SQL Server
instance, network name, IP address, and disk change ownership between the
nodes. When SQL Server starts on the new host, it recovers the databases
just as if you had just restarted the services.
Sincerely,
Anthony Thomas
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:5EDABA97-5CC3-42BF-9EEA-A051C2A92FC7@.microsoft.com...
> It's less of a concern, than a comprension issue.
> So, if I have one database per instance (what I would have expected)
during
> a failover, that DB will be seen by the other instance. However, if I have
> the same database name on both instances, then during a failover, the
local[vbcol=seagreen]
> copy will be the only visable one?
> Like I sad, a compresion issue.
>
> "Edwin vMierlo [MVP]" wrote:
difference,[vbcol=seagreen]
disks,[vbcol=seagreen]
could[vbcol=seagreen]
that[vbcol=seagreen]
DB[vbcol=seagreen]
duplicated[vbcol=seagreen]
special.[vbcol=seagreen]
called[vbcol=seagreen]
called[vbcol=seagreen]
In the 2 node cluster I'm working with, I noticed that there is a database
named "Utility" on both nodes of the cluster, which confuses me. I thought an
instance was clustered, not a database, so finding the duplicate DB doesn't
make sense.
Now I understand that master, model, msdb & tempdb will be duplicated across
nodes, but these are system DBs and (I would have assumed) special. The
Utility DB is serving the function for local stuff similar to a combination
of msdb & master, but for local admin applications.
I was of the impression that an instance was clustered, not a database. So,
I don't understand how the duplicate could (or should) be there.
OK, it is clear that you "can", but what happens during a failover? It would
seem that the copy on the failed instance would be unavailable and could
cause issues.
Also, does that mean that you can have a db on a clustered instance that is
not available after a failover?
"Edwin vMierlo [MVP]" wrote:
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:9467D8C2-0970-485E-9ADF-D3DA2E4E314A@.microsoft.com...
> an
> doesn't
> across
> combination
> So,
> Yes you can,
> so "Instance1" has a database called "data1" in a cluster group called
> "Group1"
> and "Instance2" has a database called "data1" in a cluster group called
> "Group2"
> Maybe I misunderstand your post, but I do not see a problem
> rgds,
> Edwin.
>
>
|||Two different instances on the same cluster act completely independently of
each other, so the database names can all be the same or different.
No different really than if you installed two instances on a stand alone and
created a database called "myDB" on each, other than the fact that in your
cluster, each instance has its own drive resources.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:12B664B7-DF3F-4B5D-8B0F-4AA9F1B2B4E3@.microsoft.com...[vbcol=seagreen]
> OK, it is clear that you "can", but what happens during a failover? It
> would
> seem that the copy on the failed instance would be unavailable and could
> cause issues.
> Also, does that mean that you can have a db on a clustered instance that
> is
> not available after a failover?
> "Edwin vMierlo [MVP]" wrote:
|||How many instances do you have installed?
There should only be one set of system databases per instance. And, they
should be located on a "shared" physical disk. Only 1 node should have
ownership of this disk at a time; therefore, you shouldn't have multiple
copies unless you had multiple instances.
Sincerely,
Anthony Thomas
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9467D8C2-0970-485E-9ADF-D3DA2E4E314A@.microsoft.com...
> I'm not completly sure how to phrase this question.
> In the 2 node cluster I'm working with, I noticed that there is a database
> named "Utility" on both nodes of the cluster, which confuses me. I thought
an
> instance was clustered, not a database, so finding the duplicate DB
doesn't
> make sense.
> Now I understand that master, model, msdb & tempdb will be duplicated
across
> nodes, but these are system DBs and (I would have assumed) special. The
> Utility DB is serving the function for local stuff similar to a
combination
> of msdb & master, but for local admin applications.
> I was of the impression that an instance was clustered, not a database.
So,
> I don't understand how the duplicate could (or should) be there.
|||It's less of a concern, than a comprension issue.
So, if I have one database per instance (what I would have expected) during
a failover, that DB will be seen by the other instance. However, if I have
the same database name on both instances, then during a failover, the local
copy will be the only visable one?
Like I sad, a compresion issue.
"Edwin vMierlo [MVP]" wrote:
> As Kevin already mentioned, after failover, there should be no difference,
> other than the two instances are online on the same physical node.
> An instance "lives" in a cluster group. The cluster group "acts" like an
> completely independent server, with it own Network Name, Ip address, disks,
> databases.
> Hope this helps to take your concerns away,
> Best Regards,
> Edwin.
> MVP - Windows Server - Clustering
>
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:12B664B7-DF3F-4B5D-8B0F-4AA9F1B2B4E3@.microsoft.com...
> would
> is
> database
> thought
> The
> database.
>
>
|||SQL Server instances failover, not databases. It is no difference than
running multiple instances on a stand-alone server, except instances in a
clustered configuration also differ by virtual server network name, but they
are still independent, ISOLATED binaries and databases. Nothing is "shared"
between the instances except for the cluster nodes that can potentially host
the resources.
The databases failover from one node to the other because the SQL Server
instance, network name, IP address, and disk change ownership between the
nodes. When SQL Server starts on the new host, it recovers the databases
just as if you had just restarted the services.
Sincerely,
Anthony Thomas
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:5EDABA97-5CC3-42BF-9EEA-A051C2A92FC7@.microsoft.com...
> It's less of a concern, than a comprension issue.
> So, if I have one database per instance (what I would have expected)
during
> a failover, that DB will be seen by the other instance. However, if I have
> the same database name on both instances, then during a failover, the
local[vbcol=seagreen]
> copy will be the only visable one?
> Like I sad, a compresion issue.
>
> "Edwin vMierlo [MVP]" wrote:
difference,[vbcol=seagreen]
disks,[vbcol=seagreen]
could[vbcol=seagreen]
that[vbcol=seagreen]
DB[vbcol=seagreen]
duplicated[vbcol=seagreen]
special.[vbcol=seagreen]
called[vbcol=seagreen]
called[vbcol=seagreen]
Sunday, March 25, 2012
A connection could not be made with report server at https://webse
Hi,
My report server and report manager was working fine till yesterday. But
suddenly today when i tried to deploy a report i got error: "A connection
could not be made with report server at https://webserver1/ReportServer "
So when i tried to browse the report manager from IIS, I get error:"Could
not establish trust relationship with remote server."
I think its someting to do with SSL. Please help me with that 'czo out of
the blue today I have started getting this error.
--
pmudLook in the event log at the server machine for SSL errors.
Regards,
Martin
My report server and report manager was working fine till yesterday. But
suddenly today when i tried to deploy a report i got error: "A connection
could not be made with report server at https://webserver1/ReportServer "
So when i tried to browse the report manager from IIS, I get error:"Could
not establish trust relationship with remote server."
I think its someting to do with SSL. Please help me with that 'czo out of
the blue today I have started getting this error.
--
pmudLook in the event log at the server machine for SSL errors.
Regards,
Martin
Thursday, March 22, 2012
A connection cannot be made to Analysis Services 2005
Please help!
I have been trying to deploy an Analysis Services 2005 cube to a server from a local PC. I have had no luck. This was working yesterday and I cannot work out what might be different. This is the error message I am getting:
The project could not be deployed to the 'qccatsvr1' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.
By the way this works on another PC that is set up the same way...the only thing I can see different is the Novell user account I used to log in to the PC.
This is what I have tried so far:
- Checked that the Analysis Services service is running on the server - it is. Check the target server - this is ok. Re-install Analysis Services 2005 - no problems occurred during this Tried all different changes to the security log on in the Analysis Services service - nothing worked. Tried to open the AS solution file from the actual server and deploy - no luck.
So you can see that I am now getting quite frustrated. I know (hope) it is something simple that I have missed.
Does anyone have any ideas/solutions for me.
Thanks in advance!!!
Steve
SSAS uses windows authentication exclusively. It will not authenticate using a Novell account, but there should be a setting in your Novell client which controls how the user is logged on to the local machine. It is the local windows account that needs to have administrator priviledges in SSAS in order to be able to deploy a new database.
Were you using the same user account when you logged on to the server and tried to deploy the solution? If you use the same account that was used to install SSAS you should not have any issues.
If you want to deploy a solution from a remote machine on a Novell network you will need to setup a local account on the remote workstation which is set to log on to the local machine when the Novell user logs in and then you will need to set up an identical local account (with an identical password) on the server machine and assign the account on the server to the server administration role in SSAS.
|||I have setup exactly the same account on the server as the windows username used to logon to the remote PC. The account also has exactyl the same password and has been given full access to SSAS. However this does not work either.
The things that has got me confused is that this user was able to deploy a few days before I posted this thread with no problems...that was without a username and password set up on the server with access to SSAS.
Do you have any aother ideas?
|||I thought you mentioned in your original post that you were now using a different user account. Which is what made me think that it must be something to do with the Novell account/local account connection.
You could have a look at the following link, it contains information on troubleshooting SSAS connection issues: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx
|||Darren,
Thank-you for your advice. I got onto that document and tried a couple of things. The one that helped the most was the SQL Server Profiler. This showed me what was happening with particular accounts and I was able to change the account permission accordingly.
Everything is now ok.
Thanks again for your help with this one...
A connection cannot be made to Analysis Services 2005
Please help!
I have been trying to deploy an Analysis Services 2005 cube to a server from a local PC. I have had no luck. This was working yesterday and I cannot work out what might be different. This is the error message I am getting:
The project could not be deployed to the 'qccatsvr1' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.
By the way this works on another PC that is set up the same way...the only thing I can see different is the Novell user account I used to log in to the PC.
This is what I have tried so far:
Checked that the Analysis Services service is running on the server - it is.
Check the target server - this is ok.
Re-install Analysis Services 2005 - no problems occurred during this
Tried all different changes to the security log on in the Analysis Services service - nothing worked.
Tried to open the AS solution file from the actual server and deploy - no luck.
So you can see that I am now getting quite frustrated. I know (hope) it is something simple that I have missed.
Does anyone have any ideas/solutions for me.
Thanks in advance!!!
Steve
SSAS uses windows authentication exclusively. It will not authenticate using a Novell account, but there should be a setting in your Novell client which controls how the user is logged on to the local machine. It is the local windows account that needs to have administrator priviledges in SSAS in order to be able to deploy a new database.
Were you using the same user account when you logged on to the server and tried to deploy the solution? If you use the same account that was used to install SSAS you should not have any issues.
If you want to deploy a solution from a remote machine on a Novell network you will need to setup a local account on the remote workstation which is set to log on to the local machine when the Novell user logs in and then you will need to set up an identical local account (with an identical password) on the server machine and assign the account on the server to the server administration role in SSAS.
|||I have setup exactly the same account on the server as the windows username used to logon to the remote PC. The account also has exactyl the same password and has been given full access to SSAS. However this does not work either.
The things that has got me confused is that this user was able to deploy a few days before I posted this thread with no problems...that was without a username and password set up on the server with access to SSAS.
Do you have any aother ideas?
|||I thought you mentioned in your original post that you were now using a different user account. Which is what made me think that it must be something to do with the Novell account/local account connection.
You could have a look at the following link, it contains information on troubleshooting SSAS connection issues: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx
|||Darren,
Thank-you for your advice. I got onto that document and tried a couple of things. The one that helped the most was the SQL Server Profiler. This showed me what was happening with particular accounts and I was able to change the account permission accordingly.
Everything is now ok.
Thanks again for your help with this one...
Tuesday, March 20, 2012
A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
sqlA call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication
Hi,
Iam trying to perform merge replication between SQL Server 2005 and SQL server mobile. It has previously been working. Recently something is causing the following problem when i try to perform the merge. I grabed the following output from the replication monitor.
Error messages:
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQLServer, Error number: 2147767868)
Get help: http://help/2147767868
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 2147766295)
Get help: http://help/2147766295
From Sql Server managment studio i:
--> right clicked on the publication
--> selected view snapshot agent status
--> i then pressed start
the result was [100%] A snapshot of 8 article(s) was generated.
I then retried to sync it and it had the same problem. I upped then logging to level 3. The log produced the following:
2005/10/02 20:56:18 Hr=00000000 Compression Level set to 1
2005/10/02 20:56:18 Hr=00000000 Count of active RSCBs = 0
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Compressed bytes in = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Total Uncompressed bytes in = 309
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 Responding to OpenWrite, total bytes = 175
2005/10/02 20:56:18 Thread=C38 RSCB=15 Command=OPWC Hr=00000000 C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\30.4E1B0DC71663_8B01FD62-37A2-8AD5-F4E5-B5FF8162902B 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=00000000 Synchronize prepped 0
<PARAMS RSCB="15" HostName="" Publisher="i-removed-this-for-the-post" PublisherNetwork="" PublisherAddress="" PublisherSecurityMode="1" PublisherLogin="" PublisherDatabase="CARSRBO_DB" Publication="CARSRBO_DB" ProfileName="DEFAULT" SubscriberServer="CARSRBO_DB - 4e1b0dc71663" SubscriberDatabasePath="Program Files\carsmr.ui\CARSMR_DB.sdf" Distributor="i-removed-this-for-the-post" DistributorNetwork="" DistributorAddress="" DistributorSecurityMode="1" DistributorLogin="" ExchangeType="3" ValidationType="0" QueryTimeout="300" LoginTimeout="15" SnapshotTransferType="0" DistributorSessionId="72"/>
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=8004563C An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. -2147199428
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=80004005 SyncCheck responding 0
2005/10/02 20:56:19 Thread=E64 RSCB=15 Command=SCHK Hr=00000000 Removing this RSCB 0
Note i noticed that the "MSMerge_rowtrack" does not exist in the snapshot folder only variations in the format MSMerge_rowtrack*.bcp such as MSMerge_rowtrack90.bcp Any advice on this issue would be appreciated as i have not found anything solid through google. Thanks in advance,
pdns
The error code 80045017 seems to indicate a permission problem.
Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files.
And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too.
The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured.
I think you were right in it being a security problem.
i followed the tutorial at http://msdn2.microsoft.com/en-us/library/ms240364
and the issue is now resolved.
I am not sure of exactly where the permission problem was.
Could it have been that for the SQL Server 2005 security properties that the settings for Security -> Logins -> Computername/IUSR_*
did not have MSmerge_* database roles assigned for the database ?|||Glad that the issue is resolved.
I think the user ISUR* did not read permissions on the snapshot folder.|||
I have following error in propagate while subscription, the error like this.
Error messages:
The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\ReplData\unc\TOSHIBA$SQLSERVER2005ENT_TESTMOBILE_TESTSQLMOBILE\20060303170979\tblTest_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024891)
Get help: http://help/MSSQL_REPL-2147024891
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
I try to synchronization from SQL Server 2005 Enterprise to SQL Mobile 2005. While add new subscription on SQL Mobile 2005 it raised error like this on synchronizing data :
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed.
HRESULT 0x80045901 (29045)
The remote server "TOSHIBA\SQLSERVER2005ENT" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
HRESULT 0x00003700 (0)
{call sp_helpdistpublisher (N'TOSHIBA\SQLSERVER2005ENT') }
HRESULT 0x00000000 (0)
You do not have the required permissions to complete the operation.
HRESULT 0x0000372E (0)
The operation could not be completed.
Any way to fix this ? The both error are on the same scene (while add SQL Mobile Subscription).
Thanks.
|||Hello...
I had the same problem the past few days...and what solved it for me was two things:
1. making sure that the windows user accounts for the replication had read/write control of the replication problems.
2. making sure that those windows user accounts (and my one sql login account that is used for anonymous access) were in the Publication Access List for the publication.
|||how do I check if the user account is in publication access list or not? Thx.|||should be somewhere on the publication properties page. Search books online for "merge publication access list" for more information.|||I found the list and verified the the two things you mentioned. Yet I still have the exact same error message as you did. I am trying to snyc sql server 2005 standard to SQL mobile. Our IIS is on a seperate box. Did you use IIS on a seperate box too? Much obliged to your info.
|||We are also synchronizing between sql server 2005 standard and sql server Mobile. However, we are running IIS and SQL Server on the same box.|||We are experiencing the same symptoms. All was going fine until yesterday morning. Nobody has changed anything and the issue is not happening for all of the users. This makes me suspcious of the theory that permissions are at fault.Can anyone who had this problem and resolved it recount what processes they went through?
Thanks|||Additionally you have to make sure that the user connecting to the IIS site has permissions to launch and execute the replisapi.dll. This can be done using the Configure Web Sync wizard.|||Also check if the snapshot is still valid and the files are not cleaned up. If they arent, then rerun the snapshot agent.|||Re-runnning the snapshot is what we do to get it going again - but this does not explain why there is different behaviour for individual users.
Each user has an individual snapshot - should they be updated? The normal snapshot gets run every night so shouldn't be out of date.
What we have established is that this only occurs for some users when the application has been re-installed. (Again, not for all users).|||
When a normal snapshot runs, it invalidates all the dynamic snapshots. Also if the snapshot is expired due to retention or other admin actions then the snapshot needs to be run to make the syncs succeed.
What do you mean by application has been re-installed? On what server, and what does it mean to the subscriber?
Subscribe to:
Posts (Atom)