When I alter a table to add columns or change their properties, either in
Enterprise Manager or with T-SQL statements in Query Analyzer, the
alterations are not automatically noticed by the views that use the table.
The only thing I have been able to figure out to do is to find each
potentially affected view and then:
1. Open it in design mode
2. Check or uncheck a field in the Altered table
3. Uncheck or check the same field to restore the view to its original field
selection
4. Close the view and Save it.
Two problems:
1. That's tedious and time-consuming
2. It is easy to miss an affected view.
Can someone advise me as to a better way? (I am mostly using SQL Server 2000
)
Thanks,
Doug MacLeanDoug MacLean wrote:
> When I alter a table to add columns or change their properties, either in
> Enterprise Manager or with T-SQL statements in Query Analyzer, the
> alterations are not automatically noticed by the views that use the table.
> The only thing I have been able to figure out to do is to find each
> potentially affected view and then:
> 1. Open it in design mode
> 2. Check or uncheck a field in the Altered table
> 3. Uncheck or check the same field to restore the view to its original fie
ld
> selection
> 4. Close the view and Save it.
> Two problems:
> 1. That's tedious and time-consuming
> 2. It is easy to miss an affected view.
> Can someone advise me as to a better way? (I am mostly using SQL Server 20
00)
> --
> Thanks,
> Doug MacLean
Firstly, do not use SELECT * in views. It's generally a bad idea to use
SELECT * anywhere in production code. It's a particularly bad idea in
views because of the way views handle changes to the columns.
So assuming you have named columns in all your views, use
sp_refreshview to ensure the view is up to date with changes to its
columns. If you want to add a column you'll have to edit the view
definition of course, which is good practice and not difficult if you
have adequate source control and change control procedures. Table
changes should never be executed by Enterprise Manager in a production
environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thanks. That's very helpful. It eliminates the need for my 4-step process in
Enterprise Manager.
I wonder if there is a tool to list all of the views that depend on a
specified table, because the other challenge is actually finding all of them
when altering a table.
Best Regards,
--
Doug MacLean
"David Portas" wrote:
> Doug MacLean wrote:
> Firstly, do not use SELECT * in views. It's generally a bad idea to use
> SELECT * anywhere in production code. It's a particularly bad idea in
> views because of the way views handle changes to the columns.
> So assuming you have named columns in all your views, use
> sp_refreshview to ensure the view is up to date with changes to its
> columns. If you want to add a column you'll have to edit the view
> definition of course, which is good practice and not difficult if you
> have adequate source control and change control procedures. Table
> changes should never be executed by Enterprise Manager in a production
> environment.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Greetings,
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
Create the views WITH SCHEMABINDING and you won't be able to change the
table without first dropping the view. This is a good reminder to fix the
view ;-)
Regards,
Neale NOON|||You may want to use this.
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where table_name = '<table_name>'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
You might find it easier to refresh all views since you cannot rely on
dependency information unless the views were created WITH SCHEMABINDING.
The script below will generate a script to refresh all views in the current
database. You can wrap it in a cursor and execute according to your
preference. BTW, even without 'SELECT *', you can run into issues with
changed datatypes in the referenced tables.
SELECT
'EXEC sp_refreshview ''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
AND OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
>
> "David Portas" wrote:
>|||Thanks, Neale.
That's an interesting idea to know when I change a table that has one or
more dependent views. So it is a good reminder.
But I KNOW there are views. And what I want is a clean way of finding and
refreshing them. Adding a process of dropping (and then re-adding) them seem
s
tedious.
Thanks Best Regards,
--
Doug MacLean
"Neale NOON" wrote:
> Greetings,
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
> Create the views WITH SCHEMABINDING and you won't be able to change the
> table without first dropping the view. This is a good reminder to fix the
> view ;-)
> --
> Regards,
> Neale NOON
>
>|||Dan,
Ahhh!. That's a great idea. Thanks for both the idea and the sample code.
And it's practical because I only do such updates on the production system a
t
times when no one is using it.
You're right. I've already seen the problem that -- even without adding
columns -- the view needs to be refreshed.
Thanks much and Best regards,
--
Doug MacLean
"Dan Guzman" wrote:
> You might find it easier to refresh all views since you cannot rely on
> dependency information unless the views were created WITH SCHEMABINDING.
> The script below will generate a script to refresh all views in the curren
t
> database. You can wrap it in a cursor and execute according to your
> preference. BTW, even without 'SELECT *', you can run into issues with
> changed datatypes in the referenced tables.
> SELECT
> 'EXEC sp_refreshview ''' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> ''''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'VIEW'
> AND OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> ), 'IsMSShipped') = 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
>
Showing posts with label alter. Show all posts
Showing posts with label alter. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
Programmatically altering object code
Has anyone developed a way to programmatically alter the code for multiple
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software
Programmatically alter grouping
Hi Everyone:
We are evaluating the SQL Server Reporting Services for use in our
current web project. The users will have the ability to select report
fields that they can group on and filter on, via a web page.
The materials I have read so far have not touched on how one can
programmatically alter groups on a report and also how should the
report that offers several grouping options to the user be designed(as
in whether the report should have all possible groupings defined at
design time and "disabled" by default)?
In Crystal Report we remember creating the various goups and
"deactivating" them at design time and programmatically altering the
group hierarchy at run time.
If anyone has any information on this issue, it would be very much
appreciated. Thanks a lot.
-Raghu.http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Raghu" <raghu_seshadri@.hotmail.com> wrote in message
news:5c2d0972.0407150214.106d400d@.posting.google.com...
> Hi Everyone:
> We are evaluating the SQL Server Reporting Services for use in our
> current web project. The users will have the ability to select report
> fields that they can group on and filter on, via a web page.
> The materials I have read so far have not touched on how one can
> programmatically alter groups on a report and also how should the
> report that offers several grouping options to the user be designed(as
> in whether the report should have all possible groupings defined at
> design time and "disabled" by default)?
> In Crystal Report we remember creating the various goups and
> "deactivating" them at design time and programmatically altering the
> group hierarchy at run time.
> If anyone has any information on this issue, it would be very much
> appreciated. Thanks a lot.
> -Raghu.|||Your blog made an interesting reading. I will give this technique a
try. Thank you very much for your assistance.
Regards,
Raghu.|||Hi Chris:
I tried the grouping technique explained in your blog. It worked like
a charm. Thanks a lot for your help.
Regards,
Raghu.
We are evaluating the SQL Server Reporting Services for use in our
current web project. The users will have the ability to select report
fields that they can group on and filter on, via a web page.
The materials I have read so far have not touched on how one can
programmatically alter groups on a report and also how should the
report that offers several grouping options to the user be designed(as
in whether the report should have all possible groupings defined at
design time and "disabled" by default)?
In Crystal Report we remember creating the various goups and
"deactivating" them at design time and programmatically altering the
group hierarchy at run time.
If anyone has any information on this issue, it would be very much
appreciated. Thanks a lot.
-Raghu.http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Raghu" <raghu_seshadri@.hotmail.com> wrote in message
news:5c2d0972.0407150214.106d400d@.posting.google.com...
> Hi Everyone:
> We are evaluating the SQL Server Reporting Services for use in our
> current web project. The users will have the ability to select report
> fields that they can group on and filter on, via a web page.
> The materials I have read so far have not touched on how one can
> programmatically alter groups on a report and also how should the
> report that offers several grouping options to the user be designed(as
> in whether the report should have all possible groupings defined at
> design time and "disabled" by default)?
> In Crystal Report we remember creating the various goups and
> "deactivating" them at design time and programmatically altering the
> group hierarchy at run time.
> If anyone has any information on this issue, it would be very much
> appreciated. Thanks a lot.
> -Raghu.|||Your blog made an interesting reading. I will give this technique a
try. Thank you very much for your assistance.
Regards,
Raghu.|||Hi Chris:
I tried the grouping technique explained in your blog. It worked like
a charm. Thanks a lot for your help.
Regards,
Raghu.
Programmatically Alter a Report on Report Server
Hi,
I am looking at altering a report on report server programmatically from a
web page.
Is this possible, has someone done this?
Thanks
PonnuIf your ASP/Perl program can handle RDL file with XML format. Technically,
You can do it.
Ponnu wrote:
>Hi,
>I am looking at altering a report on report server programmatically from a
>web page.
>Is this possible, has someone done this?
>Thanks
>Ponnu
--
I am a frog enjoying the sunshine on waikiki beach. Do you want to join me?!
Message posted via http://www.sqlmonster.com|||Hi WaikikiFrog,
I am using ASP.Net,
how could I read a report present in Report Server?
and once read, I could modify it, then how to put it back onto the Report
Server?
I would greatly appreciate your suggestions\guidance on this
Thanks
Ponnu
"WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
news:6c79610a3e0cd@.uwe...
> If your ASP/Perl program can handle RDL file with XML format. Technically,
> You can do it.
>
> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically from a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
> --
> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
> me?!
> Message posted via http://www.sqlmonster.com
>|||This is a very bad idea. Although technically possible you cannot do what
you want with the server based product for all practical purposes. The RDL
is shared by everyone running the report. If you read the report in (which
should be possible using the web services api) you then deploy it. That rdl
is used for everyone. Understanding and modifying the rdl is non-trivial
(the specificiation can be found here:
http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
You will have performance problems and (as I noted above) anyone else
running the report will get the new report.
But, if you absolutely have to, instead you should get the new reportviewer
controls that ship with VS 2005. There is an asp.net 2.0 control and a
winform control. You can use this in local mode. In local mode no server is
used. You give the control the report and the data and it renders it. Again,
it is non-trivial if you have any sort of complex report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ponnu" <someone@.somewhere.com> wrote in message
news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
> Hi WaikikiFrog,
> I am using ASP.Net,
> how could I read a report present in Report Server?
> and once read, I could modify it, then how to put it back onto the Report
> Server?
> I would greatly appreciate your suggestions\guidance on this
> Thanks
> Ponnu
> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically from
>>a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
>> me?!
>> Message posted via http://www.sqlmonster.com
>|||Hi Bruce,
Thanks for the suggestion.
This is what I am trying to do, you might be able to help me out
I already have some reports designed using Report Designer(SSRS 2005) and
deployed to report server. I need to provide options for the end users to
change the order of columns in the report using a ASP.Net web page. So I
thought I will use a report viewer control to preivew the report in web page
and a grid(showing the list of columns) using which you could change the
order of columns. I then should be able to make this change in the report in
the report server.
I can't use Report Builder because Report Builder will not open\render the
reports designed in Report Designer even though the reverse is possible. For
more details please refer to this link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=595894&SiteID=1
So all I need is to be able to read a report from report server and make the
changes in the order of columns and deploy it back to report server.
I would greatly appreciate your help in this.
Thanks
Ponnu
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OCE6mTzOHHA.4372@.TK2MSFTNGP04.phx.gbl...
> This is a very bad idea. Although technically possible you cannot do what
> you want with the server based product for all practical purposes. The RDL
> is shared by everyone running the report. If you read the report in (which
> should be possible using the web services api) you then deploy it. That
> rdl is used for everyone. Understanding and modifying the rdl is
> non-trivial (the specificiation can be found here:
> http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
> You will have performance problems and (as I noted above) anyone else
> running the report will get the new report.
> But, if you absolutely have to, instead you should get the new
> reportviewer controls that ship with VS 2005. There is an asp.net 2.0
> control and a winform control. You can use this in local mode. In local
> mode no server is used. You give the control the report and the data and
> it renders it. Again, it is non-trivial if you have any sort of complex
> report.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ponnu" <someone@.somewhere.com> wrote in message
> news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
>> Hi WaikikiFrog,
>> I am using ASP.Net,
>> how could I read a report present in Report Server?
>> and once read, I could modify it, then how to put it back onto the Report
>> Server?
>> I would greatly appreciate your suggestions\guidance on this
>> Thanks
>> Ponnu
>> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
>> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically from
>>a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
>> me?!
>> Message posted via http://www.sqlmonster.com
>>
>|||Read up on web services. Report Manager itself uses web services with Report
Server.
The effort to do this is large. You would be better off to have the users
export it to Excel if they want a different order of the columns.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ponnu" <someone@.somewhere.com> wrote in message
news:udQrnX0OHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Hi Bruce,
> Thanks for the suggestion.
> This is what I am trying to do, you might be able to help me out
> I already have some reports designed using Report Designer(SSRS 2005) and
> deployed to report server. I need to provide options for the end users to
> change the order of columns in the report using a ASP.Net web page. So I
> thought I will use a report viewer control to preivew the report in web
> page and a grid(showing the list of columns) using which you could change
> the order of columns. I then should be able to make this change in the
> report in the report server.
> I can't use Report Builder because Report Builder will not open\render the
> reports designed in Report Designer even though the reverse is possible.
> For more details please refer to this link
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=595894&SiteID=1
> So all I need is to be able to read a report from report server and make
> the changes in the order of columns and deploy it back to report server.
> I would greatly appreciate your help in this.
> Thanks
> Ponnu
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OCE6mTzOHHA.4372@.TK2MSFTNGP04.phx.gbl...
>> This is a very bad idea. Although technically possible you cannot do what
>> you want with the server based product for all practical purposes. The
>> RDL is shared by everyone running the report. If you read the report in
>> (which should be possible using the web services api) you then deploy it.
>> That rdl is used for everyone. Understanding and modifying the rdl is
>> non-trivial (the specificiation can be found here:
>> http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
>> You will have performance problems and (as I noted above) anyone else
>> running the report will get the new report.
>> But, if you absolutely have to, instead you should get the new
>> reportviewer controls that ship with VS 2005. There is an asp.net 2.0
>> control and a winform control. You can use this in local mode. In local
>> mode no server is used. You give the control the report and the data and
>> it renders it. Again, it is non-trivial if you have any sort of complex
>> report.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ponnu" <someone@.somewhere.com> wrote in message
>> news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
>> Hi WaikikiFrog,
>> I am using ASP.Net,
>> how could I read a report present in Report Server?
>> and once read, I could modify it, then how to put it back onto the
>> Report Server?
>> I would greatly appreciate your suggestions\guidance on this
>> Thanks
>> Ponnu
>> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
>> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically
>>from a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
>> me?!
>> Message posted via http://www.sqlmonster.com
>>
>>
>|||Hi Bruce,
Can't go for excel option because, in our application the users in admin
role will make the changes so that the others users could download in
whatever format they want(preferably PDF)
If there is any link\resource relating to reading a report from Report
Server using a webservice and deploying it back again, please let me know.
In the meanwhile I will investigate how this Report Manager actually
downloads and uploads reports to Report Server.
Thanks
Ponnu
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O55xfC2OHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Read up on web services. Report Manager itself uses web services with
> Report Server.
> The effort to do this is large. You would be better off to have the users
> export it to Excel if they want a different order of the columns.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ponnu" <someone@.somewhere.com> wrote in message
> news:udQrnX0OHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Hi Bruce,
>> Thanks for the suggestion.
>> This is what I am trying to do, you might be able to help me out
>> I already have some reports designed using Report Designer(SSRS 2005) and
>> deployed to report server. I need to provide options for the end users to
>> change the order of columns in the report using a ASP.Net web page. So I
>> thought I will use a report viewer control to preivew the report in web
>> page and a grid(showing the list of columns) using which you could change
>> the order of columns. I then should be able to make this change in the
>> report in the report server.
>> I can't use Report Builder because Report Builder will not open\render
>> the reports designed in Report Designer even though the reverse is
>> possible. For more details please refer to this link
>> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=595894&SiteID=1
>> So all I need is to be able to read a report from report server and make
>> the changes in the order of columns and deploy it back to report server.
>> I would greatly appreciate your help in this.
>> Thanks
>> Ponnu
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OCE6mTzOHHA.4372@.TK2MSFTNGP04.phx.gbl...
>> This is a very bad idea. Although technically possible you cannot do
>> what you want with the server based product for all practical purposes.
>> The RDL is shared by everyone running the report. If you read the report
>> in (which should be possible using the web services api) you then deploy
>> it. That rdl is used for everyone. Understanding and modifying the rdl
>> is non-trivial (the specificiation can be found here:
>> http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
>> You will have performance problems and (as I noted above) anyone else
>> running the report will get the new report.
>> But, if you absolutely have to, instead you should get the new
>> reportviewer controls that ship with VS 2005. There is an asp.net 2.0
>> control and a winform control. You can use this in local mode. In local
>> mode no server is used. You give the control the report and the data and
>> it renders it. Again, it is non-trivial if you have any sort of complex
>> report.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ponnu" <someone@.somewhere.com> wrote in message
>> news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
>> Hi WaikikiFrog,
>> I am using ASP.Net,
>> how could I read a report present in Report Server?
>> and once read, I could modify it, then how to put it back onto the
>> Report Server?
>> I would greatly appreciate your suggestions\guidance on this
>> Thanks
>> Ponnu
>> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
>> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically
>>from a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to
>> join me?!
>> Message posted via http://www.sqlmonster.com
>>
>>
>>
>
I am looking at altering a report on report server programmatically from a
web page.
Is this possible, has someone done this?
Thanks
PonnuIf your ASP/Perl program can handle RDL file with XML format. Technically,
You can do it.
Ponnu wrote:
>Hi,
>I am looking at altering a report on report server programmatically from a
>web page.
>Is this possible, has someone done this?
>Thanks
>Ponnu
--
I am a frog enjoying the sunshine on waikiki beach. Do you want to join me?!
Message posted via http://www.sqlmonster.com|||Hi WaikikiFrog,
I am using ASP.Net,
how could I read a report present in Report Server?
and once read, I could modify it, then how to put it back onto the Report
Server?
I would greatly appreciate your suggestions\guidance on this
Thanks
Ponnu
"WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
news:6c79610a3e0cd@.uwe...
> If your ASP/Perl program can handle RDL file with XML format. Technically,
> You can do it.
>
> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically from a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
> --
> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
> me?!
> Message posted via http://www.sqlmonster.com
>|||This is a very bad idea. Although technically possible you cannot do what
you want with the server based product for all practical purposes. The RDL
is shared by everyone running the report. If you read the report in (which
should be possible using the web services api) you then deploy it. That rdl
is used for everyone. Understanding and modifying the rdl is non-trivial
(the specificiation can be found here:
http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
You will have performance problems and (as I noted above) anyone else
running the report will get the new report.
But, if you absolutely have to, instead you should get the new reportviewer
controls that ship with VS 2005. There is an asp.net 2.0 control and a
winform control. You can use this in local mode. In local mode no server is
used. You give the control the report and the data and it renders it. Again,
it is non-trivial if you have any sort of complex report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ponnu" <someone@.somewhere.com> wrote in message
news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
> Hi WaikikiFrog,
> I am using ASP.Net,
> how could I read a report present in Report Server?
> and once read, I could modify it, then how to put it back onto the Report
> Server?
> I would greatly appreciate your suggestions\guidance on this
> Thanks
> Ponnu
> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically from
>>a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
>> me?!
>> Message posted via http://www.sqlmonster.com
>|||Hi Bruce,
Thanks for the suggestion.
This is what I am trying to do, you might be able to help me out
I already have some reports designed using Report Designer(SSRS 2005) and
deployed to report server. I need to provide options for the end users to
change the order of columns in the report using a ASP.Net web page. So I
thought I will use a report viewer control to preivew the report in web page
and a grid(showing the list of columns) using which you could change the
order of columns. I then should be able to make this change in the report in
the report server.
I can't use Report Builder because Report Builder will not open\render the
reports designed in Report Designer even though the reverse is possible. For
more details please refer to this link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=595894&SiteID=1
So all I need is to be able to read a report from report server and make the
changes in the order of columns and deploy it back to report server.
I would greatly appreciate your help in this.
Thanks
Ponnu
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OCE6mTzOHHA.4372@.TK2MSFTNGP04.phx.gbl...
> This is a very bad idea. Although technically possible you cannot do what
> you want with the server based product for all practical purposes. The RDL
> is shared by everyone running the report. If you read the report in (which
> should be possible using the web services api) you then deploy it. That
> rdl is used for everyone. Understanding and modifying the rdl is
> non-trivial (the specificiation can be found here:
> http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
> You will have performance problems and (as I noted above) anyone else
> running the report will get the new report.
> But, if you absolutely have to, instead you should get the new
> reportviewer controls that ship with VS 2005. There is an asp.net 2.0
> control and a winform control. You can use this in local mode. In local
> mode no server is used. You give the control the report and the data and
> it renders it. Again, it is non-trivial if you have any sort of complex
> report.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ponnu" <someone@.somewhere.com> wrote in message
> news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
>> Hi WaikikiFrog,
>> I am using ASP.Net,
>> how could I read a report present in Report Server?
>> and once read, I could modify it, then how to put it back onto the Report
>> Server?
>> I would greatly appreciate your suggestions\guidance on this
>> Thanks
>> Ponnu
>> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
>> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically from
>>a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
>> me?!
>> Message posted via http://www.sqlmonster.com
>>
>|||Read up on web services. Report Manager itself uses web services with Report
Server.
The effort to do this is large. You would be better off to have the users
export it to Excel if they want a different order of the columns.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ponnu" <someone@.somewhere.com> wrote in message
news:udQrnX0OHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Hi Bruce,
> Thanks for the suggestion.
> This is what I am trying to do, you might be able to help me out
> I already have some reports designed using Report Designer(SSRS 2005) and
> deployed to report server. I need to provide options for the end users to
> change the order of columns in the report using a ASP.Net web page. So I
> thought I will use a report viewer control to preivew the report in web
> page and a grid(showing the list of columns) using which you could change
> the order of columns. I then should be able to make this change in the
> report in the report server.
> I can't use Report Builder because Report Builder will not open\render the
> reports designed in Report Designer even though the reverse is possible.
> For more details please refer to this link
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=595894&SiteID=1
> So all I need is to be able to read a report from report server and make
> the changes in the order of columns and deploy it back to report server.
> I would greatly appreciate your help in this.
> Thanks
> Ponnu
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OCE6mTzOHHA.4372@.TK2MSFTNGP04.phx.gbl...
>> This is a very bad idea. Although technically possible you cannot do what
>> you want with the server based product for all practical purposes. The
>> RDL is shared by everyone running the report. If you read the report in
>> (which should be possible using the web services api) you then deploy it.
>> That rdl is used for everyone. Understanding and modifying the rdl is
>> non-trivial (the specificiation can be found here:
>> http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
>> You will have performance problems and (as I noted above) anyone else
>> running the report will get the new report.
>> But, if you absolutely have to, instead you should get the new
>> reportviewer controls that ship with VS 2005. There is an asp.net 2.0
>> control and a winform control. You can use this in local mode. In local
>> mode no server is used. You give the control the report and the data and
>> it renders it. Again, it is non-trivial if you have any sort of complex
>> report.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ponnu" <someone@.somewhere.com> wrote in message
>> news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
>> Hi WaikikiFrog,
>> I am using ASP.Net,
>> how could I read a report present in Report Server?
>> and once read, I could modify it, then how to put it back onto the
>> Report Server?
>> I would greatly appreciate your suggestions\guidance on this
>> Thanks
>> Ponnu
>> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
>> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically
>>from a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to join
>> me?!
>> Message posted via http://www.sqlmonster.com
>>
>>
>|||Hi Bruce,
Can't go for excel option because, in our application the users in admin
role will make the changes so that the others users could download in
whatever format they want(preferably PDF)
If there is any link\resource relating to reading a report from Report
Server using a webservice and deploying it back again, please let me know.
In the meanwhile I will investigate how this Report Manager actually
downloads and uploads reports to Report Server.
Thanks
Ponnu
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O55xfC2OHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Read up on web services. Report Manager itself uses web services with
> Report Server.
> The effort to do this is large. You would be better off to have the users
> export it to Excel if they want a different order of the columns.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ponnu" <someone@.somewhere.com> wrote in message
> news:udQrnX0OHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Hi Bruce,
>> Thanks for the suggestion.
>> This is what I am trying to do, you might be able to help me out
>> I already have some reports designed using Report Designer(SSRS 2005) and
>> deployed to report server. I need to provide options for the end users to
>> change the order of columns in the report using a ASP.Net web page. So I
>> thought I will use a report viewer control to preivew the report in web
>> page and a grid(showing the list of columns) using which you could change
>> the order of columns. I then should be able to make this change in the
>> report in the report server.
>> I can't use Report Builder because Report Builder will not open\render
>> the reports designed in Report Designer even though the reverse is
>> possible. For more details please refer to this link
>> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=595894&SiteID=1
>> So all I need is to be able to read a report from report server and make
>> the changes in the order of columns and deploy it back to report server.
>> I would greatly appreciate your help in this.
>> Thanks
>> Ponnu
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OCE6mTzOHHA.4372@.TK2MSFTNGP04.phx.gbl...
>> This is a very bad idea. Although technically possible you cannot do
>> what you want with the server based product for all practical purposes.
>> The RDL is shared by everyone running the report. If you read the report
>> in (which should be possible using the web services api) you then deploy
>> it. That rdl is used for everyone. Understanding and modifying the rdl
>> is non-trivial (the specificiation can be found here:
>> http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx)
>> You will have performance problems and (as I noted above) anyone else
>> running the report will get the new report.
>> But, if you absolutely have to, instead you should get the new
>> reportviewer controls that ship with VS 2005. There is an asp.net 2.0
>> control and a winform control. You can use this in local mode. In local
>> mode no server is used. You give the control the report and the data and
>> it renders it. Again, it is non-trivial if you have any sort of complex
>> report.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ponnu" <someone@.somewhere.com> wrote in message
>> news:ugJhRPzOHHA.324@.TK2MSFTNGP06.phx.gbl...
>> Hi WaikikiFrog,
>> I am using ASP.Net,
>> how could I read a report present in Report Server?
>> and once read, I could modify it, then how to put it back onto the
>> Report Server?
>> I would greatly appreciate your suggestions\guidance on this
>> Thanks
>> Ponnu
>> "WaikikiFrog via SQLMonster.com" <u19654@.uwe> wrote in message
>> news:6c79610a3e0cd@.uwe...
>> If your ASP/Perl program can handle RDL file with XML format.
>> Technically,
>> You can do it.
>>
>> Ponnu wrote:
>>Hi,
>>I am looking at altering a report on report server programmatically
>>from a
>>web page.
>>Is this possible, has someone done this?
>>Thanks
>>Ponnu
>> --
>> I am a frog enjoying the sunshine on waikiki beach. Do you want to
>> join me?!
>> Message posted via http://www.sqlmonster.com
>>
>>
>>
>
Programmatically adding new column at desired place...not at the end
I understand that adding new column programmatically (using ALTER TABLE...),
adds it at the end of the table. Is it possible to specify that this column
should be placed at the 3rd place (not at the end).
Thanks in advance
J JustinHi Justin,
No, You can not do that. Only way for that is to drop and recreate the table
with new order.
Thanks
Hari
SQL Server MVP
"Justin" <justin@.gjsoft.com> wrote in message
news:%231cEsy0NFHA.2736@.tk2msftngp13.phx.gbl...
> I understand that adding new column programmatically (using ALTER
> TABLE...),
> adds it at the end of the table. Is it possible to specify that this
> column
> should be placed at the 3rd place (not at the end).
> Thanks in advance
>
> J Justin
>
>|||As Hari said, you can't without dropping and recreating the table. And your
code shouldn't be dependent on the physical order of the columns. Logically
speaking, columns in a table do not have an order. All references to columns
should be by name and not by ordinal.
Jacco Schalkwijk
SQL Server MVP
"Justin" <justin@.gjsoft.com> wrote in message
news:%231cEsy0NFHA.2736@.tk2msftngp13.phx.gbl...
> I understand that adding new column programmatically (using ALTER
> TABLE...),
> adds it at the end of the table. Is it possible to specify that this
> column
> should be placed at the 3rd place (not at the end).
> Thanks in advance
>
> J Justin
>
>|||http://www.aspfaq.com/2528
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Justin" <justin@.gjsoft.com> wrote in message
news:#1cEsy0NFHA.2736@.tk2msftngp13.phx.gbl...
> I understand that adding new column programmatically (using ALTER
TABLE...),
> adds it at the end of the table. Is it possible to specify that this
column
> should be placed at the 3rd place (not at the end).
> Thanks in advance
>
> J Justin
>
>
adds it at the end of the table. Is it possible to specify that this column
should be placed at the 3rd place (not at the end).
Thanks in advance
J JustinHi Justin,
No, You can not do that. Only way for that is to drop and recreate the table
with new order.
Thanks
Hari
SQL Server MVP
"Justin" <justin@.gjsoft.com> wrote in message
news:%231cEsy0NFHA.2736@.tk2msftngp13.phx.gbl...
> I understand that adding new column programmatically (using ALTER
> TABLE...),
> adds it at the end of the table. Is it possible to specify that this
> column
> should be placed at the 3rd place (not at the end).
> Thanks in advance
>
> J Justin
>
>|||As Hari said, you can't without dropping and recreating the table. And your
code shouldn't be dependent on the physical order of the columns. Logically
speaking, columns in a table do not have an order. All references to columns
should be by name and not by ordinal.
Jacco Schalkwijk
SQL Server MVP
"Justin" <justin@.gjsoft.com> wrote in message
news:%231cEsy0NFHA.2736@.tk2msftngp13.phx.gbl...
> I understand that adding new column programmatically (using ALTER
> TABLE...),
> adds it at the end of the table. Is it possible to specify that this
> column
> should be placed at the 3rd place (not at the end).
> Thanks in advance
>
> J Justin
>
>|||http://www.aspfaq.com/2528
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Justin" <justin@.gjsoft.com> wrote in message
news:#1cEsy0NFHA.2736@.tk2msftngp13.phx.gbl...
> I understand that adding new column programmatically (using ALTER
TABLE...),
> adds it at the end of the table. Is it possible to specify that this
column
> should be placed at the 3rd place (not at the end).
> Thanks in advance
>
> J Justin
>
>
Subscribe to:
Posts (Atom)