Post reply

insert error with computed column index

  • January 4, 2011 at 5:37 am

    #229246

    Hi Guys,

    I have a computed column index on this table, and I'm getting a SET error on an insert. Any Ideas?

    SET DATEFORMAT 'dmy'

    SET ARITHABORT ON

    SET CONCAT_NULL_YIELDS_NULL ON

    SET QUOTED_IDENTIFIER ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    INSERT INTO tblImportDaily_ageukddf_hhi

    ( iFileSeqNo,sFileDate,sGlDate,sPolStartDate,sPolEndDate,sEffectiveDate,sTerminationDate,iGlTxnSeqNum,cElectraPolicy,cTransType,mPremiumExcIPT,mTotalCommission )

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','1','FF00001553850 ','NB','226.66','68'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','18/12/2010','19/12/2011','18/12/2010','','2','FF00001553852 ','NB','45.7','13.71'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','3','FF00001553853 ','NB','88.56','26.57'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','01/12/2010','01/12/2011','01/12/2010','','4','FF00001553854 ','NB','206.66','62'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','07/12/2010','07/12/2011','07/12/2010','','5','FF00001553855 ','NB','159.04','47.71'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','01/12/2010','01/12/2011','01/12/2010','','6','FF00001553856 ','NB','79.99','24'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','03/12/2010','03/12/2011','03/12/2010','','7','FF00001553857 ','NB','248.56','74.57'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','12/12/2010','12/12/2011','12/12/2010','','8','FF00001553858 ','NB','130.47','39.14'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','30/11/2010','30/11/2011','30/11/2010','','9','FF00001553859 ','NB','165.04','49.51'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','10','FF00001553860 ','NB','60.94','18.28'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','11','FF00001553861 ','NB','241.9','72.57'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','03/12/2010','03/12/2011','03/12/2010','','12','FF00001553862 ','NB','59.99','18'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','13','FF00001553863 ','NB','75.23','22.57'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','30/11/2010','30/11/2011','30/11/2010','','14','FF00001553864 ','NB','179.04','53.71'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','30/11/2010','30/11/2011','30/11/2010','','15','FF00001553865 ','NB','179.04','53.71'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','16','FF00001553866 ','NB','76.18','22.85'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','11/11/2010','11/11/2011','11/11/2010','','17','FF00001553867 ','NB','135.23','40.57'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','15/12/2010','15/12/2011','15/12/2010','','18','FF00001553868 ','NB','155.23','46.57'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','02/12/2010','02/12/2011','02/12/2010','','19','FF00001553871 ','NB','73.32','22'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','30/11/2010','30/11/2011','30/11/2010','','20','Not Available ','NB','156.35','46.91'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','06/12/2010','06/12/2011','06/12/2010','','21','FF00001553714 ','NB','181.9','54.57'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','22','FF00001553736 ','NB','165.7','49.71'

    UNION ALL

    SELECT 1,'01 Nov 2010 01:00:00','01/11/2010','29/11/2010','29/11/2011','29/11/2010','','23','FF00001553790 ','NB','278.09','83.43'

    error message on PHP:

    Warning: mssql_query(): message: INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (severity 16)

    this works fine through SSMS. I tried putting a 'GO' in after the set statements but that returns a syntax error in PHP too. I dont really know what im doing 🙁

    Any suggestions?

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 4, 2011 at 6:29 am

    #1268890

    Please provide the DDL of the the table.

  • January 4, 2011 at 7:06 am

    #1268921

    Thanks for reply.

    this what you're after?

    USE [KPI]

    GO

    /****** Object: Table [dbo].[tblImportDaily_ageukddf_hhi] Script Date: 01/04/2011 14:03:51 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[tblImportDaily_ageukddf_hhi](

    [iFileSeqNo] [int] NOT NULL,

    [sFileDate] [smalldatetime] NOT NULL,

    [bProcessedInd] [bit] NOT NULL CONSTRAINT [DF_tblImportDaily_ageukddf_hhi_bProcessedInd] DEFAULT ((0)),

    [sGlDate] [smalldatetime] NOT NULL,

    [sPolStartDate] [smalldatetime] NOT NULL,

    [sPolEndDate] [smalldatetime] NOT NULL,

    [sEffectiveDate] [smalldatetime] NOT NULL,

    [sTerminationDate] [smalldatetime] NOT NULL,

    [cGlMonth] AS ([dbo].[funMonthYear]([sGlDate])) PERSISTED NOT NULL,

    [iGlTxnSeqNum] [int] NOT NULL,

    [cElectraPolicy] [char](13) COLLATE Latin1_General_CI_AS NOT NULL,

    [cTransType] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,

    [mPremiumExcIPT] [money] NOT NULL,

    [mTotalCommission] [money] NOT NULL,

    [dWhenLoaded] [timestamp] NOT NULL,

    CONSTRAINT [PK_tblImportDaily_ageukddf_hhi] PRIMARY KEY CLUSTERED

    (

    [cGlMonth] ASC,

    [iGlTxnSeqNum] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    ([dbo].[funMonthYear]([sGlDate])) is a scalar function that returns a string like 'Jan-2010' based on a smalldatetime value.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 5, 2011 at 12:14 am

    #1269314

    Why don't you keep this code in stored procedure and test it? I assume that will work.

  • January 5, 2011 at 2:03 am

    #1269344

    Because I am inserting multiple rows that I find in a csv file and building te query dynamically. I could try the SP route but I'll be making 8000+ consecutive stored proc calls at a time, it may not be a bad thing given it will run only once per day but that seems an inefficient option to me.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 5, 2011 at 9:20 am

    #1269586

    Fixed it!

    Got the best of both worlds, I have created a stored procedure that takes a parameter of the SQL query to be executed. This means I can choose my SET options in the stored proc (seeing as mssql functions for php seem to ignore my set options)

    -- =============================================

    -- Author:Ben Ward

    -- Create date: 05/01/2011

    -- Description:get around the issue of set options not working in PHP

    -- =============================================

    CREATE PROCEDURE sprocRunSQLFromPHP

    -- Add the parameters for the stored procedure here

    @vCommand text

    AS

    BEGIN

    SET NOCOUNT ON

    SET DATEFORMAT 'dmy'

    SET ARITHABORT ON

    SET CONCAT_NULL_YIELDS_NULL ON

    SET QUOTED_IDENTIFIER ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    SET NUMERIC_ROUNDABORT OFF

    EXEC (@vCommand)

    END

    GO

    Then in my PHP program, instead of doing a simple mssql_query() I initialise a statement and bind the query.

    $sqlStatement = "INSERT INTO tblWithPCCIndex SELECT 1,2,3 UNION ALL SELECT 4,5,6";

    //set up that statement as a sproc binding

    $sqlStatementInit = mssql_init('sprocRunSQLFromPHP',$dbConn);

    mssql_bind($sqlStatementInit, '@vCommand', $sqlStatement, SQLTEXT, False, False);

    //Parse the sql statement

    if (mssql_execute($sqlStatementInit))

    {

    echo "Query execution Succeeded." . PHP_EOL;

    }

    Ta da! Issue resolved.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 5, 2011 at 9:41 am

    #1269598

    May I recommend a minor change?

    Change the data type of @vCommand in your sproc to NVARCHAR(MAX)?

    The data type text is marked as deprecated starting with SS2K5.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 6, 2011 at 2:13 am

    #1269932

    Hi Lutz,

    That is a good idea, however I tried that first & unfortunately the mssql_bind() command fell over every time with no helpful error description but text worked fine first time.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy