Question transport-level error

Hi all,

I'm having a little problem with my sqlserver 2005.

I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.

Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO

USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO

Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO

And drops the user by executing following:

Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]

-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I'f i then runs the createscript again, the user is created correctly.

Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?

Best Regards
Henrik

Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?

Thanks
Laurentiu

|||

Hi Cristofor,

Thanks for you reply. I'v just tried that and get following:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

But under "Server Properties" - "Connections"

The "Allow Remote connections to this server" is checked, with a query timeout on 600

|||

In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I have just enabled this it was disabled, but still im getting the same error.

But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"

But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?

Soucre:

.Net SqlClient Data Provider

Error Message:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

|||

My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.

I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.

But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.

|||

Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?

If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.

But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....

So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.

But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"

But for the moment im happy :)

|||

I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.

-Raul Garcia

SDE/T

SQL Server Engine

Question transport-level error

Hi all,

I'm having a little problem with my sqlserver 2005.

I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.

Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO

USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO

Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO

And drops the user by executing following:

Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]

-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I'f i then runs the createscript again, the user is created correctly.

Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?

Best Regards
Henrik

Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?

Thanks
Laurentiu

|||

Hi Cristofor,

Thanks for you reply. I'v just tried that and get following:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

But under "Server Properties" - "Connections"

The "Allow Remote connections to this server" is checked, with a query timeout on 600

|||

In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I have just enabled this it was disabled, but still im getting the same error.

But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"

But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?

Soucre:

.Net SqlClient Data Provider

Error Message:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

|||

My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.

I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.

But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.

|||

Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?

If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.

But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....

So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.

But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"

But for the moment im happy :)

|||

I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.

-Raul Garcia

SDE/T

SQL Server Engine

sql

Question regarding running a stored procedure as a job step

I am running a stored procedure as a job step and in the stored procedure I use return to pass one of several possible values when there is an error in processing (not an system error) so that the job step will fail. However, even when I return a non-zero value using return the job step completes as successful. What should I be doing so that the job step picks up the non-zero value and then indicates the step failed?

Try using a RAISERROR with a severity greater than 10.

-Sue

|||Thank you very much Sue

question on tasks

hi,

if a package fails on error, would i be able to know code wise which particular task failed?
also, if the task is using a connection manager, how will i be able to access that particular connection object?

If you turn on logging then you will know what failed. Does that not suffice?

-Jamie

|||

i'm not sure if this answers you questions, however...

you could execute the package programatically and trap for a DtsTaskException that may be generated. then, you can determine which task caused the error by checking the subcomponent property of the dtserror object.

i believe that you should already know which connection manager a task uses because (as far as i know) this property cannot be set dynamically.

|||

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

|||

Ranier wrote:

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

do you really want to do this? why don't you just execute the package within a transaction and then re-execute it if the package fails?|||

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

|||

Ranier wrote:

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

it may be possible, but unnecessary.

if i understand you correctly, you want to re-execute a task if it fails. why do you want to do this? wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it?

|||

the component that i want to make should be on a script task. i need to be able to reuse it on every package just drag and drop.

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

|||

Ranier wrote:

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it? you can use transactions and checkpoints to accomplish this.

Question on QUOTED_IDENTIFIER

I have a very strange problem, it only happen to one SQL Server, other
SQL Server seems to be fine

I got the following error when trying to run a sp against one of the
SQL Server:

SELECT failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
use with indexed views and/or indexes on computed columns and/or query
notifications and/or xml data type methods.

If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
everything works fine, but the questions is why should I do that? and
why it only happen to only one SQLServer ? The database option on
QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)

Thanks in advance.
John

Enclose is the statement that create the database

if db_id('testdb') is not null
drop database [testdb]
go
begin
USE [master]

CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
SIZE = 29504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',

@.new_cmptlevel=90

ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [testdb] SET ANSI_NULLS OFF

ALTER DATABASE [testdb] SET ANSI_PADDING OFF

ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF

ALTER DATABASE [testdb] SET ARITHABORT OFF

ALTER DATABASE [testdb] SET AUTO_CLOSE OFF

ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE [testdb] SET AUTO_SHRINK OFF

ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [testdb] SET ENABLE_BROKER

ALTER DATABASE [testdb]
SET
AUTO_UPDATE_STATISTICS_ASYNC ON

ALTER DATABASE [testdb]
SET
DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [testdb] SET TRUSTWORTHY OFF

ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [testdb] SET READ_WRITE

ALTER DATABASE [testdb] SET RECOVERY FULL

ALTER DATABASE [testdb] SET MULTI_USER

ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [testdb] SET DB_CHAINING OFF

endAnother interesting thing, on the server that does not generate the
error, even I put SET QUOTED_IDENTIFIER ON inside the SP, the sp still
work without any error, so it looks on the particular sever the
QUOTED_IDENTIFIER has to be off OFF inside the SP, is this because of a
Server Side setting??

John wrote:

Quote:

Originally Posted by

I have a very strange problem, it only happen to one SQL Server, other
SQL Server seems to be fine
>
I got the following error when trying to run a sp against one of the
SQL Server:
>
SELECT failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
use with indexed views and/or indexes on computed columns and/or query
notifications and/or xml data type methods.
>
If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
everything works fine, but the questions is why should I do that? and
why it only happen to only one SQLServer ? The database option on
QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)
>
Thanks in advance.
John
>
Enclose is the statement that create the database
>
if db_id('testdb') is not null
drop database [testdb]
go
begin
USE [master]
>
CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
SIZE = 29504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',
>
@.new_cmptlevel=90
>
ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF
>
ALTER DATABASE [testdb] SET ANSI_NULLS OFF
>
ALTER DATABASE [testdb] SET ANSI_PADDING OFF
>
ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF
>
ALTER DATABASE [testdb] SET ARITHABORT OFF
>
ALTER DATABASE [testdb] SET AUTO_CLOSE OFF
>
ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON
>
ALTER DATABASE [testdb] SET AUTO_SHRINK OFF
>
ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON
>
ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF
>
ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL
>
ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF
>
ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF
>
ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF
>
ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF
>
ALTER DATABASE [testdb] SET ENABLE_BROKER
>
ALTER DATABASE [testdb]
SET
AUTO_UPDATE_STATISTICS_ASYNC ON
>
ALTER DATABASE [testdb]
SET
DATE_CORRELATION_OPTIMIZATION OFF
>
ALTER DATABASE [testdb] SET TRUSTWORTHY OFF
>
ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON
>
ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE
>
ALTER DATABASE [testdb] SET READ_WRITE
>
ALTER DATABASE [testdb] SET RECOVERY FULL
>
ALTER DATABASE [testdb] SET MULTI_USER
>
ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM
>
ALTER DATABASE [testdb] SET DB_CHAINING OFF
>
end

|||I think that the state of QUOTED_IDENTIFIER that is used for a stored
procedure is the state that was in place WHEN the stored procedure was
created on the server. The QUOTED_IDENTIFIER state is saved with the stored
procedure metadata.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"John" <johnxhc@.yahoo.comwrote in message
news:1153494983.858599.96180@.m79g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Another interesting thing, on the server that does not generate the
error, even I put SET QUOTED_IDENTIFIER ON inside the SP, the sp still
work without any error, so it looks on the particular sever the
QUOTED_IDENTIFIER has to be off OFF inside the SP, is this because of a
Server Side setting??
>
John wrote:

Quote:

Originally Posted by

>I have a very strange problem, it only happen to one SQL Server, other
>SQL Server seems to be fine
>>
>I got the following error when trying to run a sp against one of the
>SQL Server:
>>
>SELECT failed because the following SET options have incorrect
>settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
>use with indexed views and/or indexes on computed columns and/or query
>notifications and/or xml data type methods.
>>
>If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
>everything works fine, but the questions is why should I do that? and
>why it only happen to only one SQLServer ? The database option on
>QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)
>>
>Thanks in advance.
>John
>>
>Enclose is the statement that create the database
>>
>if db_id('testdb') is not null
>drop database [testdb]
>go
>begin
>USE [master]
>>
>CREATE DATABASE [testdb] ON PRIMARY
>( NAME = N'testdb',
> FILENAME = N'C:\Program Files\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
> SIZE = 8192KB ,
> MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
>LOG ON
>( NAME = N'testdb_log',
> FILENAME = N'C:\Program Files\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
> SIZE = 29504KB ,
> MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
>COLLATE SQL_Latin1_General_CP1_CI_AS
>EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',
>>
>@.new_cmptlevel=90
>>
>ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF
>>
>ALTER DATABASE [testdb] SET ANSI_NULLS OFF
>>
>ALTER DATABASE [testdb] SET ANSI_PADDING OFF
>>
>ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF
>>
>ALTER DATABASE [testdb] SET ARITHABORT OFF
>>
>ALTER DATABASE [testdb] SET AUTO_CLOSE OFF
>>
>ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON
>>
>ALTER DATABASE [testdb] SET AUTO_SHRINK OFF
>>
>ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON
>>
>ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF
>>
>ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL
>>
>ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF
>>
>ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF
>>
>ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF
>>
>ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF
>>
>ALTER DATABASE [testdb] SET ENABLE_BROKER
>>
>ALTER DATABASE [testdb]
> SET
>AUTO_UPDATE_STATISTICS_ASYNC ON
>>
>ALTER DATABASE [testdb]
> SET
>DATE_CORRELATION_OPTIMIZATION OFF
>>
>ALTER DATABASE [testdb] SET TRUSTWORTHY OFF
>>
>ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON
>>
>ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE
>>
>ALTER DATABASE [testdb] SET READ_WRITE
>>
>ALTER DATABASE [testdb] SET RECOVERY FULL
>>
>ALTER DATABASE [testdb] SET MULTI_USER
>>
>ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM
>>
>ALTER DATABASE [testdb] SET DB_CHAINING OFF
>>
>end

>

|||John (johnxhc@.yahoo.com) writes:

Quote:

Originally Posted by

I have a very strange problem, it only happen to one SQL Server, other
SQL Server seems to be fine
>
I got the following error when trying to run a sp against one of the
SQL Server:
>
SELECT failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
use with indexed views and/or indexes on computed columns and/or query
notifications and/or xml data type methods.
>
If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
everything works fine, but the questions is why should I do that? and
why it only happen to only one SQLServer ? The database option on
QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)

There are a couple of features in SQL Server that requires that the
setting QUOTED_IDENTIIER is ON. They are:

o Indexed views.
o Indexed computed columns.
o XQuery.

Important to understand is that the setting of QUOTED_IDENTIFIER is saved
with the procedure. The same applies to the ANSI_NULLS setting, whereas
for other SET options the run-time setting apply. (Save ANSI_PADDING where
it depends on the setting when the table column was created.)

You can determine the create-time setting for a stored procedure with
this SELECT:

SELECT uses_quoted_identifier, uses_ansi_nulls
FROM sys.sql_modules
WHERE object_id = object_id('yoursp')

As to why a procedure was created with QUOTED_IDENTIFIER off, the most
likely reason in SQL 2005 is that the procedure was loaded through
SQLCMD, which by default has QUOTED_IDENTIFIER off. (Always use the -I
option with SQLCMD to circumvent this problem.) Another possibility is
that the database origins from SQL 2000, where also Enterprise Manager
had QUOTED_IDENTIFIER (and ANSI_NULLS) off by default.

I suspect that the reason it appearst to work if you put SET
QUOTED_IDENTIFIER OFF in the procedure is simply because you reload
the procedure with the correct setting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Another improvement in your code is to use the ANSI/ISO double quote
marks instead of the dialect square brackets and single quotes.

Question on Error message

Running SQL Server 2000 and new to the DBMS. Getting a consistent error
messahe on a test system
'Backup failed to complete the command EmptyTransactionLog1"
I checked the documentation, and the OS logs but no additional info.
Seems to be happening either at startup ot shortly thereafter.
Any thoughts on this?
As a newbie, one (of many) things I find a bit confusing in SQL Server
is that if someone clicked through the database...selected all
tasks...create backup etc...that that backup would not show up in a
database maintenance plan. If you go into that wizard anything already
set to run does not show.
Is there any way to determine if somebody has any type of backup taking
place on a db other than log messages?
Thanks in advance.
GerryIn Enterprise Manager, Under Management, you will find both Backup jobs and
Maintenance plans.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
> Running SQL Server 2000 and new to the DBMS. Getting a consistent error
> messahe on a test system
> 'Backup failed to complete the command EmptyTransactionLog1"
> I checked the documentation, and the OS logs but no additional info.
> Seems to be happening either at startup ot shortly thereafter.
> Any thoughts on this?
> As a newbie, one (of many) things I find a bit confusing in SQL Server
> is that if someone clicked through the database...selected all
> tasks...create backup etc...that that backup would not show up in a
> database maintenance plan. If you go into that wizard anything already
> set to run does not show.
> Is there any way to determine if somebody has any type of backup taking
> place on a db other than log messages?
> Thanks in advance.
> Gerry
>|||Arnie:
I don't see anything in either place. I assume that means that no
backups are taking place.
What I'm not clear on is if no backups of any type are taking
place...why am I getting the 'backup failed to complete the comand'
error message
Thanks
Gerry
Arnie Rowland wrote:
> In Enterprise Manager, Under Management, you will find both Backup jobs and
> Maintenance plans.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
> > Running SQL Server 2000 and new to the DBMS. Getting a consistent error
> > messahe on a test system
> >
> > 'Backup failed to complete the command EmptyTransactionLog1"
> >
> > I checked the documentation, and the OS logs but no additional info.
> > Seems to be happening either at startup ot shortly thereafter.
> >
> > Any thoughts on this?
> >
> > As a newbie, one (of many) things I find a bit confusing in SQL Server
> > is that if someone clicked through the database...selected all
> > tasks...create backup etc...that that backup would not show up in a
> > database maintenance plan. If you go into that wizard anything already
> > set to run does not show.
> >
> > Is there any way to determine if somebody has any type of backup taking
> > place on a db other than log messages?
> >
> > Thanks in advance.
> >
> > Gerry
> >|||Check to see if there are Maintenance Plans configured. Backup could be part
of a Maintenance Plan.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...
> Arnie:
> I don't see anything in either place. I assume that means that no
> backups are taking place.
> What I'm not clear on is if no backups of any type are taking
> place...why am I getting the 'backup failed to complete the comand'
> error message
> Thanks
> Gerry
>
> Arnie Rowland wrote:
>> In Enterprise Manager, Under Management, you will find both Backup jobs
>> and
>> Maintenance plans.
>> --
>> Arnie Rowland
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
>> > Running SQL Server 2000 and new to the DBMS. Getting a consistent error
>> > messahe on a test system
>> >
>> > 'Backup failed to complete the command EmptyTransactionLog1"
>> >
>> > I checked the documentation, and the OS logs but no additional info.
>> > Seems to be happening either at startup ot shortly thereafter.
>> >
>> > Any thoughts on this?
>> >
>> > As a newbie, one (of many) things I find a bit confusing in SQL Server
>> > is that if someone clicked through the database...selected all
>> > tasks...create backup etc...that that backup would not show up in a
>> > database maintenance plan. If you go into that wizard anything already
>> > set to run does not show.
>> >
>> > Is there any way to determine if somebody has any type of backup taking
>> > place on a db other than log messages?
>> >
>> > Thanks in advance.
>> >
>> > Gerry
>> >
>|||No, there aren't.
Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
command?
Arnie Rowland wrote:
> Check to see if there are Maintenance Plans configured. Backup could be part
> of a Maintenance Plan.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...
> > Arnie:
> >
> > I don't see anything in either place. I assume that means that no
> > backups are taking place.
> >
> > What I'm not clear on is if no backups of any type are taking
> > place...why am I getting the 'backup failed to complete the comand'
> > error message
> >
> > Thanks
> > Gerry
> >
> >
> > Arnie Rowland wrote:
> >> In Enterprise Manager, Under Management, you will find both Backup jobs
> >> and
> >> Maintenance plans.
> >>
> >> --
> >> Arnie Rowland
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "DataPro" <datapro01@.yahoo.com> wrote in message
> >> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
> >> > Running SQL Server 2000 and new to the DBMS. Getting a consistent error
> >> > messahe on a test system
> >> >
> >> > 'Backup failed to complete the command EmptyTransactionLog1"
> >> >
> >> > I checked the documentation, and the OS logs but no additional info.
> >> > Seems to be happening either at startup ot shortly thereafter.
> >> >
> >> > Any thoughts on this?
> >> >
> >> > As a newbie, one (of many) things I find a bit confusing in SQL Server
> >> > is that if someone clicked through the database...selected all
> >> > tasks...create backup etc...that that backup would not show up in a
> >> > database maintenance plan. If you go into that wizard anything already
> >> > set to run does not show.
> >> >
> >> > Is there any way to determine if somebody has any type of backup taking
> >> > place on a db other than log messages?
> >> >
> >> > Thanks in advance.
> >> >
> >> > Gerry
> >> >
> >|||DataPro wrote:
> No, there aren't.
> Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
> command?
>
Where are you seeing this error message? Can you post the ENTIRE error
message here?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I haven't seen or heard of such a command. I'm wondering if it is a
'comment' in a script, job, etc.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153478749.916109.113570@.i42g2000cwa.googlegroups.com...
> No, there aren't.
> Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
> command?
>
> Arnie Rowland wrote:
>> Check to see if there are Maintenance Plans configured. Backup could be
>> part
>> of a Maintenance Plan.
>> --
>> Arnie Rowland
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...
>> > Arnie:
>> >
>> > I don't see anything in either place. I assume that means that no
>> > backups are taking place.
>> >
>> > What I'm not clear on is if no backups of any type are taking
>> > place...why am I getting the 'backup failed to complete the comand'
>> > error message
>> >
>> > Thanks
>> > Gerry
>> >
>> >
>> > Arnie Rowland wrote:
>> >> In Enterprise Manager, Under Management, you will find both Backup
>> >> jobs
>> >> and
>> >> Maintenance plans.
>> >>
>> >> --
>> >> Arnie Rowland
>> >> Most good judgment comes from experience.
>> >> Most experience comes from bad judgment.
>> >> - Anonymous
>> >>
>> >>
>> >> "DataPro" <datapro01@.yahoo.com> wrote in message
>> >> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
>> >> > Running SQL Server 2000 and new to the DBMS. Getting a consistent
>> >> > error
>> >> > messahe on a test system
>> >> >
>> >> > 'Backup failed to complete the command EmptyTransactionLog1"
>> >> >
>> >> > I checked the documentation, and the OS logs but no additional info.
>> >> > Seems to be happening either at startup ot shortly thereafter.
>> >> >
>> >> > Any thoughts on this?
>> >> >
>> >> > As a newbie, one (of many) things I find a bit confusing in SQL
>> >> > Server
>> >> > is that if someone clicked through the database...selected all
>> >> > tasks...create backup etc...that that backup would not show up in a
>> >> > database maintenance plan. If you go into that wizard anything
>> >> > already
>> >> > set to run does not show.
>> >> >
>> >> > Is there any way to determine if somebody has any type of backup
>> >> > taking
>> >> > place on a db other than log messages?
>> >> >
>> >> > Thanks in advance.
>> >> >
>> >> > Gerry
>> >> >
>> >
>|||This is the message...now repeating every 30 minutes
BACKUP failed to complete the command EmptyTransactionLog;1
It seems to happen shortly after startup and follows this notation in
the log, before just repeating itself over and over
SQL global counter collection task is created.
I did a search on the web and found one reference to this message. But
no clue on what it means.
Thanks
Gerry|||Gerry,
What service pack do you have installed?
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153765668.194365.252410@.m79g2000cwm.googlegroups.com...
> This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
>|||This is a multi-part message in MIME format.
--020206070802010300020903
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
DataPro wrote:
> This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
>
Hi Gerry
It sounds like you have a job scheduled to run every 30 minutes that
does something to a logfile. It could the that it tries to backup a
logfile for a database that has been set to SIMPLE recovery.
--
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--020206070802010300020903
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
DataPro wrote:
<blockquote
cite="mid1153765668.194365.252410@.m79g2000cwm.googlegroups.com"
type="cite">
<pre wrap="">This is the message...now repeating every 30 minutes
BACKUP failed to complete the command EmptyTransactionLog;1
It seems to happen shortly after startup and follows this notation in
the log, before just repeating itself over and over
SQL global counter collection task is created.
I did a search on the web and found one reference to this message. But
no clue on what it means.
Thanks
Gerry
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi Gerry<br>
<br>
It sounds like you have a job scheduled to run every 30 minutes that
does something to a logfile. It could the that it tries to backup a
logfile for a database that has been set to SIMPLE recovery.<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--020206070802010300020903--|||Arnie:
Service pack 4
Gerry
Arnie Rowland wrote:
> Gerry,
> What service pack do you have installed?
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153765668.194365.252410@.m79g2000cwm.googlegroups.com...
> > This is the message...now repeating every 30 minutes
> >
> > BACKUP failed to complete the command EmptyTransactionLog;1
> >
> > It seems to happen shortly after startup and follows this notation in
> > the log, before just repeating itself over and over
> >
> > SQL global counter collection task is created.
> >
> > I did a search on the web and found one reference to this message. But
> > no clue on what it means.
> >
> > Thanks
> > Gerry
> >|||Steen:
There is no reference in the message to which db might be involved.
However I do see that both databases they are using are set to Simple
mode.
When I look under SQL Agent/Jobs I only see job scheduled to run
every end of month.
Any thoughts ?
Thanks
Gerry
Steen Persson (DK) wrote:
> DataPro wrote:
> > This is the message...now repeating every 30 minutes
> >
> > BACKUP failed to complete the command EmptyTransactionLog;1
> >
> > It seems to happen shortly after startup and follows this notation in
> > the log, before just repeating itself over and over
> >
> > SQL global counter collection task is created.
> >
> > I did a search on the web and found one reference to this message. But
> > no clue on what it means.
> >
> > Thanks
> > Gerry
> >
> >
> Hi Gerry
> It sounds like you have a job scheduled to run every 30 minutes that
> does something to a logfile. It could the that it tries to backup a
> logfile for a database that has been set to SIMPLE recovery.
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
>
> --020206070802010300020903
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 1139
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> DataPro wrote:
> <blockquote
> cite=3D"mid1153765668.194365.252410@.m79g2000cwm.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
> </pre>
> </blockquote>
> <font size=3D"-1"><font face=3D"Arial">Hi Gerry<br>
> <br>
> It sounds like you have a job scheduled to run every 30 minutes that
> does something to a logfile. It could the that it tries to backup a
> logfile for a database that has been set to SIMPLE recovery.<br>
> <br>
> -- <br>
> Regards<br>
> Steen Schlüter Persson<br>
> Databaseadministrator / Systemadministrator<br>
> <br>
> </font></font>
> </body>
> </html>
> > --020206070802010300020903--

Question on Error message

Running SQL Server 2000 and new to the DBMS. Getting a consistent error
messahe on a test system
'Backup failed to complete the command EmptyTransactionLog1"
I checked the documentation, and the OS logs but no additional info.
Seems to be happening either at startup ot shortly thereafter.
Any thoughts on this?
As a newbie, one (of many) things I find a bit confusing in SQL Server
is that if someone clicked through the database...selected all
tasks...create backup etc...that that backup would not show up in a
database maintenance plan. If you go into that wizard anything already
set to run does not show.
Is there any way to determine if somebody has any type of backup taking
place on a db other than log messages?
Thanks in advance.
GerryIn Enterprise Manager, Under Management, you will find both Backup jobs and
Maintenance plans.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
> Running SQL Server 2000 and new to the DBMS. Getting a consistent error
> messahe on a test system
> 'Backup failed to complete the command EmptyTransactionLog1"
> I checked the documentation, and the OS logs but no additional info.
> Seems to be happening either at startup ot shortly thereafter.
> Any thoughts on this?
> As a newbie, one (of many) things I find a bit confusing in SQL Server
> is that if someone clicked through the database...selected all
> tasks...create backup etc...that that backup would not show up in a
> database maintenance plan. If you go into that wizard anything already
> set to run does not show.
> Is there any way to determine if somebody has any type of backup taking
> place on a db other than log messages?
> Thanks in advance.
> Gerry
>|||Arnie:
I don't see anything in either place. I assume that means that no
backups are taking place.
What I'm not clear on is if no backups of any type are taking
place...why am I getting the 'backup failed to complete the comand'
error message
Thanks
Gerry
Arnie Rowland wrote:[vbcol=seagreen]
> In Enterprise Manager, Under Management, you will find both Backup jobs an
d
> Maintenance plans.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...|||Check to see if there are Maintenance Plans configured. Backup could be part
of a Maintenance Plan.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...
> Arnie:
> I don't see anything in either place. I assume that means that no
> backups are taking place.
> What I'm not clear on is if no backups of any type are taking
> place...why am I getting the 'backup failed to complete the comand'
> error message
> Thanks
> Gerry
>
> Arnie Rowland wrote:
>|||No, there aren't.
Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
command?
Arnie Rowland wrote:[vbcol=seagreen]
> Check to see if there are Maintenance Plans configured. Backup could be pa
rt
> of a Maintenance Plan.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...|||DataPro wrote:
> No, there aren't.
> Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
> command?
>
Where are you seeing this error message? Can you post the ENTIRE error
message here?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I haven't seen or heard of such a command. I'm wondering if it is a
'comment' in a script, job, etc.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153478749.916109.113570@.i42g2000cwa.googlegroups.com...
> No, there aren't.
> Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
> command?
>
> Arnie Rowland wrote:
>|||This is the message...now repeating every 30 minutes
BACKUP failed to complete the command EmptyTransactionLog;1
It seems to happen shortly after startup and follows this notation in
the log, before just repeating itself over and over
SQL global counter collection task is created.
I did a search on the web and found one reference to this message. But
no clue on what it means.
Thanks
Gerry|||Gerry,
What service pack do you have installed?
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153765668.194365.252410@.m79g2000cwm.googlegroups.com...
> This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
>|||DataPro wrote:
> This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
>
Hi Gerry
It sounds like you have a job scheduled to run every 30 minutes that
does something to a logfile. It could the that it tries to backup a
logfile for a database that has been set to SIMPLE recovery.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Question on attribute keys of measure group

Hi, dear friends,

I encountered a problem processing my cube with the error message telling: processing measure_group_name (which only has a count as its only measure)failed as attribute keys can not be found? Why is that? I have no idea why is that? Does it mean I have to include all the keys of the fact table where the measure group from in the measure groups as measures? Otherwise the system failed to identify the distinct count of the system? Or whatever measures the measure group contains, it alwys have to include all the keys including the dimension keys of dimensions which the measure group is related to?

Thanks in advance for your help and advices and I am always looking forward to hearing from you shortly from you.

With best regards,

Yours sincerely,

This message means that a column used to join the measure group to a dimension contains a value not found in the dimension. This is usually caused by a referential integrity error - the FK does not existing in the PK of the lookup table. However, it can also be caused by the presence of a null in the FK as by default this null will be converted to zero or blank which probably does not exist as a key in the dimension. You can handle the situation of nulls in FK columns within the fact table by setting the NullProcessing option in the advanced tab of the measure group relationship dialog from the Dimension Usage tab of the cube editor.|||

Hi, Matt,

Thanks a lot. Yes, the problem is the matching of the PK and the FK between the tables.

However, the data source view logical key and the relationships between tables are really annoying then, as it is not able to check the integrity of the tables?

With best regards,

Yours sincerely,

|||

So it sounds like you would like to have an option on the Data Source View to validate logical keys and relationships. I'd recommend you file a feature request on http://connect.microsoft.com/SQLServer/Feedback. Such feature requests will be automatically added to our issue tracking system for consideration in future releases and those that originate from a customer such as yourself get extra consideration since we know it's a real world scenario. If you can describe the scenario it will also help as it will allow us to better identify which requests will help the most people and how to implement them so that they actually provide the help intended.

Thanks, Matt

|||

Hi, Matt,

Thanks a lot and this sounds really a good idea of it.

With best regards,

Yours sincerely,