Executing SQL Package

Hi

I have created a DTS package in SQL 2005 which will pull the data from oracle and pushes into SQLServer. I am able to execute the package from business intelligence wizard.

Is there anyway to trigger this package manually apart from the wizard?.

looking for responses.

Thanks
GaneshIt can also be executed from query analyzer.

You call it from any forntend tool also.|||Hi

Thanks for your response.

When I create package from Business intelligence wizard, It was created as Package1.dtsx. Now , How can I invoke package1.dtsx from query analyzer?.

As far as I know, In oracle, a package will contain main and a body procedures. We can invoke the main procedure from any command prompt. But I am new to SQL packages. Can you suggest me in this regard.

Thanks,
Ganesh

Executing sp on other sqlserver

It's possible ?
From SQLServer1, execute sp_addsubscriber of SQLServer2 ?
Thanks,
PePiCKTry Linked servers.
http://www.databasejournal.com/feat...cle.php/3085211
"PePiCK" wrote:

> It's possible ?
> From SQLServer1, execute sp_addsubscriber of SQLServer2 ?
> Thanks,
> PePiCK
>
>

Executing package from C# on 64bit platform

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

sql

Executing package from C#

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

Executing N procedures in 1 Round trip

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @.Campaign, @.Contact 20 times how would I pass in the values for those 20 diffrent versions?

You could pass all the parameters to another stored procedure as a delimited list and parse them over there, create a loop, and call the stored proc in the loop.

|||I'm sure I could. I just thought I'd heard of something similar to ODP.Net's ArrayBinding syntax for SqlServer. I'm fairly sure I wasnt thinking about bulkcopy.|||

To my knowledge I dont think there is any standardized way to make multiple calls in one trip. Perhaps someone else here knows if there's any new feature in 2005. I havent been doing much development in 2005.

|||hrm...

Will the SQL Server provider let me execute blocks of t-sql?

eg something like:

AddContact(@.C1, @.U1);
AddContact(@.C2, @.U2);
AddContact(@.C3, @.U3);
...
AddContact(@.CN, @.UN);
go;|||

Yes, however, you must specify that the call type is text, not stored procedure, and you must properly format your calls like:

EXECUTE AddContact @.C1,@.U1
EXECUTE AddContact @.C2,@.U2
...
GO

|||So what is the "proper" format for sp calls in a 'anonymous block'.

Executing DTS Package - Error not able to find the package.

I am running Sqlserver 2005 and IS.
Here is the SP I am running.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: Name
-- Create date:
-- Description:
-- ========================================
=====
ALTER PROCEDURE [dbo].[CallRiskDataDTS]
-- Add the parameters for the stored procedure here
@.loaddate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.DTSPackageObject int
DECLARE @.HRESULT int
DECLARE @.property varchar(255)
DECLARE @.return int
DECLARE @.ErrDescrip varchar(255)
DECLARE @.ErrSource varchar(30)
DECLARE @.ErrHelpId int
DECLARE @.ErrHFile varchar(255)
DECLARE @.ErrMsg varchar(255)
Declare @.FileFilter varchar(8)
DECLARE @.sDTSPackagePath varchar(1000)
DECLARE @.sDTSSpecialUser varchar(50)
if not IsDate(@.loaddate) = 0
Begin
select @.loaddate = getdate()
End
select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(mm,@.loaddate)))),'') +
convert(varchar(2), Datepart(mm,@.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(dd,@.loaddate)))),'') +
convert(varchar(2), Datepart(dd,@.loaddate))
SELECT @.ErrMsg = 'Error running DTS package'
-- Create a DTS Package object
EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR (@.ErrMsg,11,1)
RETURN
END
EXEC @.HRESULT = sp_OAMethod
@.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolData
Upload",
Nothing)', NULL --, @.flags=256,
@.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
@.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
@.PackagePassword =''
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
OUTPUT,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrDescrip,11,1)
RETURN
END
-- Set the FailOnError property to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Set
to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
@.FileFilter --Set to true
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
-- Call the EXECute method
EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
--remove the object from memory
EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
END
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
I am getting the following message
Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
{}.{}') does not exist. I want to set this up so I can execute is from an
ASP.NET page. I am unable to find the cause of it.
In my SQL Management Studio, connect to Integration services, I see the
package in STORED PACKAGES , MSDB tree.
Any help is greatly appreciated.
ThanksFor that matter I am not able execute any package, I am getting the same
error. Thanks
Raj.
"Raj25" wrote:

> I am running Sqlserver 2005 and IS.
> Here is the SP I am running.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
>
>
> -- ========================================
=====
> -- Author: Name
> -- Create date:
> -- Description:
> -- ========================================
=====
> ALTER PROCEDURE [dbo].[CallRiskDataDTS]
> -- Add the parameters for the stored procedure here
> @.loaddate datetime
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> DECLARE @.DTSPackageObject int
> DECLARE @.HRESULT int
> DECLARE @.property varchar(255)
> DECLARE @.return int
> DECLARE @.ErrDescrip varchar(255)
> DECLARE @.ErrSource varchar(30)
> DECLARE @.ErrHelpId int
> DECLARE @.ErrHFile varchar(255)
> DECLARE @.ErrMsg varchar(255)
> Declare @.FileFilter varchar(8)
> DECLARE @.sDTSPackagePath varchar(1000)
> DECLARE @.sDTSSpecialUser varchar(50)
> if not IsDate(@.loaddate) = 0
> Begin
> select @.loaddate = getdate()
> End
> select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(mm,@.loaddate)))),'') +
> convert(varchar(2), Datepart(mm,@.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(dd,@.loaddate)))),'') +
> convert(varchar(2), Datepart(dd,@.loaddate))
> SELECT @.ErrMsg = 'Error running DTS package'
> -- Create a DTS Package object
> EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR (@.ErrMsg,11,1)
> RETURN
> END
> EXEC @.HRESULT = sp_OAMethod
> @.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolDa
taUpload",
> Nothing)', NULL --, @.flags=256,
> @.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
> @.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
> @.PackagePassword =''
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
> OUTPUT,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrDescrip,11,1)
> RETURN
> END
> -- Set the FailOnError property to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Se
t
> to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
> @.FileFilter --Set to true
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> -- Call the EXECute method
> EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> --remove the object from memory
> EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
> END
>
> SET QUOTED_IDENTIFIER OFF
> SET ANSI_NULLS ON
>
> I am getting the following message
> Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
> The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
> {}.{}') does not exist. I want to set this up so I can execute it from a
n
> ASP.NET page. I am unable to find the cause of it.
> In my SQL Management Studio, connect to Integration services, I see the
> package in STORED PACKAGES , MSDB tree.
> Any help is greatly appreciated.
> Thanks
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

Executing a DTS package

Hi and TIA. What I'm trying to do is create a DTS package in SQL
Server(2K). What I then want to do is have my users locate the
database(Access2K) file, upload the file to a virtual directory, and then
fire off the DTS package to import the data from the access tables into
existing tables in SQL server. I can't seem to find anything on how to do
this from my web app. Is this possible and if so if you could point me in
the right direction for my research and or provide any sample basic codes
snippets as to how to accomplish this task would be greatly appreciated.
Using .net v1.1, vb.net and sql server 2000. Thank you for your time.
******************
ReggieYou could have the DTS package stored as a job in Sql Server, then call
this job from a stored procedure. To call a job from inside a stored
procedure, the syntax is:
EXEC msdb.dbo.sp_start_job @.job_name = 'Put your job name here'
Then just call the stored procedure as normal from your asp.net code
Hope this helps :)
James|||Awesome. I'll give it a try. Thanks James!
******************
Reggie
"jamesb" <jxb@.ovum.com> wrote in message
news:1138965419.571185.296990@.g44g2000cwa.googlegroups.com...
> You could have the DTS package stored as a job in Sql Server, then call
> this job from a stored procedure. To call a job from inside a stored
> procedure, the syntax is:
> EXEC msdb.dbo.sp_start_job @.job_name = 'Put your job name here'
> Then just call the stored procedure as normal from your asp.net code
> Hope this helps :)
> James
>

executing (from sql server 2000) procedures from oracle package through linkserv

Hello,
I deaply need to know how to execute procedures from package in oracle, from sqlserver 2000 using linkserver.
Thank you very much,
Victor
DBAJust ot clarify: are you trying to execute an Oracle Stored procedure using SQL Server with the Oracle server configured as a Linked Server?

Or are you trying to execute a SQL stored procedure from Oracle?

Sorry, I wasn't entirely clear on your intent from your question.

Regards,

hmscott|||Well .. you cant directly execute a stored procedure in ORACLE from SQL Server

If you really need to do that .. here is a get around

1. Create a table in Oracle .. lets say execProc
2. On update of the table make a trigger in oracle to execute a stored procedure.
3. Update the oracle table through linked server query

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

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

Output from the program:

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

//--

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

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

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

//

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

//

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

//

//--

using System;

using System.Diagnostics;

using Microsoft.SqlServer.Dts.Runtime;

namespace Misc

{

/// <summary>

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

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

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

/// </summary>

static class ExecPackage

{

static void DisplayUsage()

{

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

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

}

static void Main(string[] args)

{

// Parse command line arguments.

if (args.Length != 1)

{

DisplayUsage();

return;

}

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

// Programmatically execute the package several times.

Application app = new Application();

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

{

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

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

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

pkg.Dispose();

pkg = null;

// Do garbage collection, then display memory usage

GC.Collect();

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

i, GC.GetTotalMemory(true));

}

}

}

}

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

|||

Craig,

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

Thanks,

Bin

|||

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

|||

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

Thanks,

Drew

|||

dferraro wrote:

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

Thanks,

Drew

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

Thanks again,

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

Hello,

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

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

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

Kind regards, Jeroen

execute SSIS package stored in remote SQL server from a client machine.

I have written a VB component to execute a SSIS package stored in SQL server.

I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand

and executereader and everything works perfect on the local machine. This is descibed in a msdn site .

But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.

My question is: Can you still use dtsclient or do you have to do in some other way?

rose-marie

It depends where your VB component is going to be running. You will need SSIS installed on the same machine.

-Jamie

|||

The whole idea was to run the VB component in a client machine without any extra i.e without SSIS.

I understand that this is not possible, is it possible to achieve this in another way?

rose-marie

|||

Can you not run the component somewhere else?

|||

No, in this case there is a demand on a stand-alone windows application

without any dependencies other than oledb connections to SQL server.

Otherwise I would have liked a web service in the server machine or something like that, but...

rose-marie

execute ssis and loggin with sqlserver provider cause error

Hi,

I execute my ssis from BI without problem (debug mode), but when I import into SQL server and try to run with logging on sqlserver provider always my package failure.

If option of logging is empty all works fine (No one logging method is active).

There is some permission to set for this option?

I use into SiSS ole db native client connection.

Hope some one can help me.

Alen Italy

This belove is the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "La transazione distribuita è stata
completata. Integrare questa sessione in una nuova transazione o nella
transazione Null.".

How exactly are you running the package, and what security is the connection manager used by the log provider set to use?

|||

I create the package on server log-in with the administrator user anda connect the Sql Server management with a windows autentication...so are the same level user.

With no looggin method works fine...with no one error.

I running my package from SQL server management studio...under the folder STORED PACKAGES > MSDB > MYFOLDER > MyPack and right clic on "run package".

Below the connection string:

DB1
Data Source=SQLTEST;Initial Catalog=NEW_LISTINI;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

DB2
Data Source=SQLTEST;Initial Catalog=NEW_PANGEA;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

EXCELL FILE
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\manny-slave\Fornitori\Listini\Altri\\Ready_4_Import\ePRICE.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;MAXSCANROWS=8;";

I translate in english the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "The distributed transaction has been completed. To integrate this session in one new transaction or the Null transaction"

The comand line -

/DTS "\MSDB\DTS to SSIS\NEW_LISTINI_IMPORTA_XLS" /SERVER SQLTEST /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"SQLTEST.NEW_LISTINI" /SET "\Package.Variables[arrIDsources].Value";5

_ after many test __

I try many combinations of transaction option.

If I set package "required" works with logging on DB server, but If active "required" on two containers alwails falls.

Seems been not another way for loogging into SQLSERVER.

Or SET all transactions of containers to "supported" or set "Required" at packege level...

If somone heve another vision...here I'm to hear.
Alen

Execute permission denied on 'sp_xml_preparedocument'

Hello,
We have hosted our .NET application on a windows 2003 server runing SQL
Server 2000. We have a stored procedure that uses
"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
is throwing the following error in one of the testing environment, but
working fine in the development environment, Any Ideas ?
Failed to update company details. Error message: EXECUTE permission
denied on object 'sp_xml_preparedocument',
database 'master', owner 'dbo'. Could not find prepared statement with
handle 0.
EXECUTE permission denied on object 'sp_xml_removedocument', database
'master', owner 'dbo'.
The statement has been terminated.
Thanks in Advance.
With Regards,
Sharath.Whoever you're logging in as in the test environment needs execute
permissions on the sp. You can easily check (and change) the permissions on
it in EM and compare to your other server. You can also change the
permissions through QA (GRANT statement).
"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
> Hello,
>
> We have hosted our .NET application on a windows 2003 server runing SQL
> Server 2000. We have a stored procedure that uses
> "'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
> is throwing the following error in one of the testing environment, but
> working fine in the development environment, Any Ideas ?
>
> Failed to update company details. Error message: EXECUTE permission denied
> on object 'sp_xml_preparedocument',
> database 'master', owner 'dbo'. Could not find prepared statement with
> handle 0.
> EXECUTE permission denied on object 'sp_xml_removedocument', database
> 'master', owner 'dbo'.
> The statement has been terminated.
>
> Thanks in Advance.
> With Regards,
> Sharath.|||Mike C# wrote:
> Whoever you're logging in as in the test environment needs execute
> permissions on the sp. You can easily check (and change) the permissions
on
> it in EM and compare to your other server. You can also change the
> permissions through QA (GRANT statement).
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
>
>
>
Thanks Mike for the reply. I am not well versed with SQL commands nor I
have access to the Server thru EM, If you could please send the script
(GRANT), that would be very helpful.|||"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks Mike for the reply. I am not well versed with SQL commands nor I
> have access to the Server thru EM, If you could please send the script
> (GRANT), that would be very helpful.
USE master
GO
GRANT EXEC ON sp_xml_preparedocument TO public
GO
Of course you can change "public" above to another role/user. More
information is available in BOL:
http://msdn.microsoft.com/library/d...br />
8odw.asp|||Thank you Mike. I will try that.
Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/d... />
z_8odw.asp
>

Execute permission denied on 'sp_xml_preparedocument'

Hello,
We have hosted our .NET application on a windows 2003 server runing SQL
Server 2000. We have a stored procedure that uses
"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
is throwing the following error in one of the testing environment, but
working fine in the development environment, Any Ideas ?
Failed to update company details. Error message: EXECUTE permission
denied on object 'sp_xml_preparedocument',
database 'master', owner 'dbo'. Could not find prepared statement with
handle 0.
EXECUTE permission denied on object 'sp_xml_removedocument', database
'master', owner 'dbo'.
The statement has been terminated.
Thanks in Advance.
With Regards,
Sharath.
Whoever you're logging in as in the test environment needs execute
permissions on the sp. You can easily check (and change) the permissions on
it in EM and compare to your other server. You can also change the
permissions through QA (GRANT statement).
"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
> Hello,
>
> We have hosted our .NET application on a windows 2003 server runing SQL
> Server 2000. We have a stored procedure that uses
> "'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
> is throwing the following error in one of the testing environment, but
> working fine in the development environment, Any Ideas ?
>
> Failed to update company details. Error message: EXECUTE permission denied
> on object 'sp_xml_preparedocument',
> database 'master', owner 'dbo'. Could not find prepared statement with
> handle 0.
> EXECUTE permission denied on object 'sp_xml_removedocument', database
> 'master', owner 'dbo'.
> The statement has been terminated.
>
> Thanks in Advance.
> With Regards,
> Sharath.
|||Mike C# wrote:
> Whoever you're logging in as in the test environment needs execute
> permissions on the sp. You can easily check (and change) the permissions on
> it in EM and compare to your other server. You can also change the
> permissions through QA (GRANT statement).
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
>
>
Thanks Mike for the reply. I am not well versed with SQL commands nor I
have access to the Server thru EM, If you could please send the script
(GRANT), that would be very helpful.
|||"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks Mike for the reply. I am not well versed with SQL commands nor I
> have access to the Server thru EM, If you could please send the script
> (GRANT), that would be very helpful.
USE master
GO
GRANT EXEC ON sp_xml_preparedocument TO public
GO
Of course you can change "public" above to another role/user. More
information is available in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
|||Thank you Mike. I will try that.
Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
>

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 ADO in VB

I'm using ADO objects in a VB Macro for Excel. I'd like to execute a DTS package located on SQLServer.

What is the syntax to do this?

Here's my current database connection code:Option Explicit

Dim db_connection As ADODB.Connection
Dim db_results As ADODB.Recordset
Dim db_error As ADODB.Error

Private Sub DB_Initialize()
Set db_connection = New ADODB.Connection
db_connection.Open "Provider='SQLOLEDB';Data Source='BACK_SQL';" & _
"Initial Catalog='my_db';Integrated Security='SSPI';"
Set db_results = New ADODB.Recordset
End SubIs it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?|||Originally posted by odinsdream
Is it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?

I don't know very much about VB but I definately know my DTS packages.. yes you can execute a DTS package via a stored procedure

server_name=server name dts package is on
user_name=login to access server
password=user's password
package_name = DTS package name
package_password=DTS package pwd

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name" /Mpackage_password'

GO
-------

if your package does not have a pwd then your sp should look like this

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name"

GO|||So how would one execute that stored procedure from VBA for Access?