Is there any way to turn off (or hide) the export function when
viewing a report though report manager. There are certain reports
that we would only like people to view through report manager, we do
not want them to be able to export into PDF, Excel, etc..
Thanks,Steve,
You should be able to do this by creating a custom stylesheet for these
reports. By default, these are located in C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\styles. Create a copy of
htmlviewer.css, (i.e. noexport.css), and change the .ToolbarExport display
from "inline" to "none. Then, you can use the rc:stylesheetcommand=noexport
parameter to tell your webbrowser to display the viewer with that particular
stylesheet. for instance, you would use render the report like this:
http://myserver/reportserver/?/MyReport&rc:stylesheet=noexport&rs:Command=Render&rs:Format=HTML4.0
"Steve" <steven.cooper@.infocision.com> wrote in message
news:aa4429ef.0409230514.7ec55869@.posting.google.com...
> Is there any way to turn off (or hide) the export function when
> viewing a report though report manager. There are certain reports
> that we would only like people to view through report manager, we do
> not want them to be able to export into PDF, Excel, etc..
> Thanks,|||"Joe Krill" <jmk@.endowmentcapital.com> wrote in message news:<OQeONWNpEHA.3424@.TK2MSFTNGP12.phx.gbl>...
> Steve,
> You should be able to do this by creating a custom stylesheet for these
> reports. By default, these are located in C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\styles. Create a copy of
> htmlviewer.css, (i.e. noexport.css), and change the .ToolbarExport display
> from "inline" to "none. Then, you can use the rc:stylesheetcommand=noexport
> parameter to tell your webbrowser to display the viewer with that particular
> stylesheet. for instance, you would use render the report like this:
> http://myserver/reportserver/?/MyReport&rc:stylesheet=noexport&rs:Command=Render&rs:Format=HTML4.0
>
> "Steve" <steven.cooper@.infocision.com> wrote in message
> news:aa4429ef.0409230514.7ec55869@.posting.google.com...
> > Is there any way to turn off (or hide) the export function when
> > viewing a report though report manager. There are certain reports
> > that we would only like people to view through report manager, we do
> > not want them to be able to export into PDF, Excel, etc..
> >
> > Thanks,
Thanks. I don't think I was veru clear. What I meant, is there any
way in Report Manager to hide the export dropdown box, or gray it out.
They will have access to report manager, but I want to remove the
options from report manager. Preferrable hide them off the UI.
Showing posts with label manager. Show all posts
Showing posts with label manager. Show all posts
Thursday, March 29, 2012
Exporting Reporting Data to MS Access
Is it possible Reporting data to MS Access.
As such it is possible to export data from Report Manager to MS Excel,
PDF, HTML etc. Now I have a situation in my project to export data from
Report Manager to MS Access. Is this possible programmatically or in
any manner?The best would be to export it as CSV. Access can handle that. But, no,
there is no direct support for Access.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<kishore.lakkimsetty@.wipro.com> wrote in message
news:1139381808.546186.62460@.f14g2000cwb.googlegroups.com...
> Is it possible Reporting data to MS Access.
> As such it is possible to export data from Report Manager to MS Excel,
> PDF, HTML etc. Now I have a situation in my project to export data from
> Report Manager to MS Access. Is this possible programmatically or in
> any manner?
>
As such it is possible to export data from Report Manager to MS Excel,
PDF, HTML etc. Now I have a situation in my project to export data from
Report Manager to MS Access. Is this possible programmatically or in
any manner?The best would be to export it as CSV. Access can handle that. But, no,
there is no direct support for Access.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<kishore.lakkimsetty@.wipro.com> wrote in message
news:1139381808.546186.62460@.f14g2000cwb.googlegroups.com...
> Is it possible Reporting data to MS Access.
> As such it is possible to export data from Report Manager to MS Excel,
> PDF, HTML etc. Now I have a situation in my project to export data from
> Report Manager to MS Access. Is this possible programmatically or in
> any manner?
>
Tuesday, March 27, 2012
Exporting of Large Reports (Report Manager)
How can I increase the performance when attempting to export large reports to
an MS Excel or PDF file?
The report currently has an average of 29,995 records and is approximately
19,398,584 bytes in size. (650+ pages)
However, when rendering the report, the report takes only 30+ seconds.
http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
2fMember_Verification%2fReports%2fIPC_Members_Verification
Thank you in advance for your support.If it takes 30 seconds to html then it will take as much as 10 times longer
to Excel or PDF. For Excel there is a work around.
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager (RS 2000) it puts CSV data in unicode which Excel puts all in one
column. If you export in ASCII then Excel does just as you want. To prevent
a problem with cells (Excel will object to sorting the data) you need to
remove any textboxes you have (for instance with a title, showing the
parameters run etc) and instead add additional header rows, merge the cells
and put your text in there instead. I add a link at the top of the report
that says Export Data. With RS 2005 you can be able to configure it to use
ASCII instead of Unicode when exporting to CSV.
So, in RS 2000 I add a link at the top of the page and then use Jump to URL.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast. CSV ASCII will be just as fast as HTML.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
> How can I increase the performance when attempting to export large reports
> to
> an MS Excel or PDF file?
> The report currently has an average of 29,995 records and is approximately
> 19,398,584 bytes in size. (650+ pages)
> However, when rendering the report, the report takes only 30+ seconds.
> http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
> 2fMember_Verification%2fReports%2fIPC_Members_Verification
> Thank you in advance for your support.|||The solution worked! Thank you very much.
However, is there a way of scheduling the report using the same solution?
"Bruce L-C [MVP]" wrote:
> If it takes 30 seconds to html then it will take as much as 10 times longer
> to Excel or PDF. For Excel there is a work around.
> Depending on how you design your reports you can do the following to export
> to Excel. Or, what I do sometimes is make a copy of the report and clean it
> up for data export and then hide it in list view. If you export from Report
> Manager (RS 2000) it puts CSV data in unicode which Excel puts all in one
> column. If you export in ASCII then Excel does just as you want. To prevent
> a problem with cells (Excel will object to sorting the data) you need to
> remove any textboxes you have (for instance with a title, showing the
> parameters run etc) and instead add additional header rows, merge the cells
> and put your text in there instead. I add a link at the top of the report
> that says Export Data. With RS 2005 you can be able to configure it to use
> ASCII instead of Unicode when exporting to CSV.
> So, in RS 2000 I add a link at the top of the page and then use Jump to URL.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
>
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump to
> URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>
> Very nice and very fast. CSV ASCII will be just as fast as HTML.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
> > How can I increase the performance when attempting to export large reports
> > to
> > an MS Excel or PDF file?
> >
> > The report currently has an average of 29,995 records and is approximately
> > 19,398,584 bytes in size. (650+ pages)
> >
> > However, when rendering the report, the report takes only 30+ seconds.
> >
> > http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
> > 2fMember_Verification%2fReports%2fIPC_Members_Verification
> >
> > Thank you in advance for your support.
>
>|||It looks to me like you can't from the report manager. I don't know if you
can use web services (my guess is that you can). In RS 2005 you can
configure RS to automatically use CSV ASCII (which is what I have done).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:68EB7C88-F34A-4254-B963-A0F5F3FDE734@.microsoft.com...
> The solution worked! Thank you very much.
> However, is there a way of scheduling the report using the same solution?
>
> "Bruce L-C [MVP]" wrote:
>> If it takes 30 seconds to html then it will take as much as 10 times
>> longer
>> to Excel or PDF. For Excel there is a work around.
>> Depending on how you design your reports you can do the following to
>> export
>> to Excel. Or, what I do sometimes is make a copy of the report and clean
>> it
>> up for data export and then hide it in list view. If you export from
>> Report
>> Manager (RS 2000) it puts CSV data in unicode which Excel puts all in one
>> column. If you export in ASCII then Excel does just as you want. To
>> prevent
>> a problem with cells (Excel will object to sorting the data) you need to
>> remove any textboxes you have (for instance with a title, showing the
>> parameters run etc) and instead add additional header rows, merge the
>> cells
>> and put your text in there instead. I add a link at the top of the report
>> that says Export Data. With RS 2005 you can be able to configure it to
>> use
>> ASCII instead of Unicode when exporting to CSV.
>> So, in RS 2000 I add a link at the top of the page and then use Jump to
>> URL.
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up
>> with the data in a separate window:
>>
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in jump
>> to
>> URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>>
>> Very nice and very fast. CSV ASCII will be just as fast as HTML.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Terry" <Terry@.discussions.microsoft.com> wrote in message
>> news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
>> > How can I increase the performance when attempting to export large
>> > reports
>> > to
>> > an MS Excel or PDF file?
>> >
>> > The report currently has an average of 29,995 records and is
>> > approximately
>> > 19,398,584 bytes in size. (650+ pages)
>> >
>> > However, when rendering the report, the report takes only 30+ seconds.
>> >
>> > http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
>> > 2fMember_Verification%2fReports%2fIPC_Members_Verification
>> >
>> > Thank you in advance for your support.
>>
an MS Excel or PDF file?
The report currently has an average of 29,995 records and is approximately
19,398,584 bytes in size. (650+ pages)
However, when rendering the report, the report takes only 30+ seconds.
http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
2fMember_Verification%2fReports%2fIPC_Members_Verification
Thank you in advance for your support.If it takes 30 seconds to html then it will take as much as 10 times longer
to Excel or PDF. For Excel there is a work around.
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager (RS 2000) it puts CSV data in unicode which Excel puts all in one
column. If you export in ASCII then Excel does just as you want. To prevent
a problem with cells (Excel will object to sorting the data) you need to
remove any textboxes you have (for instance with a title, showing the
parameters run etc) and instead add additional header rows, merge the cells
and put your text in there instead. I add a link at the top of the report
that says Export Data. With RS 2005 you can be able to configure it to use
ASCII instead of Unicode when exporting to CSV.
So, in RS 2000 I add a link at the top of the page and then use Jump to URL.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast. CSV ASCII will be just as fast as HTML.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
> How can I increase the performance when attempting to export large reports
> to
> an MS Excel or PDF file?
> The report currently has an average of 29,995 records and is approximately
> 19,398,584 bytes in size. (650+ pages)
> However, when rendering the report, the report takes only 30+ seconds.
> http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
> 2fMember_Verification%2fReports%2fIPC_Members_Verification
> Thank you in advance for your support.|||The solution worked! Thank you very much.
However, is there a way of scheduling the report using the same solution?
"Bruce L-C [MVP]" wrote:
> If it takes 30 seconds to html then it will take as much as 10 times longer
> to Excel or PDF. For Excel there is a work around.
> Depending on how you design your reports you can do the following to export
> to Excel. Or, what I do sometimes is make a copy of the report and clean it
> up for data export and then hide it in list view. If you export from Report
> Manager (RS 2000) it puts CSV data in unicode which Excel puts all in one
> column. If you export in ASCII then Excel does just as you want. To prevent
> a problem with cells (Excel will object to sorting the data) you need to
> remove any textboxes you have (for instance with a title, showing the
> parameters run etc) and instead add additional header rows, merge the cells
> and put your text in there instead. I add a link at the top of the report
> that says Export Data. With RS 2005 you can be able to configure it to use
> ASCII instead of Unicode when exporting to CSV.
> So, in RS 2000 I add a link at the top of the page and then use Jump to URL.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
>
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump to
> URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>
> Very nice and very fast. CSV ASCII will be just as fast as HTML.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
> > How can I increase the performance when attempting to export large reports
> > to
> > an MS Excel or PDF file?
> >
> > The report currently has an average of 29,995 records and is approximately
> > 19,398,584 bytes in size. (650+ pages)
> >
> > However, when rendering the report, the report takes only 30+ seconds.
> >
> > http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
> > 2fMember_Verification%2fReports%2fIPC_Members_Verification
> >
> > Thank you in advance for your support.
>
>|||It looks to me like you can't from the report manager. I don't know if you
can use web services (my guess is that you can). In RS 2005 you can
configure RS to automatically use CSV ASCII (which is what I have done).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:68EB7C88-F34A-4254-B963-A0F5F3FDE734@.microsoft.com...
> The solution worked! Thank you very much.
> However, is there a way of scheduling the report using the same solution?
>
> "Bruce L-C [MVP]" wrote:
>> If it takes 30 seconds to html then it will take as much as 10 times
>> longer
>> to Excel or PDF. For Excel there is a work around.
>> Depending on how you design your reports you can do the following to
>> export
>> to Excel. Or, what I do sometimes is make a copy of the report and clean
>> it
>> up for data export and then hide it in list view. If you export from
>> Report
>> Manager (RS 2000) it puts CSV data in unicode which Excel puts all in one
>> column. If you export in ASCII then Excel does just as you want. To
>> prevent
>> a problem with cells (Excel will object to sorting the data) you need to
>> remove any textboxes you have (for instance with a title, showing the
>> parameters run etc) and instead add additional header rows, merge the
>> cells
>> and put your text in there instead. I add a link at the top of the report
>> that says Export Data. With RS 2005 you can be able to configure it to
>> use
>> ASCII instead of Unicode when exporting to CSV.
>> So, in RS 2000 I add a link at the top of the page and then use Jump to
>> URL.
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up
>> with the data in a separate window:
>>
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in jump
>> to
>> URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>>
>> Very nice and very fast. CSV ASCII will be just as fast as HTML.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Terry" <Terry@.discussions.microsoft.com> wrote in message
>> news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
>> > How can I increase the performance when attempting to export large
>> > reports
>> > to
>> > an MS Excel or PDF file?
>> >
>> > The report currently has an average of 29,995 records and is
>> > approximately
>> > 19,398,584 bytes in size. (650+ pages)
>> >
>> > However, when rendering the report, the report takes only 30+ seconds.
>> >
>> > http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
>> > 2fMember_Verification%2fReports%2fIPC_Members_Verification
>> >
>> > Thank you in advance for your support.
>>
Exporting Jobs in SQL Server Agent
Hello,
We have about 20 Jobs in the SQL Server Agent, under the Management section
of the Enterprise Manager tree that run on different schedules daily. We're
moving to a new server and I can't seem to find a way or a tool that will
let me copy the jobs to the new server. Does anyone know if there is a way
or a tool for such a task? We can redo them manually but thought I would ask
first before we started. We're on SQL2K. Thanks for any information you may
be able to give me.
RGPyou can right click on the job and choose Generate SQL Script...
btw, jobs are saved in msdb. :-)
"riley" <riley3@.microtek-sales.com> wrote in message
news:%23lSae4BxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We have about 20 Jobs in the SQL Server Agent, under the Management
section
> of the Enterprise Manager tree that run on different schedules daily.
We're
> moving to a new server and I can't seem to find a way or a tool that will
> let me copy the jobs to the new server. Does anyone know if there is a way
> or a tool for such a task? We can redo them manually but thought I would
ask
> first before we started. We're on SQL2K. Thanks for any information you
may
> be able to give me.
> RGP
>|||Right click the Jobs under Management | SQL Server Agent
for that SQL Server instance, select All Tasks -> Generate
SQL Scripts ..., and save the SQL script for all the local
jobs.
You can then run this generated script on a differen
server to re-create the jobs. You may need to edit the
script somewhat if the script contains references that are
not valid on this other server.
Linchi
>--Original Message--
>Hello,
>We have about 20 Jobs in the SQL Server Agent, under the
Management section
>of the Enterprise Manager tree that run on different
schedules daily. We're
>moving to a new server and I can't seem to find a way or
a tool that will
>let me copy the jobs to the new server. Does anyone know
if there is a way
>or a tool for such a task? We can redo them manually but
thought I would ask
>first before we started. We're on SQL2K. Thanks for any
information you may
>be able to give me.
>RGP
>
>.
>|||Thank you very much -I didn't realize the Generate SQL Scripts was for that
purpose. Appreciate the help.
RGP
"NEW NAME, OLD GUY" <PLEASE!> wrote in message
news:O4vnJFCxDHA.3436@.tk2msftngp13.phx.gbl...
> you can right click on the job and choose Generate SQL Script...
> btw, jobs are saved in msdb. :-)
> "riley" <riley3@.microtek-sales.com> wrote in message
> news:%23lSae4BxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > We have about 20 Jobs in the SQL Server Agent, under the Management
> section
> > of the Enterprise Manager tree that run on different schedules daily.
> We're
> > moving to a new server and I can't seem to find a way or a tool that
will
> > let me copy the jobs to the new server. Does anyone know if there is a
way
> > or a tool for such a task? We can redo them manually but thought I would
> ask
> > first before we started. We're on SQL2K. Thanks for any information you
> may
> > be able to give me.
> > RGP
> >
> >
>
We have about 20 Jobs in the SQL Server Agent, under the Management section
of the Enterprise Manager tree that run on different schedules daily. We're
moving to a new server and I can't seem to find a way or a tool that will
let me copy the jobs to the new server. Does anyone know if there is a way
or a tool for such a task? We can redo them manually but thought I would ask
first before we started. We're on SQL2K. Thanks for any information you may
be able to give me.
RGPyou can right click on the job and choose Generate SQL Script...
btw, jobs are saved in msdb. :-)
"riley" <riley3@.microtek-sales.com> wrote in message
news:%23lSae4BxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We have about 20 Jobs in the SQL Server Agent, under the Management
section
> of the Enterprise Manager tree that run on different schedules daily.
We're
> moving to a new server and I can't seem to find a way or a tool that will
> let me copy the jobs to the new server. Does anyone know if there is a way
> or a tool for such a task? We can redo them manually but thought I would
ask
> first before we started. We're on SQL2K. Thanks for any information you
may
> be able to give me.
> RGP
>|||Right click the Jobs under Management | SQL Server Agent
for that SQL Server instance, select All Tasks -> Generate
SQL Scripts ..., and save the SQL script for all the local
jobs.
You can then run this generated script on a differen
server to re-create the jobs. You may need to edit the
script somewhat if the script contains references that are
not valid on this other server.
Linchi
>--Original Message--
>Hello,
>We have about 20 Jobs in the SQL Server Agent, under the
Management section
>of the Enterprise Manager tree that run on different
schedules daily. We're
>moving to a new server and I can't seem to find a way or
a tool that will
>let me copy the jobs to the new server. Does anyone know
if there is a way
>or a tool for such a task? We can redo them manually but
thought I would ask
>first before we started. We're on SQL2K. Thanks for any
information you may
>be able to give me.
>RGP
>
>.
>|||Thank you very much -I didn't realize the Generate SQL Scripts was for that
purpose. Appreciate the help.
RGP
"NEW NAME, OLD GUY" <PLEASE!> wrote in message
news:O4vnJFCxDHA.3436@.tk2msftngp13.phx.gbl...
> you can right click on the job and choose Generate SQL Script...
> btw, jobs are saved in msdb. :-)
> "riley" <riley3@.microtek-sales.com> wrote in message
> news:%23lSae4BxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > We have about 20 Jobs in the SQL Server Agent, under the Management
> section
> > of the Enterprise Manager tree that run on different schedules daily.
> We're
> > moving to a new server and I can't seem to find a way or a tool that
will
> > let me copy the jobs to the new server. Does anyone know if there is a
way
> > or a tool for such a task? We can redo them manually but thought I would
> ask
> > first before we started. We're on SQL2K. Thanks for any information you
> may
> > be able to give me.
> > RGP
> >
> >
>
Exporting From SQL SERVER
When I am trying to export a table from SQL Server Enterprise (using
Enterprise Manager), I am gettin errors at the end. It is saying the
'Employees' table does not exist. Please help.Hi
Enterprise Manager), I am gettin errors at the end. It is saying the
'Employees' table does not exist. Please help.Hi
You don't say if this is the source or destination table, but I would assume
that this is may be caused by a permissions problem.
John
"Birdman" <brianwsnyder1@.comcast.net> wrote in message
news:Q8Gdnd5vlNpaN6jZnZ2dnUVZ_vqdnZ2d@.comcast.com. ..
> When I am trying to export a table from SQL Server Enterprise (using
> Enterprise Manager), I am gettin errors at the end. It is saying the
> 'Employees' table does not exist. Please help.
Sunday, March 25, 2012
exporting db schema
Using SQL Server Enterprise Manager,
how do I export a database schema?
Thanks.JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com
how do I export a database schema?
Thanks.JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com
exporting db schema
Using SQL Server Enterprise Manager,
how do I export a database schema?
Thanks.
JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com
how do I export a database schema?
Thanks.
JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com
exporting db schema
Using SQL Server Enterprise Manager,
how do I export a database schema?
Thanks.JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com
how do I export a database schema?
Thanks.JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com
Wednesday, March 21, 2012
Export/Import SQL EM Registrations?
I have a development laptop I frequently need to reimage (ghost). I have about 25 SQL Servers registered in Enterprise Manager. Some use Windows Auth, some SQL Auth. Can I export the registration information so I don’t have to re-enter it every time
I rebuild my laptop?
I have been able to export/import the server aliases using this key (this works fine): [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client\ConnectTo]
I have also exported/imported: [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X]
But All I get are the groups, no servers.
TIA
It is possible. You have to use DMO to save the registered servers to a
file, from the source machine. And then move this file to the target machine
and add the servers on the target machine by looping through the rows in the
file (again you will have to use DMO).
I hope this gives you a starting point. If not, post back and I'll try to
post some code.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
<hpux9@.nospam.nospam> wrote in message
news:2DFF37FF-E4D3-4FFC-BF8E-5DFA8DA9C482@.microsoft.com...
> I have a development laptop I frequently need to reimage (ghost). I have
about 25 SQL Servers registered in Enterprise Manager. Some use Windows
Auth, some SQL Auth. Can I export the registration information so I don't
have to re-enter it every time I rebuild my laptop?
> I have been able to export/import the server aliases using this key (this
works fine):
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client\ConnectTo]
>
> I have also exported/imported:
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
> But All I get are the groups, no servers.
> TIA
>
|||Hi TIA,
From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I understood
you? If there is anything I misunderstood, please feel free to let me know
Based on my scope, The method by exporting Register Files is not supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO
Here are the steps:
1. Create a vbs file and call it readreg.vbs which contains the following.
Please substitute [servername] with SQL Server name and [password] with the
SA password.
It also creates a table RegServers in pubs database so make sure that this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next
oSQLServer.Disconnect
msgbox "complete"
2. To run, open a command-prompt and run "cscript readreg.vbs".
3. To verify that the information was retrieved correctly, go to the SQL
Server
specified in the script and run the following:
use pubs
select * from RegServers
4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] with the
sa
password.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")
For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)
If LoginOutput = "" Then
LoginOutput = " "
End if
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer )
Next
Msgbox "complete"
5. To run, open a command-prompt and run "cscript createreg.vbs".
NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries in
the
following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered
Servers X
Please test the method and I strongly recommand you doing this in a
development machine first
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for the scripts. I have changed them to JavaScript and am using XML to do the export/import. Is it possible to get the Server aliases using SQLDMO, or should I just read straight from the registry?
Thanks
-Kevin
""Mingqing Cheng [MSFT]"" wrote:
> Hi TIA,
> From your descriptions, I understood that you would like to export all
> registered Server and then import them in a new machine. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> Based on my scope, The method by exporting Register Files is not supported
> from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
> could do it by creating SQL-DMO
> Here are the steps:
> 1. Create a vbs file and call it readreg.vbs which contains the following.
> Please substitute [servername] with SQL Server name and [password] with the
> SA password.
> It also creates a table RegServers in pubs database so make sure that this
> does not exist. This script collects information about the currently
> registered servers and stores the information in the RegServers table.
> Dim oApplication
> Dim oServerGroups
> Dim oServerGroup
> Dim oRegisteredServer
> Dim oNewRegisteredServer
> Dim oSQLServer
> Dim MsgOutput
> set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oApplication = CreateObject("SQLDMO.Application")
> Set oServerGroups = oApplication.ServerGroups
> oSQLServer.Connect "servername", "sa", "password"
> oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
> varchar(30),
> SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
> For Each oServerGroup in oApplication.ServerGroups
> For Each oRegisteredServer in oServerGroup.RegisteredServers
> MsgOutput = "Insert Into Pubs..RegServers values("
> MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
> oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
> CStr(oRegisteredServer.UseTrustedConnection) + "')"
> oSQLServer.ExecuteImmediate MsgOutput
> Next
> Next
> oSQLServer.Disconnect
> msgbox "complete"
>
> 2. To run, open a command-prompt and run "cscript readreg.vbs".
> 3. To verify that the information was retrieved correctly, go to the SQL
> Server
> specified in the script and run the following:
> use pubs
> select * from RegServers
> 4. Create another vbs file and call it createreg.vbs which contains the
> following:
> Again substitute [servername] with SQL Server name and [password] with the
> sa
> password.
> Dim oApplication
> Dim oServerGroups
> Dim oServerGroup
> Dim oRegisteredServer
> Dim oNewRegisteredServer
> Dim oSQLServer
> Dim oQueryResults
> Dim MsgOutput
> Dim num
> Dim SrvNameOutput
> Dim LoginOutput
> Dim PassOutput
> Dim AuthOutput
> Dim GetLoginOutput
> set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oApplication = CreateObject("SQLDMO.Application")
> Set oServerGroups = oApplication.ServerGroups
> oSQLServer.Connect "servername" , "sa", "password"
> set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
> SLogin,
> SPassword, IsNtAuth from pubs..RegServers")
> For num = 1 To oQueryResults.Rows
> SrvNameOutput = oQueryResults.GetColumnString(num, 1)
> LoginOutput = oQueryResults.GetColumnString(num, 2)
> PassOutput = oQueryResults.GetColumnString(num, 3)
> AuthOutput = oQueryResults.GetColumnString(num, 4)
> If LoginOutput = "" Then
> LoginOutput = " "
> End if
> Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
> oNewRegisteredServer.Login = LoginOutput
> oNewRegisteredServer.Name = SrvNameOutput
> oNewRegisteredServer.Password = PassOutput
> oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
> oServerGroups("SQL Server
> Group").RegisteredServers.Add(oNewRegisteredServer )
> Next
> Msgbox "complete"
> 5. To run, open a command-prompt and run "cscript createreg.vbs".
> NOTE:
> a. When doing the tests, please make sure that Enterprise Manager is
> closed.
> b. The script gathers information about the registered servers for the
> currently
> logged on user.
> c. If you want to test on a single machine, you may do the following:
> - run readreg.vbs
> - Delete the registration from Enterprise Manager or delete the entries in
> the
> following registry key:
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered
> Servers X
> Please test the method and I strongly recommand you doing this in a
> development machine first
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
|||Hi Kevin,
Does the script works fine with you? Based on my scope, I am afraid you
could not find Server Aliases by means of SQLDMO. I think reading directly
from Registry is fine.
BTW, It is highly appreciated if you could share us with your JavaScript
and XML files among Newsgroup Community. In this way, I believe, provided
multi ways for those who may encounter the same issue. Thanks in advance.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for your help! The scripts below are a hack, but they work fine for me.
These scripts can be used to dump/load SQL registration information so you don’t have to enter/re-enter it by hand (quite handy after you re-image your development workstation).
Dump_SQL_Reg.js
===============
Run from the command line to dump all SQL registration for the current machine and user to an XML file.
cscript /nologo Dump_SQL_Reg.js > servers.xml
NOTE: If there are any SQL servers using SQL auth, the passwords will be in clear text in the XML file.
Load_SQL_Reg.js
===============
Run from the command line to load SQL registrations from servers.xml (in same dir) for the currently logged in user.
cscript /nologo Load_SQL_Reg.js > load.log
Yes you can edit the servers.xml by hand and just use the loader script.
I whipped these up in a hurry, so I would not be surprised if there are some things it can’t deal with.
If you have any questions, let me know.
Sample of the XML file
======================
<?xml version='1.0' standalone='yes' ?>
<registrations>
<aliaslist>
<alias name='App1PRD_DB' data='DBMSSOCN,PRDDB01,1433'/>
<alias name='App2PRD_DB' data='DBMSSOCN, ,PRDDB01,1433'/>
<alias name='App1DEV_DB' data='DBMSSOCN,DEVDB01,1433'/>
<alias name='App2DEV_DB' data='DBMSSOCN, ,DEVDB01,1433'/>
</aliaslist>
<group name='App1'>
<reg server='App1_PRD_DB' login='' password='' trustedyn='1'/>
<reg server='App1_DEV_DB' login='' password='' trustedyn='1'/>
</group>
<group name='App2'>
<reg server=App2PRD_DB' login='XXXX' password='XXXX' trustedyn='0'/>
<reg server='App2DEV_DB' login='XXXX' password='XXXX' trustedyn='0'/>
</group>
</registrations>
//*************************************************//
// Dump_SQL_Reg.js
var oApplication = new ActiveXObject ("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups
var reReplaceXMLChars = new RegExp ("<|>|&|'","g");
var serverGroupEnum = new Enumerator(oServerGroups);
var xml = "<?xml version='1.0' standalone='yes' ?>\n<registrations>\n";
// Grab the alias list (if any)
var aliaslist = getSQLAliasXML();
xml += aliaslist;
for (;!serverGroupEnum.atEnd();serverGroupEnum.moveNex t())
{
oServerGroup = serverGroupEnum.item();
xml += " <group name='" + oServerGroup.Name.replace(reReplaceXMLChars, XMLEncode) +"'>\n";
registeredServerEnum = new Enumerator(oServerGroup.RegisteredServers);
for (;!registeredServerEnum.atEnd();registeredServerEn um.moveNext())
{
oRegisteredServer = registeredServerEnum.item();
xml += " <reg server='" + oRegisteredServer.Name.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "login='" + oRegisteredServer.Login.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "password='" + oRegisteredServer.Password.replace(reReplaceXMLCha rs, XMLEncode) + "' ";
xml += "trustedyn='" + oRegisteredServer.UseTrustedConnection + "'/>\n";
}
xml +=" </group>\n";
}
xml += "</registrations>";
WScript.Echo (xml);
/*
|| returns the alias on the current machine in an XML fragment
|| <aliaslist>
|| <alias name="my server alias" data="DBMSSOCN,xxxxxx,1433"/>
|| </aliaslist>
*/
function getSQLAliasXML()
{
var XMLFrag = ""
var hive = "HKLM";
var key = "SOFTWARE\\Microsoft\\MSSQLServer\\Client\\Connect To";
aAliasValues = HKLM_EnumValue(key);
if (aAliasValues != null)
{
var WshShell = WScript.CreateObject ("WScript.Shell");
var buffer = "";
XMLFrag += " <aliaslist>\n";
for (var x=0; x < aAliasValues.length; x++)
{
buffer = WshShell.RegRead (hive + "\\" + key + "\\" + aAliasValues[x]);
XMLFrag += " <alias name='" + aAliasValues[x] + "' data='" + buffer + "'/>\n";
}
XMLFrag += " </aliaslist>\n";
}
return (XMLFrag);
}
/*
|| Pass path to registry Key in HKLM
|| Returns an array with the values in the key
|| null if key is not found
||
*/
function HKLM_EnumValue (regPath)
{
HKLM = 0x80000002;
sRegPath = regPath;
try
{
oLoc = new ActiveXObject("WbemScripting.SWbemLocator");
oSvc = oLoc.ConnectServer(null, "root\\default");
oReg = oSvc.Get("StdRegProv");
oMethod = oReg.Methods_.Item("EnumValues");
oInParam = oMethod.InParameters.SpawnInstance_();
oInParam.hDefKey = HKLM;
oInParam.sSubKeyName = sRegPath;
oOutParam = oReg.ExecMethod_(oMethod.Name, oInParam);
if (oOutParam.sNames != null)
return (oOutParam.sNames.toArray());
else
return (null);
}
catch(err)
{
WScript.Echo("Error occurred\nCode: " + hex(err.number) +"; Description: " + err.description);
}
}
//User-defined function to format error codes.
//VBScript has a Hex() function but JScript does not.
function hex(nmb)
{
if (nmb > 0)
return nmb.toString(16);
else
return (nmb + 0x100000000).toString(16);
}
// Strip out special chars
function XMLEncode(str)
{
return (
(str == "<") ? "<" :
(str == ">") ? ">" :
(str == "&") ? "&" :
(str == "'") ? "'" :
(str == "\"") ? """ : str
);
}
//*************************************************//
//*************************************************//
// Load_SQL_Reg.js
// open the xml file
var fname = "servers.xml";
var xmlDoc = new ActiveXObject("Msxml2.DOMDocument");
xmlDoc.async = false;
xmlDoc.resolveExternals = false;
xmlDoc.load(fname);
if (xmlDoc.parseError.errorCode != 0)
{
var myErr = xmlDoc.parseError;
WScript.Echo("You have error " + myErr.reason);
}
else
{
// process each alias
var objNodeList = xmlDoc.getElementsByTagName("alias");
for (var i=0; i < objNodeList.length; i++)
{
var alias = objNodeList.item(i).getAttribute("name");
var data = objNodeList.item(i).getAttribute("data");
addAlias (alias, data);
}
var oApplication = new ActiveXObject("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups;
var oRegisteredServers = oApplication.RegisteredServers;
var objNodeList = xmlDoc.getElementsByTagName("reg");
for (var i=0; i < objNodeList.length; i++)
{
// Test the group
var group = objNodeList.item(i).parentNode.getAttribute("name" );
try
{
// does the group already exist?
var oServerGroup = oServerGroups(group);
}
catch (e)
{
// add it
WScript.Echo("Adding group: " + group);
var oGroup = new ActiveXObject("SQLDMO.ServerGroup");
oGroup.Name = group;
oServerGroups.Add(oGroup);
}
// Test the server
server = objNodeList.item(i).getAttribute("server");
try
{// Does the server already exist in this group?
var oRegisteredServer = oServerGroups(group).RegisteredServers(server);
// If so delete it!
WScript.Echo ("Removing Server: " + server);
oServerGroups(group).RegisteredServers.Remove(serv er);
}
catch (e)
{
// does not exist, do nothing
}
try
{
// NOTE: this will fail if server is already registered in a different group
WScript.Echo ("Adding Server: " + server);
var oNewServer = new ActiveXObject("SQLDMO.RegisteredServer");
oNewServer.Name = server;
oNewServer.Login = objNodeList.item(i).getAttribute("login").replace( /^$/, " ");
oNewServer.Password = objNodeList.item(i).getAttribute("password");
oNewServer.UseTrustedConnection = parseInt (objNodeList.item(i).getAttribute("trustedyn"));
oServerGroups(group).RegisteredServers.Add(oNewSer ver);
}
catch (e)
{
// print error and continue
WScript.Echo ("***Error**: " + e.description);
}
}
}
function addAlias(alias, data)
{
var regKey = "HKLM\\SOFTWARE\\Microsoft\\MSSQLServer\\Client\\C onnectTo\\" + alias ;
// see if the key exists
var WshShell = WScript.CreateObject ("WScript.Shell");
try
{
var found = WshShell.RegRead (regKey);
}
catch (e)
{
//do nothing
}
if (found != null)
{
// delete the key before re-add
WshShell.RegDelete (regKey);
}
// add the key
WScript.Echo ("Adding alias for : " + regKey);
WshShell.RegWrite (regKey, data, "REG_SZ");
}
//*************************************************//
""Mingqing Cheng [MSFT]"" wrote:
> Hi Kevin,
> Does the script works fine with you? Based on my scope, I am afraid you
> could not find Server Aliases by means of SQLDMO. I think reading directly
> from Registry is fine.
> BTW, It is highly appreciated if you could share us with your JavaScript
> and XML files among Newsgroup Community. In this way, I believe, provided
> multi ways for those who may encounter the same issue. Thanks in advance.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
I rebuild my laptop?
I have been able to export/import the server aliases using this key (this works fine): [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client\ConnectTo]
I have also exported/imported: [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X]
But All I get are the groups, no servers.
TIA
It is possible. You have to use DMO to save the registered servers to a
file, from the source machine. And then move this file to the target machine
and add the servers on the target machine by looping through the rows in the
file (again you will have to use DMO).
I hope this gives you a starting point. If not, post back and I'll try to
post some code.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
<hpux9@.nospam.nospam> wrote in message
news:2DFF37FF-E4D3-4FFC-BF8E-5DFA8DA9C482@.microsoft.com...
> I have a development laptop I frequently need to reimage (ghost). I have
about 25 SQL Servers registered in Enterprise Manager. Some use Windows
Auth, some SQL Auth. Can I export the registration information so I don't
have to re-enter it every time I rebuild my laptop?
> I have been able to export/import the server aliases using this key (this
works fine):
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client\ConnectTo]
>
> I have also exported/imported:
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
> But All I get are the groups, no servers.
> TIA
>
|||Hi TIA,
From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I understood
you? If there is anything I misunderstood, please feel free to let me know
Based on my scope, The method by exporting Register Files is not supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO
Here are the steps:
1. Create a vbs file and call it readreg.vbs which contains the following.
Please substitute [servername] with SQL Server name and [password] with the
SA password.
It also creates a table RegServers in pubs database so make sure that this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next
oSQLServer.Disconnect
msgbox "complete"
2. To run, open a command-prompt and run "cscript readreg.vbs".
3. To verify that the information was retrieved correctly, go to the SQL
Server
specified in the script and run the following:
use pubs
select * from RegServers
4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] with the
sa
password.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")
For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)
If LoginOutput = "" Then
LoginOutput = " "
End if
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer )
Next
Msgbox "complete"
5. To run, open a command-prompt and run "cscript createreg.vbs".
NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries in
the
following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered
Servers X
Please test the method and I strongly recommand you doing this in a
development machine first
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for the scripts. I have changed them to JavaScript and am using XML to do the export/import. Is it possible to get the Server aliases using SQLDMO, or should I just read straight from the registry?
Thanks
-Kevin
""Mingqing Cheng [MSFT]"" wrote:
> Hi TIA,
> From your descriptions, I understood that you would like to export all
> registered Server and then import them in a new machine. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> Based on my scope, The method by exporting Register Files is not supported
> from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
> could do it by creating SQL-DMO
> Here are the steps:
> 1. Create a vbs file and call it readreg.vbs which contains the following.
> Please substitute [servername] with SQL Server name and [password] with the
> SA password.
> It also creates a table RegServers in pubs database so make sure that this
> does not exist. This script collects information about the currently
> registered servers and stores the information in the RegServers table.
> Dim oApplication
> Dim oServerGroups
> Dim oServerGroup
> Dim oRegisteredServer
> Dim oNewRegisteredServer
> Dim oSQLServer
> Dim MsgOutput
> set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oApplication = CreateObject("SQLDMO.Application")
> Set oServerGroups = oApplication.ServerGroups
> oSQLServer.Connect "servername", "sa", "password"
> oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
> varchar(30),
> SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
> For Each oServerGroup in oApplication.ServerGroups
> For Each oRegisteredServer in oServerGroup.RegisteredServers
> MsgOutput = "Insert Into Pubs..RegServers values("
> MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
> oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
> CStr(oRegisteredServer.UseTrustedConnection) + "')"
> oSQLServer.ExecuteImmediate MsgOutput
> Next
> Next
> oSQLServer.Disconnect
> msgbox "complete"
>
> 2. To run, open a command-prompt and run "cscript readreg.vbs".
> 3. To verify that the information was retrieved correctly, go to the SQL
> Server
> specified in the script and run the following:
> use pubs
> select * from RegServers
> 4. Create another vbs file and call it createreg.vbs which contains the
> following:
> Again substitute [servername] with SQL Server name and [password] with the
> sa
> password.
> Dim oApplication
> Dim oServerGroups
> Dim oServerGroup
> Dim oRegisteredServer
> Dim oNewRegisteredServer
> Dim oSQLServer
> Dim oQueryResults
> Dim MsgOutput
> Dim num
> Dim SrvNameOutput
> Dim LoginOutput
> Dim PassOutput
> Dim AuthOutput
> Dim GetLoginOutput
> set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oApplication = CreateObject("SQLDMO.Application")
> Set oServerGroups = oApplication.ServerGroups
> oSQLServer.Connect "servername" , "sa", "password"
> set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
> SLogin,
> SPassword, IsNtAuth from pubs..RegServers")
> For num = 1 To oQueryResults.Rows
> SrvNameOutput = oQueryResults.GetColumnString(num, 1)
> LoginOutput = oQueryResults.GetColumnString(num, 2)
> PassOutput = oQueryResults.GetColumnString(num, 3)
> AuthOutput = oQueryResults.GetColumnString(num, 4)
> If LoginOutput = "" Then
> LoginOutput = " "
> End if
> Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
> oNewRegisteredServer.Login = LoginOutput
> oNewRegisteredServer.Name = SrvNameOutput
> oNewRegisteredServer.Password = PassOutput
> oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
> oServerGroups("SQL Server
> Group").RegisteredServers.Add(oNewRegisteredServer )
> Next
> Msgbox "complete"
> 5. To run, open a command-prompt and run "cscript createreg.vbs".
> NOTE:
> a. When doing the tests, please make sure that Enterprise Manager is
> closed.
> b. The script gathers information about the registered servers for the
> currently
> logged on user.
> c. If you want to test on a single machine, you may do the following:
> - run readreg.vbs
> - Delete the registration from Enterprise Manager or delete the entries in
> the
> following registry key:
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered
> Servers X
> Please test the method and I strongly recommand you doing this in a
> development machine first
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
|||Hi Kevin,
Does the script works fine with you? Based on my scope, I am afraid you
could not find Server Aliases by means of SQLDMO. I think reading directly
from Registry is fine.
BTW, It is highly appreciated if you could share us with your JavaScript
and XML files among Newsgroup Community. In this way, I believe, provided
multi ways for those who may encounter the same issue. Thanks in advance.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for your help! The scripts below are a hack, but they work fine for me.
These scripts can be used to dump/load SQL registration information so you don’t have to enter/re-enter it by hand (quite handy after you re-image your development workstation).
Dump_SQL_Reg.js
===============
Run from the command line to dump all SQL registration for the current machine and user to an XML file.
cscript /nologo Dump_SQL_Reg.js > servers.xml
NOTE: If there are any SQL servers using SQL auth, the passwords will be in clear text in the XML file.
Load_SQL_Reg.js
===============
Run from the command line to load SQL registrations from servers.xml (in same dir) for the currently logged in user.
cscript /nologo Load_SQL_Reg.js > load.log
Yes you can edit the servers.xml by hand and just use the loader script.
I whipped these up in a hurry, so I would not be surprised if there are some things it can’t deal with.
If you have any questions, let me know.
Sample of the XML file
======================
<?xml version='1.0' standalone='yes' ?>
<registrations>
<aliaslist>
<alias name='App1PRD_DB' data='DBMSSOCN,PRDDB01,1433'/>
<alias name='App2PRD_DB' data='DBMSSOCN, ,PRDDB01,1433'/>
<alias name='App1DEV_DB' data='DBMSSOCN,DEVDB01,1433'/>
<alias name='App2DEV_DB' data='DBMSSOCN, ,DEVDB01,1433'/>
</aliaslist>
<group name='App1'>
<reg server='App1_PRD_DB' login='' password='' trustedyn='1'/>
<reg server='App1_DEV_DB' login='' password='' trustedyn='1'/>
</group>
<group name='App2'>
<reg server=App2PRD_DB' login='XXXX' password='XXXX' trustedyn='0'/>
<reg server='App2DEV_DB' login='XXXX' password='XXXX' trustedyn='0'/>
</group>
</registrations>
//*************************************************//
// Dump_SQL_Reg.js
var oApplication = new ActiveXObject ("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups
var reReplaceXMLChars = new RegExp ("<|>|&|'","g");
var serverGroupEnum = new Enumerator(oServerGroups);
var xml = "<?xml version='1.0' standalone='yes' ?>\n<registrations>\n";
// Grab the alias list (if any)
var aliaslist = getSQLAliasXML();
xml += aliaslist;
for (;!serverGroupEnum.atEnd();serverGroupEnum.moveNex t())
{
oServerGroup = serverGroupEnum.item();
xml += " <group name='" + oServerGroup.Name.replace(reReplaceXMLChars, XMLEncode) +"'>\n";
registeredServerEnum = new Enumerator(oServerGroup.RegisteredServers);
for (;!registeredServerEnum.atEnd();registeredServerEn um.moveNext())
{
oRegisteredServer = registeredServerEnum.item();
xml += " <reg server='" + oRegisteredServer.Name.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "login='" + oRegisteredServer.Login.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "password='" + oRegisteredServer.Password.replace(reReplaceXMLCha rs, XMLEncode) + "' ";
xml += "trustedyn='" + oRegisteredServer.UseTrustedConnection + "'/>\n";
}
xml +=" </group>\n";
}
xml += "</registrations>";
WScript.Echo (xml);
/*
|| returns the alias on the current machine in an XML fragment
|| <aliaslist>
|| <alias name="my server alias" data="DBMSSOCN,xxxxxx,1433"/>
|| </aliaslist>
*/
function getSQLAliasXML()
{
var XMLFrag = ""
var hive = "HKLM";
var key = "SOFTWARE\\Microsoft\\MSSQLServer\\Client\\Connect To";
aAliasValues = HKLM_EnumValue(key);
if (aAliasValues != null)
{
var WshShell = WScript.CreateObject ("WScript.Shell");
var buffer = "";
XMLFrag += " <aliaslist>\n";
for (var x=0; x < aAliasValues.length; x++)
{
buffer = WshShell.RegRead (hive + "\\" + key + "\\" + aAliasValues[x]);
XMLFrag += " <alias name='" + aAliasValues[x] + "' data='" + buffer + "'/>\n";
}
XMLFrag += " </aliaslist>\n";
}
return (XMLFrag);
}
/*
|| Pass path to registry Key in HKLM
|| Returns an array with the values in the key
|| null if key is not found
||
*/
function HKLM_EnumValue (regPath)
{
HKLM = 0x80000002;
sRegPath = regPath;
try
{
oLoc = new ActiveXObject("WbemScripting.SWbemLocator");
oSvc = oLoc.ConnectServer(null, "root\\default");
oReg = oSvc.Get("StdRegProv");
oMethod = oReg.Methods_.Item("EnumValues");
oInParam = oMethod.InParameters.SpawnInstance_();
oInParam.hDefKey = HKLM;
oInParam.sSubKeyName = sRegPath;
oOutParam = oReg.ExecMethod_(oMethod.Name, oInParam);
if (oOutParam.sNames != null)
return (oOutParam.sNames.toArray());
else
return (null);
}
catch(err)
{
WScript.Echo("Error occurred\nCode: " + hex(err.number) +"; Description: " + err.description);
}
}
//User-defined function to format error codes.
//VBScript has a Hex() function but JScript does not.
function hex(nmb)
{
if (nmb > 0)
return nmb.toString(16);
else
return (nmb + 0x100000000).toString(16);
}
// Strip out special chars
function XMLEncode(str)
{
return (
(str == "<") ? "<" :
(str == ">") ? ">" :
(str == "&") ? "&" :
(str == "'") ? "'" :
(str == "\"") ? """ : str
);
}
//*************************************************//
//*************************************************//
// Load_SQL_Reg.js
// open the xml file
var fname = "servers.xml";
var xmlDoc = new ActiveXObject("Msxml2.DOMDocument");
xmlDoc.async = false;
xmlDoc.resolveExternals = false;
xmlDoc.load(fname);
if (xmlDoc.parseError.errorCode != 0)
{
var myErr = xmlDoc.parseError;
WScript.Echo("You have error " + myErr.reason);
}
else
{
// process each alias
var objNodeList = xmlDoc.getElementsByTagName("alias");
for (var i=0; i < objNodeList.length; i++)
{
var alias = objNodeList.item(i).getAttribute("name");
var data = objNodeList.item(i).getAttribute("data");
addAlias (alias, data);
}
var oApplication = new ActiveXObject("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups;
var oRegisteredServers = oApplication.RegisteredServers;
var objNodeList = xmlDoc.getElementsByTagName("reg");
for (var i=0; i < objNodeList.length; i++)
{
// Test the group
var group = objNodeList.item(i).parentNode.getAttribute("name" );
try
{
// does the group already exist?
var oServerGroup = oServerGroups(group);
}
catch (e)
{
// add it
WScript.Echo("Adding group: " + group);
var oGroup = new ActiveXObject("SQLDMO.ServerGroup");
oGroup.Name = group;
oServerGroups.Add(oGroup);
}
// Test the server
server = objNodeList.item(i).getAttribute("server");
try
{// Does the server already exist in this group?
var oRegisteredServer = oServerGroups(group).RegisteredServers(server);
// If so delete it!
WScript.Echo ("Removing Server: " + server);
oServerGroups(group).RegisteredServers.Remove(serv er);
}
catch (e)
{
// does not exist, do nothing
}
try
{
// NOTE: this will fail if server is already registered in a different group
WScript.Echo ("Adding Server: " + server);
var oNewServer = new ActiveXObject("SQLDMO.RegisteredServer");
oNewServer.Name = server;
oNewServer.Login = objNodeList.item(i).getAttribute("login").replace( /^$/, " ");
oNewServer.Password = objNodeList.item(i).getAttribute("password");
oNewServer.UseTrustedConnection = parseInt (objNodeList.item(i).getAttribute("trustedyn"));
oServerGroups(group).RegisteredServers.Add(oNewSer ver);
}
catch (e)
{
// print error and continue
WScript.Echo ("***Error**: " + e.description);
}
}
}
function addAlias(alias, data)
{
var regKey = "HKLM\\SOFTWARE\\Microsoft\\MSSQLServer\\Client\\C onnectTo\\" + alias ;
// see if the key exists
var WshShell = WScript.CreateObject ("WScript.Shell");
try
{
var found = WshShell.RegRead (regKey);
}
catch (e)
{
//do nothing
}
if (found != null)
{
// delete the key before re-add
WshShell.RegDelete (regKey);
}
// add the key
WScript.Echo ("Adding alias for : " + regKey);
WshShell.RegWrite (regKey, data, "REG_SZ");
}
//*************************************************//
""Mingqing Cheng [MSFT]"" wrote:
> Hi Kevin,
> Does the script works fine with you? Based on my scope, I am afraid you
> could not find Server Aliases by means of SQLDMO. I think reading directly
> from Registry is fine.
> BTW, It is highly appreciated if you could share us with your JavaScript
> and XML files among Newsgroup Community. In this way, I believe, provided
> multi ways for those who may encounter the same issue. Thanks in advance.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
export/import registered server list to/from enterprise manager.
how can I export the list of servers I've registered in my enterprise manager
and import it back on other computer's EM? TIA.
Adi ..
I posted a reply in the .server group. Please do not multipost.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"adi" <adi@.discussions.microsoft.com> wrote in message
news:E649596F-2163-4726-99AB-6270F6550EBB@.microsoft.com...
how can I export the list of servers I've registered in my enterprise
manager
and import it back on other computer's EM? TIA.
Adi ..
sql
and import it back on other computer's EM? TIA.
Adi ..
I posted a reply in the .server group. Please do not multipost.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"adi" <adi@.discussions.microsoft.com> wrote in message
news:E649596F-2163-4726-99AB-6270F6550EBB@.microsoft.com...
how can I export the list of servers I've registered in my enterprise
manager
and import it back on other computer's EM? TIA.
Adi ..
sql
Monday, March 19, 2012
export/import registered server list from/to EM
how can I export the list of servers I've registered in my enterprise manager
and import it back on other computer's EM? TIA.
Adi ..See if this helps:
http://groups-beta.google.com/group/microsoft.public.sqlserver.tools/msg/53d641423f7828bb?hl=en
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"adi" <adi@.discussions.microsoft.com> wrote in message
news:40E5E439-F0F8-425E-B7A5-41D72A08E070@.microsoft.com...
how can I export the list of servers I've registered in my enterprise
manager
and import it back on other computer's EM? TIA.
Adi ..|||Thanks Vyas,
That was helpful. That script imported all servers under default "SQL Server
Group", but it was better than nothing.
"Narayana Vyas Kondreddi" wrote:
> See if this helps:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.tools/msg/53d641423f7828bb?hl=en
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "adi" <adi@.discussions.microsoft.com> wrote in message
> news:40E5E439-F0F8-425E-B7A5-41D72A08E070@.microsoft.com...
> how can I export the list of servers I've registered in my enterprise
> manager
> and import it back on other computer's EM? TIA.
> Adi ..
>
>
and import it back on other computer's EM? TIA.
Adi ..See if this helps:
http://groups-beta.google.com/group/microsoft.public.sqlserver.tools/msg/53d641423f7828bb?hl=en
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"adi" <adi@.discussions.microsoft.com> wrote in message
news:40E5E439-F0F8-425E-B7A5-41D72A08E070@.microsoft.com...
how can I export the list of servers I've registered in my enterprise
manager
and import it back on other computer's EM? TIA.
Adi ..|||Thanks Vyas,
That was helpful. That script imported all servers under default "SQL Server
Group", but it was better than nothing.
"Narayana Vyas Kondreddi" wrote:
> See if this helps:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.tools/msg/53d641423f7828bb?hl=en
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "adi" <adi@.discussions.microsoft.com> wrote in message
> news:40E5E439-F0F8-425E-B7A5-41D72A08E070@.microsoft.com...
> how can I export the list of servers I've registered in my enterprise
> manager
> and import it back on other computer's EM? TIA.
> Adi ..
>
>
Export window does not close
Report Server 2005; .NET framework 2.0; IE 6.0
I view a report in Report Manager, then I click the drop-down box and select an output type, select Save, and a new blank web page opens, then the small export window opens asking for the filename. The file is created, then I close the export window, but the blank web page window does not close. I can see that the web page showing my original view of the report is still open, but I have to close the blank web page myself. While not really an error, this is an annoyance for users. Is there a setting that will help prevent this problem? Is this a bug?
Thanks for any help.
Getting same problem as mentioned by DawnJ..
i want to rectify this problem as soon as possible..Reply back friends if u have relevant information for this thread...
Thanks in Advance
Bhoopathi...
Export window does not close
Report Server 2005; .NET framework 2.0; IE 6.0
I view a report in Report Manager, then I click the drop-down box and select an output type, select Save, and a new blank web page opens, then the small export window opens asking for the filename. The file is created, then I close the export window, but the blank web page window does not close. I can see that the web page showing my original view of the report is still open, but I have to close the blank web page myself. While not really an error, this is an annoyance for users. Is there a setting that will help prevent this problem? Is this a bug?
Thanks for any help.
Getting same problem as mentioned by DawnJ..
i want to rectify this problem as soon as possible..Reply back friends if u have relevant information for this thread...
Thanks in Advance
Bhoopathi...
Sunday, February 26, 2012
Export to excel from report manager problem
When i use export from report manager to excel for some reason it gave us
that error
but one thing more i used open that report using visual studio on my friend
computer and then preview the report and then select save as excel file . it
didnt give me error but when i use report manager and then try to export to
excel has that error
Microsoft Excel File Repair Log
Errors were detected in file C:\test.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.Hello Amjad.
I've seen this before. This happened when i had certain formulas in my
report, either in text boxes or in a chart. Are you using any complex
formulas? If you are, take them out temporarily, and try to export the
file. Let me know if it works.
Amjad wrote:
> When i use export from report manager to excel for some reason it gave us
> that error
> but one thing more i used open that report using visual studio on my friend
> computer and then preview the report and then select save as excel file . it
> didnt give me error but when i use report manager and then try to export to
> excel has that error
> Microsoft Excel File Repair Log
> Errors were detected in file C:\test.xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.|||Thanks
Yeah i was using a two charts but for certain reason for one year as year
was my parameter did give me any error and for another year data it gave me
error to open affter export. then i removed the chart and after that it didnt
actually i was using two chart , removing either of one solved the problem
but i need to include these chart back but due to that problem i removed it
temprarly....
Do you have any solution or any hot fix availabe to solve this problem.
because on of my friend computer it did export from visual studio preview
option not from report manager but on my computer it did not export both from
visual studio and report manager. it seem that his computer got some updates
for visual studio that i am missing thats why its giving me that problem even
from visual studio preview option. so if we find that hotfix so i can install
both on report manager server and my local computer...
"KevinGreen24@.gmail.com" wrote:
> Hello Amjad.
> I've seen this before. This happened when i had certain formulas in my
> report, either in text boxes or in a chart. Are you using any complex
> formulas? If you are, take them out temporarily, and try to export the
> file. Let me know if it works.
>
> Amjad wrote:
> > When i use export from report manager to excel for some reason it gave us
> > that error
> > but one thing more i used open that report using visual studio on my friend
> > computer and then preview the report and then select save as excel file . it
> > didnt give me error but when i use report manager and then try to export to
> > excel has that error
> >
> > Microsoft Excel File Repair Log
> >
> > Errors were detected in file C:\test.xls'
> > The following is a list of repairs:
> >
> > Damage to the file was so extensive that repairs were not possible. Excel
> > attempted to recover your formulas and values, but some data may have been
> > lost or corrupted.
>|||OK. Put the charts back in, and try to export again.
I'm not exactly sure of any hotfixes right now. If there's any service
packs / patches out there for MSRS 2005 you should install them. But
I'm not positive there are any. I'm sorry I couldn't be of more help.
Amjad wrote:
> Thanks
> Yeah i was using a two charts but for certain reason for one year as year
> was my parameter did give me any error and for another year data it gave me
> error to open affter export. then i removed the chart and after that it didnt
> actually i was using two chart , removing either of one solved the problem
> but i need to include these chart back but due to that problem i removed it
> temprarly....
> Do you have any solution or any hot fix availabe to solve this problem.
> because on of my friend computer it did export from visual studio preview
> option not from report manager but on my computer it did not export both from
> visual studio and report manager. it seem that his computer got some updates
> for visual studio that i am missing thats why its giving me that problem even
> from visual studio preview option. so if we find that hotfix so i can install
> both on report manager server and my local computer...
>
> "KevinGreen24@.gmail.com" wrote:
> > Hello Amjad.
> >
> > I've seen this before. This happened when i had certain formulas in my
> > report, either in text boxes or in a chart. Are you using any complex
> > formulas? If you are, take them out temporarily, and try to export the
> > file. Let me know if it works.
> >
> >
> > Amjad wrote:
> > > When i use export from report manager to excel for some reason it gave us
> > > that error
> > > but one thing more i used open that report using visual studio on my friend
> > > computer and then preview the report and then select save as excel file . it
> > > didnt give me error but when i use report manager and then try to export to
> > > excel has that error
> > >
> > > Microsoft Excel File Repair Log
> > >
> > > Errors were detected in file C:\test.xls'
> > > The following is a list of repairs:
> > >
> > > Damage to the file was so extensive that repairs were not possible. Excel
> > > attempted to recover your formulas and values, but some data may have been
> > > lost or corrupted.
> >
> >
that error
but one thing more i used open that report using visual studio on my friend
computer and then preview the report and then select save as excel file . it
didnt give me error but when i use report manager and then try to export to
excel has that error
Microsoft Excel File Repair Log
Errors were detected in file C:\test.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.Hello Amjad.
I've seen this before. This happened when i had certain formulas in my
report, either in text boxes or in a chart. Are you using any complex
formulas? If you are, take them out temporarily, and try to export the
file. Let me know if it works.
Amjad wrote:
> When i use export from report manager to excel for some reason it gave us
> that error
> but one thing more i used open that report using visual studio on my friend
> computer and then preview the report and then select save as excel file . it
> didnt give me error but when i use report manager and then try to export to
> excel has that error
> Microsoft Excel File Repair Log
> Errors were detected in file C:\test.xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.|||Thanks
Yeah i was using a two charts but for certain reason for one year as year
was my parameter did give me any error and for another year data it gave me
error to open affter export. then i removed the chart and after that it didnt
actually i was using two chart , removing either of one solved the problem
but i need to include these chart back but due to that problem i removed it
temprarly....
Do you have any solution or any hot fix availabe to solve this problem.
because on of my friend computer it did export from visual studio preview
option not from report manager but on my computer it did not export both from
visual studio and report manager. it seem that his computer got some updates
for visual studio that i am missing thats why its giving me that problem even
from visual studio preview option. so if we find that hotfix so i can install
both on report manager server and my local computer...
"KevinGreen24@.gmail.com" wrote:
> Hello Amjad.
> I've seen this before. This happened when i had certain formulas in my
> report, either in text boxes or in a chart. Are you using any complex
> formulas? If you are, take them out temporarily, and try to export the
> file. Let me know if it works.
>
> Amjad wrote:
> > When i use export from report manager to excel for some reason it gave us
> > that error
> > but one thing more i used open that report using visual studio on my friend
> > computer and then preview the report and then select save as excel file . it
> > didnt give me error but when i use report manager and then try to export to
> > excel has that error
> >
> > Microsoft Excel File Repair Log
> >
> > Errors were detected in file C:\test.xls'
> > The following is a list of repairs:
> >
> > Damage to the file was so extensive that repairs were not possible. Excel
> > attempted to recover your formulas and values, but some data may have been
> > lost or corrupted.
>|||OK. Put the charts back in, and try to export again.
I'm not exactly sure of any hotfixes right now. If there's any service
packs / patches out there for MSRS 2005 you should install them. But
I'm not positive there are any. I'm sorry I couldn't be of more help.
Amjad wrote:
> Thanks
> Yeah i was using a two charts but for certain reason for one year as year
> was my parameter did give me any error and for another year data it gave me
> error to open affter export. then i removed the chart and after that it didnt
> actually i was using two chart , removing either of one solved the problem
> but i need to include these chart back but due to that problem i removed it
> temprarly....
> Do you have any solution or any hot fix availabe to solve this problem.
> because on of my friend computer it did export from visual studio preview
> option not from report manager but on my computer it did not export both from
> visual studio and report manager. it seem that his computer got some updates
> for visual studio that i am missing thats why its giving me that problem even
> from visual studio preview option. so if we find that hotfix so i can install
> both on report manager server and my local computer...
>
> "KevinGreen24@.gmail.com" wrote:
> > Hello Amjad.
> >
> > I've seen this before. This happened when i had certain formulas in my
> > report, either in text boxes or in a chart. Are you using any complex
> > formulas? If you are, take them out temporarily, and try to export the
> > file. Let me know if it works.
> >
> >
> > Amjad wrote:
> > > When i use export from report manager to excel for some reason it gave us
> > > that error
> > > but one thing more i used open that report using visual studio on my friend
> > > computer and then preview the report and then select save as excel file . it
> > > didnt give me error but when i use report manager and then try to export to
> > > excel has that error
> > >
> > > Microsoft Excel File Repair Log
> > >
> > > Errors were detected in file C:\test.xls'
> > > The following is a list of repairs:
> > >
> > > Damage to the file was so extensive that repairs were not possible. Excel
> > > attempted to recover your formulas and values, but some data may have been
> > > lost or corrupted.
> >
> >
Friday, February 24, 2012
Export to Excel - file size bloat
When exporting to Excel from Report Manager, we are finding that the file
size is bloated. If I open the exported file, then do a Save As, the file
size drops by at least 50%.
Any thoughts?Make sure your on SP2 - we made some big improvements over RTM in Excel file
size.
There are some optimizations the Excel format is capable of that we do not
currently support. 50% difference seems high but is theoretically possible
even with SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||Already on SP2.
"Donovan Smith [MSFT]" wrote:
> Make sure your on SP2 - we made some big improvements over RTM in Excel file
> size.
> There are some optimizations the Excel format is capable of that we do not
> currently support. 50% difference seems high but is theoretically possible
> even with SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> > When exporting to Excel from Report Manager, we are finding that the file
> > size is bloated. If I open the exported file, then do a Save As, the file
> > size drops by at least 50%.
> >
> > Any thoughts?
>
>|||I also noticed the same thing with all service packs.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> Already on SP2.
> "Donovan Smith [MSFT]" wrote:
>> Make sure your on SP2 - we made some big improvements over RTM in Excel
>> file
>> size.
>> There are some optimizations the Excel format is capable of that we do
>> not
>> currently support. 50% difference seems high but is theoretically
>> possible
>> even with SP2.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> > When exporting to Excel from Report Manager, we are finding that the
>> > file
>> > size is bloated. If I open the exported file, then do a Save As, the
>> > file
>> > size drops by at least 50%.
>> >
>> > Any thoughts?
>>|||I am having the same problem. Not only the huge size frezes the exporting also.
"Amila Liyanaarchchi" wrote:
> I also noticed the same thing with all service packs.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> > Already on SP2.
> >
> > "Donovan Smith [MSFT]" wrote:
> >
> >> Make sure your on SP2 - we made some big improvements over RTM in Excel
> >> file
> >> size.
> >>
> >> There are some optimizations the Excel format is capable of that we do
> >> not
> >> currently support. 50% difference seems high but is theoretically
> >> possible
> >> even with SP2.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> >> > When exporting to Excel from Report Manager, we are finding that the
> >> > file
> >> > size is bloated. If I open the exported file, then do a Save As, the
> >> > file
> >> > size drops by at least 50%.
> >> >
> >> > Any thoughts?
> >>
> >>
> >>
>
>|||If you have a lot of data, try exporting as CSV ASCII. In 2005 you can have
Report Manager default to this when exporting from there.
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sun" <sun@.discussions.microsoft.com> wrote in message
news:CB2B8A34-3994-444C-9059-E5FAB6CFE764@.microsoft.com...
>I am having the same problem. Not only the huge size frezes the exporting
>also.
> "Amila Liyanaarchchi" wrote:
>> I also noticed the same thing with all service packs.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
>> > Already on SP2.
>> >
>> > "Donovan Smith [MSFT]" wrote:
>> >
>> >> Make sure your on SP2 - we made some big improvements over RTM in
>> >> Excel
>> >> file
>> >> size.
>> >>
>> >> There are some optimizations the Excel format is capable of that we do
>> >> not
>> >> currently support. 50% difference seems high but is theoretically
>> >> possible
>> >> even with SP2.
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> >> > When exporting to Excel from Report Manager, we are finding that the
>> >> > file
>> >> > size is bloated. If I open the exported file, then do a Save As,
>> >> > the
>> >> > file
>> >> > size drops by at least 50%.
>> >> >
>> >> > Any thoughts?
>> >>
>> >>
>> >>
>>|||HI there,
we have the same problem. I have a report with 389 records having 23
columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
(feature) is, that RS saves the file as Excel XML. That's progress to you!!!
If you open the file in Excel and save it as normal Excel format, you will
get a much smaller fle size!
Honestly, I am not impressed. I get files with around 7700 records which is
80 MByte!!! It is not possible to open such a file! If I save this file as
CSV it is just over 12 MByte! But if you think you can just double click on
the CSV you'll get another disappointment. EXCEL opens it with one line per
record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
Don't get me wrong, I love RS! But the export function just sucks! Sorry!
Cheers
Peter
"Kristen" wrote:
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||You are running RS 2000 with no service packs. Install either SP1 or SP2.
The Excel export was changed with service pack one to use native format.
If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
RS 2000 I added a link to export to CSV using ASCII.
At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
problem free upgrade for me and has some very good new features: faster,
multi-select parameters, date picker, end user sorting, etc.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> HI there,
> we have the same problem. I have a report with 389 records having 23
> columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> (feature) is, that RS saves the file as Excel XML. That's progress to
> you!!!
> If you open the file in Excel and save it as normal Excel format, you will
> get a much smaller fle size!
> Honestly, I am not impressed. I get files with around 7700 records which
> is
> 80 MByte!!! It is not possible to open such a file! If I save this file as
> CSV it is just over 12 MByte! But if you think you can just double click
> on
> the CSV you'll get another disappointment. EXCEL opens it with one line
> per
> record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> Cheers
> Peter
> "Kristen" wrote:
>> When exporting to Excel from Report Manager, we are finding that the file
>> size is bloated. If I open the exported file, then do a Save As, the
>> file
>> size drops by at least 50%.
>> Any thoughts?|||Hiya Bruce,
first of all, I am sorry that I wrote this post yesterday. Too many emotions
because I had all this trouble with the Excel Export. You are absolutely
right, SP2 fixes the problem. I now have to convince the people to update a
production server with SP2.
I know that the upgrade to 2005 improves a lot. But I guess you are in IT
for quite a while. Big organisations usually have a problem with upgrading.
We techos can't understand this. I, for example work in a world wide IT
company. Can you imagine that we still run on XP without SP2?
I am also quite concerned about Excel and XML based on the experiences I
made with the RS Excel file size. If over 7000 rows in a spreadsheet have a
filesize of over 80 MByte, then there is definitely a problem. But that's of
course a different story.
Anyway, I get into waffling.
Thank you for your help with this. Very much appreciated!
Cheers
Peter
"Bruce L-C [MVP]" wrote:
> You are running RS 2000 with no service packs. Install either SP1 or SP2.
> The Excel export was changed with service pack one to use native format.
> If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
> RS 2000 I added a link to export to CSV using ASCII.
> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
> problem free upgrade for me and has some very good new features: faster,
> multi-select parameters, date picker, end user sorting, etc.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> > HI there,
> >
> > we have the same problem. I have a report with 389 records having 23
> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> > (feature) is, that RS saves the file as Excel XML. That's progress to
> > you!!!
> > If you open the file in Excel and save it as normal Excel format, you will
> > get a much smaller fle size!
> >
> > Honestly, I am not impressed. I get files with around 7700 records which
> > is
> > 80 MByte!!! It is not possible to open such a file! If I save this file as
> > CSV it is just over 12 MByte! But if you think you can just double click
> > on
> > the CSV you'll get another disappointment. EXCEL opens it with one line
> > per
> > record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> >
> > Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> >
> > Cheers
> >
> > Peter
> >
> > "Kristen" wrote:
> >
> >> When exporting to Excel from Report Manager, we are finding that the file
> >> size is bloated. If I open the exported file, then do a Save As, the
> >> file
> >> size drops by at least 50%.
> >>
> >> Any thoughts?
>
>|||One thing to remember when it comes to RS 2005. You can upgrade to RS 2005
without upgrade SQL Server to 2005. That might be easier to do since in
large organizations, database versions are changed slowly (I work for a
fortune 100 company, maybe fortune 50). You do need a SQL Server 2005
license.
By the way, my company is also on XP with SP1, not SP2. However, for RS you
are only affecting reporting services, not the database, not the users.
Another biggie with going to SP1 or SP2 is you get client side printing.
Just based on that it is worthwhile to install the service pack.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:BFC3D54D-6F05-42E7-B4B6-1B75FFAFC95A@.microsoft.com...
> Hiya Bruce,
> first of all, I am sorry that I wrote this post yesterday. Too many
> emotions
> because I had all this trouble with the Excel Export. You are absolutely
> right, SP2 fixes the problem. I now have to convince the people to update
> a
> production server with SP2.
> I know that the upgrade to 2005 improves a lot. But I guess you are in IT
> for quite a while. Big organisations usually have a problem with
> upgrading.
> We techos can't understand this. I, for example work in a world wide IT
> company. Can you imagine that we still run on XP without SP2?
> I am also quite concerned about Excel and XML based on the experiences I
> made with the RS Excel file size. If over 7000 rows in a spreadsheet have
> a
> filesize of over 80 MByte, then there is definitely a problem. But that's
> of
> course a different story.
> Anyway, I get into waffling.
> Thank you for your help with this. Very much appreciated!
> Cheers
> Peter
> "Bruce L-C [MVP]" wrote:
>> You are running RS 2000 with no service packs. Install either SP1 or SP2.
>> The Excel export was changed with service pack one to use native format.
>> If RS 2005 you can set up Report Manager to export CSV in ASCII format.
>> In
>> RS 2000 I added a link to export to CSV using ASCII.
>> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
>> problem free upgrade for me and has some very good new features: faster,
>> multi-select parameters, date picker, end user sorting, etc.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
>> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
>> > HI there,
>> >
>> > we have the same problem. I have a report with 389 records having 23
>> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
>> > (feature) is, that RS saves the file as Excel XML. That's progress to
>> > you!!!
>> > If you open the file in Excel and save it as normal Excel format, you
>> > will
>> > get a much smaller fle size!
>> >
>> > Honestly, I am not impressed. I get files with around 7700 records
>> > which
>> > is
>> > 80 MByte!!! It is not possible to open such a file! If I save this file
>> > as
>> > CSV it is just over 12 MByte! But if you think you can just double
>> > click
>> > on
>> > the CSV you'll get another disappointment. EXCEL opens it with one line
>> > per
>> > record. Because it is Unicode! If I transform it to ASCII it is 6
>> > MByte!
>> >
>> > Don't get me wrong, I love RS! But the export function just sucks!
>> > Sorry!
>> >
>> > Cheers
>> >
>> > Peter
>> >
>> > "Kristen" wrote:
>> >
>> >> When exporting to Excel from Report Manager, we are finding that the
>> >> file
>> >> size is bloated. If I open the exported file, then do a Save As, the
>> >> file
>> >> size drops by at least 50%.
>> >>
>> >> Any thoughts?
>>
size is bloated. If I open the exported file, then do a Save As, the file
size drops by at least 50%.
Any thoughts?Make sure your on SP2 - we made some big improvements over RTM in Excel file
size.
There are some optimizations the Excel format is capable of that we do not
currently support. 50% difference seems high but is theoretically possible
even with SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||Already on SP2.
"Donovan Smith [MSFT]" wrote:
> Make sure your on SP2 - we made some big improvements over RTM in Excel file
> size.
> There are some optimizations the Excel format is capable of that we do not
> currently support. 50% difference seems high but is theoretically possible
> even with SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> > When exporting to Excel from Report Manager, we are finding that the file
> > size is bloated. If I open the exported file, then do a Save As, the file
> > size drops by at least 50%.
> >
> > Any thoughts?
>
>|||I also noticed the same thing with all service packs.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> Already on SP2.
> "Donovan Smith [MSFT]" wrote:
>> Make sure your on SP2 - we made some big improvements over RTM in Excel
>> file
>> size.
>> There are some optimizations the Excel format is capable of that we do
>> not
>> currently support. 50% difference seems high but is theoretically
>> possible
>> even with SP2.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> > When exporting to Excel from Report Manager, we are finding that the
>> > file
>> > size is bloated. If I open the exported file, then do a Save As, the
>> > file
>> > size drops by at least 50%.
>> >
>> > Any thoughts?
>>|||I am having the same problem. Not only the huge size frezes the exporting also.
"Amila Liyanaarchchi" wrote:
> I also noticed the same thing with all service packs.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> > Already on SP2.
> >
> > "Donovan Smith [MSFT]" wrote:
> >
> >> Make sure your on SP2 - we made some big improvements over RTM in Excel
> >> file
> >> size.
> >>
> >> There are some optimizations the Excel format is capable of that we do
> >> not
> >> currently support. 50% difference seems high but is theoretically
> >> possible
> >> even with SP2.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> >> > When exporting to Excel from Report Manager, we are finding that the
> >> > file
> >> > size is bloated. If I open the exported file, then do a Save As, the
> >> > file
> >> > size drops by at least 50%.
> >> >
> >> > Any thoughts?
> >>
> >>
> >>
>
>|||If you have a lot of data, try exporting as CSV ASCII. In 2005 you can have
Report Manager default to this when exporting from there.
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sun" <sun@.discussions.microsoft.com> wrote in message
news:CB2B8A34-3994-444C-9059-E5FAB6CFE764@.microsoft.com...
>I am having the same problem. Not only the huge size frezes the exporting
>also.
> "Amila Liyanaarchchi" wrote:
>> I also noticed the same thing with all service packs.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
>> > Already on SP2.
>> >
>> > "Donovan Smith [MSFT]" wrote:
>> >
>> >> Make sure your on SP2 - we made some big improvements over RTM in
>> >> Excel
>> >> file
>> >> size.
>> >>
>> >> There are some optimizations the Excel format is capable of that we do
>> >> not
>> >> currently support. 50% difference seems high but is theoretically
>> >> possible
>> >> even with SP2.
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> >> > When exporting to Excel from Report Manager, we are finding that the
>> >> > file
>> >> > size is bloated. If I open the exported file, then do a Save As,
>> >> > the
>> >> > file
>> >> > size drops by at least 50%.
>> >> >
>> >> > Any thoughts?
>> >>
>> >>
>> >>
>>|||HI there,
we have the same problem. I have a report with 389 records having 23
columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
(feature) is, that RS saves the file as Excel XML. That's progress to you!!!
If you open the file in Excel and save it as normal Excel format, you will
get a much smaller fle size!
Honestly, I am not impressed. I get files with around 7700 records which is
80 MByte!!! It is not possible to open such a file! If I save this file as
CSV it is just over 12 MByte! But if you think you can just double click on
the CSV you'll get another disappointment. EXCEL opens it with one line per
record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
Don't get me wrong, I love RS! But the export function just sucks! Sorry!
Cheers
Peter
"Kristen" wrote:
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||You are running RS 2000 with no service packs. Install either SP1 or SP2.
The Excel export was changed with service pack one to use native format.
If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
RS 2000 I added a link to export to CSV using ASCII.
At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
problem free upgrade for me and has some very good new features: faster,
multi-select parameters, date picker, end user sorting, etc.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> HI there,
> we have the same problem. I have a report with 389 records having 23
> columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> (feature) is, that RS saves the file as Excel XML. That's progress to
> you!!!
> If you open the file in Excel and save it as normal Excel format, you will
> get a much smaller fle size!
> Honestly, I am not impressed. I get files with around 7700 records which
> is
> 80 MByte!!! It is not possible to open such a file! If I save this file as
> CSV it is just over 12 MByte! But if you think you can just double click
> on
> the CSV you'll get another disappointment. EXCEL opens it with one line
> per
> record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> Cheers
> Peter
> "Kristen" wrote:
>> When exporting to Excel from Report Manager, we are finding that the file
>> size is bloated. If I open the exported file, then do a Save As, the
>> file
>> size drops by at least 50%.
>> Any thoughts?|||Hiya Bruce,
first of all, I am sorry that I wrote this post yesterday. Too many emotions
because I had all this trouble with the Excel Export. You are absolutely
right, SP2 fixes the problem. I now have to convince the people to update a
production server with SP2.
I know that the upgrade to 2005 improves a lot. But I guess you are in IT
for quite a while. Big organisations usually have a problem with upgrading.
We techos can't understand this. I, for example work in a world wide IT
company. Can you imagine that we still run on XP without SP2?
I am also quite concerned about Excel and XML based on the experiences I
made with the RS Excel file size. If over 7000 rows in a spreadsheet have a
filesize of over 80 MByte, then there is definitely a problem. But that's of
course a different story.
Anyway, I get into waffling.
Thank you for your help with this. Very much appreciated!
Cheers
Peter
"Bruce L-C [MVP]" wrote:
> You are running RS 2000 with no service packs. Install either SP1 or SP2.
> The Excel export was changed with service pack one to use native format.
> If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
> RS 2000 I added a link to export to CSV using ASCII.
> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
> problem free upgrade for me and has some very good new features: faster,
> multi-select parameters, date picker, end user sorting, etc.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> > HI there,
> >
> > we have the same problem. I have a report with 389 records having 23
> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> > (feature) is, that RS saves the file as Excel XML. That's progress to
> > you!!!
> > If you open the file in Excel and save it as normal Excel format, you will
> > get a much smaller fle size!
> >
> > Honestly, I am not impressed. I get files with around 7700 records which
> > is
> > 80 MByte!!! It is not possible to open such a file! If I save this file as
> > CSV it is just over 12 MByte! But if you think you can just double click
> > on
> > the CSV you'll get another disappointment. EXCEL opens it with one line
> > per
> > record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> >
> > Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> >
> > Cheers
> >
> > Peter
> >
> > "Kristen" wrote:
> >
> >> When exporting to Excel from Report Manager, we are finding that the file
> >> size is bloated. If I open the exported file, then do a Save As, the
> >> file
> >> size drops by at least 50%.
> >>
> >> Any thoughts?
>
>|||One thing to remember when it comes to RS 2005. You can upgrade to RS 2005
without upgrade SQL Server to 2005. That might be easier to do since in
large organizations, database versions are changed slowly (I work for a
fortune 100 company, maybe fortune 50). You do need a SQL Server 2005
license.
By the way, my company is also on XP with SP1, not SP2. However, for RS you
are only affecting reporting services, not the database, not the users.
Another biggie with going to SP1 or SP2 is you get client side printing.
Just based on that it is worthwhile to install the service pack.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:BFC3D54D-6F05-42E7-B4B6-1B75FFAFC95A@.microsoft.com...
> Hiya Bruce,
> first of all, I am sorry that I wrote this post yesterday. Too many
> emotions
> because I had all this trouble with the Excel Export. You are absolutely
> right, SP2 fixes the problem. I now have to convince the people to update
> a
> production server with SP2.
> I know that the upgrade to 2005 improves a lot. But I guess you are in IT
> for quite a while. Big organisations usually have a problem with
> upgrading.
> We techos can't understand this. I, for example work in a world wide IT
> company. Can you imagine that we still run on XP without SP2?
> I am also quite concerned about Excel and XML based on the experiences I
> made with the RS Excel file size. If over 7000 rows in a spreadsheet have
> a
> filesize of over 80 MByte, then there is definitely a problem. But that's
> of
> course a different story.
> Anyway, I get into waffling.
> Thank you for your help with this. Very much appreciated!
> Cheers
> Peter
> "Bruce L-C [MVP]" wrote:
>> You are running RS 2000 with no service packs. Install either SP1 or SP2.
>> The Excel export was changed with service pack one to use native format.
>> If RS 2005 you can set up Report Manager to export CSV in ASCII format.
>> In
>> RS 2000 I added a link to export to CSV using ASCII.
>> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
>> problem free upgrade for me and has some very good new features: faster,
>> multi-select parameters, date picker, end user sorting, etc.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
>> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
>> > HI there,
>> >
>> > we have the same problem. I have a report with 389 records having 23
>> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
>> > (feature) is, that RS saves the file as Excel XML. That's progress to
>> > you!!!
>> > If you open the file in Excel and save it as normal Excel format, you
>> > will
>> > get a much smaller fle size!
>> >
>> > Honestly, I am not impressed. I get files with around 7700 records
>> > which
>> > is
>> > 80 MByte!!! It is not possible to open such a file! If I save this file
>> > as
>> > CSV it is just over 12 MByte! But if you think you can just double
>> > click
>> > on
>> > the CSV you'll get another disappointment. EXCEL opens it with one line
>> > per
>> > record. Because it is Unicode! If I transform it to ASCII it is 6
>> > MByte!
>> >
>> > Don't get me wrong, I love RS! But the export function just sucks!
>> > Sorry!
>> >
>> > Cheers
>> >
>> > Peter
>> >
>> > "Kristen" wrote:
>> >
>> >> When exporting to Excel from Report Manager, we are finding that the
>> >> file
>> >> size is bloated. If I open the exported file, then do a Save As, the
>> >> file
>> >> size drops by at least 50%.
>> >>
>> >> Any thoughts?
>>
Friday, February 17, 2012
Export SQL Table using SQL Enterprise Manager
How do I export an sql table from a server db using SQL Enterprise Manager?
I have tried using Microsoft Web Data Administrator but it can't connect to the db.
The db that is live has all the data, but I don't have a copy locally (I accidently deleted it)...which is why i need to export it to an sql file.
Can someone please help me as I have exhausted my own knowledge.
Cheers
You don't have to export a Table as .SQL file, it is just the extension for code used by SQL Server. I save mine sometimes a .txt, when you want to use it copy it to the Query Analyzer and click on execute and your code will run.
Now to your problem just right click on the table you want and click on generate script and execute it in your copy of the database. If you get an error then you need to use ANSI ALTER Table to recreate it. Run a search for alter table in the BOL(books online).
If you deleted the database you can just restore the whole database from your last backup, when using the backup wizard use restore from device and most cases SQL Server will receate everything in that databse for you including permissions. Hope this helps.
|||Have you tried using DTS? (Data Transformation Services)
Try right clicking on the Table -> All Tasks -> Export Data
|||Do you mean in SQL Enteprise Manager? I connected to the db, navigatedto the table in question, right clicked. I just the standard IE options.How do I use DTS? I am running SQL Ent Man locally to access a remotedb. Basically, i just want to copy a table from the remote MS SQL dband either bring it to a local SQL (MSDE) db or excel, or .txt.
Cheers
|||Open the Query Analyzer and open the database in the object browser, right click on the table you need and click on script object as a new window then click on create and you have the create table statement you can execute locally to create the table. Hope thie helps.
Wednesday, February 15, 2012
Export some tables from one DB to another WITHOUT lost of primary key?
Hi,
i am using the enterprise manager of our sql server 2000. i want toexport some (not all) tables from one DB to another one with all thedata inside these tables AND the primary keys. if i do i only receivethe tables with the data but all primary keys and indexes will be lost.
how to do it?
thanks, tobi
Script the tables and indexes and execute them in the new database, and create a linked server between the two and do SELECT INSERT INTO. Hope this helps.|||You should be able to use the DTS Export Wizard. Right-click on a database, All Tasks, Export Data.
Select your source and target server/databases, and select "CopyObjects and Data" instead of the default "Copy Tables and Views".
De-select "Copy All Objects" and click the Select Objects button toselect the tables you want. The option to export primary keys isautomatically selected, but can be modified by de-selecting the "Usedefault options" checkbox.
|||AdamMachanic, thanks for your reply.
in 99% i'll get an error (translated by me from german ;):
"[microsoft][odbc sql server driver][sql server]the windows nt-user orthe -group "servername\username" where not found. please check thename."
whats wrong with this?
|||In the DTS Wizard, you need to uncheck 'Use default options', click Options and uncheck both 'Copy database users and roles' and 'Copy object level permissions'.
Unless the Users exist on both SQL Servers, this will generate errors. Objects such as PKs, FKs, Tables, SPs etc will usually copy fine, but Users will not.|||thanks!!! it worked finally!
|||hi,
it worked for a while...but now i have some big trouble with it.
if i do the same steps today:
-"CopyObjects and Data"
-De-select "Copy All Objects" and click the Select Objects button toselect the tables i want.
-uncheck 'Usedefault options', click Options and uncheck both 'Copy database usersand roles' and 'Copy object level permissions'.
all data will be overwritten on the destination DB. i tried thisseveral times. even in the overview (the last step) it only show"Tables" at the end of the overview, not the table-names to copy.
i get mad on this!
isnt there an option in the dts wizard to generate in sql scripts so ican check what it will do before executing it? (like mysql tools do)
|||yesterday again i tried to export some tables from the live DB to my development DB and again i had this problem. is there a known bug with enterprise manager? maybe someone could give me a step by step tut about how to do this. i get mad on this DTS stuff - in mysql this all is so easy.
i am using the enterprise manager of our sql server 2000. i want toexport some (not all) tables from one DB to another one with all thedata inside these tables AND the primary keys. if i do i only receivethe tables with the data but all primary keys and indexes will be lost.
how to do it?
thanks, tobi
Script the tables and indexes and execute them in the new database, and create a linked server between the two and do SELECT INSERT INTO. Hope this helps.|||You should be able to use the DTS Export Wizard. Right-click on a database, All Tasks, Export Data.
Select your source and target server/databases, and select "CopyObjects and Data" instead of the default "Copy Tables and Views".
De-select "Copy All Objects" and click the Select Objects button toselect the tables you want. The option to export primary keys isautomatically selected, but can be modified by de-selecting the "Usedefault options" checkbox.
|||AdamMachanic, thanks for your reply.
in 99% i'll get an error (translated by me from german ;):
"[microsoft][odbc sql server driver][sql server]the windows nt-user orthe -group "servername\username" where not found. please check thename."
whats wrong with this?
|||In the DTS Wizard, you need to uncheck 'Use default options', click Options and uncheck both 'Copy database users and roles' and 'Copy object level permissions'.
Unless the Users exist on both SQL Servers, this will generate errors. Objects such as PKs, FKs, Tables, SPs etc will usually copy fine, but Users will not.|||thanks!!! it worked finally!
|||hi,
it worked for a while...but now i have some big trouble with it.
if i do the same steps today:
-"CopyObjects and Data"
-De-select "Copy All Objects" and click the Select Objects button toselect the tables i want.
-uncheck 'Usedefault options', click Options and uncheck both 'Copy database usersand roles' and 'Copy object level permissions'.
all data will be overwritten on the destination DB. i tried thisseveral times. even in the overview (the last step) it only show"Tables" at the end of the overview, not the table-names to copy.
i get mad on this!
isnt there an option in the dts wizard to generate in sql scripts so ican check what it will do before executing it? (like mysql tools do)
|||yesterday again i tried to export some tables from the live DB to my development DB and again i had this problem. is there a known bug with enterprise manager? maybe someone could give me a step by step tut about how to do this. i get mad on this DTS stuff - in mysql this all is so easy.
Subscribe to:
Posts (Atom)