EXECUTE AS question: client

Showing posts with label client. Show all posts

Tuesday, March 27, 2012

Executing Package Question

Hi,

Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP

1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?

Thanks a lot for any help you could provide!

John

1 - Yes

2 - Not sure, maybe, but I would just install it all, some stuff will not work unless it is all there correctly.

3 - On your workstation. SSIS is a client-side hosted process, so it runs under the context of the excution host, which means the same machine and security context as teh user running dtexec, BIDS, or when scheduled it use the same rules as for the SQL Server Agent job context, normally the service account, or maybe the proxy account if set.

4 - What issues?

|||

Hi there,

As far as my experience goes the following :

1. you need to have the developer edition op SQL SERVER 2005 to be able to run your packages from the command line With the regular enterprise edition you can only run them under devenv

3. You can only install SQL SERVER 2005 Integration services on a server not on a workstation

Greetings,

Paul

|||Thanks for the info!

The issues I mean are only being able to run an SSIS package via the command line from a server with SSIS installed. With DTS2000, I could use a command on any box that would specify the SQL Server the DTS package resided on. Is this lost functionality with 2005?|||

John, DTS could be installed as a client redistributable, and required a CAL only. SSIS is a licensed as part of the server and has a slightly more involved install, it is not redistributable. You can install SSIS only, you don't need tools or SQL Server etc, but you need to license the machine as a SQL Server.

Paul, sorry but wrong on both counts. Executing packages from the command line is available in all SQL Server supporting SSIS editions, Dev, Std and Ent editions. (The three editions that include SSIS). You can run SSIS on a workstation, the Developer edition is just for that scenario, and *all* components can be installed. People often install SQL Server workstation components only, and forget the SSIS service which causes issues. Just search this forum!

|||Darren,

Thank you for the information. As I understand it, I can't execute an SSIS package via the command line utility unless I'm doing so on a Server OS (i.e. not XP) and have SSIS running.

That being said, is there a work around method that would allow me to run an SSIS package from the command line utility without having a Server OS (i.e. XP) and without SSIS running? You mentioned something about a "client redistributable" and a "CAL", but I'm not sure what you mean. Is what I want to do possible? I'm basically trying to preserve the functionality and processes we had in place before we upgraded from 2000 to 2005.

Thanks again!

John|||Does anyone have a work around for this?

Being able to call an SSIS package via the command line from a workstation that is executed on the server. (This is what I do with DTS2000.)

Thanks!

John|||

For the DTS 2000, unless you're doing a remote execution or starting a job, you're actually loading that DTS package from the server to your workstation and then running it on your workstation. DTExec I believe works the same way on SQL 2005, i.e. if run on your desktop, that's where the package will execute.

|||I believe I found a suitable solution to my scenario. I can use the sqlcmd.exe at the command line to execute a stored procedure on the SQL Server running SSIS with the packages. I just need to set up a SQL Agent with the SSIS package as a step without a schedule. The built-in stored procedure for executing a SQL Agent should take care of it. Thoughts? Thanks!

John|||

Larry is correct. DTS is no different from SSIS in the execution location and behaviour of command line tools, the only difference for you to be aware of is the is the licensing. You still needed to install DTS on the workstation to get DTSRUN, as you need to install SSIS to get DTEXEC.

To execute packages on a server, the job method works well. It is asynch, and gives no progress, but it absracts you away from the workstation and also the user security context of that which I see as a good thing.

|||

If you're trying to execute an SSIS package via the job scheduler but don't want to use the built in job type for some reason, you could just set up a OS Command and use DTExec to execute it and you are correct that the job would need no schedule.

You can start the job with sp_start_job assuming the user has permissions to that and the job or set up a stored procedure with an EXECUTE AS to elevate priviledges. Just be careful what accounts you give the ability to execute that stored procedure.

|||Great - thanks for the confirmation!

I have a follow-up question though. With DTS2000, I was able to execute a command and it would return witha success/failure code when the package would end. My programs would wait for that message until continuing with other tasks. However, now with SSIS I get a message returned right away from the -Q "exec ..." statement, not when the SQL Agent and in turn SSIS package has succeeded/failed. Is there anyway to get this information returned? Thanks again for all the help!|||Anyone have a solution to this? Thanks!

Executing Package Question

Hi,

Thanks a lot for any help you could provide!

John

1 - Yes

2 - Not sure, maybe, but I would just install it all, some stuff will not work unless it is all there correctly.

4 - What issues?

|||

Hi there,

As far as my experience goes the following :

1. you need to have the developer edition op SQL SERVER 2005 to be able to run your packages from the command line With the regular enterprise edition you can only run them under devenv

3. You can only install SQL SERVER 2005 Integration services on a server not on a workstation

Greetings,

Paul

|||Thanks for the info!

|||Darren,

Thank you for the information. As I understand it, I can't execute an SSIS package via the command line utility unless I'm doing so on a Server OS (i.e. not XP) and have SSIS running.

Thanks again!

John|||Does anyone have a work around for this?

Being able to call an SSIS package via the command line from a workstation that is executed on the server. (This is what I do with DTS2000.)

Thanks!

John|||

|||

|||Great - thanks for the confirmation!

Monday, March 26, 2012

Executing DB2 Stored Procedures from Reporting Services

I have this international client using AS/400 with DB2 UDB as the main
database.
They are searching for a reporting tool. They liked the Reporting Services
capabilities and now we are trying to find a way to run DB2 Stored Procedures
from Reporting Services.
The client tried to reach this goal with the following providers with no
success:
1) ODBC for DB2
2) IBM OLE DB Provider for DB2
3) ODBC and OLE DB for iSeries
4) Microsoft OLE DB Provider for DB2.
In my laboratory environment I installed DB2 UDB Enterprise Edition
(evaluation) - V 8.1.7.445 on WIN/XP and on WINDOWS 2003.
I installed SQL SERVER 2005 with Reporting Services on both machines
(WINDOWS 2003, WINDOWS XP).
With "Microsoft OLE DB Provider for DB2, I succeed running the DB2 Stored
Procedures with no problem from both machines and from one machine to the
other as well.
I asked the client to uninstall all the providers and re-install the
"Microsoft OLE DB Provider for DB2".
He has done so and get the following message:
"failed to convert parameter value from a string to a Byte[] (System.Data)"
We tried the same with int parameter and with char parameter. The Stored
Procedure is a very simple one (in the DB2 - declare cursor with a simple
select and then open the cursor). Running the Sp from DB2 and in my lab
succeed.
Does anyone know how can I run DB2 Stored Procedures with no problem?
Which provider should I use?
Are there other clients running reports with MSRS against DB2 UDB on AS/400?
Many thanks,
Michelle.Hi Michelle,
We use DB2 v8.1 on unix and I am able to run stored procedures. It can be
a little qwerky at times. It sounds like you are running fine also. I dont
think I am using the Microsoft OLE DB provider for DB2 ... I believe it is
the IBM OLE DB Provider for DB2. It just says "OLE DB" in the type box and
the connection string looks like this:
Provider=IBMDADB2.1;Data Source=<dbNameHere>;Location=<ipAddressHere>
I know it works because that is what our main database is - DB2
"Michelle" wrote:
> I have this international client using AS/400 with DB2 UDB as the main
> database.
> They are searching for a reporting tool. They liked the Reporting Services
> capabilities and now we are trying to find a way to run DB2 Stored Procedures
> from Reporting Services.
> The client tried to reach this goal with the following providers with no
> success:
> 1) ODBC for DB2
> 2) IBM OLE DB Provider for DB2
> 3) ODBC and OLE DB for iSeries
> 4) Microsoft OLE DB Provider for DB2.
> In my laboratory environment I installed DB2 UDB Enterprise Edition
> (evaluation) - V 8.1.7.445 on WIN/XP and on WINDOWS 2003.
> I installed SQL SERVER 2005 with Reporting Services on both machines
> (WINDOWS 2003, WINDOWS XP).
> With "Microsoft OLE DB Provider for DB2, I succeed running the DB2 Stored
> Procedures with no problem from both machines and from one machine to the
> other as well.
> I asked the client to uninstall all the providers and re-install the
> "Microsoft OLE DB Provider for DB2".
> He has done so and get the following message:
> "failed to convert parameter value from a string to a Byte[] (System.Data)"
> We tried the same with int parameter and with char parameter. The Stored
> Procedure is a very simple one (in the DB2 - declare cursor with a simple
> select and then open the cursor). Running the Sp from DB2 and in my lab
> succeed.
> Does anyone know how can I run DB2 Stored Procedures with no problem?
> Which provider should I use?
> Are there other clients running reports with MSRS against DB2 UDB on AS/400?
> Many thanks,
> Michelle.

Wednesday, March 21, 2012

ExecuteScalar Problems. Need Help

Hi all

I am currently developing a Help Desk for our company. One of my problems is Data lookups in other tables within a SQL 2000 DB. i.e. Client Details and Information in one table (hd_clients) and Client History (hd_history) in another.

'hd_history' contains a column called 'c_id' which references the 'hd_clients' table 'c_id' column A typical One-to-Many relationship. When a user goes to the Help Desk's Service page. I want to display the client's name in one of my GridView's Databound Columns. See Below:

...

asp:TemplateFieldHeaderText="Client"SortExpression="c_id"> <ItemTemplate> <asp:LabelID="lblClient"runat="server"Text='<%#GetClient(Eval("c_id")) %>'/> </ItemTemplate></asp:TemplateField>

...

This then calls: GetClientName - Which is as follows.

...

Public

Function GetClientName(ByVal ClientID)Dim ScalarValueAsString =""Dim myConnectionAsNew SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")Dim myCommandAsNew SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @.ClientID", myConnection)

myCommand.Parameters.Add(

"@.ClientID", SqlDbType.Int)

myCommand.Parameters(

"@.ClientID").Value = ClientID Try

myConnection.Open()

ScalarValue = myCommand.ExecuteScalar

Catch exAs Exception

Console.Write(ex.Message)

EndTry

If ScalarValue >""Then Return ScalarValue.ToString Else Return"<span style='color: #CCCCCC'>- NULL -</span>" EndIf

EndFunction

...

This works perfectly on my Laptop (which runs the IIS and SQL Server Instances + VS2005). But, when placed on our production server brings back the '- null -' value instead of the Client's Name. I have set both machines up in exaclty the same way - and cannot get this to work. I have tried 'ExecuteReader' but from what I understand is 'ExecuteScalar' is better for single value lookups.

Any help in this matter would be great and really appreciated. Thanks.

David

Dave_Winchester:
ScalarValue = myCommand.ExecuteScalar

Since ExecuteScalar returns and object, try converting the result to a string before assigning it to the local variable.

|||

Thanks for the help. Now fixed - Also changed the Exception handling to be a bit better.

Dave

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.sql

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.
Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.

Monday, March 19, 2012

Execute windows on client machine

Hello.

How to execute add users window, backup wizard and other windows from
Enterprice Manager on client machine? Does MDAC or sth is able to do this?

bye...

--
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +----------+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+Hi

It is not clear exactly what you are wishing, but at a guess you have EM on
the client machine but your server is not registered using an account that
is sufficiently privileged.

You may want to write your own interface that calls the appropriate stored
procedures.

John

"[BuKoX]" <bukox.wytnij@.tlen.pl> wrote in message
news:186574153.20041223193834@.tlen.pl...
> Hello.
> How to execute add users window, backup wizard and other windows from
> Enterprice Manager on client machine? Does MDAC or sth is able to do this?
> bye...
> --
> __ __
> |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
> |__// |__|| | \\ \__// / \\ +----------+
> +[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+

Monday, March 12, 2012

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

Friday, February 17, 2012

Execute Integration Package from a C# Solution

Hi all
Does somebody know how I can execute a SSIS Package on a SQL 2005 Server?
I need to execute from a C# Solution which is running on a client.
Thanks for any comments
Best regards
Frank Uray

On the server, configure an unscheduled SQL Agent job. On the client, use ADO.NET to invoke the sp_startjob system stored procedure.

It would also be possible to create a simple CLR stored procedure that uses the Integration Services API to launch a package on the server. I am working on the sample code for this today.

-Doug

|||

Hi Doug

You mean that there is no Namespace for accessing SSIS Packages ?
On SQL Server 2000, there was this need dts Namespace (API) ...

Thanks anyway

Best regards
Frank Uray

|||

Yes, certainly there is a very complete Integration Services API, and you'll note that I mentioned it in my reply.

However I'm not certain, without testing, whether you can use the API from a client to execute packages. As you know, a package runs on the computer it is called from (therefore, in this case, the client), and NOT on the computer where it is stored.

Integration Services is now a server platform and no longer a redistributable tool like DTS. Although installing Tools on the client gives you enough of SSIS to design packages, they cannot normally be executed outside the IDE.

Running the package on the server where it is stored -- as the SQL Agent solution accomplishes -- actually avoids a lot of common issues with paths to resources that are not available from client computers.

-Doug

|||

Hi Doug

Thanks again for your answer !

Yes, I have seen it on your first replay but I don't want to execute through
a stored procedure ...
The idea is to execute the Package on the server,
as if you execute a stored procedure. They also don't run on the client.
For example: I make a SQL connection and than start the package async. on
this created connection ...

Do you have a sample code for executing a SSIS Package in a
stored procedure on the 2005 server?

Best regards
Frank Uray

|||

Using the API will launch the package wherever the API code runs...that's why you would want to put the code in a CLR stored procedure to ensure that it runs on the server.

I won't have my sample finished and tested until later today, but it's basically the same "run a package" code shown in "Running an Existing Package from a Client Application ," wrapped in a CLR procedure with input params for package name etc as needed.

using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppCS
{
class Program
{
static void Main(string[] args)
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;

pkgLocation =
@."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();

Console.WriteLine(pkgResults.ToString());
Console.ReadKey();

}
}
}

|||

Hi Doug

Thanks a lot, that is helping !

Would be happy to get your sample when you have it finished.

Best regards
Frank Uray

|||

Frank,

It looks like my idea of a CLR stored procedure to launch packages using the Integration Services API may not be feasible. I've run into too many errors and security complications trying to register the Runtime Wrapper interop assembly and the Runtime assembly in SQL Server before I can even start on the simple code.

It sounded like a simple and useful concept, but SQL CLR is extremely fussy about what's going to run inside the SQL process space (understandably).

-Doug

|||Doug,

I ran into the exact same issue you did. I needed to invoke a package from an asp.net app and wanted to do it via a CLR stored proc. Oh well...

I documented all my SSIS experiences in a series of articles:

http://www.blognet.info/weblogs/entry.php?u=adhalejr&e_id=849

Maybe that will provide some pointers.

Donnie

Wednesday, February 15, 2012

execute dts 2000 packgage under sql 2005

hello world,

i read some kb on msde that explain how to do the task in the title, but in sql 2005 client tool i can't see where to execute dts packgage in 2000 mode,

I don't think you can do that with client tools because SSIS(sql server integration services) is a service like the Agent and SQL Server service. I have got you the eval but try and get the developer edition it cost a few dollars online. The first link covers what you are looking for the second is eval software. Hope this helps.

http://www.databasejournal.com/features/mssql/article.php/3615516
http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

Hi,

Do you need to know what problem will happen when you run a SQL Server 2000 DTS package after you upgrade all the SQL Server 2000 instance to SQL Server 2005?

If so, the following KB article will be helpful.

http://support.microsoft.com/kb/919131/en-us

Tuesday, March 27, 2012

Executing Package Question

Executing Package Question

Monday, March 26, 2012

Executing DB2 Stored Procedures from Reporting Services

Wednesday, March 21, 2012

ExecuteScalar Problems. Need Help

ExecuteScalar closed my connection?

ExecuteScalar closed my connection?

ExecuteScalar closed my connection?

Monday, March 19, 2012

Execute windows on client machine

Monday, March 12, 2012

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

Friday, February 17, 2012

Execute Integration Package from a C# Solution

Wednesday, February 15, 2012

execute dts 2000 packgage under sql 2005

EXECUTE AS question

Blog Archive

About Me