SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008, Unclosed quotation mark after the character string ' COMMIT TRAN'.


SQL 2008, Unclosed quotation mark after the character string ' COMMIT TRAN'.

Author
Message
tksqqo
tksqqo
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 31
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18452 Visits: 20432
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
tksqqo
tksqqo
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 31
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16234 Visits: 19550
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
tksqqo
tksqqo
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 31
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 ....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search