EXECUTING PACKAGES from vb.net

Hello:
I have a web application from where I need to execute a DTS,
my platform is SQL 2000 and .net 2003. From a Vb.net module I want to
execute the package, my question is: does the package need to be STRUCTURED
STORAGE FILE or could it be SQL SERVER package (local to the server) in
order to be executed from .NET.
- If I can execute the local SQL package , how can I do it?
- If I can only execute STRUCTURED STORAGE FILE, how can I get ride off of
the multiple versions that are created everytime the package is opened and I
make any update on it.
ThanksGina Hernandez wrote:
> Hello:
> I have a web application from where I need to execute a DTS,
> my platform is SQL 2000 and .net 2003. From a Vb.net module I want
to
> execute the package, my question is: does the package need to be STRUCTUR
ED
> STORAGE FILE or could it be SQL SERVER package (local to the server) in
> order to be executed from .NET.
> - If I can execute the local SQL package , how can I do it?
> - If I can only execute STRUCTURED STORAGE FILE, how can I get ride off o
f
> the multiple versions that are created everytime the package is opened and
I
> make any update on it.
> Thanks
>
I assume you could go both ways, or you could use the dts com class in
.NET, without using a storage file or an sql package.
Steven|||> execute the package, my question is: does the package need to be
> STRUCTURED STORAGE FILE or could it be SQL SERVER package (local to the
> server) in order to be executed from .NET.
To expand on Steven's response, you can use the DtsPackage LoadFromSqlServer
or LoadFromStorageFile methods depending on the package location.

> how can I get ride off of the multiple versions that are created everytime
> the package is opened and I make any update on it.
If you delete the file before saving (or save to a new name and rename),
only the current version is saved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gina Hernandez" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uIHXLnlBGHA.3156@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I have a web application from where I need to execute a DTS,
> my platform is SQL 2000 and .net 2003. From a Vb.net module I want
> to execute the package, my question is: does the package need to be
> STRUCTURED STORAGE FILE or could it be SQL SERVER package (local to the
> server) in order to be executed from .NET.
> - If I can execute the local SQL package , how can I do it?
> - If I can only execute STRUCTURED STORAGE FILE, how can I get ride off
> of the multiple versions that are created everytime the package is opened
> and I make any update on it.
> Thanks
>

Executing Oracle Stored Procedure with output parameters using ADO.NET connection

I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DB\Microsoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:

[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.

Any help on this would be most appriciated.

hi Jason,

I got the same error. What driver are you using? I use .NET provider Oracle client data provider.

Can you also tell me how did you successfully invoke the stored proc using OLEDB for Oracle?

I have an sp with 1 input parameter of type Number and 14 output parameters some of type Number and some Varchar2.

Can you tell me the sql statement syntax to use and the parameter mapping to use. I know that the parameter name starts with 0,1,2...

Please help

thanks.

sandeep

sql

executing Insert SQLdatasource

Hey forum,

the .NET form controls make it extremely easy to manage database data, control + SQLdatasource et voilà, its there.

i have a web form, and a SQLdatasource with an insert query with parameters (works perfectly, only the Date picker Lite textbox ID as parameter wont register, but i have a thread runnin on their forums for that). when i open the Query builder and execute the query it works! woot! but now, how can i make it execute on a button press? i tried with several commands, like SQLdatasource1.execute, and several other commands i searched (can post later, not behind my test environment with my tools/webpage at the moment). so what is the command to let the SQLdatasource execute it's query? it's all i need to know to make several forms, please ghelp me! :)

gr. michael

bump|||

Hi,

According to your question, are you in trouble with executing insert operation by a button? If so, you may create the Button_Click event and useSqlDataSource.InsertCommand andSqlDataSource.Insert()to achieve your purpose.

For more details about the usage of SqlDataSource, pls refer the following link:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.aspx

Thanks.

sql

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel B1) Locate the job (under SQL Agent)
2) Right click on the job
3) Pick View Job History...
4) Check the Show Job Details box
5) Read the error message(s)

-PatP|||This is the error message i get.

The job failed. The Job was invoked by User REPEATSEAT\dberezniak. The last step to run was step 1 (DTS Execute App).

This doesn't tell me much. It blows up when trying to execute the app.
( The application is test and does work on its own and within the package. )|||Did you click on the "Show Job Details" box to be sure that it is checked? That message sounds like what you'd get if that box wasn't checked.

-PatPsql

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel BThis is really a question for the Microsoft SQL Server (http://www.dbforums.com/f7) forum. I'd be happy to answer the question there!

-PatP

Executing an string on MS SQL from .Net

Hi there,

I have this:

Dim strSQL As String = "SELECT * FROM GrupoRegistro WHERE Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 5) OR g.Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 3)"

How do I execute on .Net? I am using VWD2005 Express Edition and MS SQL 2000 server on a WinXp pro with MS Framework 2.0

I am using o trying and some variants :
SelectCommand="@.strsqlvar">
<SelectParameters>
<asp:QueryStringParameter Name="strsqlvar" DefaultValue="strsql.string" />
</SelectParameters>

And it doesn′t work... any help will e well apreciated. thank u in advance.

Lesson number "8" here might help:

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

Executing an string on MS SQL from .Net

Hi there,

I have this:

Dim strSQL As String = "SELECT * FROM GrupoRegistro WHERE Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 5) OR g.Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 3)"

How do I execute on .Net? I am using VWD2005 Express Edition and MS SQL 2000 server on a WinXp pro with MS Framework 2.0

I am using o trying and some variants :
SelectCommand="@.strsqlvar">
<SelectParameters>
<asp:QueryStringParameter Name="strsqlvar" DefaultValue="strsql.string" />
</SelectParameters>

And it doesn′t work... any help will e well apreciated. thank u in advance.

Lesson number "8" here might help:

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

sql

Executing a stored procedure that uses linked server from vb.NET

I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikaelbermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?
> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>> How long does it take to run in QA, and how long is your timeout set for
>> in .NET?
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> > I'm no .NET programmer, but with typical web apps you have a CONNECTION
> > timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> > So, in a nutshell, if your query takes longer than 30 seconds, the web
> > connection is going to give up and report a timeout.
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)

Executing a stored procedure that uses linked server from vb.NET

bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)|||I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikael|||bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M

Executing a SSIS Package from an ASP.NET page.

I am using the following code to try and execute a package from a asp.net page. The server has both SQL, SSIS, IIS and ASP.NET on it. The package runs fine from the SQL Management Studio. The Execute result from the web page is 'Failure'.

My questions are:
1. How do I catch errors to see exactly what is failing and why?
2. Is there a better way to execute a package using SSIS from asp.net?

Thanks,

Nathan

Sub ExecutePackage()
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

Dim testBool As Boolean = app.ExistsOnSqlServer("\\Import_Quotes", "povnet", "webapps", "password")
lblStatus.Text = testBool.ToString
pkg = app.LoadFromSqlServer("\\Import_Quotes", "povnet", "webapps", "password", Nothing)
pkgResults = pkg.Execute()

lblErrorOther.Text = pkgResults.ToString()
End Sub

> How do I catch errors to see exactly what is failing and why?

There are multiple ways:
1) Enable logging and configure package to log execution and error information to a file, event log or other log provider. This gives you a lot of information about package.
2) Implement IDtsEvents interface and supply it to Execute method. You'll get lots of information (in particular, all the error information) back.

The code seems fine.

By the way, the common problem with executing SSIS package from ASP.NET is user identify - the package is executed under account used by ASP.NET service, which might not have permissions to access all the data sources.|||Michael,

Can you help me with some code samples of implementing IDtsEvents and using it in the Execute method?

Also, I will check BOL but any info you can provide on enabling logging on a package would be nice.

Thanks for your quick response.

Nathan|||Implementing IDtsEvents is simple - subclass from Microsoft.SqlServer.Dts.Runtime.DefaultEvents class and override the methods corresponding to events you are interested in, most probably OnError. Pass instance of your class to Execute call, your OnError method will be called whenever an error occurs during execution.

To enable logging, right click the package main control flow in designer, select Package Configurations and follow the wizard.

Note - to edit configuration the package should be part of SSIS project, this currently does not work for packages edited "standalone" (unfortunately, this problem was found too late). I'm fixing it for SP1.|||

Here are some of the errors. I believe it is a permissions problem. I am importing an Excel spreadsheet to the database. The spreadsheet exists on the same server as the database. I have given NTFS permissions to NETWORK SERVICE to access the file. What other types of permissions do I need to grant the NETWORK SERVICE account?

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: The AcquireConnection method call to the connection manager "POVNET SQL Database" failed with error code 0xC0202009.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1071611876

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

AND

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1073450985

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

|||The message
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
indicates an error with SQL Server destination, not the Excel spreadsheet.

Also see the error the connection manager "POVNET SQL Database" - well, only you know where this connection manager points to.|||When I am in SQL Management Studio and run the Package, it runs without errors. I understand that the error is realted to the SQL Destination. When I execute this command from ASP.NET, which username is used for the operation?

Thanks,
Nathan|||

Do you use integrated NT authentication for SQL Destination? Then account ASP.NET is running under, usually Network Service (it authenticates as Domain\Computer$ to remote servers).

|||I can get my package to execute successfully now using ASP.NET.

Can you help me out on implementing IDTSEvents using asp.net and vb.net? I know you mentioned that it is simple, however, I am a beginner and am not sure how to tackle it.

I am looking to collect information on the results of the package like success or failure and also other things like varibles from the package like row count etc.

Thanks for your help on this.

Nathan|||

Microsoft.SqlServer.ManagedDTS assembly has a class DefaultEvents (in namespace Microsoft.SqlServer.Dts.Runtime). You just subclass this type and override the methods corresponding to the events you are interested in (e.g. OnError).

Then you pass an instance of your class to package.Execute method, like
package.Execute(null, null, myEvents, null);

|||Michael,
As you know, the word 'simple' is totally relative to a persons knowledge base. In my case at least, it is very limited in the program development arena so could you please direct me to documentation or an example of how to implement the event handler that you mentioned?

Thanks,
Mark.

Executing a SSIS Package from an ASP.NET page.

I am using the following code to try and execute a package from a asp.net page. The server has both SQL, SSIS, IIS and ASP.NET on it. The package runs fine from the SQL Management Studio. The Execute result from the web page is 'Failure'.

My questions are:
1. How do I catch errors to see exactly what is failing and why?
2. Is there a better way to execute a package using SSIS from asp.net?

Thanks,

Nathan

Sub ExecutePackage()
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

Dim testBool As Boolean = app.ExistsOnSqlServer("\\Import_Quotes", "povnet", "webapps", "password")
lblStatus.Text = testBool.ToString
pkg = app.LoadFromSqlServer("\\Import_Quotes", "povnet", "webapps", "password", Nothing)
pkgResults = pkg.Execute()

lblErrorOther.Text = pkgResults.ToString()
End Sub

> How do I catch errors to see exactly what is failing and why?

There are multiple ways:
1) Enable logging and configure package to log execution and error information to a file, event log or other log provider. This gives you a lot of information about package.
2) Implement IDtsEvents interface and supply it to Execute method. You'll get lots of information (in particular, all the error information) back.

The code seems fine.

By the way, the common problem with executing SSIS package from ASP.NET is user identify - the package is executed under account used by ASP.NET service, which might not have permissions to access all the data sources.|||Michael,

Can you help me with some code samples of implementing IDtsEvents and using it in the Execute method?

Also, I will check BOL but any info you can provide on enabling logging on a package would be nice.

Thanks for your quick response.

Nathan|||Implementing IDtsEvents is simple - subclass from Microsoft.SqlServer.Dts.Runtime.DefaultEvents class and override the methods corresponding to events you are interested in, most probably OnError. Pass instance of your class to Execute call, your OnError method will be called whenever an error occurs during execution.

To enable logging, right click the package main control flow in designer, select Package Configurations and follow the wizard.

Note - to edit configuration the package should be part of SSIS project, this currently does not work for packages edited "standalone" (unfortunately, this problem was found too late). I'm fixing it for SP1.|||

Here are some of the errors. I believe it is a permissions problem. I am importing an Excel spreadsheet to the database. The spreadsheet exists on the same server as the database. I have given NTFS permissions to NETWORK SERVICE to access the file. What other types of permissions do I need to grant the NETWORK SERVICE account?

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: The AcquireConnection method call to the connection manager "POVNET SQL Database" failed with error code 0xC0202009.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1071611876

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

AND

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1073450985

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

|||The message
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
indicates an error with SQL Server destination, not the Excel spreadsheet.

Also see the error the connection manager "POVNET SQL Database" - well, only you know where this connection manager points to.|||When I am in SQL Management Studio and run the Package, it runs without errors. I understand that the error is realted to the SQL Destination. When I execute this command from ASP.NET, which username is used for the operation?

Thanks,
Nathan|||

Do you use integrated NT authentication for SQL Destination? Then account ASP.NET is running under, usually Network Service (it authenticates as Domain\Computer$ to remote servers).

|||I can get my package to execute successfully now using ASP.NET.

Can you help me out on implementing IDTSEvents using asp.net and vb.net? I know you mentioned that it is simple, however, I am a beginner and am not sure how to tackle it.

I am looking to collect information on the results of the package like success or failure and also other things like varibles from the package like row count etc.

Thanks for your help on this.

Nathan|||

Microsoft.SqlServer.ManagedDTS assembly has a class DefaultEvents (in namespace Microsoft.SqlServer.Dts.Runtime). You just subclass this type and override the methods corresponding to the events you are interested in (e.g. OnError).

Then you pass an instance of your class to package.Execute method, like
package.Execute(null, null, myEvents, null);

|||Michael,
As you know, the word 'simple' is totally relative to a persons knowledge base. In my case at least, it is very limited in the program development arena so could you please direct me to documentation or an example of how to implement the event handler that you mentioned?

Thanks,
Mark.

Executing a SSIS Package from an ASP.NET page.

I am using the following code to try and execute a package from a asp.net page. The server has both SQL, SSIS, IIS and ASP.NET on it. The package runs fine from the SQL Management Studio. The Execute result from the web page is 'Failure'.

My questions are:
1. How do I catch errors to see exactly what is failing and why?
2. Is there a better way to execute a package using SSIS from asp.net?

Thanks,

Nathan

Sub ExecutePackage()
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

Dim testBool As Boolean = app.ExistsOnSqlServer("\\Import_Quotes", "povnet", "webapps", "password")
lblStatus.Text = testBool.ToString
pkg = app.LoadFromSqlServer("\\Import_Quotes", "povnet", "webapps", "password", Nothing)
pkgResults = pkg.Execute()

lblErrorOther.Text = pkgResults.ToString()
End Sub

> How do I catch errors to see exactly what is failing and why?

There are multiple ways:
1) Enable logging and configure package to log execution and error information to a file, event log or other log provider. This gives you a lot of information about package.
2) Implement IDtsEvents interface and supply it to Execute method. You'll get lots of information (in particular, all the error information) back.

The code seems fine.

By the way, the common problem with executing SSIS package from ASP.NET is user identify - the package is executed under account used by ASP.NET service, which might not have permissions to access all the data sources.|||Michael,

Can you help me with some code samples of implementing IDtsEvents and using it in the Execute method?

Also, I will check BOL but any info you can provide on enabling logging on a package would be nice.

Thanks for your quick response.

Nathan|||Implementing IDtsEvents is simple - subclass from Microsoft.SqlServer.Dts.Runtime.DefaultEvents class and override the methods corresponding to events you are interested in, most probably OnError. Pass instance of your class to Execute call, your OnError method will be called whenever an error occurs during execution.

To enable logging, right click the package main control flow in designer, select Package Configurations and follow the wizard.

Note - to edit configuration the package should be part of SSIS project, this currently does not work for packages edited "standalone" (unfortunately, this problem was found too late). I'm fixing it for SP1.|||

Here are some of the errors. I believe it is a permissions problem. I am importing an Excel spreadsheet to the database. The spreadsheet exists on the same server as the database. I have given NTFS permissions to NETWORK SERVICE to access the file. What other types of permissions do I need to grant the NETWORK SERVICE account?

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: The AcquireConnection method call to the connection manager "POVNET SQL Database" failed with error code 0xC0202009.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1071611876

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

AND

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1073450985

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.|||The message
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
indicates an error with SQL Server destination, not the Excel spreadsheet.

Also see the error the connection manager "POVNET SQL Database" - well, only you know where this connection manager points to.|||When I am in SQL Management Studio and run the Package, it runs without errors. I understand that the error is realted to the SQL Destination. When I execute this command from ASP.NET, which username is used for the operation?

Thanks,
Nathan|||

Do you use integrated NT authentication for SQL Destination? Then account ASP.NET is running under, usually Network Service (it authenticates as Domain\Computer$ to remote servers).

|||I can get my package to execute successfully now using ASP.NET.

Can you help me out on implementing IDTSEvents using asp.net and vb.net? I know you mentioned that it is simple, however, I am a beginner and am not sure how to tackle it.

I am looking to collect information on the results of the package like success or failure and also other things like varibles from the package like row count etc.

Thanks for your help on this.

Nathan|||

Microsoft.SqlServer.ManagedDTS assembly has a class DefaultEvents (in namespace Microsoft.SqlServer.Dts.Runtime). You just subclass this type and override the methods corresponding to the events you are interested in (e.g. OnError).

Then you pass an instance of your class to package.Execute method, like
package.Execute(null, null, myEvents, null);

|||Michael,
As you know, the word 'simple' is totally relative to a persons knowledge base. In my case at least, it is very limited in the program development arena so could you please direct me to documentation or an example of how to implement the event handler that you mentioned?

Thanks,
Mark.

Executing a SQL Script with MSDE

Hello,

I am learning about .NET apps and working with the ASP.NET Unleashed book. The project I am working on says to execute the SQL script using the SQL Query Analyzer, which I assume is available with Enterprise Server.

Could anyone guide me through the process of executing a SQL Script with the MSDE? Or point me in the right directions (white papers, etc)

Much appreciated

MPYou can use OSQL which is a command line tool that comes with MSDE.

This should get you started|||Thanks, that should get me started on something. I appreciate the prompt reply.

MP

Executing a script and returning the results..

You know how osql can return the results of executing a .sql script to a window? Is there any way to achieve that same behavior in .NET? I am in the process of working on an application that needs to execute a .SQL script (which is easy to do) and then return the actual SQL output from the execution to a window. How do I get that output? None of the sql data objects seem to support this.I actually figured this one out. I can launch a process (like the command prompt) and then return the output through a stream.

Executing .sql file using in .NET

Hello,

I want to execute .sql files against my database in c#.

please Help me.

Hi,

funny thing, I posted that this week in the SMO group:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=318250&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hello, Jens

Yes i know that you have posted that .

but i have replied it problem of that code

bcoz i had written same kind of code.

http://forums.microsoft.com/MSDN/showpost.aspx?postid=323013&siteid=1

Uday

|||Do you want to execute more than one file in a directory ? Or what do you want to achieve ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I assume this is the problem you are referring to:

"Code gives error where GO command is there in script;".

A SqlCommand string cannot contain the "GO" command. If you remove the lines containing the "GO" commands you should be fine.

|||

Thanks peter for reply

I have already tried script removeing "GO" but still it is not working.

actually i have generated script files by system(SQL Sever Enterprise Manager) itself.

|||

Jens Thanks for Reply

I have .sql file generated by SQL Server Enterprise manager.

i want to execute this file. but it does not.

Then i have generate .sql file for each object separately.

now file table.sql (for tables only) runs correctly,

but for view (view.sql) doesnot run properly.

in all case error is same

"Create view must be first line in Query Batch."

Now what to do ?

Waiting for Your Reply

Emersion

|||

Ok, did you try to serparate the string object you are getting back from reading from the file by splitting it into a string array and executing each string in the string array ?

Thats the think which comes up in mind for this problem.

Jens.

|||

Or you can split the file .sql file into multiple files at the lines where the "GO" commands were originally.

ExecuteXMLReader fails with valid xml from sql server. Why?

(From a .net class using sqlcommand)
A stored proc that takes 2 input params is firing on the server (i've
checked using sql profiler). The sp in question produces valid xml (i've
checked by creating a sqlxml virutal dir and allowing url queries - the
result is valid in IE and therefore well formed xml)
The stored proc in question is not a single "select X for xml ..." query. It
is made up of fragments
example of results
<root>
<subnode>
select x ... for xml ...
</subnode>
<subnode>
select x ... for xml ...
</subnode>
...
</root>
Why won't ExecuteXmlReader work with the results?
XR = CMD.ExecuteXmlReader()
(where xr is an xml reader)
this line comes back with the error
"Invalid command sent to ExecuteXmlReader. The command must return an Xml
result."
The command is not invalid! I can see it executing on sql. The result is
valid xml.
I have also tried using the sqlxmlcommand but it claimed I was not passing
parameters in (which I was) so I gave up on that.
I am beginning to hate .net
The concept is great but there is too much pain
trying to connect things together...
Any ideas...?
CODE--
Dim StartDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
Dim EndDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
CMD = New SqlCommand
CMD.Connection = CN
Select Case SubType
Case Nothing
CMD.CommandText = "XXXXX"
CMD.CommandType = CommandType.StoredProcedure
Dim Param As New SqlParameter
CN.Open()
Try
Param.ParameterName = "@.start_date" : Param.Value =
StartDate : CMD.Parameters.Add(Param)
Param = New SqlParameter
Param.ParameterName = "@.end_date" : Param.Value =
EndDate : CMD.Parameters.Add(Param)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
Case Else
'do something
End Select
'--
Try
XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
* * * *
ResultsDoc = New XmlDocument
ResultsDoc.Load(XR)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
END CODE--
In your description, you create a command like this:

> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
Where did you do such construction? What type of variable or command are you
using?
One easy solution on Yukon is that you can try is store the result in a
"xml" type variable, and then select from it.
For the ExecuteXmlReader() expecting an "XML" typed data back, instead of
any string that looks like "XML".
Thanks.
Xin
"adolf garlic" wrote:

> (From a .net class using sqlcommand)
> A stored proc that takes 2 input params is firing on the server (i've
> checked using sql profiler). The sp in question produces valid xml (i've
> checked by creating a sqlxml virutal dir and allowing url queries - the
> result is valid in IE and therefore well formed xml)
> The stored proc in question is not a single "select X for xml ..." query. It
> is made up of fragments
> example of results
> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
> Why won't ExecuteXmlReader work with the results?
> XR = CMD.ExecuteXmlReader()
> (where xr is an xml reader)
> this line comes back with the error
> "Invalid command sent to ExecuteXmlReader. The command must return an Xml
> result."
> The command is not invalid! I can see it executing on sql. The result is
> valid xml.
> I have also tried using the sqlxmlcommand but it claimed I was not passing
> parameters in (which I was) so I gave up on that.
> I am beginning to hate .net
> The concept is great but there is too much pain
> trying to connect things together...
> Any ideas...?
>
> CODE--
>
> Dim StartDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
> Dim EndDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
> CMD = New SqlCommand
> CMD.Connection = CN
> Select Case SubType
> Case Nothing
> CMD.CommandText = "XXXXX"
> CMD.CommandType = CommandType.StoredProcedure
> Dim Param As New SqlParameter
> CN.Open()
> Try
> Param.ParameterName = "@.start_date" : Param.Value =
> StartDate : CMD.Parameters.Add(Param)
> Param = New SqlParameter
> Param.ParameterName = "@.end_date" : Param.Value =
> EndDate : CMD.Parameters.Add(Param)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
> Case Else
> 'do something
> End Select
> '--
> Try
> XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
> * * * *
> ResultsDoc = New XmlDocument
> ResultsDoc.Load(XR)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
>
> END CODE--

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
..net framework - 1.0.3705
Thanks,
Scott
Hi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about the
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the[vbcol=seagreen]
|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode-532459699int
StackTrace" at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManaged Interface.ExecuteToOutputStream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(S tream strm)"string
Scott Walters wrote:[vbcol=seagreen]
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fake
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote:
sql

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
.net framework - 1.0.3705
Thanks,
ScottHi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about t
he
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode -532459699 int
StackTrace " at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManagedInterface.ExecuteToOutputS
tream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(Stream strm)" string
Scott Walters wrote:
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
>|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fa
ke
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote:

ExecuteScalar --> How To Get the OrderID(Identity) from a table to another Table ?

I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??

Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick
' Insert the order and order lines into the database
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand

Try
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans

' set the order details
cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total)"
cmd.Parameters.Add("@.MemberName", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.OrderDate", Data.SqlDbType.DateTime)
cmd.Parameters.Add("@.Name", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Address", Data.SqlDbType.VarChar, 255)
cmd.Parameters.Add("@.City", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.State", SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.PostCode", Data.SqlDbType.VarChar, 15)
cmd.Parameters.Add("@.Country", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Total", Data.SqlDbType.Money)

cmd.Parameters("@.MemberName").Value = User.Identity.Name
cmd.Parameters("@.OrderDate").Value = DateTime.Now()
cmd.Parameters("@.Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text
cmd.Parameters("@.Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text
cmd.Parameters("@.City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text
cmd.Parameters("@.State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text
cmd.Parameters("@.PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text
cmd.Parameters("@.Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text
cmd.Parameters("@.Total").Value = Profile.Basket.Total

Dim OrderID As Integer
OrderID = Convert.ToInt32(cmd.ExecuteScalar()) <-- Is it wrong or need to add wat ?
' change the query and parameters for the order lines
cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@.OrderID, @.ProductID, @.Quantity, @.Price)"
cmd.Parameters.Clear()
cmd.Parameters.Add("@.OrderID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.ProductID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Quantity", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Price", Data.SqlDbType.Money)
cmd.Parameters("@.OrderID").Value = OrderID

For Each item As CartItem In Profile.Basket.Items
cmd.Parameters("@.ProductID").Value = item.ProductID
cmd.Parameters("@.Quantity").Value = item.Quantity
cmd.Parameters("@.Price").Value = item.UnitPrice
cmd.ExecuteNonQuery()
Next
' commit the transaction
trans.Commit()
Catch SqlEx As SqlException
' some form of error - rollback the transaction
' and rethrow the exception
If trans IsNot Nothing Then
trans.Rollback()
End If
' Log the exception
Throw

Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
' we will only reach here if the order has been created successfully
' so clear the cart
Profile.Basket.Items.Clear()
End Sub

Hi, there is no SELECT query in your SQL statement, so ExecuteScalar has nothing to return. Try this instead:

' set the order details
cmd.CommandText = "INSERT INTOOrders(MemberName, OrderDate, Name, Address, City, State, PostCode,Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address,@.City,@.State, @.PostCode, @.Country, @.Total; SELECT SCOPE_IDENTITY())"

|||

Thanks tmorton for your reply.. i try with ur suggested code can return an error

Incorrect syntax near ';'.
Incorrect syntax near ')'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near ';'.
Incorrect syntax near ')'.

Line 84: End If
Line 85: ' Log the exception
Line 86: Throw
Line 87:
Line 88: Finally

I am noob for this case, is it other way to retrieve the new data identity ?

|||cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"|||

Motley wrote:

cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"

Thanks for fixing my keying error, Motley :-)

ExecuteRow - Missing Definition

Hello,

I'm using Visual Studio 2005 with ADO.Net 2.0 but I am missing a definition for the sqlcommands' object "ExecuteRow"

oCommand =new System.Data.SqlClient.SqlCommand();

oCommand.ExecuteRow() ---------> this is the missing definition

Any ideas anyone ?

The SqlCommand class has no ExecuteRow method. Seethis thread.

What are you trying to do?

Don

|||

well I'm actually just running some tests (I'm currently migrating from 1.1 to 2.0)

I have a book by WROX which specifies the definitions for the command object (See attached image), it contains

a definition for ExecuteRow

|||

ItayZ:

I have a book by WROX which specifies the definitions for the command object (See attached image), it contains

a definition for ExecuteRow

Looks like you need a better book. ExecuteRow was dropped between Beta 1 and Beta 2.

|||

Geez, I hate day and date books. They inevitably have stuff wrong, since they have to go to press months before the final release. And I'm afraid that I've written a few. But Never Again.

A better book is indeed in order, but in the meantime you might check the Wrox Web site for an errata sheet.

Don