Conversion for Time

I can get my DB to accept my date by doing the following: row.Item("RequestDate") =Me.fullDate.Date --I have fulldate dimensioned as date above. However if I try to do the follwing for a Time it gives me an error when it trys to update the DB the column is set to datetime & when I check the value of the row Item in my command window it says

?row.Item("BeginTime")
#6:00:00 AM# {Date}
[Date]: #6:00:00 AM#

row.Item("BeginTime") =CDate(ddlBegin.SelectedValue & beginAMPM)
row.Item("EndTime") =CDate(ddlEnd.SelectedValue & endAMPM)
The SQL Error I get is the following:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source Error:

Line 335: row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM)Line 336: DsVacationData1.RequestData.AddRequestDataRow(row)Line 337: SqlDataAdapter2.Update(DsVacationData1)Line 338: DsVacationData1.AcceptChanges()Line 339: End Sub

Thanks for any help.Ok i figured out what I was doing wrong, to a point. I got it to store my time but it also put in today's date along w/the time is there a way to just enter in the time w/no date?|||There's no Time data type. There's only datetime andsmalldatetime. So the answer to your question, only way is toutilize char, var, nchar, nvar or add in a universal date that will beignored.
|||

The cause of the problem is ANSI SQL NULL is an unknown while .NET NULL is an empty string so the difference is causing the overflow. I found two VB code and a C# link with code. Hope this helps.
http://www.inq.net/WebLog/dbalzer/archive/2005/06/16/125145.aspx

(If SomeDate = DateTime.MinValue Then
cmd.Parameters("@.SomeDate").Value = DBNull.Value
Else
cmd.Parameters("@.SomeDate").Value = SomeDate
End If)
First codeblock

(Public Function chkDateParam(ByVal d As Date, ByRef sqlParam As
SqlParameter)
Try
If d = System.DateTime.MinValue Then
sqlParam.Value = DBNull.Value
Else
sqlParam.Value = d
End If

Catch ex As NullReferenceException

'if the field is blank, str will = null. so set the param to
null too!
sqlParam.Value = DBNull.Value

End Try
End Function )
Second codeblock

sqlsql

Conversation enpoints are not getting cleaned up on target end

Hi,

We are using service broker between two different instances. But were facing issues with increasing row count in conversation_endpoints view. We found that this was because we were using default value for lifetime for the conversation which is value of size int. Later on we changed the lifetime to 1 minute and conversation_endpoints view start getting cleaned up after 30 minutes

Following commands are used to send message

Before :

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

After:

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

But as we use default life time for a long due to which around 15 million records got acumlated in this view. What is the best way to clean up this view.

END Conversation @.handle with cleanup is taking so long is their any other way to do this

Thanks,

Prashant

Not sure if this is the total cause of your problem but ending a conversation before the first message is sent on it tends to leave it in an unstable state. Remus has a good explanation here: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx|||

Thanks for information. But as of now i want to know what is the best way do cleanup on sys.conversation_endpoints with 14 million rows

|||

I see - not interested in doing it right but doing it wrong faster.

I assume you have a script that loads all the dialog handles into a cursor and then calls END DIAlOG WITH CLEANUP on each one. If you want your application to continue working while you are cleaning up then that's the only way. If you can shut down your applicationso that there are no dialogs or messages that you care about then an ALTER DATABASE command with the SET NEW_BROKER parameter will blow away all traces of any dialogs and messages.

|||

If you moved to SP2, you can do ALTER DATABASE ... SET NEW_BROKER, it will truncate every relevant internal table (conversation_endpoints, conversation_groups, transmission_queue and all message queues).

Do not attempt this with pre-SP2 SQL 20005 because it will literaly do 14 mil END CONVERSATION ... WITH CLEANUP in one transaction lasting forever.

Note that NEW_BROKER will nuke every conversation, including currently active ones. If you cannot afford this, then you must END conversations individually, if you batch commit it doesn't take that long actually.

|||

Thanks for the instant replies. Unfortunately we cannot shutdown the application here. So i think only option left is using WITH CLEANUP. But this might be a help in sometime in future. Thanks again for wonderful upport

|||In that case, be sure to use Remus' suggestion of batch commits. END a few hundred conversations and then commit the transaction. This is much more efficient than doing each END CONVERSATION in its own transaction.

Controlling the freeze pane location in Reporting Services

So I have a report that has a header, and in the body there is a
table. The table has a heading row. When I export this report to
excel, the freeze pane is put under the header.
Is there a way to specify the location of the freeze pane when the
report is exported to excel? I would like to have the freeze pane
directly below the column header row.
One possible solution I thought of was to put the column header row
into the actual header, but I can't do that as right above the column
header row is a summary of the data in the table. This summary
references fields from my datasets. And anything that references a
field cannot be put in the header row.
Thanks in advance.On Apr 28, 8:41 am, Jesse...@.gmail.com wrote:
> So I have a report that has a header, and in the body there is a
> table. The table has a heading row. When I export this report to
> excel, the freeze pane is put under the header.
> Is there a way to specify the location of the freeze pane when the
> report is exported to excel? I would like to have the freeze pane
> directly below the column header row.
> One possible solution I thought of was to put the column header row
> into the actual header, but I can't do that as right above the column
> header row is a summary of the data in the table. This summary
> references fields from my datasets. And anything that references a
> field cannot be put in the header row.
> Thanks in advance.
As far as I know, there is not really anyway to control this. I'm
actually surprised that you have managed to get the Excel export to
maintain the freeze panes at all, as I have not seen it work
automatically after export. Sorry that I could not be of further
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Controlling Data Modification at row level

Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.

At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.

I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).

Can anyone suggest a method of implementing a more generic row
permissions system?

Cheers,
Ben"Bomza" <benelvin@.hotmail.com> wrote in message
news:1106584255.027100.151190@.c13g2000cwb.googlegr oups.com...
> Hi,
> I'm writing an application that involves data that has a set of users
> that are allowed to perform certain operations on it.
> i.e. Only the row owner can modify a row, but there is a set of users
> who can view it.
> At the moment, I've started to implement this by calling a UDF at the
> beginning of each stored procedure that validates that the user is
> allowed to call the procedure on that particular row (trusting a higher
> teir to verify the user), and throws an error if they are not.
> I don't particularly like this solution, as I need a UDF for each
> procedure, and will have to re-write the udf's if the access rules
> change (which they might).
> Can anyone suggest a method of implementing a more generic row
> permissions system?
> Cheers,
> Ben

Unfortunately there's no built-in or generic solution, so you need to
implement something yourself. Here's an alternative view-based approach,
which might give you some more ideas:

http://vyaskn.tripod.com/row_level_...r_databases.htm

Simon|||Views are of good use when situations like this.
In your table create a field called userID and assing the userID to
each row that has modifying permission. Create a view like this for
egsample....

Create view <name>
AS
Select col1, col2, col3
from <table>
where userid = sUser_sName().

Then update the table by updating this view so that only those records
that are visible to that user can update these records on the
destination table....!

Also create another view where userID<> sUser_sName() and grant only
view permission to the users... So they cant update the table thru this
view...!

This is just a guess. I have not tried it myself. So feel free to
comment on this..!!

Good luck..!|||They're both interesting ideas, and are probably a fair bit faster than
the way I came up with. I like the idea of being able to throw an
error when a client does something illegal rather than just doing
nothing and as I'm using ADO.NET, my "authorising" UDF method provides
a neat way of doing it. At worst the UDF does an EXISTS on a SELECT
statement, so its not a totally horrible method.

It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...|||Bomza (benelvin@.hotmail.com) writes:
> It must be a fairly common problem, so when I'm finished I might turn
> it into a generic framework where you can grant and evoke various
> permissions to any users on any rows, but at the moment I've just got
> to get this working :) I think its all been addressed in SQL 2005
> anyway...

Not really. There is no particular support in SQL 2005 for row-level
security either.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bomza" <benelvin@.hotmail.com> wrote in message
news:1106598807.774687.263530@.z14g2000cwz.googlegr oups.com...
<<>>
> It must be a fairly common problem, so when I'm finished I might turn
> it into a generic framework where you can grant and evoke various
> permissions to any users on any rows, but at the moment I've just got
> to get this working :) I think its all been addressed in SQL 2005
> anyway...

I usually address this in the front end design.
Something like.
All users see everything in one screen is read only.
There are links butons or whatever take them to another screen to do
updates.
The update screen is only available to supervisors or when you pick data is
maintained by your team.

This does of course rely on users not trying to break the system by
connecting up using odbc or something.
But.
I suspect others use the same sort of approach and that's why there aren't
loads of solutions available.

--
Regards,
Andy O'Neill

Contains + Special caractere

When I execute this request
select *
from fiche
where contains(numerofiche,'A30/05')
I get all the row where numerofiche contains 05. Does it come from the
caractere "/"? Is there a way to get exactly the row containing exactly
"A30/05" using the contains predicat.
Thanks
I don't get this result.
What word breaker are you using, French, neutral, or another language?
Do you get this result if you wrap your search phrase in double quotes?
"Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
news:OxSfGRxwEHA.1192@.tk2msftngp13.phx.gbl...
> When I execute this request
> select *
> from fiche
> where contains(numerofiche,'A30/05')
> I get all the row where numerofiche contains 05. Does it come from the
> caractere "/"? Is there a way to get exactly the row containing exactly
> "A30/05" using the contains predicat.
> Thanks
>
|||what do you mean by "word breaker". I'm using a french version of SQL Server
2000
If I execute the request:
select *
from fiche
where contains(numerofiche,'"A30/05"')
I've got the same set of results
numerofiche
A30/05
C99/05
E 05
05
H 05
"Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de
news:ubME$LzwEHA.3844@.TK2MSFTNGP09.phx.gbl...
> I don't get this result.
> What word breaker are you using, French, neutral, or another language?
> Do you get this result if you wrap your search phrase in double quotes?
>
> "Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
> news:OxSfGRxwEHA.1192@.tk2msftngp13.phx.gbl...
>
|||The default for a french version of SQL Server will be using the french word
breaker.
You can verify this by doing the following:
sp_help_fulltext_columns 'TableName' and then noting the number for the
FullText_Language column. It should be 1036. You can get the language which
matches this by issuing a xp_MSFullText which will display a list of
languages and their LocID which corresponds to the value in the
FullText_Language column.
I do get this behavior with the French Word breaker, but not the English
ones or the neutral word breaker.
Are you doing any inflectional or FreeText searches? If not, your best
optioin might be to use the neutral word breaker.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
news:ujzUBdzwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> what do you mean by "word breaker". I'm using a french version of SQL
Server[vbcol=seagreen]
> 2000
> If I execute the request:
> select *
> from fiche
> where contains(numerofiche,'"A30/05"')
> I've got the same set of results
> numerofiche
> A30/05
> C99/05
> E 05
> 05
> H 05
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de
> news:ubME$LzwEHA.3844@.TK2MSFTNGP09.phx.gbl...
exactly
>
|||another option is to replace the / with a _
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
news:ujzUBdzwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> what do you mean by "word breaker". I'm using a french version of SQL
Server[vbcol=seagreen]
> 2000
> If I execute the request:
> select *
> from fiche
> where contains(numerofiche,'"A30/05"')
> I've got the same set of results
> numerofiche
> A30/05
> C99/05
> E 05
> 05
> H 05
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de
> news:ubME$LzwEHA.3844@.TK2MSFTNGP09.phx.gbl...
exactly
>
|||BTW - you'ld have to do this replacement on your content as well in your
search phrases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ual$280wEHA.2676@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> another option is to replace the / with a _
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
> news:ujzUBdzwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Server
quotes?[vbcol=seagreen]
the
> exactly
>