Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts

Tuesday, March 27, 2012

Calling store procedure in Report throu VB

Dears
How to call store procedure in Crystal Report through VISUAL BASIC. i have Sp which has 3 parameters one is string others are dates parameter ,
how to to in the visual basic coding.
can any one help me passing paramerters .
Thanks
AnnuFor Vb 6:

Get rs (Recordset) calling stored procedure with parameters

After:

Dim report As CRAXDRT.report

Set report = crystal.OpenReport(App.Path & _
IIf(Right(App.Path, 1) = "\", "", "\") & _
s_Rpt & GetAdditionalNamePart(s_Rpt) & ".rpt") 'OPEN REPORT

report.Database.LogOnServerEx _
"crdb_odbc.dll", _
c_Settings.GetVal(CRYSTALDSN), _
c_Settings.GetVal(DBNAME), _
c_User.Name, _
c_User.Password, , _
c_Db.ConnectionStr
report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET
report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET

Load frmReport
'ShowSendButton s_Rpt, frmReport.crV, frmReport.cmdSend

'crV - crystal viewer control
frmReport.crV.ReportSource = report 'LINK VIEWER TO REPORT
frmReport.crV.ViewReport

Best,

ika|||This is using VB 6 and CR 8.5 with RDC...

You can get your data from the Stored Proc through ADO and then pass the ADO recordset to Crystal:

'dsrReport is the name of your dsr file
Dim Report As New dsrReport

Set cmdData = New ADODB.Command
Set rsData = New ADODB.Recordset
cmdData.ActiveConnection = cnConnection
cmdData.CommandText = "sp_StoredProcName"
cmdData.CommandType = adCmdStoredProc
cmdData.Parameters.Refresh
cmdData.Parameters("@.Param1") = strParam1
cmdData.Parameters("@.Param2") = dtmParam2
cmdData.Parameters("@.Param3") = dtmParam3

'Required for RecordCount to return an accurate count
rsData.CursorType = adOpenDynamic
rsData.CursorLocation = adUseClient
rsData.Open cmdData

If Not rsData.EOF Then
Report.DiscardSavedData
Report.Database.SetDataSource rsData, 3, 1
End Ifsql

Sunday, March 25, 2012

Calling SQL 2005 stored procedure that returns a value

I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.

I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).

I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.

I am calling the SP as follows:

Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val)
Dim result As String
Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "CreateLogEntry"
cmd.Parameters.Add("@.ChID", Data.SqlDbType.Int)
cmd.Parameters("@.ChID").Value = ID
cmd.Parameters.Add("@.Value", Data.SqlDbType.Int)
cmd.Parameters("@.Value").Value = val
result = cmd.ExecuteNonQuery().ToString
End Using
End Sub

I have tried amending the ExecuteNonQuery line to ExecuteReader()

Any help appreciated

Regards

Clive

Greetings Clive

I would have made the stored procedure use an output parameter as more outputs can always be added. A return can only return one value!

So as output you would add the lines:

cmd.Parameters.Add("Fred"), Data.SqlDbType.Int)

cmd.Parameters("Fred").Direction = Output;

after the result add

Dim iResult As Integer = md.Parameters("Fred").Value

|||

Thanks for that - you are right the ability to return more than one value would be useful.

At the moment in my T-SQL I use the RETURN construct to generate the return value. How would I change that to use an output parameter instead?

regards

Clive

|||

As in

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clive Chinery
-- Create date: 13Aug2007
-- Description: Test for records for today and tomorrow
-- =============================================
CREATE PROCEDURE dbo.DoDateTest
@.Test1 BIT OUTPUT,
@.Test2 BIT OUTPUT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tblToday WHERE Date = GetDate()) SET @.Test1 = 1 ELSE SET @.Test1 = 0
IF EXISTS(SELECT * FROM tblToday WHERE Date = DateAdd(day, 1, GetDate()))
SET @.Test2 = 1 ELSE SET @.Test2 = 0
------- This is the End ----
GO

This returns two output values. I normally put the output parameters at the end of the declaration.

Regards

Clive Chinery

Monday, March 19, 2012

Calling a VB function/sub from SQL

Hello,
Is there any way for calling a VB function/sub (with parameters) from SQL?
Thank youWith Access (Jet SQL) you can call a few functions like DateDiff() and IIf() but not any custom functions that you may write.

Calling a UDF that returns a table from within a select statement

I have a UDF that returns a table data type by recursion. The UDF takes a
two input parameters. Can I call the UDF within a Select statement?
i.e.
SELECT dbo.GetTable(col2,1)
FROM table
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
>I have a UDF that returns a table data type by recursion. The UDF takes a
> two input parameters. Can I call the UDF within a Select statement?
> i.e.
> SELECT dbo.GetTable(col2,1)
> FROM table
If you are passing a column value into the UDF you must use the new (SQL
2005) APPLY operator
SELECT t.whatever, gt.*
FROM table t
CROSS APPLY dbo.GetTable(col2,1) gt
David
|||David,
Does this apply only to CLR functions?
-- Bill
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eG9QJ7JTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
> If you are passing a column value into the UDF you must use the new (SQL
> 2005) APPLY operator
> SELECT t.whatever, gt.*
> FROM table t
> CROSS APPLY dbo.GetTable(col2,1) gt
> David
>
|||Thanks,
So, is there a method or workaround for doing this in SQL Server 2000?
"David Browne" wrote:

>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
> If you are passing a column value into the UDF you must use the new (SQL
> 2005) APPLY operator
> SELECT t.whatever, gt.*
> FROM table t
> CROSS APPLY dbo.GetTable(col2,1) gt
> David
>
>
|||"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:C5C4903C-A9CA-4C80-8B8D-85E0C7C9A4AD@.microsoft.com...
> Thanks,
> So, is there a method or workaround for doing this in SQL Server 2000?
>
Not in the general case (thus the need to extend SQL). But if you have only
a few values for col2, you can perhaps use a cursor and a temp table to
construct the results.
David
|||"AlterEgo" <alterego55@.dslextreme.com> wrote in message
news:e7iqBLKTHHA.4756@.TK2MSFTNGP06.phx.gbl...
> David,
> Does this apply only to CLR functions?
>
No. All Table-Valued UDF's/.
David

Calling a UDF that returns a table from within a select statement

I have a UDF that returns a table data type by recursion. The UDF takes a
two input parameters. Can I call the UDF within a Select statement?
i.e.
SELECT dbo.GetTable(col2,1)
FROM table"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
>I have a UDF that returns a table data type by recursion. The UDF takes a
> two input parameters. Can I call the UDF within a Select statement?
> i.e.
> SELECT dbo.GetTable(col2,1)
> FROM table
If you are passing a column value into the UDF you must use the new (SQL
2005) APPLY operator
SELECT t.whatever, gt.*
FROM table t
CROSS APPLY dbo.GetTable(col2,1) gt
David|||David,
Does this apply only to CLR functions?
-- Bill
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eG9QJ7JTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
>>I have a UDF that returns a table data type by recursion. The UDF takes a
>> two input parameters. Can I call the UDF within a Select statement?
>> i.e.
>> SELECT dbo.GetTable(col2,1)
>> FROM table
> If you are passing a column value into the UDF you must use the new (SQL
> 2005) APPLY operator
> SELECT t.whatever, gt.*
> FROM table t
> CROSS APPLY dbo.GetTable(col2,1) gt
> David
>|||Thanks,
So, is there a method or workaround for doing this in SQL Server 2000?
"David Browne" wrote:
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
> >I have a UDF that returns a table data type by recursion. The UDF takes a
> > two input parameters. Can I call the UDF within a Select statement?
> >
> > i.e.
> >
> > SELECT dbo.GetTable(col2,1)
> > FROM table
> If you are passing a column value into the UDF you must use the new (SQL
> 2005) APPLY operator
> SELECT t.whatever, gt.*
> FROM table t
> CROSS APPLY dbo.GetTable(col2,1) gt
> David
>
>|||"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:C5C4903C-A9CA-4C80-8B8D-85E0C7C9A4AD@.microsoft.com...
> Thanks,
> So, is there a method or workaround for doing this in SQL Server 2000?
>
Not in the general case (thus the need to extend SQL). But if you have only
a few values for col2, you can perhaps use a cursor and a temp table to
construct the results.
David|||"AlterEgo" <alterego55@.dslextreme.com> wrote in message
news:e7iqBLKTHHA.4756@.TK2MSFTNGP06.phx.gbl...
> David,
> Does this apply only to CLR functions?
>
No. All Table-Valued UDF's/.
David

Calling a UDF that returns a table from within a select statement

I have a UDF that returns a table data type by recursion. The UDF takes a
two input parameters. Can I call the UDF within a Select statement?
i.e.
SELECT dbo.GetTable(col2,1)
FROM table"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
>I have a UDF that returns a table data type by recursion. The UDF takes a
> two input parameters. Can I call the UDF within a Select statement?
> i.e.
> SELECT dbo.GetTable(col2,1)
> FROM table
If you are passing a column value into the UDF you must use the new (SQL
2005) APPLY operator
SELECT t.whatever, gt.*
FROM table t
CROSS APPLY dbo.GetTable(col2,1) gt
David|||David,
Does this apply only to CLR functions?
-- Bill
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eG9QJ7JTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:DA800CC3-27DD-4003-B337-8B4C990B3A7E@.microsoft.com...
> If you are passing a column value into the UDF you must use the new (SQL
> 2005) APPLY operator
> SELECT t.whatever, gt.*
> FROM table t
> CROSS APPLY dbo.GetTable(col2,1) gt
> David
>|||"AlterEgo" <alterego55@.dslextreme.com> wrote in message
news:e7iqBLKTHHA.4756@.TK2MSFTNGP06.phx.gbl...
> David,
> Does this apply only to CLR functions?
>
No. All Table-Valued UDF's/.
David

Calling a stored procedure that inserts records and generates an output parameter

I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.

How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.

The Execute SQL Task sounds best suited to this scenario: http://www.sqlis.com/default.aspx?58

-Jamie

|||I've already tried that but I need to pass multiple records in the Execute SQL task, not just one.|||

So you're going to be executing the SP for every record in the pipeline? If that is the case then the OLE DB Command transform will be of help but I would recommend you find an alternate means of doing this. Executing a seperate query for each row is not considered great practice.

-Jamie

|||How do you use the OLEDB Command Transform? Actually, I just created a simple app to do the trick. The thing is, I cannot do anything about it. I just need to migrate existing records into a new database. I need to use the existing SP to insert the records from an Oracle database into the new SQL Server database because they are of different schemas and design.

Sunday, March 11, 2012

Calling a report by the URL

I have a report on our server which can accept Report parameters with which to search our database... like so.

http://Server/ReportServer?/Detail%20Reports/Person%20Details[

&SSN=<SSN as String>

&LASTNAME=<LASTNAME as String>

&FIRSTNAME=<FIRSTNAME as String>

&MIDDLENAME=<MIDDLENAME as String>

&DOB=<DOB as DateTime>

]&rs:Command=Render

The thing I'm wondering is when I pass:

http://Server/ReportServer?/Detail%20Reports/Person%20Details&SSN=444-44-4444&rs:Command=Render

The report does not automagicklly search, instead it come up in a browser with the parameters filled in and waits for you to press the "View Report" button. Is it possble to force it past that step into the actual report?

Change the definition of your parameters in your report and give all the parameters a default so that when you browse to the report in Report Manager it automatically runs. Then the second URL you mention above should work as you want.|||

OK, that makes sense... I'll have to mess around with it and see what I can come up with.

Thanks for the response! Big Smile

calling a nested procedure

Everytime I execute this script - I am not getting the result I want. I was hoping to take the some parameters call one stored procedufre with in the main procedure. I was hoping to assign a variable to the nested procedure and use that variable in the main procedure, but I am getting a '0' as result - however when I call the a inner procedure by itself if get the correct result. Can someone look at this and tell me where I am wrong? It seems to be a scope problem.

Thanks

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: September 8,2006
-- Description: Inserts a new project
-- =============================================
ALTER PROCEDURE [echo88].[insertProject]
-- Add the parameters for the stored procedure here
@.name varchar(50),
@.desc varchar(MAX),
@.start datetime,
@.finish datetime,
@.memid int,
@.addr1 varchar(32),
@.addr2 varchar(16),
@.city varchar(32),
@.stateid int,
@.zipcode char(5)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.siteid int;
EXEC @.siteid = insertSite @.addr1, @.addr2, @.city, @.stateid, @.zipcode;
-- Insert statements for procedure here
INSERT INTO Project(Title, Description, Start, Finish, SiteID, MemberID ) VALUES ( @.name, @.desc, @.start, @.finish, @.siteid, @.memid );
END

SQL Server currently uses return values 0 through -14 to indicate the execution status of stored procedures. Have you defined RETURN values in the 'insertSite' stored procedure? If not, by default it will return 0 for successful execution, so you'll always get 0 for @.siteid. In this case I suggest you use OUTPUT parameter in the stored procedure, for example:

ALTER PROCEDURE [echo88].[insertSite]
-- Add the parameters for the stored procedure here
@.siteid int output
--add other parameters
AS
BEGIN

--add your code

SET @.siteid=1--set the value of the output parameter

END

Then when call it a another procedure:

ALTER PROCEDURE [echo88].[insertProject]
-- Add the parameters for the stored procedure here
@.name varchar(50),
@.desc varchar(MAX),
@.start datetime,
@.finish datetime,
@.memid int,
@.addr1 varchar(32),
@.addr2 varchar(16),
@.city varchar(32),
@.stateid int,
@.zipcode char(5)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.siteid int;
EXEC insertSite @.addr1, @.addr2, @.city, @.stateid, @.zipcode,@.siteid output;
-- Insert statements for procedure here
INSERT INTO Project(Title, Description, Start, Finish, SiteID, MemberID ) VALUES ( @.name, @.desc, @.start, @.finish, @.siteid, @.memid );
END

Wednesday, March 7, 2012

Call SP from VB using optional parameters

Evenyone,
I am using VB6 ADO command object to call Stored procedure. the SP have
lots of paramters, some of parameters have already been given default
values. To that parameters that have defalut values, I don't want to
create these parameter objects when calling SP from VB side. But it
seems doesn't work. It seems you must give each parameters. It pass
parameter by position, not by name. I know in SQL Server, SP can call
another SP by passing parameter by name.
Some idea about this? Thanks for your help.
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
For VB6 there is a stored procedure add-in that you can download for
free from msdn (search for "ADO Stored Procedure Add-In"). Follow the
instructions for installing it as an add-in, and it will take care of
generating the parameters and all the rest of the ADO code you need
for calling your stored procedures.
--Mary
On Wed, 29 Sep 2004 11:31:40 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Evenyone,
>I am using VB6 ADO command object to call Stored procedure. the SP have
>lots of paramters, some of parameters have already been given default
>values. To that parameters that have defalut values, I don't want to
>create these parameter objects when calling SP from VB side. But it
>seems doesn't work. It seems you must give each parameters. It pass
>parameter by position, not by name. I know in SQL Server, SP can call
>another SP by passing parameter by name.
>Some idea about this? Thanks for your help.
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Hi Mary,
I can't find that add-in download link, Can you pls give me that?
Thanks
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I wasn't able to find the msdn link to download the official version,
but googling "Microsoft ADO Stored Procedure Add-In" gave several
promising hits from developers elaborating on the original.
--Mary
On Thu, 30 Sep 2004 14:53:03 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Hi Mary,
>I can't find that add-in download link, Can you pls give me that?
>Thanks
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Mary,
Thank you for your reply.
I already got the solution even if i didn't install the add-in. By
setting commmandObj.NamedParameters = True, I can pass named
parameters,not by postions of parameters.
By the way, I am always trying to find a way to debuging the Stored
procedure during the VB6 run time for long time. I am meaning
continusely debugging VB code and SP code. Any suggest about that?
Thanks
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Better google that one too--there's kb articles and advice on that,
but getting it to actually work is another story. Attempting to debug
stored procedures from VB is a real PITA that most people have just
given up on. I always recommend using Query Analyzer rather than
wasting the time and energy on VB6 configuration for debugging sprocs.
--Mary
On Fri, 01 Oct 2004 08:52:00 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Mary,
>Thank you for your reply.
>I already got the solution even if i didn't install the add-in. By
>setting commmandObj.NamedParameters = True, I can pass named
>parameters,not by postions of parameters.
>By the way, I am always trying to find a way to debuging the Stored
>procedure during the VB6 run time for long time. I am meaning
>continusely debugging VB code and SP code. Any suggest about that?
>Thanks
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!

Call RS Report from SQL Script

Is it possible to call for a RS report and pass in parameters from a SQL
script?Where do you want to report to display? Where/how are you running your SQL
script? There is the sp_OACreate sproc that might serve the purpose.
Jeff
"Devon" <devon11livolsi@.optonline.net> wrote in message
news:eIREuSriEHA.3564@.TK2MSFTNGP10.phx.gbl...
> Is it possible to call for a RS report and pass in parameters from a SQL
> script?
>

call function inside Stored Procedure Error

i call the function inside the stored procedure and when i execute it theres a error

ALTERPROCEDURE [dbo].[usp_LoadEmployees]

-- Add the parameters for the stored procedure here

AS

BEGIN

SELECT dbo.Employees.EmployeeID,dbo.fn_ProperEmployees(dbo.Employees.EmployeeID)as MyEmployee,

dbo.Employees.Age, dbo.Employees.Sex, dbo.Employees.PositionID, dbo.Positions.Position_Name, dbo.Employees.DepartmentID,

dbo.Departments.DepartmentName

FROM dbo.Employees INNERJOIN

dbo.Departments ON dbo.Employees.DepartmentID = dbo.Departments.DepartmentID INNERJOIN

dbo.Positions ON dbo.Employees.PositionID = dbo.Positions.PositionID

GROUPBY dbo.Employees.EmployeeID, dbo.Employees.MI,

dbo.Employees.Age, dbo.Employees.Sex, dbo.Employees.PositionID, dbo.Positions.Position_Name, dbo.Employees.DepartmentID,

dbo.Departments.DepartmentName

ORDERBY dbo.Employees.EmployeeID

END

- function is this -

ALTER FUNCTION [dbo].[fn_ProperEmployees](@.cEmployeeID varchar(50))

RETURNS TABLE

AS

RETURN

(

-- Add the SELECT statement with parameter references here

SELECT EmployeeName =(Upper(dbo.Employees.Lastname)+','+ dbo.Employees.Firstname +' '+ dbo.Employees.MI)

FROM Employees

WHERE EmployeeID = @.cEmployeeID

)

the error is

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ProperEmployees", or the name is ambiguous.

The function is a inline table function, if it is table function then you can't use it as column (only scalar functions are allowed)..

Change your function as follow as ,

Code Snippet

ALTER FUNCTION [dbo].[fn_ProperEmployees](@.cEmployeeID varchar(50))

RETURNS Varchar(1000)

AS

Begin

Declare @.EmployeeName as Varchar(1000);

-- Add the SELECT statement with parameter references here

SELECT

@.EmployeeName = (Upper(dbo.Employees.Lastname)

+ ',' + dbo.Employees.Firstname

+ ' ' + dbo.Employees.MI)

FROM

Employees

WHERE

EmployeeID = @.cEmployeeID;

Return @.EmployeeName;

End

call db2 stored procedure

Are there any examples of aclling a db2 stored procedure with input and output parameters from sql 2000 tsql? Thanks

use linked server..

example:

EXEC sp_addlinkedserver

@.server='DB2',

@.srvproduct='Microsoft OLE DB Provider for DB2',

@.catalog='DB2',

@.provider='DB2OLEDB',

@.provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'

EXEC sp_addlinkedsrvlogin 'DB2', 'false', NULL, 'DB2User', 'DB2Password'

Then use OPENQUERY function to call your target DB2 objects..See more on online book about Linked Server.|||Thanks for the update. I see examples of using openquery with db2 select statements but couldn't find any that called a stored procedure with input and output procedures. Is there another function that should be used? Thanks|||Are there any examples of calling a db2 stored procedure with input and output parameters? Should I use the {call format. are there any examples for that? Thanks

Saturday, February 25, 2012

Calendar slow to load

Is anyone else experiencing performance problems with reports that use the calendar control for date parameters? Whenever we load a report that uses a calendar it takes several seconds to fully load the form (you can see something like this in the status bar:"...ReportViewerWebControl.axd?OpType=Calendar..."). This is not a serious problem, but if the user tries to use the control before it fully loads then the page throws javascript errors. I am wondering if this is a issue with the report itself or the server configuration, or if it is just something we have to live with.

The calendar control works by loading a years worth of dates to allow you to browse through the months more quickly. This is not a overly large amount of data and I have not seen it take a significant amount of time myself. The is, however, no way to change the behavior of this control. Things that might cause this behavior are a slow network connection or a slow server, however you would notice everything having longer load times then.

-Daniel

|||

I have a new RS2005 server in production and the speed at which the calendar control loads is also painfully slow.

On the initial rounds of testing it was so slow that my Business Analysts / QA person reported *bugs* with the calendar control refreshing the page or displaying blank and so forth.

Is there a way to limit how much date information it pulls down initially or to possibly build a custom control based on the out of the box control and then limit the date range loaded by default?

For my users at least the date range is going to be from minus 3 months to present for about 95% of the runs.

|||Just to add to this, I have the same problem, I thought it was a bug too but there doesnt seem to be a way of fixing this....at least none that I know of.|||

We have the same problem with calendar control. I have a filter with a date that should be greater than a value given by the user in the report. If I have a default value on the date the report loads very slow, in about 10 seconds. When I change the value in the report and click view report it still load slow.

On the other hand if I dont give a default value in the filter the report loads within 2 seconds. It also loads within seconds if I change the value in the report?

Any suggestions?

/Stefan

|||

I wonder if this isn't related to bugs in the control?

The two behaviors I notice are an incredible amount of sluggishness associated with having a default value and that each time you change the value it makes a server round-trip even though you haven't clicked on the View Report button.

|||

Can you file a bug on this issue along with your report definition that makes this happen? When you do so, please include infomration on the SQL Server Reporting Services version you're using and what browser and it's version you're using.

You can file the bug here: http://lab.msdn.microsoft.com/productfeedback/

When you have an expression based default value, we need to do a round trip to the server to determine the new value for the parameter. If the parameter depends on a previous parameter, whenever you change the upstream parameter we'll do a round trip. What I'd like to know is if the slowness persists if you were to substitute a string parameter for your datatime parameter. If it does, it is probably that the server is taking a long time to process your request - is it adequately resourced for the work load? Otherwise it might be specific to the data picker control.

Hope that helps,

-Lukasz

Calendar slow to load

Is anyone else experiencing performance problems with reports that use the calendar control for date parameters? Whenever we load a report that uses a calendar it takes several seconds to fully load the form (you can see something like this in the status bar:"...ReportViewerWebControl.axd?OpType=Calendar..."). This is not a serious problem, but if the user tries to use the control before it fully loads then the page throws javascript errors. I am wondering if this is a issue with the report itself or the server configuration, or if it is just something we have to live with.

The calendar control works by loading a years worth of dates to allow you to browse through the months more quickly. This is not a overly large amount of data and I have not seen it take a significant amount of time myself. The is, however, no way to change the behavior of this control. Things that might cause this behavior are a slow network connection or a slow server, however you would notice everything having longer load times then.

-Daniel

|||

I have a new RS2005 server in production and the speed at which the calendar control loads is also painfully slow.

On the initial rounds of testing it was so slow that my Business Analysts / QA person reported *bugs* with the calendar control refreshing the page or displaying blank and so forth.

Is there a way to limit how much date information it pulls down initially or to possibly build a custom control based on the out of the box control and then limit the date range loaded by default?

For my users at least the date range is going to be from minus 3 months to present for about 95% of the runs.

|||Just to add to this, I have the same problem, I thought it was a bug too but there doesnt seem to be a way of fixing this....at least none that I know of.|||

We have the same problem with calendar control. I have a filter with a date that should be greater than a value given by the user in the report. If I have a default value on the date the report loads very slow, in about 10 seconds. When I change the value in the report and click view report it still load slow.

On the other hand if I dont give a default value in the filter the report loads within 2 seconds. It also loads within seconds if I change the value in the report?

Any suggestions?

/Stefan

|||

I wonder if this isn't related to bugs in the control?

The two behaviors I notice are an incredible amount of sluggishness associated with having a default value and that each time you change the value it makes a server round-trip even though you haven't clicked on the View Report button.

|||

Can you file a bug on this issue along with your report definition that makes this happen? When you do so, please include infomration on the SQL Server Reporting Services version you're using and what browser and it's version you're using.

You can file the bug here: http://lab.msdn.microsoft.com/productfeedback/

When you have an expression based default value, we need to do a round trip to the server to determine the new value for the parameter. If the parameter depends on a previous parameter, whenever you change the upstream parameter we'll do a round trip. What I'd like to know is if the slowness persists if you were to substitute a string parameter for your datatime parameter. If it does, it is probably that the server is taking a long time to process your request - is it adequately resourced for the work load? Otherwise it might be specific to the data picker control.

Hope that helps,

-Lukasz

Calendar slow to load

Is anyone else experiencing performance problems with reports that use the calendar control for date parameters? Whenever we load a report that uses a calendar it takes several seconds to fully load the form (you can see something like this in the status bar:"...ReportViewerWebControl.axd?OpType=Calendar..."). This is not a serious problem, but if the user tries to use the control before it fully loads then the page throws javascript errors. I am wondering if this is a issue with the report itself or the server configuration, or if it is just something we have to live with.

The calendar control works by loading a years worth of dates to allow you to browse through the months more quickly. This is not a overly large amount of data and I have not seen it take a significant amount of time myself. The is, however, no way to change the behavior of this control. Things that might cause this behavior are a slow network connection or a slow server, however you would notice everything having longer load times then.

-Daniel

|||

I have a new RS2005 server in production and the speed at which the calendar control loads is also painfully slow.

On the initial rounds of testing it was so slow that my Business Analysts / QA person reported *bugs* with the calendar control refreshing the page or displaying blank and so forth.

Is there a way to limit how much date information it pulls down initially or to possibly build a custom control based on the out of the box control and then limit the date range loaded by default?

For my users at least the date range is going to be from minus 3 months to present for about 95% of the runs.

|||Just to add to this, I have the same problem, I thought it was a bug too but there doesnt seem to be a way of fixing this....at least none that I know of.|||

We have the same problem with calendar control. I have a filter with a date that should be greater than a value given by the user in the report. If I have a default value on the date the report loads very slow, in about 10 seconds. When I change the value in the report and click view report it still load slow.

On the other hand if I dont give a default value in the filter the report loads within 2 seconds. It also loads within seconds if I change the value in the report?

Any suggestions?

/Stefan

|||

I wonder if this isn't related to bugs in the control?

The two behaviors I notice are an incredible amount of sluggishness associated with having a default value and that each time you change the value it makes a server round-trip even though you haven't clicked on the View Report button.

|||

Can you file a bug on this issue along with your report definition that makes this happen? When you do so, please include infomration on the SQL Server Reporting Services version you're using and what browser and it's version you're using.

You can file the bug here: http://lab.msdn.microsoft.com/productfeedback/

When you have an expression based default value, we need to do a round trip to the server to determine the new value for the parameter. If the parameter depends on a previous parameter, whenever you change the upstream parameter we'll do a round trip. What I'd like to know is if the slowness persists if you were to substitute a string parameter for your datatime parameter. If it does, it is probably that the server is taking a long time to process your request - is it adequately resourced for the work load? Otherwise it might be specific to the data picker control.

Hope that helps,

-Lukasz

Calendar parameter input

Hi,
We have several challenges because of lack of calendar parameter input for
report parameters in Reporting Service 2000
Do we have Calendar parameter input in Reporting Services 2005?
Thank you,
Alanyes,you have datepicker.
"A.M-SG" wrote:
>
> Hi,
>
> We have several challenges because of lack of calendar parameter input for
> report parameters in Reporting Service 2000
>
> Do we have Calendar parameter input in Reporting Services 2005?
>
> Thank you,
> Alan
>
>

Friday, February 24, 2012

Calendar object for parameters

Hi,
I've some searching around this group but haven't found any good
answers on my question. There isn't an out of the box way of
attaching a calendar object to let's say a startdate parameter on a
report. I was reading you could accomplish this through the use of an
active x object. Has anyone attempted this? If not does anyone have
any idea of how it would be accomplished?You'd have to write a custom ASP.NET app with the calendar control and the
report in it, and pass the dates as parameters to the report.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"cordata5" <cordata5@.yahoo.com> wrote in message
news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> Hi,
> I've some searching around this group but haven't found any good
> answers on my question. There isn't an out of the box way of
> attaching a calendar object to let's say a startdate parameter on a
> report. I was reading you could accomplish this through the use of an
> active x object. Has anyone attempted this? If not does anyone have
> any idea of how it would be accomplished?|||I can't seem to get this to work.
In Report Manager I go to the folder I want and upload my aspx parameter
page with the calendars on it. The calendars do not show. Is the bin folder
needed? Where would I put it?
"Ravi Mumulla (Microsoft)" wrote:
> You'd have to write a custom ASP.NET app with the calendar control and the
> report in it, and pass the dates as parameters to the report.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "cordata5" <cordata5@.yahoo.com> wrote in message
> news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> > Hi,
> > I've some searching around this group but haven't found any good
> > answers on my question. There isn't an out of the box way of
> > attaching a calendar object to let's say a startdate parameter on a
> > report. I was reading you could accomplish this through the use of an
> > active x object. Has anyone attempted this? If not does anyone have
> > any idea of how it would be accomplished?
>
>|||You should create your own web site. Do not mix your in with Reporting
Services, that is asking for trouble. Note that you also then need to
integrate with RS using either URL integration or webservices.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:D4AA33F6-E6CB-4806-8286-DD2C876B4F31@.microsoft.com...
> I can't seem to get this to work.
> In Report Manager I go to the folder I want and upload my aspx parameter
> page with the calendars on it. The calendars do not show. Is the bin
folder
> needed? Where would I put it?
> "Ravi Mumulla (Microsoft)" wrote:
> > You'd have to write a custom ASP.NET app with the calendar control and
the
> > report in it, and pass the dates as parameters to the report.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "cordata5" <cordata5@.yahoo.com> wrote in message
> > news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> > > Hi,
> > > I've some searching around this group but haven't found any good
> > > answers on my question. There isn't an out of the box way of
> > > attaching a calendar object to let's say a startdate parameter on a
> > > report. I was reading you could accomplish this through the use of an
> > > active x object. Has anyone attempted this? If not does anyone have
> > > any idea of how it would be accomplished?
> >
> >
> >

Calendar Controls + Multi-Value causes Error?

I have a Report with 2 Date Parameters using Calendar Controls, followed by 2
multi-valued (cascading) parameters. This worked fine with the June CTP15.
After upgrading to the Sept CTP16, if I change the default date (yesterday)
on either calendar control, an error page is generated, with this message:
"Value cannot be null. Parameter name: value". Of course, this is only after
deploying the report; it still previews fine in Report Designer.
Pressing the "Back" button on the browser returns to the report page, with
the date selection intact. After selecting the second date, the same thing
happens, and I have to press "Back" in the browser. However, no errors are
generated when I now set the multi-select parameters themselves.
I've been able to repro this problem with a trivial report having 2 date
parameters and 1 multi-select parameter. The behavior isn't identical,
because the error page is generated only when changing the first date from
its default. There is also no error when no default dates are configured for
the 2 calendar controls.
Wondering if anyone else has encountered similar quirks?Thanks for reporting this. It has been fixed in builds after CTP 16 and will
work e.g. in the final SQL Server 2005 RTM build.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Deepak" <Deepak@.discussions.microsoft.com> wrote in message
news:696B6FCF-BE89-46A0-A7C8-8588D4F4C9B9@.microsoft.com...
>I have a Report with 2 Date Parameters using Calendar Controls, followed by
>2
> multi-valued (cascading) parameters. This worked fine with the June CTP15.
> After upgrading to the Sept CTP16, if I change the default date
> (yesterday)
> on either calendar control, an error page is generated, with this message:
> "Value cannot be null. Parameter name: value". Of course, this is only
> after
> deploying the report; it still previews fine in Report Designer.
> Pressing the "Back" button on the browser returns to the report page, with
> the date selection intact. After selecting the second date, the same thing
> happens, and I have to press "Back" in the browser. However, no errors are
> generated when I now set the multi-select parameters themselves.
> I've been able to repro this problem with a trivial report having 2 date
> parameters and 1 multi-select parameter. The behavior isn't identical,
> because the error page is generated only when changing the first date from
> its default. There is also no error when no default dates are configured
> for
> the 2 calendar controls.
> Wondering if anyone else has encountered similar quirks?|||Robert,
Thanks for confirming that this has already been fixed - FYI I'd submitted a
bug report to BetaPlace, with Track ID: 876023652.
- Deepak
"Robert Bruckner [MSFT]" wrote:
> Thanks for reporting this. It has been fixed in builds after CTP 16 and will
> work e.g. in the final SQL Server 2005 RTM build.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Deepak" <Deepak@.discussions.microsoft.com> wrote in message
> news:696B6FCF-BE89-46A0-A7C8-8588D4F4C9B9@.microsoft.com...
> >I have a Report with 2 Date Parameters using Calendar Controls, followed by
> >2
> > multi-valued (cascading) parameters. This worked fine with the June CTP15.
> > After upgrading to the Sept CTP16, if I change the default date
> > (yesterday)
> > on either calendar control, an error page is generated, with this message:
> > "Value cannot be null. Parameter name: value". Of course, this is only
> > after
> > deploying the report; it still previews fine in Report Designer.
> >
> > Pressing the "Back" button on the browser returns to the report page, with
> > the date selection intact. After selecting the second date, the same thing
> > happens, and I have to press "Back" in the browser. However, no errors are
> > generated when I now set the multi-select parameters themselves.
> >
> > I've been able to repro this problem with a trivial report having 2 date
> > parameters and 1 multi-select parameter. The behavior isn't identical,
> > because the error page is generated only when changing the first date from
> > its default. There is also no error when no default dates are configured
> > for
> > the 2 calendar controls.
> >
> > Wondering if anyone else has encountered similar quirks?
>
>

Calendar Control issues

Hi all,

When I click on a calendar control for date parameters the page will freeze or throw a java script error. This does not happen all the time, and is not isolated to one PC. It happens randomly on several PCs. Is there any known issue with this control?

regards,

Bill

The only issue that I have seen is if you try to open the calendar control while it is still loading the iframe for the calendar. Next time it happens see if the IE status bar is still showing the page loading.|||It is in the process of loading. There are times though, when it takes 10 seconds or more to load locally, and much longer if a user is accessing the page outside of the local LAN. If the user clicks on the control during the load, a java error occurs and the page is disabled. This cant be by design , could it? :-)|||

No it is not by design. :) I am surprised that it does take so long to load. Is this happening the first time you view the report or when navigating through calendar months? If it is the first time, the calendar is one of the last things loaded on the page, if the entire page is still loading (IE will render objects and allow interaction even though the page hasn't finished loading) the calander may not be loaded until the page is loaded. Are you viewing the reports through Report Manager or your own pages? If they are your own pages, perhaps there is some improvement that can be made there to speed up the page load.

As far as anything you can do to Report Server, there isn't anything that can currently be done to affect this behavior. We are aware of the issue and are looking to fix it in a future version. In the meantime users need to be aware of the issue and wait to open the drop down until the page has fully loaded.

|||Thank you for your answer. The page is displayed through report services, not my own page. I hope you have a fix soon, as it is making report services for my company very difficult to use. On a side note, report services displayed in IE7 with a calendar control will not work at all.|||

There have been quite a few reports of problems with the calendar control (just search the forums) and the basic answer seems to be "we cant replicate any problem" so I think MS dont know what to fix.

Certainly I have the same issue as you, the calendar controls throwing script errors and taking ages to load.

|||I am just hoping that some sort of hot fix is available soon. Like I said though, it does not work at all with IE7 so if MS is planning to release IE7 as a critical update like it is rumored, it will break all RS sites.|||IE7 is only in beta 3 mode, I believe the issues with IE7 and RS will be resolved but there is no fix for IE7 Beta 3.|||So I guess the answer is "deal with it" At least most companies dont use parameters in reporting , so it should not be a big issue. |||Just to clarify, we are aware of the issue with the script errors and how to fix it. In order to be fixed in a QFE, a customer will need to contact CSS and request the fix, otherwise look for the fix in a future release. If your are hindered by this issue, your best course of action is to contact Customer Support and request a fix.|||It appears it is an IE issue. I am able to work with the calendar control fine with Firefox. Can anyone verify this?