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 & rdgs
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
|||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 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:
[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>

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

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 machi
nes, 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 mac
hines, 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 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:
[vbcol=seagreen]
> 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:
>|||gain, prompting a user need to be done in your client applications. You cann
ot write the whole
application in SQL only, SQL is only a data retrieval and modification langu
age. 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...[vbcol=seagreen]
> 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:
>|||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 ca
nnot write the whole
> application in SQL only, SQL is only a data retrieval and modification lan
guage. 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...
>
>

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?
There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>
|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>
|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:
> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
> > I am doing a code review of a stored procedure.
> > What the procedure does is to create a temp table and then based on parameters sent to
> > the procedure, call different stored procedures which inserts into the temp table just
> > created.
> > Are their any issues with procedures creating temp table on the fly. Any scalability
> > issues?
> >
> >|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.sql

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on paramete
rs sent to
the procedure, call different stored procedures which inserts into the temp
table just
created.
Are their any issues with procedures creating temp table on the fly. Any sca
lability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parame
ters sent to
> the procedure, call different stored procedures which inserts into the tem
p table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any s
calability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
>|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

creating new tables vs passing parameters

Hi,
I am using an Access ADP front end with a SQL Server backend.
I have a report which is based on a stored procedure with multiple parameters.
I have a Search form with multiple drop downs and the parameters are passed to the Sp from this form.

I had many problems passing the parameters to the Report since some of them may not be supplied and a default of % should be used.

I finally decided to creat a new table using the SP and insert the data to that table.

My question is if I create a temp table named "A" each time the report is ran, and say two users run the report at the same time, what will happen?

Is there any way to creat a temp table that SQL Server would take care of its name?

ThanksThere is no problem if you create a temp table during the execution of a SP. SQLServer will take care of you temp table and identify it after the connection user, so that even if two different users executes the same store proc the temp tables that are created are different. A temporary table is scoped to the execution of the batch (here the store proc), that means the table will be removed after the store proc ends.

But I still don't understant why you are not satisfied with store procs and which is the big problem in passing parameters to one. what the benefit will be if you create a temp table?

ionut calin|||The problem is that I want the SP to be the recordsource for a report.
See, I have a search form with the below criteria:

Caller
Property Manager
Date
BuildingID
Comm. Status
Ops Mgr

Then, I have a stored procedure based on 3 joins. The user should be able to check the Parameters he wants to search for and the SP will return the results. For example if I want all the records for Date='2/2/2002' and BuildingID='55555', the SP will return all the recods for these values and % (any thing) for other parameters.

I am using an access front end and I use VB to pass the paramertes to the SP. Its fine so far. But when I set the recordsource of the report to a sql string executing the SP with the parameters the report gets the first parameter and not the second or third or... one.
I don't get why?
Thats why I decided to set the recordsource of the report to a table.
You can see the VB code and the SP below:

Stored Procedure:---------------
---------------
CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = '%',
@.RPropMgr varchar(50) = '%',
@.RDate varchar(50) = '%',
@.RComStat varchar(50) = '%',
@.ROpsMgr varchar(50) = '%',
@.RBID varchar(50) = '%')

AS SELECT
dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID LIKE @.RCaller OR
dbo.tblComIssue.ContactID IS NULL) AND

(dbo.tblProperty.ContactID LIKE @.RPropMgr OR
dbo.tblProperty.ContactID IS NULL) AND

(dbo.tblComIssue.IssueClosed LIKE @.RComStat OR
dbo.tblComIssue.IssueClosed IS NULL) AND

(dbo.tblProperty.OpsMgrID LIKE @.ROpsMgr OR
dbo.tblProperty.OpsMgrID IS NULL) AND

(dbo.tblProperty.BuildingID LIKE @.RBID OR
dbo.tblProperty.BuildingID IS NULL) AND

(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE CONVERT(Varchar(10), @.RDate, 101) OR
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

----------------------

VB Code:------------------
'I have check boxes for each parameter. If the check box is check and the users wants to serach by that check box I will add that parameter to the string:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = Param & "@.RCaller=" & "'" & Me!RCaller & "'"
End If

If Me!ChBID = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RBID=" & "'" & Me!RBID & "'"
End If

If Me!ChPropMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RPropMgr=" & Me!RPropMgr
End If

If Me!ChDate = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
End If

If Me!ChComStat = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RComStat=" & "'" & Me!RComStat "'"&
End If

If Me!ChOpsMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.ROpsMgr=" & "'" & Me!ROpsMgr & "'"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview|||Sorry for the delay, but not my fault. I've wrote a replay for you post it but it didn't apear (I don't know why).

First of all, passing parameters to stored procs:

-I would try to set null as the default value for params (i've had problem passing null value through an ODBC connection (here you have an OLEDB one, and it just might work, but why take chances)

So store proc would look like this:

CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = NULL,
@.RPropMgr varchar(50) = NULL,
@.RComStat varchar(50) = NULL,
@.ROpsMgr varchar(50) = NULL,
@.RBID varchar(50) = NULL)

AS SELECT

--@.RDate varchar(50) = '%', I can't see the point of this parm

dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID IS NULL or @.RCaller is null or dbo.tblComIssue.ContactID LIKE '%'+@.RCaller
) AND

(
dbo.tblProperty.ContactID IS NULL or @.RPropMgr is null or dbo.tblProperty.ContactID LIKE '%'+@.RPropMgr
) AND

(
dbo.tblComIssue.IssueClosed IS NULL or @.RComStat is null or
dbo.tblComIssue.IssueClosed LIKE '%'+@.RComStat
) AND

(
dbo.tblProperty.OpsMgrID IS NULL or @.ROpsMgr is null or
dbo.tblProperty.OpsMgrID LIKE '%'+@.ROpsMgr
) AND

(
dbo.tblProperty.BuildingID IS NULL or @.RBID is null or
dbo.tblProperty.BuildingID LIKE '%'+@.RBID OR
) --AND

--this make no sense at all. What's the point of next possible condition
--"03/12/2003" like "%01/12/2003". You can not use like to compare a
--date field with what another date, or another date part. No sense at all
--(at least not for me) so I've remove it
--(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE
--CONVERT(Varchar(10), @.RDate, 101) OR
--CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

VB:

In VB when you want to call a sub, a functin or a method and you don't want to give explicit values for the parameters that have a default one, you simply "step over" the parameter, but still put commas, like:

DOCmd.SomeMethod 1,,3,4 (here param 2 takes whatever its default value is)

In SQLServer store proc you can't just simply ignore a param. If you want a parameter to take its default value you must write DEFAULT instead of explicit value.

exec SomeStoreProc 1,DEFAULT,3,4 (You don't have to specify param names like @.PARAM1=1, @.PARAM2=default and so on...)

So here is your VB code:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = "'" & Me!RCaller & "' , "
else
Param="DEFAULT , "
End If

If Me!ChBID = True Then
Param = Param & "'" & Me!RBID & "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChPropMgr = True Then
Param = Param & "'" & Me!RPropMgr & "' , "
else
Param=Param & "DEFAULT , "
End If

'I removed the follwoing lines (for the known reason)
'If Me!ChDate = True Then
'If Not Param = "" Then
'Param = Param & ","
'End If
'Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
'End If

If Me!ChComStat = True Then
Param = Param & "'" & Me!RComStat "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChOpsMgr = True Then
Param = Param & "'" & Me!ROpsMgr & "'"
else
Param=Param & "DEFAULT"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview

I am not too familiar with .adp type of project, and I'm not sure if this can really work:

RecordSource = "execute SP_Report_ComIssue_Custom_Param " & Param

but if you said that it works its ok. The main problem was the way that you passed the parameters.

Another thing. If you use Access XP .adp's then your store procedures are seen by Access as querys, so you can set that query as record source for report in design view, and not need to change afterwards (only need to set parameters value for querys -> it's very simple using ADODB and ADOX objects hierachy-> see Catalog object in ADO help)

In Access 2000, .adp's works with SQLServer7 and sees store proces as store procs. I don't think that you can set the record source of a report to a store proc (only to a table or a query, maybe that was thw reason they change the way that store procs aree seen in Access 2002)

Anyway record source property of Access report sucks, you can not for instance set the recor source to a ADO recordset (or to a DAO recordset for that matter). I think this is relly stupid, because prevent programmers from benefit the real power of SQLServer store procs, and I mean it store procs are the best (and now user defined functions too).

Anyway, good luck!

ionut calin

Creating Dynamic MDX Queries within SQL Server 2005 Reporting Services

Please I need some help urgently, How can I create Dynamic MDX Queries
within SQL Server 2005 Reporting Services to use Parameters created
from SQL Queries Dataset.
I was able to do this in SQL Server 2000 Reporting Services by
replacing MDX Statement (1) with (2) below, but I notice the symbol ="
at the begining of statement (2) is not allowed.
(1)
SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
[Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
[Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
[Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
[Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
[Measures].[Expected ROE], [Measures].[Allocated Capital],
[Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS ,
NON EMPTY { DESCENDANTS( [Underwriting Year].[Underwriting Year].[All
Underwriting Years], [Underwriting Year].[Underwriting
Year].[Underwriting Year] ) } ON ROWS
FROM [GroupReporting]
WHERE ( [Transaction Original Currency].[Currency].&[85], [Lifetime
Contract].[Lifetime Contract].[Originating Contract
Reference].&[C12664] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
(2)
="SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
[Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
[Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
[Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
[Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
[Measures].[Expected ROE], [Measures].[Allocated Capital],
[Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS , NON EMPTY {
DESCENDANTS( [Underwriting Year].[Underwriting Year].[All Underwriting
Years], [Underwriting Year].[Underwriting Year].[Underwriting Year] ) }
ON ROWS
FROM [GroupReporting]
WHERE ( [Transaction Original Currency].[Currency].&[" &
Parameters!CurrencyCode.Value & "], [Lifetime Contract].[Lifetime
Contract].[Originating Contract Reference].&[" &
Parameters!MISOriginatingContractID.Value & "] ) CELL PROPERTIES
VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING"You need to change it to be an expression.
Open the dataset properties window.
Make sure the command type is text,
Click Expression Builder (fx) next to the Query String input box, and then
Enter your expression. You won't be able to execute it anymore in the
dataset window, but it will
execute when you refresh your dataset and go to use it.
<michael.oseni@.aleagroup.com> wrote in message
news:1157464471.474318.252920@.d34g2000cwd.googlegroups.com...
> Please I need some help urgently, How can I create Dynamic MDX Queries
> within SQL Server 2005 Reporting Services to use Parameters created
> from SQL Queries Dataset.
> I was able to do this in SQL Server 2000 Reporting Services by
> replacing MDX Statement (1) with (2) below, but I notice the symbol ="
> at the begining of statement (2) is not allowed.
> (1)
> SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> [Measures].[Expected ROE], [Measures].[Allocated Capital],
> [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS ,
> NON EMPTY { DESCENDANTS( [Underwriting Year].[Underwriting Year].[All
> Underwriting Years], [Underwriting Year].[Underwriting
> Year].[Underwriting Year] ) } ON ROWS
> FROM [GroupReporting]
> WHERE ( [Transaction Original Currency].[Currency].&[85], [Lifetime
> Contract].[Lifetime Contract].[Originating Contract
> Reference].&[C12664] )
> CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
> (2)
> ="SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> [Measures].[Expected ROE], [Measures].[Allocated Capital],
> [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS , NON EMPTY {
> DESCENDANTS( [Underwriting Year].[Underwriting Year].[All Underwriting
> Years], [Underwriting Year].[Underwriting Year].[Underwriting Year] ) }
> ON ROWS
> FROM [GroupReporting]
> WHERE ( [Transaction Original Currency].[Currency].&[" &
> Parameters!CurrencyCode.Value & "], [Lifetime Contract].[Lifetime
> Contract].[Originating Contract Reference].&[" &
> Parameters!MISOriginatingContractID.Value & "] ) CELL PROPERTIES
> VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING"
>|||Thanks Chris, your below solution works fine.
Chris wrote:
> You need to change it to be an expression.
> Open the dataset properties window.
> Make sure the command type is text,
> Click Expression Builder (fx) next to the Query String input box, and then
> Enter your expression. You won't be able to execute it anymore in the
> dataset window, but it will
> execute when you refresh your dataset and go to use it.
>
> <michael.oseni@.aleagroup.com> wrote in message
> news:1157464471.474318.252920@.d34g2000cwd.googlegroups.com...
> > Please I need some help urgently, How can I create Dynamic MDX Queries
> > within SQL Server 2005 Reporting Services to use Parameters created
> > from SQL Queries Dataset.
> >
> > I was able to do this in SQL Server 2000 Reporting Services by
> > replacing MDX Statement (1) with (2) below, but I notice the symbol ="
> > at the begining of statement (2) is not allowed.
> >
> > (1)
> > SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> > [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> > [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> > [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> > [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> > Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> > [Measures].[Expected ROE], [Measures].[Allocated Capital],
> > [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS ,
> >
> > NON EMPTY { DESCENDANTS( [Underwriting Year].[Underwriting Year].[All
> > Underwriting Years], [Underwriting Year].[Underwriting
> > Year].[Underwriting Year] ) } ON ROWS
> >
> > FROM [GroupReporting]
> >
> > WHERE ( [Transaction Original Currency].[Currency].&[85], [Lifetime
> > Contract].[Lifetime Contract].[Originating Contract
> > Reference].&[C12664] )
> > CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
> >
> > (2)
> > ="SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> > [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> > [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> > [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> > [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> > Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> > [Measures].[Expected ROE], [Measures].[Allocated Capital],
> > [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS , NON EMPTY {
> > DESCENDANTS( [Underwriting Year].[Underwriting Year].[All Underwriting
> > Years], [Underwriting Year].[Underwriting Year].[Underwriting Year] ) }
> > ON ROWS
> > FROM [GroupReporting]
> > WHERE ( [Transaction Original Currency].[Currency].&[" &
> > Parameters!CurrencyCode.Value & "], [Lifetime Contract].[Lifetime
> > Contract].[Originating Contract Reference].&[" &
> > Parameters!MISOriginatingContractID.Value & "] ) CELL PROPERTIES
> > VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING"
> >

Creating DTS

Is there a way to create a DTS(Data transformation services) from Windows application ?

And how can I send parameters to DTS from my CSharp Code ?

ThanksDTS is based around a COM object model, so any COM compatible programming language can be used to leverage this object model and therefore build, and execute a package.

You can create SSIS packages in code as well.

Rather than writing your own application why not use Enterprise Manager for DTS or Business Intelligence Development Studio for SSIS?|||Cause at my work we do not have Business Intelligence Development Studio, And I don't know how SSIS works,....

I Have a dll to work with DTS but I don't know how to send the parameters....

I downloaded from http://www.sqldts.com/

Any sugestion ?|||Are you really asking about DTS for SQL Server 2000? My confusion arises because this is a forum for SSIS and not DTS, the title indicates that.

How is this DLL expected to work with DTS? What is it?

If you downloaded the DLL from SQLDTS, what DLL is that from the site?|||UHUn It's DTS for SQL 2000

It's a class that I can call DTS from SQL Server or a file .dts|||So where did this class come from?

dtspkg.dll is a DLL from MS that allows you to load and then execute a DTS package.|||Is this one that I'm using

creating databases on the fly

hello,

i am trying to create a database by using a store procedure. This stored procedure takes two input parameters. i want to assign these parameters to the 'Filename' attributes when i'm creating the database both for the .mdf and .ldf files. However i keep getting an error.

These work ---
FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.mdf',

FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf',

These do'nt work---
FILENAME = @.Databasepath,
FILENAME = @.Databaselogpath,

Here is my code:
------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@.Databasepath varchar(100) = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf' , @.Databaselogpath varchar(100)= 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]

CREATE DATABASE Sardonyxrioctestdb
ON
( NAME = 'Sardonyxrioctestdb_dat',
FILENAME = @.Databasepath,
--FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @.Databaselogpath,
--FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

I am still researching my problem but i would appreciate any help. Thanks guys.

JamaicanGuy
**I am a newbie .net developer.
Jah Bless!!!!!!!!!!!!Use dynamic sql:
CREATE PROCEDURE rico_dbasescript
@.Databasepath varchar(100) = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf' , @.Databaselogpath varchar(100)= 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]

declare @.SQLString varchar(8000)

set @.SQLString = 'CREATE DATABASE Sardonyxrioctestdb ON
(NAME = ''Sardonyxrioctestdb_dat'',
FILENAME = ' + @.Databasepath + ',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
(NAME = ''Sardonyxrioctestdb_log'',
FILENAME = ' + @.Databaselogpath + ',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'

execute (@.SQLString)
GO

Creating Custom code with DBNull and Report Parameters

How can I force report parameter to accept a null value. I tried using the check box for null values to no avail. I am creating a custom code to get around this problem. I used the simple IIf statement below:

=IIF (Parameters!MarketID.Value = "None", IsDBNull.Value, Parameters!MarketID.Value)

...I was getting an error message like this:

The value expression for the report parameter ‘MarketID’ contains an error: [BC30455] Argument not specified for parameter 'value' of 'Public Shared Function IsDBNull(value As Object) As Boolean'.

How can I frame the IIf statement?

Thx in advance

You could try replacing IsDBNull.Value with System.DBNull.Value