Ho to make data source web method execute once per a report?

My report works with XMLDP data provider and obtains source data as a

result of a web method call. This memthod returns all data necessary

for the report. Thus, all of about 20 report datasets query the same

web method with the same parameters and then select appropriate data

with different ElementPath expressions. It's so inefficient and

time-consuming! I cant have respective web method for every dataset.

How can I configure SSRS to call web method only once while the report

is executing? Or how can I leverage the performance at all?I seem to have founed the solution. I've configured report datasource to execute in single transaction and extra marked the source web method with [WebMethod(CashDuration=%TIME)] attribute. Hence, the report datasets call the web method one after another and the method is actually executed only once if %TIME value is more than report processing time. That's what I need!

Ho to make data source web method execute once per a report?

My report works with XMLDP data provider and obtains source data as a

result of a web method call. This memthod returns all data necessary

for the report. Thus, all of about 20 report datasets query the same

web method with the same parameters and then select appropriate data

with different ElementPath expressions. It's so inefficient and

time-consuming! I cant have respective web method for every dataset.

How can I configure SSRS to call web method only once while the report

is executing? Or how can I leverage the performance at all?I seem to have founed the solution. I've configured report datasource to execute in single transaction and extra marked the source web method with [WebMethod(CashDuration=%TIME)] attribute. Hence, the report datasets call the web method one after another and the method is actually executed only once if %TIME value is more than report processing time. That's what I need!

Ho to Execute a Scheduled Job from VB6

Hi ,
I have an schedueled job which runs a DTS .
This job is executing everyday at 2:00 Pm and it transfers data from oracle to the SQl server.
In some cases data in Oracle is not ready at 2:00 , sometimes it is ready at 11:00 AM so what I need is to run that Scheduled job from an exe file .
I can write a code to run the DTS jobs but the problem in this case is I will lose the History Information I have with my schedueled job.So I need to run The Scheduled job to kep the history .
I researched the Internet and all I get is how to create a job but I dont have a clue how to run it .
I'm using Visual basic 6 .
Thanx .Have you looked at...

sp_add_jobschedule|||Thanx for you reply
sp_add_jobs is creating a new job. I dont wan to creat e a new job because the reason for this application is simply keeping track of this Job's history. Otherwise I could run the DTS itself .|||OK, lets try again...

Have you looked at...

sp_add_jobschedule|||I can use sp_update_jobschedule and change the data parameters of job and let it run .
But can't I really run a Job from visual basic ?|||OK...look at

EXECUTE msdb..sp_start_job|||PERFECT !
Thanx .

Hiding Parameters in Report

I'm using a URL to execute reports and I wish to have the parameters not displayed to the user. However, when I either uncheck the Promt User box or delete the prompt string, I get an error stating read only parameters can not be changed.
We have installed SP1 on the server so I thought this issue had been fixed.
TIA for any help.
Dave
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If you don't want any parameters displayed to the user, add
rc:Parameters=false to the URL.
If you want only some of the parameters displayed, then for those that
should be hidden, make sure "Prompt User" is checked and the prompt string
is empty.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OA03nKUXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I'm using a URL to execute reports and I wish to have the parameters not
displayed to the user. However, when I either uncheck the Promt User box or
delete the prompt string, I get an error stating read only parameters can
not be changed.
> We have installed SP1 on the server so I thought this issue had been
fixed.
> TIA for any help.
> Dave
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Hiding a SP in Access under Query, but retain Execute on SP

Hello,

Access XP, SQL Server 2000

Is it possible to hide a SP under Queries in Access, yet still be able
to Execute it from Access?

We hooked up a custom form to accept the input parameters (MS Feature
Request!) for the Stored Procedure. We had two problems with MS's
'Input Parameter' dialog: 1) We could not customize, 2) We continually
received a message from Access stating, "The stored procedure executed
successfully but did not return records" ('SET NOCOUNT ON' gave no
joy).

Below is the sample code we are using.
TestInsert is the SP
txtName is a text box on the form
@.name is char(80)

Private Sub cmdAdd_Enter()

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute

Set cmd = Nothing

End Sub

Any help would be appreciated. We successfully hid our Tables, and
allowed access through Views. But we can't seem to find a good work
around here.

Thanks

Jeff
Jeffrey Walton
noloaderNoloader (noloader@.yahoo.com) writes:
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).

I have to admit that I understand very little of this. But it could
be because I don't know Access. Probably you should put this question
to comp.databases.ms-access instead.

As for the procedures not returning result sets - did you expect them
to return result sets? Did you run them from Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The most common advice is not to use the @. character in variable nemes with
the Jet engine. If this does not work then a specific cast of the input vale
to the parameter type might work.
Martin
"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0405061431.45bfa457@.posting.google.c om...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94E293F8A543Yazorman@.127.0.0.1>...
> Noloader (noloader@.yahoo.com) writes:
> > Is it possible to hide a SP under Queries in Access, yet still be able
> > to Execute it from Access?
> > We hooked up a custom form to accept the input parameters (MS Feature
> > Request!) for the Stored Procedure. We had two problems with MS's
> > 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> > received a message from Access stating, "The stored procedure executed
> > successfully but did not return records" ('SET NOCOUNT ON' gave no
> > joy).
> I have to admit that I understand very little of this. But it could
> be because I don't know Access. Probably you should put this question
> to comp.databases.ms-access instead.
Thanks for the suggestion. Done.

> As for the procedures not returning result sets - did you expect them
> to return result sets? Did you run them from Query Analyzer?
The SP does not return any records (its a basic insert). I don't
understand why Access is throwing the dialog. I take a cockpit
approach - silent is good. Don't flash the lights and blow the whistle
until something goes wrong.

Thanks for the help Erland and Martin.

Jeff|||noloader@.yahoo.com (Noloader) wrote in message news:<6b543aa7.0405061431.45bfa457@.posting.google.com>...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader

> 2) We continually received a message from Access
> stating, "The stored procedure executed successfully
> but did not return records" ('SET NOCOUNT ON' gave no joy).

We modified the Execute call:

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute , , adExecuteNoRecords

Set cmd = Nothing
Set par = Nothing

Jeff