I run the below on the box that has psExec installed as sa (dbo) in QA.
The MSSQLSERVER service is set to Local Account.
Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
from a CmdExec job sucessfully. I run same from command prompt successfully.
Do I have to set MSSQLSERVER agent to start as admin.?
Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
similar?
Even our DBA is stumped!!
EXEC xp_cmdshell 'c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat' ;
output
---
NULL
PsExec v1.60 - Execute processes remotely
Copyright (C) 2001-2005 Mark Russinovich
Sysinternals - www.sysinternals.com
NULL
Access is denied.
Connecting to 10.2.27.230...
Couldn't access 10.2.27.230:
NULL
It looks like psExec doesn't have permission but it is SQL QA that is the
sourcer of the problem.Local Account? or Local System? The SQL Server service must execute with
the credentials needed to execute commands on the other computer. It cannot
execute as Local System. It must execute as a domain user account.
WARNING!!! It is extremely dangerous to use xp_cmdshell in this way. It is
even more dangerous to run as a domain admin. If you're a consultant, be
sure you have a battalion of lawyers in your pocket and review your
professional insurance contract, because it would be a simple thing for a
smart lawyer to prove gross negligence on your part.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:642C7D5B-DA48-427D-9535-0BF66E90ADA4@.microsoft.com...
> I run the below on the box that has psExec installed as sa (dbo) in QA.
> The MSSQLSERVER service is set to Local Account.
> Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
> from a CmdExec job sucessfully. I run same from command prompt
successfully.
> Do I have to set MSSQLSERVER agent to start as admin.?
> Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
> similar?
> Even our DBA is stumped!!
> EXEC xp_cmdshell 'c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat' ;
> output
> ---
> NULL
> PsExec v1.60 - Execute processes remotely
> Copyright (C) 2001-2005 Mark Russinovich
> Sysinternals - www.sysinternals.com
> NULL
> Access is denied.
> Connecting to 10.2.27.230...
>
> Couldn't access 10.2.27.230:
> NULL
> It looks like psExec doesn't have permission but it is SQL QA that is the
> sourcer of the problem.|||thx again, what are the alternatives, create an account and lock it down,
maybe? or
"Brian Selzer" wrote:
> Local Account? or Local System? The SQL Server service must execute with
> the credentials needed to execute commands on the other computer. It cann
ot
> execute as Local System. It must execute as a domain user account.
> WARNING!!! It is extremely dangerous to use xp_cmdshell in this way. It i
s
> even more dangerous to run as a domain admin. If you're a consultant, be
> sure you have a battalion of lawyers in your pocket and review your
> professional insurance contract, because it would be a simple thing for a
> smart lawyer to prove gross negligence on your part.
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:642C7D5B-DA48-427D-9535-0BF66E90ADA4@.microsoft.com...
> successfully.
>
>|||Create an external process to do whatever it is you need to do. Then you
don't have to worry about the xp_cmdshell security hole. Another solution
is to use replication, but if you can't, then you can queue up the
information in a table and create a dequeue process to move it. Both of
these solutions is more robust, because you can still collect information if
the other system is down.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:97BD8606-7F5A-4904-B13C-533FE167A77D@.microsoft.com...
> thx again, what are the alternatives, create an account and lock it down,
> maybe? or
> "Brian Selzer" wrote:
>
with
cannot
is
be
a
QA.
cmdmgr_Resume.bat
or
the|||On Wed, 7 Sep 2005 05:46:10 -0700, marcmc wrote:
>I run the below on the box that has psExec installed as sa (dbo) in QA.
>The MSSQLSERVER service is set to Local Account.
>Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
>from a CmdExec job sucessfully. I run same from command prompt successfully
.
>Do I have to set MSSQLSERVER agent to start as admin.?
>Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
>similar?
>Even our DBA is stumped!!
Hi marcmc,
(S)he should check out what BOL says about services accounts.
If you really want to do it like this, than the least unsafe way is to:
a) Have your network administrator set up a domain account for use by
the SQL Server service. Make sure to change the password policy to not
automatically age the password.
b) Give this account all privileges required for SQL Server operation,
plus all privileges required to run your batch file. Don't give any
other privileges to this account. If your version of windows allows it,
disable the posibility to log on using this account as a normal user
(i.e. make it an account that can only be used by services)
c) Change the startup parameters for your SQL Server instance to log in
using this account instead of logging in as Local System.
d) Keep your fingers crossed :-)
But I agree with Brian that there are other ways to do this, and that
they are probably better!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
On delete trigger for a view
Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview]
FOR DELETE
AS
Declare @.user_name sysname, @.msg varchar(3000)
select @.user_name = name
from deleted
set @.msg = 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur)
values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You cannot create a FOR trigger (now known as an AFTER trigger) on a =view. You can create an INSTEAD OF trigger on a view, however.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roust_m" <roustam@.hotbox.ru> wrote in message =news:a388fd78.0309300241.4462ca59@.posting.google.com...
Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name sysname, @.msg varchar(3000) select @.user_name =3D name from deleted
set @.msg =3D 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur) values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You cannot create a FOR trigger (now =known as an AFTER trigger) on a view. You can create an INSTEAD OF trigger on =a view, however.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roust_m" wrote in message news:a388fd=78.0309300241.4462ca59@.posting.google.com...Hi!I have a view defined in MANAGE database as:CREATE VIEW dbo.sysdatabasesview ASSELECT *FROM master.dbo.sysdatabases WITH (nolock)GOTrying to place a =trigger on it:CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name =sysname, @.msg varchar(3000) select @.user_name =3D name from deleted =set @.msg =3D 'Delete database ' + @.user_name + ' on server ' =+@.@.servername + ' from host ' + host_name()insert into MANAGE..MAIL (recipient, =subject, message, occur) values ('myemail@.domain.local', ='Delete datadase', @.msg, getdate()) Get an error:Error =208: Invalid object name 'dbo.sysdatabasesview'What is wrong?Thanks.
--=_NextPart_000_0012_01C3872D.EA0155E0--sql
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview]
FOR DELETE
AS
Declare @.user_name sysname, @.msg varchar(3000)
select @.user_name = name
from deleted
set @.msg = 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur)
values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You cannot create a FOR trigger (now known as an AFTER trigger) on a =view. You can create an INSTEAD OF trigger on a view, however.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roust_m" <roustam@.hotbox.ru> wrote in message =news:a388fd78.0309300241.4462ca59@.posting.google.com...
Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name sysname, @.msg varchar(3000) select @.user_name =3D name from deleted
set @.msg =3D 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur) values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You cannot create a FOR trigger (now =known as an AFTER trigger) on a view. You can create an INSTEAD OF trigger on =a view, however.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roust_m"
--=_NextPart_000_0012_01C3872D.EA0155E0--sql
on delete cascade & hierarchical table
for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE
CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE
but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under
thank you for helpingI tried running your code on SQL Server 2005, and got the following message:
Introducing FOREIGN KEY constraint 'FK_Users_Sup' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
In my opinion, this error message tells pretty well why you are not allowed to use on delete cascade on a self-join.|||You will need to implement this cascading referential integrity using a trigger.
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE
CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE
but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under
thank you for helpingI tried running your code on SQL Server 2005, and got the following message:
Introducing FOREIGN KEY constraint 'FK_Users_Sup' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
In my opinion, this error message tells pretty well why you are not allowed to use on delete cascade on a self-join.|||You will need to implement this cascading referential integrity using a trigger.
Wednesday, March 7, 2012
OLE DB error - Please Help
OLE DB error
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmoun t,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmoun t,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
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].
Gerald Blackwell
gblackwell@.alliedhomenet.com
I cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmoun t,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmoun t,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
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].
Gerald Blackwell
gblackwell@.alliedhomenet.com
I cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.
OLE DB error - Please Help
OLE DB error
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
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].
Gerald Blackwell
gblackwell@.alliedhomenet.comI cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
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].
Gerald Blackwell
gblackwell@.alliedhomenet.comI cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Subscribe to:
Posts (Atom)