Problem with procedure

Hi,

I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,

even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."

code:

CREATE PROCEDURE x1 (@.y1 AS numeric=NULL )AS
declare @.z1 Varchar(200)

begin
set @.z1= 'and a1.id='
print @.y1
print @.z1
end

Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....

I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

Thanks in Advance!meendar (askjavaprogrammers@.gmail.com) writes:

Quote:

Originally Posted by

I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,
>
even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."
>
>...
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....
>
>
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

SQL Server tries to convert all values in employee.z2 to numeric, and
when this fails for some value, the query fails.

You need to use the CASE expression:

WHERE CASE WHEN ltrim(rtrim(z2)) NOT LIKE '%[^0-9]%'
THEN convert(numeric, z2)
END = @.z1 + 45

Now it will only attempt to convert z2 which it consists of digits only.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 2, 12:10 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

meendar (askjavaprogramm...@.gmail.com) writes:

Quote:

Originally Posted by

I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,

>

Quote:

Originally Posted by

even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."

>

Quote:

Originally Posted by

...
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....

>

Quote:

Originally Posted by

I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

>
SQL Server tries to convert all values in employee.z2 to numeric, and
when this fails for some value, the query fails.
>
You need to use the CASE expression:
>
WHERE CASE WHEN ltrim(rtrim(z2)) NOT LIKE '%[^0-9]%'
THEN convert(numeric, z2)
END = @.z1 + 45
>
Now it will only attempt to convert z2 which it consists of digits only.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

Thanks to All

problem with procedure

Hi,
I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,
even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."
code:
CREATE PROCEDURE x1 (@.y1 AS numeric=NULL )AS
declare @.z1 Varchar(200)
begin
set @.z1= 'and a1.id='
print @.y1
print @.z1
end
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.
Thanks in Advance!You cannot execute the statement dynamically in this fashion.
Rather, try this...
-- For storing Unicode SQL statements to be executed on the fly.
DECLARE @.sql_statement_string nvarchar(1024)
-- Construct SQL statement to select
SET @.sql_statement_string =3D 'select z1 from employee a1 where z2=3D'
+ @.z1
+ ' + 45 ...'
-- Execute the SQL & insert activity details.
EXECUTE sp_executesql @.sql_statement_string
--Seenu
On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
> Hi,
> I have selected a field name and declared it as varchar, since it is
> varchar in table and performed some numeric operation with numbers,
> even after i cast the sql in below code, it throws an exception as
> "Error converting data type varchar to numeric."
> code:
> CREATEPROCEDUREx1 (@.y1 AS numeric=3DNULL )AS
> declare @.z1 Varchar(200)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 begin
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 set @.z1=3D 'and a1.id=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.y1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 end
> Declare r1 cursor
> local Scroll Keyset Optimistic
> For
> select z1 =C2=A0from =C2=A0employee a1 where =C2=A0z2=3D @.z1 + 45 ....
> I want to clear that how can we cast the field with varchar for
> numeric operations, i have also tried cast and convert to change it
> but all in vain.
> Thanks in Advance!|||On May 2, 7:42=C2=A0pm, =E0=AE=9A=E0=AF=80=E0=AE=A9=E0=AF=81 <srinivasan...=
@.gmail.com> wrote:
> You cannot execute the statement dynamically in this fashion.
> Rather, try this...
> =C2=A0 -- For storing Unicode SQL statements to be executed on the fly.
> =C2=A0 DECLARE @.sql_statement_string nvarchar(1024)
> =C2=A0 =C2=A0 -- Construct SQL statement to select
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 SET @.sql_statement_string =3D 'select z1 =C2=
=A0from =C2=A0employee a1 where =C2=A0z2=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0+ @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0+ ' + 45 ...'
> =C2=A0 =C2=A0 -- Execute the SQL & insert activity details.
> =C2=A0 =C2=A0 EXECUTE sp_executesql @.sql_statement_string
> --Seenu
> On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Thanks to All

problem with procedure

Hi,
I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,
even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."
code:
CREATE PROCEDURE x1 (@.y1 AS numeric=NULL )AS
declare @.z1 Varchar(200)
begin
set @.z1= 'and a1.id='
print @.y1
print @.z1
end
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.
Thanks in Advance!
You cannot execute the statement dynamically in this fashion.
Rather, try this...
-- For storing Unicode SQL statements to be executed on the fly.
DECLARE @.sql_statement_string nvarchar(1024)
-- Construct SQL statement to select
SET @.sql_statement_string = 'select z1 from employee a1 where z2='
+ @.z1
+ ' + 45 ...'
-- Execute the SQL & insert activity details.
EXECUTE sp_executesql @.sql_statement_string
--Seenu
On May 2, 1:04Xam, meendar <askjavaprogramm...@.gmail.com> wrote:
> Hi,
> I have selected a field name and declared it as varchar, since it is
> varchar in table and performed some numeric operation with numbers,
> even after i cast the sql in below code, it throws an exception as
> "Error converting data type varchar to numeric."
> code:
> CREATEPROCEDUREx1 (@.y1 AS numeric=NULL )AS
> declare @.z1 Varchar(200)
> X X X X begin
> X X X X set @.z1= 'and a1.id='
> X X X X print @.y1
> X X X X print @.z1
> X X X X end
> Declare r1 cursor
> local Scroll Keyset Optimistic
> For
> select z1 Xfrom Xemployee a1 where Xz2= @.z1 + 45 ....
> I want to clear that how can we cast the field with varchar for
> numeric operations, i have also tried cast and convert to change it
> but all in vain.
> Thanks in Advance!
|||On May 2, 7:42Xpm, ???? <srinivasan...@.gmail.com> wrote:
> You cannot execute the statement dynamically in this fashion.
> Rather, try this...
> X -- For storing Unicode SQL statements to be executed on the fly.
> X DECLARE @.sql_statement_string nvarchar(1024)
> X X -- Construct SQL statement to select
> X X X X SET @.sql_statement_string = 'select z1 Xfrom Xemployee a1 where Xz2='
> X X X X X X X X X X X X X+ @.z1
> X X X X X X X X X X X X X+ ' + 45 ...'
> X X -- Execute the SQL & insert activity details.
> X X EXECUTE sp_executesql @.sql_statement_string
> --Seenu
> On May 2, 1:04Xam, meendar <askjavaprogramm...@.gmail.com> wrote:
>
>
>
>
>
>
> - Show quoted text -
Thanks to All

Problem with printing report directly to printer

Hello,

I am trying to get a SSRS 2005 report to print from my Visual Studio 2005 C++ application without using the ReportViewer to preview it first. What I have done is created a dll that I call into when I want to access a certain report and print it. While searching around on the internet I found an MSDN article about printing a report without previewing and it had an example in C# code. So I used that as a guide for my C++ code but I am still having problems with rendering the report properly so it can be printed. When I try to render a report using the "Image" format, my streamid string is empty but the byte array that the render routine returns is not. Here is the code I am using, what could be the problem here?

Note: m_Streams is define elsewhere as

array<String^>^ m_Streams = gcnew array<String^>(10);

void Print::Export(LocalReport^ report)

{

array<Warning^>^ Warn = gcnew array<Warning^>(10);

String^ deviceinfo =

"<DeviceInfo>" +

" <OutputFormat>EMF</OutputFormat>" +

" <PageWidth>8.5in</PageWidth>" +

" <PageHeight>11in</PageHeight>" +

" <MarginTop>0.25in</MarginTop>" +

" <MarginLeft>0.25in</MarginLeft>" +

" <MarginRight>0.25in</MarginRight>" +

" <MarginBottom>0.25in</MarginBottom>" +

"</DeviceInfo>";

String^ mimeType;

String^ enc;

String^ FileExt;

array<Byte>^ bytes;

bytes = report->Render("Image",deviceinfo, mimeType, enc, FileExt, m_Streams,Warn); // m_Streams has a length of

return; // 0 after the Render

}

void Print:: PrintPage(System:: Object^ sender, System:: Drawing:: Printing:: PrintPageEventArgs^ ev)

{

Metafile^ pageImage = gcnew Metafile(m_Streams[m_CurrentPage]);

ev->Graphics->DrawImage(pageImage, ev->PageBounds);

m_CurrentPage++;

ev->HasMorePages = (m_CurrentPage < m_Streams->Length);

return;

}

void Print:rintRpt()

{

String^ printerName = "Default";

if (m_Streams->Length < 0)

return;

PrintDocument^ printDoc = gcnew PrintDocument();

if (!printDoc->PrinterSettings->IsValid) {

return;

}

printDoc->PrintPage += gcnew PrintPageEventHandler(this, &Print:: PrintPage);

printDoc->Print();

return;

}

void Print::Run()

{

LocalReport^ report = gcnew LocalReport();

DataSet^ ds = gcnew DataSet();

LoadData(ds);

report->ReportPath = "c:\\bmi\\bulrpt\\Report1.rdlc";

ReportDataSource^ RDS = gcnew ReportDataSource();

RDS->Name = "DataSet1_Subject";

RDS->Value = ds->Tables["Subject"];

report->DataSources->Add(RDS);

Export(report);

PrintRpt();

return;

}

DataTable^ Print:: LoadData(DataSet^ ds)

{

System:: String ^ConnStr = "SELECT * FROM Subject";

SqlConnection^ conn = gcnew SqlConnection("Data Source=JOE-PC\\BMIMSDESERVER; Initial Catalog=stx52013;Integrated Security = TRUE");

SqlCommand^ command = gcnew SqlCommand(ConnStr, conn);

SqlDataAdapter^ adapt = gcnew SqlDataAdapter(command);

adapt->TableMappings->Add("Table", "Subject");

conn->Open();

adapt->Fill(ds);

return ds->Tables["Subject"];

}

I had the same problem with that render method and simply looped until the byte lenth was zero and that worked. There is another example out there that used the second overload of the method that works pretty much as is. In that one you pass a call back event handler.

|||

I've tried to do

bytes = report->Render("Image",deviceinfo, &Print::CreateStream,Warn);

as well but when I compile that I get the error -

error C3374: can't take address of 'Print::CreateStream' unless creating delegate instance

What does this mean?

Problem with Printing of Crystal Report

Hello

I have an application which uses crystal reports.
Only the runtime dll's are shipped with the application in order to design and also print.

I'm unable to print the report .I guess some mandatory runtime dll's are missed
to ship with the application.

Can anyone help me out in listing all the runtime dll's used for crystal report printing? I use Crystal report 9.0 version dll's.

If I install crystal reportDesigner, then the print option works.

Regards
PallCrystal Reports 8.5 comes with a file called runtime.hlp that lists all the runtime files needed and (I believe) their dependencies. Maybe version 9 has something similar?

I use InstallShield to create my Setups. There's a download for InstallShield that will add all the MergeModules for Crystal Reports. (A MergeModule is a collection of system files and their dependecies.) You just check off the ones you need and it takes care of installing all the dependency files.|||Open your crystal reports in EDIT mode and open the printer / printing options. Uncheck the NoPrint option.

This problem is with CR 8.0.

Problem with printing database diagrams

When I print a database diagram directly, I get the top left corner obsrured on the printout by what looks like an empty popup box. This means the columns names are blanked out for the first 5-6 rows. I have tried this with a variety of printers and the effectis the same on all. The problem appears to be specific to SQL2005 as one of the servers has both SQL2000 and SQL2005. The disagrams from the SQL2000 print perfectly, the ones from SQL2005 do not.

SQL2005 has been patched to SP2.

I have circumvented the problem by writing an SSRS package to print the output of a stored procedure that emulates the database diagram. Also there is an option in SQL2005 database diagrams to get an image of the diagram. I have printed this in Word and it prints flawlessly that way.

I believe this is an unique problem you are facing and I think nothing much done from SQL dev. team.

See thsi http://www.codeproject.com/dotnet/ScriptDiagram2005.asp is any help and you can report the same to MS Connect website http://connect.microsoft.com/

|||Regrettably I still have the problem with diagram on SQL2005 (but SQL2000)
|||

This is not a unique problem.

I've been encountering it for a month or two.

It's intermittent. Sometimes the diagrams are okay, sometimes these boxes obscure fields.

My only work-around is to PRINT SCREEN and then copy it into Word and print it.

|||We are having this same problem. If you copy to Word it will print. Has there been a solution found yet?|||Copying to Word is a good work-around, but is not a solution. It did happen with all the printers I could try at that site.

Problem with printing bank bills

Hi everybody,

I have done a Crystal Report document with the heigth of a bank bill, with the option of printing in a continuous paper in a matrix printer.

Then I create a PDF document, but when I print them, at the end of each page of the PDF document, i have a blank. And this blank is what moves the next bills to another position and here it is the problem. I don't know why is this blank.

I have search in internet but i don't know what to do. I have done all in c# language.

Is there anyone who has printed bank bills with c#? Any ideas?

Thanks.Try Suppressing footers if data is not there to display.