Exporting Report to Excel

I'm having a problem exporting a report to Excel. It only save the first 23
lines of the report. If I save it as .csv I get everything. Any ideas?What version are you on? If RS 2000 I suggesting installing SP2.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> I'm having a problem exporting a report to Excel. It only save the first
> 23
> lines of the report. If I save it as .csv I get everything. Any ideas?|||I am using RS 2000. I loaded the latest SP for RS's. I'm still having the
problem. It occurs on two different machines. I preview the report and click
Save and select Excel. It then only saves a few lines of the report or I get
an error "operation is not valid due to the current state of the object". I
have found a work around that works on both machines. After clicking Save,
when the window appears to select the the name to save as I click cancel. I
then go back and save it like you normally would and it works. The problem
seems to be a bug. It doesn't make sense, but it works. I hope this will help
others.
"Bruce L-C [MVP]" wrote:
> What version are you on? If RS 2000 I suggesting installing SP2.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> > I'm having a problem exporting a report to Excel. It only save the first
> > 23
> > lines of the report. If I save it as .csv I get everything. Any ideas?
>
>|||You say preview, does this mean you are doing this from development? Or is
this from the Report Manager? If it works from Report Manager and it is just
development then it sounds like you have a work around.
One thing, SP2 should be installed at both the server and update the Report
Designer on the development machines as well. Especially if you bypassed
SP1. SP1 definitely had to be installed in both places.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
>I am using RS 2000. I loaded the latest SP for RS's. I'm still having the
> problem. It occurs on two different machines. I preview the report and
> click
> Save and select Excel. It then only saves a few lines of the report or I
> get
> an error "operation is not valid due to the current state of the object".
> I
> have found a work around that works on both machines. After clicking Save,
> when the window appears to select the the name to save as I click cancel.
> I
> then go back and save it like you normally would and it works. The problem
> seems to be a bug. It doesn't make sense, but it works. I hope this will
> help
> others.
> "Bruce L-C [MVP]" wrote:
>> What version are you on? If RS 2000 I suggesting installing SP2.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
>> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
>> > I'm having a problem exporting a report to Excel. It only save the
>> > first
>> > 23
>> > lines of the report. If I save it as .csv I get everything. Any ideas?
>>|||I am working in development.
"Bruce L-C [MVP]" wrote:
> You say preview, does this mean you are doing this from development? Or is
> this from the Report Manager? If it works from Report Manager and it is just
> development then it sounds like you have a work around.
> One thing, SP2 should be installed at both the server and update the Report
> Designer on the development machines as well. Especially if you bypassed
> SP1. SP1 definitely had to be installed in both places.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
> >I am using RS 2000. I loaded the latest SP for RS's. I'm still having the
> > problem. It occurs on two different machines. I preview the report and
> > click
> > Save and select Excel. It then only saves a few lines of the report or I
> > get
> > an error "operation is not valid due to the current state of the object".
> > I
> > have found a work around that works on both machines. After clicking Save,
> > when the window appears to select the the name to save as I click cancel.
> > I
> > then go back and save it like you normally would and it works. The problem
> > seems to be a bug. It doesn't make sense, but it works. I hope this will
> > help
> > others.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What version are you on? If RS 2000 I suggesting installing SP2.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> >> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> >> > I'm having a problem exporting a report to Excel. It only save the
> >> > first
> >> > 23
> >> > lines of the report. If I save it as .csv I get everything. Any ideas?
> >>
> >>
> >>
>
>|||I have seen bugs in development but then are fine in production. Make sure
you have run SP2 with the report designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
news:A3FB195D-C9F7-44DD-B138-40E162194F94@.microsoft.com...
>I am working in development.
> "Bruce L-C [MVP]" wrote:
>> You say preview, does this mean you are doing this from development? Or
>> is
>> this from the Report Manager? If it works from Report Manager and it is
>> just
>> development then it sounds like you have a work around.
>> One thing, SP2 should be installed at both the server and update the
>> Report
>> Designer on the development machines as well. Especially if you bypassed
>> SP1. SP1 definitely had to be installed in both places.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
>> news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
>> >I am using RS 2000. I loaded the latest SP for RS's. I'm still having
>> >the
>> > problem. It occurs on two different machines. I preview the report and
>> > click
>> > Save and select Excel. It then only saves a few lines of the report or
>> > I
>> > get
>> > an error "operation is not valid due to the current state of the
>> > object".
>> > I
>> > have found a work around that works on both machines. After clicking
>> > Save,
>> > when the window appears to select the the name to save as I click
>> > cancel.
>> > I
>> > then go back and save it like you normally would and it works. The
>> > problem
>> > seems to be a bug. It doesn't make sense, but it works. I hope this
>> > will
>> > help
>> > others.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> What version are you on? If RS 2000 I suggesting installing SP2.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
>> >> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
>> >> > I'm having a problem exporting a report to Excel. It only save the
>> >> > first
>> >> > 23
>> >> > lines of the report. If I save it as .csv I get everything. Any
>> >> > ideas?
>> >>
>> >>
>> >>
>>|||Ok. Thanks.
"Bruce L-C [MVP]" wrote:
> I have seen bugs in development but then are fine in production. Make sure
> you have run SP2 with the report designer.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> news:A3FB195D-C9F7-44DD-B138-40E162194F94@.microsoft.com...
> >I am working in development.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You say preview, does this mean you are doing this from development? Or
> >> is
> >> this from the Report Manager? If it works from Report Manager and it is
> >> just
> >> development then it sounds like you have a work around.
> >>
> >> One thing, SP2 should be installed at both the server and update the
> >> Report
> >> Designer on the development machines as well. Especially if you bypassed
> >> SP1. SP1 definitely had to be installed in both places.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> >> news:13C4ED96-2ADD-4CD8-B6A9-A6C99E4CC1FC@.microsoft.com...
> >> >I am using RS 2000. I loaded the latest SP for RS's. I'm still having
> >> >the
> >> > problem. It occurs on two different machines. I preview the report and
> >> > click
> >> > Save and select Excel. It then only saves a few lines of the report or
> >> > I
> >> > get
> >> > an error "operation is not valid due to the current state of the
> >> > object".
> >> > I
> >> > have found a work around that works on both machines. After clicking
> >> > Save,
> >> > when the window appears to select the the name to save as I click
> >> > cancel.
> >> > I
> >> > then go back and save it like you normally would and it works. The
> >> > problem
> >> > seems to be a bug. It doesn't make sense, but it works. I hope this
> >> > will
> >> > help
> >> > others.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> What version are you on? If RS 2000 I suggesting installing SP2.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "sgtpback" <sgtpback@.discussions.microsoft.com> wrote in message
> >> >> news:DF99A924-1458-4588-8B6B-E94004AC526F@.microsoft.com...
> >> >> > I'm having a problem exporting a report to Excel. It only save the
> >> >> > first
> >> >> > 23
> >> >> > lines of the report. If I save it as .csv I get everything. Any
> >> >> > ideas?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Exporting Impromptu report as DAT File

I have question regarding exporting impromptu IMR reports as a DAT File. the problem is when trying to save impromptu (imr) Report in .Dat file format but its saving each field in the file with double quotes around it and without any spaces between the fields. Is there any way we can avoid saving the Dat file without double quotes around each field?

Appreciate your thoughts and comments.

RajDoes this relates to SQL Server?|||No. This is something we rae trying to do in Impromptu, COGNOS

Exporting data using T-SQL... something opposite of BULK INSERT.

Hello-
We have to export data as part of processing and save it as a flat file.
What developers did was to use xp_cmdshell and export data that way.
It solved that problem but now the account calling it has be a member of
System Administration Server Role... a security risk!
Is there any other way of exporting data... with limited rights?
Regards,
MZeeshan
Do it using an app external to SQL Server? Then all they need to be is
db_datareader and be able to execute the stored procedure that generates the
results.
Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
that anybody can do anything with it... they have to get to it first.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan
|||MZeeshan wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
Could you not set up a Text File data source and add it as a linked
server, then INSERT INTO ( SELECT FROM ) it?
|||Hi MZeeshan,
You might also want to setup a SQL Server Agent proxy account allows SQL
Server users who do not belong to the sysadmin fixed server role to execute
xp_cmdshell. The administrators can assign appropriate security permissions
to the proxy account. When xp_cmdshell is invoked by a user who is a member
of the sysadmin fixed server role, xp_cmdshell will be executed under the
security context in which the SQL Server service is running. When the user
is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
always executed under the security context of the Windows 9.x user who
started SQL Server.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSERT.
>thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==
>X-WBNR-Posting-Host: 208.250.29.8
>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
>Date: Thu, 24 Mar 2005 11:57:03 -0800
>Lines: 13
>Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hello-
>We have to export data as part of processing and save it as a flat file.
>What developers did was to use xp_cmdshell and export data that way.
>It solved that problem but now the account calling it has be a member of
>System Administration Server Role... a security risk!
>Is there any other way of exporting data... with limited rights?
>--
>Regards,
>MZeeshan
>
|||Have you considered using DTS ? Unless there are some really really complex
requirement in the export DTS will probably handle anything you need. You
can define a DTS job and allow a user account to run it and avoid the
security problem you described.
"MZeeshan" wrote:

> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan

Exporting data using T-SQL... something opposite of BULK INSERT.

Hello-
We have to export data as part of processing and save it as a flat file.
What developers did was to use xp_cmdshell and export data that way.
It solved that problem but now the account calling it has be a member of
System Administration Server Role... a security risk!
Is there any other way of exporting data... with limited rights?
--
Regards,
MZeeshanDo it using an app external to SQL Server? Then all they need to be is
db_datareader and be able to execute the stored procedure that generates the
results.
Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
that anybody can do anything with it... they have to get to it first.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan|||"Aaron [SQL Server MVP]" wrote:
> Do it using an app external to SQL Server? Then all they need to be is
> db_datareader and be able to execute the stored procedure that generates the
> results.
>
Would you elaborate on this? It seems interesting!
We have nested SPs and one in the inner level has this functionality (so
that it can be called anytime any data has to be exported).
Its a 3-tier application with data requests being generated from app. server
level (calling the outer stored procedure)?
> Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
> that anybody can do anything with it... they have to get to it first.
>
I wish I could... but that's out of question. Because its an old code
(before I joined here) and the business unit is not willing to let that
functionality... unless I have a better plan!
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> > Hello-
> >
> > We have to export data as part of processing and save it as a flat file.
> > What developers did was to use xp_cmdshell and export data that way.
> >
> > It solved that problem but now the account calling it has be a member of
> > System Administration Server Role... a security risk!
> >
> > Is there any other way of exporting data... with limited rights?
> >
> > --
> > Regards,
> > MZeeshan
>
>|||MZeeshan wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
Could you not set up a Text File data source and add it as a linked
server, then INSERT INTO ( SELECT FROM ) it?|||Can you explain with some example?
"Ryan Walberg [MCSD]" wrote:
> MZeeshan wrote:
> > Hello-
> >
> > We have to export data as part of processing and save it as a flat file.
> > What developers did was to use xp_cmdshell and export data that way.
> >
> > It solved that problem but now the account calling it has be a member of
> > System Administration Server Role... a security risk!
> >
> > Is there any other way of exporting data... with limited rights?
> Could you not set up a Text File data source and add it as a linked
> server, then INSERT INTO ( SELECT FROM ) it?
>|||Hi MZeeshan,
You might also want to setup a SQL Server Agent proxy account allows SQL
Server users who do not belong to the sysadmin fixed server role to execute
xp_cmdshell. The administrators can assign appropriate security permissions
to the proxy account. When xp_cmdshell is invoked by a user who is a member
of the sysadmin fixed server role, xp_cmdshell will be executed under the
security context in which the SQL Server service is running. When the user
is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
always executed under the security context of the Windows 9.x user who
started SQL Server.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSERT.
>thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==>X-WBNR-Posting-Host: 208.250.29.8
>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
>Date: Thu, 24 Mar 2005 11:57:03 -0800
>Lines: 13
>Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hello-
>We have to export data as part of processing and save it as a flat file.
>What developers did was to use xp_cmdshell and export data that way.
>It solved that problem but now the account calling it has be a member of
>System Administration Server Role... a security risk!
>Is there any other way of exporting data... with limited rights?
>--
>Regards,
>MZeeshan
>|||Is it also true for SQL Server on Windows 2003 Server? Can you also give some
examples. I am also searching on the net and BOL explanation was little bit
cryptic.
"William Wang[MSFT]" wrote:
> Hi MZeeshan,
> You might also want to setup a SQL Server Agent proxy account allows SQL
> Server users who do not belong to the sysadmin fixed server role to execute
> xp_cmdshell. The administrators can assign appropriate security permissions
> to the proxy account. When xp_cmdshell is invoked by a user who is a member
> of the sysadmin fixed server role, xp_cmdshell will be executed under the
> security context in which the SQL Server service is running. When the user
> is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
> Server Agent proxy account, which is specified using
> xp_sqlagent_proxy_account. If the proxy account is not available,
> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
> always executed under the security context of the Windows 9.x user who
> started SQL Server.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
> INSERT.
> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==> >X-WBNR-Posting-Host: 208.250.29.8
> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> >Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
> >Date: Thu, 24 Mar 2005 11:57:03 -0800
> >Lines: 13
> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >Path: TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Hello-
> >
> >We have to export data as part of processing and save it as a flat file.
> >What developers did was to use xp_cmdshell and export data that way.
> >
> >It solved that problem but now the account calling it has be a member of
> >System Administration Server Role... a security risk!
> >
> >Is there any other way of exporting data... with limited rights?
> >
> >--
> >Regards,
> >MZeeshan
> >
>|||That was really helpful. Thank you!!!
"William Wang[MSFT]" wrote:
> Hi MZeeshan,
> You might also want to setup a SQL Server Agent proxy account allows SQL
> Server users who do not belong to the sysadmin fixed server role to execute
> xp_cmdshell. The administrators can assign appropriate security permissions
> to the proxy account. When xp_cmdshell is invoked by a user who is a member
> of the sysadmin fixed server role, xp_cmdshell will be executed under the
> security context in which the SQL Server service is running. When the user
> is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
> Server Agent proxy account, which is specified using
> xp_sqlagent_proxy_account. If the proxy account is not available,
> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
> always executed under the security context of the Windows 9.x user who
> started SQL Server.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
> INSERT.
> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==> >X-WBNR-Posting-Host: 208.250.29.8
> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> >Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
> >Date: Thu, 24 Mar 2005 11:57:03 -0800
> >Lines: 13
> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >Path: TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Hello-
> >
> >We have to export data as part of processing and save it as a flat file.
> >What developers did was to use xp_cmdshell and export data that way.
> >
> >It solved that problem but now the account calling it has be a member of
> >System Administration Server Role... a security risk!
> >
> >Is there any other way of exporting data... with limited rights?
> >
> >--
> >Regards,
> >MZeeshan
> >
>|||To setup the Server Agent proxy account, follow these steps:
1. Start Enterprise Manager.
2. Expand a server group and expand your SQL Server.
3. Expand the Management folder and right-click SQL Server Agent, select
Properties.
4. Click on the Job System tab. Under the "Non-Sysadmin job step proxy
account" section, clear the "Only users with SysAdmin privileges can
execute CmdExec and ActiveScripting job steps" check box, and click the
"Reset Proxy Account" button.
5. Type the user name, password, and domain of the user account to be used
when non-sysadmin executes xp_cmdshell or by SQL Server Agent running jobs
owned by users who are not system administrators.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSER
>thread-index: AcUxCPfsCMB02TOwRRyHt65H99UOTw==>X-WBNR-Posting-Host: 208.250.29.8
>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>References: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
<ZacGZKQMFHA.1016@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exporting data using T-SQL... something opposite of BULK INSER
>Date: Thu, 24 Mar 2005 23:05:03 -0800
>Lines: 72
>Message-ID: <662BCC4F-0854-44B7-903C-BE71DB1C19F3@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383194
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Is it also true for SQL Server on Windows 2003 Server? Can you also give
some
>examples. I am also searching on the net and BOL explanation was little
bit
>cryptic.
>"William Wang[MSFT]" wrote:
>> Hi MZeeshan,
>> You might also want to setup a SQL Server Agent proxy account allows SQL
>> Server users who do not belong to the sysadmin fixed server role to
execute
>> xp_cmdshell. The administrators can assign appropriate security
permissions
>> to the proxy account. When xp_cmdshell is invoked by a user who is a
member
>> of the sysadmin fixed server role, xp_cmdshell will be executed under
the
>> security context in which the SQL Server service is running. When the
user
>> is not a member of the sysadmin group, xp_cmdshell will impersonate the
SQL
>> Server Agent proxy account, which is specified using
>> xp_sqlagent_proxy_account. If the proxy account is not available,
>> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0
and
>> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell
is
>> always executed under the security context of the Windows 9.x user who
>> started SQL Server.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> --
>> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
>> INSERT.
>> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==>> >X-WBNR-Posting-Host: 208.250.29.8
>> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>> >Subject: Exporting data using T-SQL... something opposite of BULK
INSERT.
>> >Date: Thu, 24 Mar 2005 11:57:03 -0800
>> >Lines: 13
>> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >Path: TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >Hello-
>> >
>> >We have to export data as part of processing and save it as a flat
file.
>> >What developers did was to use xp_cmdshell and export data that way.
>> >
>> >It solved that problem but now the account calling it has be a member
of
>> >System Administration Server Role... a security risk!
>> >
>> >Is there any other way of exporting data... with limited rights?
>> >
>> >--
>> >Regards,
>> >MZeeshan
>> >
>>
>|||Sorry that I missed your first question.
>Is it also true for SQL Server on Windows 2003 Server?
Yes, it's true.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Newsgroups: microsoft.public.sqlserver.server
>From: v-rxwang@.online.microsoft.com (William Wang[MSFT])
>Organization: Microsoft
>Date: Fri, 25 Mar 2005 07:50:34 GMT
>Subject: RE: Exporting data using T-SQL... something opposite of BULK INSER
>X-Tomcat-NG: microsoft.public.sqlserver.server
>MIME-Version: 1.0
>Content-Type: text/plain
>Content-Transfer-Encoding: 7bit
>To setup the Server Agent proxy account, follow these steps:
>1. Start Enterprise Manager.
>2. Expand a server group and expand your SQL Server.
>3. Expand the Management folder and right-click SQL Server Agent, select
>Properties.
>4. Click on the Job System tab. Under the "Non-Sysadmin job step proxy
>account" section, clear the "Only users with SysAdmin privileges can
>execute CmdExec and ActiveScripting job steps" check box, and click the
>"Reset Proxy Account" button.
>5. Type the user name, password, and domain of the user account to be used
>when non-sysadmin executes xp_cmdshell or by SQL Server Agent running jobs
>owned by users who are not system administrators.
>Sincerely,
>William Wang
>Microsoft Online Partner Support
>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>=====================================================>Business-Critical Phone Support (BCPS) provides you with technical phone
>support at no charge during critical LAN outages or "business down"
>situations. This benefit is available 24 hours a day, 7 days a week to all
>Microsoft technology partners in the United States and Canada.
>This and other support options are available here:
>BCPS:
>https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
>Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
>If you are outside the United States, please visit our International
>Support page:
>http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
>=====================================================>This posting is provided "AS IS" with no warranties, and confers no rights.
>--
>>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
>INSER
>>thread-index: AcUxCPfsCMB02TOwRRyHt65H99UOTw==>>X-WBNR-Posting-Host: 208.250.29.8
>>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>>References: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
><ZacGZKQMFHA.1016@.TK2MSFTNGXA03.phx.gbl>
>>Subject: RE: Exporting data using T-SQL... something opposite of BULK
INSER
>>Date: Thu, 24 Mar 2005 23:05:03 -0800
>>Lines: 72
>>Message-ID: <662BCC4F-0854-44B7-903C-BE71DB1C19F3@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA03.phx.gbl
>>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383194
>>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Is it also true for SQL Server on Windows 2003 Server? Can you also give
>some
>>examples. I am also searching on the net and BOL explanation was little
>bit
>>cryptic.
>>"William Wang[MSFT]" wrote:
>> Hi MZeeshan,
>> You might also want to setup a SQL Server Agent proxy account allows
SQL
>> Server users who do not belong to the sysadmin fixed server role to
>execute
>> xp_cmdshell. The administrators can assign appropriate security
>permissions
>> to the proxy account. When xp_cmdshell is invoked by a user who is a
>member
>> of the sysadmin fixed server role, xp_cmdshell will be executed under
>the
>> security context in which the SQL Server service is running. When the
>user
>> is not a member of the sysadmin group, xp_cmdshell will impersonate the
>SQL
>> Server Agent proxy account, which is specified using
>> xp_sqlagent_proxy_account. If the proxy account is not available,
>> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0
>and
>> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell
>is
>> always executed under the security context of the Windows 9.x user who
>> started SQL Server.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
>rights.
>> --
>> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
>> INSERT.
>> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==>> >X-WBNR-Posting-Host: 208.250.29.8
>> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>> >Subject: Exporting data using T-SQL... something opposite of BULK
>INSERT.
>> >Date: Thu, 24 Mar 2005 11:57:03 -0800
>> >Lines: 13
>> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >Path: TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >Hello-
>> >
>> >We have to export data as part of processing and save it as a flat
>file.
>> >What developers did was to use xp_cmdshell and export data that way.
>> >
>> >It solved that problem but now the account calling it has be a member
>of
>> >System Administration Server Role... a security risk!
>> >
>> >Is there any other way of exporting data... with limited rights?
>> >
>> >--
>> >Regards,
>> >MZeeshan
>> >
>>
>|||Have you considered using DTS ? Unless there are some really really complex
requirement in the export DTS will probably handle anything you need. You
can define a DTS job and allow a user account to run it and avoid the
security problem you described.
"MZeeshan" wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshansql

Exporting data using T-SQL... something opposite of BULK INSERT.

Hello-
We have to export data as part of processing and save it as a flat file.
What developers did was to use xp_cmdshell and export data that way.
It solved that problem but now the account calling it has be a member of
System Administration Server Role... a security risk!
Is there any other way of exporting data... with limited rights?
Regards,
MZeeshanDo it using an app external to SQL Server? Then all they need to be is
db_datareader and be able to execute the stored procedure that generates the
results.
Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
that anybody can do anything with it... they have to get to it first.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan|||MZeeshan wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
Could you not set up a Text File data source and add it as a linked
server, then INSERT INTO ( SELECT FROM ) it?|||Hi MZeeshan,
You might also want to setup a SQL Server Agent proxy account allows SQL
Server users who do not belong to the sysadmin fixed server role to execute
xp_cmdshell. The administrators can assign appropriate security permissions
to the proxy account. When xp_cmdshell is invoked by a user who is a member
of the sysadmin fixed server role, xp_cmdshell will be executed under the
security context in which the SQL Server service is running. When the user
is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
always executed under the security context of the Windows 9.x user who
started SQL Server.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSERT.
>thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==
>X-WBNR-Posting-Host: 208.250.29.8
>From: "examnotes" <mzeeshan@.community.nospam>
>Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
>Date: Thu, 24 Mar 2005 11:57:03 -0800
>Lines: 13
>Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hello-
>We have to export data as part of processing and save it as a flat file.
>What developers did was to use xp_cmdshell and export data that way.
>It solved that problem but now the account calling it has be a member of
>System Administration Server Role... a security risk!
>Is there any other way of exporting data... with limited rights?
>--
>Regards,
>MZeeshan
>|||Have you considered using DTS ? Unless there are some really really complex
requirement in the export DTS will probably handle anything you need. You
can define a DTS job and allow a user account to run it and avoid the
security problem you described.
"MZeeshan" wrote:

> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan

Exportin table in xml file

Hello everyone,
I need to save data into an xml file. Now I do this with Visual Basic using
the ado-recordset method 'Save'. I'd like to do this task with a sql server
stored procedure.
Does anyone know if it is possible, and how?
--
Thank you everyoone,
Walker BohYou could do something like
EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
' -od:\FTPresentation\imagefmt.fmt'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> Hello everyone,
> I need to save data into an xml file. Now I do this with Visual Basic
using
> the ado-recordset method 'Save'. I'd like to do this task with a sql
server
> stored procedure.
> Does anyone know if it is possible, and how?
> --
> Thank you everyoone,
> Walker Boh|||It doesn't work as I need. It doesn't create an xml file that I can open wit
h
IE for example, nor it has tha same structure the recordset methis save
create.
thank you anyway
"Wayne Snyder" wrote:

> You could do something like
> EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
> ' -od:\FTPresentation\imagefmt.fmt'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
> news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> using
> server
>
>

Exportin table in xml file

Hello everyone,
I need to save data into an xml file. Now I do this with Visual Basic using
the ado-recordset method 'Save'. I'd like to do this task with a sql server
stored procedure.
Does anyone know if it is possible, and how?
Thank you everyoone,
Walker Boh
You could do something like
EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
' -od:\FTPresentation\imagefmt.fmt'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> Hello everyone,
> I need to save data into an xml file. Now I do this with Visual Basic
using
> the ado-recordset method 'Save'. I'd like to do this task with a sql
server
> stored procedure.
> Does anyone know if it is possible, and how?
> --
> Thank you everyoone,
> Walker Boh
|||It doesn't work as I need. It doesn't create an xml file that I can open with
IE for example, nor it has tha same structure the recordset methis save
create.
thank you anyway
"Wayne Snyder" wrote:

> You could do something like
> EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
> ' -od:\FTPresentation\imagefmt.fmt'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
> news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> using
> server
>
>

Exportin table in xml file

Hello everyone,
I need to save data into an xml file. Now I do this with Visual Basic using
the ado-recordset method 'Save'. I'd like to do this task with a sql server
stored procedure.
Does anyone know if it is possible, and how?
--
Thank you everyoone,
Walker BohYou could do something like
EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
' -od:\FTPresentation\imagefmt.fmt'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> Hello everyone,
> I need to save data into an xml file. Now I do this with Visual Basic
using
> the ado-recordset method 'Save'. I'd like to do this task with a sql
server
> stored procedure.
> Does anyone know if it is possible, and how?
> --
> Thank you everyoone,
> Walker Boh|||It doesn't work as I need. It doesn't create an xml file that I can open with
IE for example, nor it has tha same structure the recordset methis save
create.
thank you anyway
"Wayne Snyder" wrote:
> You could do something like
> EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
> ' -od:\FTPresentation\imagefmt.fmt'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
> news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> > Hello everyone,
> >
> > I need to save data into an xml file. Now I do this with Visual Basic
> using
> > the ado-recordset method 'Save'. I'd like to do this task with a sql
> server
> > stored procedure.
> > Does anyone know if it is possible, and how?
> > --
> > Thank you everyoone,
> > Walker Boh
>
>

Export to PDF error

Some users get the following error when exporting to PDF:
There was an error opening this document. The file does not exist.
They can save the document locally though and open it. We're all using the
same version of Acrobat Reader (6.0.1).
Has anyone experienced this before?
Thanks,
Jason MacKenzieJason, I have aboslutely experienced this same issue and it is a major
problem. Can someone from Microsoft please comment on this issue. We have
various version of .pdf in the office. 5.0 works fine it seems for Reporting
Services rendering to .pdf but we have a requirement for employees to see
their ADP paystubs in 6.0. Adober .pdf's load fine in the browser when
pulled up directly from other sources which leads me to believe this is a
driver issue on the reporting services side?
"Jason MacKenzie" wrote:
> Some users get the following error when exporting to PDF:
> There was an error opening this document. The file does not exist.
> They can save the document locally though and open it. We're all using the
> same version of Acrobat Reader (6.0.1).
> Has anyone experienced this before?
> Thanks,
> Jason MacKenzie
>
>|||Here's an additional caveat. This seems to be happening primarily on systems
with Adobe Acrobt 6.0 Professional installed?
"Scott" wrote:
> Jason, I have aboslutely experienced this same issue and it is a major
> problem. Can someone from Microsoft please comment on this issue. We have
> various version of .pdf in the office. 5.0 works fine it seems for Reporting
> Services rendering to .pdf but we have a requirement for employees to see
> their ADP paystubs in 6.0. Adober .pdf's load fine in the browser when
> pulled up directly from other sources which leads me to believe this is a
> driver issue on the reporting services side?
> "Jason MacKenzie" wrote:
> > Some users get the following error when exporting to PDF:
> >
> > There was an error opening this document. The file does not exist.
> >
> > They can save the document locally though and open it. We're all using the
> > same version of Acrobat Reader (6.0.1).
> >
> > Has anyone experienced this before?
> >
> > Thanks,
> >
> > Jason MacKenzie
> >
> >
> >

Export to Excel--customizing the save option

Hi,
Am creating reports using Sql Reporting services. Suppose am creating a
report for all the users and the file name is "User Report" and am passing
the "username" by parametervalue. while exporting it to excel, a dialogue box
appears which gives an option of 'open','save','cancel' with "user
Report.xls". but what if i have to save it specific to the paramvalue i.e-- "
<username> UserReport.xls"'I dont think you can do that just from the report Manager.
You will have to write your own application to display the
reports and the provide a export to excel option and then
render the report in excel format(using the Web Services
Render method) and save the excel file from code with the
desired filename.
>--Original Message--
>Hi,
>Am creating reports using Sql Reporting services. Suppose
am creating a
>report for all the users and the file name is "User
Report" and am passing
>the "username" by parametervalue. while exporting it to
excel, a dialogue box
>appears which gives an option of 'open','save','cancel'
with "user
>Report.xls". but what if i have to save it specific to
the paramvalue i.e-- "
><username> UserReport.xls"'
>
>.
>

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?
>>

Export Stored Procedure SQL Script

We usually keep an "installation " table , when we save out stored
procedures they are ordered by the stated execution order. (customised
script)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
> I just use the 'Generate SQL Script' from EM to export stored procedure.
> How do I export the Stored Procedure SQL Script in to a file so in an
order
> so that they can be created successfully ?
> I got an error when execute the scripts:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp1'. The stored procedure will still be
> created.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp2'. The stored procedure will still be
> created.
> ...............
> ...............
> How do I get around that ?
>I just use the 'Generate SQL Script' from EM to export stored procedure.
How do I export the Stored Procedure SQL Script in to a file so in an order
so that they can be created successfully ?
I got an error when execute the scripts:
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp1'. The stored procedure will still be
created.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp2'. The stored procedure will still be
created.
...............
...............
How do I get around that ?|||We usually keep an "installation " table , when we save out stored
procedures they are ordered by the stated execution order. (customised
script)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
> I just use the 'Generate SQL Script' from EM to export stored procedure.
> How do I export the Stored Procedure SQL Script in to a file so in an
order
> so that they can be created successfully ?
> I got an error when execute the scripts:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp1'. The stored procedure will still be
> created.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp2'. The stored procedure will still be
> created.
> ...............
> ...............
> How do I get around that ?
>