Problem in exporting Report with multiline textbox to Excel

Hi,

I am facing a problem while exporting reports to Excel.
My report contains a multilined textbox - which is displayed correctly in Report Design.

Following is code of report Statistics contents to be displayed in a textbox: ="Report Statistics " & vbcrlf & "Date : " & Format(Globals!ExecutionTime, "MMM dd yyyy tt”) & vbcrlf & "By : " & User!UserID

Problem Description: In Excel - one extra line is added between each line

Sample output of report exported in Ms Excel 2003:

Report Statistics

Date : Jun 12 2006

Time : 6:53:23 PM

By : COMPANY\EMP ID

Correct Output Required:

Report Statistics

Date : Jun 12 2006

Time : 6:53:23 PM

By : SERWIZSOL\P97006

Configuration:
Microsoft SQL Server 2000 SP3
Microsoft SQL Reporting Services 2000 SP2

If some one is facing similar problem or knows the solution, please let me know.

Thanks in Advance.
Regards,
Abhijeet

Hi Abhijeet,

I tried your expression ="Report Statistics " & vbcrlf & "Date : " & Format(Globals!ExecutionTime,"MMM dd yyyy tt") & vbcrlf & "By : " & User!UserID

Everything worked fine for me. I added a text box and set the expression to above text and it works perfectly. Was there anything else you were doing?

Problem correctly setting a local_variable

Hi,

I've been trying to write a stored proc to no avail. I'm trying to set a local variable and every time I try to execute the stored proc I get the following error:

Running [dbo].[StoredProcedure2].

Conversion failed when converting the nvarchar value 'TEST2' to data type int.

No rows affected.

(0 row(s) returned)

@.RETURN_VALUE =

Finished running [dbo].[StoredProcedure2].

Here's the code (which I skimmed down to try and pinpoint where the issue was).

Initially, I was trying to assign a numeric string value stored as nvarchar(10) and anytime the value would be over '2147999999' (in that area) it would bomb... Now even this simple code won't work and I can't understand why. If anyone would be kind enough to enlighten me ;-)

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

RETURN @.ME

u must use Print/Select instead of Return.

fromBOL

RETURN

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

Syntax

RETURN [ integer_expression ]

alter PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

select @.me

Madhu

|||

The value following the RETURN keyword MUST be an integer value.

Strings are not allowable RETURN values.

Instead use:

Code Snippet

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

SELECT @.Me

GO

|||

It always better to use OUTPUT parameter, or Select statement to get the single row & column data from the SP. Return only supports integer.

NOTE:

PRINT – you can’t get the result on the UI. It will get suppressed.

Problem converting C application from using DBLIB bcp to using ODBC bcp.

I have an application that was converted from using DBLIB bcp to using ODBC bcp. All other field/data types bind are updated correctly, but dates put in the date

1753-01-01 00:00:00.000 when it should be NULL. The variable is a char [24] and the first item is set to \0 ( dbLoanBankruptcy.MotionforReliefReqDate[0] = NullChar; ).

Is there any example or way to put the date in as null when the variable is null and an actual date as needed?

bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefReqDate, 0, 23, NULL, 0, SQLCHARACTER, 13)

Thank you,

Joel

Following site maybe helpful:

http://msdn2.microsoft.com/en-us/library/aa198011(sql.80).aspx

|||The link above only discusses dates enough to get an actual date into a table, but does not discuss how to get a NULL date stored. Is there a way to save <NULL>?

Would it be wrong to use the bcp_bind statement you needed for each row/record and re-bind each time? As in...

if(dbLoanBankruptcy.MotionforReliefFiledDate[0]==NullChar) {
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, SQL_NULL_DATA, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}else{
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, 23, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}

|||

You don't need to rebind each time

http://msdn2.microsoft.com/en-gb/library/aa177853(SQL.80).aspx

struct

{

int iIndicator;

char szDatetime[30];

} urBCPData;

if (bcp_bind(om_hDbc2, (LPCBYTE) &urBCPData, 4, SQL_VARLEN_DATA , NULL, 0,SQLVARCHAR, 1) == FAIL)

{

// Raise error and return.

return;

}

// Insert NULL

urBCPData.iIndicator = SQL_NULL_DATA;

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

return;

}

// Insert 1753-01-01 00:00:00.000

urBCPData.iIndicator = 23;

strcpy(urBCPData.szDatetime, "1753-01-01 00:00:00.000");

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

ShowSQLError(om_hDbc2, om_hStmt2);

return;

}

|||Tested and works... thanks.

The normal bind statement but the data portion is now (byte *)&data.datetime_struct

Code Snippet

if(dbLoanBankruptcy.MotionforReliefReqDate[0]==NullChar) {
dbLoanBankruptcy.bounddate.iIndicator = SQL_NULL_DATA;
}else{
dbLoanBankruptcy.bounddate.iIndicator = 24;
strcpy(dbLoanBankruptcy.bounddate.iValue, dbLoanBankruptcy.MotionforReliefReqDate);
}