Exception Handling

Hello,

I am trying to catch an error, but not able to. I am storing the value of @.@.Error soon after the statement which I believe would generate error. In order to produce the error I have deliberately used a wrong table name. So, the statement breaks but never comes to my error handling code snippet. Instead, it just throws the SQL server error message and quits. What am I doing wrong here? Here is the code snippet. The actual name of the temporary table is #TEMPO_TABLE, but in order to generate the error I have used #TEMPO_. Since this would surely error out, it should go to the label ROLLITBACK. But is not going to that label. Neither is it printing the error number as per the PRINT statement there. It just throws the SQL error and quits the execution. (Or should it NOT?). The Error that it throws it this:

Server: Msg 208, Level 16, State 1, Procedure FEED_PULL_XX, Line 157
Invalid object name '#TEMPO_'.

Can you please let me know whats going wrong here? Thanks a lot in advance.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0
Begin
PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'
GOTO ROLLITBACK
End

Hi Mannubhai,

Which version of SQL server you are using.. if you are using SQL05, try using Try..catch block.

If you are using SQL2k.. then in above script there is no variable declared and there is no rollitback lable..

And YOU SHOULD GET THE ERROR SAYING THAT THE TABLE DOES NOT EXISTS.. AND THIS ERROR HAS THE SEVERITY LEVEL OF 16.. which says that the error can be fixed by the user and terminates the batch.. so you are not able to catch it.

Have a look on the severity levels for more details.

Regards,

|||

There are errors that cannot be caught.

Erland has written up some good articles on the subject. Please take a look.

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

|||

Hey Thanks Sajid. I am using SQL 2000. The Code that I have given is just a snippet. It is not the complete one. The Complete SP has the variables declared and yes, the ROLLITBACK label is also present. The error as I said is, INVALID OBJECT #TEMPO_. But can this error NOT be caught in the way I have attempted? I think it does not even execute this statement --> SET @.ErrNo = @.@.Error. Why does this happen? Please let me know. Appreciate the response.

Mannu.

|||

You can refer to Erland's documents on Error handline.. spcially the first link provided by OJ. .that will surely help you out to understand the Error handling in details..

Regards,

|||

Hi All,

I seem to have solved this. The below insert which I was using, I put that into a variable and executed it using sp_executesql. A Dynamic SQL.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

I put this like this:

SET @.SQL = '

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_'

EXEC sp_Executesql @.SQL

SET @.ErrNo = @.@.Error.

IF ErrNo <> 0

....

....

This gave the proper results. Let me know if more information is required.

Mannu.

|||

I partially agree with your solution.

But there is few risks you might face if you given the access permissions on SP level (readonly at Table)..

The alternate possible solution might be,

Option 1#:

Code Snippet

If Exists (Select * from tempdb..sysobjects where id=object_id('tempdb..#tempo_'))

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Else

Goto RollITBack

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

Option 2#:

Wrapp the insert statement in new proc & call it from your main proc.

Code Snippet

Create Proc InsertFromTemp

as

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Go

Exec InsertFromTemp

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

excel vba to generate flat file definition

What object do I reference to use SSIS from Excel. I want to generate a flat file definition based on Excel. I have a lot of fields to import and I don't feel like creating them as flat file columns. I have a few tables and I get the source file format from the vendor in an Excel format. What I would like to do is generate a flat file connection in an empty package using VBA.

I don't think this is possible. VBA is a COM based environment whereas the SSIS API is dotnet.

I stand to be corrected. If there is a way of calling dotnet APIs from VBA then I don't know about it.

-Jamie

|||As Jamie notes, our documentation is aimed at using the SSIS API through managed code. Using the native API is unsupported. You might be able to work backwards from the managed examples, though, if you're feeling adventurous. I'd suggest you start here: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

Excel Report - Large File

SQL Server 2005 reporting service is being used to generate a large excel
report with about 30,000 rows. The size of file generated is ~15MB. but if
the same file is opened in Excel 2003 and saved back in XLS format itself,
the size of the file reduces to approximately 50% of the original size.
One of the initial guesses why this is occuring is that SQL Server 2005
reporting service generates unicode strings for the report data elements,
while excel saving it back, saves it as ANSI. (the report is in english only).
Let me know if the assumption is correct. If so what are means of fixing
this issue or else, is there any other option.On Oct 30, 12:46 am, Madhan Raj J
<MadhanR...@.discussions.microsoft.com> wrote:
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.
This is just a thought, but it might be related to the Excel export
driver that is used in SSRS: to your point, that it could be adding
extra items. One way to check this out is to probably run a comparison
between the 2 files (maybe using something like a trial version of
Araxis Merge >> http://www.araxis.com/merge/ ). Also, I'm not sure if
you want to go this far, but if the size is a major issue you can
either zip the excel file programmatically with a third party DLL (a
few open source ones available) -or- create a small ASP.NET
application (or console EXE) that basically reads in the Excel file
(via streamreader) and does a string replace on all occurrences of the
undesired format in question and stream it back out (via
streamwriter). Of course, I'm not 100% sure that this will work with
Excel; however, it's worth a shot. Also, I think that there is a
technology called Automation in .NET that might be able to help w/
this. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You got me curious so I did a little investiaging. I don't know if Excel
export is doing as you say. I do know that CSV export defaults to unicode.
You can change CSV to export in ASCII by making the following change in
rsreportserver.config (note commenting out the original line):
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
You could see if exporting to CSV ASCII format makes for a smaller file.
This will open up appropriately in Excel 2003 (Unicode shoves each row into
a single cell).
I decided to try the same thing with the Excel entry but I did not see any
difference.
If you make the CSV change go to the RS configuration tool click on Server
Status and stop and start Report Server. It only takes a few seconds to do
this. It will cause the configuration file changes to be processed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english
> only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.|||Thanks of rthe reply..
But my client needs to the report to be exported to excel only, as there is
significant formatting around the data. As you have observed, the device
setting for excel does not have the encoding options as in CSV.
"Bruce L-C [MVP]" wrote:
> You got me curious so I did a little investiaging. I don't know if Excel
> export is doing as you say. I do know that CSV export defaults to unicode.
> You can change CSV to export in ASCII by making the following change in
> rsreportserver.config (note commenting out the original line):
> <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> You could see if exporting to CSV ASCII format makes for a smaller file.
> This will open up appropriately in Excel 2003 (Unicode shoves each row into
> a single cell).
> I decided to try the same thing with the Excel entry but I did not see any
> difference.
> If you make the CSV change go to the RS configuration tool click on Server
> Status and stop and start Report Server. It only takes a few seconds to do
> this. It will cause the configuration file changes to be processed.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> > SQL Server 2005 reporting service is being used to generate a large excel
> > report with about 30,000 rows. The size of file generated is ~15MB. but if
> > the same file is opened in Excel 2003 and saved back in XLS format itself,
> > the size of the file reduces to approximately 50% of the original size.
> >
> > One of the initial guesses why this is occuring is that SQL Server 2005
> > reporting service generates unicode strings for the report data elements,
> > while excel saving it back, saves it as ANSI. (the report is in english
> > only).
> >
> > Let me know if the assumption is correct. If so what are means of fixing
> > this issue or else, is there any other option.
>
>|||Perhaps it is the formatting making the difference but I am not seeing any
difference in size when I save from Excel (versus the Excel created by RS).
One other issue, are you on SP2?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:A5146FAB-7087-45C7-A00E-9CA8A734AB7A@.microsoft.com...
> Thanks of rthe reply..
> But my client needs to the report to be exported to excel only, as there
> is
> significant formatting around the data. As you have observed, the device
> setting for excel does not have the encoding options as in CSV.
> "Bruce L-C [MVP]" wrote:
>> You got me curious so I did a little investiaging. I don't know if Excel
>> export is doing as you say. I do know that CSV export defaults to
>> unicode.
>> You can change CSV to export in ASCII by making the following change in
>> rsreportserver.config (note commenting out the original line):
>> <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> You could see if exporting to CSV ASCII format makes for a smaller file.
>> This will open up appropriately in Excel 2003 (Unicode shoves each row
>> into
>> a single cell).
>> I decided to try the same thing with the Excel entry but I did not see
>> any
>> difference.
>> If you make the CSV change go to the RS configuration tool click on
>> Server
>> Status and stop and start Report Server. It only takes a few seconds to
>> do
>> this. It will cause the configuration file changes to be processed.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
>> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
>> > SQL Server 2005 reporting service is being used to generate a large
>> > excel
>> > report with about 30,000 rows. The size of file generated is ~15MB. but
>> > if
>> > the same file is opened in Excel 2003 and saved back in XLS format
>> > itself,
>> > the size of the file reduces to approximately 50% of the original size.
>> >
>> > One of the initial guesses why this is occuring is that SQL Server 2005
>> > reporting service generates unicode strings for the report data
>> > elements,
>> > while excel saving it back, saves it as ANSI. (the report is in english
>> > only).
>> >
>> > Let me know if the assumption is correct. If so what are means of
>> > fixing
>> > this issue or else, is there any other option.
>>|||I have the same issue. When save directly from Reporting Services, the file
is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
out Unicode. When I opened the excel file in notepad, I noticed there's lots
of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
them. I am still investigating, please let me know if you find a solution.
There's not much formatting in the report, just the column headings, so I
can't figure out what the excess binary data is.
Thanks.
"Madhan Raj J" wrote:
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.|||I spoke too soon - it could be Unicode issue after all. In the large file,
the characters take two spaces, eg. "n e w " vs. "new" (in the smaller file).
"Pranil" wrote:
> I have the same issue. When save directly from Reporting Services, the file
> is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
> out Unicode. When I opened the excel file in notepad, I noticed there's lots
> of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
> them. I am still investigating, please let me know if you find a solution.
> There's not much formatting in the report, just the column headings, so I
> can't figure out what the excess binary data is.
> Thanks.
> "Madhan Raj J" wrote:
> > SQL Server 2005 reporting service is being used to generate a large excel
> > report with about 30,000 rows. The size of file generated is ~15MB. but if
> > the same file is opened in Excel 2003 and saved back in XLS format itself,
> > the size of the file reduces to approximately 50% of the original size.
> >
> > One of the initial guesses why this is occuring is that SQL Server 2005
> > reporting service generates unicode strings for the report data elements,
> > while excel saving it back, saves it as ANSI. (the report is in english only).
> >
> > Let me know if the assumption is correct. If so what are means of fixing
> > this issue or else, is there any other option.

Excel renderer in RS2005-Sheet names

The Reporting group in our company uses SRS 2005 to generate most reports for
the Company. Many of these reports are distributed automatically to our
clients. An increasing number of these reports are distributed as Excel
spreadsheets. These Excel reports generally contain multiple worksheets.
For customer convenience, these worksheets should be named. The portion of
Report Services that writes the Excel files, is apparently not capable of
naming these worksheets with user specified names when there are multiple
worksheets. However when only one worksheet is present, it is named with the
title of the report. Is there a way to name the worksheets dynamically as the
report is generated? If not, is it possible to write a custom renderer to do
so, or to extend the excel renderer? I appreciate any code examples.
Thanks in advance.You might want to look into Software Artisan's Excel writer for Reporting
Services. It gives you more control over the Excel rendering.
http://officewriter.softartisans.com/officewriter-250.aspx
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Saritha" <Saritha@.discussions.microsoft.com> wrote in message
news:1BD099C8-6EA3-4D9B-999D-5574F9A151E6@.microsoft.com...
> The Reporting group in our company uses SRS 2005 to generate most reports
> for
> the Company. Many of these reports are distributed automatically to our
> clients. An increasing number of these reports are distributed as Excel
> spreadsheets. These Excel reports generally contain multiple worksheets.
> For customer convenience, these worksheets should be named. The portion of
> Report Services that writes the Excel files, is apparently not capable of
> naming these worksheets with user specified names when there are multiple
> worksheets. However when only one worksheet is present, it is named with
> the
> title of the report. Is there a way to name the worksheets dynamically as
> the
> report is generated? If not, is it possible to write a custom renderer to
> do
> so, or to extend the excel renderer? I appreciate any code examples.
> Thanks in advance.
>