Do SQL Analysis service needs a separate database ?

As Adventure Works have Adventure works DW as a separate database, Do we also needs to create new database such as while working with "pubs" or any other do we need to create pubs DW separately or pubs will be sufficient for our working for sql analysis ?

Hi,

you should to create a new DW database (it′s a best pratice), but it′s not mandatory.

If you have a small production database, you will not percept the performance degradation...

So to create a DW (Dataware house) can be good to isolate OLAP from OLTP.

Regards

Do I need a data extension?

I'm working on a project for a national restaurant chain. I've built a
complex class that allows them to do various forms of sales estimations for
stores who fail to report their weekly sales. I say "complex" because it
has a detailed class hierarchy under it and performs lot of SQL Server work
inside its "black box." Using this class within a web page is working
great...
Now they want this functionality to also appear in a report, and I'm
wondering what I have to do -- never done a RS data extension and first am
wondering: Is this what I need to add this function to a report?
If the answer is "yes," is there a resource that describes in detail how to
do it, with a non-trivial example?
I'm really under the gun on this one, so I'll be grateful for any help --
thanks!
JoeI'd start with the sample reports that ship with the product. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe Helmick" <joe_helmick@.4dv.net> wrote in message
news:O2r7tE5jEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I'm working on a project for a national restaurant chain. I've built a
> complex class that allows them to do various forms of sales estimations
for
> stores who fail to report their weekly sales. I say "complex" because it
> has a detailed class hierarchy under it and performs lot of SQL Server
work
> inside its "black box." Using this class within a web page is working
> great...
> Now they want this functionality to also appear in a report, and I'm
> wondering what I have to do -- never done a RS data extension and first am
> wondering: Is this what I need to add this function to a report?
> If the answer is "yes," is there a resource that describes in detail how
to
> do it, with a non-trivial example?
> I'm really under the gun on this one, so I'll be grateful for any help --
> thanks!
> Joe
>|||Joe,
One option in your scenario may be to dump your object data (state) in a
database before the report is run. Unfortunately, version 1.0 of Reporting
Services doesn't support events, so to implement preprocessing you need to
get somewhat innovative. One option is to attach an expression to a property
of the report body band (it will fire before the regions get loaded). This
expression could gather the report parameters, pass it to your object which
in turn can serialize itself to the database and let the regions pick data
from there. Of course, this is a rather simplified version of what you may
need to do.
Yes, another and more elegant option is to wrap you object in a custom data
extension. This is actually very straightforward. You need to expose your
object data as a forward-only tabular stream which conforms to the
IDataReader interface. You may find my ADO.NET custom data extension
(http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B846
8707-56EF-4864-AC51-D83FC3273FE5) useful to get you started. It comes with
step-by-step help instructions.
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Joe Helmick" <joe_helmick@.4dv.net> wrote in message
news:0l1aj0h4452vd72jles53foioiqfhfdphn@.4ax.com...
> Ravi,
> Thanks, but I have all the samples and unless I missed something due
> to my newbie-ness, I don't see what I need. I don't need drilldown or
> drillthrough, and simple functions in a code-behind don't seem like
> they'd nearly fill the bill. I'm already doing cascading parameters
> from multiple datasets and stuff like that, no problem, but attaching
> functionality from a 700-line VB class with deep inheritance? I don't
> see anything like that in the samples...
> Did I miss something, or do I need another resource besides the
> samples?
> Joe
> "Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> bellowed
> forth with this wisdom for all to hear:
> > I'd start with the sample reports that ship with the product. Check
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true
> > for details.
>sql

Do Analysis service needs a seprate Database ?

As Adventure Works have Adventure works DW, Do we also needs to create new database such as while working with pubs do we need to create pubs DW separately or pubs will be sufficient for our working ?

for sure not.

But it is a good design aproach.

You could do a seperate BI database, or

create your own tables in the exiting database, or

create views in the existing database or

use the Analysis Services Datasource View with Named querys

to do the data transformation

HANNES

DMX query and ASP.Net

I am trying to get along with SQL server 2005, made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio. I cant get predection result in to aspx. I found this article but still...nothing

http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp

After spending some weeks of testing and reading I came up with this:

Itsa part of the code of cross web application, I managed to make a modelof timeseries in SQL server and I have hopefully (since I dont get anyerrors) run my dmx query through aspx. BUT I can get the result todisplay in aspx. My guess is because the original code was made forstring variables and I am tring to get numeric variables in it. Isuppose that all I need to do is to get the result from the DMX queryin the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If


'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks

Try changing

"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]

to

"SELECT PredictTimeSeries([Apot Sales],5)AS 'APOT' FROM Sales"

Giving the column a name should allow it to be displayed.

Division not working

The following yields strange results
select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
([FieldB]>=1)
sample output is
FieldA FieldB FieldC
4 11 0
3 4 0
3 4 0
5 12 0
5 12 0
4 5 0
Addition, subtraction and multiplication all work properly, but division
isn't. Is there a flag somewhere in SQL that needs to be set for division to
work?
ThanksInteger division yield integer result. try casting one of the columns
involved in the expression to a datatype with greater precedence.
select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
TableA
where ([FieldB]>=1)
AMB
"mike" wrote:

> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
to
> work?
> Thanks|||Hi
Results are fine if the columns are integer datatype. 4/11 = 0 in integer
division
select
FieldA,
FieldB,
(CONVERT(DECIMAL(18,2), FieldA) / CONVERT(DECIMAL(18,2), FieldB)) as
FieldC
from
TableA
where
FieldB>=1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:95F2FAF7-1282-410B-8E00-831EA32921A6@.microsoft.com...
> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA
> where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
> to
> work?
> Thanks|||D'oh!
Thanks, I'd tried casting the entire result, but not each field prior to
division.
"Alejandro Mesa" wrote:
> Integer division yield integer result. try casting one of the columns
> involved in the expression to a datatype with greater precedence.
> select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
> TableA
> where ([FieldB]>=1)
>
> AMB
> "mike" wrote:
>|||Mike,
If you cast the entire result, then you will be casting the integer result
and this will give you the same problem. It is not the same casting the
variables / columns involved in the expression to yield a higher precedence
datatype as casting the result.
Example:
select cast(1 as numeric(5, 3)) / 2, cast(1 / 2 as numeric(5, 3))
AMB
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>|||Yes, unfortunately Casting the result is too late.. The Integer division has
already generated the wrong (zero) result...
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>

Divide by zero

I'm using Toolman's Public function and it's working as far as the divide by
zero, however, I'm trying to get a percentage. When I pass the two
parameters to it, if the result is 100.% it shows up. If it's anything else,
it shows 0.0%. Here is the function and the code.
Public Function DivideBy(ByVal exp1, ByVal exp2)
If exp1 = 0 Then
DivideBy = 0
Else
DivideBy = exp1 / exp2
End If
End Function
=code.DivideBy
( Fields!Under120_1.Value, Fields!TotalRecs.Value )
Any help is appreciated.
--
Thanks!Sounds like a datatype issue, maybe there is a conversion to int in your
expression?
I tried the expression:
=Code.DivideBy(5, 100) * 100
and it doesn't return zero or 100.
I wrapped a CInt() around it and I get that behavior.:
=CInt(Code.DivideBy(5, 100))
The CInt forces either a 0 or 1, then it's converted to percent.
"Candy" <Candy@.discussions.microsoft.com> wrote in message
news:3BBFBF2E-EAEB-4103-BC70-E81C14AE537F@.microsoft.com...
> I'm using Toolman's Public function and it's working as far as the divide
> by
> zero, however, I'm trying to get a percentage. When I pass the two
> parameters to it, if the result is 100.% it shows up. If it's anything
> else,
> it shows 0.0%. Here is the function and the code.
> Public Function DivideBy(ByVal exp1, ByVal exp2)
> If exp1 = 0 Then
> DivideBy = 0
> Else
> DivideBy = exp1 / exp2
> End If
> End Function
> =code.DivideBy
> ( Fields!Under120_1.Value, Fields!TotalRecs.Value )
> Any help is appreciated.
> --
> Thanks!|||Try CDbl() instead of CInt()
Also, make sure the values being passed are not integer values

'distributor_admin' is not defined as a remote login at the server

We have set up one of the clustered instance as a remote distributor and
replication has been working properly. However, on the event log of the
active node, I saw the following error appeared every few minutes:
Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
'distributor_admin' is not defined as a remote login at the server. Verify
that you have specified the correct login name. [CLIENT: 10.1.1.28].
REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
as a remote distributor and it is running on active node; the client
(10.1.1.28) is the passive node of the cluster. our environment is windows
2003 and sql server 2005 with sp2.
I wonder if anyone has any idea how to get rid of this error from my server.
Thanks in advance.
Zack.
I wonder if it is a managed news group by Microsoft, if not, someone knows
the managed news group since I was told that it is a managed group and
Microsoft will reply every post?
thanks for your kind reply because I really need to fix this error and don't
want to pay MS for this kind of error. We have paid too much to MS already
for support.
Zack.
m
"dp" wrote:

> We have set up one of the clustered instance as a remote distributor and
> replication has been working properly. However, on the event log of the
> active node, I saw the following error appeared every few minutes:
> Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
> 'distributor_admin' is not defined as a remote login at the server. Verify
> that you have specified the correct login name. [CLIENT: 10.1.1.28].
> REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
> as a remote distributor and it is running on active node; the client
> (10.1.1.28) is the passive node of the cluster. our environment is windows
> 2003 and sql server 2005 with sp2.
> I wonder if anyone has any idea how to get rid of this error from my server.
> Thanks in advance.
> Zack.
>

distributor agent

Win2003srv
mssql2000
mssql2005
Oracle
I have a mssql2000 database that I Replicate to oracle,
some tables have row filtering.
This was working fine until I create an another publication and subscription
(transaction)
on the same database, for mssql2005 This subscription has the
loopback_detection = N'True',
After this, the Oracle subscription stop to work correctly
Tables with row filter are not replicated. In Repl Monitor everything looks
fine
I try to delete all publication and then re-crate it and start snap-shot for
oracle
The bulk copying are doing the right thing regarding filter-rows.
But the distributor agent will not work properly
I can see the log-reader pushing transaction to the distibutor , but nothing
happens
What is wrong her?
-roger
What do you see if you run a sp_browsereplcmds in the distribution database?
The oracle DML should be showing up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roger Nygrd" <roger@.askit.no> wrote in message
news:11uemak27qnmp5d@.corp.supernews.com...
> Win2003srv
> mssql2000
> mssql2005
> Oracle
> I have a mssql2000 database that I Replicate to oracle,
> some tables have row filtering.
> This was working fine until I create an another publication and
> subscription (transaction)
> on the same database, for mssql2005 This subscription has the
> loopback_detection = N'True',
> After this, the Oracle subscription stop to work correctly
> Tables with row filter are not replicated. In Repl Monitor everything
> looks fine
> I try to delete all publication and then re-crate it and start snap-shot
> for oracle
> The bulk copying are doing the right thing regarding filter-rows.
> But the distributor agent will not work properly
> I can see the log-reader pushing transaction to the distibutor , but
> nothing happens
> What is wrong her?
>
> -roger
>
|||It is empty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23w5ntqyKGHA.2036@.TK2MSFTNGP14.phx.gbl...
> What do you see if you run a sp_browsereplcmds in the distribution
> database? The oracle DML should be showing up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roger Nygrd" <roger@.askit.no> wrote in message
> news:11uemak27qnmp5d@.corp.supernews.com...
>

Distribution times out...

I have a transactional replication working fine till to date. I have
the publisher on ServerA and subscriber on ServerB. The transactional
replication is running fine...
I need to get a copy of the subscriber to another server and replicate
the Publisher to that serverC while the replication on ServerB is
running.
ServerA.Publisher --> ServerB.Subscriber (runs transactional
replication)
ServerA.Publisher --> ServerC.NewSuscriber (This need to run without
impacting ServerB.Subscription). I cannot resynch the databases as It
will take days to resynch and will also kill some of my data on the
ServerB.subscriber that is not in ServerA.publisher
I went with the way they had explained on MSKB 320499. I stoped the
transactions coming into the publisher and copied a full backup of
publisher to ServerC. I created the new subscription wihout re-synch
option to ServerC. I also copied all the sp's that are needed from
ServerB.Subscriber.
When i try to enable the distribution agent between ServerA and ServerC
i get the following error..
{call sp_MSget_repl_commands(11, ?, 0, 7500000)}
Timeout expired
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
------
The replication agent from ServerA to ServerB is running fine..! (Thank
God..!)
Can anyone help me figure out how to fix this issue between
ServerA.Publisher to ServerC.Subscriber?
Thanks in advance...!
Please can you confirm that you have the latest service pack on each server
(there were issues with "sp_MSget_repl_commands" before).
If this is not a service pack issue, try increasing the -QueryTimeOut
parameter of the distribution agent and also enable logging:
http://support.microsoft.com/?id=312292 to trap any more details for us.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul,
I have SP4 Installed on both servers. SQL 2000 Standard Edition on a
Windows 2003 Enterprise server.
Paul Ibison wrote:
> Please can you confirm that you have the latest service pack on each server
> (there were issues with "sp_MSget_repl_commands" before).
> If this is not a service pack issue, try increasing the -QueryTimeOut
> parameter of the distribution agent and also enable logging:
> http://support.microsoft.com/?id=312292 to trap any more details for us.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK - it's not that then
In that case try increasing the QueryTimeout parameter and do some logging
if there are any issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Distribution table cleanup

Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.

Thanks,

You can reduce the retention period using sp_changedistributiondb.

For more info, please refer to the following articles.

http://msdn2.microsoft.com/fr-fr/library/ms146868.aspx

http://msdn2.microsoft.com/fr-fr/library/ms181877.aspx

Regards,

Gary

Distribution table cleanup

Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.

Thanks,

You can reduce the retention period using sp_changedistributiondb.

For more info, please refer to the following articles.

http://msdn2.microsoft.com/fr-fr/library/ms146868.aspx

http://msdn2.microsoft.com/fr-fr/library/ms181877.aspx

Regards,

Gary

Distribution DB constantly grows

Hi,
I have set up transactional replication for a database from one db
server to a second. The replication is working fine but the distribution
database constantly grows. The size of the replicated db is 524 MB, the
size of the distribution db is 24 GB, growing daily.
How can I shrink my distribution database and how can I limit the growth
without damaging the replication?
Markus
you need to maintain the tlog on the distribution database. Make sure it
using the full recovery and dump the tlog every 5 minutes or so. You might
want to backup the log with truncate_only, shrink the tlog, and then backup
the database.
Then check your transaction retention period, it should be 48 hours. Make
sure your distribution clean up job is enabled and running every 10 minutes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Markus Renschler" <SP-news@.renschler.net> wrote in message
news:%23uv0qzXdFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have set up transactional replication for a database from one db server
> to a second. The replication is working fine but the distribution database
> constantly grows. The size of the replicated db is 524 MB, the size of the
> distribution db is 24 GB, growing daily.
> How can I shrink my distribution database and how can I limit the growth
> without damaging the replication?
> Markus
|||Hi Hilary,
thanks for the advice.

> you need to maintain the tlog on the distribution database. Make sure it
> using the full recovery and dump the tlog every 5 minutes or so. You might
> want to backup the log with truncate_only, shrink the tlog, and then backup
> the database.
The distribution database's backup model is set to simple. Is this a
problem (except the worse disaster recovery options)?

> Then check your transaction retention period, it should be 48 hours. Make
> sure your distribution clean up job is enabled and running every 10 minutes.
The transaction retention period has been set to the default value
(72h). I changed it to 48h. Then I checked the distribution cleanup job.
It is scheduled to run every 10 minutes. It had been started 5 hours ago
and was still running. I stopped it and started it again. Now it is
running since 15 Minutes. Is it possible that this job lasts more than 5
hours?
Markus
|||It is possible that it could run for more than 5 hours at first. Subsequent
runs should not take as long. Do you have anonymous subscribers? Metadata
hangs around a lot longer for them than for names subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Markus Renschler" <SP-news@.renschler.net> wrote in message
news:uzVIaOZdFHA.3488@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi Hilary,
> thanks for the advice.
might[vbcol=seagreen]
backup[vbcol=seagreen]
> The distribution database's backup model is set to simple. Is this a
> problem (except the worse disaster recovery options)?
Make[vbcol=seagreen]
minutes.
> The transaction retention period has been set to the default value
> (72h). I changed it to 48h. Then I checked the distribution cleanup job.
> It is scheduled to run every 10 minutes. It had been started 5 hours ago
> and was still running. I stopped it and started it again. Now it is
> running since 15 Minutes. Is it possible that this job lasts more than 5
> hours?
> Markus
|||Hilary Cotter wrote:
> It is possible that it could run for more than 5 hours at first. Subsequent
> runs should not take as long.
It has been running 3:35. The next runs took less than a second, each.
After the cleanup the database was still 22GB in size. I tried to shrink
it, but without an effect.
For testing, I have set the transaction retention period to 1h. Then I
started a distribution database cleanup, but without an effect.

> Do you have anonymous subscribers? Metadata
> hangs around a lot longer for them than for names subscribers.
No, I just have one subscriber which had its subscription pushed from
the origin server (which acts as publisher and distributor).
Could it be helpful if I re-create the distribution database? Is there
any possibility to do this?
Thanks for helping,
Markus

Distribution database of Transaction Replication publication being marked SUSPECT by recov

Hi experts there,
I have a Publication created for Transactional Replication. All the
while working fine.
Now it failed and I am not able to access to the database at all. It
shows the following error message:
Error 926: Database 'distribution' cannot be opened. It has been
marked SUSPECT by recovery. See the SQLServer error log for more
information.
Tried to detach the database but getting the following error: -
"The database cannot be detached while it is being replicated"
Tried running DBCC CheckDB but cannot run with database still in
suspect mode
Basically I cannot perform backup on it, cannot detach it, cannot even
drop the publication.
Tried also the following method:
1) sp_resetstatus DISTRIBUTION
Prior to updating sysdatabases entry for database 'DISTRIBUTION', mode
= 0 and status = 24 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made.
2) DBCC CHECKDB ('DISTRIBUTION', REPAIR_REBUILD) WITH ALL_ERRORMSGS
Server: Msg 926, Level 10, State 1, Line 1
Database 'distribution' cannot be opened. It has been marked SUSPECT
by recovery. See the SQL Server errorlog for more information.
Anybody know what cause all this and how to resolve it? Please
help!!!!!
I need to make the Replication running back soonest possible.
Thanks/TewI have seen this behavior once before. The only way we could get it out of
suspect mode was to directly update the sysdatabases table and set the
status to 32768 (emergency bypass). After recycling SQL Server we were then
able to run Checkdb. You might find corruption in the database. If not you
can set the status back to 0 and see if it will recover normally.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Distribution clean up not working

Hi
Pulling my hair out trying to find a resolution to the following problem.
Any help would be greatly appreciated.
Windows 2000 OS SP4, SQL Server 2000 SP3 replicating using transactional
replication from one server (lets call this server 1) inside our network out
to the internet to another server (lets calls this server 2)
Replication job failing for Distribution Cleanup on server 1 where articles
are created and replicated from. This is the job which removes all the old
snapshots after a set period of hours.
The error produced is the same each time bar the folder name which changes
depending on the folder it has problems with.
Executed as user: domain\username. Could not remove directory
'\\DEV-SQL\H$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040601045610\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. [SQLSTATE 42000] (Error 20015) Associated
statement is not prepared [SQLSTATE HY007] (Error 0)
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent ReplicationDB@.rowcount_only
parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only
scheduled for retry. Could not clean up the distribution transaction tables.
[SQLSTATE 01000] (Message 14152). The step failed.
The domain user which the sqlserver and sqlserveragent services use is in
the local administrators group on server 1.
If I login as the user which the services use, I can execute xp_cmdshell
'rmdir /S /Q "\\DEV-SQL\H$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040601045610"' in
query analyzer with no issues and the directory is deleted.
To my knowledge there is nothing accessing the files within the folder which
would stop the server from gaining an exclusive lock on them for deletion.
Thanks
Andy
Andy,
I can't spot anything strange about your setup. Ideally the share shouldn't
be an admin one - it should be in the form \\DEV-SQL\Repldata with the
associated rights on htis share. You could reconfigure with this, but this
shouldn't be the issue in your case. I'd try to schedule a job yourself
which runs the remove directory command and see if it fails with the same
error message. Also check the opened files for any remotely locked files in
computer management. Finally if none of this helps then have a look at
filemon to monitor locked files
(http://www.sysinternals.com/ntw2k/source/filemon.shtml) or if you're on Win
2003, http://www.mvps.org/sramesh2k/processlock.htm. Please post up your
findings, and also I'm assuming that you have a central
publisher/distributor - is this the case?
Regards,
Paul Ibison
|||Paul
Thanks for your response, FileMon has helped me make some progress with this
issue. I have attached some output from the log below. CMD.exe tries to get
the directory but it is already opened by inetinfo.exe and CMD.exe then
returns a sharing violation (towards the bottom of the log output).
InetInfo.exe is the IIS Admin Service Helper, any ideas why it may be
looking at this directory ?
Andy
13591 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Attributes: D
13592 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13593 10:49:22 CMD.EXE:5224 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS FileBothDirectoryInformation: *
13594 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 0 Length: 4096
13595 10:49:22 CMD.EXE:5224 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS FileBothDirectoryInformation
13596 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS Options: Open Access: All
13597 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 531218432 Length: 4096
13598 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS FileObjectIdInformation
13599 10:49:22 CMD.EXE:5224 DELETE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS
13600 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS
13601 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 40960 Length: 4096
13602 10:49:22 inetinfo.exe:1080 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13603 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS Options: Open Access: All
13604 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 531169280 Length: 4096
13605 10:49:22 inetinfo.exe:1080 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
SUCH FILE FileBothDirectoryInformation: TBLLAT~1.BCP
13606 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS FileObjectIdInformation
13607 10:49:22 inetinfo.exe:1080 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
13608 10:49:22 CMD.EXE:5224 DELETE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS
13609 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS
13610 10:49:22 inetinfo.exe:1080 DIRECTORY H:\ SUCCESS Change Notify
13611 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS Options: Open Access: All
13612 10:49:22 inetinfo.exe:1080 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13613 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 531214336 Length: 4096
13614 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS FileObjectIdInformation
13615 10:49:22 CMD.EXE:5224 DELETE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS
13616 10:49:22 inetinfo.exe:1080 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
SUCH FILE FileBothDirectoryInformation: TBLLAT~1.SCH
13617 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS
13618 10:49:22 inetinfo.exe:1080 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
13619 10:49:22 CMD.EXE:5224 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
MORE FILES FileBothDirectoryInformation
13620 10:49:22 inetinfo.exe:1080 DIRECTORY H:\ SUCCESS Change Notify
13621 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
13622 10:49:22 inetinfo.exe:1080 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13623 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SHARING VIOLATION Options: Open Directory Access: All
13624 10:49:22 inetinfo.exe:1080 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
SUCH FILE FileBothDirectoryInformation: TBLLAT~1.IDX
13625 10:49:22 inetinfo.exe:1080 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Or%23DS9%23REHA.2976@.TK2MSFTNGP10.phx.gbl...
> Andy,
> I can't spot anything strange about your setup. Ideally the share
shouldn't
> be an admin one - it should be in the form \\DEV-SQL\Repldata with the
> associated rights on htis share. You could reconfigure with this, but this
> shouldn't be the issue in your case. I'd try to schedule a job yourself
> which runs the remove directory command and see if it fails with the same
> error message. Also check the opened files for any remotely locked files
in
> computer management. Finally if none of this helps then have a look at
> filemon to monitor locked files
> (http://www.sysinternals.com/ntw2k/source/filemon.shtml) or if you're on
Win
> 2003, http://www.mvps.org/sramesh2k/processlock.htm. Please post up your
> findings, and also I'm assuming that you have a central
> publisher/distributor - is this the case?
> Regards,
> Paul Ibison
>
|||Andy,
the only relationship I can see is if you're doing a FTP initialization as
the inetinfo.exe does manage FTP access. Is it possible there is a FTP
subscriber in mid-flow?
Regards,
Paul Ibison
|||Hi Paul
Thanks for you reply.
Cannot see anything to do with FTP replication in our setup here.
If this is of any help, this is a copy if the SQL alert which is sent to me
when the job retries.
DESCRIPTION: Error: 14152, Severity: 10, State: 1
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent ReplicationDB@.rowcount_only
parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only
scheduled for retry. Could not clean up the distribution transaction tables
Regards
Andy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e7iSIwISEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Andy,
> the only relationship I can see is if you're doing a FTP initialization as
> the inetinfo.exe does manage FTP access. Is it possible there is a FTP
> subscriber in mid-flow?
> Regards,
> Paul Ibison
>
|||Andy,
unfortunately this error message is a fairly generic one to the job failing.
Sorry but I really don't know why inetinfo is locking your snapshot files. As an interim solution you could kill the inetinfo process before running the cleanup agent.
I'd post up a question related to this on the IIS newsgroup and see if anyone has any ideas.
Regards,
Paul Ibison

distribution clean up not working

Using transactional replication and all my transactions and commands are
replicated to all my subscribers. There is nothing to be delivered from the
msdistributionstatus view , but yet when i run the distribution cleanup, the
commands and transactions are still in the msrepl_commands and trans table.
When would they get deleted ?
Do you have anonymous subscribers enabled? If so, the commands will hand
around until the transaction retention perios is reached.
Rgds,
Paul Ibison, SQL MVP
|||How do I find out ? And if they are enabled, how do i turn them off ? I know
making certain settings to a publication causes the whole publication to
initilaize and resnapshot.
It happened once to me when i changed it to concurrent snapshot while
replication was on and next thing i know it triggered a reinitialisation and
all my objects were being snapshot.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:esOIyMDbFHA.2696@.TK2MSFTNGP09.phx.gbl...
> Do you have anonymous subscribers enabled? If so, the commands will hand
> around until the transaction retention perios is reached.
> Rgds,
> Paul Ibison, SQL MVP
>
|||sp_helppublication and look for allow_anonymous.
sp_changepublication can be used to alter.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Will this reinitialise all subscriptions to the publication ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also allow_anonymous = 0 .. So why is not cleaning up ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's possible the cleanup agent is being blocked.
I have experienced a similar problem but only on databases with really huge
tables (50 million or so), and still have an open PSS on it. The
recommendation was to reduce the transaction retention period, which was not
ideal. Even when I stopped the logreader and distribution agents, the
cleanup still didn't remove the records, for some strange reason, until the
retention period was reached. So, I made sure my subscribers had
synchronized then really reduced the retention period to remove the backlog.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Where do you change the transaction retention period ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uNdEoJEbFHA.2124@.TK2MSFTNGP14.phx.gbl...
> It's possible the cleanup agent is being blocked.
> I have experienced a similar problem but only on databases with really
huge
> tables (50 million or so), and still have an open PSS on it. The
> recommendation was to reduce the transaction retention period, which was
not
> ideal. Even when I stopped the logreader and distribution agents, the
> cleanup still didn't remove the records, for some strange reason, until
the
> retention period was reached. So, I made sure my subscribers had
> synchronized then really reduced the retention period to remove the
backlog.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||It's a distributor property, available from the replication monitor,
distributor properties, properties button of the distribution database.
Rgds,
Paul Ibison

Distribution Agent Stopped Working

I recently started having an issue with a distribution agent I have setup. I
am doing a simple transactional replication of a production database to
another server at a different location. A few days ago I started to receive
the error message " Error 14262:The
specified@.job_id('5E6241B8-55E2-472F-A3B9-346B7B66CFF5') does not exist"
whenever I tried to restart the distibution agent. It seems like the agent
was stopped a few days ago and now it cannot be restarted because of a
missing job. Can I recreate the agent and the job it is looking for? Or can I
recreate just the job and have the agent use that?
It seem like this job no longer exists. I do not see any reference to this id
in the sysjobs table. I do see a job in the sql server agent that performs
the same replication I am having problems with, but I believe this job is
from my attempts to recreate replication. The original job is not there. We
do have consultants that script things against our server, but as of yet none
of them have admitted to doing anything. Is it possible to recreate the job
that this agent is referencing or point it to the other job that is
performing the same function?
"Paul Ibison" wrote:

> I can't understnad how this can occur and would like to better understand
> it. Does the job id ('5E6241B8-55E2-472F-A3B9-346B7B66CFF5') exist in
> sysjobs? Can you start the job from sql server agent? If so,then it is the
> mapping of the logical distribution agent to the physical job that has gon
> awry. Has anything changed in the configuration recently - anyone scripted
> in the replication setup perhaps?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Have a look at syssubscriptions - the distribution_jobid column. It might be
that you could update this with the id of the job which is correct for you.
We're not really supposed to edit this type of info of course and the
supported mechanism would be to reinitialize.
Cheers,
Paul Ibison

Distribution Agent Hangs

Message
Another distribution agent for the subscription(s) is running or the server
is working on a previous request by the same agent.I have two Transactional
publications that were setup as push subscriptions, occasionally when the
distribution agent failed the spid did not complete and when the distribution
agent ran again we got this error
Killing the old SPID is a work around for the problem.
However, we have recently installed SQL 2005 and this is now the
distribution server. We had problems with the DTS packages so the
subscriptions have been changed to pull subscriptions, however now we are
getting this problem most nights and the spid hangs around everytime the
agent fails. Again killing the old spid is a work around but now its
happening too often for this to be acceptable.
Subscriber and Publisher are both SQL 2000 and are not likely to be
upgrading in the near future.
This only happens on two publications, and there are three publications set
up the same way on the same servers, and we have never had a problem with the
third one.
Any suggestions would be most welcome
Thanks
The first part of my message appears to be missing from the post.
We originally had a problem with this when the distributor was SQL 2000, and
the subscriptions were pull subscriptions.
We get this error when the distribution Agent fails (doesn't seem to matter
why it failed) the Agent Job errors and has stopped, but for some reason the
SPID does not disconnect hence when the distribution agent starts again we
get the error previously mention.
"Janet" wrote:

> Message
> Another distribution agent for the subscription(s) is running or the server
> is working on a previous request by the same agent.I have two Transactional
> publications that were setup as push subscriptions, occasionally when the
> distribution agent failed the spid did not complete and when the distribution
> agent ran again we got this error
> Killing the old SPID is a work around for the problem.
> However, we have recently installed SQL 2005 and this is now the
> distribution server. We had problems with the DTS packages so the
> subscriptions have been changed to pull subscriptions, however now we are
> getting this problem most nights and the spid hangs around everytime the
> agent fails. Again killing the old spid is a work around but now its
> happening too often for this to be acceptable.
> Subscriber and Publisher are both SQL 2000 and are not likely to be
> upgrading in the near future.
> This only happens on two publications, and there are three publications set
> up the same way on the same servers, and we have never had a problem with the
> third one.
> Any suggestions would be most welcome
> Thanks
>

Distributed Transactions ?

Hi There

I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.

BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.

I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.

When i execute the update or insert i get the error:
Cannot start a distributed transaction.

I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.

Can anyone please reccomend a good link as to how to get distributed transactions to work.

Thanx
P.S I have already checked this link http://support.microsoft.com/?kbid=816701.

Also i have found references to checking avrious services are running etc, seems there is alot to setup to get MSDTC to work but i cannot find the setup in BOL, not sure everything i have to do to egt MSDTC to work ?

Please help Thanx
|||

Have you tried setting XACT_ABORT ton ON?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Hi

Tried this

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
UPDATE PSAPMSGSUBCON SET STATUSSTRING = 'ERROR'

Got this error:

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Procedure PSAPMSGSUBCON_MESSAGE_ERROR_TRIGGER, Line 11
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

ALso found this in BOL:

It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. For more information, see Distributed Queries and Distributed Transactions.

I think i am simply missing something to ensure MSDTC is setup and running correctly.
Is the only thing require for MSDTC to work , that it be running, servers have the network DTC enabled and you stipulate

BEGIN DISTRIBUTED TRANSACTION

?

distributed transaction on win 2003 server failed

Hi Guys,
I AM USING DISTRIBUTED TRANSACTION WITH sql server 2000 on
win2k working fine.
but the same transaction not working in win2k3.
i am getting the following error.
Server: Msg 7391, Level 16, State 1, Procedure P2, Line 5
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
NOTE: MSDTC IS RUNNING ON BOTH SERVERS AND ENABLED FOR
NETWORK ACCESS.
MY CODE IS LIKE THIS.
CREATE PROCEDURE P2( @.NO INT)
AS
BEGIN DISTRIBUTED TRAN
INSERT INTO TESTB VALUES(1,'GH')
EXEC SHAFEEQVIRTUAL.PUBS.DBO.P1 @.NO
COMMIT TRAN
pls advice me
On a Windows 2003 cluster you have to manually create a MSDTC cluster
instance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"bijupg" <bijupg@.hotmail.com> wrote in message
news:0dbe01c503b2$665adce0$a601280a@.phx.gbl...
> Hi Guys,
> I AM USING DISTRIBUTED TRANSACTION WITH sql server 2000 on
> win2k working fine.
> but the same transaction not working in win2k3.
> i am getting the following error.
> Server: Msg 7391, Level 16, State 1, Procedure P2, Line 5
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> NOTE: MSDTC IS RUNNING ON BOTH SERVERS AND ENABLED FOR
> NETWORK ACCESS.
> MY CODE IS LIKE THIS.
> CREATE PROCEDURE P2( @.NO INT)
> AS
> BEGIN DISTRIBUTED TRAN
> INSERT INTO TESTB VALUES(1,'GH')
> EXEC SHAFEEQVIRTUAL.PUBS.DBO.P1 @.NO
> COMMIT TRAN
> pls advice me

distributed transaction giving error on win2003

Hi Guys,
I AM USING DISTRIBUTED TRANSACTION WITH 2 sql server 2000
on
win2k and is working fine.
but the same transaction not working in win2003.
i am getting the following error.
Server: Msg 7391, Level 16, State 1, Procedure P2, Line 5
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
NOTE: MSDTC IS RUNNING ON BOTH SERVERS AND ENABLED FOR
NETWORK ACCESS.
MY CODE IS LIKE THIS.
CREATE PROCEDURE P2( @.NO INT)
AS
BEGIN DISTRIBUTED TRAN
INSERT INTO TESTB VALUES(1,'GH')
EXEC server2.PUBS.DBO.P1 @.NO
COMMIT TRAN
pls advice meTry taking a look over this
http://blogs.msdn.com/florinlazar/a...1/02/47239.aspx
Message posted via http://www.droptable.com