EXECUTE AS question: asp

Showing posts with label asp. Show all posts

Monday, March 26, 2012

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.

Any help will be appriciated.

Thanks...

hey,

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.

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

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.

Thanks for any help!
sql

Friday, March 23, 2012

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.

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.

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

lblErrorOther.Text = pkgResults.ToString()
End Sub

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

The code seems fine.

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.

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

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.

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.

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|||

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

Thanks,
Mark.

Executing a SSIS Package from an ASP.NET page.

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

lblErrorOther.Text = pkgResults.ToString()
End Sub

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

The code seems fine.

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.

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

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.

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.

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|||

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

Thanks,
Mark.

Executing a SQL Script with MSDE

Hello,

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

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

Much appreciated

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

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

Thursday, March 22, 2012

Executing a DTS Package using an ASP (VBScript)

I'm looking for an example of how to execute an existing DTS* package
from an
ASP (VB)script and would appreciate any and all response. *I don't
even
know if it's possible
Thanks
- Chuck Gatto

Dan Guzman Apr 27 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Dan Guzman" <DGuz...@.nospamplease-earthlink.net&g*t; - Find
messages by this author
Date: 2000/04/27
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

This VBScript example loads and executes an existing DTS pac*kage from
SQL
Server.

Option Explicit
Const PackageName = "PackageName"
Const ServerName = "ServerName"
Const UserName = "UserName"
Const Password = "Password"
Dim DTSPk
Set DTSPk = CreateObject("dts.package")
DTSPk.LoadFromSQLServer ServerName, UserName ,Password
,,,,,*PackageName
DTSPk.Execute
If DTSPk.Steps(1).ExecutionResult = 0 Then
Response.Write "Package execution completed"
Else
Response.Write "Package execution failed"
End If
Set DTSPk = Nothing

You can also create the entire package from scratch from wit*hin your
asp and
execute it.

Hope this helps.

Chuck Gatto <cga...@.anchorsystems.com> wrote in message

news:8eapo8$frj$1@.slb7.atl.mindspring.net...

- Hide quoted text -
- Show quoted text -

> I'm looking for an example of how to execute an existing D*TS
package from
an
> ASP (VB)script and would appreciate any and all response.* I don't
even
> know if it's possible
> Thanks
> - Chuck Gatto

Chuck Gatto May 1 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Chuck Gatto" <cga...@.anchorsystems.com> - Find messages by this
author
Date: 2000/05/01
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

Below code works 100% in VB but the load fails in ASP. I g*et...
"Microsoft OLE DB Provider for SQL Server. Login failed for *user "\".
error.
I think the IIS (server a) is set for NT auth. and sql7 (on *server b)
as
well but I can't be sure.
Any idea what I should look for.
Thanks

"Dan Guzman" <DGuz...@.nospamplease-earthlink.net> wrote in m*essage

news:sgi3hi617qo89@.corp.supernews.com...

- Hide quoted text -
- Show quoted text -

> This VBScript example loads and executes an existing DTS p*ackage
from SQL
> Server.

> Option Explicit
> Const PackageName = "PackageName"
> Const ServerName = "ServerName"
> Const UserName = "UserName"
> Const Password = "Password"
> Dim DTSPk
> Set DTSPk = CreateObject("dts.package")
> DTSPk.LoadFromSQLServer ServerName, UserName ,Password
,,,*,,PackageName
> DTSPk.Execute
> If DTSPk.Steps(1).ExecutionResult = 0 Then
> Response.Write "Package execution completed"
> Else
> Response.Write "Package execution failed"
> End If
> Set DTSPk = Nothing

> You can also create the entire package from scratch from w*ithin
your asp
and
> execute it.

> Hope this helps.

> Chuck Gatto <cga...@.anchorsystems.com> wrote in message
> news:8eapo8$frj$1@.slb7.atl.mindspring.net...
> > I'm looking for an example of how to execute an existing* DTS
package
from
> an
> > ASP (VB)script and would appreciate any and all respons*e. I
don't even
> > know if it's possible
> > Thanks
> > - Chuck Gatto

Dan Guzman May 1 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Dan Guzman" <DGuz...@.nospamplease-earthlink.net&g*t; - Find
messages by this author
Date: 2000/05/01
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

You can specify a trusted connection with flag 256 instead o*f
username and
password. For example:

DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*me

Assuming this is an intranet application running under NT 4.*0 and you
want
to execute the package under the invoking user's account, yo*u can do
this as
follows:

Specify 'clear text' for the IIS Directory Security
auth*entication
Remove 'Everyone' from the access list on the files (req*uires
NTFS) and
grant permissions to the users
Grant logins access to the database server

With this method, users must enter their Domain\UserName and* password
when
prompted.

NT authentication presents a challenge when multiple servers* are
involved
because NT 4.0 does not support delegation. See
http://msdn.microsoft.com/workshop/...re/security.asp for
details.
I understand Windows 2000 provides delegation capabilities b*ut this
can be a
bit tricky to implement.

BTW, if your DTS package does not access SQL Server, you can* save it
to a
file and use the LoadFromStorageFile method instead.

Hope this helps.

If you need to use

Chuck Gatto <cga...@.anchorsystems.com> wrote in message

news:8ekrkd$pmq$1@.slb7.atl.mindspring.net...

- Hide quoted text -
- Show quoted text -

> Below code works 100% in VB but the load fails in ASP. I* get...
> "Microsoft OLE DB Provider for SQL Server. Login failed fo*r user
"\".
error.
> I think the IIS (server a) is set for NT auth. and sql7 (o*n server
b) as
> well but I can't be sure.
> Any idea what I should look for.
> Thanks

Chuck Gatto May 6 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Chuck Gatto" <cga...@.anchorsystems.com> - Find messages by this
author
Date: 2000/05/06
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

Hey Dan
Thanks again. I really appreciate your input and help.
I actually solved the problem by calling dtsrun...
I apologize for the delay getting back w/you but thee recent* virus
atack
sidetracked me.
Thanks again.

"Dan Guzman" <DGuz...@.nospamplease-earthlink.net> wrote in m*essage

news:sgsfh8spoog87@.corp.supernews.com...

- Hide quoted text -
- Show quoted text -

> You can specify a trusted connection with flag 256 instead* of
username and
> password. For example:

> DTSPk.LoadFromSQLServer ServerName, , , 256,,,,Package*Name

> Assuming this is an intranet application running under NT *4.0 and
you want
> to execute the package under the invoking user's account, *you can
do this
as
> follows:

> Specify 'clear text' for the IIS Directory Security
au*thentication
> Remove 'Everyone' from the access list on the files (r*equires
NTFS)
and
> grant permissions to the users
> Grant logins access to the database server

> With this method, users must enter their Domain\UserName a*nd
password when
> prompted.

> NT authentication presents a challenge when multiple serve*rs are
involved
> because NT 4.0 does not support delegation. See
> http://msdn.microsoft.com/workshop/...re/security.asp for
details.
> I understand Windows 2000 provides delegation capabilities* but this
can be
a
> bit tricky to implement.

> BTW, if your DTS package does not access SQL Server, you c*an save
it to a
> file and use the LoadFromStorageFile method instead.

> Hope this helps.

> If you need to use
> Chuck Gatto <cga...@.anchorsystems.com> wrote in message
> news:8ekrkd$pmq$1@.slb7.atl.mindspring.net...
> > Below code works 100% in VB but the load fails in ASP. * I get...

> > "Microsoft OLE DB Provider for SQL Server. Login failed *for user
"\".
> error.
> > I think the IIS (server a) is set for NT auth. and sql7 *(on
server b) as
> > well but I can't be sure.
> > Any idea what I should look for.
> > Thanks

chris.duni...@.agwsha.nhs.uk Jan 31, 10:05 am show options

Hi,

I've tried adding this function to my ASP pages and get the *following

error message;

Microsoft VBScript runtime error '800a01ad'

ActiveX component can't create object: 'DTS.Package'

/asp/pages/dts.asp, line 21

Does anyone have any idea what I need to fix to get the DTS *to work?

I'm v. new to SQL Server and ASP so any help would be apprec*iated.
Many thanks,
Chris Dunigan<chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1107256044.746188.14660@.c13g2000cwb.googlegro ups.com...
I'm looking for an example of how to execute an existing DTS* package
from an
ASP (VB)script and would appreciate any and all response. *I don't
even
know if it's possible
Thanks
- Chuck Gatto

http://www.google.co.uk/search?hl=e...+vbscript&meta=

Your package.
It works OK if you try and run it from Enterprise Manager on your machine?

You tried a real user and password in those fields when you tried that
method?
This would be equal to the user you're logged on as?

--
Regards,
Andy O'Neill|||
Hi Andy,

Yes the DTS Package works fine when I run it from Enterprise Manager;
and I have used the correct server name, username, password and
packagename.

Having done some more digging into this error I found a site that told
me it could be something to so with the software installed on the PC on
which the database resides.
I think I might need to ensure that dtspkg.dll is correctly installed
(along with a host of other dll and rll's).
As I can't get hold of our IT guys today I haven't been able to see if
this solves my problem.

Does this sound like it may fix the problem??

Regards,
Chris

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:41ffb33f$1_2@.127.0.0.1...
>
> Hi Andy,
> Yes the DTS Package works fine when I run it from Enterprise Manager;
> and I have used the correct server name, username, password and
> packagename.
> Having done some more digging into this error I found a site that told
> me it could be something to so with the software installed on the PC on
> which the database resides.
> I think I might need to ensure that dtspkg.dll is correctly installed
> (along with a host of other dll and rll's).
> As I can't get hold of our IT guys today I haven't been able to see if
> this solves my problem.
> Does this sound like it may fix the problem??
> Regards,
> Chris
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

These links might help:

http://www.sqldts.com/default.aspx?207
http://support.microsoft.com/defaul...kb;en-us;252987
http://support.microsoft.com/defaul...kb;en-us;323685
http://support.microsoft.com/defaul...kb;en-us;282463

Simon|||"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:41ffb33f$1_2@.127.0.0.1...
> Hi Andy,
> Yes the DTS Package works fine when I run it from Enterprise Manager;
> and I have used the correct server name, username, password and
> packagename.
> Having done some more digging into this error I found a site that told
> me it could be something to so with the software installed on the PC on
> which the database resides.
> I think I might need to ensure that dtspkg.dll is correctly installed
> (along with a host of other dll and rll's).
> As I can't get hold of our IT guys today I haven't been able to see if
> this solves my problem.
> Does this sound like it may fix the problem??

If no dts package works when run from a job on the server.

Quite frankly, I'm confused by your huge initial post as to which technique
you're trying and what's going on.

I'm wondering if this is running and owned by a user can run the dts package
OK on the server using whichever of the alternatives you posted you're
currently trying.

Try this with the package if it's vbscript using any activex.
Open DTS Designer, right-click the task, select Workflow, then Workflow

Properties. On the Options tab, check "Execute on main package thread"

--
Regards,
Andy O'Neill

Wednesday, March 21, 2012

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

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

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

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

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

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

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

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

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

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

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

|||

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

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

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

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

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

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

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

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

Thanks for fixing my keying error, Motley :-)

Monday, March 19, 2012

ExecuteReader Command

I am currently developing an asp.net application that uses sql server 2000.
In addition, I am also using the sql application data block provide off of
Microsoft's website.
My connection string to the database is stored in the Web.config file so
that it can be accessed by all classes. The connection string does not
contain any user name and password. The problem I am encountering is that I
cannot access the same connection string in the Web.config file within the
same method. I get a security exception saying the operation is not allowed
by the security policy. But it works the first time I use ExecuteReader but
not the second time.
Example:
Dim dr1 As SqlDataReader
dr1 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...), ...)
While dr.Read()
..
Dim dr2 As SqlDataReader
dr2 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...), ...)
..
End While
where ConfigurationSettings.AppSettings(...) is the connection string in my
Web.config file.
The error occurs the second time I call ExecuteReader using the connection
string in the Web.config file. HOWEVER, if instead of accessing the
connection string in Web.config, I hardcode the string with a user name and
password, then it works fine. But it doesn't work using the connection
string in Web.config file.
Any ideas?
Any help is appreciated.
Thanks.Hi James,
I don't kknow why this would happen but
Instead of doing that way , do this
Dim _ConnectionString as String =ConfigurationSettings.AppSettings(...)
Dim dr1 As SqlDataReader
dr1 = SqlHelper.ExecuteReader(_ConnectionString, ...)
...
...
and then just use the _ConnectionString Field. It will save on IO
Next . Don't use a datareader. It holds the connection open too long and
you can only open on datareader per connection. use a dataadapter and
dataset
Here is an example
Dim _SQLDataAdapter As New SqlClient.SqlDataAdapter("select co1,
col2 from table", _ConnectionString)
Dim _Dataset As New DataSet
Try
_SQLDataAdapter.Fill(_Dataset)
Catch ex As Exception
_Dataset = Nothing
End Try
If IsNothing(_Dataset) = False AndAlso _Dataset.Tables.Count = 0
Then
For Each _Datarow As DataRow In _Dataset.Tables(0).Rows
Response.Write(_Datarow.Item(0))
Next
Else
' write error message
End If
Now the SqlDataAdapter will open, execute, fill and close the connection in
one step. In this way you reduce the load on the SQL server
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"James" <James@.discussions.microsoft.com> wrote in message
news:AFE07474-3DEA-4EDD-96BA-D28CD595BB33@.microsoft.com...
>I am currently developing an asp.net application that uses sql server 2000.
> In addition, I am also using the sql application data block provide off of
> Microsoft's website.
> My connection string to the database is stored in the Web.config file so
> that it can be accessed by all classes. The connection string does not
> contain any user name and password. The problem I am encountering is that
> I
> cannot access the same connection string in the Web.config file within the
> same method. I get a security exception saying the operation is not
> allowed
> by the security policy. But it works the first time I use ExecuteReader
> but
> not the second time.
> Example:
> Dim dr1 As SqlDataReader
> dr1 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...), ...)
> While dr.Read()
> ...
> Dim dr2 As SqlDataReader
> dr2 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...),
> ...)
> ...
> End While
> where ConfigurationSettings.AppSettings(...) is the connection string in
> my
> Web.config file.
> The error occurs the second time I call ExecuteReader using the connection
> string in the Web.config file. HOWEVER, if instead of accessing the
> connection string in Web.config, I hardcode the string with a user name
> and
> password, then it works fine. But it doesn't work using the connection
> string in Web.config file.
> Any ideas?
> Any help is appreciated.
> Thanks.|||Thanks for the reply GregO.
I think I have narrowed down my problem. It has to do with a security
policy setting on IIS and/or .NET, I believe. Basically, if the two
ExecuteReaders use the connection string without the login information for
the database, the security exception occurs. If either one, or both,
ExecuteReaders use a connection string that includes the login information,
then it works fine. I haven't had a chance to look into this further but an
y
ideas?
As for the suggestion on not using the sqldatareader, this was my
alternative if I couldn't resolve the above issue. Thanks for the heads up.
One question does come to mind though. Would it be more expensive to use a
sqldatareader and open a connection and close it right away or use a
sqldataadapter and dataset and waste memory?
James
"GregO" wrote:

> Hi James,
> I don't kknow why this would happen but
> Instead of doing that way , do this
> Dim _ConnectionString as String =ConfigurationSettings.AppSettings(...)
> Dim dr1 As SqlDataReader
> dr1 = SqlHelper.ExecuteReader(_ConnectionString, ...)
> ...
> ...
>
> and then just use the _ConnectionString Field. It will save on IO
> Next . Don't use a datareader. It holds the connection open too long and
> you can only open on datareader per connection. use a dataadapter and
> dataset
> Here is an example
> Dim _SQLDataAdapter As New SqlClient.SqlDataAdapter("select co1,
> col2 from table", _ConnectionString)
> Dim _Dataset As New DataSet
> Try
> _SQLDataAdapter.Fill(_Dataset)
> Catch ex As Exception
> _Dataset = Nothing
> End Try
> If IsNothing(_Dataset) = False AndAlso _Dataset.Tables.Count = 0
> Then
> For Each _Datarow As DataRow In _Dataset.Tables(0).Rows
> Response.Write(_Datarow.Item(0))
> Next
> Else
> ' write error message
> End If
> Now the SqlDataAdapter will open, execute, fill and close the connection i
n
> one step. In this way you reduce the load on the SQL server
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "James" <James@.discussions.microsoft.com> wrote in message
> news:AFE07474-3DEA-4EDD-96BA-D28CD595BB33@.microsoft.com...
>
>|||Just a couple of things to keep in mind:
1) The dataadapter uses a datareader "under the hood" to perform Fill
operations on a dataset or datatable, so there is no real performance
advantage to choosing a dataadapter. This is true as of version 1.1 - I have
not looked into the 2.0 library yet.
2) ConfigurationSettings uses a data cache when it retrieves data from the
web.config file, so multiple requests for the same item will not require
multiple file IO operations.
To get an idea on how the .NET framework classes perform their work, I
ecommend getting a free copy of Lutz Roeder's Reflector tool
(http://www.aisto.com/roeder/dotnet/). It is indispensible as a learning
tool.
"James" <James@.discussions.microsoft.com> wrote in message
news:AF3FDDD8-D514-41F9-999A-E156914A306F@.microsoft.com...
> Thanks for the reply GregO.
> I think I have narrowed down my problem. It has to do with a security
> policy setting on IIS and/or .NET, I believe. Basically, if the two
> ExecuteReaders use the connection string without the login information for
> the database, the security exception occurs. If either one, or both,
> ExecuteReaders use a connection string that includes the login
information,
> then it works fine. I haven't had a chance to look into this further but
any
> ideas?
> As for the suggestion on not using the sqldatareader, this was my
> alternative if I couldn't resolve the above issue. Thanks for the heads
up.
> One question does come to mind though. Would it be more expensive to use
a
> sqldatareader and open a connection and close it right away or use a
> sqldataadapter and dataset and waste memory?
> James
> "GregO" wrote:
>
and
in
SQL
2000.
off of
so
not
that
the
ExecuteReader
...)
SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...),
in
connection
name
connection|||Hi Jeremy,
1) The performance increase isn't from using the datareader or not but on
how you use it. Typically what people do is loop through the reader doing
formatting and string stuff (Which is fine) . But what you need to remember
is that the connection is open to the database and if (as in web appliction)
you have 100's of users then this can mean hundreds of open connections.
Where as using a dataadapter adn the fill method you get a populate
dataset/datatable which you can loop through as much as you want but the
connection to the database is closed when you do this (unless you have
opened the connection manually). Now openning and closing the connection to
the database as quickly as possible is the best way of handling databsae
connections for scalabity and therefore performance. As I understand it
version still uses the datareader
2) Cached or not its alway better to plac the results of any function into a
local field if you are referencing that value multiple times. As appsetting
is a function it still performs steps and logic (cached or not) I agree you
wouldn't have the IO which is the main perfromance gain
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:up%23PJZPtFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Just a couple of things to keep in mind:
> 1) The dataadapter uses a datareader "under the hood" to perform Fill
> operations on a dataset or datatable, so there is no real performance
> advantage to choosing a dataadapter. This is true as of version 1.1 - I
> have
> not looked into the 2.0 library yet.
> 2) ConfigurationSettings uses a data cache when it retrieves data from the
> web.config file, so multiple requests for the same item will not require
> multiple file IO operations.
> To get an idea on how the .NET framework classes perform their work, I
> ecommend getting a free copy of Lutz Roeder's Reflector tool
> (http://www.aisto.com/roeder/dotnet/). It is indispensible as a learning
> tool.
> "James" <James@.discussions.microsoft.com> wrote in message
> news:AF3FDDD8-D514-41F9-999A-E156914A306F@.microsoft.com...
> information,
> any
> up.
> a
> and
> in
> SQL
> 2000.
> off of
> so
> not
> that
> the
> ExecuteReader
> ...)
> SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...),
> in
> connection
> name
> connection
>|||Hi Greg,
My main goal was simply to provide some context to the points you originally
made. If taken at face value, some people might have gotten the wrong
impression about how the DataReader and ConfigurationSettings classes work:
"...It will save on IO..."
"...It holds the connection open too long and you can only open on
datareader per connection..."
As for your most recent response:
1) Yes, I was referring to "equivalent" operations (filling a
dataset/datatable compared to creating custom objects based on the
datareader). In fact, depending on the simplicity of the custom data
objects, it might even be faster than filling a datatable/dataset, since
there is a fair amount of work involved in that process. The DataReader
itself, however, does not hold the connection open too long. That was my
point here.
2) I think Martin Fowler et al. might disagree with the unequivocal tone
(see 'Replace Temp with Query' from his book "Refactoring Improving the
Design of Existing Code"). From a purely performance-minded perspective, it
will most likely be quicker to access the value from a temp variable than it
would be to call the AppSettings method each time the value is needed, but
temp variables can sometimes have an effect on method structure that leads
to an overall degradation of performance (although usually only slightly).
And raw performance is not typically the only consideration in most
projects. Be that as it may, my main point here was to address the statement
about IO, and we both seem to agree there.
Thanks for the feedback and have a great w

end!
"GregO" <grego@.community.nospam> wrote in message
news:%23SZHHhPtFHA.256@.tk2msftngp13.phx.gbl...
> Hi Jeremy,
> 1) The performance increase isn't from using the datareader or not but on
> how you use it. Typically what people do is loop through the reader doing
> formatting and string stuff (Which is fine) . But what you need to
> remember is that the connection is open to the database and if (as in web
> appliction) you have 100's of users then this can mean hundreds of open
> connections. Where as using a dataadapter adn the fill method you get a
> populate dataset/datatable which you can loop through as much as you want
> but the connection to the database is closed when you do this (unless you
> have opened the connection manually). Now openning and closing the
> connection to the database as quickly as possible is the best way of
> handling databsae connections for scalabity and therefore performance. As
> I understand it version still uses the datareader
> 2) Cached or not its alway better to plac the results of any function into
> a local field if you are referencing that value multiple times. As
> appsetting is a function it still performs steps and logic (cached or not)
> I agree you wouldn't have the IO which is the main perfromance gain
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:up%23PJZPtFHA.2892@.TK2MSFTNGP10.phx.gbl...
>|||Hey that's alright. It's good to have these discussions.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:OZZe87VtFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Hi Greg,
> My main goal was simply to provide some context to the points you
> originally made. If taken at face value, some people might have gotten the
> wrong impression about how the DataReader and ConfigurationSettings
> classes work:
> "...It will save on IO..."
> "...It holds the connection open too long and you can only open on
> datareader per connection..."
> As for your most recent response:
> 1) Yes, I was referring to "equivalent" operations (filling a
> dataset/datatable compared to creating custom objects based on the
> datareader). In fact, depending on the simplicity of the custom data
> objects, it might even be faster than filling a datatable/dataset, since
> there is a fair amount of work involved in that process. The DataReader
> itself, however, does not hold the connection open too long. That was my
> point here.
> 2) I think Martin Fowler et al. might disagree with the unequivocal tone
> (see 'Replace Temp with Query' from his book "Refactoring Improving the
> Design of Existing Code"). From a purely performance-minded perspective,
> it will most likely be quicker to access the value from a temp variable
> than it would be to call the AppSettings method each time the value is
> needed, but temp variables can sometimes have an effect on method
> structure that leads to an overall degradation of performance (although
> usually only slightly). And raw performance is not typically the only
> consideration in most projects. Be that as it may, my main point here was
> to address the statement about IO, and we both seem to agree there.
> Thanks for the feedback and have a great w

end!
> "GregO" <grego@.community.nospam> wrote in message
> news:%23SZHHhPtFHA.256@.tk2msftngp13.phx.gbl...
>

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

Monday, March 12, 2012

Execute SSIS Package from ASP.net 2 Application?

Hello, is it posssible to execute SSIS packages from ASP.NET ? Which code should I Use?

Yes it is. There is an API available to let you do this. I have never used it myself but I know its there.

Loading and Running a Local Package Programmatically
http://msdn2.microsoft.com/en-us/library/ms136090.aspx

-Jamie

|||

Hi Luis,

There's an example of this in the Professional SQL Server 2005 Integration Services book - chapter 17.

You can grab the sample code from the Wrox site.

Hope this helps,
Andy

|||How can I set programatically connection strings for the connection managers?

Execute SSIS package from asp.Net

Hi,

I am a newbie to SSIS. I am trying to execute a simple package that I created from my ASP.Net application.

I get the error 'DTSER_FAILURE'. Can anyone help?

Thanks

Turn on logging, or supply an implementation of IDtsEvents interface to Execute() method to find out some information about the error.

By the way, the most common problem: security, the package is executed under ASP.NET service account, not your domain account.|||

Thanks for the reply.

I turned on the logging. However, no entires are made in the log file.

I thought that there would be some security issues,

> the package is executed under ASP.NET service account, not your domain account.

How do I address this?

|||

HoustonRocket wrote:
> the package is executed under ASP.NET service account, not your domain account.
How do I address this?

Depends on what you mean by "address". This might be quite OK in some situations, but might be not in others. Just something to be aware of.

If you want the package to be executed in different context, use other ways to execute it, rather than invoke it from object model. A common way is to create SQL Agent job and then execute it using Agent's stored procedures. Another way is to execute package using DtExec under different account (see ProcessStartInfo.UserName and ProcessStartInfo.Password).

|||

Hi,

I tried to create a job and add the package as a 'step'. That didnt work either and I got the error

'Microsoft.SqlServer.ConnectionInfo

The specified '@.subsystem' is invalid

Here's what I am trying to do:

I created a package which grabs the data from excel file and populates sql server 2005 DB - simple. This package executes if I run it from the business intelligence studio.

I want to achieve two things

1) Create a job that will schedule the package to run twice a day

2) Execute this package from an asp.net code.

|||

ok another question

I ran the dbo.sp_enum_sqlagent_subsystems and the result does not list SSIS package.

How can I include the SSIS package as a subsystem ?

|||Strange. Are you connected to SQL 2005 system? Have you installed SSIS (a checkbox during SQL install).

Try connecting to SQL using SQL Server Management Studio, can you create new jobs that use SSIS subsystem?

Sunday, February 26, 2012

Execute Permission denied when running from IIS

My development environment is IIS 5.1, asp.net 2.0, Visual Web Developer 05 Express, MS Sql 2005 Express with XP Pro. I used a "stored procedure" in a webpage Formview to insert a record in a child table after inserting a record in the parent table. All went well when testing in VWD.

After deploying to remote site on same machine, I get an error

"EXECUTE permission denied on object 'usp_Insertdataset', database 'Job_Tracker_SQL', schema 'dbo'"

when trying to insert. I know that SQL Express is not suppose to support stored procedures. Is there a work around? I need to host this site on this machine for the immediate future.

Thanks

cbrcdr

I think that you are incorrect in stating that SQL Server Express does not support stored procedures. I'd be curious to see where that is documented (but, I have been proven wrong in the past!).

But, in any case, I would bet that in your development environment, you were connecting to the database as a DBO (database owner), and the same was not true for the remote site. By default, no one but a DBO is granted any permissions on database objects.

You should be able to grant execute permissions to the "Public" role, which should take care of allowing anyone who is permitted to connect to the database to execute your stored procedure. As an alternative, you can grant execute permission to invididual users too.

Execute the following SQL while connected to your database as the DBO user (i.e., from a query window or whatever mechanism you have available):

GRANT EXECUTE ON usp_InsertdatasetTO Public

|||

Jason

I saw a Feature Matrix on a Mircosoft Webpage that compared all the SQL products and it indicted that SQL Express did not support Stored Procedures. Maybe it was not up to date. I assumed it was right but you are. I used SSMSE and set the permissions on the Stored Procedure to execute for the login and it works.

Thanks, that was my last big hurdle for this phase of my application.

George

Friday, February 24, 2012

EXECUTE permission denied

I'm running an ASP based report. It's always worked, but for some
reason now I get the following:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
'dbo'.
Any ideas?
nick,
The error is pretty specific. Apparently the login used by your ASP report
still has access to the database, but not to that stored procedure. It
could be that someone changed the rights being granted the report user. You
can check what rights it still has by:
EXEC sp_helprotect @.username = 'YourReportUserAccount'
However, a more likely suspect is that a new version of the stored procedure
was created , but the rights were not regranted. This is a problem when a
stored procedure is dropped and recreated. You can check by:
select name, crdate
from sysobjects
where name = 'IFMSpVisitbyStatus'
If that is the case, some one will need to:
GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
Also, if that is the problem then the process for deploying updated SQL
Server objects apparently needs to be tightened up a bit to ensure that
rights are preserved or regranted.
RLF
"nick" <cipher7836@.gmail.com> wrote in message
news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
> I'm running an ASP based report. It's always worked, but for some
> reason now I get the following:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> 'dbo'.
> Any ideas?
|||On Mar 14, 10:35Xam, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> nick,
> The error is pretty specific. XApparently the login used by your ASP report
> still has access to the database, but not to that stored procedure. XIt
> could be that someone changed the rights being granted the report user. You
> can check what rights it still has by:
> EXEC sp_helprotect @.username = 'YourReportUserAccount'
> However, a more likely suspect is that a new version of the stored procedure
> was created , but the rights were not regranted. XThis is a problem whena
> stored procedure is dropped and recreated. XYou can check by:
> select name, crdate
> from sysobjects
> where name = 'IFMSpVisitbyStatus'
> If that is the case, some one will need to:
> GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
> Also, if that is the problem then the process for deploying updated SQL
> Server objects apparently needs to be tightened up a bit to ensure that
> rights are preserved or regranted.
> RLF
> "nick" <cipher7...@.gmail.com> wrote in message
> news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
>
>
> - Show quoted text -
Thanks for the information! Sad to say, but I know next to nothing
about SQL. I just wanted to help the user run an already created
report.

EXECUTE permission denied

I'm running an ASP based report. It's always worked, but for some
reason now I get the following:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
'dbo'.
Any ideas'nick,
The error is pretty specific. Apparently the login used by your ASP report
still has access to the database, but not to that stored procedure. It
could be that someone changed the rights being granted the report user. You
can check what rights it still has by:
EXEC sp_helprotect @.username = 'YourReportUserAccount'
However, a more likely suspect is that a new version of the stored procedure
was created , but the rights were not regranted. This is a problem when a
stored procedure is dropped and recreated. You can check by:
select name, crdate
from sysobjects
where name = 'IFMSpVisitbyStatus'
If that is the case, some one will need to:
GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
Also, if that is the problem then the process for deploying updated SQL
Server objects apparently needs to be tightened up a bit to ensure that
rights are preserved or regranted.
RLF
"nick" <cipher7836@.gmail.com> wrote in message
news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
> I'm running an ASP based report. It's always worked, but for some
> reason now I get the following:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> 'dbo'.
> Any ideas'|||On Mar 14, 10:35=A0am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> nick,
> The error is pretty specific. =A0Apparently the login used by your ASP rep=ort
> still has access to the database, but not to that stored procedure. =A0It
> could be that someone changed the rights being granted the report user. Yo=u
> can check what rights it still has by:
> EXEC sp_helprotect @.username =3D 'YourReportUserAccount'
> However, a more likely suspect is that a new version of the stored procedu=re
> was created , but the rights were not regranted. =A0This is a problem when= a
> stored procedure is dropped and recreated. =A0You can check by:
> select name, crdate
> from sysobjects
> where name =3D 'IFMSpVisitbyStatus'
> If that is the case, some one will need to:
> GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
> Also, if that is the problem then the process for deploying updated SQL
> Server objects apparently needs to be tightened up a bit to ensure that
> rights are preserved or regranted.
> RLF
> "nick" <cipher7...@.gmail.com> wrote in message
> news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
>
> > I'm running an ASP based report. It's always worked, but for some
> > reason now I get the following:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> > denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> > 'dbo'.
> > Any ideas?... Hide quoted text -
> - Show quoted text -
Thanks for the information! Sad to say, but I know next to nothing
about SQL. I just wanted to help the user run an already created
report.

Friday, February 17, 2012

Execute Integration Package by SP

Hi,
I'm writing a ASP.NET application, and I would like to export data from SQL
Server 2005 to MS-Excel. I know that the Integration Service in SQL 2005
has replaced the DTS in SQL2K. I'm wondering how I can execute the
Integration package from either ASP.NET or by means of Stored Procedures.
Thank you.
Regards,
JanetWell, I have not pkayed with this issue in SQL Server 2005 ,however , can
you create a linked server to EXCEL or using OPENDATSOURCE
"Janet >" <<unknown> wrote in message
news:OoNzgaYfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm writing a ASP.NET application, and I would like to export data from
> SQL Server 2005 to MS-Excel. I know that the Integration Service in SQL
> 2005 has replaced the DTS in SQL2K. I'm wondering how I can execute the
> Integration package from either ASP.NET or by means of Stored Procedures.
> Thank you.
> Regards,
> Janet
>

Execute DTS Package from Asp.net

Hi All,
How we can call and Execute DTS Scripts From Asp.net.
Can any one pls give me some help.
Regards
VAsu

There are many explanations of this on the Web. A simple Google search yielded these.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://www.sqlteam.com/item.asp?ItemID=19595

Wednesday, February 15, 2012

Execute DTS Package From ASP.NET

I am trying to execute a DTS package in my asp.net code, but the dts package has both a owner and user password. I want to be able to use the user password to execute the package within the code, but whenever I try that I get the following error:
Access to package properties requires entry of package owner password.
Line 101: oPKG.LoadFromSQLServer("(local)", "UserName", "Password", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "DTSPassword", , , "CopySourceTest1")Line 102:Line 103: For Each oStep In oPKG.StepsLine 104: oStep.ExecuteInMainThread = TrueLine 105: Next

It works fine if I execute with the owner password, it just will not work with the user password. Any ideas will be appreciated
Thanks

To run DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. What I am saying is to run DTS with SQL Server Agent dependent service like xp_CMDSHELL you must give the account used to install SQL Server Agent Admin permissions in Windows and SQL Server. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://www.sqlteam.com/item.asp?ItemID=19595

|||

We aren't executing this through a stored procedure. I am importing the DTS reference. Here is the code that makes it work:

Dim oPKGAs DTS.Package

Dim oStepAs DTS.Step

oPKG =New DTS.Package

Dim sMessageAsString

'Load Package

oPKG.LoadFromSQLServer("(local)", "UserName", "Password", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "PackagePassword", , , "CopySourceTest1")

ForEach oStepIn oPKG.Steps

oStep.ExecuteInMainThread =True

'Execute

oPKG.Execute()

oPKG.UnInitialize()

oStep =Nothing

oPKG =Nothing

I'm trying to figure out how I can run this code or something similar without having to execute the cmdShell stored proc and the DTS run utility. My question is, is it possible to execute this code with the User password on the DTS Package instead of the Owner password?
Thanks

|||I could be wrong but I don't think you can because all automation in SQL Server is SQL Server Agent dependent. Hope this helps.|||I am pretty new to this, but I just don't understand why it would work using the owner password though. If i go to Enterprise Manager I can execute the DTS package with the user password.
Also, I have another question that is kind of off the topic, but related. If I do not impersonate my user account the package does not execute. It give me an access denied error to the database .ldf file. Why do I need to impersonate my user account to execute it. I understand when I run this I am using the ASP.NET user account, but any other time that user can write to the .ldf file, for example when logging in or anything, correct?|||The text below is from Microsoft about SQL Server Agent permissions and read the post below for more info about using DTS with stored procs in an Asp.net application. Hope this helps.
("Important: SQL Server Agent will need local Windows administrator privileges if one of the following is true:
SQL Server Agent connects to SQL Server using standard SQL Server Authentication (not recommended).
SQL Server Agent uses a multiserver administration master server (MSX) account that connects using standard SQL Server Authentication.
SQL Server Agent runs Microsoft ActiveX? script or CmdExec jobs owned by users who are not members of the sysadmin fixed server role. " )

http://forums.asp.net/906564/ShowPost.aspx
|||

We decided that the code I am using will work, we will just use the owner password. But now, Are the permissions and user accounts the same for the way I am executing this? Everything I read seems to be executing it with the stored procedure xp_cmdShell and the DTS Run utility which I am not using. I am confused on why I need to impersonate my user account (which has administrator rights on the local machine). Why can't I just use the IIS Asp.net User account to execute it? When I execute the package impersonating my user account it will write to the database .ldf file and the package will execute but when I do not impersonate my user account it does not write to the .ldf file and the package does not execute, I get the access denied error. I guess I get kind of confused on the user accounts and permissions needed.

Thanks

|||

justn_m87 wrote:
I guess I get kind of confused on the user accounts and permissions needed.

I think you are just confused about the permissions needed to run SQL Server Agent dependent services and you are not alone, Microsoft just got to accept the permissions for SQL Server Agent because their customers could not get replication in SQL Server to work following their configuration guidelines. I don't know what to tell you DTS in Asp.net with stored proc must give SQL Server Agent correct permissions. You cannot use the IIS account because so many services in SQL Server are SQL Server Agent dependent so running IIS and SQL Server Agent on the same account is just not good practice. Hope this helps.|||Hello
Could U plz help me out of this problem
The problem is while executing a Dts package from aspx page
It is giving the error "Error System.Runtime.InteropServices.COMException (0x8004040E): Invalid GUID specified"
the code is :-
package.LoadFromSQLServer("(local)", "myuser", "mypwd",DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","DB8112F5-7279-486E-97AF-ACBC333A611A","D4573855-FDC6-46B7-B644-04058B49299F", "OutterSync", "")
I have given the servername,username, password,GUID, Version ID,packagename and there is no owner password and user password for thepackage.
It is working fine with dtsrun utility with the same guid and ver id, but it is giving error Invalid GUID from asp.net.
Thanks
vikky

Execute DTS package from ASP failed

I am trying to run DTS Package from ASP 3.0 with the following codes:

Set objDTSPackage = server.CreateObject("DTS.Package")
objDTSPackage.LoadFromSQLServer "serverName", "", "", 256,,,,"pkgName"
objDTSPackage.FailOnError = true
objDTSPackage.Execute

It failed with this message, "Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed".

I am using NT Authentication in both IIS and SQLServer, and both Web and SQLServer are in the same machine.

Can someone help? Thank you.The DTS package must be executed in the same security context as the web page. In ASP web pages are executed under the IIS account that begins with IUSR_. You can find this account name by right clicking My Computer and clicking Manage. Expand Local Users and Groups and look in the users folder.

Then set up a SQL server account for this login like 'MyComputerName\IUSR_...'.

Be careful to only give this account public access to the database the DTS package is accessing and I even go to the trouble of restricting permission to the specific tables.|||ps In the SQL account change the default database to the affected database (not master) and do not give it a server rol for security reason|||Thanks for the prompt reply.

I am using NT Authentication in IIS for all my web applications, does this matter?

I've also tried creating SQL Server login ID with 'myMachineName\IUSR_...', but my company's system wouldn't allow me do this - for security reason.

Any alternatives?|||No it should'nt matter.

are you not allowed to create SQL logins?

or is it giving an error?

try 'yourdomain\youriusraccount'. It might work.

The only other alternative using DTS I know creates a big ugly security hole for hackers to climb in. It invloves firing the DTS package from a shell command and going into the SQL agent properties and setting up a proxy account under the job system tab. The ramifications are ugly.|||I do have the rights to create login ID, but this is something to do with different domain in the system, and I guess not all users in certain domain can be created.

Let me clarify a bit, when you say, create login ID for 'MyComputerName\IUSR_...', you do mean to say the computer that I am personally using now, correct? Not the computer name with IIS installed, right?

I have to find a way to create login ID for this 'IUSR_...', so if this is created, I can use the ASP codes I showed to execute DTS?

Thanks.|||MyComputerName is the name of the computer where your IIS install is.

If you do this your code will work. I've incorporated DTS into 3 ASP projects in the last 4 years.|||Thank you.

I'm still tryihg find "IUSR_" for my web server in SQL Server login creation. Is there any possible reason you can think of that I can't find the "IUSR_" on SQL Server, "New Login"?|||Just want to update and close this:

I've found the IUSR account for the new login to SQL Server. The domain of this IUSR account will be the name of the server (since IUSR is a local user). I've found it there. Thanks.|||did it work?|||Yes and No, Sorry for this terrible answer. Thank you for keep interests in this.

I have used client-side VBscript to make it work - instead of server-side ASP code, I guess this bypasses the IIS issues (?). I am a bit concerned of the security, other than all the stuffs I am still learning. I think I will come back with ASP server-side code to make it work.

HOWEVER, I am having another problem. After I executed successfully the DTS Package, my query and subsequent SQL operations to the destination table don't work anymore. It gives no error messages, but it is not doing anything. A simply query to check the row count, comes back with 0 rows, even though the DTS has transform records to the table.

Help me, if you have any idea? I am not sure where the problem is coming from?!|||how do you know the DTS package executed successfully?

does it work when you fire it from the EM (i.e. rows in the table)?

what is the code you are using to execute the DTS?

i am betting this is another permissions issue and has to do with this client side script you have.

here is some vbscript that should tell you where the package is failing. Depending on how you are doing the package will not always throw errors.

http://www.sqldts.com/default.aspx?t=6&s=104&i=207&p=1&a=7

I converted this script to VB 6 that I wrapped in a COM object which is BTW how I accomplish this. See below.

Option Explicit

Private m_sError As String

Public Function Execute(ByVal sServer As String, ByVal sPackageName As String) As Boolean
On Error GoTo Err_Handler
Dim oPKG As DTS.Package, oStep As DTS.Step
Set oPKG = New DTS.Package

Dim lErr As Long, sSource As String, sDesc As String

Execute = True

' Load Package
oPKG.LoadFromSQLServer sServer, , , _
DTSSQLStgFlag_UseTrustedConnection, "!J1LLYB3@.N!", , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
m_sError = m_sError & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
"Error: " & lErr & vbCrLf & _
"Source: " & sSource & vbCrLf & _
"Description: " & sDesc & vbCrLf & vbCrLf
Execute = False
Else
m_sError = m_sError & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Clean_Up:
Set oStep = Nothing
Set oPKG = Nothing

Exit Function

Err_Handler:
Execute = False
m_sError = "Error in Object Execution" & vbCrLf & _
"Number: " & Err.Number & vbCrLf & _
"Source: " & Err.Source & vbCrLf & _
"Description: " & Err.Description
GoTo Clean_Up
End Function

Public Function GetErrorDetails() As String
GetErrorDetails = m_sError
End Function|||Before DTS starts, the destination table is cleared, then after the DTS, I went into EM to check, and they are rows in the tables. I assume that means DTS is working.

And this is my client-side VB code:

Dim objDTSPackage,
On Error Resume Next
Set objDTSPackage = CreateObject("DTS.Package")
objDTSPackage.LoadFromSQLServer "server", "", "", 256,,,,"pkgName"
objDTSPackage.FailOnError = true
objDTSPackage.Execute
objDTSPackage.UnInitialize()
Set objDTSPackage = Nothing

I will also study your codes. Thank for your help.|||Not sure if anyone would still read this long thread, but...

I've made this to work by putting codes into a VB DLL.

However, if I execute a simple SELECT statement against the destination table after the DTS execution is completed. It gives no error, but with no result either.

But if I reload the ASP page immediately, in other words, DTS is executed the second time, the SELECT statement produces the results from the FIRST DTS execution, instead of coming from the 2nd DTS execution!?

It seems to me the first DTS exectuion doesn't exist - as far as the SELECT statement is concerned.

Anyone has idea? Please help.|||rweide,
I am still here. watching this forum keeps me from working on my current ptoject which requires no effort or creativity and is boring me to death. So this is how I pass my days.

Might need to see some code here. Are you executing the DTS package and the select on the same post to the server?|||I'm so happy that you're bored!!! :)

I've decided just copy your codes into VB and created the DLL. But at the point, I'm only using client-side VBScript to call the DLL, which sits locally on my machine - I will move it to server whenl all these problems is resolved.

The DTS package is very simple - move the data from a flat text file to a holding/destination table, and it uses Windows authentication. And 2 SQL stored procedures are executed to check row count (to see if DTS works at all) and to update the main DB table using data from the temporary holding table after DTS is run.

As I've mentioend, the simply query give no result (row count = 0) and no error. But by checking thru EM, I know the DTS has populated the destination table. If I reload the ASP page (running DTS again), the query gives the result from the first DTS execution!!

Here is the abbreviation of the codes:

<Body>
<%
On Error Resume Next
Set Session("adoCnn") = server.CreateObject ("ADODB.Connection")
Session("adoCnn").ConnectionString = "connection string here"
Session("adoCnn").Open
%>

<Script Language=VBScript>
Set mc = CreateObject("mc.mcDTS")
mc.Execute
document.write mc.GetErrorDetails
set mc = nothing
</script>
<%
Response.Write "<BR><BR> Row Count in Holding Table: " & CountRows()
Call UpdateManual()

Response.Write "<BR><BR>Count Close Table: " & CountClose()
If Err.number = 0 Then
response.write "<BR><BR>Updated from Holding table to Main Table completed..."
End If
%>
</Body>
</HTML>
<%
Function CountRows()
Dim cmd, rs, Parm1

On Error Resume Next
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

rs.CursorType = adOpenStatic
cmd.CommandText = "spCountHoldingRows"
cmd.CommandType = adCmdStoredProc
' cmd.CommandText = "SELECT COUNT(*) row FROM tbl_mc_Monthly_Close_Holding"
' cmd.CommandType = adCmdtext
Set Parm1 = cmd.CreateParameter("return", adInteger, adParamReturnValue)
cmd.parameters.append parm1
cmd.ActiveConnection = Session("adoCnn").ConnectionString
set rs = cmd.Execute
If err.number <> 0 Then
Response.Write "<BR>Count Rows - " & err.number & " - " & err.Description
End If
CountRows = cmd(0)
Set cmd = Nothing
set rs = nothing
End Function

Function CountClose()
Dim cmd, rs, Parm1

On Error Resume Next
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

rs.CursorType = adOpenStatic
cmd.CommandText = "spCountCloseRows"
cmd.CommandType = adCmdStoredProc
Set Parm1 = cmd.CreateParameter("return", adInteger, adParamReturnValue)
cmd.parameters.append parm1
cmd.ActiveConnection = Session("adoCnn").ConnectionString
set rs = cmd.Execute
If err.number <> 0 Then
Response.Write "<BR>Count Close Rows - " & err.number & " - " & err.Description
End If

CountClose = cmd(0)
Set cmd = Nothing
set rs = nothing
End Function

Sub UpdateManual()
Dim cmd

On Error Resume Next
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = "spUpdateMain"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeOut = 1800
cmd.ActiveConnection = Session("adoCnn").ConnectionString
cmd.Execute
If err.number <> 0 Then
Response.Write "<BR>UpdateManual - " & err.number & " - " & err.Description
End If
Set cmd = Nothing

End Sub

%>

And I don't really think the combination of client-side and server-side codes will mess up the operation. I remember at one time, I changed all codes to be executed on the client-side, and it still doesn't work.

I wonder if this has anything to do with memory?|||I am not sure where to start.

Where does your database reside? First, get you database, web server, and DTS package all on the same server. (professionally I reccomend having the web and database server on different machines with a firewall between the 2).

Change this back to server side script:

<Script Language=VBScript>
Set mc = CreateObject("mc.mcDTS")
mc.Execute
document.write mc.GetErrorDetails
set mc = nothing
</script>

Next hide your connection string in an include file in another directory that your IUSR account does not have access to.

Finally and this meant to be constructive criticism, you write too much code. Keep it simple and concise. do not use functions unless you have reptitive code. Look at this below. It works. I just did it.

<HTML>
<Head>
<Title>My Module</Title>
</Head>
<BODY>
<H2>Sourcing Module</H2>
<H3>Upload Bibliography Data to Sterling</H3>
<%
Dim mySmartUpload
Set mySmartUpload = Server.CreateObject("aspSmartUpload.SmartUpload")
Server.ScriptTimeout = 10000
mySmartUpload.Upload
mySmartUpload.Save("/downloads")
Set mySmartUpload = Nothing
Dim oExecDTS
Dim sServer, sPackageName
Dim bResult
sServer = "MySQLServer"
sPackageName = "Mydts"
Set oExecDTS = CreateObject("SQLDTS_ExecDTS8.ExecutePackage")
bResult = oExecDTS.Execute(sServer, sPackageName)
If bResult Then
Response.Write "<p>Package " & sPackageName & " succeeded</p>"
Else
Response.Write "<p>Package " & sPackageName & " failed</p>"
Response.Write "<p>" & Replace(oExecDTS.GetErrorDetails, vbCrLf, "<br/>") & "</p>"
End If
Set oExecDTS = Nothing
Dim con,rs,constring,sql
Set rs = Server.CreateObject("ADODB.Recordset")
Set con = Server.CreateObject("ADODB.Connection")
sql = "SELECT count(*) as thecount FROM DP_PRELIMBIB"%>

<%con.Open constring
rs.Open sql, con
Response.Write rs("thecount")
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
%>
</BODY>
</HTML>

aspSmartUpload is a third party component I use to upload and download files for personal applications.

Monday, March 26, 2012

Executing an asp page from sql server.

Executing an asp page from sql server.

Friday, March 23, 2012

Executing a SSIS Package from an ASP.NET page.

Executing a SSIS Package from an ASP.NET page.

Executing a SSIS Package from an ASP.NET page.

Executing a SQL Script with MSDE

Thursday, March 22, 2012

Executing a DTS Package using an ASP (VBScript)

Wednesday, March 21, 2012

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

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

Monday, March 19, 2012

ExecuteReader Command

ExecuteNonQuery in SQL 2005

Monday, March 12, 2012

Execute SSIS Package from ASP.net 2 Application?

Execute SSIS package from asp.Net

Sunday, February 26, 2012

Execute Permission denied when running from IIS

Friday, February 24, 2012

EXECUTE permission denied

EXECUTE permission denied

Friday, February 17, 2012

Execute Integration Package by SP

Execute DTS Package from Asp.net

Wednesday, February 15, 2012

Execute DTS Package From ASP.NET

Execute DTS package from ASP failed

EXECUTE AS question

Blog Archive

About Me

Monday, March 26, 2012

Friday, March 23, 2012

Thursday, March 22, 2012

Wednesday, March 21, 2012

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

Monday, March 19, 2012

Monday, March 12, 2012

Sunday, February 26, 2012

Friday, February 24, 2012

Friday, February 17, 2012

Wednesday, February 15, 2012

Blog Archive

About Me

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