Prev: InternetSetFilePointer
Next: MS Calendar Control
From: chuckdfoster on 3 Mar 2005 15:52
This is what I use from VB.NET. I have an app that imports and exports
Excel Spreadsheet for a user to analyze. The downside is that you have to
register a bunch of SQL Server DLLs on the client PC. You also have to add
'Microsoft.DTSPackage Object Library' (COM reference) as a reference to
your project.

Dim oPKG as New DTS.Package
'Set Parameter Values
Dim sServer as String = "YourServerName"
Dim sUsername as String = "YourUserName"
Dim sPassword as String = "YourPWD"
Dim sPackageName as String = "YourDTSPackageName
'Load Package
oPKG.LoadFromSQLServer(sServer, SUsername, sPassword, _
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , sPackageName)
oPKG.Execute()
oPKG.UnInitializer()
oPKG = Nothing

Here is where you can find the SQL Server DLLs to register on the client PC.
http://www.sqldts.com/Default.aspx?225 This site tells you where to find
them and which ones to register.

Hope this helps!

Chuck Foster

"Robbe Morris [C# MVP]" <info(a)turnkeytools.com> wrote in message
news:ujLAr3$HFHA.3484(a)TK2MSFTNGP12.phx.gbl...
> http://www.eggheadcafe.com/articles/20030923.asp
>
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
>
>
> "Carl Imthurn" <nospam(a)all.com> wrote in message
> news:%23qi7te$HFHA.3196(a)TK2MSFTNGP15.phx.gbl...
> > My apologies for cross-posting, but I'm not sure which newsgroup this
> > belongs in.
> >
> > I am attempting to execute a DTS package from Visual Basic, and I'm
> > encountering a problem.
> > In a nutshell, how does a low-level user (from a security standpoint)
> > execute a DTS package?
> >
> > ' here's the relevant part of the VB procedure:
> >
> > Dim pConnectionString As String
> > pConnectionString = "Provider=SQLOLEDB.1;Data Source=xyzzy;Initial
> > Catalog=Trident;User ID=Troll;PWD=plugh"
> > Dim conn As ADODB.Connection
> > Set conn = New ADODB.Connection
> > conn.ConnectionTimeout = 10
> > conn.ConnectionString = pConnectionString
> > conn.CommandTimeout = 30
> > conn.Open
> >
> > Dim comm As ADODB.Command
> > Set comm = New ADODB.Command
> > comm.CommandType = adCmdText
> > comm.ActiveConnection = conn
> > comm.CommandText = "EXEC AcademicAdmin..s_Test"
> > comm.Execute
> >
> >
> > -- here's the stored procedure being executed
> > CREATE PROCEDURE s_Test
> > AS
> >
> > if exists (select * from sysobjects where id =
> > object_id(N'[dbo].[dentrev1]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > drop table dbo.dentrev1
> >
> > exec master..xp_cmdshell 'DTSRun /~S 0x4AE5E2A4783E358A718ED290354476AC
> > /~U 0x04382A57E6267DBDFD1F9877D7A9BC7B /~P
> > 0x88FFF4913468C337CAE4A98BA18F0319 /~N
> > 0xF3E618F4AA771D65FDA455B0EF6D57299B878D8A403B8A29E7F0AFA6F66F1C1C'
> > -- END OF STORED PROCEDURE
> >
> >
> > When I run the VB code (which logs in to SQL Server as a low-level
user),
> > it runs until it executes the comm.Execute line and the following error
> > message appears:
> >
> > Run-time error '-2147217911 (80040e09)':
> > EXECUTE permission denied on object 'xp_cmdshell', database 'master',
> > owner 'dbo'
> >
> > However, when I add this user to the SQL Server role System
> > Administrators, the VB procedure works fine.
> >
> > What are my options at this point?
> > How do I get around the xp_cmdshell issue?
> > This user cannot be added to any of the administrator-level roles in SQL
> > Server.
> >
> > I found this on SQLDTS.com; is this a better way to go?
> >
> > ' simplified version
> > Private Sub SimpleExecutePackage()
> > Dim oPKG As New DTS.Package
> > oPKG.LoadFromSQLServer "MyServer", , , _
> > DTSSQLStgFlag_UseTrustedConnection, , , , "MyPackage"
> > oPKG.Execute
> > oPKG.UnInitialize
> > Set oPKG = Nothing
> > End Sub
> >
> > Thanks in advance --
> >
> > Carl
> >
>
>


From: Dan Guzman on 3 Mar 2005 20:18
> What is the "SQL Agent proxy"? Do I need to create a Windows account with
> a very specific username, or can I pick a name myself?

> By the way, I'm running SQL Server 7 SP3 -- my apologies for not
> specifying that in the original post.

I assumed you were running the latest version, SQL Server 2000. The proxy
account is not configurable in SQL 7 and is the local SQLAgentCmdExec
account that was created when you installed SQL Server. It is this account
that will need to be granted permissions on resources needed by your DTS
package.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Carl Imthurn" <nospam(a)all.com> wrote in message
news:OLTPt6AIFHA.904(a)tk2msftngp13.phx.gbl...
> Hi Dan --
>
> I have more experience on the SQL Server programming side of things than
> administration, so I have a few (hopefully not too) dumb questions for
> you. See inline.
>
> Dan Guzman wrote:
>
>> By default, only sysadmin role members can execute xp_cmdshell. For
>> non-sysadmin users to execute xp_cmdshell, you'll need to configure the
>> proxy account using the procedure below.
>>
>> 1) create a Windows account for the SQL Agent proxy
> What is the "SQL Agent proxy"? Do I need to create a Windows account with
> a very specific username, or can I pick a name myself?
>>
>> 2) uncheck the 'only users with sysadmin ...' checkbox in Enterprise
>> manager under SQL Agent properties/Job system.
>>
>> 3) specify the proxy account as the Windows account created in step 1
> I looked around in Enterprise Manager and could not find where to do this.
>>
>> 4) grant the proxy account any permissions needed by your DTS package
>>
>
> By the way, I'm running SQL Server 7 SP3 -- my apologies for not
> specifying that in the original post.
> Thanks for your time Dan -- very much appreciated.
>
> Carl
>


From: Carl Imthurn on 4 Mar 2005 11:50
Thanks Dan -- this gets me headed in the right direction.
I appreciate your time.

Carl

Dan Guzman wrote:
>>What is the "SQL Agent proxy"? Do I need to create a Windows account with
>>a very specific username, or can I pick a name myself?
>
>
>>By the way, I'm running SQL Server 7 SP3 -- my apologies for not
>>specifying that in the original post.
>
>
> I assumed you were running the latest version, SQL Server 2000. The proxy
> account is not configurable in SQL 7 and is the local SQLAgentCmdExec
> account that was created when you installed SQL Server. It is this account
> that will need to be granted permissions on resources needed by your DTS
> package.
>

From: Carl Imthurn on 4 Mar 2005 12:03
Thanks folks -- I appreciate all your input and in particular your time.
I'm not sure which direction I'll be heading right now, but seeing the different ways to
accomplish my objective helps greatly.

Carl

First  |  Prev  | 
Pages: 1 2
Prev: InternetSetFilePointer
Next: MS Calendar Control