"Certificate not yet valid"

Hi guys,

I'm having this really strange issue with mirroring.

I've followed the instructions on http://msdn2.microsoft.com/en-us/library/ms191140.aspx to the letter, leaving out the witness server part, as I don't have one.

All works OK, but when activating the mirroring on the principal server I get the usual error 1418 error. I fired up SQL Server Profiler to see what was happening, and the following error emerged.

Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not yet valid. State 104.

Anyone have any ideas?

If you live in the eastern hemisphere the newly created certificate is not yet valid at creation for a number of hours equal with the time difference between you time zone and GMT.
You van specify the certificate start date explictly one day in the past or wait for the numbers of hours specified (they are probably valid by now)

HTH,
~ Remus|||

Well, I live in GMT, but it works now, so I can only presume something similar was the problem.

Cheers

"[DBNETLIB]ConnectionWrite (send())" && "Communication link failure"

Hi ALL

I am facing a strange problem using ADO to connect Sql Server that I can not resolve it.

My program connected to SqlServer when it started, the state of the connectionPtr was adoStateOpen.Then I disable the network card and enable it immeditately, the errors happened when I executed the sql query probably because of connectionPtr was invalid,but the state of connectionPtr was always adoStateOpen.

1)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

2)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver]Communication link failure.

Should I reconnect to SqlServer?How can I catch this error when the connection is valid except other errors?

Sorry,my english is poor and thanks for your response.

Thanks a lot.

Env:

1)OS : Windows 2000 Server

2)SqlServer:Sql Server 2000

When you disable network card, the OS may inform network layer to invalidate the socket we are using. The problem you described could happen, and we don't support this scenario.

Btw, why do you want to disable network card?

"[DBNETLIB]ConnectionWrite (send())" && "Communication link failure"

Hi ALL

I am facing a strange problem using ADO to connect Sql Server that I can not resolve it.

My program connected to SqlServer when it started, the state of the connectionPtr was adoStateOpen.Then I disable the network card and enable it immeditately, the errors happened when I executed the sql query probably because of connectionPtr was invalid,but the state of connectionPtr was always adoStateOpen.

1)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

2)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver]Communication link failure.

Should I reconnect to SqlServer?How can I catch this error when the connection is valid except other errors?

Sorry,my english is poor and thanks for your response.

Thanks a lot.

Env:

1)OS : Windows 2000 Server

2)SqlServer:Sql Server 2000

When you disable network card, the OS may inform network layer to invalidate the socket we are using. The problem you described could happen, and we don't support this scenario.

Btw, why do you want to disable network card?

<Multiple Publications>

I have a strange situation...
All of a sudden, my publications are now <Multiple Publications>
instead of each being listed individually.
How can I 'split' these back up, to display each publication?
-AND-
How can I see what publications are contained in the <Multiple
Publication> distribution agent?
AHIA,
Larry...
Normally it means that you have more than one publication per database going
to the same subscription database and the agent is being shared. Set the
indepdent agent setting to true for them to be split back.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1133541672.973822.251160@.o13g2000cwo.googlegr oups.com...
>I have a strange situation...
> All of a sudden, my publications are now <Multiple Publications>
> instead of each being listed individually.
> How can I 'split' these back up, to display each publication?
> -AND-
> How can I see what publications are contained in the <Multiple
> Publication> distribution agent?
> AHIA,
> Larry...
>

<= and comparing strings

Hi,

I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707

What I want to to is to search for log posts that are in between two dates.

I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.

How would I solve this

Best /M

Well, I would suggest you convert that data into a datetime value and you will get a lot more value. But, the problem you are having is that 'A' comes before 'AA' in a sort order. So, 070422 is before any value in your table that has a time portion. So use < 070423 and you will get the values you need:

drop table test

go

create table test

(

timecreated char(16)

)

go

insert into test

select '0704212112538707'

union all

select '0704223112538707'

union all

select '0704212112338707'

union all

select '0704242112338707'

go

select *

from test

where timeCreated >= '070421'

and timeCreated < '070423'

timecreated
-
0704212112538707
0704223112538707
0704212112338707

'0704242112338707' was not returned.

|||You cannot compare strings in this manner and get teh results you are wanting. You will need to convert the field to a datetime for comparisons.
|||

What if you sum 1 day to the final timecreated?. I mean, of you want the '070421' to '070422' you can probe with the '070422' + 1 day -> '070421' to '070423'

#Error on calculation for dividing by 0

I am using the following expression but am getting strange results:
I am trying to avoid using the calulation when the qtyprocessed field is 0
to avoid the error. What the expression basically says is if qtyprocessed = 0 make the Calulated field = 0, else use the calculation as there is a realy
number <> 0. However I still recieve the #Error.
=IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100, 2) & "%")
This does work below. When qtyprocessed = 0 i get true and false otherwise.
This function domonstrates the functionallity correctly and how I want it to
work.
=IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
There is a problem when I throw the calculation in there. This is very
confusion as the calculated field should be set to 0 when qtyprocessed = 0
to avoid the error. The only explanation I can think of is that somehow the
calucation is being parsed or looked at regardless of the Iif.
TIA,
SteveIs it because your concatenating a string to a number? Shouldn't you
convert all results to a common value like string so it would be:
=IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "0",
FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100, 2).ToString() & "%")
"Steve Wofford" <IntraRELY@.yahoo.com> wrote in message
news:%23d6WnMpMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed
> = 0 make the Calulated field = 0, else use the calculation as there is a
> realy number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false
> otherwise. This function domonstrates the functionallity correctly and how
> I want it to work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow
> the calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>|||Additionally why not take out the formatting, and simply set the textbox
format to P2..Then your formula could be
=IIf(Fields!qtyprocessed.Value = 0, 0,
((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Steve Wofford" wrote:
> I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed => 0 make the Calulated field = 0, else use the calculation as there is a realy
> number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false otherwise.
> This function domonstrates the functionallity correctly and how I want it to
> work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow the
> calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>
>|||you can format the number after execute iff expression... too
try this...
=FormatNumber(IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
(Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100, 2) & "%"
"Steve Wofford" <IntraRELY@.yahoo.com> escreveu na mensagem
news:%23d6WnMpMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed
> = 0 make the Calulated field = 0, else use the calculation as there is a
> realy number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false
> otherwise. This function domonstrates the functionallity correctly and how
> I want it to work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow
> the calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>|||Steve,
First, set the format property of the cell to P2 (Percentage with 2
decimal places). This eliminates the need for FormatNumber and the *
100 and the & "%".
Second, your expression will throw the #Error every time the
qtyprocessed value is zero because SSRS evaluates all the parts of the
expression. Makes no sense but it does.
So, to get around this, most people create a custom code function that
does the divide by zero check and calculations, then insert that into
the cell you formatted as P2.
I use:
Public Function DivideBy(ByVal exp1, ByVal exp2)
If exp2 = 0 Then
DivideBy = 0
Else
DivideBy = exp1 / exp2
End If
End Function
Once the function is created, insert this expression into your cell:
=code.DivideBy((Fields!qtyprocessed.Value -
Fields!qtyerror.Value),Fields!qtyprocessed.Value)
That should do you well.|||Thanks for all the input. After testing all of the repsonses the one that
worked was the one with the custom function. I still recieved an #Error on
all the other strings. I do appreciate all the input.
TIA,
Steve
"Steve Wofford" <IntraRELY@.yahoo.com> wrote in message
news:%23d6WnMpMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed
> = 0 make the Calulated field = 0, else use the calculation as there is a
> realy number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false
> otherwise. This function domonstrates the functionallity correctly and how
> I want it to work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow
> the calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>