Executing packages from ASP.Net

I just want to confirm my thinking here.

-I have a server (serverA) which has SSIS installed and some .dtsx files sitting in a shared folder.
-I have a server (serverB) which is running some ASP.Net apps.

One of my ASP.Net apps on serverB needs to execute the packages stored on serverA.

Am I correct in thinking that I need SSIS installed on serverB or is there another way?

-Jamie

You need to install SSIS on machine where the package is run.

So if you need to run the package on serverB - yes, you need
to install SSIS on serverB.

If you can run the package on serverA - you don't need SSIS
on serverB. Instead, run the package remotely.

E.g. you can create an Agent job (without schedule) that
executes on serverA and then run it on demand from serverB.

Thanks,
Michael.|||Thanks for the confirmation Michael.|||

I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)

Wes

|||

Wes Linz wrote:

I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)

Wes

Use sp_start_job SQL stored procedure (see syntax in Books Online)

sql

Executing packages from ASP.Net

I just want to confirm my thinking here.

-I have a server (serverA) which has SSIS installed and some .dtsx files sitting in a shared folder.
-I have a server (serverB) which is running some ASP.Net apps.

One of my ASP.Net apps on serverB needs to execute the packages stored on serverA.

Am I correct in thinking that I need SSIS installed on serverB or is there another way?

-Jamie

You need to install SSIS on machine where the package is run.

So if you need to run the package on serverB - yes, you need
to install SSIS on serverB.

If you can run the package on serverA - you don't need SSIS
on serverB. Instead, run the package remotely.

E.g. you can create an Agent job (without schedule) that
executes on serverA and then run it on demand from serverB.

Thanks,
Michael.|||Thanks for the confirmation Michael.|||

I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)

Wes

|||

Wes Linz wrote:

I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)

Wes

Use sp_start_job SQL stored procedure (see syntax in Books Online)

Executing Northwind Script to Create the Database on SLQEXPRESS Edition

I have Visual Studio 2005 Beta 2.00 install which have installed the SQLEXPRESS server.

I have the Script for the Northwind database which I need to run some demos but I can figure out how to execute this script.

Can someone tell me what todo or how to attatched the database to this server. I also have a copy of the database already created. But When I tried login in into the database I get an error login fail.

Which is the default user amd password for the northwind database?

Tia

Charles

I found a way to do it. I installed SQL manager and the tool has a option to attatch a database.

Thanks

Charles

Executing MDX StoredProcedures using AdomdClient.dll

Hi fellow developers,

At the moment, i have a SSAS 2005 installed and a working cube on it. Everything is working fine. However i want to have an ASP.NET Page to access some of the data in the cube and present it.

Well this would be no problem at all, if i use the AdomdClient and send queries over it, but this destroy my layer architecture. I dont want any Queries in my Application, but in the SQL Server.

When i used a sql server database i always used stored procedures to access the data because of security and consistence.

Now is there a possible way to create MDX Stored Procedures and execute them with the Adomd API like i did with sql server databases?

Or other clean solutions like creating mdx libraries on server and access it with the client?

I appreciate your help.

Sincerely

David

PS: If you find any gramatically mistakes, keep em :-))

Moving to SQL Server Analysis Services forum.

Thanks,
Sarah

|||So far there is no support for stored procedures. There is support for parameterized queries though.

So you do not want ASP.NET code contain explicit text of MDX queries, right? What is your real goal here? Do you want to hide the text of the MDX queries from the writer of ASP.NET code?

How about creating some .NET component accepting a reference to the connection object, a name of the "stored procedure" and list of parameters? The component would fetch the real text from some other store, use the passed connection and parameters and pass the query to the server. Then return the results back to the client.

This is really not that useful, but maybe it will cover your goal.|||

Hi Andrew

Thanks for your answer.

Well the goal would be that, other applications could access the same Queries, without copying the Queries in their application code. However we decided in the meanwhile that we use the queries in the code, because we dont have any time to search other clean solutions :-)

But still i am interested in a clean splitted Query Logic -> Application Logic solution. If anyone knows? Please let me know.

Sincerelly

David

|||By other applications you probably mean those also developed by you, right? The applications could load the component. It could also accept references to a store of MDX queries so that different scenarios would load different stores and keep the queries in memory (so that not to have performance problems in multithreaded scenarios).sql

executing job with non-admin account

hello,
i administer w2k server with sql server and analysis
service installed on it.
some developers use it to tackle their projects.
one of them needs to test job execution and can manage to
create, but is not being able to execute it.
he has registered the server on his desktop enterprise
manager with sql login that is db owner of a given
database.
i wouldn't want to give him sysadmin server role - one
that has more power than he needs.
Would someone knows an alternative way to grant him
rights to run job not being sysadmin?
TIA
Mas"Mas" <anonymous@.discussions.microsoft.com> wrote in message
news:0cbd01c3adf9$b428f1b0$a501280a@.phx.gbl...
> Would someone knows an alternative way to grant him
> rights to run job not being sysadmin?
Oracle? DB2?
One work-around I've found for the permissions problem for the Sqlagent (so
you don't get that infernal database "guest" permissions error) is to set up
the job steps as OS commands that call dtsrun & osql. Just set the user up
as an operator, he'll then be able to run the xp_* sys. procs to run the OS
commands from the job manager, with no SA privileges to worry about. But
talk about convoluted! How 'bout a real job/task security model, eh
Microsoft?
You're not the first to complain about this on this forum, it's frequent
question. MS's security model is - well - unique in the industry in this
regard. The answer in these fora so far has been "It's supposed to work that
way." Maybe one of the MVPs who hang out here might write an FAQ that
explains how "It's supposed to work that way" (along with MS's idea of error
handling and load balanced clusters). Another Yukon feature to wait/pay for?
Tell you what... Max-DB/SAP-DB & PostgreSQL are coming up close behind and
are going to pass MS if MS doesn't watch out.
--j|||Hi, many thanks! I'll try to workout this way.
Mas
>--Original Message--
>"Mas" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0cbd01c3adf9$b428f1b0$a501280a@.phx.gbl...
>> Would someone knows an alternative way to grant him
>> rights to run job not being sysadmin?
>Oracle? DB2?
>One work-around I've found for the permissions problem
for the Sqlagent (so
>you don't get that infernal database "guest" permissions
error) is to set up
>the job steps as OS commands that call dtsrun & osql.
Just set the user up
>as an operator, he'll then be able to run the xp_* sys.
procs to run the OS
>commands from the job manager, with no SA privileges to
worry about. But
>talk about convoluted! How 'bout a real job/task
security model, eh
>Microsoft?
>You're not the first to complain about this on this
forum, it's frequent
>question. MS's security model is - well - unique in the
industry in this
>regard. The answer in these fora so far has been "It's
supposed to work that
>way." Maybe one of the MVPs who hang out here might
write an FAQ that
>explains how "It's supposed to work that way" (along
with MS's idea of error
>handling and load balanced clusters). Another Yukon
feature to wait/pay for?
>Tell you what... Max-DB/SAP-DB & PostgreSQL are coming
up close behind and
>are going to pass MS if MS doesn't watch out.
>--j
>
>.
>

execute SSIS package in SQL Express environment

Dear all,

I have an SSIS package and want to execute it on a computer that only

has SQL Express installed on it. Since SQL Express didn't brought

dtexec or dtui so what should I do ?

Sorry for my bad English...

Thanks for advance,

SSIS is not contained in SQL Server express edition. You cannot run a package on box that only has the express edition. For more details on what is included on every version of the product see this:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||thanks for the reply..so there's nothing I can do ?|||

Sorry, I am a little bit confused. I am using SQL Server Developer Edition, which I believe is the same as SQL Server Express. Yesterday I was trying to find out how to do DTS in 2005 and found out on msdn that it had been renamed SQL Server Integration Services, and whilst I could not seem to start the SQL Server Import and Export Wizard via SQL Express as outlined in the knowledge base article, I could start it from the command line by running DTSWizard.exe manually as outlined in the article.

Or am I missing something?

|||Developers edition is not the same than Express edition; Developers edition includes SSIS.

Execute SSIS package from ".vbs" file

I need to execute a SSIS package from a ".vbs" file on a computer that don't have SSIS installed on it.

Thank You

The only way to remotely execute a SSIS package is to set up a SQL Server Agent job to run the package and run that remotely. If you can find a way of doing that in a .vbs file then you're laughing.

-Jamie

|||You can use ADO (from VBS) to call sp_start_job for an existing SQL Server Agent job, or even create a new job and then start. SSIS is not client/server as per SQL Server, it executes the package on the host machine. You need to use another technology to communicate wth the "server", hence the suggestion of using SQL Agent to host the process.

Execute privileges on sp

Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did

use test

create table t ( a integer )

create procedure can_truncate as
truncate table t

sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'

grant execute on can_truncate to jmartinez

Then I went to connect again, as jmartinez and did:

exec can_truncate

and I get

Server: Msg 3704, Level 16, State 1, Procedure can_truncate, Line 2
User does not have permission to perform this operation on table 't'.

So, I wonder what more permissions would user jmartinez need in order to execute this procedure successfully. I hope you all understand what I am trying to achieve.

Thanks!Shake things up a bit, and try:use test
GO
sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'
GO
create table t ( a integer )
GO
create procedure can_truncate as
truncate table t
RETURN
GO

grant execute on can_truncate to jmartinez-PatP|||I get the same exact error message using your method. Btw, I am using MSDE, this is what I get when I do SELECT @.@.VERSION:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)|||Reading through BOL, I found out this:

Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
I guess this mean I cannot transfer TRUNCATE TABLE privilege to a user, but since I am executing a procedure I created on my own ( as sa in this case ), woulnd't it have to run with my privileges instead of the user who is executing it ( in this case, jmartinez ) ?|||You need to run this code after granting rights:

sp_addrolemember 'db_ddladmin', 'jmartinez'|||But then I'm giving the user privileges to basically *destroy* my db..|||Hmm.. TRUNCATE TABLE is an operation that requires high priviledges as long as I know, so you will have to give a user them.

By the way, why does user need to perform this? IMHO, You may create something like and administration panel which will login to database on its own and perform TRUNCATE TABLE instead of user - e.g. he selects a table from the combo box and then presses SUBMIT button, and then script connects as someone special with appropriate role membership and runs TRUNCATE TABLE.|||You might use
Delete
From <TableName>|||You might use
Delete
From <TableName>

The question is about

what this procedure do is to truncate a table on which the user has no access

EXECUTE permission denied on object MySPorDB, database MyDatabase, owner dbo.

I just installed this application on my new server and I get this error for each object in the database including the tables and the stored procedures. I have been going in for each one and opening the properties and then going to permissions and making the changes for each one.
Is there a way to do this for the entire database at once?
ThanksYou can scrpt this using a SQL script that can be executed in the SQL Query Analyzer. Beside of that, security can be set through "roles" for a user on a database level (for instance, if a user is a db_owner - which is not secure to do of course!!! - it will have these rights by default on the objects).|||It is for a database I will be using over and over, so I will probably try to work it into the sql scripts.

Thanks
Greg