Exceptions

How do I code an exception to terminate a transaction?
How do I code an exception so that it doesn't terminate the program?Hello,

If you want to stop a transaction via exception handling or with other techniques, use something like this

.
.
.
WHILE cuCursor%FOUND LOOP
EXIT WHEN cuCursor.field = 'exit'
END WHILE;

or

WHILE cuCursor%FOUND LOOP
IF cuCursor.field = 'exit'
GOTO EndLabel
END IF;
END WHILE;

<<EndLabel>>
.
.
.

or

WHILE cuCursor%FOUND LOOP
IF cuCursor.field = 'exit'
THROW endException
END IF;
END WHILE;

If your want to catch a exception use something like this

WHILE cuCursor%FOUND LOOP
BEGIN
nNumber = 'abc';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END WHILE;

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Thank you, it helped

Originally posted by bbk
How do I code an exception to terminate a transaction?
How do I code an exception so that it doesn't terminate the program?

ExceptionMessageBox

I am trying to get the Exception Message Box to work in a script task in SSIS.

I am using the example from http://msdn2.microsoft.com/en-us/library/ms166340.aspx almost verbatum.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Sub Main()

'

' Define the message and caption to display.

Dim str As String = "Do you want to push to Production?"

Dim caption As String = "Zip Push"

Dim var As Variables

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, caption)

box.DefaultButton = ExceptionMessageBoxDefaultButton.Button2

box.Symbol = ExceptionMessageBoxSymbol.Question

box.Buttons = ExceptionMessageBoxButtons.YesNo

'box.SetButtonText("Yes", "No", "Cancel")

If Windows.Forms.DialogResult.Yes = box.Show(CType(Me,Windows.Forms.IWin32Window)) Then

Dts.Variables("Production").Value = True

End If

'

Dts.TaskResult = Dts.Results.Success

End Sub

And yet all I get is the following error

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Has anyone gotten this to work?

BTW: I am running SQL 2005 SP2

Just use MsgBox("...")

You don't even need to reference any assembly for it.

By the way, what are you trying to do here? SSIS is supposed to be a batch-oriented process, not interactive.

-Jamie

|||MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".|||

S1monk wrote:

MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".

Was it? I've just re-read your original post and didn't see that question anywhere. All I saw was "Has anyone gotten this to work?"

I'm afraid I don't know why your code isn't working. What exactly do you want to do?

-Jamie

|||

Jamie Thomson wrote:

What exactly do you want to do?

I second this question. Also Jamie's comment about SSIS being a batch utility, not an interactive one.

|||

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

|||

S1monk wrote:

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

I don't know for sure but my guess from the error message is that this simply cannot be done. The script task is not designed to be used interactively as you are attempting to do.

S1monk wrote:

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

If something isn't working then I would have thought suggesting an alternative was perfectly good advice. Likewise the advice about SSIS being a batch utility was intended to be helpful and, as I think your error message proves, this advice has been borne out to be true.

The question "What exactly are you trying to do?" was a lead-in to suggesting an alternative that WOULD work. Due to the very nature of SSIS, prompting users for input from INSIDE a package is not an appropriate thing to do - better to prompt them elsewhere and pass that information into the package so that it can act upon it dynamically.

I was trying to proffer some simple advice but obviously that advice is not appreciated hence I won't post on this thread again. I have no desire to help someone that doesn't value that help. Good luck in finding a solution to your problem.

-Jamie

|||

The type you are passing to the Show method is not a window. You have to catch a handle of the top window and pass it to this method. Perhaps, even null could work but it might create a weird effects (like popping up in the backround or something similar).

|||

The error is because Me is not a window, it does not implement IWin32Window, so the cast is invalid - CType(Me,Windows.Forms.IWin32Window)

You do not have a Form, because SSIS is just not aimed at being an interactive tool, which is the point others have tried to highlight.

You could get the same functionality with old MsgBox or System.Windows.Forms.MessageBox (same thing really), it supports Yes/No/Cancel if you wish.

Sorry for going so far off the question, but I thought it might help provide a solution.

|||

I found a way to make it work

Option Strict On

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Class ScriptMain

Public Sub Main()

Dim str As String = "Are you sure you want to delete file 'c:\somefile.txt'?"

Dim caption As String = "Confirm File Deletion"

Dim win As Windows.Forms.IWin32Window

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, _

caption, ExceptionMessageBoxButtons.YesNo, _

ExceptionMessageBoxSymbol.Question, _

ExceptionMessageBoxDefaultButton.Button2)

If Windows.Forms.DialogResult.Yes = box.Show(win) Then

' Delete the file.

End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||[Shaking head]

I just don't understand why you'd want to do this. I just don't understand........ SSIS is not an interactive tool - your solution only works when debugging SSIS.

[/Shaking head]|||I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.|||

S1monk wrote:

I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.

HAHAHAHA. Yeah, okay.|||I hope we can agree then, that when developing, testing or maintaining in BIDS, dialogs can be useful.

The use of dialogs IN SSIS packages is not a good idea in general, but even so, that's not a universal truth (dialogs + SSIS = misunderstanding SSIS and its intent) .

As long as the System::InteractiveMode variable is checked in advance, dialogs are fine. SSIS provides this variable since a good amount of time is spent in development and maintenance inside of BIDS, where its nice to make changes to variables without changing package source code.

Its for this same reason ("debugging and maintenance") that many shells and language interpreters have both an interactive mode ( bash, python,ruby, and powershell come to mind ) and may be run interactively as well as non-interactively.

ExceptionHandling in T-SQL?

Hi all,

Is there any concept of exception handling in T-SQL while writing Stored Procedures, plz. help me with this issue. I'm using SQL Server 2000

Hello Trid,

No, there is no structural error handling in 2000 so to speak. There are several ways to perform error handling in 2000, with the most common being to test the value of @.@.ERROR for a non-zero value, then send control to a handler lable for rollback etc.

if (@.@.error <> 0)

goto error_handler

return (0)

error_handler:

if (@.@.trancount > 0)

rollback tran

return (-1)

Cheers

Rob

|||

To back up what Robert said, T-SQL did not have a rich set of error handling capabilities in 2000, whatsoever. You have to deal with the error message on the client end. So be careful to check for and close any transactions you might have started when you get error messages back and want to stop the batch.

|||

Thank you Robert :)

EXCEPTION_FLT_UNDERFLOW

Hi,
I'm getting the message 'SqlDumpExceptionHandler: Process 12 generated fatal
exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL Server is terminating this
process.' and my connection is terminated.
what causes this error and how is it fixed.
using sql2k with sp3.
thanks.> Hi,
> I'm getting the message 'SqlDumpExceptionHandler: Process 12
> generated fatal exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL
> Server is terminating this process.' and my connection is terminated.
> what causes this error and how is it fixed.
> using sql2k with sp3.
Try checking the table with DBCC CHECKTABLE. DBCC DBREINDEX sometimes fixes
the problem. If it does not, it looks like a MS PSS case for me
(http://support.microsoft.com/defaul...estion.asp&SD=G
N&FR=0)
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||thanks Sebastian,
I've been messing with the data and it appears that earlier versions of MSSQ
L were not strict with what you entered into a float column - you could over
load the datatype.
thanks for your help though.

EXCEPTION_ACCESS_VIOLATION error

When I am in my publications folder and I attempt to add
a new publication, I get... 'Error 5: [SQL-DMO]Code
Execution exception: EXCEPTION_ACCESS_VIOLATION' after I
click next for the first time. If I click OK in the
error box, there are no publication databases listed.
HELP!!!!
what happens when you try to create the publication using the replication
stored procedures?
I would advise you to try to remove replication as something seems to be
hosed here. Removing and reinstalling replication frequently solves these
types of errors.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"harry" <anonymous@.discussions.microsoft.com> wrote in message
news:009601c48ad2$e83f3c00$a601280a@.phx.gbl...
> When I am in my publications folder and I attempt to add
> a new publication, I get... 'Error 5: [SQL-DMO]Code
> Execution exception: EXCEPTION_ACCESS_VIOLATION' after I
> click next for the first time. If I click OK in the
> error box, there are no publication databases listed.
> HELP!!!!
>
|||removing replication and reinstalled replication and this
did the trick.
Thanks!

EXCEPTION_ACCESS_VIOLATION

I am using SQL Server 7 and the following query giving
exception access violation error.
Error is : ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 14 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
Could you please suggest what could be wrong with the
Query.
It is working fine if I sub divide the Derived expression
to intermediate temporary tables.
SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
BUDGET_DATE,
(((D1.WB_AVG * ISNULL
(ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
@.MONTH_DAYS AS WB_EXPENSE
INTO #WB_MTD
FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
CHG_OFF_1,
COALESCE(D.REGION,L.REGION) AS REGION,
@.BUDGET_DATE AS BUDGET_DATE,
(((ISNULL(L.PRE_LOANS,0) - ISNULL
(D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
(L.CUR_LOANS,0)))/2) AS WB_AVG
FROM (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
FROM DEPOSITS_CUBE_V
WHERE BUSINESS_DATE = @.BUDGET_DATE AND
CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
FULL OUTER JOIN (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
FROM LOANS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
ON D.BUDGET_DATE = L.BUDGET_DATE AND
D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
(RATE_LAST_USED,0))/100) AS ANN_INTEREST
FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY REGION) AS R
ON D1.REGION = R.REGION
Thanks in advalceThere types of errors are typically bugs in SQL Server. Assuming you are current on service pack and
have searched KB already, I suggest you open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Girija Ponnaganti" <anonymous@.discussions.microsoft.com> wrote in message
news:006701c3ad6a$a59dd9b0$a401280a@.phx.gbl...
> I am using SQL Server 7 and the following query giving
> exception access violation error.
> Error is : ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 14 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> Could you please suggest what could be wrong with the
> Query.
> It is working fine if I sub divide the Derived expression
> to intermediate temporary tables.
> SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
> BUDGET_DATE,
> (((D1.WB_AVG * ISNULL
> (ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
> @.MONTH_DAYS AS WB_EXPENSE
> INTO #WB_MTD
> FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
> CHG_OFF_1,
> COALESCE(D.REGION,L.REGION) AS REGION,
> @.BUDGET_DATE AS BUDGET_DATE,
> (((ISNULL(L.PRE_LOANS,0) - ISNULL
> (D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
> (L.CUR_LOANS,0)))/2) AS WB_AVG
> FROM (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
> FROM DEPOSITS_CUBE_V
> WHERE BUSINESS_DATE = @.BUDGET_DATE AND
> CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
> FULL OUTER JOIN (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
> FROM LOANS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
> ON D.BUDGET_DATE = L.BUDGET_DATE AND
> D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
> JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
> TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
> (RATE_LAST_USED,0))/100) AS ANN_INTEREST
> FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY REGION) AS R
> ON D1.REGION = R.REGION
>
> Thanks in advalce

Exception.Data[key]=value

Hi.

Is it possible to transmit custom data from SQL 2005 to the client via Exception.Data[key]=value?

The idea
I have a number of SPs that accept userId parameter which must be verified. I have written a simple CLR SP (check_userid_valid) which performs the verification of the userId parameter's value. If the parameter's value is considered invalid an exception is thrown. When the exception (System.Exception) is instantiated additional data is added via the Data property of the exception:

System.Exception ex = new System.Exception();
ex.Data["Source"] = "check_userid_valid"

So, any SP that calls the check_userid_valid with invalid userId is expected to "crash" and the exception with all the additional data is expected to be propagated back to the client which, in turn, could read the data.
Unfortunately, it seems that the ex.Data contains only entries put by the MS SQL 2005 server itself, eliminating the rest.

The question is: how can I supply additional exception data with the exception I do throw from my CLR code on the server-side, so that is can be consumed at the client-side.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomException()
{
try
{
//try code goes here
System.Exception firstEx = new System.Exception("first exception");
throw firstEx;
}
catch (System.Exception ex)
{

}
finally {
System.Exception secondEx = new System.Exception("check_userid_valid");
throw secondEx;
}
}
};

Exception with ReportViewer in local mode

I use a ReportViewer to display a report in local mode (WinForm). As long as I use VS2005 in debug mode, the report is displayed correct without any problems. If I run the compiled application directly, I get a crash message :

"xxx has encountered a problem and needs to close. We are sorry for the inconvenience."....

None of the exception handlings are called (the one where the "Me.ReportViewer1.RefreshReport()" is located and the one from the application "MyApplication_UnhandledException")

Thanks for help

Peter

Hi Panpan,

I'm experiencing the exact same problem right now and I can't find out what's the cause. Did you ever figure it out? If you did I'd appreciate your answer!

Greetings,

Sjako

Exception with ReportViewer in local mode

I use a ReportViewer to display a report in local mode (WinForm). As long as I use VS2005 in debug mode, the report is displayed correct without any problems. If I run the compiled application directly, I get a crash message :

"xxx has encountered a problem and needs to close. We are sorry for the inconvenience."....

None of the exception handlings are called (the one where the "Me.ReportViewer1.RefreshReport()" is located and the one from the application "MyApplication_UnhandledException")

Thanks for help

Peter

Hi Panpan,

I'm experiencing the exact same problem right now and I can't find out what's the cause. Did you ever figure it out? If you did I'd appreciate your answer!

Greetings,

Sjako

Exception while using MS SQL SERVER 2005

Hi
When I deploy my application on JBOSS connecting to SQL SERVER 2005 using the latest beta driver, I get this exception
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 5000000003
There is no transaction context here, its just a select query.
The same application works fine on SQL SERVER 2000 with new DRIVER (2005 driver)
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
I'll take a look at this and post back to you.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Naren Chelluri" <naren.chelluri@.emagia.com> wrote in message
news:efDvk2W1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> When I deploy my application on JBOSS connecting to SQL SERVER 2005 using
> the latest beta driver, I get this exception
> com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume
> the transaction, desc: 5000000003
> There is no transaction context here, its just a select query.
> The same application works fine on SQL SERVER 2000 with new DRIVER (2005
> driver)
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...

Exception while updating NS instance through code

Hi,

I am trying to add subscription classes through code. While updating the instance it throws following exception.

Microsoft.SqlServer.Management.Smo.SmoException: The Notification Services operation performed is invalid. > System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. > Microsoft.SqlServer.NotificationServices.NSException: An attempt to run a Transact-SQL CREATE TABLE statement failed.

I don't know which table is being created on updating this instance. Does it sound to be a permission problem?

SubscriptionClass myClass = new SubscriptionClass(nmoApplication, "Publication0");
myClass.FileGroup = "DEFAULT";

......................................
......................................
......................................

SubscriptionField salesAmountRate = new nmo.SubscriptionField(myClass, "SalesAmountRate ");
salesAmountRate .Type = "tinyint";
salesAmountRate .TypeModifier = "not null";
myClass.SubscriptionFields.Add(salesAmountRate );

.........................................
.........................................
.........................................

myClass.SubscriptionEventRules.Add(eventRule);

myClass.SubscriptionScheduledRules.Add(scheduledRule);

nmoApplication.SubscriptionClasses.Add(myClass);

nmoInstance.Disable();

nmoInstance.Update();

nmoInstance.Enable();

Could be a permissions problem. How are you connecting to the database? What db roles are your user associated with?

Also check to see if the table (or another table named Publication0Old) already exists.

HTH...

Joe

Exception while updating NS instance through code

Hi,

I am trying to add subscription classes through code. While updating the instance it throws following exception.

Microsoft.SqlServer.Management.Smo.SmoException: The Notification Services operation performed is invalid. > System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. > Microsoft.SqlServer.NotificationServices.NSException: An attempt to run a Transact-SQL CREATE TABLE statement failed.

I don't know which table is being created on updating this instance. Does it sound to be a permission problem?

SubscriptionClass myClass = new SubscriptionClass(nmoApplication, "Publication0");
myClass.FileGroup = "DEFAULT";

......................................
......................................
......................................

SubscriptionField salesAmountRate = new nmo.SubscriptionField(myClass, "SalesAmountRate ");
salesAmountRate .Type = "tinyint";
salesAmountRate .TypeModifier = "not null";
myClass.SubscriptionFields.Add(salesAmountRate );

.........................................
.........................................
.........................................

myClass.SubscriptionEventRules.Add(eventRule);

myClass.SubscriptionScheduledRules.Add(scheduledRule);

nmoApplication.SubscriptionClasses.Add(myClass);

nmoInstance.Disable();

nmoInstance.Update();

nmoInstance.Enable();

Could be a permissions problem. How are you connecting to the database? What db roles are your user associated with?

Also check to see if the table (or another table named Publication0Old) already exists.

HTH...

Joe

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Exception when ExecuteNonQuery is executed

Hi All,
SqlComand.ExecuteNonQuery()
gives the following exception:
Incorrect syntax near '1.', .Net SqlClient Data Provider, at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
Would anybody be able to tell me the cause of this exception?
Thanks.This error means that the SQL statement you are trying to execute is
syntactically invalid. Try running the same SQL statement via Query
Analyzer for debugging. Post the CommandText if you need more help.
Hope this helps.
Dan Guzman
SQL Server MVP
"kd" <kd@.discussions.microsoft.com> wrote in message
news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
> Hi All,
> SqlComand.ExecuteNonQuery()
> gives the following exception:
> Incorrect syntax near '1.', .Net SqlClient Data Provider, at
> System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior,
> RunBehavior runBehavior, Boolean returnStream)
> Would anybody be able to tell me the cause of this exception?
> Thanks.|||Hi Dan,
The SQL command is being executed in the Query analyser and returns 0 rows.
Here is the SQL statement
select count(*) from testtab where UpdateDt = getdate()
Could there be any other reason for the exception?
Regards,
kd
"Dan Guzman" wrote:

> This error means that the SQL statement you are trying to execute is
> syntactically invalid. Try running the same SQL statement via Query
> Analyzer for debugging. Post the CommandText if you need more help.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
>
>|||kd wrote:
> Hi Dan,
> The SQL command is being executed in the Query analyser and returns 0
> rows. Here is the SQL statement
> select count(*) from testtab where UpdateDt = getdate()
You're not making any sense. This is a record-returning select statement. It
should always return a single record. In this case, the record will contain
a single field containing the value "0" if no rows in testtab meet the
criteria in the WHERE clause.

> Could there be any other reason for the exception?
Yes, you are using ExecuteNonQuery to execute a select statement that
returns records. Although I am curious: I do not see the character "1"
(mentioned in the error message) in this sql statement.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
I am sorry about that...its not the ExecuteNonQuery that is throwing an
exception, but, it is the ExecuteScalar!
kd
"Bob Barrows [MVP]" wrote:

> kd wrote:
> You're not making any sense. This is a record-returning select statement.
It
> should always return a single record. In this case, the record will contai
n
> a single field containing the value "0" if no rows in testtab meet the
> criteria in the WHERE clause.
>
> Yes, you are using ExecuteNonQuery to execute a select statement that
> returns records. Although I am curious: I do not see the character "1"
> (mentioned in the error message) in this sql statement.
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||So is your problem solved? If not, we're going to need to see some code
(although this would be better handled in one of the dotnet newsgroups)
Bob Barrows
kd wrote:
> Hi Bob,
> I am sorry about that...its not the ExecuteNonQuery that is throwing
> an exception, but, it is the ExecuteScalar!
> kd
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
The problem is solved. The following statement was the reason for the
exception to occur; though I can't understand why this statement cannot be
given.
SqlCommandObj.CommandText = CommandType.Text
When I commented the line, the exception did not occur when
SqlCommandObj.ExecuteScalar was executed.
Thanks for the assistance.
Regards,
kd.
"Bob Barrows [MVP]" wrote:

> So is your problem solved? If not, we're going to need to see some code
> (although this would be better handled in one of the dotnet newsgroups)
> Bob Barrows
> kd wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||kd wrote:
> Hi Bob,
> The problem is solved. The following statement was the reason for the
> exception to occur; though I can't understand why this statement
> cannot be given.
> SqlCommandObj.CommandText = CommandType.Text
What are you trying to do with that statement? CommandText is the text
for the command and CommandType is the type of command: SP, TableDirect,
or Text.
David Gugick
Imceda Software
www.imceda.com

Exception trying to import data from Excel .

Hello,
I am running SQL Management Studio and was trying to import excel data into
a table on my local 2005 database. When I choose the import task then choose
Excel as the data source I get this lovely message:
TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
The connection type "EXCEL" specified for connection manager
"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({06C927B9-F2F2-429B-B488-591883AE4655})
The connection type "EXCEL" specified for connection manager
"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({06C927B9-F2F2-429B-B488-591883AE4655})
I have searched the net (google and groups) and have come up with nothing.
Any ideas would be greatly appreciated.
Rich
Hello Rich,
It seems there is some issues in SSIS related components are not registered
properly. You may want to try the following:
Bring up a command prompt and
-- go to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
-- type below to unregister:
regsvr32.exe -u dtsconn.dll
--Then type below to register:
regsvr32.exe dtsconn.dll
If the issue persists, please ensure the Users (machinename\Users) have
Full Control on the
HKEY_CLASSES_ROOT\CLSID\.
In fact, you should go to PERMISSION-->ADVANCE
Ensure machinename\Users have full control permission.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY9aoUve4FjMD2oRuCRoGREQJdwgw==
>X-WBNR-Posting-Host: 143.166.226.16
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>Subject: Exception trying to import data from Excel .
>Date: Wed, 1 Mar 2006 11:58:33 -0800
>Lines: 39
>Message-ID: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29869
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello,
>I am running SQL Management Studio and was trying to import excel data
into
>a table on my local 2005 database. When I choose the import task then
choose
>Excel as the data source I get this lovely message:
>TITLE: SQL Server Import and Export Wizard
>--
>An error occurred which the SQL Server Integration Services Wizard was not
>prepared to handle.
>--
>ADDITIONAL INFORMATION:
>Exception has been thrown by the target of an invocation. (mscorlib)
>--
>The connection type "EXCEL" specified for connection manager
>"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({06C927B9-F2F2-429B-B488-591883AE4655})
>--
>The connection type "EXCEL" specified for connection manager
>"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({06C927B9-F2F2-429B-B488-591883AE4655})
>I have searched the net (google and groups) and have come up with nothing.

>Any ideas would be greatly appreciated.
>--
>Rich
>
|||Peter,
Thanks for the quick response. I tried what you said (unregister and
re-register) and it yeilded no results. I then applied the registry
permisison (while SQL Management studio was open and the error was on the
screen) to the CLSID folder (giving Users full control). I then tried the
operation again and got a message saying something to the effect of Server
Name Unknown (sorry I didnt think to write it down). So I closed the SQL
Management studio and re-opened it. When I tried again, I got a similar
message to the first time I tried but it had different CLSID's. I have
attached the message below.
One other thing to note, I noticed that I also do not have the drivers to be
able to read in such things as flat files. On a co-workers computer it
appears between the first .net dirvers and the media catalog drivers. I am
not sure if te two are related but I thought I would bring it up.
Lastly before, you replied today, I uninstalled SQL Server and the tools and
then re-installed. Still nothing.
Message follows:
TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
The connection type "EXCEL" specified for connection manager
"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({38BF22B4-3036-4BAB-9177-4820DA4EE187})
The connection type "EXCEL" specified for connection manager
"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({38BF22B4-3036-4BAB-9177-4820DA4EE187})
BUTTONS:
OK
Rich
"Peter Yang [MSFT]" wrote:

> Hello Rich,
> It seems there is some issues in SSIS related components are not registered
> properly. You may want to try the following:
> Bring up a command prompt and
> -- go to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> -- type below to unregister:
> regsvr32.exe -u dtsconn.dll
> --Then type below to register:
> regsvr32.exe dtsconn.dll
> If the issue persists, please ensure the Users (machinename\Users) have
> Full Control on the
> HKEY_CLASSES_ROOT\CLSID\.
> In fact, you should go to PERMISSION-->ADVANCE
> Ensure machinename\Users have full control permission.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> into
> choose
>
>
|||Hello Rich,
It seems that oledb related driver has issues on this server. I suggest
that you try the following steps:
1. Reinstall MDAC by right clicking %windir%\inf\mdac.inf->Install to
reinstall MDAC. You may prompt to insert Win2003 setup CD.
2. Reinstall Jet SP8 on your server:
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/default...b;en-us;239114
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY9rUgr1ynjYsVLQnuskA4GOXAWCw==
>X-WBNR-Posting-Host: 70.123.136.122
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>References: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
<53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exception trying to import data from Excel .
>Date: Wed, 1 Mar 2006 19:56:27 -0800
>Lines: 167
>Message-ID: <ADECD008-AC46-4800-B932-121744E9B779@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29881
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Peter,
>Thanks for the quick response. I tried what you said (unregister and
>re-register) and it yeilded no results. I then applied the registry
>permisison (while SQL Management studio was open and the error was on the
>screen) to the CLSID folder (giving Users full control). I then tried the
>operation again and got a message saying something to the effect of Server
>Name Unknown (sorry I didnt think to write it down). So I closed the SQL
>Management studio and re-opened it. When I tried again, I got a similar
>message to the first time I tried but it had different CLSID's. I have
>attached the message below.
>One other thing to note, I noticed that I also do not have the drivers to
be
>able to read in such things as flat files. On a co-workers computer it
>appears between the first .net dirvers and the media catalog drivers. I
am
>not sure if te two are related but I thought I would bring it up.
>Lastly before, you replied today, I uninstalled SQL Server and the tools
and[vbcol=seagreen]
>then re-installed. Still nothing.
>Message follows:
>TITLE: SQL Server Import and Export Wizard
>--
>An error occurred which the SQL Server Integration Services Wizard was not
>prepared to handle.
>--
>ADDITIONAL INFORMATION:
>Exception has been thrown by the target of an invocation. (mscorlib)
>--
>The connection type "EXCEL" specified for connection manager
>"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({38BF22B4-3036-4BAB-9177-4820DA4EE187})
>--
>The connection type "EXCEL" specified for connection manager
>"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({38BF22B4-3036-4BAB-9177-4820DA4EE187})
>--
>BUTTONS:
>OK
>--
>
>--
>Rich
>
>"Peter Yang [MSFT]" wrote:
registered[vbcol=seagreen]
rights.[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
to[vbcol=seagreen]
nothing.
>
|||Peter,
You are a genius. Thanks a million. I re-installed the MDAC inf and
everything started working again. Thank you so much. I have a task where I
REALLY needed to be able to import some xls/csv spreadsheets and was not
looking forward to having to write the code to do it myself.
Thanks again.
Rich
"Peter Yang [MSFT]" wrote:

> Hello Rich,
> It seems that oledb related driver has issues on this server. I suggest
> that you try the following steps:
> 1. Reinstall MDAC by right clicking %windir%\inf\mdac.inf->Install to
> reinstall MDAC. You may prompt to insert Win2003 setup CD.
> 2. Reinstall Jet SP8 on your server:
> 239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
> http://support.microsoft.com/default...b;en-us;239114
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> <53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
> be
> am
> and
> registered
> rights.
> not
> to
> to
> nothing.
>
|||Hello Rich,
Welcome! Great to hear the issue is resolved. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY+EDFvaKWB5xrzSHyd9a5fSbx9sA==
>X-WBNR-Posting-Host: 143.166.226.17
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>References: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
<53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
<ADECD008-AC46-4800-B932-121744E9B779@.microsoft.com>
<MZFTnFdPGHA.2528@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exception trying to import data from Excel .
>Date: Thu, 2 Mar 2006 07:44:29 -0800
>Lines: 247
>Message-ID: <FF059899-A260-49CC-85F8-134D3D7F75BA@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29886
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Peter,
>You are a genius. Thanks a million. I re-installed the MDAC inf and
>everything started working again. Thank you so much. I have a task where
I[vbcol=seagreen]
>REALLY needed to be able to import some xls/csv spreadsheets and was not
>looking forward to having to write the code to do it myself.
>Thanks again.
>--
>Rich
>
>"Peter Yang [MSFT]" wrote:
4.0[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
Server[vbcol=seagreen]
SQL[vbcol=seagreen]
similar[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
tools[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
to[vbcol=seagreen]
have[vbcol=seagreen]
so[vbcol=seagreen]
data[vbcol=seagreen]
then[vbcol=seagreen]
was[vbcol=seagreen]
valid[vbcol=seagreen]
made[vbcol=seagreen]
the[vbcol=seagreen]
valid[vbcol=seagreen]
made[vbcol=seagreen]
the
>

Exception thrown: database file cannot be found

Hi,

I'm developing a desktop C# app that uses SQL Everywhere as an embedded database.
I generated strongly typed DataSet and use that to populate a DataGrid on my app.

When the app first loads, it populates the DataGrid with a line like this:

this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);

That all works fine. Later on, after adding more data to the database (through reading a csv file), I wanted to refresh the display on the DataGrid.

I used the same line of code:

this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);

however, this time, the following exception was thrown:

The database file cannot be found. Check the path to the database. [ File name = .\\Inventories.sdf ]

Does anyone know what may be going on? I saw this article about a bug in VS 2005 when using strongly typed DataSets (http://channel9.msdn.com/wiki/default.aspx/MobileDeveloper.DatabaseCannotBeFoundErrorInTypedDataset)
but that doesn't seem to apply here.

The connection string is identical both times that line of code is called so I'm a bit baffled with what's going on.

Any help would be appreciated. Thanks,

Jose

Windows CE does not support relative paths you're trying to use. You must specify correct absolute path to the database located on device file system.

Also keep in mind Windows CE does not have drive letters and can't see your desktop's C: (D:, etc) drive as many developers seem to believe.

|||I'm not running this on Windows CE. I'm trying out the new SQL Server Everywhere and using it on Windows XP.

Thanks though.|||

If file can not be found that's probably because it can't be found. You can use File.Exists() to verify that.

|||Thanks for the input!!

It sounds so simple and yet I hadn't thought about that. I kept looking for the complicated answer.

It turns out, my database file was there all along. The problem was, after opening and reading the CSV files to import into the db, the next time I tried to access the db the app was looking for the db file in the same directory where my CSVs where...and of course, it wasn't finding it.

So now, after reading a CSV and prior to re-querying the db, I use Directory.SetCurrentDirectory() to reset where the app looks for its db.

Thanks for the help, I had been stumped by this for a week.

-Jose|||

this one has had me scratching my head for a day so I'm glad I haven't spent a week...

I'm using a separate project as a class library with a *.sdf database so it can be re-used for several projects.

I had the exact problem, everything was good until I opened OpenFileDialog(), then things went south after that.

Just a note to your solution... (maybe you do this already)...but you can assign the OpenFileDialog.RestoreDirectory flag to true and then after it closes the original directory will be restored prior to the open dialog....that way you don't need the Directory.SetCurrentDirectory.

fileChooser = new OpenFileDialog();

fileChooser.RestoreDirectory = true;

gl

Exception thrown: database file cannot be found

Hi,

I'm developing a desktop C# app that uses SQL Everywhere as an embedded database.
I generated strongly typed DataSet and use that to populate a DataGrid on my app.

When the app first loads, it populates the DataGrid with a line like this:

this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);

That all works fine. Later on, after adding more data to the database (through reading a csv file), I wanted to refresh the display on the DataGrid.

I used the same line of code:

this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);

however, this time, the following exception was thrown:

The database file cannot be found. Check the path to the database. [ File name = .\\Inventories.sdf ]

Does anyone know what may be going on? I saw this article about a bug in VS 2005 when using strongly typed DataSets (http://channel9.msdn.com/wiki/default.aspx/MobileDeveloper.DatabaseCannotBeFoundErrorInTypedDataset)
but that doesn't seem to apply here.

The connection string is identical both times that line of code is called so I'm a bit baffled with what's going on.

Any help would be appreciated. Thanks,

Jose

Windows CE does not support relative paths you're trying to use. You must specify correct absolute path to the database located on device file system.

Also keep in mind Windows CE does not have drive letters and can't see your desktop's C: (D:, etc) drive as many developers seem to believe.

|||I'm not running this on Windows CE. I'm trying out the new SQL Server Everywhere and using it on Windows XP.

Thanks though.

|||

If file can not be found that's probably because it can't be found. You can use File.Exists() to verify that.

|||Thanks for the input!!

It sounds so simple and yet I hadn't thought about that. I kept looking for the complicated answer.

It turns out, my database file was there all along. The problem was, after opening and reading the CSV files to import into the db, the next time I tried to access the db the app was looking for the db file in the same directory where my CSVs where...and of course, it wasn't finding it.

So now, after reading a CSV and prior to re-querying the db, I use Directory.SetCurrentDirectory() to reset where the app looks for its db.

Thanks for the help, I had been stumped by this for a week.

-Jose
|||

this one has had me scratching my head for a day so I'm glad I haven't spent a week...

I'm using a separate project as a class library with a *.sdf database so it can be re-used for several projects.

I had the exact problem, everything was good until I opened OpenFileDialog(), then things went south after that.

Just a note to your solution... (maybe you do this already)...but you can assign the OpenFileDialog.RestoreDirectory flag to true and then after it closes the original directory will be restored prior to the open dialog....that way you don't need the Directory.SetCurrentDirectory.

fileChooser = new OpenFileDialog();

fileChooser.RestoreDirectory = true;

gl

exception that does nothing?

Hi,
I want to have an exception do nothing. or rather, I just want my trigger to ignore this problem, terminate the current block, and go on to the next one.

here is my code:

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (''); -- do nothing.

currently, im getting the error below. I think it has to do with the DBMS overflowing

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139

thanks.never mind, i believe i found out that you could just put 'null' instead of having dbms output, and the block terminates succesfully, without doing anything.

Exception returned from reporting servicing

Hi, I'm got this unhandled exeption return from our reporting server and I don't know what it means or how I can ensure it doesn't happen again. We are using SQL Server 2005 and Reporting Services 2005 with .Net 2.0 (VS 2005) ASP.NET

any help appreciated

regards

Satvinder

Exception information: Exception typeoapException

Exception message: System.Web.Services.Protocols.SoapException: Execution 'l53cr2bl5ces523lsvet0s2y'
cannot be found > Microsoft.ReportingServices.Diagnostics.Utilities.ExecutionNotFoundException: Execution
'l53cr2bl5ces523lsvet0s2y' cannot be found
End of inner exception stack trace

at Microsoft.ReportingServices.WebServer.ReportExecutionService.GetExecutionInfo(ExecutionInfo_executionInfo)
Request information: Request URL: http://uat-WebSite/Reserved.ReportViewerWebControl.axd?ReportSession=l53cr2bl5ces523lsvet0s2y_ControlID=90412828-217e-4c47-a8b4-445adb99cf71_Culture=2057_UICulture=1033_ReportStack=1_OpType=SessionKeepAlive_Interval=454000

Request path: /Reserved.ReportViewerWebControl.axd User host address: 10.110.125.68
User: LONDON_UAT_SONASA Is authenticated: True Authentication Type: NTLM Thread
account name: LONDON_UAT Thread information: Thread ID: 10 Thread account
name: LONDON_UAT Is impersonating: False

Stack trace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage
message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2005.Execution.ReportExecutionService.GetExecutionInfo()
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.TouchSession()
at Microsoft.Reporting.WebForms.SessionKeepAliveOperation.PerformOperation(NameValueCollection
urlQuery, HttpResponse response)
at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean_ completedSynchronously)

Custom event details: "
App: E 'Mon Jun 04 16:58:34 2007': LONDONWEB - " An internal error occurred on the report server. See the
error log for more details. (rsInternalError) "
App: E 'Mon Jun 04 16:58:34 2007': LONDONWEB - " An internal error occurred on the report server. See the
error log for more details. (rsInternalError) "
Sys: E 'Mon Jun 04 16:58:37 2007': DCOM - " The application-specific permission settings do not grant
Local Activation permission for the COM Server application with CLSID {BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user LONDON_ABC_PROD_UAT SID (S-1-5-21-388395589-1927312763-1538882281-22140). This security
permission can be modified using the Component Services administrative tool. "

I'm not sure, but it could have something to do with the fact that in some cases when executing a report, the URL can contain an execution ID. The execution is specific to the session and user running the report. When the session times out, or the URL is copied or used by someone else, errors may appear indication that the execution is not valid.

The URL in the error message contains a Execution ID and so this URL is only valid for some time and only for the user who started the report.

Regards, Jeroen