End of support for SQL 2000

Does anybody know when Microsoft is planning to cut support for SQL 2000? Just recently we have learned that 7.0 is no longer supported.

Thank you in advance,

Leon Shargorodsky

here you go: http://support.microsoft.com/lifecycle/?LN=en-us&p1=2852&x=6&y=8

Thanks,

Peter Saddow

end of month

hello
what is the function that calculate the number of days in a given month ?
10xromy
I don't know such functions
Try this
declare @.d datetime
set @.d='20050110'
select datestring, case
when isdate(datestring/100*100+31) = 1 then 31
when isdate(datestring/100*100+30) = 1 then 30
when isdate(datestring/100*100+29) = 1 then 29
when isdate(datestring/100*100+28) = 1 then 28
end
from (
select convert(varchar,@.d,112) as datestring
) D
"romy" <romyedevelop@.hotmail.com> wrote in message
news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> hello
> what is the function that calculate the number of days in a given month ?
> 10x
>
>|||You can write your own user defined function that takes the month number or
month name as input parameter and returns the number of days in that month
using a CASE or an IF statement.
"romy" <romyedevelop@.hotmail.com> wrote in message
news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> hello
> what is the function that calculate the number of days in a given month ?
> 10x
>
>|||Try,
declare @.d datetime
set @.d = '20050215'
select datediff(day, convert(char(6), @.d, 112) + '01', dateadd(month, 1,
convert(char(6), @.d, 112) + '01'))
go
AMB
"romy" wrote:

> hello
> what is the function that calculate the number of days in a given month ?
> 10x
>
>
>|||romy,
"romy" <romyedevelop@.hotmail.com> wrote in message
news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> hello
> what is the function that calculate the number of days in a given month ?
> 10x
I use this:
declare @.d datetime
set @.d = '20050215'
select day(dateadd(ss, -1, dateadd(mm, datediff(m, 0, @.d) + 1, 0)))
Andrea|||DECLARE @.month INT
SET @.month=2
select day(CONVERT(Datetime, Convert(VARCHAR, (CASE WHEN @.month=12 THEN 0
Else @.month END +1)) + '/01/2005' ) -1)
"Andrea Benedetti" <abenedetti@.absistemi.it> wrote in message
news:OqHur9bGFHA.2748@.tk2msftngp13.phx.gbl...
> romy,
> "romy" <romyedevelop@.hotmail.com> wrote in message
> news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
?
> I use this:
> declare @.d datetime
> set @.d = '20050215'
> select day(dateadd(ss, -1, dateadd(mm, datediff(m, 0, @.d) + 1, 0)))
> Andrea
>

End Date and Beginning date in an ADP

Can anyone pleae help me, I inheited this databas which is an MDB and I have to convert it to an ADP (Sql server is the engine) and I need for my users to be able to enter a end date and a beginning date when they first open a report so they can see from the end of the month to the beginning in one report. This is Accesses code as an MDB

Code Snippet

[code]SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]

SELECT [Main Table].[IR Number], [Main Table].[Date], [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].[Action], [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].[Date] Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [IR Number]
GO

And here is SQL Servers code the only problem is that when I run this in the Query analyzer it gives me an error message that says, can anyone help me please?

"Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Enter the Start Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Enter the End Date'."

Change the [Enter the start date] placeholders to parameters (@.StartDate @.EndDate)|||

Oh thank you so much is this what you mean?

Code Snippet

SELECT [IR Number], Date, Inspector, Area, Violation, [Violation Type], Loss, [Loss Type], Employee, Action, Notes, [Action Type]
FROM dbo.[Main Table]
WHERE (Date = @.StartDate) AND (Date = @.EndDate)

|||

You can still use the BETWEEN clause

Code Snippet

SELECT [IR Number], Date, Inspector, Area, Violation, [Violation Type], Loss, [Loss Type], Employee, Action, Notes, [Action Type]
FROM dbo.[Main Table]
WHERE Date IS BETWEEN @.StartDate AND @.EndDate

|||

Whoops, the 'IS' should not be in there

Code Snippet

SELECT [IR Number], Date, Inspector, Area, Violation, [Violation Type], Loss, [Loss Type], Employee, Action, Notes, [Action Type]
FROM dbo.[Main Table]
WHERE Date BETWEEN @.StartDate AND @.EndDate

|||

BRUCE YOU ARE THE BEST THANK YOU SO MUCH YOU DONT KNOW HOW LONG I HAVE BEEN WRESTLING WITH THIS

I inherited this database and I have queries that have to be converted from an MDB to an ADP and its not easy.

thank you so much

sql

END CONVERSATION WITH CLEANUP, Is this bad? Records pile up in queues without it.

I have read from a variety of sources that using the "WITH CLEANUP" option on a "END CONVERSATION" statement is bad and unnecessary. (Question #1) Is this true?

My code does not work properly if I don't use the "WITH CLEANUP" option. My code leaves closed conversation records in the queues if I leave out the "WITH CLEANUP" option. The "END CONVERSATION" statement is executing properly and flagging the conversation record as closed but the records don't get deleted. All the messages are going back and forth properly too.

My code is based on the HelloWorld ServiceBroker sample which does not use "WITH CLEANUP". When I run the sample scripts everything works great and the conversation records are deleted. However, this sample does not uses an activation stored procedure to receive messages and respond with the results. When I copy and paste the receive messages sample code into an activation stored procedure is when the problem comes up. It's the same code! (Question #2) Why am I getting different results depending how the code is executed/activated?

This is could be a tough one. I just hope somebody else has seen it too and figured it out. Thanks for the help!

Thanks,
Greg Van Mullem

? "Bad" and "unnecessary" are interesting labels, because they can be applied so differently to various scenarios. If WITH CLEANUP were not necessary in any scenario, I seriously doubt it would have been included in the product. But is it right for every scenario? No. Essentially, according to the documentation and Roger Wolter's book (which you should definitely buy a copy of if you're working with SSB), those closed conversations are supposed to stick around for a while, to avoid certain types of replay attacks. But they should disappear after a while. As for the WITH CLEANUP, it is bad if your conversation is truly a conversation (i.e., a two-way message exchange), because when one side ends the conversation and uses that option, the other side doesn't find out about it. And that can certainly be bad in many cases. On the other hand, if you're only doing one-way transmission, that may or may not be an issue. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Greg Van Mullem@.discussions.microsoft.com> wrote in message news:1c9bb57b-0113-4fd6-ba81-14c686a99b16@.discussions.microsoft.com... I have read from a variety of sources that using the "WITH CLEANUP" option on a "END CONVERSATION" statement is bad and unnecessary. (Question #1) Is this true?My code does not work properly if I don't use the "WITH CLEANUP" option. My code leaves closed conversation records in the queues if I leave out the "WITH CLEANUP" option. The "END CONVERSATION" statement is executing properly and flagging the conversation record as closed but the records don't get deleted. All the messages are going back and forth properly too.My code is based on the HelloWorld ServiceBroker sample which does not use "WITH CLEANUP". When I run the sample scripts everything works great and the conversation records are deleted. However, this sample does not uses an activation stored procedure to receive messages and respond with the results. When I copy and paste the receive messages sample code into an activation stored procedure is when the problem comes up. It's the same code! (Question #2) Why am I getting different results depending how the code is executed/activated?This is could be a tough one. I just hope somebody else has seen it too and figured it out. Thanks for the help!Thanks,Greg Van Mullem|||

Adam,

Thanks for the info. This is exactly what I was looking for. I will be removing the "WITH CLEANUP" clause from my code now that I know it's not necessary.

Also, I have Roger Wolters book and your book "Pro SQL Server 2005". Both are books excellent! I found the XML section in your book to be especially helpful in setting up my Service Broker messages.

Thanks,
Greg Van Mullem

end conversation causing blocks

Hi

I was trying to clean up some conversation in Service Broker and caused alot of blocking that I seem to unable to kill. there was 1 conversation that I was not able to end, so I wanted to restart sql service, But I can't even restart the SQL service. I get the following in Event Viewer

Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_INIT', id 00000001A2B03540, type 2, Task 0x0000000000C2EDA8 : 0, waittime 5400, flags 0xa, owning task 0x00000002DEBCA5C8. Continuing to wait.

Has anyone come across this

thanks

Paul

have you tried to clear the conversation?

declare @.conversation uniqueidentifier

while exists (select 1 from sys.conversation_endpoints )

begin

set @.conversation = (select top 1 conversation_handle from sys.conversation_endpoints )
print @.conversation
end conversation @.conversation with cleanup

end

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.