Executing an asp page from sql server.

Hi;

I don't know if this is the right forum, if not please move it.

I have an asp page using fso etc to create txt files on server. I want to call this asp page from sql server for example a table is updated. I mean I want to execute or call this file inside a trigger so a table is updated sql server will execute that asp page and create the text files i needed automatically.

Any help will be appriciated.

Thanks...

hey,

Triggers behave synchronously which means that you will have to wait for the external application (which you probably would need) to come back for the transaction to commit. In common this leads to very bad performance as well as a lot of ugly error retrieving if anything goes wrong. I would suggest using the following approach. After inserting the data into the table, write a log entry in a separate table. Write an application which can retrieve this information and call the asp page if needed. The scheduling of this application can be then either done on an OS basis (like the AT command) or SQl Server Agent, depends on where you want to have the control and wheter you have SQL Server Agent in place (e.g. It does not exists on SQL Server Express). That would act in a asynchronous way, not blocking the original transaction.

HTH, Jens K. Suessmeyer.

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

Assumption : Using SQL Server 2000

Yes.. As Jens K. Suessmeyer suggested you can convert your logic on Triggers itself.

If you don't want to write the code on SQL Server then you can write those logic as
components (ActiveX DLL - just copy paste the VB script from ASO) and you can register those on your SQL Server box, reuse it
from the SQL Server.

Declare @.oComp int
Declare @.HResult int
Declare @.Result as Varchar(8000)

Exec @.HResult = sp_OACreate 'YourCompPackageName.YourComponentName', @.oComp Output;

If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oComp, 'YourMethod', @.Result Output;

EXEC sp_OADestroy @.oComp;

If you don't want to write a component and still want to execute from SQL Server. the
following code will do. But it will degrade your performance.(NOT RECOMANDED)

Declare @.oHttp int
Declare @.HResult int
Declare @.Output as Varchar(8000)
Exec @.HResult = sp_OACreate 'MSXML2.XMLHttp', @.oHttp Output, 1

if @.HResult = 0
Begin
Exec @.HResult = sp_OAMethod @.oHttp, 'Open', NULL, 'POST', 'http://localhost', 'false'
If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, ''
If @.HResult = 0
Exec @.HResult = sp_OAGetProperty @.oHttp, 'ResponseText', @.Output OUTPUT
If @.HResult = 0
Select @.Output
End

EXEC sp_OADestroy @.oHttp

|||

Thanks for both of yours replies, I will try.

Have a nice day.

|||I tried the example and it works great.

How would you POST XML data (or any data) to the page? I have tried the following but keep getting errors on the 'Send'. Is there something I'm missing?

Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, '<field name="id"/>'
AND
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', '<field name="id"/>'

Thanks for any help!

Executing an asp page from sql server.

Hi;

I don't know if this is the right forum, if not please move it.

I have an asp page using fso etc to create txt files on server. I want to call this asp page from sql server for example a table is updated. I mean I want to execute or call this file inside a trigger so a table is updated sql server will execute that asp page and create the text files i needed automatically.

Any help will be appriciated.

Thanks...

hey,

Triggers behave synchronously which means that you will have to wait for the external application (which you probably would need) to come back for the transaction to commit. In common this leads to very bad performance as well as a lot of ugly error retrieving if anything goes wrong. I would suggest using the following approach. After inserting the data into the table, write a log entry in a separate table. Write an application which can retrieve this information and call the asp page if needed. The scheduling of this application can be then either done on an OS basis (like the AT command) or SQl Server Agent, depends on where you want to have the control and wheter you have SQL Server Agent in place (e.g. It does not exists on SQL Server Express). That would act in a asynchronous way, not blocking the original transaction.

HTH, Jens K. Suessmeyer.

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

Assumption : Using SQL Server 2000

Yes.. As Jens K. Suessmeyer suggested you can convert your logic on Triggers itself.

If you don't want to write the code on SQL Server then you can write those logic as
components (ActiveX DLL - just copy paste the VB script from ASO) and you can register those on your SQL Server box, reuse it
from the SQL Server.

Declare @.oComp int
Declare @.HResult int
Declare @.Result as Varchar(8000)

Exec @.HResult = sp_OACreate 'YourCompPackageName.YourComponentName', @.oComp Output;

If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oComp, 'YourMethod', @.Result Output;

EXEC sp_OADestroy @.oComp;

If you don't want to write a component and still want to execute from SQL Server. the
following code will do. But it will degrade your performance.(NOT RECOMANDED)

Declare @.oHttp int
Declare @.HResult int
Declare @.Output as Varchar(8000)
Exec @.HResult = sp_OACreate 'MSXML2.XMLHttp', @.oHttp Output, 1

if @.HResult = 0
Begin
Exec @.HResult = sp_OAMethod @.oHttp, 'Open', NULL, 'POST', 'http://localhost', 'false'
If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, ''
If @.HResult = 0
Exec @.HResult = sp_OAGetProperty @.oHttp, 'ResponseText', @.Output OUTPUT
If @.HResult = 0
Select @.Output
End

EXEC sp_OADestroy @.oHttp

|||

Thanks for both of yours replies, I will try.

Have a nice day.

|||I tried the example and it works great.

How would you POST XML data (or any data) to the page? I have tried the following but keep getting errors on the 'Send'. Is there something I'm missing?

Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, '<field name="id"/>'
AND
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', '<field name="id"/>'

Thanks for any help!
sql

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.

ExecuteNonQuery in SQL 2005

I have an ASP.Net page that runs the following command which is giving me
the following SQL error:
The statement has been terminated.
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The code in the page is as follows:
strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
Request.Form("cboFileTypeID") & ", 0"
cmd = New OleDbCommand(strSQL, conFileData)
lngRows = cmd.ExecuteNonQuery()
It is failing on the last statement and I don't know why. Also, below is
the stored proc code. Can anyone help? Thanks
David
CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
(
@.FileNumber int,
@.FileTypeID int,
@.Rows int output
)
AS
/* SET NOCOUNT ON */
INSERT INTO FileTasks
(FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
AssignedID)
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
RETURN @.RowsTry executing the stored procedure directly from query analyzer and see if
you get the same error?
"David" <dlchase@.lifetimeinc.com> wrote in message
news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have an ASP.Net page that runs the following command which is giving me
> the following SQL error:
> The statement has been terminated.
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The code in the page is as follows:
> strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
> Request.Form("cboFileTypeID") & ", 0"
> cmd = New OleDbCommand(strSQL, conFileData)
> lngRows = cmd.ExecuteNonQuery()
> It is failing on the last statement and I don't know why. Also, below is
> the stored proc code. Can anyone help? Thanks
> David
>
> CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
> (
> @.FileNumber int,
> @.FileTypeID int,
> @.Rows int output
> )
> AS
> /* SET NOCOUNT ON */
> INSERT INTO FileTasks
> (FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
> AssignedID)
> SELECT @.FileNumber, TaskTitle,
> DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
> CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
> FROM FileTaskDefaults
> WHERE FileTypeID = @.FileTypeID
> RETURN @.Rows
>|||Yes, same error.
David
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
> Try executing the stored procedure directly from query analyzer and see if
> you get the same error?
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> subquery
> expression.
>|||Is FileTaskDefaults a view?
Are there any triggers on FileTasks?
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
> Yes, same error.
> David
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
>|||It doesn't look to me that your output parameter is ever assigned to
any value. Don't you need something like this
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
-- assign row count to @.Rows
SELECT @.Rows = @.@.Rowcount|||> RETURN @.Rows
Where does this value get populated? Do you want to use a RETURN, OUTPUT,
or both? I suggest sticking to output parameters for this kind of data, and
not using RETURN. RETURN is meant to return a status code (e.g.
success/failure), not data. This is one of the reasons they're limited to
INTeger datatypes.
A|||That was it! I removed the trigger and it worked. Thank you.
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23%236acDUbGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Is FileTaskDefaults a view?
> Are there any triggers on FileTasks?
> David
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||> That was it! I removed the trigger and it worked. Thank you.
I don't see how that is possible, unless either
(a) you didn't post all of the stored procedure code in your original post,
or
(b) you eliminated the error, but you aren't actually verifying that the
stored procedure is correctly returning the rowcount.
Anyway, it sounds like your trigger was written expecting only single-row
row modifications. You should re-visit that logic instead of just throwing
the trigger away, especially if the trigger is not yours and you are not
sure what it was doing.|||David (dlchase@.lifetimeinc.com) writes:
> That was it! I removed the trigger and it worked. Thank you.
And the trigger did not serve any purpose? Yeah, maybe it was just an
old relic, but I get nervous when I hear things like this. Just because
you did not get any error message, does not mean that it worked. If you
removed a trigger that performed some important task to maintain database
integrity, I would not call that working...
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|||hopefully the OP removed to trigger temporarily just to confirm that was the
issue and is rewriting the trigger to correct the subquery.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B6D62F991A4Yazorman@.127.0.0.1...
> David (dlchase@.lifetimeinc.com) writes:
> And the trigger did not serve any purpose? Yeah, maybe it was just an
> old relic, but I get nervous when I hear things like this. Just because
> you did not get any error message, does not mean that it worked. If you
> removed a trigger that performed some important task to maintain database
> integrity, I would not call that working...
> --
> 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

Execute SSIS project from ASP.NET page

Can you execute a SSIS project from an ASPX page? Or do you have to wrap it into a SQL job and run that?

Does anybody have a code snippet to do this?

Well, you can, but it is rarely a good idea for at least two reasons:

you are sharing address space and threads with IIS, so the scalability of this solution is limited

execute query later

Hi NG
Im writing a web-interface that enables the user to update a DB. The
(non)queries takes heaps of time, and since my page "waits" for the query to
finish, wich is a pain. So basically I was wondering if there is kinda a
"update mytable set myrow=... LATER" kinda keyword or something, to make the
query execute whenever the sqlserver feels like it, but return "ok" right
away.
- Kasper"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>
As far as I know, there is no direct way of doing this.
One thing comes to mind... Have a simple table where you can drop the
information quickly. A SQL Server job can come along and check the table
every few minutes and then perform the work.
There are some caveats however. What if the data is bad, or SQL Server has
an error attempting to process the data. You have already told the web-user
that everything is ok, but in reality it is not. How do you notify the
user that there were problems.
You may need to do some architecturual work to get this to work the way you
want it to.
Rick Sawtell
MCT, MCSD, MCDBA|||Implement the sql update as a stored procedure. If you are using an ADO /
ADO.NET connection, then look executing the SP asynchronously. The SP can
send an email notification back to the user when it completes.
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>|||Service Broker queue(s) come to mind. Need 2005 however or come up with
your own queue table.
William Stacey [MVP]
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
> to finish, wich is a pain. So basically I was wondering if there is kinda
> a "update mytable set myrow=... LATER" kinda keyword or something, to make
> the query execute whenever the sqlserver feels like it, but return "ok"
> right away.
> - Kasper
>