Computed Column With Index Issue

Hello,
I have a table with a computed column TransPerKey, which is based on two
other columns: TransYear and TransMonth:
(CONVERT([char](4),[TransYear],(0))+case when len([transmonth])=
(1) then
'0'+CONVERT([varchar](2),[TransMonth],(0)) else
CONVERT([varchar](2),[TransMonth],(0)) end)
I have put an index on this column, and updates, deletes, etc. work fine if
done from within SSSMS. However, when I try to do updates, deletes, etc. fro
m
an MS Access (2003) front end via a function which runs a stored proc on the
server, I get an error:
Insert (or Delete) failed because the following SET options have incorrect
settings: "ANSI_NULLS, QUOTED_IDENTIFIER'......
I have dropped and recreated the table and index using the set options, but
still get the error. Since I don't get this error in SSMS, I have to assume
this is not the problem.
The MS Access function:
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
If OpenAdoCon = True Then
Set cmd.ActiveConnection = adoCon
End If
cmd.CommandText = "Marketing.spRunSQL"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 5
Set param1 = cmd.CreateParameter("@.strsql", adVarChar, adParamInput, 8000)
param1.Value = strsql
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("@.rowsRtn", adInteger, adParamOutput)
cmd.Parameters.Append param2
cmd.Execute
RunSql = Nz(cmd(1), 0)
The sp simply executes a sql statement passed in via the above, e.g.:
DELETE FROM
Customer.BINTransactions
WHERE
(TransMonth = 1)
AND (TransYear = 2007)
and here is the sp:
ALTER PROCEDURE [Marketing].[spRunSQL]
--run sql statement; no identity or any result except for rows affected.
@.strsql NVARCHAR(2500),
@.rowsRtn INT = NULL OUTPUT
AS
SET NOCOUNT ON ;
EXECUTE sp_executesql @.strsql
SET @.rowsRtn = @.@.ROWCOUNT
The connection for the Access function:
Public Const adoConStr As String = "PROVIDER=SQLOLEDB;DATA SOURCE=" &
svrName & _
";UID=AllpointMgmtUser;PWD=xxx;DATABASE=" & sqlDBName &
";APP=Allpoint2007"
Public adoCon As ADODB.Connection
If I remove the index, everything works properly.
The only thing I can figure at this point is that there's something going on
with the ado connection?
Any help would be greatly appreciated.Hello,
When you have ANSI_WARNINGS in effect (by default), you can put SET
ARITHABORT ON in the SP itself. However, this will cause a recompilation
each time you the procedure, so this can be costly. If you want to go this
route, you may want to consider demote the procedure code to a new
procedure, and let the old procedure be a wrapper that calls the new
procedure after having executed SET ARITHABORT ON.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter, thanks for the reply.
If I understand you, adding SET ARITHABORT ON to the sp should eliminate the
error?
For testing purposes, I did this, however it did not make any difference.
Here's what the sp looks like:
ALTER PROCEDURE [Marketing].[spRunSQL]
--run sql statement; no identity or any result except for rows affected.
@.strsql NVARCHAR(2500),
@.rowsRtn INT = NULL OUTPUT
AS
SET NOCOUNT ON ;
SET ANSI_WARNINGS ON ;
SET ANSI_NULLS ON ;
SET ARITHABORT ON ;
SET QUOTED_IDENTIFIER ON ;
EXECUTE sp_executesql @.strsql
SET @.rowsRtn = @.@.ROWCOUNT
Am I missing something?
""Peter Yang[MSFT]"" wrote:

> Hello,
> When you have ANSI_WARNINGS in effect (by default), you can put SET
> ARITHABORT ON in the SP itself. However, this will cause a recompilation
> each time you the procedure, so this can be costly. If you want to go this
> route, you may want to consider demote the procedure code to a new
> procedure, and let the old procedure be a wrapper that calls the new
> procedure after having executed SET ARITHABORT ON.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello,
I wasn't able to reproduce the issue on my side. Did you set the proper
options when you creating the options?
I use the following steps to test on a mdb file in Access 2007/sql 2005
machine.
1. Use the script below to create table/index/SP
=====
use tempdb
go
set
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS on
set NUMERIC_ROUNDABORT off
go
create table set02 ( TransYear int, transmonth int, c1 as
(CONVERT([char](4),[TransYear],(0))+case when len([transmonth])=
(1) then
'0'+CONVERT([varchar](2),[TransMonth],(0)) else
CONVERT([varchar](2),[TransMonth],(0)) end))
create index icci2 on set02(c1)
insert set02 values (1999, 2)
create PROCEDURE sprunsql
--run sql statement; no identity or any result except for rows affected.
@.strsql NVARCHAR(2500),
@.rowsRtn INT = NULL OUTPUT
AS
SET NOCOUNT ON ;
--SET ANSI_WARNINGS ON ;
--SET ANSI_NULLS ON ;
--SET ARITHABORT ON ;
--SET QUOTED_IDENTIFIER ON ;
--Set NUMERIC_ROUNDABORT off;
EXECUTE sp_executesql @.strsql
SET @.rowsRtn = @.@.ROWCOUNT
=====
2. Create a function in a module:
Function set01()
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim strSQL As String
strSQL = "insert set02 values (2000, 4)"
Dim adoConStr As String
adoConStr = "PROVIDER=SQLOLEDB;DATA SOURCE=" & "sql2005sp2" & _
";UID=sa;PWD=Password;DATABASE=" & "tempdb"
Dim adocon As New ADODB.Connection
adocon.ConnectionString = adoConStr
adocon.Open
Set cmd.ActiveConnection = adocon
cmd.CommandText = "sprunsql"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 5
Set param1 = cmd.CreateParameter("@.strsql", adVarChar, adParamInput,
8000)
param1.Value = strSQL
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("@.rowsRtn", adInteger, adParamOutput)
cmd.Parameters.Append param2
cmd.Execute
adocon.Close
End Function
3. Run the function and it doesn't return any error.
Will you check if above steps work on your side?
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Am I missing something?
To add to Peter's response, here's an important note from the Books Online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/10f66b71-9241-4a3a
-9292-455ae7252565.htm">
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
ANSI_NULLS settings are captured and used for subsequent invocations of that
stored procedure.
</Excerpt>
Because these options are used during parsing, they must be turned on before
the procedure is created rather than during execution. There is no need to
set these in the procedure code.
Hope this helps.
Dan Guzman
SQL Server MVP
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:66E41049-72A8-4CF8-AA03-D791FF0B0C6C@.microsoft.com...[vbcol=seagreen]
> Peter, thanks for the reply.
> If I understand you, adding SET ARITHABORT ON to the sp should eliminate
> the
> error?
> For testing purposes, I did this, however it did not make any difference.
> Here's what the sp looks like:
> ALTER PROCEDURE [Marketing].[spRunSQL]
> --run sql statement; no identity or any result except for rows affected.
> @.strsql NVARCHAR(2500),
> @.rowsRtn INT = NULL OUTPUT
> AS
> SET NOCOUNT ON ;
> SET ANSI_WARNINGS ON ;
> SET ANSI_NULLS ON ;
> SET ARITHABORT ON ;
> SET QUOTED_IDENTIFIER ON ;
> EXECUTE sp_executesql @.strsql
> SET @.rowsRtn = @.@.ROWCOUNT
> Am I missing something?
> ""Peter Yang[MSFT]"" wrote:
>|||Dan, that was the problem. I dropped and re-created the sp with the set
options:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_WARNINGS ON
go
and it works fine now. Thanks for your help.
"Dan Guzman" wrote:

> To add to Peter's response, here's an important note from the Books Online
:
> <Excerpt
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/10f66b71-9241-4a
3a-9292-455ae7252565.htm">
> When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
> ANSI_NULLS settings are captured and used for subsequent invocations of th
at
> stored procedure.
> </Excerpt>
> Because these options are used during parsing, they must be turned on befo
re
> the procedure is created rather than during execution. There is no need t
o
> set these in the procedure code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:66E41049-72A8-4CF8-AA03-D791FF0B0C6C@.microsoft.com...
>sqlsql