abnormal message when performing restore on 2005

Hi!
I have performed a full, differential and translog backup of my database.
Now when I try to restore the full backup it I get this message
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "userdb" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I then used this command
restore database userdb from disk='X:\backups\userfulldev.bak' with replace
and it restored the information correctly...
But im a bit curious exactly what I did wrong. Usually I never have to use
the "with replace" option when I restore my databases...
your thoughts?
/henrikThat is a new "error" message designed to keep you from erasing potentially
useful data. It applies when the following conditions are al true
You are restoring over an existing database
The database is in full recovery mode
There are active log segments (segments that have not been backed up).
The assumption is that the database contains useful transactions that must
be preserved.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Henrik Nordgren" <HenrikNordgren@.discussions.microsoft.com> wrote in
message news:84A018FB-0A32-4991-BC94-BD875EAB553B@.microsoft.com...
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
> not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with
> replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
>|||On Feb 14, 1:47 pm, Henrik Nordgren
<HenrikNordg...@.discussions.microsoft.com> wrote:
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
This is telling you that something modified your database after your
last log backup was done. There are "new" transactions in the log
that you will lose if you proceed with the restore...

abnormal message when performing restore on 2005

Hi!
I have performed a full, differential and translog backup of my database.
Now when I try to restore the full backup it I get this message
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "userdb" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I then used this command
restore database userdb from disk='X:\backups\userfulldev.bak' with replace
and it restored the information correctly...
But im a bit curious exactly what I did wrong. Usually I never have to use
the "with replace" option when I restore my databases...
your thoughts?
/henrik
That is a new "error" message designed to keep you from erasing potentially
useful data. It applies when the following conditions are al true
You are restoring over an existing database
The database is in full recovery mode
There are active log segments (segments that have not been backed up).
The assumption is that the database contains useful transactions that must
be preserved.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Henrik Nordgren" <HenrikNordgren@.discussions.microsoft.com> wrote in
message news:84A018FB-0A32-4991-BC94-BD875EAB553B@.microsoft.com...
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
> not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with
> replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
>
|||On Feb 14, 1:47 pm, Henrik Nordgren
<HenrikNordg...@.discussions.microsoft.com> wrote:
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
This is telling you that something modified your database after your
last log backup was done. There are "new" transactions in the log
that you will lose if you proceed with the restore...
sql

abnormal message when performing restore on 2005

Hi!
I have performed a full, differential and translog backup of my database.
Now when I try to restore the full backup it I get this message
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "userdb" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I then used this command
restore database userdb from disk='X:\backups\userfulldev.bak' with replace
and it restored the information correctly...
But im a bit curious exactly what I did wrong. Usually I never have to use
the "with replace" option when I restore my databases...
your thoughts?
/henrikThat is a new "error" message designed to keep you from erasing potentially
useful data. It applies when the following conditions are al true
You are restoring over an existing database
The database is in full recovery mode
There are active log segments (segments that have not been backed up).
The assumption is that the database contains useful transactions that must
be preserved.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Henrik Nordgren" <HenrikNordgren@.discussions.microsoft.com> wrote in
message news:84A018FB-0A32-4991-BC94-BD875EAB553B@.microsoft.com...
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
> not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with
> replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
>|||On Feb 14, 1:47 pm, Henrik Nordgren
<HenrikNordg...@.discussions.microsoft.com> wrote:
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do no
t
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with replac
e
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
This is telling you that something modified your database after your
last log backup was done. There are "new" transactions in the log
that you will lose if you proceed with the restore...

A truncation occurred during evaluation of the expression

First of all, I get the following error message for one of my packages which uses user variables:

SSIS package "UsageAnalysis.dtsx" starting.
Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.
Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.
Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.
Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.
Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "UsageAnalysis.dtsx" finished: Failure.

Now my package has the following variables:

GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX

MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above

GetAnalysisData: scope package level, type string, Evaluate as Expression

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @.[User::MaxUsageID]

The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated.

Thanks,

Asaf

Are you sure MAX(UsageID) is 1,000,000 and not 100,000,000?

Try making your cast a longer length in the GetAnalysisData variable. Perhaps: (DT_STR,20,1252)@.[User::MaxUsageID]

|||

Hi Phil,

Thanks for your reply but I am afraid I already have tried the solution you suggested. Max(UsageID) is calculated against sql table and then stored to MaxUsageID user variable.

select max(usageID) from dbo.XXX

--1027005

So you could see value by no means is tens of millions as you anticipated. I also have tried changing DT_STR string length parameter to larger values as proposed

SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 20, 1252) @.[User::MaxUsageID]

Even when I have no casting involved such as

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

I still get the same truncation error message. I would appreciate if you could suggest something else which obviously I am missing here.

Thanks,

Asaf

|||

Phil,

I have worked it out.

It looks like one of the SSIS bugs. Even though I updated the expression by changing (DT_STR, 6, 1252) to (DT_STR, 12, 1252) so that I could avoid any obvious truncation issues for values above 1 million and after saving the package I rebuilt it but project would fail whenever I executed it.

When I opened the project again, I ended up having the old value for (DT_STR, 6, 1252) in xml code view. Somehow SSIS wouldn't save the updated expression. Hence I kept getting that error message even though I had reasons to believe I had done everything right.

To resolve that, I just decided to define a new variable with required DT_STR string length and my package works without any complain.

Thanks,

Asaf

|||

Sounds like you may have been trying to update the "Value" parameter, not the "Expression" parameter. In that case, it would revert back to whatever you had in the expression.

With that being said, if it is a bug and you can reproduce it in the future, please submit it at http://connect.microsoft.com/sqlserver/feedback and report back here with the link to your submission. But please only submit it if you can reproduce it.

Thanks,

Phil

sql

A truncation occurred during evaluation of the expression

First of all, I get the following error message for one of my packages which uses user variables:

SSIS package "UsageAnalysis.dtsx" starting.
Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.
Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.
Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.
Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.
Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "UsageAnalysis.dtsx" finished: Failure.

Now my package has the following variables:

GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX

MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above

GetAnalysisData: scope package level, type string, Evaluate as Expression

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @.[User::MaxUsageID]

The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated.

Thanks,

Asaf

Are you sure MAX(UsageID) is 1,000,000 and not 100,000,000?

Try making your cast a longer length in the GetAnalysisData variable. Perhaps: (DT_STR,20,1252)@.[User::MaxUsageID]

|||

Hi Phil,

Thanks for your reply but I am afraid I already have tried the solution you suggested. Max(UsageID) is calculated against sql table and then stored to MaxUsageID user variable.

select max(usageID) from dbo.XXX

--1027005

So you could see value by no means is tens of millions as you anticipated. I also have tried changing DT_STR string length parameter to larger values as proposed

SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 20, 1252) @.[User::MaxUsageID]

Even when I have no casting involved such as

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

I still get the same truncation error message. I would appreciate if you could suggest something else which obviously I am missing here.

Thanks,

Asaf

|||

Phil,

I have worked it out.

It looks like one of the SSIS bugs. Even though I updated the expression by changing (DT_STR, 6, 1252) to (DT_STR, 12, 1252) so that I could avoid any obvious truncation issues for values above 1 million and after saving the package I rebuilt it but project would fail whenever I executed it.

When I opened the project again, I ended up having the old value for (DT_STR, 6, 1252) in xml code view. Somehow SSIS wouldn't save the updated expression. Hence I kept getting that error message even though I had reasons to believe I had done everything right.

To resolve that, I just decided to define a new variable with required DT_STR string length and my package works without any complain.

Thanks,

Asaf

|||

Sounds like you may have been trying to update the "Value" parameter, not the "Expression" parameter. In that case, it would revert back to whatever you had in the expression.

With that being said, if it is a bug and you can reproduce it in the future, please submit it at http://connect.microsoft.com/sqlserver/feedback and report back here with the link to your submission. But please only submit it if you can reproduce it.

Thanks,

Phil

A strong SA password is required for security reasons?

When I try to install SQL Server I get the follwing message when I click on the installation file :

A strong SA password is required for securityreasons.

Please use SAPWDswitch to supply the same. Refer to readme

for more details.Setup will now exit.

I've checked the readme but can't really find any info on how to fix this.
I don't know what password this SA password is nor how to change it.
Can anyone help me out?

It sounds like you are trying to set up MSDE. Instead of runningSETUP.EXE, you need to run SETUP.EXE with a SAPWD switch, somethinglike this:

setup sapwd="astrongpassword" securitymode=sql

|||Thanks for the help but there are just a few things I want to know about what you said.
1.How do I run this switch. By going to Start->Run and then typing in the switch?
2. Where you said sapwd="astrongpassword". Can this be any password or is this some existing password?

|||

What steps are you taking to do the installation?

Do you have the readme file that came with the MSDE installation files?

It should really explain how to do all of this. You are likelyrunning a .bat file in which one of the lines being executed issetup.exe. This is the line to which the switch should beadded. Something like this:

setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL

According toSecurity Rulesa strong password is:

at least seven characters long|||

Hi,

Looks like you got some good advice already. I just wanted to add a caution that you'll likely need to consider soon enough. While having a strong (harder to crack) PW is important, youdon't everwant to use the sa (System Administrator) account when creating your ASP.NET applications. There are other "users" that you'll be able to have access your DBs. ASP.NET has a worker process that acts as a user on behalf of ASP.NET applications. It's called ASPNET (though you'll need to use NETWORK SERVICE if you're using IIS 6). In any case, you can look up those terms when the time comes.

Hope it helps. BRN..

|||

Hi,

I changed the sa PWD on my system just a while ago. I noticed that it would not accept spaces in the PWD. I generally like to use pass phrases now, as they're easier for me to remember. Not having spaces makes it awkward. Wonder why windows and SQL Server differ on that? Do you happen to know why? BRN..

|||Thank you very much for the help as I got the problem sorted out as you suggested.

A severe error occurred on the current command.

I am having problems with an error that I keep receiving. The error message states "A severe error occurred on the current command. The results, if any, should be discarded."

This error is popping up when I try to call a specific stored procedure in SQL2005. The error is not specific to ASP.NET code because it also occurs if I try to run the procedure in SQL Server Management Studio. The procedure is exactly the same that is running on an SQL2000 server on a live site, so it apparently is specific to SQL2005.

I have not worked much with SQL2005 or SQL cursors in the past and this code was not written by me so I am not sure where to start making changes, if any are needed.

The procedure is as follows:

ALTER PROCEDURE [dbo].[spGetAdmins]
@.userid int,
@.forDate datetime
AS

DECLARE @.lngTabCount INTEGER,
@.lngLoopCount INTEGER,
@.type varchar(15),
@.id varchar(20),
@.statement varchar(1000),
@.useridLocal int,
@.forDateLocal datetime

SET @.useridLocal = @.userid
SET @.forDateLocal = @.forDate

DECLARE @.AdminInfo TABLE (AdminID varchar(20), AdminName varchar(50))

/* check parameters */
IF (@.useridLocal <= 0)
BEGIN
RAISERROR( 'Invalid UserID (%d)', 16, 1, @.useridLocal )
END
IF (@.forDateLocal IS NULL)
BEGIN
SELECT @.forDateLocal = GETDATE()
END

DECLARE profInfo CURSOR
LOCAL FORWARD_ONLY
FOR
SELECT ProfileType, ProfileValue
FROM tblCSUserProfile
WHERE UserID = @.useridLocal and
(StartDate is not null or StartDate <= @.forDateLocal ) and
(EndDate is null or EndDate > @.forDateLocal )
ORDER BY ProfileType

OPEN profInfo
FETCH profInfo
INTO @.type, @.id

WHILE @.@.Fetch_Status = 0
BEGIN
IF @.id = '*'
BEGIN
INSERT INTO @.AdminInfo
VALUES ('*', '* Everything')
END
ELSE
BEGIN
INSERT INTO @.AdminInfo
SELECT adm.[ADM_CODE], adm.[ADMIN_NAM]
FROM OPENROWSET('SQLOLEDB', 'server'; 'database'; 'password', [table]) AS adm
WHERE adm.[ADM_CODE] = @.id
END

FETCH profInfo INTO @.type, @.id

END

CLOSE profInfo

DEALLOCATE profInfo

SELECT DISTINCT(UPPER(AdminID)) AS AdminCode
FROM @.AdminInfo
GROUP BY AdminID
ORDER BY AdminCode

(1) I would change the line

FETCH profInfo
INTO @.type, @.id

to

FETCH NEXT FROM profInfo INTO @.type, @.id

and

FETCH profInfo INTO @.type, @.id

to

FETCH NEXT FROM profInfo INTO @.type, @.id

I would also recommend using a table variable instead of cursors.

(2) Check out the syntax for OPENROWSET in BOL.

Try using the SQLNCLI (SQL Native Client) instead of SQLOLEDB and see if it makes a difference.

A request to establish a connection with transaction manager denie

A user has reported the message "A request to establish a connection with the
transaction manager was denied". The code is at this point attempting to
START a transaction. So far I have been unable to duplicate this, though the
user has been able to get through this code in the past. Of course, no known
changes.
What causes this particular message?
--
Brad AshforthHi Brad
This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
you already have the maximum number of connections.
John
"Brad Ashforth" wrote:
> A user has reported the message "A request to establish a connection with the
> transaction manager was denied". The code is at this point attempting to
> START a transaction. So far I have been unable to duplicate this, though the
> user has been able to get through this code in the past. Of course, no known
> changes.
> What causes this particular message?
> --
> Brad Ashforth|||Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
level? Our application is designed such that we do not make use of nested
transactions. We have a "transaction" routine that is called with either
"Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
(as it was in this case), we first execute a commit (in case a transaction is
pending that was not committed or rolled back) and then do the BEGIN. Of
course, if when we issue the commit if there is no pending transaction, we
get an error ... but that is trapped for and ignored if we are trying to
begin a new transaction.
So, that said ... there should only be one transaction active for the
application at any given time and if by chance we try to start another one
the code automatically commits the first one before starting the 2nd one.
Is there a way to query how many connections there are?
--
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> you already have the maximum number of connections.
> John
> "Brad Ashforth" wrote:
> > A user has reported the message "A request to establish a connection with the
> > transaction manager was denied". The code is at this point attempting to
> > START a transaction. So far I have been unable to duplicate this, though the
> > user has been able to get through this code in the past. Of course, no known
> > changes.
> >
> > What causes this particular message?
> > --
> > Brad Ashforth|||Hi Brad,
Thank you for your posting!
I would like to know some detailed information:
1. Does your customer use Distribute Transaction? If so, does the DTC
services start?
2. Are there any error log in event log? If so, please post it here.
3. What's the SQL Server Version and the Server OS version?
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei ... thank you.
1). No, the customer does not use Distributed Transactions (at least not to
their knowledge)
2) No errors in any of the event logs on the SQL Server box
3) From SQL Query Analyzer @.@.Version returns 8.00.760, from a cmd shell VER
returns 5.2.3790 (Windows Server 2003 Enterprise Edition)
Please note that this error occurred in an acceptance testing phase, not in
production.
--
Brad Ashforth
"Wei Lu [MSFT]" wrote:
> Hi Brad,
> Thank you for your posting!
> I would like to know some detailed information:
> 1. Does your customer use Distribute Transaction? If so, does the DTC
> services start?
> 2. Are there any error log in event log? If so, please post it here.
> 3. What's the SQL Server Version and the Server OS version?
> Please let me know the result and so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Brad
Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
Using a commit that when a transaction has not begun will cause an error:
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Which you can easily avoid.
I don't think this is the cause of the issue! Do you reference any other
databases either in your stored procedures or triggers? Are you using
multipart naming conventions? Are you enquoting your names using square
braces []?
John
"Brad Ashforth" wrote:
> Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> level? Our application is designed such that we do not make use of nested
> transactions. We have a "transaction" routine that is called with either
> "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> (as it was in this case), we first execute a commit (in case a transaction is
> pending that was not committed or rolled back) and then do the BEGIN. Of
> course, if when we issue the commit if there is no pending transaction, we
> get an error ... but that is trapped for and ignored if we are trying to
> begin a new transaction.
> So, that said ... there should only be one transaction active for the
> application at any given time and if by chance we try to start another one
> the code automatically commits the first one before starting the 2nd one.
> Is there a way to query how many connections there are?
> --
> Brad Ashforth
>
> "John Bell" wrote:
> > Hi Brad
> >
> > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > you already have the maximum number of connections.
> >
> > John
> >
> > "Brad Ashforth" wrote:
> >
> > > A user has reported the message "A request to establish a connection with the
> > > transaction manager was denied". The code is at this point attempting to
> > > START a transaction. So far I have been unable to duplicate this, though the
> > > user has been able to get through this code in the past. Of course, no known
> > > changes.
> > >
> > > What causes this particular message?
> > > --
> > > Brad Ashforth|||Hi John. Thank you.
We had not known about @.@.TRANCOUNT and agree that it makes more sense to
check this instead of trapping for a known error that is likely to occur (on
the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
agree that this is not the likely cause of the error I originally posted a
question about.
We do not reference other databases, and in most (99%?) cases do not use
fully qualified table names. This is because most tables are in the DBO
schema and do not get to the user's own schema ... each user temp table (for
reports, etc) are in their own schema. In most cases the object names follow
standard conventions and should not need braces. This is also not an error
that is easily repeatable. It has been seen before but have had trouble
duplicating it.
Thanks,
Brad
--
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> Using a commit that when a transaction has not begun will cause an error:
> Server: Msg 3902, Level 16, State 1, Line 1
> The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> Which you can easily avoid.
> I don't think this is the cause of the issue! Do you reference any other
> databases either in your stored procedures or triggers? Are you using
> multipart naming conventions? Are you enquoting your names using square
> braces []?
>
> John
> "Brad Ashforth" wrote:
> > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > level? Our application is designed such that we do not make use of nested
> > transactions. We have a "transaction" routine that is called with either
> > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > (as it was in this case), we first execute a commit (in case a transaction is
> > pending that was not committed or rolled back) and then do the BEGIN. Of
> > course, if when we issue the commit if there is no pending transaction, we
> > get an error ... but that is trapped for and ignored if we are trying to
> > begin a new transaction.
> >
> > So, that said ... there should only be one transaction active for the
> > application at any given time and if by chance we try to start another one
> > the code automatically commits the first one before starting the 2nd one.
> >
> > Is there a way to query how many connections there are?
> > --
> > Brad Ashforth
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Brad
> > >
> > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > you already have the maximum number of connections.
> > >
> > > John
> > >
> > > "Brad Ashforth" wrote:
> > >
> > > > A user has reported the message "A request to establish a connection with the
> > > > transaction manager was denied". The code is at this point attempting to
> > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > user has been able to get through this code in the past. Of course, no known
> > > > changes.
> > > >
> > > > What causes this particular message?
> > > > --
> > > > Brad Ashforth|||Hi Brad
Prefixing with the schema name should improve performance and caching.
If this problem is periodic you are not going to know for certain that you
have cured it!! How often does it occur? Does it mainly occur after a re-boot
or a high load levels? I know you have checked the Event logs what about the
SQL Server log?
If MSDTC is currently running try switching it off, as this should not
effect your application if it is not being used!!
John
"Brad Ashforth" wrote:
> Hi John. Thank you.
> We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> check this instead of trapping for a known error that is likely to occur (on
> the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> agree that this is not the likely cause of the error I originally posted a
> question about.
> We do not reference other databases, and in most (99%?) cases do not use
> fully qualified table names. This is because most tables are in the DBO
> schema and do not get to the user's own schema ... each user temp table (for
> reports, etc) are in their own schema. In most cases the object names follow
> standard conventions and should not need braces. This is also not an error
> that is easily repeatable. It has been seen before but have had trouble
> duplicating it.
> Thanks,
> Brad
> --
> Brad Ashforth
>
> "John Bell" wrote:
> > Hi Brad
> >
> > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > Using a commit that when a transaction has not begun will cause an error:
> >
> > Server: Msg 3902, Level 16, State 1, Line 1
> > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> >
> > Which you can easily avoid.
> >
> > I don't think this is the cause of the issue! Do you reference any other
> > databases either in your stored procedures or triggers? Are you using
> > multipart naming conventions? Are you enquoting your names using square
> > braces []?
> >
> >
> > John
> >
> > "Brad Ashforth" wrote:
> >
> > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > level? Our application is designed such that we do not make use of nested
> > > transactions. We have a "transaction" routine that is called with either
> > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > (as it was in this case), we first execute a commit (in case a transaction is
> > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > course, if when we issue the commit if there is no pending transaction, we
> > > get an error ... but that is trapped for and ignored if we are trying to
> > > begin a new transaction.
> > >
> > > So, that said ... there should only be one transaction active for the
> > > application at any given time and if by chance we try to start another one
> > > the code automatically commits the first one before starting the 2nd one.
> > >
> > > Is there a way to query how many connections there are?
> > > --
> > > Brad Ashforth
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Brad
> > > >
> > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > you already have the maximum number of connections.
> > > >
> > > > John
> > > >
> > > > "Brad Ashforth" wrote:
> > > >
> > > > > A user has reported the message "A request to establish a connection with the
> > > > > transaction manager was denied". The code is at this point attempting to
> > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > user has been able to get through this code in the past. Of course, no known
> > > > > changes.
> > > > >
> > > > > What causes this particular message?
> > > > > --
> > > > > Brad Ashforth|||Hi John .. Thank you.
As the application is used in multiple environments, we have no control or
knowledge of the deployment and don't code the fully qualified object name,
currently it would take a LOT of rework to do this. Is the performance
improvement enough to make it worthwhile?
Yes, periodic failures are a pain to diagnose. The problem occurs RARELY (I
think it has only happened once or twice in a deployment. Not all deployments
have seen the problem and the total number of occurrences is certainly less
than 6 or 7. Rebooting does not seem to be the issue, nor does load level (no
reboots either before or after the error). I currently do not have access to
the SQL Server log. Will try to obtain it.
MSDTC is not used by our application, but if a deployment decides to house
multiple databases on the SQLServer, wouldn't it be better to leave it on?
--
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> Prefixing with the schema name should improve performance and caching.
> If this problem is periodic you are not going to know for certain that you
> have cured it!! How often does it occur? Does it mainly occur after a re-boot
> or a high load levels? I know you have checked the Event logs what about the
> SQL Server log?
> If MSDTC is currently running try switching it off, as this should not
> effect your application if it is not being used!!
> John
>
>
> "Brad Ashforth" wrote:
> > Hi John. Thank you.
> >
> > We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> > check this instead of trapping for a known error that is likely to occur (on
> > the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> > agree that this is not the likely cause of the error I originally posted a
> > question about.
> >
> > We do not reference other databases, and in most (99%?) cases do not use
> > fully qualified table names. This is because most tables are in the DBO
> > schema and do not get to the user's own schema ... each user temp table (for
> > reports, etc) are in their own schema. In most cases the object names follow
> > standard conventions and should not need braces. This is also not an error
> > that is easily repeatable. It has been seen before but have had trouble
> > duplicating it.
> >
> > Thanks,
> > Brad
> > --
> > Brad Ashforth
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Brad
> > >
> > > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > > Using a commit that when a transaction has not begun will cause an error:
> > >
> > > Server: Msg 3902, Level 16, State 1, Line 1
> > > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> > >
> > > Which you can easily avoid.
> > >
> > > I don't think this is the cause of the issue! Do you reference any other
> > > databases either in your stored procedures or triggers? Are you using
> > > multipart naming conventions? Are you enquoting your names using square
> > > braces []?
> > >
> > >
> > > John
> > >
> > > "Brad Ashforth" wrote:
> > >
> > > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > > level? Our application is designed such that we do not make use of nested
> > > > transactions. We have a "transaction" routine that is called with either
> > > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > > (as it was in this case), we first execute a commit (in case a transaction is
> > > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > > course, if when we issue the commit if there is no pending transaction, we
> > > > get an error ... but that is trapped for and ignored if we are trying to
> > > > begin a new transaction.
> > > >
> > > > So, that said ... there should only be one transaction active for the
> > > > application at any given time and if by chance we try to start another one
> > > > the code automatically commits the first one before starting the 2nd one.
> > > >
> > > > Is there a way to query how many connections there are?
> > > > --
> > > > Brad Ashforth
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Brad
> > > > >
> > > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > > you already have the maximum number of connections.
> > > > >
> > > > > John
> > > > >
> > > > > "Brad Ashforth" wrote:
> > > > >
> > > > > > A user has reported the message "A request to establish a connection with the
> > > > > > transaction manager was denied". The code is at this point attempting to
> > > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > > user has been able to get through this code in the past. Of course, no known
> > > > > > changes.
> > > > > >
> > > > > > What causes this particular message?
> > > > > > --
> > > > > > Brad Ashforth|||Hi Brad
Using the schema name should not effect the deployments as this should be
constant, two part naming will help to varying degrees, depending on what
specification the server has and the load it takes. In general it is good
practice to do this and is usually easier to implement though of coding
standards and QA than retrospectively.
If you share the server with other applications you would need to make sure
that they don't use MSDTC, but any service on the server that is not being
used should be stopped as they will take up resources. If you stop this
service then it may make it easier to highlight something wrong with your own
application.
John
"Brad Ashforth" wrote:
> Hi John .. Thank you.
> As the application is used in multiple environments, we have no control or
> knowledge of the deployment and don't code the fully qualified object name,
> currently it would take a LOT of rework to do this. Is the performance
> improvement enough to make it worthwhile?
> Yes, periodic failures are a pain to diagnose. The problem occurs RARELY (I
> think it has only happened once or twice in a deployment. Not all deployments
> have seen the problem and the total number of occurrences is certainly less
> than 6 or 7. Rebooting does not seem to be the issue, nor does load level (no
> reboots either before or after the error). I currently do not have access to
> the SQL Server log. Will try to obtain it.
> MSDTC is not used by our application, but if a deployment decides to house
> multiple databases on the SQLServer, wouldn't it be better to leave it on?
> --
> Brad Ashforth
>
> "John Bell" wrote:
> > Hi Brad
> >
> > Prefixing with the schema name should improve performance and caching.
> >
> > If this problem is periodic you are not going to know for certain that you
> > have cured it!! How often does it occur? Does it mainly occur after a re-boot
> > or a high load levels? I know you have checked the Event logs what about the
> > SQL Server log?
> >
> > If MSDTC is currently running try switching it off, as this should not
> > effect your application if it is not being used!!
> >
> > John
> >
> >
> >
> >
> >
> > "Brad Ashforth" wrote:
> >
> > > Hi John. Thank you.
> > >
> > > We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> > > check this instead of trapping for a known error that is likely to occur (on
> > > the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> > > agree that this is not the likely cause of the error I originally posted a
> > > question about.
> > >
> > > We do not reference other databases, and in most (99%?) cases do not use
> > > fully qualified table names. This is because most tables are in the DBO
> > > schema and do not get to the user's own schema ... each user temp table (for
> > > reports, etc) are in their own schema. In most cases the object names follow
> > > standard conventions and should not need braces. This is also not an error
> > > that is easily repeatable. It has been seen before but have had trouble
> > > duplicating it.
> > >
> > > Thanks,
> > > Brad
> > > --
> > > Brad Ashforth
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Brad
> > > >
> > > > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > > > Using a commit that when a transaction has not begun will cause an error:
> > > >
> > > > Server: Msg 3902, Level 16, State 1, Line 1
> > > > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> > > >
> > > > Which you can easily avoid.
> > > >
> > > > I don't think this is the cause of the issue! Do you reference any other
> > > > databases either in your stored procedures or triggers? Are you using
> > > > multipart naming conventions? Are you enquoting your names using square
> > > > braces []?
> > > >
> > > >
> > > > John
> > > >
> > > > "Brad Ashforth" wrote:
> > > >
> > > > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > > > level? Our application is designed such that we do not make use of nested
> > > > > transactions. We have a "transaction" routine that is called with either
> > > > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > > > (as it was in this case), we first execute a commit (in case a transaction is
> > > > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > > > course, if when we issue the commit if there is no pending transaction, we
> > > > > get an error ... but that is trapped for and ignored if we are trying to
> > > > > begin a new transaction.
> > > > >
> > > > > So, that said ... there should only be one transaction active for the
> > > > > application at any given time and if by chance we try to start another one
> > > > > the code automatically commits the first one before starting the 2nd one.
> > > > >
> > > > > Is there a way to query how many connections there are?
> > > > > --
> > > > > Brad Ashforth
> > > > >
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Brad
> > > > > >
> > > > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > > > you already have the maximum number of connections.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Brad Ashforth" wrote:
> > > > > >
> > > > > > > A user has reported the message "A request to establish a connection with the
> > > > > > > transaction manager was denied". The code is at this point attempting to
> > > > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > > > user has been able to get through this code in the past. Of course, no known
> > > > > > > changes.
> > > > > > >
> > > > > > > What causes this particular message?
> > > > > > > --
> > > > > > > Brad Ashforth|||Hi John, Thank you ... I found that the test server does NOT host other
databases that require MSDTC but the server DID have the service as auto
start. I've set it to manual and stopped the service. We'll keep checking to
see if it happens again.
Thank you,
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> Using the schema name should not effect the deployments as this should be
> constant, two part naming will help to varying degrees, depending on what
> specification the server has and the load it takes. In general it is good
> practice to do this and is usually easier to implement though of coding
> standards and QA than retrospectively.
> If you share the server with other applications you would need to make sure
> that they don't use MSDTC, but any service on the server that is not being
> used should be stopped as they will take up resources. If you stop this
> service then it may make it easier to highlight something wrong with your own
> application.
> John
>
> "Brad Ashforth" wrote:
> > Hi John .. Thank you.
> >
> > As the application is used in multiple environments, we have no control or
> > knowledge of the deployment and don't code the fully qualified object name,
> > currently it would take a LOT of rework to do this. Is the performance
> > improvement enough to make it worthwhile?
> >
> > Yes, periodic failures are a pain to diagnose. The problem occurs RARELY (I
> > think it has only happened once or twice in a deployment. Not all deployments
> > have seen the problem and the total number of occurrences is certainly less
> > than 6 or 7. Rebooting does not seem to be the issue, nor does load level (no
> > reboots either before or after the error). I currently do not have access to
> > the SQL Server log. Will try to obtain it.
> >
> > MSDTC is not used by our application, but if a deployment decides to house
> > multiple databases on the SQLServer, wouldn't it be better to leave it on?
> >
> > --
> > Brad Ashforth
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Brad
> > >
> > > Prefixing with the schema name should improve performance and caching.
> > >
> > > If this problem is periodic you are not going to know for certain that you
> > > have cured it!! How often does it occur? Does it mainly occur after a re-boot
> > > or a high load levels? I know you have checked the Event logs what about the
> > > SQL Server log?
> > >
> > > If MSDTC is currently running try switching it off, as this should not
> > > effect your application if it is not being used!!
> > >
> > > John
> > >
> > >
> > >
> > >
> > >
> > > "Brad Ashforth" wrote:
> > >
> > > > Hi John. Thank you.
> > > >
> > > > We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> > > > check this instead of trapping for a known error that is likely to occur (on
> > > > the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> > > > agree that this is not the likely cause of the error I originally posted a
> > > > question about.
> > > >
> > > > We do not reference other databases, and in most (99%?) cases do not use
> > > > fully qualified table names. This is because most tables are in the DBO
> > > > schema and do not get to the user's own schema ... each user temp table (for
> > > > reports, etc) are in their own schema. In most cases the object names follow
> > > > standard conventions and should not need braces. This is also not an error
> > > > that is easily repeatable. It has been seen before but have had trouble
> > > > duplicating it.
> > > >
> > > > Thanks,
> > > > Brad
> > > > --
> > > > Brad Ashforth
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Brad
> > > > >
> > > > > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > > > > Using a commit that when a transaction has not begun will cause an error:
> > > > >
> > > > > Server: Msg 3902, Level 16, State 1, Line 1
> > > > > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> > > > >
> > > > > Which you can easily avoid.
> > > > >
> > > > > I don't think this is the cause of the issue! Do you reference any other
> > > > > databases either in your stored procedures or triggers? Are you using
> > > > > multipart naming conventions? Are you enquoting your names using square
> > > > > braces []?
> > > > >
> > > > >
> > > > > John
> > > > >
> > > > > "Brad Ashforth" wrote:
> > > > >
> > > > > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > > > > level? Our application is designed such that we do not make use of nested
> > > > > > transactions. We have a "transaction" routine that is called with either
> > > > > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > > > > (as it was in this case), we first execute a commit (in case a transaction is
> > > > > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > > > > course, if when we issue the commit if there is no pending transaction, we
> > > > > > get an error ... but that is trapped for and ignored if we are trying to
> > > > > > begin a new transaction.
> > > > > >
> > > > > > So, that said ... there should only be one transaction active for the
> > > > > > application at any given time and if by chance we try to start another one
> > > > > > the code automatically commits the first one before starting the 2nd one.
> > > > > >
> > > > > > Is there a way to query how many connections there are?
> > > > > > --
> > > > > > Brad Ashforth
> > > > > >
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Brad
> > > > > > >
> > > > > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > > > > you already have the maximum number of connections.
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "Brad Ashforth" wrote:
> > > > > > >
> > > > > > > > A user has reported the message "A request to establish a connection with the
> > > > > > > > transaction manager was denied". The code is at this point attempting to
> > > > > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > > > > user has been able to get through this code in the past. Of course, no known
> > > > > > > > changes.
> > > > > > > >
> > > > > > > > What causes this particular message?
> > > > > > > > --
> > > > > > > > Brad Ashforth

A question on Conversation timer persistence

I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:

"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."

I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?

Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?

Thanks,

Chris

I'm not sure I understand the requirements. Why is that you need to fire a timer as a result of a field change? The usual requirement is to fire a message so that some asynch processing happens later, but not based on a timer. Can you give some more details?

You can have only one timer per conversation. That what the BOL line refers to. You cannot have multiple timers, setting a new timer will erase the old one.

The timers are set as absolute time, not interval. After a database/server restart, if the time of the timer is in the past, then the timer will be fired.

HTH,
~ Remus

|||

Remus,

Thanks for the quick response.

Here's the workflow of the process: A user creates a work order for which they can assign a follow-up time. When this follow-up time arrives, I want to send a message to a Service Broker queue that I have already set up to process the message. If the follow-up time changes, the timer is adjusted to account for the change in time.

The only difference between what I need to do now and what I've already done is sending the message to the queue at a specific time. I imagined a trigger that would fire every time the follow-up time changed so that I could alter the conversation timer. This trigger would begin a new conversation, set a timer, and an activated stored procedure would look for the message type http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer and send a message to my original queue where it will be processed. I see 2 problems with my logic: I am looking for a DialogTimer message type, so the activated stored procedure only knows it is time to do something, but it has no message body that I can use to forward onto the final Service Broker queue. Also, I have no way of finding the conversation I started the last time the trigger fired.

I'm wondering if using a conversation timer is the wrong approach, and if so, what is?

Thanks,

Chris

|||

What you need is a table to associate the conversation which fired the timer with the original work order. When the work order is created, the trigger begin a dialog, sets the timer and then inserts into this table the newly created conversation handle and the work order id.

When the timer fires, the activated procedure receives the message, looks up the work order id in this table (based on the conversation handle the message was RECEIVE on) and does whatever work is required at that moment.

The same table can be also used when updates occur on the follow_up field. Instead of beginning a conversation, the trigger will look up this association table and find the existing conversation.

One thing to note is that timer messages are unlike any message in the sense that they are sent by one conversation endpoint to itself. So the conversation handle on which the timer was set is the same one as on which is going to be received.

I do believe that conversation timers are the right approach. No other approach I can think of is better. Conversation timers are very cheap from a resource point of view, completely contained within the database (this gives lots of advantages related to backup/restore, failover and availability), and offer the possibility to actually luch a procedure.

HTH,
~ Remus

|||

Thanks a lot Remus. A state table was what I came up with as well. I really appreciate being able to come here for valuable, practical advice on how to approach Service Broker issues. Thanks again,

Chris

a question about Exec

Hi,

When I run the following command on sql server:

exec ('print "OK"')
go

This message appears:

Server: Msg 128, Level 15, State 1, Line 1
The name 'OK' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.

Why? Thanks.findu_2005@.yahoo.com (findu_2005@.yahoo.com) writes:

Quote:

Originally Posted by

When I run the following command on sql server:
>
exec ('print "OK"')
go
>
This message appears:
>
Server: Msg 128, Level 15, State 1, Line 1
The name 'OK' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
>
Why? Thanks.

Because when the setting QUOTED_IDENTIFIER is in effect, "" delmits
identifier. This permits you to use table names like Order Details, for
instance:

SELECT ... FROM "Order Details"

In the SQL Server world, we tend to use [] for this function, but "" is
what ANSI mandates.

QUOTED_IDENTIFIER is on by default in most contexts, but not when you
run from SQLCMD, OSQL or Enterprise Manager in SQL 2000. Or for that
matter all DB-Library applications.

When the setting is off, SQL Server reverts to the original behaviour
from 4.x days when you couls use both '' and "" to delimit strings.

--
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|||Something like this will work

exec ('print ''OK''') . *Note: all are single quotes.

or you'll have to use SET QUOTED_IDENTIFIER OFF and then execute the
SQL.

Regards,
Thyagu.

A question about an error message

Hello!

I am new to this group and I hope anyone can help me. I have an error
message which is very complicated to me. Okay this message is very
simpel, but I don`t understand how to build my SQL statement. I use
MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
read the online help, but it is strange to me. At first here is my
statement:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC

GO
SET_QUOTED IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!I don't think you can have dbo.table.* and probably that's what the
error says when it tried to expand dbo.view_results_7a.*
...not sure though...

Comagmbh@.gmx.de wrote:

Quote:

Originally Posted by

Hello!
>
I am new to this group and I hope anyone can help me. I have an error
message which is very complicated to me. Okay this message is very
simpel, but I don`t understand how to build my SQL statement. I use
MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
read the online help, but it is strange to me. At first here is my
statement:
>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
>
ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC
>
GO
SET_QUOTED IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
>
The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!

|||Hi,
I modified your query a little bit:

ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC

Comagmbh@.gmx.de wrote:

Quote:

Originally Posted by

Hello!
>
I am new to this group and I hope anyone can help me. I have an error
message which is very complicated to me. Okay this message is very
simpel, but I don`t understand how to build my SQL statement. I use
MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
read the online help, but it is strange to me. At first here is my
statement:
>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
>
ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC
>
GO
SET_QUOTED IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
>
The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!

|||Hi

Thanks for your answer, but the problem still exists. There is the same
error message like before.

othellomy@.yahoo.com schrieb:

Quote:

Originally Posted by

>
ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC
>

I have forgot to tell you that the table tblIMSI_Stiering_Blacklist is
from another database but on the same server, so I have to write in
this way:

ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist.dbo.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC

The error is still the same :-(|||(Comagmbh@.gmx.de) writes:

Quote:

Originally Posted by

I am new to this group and I hope anyone can help me. I have an error
message which is very complicated to me. Okay this message is very
simpel, but I don`t understand how to build my SQL statement. I use
MsSQL 2000 and I am new to Microsoft SQL. I have searched the web and
read the online help, but it is strange to me. At first here is my
statement:
>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
>
ALTER VIEW dbo.view_results_7
AS
SELECT TOP 100 PERCENT dbo.view_results_7a.*, dbo.table.MCC,
dbo.table.MNC, ...dbo.table.HPRP
FROM dbo.view_results_7a INNER JOIN
dbo.table ON dbo.view_results_7a.MCC COLLATE SQL_Latin1_CP1_CI_AS =
dbo.table.MCC
WHERE dbo.view_results_7a.IMSI <>
IMSI_Blacklist.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY dbo.view_results_7a.BegTime DESC
>...
The part in the WHERE clause throws this error. It is the error message
number 107 and the message:
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."
What is wrong with this statement? My only experience in SQL is MySQL
and I have written this statement like a MySQL statement. So is there
anyone who can help me? Please I need your help!

Didn't you get a complete error message? That should tell you what is
wrong.

But I can see the error: IMSI_Blacklist.tblIMSI_Stiering_Blacklist comes
out of nowhere. I don't know what is supposed to be, so I can't suggest
an alternative. But I would not expect the above to work on MySQL either.

Two more things:
1) Remove TOP 100 PERCENT and ORDER BY. They don't mean anything logically,
but they can result in extra processing. In SQL 2000 it may seem that
if you run a SELECT on the view that you always get data in the order
of the ORDER BY clause, but that is mere chance, and it does not happen
that easily in SQL 2005.

2) Try to remove the COLLATE clause. Adding to the query when it is not
needed can prevent indexes from being used and hamper performance.

--
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|||Erland Sommarskog schrieb:

Quote:

Originally Posted by

>
Didn't you get a complete error message? That should tell you what is
wrong.
>
But I can see the error: IMSI_Blacklist.tblIMSI_Stiering_Blacklist comes
out of nowhere. I don't know what is supposed to be, so I can't suggest
an alternative. But I would not expect the above to work on MySQL either.
>
Two more things:
1) Remove TOP 100 PERCENT and ORDER BY. They don't mean anything logically,
but they can result in extra processing. In SQL 2000 it may seem that
if you run a SELECT on the view that you always get data in the order
of the ORDER BY clause, but that is mere chance, and it does not happen
that easily in SQL 2005.
>
2) Try to remove the COLLATE clause. Adding to the query when it is not
needed can prevent indexes from being used and hamper performance.
>

Hi! Okay I wrote this:

ALTER VIEW view_results_7
AS
SELECT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC AND view_results_7a.MNC =
table.MNC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist..tblIMSI_Stiering_Blacklist.IMSI

But there is still this error message:

Server: No 107, 16, state 3 procedure view_results_7
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."|||(Comagmbh@.gmx.de) writes:

Quote:

Originally Posted by

Hi! Okay I wrote this:
>
ALTER VIEW view_results_7
AS
SELECT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC AND view_results_7a.MNC =
table.MNC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist..tblIMSI_Stiering_Blacklist.IMSI
>
>
But there is still this error message:
>
Server: No 107, 16, state 3 procedure view_results_7
"The column prefix '%.*ls' does not match with a table name or alias
name used in the query."

What ugly environment are you using that only gives the unexpanaded
error message?

In any case, that IMSI_Blacklist..tblIMSI_Stiering_Blacklist still
comes out of nowhere. You need to mention it in the FROM-JOIN clause.

Besides, you have something called "table" in the query above. Since
TABLE is a reserved keyword, I would expect that to yield a syntax error,
so I suspect that you are not even posting the query you are using.

--
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|||Hi,
As suggested by Erland, table is a reserve keyword therefore you
can't have it in your query. I thought you are using table to
reference some pseodo table in the from clause so I used that because
you did not post the actual query. You have to post the actual query
for two reasons. Reason 1: I can see if you are using the 'table'
keyword in your query. If yeas, then it is obvious that was the error.
Number 2: if you have a table name in the select clause but missing
from the from clause then it will also cause error and also if you have
more tables in the from clause but not joining them appropriately in
the join or where clasue then it will be error too. So, the best thing
is if you post the actual query exactly as is.

Comagmbh@.gmx.de wrote:

Quote:

Originally Posted by

Hi
>
Thanks for your answer, but the problem still exists. There is the same
error message like before.
>
othellomy@.yahoo.com schrieb:
>

Quote:

Originally Posted by

ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC

>
>
I have forgot to tell you that the table tblIMSI_Stiering_Blacklist is
from another database but on the same server, so I have to write in
this way:
>
ALTER VIEW view_results_7
AS
SELECT TOP 100 PERCENT view_results_7a.*, table.MCC,
table.MNC, ...table.HPRP
FROM view_results_7a INNER JOIN
table ON view_results_7a.MCC = table.MCC
WHERE view_results_7a.IMSI <>
IMSI_Blacklist.dbo.tblIMSI_Stiering_Blacklist.IMSI
ORDER BY view_results_7a.BegTime DESC
>
The error is still the same :-(

A problem with SQL Server Authentication?

Hi everybody...
I need to implement a publication but I got the next message when I start
the assistant: "SQL Server Agent on 'USER07' currently uses the system
account, which causes replication between servers to fail. In the following
dialog box, specify another account for the Service startup account". I
don't know which account I have to specify because the error persist. Do I
have to create a new account?
Thanks very much for any help!
Juan
juan,
if you're using replication across different boxes, you'll need a domain
user as the startup account for the SQL Server agent.
There's a few sections on this under Replication, Security in BOL.
HTH,
Paul Ibison
|||If I might just quibble with Paul's excellent answer.
For a push subscription the SQL Agent Account on the distribution must be
able to access the snapshot share on the publisher. This share is by default
\\PublisherServerName\C$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\UNC
If your Publisher and Distributor are on the same server this is not a
problem.
Its only a problem when you are doing pull subscriptions, because then its
your SQL Server agent account on the Subscriber which must be able to map a
drive to
\\PublisherServerName\C$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\UNC
So for this to work
1) your Subscriber must be part of the admin group on the Publisher, and you
select the impersonate the SQL Server Agent account on the Publisher
or
2) you change the share name to a share which the SQL Server agent account
on the subscriber can access and give permission to this account to access
the share and the underlying files and folders.
If you are in an untrusted domain you can use pass through authentication
http://support.microsoft.com/default...&Product=sql2k
and if you are an internet user you will have to use either the everyone
group/account (not recommended) or use FTP (recommended).
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Om$SH07IEHA.228@.TK2MSFTNGP10.phx.gbl...
> juan,
> if you're using replication across different boxes, you'll need a domain
> user as the startup account for the SQL Server agent.
> There's a few sections on this under Replication, Security in BOL.
> HTH,
> Paul Ibison
>

A float data type is recognized as varchar

Hello,
I am on a c# project right now. If I want to insert a float data type (c#)
into the sql server, I am getting the message then, that a varchar cannot be
inserted into a float. I tried it with money, small money and decimal, too.
And the result is ever the same. (Varchar cannot be entered into a
float/decimal, money etc..)
I am using direct sql command, not a SP.
As example: (simplified, without connection opening, etc.)
In c#:
float variable = 20;
sqlcommandobject.commandtext = "insert into products (price) VALUES
('"+variable+"')"Why are you putting the value between apostrophes?

> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
sqlcommandobject.commandtext = "insert into products (price) VALUES
(" + variable + ")"
AMB
"the friendly display name" wrote:

> Hello,
> I am on a c# project right now. If I want to insert a float data type (c#)
> into the sql server, I am getting the message then, that a varchar cannot
be
> inserted into a float. I tried it with money, small money and decimal, too
.
> And the result is ever the same. (Varchar cannot be entered into a
> float/decimal, money etc..)
> I am using direct sql command, not a SP.
> As example: (simplified, without connection opening, etc.)
> In c#:
> float variable = 20;
> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
>
>|||Get rid of the single quotes around the value, e.g.
sqlCommandObject.CommandText =
"INSERT INTO PRODUCTS (price) VALUES (" + variable.ToString() + ")"
Better yet, use parameters in your query so that it will automatically do
the formatting for you, this is also more flexible for data types like
binary, etc. and you don't have to worry about escaping text strings::
sqlCommandObject.CommandText =
"INSERT INTO PRODUCTS (price) VALUES (@.var)";
sqlCommandObject.Parameters.Add("@.var", variable);
Mike
"the friendly display name"
<thefriendlydisplayname@.discussions.microsoft.com> wrote in message
news:26A40CD8-45CE-4149-BF00-6064A0741640@.microsoft.com...
> Hello,
> I am on a c# project right now. If I want to insert a float data type (c#)
> into the sql server, I am getting the message then, that a varchar cannot
> be
> inserted into a float. I tried it with money, small money and decimal,
> too.
> And the result is ever the same. (Varchar cannot be entered into a
> float/decimal, money etc..)
> I am using direct sql command, not a SP.
> As example: (simplified, without connection opening, etc.)
> In c#:
> float variable = 20;
> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
>
>|||Thank you.
The parameters solved the problem.
"Mike Jansen" wrote:

> Get rid of the single quotes around the value, e.g.
> sqlCommandObject.CommandText =
> "INSERT INTO PRODUCTS (price) VALUES (" + variable.ToString() + ")"
> Better yet, use parameters in your query so that it will automatically do
> the formatting for you, this is also more flexible for data types like
> binary, etc. and you don't have to worry about escaping text strings::
> sqlCommandObject.CommandText =
> "INSERT INTO PRODUCTS (price) VALUES (@.var)";
> sqlCommandObject.Parameters.Add("@.var", variable);
> Mike