Invoke 'Refresh fields' programmatically

Hi all
I've implemented our custom data processing extension for Reporting Services
and the 'Refresh Fields' button available in the Generic Query Designer is
very useful to us to make sure the fields in the RDL files are updated.
Unfortunately, I can't seem to find a way to programmatically invoke this
'Refresh Fields' command. I want to write my own utility, but I'm not an
expert in .NET nor XML. Any pointers or guidance on how to achieve my goal
is greatly appreciated!
Thanks!!Hi,
I'll see if I can find the answer. I'll update you once I have more
information.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you and your
"William Wang[MSFT]" wrote:
> Hi,
> I'll see if I can find the answer. I'll update you once I have more
> information.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi,
You may want to implement the refresh logic externally by calling
IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this thread
for more information:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
ae765089b645dc3a
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the info. It's helpful to know what exactly happens for 'Refresh
Fields' behind the scene.
Unfortunately my main goal is to update the given RDL file(s). Everytime I
clicked the 'Refresh Fields' button the corresponding RDL file gets updated,
which is what I'm looking for.
In a Reporting Project, I want to be able to programmatically refresh all
its RDL files. Maybe I should ask how to get access to an IDbCommand object
for each report?
Your help is appreciated!! Thanks!
Jenny
"William Wang[MSFT]" wrote:
> Hi,
> You may want to implement the refresh logic externally by calling
> IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this thread
> for more information:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
> se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
> s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
> ae765089b645dc3a
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||There's not a direct way so far.
<yinjennytam@.newsgroup.nospam> wrote in message
news:28AD35DA-113D-43C3-B95A-50C910D01CD8@.microsoft.com...
> Thanks for the info. It's helpful to know what exactly happens for
> 'Refresh
> Fields' behind the scene.
> Unfortunately my main goal is to update the given RDL file(s). Everytime
> I
> clicked the 'Refresh Fields' button the corresponding RDL file gets
> updated,
> which is what I'm looking for.
> In a Reporting Project, I want to be able to programmatically refresh all
> its RDL files. Maybe I should ask how to get access to an IDbCommand
> object
> for each report?
> Your help is appreciated!! Thanks!
> Jenny
>
> "William Wang[MSFT]" wrote:
>> Hi,
>> You may want to implement the refresh logic externally by calling
>> IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this
>> thread
>> for more information:
>> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
>> se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
>> s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
>> ae765089b645dc3a
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>

InvalidReportParameterException using Data Processing Extensions

We have a report that runs via the data processing extensions. Running this
report from the reporting service interface or via the web is fine.
However, we have a requirement for running this report via some very
speciifc schedules. To achieve this we are programatically rendering this
report. We supply three paramters for this to run. The report runs a stored
procedure to obtain a list of valid periodIds. We pass it a valid period id
(can be found in the list) however, we get the error message below.
Any pointers greatly appreciated.
Steve
*** EXCEPTION: SoapException
*** MSG: System.Web.Services.Protocols.SoapException: Default value or value
provided for the report parameter 'periodId' is not a valid value. -->
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Default value
or value provided for the report parameter 'periodId' is not a valid value.
-->
Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException:
Default value or value provided for the report parameter 'periodId' is not a
valid value.
at
Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection.ThrowIfNotValid()
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack traceHi Steve,
I am having a similar problem and I wonder if you figured out what the
problem was...
Thanks,
Ana|||Hi Ana,
It turned out that the problem was one of case sensitivity.
The IDs we were dealing with were all GUIDs. Some of the lists were
uppercase, some were lowercase. As the comparison does not happen in our case
insensitive SQL Server environment, this caused an issue.
Hopefully, any problem you have is just as simple.
Good luck
Steve
"awatanabe@.herald.com" wrote:
> Hi Steve,
> I am having a similar problem and I wonder if you figured out what the
> problem was...
> Thanks,
> Ana
>

Invalid column name 'ChunkFlags'

I get the following error whenever I try to run a report.
--
An unexpected error occurred in Report Processing. (rsInternalError)
Invalid column name 'ChunkFlags'
--
A search turned up nothing. This is after installing SP2. Any ideas?
I don't have any columns named 'ChunkFlags' in my databases, so I guess
it must be in the reporting services database?
--
Scott Stonehouse
http://www.ifilter.orgI have never seen this issue before. I don't believe ChunkFlags was a
column added during SP2 but I will have to check. If it is then it seems
that something went wrong during the upgrade.
Is there any other information that might be relevant? Was this part of a
web farm? Did you use RSConfig to point to different database? Any DB
restores that might have happened?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Stonehouse" <scott@.ifilter.org> wrote in message
news:OOOXPA$SFHA.3140@.TK2MSFTNGP14.phx.gbl...
>I get the following error whenever I try to run a report.
> --
> An unexpected error occurred in Report Processing. (rsInternalError)
> Invalid column name 'ChunkFlags'
> --
> A search turned up nothing. This is after installing SP2. Any ideas? I
> don't have any columns named 'ChunkFlags' in my databases, so I guess it
> must be in the reporting services database?
> --
> Scott Stonehouse
> http://www.ifilter.org
>|||Daniel,
Absolutely, I shouldn't have attributed the error to SP2, although I did
see it first immediately after installing SP2.
This is also after moving to a new (hardware) server. I installed RS,
then restored the Reporting Services database. I think I may have just
opened the report manager website without actually testing a report -
not sure. Then, I discovered SP2 was available, so I installed it.
Then I tried running a report and got the error.
It isn't in a web farm, it's standalone. I didn't use RSConfig. But I
did do the restore.
--
Scott Stonehouse
http://www.ifilter.org
Daniel Reib (MSFT) wrote:
> I have never seen this issue before. I don't believe ChunkFlags was a
> column added during SP2 but I will have to check. If it is then it seems
> that something went wrong during the upgrade.
> Is there any other information that might be relevant? Was this part of a
> web farm? Did you use RSConfig to point to different database? Any DB
> restores that might have happened?
>|||Ok, so the ChunkFlags column was added during SP1 so it seems you have a RTM
database that you restored. Your best option would be to uninstall RS.
Install a new RS (pointing to a new DB). After the install point RS to your
old DB.
Make sure everything is working.
Then run the SP2 setup.
Hopefully this will work for you.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Stonehouse" <scott@.ifilter.org> wrote in message
news:eI1OCdLTFHA.1896@.TK2MSFTNGP14.phx.gbl...
> Daniel,
> Absolutely, I shouldn't have attributed the error to SP2, although I did
> see it first immediately after installing SP2.
> This is also after moving to a new (hardware) server. I installed RS,
> then restored the Reporting Services database. I think I may have just
> opened the report manager website without actually testing a report - not
> sure. Then, I discovered SP2 was available, so I installed it. Then I
> tried running a report and got the error.
> It isn't in a web farm, it's standalone. I didn't use RSConfig. But I
> did do the restore.
> --
> Scott Stonehouse
> http://www.ifilter.org
> Daniel Reib (MSFT) wrote:
>> I have never seen this issue before. I don't believe ChunkFlags was a
>> column added during SP2 but I will have to check. If it is then it seems
>> that something went wrong during the upgrade.
>> Is there any other information that might be relevant? Was this part of
>> a web farm? Did you use RSConfig to point to different database? Any DB
>> restores that might have happened?

Invalid column name

I am writing sql script where I have to get colum values from table and
do some processing and then delete those column. This is a new version
of the application and we don't need those columns anymore.
But the problem is that I need to be able to run this script more then
once without any errors. So, second time when I run it, it gives an
error that Invalid column name.
Before I get values from those columns, I check if they exists or not
and then get the value, but still it gives error, so I put everything
as string and use EXEC to execute that string - but still.
I don't know what to do.
I am copying my code here.
DECLARE @.value VARCHAR(8000)
SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
(''DINING_ROOM_MOD_SQFT'') ' +
' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
N''IsUserTable'') = 1))) ' +
' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
' FROM RESTAURANT ' +
' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
exec (@.value)
The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
Anybody has any idea?
Thanks
Adnan Masood
www.newbalanceindy.comThis is just an idea, so you'll have to do the coding yourself (let me
know if that's a problem), but perhaps it would be better to not try to
do so much control flow in the dynamic sql. instead you could use the
stored procedures sp_tables and sp_columns, a couple nested of cursors
looping through these basically gives you a map of your database (not
very efficient, but dynamic without using exec). Once you've got this
you could build up a much more lightweight part of the dynamic sql,
making it far easier to debug.
As for why you're getting that error - are you regenerating the dynamic
sql on the second run? perhaps using sp_executesql will be get around
it as it will regenerate the query plan.
Cheers
Will|||Print the contents of the @.value variable and see what is wrong. If you don'
t find it, post it here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145289437.588507.216360@.i39g2000cwa.googlegroups.com...
>I am writing sql script where I have to get colum values from table and
> do some processing and then delete those column. This is a new version
> of the application and we don't need those columns anymore.
> But the problem is that I need to be able to run this script more then
> once without any errors. So, second time when I run it, it gives an
> error that Invalid column name.
> Before I get values from those columns, I check if they exists or not
> and then get the value, but still it gives error, so I put everything
> as string and use EXEC to execute that string - but still.
> I don't know what to do.
> I am copying my code here.
> DECLARE @.value VARCHAR(8000)
> SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
> ' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
> exec (@.value)
> The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
> Anybody has any idea?
> Thanks
> Adnan Masood
> www.newbalanceindy.com
>|||Here is my code:
DECLARE @.value VARCHAR(8000)
SELECT @.value =
'DECLARE @.Dining_Mod smallint ' +
' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
(''DINING_ROOM_MOD_SQFT'') ' +
' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
N''IsUserTable'') = 1))) ' +
' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
' FROM RESTAURANT ' +
' WHERE RESTAURANT_ID = 1 '
print @.value
EXEC (@.value)
Here is what I get in the print
DECLARE @.Dining_Mod smallint
IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
('DINING_ROOM_MOD_SQFT')
AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[RESTAURANT]') AND OBJECTPROPERTY(id, N'IsUserTable')
= 1)))
SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT
FROM RESTAURANT
WHERE RESTAURANT_ID = 1
...and here is the error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DINING_ROOM_MOD_SQFT'.
Because this column has been delete in the first run of the script. I
need to be able to run this as many times as I want.
Thanks
Any help will be appreciated.
Adnan Masood
http://www.newbalanceindy.com|||The problem is that when the parsing of the statement is performed, the prio
r IF statement isn't in
effect yet. So, the error that the column isn't there is returned at the par
sing stage, not
execution. Seems you have to break down this into two batches.
A more serious question would be why you need to do this check. A voilile sc
hema often indicates
problems with the data model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145295533.954382.286730@.t31g2000cwb.googlegroups.com...
> Here is my code:
> DECLARE @.value VARCHAR(8000)
> SELECT @.value =
> 'DECLARE @.Dining_Mod smallint ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = 1 '
>
> print @.value
> EXEC (@.value)
> Here is what I get in the print
> DECLARE @.Dining_Mod smallint
> IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> ('DINING_ROOM_MOD_SQFT')
> AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N'[dbo].[RESTAURANT]') AND OBJECTPROPERTY(id, N'IsUserTable')
> = 1)))
> SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT
> FROM RESTAURANT
> WHERE RESTAURANT_ID = 1
> ...and here is the error:
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'DINING_ROOM_MOD_SQFT'.
>
> Because this column has been delete in the first run of the script. I
> need to be able to run this as many times as I want.
> Thanks
> Any help will be appreciated.
> Adnan Masood
> http://www.newbalanceindy.com
>|||IF you need to drop a column, why not remove it from this script entirely
and handle the column in a different script that will only be run once? You
really shouldnt have production code running on a regular basis for a task
that will only be done once. Run once what needs to be run once, then never
reference the code again.
I can't think of a reason to write code in a reusable procedure for a field
that is not going to exist. If you explain why you are doing this and what
this field is for you will probably get some good advice on alternative
approaches.
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145289437.588507.216360@.i39g2000cwa.googlegroups.com...
> I am writing sql script where I have to get colum values from table and
> do some processing and then delete those column. This is a new version
> of the application and we don't need those columns anymore.
> But the problem is that I need to be able to run this script more then
> once without any errors. So, second time when I run it, it gives an
> error that Invalid column name.
> Before I get values from those columns, I check if they exists or not
> and then get the value, but still it gives error, so I put everything
> as string and use EXEC to execute that string - but still.
> I don't know what to do.
> I am copying my code here.
> DECLARE @.value VARCHAR(8000)
> SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
> ' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
> exec (@.value)
> The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
> Anybody has any idea?
> Thanks
> Adnan Masood
> www.newbalanceindy.com
>