Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008, Unclosed quotation mark after the character string ' COMMIT TRAN'. Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 3:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 2:07 AM
Points: 39, Visits: 22
ALTER PROC [dbo].[usp_UploadSantam] (@ImportOriginator varchar (50))
AS
DECLARE @Headers VARCHAR(1000)
DECLARE @x VARCHAR(8000)
DECLARE @Maxrows int
DECLARE @RowCount int
DECLARE @branch varchar(50)
DECLARE @UploadID int
DECLARE @InsurerID tinyint
DECLARE @RecCount tinyint
DECLARE @PolicyNumber varchar(25)
DECLARE @Type char(3)
DECLARE @BrokerIDSecondary INT
DECLARE @Partnerid INT
DECLARE @Brokerid INT
DECLARE @SegmentID INT
DECLARE @PolicyID INT
DECLARE @CompanyName varchar (255)
DECLARE @ClientID int
DECLARE @BranchID int
DECLARE @AgentcodeData varchar(255)
DECLARE @AgentCodeID int
DECLARE @TransactionDateChar char(8)
DECLARE @TransactionDate datetime
DECLARE @EarningMonth int
DECLARE @EarningYear int
DECLARE @PremiumVested DECIMAL (12,4)
DECLARE @PremiumPolicyFee DECIMAL (12,4)
DECLARE @PremiumBase DECIMAL (12,4)
DECLARE @Commission DECIMAL (12,4)
DECLARE @CommissionPerc DECIMAL (12,4)
DECLARE @FullName varchar(255)
DECLARE @CompanyID INT
DECLARE @ProductID smallint
DECLARE @PortFolioID smallint
DECLARE @Sender varchar (100)
DECLARE @SystemID int
DECLARE @FiscalPeriodID int
--ROLL-UP


DECLARE @Table TABLE
([UploadID] [int] identity NOT NULL ,
[Branch] [int] ,
[AgentCode] [varchar] (255) ,
[PolicyNo] [varchar] (35) ,
[FullName] [varchar] (255),
[Premium] [decimal](15, 2) NULL ,
[PolicyFee] [decimal](15, 2) NULL ,
[Commission] [decimal](15, 2) NULL ,
[Earning Month] [int] NULL ,
[Earning Year] [int] NULL )

BEGIN
SET @Sender = 'usp_UploadSantam'

--usp_UploadSantam
--usp_UploadSBInvest

DELETE FROM tblMIS_UploadSantam WHERE PolicyNo IS NULL
-------------------------------------------------------------------------------------------------------
-- First validate Branch and agentcode

UPDATE tblMIS_UploadSantam
SET Branch = (case WHEN len(branch) = 1 THEN '00'+Branch
WHEN len(Branch) = 2 THEN '0'+ Branch
ELSE Branch END)

UPDATE tblMIS_UploadSantam
SET AgentCode = LTRIM(RTRIM(Branch)) + '/' + LTRIM(RTRIM(AgentCode))


UPDATE tblMIS_UploadSantam
SET Branch = T.BranchID
FROM tblMIS_AgentCodeTranslation T, tblMIS_UploadSantam U
WHERE T.AgentCode = U.AgentCode


-- ----now process individual records----------------------------------------------------------------------
SELECT @PartnerID = 1, @BrokerIDSecondary = 1, @SegmentID = 1, @ProductID = 1, @PortFolioID = 1, @SystemID = 3


--get brokerID from New Business Table
IF EXISTS (SELECT BrokerID
FROM tblMIS_BrokerNewBusiness
WHERE PolicyNumber = @PolicyNumber)
SELECT @BrokerID = BrokerID
FROM tblMIS_BrokerNewBusiness
WHERE PolicyNumber = @PolicyNumber
ELSE
SET @BrokerID = 1

INSERT INTO @Table (Branch, AgentCode,PolicyNo, FullName,
Premium, PolicyFee, Commission,
[Earning Month], [Earning Year] )
SELECT Branch, AgentCode,PolicyNo, FullName,
Sum(isnull(PremiumVested,0)), sum(isnull([Policy Fee],0)), sum(isnull(Commission,0)),
[Earning Month], [Earning Year]
FROM tblMIS_UploadSantam
GROUP BY Branch, AgentCode, PolicyNo, FullName, [Earning Month], [Earning Year]

Update @Table
set Premium = Premium+PolicyFee

SET @RowCount = 1
SELECT @MaxRows = count(1) FROM @Table

WHILE @RowCount <= @MaxRows
BEGIN
SELECT @Policynumber = PolicyNo,
@BranchID = Branch,
@FullName = FullName,
@AgentCodeData = AgentCode,
@PremiumVested = Premium,
@PremiumPolicyFee = PolicyFee,
@Commission = Commission,
@CommissionPerc = (case when premium <> 0 then (commission/premium)*100 else 0 end),
@EarningYear = [Earning Year],
@EarningMonth = [Earning Month]
FROM @Table
WHERE UploadID = @RowCount

SELECT @InsurerID =
CASE
WHEN SUBSTRING(@AgentcodeData,1,3) IN ('432','433','434','439','527') THEN 6
WHEN SUBSTRING(@AgentcodeData,1,3) IN ('225') THEN 58
ELSE 37
END


--get details for header record
SELECT @ClientID = 1
IF EXISTS (select ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName)
SELECT @ClientID = ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName
--create new client if necessary
IF @ClientID = 1
EXEC usp_InsertClient null, null, @FullName, @FullName, null, @FullName, NULL, NULL, @ClientID OUTPUT
--if header exists modify else insert
IF EXISTS (SELECT PolicyID FROM tblMIS_PolicyHead (NOLOCK) WHERE PolicyNumber = @PolicyNumber)
SELECT @Type = 'UPD'
ELSE
SELECT @Type = 'INS'
--process header record
EXEC usp_InsertPolicyHead
@Sender, @Type,@PolicyNumber, 3, null, @BrokerID,@PartnerID, @ClientID, 4, @SegmentID, null,
@InsurerID, @SystemID, @AgentCodeData, @ProductID, @PortFolioID, @PolicyID OUTPUT


--------------------------------------------------------------------------------------------------------
--get details for detail record
SELECT @TransactiondateChar = FiscalDateChar , @FiscalPeriodID = FiscalPeriodID,
@TransactionDate = FiscalDate FROM tblMIS_FiscalPeriods WHERE EarningYear = @EarningYear AND EarningMonth = @EarningMonth

IF EXISTS ( SELECT AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID)
SELECT @AgentCodeID = AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID
ELSE
SELECT @AgentCodeID = 1

IF isnull(@AgentCodeID,0) = 0
RAISERROR ('AgentCode does NOT EXIST' ,11,1,@AgentCodeData)


IF EXISTS (SELECT DetailID
FROM tblMIS_PolicyDetail
WHERE PolicyID = @PolicyID
AND FiscalPeriodID = @FiscalPeriodID
AND AgentCodeID = @AgentCodeID)
SELECT @Type = 'UPD'
ELSE

SELECT @Type = 'INS'

IF EXISTS ( SELECT PolicyNumber FROM tblMIS_BrokerNewBusiness WHERE PolicyNumber = @PolicyNumber)
SELECT @PremiumBase = PremiumGross FROM tblMIS_BrokerNewBusiness (NOLOCK) WHERE PolicyNumber = @PolicyNumber
ELSE
SELECT @PremiumBase = @PremiumVested

EXEC usp_InsertPolicyDetail
@Type, @PolicyID, @TransactionDate, @TransactionDateChar, @PremiumBase,
@PremiumVested, @PremiumPolicyFee, @Commission, @CommissionPerc, @AgentCodeID,
@BranchID, @InsurerID, @SystemID,@ImportOriginator

SET @RowCount = @RowCount + 1
END


DELETE FROM tblMIS_PolicyDetail
WHERE PremiumVestedIncl = 0
AND Commission = 0
AND PremiumPolicyFee=0

---santam direct have been unable to provide policy fee details
---since mar 2006. therefore we are copying the policyfee from march to
---subsequent months as a provision
-- update tblMIS_PolicyDetail
-- set PremiumPolicyFee = M.PremiumPolicyFee
-- from tblMIS_SantamDirectPFMar2006 M inner join
-- tblMIS_PolicyDetail on tblMIS_PolicyDetail.PolicyId = M.PolicyId
-- where TransactionDateChar = @TransactionDateChar

END


























Post #1470357
Posted Thursday, July 4, 2013 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 5,317, Visits: 12,354
What is this all about?

If your question is about SQL 2008, why not post it in that forum?

If your question is about T-SQL, why post it in an SSIS forum?

What is your question?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1470369
Posted Friday, July 5, 2013 2:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 2:07 AM
Points: 39, Visits: 22
I am trying to import a flat file but everytime it fails but I cant fine where is failing ... please help...
oh yah is T-Sql
Post #1470628
Posted Friday, July 5, 2013 3:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
How about posting the code for the stored procedures which are called from this one?

There's nothing in this stored procedure related to importing a flat file - how have you determined that it is this stored procedure which is causing the problem?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1470630
Posted Tuesday, July 30, 2013 2:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 2:07 AM
Points: 39, Visits: 22
Thank you guys, it took me a while but i found the problem. It was inside the stored procedure usp_InsertPolicyHead. There's stored procedure that runs inside usp_InsertPolicyHead ... that's where it failing ...

Thank you very much ....
Post #1478870
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse