Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Are possible errors caught within the procedure or your vb code ? How do you know that the procedure is not executed successfully if you get no additional error and why do you think it stops ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Quote:

Originally Posted by SQLusername

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.|||

Quote:

Originally Posted by ck9663

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.

Can you descibe what object locking is and how to remedy it? Also using SQL Server Enterprise Manager where can I edit time-out settings?

Executing sql scripts from aspx....plz Help!

Hi All,
How to execute the sql scripts (which may be in .sql file or a string) from code behind files?

Thanx
Veeru.SqlCommand.ExecuteNonQuery()

The only problem I can think of is that .SQL scripts generated by Query Analyzer often have the 'GO' keyword in them, which is not a SQL keyword, but rather used for batching in QA.|||True, going to have to parse out GO statement. Some more things needed

SqlCommand comm = New SqlCommand("select * from table", sqlConnection)
comm.CommandType = CommandType.Textsql

executing sp_changeownerdb

Hi,
I've a problem executing sp_changedbowner in my stored procedure which
is saved in the master's stored procedures.
The code fails after executing @.proc1. The errors says that i could not
find proc2.
Here's the code:
CREATE PROC usp_RestoreDB(
@.db_name varchar(10),
@.backup_location varchar(255),
@.login varchar(20)
)
AS
DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
SET @.proc1 = @.db_name + '..sp_fixusers'
SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
RESTORE DATABASE @.db_name
FROM DISK = @.backup_location
WITH REPLACE
Begin
EXEC @.proc1
EXEC @.proc2
ENDTry,
...
exec (@.proc1)
exec (@.proc2)
...
AMB
"Jason" wrote:

> Hi,
> I've a problem executing sp_changedbowner in my stored procedure which
> is saved in the master's stored procedures.
> The code fails after executing @.proc1. The errors says that i could not
> find proc2.
> Here's the code:
> CREATE PROC usp_RestoreDB(
> @.db_name varchar(10),
> @.backup_location varchar(255),
> @.login varchar(20)
> )
> AS
> DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
> SET @.proc1 = @.db_name + '..sp_fixusers'
> SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
> RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
> Begin
> EXEC @.proc1
> EXEC @.proc2
> END
>|||On Thu, 29 Sep 2005 16:21:18 +0200, Jason wrote:

>Hi,
>I've a problem executing sp_changedbowner in my stored procedure which
>is saved in the master's stored procedures.
>The code fails after executing @.proc1. The errors says that i could not
>find proc2.
>Here's the code:
>CREATE PROC usp_RestoreDB(
>@.db_name varchar(10),
>@.backup_location varchar(255),
>@.login varchar(20)
> )
>AS
>DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
>SET @.proc1 = @.db_name + '..sp_fixusers'
>SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
>RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
>Begin
>EXEC @.proc1
>EXEC @.proc2
>END
Hi Jason,
Try changing the logic for proc2 to
(...)
SET @.proc2 = @.db_name + '..sp_changedbowner'
(...)
EXEC @.proc2 @.loginame = @.login
(...)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

executing multiple query at one time

hi,

i am making a n application which in between deletes the multiple tables from the sql database.

for that i have written the following code:

SqlCommand cmd = newSqlCommand();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

in this way, many more tables are to be deleted.

is there any need to create the new SQLCommand object again an\d agin for each and every query. can iot be done like the given above or can there be some better method?

thanz in advance..

divya

What about creating a static method, passing in the connection and the things that can change in the query, composing query query within the method and executing this thing asynchronously ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Executing code on publisher after replication completes

I am using anonymous merge "pull" subscriptions to synchronize a
SqlServerCE database and a SQL 2000 database. The handheld (PocketPC)
users are entering data, which is replicated to the SQL 2000 database.
This is all working as expected.
The only thing I am missing is the ability in SQL 2000 to detect when
one of the handheld/subscribers has replicated. In particular, the SQL
2000 database needs to run code (preferably a stored procedure) when a
subscriber has finished replicating. I do not know how to detect when
replication is complete -- how do I do that?
Thanks,
Rod Early
Why can't you have a statement after the sync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rod" <REarly@.jswcoinc.com> wrote in message
news:1132605090.860477.266420@.o13g2000cwo.googlegr oups.com...
>I am using anonymous merge "pull" subscriptions to synchronize a
> SqlServerCE database and a SQL 2000 database. The handheld (PocketPC)
> users are entering data, which is replicated to the SQL 2000 database.
> This is all working as expected.
> The only thing I am missing is the ability in SQL 2000 to detect when
> one of the handheld/subscribers has replicated. In particular, the SQL
> 2000 database needs to run code (preferably a stored procedure) when a
> subscriber has finished replicating. I do not know how to detect when
> replication is complete -- how do I do that?
> Thanks,
> Rod Early
>
|||Thanks Hilary. For clarification, the code needs to run on the
publisher, the SQL 2000 database. I already have some code that runs
after sync on the handheld.
The main reason for needing to run code after sync on the publisher is
to update the a few "date received" columns so there is a record of
when the data was actually received at the server, as opposed to when
it was entered on the handheld.
Certainly on the handheld I could run code to update the "date
received" columns prior to sync. But two issues come to mind -- if the
sync fails for any reason, "date received" won't be correct because the
server wouldn't have actually received the rows. Secondly, the dates
in those fields would be the handheld's date/time (which would vary per
device because of time zone and how the device clock is set), rather
than when it was received at the server -- regardless of the handheld's
clock.
Thanks,
Rod
|||You may be able to use triggers as an option. These will only run at the
server of course.
However, you may want to consider whether you like the idea of updating a
table that has just been synchronised. Any update will cause it to be
transfered back to the device in it's newly updated state. Unless of source
you are using a reference table to point back at the changed data.
"Rod" wrote:

> Thanks Hilary. For clarification, the code needs to run on the
> publisher, the SQL 2000 database. I already have some code that runs
> after sync on the handheld.
> The main reason for needing to run code after sync on the publisher is
> to update the a few "date received" columns so there is a record of
> when the data was actually received at the server, as opposed to when
> it was entered on the handheld.
> Certainly on the handheld I could run code to update the "date
> received" columns prior to sync. But two issues come to mind -- if the
> sync fails for any reason, "date received" won't be correct because the
> server wouldn't have actually received the rows. Secondly, the dates
> in those fields would be the handheld's date/time (which would vary per
> device because of time zone and how the device clock is set), rather
> than when it was received at the server -- regardless of the handheld's
> clock.
> Thanks,
> Rod
>

Executing BCP from C# code

Aloha to all,
I have thrown together some code that generates a file with a lot of
data (something like 200,000 rows). I would like to load all this data
in a SQL Server table, but my attempt to execute BCP from C# doesn't
want to behave.
I do as follows (more of less stealing everything from
http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
):
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.FileName = "bcp";
proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
-Sservername";
proc.EnableRaisingEvents = true;
proc.Start();
string s1 = proc.StandardOutput.ReadLine();
string s2 = proc.StandardOutput.ReadLine();
All I get from this code is:
s1 = "SQLState = 28000, NativeError = 18456";
s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
for user 'username'."
I have off course checked the username/password a thousand times and it
is correct. If I copy my Arguments string to the command prompt, I can
execute BCP without any problem
Thank you and roger over, MadsYou could try creating a batch file and then run that. It seems like it's
having trouble putting the arguments in correctly. I don't have the time
right now to try compiling and running your code... but I'm sure you could
easily throw the whole line into a file and running it.
Security-wise though, I'd consider using a trusted connection and
impersonating a particular user.
Unfortunately my timezone means I'm about to go offline for a while, so I
can't post a follow-up. Someone else will though I'm sure (and they'll
probably tell you to ignore me!)
Rob
"Mads.phi@.gmail.com" wrote:

> Aloha to all,
> I have thrown together some code that generates a file with a lot of
> data (something like 200,000 rows). I would like to load all this data
> in a SQL Server table, but my attempt to execute BCP from C# doesn't
> want to behave.
> I do as follows (more of less stealing everything from
> http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
> ):
> System.Diagnostics.Process proc = new System.Diagnostics.Process();
> proc.EnableRaisingEvents = false;
> proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
> proc.StartInfo.UseShellExecute = false;
> proc.StartInfo.RedirectStandardOutput = true;
> proc.StartInfo.RedirectStandardError = true;
> proc.StartInfo.FileName = "bcp";
> proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
> c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
> -Sservername";
> proc.EnableRaisingEvents = true;
> proc.Start();
> string s1 = proc.StandardOutput.ReadLine();
> string s2 = proc.StandardOutput.ReadLine();
> All I get from this code is:
> s1 = "SQLState = 28000, NativeError = 18456";
> s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
> for user 'username'."
> I have off course checked the username/password a thousand times and it
> is correct. If I copy my Arguments string to the command prompt, I can
> execute BCP without any problem
> Thank you and roger over, Mads
>|||Excellent! That fixed my problems, so the only thing left is that I'm
ashamed of not having thought of a .bat file myself.
Thank you, Mads|||About the C# code, in general I'd change
proc.StandardOutput.ReadLine();
to
proc.StandardOutput.ReadToEnd();
to grab all the error message for troubleshooting purposes. Of course, this
doesn't address your particular problem.
Linchi
"Mads.phi@.gmail.com" wrote:

> Excellent! That fixed my problems, so the only thing left is that I'm
> ashamed of not having thought of a .bat file myself.
> Thank you, Mads
>

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 command inside a read loop

How can I execute an SQL command inside a read loop? My code looks something
like this:
With cmd1.ExecuteReader
Do While .Read
cmd2.CommandText = "..."
Name = CStr(cmd2.ExecuteScalar)
Loop
.Close
End With
But it fails at the ExecuteScalar method, with an invalid operation
exception.ADO.NET 1 doesn't allow sending a query against a connection that you are cu
rrently using. Either
have a separate connection for cmd2, or cache the result from cmd1 immediate
and loop that cached
result.
Or, use ADO.NET 2.0, and read up on "MARS" (multiple active resultsets). You
need to set this
attribute in the connection string.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Philip Sheard" <sheardp@.myisp.com> wrote in message news:Ois4sTHZGHA.3444@.TK2MSFTNGP05.phx
.gbl...
> How can I execute an SQL command inside a read loop? My code looks somethi
ng like this:
> With cmd1.ExecuteReader
> Do While .Read
> cmd2.CommandText = "..."
> Name = CStr(cmd2.ExecuteScalar)
> Loop
> .Close
> End With
> But it fails at the ExecuteScalar method, with an invalid operation except
ion.
>|||(I know this isn't the answer that you want to hear)
I would highly recommend looking into how your query is written. You
shouldn't have to loop through things like this. If you're trying to
append a column to the result set of a stored procedure, consider using
the INSERT EXEC syntax:
CREATE TABLE #ResultSet
(
:: <column-list>
)
INSERT #ResultSet
EXEC <procedure_name>
or, if the returned column-list is nondeterministic: SELECT a.*,
b.ColumnName FROM OPENQUERY('...', 'EXEC <procedure-name>') a,
<other_table> b
-Alan
Philip Sheard wrote:
> How can I execute an SQL command inside a read loop? My code looks somethi
ng
> like this:
> With cmd1.ExecuteReader
> Do While .Read
> cmd2.CommandText = "..."
> Name = CStr(cmd2.ExecuteScalar)
> Loop
> .Close
> End With
> But it fails at the ExecuteScalar method, with an invalid operation
> exception.|||Philip Sheard (sheardp@.myisp.com) writes:
> How can I execute an SQL command inside a read loop? My code looks
> something like this:
> With cmd1.ExecuteReader
> Do While .Read
> cmd2.CommandText = "..."
> Name = CStr(cmd2.ExecuteScalar)
> Loop
> .Close
> End With
> But it fails at the ExecuteScalar method, with an invalid operation
> exception.
If you are on SQL 2005 and use ADO .Net 2.0, you can enable MARS.
However, as Alan points out, you are probably barking up the wrong
tree. Calling back to the server for every row you get, does not
bode well for performance. You should try to get that scalar with
the first result set.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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:

ExecuteWithResultsAndMessages2, syntax problem?

Here is a code snippet that I am using from Ken's sp_run_xml_proc.
I want to modify it, such that when I invoke sp_run_xml_proc I want to pass
a SP and a parameter. Something like EXEC sp_run_xml_proc 'get_xml_LAC',
@.parameter. This @.parameter should be used as a parameter to get_xml_LAC
stored proc. Is it possible with 'ExecuteWithResultsAndMessages2'? If yes,
then can you direct me how I can fix it? BOL hasn't been much help.
EXEC @.hr = sp_OAMethod @.object, 'ExecuteWithResultsAndMessages2',
@.results OUT, @.procname, @.msgs OUT, @.parameter
IF @.hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
ENDHi
Have you tried adding the value to the procedure name?
set @.procname = @.procname + ' ' + @.parameter
John
"S" wrote:

> Here is a code snippet that I am using from Ken's sp_run_xml_proc.
> I want to modify it, such that when I invoke sp_run_xml_proc I want to pas
s
> a SP and a parameter. Something like EXEC sp_run_xml_proc 'get_xml_LAC',
> @.parameter. This @.parameter should be used as a parameter to get_xml_LAC
> stored proc. Is it possible with 'ExecuteWithResultsAndMessages2'? If yes,
> then can you direct me how I can fix it? BOL hasn't been much help.
> EXEC @.hr = sp_OAMethod @.object, 'ExecuteWithResultsAndMessages2',
> @.results OUT, @.procname, @.msgs OUT, @.parameter
> IF @.hr <> 0 BEGIN
> EXEC sp_displayoaerrorinfo @.object, @.hr
> RETURN
> END|||Worked like a charm. Appreciate it.
"John Bell" wrote:
> Hi
> Have you tried adding the value to the procedure name?
> set @.procname = @.procname + ' ' + @.parameter
> John
> "S" wrote:
>

ExecuteScalar() Not Returning Value?

Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.

I noticed that this chunk of code is not producing a value...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%?CurrentPage%') AND Enabled=1", Conn)With Cmd.Parameters.Add(New MySqlParameter("?CurrentPage",thisPage))End WithConn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog.

SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1

This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself.

Does anybody have any ideas?

Try

WHERE (PermissionFiles LIKE'%' + ?CurrentPage + '%')

Jos

|||

That didn't give me the desired result, either. It started returning "every" row that met all criteria but theCurrentPage.

But, thanks to your suggestion, what I ended up with was...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%" & thisPage & "%') AND Enabled=1 AND Everybody=0", Conn)Conn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Which works "as intended". Thanks!|||

execute scalar returns firts column from firts row of returned data so use this:

SELECT (SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

you can also use:

ifexists(SELECT *FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

select 1

else

select 0

which can work faster if you have more than one record whcih meet your criteria

I hope that it will work

sql

ExecuteScalar returns null

I am using the following C# code and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.

-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);

-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;

select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId

if @.@.rowcount <0
begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))

-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached

if @.@.rowcount <0
begin
print 'Path 3'
set @.pOutput = '2 reservations are already taken for the hours'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 4'
--safe to insert
insert dbo.myCalendar(beginTime, endTime,userId)
values (@.pBegin, @.pEnd, @.pUserId)
if @.@.error = 0
begin
print 'Path 4:1 @.@.error=' + cast(@.@.error as varchar(1))
print 'Path 4:1 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Reservation succeeded'
select @.pOutput as 'Result'
return 0
end
else
begin
print 'Path 4:2 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Failed to make reservation'
select @.pOutput as 'Result'
return -1
end
end
end
ENDjs wrote:

Quote:

Originally Posted by

I am using the following C# code

There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.

But read on:

Quote:

Originally Posted by

and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.
>
-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);
>
-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;
>
select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId
>
if @.@.rowcount <0

This is extremely misguided. Not only is it grossly inefficient, retrieving
all the records that meet the requirements, it is also preventing you from
retrieving your output value. SQL Server does not send RETURN and OUTPUT
parameter values to the client until all resultsets are sent. The above
select statement is creating a resultset that wwill be sent to the client.

If you want to verify if records exist, use IF EXISTS, as in

IF EXISTS (select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)

This is more efficient because it does not retrieve a resultset, it only
verifies that the records meeting therequirements exist. If you really want
a count of the records that meet the requirements (which does not seem to be
te case here) you should use:

declare @.cnt int
Set @.cnt= (select count(*) from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)

Because the result is assigned to a variable, no resultset is created that
needs to be sent to the client.

Quote:

Originally Posted by

begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))

I'm not sure what the point of the above concatenation is: are you trying to
present a datetime in a particular format? If so, are you aware that

Quote:

Originally Posted by

>
-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached

You do realize that because of the intervening statements, the @.@.rowcount
function returns a different value than was returned the first time you used
it ... ? @.@.error and @.@.rowcount are only useful if used immediately after
the statement you wish to test. New statements cause these functions to
return new values.

Anyways, you already determined above that the records exist. Why bother
checking again?

Quote:

Originally Posted by

>
if @.@.rowcount <0

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"

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteNonQuery for sql2005

I hope you would help me in this problem. I use the code below for executenonquery command for mdb DB.But I do not know the changes I should made when Using SQL2005.

----
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\ASPNET20\data\Northwind.mdb"
Dim dbConnection As New OleDbConnection(connectionString)
dbConnection.Open()

Dim commandString As String = "INSERT INTO Employees(FirstName, LastName) " & _
"Values(@.FirstName, @.LastName)"

Dim dbCommand As New OleDbCommand(commandString, dbConnection)

Dim firstNameParam As New OleDbParameter("@.FirstName", OleDbType.VarChar, 10)
firstNameParam.Value = txtFirstName.Text
dbCommand.Parameters.Add(firstNameParam)

Dim lastNameParam As New OleDbParameter("@.LastName", OleDbType.VarChar, 20)
LastNameParam.Value = txtLastName.Text
dbCommand.Parameters.Add(LastNameParam)

dbCommand.ExecuteNonQuery()

dbConnection.Close()
---

You can check SqlCommand class. You can take a look at this link:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

|||Search "Ole" and replace with "Sql"

Execute() in class Microsoft.SqlServer.Dts.RunTime.Package has memory leak

The Execute method in Microsoft.SqlServer.Dts.RunTime.Package class has memory leak after each invokation. This following code demonstrates it.

Output from the program:

Allocated memory after 1 iteration(s) = 476316
Allocated memory after 2 iteration(s) = 546448
Allocated memory after 3 iteration(s) = 555008
Allocated memory after 4 iteration(s) = 563632
Allocated memory after 5 iteration(s) = 572232
Allocated memory after 6 iteration(s) = 580856
Allocated memory after 7 iteration(s) = 589480
Allocated memory after 8 iteration(s) = 598240
Allocated memory after 9 iteration(s) = 606816
Allocated memory after 10 iteration(s) = 615424
Allocated memory after 11 iteration(s) = 624000
Allocated memory after 12 iteration(s) = 632576
Allocated memory after 13 iteration(s) = 641152
Allocated memory after 14 iteration(s) = 649728
Allocated memory after 15 iteration(s) = 658352
Allocated memory after 16 iteration(s) = 666948
Allocated memory after 17 iteration(s) = 675760
Allocated memory after 18 iteration(s) = 684380
Allocated memory after 19 iteration(s) = 693008
Allocated memory after 20 iteration(s) = 701532

//--

// The Execute method in Microsoft.SqlServer.Dts.RunTime.Package has memory

// leak. This program demonstrates it. The package invoked by this program has

// only a single 'Script Task' that does nothing.

//

// To compile, add referece to Microsoft.SQLServer.ManagedDTS.dll.

//

// csc /r:"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll" ExecPackage.cs

//

//--

using System;

using System.Diagnostics;

using Microsoft.SqlServer.Dts.Runtime;

namespace Misc

{

/// <summary>

/// Programmatically executes SSIS package, then displays memeory usage

/// after each execution. The memeory usage goes up after each

/// Package.Execute() call, which indicates memory leak!

/// </summary>

static class ExecPackage

{

static void DisplayUsage()

{

Console.WriteLine(@."Usage: ExecPackage <pkgName>");

Console.WriteLine(@." Package <pkgName> resides in Package Store on localhost under \File System\");

}

static void Main(string[] args)

{

// Parse command line arguments.

if (args.Length != 1)

{

DisplayUsage();

return;

}

string pkgName = @."\File System\" + args[0];

// Programmatically execute the package several times.

Application app = new Application();

for (int i = 1; i <= 20; i++)

{

Package pkg = app.LoadFromDtsServer(pkgName, "localhost", null);

pkg.Execute(); // comment out this line, then allocated memory does not increase

// Process.Start("dtexec.exe", "/dts \"" + pkgName + "\"");

pkg.Dispose();

pkg = null;

// Do garbage collection, then display memory usage

GC.Collect();

Console.WriteLine("Allocated memory after {0} iteration(s) = {1}",

i, GC.GetTotalMemory(true));

}

}

}

}

Thank you for the detailed posting and your time. I have gathered the information and we will investigate.

|||

Craig,

Are you able to confirm the issue? Is there a fix or workaround?

Thanks,

Bin

|||

Bin, yes we are able to reproduce the issue and we are reviewing it for inclusion into the next service pack. However at this time there is not real work around other than restarting the appliction that calls the package. Thank you again for your time and patience.

|||

Has there been any updates with this issue? I am currently dealing with the same problem.

Thanks,

Drew

|||

dferraro wrote:

Has there been any updates with this issue? I am currently dealing with the same problem.

Thanks,

Drew

the next service pack would be sp2, which has not yet been released.|||Thanks, but... is this really such a low priority it doesn't justify a hotfix? I'd say not being able to execut SSIS packages programmatically without having to write hacked code would be up on the top of their list... I know they had released a hotfix for the fuzzy lookup memory leak in the past... Is there anyone from MS who can confirm this is true?
Thanks again,
Drew|||Please advise...... we are looking to use this in a production environment, and I would like to know if/when this issue is fixed, so I can have better knowledge when deciding which options to pursue. How are other people dealing with this issue? Using dtexec? But what if you need to check return values, etc? Any other work-arounds besides building a composite app that runs the package and then dies off?

Thanks again,

Drew|||Looks like I'm having the same problem as well. I've got a .Net app that dynamically builds 120 tasks in a package and pulls in 3 million records running once an hour. I thought I had a memory leak with some other objects but it appears to happen whenever executing this package through the .Excecute() method in my app and still remains after destroying the object. Running this much data that often is going to be a real show stopper with a memory leak.|||The issue appears to be identified and we are working on the fix for a future Service pack.|||

Hello,

We have a production server which runs a nightly batch which is quite memory intensive. The batch contains a start package which is started by SQL Server Agent, and starts other packages using the "Execute Package Task".

The server crashes completely, without logging any error information at all, at random moments during the batch, usually with an interval of a few days. The vendor of the server is investigating the hardware configuration, but we weren't able to find a problem there as of yet.

Can the issue described in this topic have anything to do with the server crashes?

Kind regards, Jeroen