insert error with computed column index

Question

Post reply

insert error with computed column index

BenWard

SSCertifiable

Points: 5903
More actions
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
----------------------------------------

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

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

harinerella Mr or Mrs. 500 Points: 548 More actions · Answer 1

harinerella

Mr or Mrs. 500

Points: 548

January 4, 2011 at 6:29 am

#1268890

Please provide the DDL of the the table.

BenWard SSCertifiable Points: 5903 More actions · Answer 2

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
----------------------------------------

harinerella Mr or Mrs. 500 Points: 548 More actions · Answer 3

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

BenWard SSCertifiable Points: 5903 More actions · Answer 4

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
----------------------------------------

BenWard SSCertifiable Points: 5903 More actions · Answer 5

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
----------------------------------------

LutzM SSC Guru Points: 107049 More actions · Answer 6

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]

BenWard SSCertifiable Points: 5903 More actions · Answer 7

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
----------------------------------------