I read the Error Handling article by Erland Sommarskog, he said when
ANSI_WARNINGS is OFF, assigning a char column a value that exceeds the max
length of the column will not result any error. But I still get error 8152:
String or binary data would be truncated, within a trigger, even I set
ANSI_WARNINGS to OFF. Anybody knows why? Is it because of the Trigger Contex
t?hi
Just see this:
ANSI_WARNINGS
When set to ON, errors or warnings are issued when conditions such as
"divide by zero" occur or null values appear in aggregate functions. When se
t
to OFF, no warnings are raised when null values appear in aggregate
functions, and null values are returned when conditions such as "divide by
zero" occur. By default, ANSI_WARNINGS is OFF.
SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes on
computed columns or indexed views.
Connection-level settings (set using the SET statement) override the default
database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issu
e
a connection-level SET statement setting ANSI_WARNINGS to ON for the session
when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.
The status of this option can be determined by examining the
IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"nonno" wrote:
> I read the Error Handling article by Erland Sommarskog, he said when
> ANSI_WARNINGS is OFF, assigning a char column a value that exceeds the max
> length of the column will not result any error. But I still get error 8152
:
> String or binary data would be truncated, within a trigger, even I set
> ANSI_WARNINGS to OFF. Anybody knows why? Is it because of the Trigger Context?[/co
lor]|||Can you post a reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:A6D01321-73CE-4EE8-BACC-B8C2B9A08F06@.microsoft.com...
>I read the Error Handling article by Erland Sommarskog, he said when
> ANSI_WARNINGS is OFF, assigning a char column a value that exceeds the max
> length of the column will not result any error. But I still get error 8152
:
> String or binary data would be truncated, within a trigger, even I set
> ANSI_WARNINGS to OFF. Anybody knows why? Is it because of the Trigger Context?[/co
lor]|||thx 4 ur reply, Chandra
I know that Query Analyzer is connected thro' ODBC, but I get 0 from
IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function, and I'm
sure that the connection property 'Set ansi_warnings' is ON, any idea?
"Chandra" wrote:
> hi
> Just see this:
> ANSI_WARNINGS
> When set to ON, errors or warnings are issued when conditions such as
> "divide by zero" occur or null values appear in aggregate functions. When
set
> to OFF, no warnings are raised when null values appear in aggregate
> functions, and null values are returned when conditions such as "divide by
> zero" occur. By default, ANSI_WARNINGS is OFF.
> SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes
on
> computed columns or indexed views.
> Connection-level settings (set using the SET statement) override the defau
lt
> database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients is
sue
> a connection-level SET statement setting ANSI_WARNINGS to ON for the sessi
on
> when connecting to SQL Server. For more information, see SET ANSI_WARNINGS
.
> The status of this option can be determined by examining the
> IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "nonno" wrote:
>
Showing posts with label article. Show all posts
Showing posts with label article. Show all posts
Thursday, March 22, 2012
Sunday, February 26, 2012
Error 3154: The backup set holds a backup of a database other than the existing 'xx' database.
HELP!
This is happening with a restore to SS 2005 from a SS2000 backup. This article says to use the 'WITH REPLACE' clause. The SS2005 management studio does not seem to allow this via the GUI.
http://msdn2.microsoft.com/en-us/library/aa238217(sql.80).aspx
Did you use the overwrite flag in the GUI if the database already existed ?HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||True and you can use the Query Editior to restore the same using RESTORE DATABASE .... WITH REPLACE, as referred in BOL it is easy.|||asdaf sa gs ga sgasg asg g a
Error 3154: The backup set holds a backup of a database other than the existing 'xx' datab
HELP!
This is happening with a restore to SS 2005 from a SS2000 backup. This article says to use the 'WITH REPLACE' clause. The SS2005 management studio does not seem to allow this via the GUI.
http://msdn2.microsoft.com/en-us/library/aa238217(sql.80).aspx
Did you use the overwrite flag in the GUI if the database already existed ?HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||True and you can use the Query Editior to restore the same using RESTORE DATABASE .... WITH REPLACE, as referred in BOL it is easy.
Wednesday, February 15, 2012
Error 20068, Article Column Max 256
I'm trying to replicate a table that now has more than 256 columns. SQL2000
chokes when creating replication with error 20068, stating that the maximum
number of columns allowed for an article is 256.
The workarounds I am aware of involve creating two tables and using views to
abstract this change from the front end application. There is no way my
client will pay for a change as drastic as this with the only benifit is that
replication will now be allowed to work.
I have two questions:
a. Is there a better workaround?
b. Does SQL2005 solve this issue (I might be better able to sell my client
on an upgrade)?
partitioning the table as you describe is the standard workaround in SQL
Serever 2000.
In SQL Server 2005, for merge the max no of columns is 246. For
transactional and snapshot it is 1000 (995 with Oracle).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the application using this table uses a stored procedure to make the
updates you may be able to replicate the execution of the stored procedure -
however there is the same procedure limit. Another option is to use a
trigger to write to two "audit" tables and replicate these audit tables to
the subscriber where they in turn assemble the final row.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dtaylo75" <dtaylo75@.discussions.microsoft.com> wrote in message
news:EE67681F-C2FE-489B-88B1-1A69F719DE6D@.microsoft.com...
> I'm trying to replicate a table that now has more than 256 columns.
> SQL2000
> chokes when creating replication with error 20068, stating that the
> maximum
> number of columns allowed for an article is 256.
> The workarounds I am aware of involve creating two tables and using views
> to
> abstract this change from the front end application. There is no way my
> client will pay for a change as drastic as this with the only benifit is
> that
> replication will now be allowed to work.
> I have two questions:
> a. Is there a better workaround?
> b. Does SQL2005 solve this issue (I might be better able to sell my client
> on an upgrade)?
chokes when creating replication with error 20068, stating that the maximum
number of columns allowed for an article is 256.
The workarounds I am aware of involve creating two tables and using views to
abstract this change from the front end application. There is no way my
client will pay for a change as drastic as this with the only benifit is that
replication will now be allowed to work.
I have two questions:
a. Is there a better workaround?
b. Does SQL2005 solve this issue (I might be better able to sell my client
on an upgrade)?
partitioning the table as you describe is the standard workaround in SQL
Serever 2000.
In SQL Server 2005, for merge the max no of columns is 246. For
transactional and snapshot it is 1000 (995 with Oracle).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the application using this table uses a stored procedure to make the
updates you may be able to replicate the execution of the stored procedure -
however there is the same procedure limit. Another option is to use a
trigger to write to two "audit" tables and replicate these audit tables to
the subscriber where they in turn assemble the final row.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dtaylo75" <dtaylo75@.discussions.microsoft.com> wrote in message
news:EE67681F-C2FE-489B-88B1-1A69F719DE6D@.microsoft.com...
> I'm trying to replicate a table that now has more than 256 columns.
> SQL2000
> chokes when creating replication with error 20068, stating that the
> maximum
> number of columns allowed for an article is 256.
> The workarounds I am aware of involve creating two tables and using views
> to
> abstract this change from the front end application. There is no way my
> client will pay for a change as drastic as this with the only benifit is
> that
> replication will now be allowed to work.
> I have two questions:
> a. Is there a better workaround?
> b. Does SQL2005 solve this issue (I might be better able to sell my client
> on an upgrade)?
Error 1934 Severity 16 State 1
SQL Server 2000 Standard Sp3. on Windows 2003
What is Error 1934.
I can't find documentaiton on this anywhere. There's only one article in KB
in relation to merge replication. And other stuff on the web about ANSI
settings and indexed views (which I'm not using).
I get these errors in different contexts.
Here's one:
Profiler screenshot
http://www.vinnychi.com/vince/images/sql.jpg
This is a stored procedure that SQL Agent is running from a job. When i run
it this way, I get the 1934 error. If I run it from Query Analyzer I get no
error, and it runs to completion.
SP:
CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
AS
exec UTIL_DEFRAG
RETURN
========
UTIL_DEFRAG is:
CREATE procedure UTIL_DEFRAG
AS
TRUNCATE TABLE BLCSO
TRUNCATE TABLE IBFF_BOL_Temp
TRUNCATE TABLE IBFF_Container_Temp
TRUNCATE TABLE IBFFContainerImportPhase2
TRUNCATE TABLE JobOrderDoor_Temp
TRUNCATE TABLE JobOrderDoorContainer_Temp
TRUNCATE TABLE JobOrderITM_Temp
TRUNCATE TABLE JobOrderITMContainer_Temp
TRUNCATE TABLE JTQ510
TRUNCATE TABLE JTQ520
TRUNCATE TABLE NAIBFF
TRUNCATE TABLE NAIBFF_DeleteTable
TRUNCATE TABLE SpecialRequirementMilestone
declare @.tablename nvarchar(125)
declare cur CURSOR
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
and name not like 'conflict_%' and name not like 'dt%' and name not like
'MS%' and name not like 'sys%'
order by name
open cur
fetch next from cur
into @.tablename
while @.@.fetch_status = 0
BEGIN
PRINT 'REINDEXING ' + @.tablename
dbcc dbreindex (@.tablename)
fetch next from cur
into @.tablename
END
CLOSE cur
DEALLOCATE curI wouldn't rule out the ANSI settings. This issue has come
up before. SQL Agent and Query Analyzer use different set
options/ansi settings when they connect to SQL Server.
Whatever the set option errors you are seeing as missing,
add to your job script and make sure they are just before
the calling of your stored procedures.
-Sue
On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>SQL Server 2000 Standard Sp3. on Windows 2003
>What is Error 1934.
>I can't find documentaiton on this anywhere. There's only one article in K
B
>in relation to merge replication. And other stuff on the web about ANSI
>settings and indexed views (which I'm not using).
>I get these errors in different contexts.
>Here's one:
>Profiler screenshot
>http://www.vinnychi.com/vince/images/sql.jpg
>This is a stored procedure that SQL Agent is running from a job. When i ru
n
>it this way, I get the 1934 error. If I run it from Query Analyzer I get no
>error, and it runs to completion.
>SP:
>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>AS
>exec UTIL_DEFRAG
> RETURN
>
>========
>UTIL_DEFRAG is:
>CREATE procedure UTIL_DEFRAG
>AS
>TRUNCATE TABLE BLCSO
>TRUNCATE TABLE IBFF_BOL_Temp
>TRUNCATE TABLE IBFF_Container_Temp
>TRUNCATE TABLE IBFFContainerImportPhase2
>TRUNCATE TABLE JobOrderDoor_Temp
>TRUNCATE TABLE JobOrderDoorContainer_Temp
>TRUNCATE TABLE JobOrderITM_Temp
>TRUNCATE TABLE JobOrderITMContainer_Temp
>TRUNCATE TABLE JTQ510
>TRUNCATE TABLE JTQ520
>TRUNCATE TABLE NAIBFF
>TRUNCATE TABLE NAIBFF_DeleteTable
>TRUNCATE TABLE SpecialRequirementMilestone
>declare @.tablename nvarchar(125)
>declare cur CURSOR
>FORWARD_ONLY
>STATIC
>READ_ONLY
>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>and name not like 'conflict_%' and name not like 'dt%' and name not like
>'MS%' and name not like 'sys%'
>order by name
>open cur
>fetch next from cur
>into @.tablename
>while @.@.fetch_status = 0
>BEGIN
>PRINT 'REINDEXING ' + @.tablename
>dbcc dbreindex (@.tablename)
>fetch next from cur
>into @.tablename
>END
>CLOSE cur
>DEALLOCATE cur
>|||It ended up being
Set ArithAbort ON
needed to be set.
How come this error isn't documented anywhere "official?
I'm also gettinga lot of error 1933 and 1935
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.
4ax.com...
>I wouldn't rule out the ANSI settings. This issue has come
> up before. SQL Agent and Query Analyzer use different set
> options/ansi settings when they connect to SQL Server.
> Whatever the set option errors you are seeing as missing,
> add to your job script and make sure they are just before
> the calling of your stored procedures.
> -Sue
> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
>|||Based on your errors, I would guess that you have computed
columns. ANSI settings and computed columns is addressed in
books online under the topics:
SET Options That Affect Results
Creating Indexes on Computed Columns
The Arithabort setting is addressed in the books online
topic Create Index
The article does mention the following:
It is strongly recommended that the ARITHABORT user option
be set server-wide to ON as soon as the first indexed view
or index on a computed column is created in any database on
the server.
You can also find related information in the following:
http://www.microsoft.com/sql/techin...pment/july2.asp
-Sue
On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>It ended up being
>Set ArithAbort ON
>needed to be set.
>
>How come this error isn't documented anywhere "official?
>I'm also gettinga lot of error 1933 and 1935
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.
4ax.com...
>|||Sue: Thanks for your leads. A large part of the problem was ArithAbort.
Thank you very much.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4c6fv0l327hq7dm20npbajt0vv2nsf10u1@.
4ax.com...
> Based on your errors, I would guess that you have computed
> columns. ANSI settings and computed columns is addressed in
> books online under the topics:
> SET Options That Affect Results
> Creating Indexes on Computed Columns
> The Arithabort setting is addressed in the books online
> topic Create Index
> The article does mention the following:
> It is strongly recommended that the ARITHABORT user option
> be set server-wide to ON as soon as the first indexed view
> or index on a computed column is created in any database on
> the server.
> You can also find related information in the following:
> http://www.microsoft.com/sql/techin...pment/july2.asp
> -Sue
> On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
>
What is Error 1934.
I can't find documentaiton on this anywhere. There's only one article in KB
in relation to merge replication. And other stuff on the web about ANSI
settings and indexed views (which I'm not using).
I get these errors in different contexts.
Here's one:
Profiler screenshot
http://www.vinnychi.com/vince/images/sql.jpg
This is a stored procedure that SQL Agent is running from a job. When i run
it this way, I get the 1934 error. If I run it from Query Analyzer I get no
error, and it runs to completion.
SP:
CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
AS
exec UTIL_DEFRAG
RETURN
========
UTIL_DEFRAG is:
CREATE procedure UTIL_DEFRAG
AS
TRUNCATE TABLE BLCSO
TRUNCATE TABLE IBFF_BOL_Temp
TRUNCATE TABLE IBFF_Container_Temp
TRUNCATE TABLE IBFFContainerImportPhase2
TRUNCATE TABLE JobOrderDoor_Temp
TRUNCATE TABLE JobOrderDoorContainer_Temp
TRUNCATE TABLE JobOrderITM_Temp
TRUNCATE TABLE JobOrderITMContainer_Temp
TRUNCATE TABLE JTQ510
TRUNCATE TABLE JTQ520
TRUNCATE TABLE NAIBFF
TRUNCATE TABLE NAIBFF_DeleteTable
TRUNCATE TABLE SpecialRequirementMilestone
declare @.tablename nvarchar(125)
declare cur CURSOR
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
and name not like 'conflict_%' and name not like 'dt%' and name not like
'MS%' and name not like 'sys%'
order by name
open cur
fetch next from cur
into @.tablename
while @.@.fetch_status = 0
BEGIN
PRINT 'REINDEXING ' + @.tablename
dbcc dbreindex (@.tablename)
fetch next from cur
into @.tablename
END
CLOSE cur
DEALLOCATE curI wouldn't rule out the ANSI settings. This issue has come
up before. SQL Agent and Query Analyzer use different set
options/ansi settings when they connect to SQL Server.
Whatever the set option errors you are seeing as missing,
add to your job script and make sure they are just before
the calling of your stored procedures.
-Sue
On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>SQL Server 2000 Standard Sp3. on Windows 2003
>What is Error 1934.
>I can't find documentaiton on this anywhere. There's only one article in K
B
>in relation to merge replication. And other stuff on the web about ANSI
>settings and indexed views (which I'm not using).
>I get these errors in different contexts.
>Here's one:
>Profiler screenshot
>http://www.vinnychi.com/vince/images/sql.jpg
>This is a stored procedure that SQL Agent is running from a job. When i ru
n
>it this way, I get the 1934 error. If I run it from Query Analyzer I get no
>error, and it runs to completion.
>SP:
>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>AS
>exec UTIL_DEFRAG
> RETURN
>
>========
>UTIL_DEFRAG is:
>CREATE procedure UTIL_DEFRAG
>AS
>TRUNCATE TABLE BLCSO
>TRUNCATE TABLE IBFF_BOL_Temp
>TRUNCATE TABLE IBFF_Container_Temp
>TRUNCATE TABLE IBFFContainerImportPhase2
>TRUNCATE TABLE JobOrderDoor_Temp
>TRUNCATE TABLE JobOrderDoorContainer_Temp
>TRUNCATE TABLE JobOrderITM_Temp
>TRUNCATE TABLE JobOrderITMContainer_Temp
>TRUNCATE TABLE JTQ510
>TRUNCATE TABLE JTQ520
>TRUNCATE TABLE NAIBFF
>TRUNCATE TABLE NAIBFF_DeleteTable
>TRUNCATE TABLE SpecialRequirementMilestone
>declare @.tablename nvarchar(125)
>declare cur CURSOR
>FORWARD_ONLY
>STATIC
>READ_ONLY
>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>and name not like 'conflict_%' and name not like 'dt%' and name not like
>'MS%' and name not like 'sys%'
>order by name
>open cur
>fetch next from cur
>into @.tablename
>while @.@.fetch_status = 0
>BEGIN
>PRINT 'REINDEXING ' + @.tablename
>dbcc dbreindex (@.tablename)
>fetch next from cur
>into @.tablename
>END
>CLOSE cur
>DEALLOCATE cur
>|||It ended up being
Set ArithAbort ON
needed to be set.
How come this error isn't documented anywhere "official?
I'm also gettinga lot of error 1933 and 1935
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.
4ax.com...
>I wouldn't rule out the ANSI settings. This issue has come
> up before. SQL Agent and Query Analyzer use different set
> options/ansi settings when they connect to SQL Server.
> Whatever the set option errors you are seeing as missing,
> add to your job script and make sure they are just before
> the calling of your stored procedures.
> -Sue
> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
>|||Based on your errors, I would guess that you have computed
columns. ANSI settings and computed columns is addressed in
books online under the topics:
SET Options That Affect Results
Creating Indexes on Computed Columns
The Arithabort setting is addressed in the books online
topic Create Index
The article does mention the following:
It is strongly recommended that the ARITHABORT user option
be set server-wide to ON as soon as the first indexed view
or index on a computed column is created in any database on
the server.
You can also find related information in the following:
http://www.microsoft.com/sql/techin...pment/july2.asp
-Sue
On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>It ended up being
>Set ArithAbort ON
>needed to be set.
>
>How come this error isn't documented anywhere "official?
>I'm also gettinga lot of error 1933 and 1935
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.
4ax.com...
>|||Sue: Thanks for your leads. A large part of the problem was ArithAbort.
Thank you very much.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4c6fv0l327hq7dm20npbajt0vv2nsf10u1@.
4ax.com...
> Based on your errors, I would guess that you have computed
> columns. ANSI settings and computed columns is addressed in
> books online under the topics:
> SET Options That Affect Results
> Creating Indexes on Computed Columns
> The Arithabort setting is addressed in the books online
> topic Create Index
> The article does mention the following:
> It is strongly recommended that the ARITHABORT user option
> be set server-wide to ON as soon as the first indexed view
> or index on a computed column is created in any database on
> the server.
> You can also find related information in the following:
> http://www.microsoft.com/sql/techin...pment/july2.asp
> -Sue
> On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
>
Error 1934 Severity 16 State 1
SQL Server 2000 Standard Sp3. on Windows 2003
What is Error 1934.
I can't find documentaiton on this anywhere. There's only one article in KB
in relation to merge replication. And other stuff on the web about ANSI
settings and indexed views (which I'm not using).
I get these errors in different contexts.
Here's one:
Profiler screenshot
http://www.vinnychi.com/vince/images/sql.jpg
This is a stored procedure that SQL Agent is running from a job. When i run
it this way, I get the 1934 error. If I run it from Query Analyzer I get no
error, and it runs to completion.
SP:
CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
AS
exec UTIL_DEFRAG
RETURN
========
UTIL_DEFRAG is:
CREATE procedure UTIL_DEFRAG
AS
TRUNCATE TABLE BLCSO
TRUNCATE TABLE IBFF_BOL_Temp
TRUNCATE TABLE IBFF_Container_Temp
TRUNCATE TABLE IBFFContainerImportPhase2
TRUNCATE TABLE JobOrderDoor_Temp
TRUNCATE TABLE JobOrderDoorContainer_Temp
TRUNCATE TABLE JobOrderITM_Temp
TRUNCATE TABLE JobOrderITMContainer_Temp
TRUNCATE TABLE JTQ510
TRUNCATE TABLE JTQ520
TRUNCATE TABLE NAIBFF
TRUNCATE TABLE NAIBFF_DeleteTable
TRUNCATE TABLE SpecialRequirementMilestone
declare @.tablename nvarchar(125)
declare cur CURSOR
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
and name not like 'conflict_%' and name not like 'dt%' and name not like
'MS%' and name not like 'sys%'
order by name
open cur
fetch next from cur
into @.tablename
while @.@.fetch_status = 0
BEGIN
PRINT 'REINDEXING ' + @.tablename
dbcc dbreindex (@.tablename)
fetch next from cur
into @.tablename
END
CLOSE cur
DEALLOCATE cur
I wouldn't rule out the ANSI settings. This issue has come
up before. SQL Agent and Query Analyzer use different set
options/ansi settings when they connect to SQL Server.
Whatever the set option errors you are seeing as missing,
add to your job script and make sure they are just before
the calling of your stored procedures.
-Sue
On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>SQL Server 2000 Standard Sp3. on Windows 2003
>What is Error 1934.
>I can't find documentaiton on this anywhere. There's only one article in KB
>in relation to merge replication. And other stuff on the web about ANSI
>settings and indexed views (which I'm not using).
>I get these errors in different contexts.
>Here's one:
>Profiler screenshot
>http://www.vinnychi.com/vince/images/sql.jpg
>This is a stored procedure that SQL Agent is running from a job. When i run
>it this way, I get the 1934 error. If I run it from Query Analyzer I get no
>error, and it runs to completion.
>SP:
>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>AS
>exec UTIL_DEFRAG
> RETURN
>
>========
>UTIL_DEFRAG is:
>CREATE procedure UTIL_DEFRAG
>AS
>TRUNCATE TABLE BLCSO
>TRUNCATE TABLE IBFF_BOL_Temp
>TRUNCATE TABLE IBFF_Container_Temp
>TRUNCATE TABLE IBFFContainerImportPhase2
>TRUNCATE TABLE JobOrderDoor_Temp
>TRUNCATE TABLE JobOrderDoorContainer_Temp
>TRUNCATE TABLE JobOrderITM_Temp
>TRUNCATE TABLE JobOrderITMContainer_Temp
>TRUNCATE TABLE JTQ510
>TRUNCATE TABLE JTQ520
>TRUNCATE TABLE NAIBFF
>TRUNCATE TABLE NAIBFF_DeleteTable
>TRUNCATE TABLE SpecialRequirementMilestone
>declare @.tablename nvarchar(125)
>declare cur CURSOR
>FORWARD_ONLY
>STATIC
>READ_ONLY
>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>and name not like 'conflict_%' and name not like 'dt%' and name not like
>'MS%' and name not like 'sys%'
>order by name
>open cur
>fetch next from cur
>into @.tablename
>while @.@.fetch_status = 0
>BEGIN
>PRINT 'REINDEXING ' + @.tablename
>dbcc dbreindex (@.tablename)
>fetch next from cur
>into @.tablename
>END
>CLOSE cur
>DEALLOCATE cur
>
|||It ended up being
Set ArithAbort ON
needed to be set.
How come this error isn't documented anywhere "official?
I'm also gettinga lot of error 1933 and 1935
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>I wouldn't rule out the ANSI settings. This issue has come
> up before. SQL Agent and Query Analyzer use different set
> options/ansi settings when they connect to SQL Server.
> Whatever the set option errors you are seeing as missing,
> add to your job script and make sure they are just before
> the calling of your stored procedures.
> -Sue
> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
|||Based on your errors, I would guess that you have computed
columns. ANSI settings and computed columns is addressed in
books online under the topics:
SET Options That Affect Results
Creating Indexes on Computed Columns
The Arithabort setting is addressed in the books online
topic Create Index
The article does mention the following:
It is strongly recommended that the ARITHABORT user option
be set server-wide to ON as soon as the first indexed view
or index on a computed column is created in any database on
the server.
You can also find related information in the following:
http://www.microsoft.com/sql/techinf...ment/july2.asp
-Sue
On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>It ended up being
>Set ArithAbort ON
>needed to be set.
>
>How come this error isn't documented anywhere "official?
>I'm also gettinga lot of error 1933 and 1935
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com.. .
>
|||Sue: Thanks for your leads. A large part of the problem was ArithAbort.
Thank you very much.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4c6fv0l327hq7dm20npbajt0vv2nsf10u1@.4ax.com...
> Based on your errors, I would guess that you have computed
> columns. ANSI settings and computed columns is addressed in
> books online under the topics:
> SET Options That Affect Results
> Creating Indexes on Computed Columns
> The Arithabort setting is addressed in the books online
> topic Create Index
> The article does mention the following:
> It is strongly recommended that the ARITHABORT user option
> be set server-wide to ON as soon as the first indexed view
> or index on a computed column is created in any database on
> the server.
> You can also find related information in the following:
> http://www.microsoft.com/sql/techinf...ment/july2.asp
> -Sue
> On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
What is Error 1934.
I can't find documentaiton on this anywhere. There's only one article in KB
in relation to merge replication. And other stuff on the web about ANSI
settings and indexed views (which I'm not using).
I get these errors in different contexts.
Here's one:
Profiler screenshot
http://www.vinnychi.com/vince/images/sql.jpg
This is a stored procedure that SQL Agent is running from a job. When i run
it this way, I get the 1934 error. If I run it from Query Analyzer I get no
error, and it runs to completion.
SP:
CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
AS
exec UTIL_DEFRAG
RETURN
========
UTIL_DEFRAG is:
CREATE procedure UTIL_DEFRAG
AS
TRUNCATE TABLE BLCSO
TRUNCATE TABLE IBFF_BOL_Temp
TRUNCATE TABLE IBFF_Container_Temp
TRUNCATE TABLE IBFFContainerImportPhase2
TRUNCATE TABLE JobOrderDoor_Temp
TRUNCATE TABLE JobOrderDoorContainer_Temp
TRUNCATE TABLE JobOrderITM_Temp
TRUNCATE TABLE JobOrderITMContainer_Temp
TRUNCATE TABLE JTQ510
TRUNCATE TABLE JTQ520
TRUNCATE TABLE NAIBFF
TRUNCATE TABLE NAIBFF_DeleteTable
TRUNCATE TABLE SpecialRequirementMilestone
declare @.tablename nvarchar(125)
declare cur CURSOR
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
and name not like 'conflict_%' and name not like 'dt%' and name not like
'MS%' and name not like 'sys%'
order by name
open cur
fetch next from cur
into @.tablename
while @.@.fetch_status = 0
BEGIN
PRINT 'REINDEXING ' + @.tablename
dbcc dbreindex (@.tablename)
fetch next from cur
into @.tablename
END
CLOSE cur
DEALLOCATE cur
I wouldn't rule out the ANSI settings. This issue has come
up before. SQL Agent and Query Analyzer use different set
options/ansi settings when they connect to SQL Server.
Whatever the set option errors you are seeing as missing,
add to your job script and make sure they are just before
the calling of your stored procedures.
-Sue
On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>SQL Server 2000 Standard Sp3. on Windows 2003
>What is Error 1934.
>I can't find documentaiton on this anywhere. There's only one article in KB
>in relation to merge replication. And other stuff on the web about ANSI
>settings and indexed views (which I'm not using).
>I get these errors in different contexts.
>Here's one:
>Profiler screenshot
>http://www.vinnychi.com/vince/images/sql.jpg
>This is a stored procedure that SQL Agent is running from a job. When i run
>it this way, I get the 1934 error. If I run it from Query Analyzer I get no
>error, and it runs to completion.
>SP:
>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>AS
>exec UTIL_DEFRAG
> RETURN
>
>========
>UTIL_DEFRAG is:
>CREATE procedure UTIL_DEFRAG
>AS
>TRUNCATE TABLE BLCSO
>TRUNCATE TABLE IBFF_BOL_Temp
>TRUNCATE TABLE IBFF_Container_Temp
>TRUNCATE TABLE IBFFContainerImportPhase2
>TRUNCATE TABLE JobOrderDoor_Temp
>TRUNCATE TABLE JobOrderDoorContainer_Temp
>TRUNCATE TABLE JobOrderITM_Temp
>TRUNCATE TABLE JobOrderITMContainer_Temp
>TRUNCATE TABLE JTQ510
>TRUNCATE TABLE JTQ520
>TRUNCATE TABLE NAIBFF
>TRUNCATE TABLE NAIBFF_DeleteTable
>TRUNCATE TABLE SpecialRequirementMilestone
>declare @.tablename nvarchar(125)
>declare cur CURSOR
>FORWARD_ONLY
>STATIC
>READ_ONLY
>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>and name not like 'conflict_%' and name not like 'dt%' and name not like
>'MS%' and name not like 'sys%'
>order by name
>open cur
>fetch next from cur
>into @.tablename
>while @.@.fetch_status = 0
>BEGIN
>PRINT 'REINDEXING ' + @.tablename
>dbcc dbreindex (@.tablename)
>fetch next from cur
>into @.tablename
>END
>CLOSE cur
>DEALLOCATE cur
>
|||It ended up being
Set ArithAbort ON
needed to be set.
How come this error isn't documented anywhere "official?
I'm also gettinga lot of error 1933 and 1935
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>I wouldn't rule out the ANSI settings. This issue has come
> up before. SQL Agent and Query Analyzer use different set
> options/ansi settings when they connect to SQL Server.
> Whatever the set option errors you are seeing as missing,
> add to your job script and make sure they are just before
> the calling of your stored procedures.
> -Sue
> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
|||Based on your errors, I would guess that you have computed
columns. ANSI settings and computed columns is addressed in
books online under the topics:
SET Options That Affect Results
Creating Indexes on Computed Columns
The Arithabort setting is addressed in the books online
topic Create Index
The article does mention the following:
It is strongly recommended that the ARITHABORT user option
be set server-wide to ON as soon as the first indexed view
or index on a computed column is created in any database on
the server.
You can also find related information in the following:
http://www.microsoft.com/sql/techinf...ment/july2.asp
-Sue
On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>It ended up being
>Set ArithAbort ON
>needed to be set.
>
>How come this error isn't documented anywhere "official?
>I'm also gettinga lot of error 1933 and 1935
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com.. .
>
|||Sue: Thanks for your leads. A large part of the problem was ArithAbort.
Thank you very much.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4c6fv0l327hq7dm20npbajt0vv2nsf10u1@.4ax.com...
> Based on your errors, I would guess that you have computed
> columns. ANSI settings and computed columns is addressed in
> books online under the topics:
> SET Options That Affect Results
> Creating Indexes on Computed Columns
> The Arithabort setting is addressed in the books online
> topic Create Index
> The article does mention the following:
> It is strongly recommended that the ARITHABORT user option
> be set server-wide to ON as soon as the first indexed view
> or index on a computed column is created in any database on
> the server.
> You can also find related information in the following:
> http://www.microsoft.com/sql/techinf...ment/july2.asp
> -Sue
> On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
Error 1934 Severity 16 State 1
SQL Server 2000 Standard Sp3. on Windows 2003
What is Error 1934.
I can't find documentaiton on this anywhere. There's only one article in KB
in relation to merge replication. And other stuff on the web about ANSI
settings and indexed views (which I'm not using).
I get these errors in different contexts.
Here's one:
Profiler screenshot
http://www.vinnychi.com/vince/images/sql.jpg
This is a stored procedure that SQL Agent is running from a job. When i run
it this way, I get the 1934 error. If I run it from Query Analyzer I get no
error, and it runs to completion.
SP:
CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
AS
exec UTIL_DEFRAG
RETURN
======== UTIL_DEFRAG is:
CREATE procedure UTIL_DEFRAG
AS
TRUNCATE TABLE BLCSO
TRUNCATE TABLE IBFF_BOL_Temp
TRUNCATE TABLE IBFF_Container_Temp
TRUNCATE TABLE IBFFContainerImportPhase2
TRUNCATE TABLE JobOrderDoor_Temp
TRUNCATE TABLE JobOrderDoorContainer_Temp
TRUNCATE TABLE JobOrderITM_Temp
TRUNCATE TABLE JobOrderITMContainer_Temp
TRUNCATE TABLE JTQ510
TRUNCATE TABLE JTQ520
TRUNCATE TABLE NAIBFF
TRUNCATE TABLE NAIBFF_DeleteTable
TRUNCATE TABLE SpecialRequirementMilestone
declare @.tablename nvarchar(125)
declare cur CURSOR
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
and name not like 'conflict_%' and name not like 'dt%' and name not like
'MS%' and name not like 'sys%'
order by name
open cur
fetch next from cur
into @.tablename
while @.@.fetch_status = 0
BEGIN
PRINT 'REINDEXING ' + @.tablename
dbcc dbreindex (@.tablename)
fetch next from cur
into @.tablename
END
CLOSE cur
DEALLOCATE curI wouldn't rule out the ANSI settings. This issue has come
up before. SQL Agent and Query Analyzer use different set
options/ansi settings when they connect to SQL Server.
Whatever the set option errors you are seeing as missing,
add to your job script and make sure they are just before
the calling of your stored procedures.
-Sue
On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>SQL Server 2000 Standard Sp3. on Windows 2003
>What is Error 1934.
>I can't find documentaiton on this anywhere. There's only one article in KB
>in relation to merge replication. And other stuff on the web about ANSI
>settings and indexed views (which I'm not using).
>I get these errors in different contexts.
>Here's one:
>Profiler screenshot
>http://www.vinnychi.com/vince/images/sql.jpg
>This is a stored procedure that SQL Agent is running from a job. When i run
>it this way, I get the 1934 error. If I run it from Query Analyzer I get no
>error, and it runs to completion.
>SP:
>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>AS
>exec UTIL_DEFRAG
> RETURN
>
>========>UTIL_DEFRAG is:
>CREATE procedure UTIL_DEFRAG
>AS
>TRUNCATE TABLE BLCSO
>TRUNCATE TABLE IBFF_BOL_Temp
>TRUNCATE TABLE IBFF_Container_Temp
>TRUNCATE TABLE IBFFContainerImportPhase2
>TRUNCATE TABLE JobOrderDoor_Temp
>TRUNCATE TABLE JobOrderDoorContainer_Temp
>TRUNCATE TABLE JobOrderITM_Temp
>TRUNCATE TABLE JobOrderITMContainer_Temp
>TRUNCATE TABLE JTQ510
>TRUNCATE TABLE JTQ520
>TRUNCATE TABLE NAIBFF
>TRUNCATE TABLE NAIBFF_DeleteTable
>TRUNCATE TABLE SpecialRequirementMilestone
>declare @.tablename nvarchar(125)
>declare cur CURSOR
>FORWARD_ONLY
>STATIC
>READ_ONLY
>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>and name not like 'conflict_%' and name not like 'dt%' and name not like
>'MS%' and name not like 'sys%'
>order by name
>open cur
>fetch next from cur
>into @.tablename
>while @.@.fetch_status = 0
>BEGIN
>PRINT 'REINDEXING ' + @.tablename
>dbcc dbreindex (@.tablename)
>fetch next from cur
>into @.tablename
>END
>CLOSE cur
>DEALLOCATE cur
>|||It ended up being
Set ArithAbort ON
needed to be set.
How come this error isn't documented anywhere "official?
I'm also gettinga lot of error 1933 and 1935
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>I wouldn't rule out the ANSI settings. This issue has come
> up before. SQL Agent and Query Analyzer use different set
> options/ansi settings when they connect to SQL Server.
> Whatever the set option errors you are seeing as missing,
> add to your job script and make sure they are just before
> the calling of your stored procedures.
> -Sue
> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>>SQL Server 2000 Standard Sp3. on Windows 2003
>>What is Error 1934.
>>I can't find documentaiton on this anywhere. There's only one article in
>>KB
>>in relation to merge replication. And other stuff on the web about ANSI
>>settings and indexed views (which I'm not using).
>>I get these errors in different contexts.
>>Here's one:
>>Profiler screenshot
>>http://www.vinnychi.com/vince/images/sql.jpg
>>This is a stored procedure that SQL Agent is running from a job. When i
>>run
>>it this way, I get the 1934 error. If I run it from Query Analyzer I get
>>no
>>error, and it runs to completion.
>>SP:
>>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>>AS
>>exec UTIL_DEFRAG
>> RETURN
>>
>>========>>UTIL_DEFRAG is:
>>CREATE procedure UTIL_DEFRAG
>>AS
>>TRUNCATE TABLE BLCSO
>>TRUNCATE TABLE IBFF_BOL_Temp
>>TRUNCATE TABLE IBFF_Container_Temp
>>TRUNCATE TABLE IBFFContainerImportPhase2
>>TRUNCATE TABLE JobOrderDoor_Temp
>>TRUNCATE TABLE JobOrderDoorContainer_Temp
>>TRUNCATE TABLE JobOrderITM_Temp
>>TRUNCATE TABLE JobOrderITMContainer_Temp
>>TRUNCATE TABLE JTQ510
>>TRUNCATE TABLE JTQ520
>>TRUNCATE TABLE NAIBFF
>>TRUNCATE TABLE NAIBFF_DeleteTable
>>TRUNCATE TABLE SpecialRequirementMilestone
>>declare @.tablename nvarchar(125)
>>declare cur CURSOR
>>FORWARD_ONLY
>>STATIC
>>READ_ONLY
>>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>>and name not like 'conflict_%' and name not like 'dt%' and name not like
>>'MS%' and name not like 'sys%'
>>order by name
>>open cur
>>fetch next from cur
>>into @.tablename
>>while @.@.fetch_status = 0
>>BEGIN
>>PRINT 'REINDEXING ' + @.tablename
>>dbcc dbreindex (@.tablename)
>>fetch next from cur
>>into @.tablename
>>END
>>CLOSE cur
>>DEALLOCATE cur
>|||Based on your errors, I would guess that you have computed
columns. ANSI settings and computed columns is addressed in
books online under the topics:
SET Options That Affect Results
Creating Indexes on Computed Columns
The Arithabort setting is addressed in the books online
topic Create Index
The article does mention the following:
It is strongly recommended that the ARITHABORT user option
be set server-wide to ON as soon as the first indexed view
or index on a computed column is created in any database on
the server.
You can also find related information in the following:
http://www.microsoft.com/sql/techinfo/tips/development/july2.asp
-Sue
On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>It ended up being
>Set ArithAbort ON
>needed to be set.
>
>How come this error isn't documented anywhere "official?
>I'm also gettinga lot of error 1933 and 1935
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>>I wouldn't rule out the ANSI settings. This issue has come
>> up before. SQL Agent and Query Analyzer use different set
>> options/ansi settings when they connect to SQL Server.
>> Whatever the set option errors you are seeing as missing,
>> add to your job script and make sure they are just before
>> the calling of your stored procedures.
>> -Sue
>> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
>> <remove@.pacelviATspeakeasy.net> wrote:
>>SQL Server 2000 Standard Sp3. on Windows 2003
>>What is Error 1934.
>>I can't find documentaiton on this anywhere. There's only one article in
>>KB
>>in relation to merge replication. And other stuff on the web about ANSI
>>settings and indexed views (which I'm not using).
>>I get these errors in different contexts.
>>Here's one:
>>Profiler screenshot
>>http://www.vinnychi.com/vince/images/sql.jpg
>>This is a stored procedure that SQL Agent is running from a job. When i
>>run
>>it this way, I get the 1934 error. If I run it from Query Analyzer I get
>>no
>>error, and it runs to completion.
>>SP:
>>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>>AS
>>exec UTIL_DEFRAG
>> RETURN
>>
>>========>>UTIL_DEFRAG is:
>>CREATE procedure UTIL_DEFRAG
>>AS
>>TRUNCATE TABLE BLCSO
>>TRUNCATE TABLE IBFF_BOL_Temp
>>TRUNCATE TABLE IBFF_Container_Temp
>>TRUNCATE TABLE IBFFContainerImportPhase2
>>TRUNCATE TABLE JobOrderDoor_Temp
>>TRUNCATE TABLE JobOrderDoorContainer_Temp
>>TRUNCATE TABLE JobOrderITM_Temp
>>TRUNCATE TABLE JobOrderITMContainer_Temp
>>TRUNCATE TABLE JTQ510
>>TRUNCATE TABLE JTQ520
>>TRUNCATE TABLE NAIBFF
>>TRUNCATE TABLE NAIBFF_DeleteTable
>>TRUNCATE TABLE SpecialRequirementMilestone
>>declare @.tablename nvarchar(125)
>>declare cur CURSOR
>>FORWARD_ONLY
>>STATIC
>>READ_ONLY
>>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>>and name not like 'conflict_%' and name not like 'dt%' and name not like
>>'MS%' and name not like 'sys%'
>>order by name
>>open cur
>>fetch next from cur
>>into @.tablename
>>while @.@.fetch_status = 0
>>BEGIN
>>PRINT 'REINDEXING ' + @.tablename
>>dbcc dbreindex (@.tablename)
>>fetch next from cur
>>into @.tablename
>>END
>>CLOSE cur
>>DEALLOCATE cur
>>
>|||Sue: Thanks for your leads. A large part of the problem was ArithAbort.
Thank you very much.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4c6fv0l327hq7dm20npbajt0vv2nsf10u1@.4ax.com...
> Based on your errors, I would guess that you have computed
> columns. ANSI settings and computed columns is addressed in
> books online under the topics:
> SET Options That Affect Results
> Creating Indexes on Computed Columns
> The Arithabort setting is addressed in the books online
> topic Create Index
> The article does mention the following:
> It is strongly recommended that the ARITHABORT user option
> be set server-wide to ON as soon as the first indexed view
> or index on a computed column is created in any database on
> the server.
> You can also find related information in the following:
> http://www.microsoft.com/sql/techinfo/tips/development/july2.asp
> -Sue
> On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>>It ended up being
>>Set ArithAbort ON
>>needed to be set.
>>
>>How come this error isn't documented anywhere "official?
>>I'm also gettinga lot of error 1933 and 1935
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>>I wouldn't rule out the ANSI settings. This issue has come
>> up before. SQL Agent and Query Analyzer use different set
>> options/ansi settings when they connect to SQL Server.
>> Whatever the set option errors you are seeing as missing,
>> add to your job script and make sure they are just before
>> the calling of your stored procedures.
>> -Sue
>> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
>> <remove@.pacelviATspeakeasy.net> wrote:
>>SQL Server 2000 Standard Sp3. on Windows 2003
>>What is Error 1934.
>>I can't find documentaiton on this anywhere. There's only one article
>>in
>>KB
>>in relation to merge replication. And other stuff on the web about ANSI
>>settings and indexed views (which I'm not using).
>>I get these errors in different contexts.
>>Here's one:
>>Profiler screenshot
>>http://www.vinnychi.com/vince/images/sql.jpg
>>This is a stored procedure that SQL Agent is running from a job. When i
>>run
>>it this way, I get the 1934 error. If I run it from Query Analyzer I get
>>no
>>error, and it runs to completion.
>>SP:
>>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>>AS
>>exec UTIL_DEFRAG
>> RETURN
>>
>>========>>UTIL_DEFRAG is:
>>CREATE procedure UTIL_DEFRAG
>>AS
>>TRUNCATE TABLE BLCSO
>>TRUNCATE TABLE IBFF_BOL_Temp
>>TRUNCATE TABLE IBFF_Container_Temp
>>TRUNCATE TABLE IBFFContainerImportPhase2
>>TRUNCATE TABLE JobOrderDoor_Temp
>>TRUNCATE TABLE JobOrderDoorContainer_Temp
>>TRUNCATE TABLE JobOrderITM_Temp
>>TRUNCATE TABLE JobOrderITMContainer_Temp
>>TRUNCATE TABLE JTQ510
>>TRUNCATE TABLE JTQ520
>>TRUNCATE TABLE NAIBFF
>>TRUNCATE TABLE NAIBFF_DeleteTable
>>TRUNCATE TABLE SpecialRequirementMilestone
>>declare @.tablename nvarchar(125)
>>declare cur CURSOR
>>FORWARD_ONLY
>>STATIC
>>READ_ONLY
>>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>>and name not like 'conflict_%' and name not like 'dt%' and name not like
>>'MS%' and name not like 'sys%'
>>order by name
>>open cur
>>fetch next from cur
>>into @.tablename
>>while @.@.fetch_status = 0
>>BEGIN
>>PRINT 'REINDEXING ' + @.tablename
>>dbcc dbreindex (@.tablename)
>>fetch next from cur
>>into @.tablename
>>END
>>CLOSE cur
>>DEALLOCATE cur
>>
>
What is Error 1934.
I can't find documentaiton on this anywhere. There's only one article in KB
in relation to merge replication. And other stuff on the web about ANSI
settings and indexed views (which I'm not using).
I get these errors in different contexts.
Here's one:
Profiler screenshot
http://www.vinnychi.com/vince/images/sql.jpg
This is a stored procedure that SQL Agent is running from a job. When i run
it this way, I get the 1934 error. If I run it from Query Analyzer I get no
error, and it runs to completion.
SP:
CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
AS
exec UTIL_DEFRAG
RETURN
======== UTIL_DEFRAG is:
CREATE procedure UTIL_DEFRAG
AS
TRUNCATE TABLE BLCSO
TRUNCATE TABLE IBFF_BOL_Temp
TRUNCATE TABLE IBFF_Container_Temp
TRUNCATE TABLE IBFFContainerImportPhase2
TRUNCATE TABLE JobOrderDoor_Temp
TRUNCATE TABLE JobOrderDoorContainer_Temp
TRUNCATE TABLE JobOrderITM_Temp
TRUNCATE TABLE JobOrderITMContainer_Temp
TRUNCATE TABLE JTQ510
TRUNCATE TABLE JTQ520
TRUNCATE TABLE NAIBFF
TRUNCATE TABLE NAIBFF_DeleteTable
TRUNCATE TABLE SpecialRequirementMilestone
declare @.tablename nvarchar(125)
declare cur CURSOR
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
and name not like 'conflict_%' and name not like 'dt%' and name not like
'MS%' and name not like 'sys%'
order by name
open cur
fetch next from cur
into @.tablename
while @.@.fetch_status = 0
BEGIN
PRINT 'REINDEXING ' + @.tablename
dbcc dbreindex (@.tablename)
fetch next from cur
into @.tablename
END
CLOSE cur
DEALLOCATE curI wouldn't rule out the ANSI settings. This issue has come
up before. SQL Agent and Query Analyzer use different set
options/ansi settings when they connect to SQL Server.
Whatever the set option errors you are seeing as missing,
add to your job script and make sure they are just before
the calling of your stored procedures.
-Sue
On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>SQL Server 2000 Standard Sp3. on Windows 2003
>What is Error 1934.
>I can't find documentaiton on this anywhere. There's only one article in KB
>in relation to merge replication. And other stuff on the web about ANSI
>settings and indexed views (which I'm not using).
>I get these errors in different contexts.
>Here's one:
>Profiler screenshot
>http://www.vinnychi.com/vince/images/sql.jpg
>This is a stored procedure that SQL Agent is running from a job. When i run
>it this way, I get the 1934 error. If I run it from Query Analyzer I get no
>error, and it runs to completion.
>SP:
>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>AS
>exec UTIL_DEFRAG
> RETURN
>
>========>UTIL_DEFRAG is:
>CREATE procedure UTIL_DEFRAG
>AS
>TRUNCATE TABLE BLCSO
>TRUNCATE TABLE IBFF_BOL_Temp
>TRUNCATE TABLE IBFF_Container_Temp
>TRUNCATE TABLE IBFFContainerImportPhase2
>TRUNCATE TABLE JobOrderDoor_Temp
>TRUNCATE TABLE JobOrderDoorContainer_Temp
>TRUNCATE TABLE JobOrderITM_Temp
>TRUNCATE TABLE JobOrderITMContainer_Temp
>TRUNCATE TABLE JTQ510
>TRUNCATE TABLE JTQ520
>TRUNCATE TABLE NAIBFF
>TRUNCATE TABLE NAIBFF_DeleteTable
>TRUNCATE TABLE SpecialRequirementMilestone
>declare @.tablename nvarchar(125)
>declare cur CURSOR
>FORWARD_ONLY
>STATIC
>READ_ONLY
>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>and name not like 'conflict_%' and name not like 'dt%' and name not like
>'MS%' and name not like 'sys%'
>order by name
>open cur
>fetch next from cur
>into @.tablename
>while @.@.fetch_status = 0
>BEGIN
>PRINT 'REINDEXING ' + @.tablename
>dbcc dbreindex (@.tablename)
>fetch next from cur
>into @.tablename
>END
>CLOSE cur
>DEALLOCATE cur
>|||It ended up being
Set ArithAbort ON
needed to be set.
How come this error isn't documented anywhere "official?
I'm also gettinga lot of error 1933 and 1935
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>I wouldn't rule out the ANSI settings. This issue has come
> up before. SQL Agent and Query Analyzer use different set
> options/ansi settings when they connect to SQL Server.
> Whatever the set option errors you are seeing as missing,
> add to your job script and make sure they are just before
> the calling of your stored procedures.
> -Sue
> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>>SQL Server 2000 Standard Sp3. on Windows 2003
>>What is Error 1934.
>>I can't find documentaiton on this anywhere. There's only one article in
>>KB
>>in relation to merge replication. And other stuff on the web about ANSI
>>settings and indexed views (which I'm not using).
>>I get these errors in different contexts.
>>Here's one:
>>Profiler screenshot
>>http://www.vinnychi.com/vince/images/sql.jpg
>>This is a stored procedure that SQL Agent is running from a job. When i
>>run
>>it this way, I get the 1934 error. If I run it from Query Analyzer I get
>>no
>>error, and it runs to completion.
>>SP:
>>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>>AS
>>exec UTIL_DEFRAG
>> RETURN
>>
>>========>>UTIL_DEFRAG is:
>>CREATE procedure UTIL_DEFRAG
>>AS
>>TRUNCATE TABLE BLCSO
>>TRUNCATE TABLE IBFF_BOL_Temp
>>TRUNCATE TABLE IBFF_Container_Temp
>>TRUNCATE TABLE IBFFContainerImportPhase2
>>TRUNCATE TABLE JobOrderDoor_Temp
>>TRUNCATE TABLE JobOrderDoorContainer_Temp
>>TRUNCATE TABLE JobOrderITM_Temp
>>TRUNCATE TABLE JobOrderITMContainer_Temp
>>TRUNCATE TABLE JTQ510
>>TRUNCATE TABLE JTQ520
>>TRUNCATE TABLE NAIBFF
>>TRUNCATE TABLE NAIBFF_DeleteTable
>>TRUNCATE TABLE SpecialRequirementMilestone
>>declare @.tablename nvarchar(125)
>>declare cur CURSOR
>>FORWARD_ONLY
>>STATIC
>>READ_ONLY
>>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>>and name not like 'conflict_%' and name not like 'dt%' and name not like
>>'MS%' and name not like 'sys%'
>>order by name
>>open cur
>>fetch next from cur
>>into @.tablename
>>while @.@.fetch_status = 0
>>BEGIN
>>PRINT 'REINDEXING ' + @.tablename
>>dbcc dbreindex (@.tablename)
>>fetch next from cur
>>into @.tablename
>>END
>>CLOSE cur
>>DEALLOCATE cur
>|||Based on your errors, I would guess that you have computed
columns. ANSI settings and computed columns is addressed in
books online under the topics:
SET Options That Affect Results
Creating Indexes on Computed Columns
The Arithabort setting is addressed in the books online
topic Create Index
The article does mention the following:
It is strongly recommended that the ARITHABORT user option
be set server-wide to ON as soon as the first indexed view
or index on a computed column is created in any database on
the server.
You can also find related information in the following:
http://www.microsoft.com/sql/techinfo/tips/development/july2.asp
-Sue
On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:
>It ended up being
>Set ArithAbort ON
>needed to be set.
>
>How come this error isn't documented anywhere "official?
>I'm also gettinga lot of error 1933 and 1935
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>>I wouldn't rule out the ANSI settings. This issue has come
>> up before. SQL Agent and Query Analyzer use different set
>> options/ansi settings when they connect to SQL Server.
>> Whatever the set option errors you are seeing as missing,
>> add to your job script and make sure they are just before
>> the calling of your stored procedures.
>> -Sue
>> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
>> <remove@.pacelviATspeakeasy.net> wrote:
>>SQL Server 2000 Standard Sp3. on Windows 2003
>>What is Error 1934.
>>I can't find documentaiton on this anywhere. There's only one article in
>>KB
>>in relation to merge replication. And other stuff on the web about ANSI
>>settings and indexed views (which I'm not using).
>>I get these errors in different contexts.
>>Here's one:
>>Profiler screenshot
>>http://www.vinnychi.com/vince/images/sql.jpg
>>This is a stored procedure that SQL Agent is running from a job. When i
>>run
>>it this way, I get the 1934 error. If I run it from Query Analyzer I get
>>no
>>error, and it runs to completion.
>>SP:
>>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>>AS
>>exec UTIL_DEFRAG
>> RETURN
>>
>>========>>UTIL_DEFRAG is:
>>CREATE procedure UTIL_DEFRAG
>>AS
>>TRUNCATE TABLE BLCSO
>>TRUNCATE TABLE IBFF_BOL_Temp
>>TRUNCATE TABLE IBFF_Container_Temp
>>TRUNCATE TABLE IBFFContainerImportPhase2
>>TRUNCATE TABLE JobOrderDoor_Temp
>>TRUNCATE TABLE JobOrderDoorContainer_Temp
>>TRUNCATE TABLE JobOrderITM_Temp
>>TRUNCATE TABLE JobOrderITMContainer_Temp
>>TRUNCATE TABLE JTQ510
>>TRUNCATE TABLE JTQ520
>>TRUNCATE TABLE NAIBFF
>>TRUNCATE TABLE NAIBFF_DeleteTable
>>TRUNCATE TABLE SpecialRequirementMilestone
>>declare @.tablename nvarchar(125)
>>declare cur CURSOR
>>FORWARD_ONLY
>>STATIC
>>READ_ONLY
>>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>>and name not like 'conflict_%' and name not like 'dt%' and name not like
>>'MS%' and name not like 'sys%'
>>order by name
>>open cur
>>fetch next from cur
>>into @.tablename
>>while @.@.fetch_status = 0
>>BEGIN
>>PRINT 'REINDEXING ' + @.tablename
>>dbcc dbreindex (@.tablename)
>>fetch next from cur
>>into @.tablename
>>END
>>CLOSE cur
>>DEALLOCATE cur
>>
>|||Sue: Thanks for your leads. A large part of the problem was ArithAbort.
Thank you very much.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4c6fv0l327hq7dm20npbajt0vv2nsf10u1@.4ax.com...
> Based on your errors, I would guess that you have computed
> columns. ANSI settings and computed columns is addressed in
> books online under the topics:
> SET Options That Affect Results
> Creating Indexes on Computed Columns
> The Arithabort setting is addressed in the books online
> topic Create Index
> The article does mention the following:
> It is strongly recommended that the ARITHABORT user option
> be set server-wide to ON as soon as the first indexed view
> or index on a computed column is created in any database on
> the server.
> You can also find related information in the following:
> http://www.microsoft.com/sql/techinfo/tips/development/july2.asp
> -Sue
> On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>>It ended up being
>>Set ArithAbort ON
>>needed to be set.
>>
>>How come this error isn't documented anywhere "official?
>>I'm also gettinga lot of error 1933 and 1935
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.4ax.com...
>>I wouldn't rule out the ANSI settings. This issue has come
>> up before. SQL Agent and Query Analyzer use different set
>> options/ansi settings when they connect to SQL Server.
>> Whatever the set option errors you are seeing as missing,
>> add to your job script and make sure they are just before
>> the calling of your stored procedures.
>> -Sue
>> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
>> <remove@.pacelviATspeakeasy.net> wrote:
>>SQL Server 2000 Standard Sp3. on Windows 2003
>>What is Error 1934.
>>I can't find documentaiton on this anywhere. There's only one article
>>in
>>KB
>>in relation to merge replication. And other stuff on the web about ANSI
>>settings and indexed views (which I'm not using).
>>I get these errors in different contexts.
>>Here's one:
>>Profiler screenshot
>>http://www.vinnychi.com/vince/images/sql.jpg
>>This is a stored procedure that SQL Agent is running from a job. When i
>>run
>>it this way, I get the 1934 error. If I run it from Query Analyzer I get
>>no
>>error, and it runs to completion.
>>SP:
>>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>>AS
>>exec UTIL_DEFRAG
>> RETURN
>>
>>========>>UTIL_DEFRAG is:
>>CREATE procedure UTIL_DEFRAG
>>AS
>>TRUNCATE TABLE BLCSO
>>TRUNCATE TABLE IBFF_BOL_Temp
>>TRUNCATE TABLE IBFF_Container_Temp
>>TRUNCATE TABLE IBFFContainerImportPhase2
>>TRUNCATE TABLE JobOrderDoor_Temp
>>TRUNCATE TABLE JobOrderDoorContainer_Temp
>>TRUNCATE TABLE JobOrderITM_Temp
>>TRUNCATE TABLE JobOrderITMContainer_Temp
>>TRUNCATE TABLE JTQ510
>>TRUNCATE TABLE JTQ520
>>TRUNCATE TABLE NAIBFF
>>TRUNCATE TABLE NAIBFF_DeleteTable
>>TRUNCATE TABLE SpecialRequirementMilestone
>>declare @.tablename nvarchar(125)
>>declare cur CURSOR
>>FORWARD_ONLY
>>STATIC
>>READ_ONLY
>>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>>and name not like 'conflict_%' and name not like 'dt%' and name not like
>>'MS%' and name not like 'sys%'
>>order by name
>>open cur
>>fetch next from cur
>>into @.tablename
>>while @.@.fetch_status = 0
>>BEGIN
>>PRINT 'REINDEXING ' + @.tablename
>>dbcc dbreindex (@.tablename)
>>fetch next from cur
>>into @.tablename
>>END
>>CLOSE cur
>>DEALLOCATE cur
>>
>
Subscribe to:
Posts (Atom)