Showing posts with label example. Show all posts
Showing posts with label example. Show all posts

Monday, March 26, 2012

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

|||etetetet

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

Thursday, March 22, 2012

Executing a DTS Package using an ASP (VBScript)

I'm looking for an example of how to execute an existing DTS* package
from an
ASP (VB)script and would appreciate any and all response. *I don't
even
know if it's possible
Thanks
- Chuck Gatto

Dan Guzman Apr 27 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Dan Guzman" <DGuz...@.nospamplease-earthlink.net&g*t; - Find
messages by this author
Date: 2000/04/27
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

This VBScript example loads and executes an existing DTS pac*kage from
SQL
Server.

Option Explicit
Const PackageName = "PackageName"
Const ServerName = "ServerName"
Const UserName = "UserName"
Const Password = "Password"
Dim DTSPk
Set DTSPk = CreateObject("dts.package")
DTSPk.LoadFromSQLServer ServerName, UserName ,Password
,,,,,*PackageName
DTSPk.Execute
If DTSPk.Steps(1).ExecutionResult = 0 Then
Response.Write "Package execution completed"
Else
Response.Write "Package execution failed"
End If
Set DTSPk = Nothing

You can also create the entire package from scratch from wit*hin your
asp and
execute it.

Hope this helps.

Chuck Gatto <cga...@.anchorsystems.com> wrote in message

news:8eapo8$frj$1@.slb7.atl.mindspring.net...

- Hide quoted text -
- Show quoted text -

> I'm looking for an example of how to execute an existing D*TS
package from
an
> ASP (VB)script and would appreciate any and all response.* I don't
even
> know if it's possible
> Thanks
> - Chuck Gatto

Chuck Gatto May 1 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Chuck Gatto" <cga...@.anchorsystems.com> - Find messages by this
author
Date: 2000/05/01
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

Below code works 100% in VB but the load fails in ASP. I g*et...
"Microsoft OLE DB Provider for SQL Server. Login failed for *user "\".
error.
I think the IIS (server a) is set for NT auth. and sql7 (on *server b)
as
well but I can't be sure.
Any idea what I should look for.
Thanks

"Dan Guzman" <DGuz...@.nospamplease-earthlink.net> wrote in m*essage

news:sgi3hi617qo89@.corp.supernews.com...

- Hide quoted text -
- Show quoted text -

> This VBScript example loads and executes an existing DTS p*ackage
from SQL
> Server.

> Option Explicit
> Const PackageName = "PackageName"
> Const ServerName = "ServerName"
> Const UserName = "UserName"
> Const Password = "Password"
> Dim DTSPk
> Set DTSPk = CreateObject("dts.package")
> DTSPk.LoadFromSQLServer ServerName, UserName ,Password
,,,*,,PackageName
> DTSPk.Execute
> If DTSPk.Steps(1).ExecutionResult = 0 Then
> Response.Write "Package execution completed"
> Else
> Response.Write "Package execution failed"
> End If
> Set DTSPk = Nothing

> You can also create the entire package from scratch from w*ithin
your asp
and
> execute it.

> Hope this helps.

> Chuck Gatto <cga...@.anchorsystems.com> wrote in message
> news:8eapo8$frj$1@.slb7.atl.mindspring.net...
> > I'm looking for an example of how to execute an existing* DTS
package
from
> an
> > ASP (VB)script and would appreciate any and all respons*e. I
don't even
> > know if it's possible
> > Thanks
> > - Chuck Gatto

Dan Guzman May 1 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Dan Guzman" <DGuz...@.nospamplease-earthlink.net&g*t; - Find
messages by this author
Date: 2000/05/01
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

You can specify a trusted connection with flag 256 instead o*f
username and
password. For example:

DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*me

Assuming this is an intranet application running under NT 4.*0 and you
want
to execute the package under the invoking user's account, yo*u can do
this as
follows:

Specify 'clear text' for the IIS Directory Security
auth*entication
Remove 'Everyone' from the access list on the files (req*uires
NTFS) and
grant permissions to the users
Grant logins access to the database server

With this method, users must enter their Domain\UserName and* password
when
prompted.

NT authentication presents a challenge when multiple servers* are
involved
because NT 4.0 does not support delegation. See
http://msdn.microsoft.com/workshop/...re/security.asp for
details.
I understand Windows 2000 provides delegation capabilities b*ut this
can be a
bit tricky to implement.

BTW, if your DTS package does not access SQL Server, you can* save it
to a
file and use the LoadFromStorageFile method instead.

Hope this helps.

If you need to use

Chuck Gatto <cga...@.anchorsystems.com> wrote in message

news:8ekrkd$pmq$1@.slb7.atl.mindspring.net...

- Hide quoted text -
- Show quoted text -

> Below code works 100% in VB but the load fails in ASP. I* get...
> "Microsoft OLE DB Provider for SQL Server. Login failed fo*r user
"\".
error.
> I think the IIS (server a) is set for NT auth. and sql7 (o*n server
b) as
> well but I can't be sure.
> Any idea what I should look for.
> Thanks

Chuck Gatto May 6 2000, 12:00 am show options

Newsgroups: comp.databases.ms-sqlserver
From: "Chuck Gatto" <cga...@.anchorsystems.com> - Find messages by this
author
Date: 2000/05/06
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

Hey Dan
Thanks again. I really appreciate your input and help.
I actually solved the problem by calling dtsrun...
I apologize for the delay getting back w/you but thee recent* virus
atack
sidetracked me.
Thanks again.

"Dan Guzman" <DGuz...@.nospamplease-earthlink.net> wrote in m*essage

news:sgsfh8spoog87@.corp.supernews.com...

- Hide quoted text -
- Show quoted text -

> You can specify a trusted connection with flag 256 instead* of
username and
> password. For example:

> DTSPk.LoadFromSQLServer ServerName, , , 256,,,,Package*Name

> Assuming this is an intranet application running under NT *4.0 and
you want
> to execute the package under the invoking user's account, *you can
do this
as
> follows:

> Specify 'clear text' for the IIS Directory Security
au*thentication
> Remove 'Everyone' from the access list on the files (r*equires
NTFS)
and
> grant permissions to the users
> Grant logins access to the database server

> With this method, users must enter their Domain\UserName a*nd
password when
> prompted.

> NT authentication presents a challenge when multiple serve*rs are
involved
> because NT 4.0 does not support delegation. See
> http://msdn.microsoft.com/workshop/...re/security.asp for
details.
> I understand Windows 2000 provides delegation capabilities* but this
can be
a
> bit tricky to implement.

> BTW, if your DTS package does not access SQL Server, you c*an save
it to a
> file and use the LoadFromStorageFile method instead.

> Hope this helps.

> If you need to use
> Chuck Gatto <cga...@.anchorsystems.com> wrote in message
> news:8ekrkd$pmq$1@.slb7.atl.mindspring.net...
> > Below code works 100% in VB but the load fails in ASP. * I get...

> > "Microsoft OLE DB Provider for SQL Server. Login failed *for user
"\".
> error.
> > I think the IIS (server a) is set for NT auth. and sql7 *(on
server b) as
> > well but I can't be sure.
> > Any idea what I should look for.
> > Thanks

chris.duni...@.agwsha.nhs.uk Jan 31, 10:05 am show options

Newsgroups: comp.databases.ms-sqlserver
From: chris.duni...@.agwsha.nhs.uk - Find messages by this author
Date: 31 Jan 2005 10:05:00 -0800
Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Hi,

I've tried adding this function to my ASP pages and get the *following

error message;

Microsoft VBScript runtime error '800a01ad'

ActiveX component can't create object: 'DTS.Package'

/asp/pages/dts.asp, line 21

Does anyone have any idea what I need to fix to get the DTS *to work?

I'm v. new to SQL Server and ASP so any help would be apprec*iated.
Many thanks,
Chris Dunigan<chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1107256044.746188.14660@.c13g2000cwb.googlegro ups.com...
I'm looking for an example of how to execute an existing DTS* package
from an
ASP (VB)script and would appreciate any and all response. *I don't
even
know if it's possible
Thanks
- Chuck Gatto

http://www.google.co.uk/search?hl=e...+vbscript&meta=

Your package.
It works OK if you try and run it from Enterprise Manager on your machine?

You tried a real user and password in those fields when you tried that
method?
This would be equal to the user you're logged on as?

--
Regards,
Andy O'Neill|||
Hi Andy,

Yes the DTS Package works fine when I run it from Enterprise Manager;
and I have used the correct server name, username, password and
packagename.

Having done some more digging into this error I found a site that told
me it could be something to so with the software installed on the PC on
which the database resides.
I think I might need to ensure that dtspkg.dll is correctly installed
(along with a host of other dll and rll's).
As I can't get hold of our IT guys today I haven't been able to see if
this solves my problem.

Does this sound like it may fix the problem??

Regards,
Chris

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:41ffb33f$1_2@.127.0.0.1...
>
> Hi Andy,
> Yes the DTS Package works fine when I run it from Enterprise Manager;
> and I have used the correct server name, username, password and
> packagename.
> Having done some more digging into this error I found a site that told
> me it could be something to so with the software installed on the PC on
> which the database resides.
> I think I might need to ensure that dtspkg.dll is correctly installed
> (along with a host of other dll and rll's).
> As I can't get hold of our IT guys today I haven't been able to see if
> this solves my problem.
> Does this sound like it may fix the problem??
> Regards,
> Chris
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

These links might help:

http://www.sqldts.com/default.aspx?207
http://support.microsoft.com/defaul...kb;en-us;252987
http://support.microsoft.com/defaul...kb;en-us;323685
http://support.microsoft.com/defaul...kb;en-us;282463

Simon|||"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:41ffb33f$1_2@.127.0.0.1...
> Hi Andy,
> Yes the DTS Package works fine when I run it from Enterprise Manager;
> and I have used the correct server name, username, password and
> packagename.
> Having done some more digging into this error I found a site that told
> me it could be something to so with the software installed on the PC on
> which the database resides.
> I think I might need to ensure that dtspkg.dll is correctly installed
> (along with a host of other dll and rll's).
> As I can't get hold of our IT guys today I haven't been able to see if
> this solves my problem.
> Does this sound like it may fix the problem??

If no dts package works when run from a job on the server.

Quite frankly, I'm confused by your huge initial post as to which technique
you're trying and what's going on.

I'm wondering if this is running and owned by a user can run the dts package
OK on the server using whichever of the alternatives you posted you're
currently trying.

Try this with the package if it's vbscript using any activex.
Open DTS Designer, right-click the task, select Workflow, then Workflow

Properties. On the Options tab, check "Execute on main package thread"

--
Regards,
Andy O'Neill

Wednesday, March 21, 2012

Executin SQL statement from variable in functions

Hello All

Within my function depends on value forming a SQL statement

As Example

set @.SqlString = 'select ' + @.ColumnName + ' from ' + @.tableName + ' where ' + @.whereCond

How can i execute this Statement. within function

Thanks in advance

RK

> Within my function depends on value forming a SQL statement

Sorry, no can do, you cannot execute dynamic SQL in a function. Maybe you

meant to use a stored procedure, not a function.

|||

I can change function to stored procedure within storedprocedure how can i execute a dynamic SQL

My requirement is to open cursor with dynamic SQL.
table name, columns list and conditions are depends on user parameters

|||

> My requirement is to open cursor with dynamic SQL.

You need a cursor and dynamic SQL. Fantastic. First, please read this:

http://www.smmarskog.se/dynamic_sql.html

Then, if you can give better specs, maybe we can help.

http://www.aspfaq.com/5006

|||looks like the site was down (http://www.smmarskog.se/dynamic_sql.html)

is there a way i can a open cursor with dynamic sql.

Our requirement is we have set of related tables with different data but all integer columns

We are trying to create few statistical functions/procs So user can pass Table name and column name and optionally any conditions, so that my function/procs returns results.

|||

Sorry, typo

http://www.sommarskog.se/dynamic_sql.html

> looks like the site was down

Monday, March 19, 2012

Execute Stored Procedure Using Select Statement As Parameters

Hi,
I'm trying to run a SP with a select statement. Example below:
Stored procedure: uspI_InsertUsername (@.Username VARCHAR(50))
SQL Statement: EXEC uspR_InsertUsername SELECT Username FROM TempUser
Will this work?
--
Thanks,
Jig PatelYou can store the result of the query to a temporary variable and execute th
e
sp using the result
declare @.user VARCHAR(50)
select @.user = Username
FROM TempUser
EXEC uspR_InsertUsername @.user
it will work when
select Username
FROM TempUser
returns only one user, otherwise you will have to declare @.user as a table
variable and loop through it to execute the procedure for each row
Let me know if it helps
"Jig" wrote:

> Hi,
> I'm trying to run a SP with a select statement. Example below:
> Stored procedure: uspI_InsertUsername (@.Username VARCHAR(50))
> SQL Statement: EXEC uspR_InsertUsername SELECT Username FROM TempUser
> Will this work?
> --
> Thanks,
> Jig Patel|||Could you be more specific on what you want to reach.

Monday, March 12, 2012

execute ssis in sql

Hi,
Using SSIS, how is it possible to execute another ssis package and pass a parameter to it?
For example, I would like to have a sql code that runs the package say "d:\sysappl\CEM\SSIS\CSA.dtsx" and pass a parameter of fileName because the CSA.dtsx package requires a filename.

Something like:
execute "d:\sysappl\CEM\SSIS\CSA.dtsx", varfileName

Thanks

You can use an Execute Process task to execute DTEXEC.exe. If you search for DTEXEC.EXE on Microsoft's site, you'll get a page with all of the parameters necessary to do what you desire.

Or, you can use an Execute Package task along with Parent Package Configurations to pass down a variable value.

Sunday, February 19, 2012

Execute package on conditions

Hi,

I need to write a condition on the start of my package. Based on the condition different task has to execute.

Example :

I will create a package level var MyVar bool. if it's true task a has to execute else task b.

I thought of using conditional split, But it asks for input connection as it's a transform control.

How to do this?

Any Ideas?

You need to use this condition in control flow, not in data flow. At the control flow you may define precedence constraints between tasks, and have conditional expressions on the precedence constraints.

So, if a task does not need to be executed, double click precedence constraint leading to it, and type expression !MyVar.

Wednesday, February 15, 2012

Execute Dos Commands in T-SQL Script

Friends,
I know there is a way to execute DOS commands inside T-Sql script, but can't
seem to find it. For example: using the MOVE command inside a stored
procedure to move .txt files into another directory after they've been
imported.
Can someone point me in the right direction?
Thanks ...The thing you are looking for is called xp_cmdshell
Have a look in the BOL for the details. You need to be symin to run this
command
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:BBD58F20-E45C-418D-9B5A-E1221B81BE05@.microsoft.com...
> Friends,
> I know there is a way to execute DOS commands inside T-Sql script, but
> can't
> seem to find it. For example: using the MOVE command inside a stored
> procedure to move .txt files into another directory after they've been
> imported.
> Can someone point me in the right direction?
> Thanks ...
>|||Thanks, Greg ... I did run into that one, but wasn't getting it to work righ
t
(am running it on my own version of SQL Server so shouldn't be having an
Admin problem).
Now that I'm sure that's what I need to pursue, I'll give it another try.
"GregO" wrote:

> The thing you are looking for is called xp_cmdshell
>
> Have a look in the BOL for the details. You need to be symin to run th
is
> command
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
> news:BBD58F20-E45C-418D-9B5A-E1221B81BE05@.microsoft.com...
>
>|||Basic question I assume is also true, you are a SysAdmin on the server?
Where are you trying to make calls to? If it is on a network share, then
your SQL Server service account needs permissions there. If you are running
under a local system account, then you will have to grant the machine
account access to the share.
Here is a basic statement that should return values for you:
exec xp_cmdshell 'dir c:'
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:CC18C513-D5DB-44DB-B849-BFB29965B1A8@.microsoft.com...
> Thanks, Greg ... I did run into that one, but wasn't getting it to work
> right
> (am running it on my own version of SQL Server so shouldn't be having an
> Admin problem).
> Now that I'm sure that's what I need to pursue, I'll give it another try.
> "GregO" wrote:
>

Execute as Caller

This is a followup to my previous question.
Example SP:
CREATE PROCEDURE [dbo].[ChangeWorkspace]
@.UserName varchar(32),
@.Workspace varchar(32)
WITH EXECUTE AS CALLER
AS
BEGIN
update table1
set workspace = @.Workspace
where username = @.Username;
END
In the DB table1 is in a schema: user1 (user1.table1)
I connect via ASP.Net with a simple connection string that logs in with User
Id= user1:
In Asp.Net I run the Stored procedure
cmd.ExecuteNonQuery();
The error is:
System.Data.SqlClient.SqlException: Invalid object name 'table1'.
***so I assume its looking for dbo.table1 -- now if in my sp I use the
schema prefix it works:
update user1.table1s
set workspace = @.MapWorkspace
where username = @.Username;
So the problem is why doesn't the SP act as if I did user1.table1 since in
the SP I have WITH EXECUTE AS CALLER
Thanksdev648237923 (dev648237923@.noemail.noemail) writes:
> So the problem is why doesn't the SP act as if I did user1.table1 since in
> the SP I have WITH EXECUTE AS CALLER
Because the procedure uses the default schema of the procedure owner.
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|||Hi dev648237923,
I agree with Erland that the problem you met is caused by the "dbo" schema
name of the stored procedure, the stored procedure's code will inherit the
schema context from the procedure's schema. Actually stored procedure is
designed to be schema specific, therefore, when we specify a schema name
for one sp, its contained T-SQL code will also followup that schema
context. While the "EXECUTE AS" is mainly for security context purpose.
For your scenario, the target table's schema name should match the stored
procedure's schema name , like:
CREATE PROCEDURE [user1].[ChangeWorkspace]
...............
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi dev648237923,
Have you got any further ideas on this issue? If there is still anything we
can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)