RE: Cursor Not Running Correctly.. Need Help/Suggestions...
From: Robert Leonard III (RobertLeonardIII_at_discussions.microsoft.com)
Date: 08/09/04
- Next message: Alejandro Mesa: "RE: Error Handling"
- Previous message: Snake: "Batch input Multiple Files into ISQL"
- In reply to: John Bell: "RE: Cursor Not Running Correctly.. Need Help/Suggestions..."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 9 Aug 2004 09:28:24 -0700
I understand it isn't ideal, but it is what we have.. at the moment anyhow..
if it is a permission issue, which permissions would you think need to be
modified.. you are right in that I know I was using the "SA" password when
using the query analyzer.. but I'm not sure what account or permissions are
being used/checked when the trigger get's called.. the oddest part of it all
is that the first sections of the code work, it's just the looping mechanism
of the cursor that seems to fail..
Thanks
"John Bell" wrote:
> Hi
>
> It is not a good idea to use a trigger to send mail as this could severly
> increase the time your transactions are taking and therefore make your system
> unusable/unscaleable The most likely reasons you are having problems is to do
> with permissions or you are trying to use data that has not been commited.
>
> John
>
> "Robert Leonard III" wrote:
>
> > I'm a newbie here, so if this is an obvious problem with my code, please be
> > kind! :)
> >
> > Firstly, I am using SQL Server 2000 on a Windows 2000 Advanced Server...
> >
> > For some reason the following code works perfectly when run through Query
> > Analyzer, yet when I fire it off from a Trigger, the CURSOR portion of the
> > code doesn't appear to work.. the rest of the report works fine..
> >
> > Is there something I need to change to get this to work? The only
> > difference I can think of between running it as a Stored Procedure and
> > through Query Analyzer is that the Stored Procedure executes it from a
> > Trigger call.. When I moved this code into the trigger itself, rather than
> > calling the Stored Procedure from within the trigger, it failed the same
> > way. and that code also worked when pasted into the query analyzer.. I don't
> > get it! The @@Fetch_Status variable returns a -1 the first pass when run in
> > the "Non-Query Ananlyzer", ie, real, mode.. when I run that SAME code in
> > Query Analyzer it works perfectly..
> >
> > Any Idea?
> >
> > The Code is pasted Below..
> > Thanks!
> >
> > ---------------------------------------
> > CREATE PROCEDURE dbo.usp_SendNewWebOrder AS
> >
> >
> > EXEC master.dbo.xp_sendmail
> > @recipients = 'Preferred Customer Specialists',
> > @subject = 'New Web Order',
> > @message = 'A HTML File Containing New Web Order Information is
> > Attached. Please Open to retreive Information',
> > @attachments='NewWebOrder.html',
> > @attach_results = 'TRUE',
> > @width = 999,
> > @dbuse = 'iGoma',
> > @no_header='TRUE',
> > @query = '
> >
> >
> > SELECT ''<HTML><HEAD><TITLE>New Web Order</TITLE></HEAD>'',
> > ''<BODY>
> > <H1><CENTER>New Web Order</CENTER></H1>''
> >
> >
> > DECLARE @NewOrderID INT
> > Select @NewOrderID = OrderID from NewWebOrder
> >
> > DECLARE @CustName VARCHAR (30)
> > DECLARE @CompanyName VARCHAR (40)
> > DECLARE @AddressLine1 VARCHAR (80)
> > DECLARE @AddressLine2 VARCHAR (80)
> > DECLARE @City VARCHAR (20)
> > DECLARE @REGION VARCHAR (20)
> > DECLARE @PostCode VARCHAR (15)
> > DECLARE @Phone VARCHAR (15)
> > DECLARE @Extension VARCHAR (15)
> > DECLARE @Fax VARCHAR (15)
> > DECLARE @Country VARCHAR (30)
> >
> > /* Bill To Information */
> > SELECT ''<H3>Bill To Information</H3>''
> > SELECT @CustName = OrderAddresses.FirstName + '' '' +
> > OrderAddresses.LastName,
> > @CompanyName = OrderAddresses.Company,@AddressLine1 =
> > OrderAddresses.AddressLine1,
> > @AddressLine2 = OrderAddresses.AddressLine2, @City = OrderAddresses.City,
> > @Region = OrderAddresses.Region, @PostCode = OrderAddresses.PostalCode,
> > @Country = OrderAddresses.Country, @phone = OrderAddresses.Phone,
> > @Extension = OrderAddresses.Extension,
> > @Fax = OrderAddresses.Fax
> > FROM OrderAddresses
> > INNER JOIN Orders
> > ON OrderAddresses.OrderAddressID = Orders.BillToAddressID
> > WHERE (Orders.OrderID = @NewOrderID)
> >
> >
> > SELECT ''Customer Name: '', @CustName, ''<BR>'', ''Company Name: '',
> > @CompanyName, ''<BR>'', ''Billing Address Line1: '',
> > @AddressLine1, ''<BR>'', ''Billing Address Line2: '', @AddressLine2,
> > ''<BR>'', ''City: '', @City, ''<BR>'',''State/Region: '',
> > @Region, ''<BR>'', ''Postal Code: '', @PostCode, ''<BR>'', ''Country: '',
> > @Country, ''<BR>'',
> > ''Phone Number: '', @Phone, ''<BR>'', '' Extension: '', @Extension,
> > ''<BR>'', ''Fax Number: '', @Fax, ''<BR>''
> >
> >
> > /* Ship To Information */
> > SELECT ''<H3>Ship To Information</H3>''
> > SELECT @CustName = OrderAddresses.FirstName + '' '' +
> > OrderAddresses.LastName,
> > @CompanyName = OrderAddresses.Company,@AddressLine1 =
> > OrderAddresses.AddressLine1,
> > @AddressLine2 = OrderAddresses.AddressLine2, @City = OrderAddresses.City,
> > @Region = OrderAddresses.Region, @PostCode = OrderAddresses.PostalCode,
> > @Country = OrderAddresses.Country, @phone = OrderAddresses.Phone,
> > @Extension = OrderAddresses.Extension,
> > @Fax = OrderAddresses.Fax
> > FROM OrderAddresses
> > INNER JOIN Orders
> > ON OrderAddresses.OrderAddressID = Orders.ShipToAddressID
> > WHERE (Orders.OrderID = @NewOrderID)
> >
> >
> > SELECT ''Customer Name: '', @CustName, ''<BR>'', ''Company Name: '',
> > @CompanyName, ''<BR>'', ''Billing Address Line1: '',
> > @AddressLine1, ''<BR>'', ''Billing Address Line2: '', @AddressLine2,
> > ''<BR>'', ''City: '', @City, ''<BR>'',''State/Region: '',
> > @Region, ''<BR>'', ''Postal Code: '', @PostCode, ''<BR>'', ''Country: '',
> > @Country, ''<BR>'',
> > ''Phone Number: '', @Phone, ''<BR>'', '' Extension: '', @Extension,
> > ''<BR>'', ''Fax Number: '', @Fax, ''<BR>''
> >
> > SELECT ''<H3>Order Information</H3>''
> > DECLARE @OrderNo VARCHAR (15)
> > DECLARE @DateOrdered VARCHAR (30)
> > DECLARE @PONumber VARCHAR (10)
> > DECLARE @SubTotal MONEY
> > DECLARE @Tax Money
> > DECLARE @ShippingCharge MONEY
> > DECLARE @Total MONEY
> > DECLARE @ShipMethod VARCHAR (10)
> > DECLARE @TenderType VARCHAR (15)
> >
> > SELECT @OrderNo = Orders.OrderNo, @DateOrdered = Orders.DateOrdered,
> > @PONumber = Orders.PONumber,
> > @SubTotal = Orders.SubTotal, @ShipMethod = ShippingMethods.Description,
> > @TenderType = TenderTypes.Description
> > FROM Orders
> > INNER JOIN ShippingMethods
> > ON Orders.ShippingMethodID = ShippingMethods.ShippingMethodID
> > INNER JOIN TenderTypes
> > ON Orders.TenderTypeID = TenderTypes.TenderTypeID
> > WHERE (Orders.OrderID = @NewOrderID)
> >
> > SELECT ''Order Number: '', @OrderNo, ''<BR>'', ''Date of Order: '',
> > @DateOrdered, ''<BR>'',
> > ''PO Number: '', @PONumber, ''<BR>'', ''SubTotal: '', @SubTotal, ''<BR>'',
> > ''Tax: '', @Tax,
> > ''<BR>'', ''Shipping Charge: '', @ShippingCharge, ''<BR>'', ''Total: '',
> > @Total, ''<BR>'',
> > ''Shipping Method: '', @ShipMethod, ''<BR>'', ''Tender Type: '',
> > @TenderType, ''<BR>''
> >
> > SELECT ''<H3>Parts Ordered</H3>''
> > DECLARE @PartNum VARCHAR (80)
> > DECLARE @Quantity VARCHAR (10)
> > DECLARE @UnitCost MONEY
> > DECLARE @PartName VARCHAR (80)
> > DECLARE Parts CURSOR LOCAL
> > FORWARD_ONLY
> > FOR
> > SELECT Parts.PartNumber, Parts.PartName, OrderDetails.Quantity,
> > OrderDetails.UnitCost
> > FROM OrderDetails
> > INNER JOIN Parts
> > ON OrderDetails.PartID = Parts.PartID
> > INNER JOIN Orders
> > ON OrderDetails.OrderID = Orders.OrderID
> > WHERE (Orders.OrderID = @NewOrderID)
> > OPEN Parts
> > FETCH NEXT FROM Parts
> > INTO @PartNum, @PartName, @Quantity, @UnitCost
> > WHILE @@FETCH_STATUS=0
> > BEGIN
> > SELECT ''Part Number: '', @Partnum, ''<BR>'',
> > ''Part Name: '', @PartName, ''<BR>'',
> > ''Quantity: '', @Quantity, ''<BR>'',
> > ''Unit Cost: '', @UnitCost, ''<BR><BR>''
> > FETCH NEXT FROM Parts
> > INTO @PartNum, @PartName, @Quantity, @UnitCost
> > END
> > CLOSE Parts
> > DEALLOCATE Parts
> > SELECT ''</BODY></HTML>''
> > '
> > DROP TABLE NewWebOrder
> > GO
> >
> >
- Next message: Alejandro Mesa: "RE: Error Handling"
- Previous message: Snake: "Batch input Multiple Files into ISQL"
- In reply to: John Bell: "RE: Cursor Not Running Correctly.. Need Help/Suggestions..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
