Having a MAJOR brain fart here...

Guys I'm sorry to be asking such a routine question...

I'm having trouble figuring out how to make this function dynamic enough to handle multiple insert statements.

1public int Add()23{45string SQL;67SQL ="INSERT INTO [BuildingInterior] (PropertyID, CeilingHeight, " +89"LoadingDocks, PassengerElevators, FreightElevators, PassengerEscalators, " +1011"FireSprinklersID, SecurityCameras, SmokeDetection, FireAlarms, " +1213"GasDetection, SecureAccess, HeatTypeID, AirConditioningID, " +1415"AirExchange, InternetAccessID, InteriorDescription) " +1617"VALUES ( @.PropertyID, @.CeilingHeight, " +1819"@.LoadingDocks, @.PassengerElevators, @.FreightElevators, @.PassengerEscalators, " +2021"@.FireSprinklersID, @.SecurityCameras, @.SmokeDetection, @.FireAlarms, " +2223"@.GasDetection, @.SecureAccess, @.HeatTypeID, @.AirConditioningID, " +2425"@.AirExchange, @.InternetAccessID, @.InteriorDescription)";2627PropertyDB myConnection =new PropertyDB();2829SqlConnection conn = myConnection.GetOpenConnection();3031SqlCommand cmd =new SqlCommand(SQL, conn);3233cmd.Parameters.Add("@.PropertyID", SqlDbType.Int).Value = PropertyID;3435cmd.Parameters.Add("@.CeilingHeight", SqlDbType.NVarChar, 50).Value = CeilingHeight;3637cmd.Parameters.Add("@.LoadingDocks", SqlDbType.NVarChar, 50).Value = LoadingDocks;3839cmd.Parameters.Add("@.PassengerElevators", SqlDbType.NVarChar, 50).Value = PassengerElevators;4041cmd.Parameters.Add("@.FreightElevators", SqlDbType.NVarChar, 50).Value = FreightElevators;4243cmd.Parameters.Add("@.PassengerEscalators", SqlDbType.NVarChar, 50).Value = PassengerEscalators;4445cmd.Parameters.Add("@.FireSprinklersID", SqlDbType.Int).Value = FireSprinklersID;4647cmd.Parameters.Add("@.SecurityCameras", SqlDbType.NVarChar, 50).Value = SecurityCameras;4849cmd.Parameters.Add("@.SecurityAlarms", SqlDbType.NVarChar, 50).Value = SecurityAlarms;5051cmd.Parameters.Add("@.SmokeDetection", SqlDbType.NVarChar, 50).Value = SmokeDetection;5253cmd.Parameters.Add("@.FireAlarms", SqlDbType.NVarChar, 50).Value = FireAlarms;5455cmd.Parameters.Add("@.GasDetection", SqlDbType.NVarChar, 50).Value = GasDetection;5657cmd.Parameters.Add("@.SecureAccess", SqlDbType.NVarChar, 50).Value = SecureAccess;5859cmd.Parameters.Add("@.HeatTypeID", SqlDbType.Int).Value = HeatTypeID;6061cmd.Parameters.Add("@.AirConditioningID", SqlDbType.Int).Value = AirConditioningID;6263cmd.Parameters.Add("@.AirExchange", SqlDbType.NVarChar, 50).Value = AirExchange;6465cmd.Parameters.Add("@.InternetAccessID", SqlDbType.Int).Value = InternetAccessID;6667cmd.Parameters.Add("@.InteriorDescription", SqlDbType.NVarChar, 50).Value = InteriorDescription;6869cmd.ExecuteNonQuery();7071cmd.CommandText ="SELECT @.@.IDENTITY";7273this.BuildingInteriorID = Int32.Parse(cmd.ExecuteScalar().ToString());7475conn.Close();7677return this.BuildingInteriorID;7879}80

Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array?

Any comments are greatly welcomed.

Hi eterry,

As far as I can see there is no better way to assign value by iterating through each column like this.

But I think there is one thing that you can improve in your code. You can put SELECT SCOPE_IDENTITY() (use SCOPE_IDENTITY() instead of @.@.IDENTITY in SQL Server)at the end of the INSERT statement. Seperate them with a ";", like

INSERT INTO ......; SELECT SCOPE_IDENTITY()

Then you can use ExecuteScalar to have the 2 statement executed at once. This will have 2 advantages.

1. Save a roundtrip to the server and gain better performance.
2. Prevent the concurrency issues. In your code, if 2 users do this together, there is possibility that they will get wrong identity if one's execution is interrupted by the other.

HTH. If this does not answer you question, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||

Thanks for the tip Kevin.

I was hoping that there would be a better way of doing, but it is what it is.

Handling SQL Exception

I'm unsure how to handle an SQL Exception correctly when the database is unavailable/offline.

I have my aspx file with the C# code-behind, but all of the SQL stuff is done in a separate code file in the App_Code directory.
E.g.

CODE-BEHIND
DatabaseModifier.deleteUser(username);

DATABASEMODIFIER.cs
public static void deleteUser(string username)
{
SqlConnection conn = SqlLogin.SqlConnect;
SqlCommand command = new SqlCommand("DELETE FROM <table> WHERE Username = '" + username + "'", conn);
conn.Open()
command.ExecuteNonQuery();
conn.Close()
}

Now, that code works perfectly, however if the database I'm connecting to is offline, an SQLException is thrown and because the SQL is handled in my DatabaseModifier class, I'm not sure how to handle it correctly.
If I use a Try/Catch block in my code-behind, it doesn't get thrown because the error occurs in my DatabaseModifier class. If I use a Try/Catch block in my DatabaseModifier class, what can I put in the catch block that will inform the user of the database being offline and/or how can I perform a url redirection?

Any help is greatly appreciated.

You can write the connection open code in the database modifier class with try-catch block, and in the catch block you can just throw the exception being written. Again the the page code behind you can write the database activity in a try-catch block, and here in this catch you'll receive any exception which may be generated in the database modifier class. As the code in the database modifier class does nothing but to throw the exception, you can handle them in the page code.

Hope this will help.

|||

So in the DatabaseModifier class:
Try
{
code
}
Catch(SqlException)
{
//nothing in here
}

In code behind:
Try
{
code
}
Catch(SqlException e)
{
//output e.Message to user?
}

|||

Something like that:

In database modifier:Try{ code}Catch(SqlException exp){throw exp;}In code behind:Try{ code}Catch(SqlException e){//output e.Message to user?}

Now, you can see that the db modifier just throws any exceptions which it receives during any operation (this is fair also because you don't have any user out put system set in the db modifier class), and the page code is set up to handle the exceptions thrown by the db modifier as well as any other exceptions during the code execution of its own.

Hope this will help

handling special characters using oledb to oracle

hi,

i am using oledb to connect to oracle.
i want to know if there is a way to handle different character sets in this type of connection. for sql to sql, i have been using auto translate in the connection string.
what about for sql oledb to oracle? how can i make sure that the data from sql to oracle is transferred as is?

many thanks.

You might be better off asking this on the Data Access forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

-Jamie

Handling Schema Changes

What is the best way to handle schema changes as database needs change.
What I need to find out is
what is the best way to write database interface code in order to be
flexible to schema changes. Would having stored procedures as an interface
mechanism be best so that old app software will still be compatible? It is
impractical for us to upgrade all apps simultaneously, so if a new column is
added to a table, old apps will not fill in this column while newer apps who
support it will.

ThanksUse stored procedures as your data access layer. Supporting multiple
applications is much easier that way because the SPs can insulate the
application from underlying schema changes. This is just one of the
benefits of using SPs.

--
David Portas
SQL Server MVP
--

Handling of xml data within Oracle 9/10 and sql Server 2005

Hi there,
I would like to know the best way to handle Xml data stored on Oracle and
Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
not be different for Oracle and Sql Server. Any hint ?
Regards
Sql Server 2005/2000 has a OPENXML statement.
When I used Oracle 9 (while back), I was very disappointed in its xml
capabilities.
First question:
Are you pushing xml into the db, or reading it out?
It looks like reading it out, but wanted to make sure.
"Oriane" <oriane@.guermantes.fr> wrote in message
news:BD4ABE46-921B-4C70-92E4-F949528428AC@.microsoft.com...
> Hi there,
> I would like to know the best way to handle Xml data stored on Oracle and
> Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
> not be different for Oracle and Sql Server. Any hint ?
> Regards
|||Hi Sloan,
"sloan" <sloan@.ipass.net> a crit dans le message de
news:uZvmoiYuHHA.536@.TK2MSFTNGP06.phx.gbl...
> Sql Server 2005/2000 has a OPENXML statement.
> When I used Oracle 9 (while back), I was very disappointed in its xml
> capabilities.
And what about Oracle 10g ?
> First question:
> Are you pushing xml into the db, or reading it out?
Reading and writing, but mostly reading...
> It looks like reading it out, but wanted to make sure.
|||Oracle 10? No idea.
Here is what I found.
I now remember how much I hated Oracle XML.
Good luck. I have no more I can offer beyond this.
/* Actual Logic of This procedure */
--this is just a check to make sure it can be cast as a XMLTYPE document
SELECT sys.xmltype.createxml(in_errorlogxml) INTO xmlvar FROM dual;
convertedBlobToXMLType := XMLTYPE(in_errorlogxml);
SELECT SEQ_ErrorLogID.NEXTVAL INTO ErrorID FROM DUAL;
--There is an issue with referring to the XML directly (as a cast clob
object)
--This is a workaround ... by putting the value into a temp database
--and then referring to that value, it will work.
delete from XMLTempHolderTable;
commit;
insert into XMLTempHolderTable values
(ErrorID,convertedBlobToXMLType);--in_errorlogxml);
commit;
INSERT INTO ERRORLOG (
ErrorID,
MachineName,
TimeStampValue,
FullName,
AppDomainName,
ThreadIdentity,
WindowsIdentity,
ExceptionType,
Message,
TargetSite,
Source,
StackTrace,
EntryDateTime
)
SELECT ErrorID,
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.MachineName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.TimeStamp'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.FullName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.AppDomainName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.ThreadIdentity'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.WindowsIdentity'),
extractValue(value(d),'/ExceptionInformation/Exception/@.ExceptionType'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Message'),
extractValue(value(d),'/ExceptionInformation/Exception/@.TargetSite'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Source'),
extractValue(value(d),'/ExceptionInformation/Exception/StackTrace'),
sysdate
--FROM table (xmlsequence(extract(XMLTYPE.createXML(in_errorlog xml),
'/ExceptionInformation'))) d; --Does not work
--FROM XMLTempHolderTable tmp,table
(xmlsequence(extract(xmltype(tmp.XMLValue), '/ExceptionInformation')))
; --if the XMLValue is a clob
FROM XMLTempHolderTable tmp,
table (xmlsequence(extract((tmp.XMLValue),
'/ExceptionInformation'))) d--; --if the XMLValue is a XMLType
WHERE tmp.XMLID = ErrorID;
--Here's the deal. The second and third "FROM" is reading the value from
an intermediate table
--and it works
--the first FROM is trying to read the variable outright, and it fails
?
--For some reason, the code cannot refer to the cast clob (as xmltype)
directly
--but if one puts it into an intermediate table, and then read it, it
works?
COMMIT;
++++++++++++++++++++++++++=
DROP TABLE ERRORLOG
/
CREATE TABLE ERRORLOG (
/*
The below table definition maps to the information being provided by the
Microsoft.ApplicationBlocks.ExceptionManagement XMLPublisher
Here is a sample xml document.
<ExceptionInformation>
<AdditionalInformationProperty
ExceptionManager.MachineName="CelineDionXP1"
ExceptionManager.TimeStamp="11/8/2002 1:13:48 PM"
ExceptionManager.FullName="Microsoft.ApplicationBl ocks.ExceptionManagement,
Version=1.0.1769.18782, Culture=neutral, PublicKeyToken=null"
ExceptionManager.AppDomainName="ExceptionManagemen tQuickStartSamples.exe"
ExceptionManager.ThreadIdentity=""
ExceptionManager.WindowsIdentity="jean claude van damme" />
<Exception ExceptionType="System.DivideByZeroException"
Message="Attempted to divide by zero."
TargetSite="Void btnLogon_Click(System.Object, System.EventArgs)"
Source="ExceptionManagementQuickStartSamples">
<StackTrace> at
ExceptionManagementQuickStartSamples.Form1.btnLogo n_Click(Object sender,
EventArgs e) in c:\program files\microsoft application blocks for
..net\exception
management\code\cs\exceptionmanagementquickstartsa mples\form1.cs:line
171</StackTrace>
</Exception>
</ExceptionInformation>
*/
ErrorID int not null primary key ,
MachineName varchar2(128) null ,
TimeStampValue varchar2(64) null ,
FullName varchar2(128) null ,
AppDomainName varchar2(128) null ,
ThreadIdentity varchar2(128) null ,
WindowsIdentity varchar2(128) null ,
StackTrace varchar2(4000) null ,
ExceptionType varchar2(128) null ,
Message varchar2(640) not null ,
TargetSite varchar2(128) null ,
Source varchar2(128) null ,
EntryDateTime date default sysdate not null
)
/
DROP SEQUENCE SEQ_ErrorLog
/
CREATE SEQUENCE SEQ_ErrorLog
start with 1
increment by 1
nomaxvalue
/
DROP TABLE XMLTempHolderTable
/
CREATE TABLE XMLTempHolderTable (
/*
There is an issue reading a clob as an XMLTYPE directly
This is a temporary workaround, this table should never have more than 1
record in it
and is just a working table.
*/
XMLID int not null primary key ,
XMLValue XMLTYPE --CLOB
)
/
COMMIT
/
|||"sloan" <sloan@.ipass.net> a crit dans le message de
news:eadkgEeuHHA.3368@.TK2MSFTNGP02.phx.gbl...
> Oracle 10? No idea.
>
> Here is what I found.
>
> I now remember how much I hated Oracle XML.
> Good luck. I have no more I can offer beyond this.
Ok thanks

Handling of xml data within Oracle 9/10 and sql Server 2005

Hi there,
I would like to know the best way to handle Xml data stored on Oracle and
Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
not be different for Oracle and Sql Server. Any hint ?
RegardsSql Server 2005/2000 has a OPENXML statement.
When I used Oracle 9 (while back), I was very disappointed in its xml
capabilities.
First question:
Are you pushing xml into the db, or reading it out?
It looks like reading it out, but wanted to make sure.
"Oriane" <oriane@.guermantes.fr> wrote in message
news:BD4ABE46-921B-4C70-92E4-F949528428AC@.microsoft.com...
> Hi there,
> I would like to know the best way to handle Xml data stored on Oracle and
> Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
> not be different for Oracle and Sql Server. Any hint ?
> Regards|||Hi Sloan,
"sloan" <sloan@.ipass.net> a crit dans le message de
news:uZvmoiYuHHA.536@.TK2MSFTNGP06.phx.gbl...
> Sql Server 2005/2000 has a OPENXML statement.
> When I used Oracle 9 (while back), I was very disappointed in its xml
> capabilities.
And what about Oracle 10g ?
> First question:
> Are you pushing xml into the db, or reading it out?
Reading and writing, but mostly reading...
> It looks like reading it out, but wanted to make sure.|||Oracle 10? No idea.
Here is what I found.
I now remember how much I hated Oracle XML.
Good luck. I have no more I can offer beyond this.
/* Actual Logic of This procedure */
--this is just a check to make sure it can be cast as a XMLTYPE document
SELECT sys.xmltype.createxml(in_errorlogxml) INTO xmlvar FROM dual;
convertedBlobToXMLType := XMLTYPE(in_errorlogxml);
--
SELECT SEQ_ErrorLogID.NEXTVAL INTO ErrorID FROM DUAL;
--There is an issue with referring to the XML directly (as a cast clob
object)
--This is a workaround ... by putting the value into a temp database
--and then referring to that value, it will work.
delete from XMLTempHolderTable;
commit;
insert into XMLTempHolderTable values
(ErrorID,convertedBlobToXMLType);--in_errorlogxml);
commit;
INSERT INTO ERRORLOG (
ErrorID,
MachineName,
TimeStampValue,
FullName,
AppDomainName,
ThreadIdentity,
WindowsIdentity,
ExceptionType,
Message,
TargetSite,
Source,
StackTrace,
EntryDateTime
)
SELECT ErrorID,
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.MachineName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.TimeStamp'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.FullName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.AppDomainName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.ThreadIdentity'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.WindowsIdentity'),
extractValue(value(d),'/ExceptionInformation/Exception/@.ExceptionType'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Message'),
extractValue(value(d),'/ExceptionInformation/Exception/@.TargetSite'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Source'),
extractValue(value(d),'/ExceptionInformation/Exception/StackTrace'),
sysdate
--FROM table (xmlsequence(extract(XMLTYPE.createXML(in_errorlogxml),
'/ExceptionInformation'))) d; --Does not work
--FROM XMLTempHolderTable tmp,table
(xmlsequence(extract(xmltype(tmp.XMLValue), '/ExceptionInformation')))
; --if the XMLValue is a clob
FROM XMLTempHolderTable tmp,
table (xmlsequence(extract((tmp.XMLValue),
'/ExceptionInformation'))) d--; --if the XMLValue is a XMLType
WHERE tmp.XMLID = ErrorID;
--Here's the deal. The second and third "FROM" is reading the value from
an intermediate table
--and it works
--the first FROM is trying to read the variable outright, and it fails
'
--For some reason, the code cannot refer to the cast clob (as xmltype)
directly
--but if one puts it into an intermediate table, and then read it, it
works'
COMMIT;

++++++++++++++++++++++++++=
DROP TABLE ERRORLOG
/
CREATE TABLE ERRORLOG (
/*
The below table definition maps to the information being provided by the
Microsoft.ApplicationBlocks.ExceptionManagement XMLPublisher
Here is a sample xml document.
<ExceptionInformation>
<AdditionalInformationProperty
ExceptionManager.MachineName="CelineDionXP1"
ExceptionManager.TimeStamp="11/8/2002 1:13:48 PM"
ExceptionManager.FullName="Microsoft.ApplicationBlocks.ExceptionManagement,
Version=1.0.1769.18782, Culture=neutral, PublicKeyToken=null"
ExceptionManager.AppDomainName="ExceptionManagementQuickStartSamples.exe"
ExceptionManager.ThreadIdentity=""
ExceptionManager.WindowsIdentity="jean claude van damme" />
<Exception ExceptionType="System.DivideByZeroException"
Message="Attempted to divide by zero."
TargetSite="Void btnLogon_Click(System.Object, System.EventArgs)"
Source="ExceptionManagementQuickStartSamples">
<StackTrace> at
ExceptionManagementQuickStartSamples.Form1.btnLogon_Click(Object sender,
EventArgs e) in c:\program files\microsoft application blocks for
.net\exception
management\code\cs\exceptionmanagementqu
ickstartsamples\form1.cs:line
171</StackTrace>
</Exception>
</ExceptionInformation>
*/
ErrorID int not null primary key ,
MachineName varchar2(128) null ,
TimeStampValue varchar2(64) null ,
FullName varchar2(128) null ,
AppDomainName varchar2(128) null ,
ThreadIdentity varchar2(128) null ,
WindowsIdentity varchar2(128) null ,
StackTrace varchar2(4000) null ,
ExceptionType varchar2(128) null ,
Message varchar2(640) not null ,
TargetSite varchar2(128) null ,
Source varchar2(128) null ,
EntryDateTime date default sysdate not null
)
/
DROP SEQUENCE SEQ_ErrorLog
/
CREATE SEQUENCE SEQ_ErrorLog
start with 1
increment by 1
nomaxvalue
/
DROP TABLE XMLTempHolderTable
/
CREATE TABLE XMLTempHolderTable (
/*
There is an issue reading a clob as an XMLTYPE directly
This is a temporary workaround, this table should never have more than 1
record in it
and is just a working table.
*/
XMLID int not null primary key ,
XMLValue XMLTYPE --CLOB
)
/
COMMIT
/|||"sloan" <sloan@.ipass.net> a crit dans le message de
news:eadkgEeuHHA.3368@.TK2MSFTNGP02.phx.gbl...
> Oracle 10? No idea.
>
> Here is what I found.
>
> I now remember how much I hated Oracle XML.
> Good luck. I have no more I can offer beyond this.
Ok thanks

Handling Failover occur using T-SQL

Hi all,

With C# or VC++ we can use ADO.NET that support the system work smothly when failover occur. I would like to handle failover in t-sql enviroment and it seam to be hard for me when swiching ":connect <servername> code

Do you have any idea to handle it with T-SQL. I need to make a demo on it. Please help!

There is no automated way to switch to the mirror using the :CONNECT command. You would need to change the server name manually.

If you are scheduling T/SQL commands to run via the SQL scheduler in sqlcmd you will probably want to write a wrapper in C#.NET or VB.NET so that you can take advantage of the failover options which are available there.

Handling Failover occur using T-SQL

Hi all,

With C# or VC++ we can use ADO.NET that support the system work smothly when failover occur. I would like to handle failover in t-sql enviroment and it seam to be hard for me when swiching ":connect <servername> code

Do you have any idea to handle it with T-SQL. I need to make a demo on it. Please help!

There is no automated way to switch to the mirror using the :CONNECT command. You would need to change the server name manually.

If you are scheduling T/SQL commands to run via the SQL scheduler in sqlcmd you will probably want to write a wrapper in C#.NET or VB.NET so that you can take advantage of the failover options which are available there.

Handling Events in Report Builder

Is there any way that I can handle events using a custom library in the
Report Builder? For example, I have written my stored procs to specifically
handle the "All" case of multi-select parameters without the necessity of a
huge comma-separated list. I'd like to post-process the parameters before the
report is rendered.
Any suggestions?
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com"Event driven" is not possible in ReportBuilder. Will check from my side,
whether any API's can be used to handle events. But I dont think this is
possible.
Amarnath
"Helen Warn" wrote:
> Is there any way that I can handle events using a custom library in the
> Report Builder? For example, I have written my stored procs to specifically
> handle the "All" case of multi-select parameters without the necessity of a
> huge comma-separated list. I'd like to post-process the parameters before the
> report is rendered.
> Any suggestions?
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com

Handling errors returned by SSRS

We are displaying the report in our reporting application but we do not want to display errors from SSRS to the user. We want to handle the errors and display a user friendly message.

How can that be done?. We are making URL access to the report server.

Thanks.

Please help me to understand this better. If you use URL addressability what application layer will handle the error messages? If you use the VS.NET 2005 Report Viewer control, your application can handle the ReportError event.|||

Alright,

So we are using an iframe in our application which we are making a url call to the report server. Hence, if there is a problem like "access" denied, we do not want the iframe to read "SQL Server error" but have some error that shows that the user is interacting with our application. This can only be done if on the report server we could write some generic error page which will always get called anytime ssrs throws an error.

Thanks.

|||Sorry, you are out of lack here. URL addressability is certainly very easy but not that flexible. Same limitations apply as invoking a server-side web page by URL.|||

Hi,

I have my report viewer control and reporterror event to handel all the exception.Do we need to call the reporterror event in my code or automatically it will be called when error occurs?

Thanks,

Ranjan

|||Not sure what you mean by call the event. The event handler will be called for you when the event happens (in this case the report errors out).|||

Hi Teo,

If u have any sample code of how to show a report in reportviewer along with reporterror event and if you can post it here it would be very helpful.

Thanks,

Ranjan

|||

private void reportViewer1_ReportError(object sender, Microsoft.Reporting.WinForms.ReportErrorEventArgs e)

{

// use e.Exception to get to the exception

// set e.Handled to true to prevent the ReportViewer from displaying an error message.

}

More about ReportViewer in this article.

Handling errors returned by SSRS

We are displaying the report in our reporting application but we do not want to display errors from SSRS to the user. We want to handle the errors and display a user friendly message.

How can that be done?. We are making URL access to the report server.

Thanks.

Please help me to understand this better. If you use URL addressability what application layer will handle the error messages? If you use the VS.NET 2005 Report Viewer control, your application can handle the ReportError event.|||

Alright,

So we are using an iframe in our application which we are making a url call to the report server. Hence, if there is a problem like "access" denied, we do not want the iframe to read "SQL Server error" but have some error that shows that the user is interacting with our application. This can only be done if on the report server we could write some generic error page which will always get called anytime ssrs throws an error.

Thanks.

|||Sorry, you are out of lack here. URL addressability is certainly very easy but not that flexible. Same limitations apply as invoking a server-side web page by URL.|||

Hi,

I have my report viewer control and reporterror event to handel all the exception.Do we need to call the reporterror event in my code or automatically it will be called when error occurs?

Thanks,

Ranjan

|||Not sure what you mean by call the event. The event handler will be called for you when the event happens (in this case the report errors out).|||

Hi Teo,

If u have any sample code of how to show a report in reportviewer along with reporterror event and if you can post it here it would be very helpful.

Thanks,

Ranjan

|||

private void reportViewer1_ReportError(object sender, Microsoft.Reporting.WinForms.ReportErrorEventArgs e)

{

// use e.Exception to get to the exception

// set e.Handled to true to prevent the ReportViewer from displaying an error message.

}

More about ReportViewer in this article.

Handling Errors

Is it possible to handle an FK violation error in SQL Server 2000?
IOW, if I encounter this error, is it possible for the code in my proc to
execute alternative code rather than immediately exiting with an error
message?
I can write code to always test for a FK violation before I try to insert,
but is it possible to do the same thing with error handling (i.e., try the
insert straight off and then if I get an error, do something else)?
I believe I can do this with the TRY/CATCH in SQL Server 2005 but I was
wondering if it is possible with the more limited error handling of SQL
Server 2000.
DaveHi, Dave
Yes, it is possible in a stored procedure, but not if the code is in a
trigger or called from a trigger. For example:
USE Northwind
INSERT INTO Territories
(TerritoryID, TerritoryDescription, RegionID)
VALUES (10000, 'Mars', 7)
IF @.@.ERROR<>0 BEGIN
INSERT INTO Region
(RegionID, RegionDescription)
VALUES (7, 'Outer space')
INSERT INTO Territories
(TerritoryID, TerritoryDescription, RegionID)
VALUES (10000, 'Mars', 7)
END
DELETE Territories WHERE RegionID=7
DELETE Region WHERE RegionID=7
However, it's recommended that you check for the FK violation before
the insert, because if you call this from ADO, it is difficult to
handle the error from the client side (it will still be raised on the
client and you don't know if it was handled by the procedure or if it
is a real error that should be logged or reported to the user).
You should also know that there are some errors that cannot be handled
this way in the procedure, because SQL Server aborts the batch when it
encounters errors like conversion errors, for example.
For more informations, see this excellent article by Erland Sommarskog,
SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html
Razvan|||Thank you very much Razvan!
That is indeed an excellent article by Erland that you refernce.
Dave
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116564456.658264.99430@.g44g2000cwa.googlegroups.com...
> Hi, Dave
> Yes, it is possible in a stored procedure, but not if the code is in a
> trigger or called from a trigger. For example:
> USE Northwind
> INSERT INTO Territories
> (TerritoryID, TerritoryDescription, RegionID)
> VALUES (10000, 'Mars', 7)
> IF @.@.ERROR<>0 BEGIN
> INSERT INTO Region
> (RegionID, RegionDescription)
> VALUES (7, 'Outer space')
> INSERT INTO Territories
> (TerritoryID, TerritoryDescription, RegionID)
> VALUES (10000, 'Mars', 7)
> END
> DELETE Territories WHERE RegionID=7
> DELETE Region WHERE RegionID=7
> However, it's recommended that you check for the FK violation before
> the insert, because if you call this from ADO, it is difficult to
> handle the error from the client side (it will still be raised on the
> client and you don't know if it was handled by the procedure or if it
> is a real error that should be logged or reported to the user).
> You should also know that there are some errors that cannot be handled
> this way in the procedure, because SQL Server aborts the batch when it
> encounters errors like conversion errors, for example.
> For more informations, see this excellent article by Erland Sommarskog,
> SQL Server MVP:
> http://www.sommarskog.se/error-handling-I.html
> Razvan
>|||The "set xact_abort on / off" setting determines whether some exceptions
immediately abort the transaction or continue processing with @.@.error.
"Dave" <dave@.nospam.ru> wrote in message
news:OvTcy4MXFHA.1148@.tk2msftngp13.phx.gbl...
> Is it possible to handle an FK violation error in SQL Server 2000?
> IOW, if I encounter this error, is it possible for the code in my proc to
> execute alternative code rather than immediately exiting with an error
> message?
> I can write code to always test for a FK violation before I try to insert,
> but is it possible to do the same thing with error handling (i.e., try the
> insert straight off and then if I get an error, do something else)?
> I believe I can do this with the TRY/CATCH in SQL Server 2005 but I was
> wondering if it is possible with the more limited error handling of SQL
> Server 2000.
> Dave
>

Handling conflicts with a Custom COM resolver

Hello,
I'm writing a custom COM conflict resolver to handle replication conflicts.
I'm having problems with handling a conflict caused by the violation of a foreign key constraint.
Situation:
Publisher deletes item X
Concurrently the subsriber creates item Y with a foreign key to item X
My goal of the replication is, that the deletion of item X on the publisher is rollbacked and that item Y is replicated to the publisher. So after replication, the publisher and subscriber contain both item X and item Y.
The replication procedure is:
In Upload phase, a conflict (REPOLEChange_UploadInsertFailed) occurs, because item Y cannot be created on the publisher (caused by foreign key violation). SQLServer adds item Y automatically to the MSMerge_tombstone table of the publisher.
In Download phase, the change event 'REPOLEChange_PublisherSystemDelete' happens, because the system tries to delete item Y at the subscriber.
My questions are:
How can I prevent item Y from being deleted on the subscriber?
How can I achieve that item Y is created at the publisher?
The problem is that item Y is put in the MSMerge_tombstone table of the publisher. I don't think it is nice to remove it manually from this system table.
Does someone has ideas ?
thanks in advance, Marco
What you are describing is a feature called "compensation". That is, if
we try and apply a row and an error occurs replication will compensate
for that error and delete the row in order to get both sides in sync
with each other.
This is not always the desired effect. There is a fix which will allow
you to control whether compensation occurs.
See:
http://support.microsoft.com/?kbid=828637
Please don't delete manually out of tombstone or contents
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Marco wrote:
> Hello,
> I'm writing a custom COM conflict resolver to handle replication conflicts.
> I'm having problems with handling a conflict caused by the violation of a foreign key constraint.
> Situation:
> Publisher deletes item X
> Concurrently the subsriber creates item Y with a foreign key to item X
> My goal of the replication is, that the deletion of item X on the publisher is rollbacked and that item Y is replicated to the publisher. So after replication, the publisher and subscriber contain both item X and item Y.
> The replication procedure is:
> In Upload phase, a conflict (REPOLEChange_UploadInsertFailed) occurs, because item Y cannot be created on the publisher (caused by foreign key violation). SQLServer adds item Y automatically to the MSMerge_tombstone table of the publisher.
> In Download phase, the change event 'REPOLEChange_PublisherSystemDelete' happens, because the system tries to delete item Y at the subscriber.
> My questions are:
> How can I prevent item Y from being deleted on the subscriber?
> How can I achieve that item Y is created at the publisher?
> The problem is that item Y is put in the MSMerge_tombstone table of the publisher. I don't think it is nice to remove it manually from this system table.
> Does someone has ideas ?
> thanks in advance, Marco
>
|||thanks!
I've tried the hotfix and it gives the desired result.
Marco

Handling a SQL Exceptions and Custom Error Messages

Hello guys,

I need some ideas on how to handle an exception or a user defined error message.

I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?

This is what I was thinking...

Solution #1
-----
My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.

Solution #2
-----
The proc should have an output param ( @.CreationStatus CHAR(1) ).
If the @.CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.

What are some of the ways you deal with this situation?

Your suggestions are greatly appreciated.

Thank you!

You could return a @.status value with (0=success, 1= failure and an appropriate status message @.Statusmsg ( = 'Success' if @.status = 0, custom error message if @.status = 1)

From your application you could check the value in @.status and if its not 0, then display the message from @.statusmsg. You can handle this in a number of ways, It comes down to setting up one standard way of doing it across all procs and communicating with your team and documenting it so the same logic is followed across all procs.

Handle Time Zones and Daylight Savings Time in SQL Server

I wanted to know how we can handle Time Zones and Daylight Savings Time in SQL Server 2000 as well as 2005.

Any pointers would be helpful.

Pranav

Pranav:

Get started by giving the getUTCDate() funciton a look. Other useful functions include dateadd, datediff, datename, datepart, day, getdate, month, year and day. Check out books online for more information about the functions.

Also, remember that in most cases the server platforms that MS SQL Server runs on automatically corrects for daylight savings time on the OS level so this is normally not something that you have to worry about.

Dave

Handle TEXT FIELDS in Recordset Using ASP

hi there,

I have a problem when i tried to get data from mssql server using recordset in asp language. For example, i have table containing 5 fields. One of them is text field and the rest are varchar.

here i list the table structure (table name: Info):

Field Name Field Type Length

---

Name varchar 50

Gender varchar 50

Address text 16

Status varchar 50

Age varchar 50

---

Sample data inside table info.

Name Gender Address Status Age

---

Ali male MAL S 26

this is my code:

set rs = server.createobject ("adodb.recordset")

rs.open "select * from info", connectionstring

if not rs.eof then

name = rs("name")

gender = rs("gender")

address = rs("address")

status = rs("status")

age = rs("age")

end if

rs.close

what happend here, i managed to get name = Ali, gender = male but for address, status and age is nothing, just empty string.What was really happened, does anyone knows this problem.

If I change

rs.open "select * from info", connectionstring

to

rs.open "select * from info", connectionstring, 3

i managed to get all the info but i have to do a lot of code change for my program. Is there any solution to this problem?

i'm using win server 2003 and mssql server 2000

thanks for cooperation from you guys.

please accept my apologize for my bad language.

regards

kharulli

Firts you need to move all your BLOB-type fields to the end of the list of your fields in a selection list of the SELECT statement. It means your SELECT should look like

SELECT [Name], Gender , Status, Age, Address FROM ....

Now, after you query data, you need to get GetChunk method of the field to read the data in a case if it is longer than 255 characters. Otherwise you could use Value property

|||

thanks VMazur for quick replies,

I tried and its works. But it seems i must do a lot code conversion to overcome this problem. Its this MSSQL weaknesses or blob-type just work like this? Does MSSQL have a solution to this problem. Sorry to cause trouble to your guys in answering my post.

best regards

kharulli.

|||I believe this is how provider handles BLOB fields. They have to be at the end of your selection. Reason for this is, probably, memory allocation|||thanks for the info. it helped me a lot.

handle store procedure return 2 table

I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?In .NET 2.0 you can return multiple datasets with a datareader. You use the NextResult() method to get the next set. Seehere.

Handle SQL errors with severity 10

how to handle sql errors with severity less then 10 in .NET

i call to stored procedure which raise error with severity 10 for example
however in c# i can't cach this error (with severiry >= 11 it going to the
catch block in my c# app), also the @.@.ERROR is equal to zero when severity
<= 10

--
Message posted via http://www.sqlmonster.comE B via SQLMonster.com (forum@.SQLMonster.com) writes:
> how to handle sql errors with severity less then 10 in .NET
> i call to stored procedure which raise error with severity 10 for example
> however in c# i can't cach this error (with severiry >= 11 it going to the
> catch block in my c# app), also the @.@.ERROR is equal to zero when severity
><= 10

You set up an InfoMessages event handler. Please see the .Net Framework
reference for details.

In SqlClient 2.0, there is a new property which permits you divert all
errors with severity <= 16, that is all user errors, to the InfoMessage
handler. This actually has a couple of advantages, particulary when
you use ExecuteReader, so there is all reason to get used to InfoMessages.

By the way, a small tidbit: you can never get a message with severity
10 from SQL Server. 10 is always changed to level 0.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok i understand., Thank u in andvance.

Learning learning an learning

--
Message posted via http://www.sqlmonster.com

Handle Postbacks

RS2000
If a report have several parameter dropdowns, the data for each populated
dropdown is fetched again when data for non populated drop down is fetched.
Is there a way to handle postbacks events/viewstate more effective?
Regards Martin BringHave you created your own asp.net dropdowns to feed the parameters into the
report url address?
You can use the Atlas framework from Microsoft (see http://atlas.asp.net).
This allows partial postbacks of an asp.net 2.0 page, perfect for dependent
dropdowns...
"Maran" wrote:
> RS2000
> If a report have several parameter dropdowns, the data for each populated
> dropdown is fetched again when data for non populated drop down is fetched.
> Is there a way to handle postbacks events/viewstate more effective?
> Regards Martin Bring|||Thank you for replying.
No, I am just using the "out-of-the-box" functionality and so will it stay
for some time. In the future we might use our own code for paramters and
h´just call RS web service.
Regards Martin
************
"NH" wrote:
> Have you created your own asp.net dropdowns to feed the parameters into the
> report url address?
> You can use the Atlas framework from Microsoft (see http://atlas.asp.net).
> This allows partial postbacks of an asp.net 2.0 page, perfect for dependent
> dropdowns...
> "Maran" wrote:
> > RS2000
> >
> > If a report have several parameter dropdowns, the data for each populated
> > dropdown is fetched again when data for non populated drop down is fetched.
> >
> > Is there a way to handle postbacks events/viewstate more effective?
> >
> > Regards Martin Bring|||I am not sure I fully understand what problem you have. Are you trying to
have dependent dropdowns in the report viewer ?
"Maran" wrote:
> Thank you for replying.
> No, I am just using the "out-of-the-box" functionality and so will it stay
> for some time. In the future we might use our own code for paramters and
> h´just call RS web service.
> Regards Martin
> ************
> "NH" wrote:
> > Have you created your own asp.net dropdowns to feed the parameters into the
> > report url address?
> >
> > You can use the Atlas framework from Microsoft (see http://atlas.asp.net).
> > This allows partial postbacks of an asp.net 2.0 page, perfect for dependent
> > dropdowns...
> >
> > "Maran" wrote:
> >
> > > RS2000
> > >
> > > If a report have several parameter dropdowns, the data for each populated
> > > dropdown is fetched again when data for non populated drop down is fetched.
> > >
> > > Is there a way to handle postbacks events/viewstate more effective?
> > >
> > > Regards Martin Bring|||Yes, our parameter dropdowns is dependent on each other.
For example: If the user choose a country in one dropdown, regions will show
in another dropdown.
When running the report from the report server the data for the dropdowns
will be fetched each time (for the populated ones). Relating to ASP.Net I
would like the report to have som "ViewState" to remember the data it have
already fetched.
I just found out today that running the report in Visual Studio will not
fetch the data twice.
Regards
Martin Bring
"NH" wrote:
> I am not sure I fully understand what problem you have. Are you trying to
> have dependent dropdowns in the report viewer ?
> "Maran" wrote:
> > Thank you for replying.
> >
> > No, I am just using the "out-of-the-box" functionality and so will it stay
> > for some time. In the future we might use our own code for paramters and
> > h´just call RS web service.
> >
> > Regards Martin
> >
> > ************
> >
> > "NH" wrote:
> >
> > > Have you created your own asp.net dropdowns to feed the parameters into the
> > > report url address?
> > >
> > > You can use the Atlas framework from Microsoft (see http://atlas.asp.net).
> > > This allows partial postbacks of an asp.net 2.0 page, perfect for dependent
> > > dropdowns...
> > >
> > > "Maran" wrote:
> > >
> > > > RS2000
> > > >
> > > > If a report have several parameter dropdowns, the data for each populated
> > > > dropdown is fetched again when data for non populated drop down is fetched.
> > > >
> > > > Is there a way to handle postbacks events/viewstate more effective?
> > > >
> > > > Regards Martin Bring

handle errors in trigger

How to handle error in trigger.
i m inserting record in other table in after insert trigger. but if insert
statement in trigger has error, procedure quits with error and i cannot use
@.@.error to check itThats an artifical error, but that the way you could handle it:
Create Table SomeTable
(
SomeColumn INT
)
Go
ALTER TRIGGER SomeTrigger
on SomeTable
FOR INSERT
AS
BEGIN
RAISERROR('SomeError',16,1)
PRINT @.@.ERROR
IF @.@.ERROR <> 0
PRINT 'Seems that an error occured.'
END
INSERT INTO SomeTable VALUES (1)
DELETE FROM SomeTable
SELECT * FROM SomeTable
Keep in mind that some error aren=B4catchable due to the severity.
I suggest taking a look in Erland=B4s Error handling script
http://www.sommarskog.se/error-hand...triggercontext.
HTH, jens Suessmeyer.|||I don't know exactly what you're doing but there are a couple of things
you may want to check before you do an insert.
If you're using variables in your values list check them before you
insert if they aren't null in case your table has colums with not null
defined.
But in general do have a look at Erland's article.|||Ya variable is null and table does not allow null, but error should be
trapable isnt it ?
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137149721.665336.187300@.g44g2000cwa.googlegroups.com...
> I don't know exactly what you're doing but there are a couple of things
> you may want to check before you do an insert.
> If you're using variables in your values list check them before you
> insert if they aren't null in case your table has colums with not null
> defined.
> But in general do have a look at Erland's article.
>|||Error handling in sql server 2000 within triggers is tricky business,
have a look at Erland's article.
http://www.sommarskog.se/error-hand...triggercontext.
But since you know that the variable is null then something like this
may avoid the error:
DECLARE @.var int
SET @.var = ISNULL(@.var, 1) /* the variable will be assigned the value
of 1 if it is null
or
IF(@.var) IS NULL
BEGIN
-- write all variables to a log table
INSERT INTO ...
RETURN
END|||The AFTER trigger fires *after* the insert - hence the name. In case of a
constraint violation (such as the nullability constraint) the the AFTER
trigger will not fire at all, since the insert is aborted.
Consider using an INSTEAD OF trigger or better yet - set values properly
before inserting them or use a deafult.
ML
http://milambda.blogspot.com/|||who says the variable is coming from inserted?|||I was guessing based on OP's narrative.
ML
http://milambda.blogspot.com/|||from OP
> but if insert statement in trigger|||> but if insert statement in trigger