DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.
Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server
does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

dtexec hangs unexpectedly

I have noticed a strange behaviour when running some of my packages with dtexec. The packages use EventHandlers to log information to a database using the Execute SQL Task with the query stored in a variable. This variable is further part of the package configuration, so that I can change the query without changing the package itself. Now, this all works fine, until I made a typo in the query causing the syntax to be invalid, ie changing SELECT to SELCET. Now, one would expect the package to fail, and it does, if I run it through the debugger in Visual Studio, but when I run it using dtexec it just hangs and I have to kill the process using the Task Manager.

Peculiarly, I tried doing the same thing with a task that was not contained in an EventHandler, and then the package fails as expected, both when running it in the debugger and using dtexec. This is not a major problem, but what I am afraid of is that one day the database server will be down and cause an error in the Execute SQL Task in the EventHandler, causing dtexec to hang indefinitely. Has anyone else had problems like these?

Regards,
Lars

That's a really interesting observation Lars. I'd certainly be very worried about this happening as well. It might be worth logging as a bug.

I've certainly noticed dtexec appearing to hang but have never managed to track down why. Perhaps this was the reason.

-Jamie

|||

I finally managed to track down the problem. It is described here: http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.standardoutput.aspx and it is the deadlock that arises when reading both standard input and standard error after eachoter. In other words, this was related to my poor .NET skills and not a problem within SSIS.

Regards,
Lars

DTC problem with SQL 2005 on Server 2003

I'm getting a strange error trying to run a distributed transaction between 2
SQL 2005 machines.
The error reads
'OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message
"No transaction is active"'
'Msg 7391, Level 16, State 2, Line 2'
'The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "RemoteServer" was unable to begin a distributed transaction"
The query that I'm trying to run is very simple. The table A exists on both
machines
BEGIN TRANSACTION
INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')
INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Local')
COMMIT TRANSACTION
DTC is running on both machines and I've configured it to allow network
access, remote clients, remote administration, Inbound and Outbound
communications(Component Services->My Computer->DTC)
The linked server uses a specific security context and allows data access,
rpc and rpc out.
Both the inserts run fine by themselves. It's only when run together in a
transaction that they break.
Has anyone got a solution, or suggestion please?
Thanks.
Gail Shaw (MCSD)
http://gail.rucus.net/
As an off the wall possibility or two, can the two servers see each other and
do the SS service accounts have the 'right' os level privileges?
I have seen this type of situation when there are network firewall and
similar issuses/interferences present.
Joseph R.P. Maloney, CSP,CCP,CDP
"GilaMonster" wrote:

> I'm getting a strange error trying to run a distributed transaction between 2
> SQL 2005 machines.
> The error reads
> 'OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message
> "No transaction is active"'
> 'Msg 7391, Level 16, State 2, Line 2'
> 'The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "RemoteServer" was unable to begin a distributed transaction"
> The query that I'm trying to run is very simple. The table A exists on both
> machines
> BEGIN TRANSACTION
> INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')
> INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Local')
> COMMIT TRANSACTION
> DTC is running on both machines and I've configured it to allow network
> access, remote clients, remote administration, Inbound and Outbound
> communications(Component Services->My Computer->DTC)
> The linked server uses a specific security context and allows data access,
> rpc and rpc out.
> Both the inserts run fine by themselves. It's only when run together in a
> transaction that they break.
> Has anyone got a solution, or suggestion please?
> Thanks.
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/
|||The two machines can see each other fine. They're part of a replication
scenario I'm testing. The remote query works fine if run alone, it's only
when the two are run together that there's a problem.
DTC is running as the Network Service account. Both SQL services are running
as local admin.
I don't think there's a firewall. Does Server 2003 have a firewall
automatically on? There's no fancy hardware between them, just a 100Mbit hub.
Thanks
Gail Shaw (MCSD)
http://gail.rucus.net/
|||When is someone going to post an answer to this question? I am having the
same problem and can't find an answer anywhere.
Please, Microsoft, answer this question!
Corey Young
"GilaMonster" wrote:

> The two machines can see each other fine. They're part of a replication
> scenario I'm testing. The remote query works fine if run alone, it's only
> when the two are run together that there's a problem.
> DTC is running as the Network Service account. Both SQL services are running
> as local admin.
> I don't think there's a firewall. Does Server 2003 have a firewall
> automatically on? There's no fancy hardware between them, just a 100Mbit hub.
> Thanks
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/
>
>

DTC problem with SQL 2005 on Server 2003

I'm getting a strange error trying to run a distributed transaction between
2
SQL 2005 machines.
The error reads
'OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message
"No transaction is active"'
'Msg 7391, Level 16, State 2, Line 2'
'The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "RemoteServer" was unable to begin a distributed transaction"
The query that I'm trying to run is very simple. The table A exists on both
machines
BEGIN TRANSACTION
INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')
INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Local')
COMMIT TRANSACTION
DTC is running on both machines and I've configured it to allow network
access, remote clients, remote administration, Inbound and Outbound
communications(Component Services->My Computer->DTC)
The linked server uses a specific security context and allows data access,
rpc and rpc out.
Both the inserts run fine by themselves. It's only when run together in a
transaction that they break.
Has anyone got a solution, or suggestion please?
Thanks.
--
Gail Shaw (MCSD)
http://gail.rucus.net/As an off the wall possibility or two, can the two servers see each other an
d
do the SS service accounts have the 'right' os level privileges?
I have seen this type of situation when there are network firewall and
similar issuses/interferences present.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"GilaMonster" wrote:

> I'm getting a strange error trying to run a distributed transaction betwee
n 2
> SQL 2005 machines.
> The error reads
> 'OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned messa
ge
> "No transaction is active"'
> 'Msg 7391, Level 16, State 2, Line 2'
> 'The operation could not be performed because OLE DB provider "SQLNCLI" fo
r
> linked server "RemoteServer" was unable to begin a distributed transaction
"
> The query that I'm trying to run is very simple. The table A exists on bot
h
> machines
> BEGIN TRANSACTION
> INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')
> INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Local')
> COMMIT TRANSACTION
> DTC is running on both machines and I've configured it to allow network
> access, remote clients, remote administration, Inbound and Outbound
> communications(Component Services->My Computer->DTC)
> The linked server uses a specific security context and allows data access,
> rpc and rpc out.
> Both the inserts run fine by themselves. It's only when run together in a
> transaction that they break.
> Has anyone got a solution, or suggestion please?
> Thanks.
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/|||The two machines can see each other fine. They're part of a replication
scenario I'm testing. The remote query works fine if run alone, it's only
when the two are run together that there's a problem.
DTC is running as the Network Service account. Both SQL services are running
as local admin.
I don't think there's a firewall. Does Server 2003 have a firewall
automatically on? There's no fancy hardware between them, just a 100Mbit hub
.
Thanks
Gail Shaw (MCSD)
http://gail.rucus.net/|||When is someone going to post an answer to this question? I am having the
same problem and can't find an answer anywhere.
Please, Microsoft, answer this question!
Corey Young
"GilaMonster" wrote:

> The two machines can see each other fine. They're part of a replication
> scenario I'm testing. The remote query works fine if run alone, it's only
> when the two are run together that there's a problem.
> DTC is running as the Network Service account. Both SQL services are runni
ng
> as local admin.
> I don't think there's a firewall. Does Server 2003 have a firewall
> automatically on? There's no fancy hardware between them, just a 100Mbit h
ub.
> Thanks
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/
>
>

DTC problem with SQL 2005 on Server 2003

I'm getting a strange error trying to run a distributed transaction between 2
SQL 2005 machines.
The error reads
'OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message
"No transaction is active"'
'Msg 7391, Level 16, State 2, Line 2'
'The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "RemoteServer" was unable to begin a distributed transaction"
The query that I'm trying to run is very simple. The table A exists on both
machines
BEGIN TRANSACTION
INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')
INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Local')
COMMIT TRANSACTION
DTC is running on both machines and I've configured it to allow network
access, remote clients, remote administration, Inbound and Outbound
communications(Component Services->My Computer->DTC)
The linked server uses a specific security context and allows data access,
rpc and rpc out.
Both the inserts run fine by themselves. It's only when run together in a
transaction that they break.
Has anyone got a solution, or suggestion please?
Thanks.
--
Gail Shaw (MCSD)
http://gail.rucus.net/As an off the wall possibility or two, can the two servers see each other and
do the SS service accounts have the 'right' os level privileges?
I have seen this type of situation when there are network firewall and
similar issuses/interferences present.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"GilaMonster" wrote:
> I'm getting a strange error trying to run a distributed transaction between 2
> SQL 2005 machines.
> The error reads
> 'OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message
> "No transaction is active"'
> 'Msg 7391, Level 16, State 2, Line 2'
> 'The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "RemoteServer" was unable to begin a distributed transaction"
> The query that I'm trying to run is very simple. The table A exists on both
> machines
> BEGIN TRANSACTION
> INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')
> INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Local')
> COMMIT TRANSACTION
> DTC is running on both machines and I've configured it to allow network
> access, remote clients, remote administration, Inbound and Outbound
> communications(Component Services->My Computer->DTC)
> The linked server uses a specific security context and allows data access,
> rpc and rpc out.
> Both the inserts run fine by themselves. It's only when run together in a
> transaction that they break.
> Has anyone got a solution, or suggestion please?
> Thanks.
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/|||The two machines can see each other fine. They're part of a replication
scenario I'm testing. The remote query works fine if run alone, it's only
when the two are run together that there's a problem.
DTC is running as the Network Service account. Both SQL services are running
as local admin.
I don't think there's a firewall. Does Server 2003 have a firewall
automatically on? There's no fancy hardware between them, just a 100Mbit hub.
Thanks
--
Gail Shaw (MCSD)
http://gail.rucus.net/|||When is someone going to post an answer to this question? I am having the
same problem and can't find an answer anywhere.
Please, Microsoft, answer this question!
--
Corey Young
"GilaMonster" wrote:
> The two machines can see each other fine. They're part of a replication
> scenario I'm testing. The remote query works fine if run alone, it's only
> when the two are run together that there's a problem.
> DTC is running as the Network Service account. Both SQL services are running
> as local admin.
> I don't think there's a firewall. Does Server 2003 have a firewall
> automatically on? There's no fancy hardware between them, just a 100Mbit hub.
> Thanks
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/
>
>

DTC Problem - Unable to begin a transaction

I have a strange one. I have 2 Win2K3 servers, one running SQL 2K5 and the
other 2K SP4. From the 2K5 SQL Server I issue the following query ...
declare @.tmpStr varchar(2000);
declare @.ib table (EventType nvarchar(30), Parameters int, EventInfo
nvarchar(255));
set @.tmpstr = 'exec(''dbcc inputbuffer(71) WITH NO_INFOMSGS'') at
[SQL200Server];';
insert into @.ib
exec (@.tmpStr);
and I get the following error messages ...
OLE DB provider "SQLNCLI" for linked server "SQL2000Server" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "SQL2000Server" was unable to begin a distributed transaction.
If I simply comment out the 'insert into @.ib' line, it works fine. We
apparently still have something misconfigured in DTC between the servers, bu
t
I can't figure out what? Any ideas anyone? Thanks in advance.Arghknork wrote:
> If I simply comment out the 'insert into @.ib' line, it works fine. We
> apparently still have something misconfigured in DTC between the servers,
but
> I can't figure out what? Any ideas anyone? Thanks in advance.
>
Network access to MSDTC is disabled by default on a Windows 2003 Server
machine.
http://blogs.msdn.com/mab/archive/2.../30/508273.aspx|||Arghknork wrote:
> If I simply comment out the 'insert into @.ib' line, it works fine. We
> apparently still have something misconfigured in DTC between the servers,
but
> I can't figure out what? Any ideas anyone? Thanks in advance.
>
Network access to MSDTC is disabled by default on a Windows 2003 Server
machine.
http://blogs.msdn.com/mab/archive/2.../30/508273.aspx|||True, and we have it enabled. This has to be something more subtile than
that. Both these machines participate in other DTC activity with other
servers, they just don't want to play nice together in this one specific
incident. As I mentioned, I can comment out the onbe line and get a correct
response. It's only when I try to return the result set back into a table
variable that I get the failure. Thanks for the response.
"Tracy McKibben" wrote:

> Arghknork wrote:
> Network access to MSDTC is disabled by default on a Windows 2003 Server
> machine.
> http://blogs.msdn.com/mab/archive/2.../30/508273.aspx
>|||True, and we have it enabled. This has to be something more subtile than
that. Both these machines participate in other DTC activity with other
servers, they just don't want to play nice together in this one specific
incident. As I mentioned, I can comment out the onbe line and get a correct
response. It's only when I try to return the result set back into a table
variable that I get the failure. Thanks for the response.
"Tracy McKibben" wrote:

> Arghknork wrote:
> Network access to MSDTC is disabled by default on a Windows 2003 Server
> machine.
> http://blogs.msdn.com/mab/archive/2.../30/508273.aspx
>

DTC Problem - Unable to begin a transaction

I have a strange one. I have 2 Win2K3 servers, one running SQL 2K5 and the
other 2K SP4. From the 2K5 SQL Server I issue the following query ...
declare @.tmpStr varchar(2000);
declare @.ib table (EventType nvarchar(30), Parameters int, EventInfo
nvarchar(255));
set @.tmpstr = 'exec(''dbcc inputbuffer(71) WITH NO_INFOMSGS'') at
[SQL200Server];';
insert into @.ib
exec (@.tmpStr);
and I get the following error messages ...
OLE DB provider "SQLNCLI" for linked server "SQL2000Server" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "SQL2000Server" was unable to begin a distributed transaction.
If I simply comment out the 'insert into @.ib' line, it works fine. We
apparently still have something misconfigured in DTC between the servers, but
I can't figure out what? Any ideas anyone? Thanks in advance.Arghknork wrote:
> If I simply comment out the 'insert into @.ib' line, it works fine. We
> apparently still have something misconfigured in DTC between the servers, but
> I can't figure out what? Any ideas anyone? Thanks in advance.
>
Network access to MSDTC is disabled by default on a Windows 2003 Server
machine.
http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx|||True, and we have it enabled. This has to be something more subtile than
that. Both these machines participate in other DTC activity with other
servers, they just don't want to play nice together in this one specific
incident. As I mentioned, I can comment out the onbe line and get a correct
response. It's only when I try to return the result set back into a table
variable that I get the failure. Thanks for the response.
"Tracy McKibben" wrote:
> Arghknork wrote:
> > If I simply comment out the 'insert into @.ib' line, it works fine. We
> > apparently still have something misconfigured in DTC between the servers, but
> > I can't figure out what? Any ideas anyone? Thanks in advance.
> >
> >
> Network access to MSDTC is disabled by default on a Windows 2003 Server
> machine.
> http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx
>

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!