Accessing lower level members

Hi All,

I have used .Children function to retrieve members of dimension at one
level below the current level of dimension. For example (using FoodMart
2000 and AS 2000)

Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( [Customers].[Country].[USA].Children ) on rows

>From sales

This query returns me Children of member Country which is USA. It
returns all members of State Provinces which have USA as its parent.

Can I access or retrieve all members of City with Country member being
USA?

I am building a web application where user applies filters. This is
necessary because if user wants to view sales data about City which
belongs to country USA.

Many thanks in advance.

Raghu

Hi Raghu. Yes, you can retrieve members of City where the Country is USA. Use the MDX DESCENDANTS() function. Change your query to the following:

Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( DESCENDANTS([Customers].[Country].[USA], [Customers].[City]) ) on rows

From sales

Here's a link to the BOL description of the DESCENDANTS() function:

http://msdn2.microsoft.com/en-us/library/ms146075.aspx

Good luck - Paul Goldy

|||

Thanks Paul.

This solved it.

Raghu

Accessing local temporary tables

I've been able to get the local temporary table name from sysobjects, but I still can't select from it.

for example, the following select statement won't work
select * from #temptable_____00015
It says invalid object name.

I've even stored the temp table name in a variable and tryed to execute dynamic sql to get to it--no luck.

I need this technique to handle two support situations: A user freezes during data entry to a temp table, I want to capture the data before they reboot, so they don't have to reenter.

Temp tables are used among several stored procedures and then crunched into other tables. I'm getting incorrect results and want to see the raw data in the temp tables to assist me in figuring out what's going on.Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.|||Originally posted by rnealejr
Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.

They exist on the hard drive. Maybe if I changed the status value in sysobjects, I could select from them?|||Please define in detail the issue you are trying to solve.

Accessing Excel functions in SQL SP

Hi,
I am trying to access an Excel function in a stored procedure. First of all,
is it possible, if it is, can someone give me an example.
Thank you.
--
RamIt may be possible with sp_OAMethod, but why do it to yourself? Even if you
can get to work without blowing up your computer, performance is going to be
rubbish!
SQL has loads of functions, and you can roll your own, do you know about
user-defined functions? They're great!
Anyway, tell us which function you want to emulate and we'll see what we can
do.
Plus, also bear in mind, SQL might have the function you need, but you just
don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there's
a
ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
some conditional logic, there's the CASE statement instead of Excel's IF.
Remember to post some DDL, sample data, expected results etc.
Damien
First of all, there are few things you can't do in SQL
"ram4tech" wrote:

> Hi,
> I am trying to access an Excel function in a stored procedure. First of al
l,
> is it possible, if it is, can someone give me an example.
> Thank you.
> --
> Ram|||Hi Damien:
The looked in BOL and on the net, but didn't had much luck. I wasn't aware
that there might be some performance issues. The excel function I am plannin
g
on using is IRR().
Thank you.
--
Ram
"Damien" wrote:
> It may be possible with sp_OAMethod, but why do it to yourself? Even if y
ou
> can get to work without blowing up your computer, performance is going to
be
> rubbish!
> SQL has loads of functions, and you can roll your own, do you know about
> user-defined functions? They're great!
> Anyway, tell us which function you want to emulate and we'll see what we c
an
> do.
> Plus, also bear in mind, SQL might have the function you need, but you jus
t
> don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there'
s a
> ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
> some conditional logic, there's the CASE statement instead of Excel's IF.
> Remember to post some DDL, sample data, expected results etc.
> Damien
> First of all, there are few things you can't do in SQL
> "ram4tech" wrote:
>|||Hello Ram !
http://groups.google.de/group/micro...5d4e46703ec82cd
HTH, jens Suessmeyer.|||Right, well you'd need to know the formula that sits behind IRR to recreate
it, but I guess it boils down to addition, subtraction, maybe an average or
two? T-SQL can do all that, but Excel is better at sums, I'll give it that.
So maybe you should play to the strengths, ie SQL for holding data,
concurrent access, raw power, Excel for sums.
Have you considered linking in to your server, eg a pivot table or external
query?
"ram4tech" wrote:
> Hi Damien:
> The looked in BOL and on the net, but didn't had much luck. I wasn't aware
> that there might be some performance issues. The excel function I am plann
ing
> on using is IRR().
> Thank you.
> --
> Ram
>
> "Damien" wrote:
>|||Here is a T-SQL implementation of IRR I posted a while back.
It is probably less robust than the Excel version, but it may still
work for you:
http://groups.google.co.uk/groups?q...eam+kass+newton
Steve Kass
Drew University
ram4tech wrote:

>Hi Damien:
>The looked in BOL and on the net, but didn't had much luck. I wasn't aware
>that there might be some performance issues. The excel function I am planni
ng
>on using is IRR().
>Thank you.
>

Accessing Different Servers

If I have 2 registered servers, what syntax allows me to access both servers
from the same query window? For example, if I say "use mydb1" and mydb1
resides on server1, I can execute sql against it.
I'd like to execute another statement after finishing with mydb1 that
accesses mydb1 that resides on server2. Any help with proper syntax?Look at the :connect command in SQLCMD mode in the query editor (assuming
this is SSMS).
The help topic which should give some help is "Editing SQLCMD scripts with
Query Editor"

> If I have 2 registered servers, what syntax allows me to access both
> servers from the same query window? For example, if I say "use mydb1"
> and mydb1 resides on server1, I can execute sql against it.
> I'd like to execute another statement after finishing with mydb1 that
> accesses mydb1 that resides on server2. Any help with proper syntax?
>|||i'm just trying to find syntax like "use mydb" that would take the name of
the server.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||can you point me a little better to the help article? i don't know what
SQLCMD mode is and don't know what SSMS is.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||Hi Scott
Bruce is assuming you are working with SQL Server 2005, since you didn't
state the version. SQLCMD is the text interface, replacing osql. SSMS is SQL
Server Management Studio, replacing both Query Analyzer and Enterprise
Manager. Please always state what version you are using at the beginning of
every request for help.
There is no equivalent to USE to connect to another server.
You can access tables on another server by setting up a linked server and
using a 4-part name to access the tables:
SELECT ...
FROM servername.dbname.object_owner.object
WHERE ...
You can read about linked servers in BOL (Books Online, in both SQL 2000 and
SQL 2005).
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
> can you point me a little better to the help article? i don't know what
> SQLCMD mode is and don't know what SSMS is.
>
> "Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
> news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
>
>|||I am using 2005 and I found the help topic, but would appreciate a little
extra info ...
1. Can I use SQLCMD commands within a SPROC?
2. Is it possible to detach/re-attach databases on different servers from 1
SPROC or SQL commands from a single query window?
Do you have any good books to recommend on SQLCMD?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi Scott
> Bruce is assuming you are working with SQL Server 2005, since you didn't
> state the version. SQLCMD is the text interface, replacing osql. SSMS is
> SQL Server Management Studio, replacing both Query Analyzer and Enterprise
> Manager. Please always state what version you are using at the beginning
> of every request for help.
> There is no equivalent to USE to connect to another server.
> You can access tables on another server by setting up a linked server and
> using a 4-part name to access the tables:
> SELECT ...
> FROM servername.dbname.object_owner.object
> WHERE ...
> You can read about linked servers in BOL (Books Online, in both SQL 2000
> and SQL 2005).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
>
>|||Scott
SQLCMD is an operating system utility, not a TSQL command, so the only way
to use it within a sproc is to use xp_cmdshell which allow you to invoke an
OS command.
You can run stored procedures or other commands on other servers if you have
set the other servers up as linked servers. As I already suggested, please
read about linked servers in the BOL.
While in SSMS, you can change the server you are connected to without using
linked servers. So you could run one command against server1, then click the
button to change your connection, and connect to server 2, where you could
run a different command without changing your query window. I'm sorry, I
don't have a SQL 2005 active right now to tell you where the button to
change connections is.
I'm not familiar with any of the SQL 2005 that are out already, but I
wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
best place to look.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
> I am using 2005 and I found the help topic, but would appreciate a little
> extra info ...
> 1. Can I use SQLCMD commands within a SPROC?
> 2. Is it possible to detach/re-attach databases on different servers from
> 1 SPROC or SQL commands from a single query window?
> Do you have any good books to recommend on SQLCMD?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||I get the idea. Do you think that SQLCMD is the only way to detach/atach
databases on different servers from 1 query window? I just though there must
be a way to do this without going into SQLCMD.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUVVM$xOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Scott
> SQLCMD is an operating system utility, not a TSQL command, so the only way
> to use it within a sproc is to use xp_cmdshell which allow you to invoke
> an OS command.
> You can run stored procedures or other commands on other servers if you
> have set the other servers up as linked servers. As I already suggested,
> please read about linked servers in the BOL.
> While in SSMS, you can change the server you are connected to without
> using linked servers. So you could run one command against server1, then
> click the button to change your connection, and connect to server 2, where
> you could run a different command without changing your query window. I'm
> sorry, I don't have a SQL 2005 active right now to tell you where the
> button to change connections is.
> I'm not familiar with any of the SQL 2005 that are out already, but I
> wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
> best place to look.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
>
>

Accessing columns without the coulmn names

Hi,
I am looking for some help in MS SQL server. I want to access the columns of a table without using the names of the colulmns.
Example - SELECT table1.field[1], table1.field[2] FROM table1;
Any information to this effect is much appreciated.
cheers/- PradeepCan you explain how you want to use this information? There are probably ways to get you what you'd like, but there are at least as many really bad answers as really good ones.

-PatP|||Pat, I need to get the value of columns 1 and 2 from the table EMP, while not being bothered as to the names of the columns.|||I'd suggest something like:SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE 'table1' = c.TABLE_NAME
AND 1 = c.ORDINAL_POSITIONSee the BOL regarding INFORMATION_SCHEMA.COLUMNS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_87w3.asp) for more details.

-PatP|||Thanks a million Pat, this is what I was looking for.
Are you a SQL Server DBA?

cheers/- Pradeep|||Heck, I've been called even worse than than SQL DBA!

I answer to just about anything from "Hey you" to "Data Modeler", and everything in between. I tend to refer to myself as a Resident Curmudgeon, which seems about as good a description as any to me!

On a (very slightly) more serious note, yes, SQL DBA is one of the hats that I wear nearly every day.

-PatP|||SQL DataBase Analist|||SQL DataBase AnalistI'm a what-ist ?!?!

-PatP|||i think i can translate ...

ahem.

NEH-HERD.

Scott Davis
"If you are not part of the solution, you are probably 1/5 of the suspension" RDJabarov.|||Now Scott, what the heck does that mean? And when are you planning to have your Margarita? At my funeral?

Access to view reports problem.

I have created a few reports which I have published to
the reporting server.
There is a group created something/Admins for example,
that was assigned to some users. I have the entire folder
that contains the reports in the manager set to allow
browser previleges to view reports within.
The problem is that even though the users are assigned to
that same group as the group name we set up for the
folder to view the reports, they get denied access to
view them. Oddly enough, if we add a single users NT id
to a role to view the reports in the folder, it works
fine.
So why is it if we provide a role to view reports
assigned to a specific users NT id, that works, yet using
the group they are assigned to does not work?
Thanks for the help.You might try doing an IISRESET after adding or removing the Group. It helped me.
Koby
"Adam" wrote:
> I have created a few reports which I have published to
> the reporting server.
> There is a group created something/Admins for example,
> that was assigned to some users. I have the entire folder
> that contains the reports in the manager set to allow
> browser previleges to view reports within.
> The problem is that even though the users are assigned to
> that same group as the group name we set up for the
> folder to view the reports, they get denied access to
> view them. Oddly enough, if we add a single users NT id
> to a role to view the reports in the folder, it works
> fine.
> So why is it if we provide a role to view reports
> assigned to a specific users NT id, that works, yet using
> the group they are assigned to does not work?
> Thanks for the help.
>

Access to TaskHost from derived Task class.

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

tviel wrote:

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.

I hope this helps.

|||

If you look at the two objects and what they inherit or implement, you can see that will never happen.

You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.

Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.

Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.

|||

Hi Darren,

thanks for your reply, maybe there is another alternative for generating a solution.

I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.

Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.

For so, i have the ability to configure some values of similar tasks by one.

Hope this makes things clear.

Thanks.

PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.

|||

I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.

Example:

I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.

I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx

Thanks,

Graham

|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||

Hi,

understandable.

But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

Thanks

Thorsten

|||

tviel wrote:

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx

|||

Hi Michael,

fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)

Thanks

Thorsten

|||

What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?

Thank you,

Graham

|||

If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.

If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.

|||

Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )

class CustomTask : Task
{
private static string topMostPackage = null;

private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;

//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}

Access to TaskHost from derived Task class.

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

tviel wrote:

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.

I hope this helps.

|||

If you look at the two objects and what they inherit or implement, you can see that will never happen.

You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.

Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.

Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.

|||

Hi Darren,

thanks for your reply, maybe there is another alternative for generating a solution.

I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.

Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.

For so, i have the ability to configure some values of similar tasks by one.

Hope this makes things clear.

Thanks.

PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.

|||

I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.

Example:

I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.

I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx

Thanks,

Graham

|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||

Hi,

understandable.

But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

Thanks

Thorsten

|||

tviel wrote:

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx

|||

Hi Michael,

fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)

Thanks

Thorsten

|||

What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?

Thank you,

Graham

|||

If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.

If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.

|||

Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )

class CustomTask : Task
{
private static string topMostPackage = null;

private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;

//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}

Access to SQL migration

I am trying to migrate a set of MS Access queries to SQL that call Access VB
A
code. For example, this is typical:
SELECT Name, DOB, VeryComplexFunction([Value]) AS NumericResult
FROM Table
where,
VeryComplexFunction is a function that is in an Access VBA code
module
All the Access query/VBA works of course, but now I need to migrate to SQL.
I be very grateful for for any help or pointers that anyone can offer.
Thanks in advance."A Shasore" <AShasore@.discussions.microsoft.com> wrote in message
news:8B40B5D3-D11F-437D-B1BA-BEBA142D4C4F@.microsoft.com...
> I am trying to migrate a set of MS Access queries to SQL that call Access
VBA
> code. For example, this is typical:
> SELECT Name, DOB, VeryComplexFunction([Value]) AS NumericResult
> FROM Table
> where,
> VeryComplexFunction is a function that is in an Access VBA code
> module
> All the Access query/VBA works of course, but now I need to migrate to
SQL.
> I be very grateful for for any help or pointers that anyone can offer.
> Thanks in advance.
>
I had a similar problem when I was tasked to upgrade my companys system from
Access to MSSQLS.
What you're looking at is recreating the VBA function as an SQL Server User
Defined Function (UDF) - which is going to require a knowledge of T-SQL.
For upgrading in general I can heartily recomend "Microsoft Access
Developer's Guide to SQL Server" by Chipman & Baron (0-672-31944-6).
Sorry this is a bit vague but I hope it helps.
Thanks
Chris.|||An in-line expression and then a user-defined function should be the
first things to try. Other options are to use an Extended SP or do the
calc in your middle tier.
Don't assume that the way it is done in VB is the best way in SQL. For
example, calculations involving many inputs could possibly be
simplified with a more set-based approach using joins and aggregates.
David Portas
SQL Server MVP
--

Access Script Task

Hi,

How to access the Package inside the Script Taks. for example

IF intStatus = 1 Then

//Execute Package1

Else

//Execute Package2

End IF

in the above sample what is the property to get the Package1 object in script

Thanks & Regards

Jegan.T

You should use the Execute Package Task in order to execute other packages. Any reason why this doesn't work for you?

-Jamie

|||

If i use Execute Package i have to supply all the connection string details.we have the requirement is like this we have to excute the package based on a routine or function execution .its more like using batch file in Data Stage.

Jegan.T

|||

Sorry, I don't know anything about DataStage.

The connection string for the package tells you where it is located. Surely you need the same information in order to execute from the Script Task?

-Jamie

|||

Hi Jamie

Thanks for your reply . but the Script Task does not have any provison for connection string .

i have the design like this in the control flow

Script Task -> DataFlow1 --> DataFlow2

we have to write a routine in the script editor which will decied which DataFlow it has to execute

Script Editor

--

Public Sub Main()

'Code to Invoke DataFlow1 or DataFlow2
End Sub

How to achive this in the script editor ?

Thanks

Jegan.T

|||

I'm confused. At the start of this thread you said you were trying to execute a package.

NOW you say you are trying to decide which data-flow to execute.

The two are completely different so which is it? Perhaps you can explain exactly what it is you are trying to do.

If what you are trying to do is conditionally execute a data-flow then you are going about it the wrong way. You do not need to use a script task - use conditional precedence constraits which are described here: http://www.sqlis.com/default.aspx?306

-Jamie

Access permissions in RS

I was wondering if is posible to set user to view certain reports only, using the active directory user credential. example user 1 can view 3 reports, user 2 can view 5 reports admin user can view all......etc.

I would set up groups on your Active Directory, and then assign people to those groups. Once you have all that set up, you can assign report permissions to certain groups only.