sql database integrity: return

Showing posts with label return. Show all posts

Sunday, March 25, 2012

creating user defined parameters with input prompts

Hi,
i am trying to create a user defined function that return a table and it
prompts for two inputs
i have created the functions but how do i get the system to prompt me for
the inputs via a view ?
appreciate any advise
tks & rdgsSQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
need to have a client application do that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||maxzsim,
Something like this?
CREATE FUNCTION myfunc
@.param1 <datatype>,
@.param2 <datatype>
RETURNS TABLE
AS
RETURN
(<your select statement>
where col1 = @.param1
and col2 = @.param2)
GO
Mark.
"maxzsim" wrote:
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||Hi ,
does a view able to prompt the user for the parameters then , i can only
open a view as a query and where it allowed me to create parameters but it
seems like there's no way for me to save those changes at all
is there other way thru using a view ?
tks & rdgs
"Tibor Karaszi" wrote:
> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
> need to have a client application do that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs
>
>|||Hi Mark,
in this case it does not run unless i input some values directly however,
i wanted to be prompted to enter the 2 parameters
will a view/stored procedure be able to do that ?
rdgs
"Mark Allison" wrote:
> maxzsim,
> Something like this?
> CREATE FUNCTION myfunc
> @.param1 <datatype>,
> @.param2 <datatype>
> RETURNS TABLE
> AS
> RETURN
> (<your select statement>
> where col1 = @.param1
> and col2 = @.param2)
> GO
> Mark.
> "maxzsim" wrote:
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs|||gain, prompting a user need to be done in your client applications. You cannot write the whole
application in SQL only, SQL is only a data retrieval and modification language. The interaction
with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
something like that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> Hi ,
> does a view able to prompt the user for the parameters then , i can only
> open a view as a query and where it allowed me to create parameters but it
> seems like there's no way for me to save those changes at all
> is there other way thru using a view ?
> tks & rdgs
> "Tibor Karaszi" wrote:
>> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
>> You
>> need to have a client application do that.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
>> > Hi,
>> >
>> > i am trying to create a user defined function that return a table and it
>> > prompts for two inputs
>> >
>> > i have created the functions but how do i get the system to prompt me for
>> > the inputs via a view ?
>> >
>> > appreciate any advise
>> >
>> > tks & rdgs
>>|||tks Tibor for taking time out to explain to me
Cheers
"Tibor Karaszi" wrote:
> gain, prompting a user need to be done in your client applications. You cannot write the whole
> application in SQL only, SQL is only a data retrieval and modification language. The interaction
> with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
> something like that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> > Hi ,
> >
> > does a view able to prompt the user for the parameters then , i can only
> > open a view as a query and where it allowed me to create parameters but it
> > seems like there's no way for me to save those changes at all
> >
> > is there other way thru using a view ?
> >
> > tks & rdgs
> >
> > "Tibor Karaszi" wrote:
> >
> >> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
> >> You
> >> need to have a client application do that.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> >> > Hi,
> >> >
> >> > i am trying to create a user defined function that return a table and it
> >> > prompts for two inputs
> >> >
> >> > i have created the functions but how do i get the system to prompt me for
> >> > the inputs via a view ?
> >> >
> >> > appreciate any advise
> >> >
> >> > tks & rdgs
> >>
> >>
> >>
>
>

creating user defined parameters with input prompts

Friday, February 17, 2012

Creating Dynamic dimension Security with Custom Procedure

Hi All,

We are trying to implement dynamic dimension security using a Custom procedure written in C#.

If we create a single entry in the return set everything works fine (See Below)

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[4]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

SetBuilder.Add(TupleBuilder.ToTuple());

However when we try and add a second item to the Tuple builder the object returns the error : The 'Sub Branch Id' Hierarchy appears more than once in the tuple.

Code snippet :

{

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[2]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

Expression=null;

Member=null;

}

{

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[3]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

Expression=null;

Member=null;

}

SetBuilder.Add(TupleBuilder.ToTuple());

Please can some one help resolve this issue as its becoming critical for the project that we are currently working on, otherwise we will have to revisit the security model.

TIA

Hello,

most likely you don't create a new tuple for each new member. Create a new instance of TupleBuilder to resolve it.

Radim

|||

Thanks for the Help Radmin, It was that.

The source example we're using was from Teo Lacevs book, applied MS Analysis Services 2005. On Teo's Forum we have found the correct syntax (though not in C#), and it does work.

This is the link to the solution, http://prologika.com/CS/forums/thread/1861.aspx

Sunday, March 25, 2012

creating user defined parameters with input prompts

creating user defined parameters with input prompts

creating user defined parameters with input prompts

Friday, February 17, 2012

Creating Dynamic dimension Security with Custom Procedure

sql database integrity

Blog Archive

About Me