Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

Hello all,

Does anyone have any idea how to access a cube from stored procedures in SQL Server 2005?

My idea was to use SQLCLR and write a function in .NET that accessed the cube through ADOMD, but there are problems with that.

See the following code sample:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MDXRdr()
{
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = @."Provider=SQLNCLI.1;Data Source=JOAHSE0\SQL2005;Integrated Security=SSPI;Initial Catalog=dbRenTAK";

conn.Open();
// Just output cube name
string str = conn.Cubes[0].Name;
conn.Close();

return new SqlString(str);
}

};

First, I wasn't able to add a reference to AdomdClient from Visual Studio. I then did add a reference manually in the project file and it compiles. But when I try to deploy, I get an error message that the "assembly adomdclient was not found in the SQL catalog".

Thanks in advance for any suggestions or hints!

Best Regards,

Johan ?hln
Consultant, IFS

Moving to the "Data Mining" Forum, which is better suited for this question.

Executing "Shell" commands.

Hi,
IIRC it is possible to execute shell commands in SQL server, is this the
case?
I intend to give public access to a test server (MSDE). My main concern is
that people could use SQLServer shell commands as a back door to hack my
operating system.
I'm not too concerned at this stage about securing the data though, since it
is only for testing & debugging.
Please advise, many thanks.
--
Mike Collier BSc( Hons) Comp Sci
Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
forum.
http://www.adoanywhere.com/forumlook up xp_cmdshell in BOL, do not use an SA level account for your web
service accounts
Ray Higdon MCSE, MCDBA, CCNA
--
"Mike Collier" <mike@.adoanywhere.com> wrote in message
news:Om0r3jh3DHA.2888@.tk2msftngp13.phx.gbl...

quote:

---

> Hi,
> IIRC it is possible to execute shell commands in SQL server, is this the
> case?
> I intend to give public access to a test server (MSDE). My main concern is
> that people could use SQLServer shell commands as a back door to hack my
> operating system.
> I'm not too concerned at this stage about securing the data though, since

---

it

quote:

---

> is only for testing & debugging.
> Please advise, many thanks.
> --
> Mike Collier BSc( Hons) Comp Sci
> Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
> forum.
> http://www.adoanywhere.com/forum
>
>

---

|||> look up xp_cmdshell in BOL, do not use an SA level account for your web

quote:

---

> service accounts

---

Thank you, very much appreciated.
Mike.

ExecuteOutOfProcess calling a transactional child package causes Access is Denied.

I have a master package that contains an Execute Package Task whose ExecuteOutOfProcess flag is True, and that calls a child package whose TransactionOption = Required. The job is running in Sql Agent, and the step that calls the master package is configured to run under a certain domain account that is not in the local Administrators group. With this, I get the following:

messageText: Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_Various_TransactionalChannels.dtsx". Access is denied.

When I add the domain account to the local Administrators group, this error does not occur. From a blog entry, I read that when a child package is executed out of process, the resultant OS process is called dtshost.exe (http://blogs.conchango.com/jamiethomson/comments/1414.aspx). Do I simply need to give my domain account permission to spawn this process? If so, what permission is it? Is there a group that contains this permission?

Is it possible that the domain account does not have access to "C:\program files\microsoft sql server\90\dts\Packages\ETL\"

-Jamie?

|||Unfortunately, no. The account has full control over that path.

Execute UDF/extended stored procedure only through view?

Lets say I have a view, MyView, that calls MyUDF and/or MyExtendedProcedure.
Is there a way I can allow a user to access MyView, but stop them from
directly executing MyUDF or MyExtendedProcedure?
E.g., I'd like them to be able to do this:
select * from MyView
but stop them from doing this:
Exec MyExtendedStoredProcedure
Is this possible? Thanks for any tips.As long as the objects referenced by your view are owned by the same user,
permissions on indirectly referenced objects are not checked. This behavior
is known as ownership chaining. Beginning with SQL 2000 SP3, you also need
to also turn on the 'db chaining' database option (a.k.a. cross-database
chaining) when objects reside in different databases.
Also, the databases need to be owned by the same login in order to maintain
an unbroken chain for your dbo-owned objects in different databases. The
master database is owned by the 'sa' login so your user database needs to
also be owned by 'sa' to provide an unbroken ownership chain to your
dbo-owned extended stored procedure. You can use sp_changedbowner if
needed.
Note that 'db chaining' should be enabled in an sa-owned database when only
sysadmin role members have permissions to create dbo-owned objects. See
Cross DB Onership Chaining <adminsql.chm::/ad_config_8d7m.htm> in the Books
Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W" <neilw@.REMOVEnetlib.com> wrote in message
news:uLP5XyJ1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Lets say I have a view, MyView, that calls MyUDF and/or
> MyExtendedProcedure.
> Is there a way I can allow a user to access MyView, but stop them from
> directly executing MyUDF or MyExtendedProcedure?
> E.g., I'd like them to be able to do this:
> select * from MyView
> but stop them from doing this:
> Exec MyExtendedStoredProcedure
> Is this possible? Thanks for any tips.
>
>

Execute SP_password

I'm using Access 2000 and have tried Access 2003 front-end to execute
the stored procedure sp_password on SQL Server 2000. I continue to
receive the following message: "The old (current) password was not
correct. Password not changed." If I go into query analyzer and run
an execute @.old, @.new, @.loginame with the same values as passed to my
command collection in Access, it works just fine. I'm using Windows
Authentication and my server is defined for SQL Server and Windows
authentication. I have tried using the sqladmin account and have
received the same error message. I have verified the old password and
I know it is correct because I am able to change the password by
executing the same stored procedure in query analyzer. Why are the
results different? How can I resolve this problem?ano1optimist (ano1optimist@.aol.com) writes:
> I'm using Access 2000 and have tried Access 2003 front-end to execute
> the stored procedure sp_password on SQL Server 2000. I continue to
> receive the following message: "The old (current) password was not
> correct. Password not changed." If I go into query analyzer and run
> an execute @.old, @.new, @.loginame with the same values as passed to my
> command collection in Access, it works just fine. I'm using Windows
> Authentication and my server is defined for SQL Server and Windows
> authentication. I have tried using the sqladmin account and have
> received the same error message. I have verified the old password and
> I know it is correct because I am able to change the password by
> executing the same stored procedure in query analyzer. Why are the
> results different? How can I resolve this problem?

Obviously the call that is generated from Access, is not the same
that you make from Query Analyzer.

I would use the Profiler to see what is actually being sent to SQL
Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In query analyzer, this is my statement:

execute master.dbo.sp_password 'OLD', 'NEW', 'XYZ'

In Access, here is my code:

Set Cmd = New ADODB.Command
Set Prm = New ADODB.Parameter
Set Cmd.ActiveConnection = CnnAdo
Cmd.CommandText = "master.dbo.sp_password"
Cmd.CommandType = adCmdStoredProc

Set Prm = Cmd.CreateParameter("@.old", adVarChar, adParamInput, 10,
Me!txtoldpassword)
Cmd.Parameters.Append Prm

Set Prm = Cmd.CreateParameter("@.new", adVarChar, adParamInput, 10,
Me!txtnewpassword)
Cmd.Parameters.Append Prm

Set Prm = Cmd.CreateParameter("@.loginame", adVarChar, adParamInput,
10, Me!txtusername)
Cmd.Parameters.Append Prm

Cmd.Execute , dbFailOnError

I've added a message box prior to the cmd.execute statement and it
shows the following:

@.OLD = OLD
@.NEW = NEW
@.LOGINAME = XYZ

If I try to execute the statement in Query Analyzer twice, the second
time, it shows the following, as is should on the second try:

Server: Msg 15211, Level 16, State 1, Procedure sp_password, Line 58
Old (current) password incorrect for user. The password was not
changed.

This is the message I get whenever I execute the access command code,
regardless of first or second try.

So, how could I be executing different code? Please explain further
because this has really stumped me.|||I've tried running the Profiler to see what is happening but I've
never used that product before. The Loginames were the same, except
for case. I tried it again making sure that the loginnames were of
the same case and still got the same error message. Is this a timing
issue where the change hasn't been committed yet?|||ano1optimist (ano1optimist@.aol.com) writes:
> In query analyzer, this is my statement:
> execute master.dbo.sp_password 'OLD', 'NEW', 'XYZ'
> In Access, here is my code:
>...
> Server: Msg 15211, Level 16, State 1, Procedure sp_password, Line 58
> Old (current) password incorrect for user. The password was not
> changed.
> This is the message I get whenever I execute the access command code,
> regardless of first or second try.

I ran a variation of your code from Visual Basic:

oCommand.CommandType = adCmdStoredProc
oCommand.CommandText = "master.dbo.sp_password"
oCommand.Parameters.Append oCommand.CreateParameter( _
"@.old", adVarChar, adParamInput, 10, "ggggg")
oCommand.Parameters.Append oCommand.CreateParameter( _
"@.new", adVarChar, adParamInput, 10, "hhhhh")
oCommand.Parameters.Append oCommand.CreateParameter( _
"@.login", adVarChar, adParamInput, 10, "lklklkl")
oCommand.Execute

And this worked the first time (but of course the second time). On the
first successful occassion there was no output, as it can be fairly
difficult to pick up messages that are not errors with ADO.

I'm a bit out of ideas, but one thing is to check is that you don't run
the Access code twice by mistake. And of course, that the old
password or login does not exceed 10 characters in length.

My suggestion to use the profiler was not entirely useful. Profiler
reconizes sp_password and sensors the parameter. You could could
however call a wrapper SP that calls sp_password and trace that
call.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I removed the dbFailOnError from the command execute line and changed
the code to use a wrapper stored procedure and everything works just
fine. I tried it again without the wrapper stored procedure and
continued to get the same old message about the old password not being
correct, even though I knew it was. Thanks for the info on the
Profiler, I'm fairly new to SQL Server so it will be a great help.

Execute Permissions on 400 SPROCs

Our developers are rolling out an app with 400 new SPROCS. All data access i
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?

execute permissions aspnet sql server 2005

Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error

EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.

as far as im aware ive givne the ASPNET account the correct permissions

EXEC

sp_grantlogin [MachineName\ASPNET]

EXEC

sp_grantdbaccess [MachineName\ASPNET], [ASPNET]

** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.

Is there anything else I need to do ?

Try this:

GRANT EXEC on <storedproc> TO <machinename\user>

Execute permission denied on object xp_SQLagent_notify

SQL Server 2000, SP4. I have this login MyLogin which has access in both msd
b
and master. The account is public in master and db_owner in msdb. I have the
same settings on 10 servers. I attempt to create a job, steps and schedule.
When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
'master', owner 'dbo'.
Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
EXECUTE permission denied on object 'xp_sqlagent_notify', database 'master',
owner 'dbo'.
The puzzling part here is that I got the errors on 2 servers out of the 10
above mentioned. On the other 8 the job is created successfully and there ar
e
no explicit rights granted or denied on these particular XPs.
Question: What are the minimum requirements to execute the above 2 XP? They
are not documented by Microsoft or it seems I cannot find much on them. Ther
e
is always the possibility to explicitly GRANT access to them for MyLogin, bu
t
the question remains: why is it working on 8 servers and not working on the
other 2. Must be some other setting somewhere!
Any answer will be higly appreciated.Gabriela,
On my server the xp_sqlagent* stored procedures are granted execute to
public. Check on your two problem servers to see whether that is true for
you.
If the agent XPs are not enabled, you can do so by:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
RLF
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb
> and master. The account is public in master and db_owner in msdb. I have
> the
> same settings on 10 servers. I attempt to create a job, steps and
> schedule.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
> are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They
> are not documented by Microsoft or it seems I cannot find much on them.
> There
> is always the possibility to explicitly GRANT access to them for MyLogin,
> but
> the question remains: why is it working on 8 servers and not working on
> the
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||As I said in my first post, I don't have GRANT or DENY for the xp_SQLAGENT%
SPs (not even for public) on any of the servers (the ones that work or the
ones that don't).
As for the sp_configure 'Agent SPs', I am on SQL Server 2000, not such
option there!
So thanks, but it doesn't help.
Gabriela Nanau
MCDBA
"Gabriela Nanau" wrote:

> SQL Server 2000, SP4. I have this login MyLogin which has access in both m
sdb
> and master. The account is public in master and db_owner in msdb. I have t
he
> same settings on 10 servers. I attempt to create a job, steps and schedule
.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database 'master
',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP? The
y
> are not documented by Microsoft or it seems I cannot find much on them. Th
ere
> is always the possibility to explicitly GRANT access to them for MyLogin,
but
> the question remains: why is it working on 8 servers and not working on th
e
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||Gabriela Nanau (Gabriela Nanau@.discussions.microsoft.com) writes:
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb and master. The account is public in master and db_owner in msdb. I
> have the same settings on 10 servers. I attempt to create a job, steps
> and schedule. When it comes to msdb.dbo.sp_add_jobserver I got the
> following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master', owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the
> 10 above mentioned. On the other 8 the job is created successfully and
> there are no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They are not documented by Microsoft or it seems I cannot find much on
> them. There is always the possibility to explicitly GRANT access to them
> for MyLogin, but the question remains: why is it working on 8 servers
> and not working on the other 2. Must be some other setting somewhere!
I would guess this is a owner-chaining issue. Since there are no perms
granted to revoked to these SP:s, MyLogin should not be able to execute
these procedures directly on any server.
However, when MyLogin runs sp_add_jobserver, permission is granted
through ownership chaining, if the two procedures have the same owner.
To have this:
1) The two databases must have the same owner.
2) Cross-DB chaining must be enabled for the databases.
According to Books Online is DB chaining always on for master and msdb,
so my guess is that on the two servers you have problem, master and msdb
have different owners.
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|||To add to Erland's response, you can fix msdb ownership can database options
using the script below.
USE msdb
EXEC sp_changedbowner 'sa'
EXEC sp_dboption 'msdb', 'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb
> and master. The account is public in master and db_owner in msdb. I have
> the
> same settings on 10 servers. I attempt to create a job, steps and
> schedule.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
> are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They
> are not documented by Microsoft or it seems I cannot find much on them.
> There
> is always the possibility to explicitly GRANT access to them for MyLogin,
> but
> the question remains: why is it working on 8 servers and not working on
> the
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||Thanks a lot. The different ownership was indeed, the problem! I didn't even
think to check the owners for these 2 databases! It seemed so obvious that i
t
must be sa! That was a rookie's error, I'm sort of embarrassed! Now that I
think about, the msdb was brought from a different machine at some point and
it was probably then when the owner changed.
Once again thanks!
--
Gabriela Nanau
MCDBA
"Dan Guzman" wrote:

> To add to Erland's response, you can fix msdb ownership can database optio
ns
> using the script below.
> USE msdb
> EXEC sp_changedbowner 'sa'
> EXEC sp_dboption 'msdb', 'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in messa
ge
> news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
>

Execute Permission

Hi,
How do I give user execute permission on the complete
db. I gave them db_datareader, db_datawriter and
db_ddladmin and they still get access denied. Is making
them dbo the only way? I don't realy want to give them
permission one sproc at a time
thanks in advance<anonymous@.discussions.microsoft.com> wrote in message
news:4bed01c3e438$70ad8f70$a601280a@.phx.gbl...

quote:

---

> How do I give user execute permission on the complete
> db. I gave them db_datareader, db_datawriter and
> db_ddladmin and they still get access denied. Is making
> them dbo the only way? I don't realy want to give them
> permission one sproc at a time

---

Basically you need to issue the
GRANT EXEC ON <sp_name> TO <username>
For each stored procedure, a cursor could be used to enumerate all tables.
Steve|||Hi,
Use the below script from Query analyzer, replace dbname with actual dbname
and username with actual user name
use dbname
go
declare @.x varchar(8000)
set @.x=''
select @.x = @.x + ' grant exec on ' + name+ ' to username'
from sysobjects where type='p'
exec (@.x)
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:4bed01c3e438$70ad8f70$a601280a@.phx.gbl...

quote:

---

> Hi,
> How do I give user execute permission on the complete
> db. I gave them db_datareader, db_datawriter and
> db_ddladmin and they still get access denied. Is making
> them dbo the only way? I don't realy want to give them
> permission one sproc at a time
> thanks in advance

---

Execute package from query analyzer

I have SQL 2K and I have created a local package to import data from Access
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from
>>Access mdb and I have saved it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Local Packages
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>> If you create a job for your package you can :-
>> USE msdb
>> EXEC sp_start_job @.job_name = 'Nightly Backup'
>>
>> If not you can utilise DTSRUN :-
>> To execute a DTS package saved in the SQL Server msdb database, use:
>> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from Access mdb and I have saved
>>it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>>
>

Execute DTS package from .adp ?

Hello,

Currently I save a DTS as a storage file. I then run the storage file
with VBA code in the .adp(Access Data Project). This works if you run
the ADP on the Server but the users of the .adp will be opening it on
the network from their machines. When they do it says missing
custtask.dll and some other .dll these .dll are in the SQL server Binn
on the server so they are not on the users machines.
I could not register the .dlls on the user machines without SQL Server.

It would be great if I could solve this problem but I'm not sure
how....

Another possiblity would be to save the DTS SQL Server msdb table and
run it from the server but I do not know how to run the DTS using VBA
code or a SQL statement...

Please help with any documentation or suggestions,

ChuckHi

This may be useful:
http://www.sqldts.com/default.aspx?225

You could run the DTS package as a scheduled job
http://support.microsoft.com/?kbid=269074
or a job started manually
http://msdn.microsoft.com/library/d..._sa-sz_11uq.asp

John

<meyvn77@.yahoo.com> wrote in message
news:1126375425.946180.124620@.f14g2000cwb.googlegr oups.com...
> Hello,
> Currently I save a DTS as a storage file. I then run the storage file
> with VBA code in the .adp(Access Data Project). This works if you run
> the ADP on the Server but the users of the .adp will be opening it on
> the network from their machines. When they do it says missing
> custtask.dll and some other .dll these .dll are in the SQL server Binn
> on the server so they are not on the users machines.
> I could not register the .dlls on the user machines without SQL Server.
> It would be great if I could solve this problem but I'm not sure
> how....
> Another possiblity would be to save the DTS SQL Server msdb table and
> run it from the server but I do not know how to run the DTS using VBA
> code or a SQL statement...
> Please help with any documentation or suggestions,
> Chuck|||I really need to execute it using code or a SQL statement.|||So you will need to install/register all the dlls mentioned in the first
post on every client or possibly from your own stored procedure:

http://www.sqldts.com/default.aspx?210

John

<meyvn77@.yahoo.com> wrote in message
news:1126393799.257226.56930@.o13g2000cwo.googlegro ups.com...
>I really need to execute it using code or a SQL statement.

Execute DTS package from .adp ?

Hello,
Currently I save a DTS as a storage file. I then run the storage file
with VBA code in the .adp(Access Data Project). This works if you run
the ADP on the Server but the users of the .adp will be opening it on
the network from their machines. When they do it says missing
custtask.dll and some other .dll these .dll are in the SQL server Binn
on the server so they are not on the users machines.
I could not register the .dlls on the user machines without SQL Server.
It would be great if I could solve this problem but I'm not sure
how....
Another possiblity would be to save the DTS SQL Server msdb table and
run it from the server but I do not know how to run the DTS using VBA
code or a SQL statement...
Please help with any documentation or suggestions,
ChuckYou can dts runtime. You'd want to read the readme for details as to what's
distributable.
Meanwhile, you want to save your dts package on sqlserver (i.e. in msdb) and
then create a sqljob for the package. Then in your vba, you would invoke
sp_start_job 'jobname' to execute it.
-oj
<meyvn77@.yahoo.com> wrote in message
news:1126375373.241589.256440@.z14g2000cwz.googlegroups.com...
> Hello,
> Currently I save a DTS as a storage file. I then run the storage file
> with VBA code in the .adp(Access Data Project). This works if you run
> the ADP on the Server but the users of the .adp will be opening it on
> the network from their machines. When they do it says missing
> custtask.dll and some other .dll these .dll are in the SQL server Binn
> on the server so they are not on the users machines.
> I could not register the .dlls on the user machines without SQL Server.
> It would be great if I could solve this problem but I'm not sure
> how....
> Another possiblity would be to save the DTS SQL Server msdb table and
> run it from the server but I do not know how to run the DTS using VBA
> code or a SQL statement...
> Please help with any documentation or suggestions,
> Chuck
>