Scenario:
- W2k server sp4 / sql server 2k sp4 box.
- firewall with only port 80 opened.
I have to:
Query data from remote server to export into local server.
I would like to do it via ASP/IIS in one step.
How can I do it?
Regards.
Gibe si fatto passare perch Rossi dietro come una purga, prima o
poi una cagata te la fa fare...!Hi
I am not an ASP expert and you may get a better reply in a internet
programming newsgroup.
I don't know of any way to do this in one step! Some componenets will let
you save the contents to file (such as an excel spreadsheet) but it is a two
stage process to select/insert the data and then save it. Other two process
would be to have scheduled jobs or DTS packages to create the files and then
allow the user to download them.
John
"Fabri" wrote:
> Scenario:
>
> - W2k server sp4 / sql server 2k sp4 box.
> - firewall with only port 80 opened.
>
> I have to:
> Query data from remote server to export into local server.
> I would like to do it via ASP/IIS in one step.
> How can I do it?
> Regards.
> --
> Gibe si è fatto passare perchè Rossi dietro è come una purga, prima o
> poi una cagata te la fa fare...!
>
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Wednesday, March 21, 2012
Sunday, February 26, 2012
Export to Excel failed with System.OutOfMemoryException
Server: SQL Server 2000 Ent running on Win2k AS, Ent Edition of Reporting
Services + SP1
my workstation: Win2k SP4, Microsoft Development Environment v7.1.3088,
Reporting Services Ent Ed + SP1.
I am trying to export 55,804 rows to Excel. The table that I am exporting
from has 11 columns.
-- Columns within >results< -- DataType Length
--- -- --
Source varchar 2
ClaimNumber varchar 17
MarketLoc varchar 7
Description varchar 50
SomeDate datetime 8
DetailNo smallint 2
ChargeCode varchar 1
UnitCount smallint 2
ChargedDollars decimal 5
AllowedDollars decimal 5
NetPaidDollars decimal 5
Because I need to dump this data for 17 different market locations ranging
in rowcounts of 600 rows to 56,000 rows I figured that I would just create a
report within Reporting Services and query the results table (228,825 rows)
table for each of the 17 market locations. I would export each market to an
Excel file and then I would copy/paste the worksheets into one file that I
can send to the end user. This would be easier than selecting the
information within Query Analyzer, saving to a file, importing each file,
formatting each column and repeating the process for each of the 17 groups
of data.
I started out with a small data set (1500 rows) as a proof of concept so
that I knew the process would work. When I had the report formatted
correctly I successfully exported the data to Excel. Then I moved on to one
of the larger groups (55,804 rows). This is where I had the problems.
I previewed the report within Visual Studio. Total page count 1241. The
rdl.data file is 12MB. I clicked on the disk icon on the toolbar and chose
to export the report to Excel. I have 768MB of RAM in my machine and the
memory usage continued to rise until it reached 1GB. This process took over
20 minutes. At the end of it all I received a dialog box indicating a
System.OutOfMemoryException was thrown. What would happen if I tried
running this on the server? I am guessing that it would consume lots of CPU
cycles and eat up lots of memory. Would it finish, or would it fail? I am
afraid to try :)
Why is the .rdl.data only 12MB yet over 1GB was consumed while converting to
Excel? When I save the results to a [text] file within Query Analyzer the
file is 12MB. I could import into Excel, but then I have to import x files
and format y columns x times.
This should be fairly easy to repro. Does anyone from Microsoft want to
take a look at it? Does anyone else have any ideas?
--
KeithKeith,
The only recommendation I have at this time is to increase the RAM on the
machine you are using to export to excel. I do not have a specific
recommendation, but I would suggest a minimum of 1GB of RAM. The SQL Server
2000 Reporting Services team is looking at addressing this issue in upcoming
service packs and releases.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eo8Ko5qXEHA.2868@.TK2MSFTNGP09.phx.gbl...
> Server: SQL Server 2000 Ent running on Win2k AS, Ent Edition of Reporting
> Services + SP1
> my workstation: Win2k SP4, Microsoft Development Environment v7.1.3088,
> Reporting Services Ent Ed + SP1.
> I am trying to export 55,804 rows to Excel. The table that I am exporting
> from has 11 columns.
> -- Columns within >results< -- DataType Length
> --- -- --
> Source varchar 2
> ClaimNumber varchar 17
> MarketLoc varchar 7
> Description varchar 50
> SomeDate datetime 8
> DetailNo smallint 2
> ChargeCode varchar 1
> UnitCount smallint 2
> ChargedDollars decimal 5
> AllowedDollars decimal 5
> NetPaidDollars decimal 5
> Because I need to dump this data for 17 different market locations ranging
> in rowcounts of 600 rows to 56,000 rows I figured that I would just create
a
> report within Reporting Services and query the results table (228,825
rows)
> table for each of the 17 market locations. I would export each market to
an
> Excel file and then I would copy/paste the worksheets into one file that I
> can send to the end user. This would be easier than selecting the
> information within Query Analyzer, saving to a file, importing each file,
> formatting each column and repeating the process for each of the 17 groups
> of data.
> I started out with a small data set (1500 rows) as a proof of concept so
> that I knew the process would work. When I had the report formatted
> correctly I successfully exported the data to Excel. Then I moved on to
one
> of the larger groups (55,804 rows). This is where I had the problems.
> I previewed the report within Visual Studio. Total page count 1241. The
> rdl.data file is 12MB. I clicked on the disk icon on the toolbar and
chose
> to export the report to Excel. I have 768MB of RAM in my machine and the
> memory usage continued to rise until it reached 1GB. This process took
over
> 20 minutes. At the end of it all I received a dialog box indicating a
> System.OutOfMemoryException was thrown. What would happen if I tried
> running this on the server? I am guessing that it would consume lots of
CPU
> cycles and eat up lots of memory. Would it finish, or would it fail? I
am
> afraid to try :)
> Why is the .rdl.data only 12MB yet over 1GB was consumed while converting
to
> Excel? When I save the results to a [text] file within Query Analyzer the
> file is 12MB. I could import into Excel, but then I have to import x
files
> and format y columns x times.
> This should be fairly easy to repro. Does anyone from Microsoft want to
> take a look at it? Does anyone else have any ideas?
> --
> Keith
>|||Bruce, Thanks for your reply. It is unfortunate that 768MB is not enough
memory to export ~12MB of data to Excel. I am glad that Microsoft enhanced
the versions of Excel that are supported with RS SP1. Now I guess I will
have to wait for SP2 (or SQL Server 2005) for memory optimization.
In the end I ended up getting the data into Excel via the "Get External Data
| from a database query" menu. Yeah, I had to do it 17 times, but it
worked. That method saved me the pain of having to perform a "data | text
to columns" and format each column..
Keep up the good work!
--
Keith
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:OmRdOCsXEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Keith,
> The only recommendation I have at this time is to increase the RAM on the
> machine you are using to export to excel. I do not have a specific
> recommendation, but I would suggest a minimum of 1GB of RAM. The SQL
Server
> 2000 Reporting Services team is looking at addressing this issue in
upcoming
> service packs and releases.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eo8Ko5qXEHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Server: SQL Server 2000 Ent running on Win2k AS, Ent Edition of
Reporting
> > Services + SP1
> > my workstation: Win2k SP4, Microsoft Development Environment v7.1.3088,
> > Reporting Services Ent Ed + SP1.
> >
> > I am trying to export 55,804 rows to Excel. The table that I am
exporting
> > from has 11 columns.
> >
> > -- Columns within >results< -- DataType Length
> > --- -- --
> > Source varchar 2
> > ClaimNumber varchar 17
> > MarketLoc varchar 7
> > Description varchar 50
> > SomeDate datetime 8
> > DetailNo smallint 2
> > ChargeCode varchar 1
> > UnitCount smallint 2
> > ChargedDollars decimal 5
> > AllowedDollars decimal 5
> > NetPaidDollars decimal 5
> >
> > Because I need to dump this data for 17 different market locations
ranging
> > in rowcounts of 600 rows to 56,000 rows I figured that I would just
create
> a
> > report within Reporting Services and query the results table (228,825
> rows)
> > table for each of the 17 market locations. I would export each market
to
> an
> > Excel file and then I would copy/paste the worksheets into one file that
I
> > can send to the end user. This would be easier than selecting the
> > information within Query Analyzer, saving to a file, importing each
file,
> > formatting each column and repeating the process for each of the 17
groups
> > of data.
> >
> > I started out with a small data set (1500 rows) as a proof of concept so
> > that I knew the process would work. When I had the report formatted
> > correctly I successfully exported the data to Excel. Then I moved on to
> one
> > of the larger groups (55,804 rows). This is where I had the problems.
> >
> > I previewed the report within Visual Studio. Total page count 1241.
The
> > rdl.data file is 12MB. I clicked on the disk icon on the toolbar and
> chose
> > to export the report to Excel. I have 768MB of RAM in my machine and
the
> > memory usage continued to rise until it reached 1GB. This process took
> over
> > 20 minutes. At the end of it all I received a dialog box indicating a
> > System.OutOfMemoryException was thrown. What would happen if I tried
> > running this on the server? I am guessing that it would consume lots of
> CPU
> > cycles and eat up lots of memory. Would it finish, or would it fail? I
> am
> > afraid to try :)
> >
> > Why is the .rdl.data only 12MB yet over 1GB was consumed while
converting
> to
> > Excel? When I save the results to a [text] file within Query Analyzer
the
> > file is 12MB. I could import into Excel, but then I have to import x
> files
> > and format y columns x times.
> >
> > This should be fairly easy to repro. Does anyone from Microsoft want to
> > take a look at it? Does anyone else have any ideas?
> >
> > --
> > Keith
> >
> >
>
Services + SP1
my workstation: Win2k SP4, Microsoft Development Environment v7.1.3088,
Reporting Services Ent Ed + SP1.
I am trying to export 55,804 rows to Excel. The table that I am exporting
from has 11 columns.
-- Columns within >results< -- DataType Length
--- -- --
Source varchar 2
ClaimNumber varchar 17
MarketLoc varchar 7
Description varchar 50
SomeDate datetime 8
DetailNo smallint 2
ChargeCode varchar 1
UnitCount smallint 2
ChargedDollars decimal 5
AllowedDollars decimal 5
NetPaidDollars decimal 5
Because I need to dump this data for 17 different market locations ranging
in rowcounts of 600 rows to 56,000 rows I figured that I would just create a
report within Reporting Services and query the results table (228,825 rows)
table for each of the 17 market locations. I would export each market to an
Excel file and then I would copy/paste the worksheets into one file that I
can send to the end user. This would be easier than selecting the
information within Query Analyzer, saving to a file, importing each file,
formatting each column and repeating the process for each of the 17 groups
of data.
I started out with a small data set (1500 rows) as a proof of concept so
that I knew the process would work. When I had the report formatted
correctly I successfully exported the data to Excel. Then I moved on to one
of the larger groups (55,804 rows). This is where I had the problems.
I previewed the report within Visual Studio. Total page count 1241. The
rdl.data file is 12MB. I clicked on the disk icon on the toolbar and chose
to export the report to Excel. I have 768MB of RAM in my machine and the
memory usage continued to rise until it reached 1GB. This process took over
20 minutes. At the end of it all I received a dialog box indicating a
System.OutOfMemoryException was thrown. What would happen if I tried
running this on the server? I am guessing that it would consume lots of CPU
cycles and eat up lots of memory. Would it finish, or would it fail? I am
afraid to try :)
Why is the .rdl.data only 12MB yet over 1GB was consumed while converting to
Excel? When I save the results to a [text] file within Query Analyzer the
file is 12MB. I could import into Excel, but then I have to import x files
and format y columns x times.
This should be fairly easy to repro. Does anyone from Microsoft want to
take a look at it? Does anyone else have any ideas?
--
KeithKeith,
The only recommendation I have at this time is to increase the RAM on the
machine you are using to export to excel. I do not have a specific
recommendation, but I would suggest a minimum of 1GB of RAM. The SQL Server
2000 Reporting Services team is looking at addressing this issue in upcoming
service packs and releases.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eo8Ko5qXEHA.2868@.TK2MSFTNGP09.phx.gbl...
> Server: SQL Server 2000 Ent running on Win2k AS, Ent Edition of Reporting
> Services + SP1
> my workstation: Win2k SP4, Microsoft Development Environment v7.1.3088,
> Reporting Services Ent Ed + SP1.
> I am trying to export 55,804 rows to Excel. The table that I am exporting
> from has 11 columns.
> -- Columns within >results< -- DataType Length
> --- -- --
> Source varchar 2
> ClaimNumber varchar 17
> MarketLoc varchar 7
> Description varchar 50
> SomeDate datetime 8
> DetailNo smallint 2
> ChargeCode varchar 1
> UnitCount smallint 2
> ChargedDollars decimal 5
> AllowedDollars decimal 5
> NetPaidDollars decimal 5
> Because I need to dump this data for 17 different market locations ranging
> in rowcounts of 600 rows to 56,000 rows I figured that I would just create
a
> report within Reporting Services and query the results table (228,825
rows)
> table for each of the 17 market locations. I would export each market to
an
> Excel file and then I would copy/paste the worksheets into one file that I
> can send to the end user. This would be easier than selecting the
> information within Query Analyzer, saving to a file, importing each file,
> formatting each column and repeating the process for each of the 17 groups
> of data.
> I started out with a small data set (1500 rows) as a proof of concept so
> that I knew the process would work. When I had the report formatted
> correctly I successfully exported the data to Excel. Then I moved on to
one
> of the larger groups (55,804 rows). This is where I had the problems.
> I previewed the report within Visual Studio. Total page count 1241. The
> rdl.data file is 12MB. I clicked on the disk icon on the toolbar and
chose
> to export the report to Excel. I have 768MB of RAM in my machine and the
> memory usage continued to rise until it reached 1GB. This process took
over
> 20 minutes. At the end of it all I received a dialog box indicating a
> System.OutOfMemoryException was thrown. What would happen if I tried
> running this on the server? I am guessing that it would consume lots of
CPU
> cycles and eat up lots of memory. Would it finish, or would it fail? I
am
> afraid to try :)
> Why is the .rdl.data only 12MB yet over 1GB was consumed while converting
to
> Excel? When I save the results to a [text] file within Query Analyzer the
> file is 12MB. I could import into Excel, but then I have to import x
files
> and format y columns x times.
> This should be fairly easy to repro. Does anyone from Microsoft want to
> take a look at it? Does anyone else have any ideas?
> --
> Keith
>|||Bruce, Thanks for your reply. It is unfortunate that 768MB is not enough
memory to export ~12MB of data to Excel. I am glad that Microsoft enhanced
the versions of Excel that are supported with RS SP1. Now I guess I will
have to wait for SP2 (or SQL Server 2005) for memory optimization.
In the end I ended up getting the data into Excel via the "Get External Data
| from a database query" menu. Yeah, I had to do it 17 times, but it
worked. That method saved me the pain of having to perform a "data | text
to columns" and format each column..
Keep up the good work!
--
Keith
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:OmRdOCsXEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Keith,
> The only recommendation I have at this time is to increase the RAM on the
> machine you are using to export to excel. I do not have a specific
> recommendation, but I would suggest a minimum of 1GB of RAM. The SQL
Server
> 2000 Reporting Services team is looking at addressing this issue in
upcoming
> service packs and releases.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eo8Ko5qXEHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Server: SQL Server 2000 Ent running on Win2k AS, Ent Edition of
Reporting
> > Services + SP1
> > my workstation: Win2k SP4, Microsoft Development Environment v7.1.3088,
> > Reporting Services Ent Ed + SP1.
> >
> > I am trying to export 55,804 rows to Excel. The table that I am
exporting
> > from has 11 columns.
> >
> > -- Columns within >results< -- DataType Length
> > --- -- --
> > Source varchar 2
> > ClaimNumber varchar 17
> > MarketLoc varchar 7
> > Description varchar 50
> > SomeDate datetime 8
> > DetailNo smallint 2
> > ChargeCode varchar 1
> > UnitCount smallint 2
> > ChargedDollars decimal 5
> > AllowedDollars decimal 5
> > NetPaidDollars decimal 5
> >
> > Because I need to dump this data for 17 different market locations
ranging
> > in rowcounts of 600 rows to 56,000 rows I figured that I would just
create
> a
> > report within Reporting Services and query the results table (228,825
> rows)
> > table for each of the 17 market locations. I would export each market
to
> an
> > Excel file and then I would copy/paste the worksheets into one file that
I
> > can send to the end user. This would be easier than selecting the
> > information within Query Analyzer, saving to a file, importing each
file,
> > formatting each column and repeating the process for each of the 17
groups
> > of data.
> >
> > I started out with a small data set (1500 rows) as a proof of concept so
> > that I knew the process would work. When I had the report formatted
> > correctly I successfully exported the data to Excel. Then I moved on to
> one
> > of the larger groups (55,804 rows). This is where I had the problems.
> >
> > I previewed the report within Visual Studio. Total page count 1241.
The
> > rdl.data file is 12MB. I clicked on the disk icon on the toolbar and
> chose
> > to export the report to Excel. I have 768MB of RAM in my machine and
the
> > memory usage continued to rise until it reached 1GB. This process took
> over
> > 20 minutes. At the end of it all I received a dialog box indicating a
> > System.OutOfMemoryException was thrown. What would happen if I tried
> > running this on the server? I am guessing that it would consume lots of
> CPU
> > cycles and eat up lots of memory. Would it finish, or would it fail? I
> am
> > afraid to try :)
> >
> > Why is the .rdl.data only 12MB yet over 1GB was consumed while
converting
> to
> > Excel? When I save the results to a [text] file within Query Analyzer
the
> > file is 12MB. I could import into Excel, but then I have to import x
> files
> > and format y columns x times.
> >
> > This should be fairly easy to repro. Does anyone from Microsoft want to
> > take a look at it? Does anyone else have any ideas?
> >
> > --
> > Keith
> >
> >
>
Friday, February 17, 2012
Export SQL Server 2005 Express to SQL Server 2000
I'm trying to export a SQL Server 2005 Express database to SQL Server 2000 sp4, but have so far been unsuccessful. SQL 2005 Express doesn't have an Export Data function and backup/restore doesn't work because of the different versions.
Any suggestions greatly appreciated!
I dont think you can move dbs from higher version to lower.....you cannot perform backup/restore nor detach/attach.......the same applies to sql 2005 to sql 2000
|||Well, Deepak is right, you will have to use the Import functionality of SQL Server 200 to get the data from the 2k5 to the 2k database.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)