Hidden records?

Hi all,
Using vb6 ado connection I can see the record
e.g. SELECT * FROM table where id=1234
I will see the record
but
Using the same query on Enterprise Manager
the same query results to nothing.
Also
SELECT COUNT(*) AS Expr1
FROM table
Result:
vb6 query:1726 records
Ent. Manager view: 18 records
My questions is why Ent. Manager cannot see all the records
I am using SQL Server 2000 standard edition
The database itselt is nearly 21gb.
Any help or advice appreciated
Regards,
AllanAllan
Are you sure you connected to the same database?
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
My guess is that these are different server/databases with the same table.
Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||Uri,
Yes. We only have one database. I connect via ip address and database name
"Uri Dimant" wrote:
> Allan
> Are you sure you connected to the same database?
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>
>|||Also, after verifying the same servername/databasename, make sure you use the same table by
owner-qualifying the name, like
SELECT COUNT(*) FROM dbo.table
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:2D08ED0C-D379-4DE6-97E1-3A97037EA0AE@.microsoft.com...
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table. Try verifying
> connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> Hi all,
>> Using vb6 ado connection I can see the record
>> e.g. SELECT * FROM table where id=1234
>> I will see the record
>> but
>> Using the same query on Enterprise Manager
>> the same query results to nothing.
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
>> My questions is why Ent. Manager cannot see all the records
>> I am using SQL Server 2000 standard edition
>> The database itselt is nearly 21gb.
>> Any help or advice appreciated
>> Regards,
>> Allan
>|||Hi Dan
Same reply as Uri
We only have one database. I connect via ip address and database name
I wish that was the case and the problem is solved
Will the size of the database (21 gb) makes any difference to the standard
edition of SQL server?
How about data corruption?
Thank you for your interest
"Dan Guzman" wrote:
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table.
> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>|||"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
Not at all.
> How about data corruption?
Highly unlikely that a query one place would return one set of data and the
same query from another place would return different data.
So sounds like something is missing in your description (like a table with
the same name was accidentally created in the MASTER DB and some records
inserted and when you connect the 2nd way you're hitting master, not your
database and seeing the wrong table.
As other have said, to be 100% sure, do a select * from
<dbname>.<owner>.tablename.
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I think Tibor's idea that you have different tables in different schema is
likely. The Best Practice is to always schema-qualify table names. Not
only does this avoid ambiguous references, it helps improve performance too.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
> How about data corruption?
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >|||To Uri,Dan,Greg and Tibor,
Thank for all your help. It was decided to restore from last nights backup
and redo todays activities.
After restore queries from vb6 and Ent. Manager equalled
I am printing your advice for future reference
Regards,
Allan
"Allan" wrote:
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Not only does this avoid ambiguous references, it helps improve
> performance too.
Is the performance gain documented any where?
/Sjang|||On Sep 3, 10:46 am, "Henrik Davidsen" <n...@.none.dk> wrote:
> > Not only does this avoid ambiguous references, it helps improve
> > performance too.
> Is the performance gain documented any where?
> /Sjang
You can see for yourself easily:
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'CREATE PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10)) + ' AS RETURN 0'
EXEC sp_executesql @.SQL
END
GO
DECLARE @.i INT, @.j INT, @.d DATETIME, @.SQL NVARCHAR(300), @.SQL_to_run
NVARCHAR(300)
SELECT @.i = 0, @.j = 0, @.d = getdate()
WHILE @.j < 3 BEGIN
SET @.j = @.j + 1
IF @.j = 1 BEGIN
SET @.SQL = 'EXEC DummyProc'
END ELSE BEGIN
SET @.SQL = 'EXEC dbo.DummyProc'
END
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL_to_run = @.SQL + CAST(@.i AS NVARCHAR(10))
--PRINT @.SQL_to_run
IF @.j <3 BEGIN
EXEC sp_executesql @.SQL_to_run
END
END
SELECT DATEDIFF(ms, @.d, GEtdate()) as ms
SELECT @.i = 0, @.d = getdate()
END
GO
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'DROP PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10))
EXEC sp_executesql @.SQL
END
GO
-- without dbo.
ms
--
3860
(1 row(s) affected)
-- with dbo
ms
--
640
(1 row(s) affected)
-- the overhead of running the loop
ms
--
46|||On Mon, 3 Sep 2007 17:46:30 +0200, "Henrik Davidsen" <none@.none.dk>
wrote:
>> Not only does this avoid ambiguous references, it helps improve
>> performance too.
>Is the performance gain documented any where?
Don't have a link handy, but it's mentioned in various white papers,
also just saw it mentioned in Kalen's "Storage Engine 2005" book, near
the middle, I'm too lazy to look up the page!
Per Kalen, in SQL2005 apparently it helps even a little more, if you
don't specify then there is more that SQL2005 does before it decides
you meant dbo. all along.
OK it's a small effect, but on a high-transaction system it might get
you a few TPS, and also there may be something of an increase in the
use of namespaces generally with 2005, so it might be a good practice
just on that basis.
Josh|||You *could* have had an open transaction in snapshot mode on 2005, ...
but it does sound more like corruption. Did you have any crashes
recently? Did you try running DBCC CHECKDB? Was this on a production
system? It's really a table and not a view with some kind of weird
user-sensitive parameters on it? Were you using the same login in
both cases? Was it repeatable - you could log off and log back in
again in both situations and see the same conflicting results?
I know it's moot now, but nobody likes that sort of anomaly.
J.
On Mon, 3 Sep 2007 07:00:00 -0700, Allan
<Allan@.discussions.microsoft.com> wrote:
>Hi all,
>Using vb6 ado connection I can see the record
>e.g. SELECT * FROM table where id=1234
>I will see the record
>but
>Using the same query on Enterprise Manager
>the same query results to nothing.
>Also
>SELECT COUNT(*) AS Expr1
>FROM table
>Result:
>vb6 query:1726 records
>Ent. Manager view: 18 records
>My questions is why Ent. Manager cannot see all the records
>I am using SQL Server 2000 standard edition
>The database itselt is nearly 21gb.
>Any help or advice appreciated
>Regards,
>Allan|||Hi J,
"JXStern" wrote:
> You *could* have had an open transaction in snapshot mode on 2005, ...
We are using SQL Server 2000 standard edition
> but it does sound more like corruption. Did you have any crashes
> recently?
No crashes
Did you try running DBCC CHECKDB?
No.
Was this on a production system?
We are very small company. Production and test system is the same.
It's really a table and not a view with some kind of weird user-sensitive
parameters on it?
Yes it is a table. The query was taken (copied and pasted) directly from vb6
source code that hasn't been changed for 3 years
Were you using the same login in both cases?
There is only one login "sa"
Was it repeatable - you could log off and log back in again in both
situations and see the same conflicting results?
We restarted the SQL server 2000. Same result.
Restarted Windows Server 2003 standard edition. Same result
> I know it's moot now, but nobody likes that sort of anomaly.
> J.
>
> On Mon, 3 Sep 2007 07:00:00 -0700, Allan
> <Allan@.discussions.microsoft.com> wrote:
> >Hi all,
> >
> >Using vb6 ado connection I can see the record
> >e.g. SELECT * FROM table where id=1234
> >I will see the record
> >
> >but
> >Using the same query on Enterprise Manager
> >the same query results to nothing.
> >
> >Also
> >SELECT COUNT(*) AS Expr1
> >FROM table
> >
> >Result:
> >vb6 query:1726 records
> >Ent. Manager view: 18 records
> >
> >My questions is why Ent. Manager cannot see all the records
> >
> >I am using SQL Server 2000 standard edition
> >The database itselt is nearly 21gb.
> >
> >Any help or advice appreciated
> >
> >Regards,
> >Allan
>

Hidden parameters error (SP1)

I'm having problems with executing a report with a hidden parameter. For the
record, I've got SP1 installed.
I have a report with a date range that a user can modify:
From - defaults to previous business day
To - also defaults to previous business day
I also have a parameter called CurrentUserID (an integer) which is a user ID
that exists in a table called tblUser. I want this parameter to be hidden
from the user.
In Report Manager, I've gone into Properties, then Parameters, and set the
following for the CurrentUserID parameter:
Has Default - not ticked
Default Value - blank
Prompt User - ticked
Prompt String - blank
In the URL for the report, I add the following at the end:
&CurrentUserID=1083
where 1083 is a UserID I know should return some records in my test report.
However, I'm getting the following error:
This report requires a default or user-defined value for the report
parameter 'CurrentUserID'. To run or subscribe to this report, you must
provide a parameter value. (rsReportParameterValueNotSet)
I have tried the report again, this time with the CurrentUserID parameter
displayed, and entering the user ID 1083, and this time the report works
fine, so I know the stored procedure is not the problem.
Any suggestions to get this hidden parameter functionality to work would be
greatly appreciated.
Many thanks.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)just give it a (non-used) default value fe -1
"Jadranka" wrote:
> I'm having problems with executing a report with a hidden parameter. For the
> record, I've got SP1 installed.
> I have a report with a date range that a user can modify:
> From - defaults to previous business day
> To - also defaults to previous business day
> I also have a parameter called CurrentUserID (an integer) which is a user ID
> that exists in a table called tblUser. I want this parameter to be hidden
> from the user.
> In Report Manager, I've gone into Properties, then Parameters, and set the
> following for the CurrentUserID parameter:
> Has Default - not ticked
> Default Value - blank
> Prompt User - ticked
> Prompt String - blank
> In the URL for the report, I add the following at the end:
> &CurrentUserID=1083
> where 1083 is a UserID I know should return some records in my test report.
> However, I'm getting the following error:
> This report requires a default or user-defined value for the report
> parameter 'CurrentUserID'. To run or subscribe to this report, you must
> provide a parameter value. (rsReportParameterValueNotSet)
> I have tried the report again, this time with the CurrentUserID parameter
> displayed, and entering the user ID 1083, and this time the report works
> fine, so I know the stored procedure is not the problem.
> Any suggestions to get this hidden parameter functionality to work would be
> greatly appreciated.
> Many thanks.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)|||Along the same lines, I am creating an interface to an client application to
launch reports. I need a way to be able to set a parameter from within the
application AND allow the developer to configure this setting so the report
can be deployed with no Report Manager configuration necessary. If I clear
the prompt from the report designer and deploy, the parameter then becomes
'read-only' and I cannot set it from code.
The only work around that I can find is to go in to Report Manager and turn
ON the Prompt User, then clear the Prompt text and then adapt for that in
code. This works fine except that it means I have to go to each of the 100
client's ReportManagers and configure this report this way. With 50 reports
with hidden parameters at 100 clients, that is just not possible.
Any suggestions?
"Antoon" wrote:
> just give it a (non-used) default value fe -1
> "Jadranka" wrote:
> > I'm having problems with executing a report with a hidden parameter. For the
> > record, I've got SP1 installed.
> >
> > I have a report with a date range that a user can modify:
> > From - defaults to previous business day
> > To - also defaults to previous business day
> >
> > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > that exists in a table called tblUser. I want this parameter to be hidden
> > from the user.
> >
> > In Report Manager, I've gone into Properties, then Parameters, and set the
> > following for the CurrentUserID parameter:
> > Has Default - not ticked
> > Default Value - blank
> > Prompt User - ticked
> > Prompt String - blank
> >
> > In the URL for the report, I add the following at the end:
> > &CurrentUserID=1083
> > where 1083 is a UserID I know should return some records in my test report.
> >
> > However, I'm getting the following error:
> > This report requires a default or user-defined value for the report
> > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > provide a parameter value. (rsReportParameterValueNotSet)
> >
> > I have tried the report again, this time with the CurrentUserID parameter
> > displayed, and entering the user ID 1083, and this time the report works
> > fine, so I know the stored procedure is not the problem.
> >
> > Any suggestions to get this hidden parameter functionality to work would be
> > greatly appreciated.
> >
> > Many thanks.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)|||Well, it's not clean but you could have a look at what exactly changes in the
reports (xml) when you change the settings in de report manager, and then
write a small program to make that change for your reports.
I hope they are going to fix this in RS2005
"rbowlin" wrote:
> Along the same lines, I am creating an interface to an client application to
> launch reports. I need a way to be able to set a parameter from within the
> application AND allow the developer to configure this setting so the report
> can be deployed with no Report Manager configuration necessary. If I clear
> the prompt from the report designer and deploy, the parameter then becomes
> 'read-only' and I cannot set it from code.
> The only work around that I can find is to go in to Report Manager and turn
> ON the Prompt User, then clear the Prompt text and then adapt for that in
> code. This works fine except that it means I have to go to each of the 100
> client's ReportManagers and configure this report this way. With 50 reports
> with hidden parameters at 100 clients, that is just not possible.
> Any suggestions?
> "Antoon" wrote:
> > just give it a (non-used) default value fe -1
> >
> > "Jadranka" wrote:
> >
> > > I'm having problems with executing a report with a hidden parameter. For the
> > > record, I've got SP1 installed.
> > >
> > > I have a report with a date range that a user can modify:
> > > From - defaults to previous business day
> > > To - also defaults to previous business day
> > >
> > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > that exists in a table called tblUser. I want this parameter to be hidden
> > > from the user.
> > >
> > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > following for the CurrentUserID parameter:
> > > Has Default - not ticked
> > > Default Value - blank
> > > Prompt User - ticked
> > > Prompt String - blank
> > >
> > > In the URL for the report, I add the following at the end:
> > > &CurrentUserID=1083
> > > where 1083 is a UserID I know should return some records in my test report.
> > >
> > > However, I'm getting the following error:
> > > This report requires a default or user-defined value for the report
> > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > provide a parameter value. (rsReportParameterValueNotSet)
> > >
> > > I have tried the report again, this time with the CurrentUserID parameter
> > > displayed, and entering the user ID 1083, and this time the report works
> > > fine, so I know the stored procedure is not the problem.
> > >
> > > Any suggestions to get this hidden parameter functionality to work would be
> > > greatly appreciated.
> > >
> > > Many thanks.
> > > --
> > > Jadranka Krapic
> > > DBA
> > > Stargate Technologies
> > > (www.stargatetech.com.au)|||Thanks for your response.
I've tried the suggestion to set a non-used default value. The report now
loads error-free, however, it comes back with no data even though I've added
&CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
return 3 records).
I've added a text box onto the report to show what's in the CurrentUserID
parameter when I run the report with &CurrentUserID=1083 in the URL. However,
it keeps showing the -1 I've set as the default. It doesn't appear to notice
the fact that I want to override the -1 default with my own user ID.
Any ideas?
Thanks.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> Well, it's not clean but you could have a look at what exactly changes in the
> reports (xml) when you change the settings in de report manager, and then
> write a small program to make that change for your reports.
> I hope they are going to fix this in RS2005
> "rbowlin" wrote:
> > Along the same lines, I am creating an interface to an client application to
> > launch reports. I need a way to be able to set a parameter from within the
> > application AND allow the developer to configure this setting so the report
> > can be deployed with no Report Manager configuration necessary. If I clear
> > the prompt from the report designer and deploy, the parameter then becomes
> > 'read-only' and I cannot set it from code.
> >
> > The only work around that I can find is to go in to Report Manager and turn
> > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > code. This works fine except that it means I have to go to each of the 100
> > client's ReportManagers and configure this report this way. With 50 reports
> > with hidden parameters at 100 clients, that is just not possible.
> >
> > Any suggestions?
> >
> > "Antoon" wrote:
> >
> > > just give it a (non-used) default value fe -1
> > >
> > > "Jadranka" wrote:
> > >
> > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > record, I've got SP1 installed.
> > > >
> > > > I have a report with a date range that a user can modify:
> > > > From - defaults to previous business day
> > > > To - also defaults to previous business day
> > > >
> > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > from the user.
> > > >
> > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > following for the CurrentUserID parameter:
> > > > Has Default - not ticked
> > > > Default Value - blank
> > > > Prompt User - ticked
> > > > Prompt String - blank
> > > >
> > > > In the URL for the report, I add the following at the end:
> > > > &CurrentUserID=1083
> > > > where 1083 is a UserID I know should return some records in my test report.
> > > >
> > > > However, I'm getting the following error:
> > > > This report requires a default or user-defined value for the report
> > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > >
> > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > displayed, and entering the user ID 1083, and this time the report works
> > > > fine, so I know the stored procedure is not the problem.
> > > >
> > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > greatly appreciated.
> > > >
> > > > Many thanks.
> > > > --
> > > > Jadranka Krapic
> > > > DBA
> > > > Stargate Technologies
> > > > (www.stargatetech.com.au)|||So you have: a default value, you've checked the box "prompt user" and have
left the prompt value blank?
If this is the case it should work.
"Jadranka" wrote:
> Thanks for your response.
> I've tried the suggestion to set a non-used default value. The report now
> loads error-free, however, it comes back with no data even though I've added
> &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> return 3 records).
> I've added a text box onto the report to show what's in the CurrentUserID
> parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> it keeps showing the -1 I've set as the default. It doesn't appear to notice
> the fact that I want to override the -1 default with my own user ID.
> Any ideas?
> Thanks.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)
>
> "Antoon" wrote:
> > Well, it's not clean but you could have a look at what exactly changes in the
> > reports (xml) when you change the settings in de report manager, and then
> > write a small program to make that change for your reports.
> > I hope they are going to fix this in RS2005
> >
> > "rbowlin" wrote:
> >
> > > Along the same lines, I am creating an interface to an client application to
> > > launch reports. I need a way to be able to set a parameter from within the
> > > application AND allow the developer to configure this setting so the report
> > > can be deployed with no Report Manager configuration necessary. If I clear
> > > the prompt from the report designer and deploy, the parameter then becomes
> > > 'read-only' and I cannot set it from code.
> > >
> > > The only work around that I can find is to go in to Report Manager and turn
> > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > code. This works fine except that it means I have to go to each of the 100
> > > client's ReportManagers and configure this report this way. With 50 reports
> > > with hidden parameters at 100 clients, that is just not possible.
> > >
> > > Any suggestions?
> > >
> > > "Antoon" wrote:
> > >
> > > > just give it a (non-used) default value fe -1
> > > >
> > > > "Jadranka" wrote:
> > > >
> > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > record, I've got SP1 installed.
> > > > >
> > > > > I have a report with a date range that a user can modify:
> > > > > From - defaults to previous business day
> > > > > To - also defaults to previous business day
> > > > >
> > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > from the user.
> > > > >
> > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > following for the CurrentUserID parameter:
> > > > > Has Default - not ticked
> > > > > Default Value - blank
> > > > > Prompt User - ticked
> > > > > Prompt String - blank
> > > > >
> > > > > In the URL for the report, I add the following at the end:
> > > > > &CurrentUserID=1083
> > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > >
> > > > > However, I'm getting the following error:
> > > > > This report requires a default or user-defined value for the report
> > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > >
> > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > fine, so I know the stored procedure is not the problem.
> > > > >
> > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > greatly appreciated.
> > > > >
> > > > > Many thanks.
> > > > > --
> > > > > Jadranka Krapic
> > > > > DBA
> > > > > Stargate Technologies
> > > > > (www.stargatetech.com.au)|||Yep, that's correct.
If I put my ID of 1083 in the default just to see if it works, my 3 records
come back fine. But when I put -1 as the default, and add the parameter to
the end of my URL (&CurrentUserID=1083), it doesn't.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> So you have: a default value, you've checked the box "prompt user" and have
> left the prompt value blank?
> If this is the case it should work.
> "Jadranka" wrote:
> > Thanks for your response.
> >
> > I've tried the suggestion to set a non-used default value. The report now
> > loads error-free, however, it comes back with no data even though I've added
> > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > return 3 records).
> >
> > I've added a text box onto the report to show what's in the CurrentUserID
> > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > the fact that I want to override the -1 default with my own user ID.
> >
> > Any ideas?
> >
> > Thanks.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)
> >
> >
> > "Antoon" wrote:
> >
> > > Well, it's not clean but you could have a look at what exactly changes in the
> > > reports (xml) when you change the settings in de report manager, and then
> > > write a small program to make that change for your reports.
> > > I hope they are going to fix this in RS2005
> > >
> > > "rbowlin" wrote:
> > >
> > > > Along the same lines, I am creating an interface to an client application to
> > > > launch reports. I need a way to be able to set a parameter from within the
> > > > application AND allow the developer to configure this setting so the report
> > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > 'read-only' and I cannot set it from code.
> > > >
> > > > The only work around that I can find is to go in to Report Manager and turn
> > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > code. This works fine except that it means I have to go to each of the 100
> > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > with hidden parameters at 100 clients, that is just not possible.
> > > >
> > > > Any suggestions?
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > just give it a (non-used) default value fe -1
> > > > >
> > > > > "Jadranka" wrote:
> > > > >
> > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > record, I've got SP1 installed.
> > > > > >
> > > > > > I have a report with a date range that a user can modify:
> > > > > > From - defaults to previous business day
> > > > > > To - also defaults to previous business day
> > > > > >
> > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > from the user.
> > > > > >
> > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > following for the CurrentUserID parameter:
> > > > > > Has Default - not ticked
> > > > > > Default Value - blank
> > > > > > Prompt User - ticked
> > > > > > Prompt String - blank
> > > > > >
> > > > > > In the URL for the report, I add the following at the end:
> > > > > > &CurrentUserID=1083
> > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > >
> > > > > > However, I'm getting the following error:
> > > > > > This report requires a default or user-defined value for the report
> > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > >
> > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > fine, so I know the stored procedure is not the problem.
> > > > > >
> > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > greatly appreciated.
> > > > > >
> > > > > > Many thanks.
> > > > > > --
> > > > > > Jadranka Krapic
> > > > > > DBA
> > > > > > Stargate Technologies
> > > > > > (www.stargatetech.com.au)|||can you give the complete url
"Jadranka" wrote:
> Yep, that's correct.
> If I put my ID of 1083 in the default just to see if it works, my 3 records
> come back fine. But when I put -1 as the default, and add the parameter to
> the end of my URL (&CurrentUserID=1083), it doesn't.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)
>
> "Antoon" wrote:
> > So you have: a default value, you've checked the box "prompt user" and have
> > left the prompt value blank?
> >
> > If this is the case it should work.
> >
> > "Jadranka" wrote:
> >
> > > Thanks for your response.
> > >
> > > I've tried the suggestion to set a non-used default value. The report now
> > > loads error-free, however, it comes back with no data even though I've added
> > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > return 3 records).
> > >
> > > I've added a text box onto the report to show what's in the CurrentUserID
> > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > the fact that I want to override the -1 default with my own user ID.
> > >
> > > Any ideas?
> > >
> > > Thanks.
> > > --
> > > Jadranka Krapic
> > > DBA
> > > Stargate Technologies
> > > (www.stargatetech.com.au)
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > reports (xml) when you change the settings in de report manager, and then
> > > > write a small program to make that change for your reports.
> > > > I hope they are going to fix this in RS2005
> > > >
> > > > "rbowlin" wrote:
> > > >
> > > > > Along the same lines, I am creating an interface to an client application to
> > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > application AND allow the developer to configure this setting so the report
> > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > 'read-only' and I cannot set it from code.
> > > > >
> > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > >
> > > > > Any suggestions?
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > just give it a (non-used) default value fe -1
> > > > > >
> > > > > > "Jadranka" wrote:
> > > > > >
> > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > record, I've got SP1 installed.
> > > > > > >
> > > > > > > I have a report with a date range that a user can modify:
> > > > > > > From - defaults to previous business day
> > > > > > > To - also defaults to previous business day
> > > > > > >
> > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > from the user.
> > > > > > >
> > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > following for the CurrentUserID parameter:
> > > > > > > Has Default - not ticked
> > > > > > > Default Value - blank
> > > > > > > Prompt User - ticked
> > > > > > > Prompt String - blank
> > > > > > >
> > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > &CurrentUserID=1083
> > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > >
> > > > > > > However, I'm getting the following error:
> > > > > > > This report requires a default or user-defined value for the report
> > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > >
> > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > >
> > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > greatly appreciated.
> > > > > > >
> > > > > > > Many thanks.
> > > > > > > --
> > > > > > > Jadranka Krapic
> > > > > > > DBA
> > > > > > > Stargate Technologies
> > > > > > > (www.stargatetech.com.au)|||Sure...URL when viewing the report via Report Manager is as follows
http://sqldev/Reports/Pages/Report.aspx?ItemPath=%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
'&CurrentUserID=1083' is the user ID that, when used with the below
defaults, I know should return 3 records.
I have a number of parameters for this report (along with the troublesome
CurrentUserID) - all are displayed to the user and are changeable by them
except CurrentUserID:
Parameter Name Data Type Has Default Default Value
From DateTime Yes 1/03/2005 12:00:00 AM
To DateTime Yes 31/03/2005 12:00:00 AM
Channel Integer Yes 0
OrigState String Yes (ALL)
OriginatorID Integer Yes 0
SAM_FBM String Yes (ALL)
ReportAs String Yes Real-time
CurrentUserID Integer Yes -1
For CurrentUserID, Prompt User is ticked and Prompt String is blank.
Thanks.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> can you give the complete url
> "Jadranka" wrote:
> > Yep, that's correct.
> >
> > If I put my ID of 1083 in the default just to see if it works, my 3 records
> > come back fine. But when I put -1 as the default, and add the parameter to
> > the end of my URL (&CurrentUserID=1083), it doesn't.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)
> >
> >
> > "Antoon" wrote:
> >
> > > So you have: a default value, you've checked the box "prompt user" and have
> > > left the prompt value blank?
> > >
> > > If this is the case it should work.
> > >
> > > "Jadranka" wrote:
> > >
> > > > Thanks for your response.
> > > >
> > > > I've tried the suggestion to set a non-used default value. The report now
> > > > loads error-free, however, it comes back with no data even though I've added
> > > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > > return 3 records).
> > > >
> > > > I've added a text box onto the report to show what's in the CurrentUserID
> > > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > > the fact that I want to override the -1 default with my own user ID.
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks.
> > > > --
> > > > Jadranka Krapic
> > > > DBA
> > > > Stargate Technologies
> > > > (www.stargatetech.com.au)
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > > reports (xml) when you change the settings in de report manager, and then
> > > > > write a small program to make that change for your reports.
> > > > > I hope they are going to fix this in RS2005
> > > > >
> > > > > "rbowlin" wrote:
> > > > >
> > > > > > Along the same lines, I am creating an interface to an client application to
> > > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > > application AND allow the developer to configure this setting so the report
> > > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > > 'read-only' and I cannot set it from code.
> > > > > >
> > > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > > >
> > > > > > Any suggestions?
> > > > > >
> > > > > > "Antoon" wrote:
> > > > > >
> > > > > > > just give it a (non-used) default value fe -1
> > > > > > >
> > > > > > > "Jadranka" wrote:
> > > > > > >
> > > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > > record, I've got SP1 installed.
> > > > > > > >
> > > > > > > > I have a report with a date range that a user can modify:
> > > > > > > > From - defaults to previous business day
> > > > > > > > To - also defaults to previous business day
> > > > > > > >
> > > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > > from the user.
> > > > > > > >
> > > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > > following for the CurrentUserID parameter:
> > > > > > > > Has Default - not ticked
> > > > > > > > Default Value - blank
> > > > > > > > Prompt User - ticked
> > > > > > > > Prompt String - blank
> > > > > > > >
> > > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > > &CurrentUserID=1083
> > > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > > >
> > > > > > > > However, I'm getting the following error:
> > > > > > > > This report requires a default or user-defined value for the report
> > > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > > >
> > > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > > >
> > > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > > greatly appreciated.
> > > > > > > >
> > > > > > > > Many thanks.
> > > > > > > > --
> > > > > > > > Jadranka Krapic
> > > > > > > > DBA
> > > > > > > > Stargate Technologies
> > > > > > > > (www.stargatetech.com.au)|||Perhaps you could try:
http://sqldev/Reports?%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
"Jadranka" wrote:
> Sure...URL when viewing the report via Report Manager is as follows:
> http://sqldev/Reports/Pages/Report.aspx?ItemPath=%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
> '&CurrentUserID=1083' is the user ID that, when used with the below
> defaults, I know should return 3 records.
> I have a number of parameters for this report (along with the troublesome
> CurrentUserID) - all are displayed to the user and are changeable by them
> except CurrentUserID:
> Parameter Name Data Type Has Default Default Value
> From DateTime Yes 1/03/2005 12:00:00 AM
> To DateTime Yes 31/03/2005 12:00:00 AM
> Channel Integer Yes 0
> OrigState String Yes (ALL)
> OriginatorID Integer Yes 0
> SAM_FBM String Yes (ALL)
> ReportAs String Yes Real-time
> CurrentUserID Integer Yes -1
> For CurrentUserID, Prompt User is ticked and Prompt String is blank.
> Thanks.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)
>
> "Antoon" wrote:
> > can you give the complete url
> >
> > "Jadranka" wrote:
> >
> > > Yep, that's correct.
> > >
> > > If I put my ID of 1083 in the default just to see if it works, my 3 records
> > > come back fine. But when I put -1 as the default, and add the parameter to
> > > the end of my URL (&CurrentUserID=1083), it doesn't.
> > > --
> > > Jadranka Krapic
> > > DBA
> > > Stargate Technologies
> > > (www.stargatetech.com.au)
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > So you have: a default value, you've checked the box "prompt user" and have
> > > > left the prompt value blank?
> > > >
> > > > If this is the case it should work.
> > > >
> > > > "Jadranka" wrote:
> > > >
> > > > > Thanks for your response.
> > > > >
> > > > > I've tried the suggestion to set a non-used default value. The report now
> > > > > loads error-free, however, it comes back with no data even though I've added
> > > > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > > > return 3 records).
> > > > >
> > > > > I've added a text box onto the report to show what's in the CurrentUserID
> > > > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > > > the fact that I want to override the -1 default with my own user ID.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Jadranka Krapic
> > > > > DBA
> > > > > Stargate Technologies
> > > > > (www.stargatetech.com.au)
> > > > >
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > > > reports (xml) when you change the settings in de report manager, and then
> > > > > > write a small program to make that change for your reports.
> > > > > > I hope they are going to fix this in RS2005
> > > > > >
> > > > > > "rbowlin" wrote:
> > > > > >
> > > > > > > Along the same lines, I am creating an interface to an client application to
> > > > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > > > application AND allow the developer to configure this setting so the report
> > > > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > > > 'read-only' and I cannot set it from code.
> > > > > > >
> > > > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > > > >
> > > > > > > Any suggestions?
> > > > > > >
> > > > > > > "Antoon" wrote:
> > > > > > >
> > > > > > > > just give it a (non-used) default value fe -1
> > > > > > > >
> > > > > > > > "Jadranka" wrote:
> > > > > > > >
> > > > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > > > record, I've got SP1 installed.
> > > > > > > > >
> > > > > > > > > I have a report with a date range that a user can modify:
> > > > > > > > > From - defaults to previous business day
> > > > > > > > > To - also defaults to previous business day
> > > > > > > > >
> > > > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > > > from the user.
> > > > > > > > >
> > > > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > > > following for the CurrentUserID parameter:
> > > > > > > > > Has Default - not ticked
> > > > > > > > > Default Value - blank
> > > > > > > > > Prompt User - ticked
> > > > > > > > > Prompt String - blank
> > > > > > > > >
> > > > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > > > &CurrentUserID=1083
> > > > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > > > >
> > > > > > > > > However, I'm getting the following error:
> > > > > > > > > This report requires a default or user-defined value for the report
> > > > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > > > >
> > > > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > > > >
> > > > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > > > greatly appreciated.
> > > > > > > > >
> > > > > > > > > Many thanks.
> > > > > > > > > --
> > > > > > > > > Jadranka Krapic
> > > > > > > > > DBA
> > > > > > > > > Stargate Technologies
> > > > > > > > > (www.stargatetech.com.au)|||Hi Antoon,
I tried your suggested URL, but that always defaulted back to the Report
Manager Home page (http://sqldev/Reports/Pages/Folder.aspx).
However, I then tried going through Report Server
(http://sqldev/Reportserver?%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083),
and hey presto - IT WORKED! - I get the 3 records I am expecting. The
CurrentUserID parameter is hidden, and my test text box I placed on the
report to check what was in CurrentUserID is now correctly showing my user ID
of 1083. I think that now I can work with this.
Many thanks for your assistance Antoon. I (and I'm sure the client of this
project) really appreciate you taking the time to help out.
Thank you again.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> Perhaps you could try:
> http://sqldev/Reports?%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
> "Jadranka" wrote:
> > Sure...URL when viewing the report via Report Manager is as follows:
> > http://sqldev/Reports/Pages/Report.aspx?ItemPath=%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
> >
> > '&CurrentUserID=1083' is the user ID that, when used with the below
> > defaults, I know should return 3 records.
> >
> > I have a number of parameters for this report (along with the troublesome
> > CurrentUserID) - all are displayed to the user and are changeable by them
> > except CurrentUserID:
> >
> > Parameter Name Data Type Has Default Default Value
> > From DateTime Yes 1/03/2005 12:00:00 AM
> > To DateTime Yes 31/03/2005 12:00:00 AM
> > Channel Integer Yes 0
> > OrigState String Yes (ALL)
> > OriginatorID Integer Yes 0
> > SAM_FBM String Yes (ALL)
> > ReportAs String Yes Real-time
> > CurrentUserID Integer Yes -1
> >
> > For CurrentUserID, Prompt User is ticked and Prompt String is blank.
> >
> > Thanks.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)
> >
> >
> > "Antoon" wrote:
> >
> > > can you give the complete url
> > >
> > > "Jadranka" wrote:
> > >
> > > > Yep, that's correct.
> > > >
> > > > If I put my ID of 1083 in the default just to see if it works, my 3 records
> > > > come back fine. But when I put -1 as the default, and add the parameter to
> > > > the end of my URL (&CurrentUserID=1083), it doesn't.
> > > > --
> > > > Jadranka Krapic
> > > > DBA
> > > > Stargate Technologies
> > > > (www.stargatetech.com.au)
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > So you have: a default value, you've checked the box "prompt user" and have
> > > > > left the prompt value blank?
> > > > >
> > > > > If this is the case it should work.
> > > > >
> > > > > "Jadranka" wrote:
> > > > >
> > > > > > Thanks for your response.
> > > > > >
> > > > > > I've tried the suggestion to set a non-used default value. The report now
> > > > > > loads error-free, however, it comes back with no data even though I've added
> > > > > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > > > > return 3 records).
> > > > > >
> > > > > > I've added a text box onto the report to show what's in the CurrentUserID
> > > > > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > > > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > > > > the fact that I want to override the -1 default with my own user ID.
> > > > > >
> > > > > > Any ideas?
> > > > > >
> > > > > > Thanks.
> > > > > > --
> > > > > > Jadranka Krapic
> > > > > > DBA
> > > > > > Stargate Technologies
> > > > > > (www.stargatetech.com.au)
> > > > > >
> > > > > >
> > > > > > "Antoon" wrote:
> > > > > >
> > > > > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > > > > reports (xml) when you change the settings in de report manager, and then
> > > > > > > write a small program to make that change for your reports.
> > > > > > > I hope they are going to fix this in RS2005
> > > > > > >
> > > > > > > "rbowlin" wrote:
> > > > > > >
> > > > > > > > Along the same lines, I am creating an interface to an client application to
> > > > > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > > > > application AND allow the developer to configure this setting so the report
> > > > > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > > > > 'read-only' and I cannot set it from code.
> > > > > > > >
> > > > > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > > > > >
> > > > > > > > Any suggestions?
> > > > > > > >
> > > > > > > > "Antoon" wrote:
> > > > > > > >
> > > > > > > > > just give it a (non-used) default value fe -1
> > > > > > > > >
> > > > > > > > > "Jadranka" wrote:
> > > > > > > > >
> > > > > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > > > > record, I've got SP1 installed.
> > > > > > > > > >
> > > > > > > > > > I have a report with a date range that a user can modify:
> > > > > > > > > > From - defaults to previous business day
> > > > > > > > > > To - also defaults to previous business day
> > > > > > > > > >
> > > > > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > > > > from the user.
> > > > > > > > > >
> > > > > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > > > > following for the CurrentUserID parameter:
> > > > > > > > > > Has Default - not ticked
> > > > > > > > > > Default Value - blank
> > > > > > > > > > Prompt User - ticked
> > > > > > > > > > Prompt String - blank
> > > > > > > > > >
> > > > > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > > > > &CurrentUserID=1083
> > > > > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > > > > >
> > > > > > > > > > However, I'm getting the following error:
> > > > > > > > > > This report requires a default or user-defined value for the report
> > > > > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > > > > >
> > > > > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > > > > >
> > > > > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > > > > greatly appreciated.
> > > > > > > > > >
> > > > > > > > > > Many thanks.
> > > > > > > > > > --
> > > > > > > > > > Jadranka Krapic
> > > > > > > > > > DBA
> > > > > > > > > > Stargate Technologies
> > > > > > > > > > (www.stargatetech.com.au)

Help: Prob handling SQL error in stored proc

Hi,

I a stored procedure that inserts a record into a table as
below.

The insert works OK, but if the insert violates a unique
indewx constraint on one of the columns, the proc terminates
immediately, and does NOT execute the 'if @.@.ERROR <> 0'
statement.

Am I doing something wrong, or do I need to set an attribute
somewhere?

tia,
Bill

begin tran

insert into Users
(UserName, UserPWD, Lname, Fname, UserDesc)
values (@.userName, @.userPWD, @.lname, @.fname, @.userDesc)

if @.@.ERROR <> 0
begin
rollback tran
set @.returnCode = -2
set @.errMsg = 'SQL error '
+ convert(varchar(6), @.@.ERROR)
+ ' occurred adding user '
+ @.userName
end[posted and mailed, please reply in news]

Bill S. (bill_sheets@.hotmail.com) writes:
> I a stored procedure that inserts a record into a table as
> below.
> The insert works OK, but if the insert violates a unique
> indewx constraint on one of the columns, the proc terminates
> immediately, and does NOT execute the 'if @.@.ERROR <> 0'
> statement.
> Am I doing something wrong, or do I need to set an attribute
> somewhere?
>
> begin tran
> insert into Users
> (UserName, UserPWD, Lname, Fname, UserDesc)
> values (@.userName, @.userPWD, @.lname, @.fname, @.userDesc)
> if @.@.ERROR <> 0
> begin
> rollback tran
> set @.returnCode = -2
> set @.errMsg = 'SQL error '
> + convert(varchar(6), @.@.ERROR)
> + ' occurred adding user '
> + @.userName
> end

First, @.@.error is set after each statement, so @.errMsg will never read
anything but "SQL Error 0 ...". Always save @.@.error in a local variable
before you do anything else with it.

So over to your question. Error handling in SQL Server is a messy topic,
and there are errors you cannot trap like this, because SQL Server
aborts the batch immediately. However, constraint violation as you
mention is not among those - unless the setting SET XACT_ABORT is ON.

So, assuming you are not using XACT_ABORT ON, the error handler should
be executed. But how do you know that it is not? What are @.returnCode
and @.errMsg? Local variables? Output parameters? If they are output
parameters, and run the procedure from Query Analyzer:

DECLARE @.ret int, @.errMsg varchar(200)
EXEC your_sp @.userName, ..., @.ret OUTPUT, @.errMsg OUTPUT
SELECT @.ret, @.errMsg

My guess is that you are running the procedure from some client library
which traps the error, before you get to read the output parameters.

As I mentioned, error handling is really a messy topic, but I have
an article on error handling of my web site that may be of interest,
http://www.sommarskog.se/error-handling-II.html. I don't know exactly
on what level you are on; if you are fairly unexperienced with SQL
Server, you may feel overwhelmed, but you could browse it now, and
save it for later reading.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

HELP: Delayed Inserts in SQL Server!

Hi all,
I have a small table with the following columns:
Refno (int), CampaignID(int), Value (int)
Sometimes a record takes >30 - 40 seconds to write. Is there a way to
ensure that a record is written in a timely manner?
Other info: I do have a stored procedure which loops the table waiting
for a response:
WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
AND @.LOOPCOUNTER < 30
BEGIN
SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
/* Pause Loop Delay in hh:mm:ss:ms format */
WAITFOR DELAY '00:00:00.200'
END
Also, once a record is read, it is deleted from the table. Could the
deletes be locking the table?
Thank you for ANY help.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Your deletes and your looping SELECT can both be blocking the inserts.
Can you post DDL for the table, including any indexes and constraints?
Also, how many rows are in the table?
Your loop might be more efficient expressed as:
WHILE NOT EXISTS (SELECT * FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/|||"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
>
What version of SQL Server are you running? I am assuming 2000.
What indexes are on the table?
Why do you have a WHILE loop running? That is most likely the cause of your
problem. It is probably holding locks on the table while an INSERT is
attempting to grab locks.
Is the looping doing something for you? Some type of notification? Could
you use a trigger on INSERT to accomplish the same task without using the
loop?
Rick Sawtell
MCT, MCSD, MCDBA|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> Can you post DDL for the table, including any indexes and constraints?
> Also, how many rows are in the table?
Approximately 20 - 30 rows at a time. After a record is read, it is deleted
from the table.
There are no indexes and constraints on the table. I am running SQL Server
2000.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
The While loop is used for notification - An ASP script executes a stored
procedure which loops the table waiting for the insert. Once the insert is
completed, the loop will complete and the value is returned to the ASP
page.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:uoA11mcxEHA.2624@.TK2MSFTNGP11.phx.gbl:
> What version of SQL Server are you running? I am assuming 2000.
Yes, SQL Server 2000.
> What indexes are on the table?
There are no indexes - I dropped all indexes in hopes that the INSERT
query will run faster.
> Why do you have a WHILE loop running? That is most likely the cause
> of your problem. It is probably holding locks on the table while an
> INSERT is attempting to grab locks.
The While loop is used for notification - An ASP script executes a
stored procedure which loops the table waiting for the insert to
complete. Once the insert is completed, the loop will find the record
and the value is returned to the ASP page. If no value is found within X
seconds the SP exits and the default value returned.
> Is the looping doing something for you? Some type of notification?
> Could you use a trigger on INSERT to accomplish the same task without
> using the loop?
Do you know of any other way to pause the execution of a select
statement and wait for a notification? A trigger *would* be nice... but
the problem is that the value must be returned to an external process (a
VoiceXML server which can only call webpages):
Here is the DDL for the table in case it might help you.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||You could start by defining a primary key... Refno, perhaps?
And maybe ease up the SELECT loop a bit, and/or use a (NOLOCK) hint to
ensure that it doesn't hold locks on the table...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B95DA8324nntprogerscom@.140.99.99.130...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> > Can you post DDL for the table, including any indexes and constraints?
> > Also, how many rows are in the table?
> Approximately 20 - 30 rows at a time. After a record is read, it is
deleted
> from the table.
> There are no indexes and constraints on the table. I am running SQL Server
> 2000.
> CREATE TABLE [dbo].[DialAnswerLookup] (
> [campID] [int] NOT NULL ,
> [refno] [int] NOT NULL ,
> [answeredby] [int] NULL
> ) ON [PRIMARY]
> GO
>
> The While loop is used for notification - An ASP script executes a stored
> procedure which loops the table waiting for the insert. Once the insert is
> completed, the loop will complete and the value is returned to the ASP
> page.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/