problem with Log

Hello,

Could anybody let me know why is this happening while executing a stored procedure through asp.net

Log Entry string is too long. A string written to the event log cannot exceed 32766 characters.

general

The message sounds like you tried to write a very large string to you system event log.
Possibly as a result of throwing an exception when trying to call the stored proc.

Do you have an Exception logging mechanism in place that is configured to put exception messages into your system Event Log?

Can you step into the code to see any possible exception when you make the stored proc call, or turn off exception handling/logging for this call and let the exception go to the page.

sql

Problem With Insert Using SQLDataSource

I have a table with 2 columns - "memberid" is a guid and "Interest" is a string. In an aspx page I want to read in all values of "Interest" for a given "memberid" and display approprite checkboxes as checked. The user can then change which boxes are checked and I want to record the new list of selected items in the table. I created a SQLDataSource (see below) but when I run it, I get this error:

========== Error ===========

Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'DB_136571.dbo.gs_MemberInterests', column 'memberid'. Use the CONVERT function to run this query.

============================

The only use of this SQLDataSource is to delete all entries for a given user and then insert an entry for each checked checkbox. The code to set the session variables is as follows:

======== Code ============

Dim guidMemberidAs Guid =CType(user.ProviderUserKey, Guid)

Session("currmember") = guidMemberid

....

Session("currinterest") =CType(item.FindControl("CheckBox1"), CheckBox).Text

==========================

Any ideas of what I am doing wrong here?

========= SQLDataSource ==========

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:GoodSamSiteDB %>"

DeleteCommand="DELETE FROM [gs_MemberInterests] WHERE [memberid] = @.memberid"

InsertCommand="INSERT INTO [gs_MemberInterests] ([memberid], [Interest]) VALUES (@.memberid, @.Interest)"

SelectCommand="SELECT memberid, Interest FROM gs_MemberInterests WHERE (memberid = @.memberid) AND (Interest = @.interest)">

<DeleteParameters>

<asp:ParameterName="memberid"Type="Object"/>

</DeleteParameters>

<SelectParameters>

<asp:SessionParameterName="memberid"SessionField="currmember"Type="Object"/>

<asp:SessionParameterName="interest"SessionField="currinterest"/>

</SelectParameters>

<InsertParameters>

<asp:ParameterName="memberid"Type="Object"/>

<asp:ParameterName="Interest"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

=============================

Type="Object"|||

Motley wrote:

Type="Object"

Thanks. I assume you are referring to the fact that the "Type='Object'" has to be removed. I just found a reference to that bug. That cleared the problem.

sql

Problem with formatting with CR and LF in a string

Hi,

I'm trying to use carriage return (CR) and line feed (LF) to format a string for use with the msdb.dbo.sp_send_dbmail stored procedure. My goal is to have several lines of text delineated with CR LF. However, it appears that SQL Server 2005 is replacing the CR LF with 2 spaces. The snipped below demonstrates this.

declare @.msg nVarChar(100)
set @.msg = 'this is before the cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'and this is after the cr'
select @.msg

If I copy the text returned by the select into a hex editor what I see for the portion of the string "cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'a" is:

63 72 20 20 0B 0C 20 0E 0F 61

I was expecting:

63 72 20 0A 0B 0C 0D 0E 20 61

This is what leads me to believe the CR and LF are being replaced with spaces as it shows hex 20 (SPACE) instead of hex 0A (LF) and hex 0D (CR).

Can someone explain to me how to make this do what I want it to?

Thanks
John

Given this simple test:

CREATE TABLE dbo.CRLF(id int,crlf nvarchar(100))

DECLARE @.msg nVarChar(100)

SET @.msg = 'this is before the cr ' + char(10)+ char(13) +'and this is after the cr'

INSERT INTO dbo.CRLF(id,crlf)VALUES(1, @.msg )

SELECT * FROM dbo.CRLF

I get this if I copy the results from Query Editor:

idcrlf

1this is before the crand this is after the cr

And this ouput put if I Open Table in Object Explorer:

1this is before the cr

and this is after the cr

|||I'm unable to duplicate your results. I get a single line when opening the table in the Object Explorer.

If you add a DECLARE @.LongMsg and set @.LongMsg = @.Msg + @.Msg + @.Msg and then use @.LongMsg as the @.body parameter of the msdb.dbo.sp_send_dbmail your email will be a single long line with no CR LF showing in the text. This is basically what I'm trying to do.

Perhaps there is a server setting somewhere that needs to be tweaked on our server.

John|||

Ya it true but you see 2 box kind of symbol when you opened

the table trough Object explore, select the option “Result to Text” in the

Query panel and select the row then output shows like this.

(1 row(s)

affected)

idcrlf

--

1This is 1 msg

and this is

2 msg

this is 3

msg

(1 row(s)

affected)

|||Ok, after changing the option I can see the CR in the results. Thanks for showing me something I didn't know.

However, I'm still having problems with the CR in the email text. Here's another snippit from the stored procedure:

Declare @.Message varchar(200)
Declare @.MessageList varChar(max)

SET @.MessageList = 'Daily Summary Report' + @.CR
SET @.MessageList = @.MessageList+'New Items'+ @.CR
SET @.MessageList = @.MessageList+'--'+ @.CR
DECLARE c2 CURSOR FOR
select MSG from Email.Messages
where Event_ID = @.EventID
OPEN c2
FETCH NEXT FROM c2
INTO @.Message
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.MessageList = @.MessageList + @.Message + @.CR
FETCH NEXT FROM c2
INTO @.Message
END
CLOSE c2
DEALLOCATE c2

This builds the body text of the email in the variable @.MessageList. It always puts a CR at the end of the line for the SET @.MessageList lines that are outside of the loop.

Inside the loop, if the message type has messages like "Added UPC 123456123456" it works fine with a CR at the end of every line.

If the message type has messages like "FAILED Client1 20050122 Source TYPE 2: 12 COLUMNS SEMI COLON DELIMITED production" it does not put a CR at the end of each line. If I put CR twice at the end of the line I do get two CR's in the email.

I'm stumped. At this point I have put in an if statement to put 2 CR's at the end of the event types that are not gettting the single CR and that seems to work, but I'm one of those types that like to understand why things work or don't work.

Any more thoughts? And thanks for your help.

John|||

Hmmm, I tried deleting a post I just made in this thread, because it seemed no longer relevant after John had made a simultaneous post, and it appears to have deleted John's new post, too.

I think that is a bug in the FORUM software, since I chose only my post when I selected "Delete".

Sorry!

Dan

problem with datepart ....

hello
i have a table with date entries of type string ...tries of type varchar li
ke
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')Try:
print DATEPART(dw,convert (datetime, '14.02.2006', 104))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
hello
i have a table with date entries of type string ...tries of type varchar
like
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')|||try this
SET DATEFORMAT DMY
SELECT DATEPART(dw,'14.02.2006')
http://sqlservercode.blogspot.com/|||Xavier,
I suggest you look up (& make use of) the
SET DATEFORMAT
statement in Books Online.
Robert
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> .....
> 01.02.2006
> .....
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to
> add
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>|||Try using CONVERT with the proper format.
print DATEPART(dw,convert(datetime, '14.02.2006', 104))|||thanks
"Tom Moreau" wrote:

> Try:
> print DATEPART(dw,convert (datetime, '14.02.2006', 104))
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> ......
> 01.02.2006
> ......
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to ad
d
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>
>|||thanks

Problem with date format

Hi!

I have a string with date parameters. Those date contains Hours/Minute/Second and i juste want the date without the time. So i have tried this:

=Format(Parameters!StartDate.Value, "d") & " to" & Format(Parameters!EndDate.Value, "d")

But this return me:

d to d

What i want is something like this 2000-01-01 to 2006-01-01

How can i do this ?

Thanks
What are the data types for parameters StartDate and EndDate? Can you make sure they are of datetime type, not string?|||

Thank for your answer. You're right. Those dates come from parameters so they were in string... i've put a CDATE before the FORMAT and now it's working fine.

Thanks !