Error from Maintenance plan

Hi there,
I get an error from the maintenance plan, when it every weekend reorganizes
the data and index tables:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I wonder if it could be because i have an calculated field in the table,
[FullName] AS ([LastName] + ', ' + [FirstName]) ?
The table is create as follows:
CREATE TABLE [dbo].[Assigner] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
[FullName] AS ([LastName] + ', ' + [FirstName]) ,
[CustomerGroup_id] [int] NOT NULL ,
[Deactivated] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 70 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Assigner] ADD
CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
(
[CustomerGroup_id]
) REFERENCES [dbo].[CustomerGroup] (
[id]
)
GOJohn
This SELECT stetement will produce a syntax error
You may want to change it as SELECT ([LastName] + ', ' + [FirstName]) AS
[FullName] FROM .....
"John Boghossian" <john.boghossian@.investorab.com> wrote in message
news:eS2iwyljEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Hi there,
> I get an error from the maintenance plan, when it every weekend
reorganizes
> the data and index tables:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
>
> The table is create as follows:
> CREATE TABLE [dbo].[Assigner] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ,
> [CustomerGroup_id] [int] NOT NULL ,
> [Deactivated] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
> CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 70 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] ADD
> CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
> (
> [CustomerGroup_id]
> ) REFERENCES [dbo].[CustomerGroup] (
> [id]
> )
> GO
>|||John,
You most probably have either an index on that computed column, or SQL Server has created statistics
on the column. In those situations, whenever you need to rebuild such an index or statistics, the
connection need special SET options defined. For some reason, Maint wizard doesn't set these. You
could try to drop the statistics (DROP STATISTICS), chances are that they will re-added. You could
tell SQL Server to not create statistics, but the issue might pop up somewhere else. To be safe,
perform the DBCC DBREINDEX commands from your own SQL Server Agent job and make sure to set the SET
commands as they need to be set. I'm sure there's a KB on this topic if you search for appropriate
keywords...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Boghossian" <john.boghossian@.investorab.com> wrote in message
news:eS2iwyljEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Hi there,
> I get an error from the maintenance plan, when it every weekend reorganizes
> the data and index tables:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
>
> The table is create as follows:
> CREATE TABLE [dbo].[Assigner] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL ,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ,
> [CustomerGroup_id] [int] NOT NULL ,
> [Deactivated] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
> CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 70 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] ADD
> CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
> (
> [CustomerGroup_id]
> ) REFERENCES [dbo].[CustomerGroup] (
> [id]
> )
> GO
>|||John,
Go with TIbors suggestions... A computed column which is index, etc , then
those settings matter during maint plan stuff... I would suspect you are
getting the error on index rebuild..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"John Boghossian" <john.boghossian@.investorab.com> wrote in message
news:eS2iwyljEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Hi there,
> I get an error from the maintenance plan, when it every weekend
reorganizes
> the data and index tables:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
>
> The table is create as follows:
> CREATE TABLE [dbo].[Assigner] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (25) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [LastName] [varchar] (30) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ,
> [CustomerGroup_id] [int] NOT NULL ,
> [Deactivated] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] WITH NOCHECK ADD
> CONSTRAINT [PK_Assigner] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 70 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Assigner] ADD
> CONSTRAINT [FK_Assigner_CustomerGroup] FOREIGN KEY
> (
> [CustomerGroup_id]
> ) REFERENCES [dbo].[CustomerGroup] (
> [id]
> )
> GO
>|||> I wonder if it could be because i have an calculated field in the table,
> [FullName] AS ([LastName] + ', ' + [FirstName]) ?
Why would you have this calculated column in the table? I would be tempted
to drop it and run this concatenation in the SELECT proc(s) or even a view.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Error Expanding tables in Linked Server to Pervasive

Pleeeease help? I get an error when expanding the Tables object under linked servers in Enterprise Manager on a MSSQL2000 server.

I have a standard install of the Pervasive.SQL V8 data engine and DemoData db on my local PC. The SQL Server is also on my local PC.

I manage to create the Linked Server using the MSSQL Enterprise Manager. I used the following configuration:

Provider Name: Pervasive.SQL V8 OLE DB Provider
Product Name: Pervasive.SQL V8
Data source: DemoData
Provider string: <blank>
Location: workstation03
Catalog: <blank>

However when I try to expand the tables object under Linked Servers in the Enterprise manager I get the following error.

Error 7311: Could not obtain the schema rowset for the OLE DB provider 'UNKNOWN'. The provider supports the interface, but retuns a failure code when it is used. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80040155 ].

How would I fix this?

Thank you.moving to microsoft sql server forumsql

Error Deploying Cube

I have just installed Evaluation version of SQL Server 2005. Created a new database with two tables. Created new project with data source pointing to localhost and build a data source view containning tables from database created earlier. After creating the cube, I get following error while deploying the Cube:

OLE DB error: OLE DB or ODBC error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Any help!

Regards.

Are you deploying locally or remotely?

Is the user you are trying to deploy as set up in the SQLSERVER2005MSOLAPUser group on the AS server?

|||

It is local and user is member of group you mentioned.

My problem was due to the wrong impersonation information in data source. I have corrected it and now everything is fine.

Thnks.

error connecting to msde

Hi everybody this is my problem:
I've just installed MSDE2000 on my machine an it all works fine.
I'm able to create tables, storedprocedures whithin an access project, but i
cant' connect any client, i tried to use the ip address and also the
computer name but it doesnt' connect.
I receive a strange error:
(it's a free traslation from italian)
not supported interface
[dbbetlib][connectionOpen]Connect())] Sql server not exist o denied access
the parameters used for the installations were:
setup SAPWD="mypwd"
SECURITYMODE=SQL
DISABLENETWORKPROTOCOLS=0
hi,
ciroteo wrote:
> Hi everybody this is my problem:
> I've just installed MSDE2000 on my machine an it all works fine.
> I'm able to create tables, storedprocedures whithin an access
> project, but i cant' connect any client, i tried to use the ip
> address and also the computer name but it doesnt' connect.
> I receive a strange error:
> (it's a free traslation from italian)
> not supported interface
> [dbbetlib][connectionOpen]Connect())] Sql server not exist o denied
> access
> the parameters used for the installations were:
> setup SAPWD="mypwd"
> SECURITYMODE=SQL
> DISABLENETWORKPROTOCOLS=0
please have a look at
http://support.microsoft.com/default...06&Product=sql
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> ha scritto nel messaggio
news:36bjn6F4vqnh0U1@.individual.net...
> hi,
> ciroteo wrote:
> please have a look at
> http://support.microsoft.com/default...06&Product=sql
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Ciao andrea grazie per la risposta, credo sia un problema di windows
firewall, perch avendo fatto un'altra installazione su un pc dove
disabilitato funziona tutto correttamente ...
bah!
ciao
|||hi,
ciroteo wrote:
> Ciao andrea grazie per la risposta, credo sia un problema di windows
> firewall, perch avendo fatto un'altra installazione su un pc dove
> disabilitato funziona tutto correttamente ...
> bah!
> ciao
as we are on an english NG, it's better to continue in english language (or
the better sort of... :D)
if it may depends on Windows Firewall, you can have a look
http://support.microsoft.com/default.aspx?kbid=841249
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> ha scritto nel messaggio
news:36bp7qF4v61k0U1@.individual.net...
> hi,
> ciroteo wrote:
> as we are on an english NG, it's better to continue in english language
> (or
> the better sort of... :D)
> if it may depends on Windows Firewall, you can have a look
> http://support.microsoft.com/default.aspx?kbid=841249
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Ok sorry! You are right.
I'll check it out, thank you so much.
|||"ciroteo" <ciroteo_chiocciola@.libero.it> ha scritto nel messaggio
news:ev03tTRCFHA.392@.TK2MSFTNGP14.phx.gbl...
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> ha scritto nel messaggio
> news:36bp7qF4v61k0U1@.individual.net...
>
> Ok sorry! You are right.
> I'll check it out, thank you so much.
>
>
If the pc isn't in the same domain as server, or it has a different
operative system (i.e. win98) is it possbile to receive a connection error?
|||hi,
ciroteo wrote:

> If the pc isn't in the same domain as server, or it has a different
> operative system (i.e. win98) is it possbile to receive a connection
> error?
as regard different OSs, this is not a problem at all, as all the related
traffic is handled by the MDAC stack and the network protocols subsystem...
as regard the domain... it depends... you can connect to web hosted servers,
which for sure are not on the same domain of the remote client, but the
connecting pc has to be recognized some way by the server... as trusted
authenticated connections are not available in this scenario, SQL Server
authenticated connection are usually used...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Error Condition for Updates

Hi,

I have a series of Updates that I perform on 7 tables. I would like to execute these updates on a nighly basis via a job. I have the update statements lined up in a stored proc.

So I have -

Create Proc Update
AS
SET NOCOUNT ON

DECLARE @.RETURNVALUE int
DECLARE @.ERRORMESSAGETXT varchar(510)
DECLARE @.ERRORNUM int

set @.RETURNVALUE = 0

BEGIN --Start here

Update table1
Update table2
.
.
.
Update table7

SELECT @.ERRORNUM = @.@.ERROR
IF @.ERRORNUM = 0
SELECT @.RETURNVALUE = 0
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE
END -- END HERE

--

Is the error checking at the appropriate place? Should I have error conditions after every update? If yes then what condition do I check for?

Thanks@.@.ERROR only shows the result of the most recent statement. It is cleared for the next statement. Therefor your code is only going to indicate whether an error occured on Update Table7.

You will need to check for an error after each critical step, and then store the results of the error check. You can store it in a single value like:
@.ERROR_MONITOR = @.ERROR_MONITOR + @.@.ERROR
...which will result in a value greater than zero if any errors occured. Or you can store the results of each check separately (@.UPDATETABLE1ERROR, @.UPDATETABLE2ERROR..etc).|||So is this correct. Check the where clause in the ELSE part and the statement after Update Table 7.
----
Update table1
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR
Update table2
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR
.
.
.
Update table7
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR

IF @.ERRORNUM = 0
SELECT @.RETURNVALUE = 0
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR (OR is this @.ERRORNUM)
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE

Let me know.

Thanks|||@.ERRORNUM holds the sum of all the error message IDs, so it can be used to determine whether any error occured but if more than one error occured then it is useless for looking up the error description.

"SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages] WHERE error = @.@.ERROR "
...will, again, only give you the error message for the last error to occur.

If you want to be able to handle multiple errors, create a varchar variable and append any error text to it after each critical step.

Depending on your process, you may just want to exit the procedure after the first error...|||Don't call your procedure "Update". And I don't think you could actually compile what you posted.

And if you are really interested in capturing all the errors while continuing to do your updates, then you'll need a @.temptable to acquire a value of @.@.error on each update. At the end you would parse a string of all errors by joining this table with sysmessages and scrolling through it while concatenating [description] into a varchar variable.

Error code: 0x80004005

I created a package that refreshes tables on one SQL Server to another SQL Server. First, I use an ExecuteSQL task to truncate the tables. Then I use a Data Flow task to copy the tables from one server to the other. Finally I update a log table. It was working fine with four tables. I added another table to the refresh and now I get these error messages:

[Source - RbcAcctSegment [1096]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".

[Source - RbcAcctSegment [1096]] Error: Opening a rowset for "[dbo].[RbcAcctSegment]" failed. Check that the object exists in the database.

[DTS.Pipeline] Error: component "Source - RbcAcctSegment" (1096) failed the pre-execute phase and returned error code 0xC02020E8.

RbcAcctSegment is the new table that I added. The errors occur in the Data Flow task. We have tried running this on a different workstation with the same results.

What would cause this?

Fred

Try changing the connection propery 'Retain Same Connection' to FALSE.|||The 'Retain Same Connection' property is already set to FALSE for both Source and Destination Connection Managers.|||

I believe I found the problem.

I made a few changes in the Data Flow task. In the Properties of the Destination components I changed the OpenRowset property to include the database name beside the table name. Not all the Destination components had the database name.

Before it was [dbo].[TableName] and I changed it to [DatabaseName].[dbo].[TableName].

Fred

|||

It wasn't the problem. It worked one time.

Is there a limit on how many tables you can copy in one DataFlow task?

Fred

|||Now that you mention, I do remember we did have a similar error when we had around 20 in a data flow and then we moved some of them out to a new data flow and it worked fine. I had to put a precedence between the two.

Error code: 0x80004005

I created a package that refreshes tables on one SQL Server to another SQL Server. First, I use an ExecuteSQL task to truncate the tables. Then I use a Data Flow task to copy the tables from one server to the other. Finally I update a log table. It was working fine with four tables. I added another table to the refresh and now I get these error messages:

[Source - RbcAcctSegment [1096]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".

[Source - RbcAcctSegment [1096]] Error: Opening a rowset for "[dbo].[RbcAcctSegment]" failed. Check that the object exists in the database.

[DTS.Pipeline] Error: component "Source - RbcAcctSegment" (1096) failed the pre-execute phase and returned error code 0xC02020E8.

RbcAcctSegment is the new table that I added. The errors occur in the Data Flow task. We have tried running this on a different workstation with the same results.

What would cause this?

Fred

Try changing the connection propery 'Retain Same Connection' to FALSE.|||The 'Retain Same Connection' property is already set to FALSE for both Source and Destination Connection Managers.|||

I believe I found the problem.

I made a few changes in the Data Flow task. In the Properties of the Destination components I changed the OpenRowset property to include the database name beside the table name. Not all the Destination components had the database name.

Before it was [dbo].[TableName] and I changed it to [DatabaseName].[dbo].[TableName].

Fred

|||

It wasn't the problem. It worked one time.

Is there a limit on how many tables you can copy in one DataFlow task?

Fred

|||Now that you mention, I do remember we did have a similar error when we had around 20 in a data flow and then we moved some of them out to a new data flow and it worked fine. I had to put a precedence between the two.