Blocking Transaction

I have a 3 simple packages get called from master package. First package populates time dimension (stored proc), second one populates transaction type dimension(stored proc) and the final one populates date dimension.

I set the TransactionOption = Required in the Master package, every thing else (package & component) set TransactionOption = Supported.

I have been testing transactions, basically I made time and transaction type dimension to fail and the package did roll back. I am stuck in the date dimension.

Within Date dimension I got a stored procedure which populates calendar date attributes into Wrk_Date table. Then I have a data flow task which reads from thats working table, but it cant access it. I tried running SP_WHO2 command, and the status is SUSPENDED, being blocked by id -2.

I saw someone had similar problem and I did read Ash's comments. I did try to change the isolation level, didnt help.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74372&SiteID=1

Any thoughts please?

Thanks

Sutha

Sutha,

I had enormous problems with this and eventually just dropped the use of distributed transactions altogether. In some cases it simply wouldn't work because of the way DTC holds onto resources. Since then I have used checkpoint files and it works just fine.

-Jamie

|||

Jamie

Thanks for the infor, but it is annoying. Do you know this is going to be fixed at any point ?

Client is not happy having a checkpoint. They want to rollback the whole dimension load. For example if we have got 6 dimensions and 5 of them loaded fine and 6th one failed, then they want to rollback the whole lot. What would be the solution in that scenario please, if we cant use transactionoption correctly?

Thanks

Sutha

|||

I don't think its a case of it being fixed - its just "the way it is". DTC isn't doing anything wrong - its doing exactly what you've told it to i.e. Maintaining a lock on a table because of a running transaction.

Its a difficult one to broach with the client, I can see that. Maybe you could restructure your package so that the locks don't occur.

-Jamie

|||

Jamie

Thanks. I was going through step by step and identified the reason for locks. During step 2, if date table exist in stage database it TRUNCATES the table else it creates the table. Truncate statement seems to hold the lock and it is not releasing it further down the line. I have managed to get the rollback to work after removing truncate.

Thanks

Sutha

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
--
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> > My fellow administrator tells me that his biztalk account needs read and
> > write permissions to the master database of out MSSQL 2000 Enterprice
> > edition, running on a windows 2000 advanced server.
> >
> > Our biztalk developer does not remember that there has been an issue, so
> > he
> > is not doing anything specific on the masters database, in the jobs he is
> > running.
> >
> > Is it for real that biztalk needs this access?
> >
> > --
> > Best regards
> >
> > Mikael
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>> Mikael
>> You have to ask him why does he want to access? What process/s does he
>> run ?
>>
>>
>> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
>> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>> > My fellow administrator tells me that his biztalk account needs read
>> > and
>> > write permissions to the master database of out MSSQL 2000 Enterprice
>> > edition, running on a windows 2000 advanced server.
>> >
>> > Our biztalk developer does not remember that there has been an issue,
>> > so
>> > he
>> > is not doing anything specific on the masters database, in the jobs he
>> > is
>> > running.
>> >
>> > Is it for real that biztalk needs this access?
>> >
>> > --
>> > Best regards
>> >
>> > Mikael
>>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I canâ't.
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> > Yes, it has been a while since they did the installation and he just
> > remembers that it needs the permissions, but not why. My question is just
> > if
> > the biztalk application itself needs this access to run?
> >
> > --
> > Best regards
> >
> > Mikael
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Mikael
> >>
> >> You have to ask him why does he want to access? What process/s does he
> >> run ?
> >>
> >>
> >>
> >>
> >> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> >> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> >> > My fellow administrator tells me that his biztalk account needs read
> >> > and
> >> > write permissions to the master database of out MSSQL 2000 Enterprice
> >> > edition, running on a windows 2000 advanced server.
> >> >
> >> > Our biztalk developer does not remember that there has been an issue,
> >> > so
> >> > he
> >> > is not doing anything specific on the masters database, in the jobs he
> >> > is
> >> > running.
> >> >
> >> > Is it for real that biztalk needs this access?
> >> >
> >> > --
> >> > Best regards
> >> >
> >> > Mikael
> >>
> >>
> >>
>
>

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a Windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a Windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just i
f
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> You have to ask him why does he want to access? What process/s does he run
?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
--
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
Mikael
Mikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael
|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>
|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>

Bit Confused over Master SQL Server

Hi,
I'm developing a bit of a server farm so I thought I would
bite the bullet and set myself up a 'Master' server. This
would automatically send updates, jobs to the other
servers.
I think I may have my facts right that there is such a
thing but was wondering if there was, and of there was is
a way can anyone provide me with some good background
material.
TIAMaria,
there are details in BOL under "master server". The
details are more on the implementation side, but it is
not really difficult technology. Once you've set up your
master and target servers (MSX, TSX) please post back if
you have any questions.
Here are a couple of articles I found Googling:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_automate_7ir2.asp
http://www.winnetmag.com/SQLServer/Article/ArticleID/23982
/23982.html
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)