Hi,
I'm exporting my report to a Text file.
Main report works fine. While exporting sub reports, all the records are exporting. The problem is sub report data starts from the second position. i.e it leaves the first position as blank. I had arragned my fields correctly. Then why am I getting this blank space.
And Is there any way to export only the sub report?
ThanksResolved.
Anyway, thank u guys.|||Could u explain me how did u solve it? ;)
Rohini|||Hi,
Actually it's not a problem of Sub reports. It's a know issue. Downloaded and installed a Hot Fix from the CrystalDecisions website.
It's working now.
But the problem is, while trying to export thru asp code it again leaves a blank space.
If any of you experienced the same, please help me
Thankssql
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
exporting large amounts of records (50K rows) to excel - painfully slow
We're trying to do an export to excel of a large amount of data.
When we do 100 even 4000 rows it happens in a decent amount of time.
However, when we try to export say 35,000 rows it never finishes and
appears to hang after a long amount of time (15-20 minutes).
Does anyone have any tips for optimizing or troubleshooting this issue.
We're using SQL Server 2000 and Reporting Services 2000.
Thanks,
JasonI use Access Data Projects for real enterprise level reporting; all the
time-- and I push out this many records without a problem.
Maybe you should consider having a better strategy; and choosing real
tools-- like Access Data Projects... instead of SSRS; where version
2005 is STILL considered a beta.
-Aaron
jason.harris@.gmail.com wrote:
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason|||RS 2005 is faster but the following technique will work. You need to export
in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
handle. In RS 2005 you can configure RS to export as ASCII. What I do is
have a link they click on and use the following URL to export in CSV. Order
of magnitudes faster.
="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
here" &casesensitiveparamname=" & Parameters!Paramname.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
When you play with this you might decide to design a report just for
exporting.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jason.harris@.gmail.com> wrote in message
news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> RS 2005 is faster but the following technique will work. You need to
> export in CSV. However, with RS 2000 it exports it as Unicode which
> Excel can't handle. In RS 2005 you can configure RS to export as
> ASCII. What I do is have a link they click on and use the following
> URL to export in CSV. Order of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
It's also useful to remember that RS is a tool to render usable reports to
view in a browser. It also has the option to export those reports. It was
never designed to be used as a data extractor|||Bruce
I have a similar problem but how do I put this URL into the RS Folder.
Thanks
Karen
Bruce L-C [MVP] wrote:
> RS 2005 is faster but the following technique will work. You need to export
> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
> have a link they click on and use the following URL to export in CSV. Order
> of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> <jason.harris@.gmail.com> wrote in message
> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> > We're trying to do an export to excel of a large amount of data.
> >
> > When we do 100 even 4000 rows it happens in a decent amount of time.
> >
> > However, when we try to export say 35,000 rows it never finishes and
> > appears to hang after a long amount of time (15-20 minutes).
> >
> >
> > Does anyone have any tips for optimizing or troubleshooting this issue.
> >
> > We're using SQL Server 2000 and Reporting Services 2000.
> >
> > Thanks,
> > Jason
> >|||it's alst important to note that since it's EXCEL we're talking about;
you're gonna have a half dozen ninja spreadsheet FAGS that try to build
ETL tools out of Excel.
In the real world; people use Excel as a word processor and a ETL tool;
just because the idiots dont know any other tools.
is it their fault? no it is the fault of the managers and teachers that
make Excel an expectation.
I would just reccomend shooting anyone that uses Excel for anything.
and then uninstalling Excel from every machine in the house.
Asher_N wrote:
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> > RS 2005 is faster but the following technique will work. You need to
> > export in CSV. However, with RS 2000 it exports it as Unicode which
> > Excel can't handle. In RS 2005 you can configure RS to export as
> > ASCII. What I do is have a link they click on and use the following
> > URL to export in CSV. Order of magnitudes faster.
> >
> > ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> > report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> > "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >
> > When you play with this you might decide to design a report just for
> > exporting.
> >
> >
> It's also useful to remember that RS is a tool to render usable reports to
> view in a browser. It also has the option to export those reports. It was
> never designed to be used as a data extractor|||This is for using the Jump To URL. If you wanted a report that just
consisted of this then you would have a report with textboxes. Properties of
the textbox, navigation, jump to URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KarenM" <karenmiddleol@.yahoo.com> wrote in message
news:1160044319.003258.210730@.b28g2000cwb.googlegroups.com...
> Bruce
> I have a similar problem but how do I put this URL into the RS Folder.
> Thanks
> Karen
> Bruce L-C [MVP] wrote:
>> RS 2005 is faster but the following technique will work. You need to
>> export
>> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
>> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
>> have a link they click on and use the following URL to export in CSV.
>> Order
>> of magnitudes faster.
>> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
>> report
>> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> When you play with this you might decide to design a report just for
>> exporting.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <jason.harris@.gmail.com> wrote in message
>> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
>> > We're trying to do an export to excel of a large amount of data.
>> >
>> > When we do 100 even 4000 rows it happens in a decent amount of time.
>> >
>> > However, when we try to export say 35,000 rows it never finishes and
>> > appears to hang after a long amount of time (15-20 minutes).
>> >
>> >
>> > Does anyone have any tips for optimizing or troubleshooting this issue.
>> >
>> > We're using SQL Server 2000 and Reporting Services 2000.
>> >
>> > Thanks,
>> > Jason
>> >
>
When we do 100 even 4000 rows it happens in a decent amount of time.
However, when we try to export say 35,000 rows it never finishes and
appears to hang after a long amount of time (15-20 minutes).
Does anyone have any tips for optimizing or troubleshooting this issue.
We're using SQL Server 2000 and Reporting Services 2000.
Thanks,
JasonI use Access Data Projects for real enterprise level reporting; all the
time-- and I push out this many records without a problem.
Maybe you should consider having a better strategy; and choosing real
tools-- like Access Data Projects... instead of SSRS; where version
2005 is STILL considered a beta.
-Aaron
jason.harris@.gmail.com wrote:
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason|||RS 2005 is faster but the following technique will work. You need to export
in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
handle. In RS 2005 you can configure RS to export as ASCII. What I do is
have a link they click on and use the following URL to export in CSV. Order
of magnitudes faster.
="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
here" &casesensitiveparamname=" & Parameters!Paramname.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
When you play with this you might decide to design a report just for
exporting.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jason.harris@.gmail.com> wrote in message
news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> RS 2005 is faster but the following technique will work. You need to
> export in CSV. However, with RS 2000 it exports it as Unicode which
> Excel can't handle. In RS 2005 you can configure RS to export as
> ASCII. What I do is have a link they click on and use the following
> URL to export in CSV. Order of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
It's also useful to remember that RS is a tool to render usable reports to
view in a browser. It also has the option to export those reports. It was
never designed to be used as a data extractor|||Bruce
I have a similar problem but how do I put this URL into the RS Folder.
Thanks
Karen
Bruce L-C [MVP] wrote:
> RS 2005 is faster but the following technique will work. You need to export
> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
> have a link they click on and use the following URL to export in CSV. Order
> of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> <jason.harris@.gmail.com> wrote in message
> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> > We're trying to do an export to excel of a large amount of data.
> >
> > When we do 100 even 4000 rows it happens in a decent amount of time.
> >
> > However, when we try to export say 35,000 rows it never finishes and
> > appears to hang after a long amount of time (15-20 minutes).
> >
> >
> > Does anyone have any tips for optimizing or troubleshooting this issue.
> >
> > We're using SQL Server 2000 and Reporting Services 2000.
> >
> > Thanks,
> > Jason
> >|||it's alst important to note that since it's EXCEL we're talking about;
you're gonna have a half dozen ninja spreadsheet FAGS that try to build
ETL tools out of Excel.
In the real world; people use Excel as a word processor and a ETL tool;
just because the idiots dont know any other tools.
is it their fault? no it is the fault of the managers and teachers that
make Excel an expectation.
I would just reccomend shooting anyone that uses Excel for anything.
and then uninstalling Excel from every machine in the house.
Asher_N wrote:
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> > RS 2005 is faster but the following technique will work. You need to
> > export in CSV. However, with RS 2000 it exports it as Unicode which
> > Excel can't handle. In RS 2005 you can configure RS to export as
> > ASCII. What I do is have a link they click on and use the following
> > URL to export in CSV. Order of magnitudes faster.
> >
> > ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> > report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> > "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >
> > When you play with this you might decide to design a report just for
> > exporting.
> >
> >
> It's also useful to remember that RS is a tool to render usable reports to
> view in a browser. It also has the option to export those reports. It was
> never designed to be used as a data extractor|||This is for using the Jump To URL. If you wanted a report that just
consisted of this then you would have a report with textboxes. Properties of
the textbox, navigation, jump to URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KarenM" <karenmiddleol@.yahoo.com> wrote in message
news:1160044319.003258.210730@.b28g2000cwb.googlegroups.com...
> Bruce
> I have a similar problem but how do I put this URL into the RS Folder.
> Thanks
> Karen
> Bruce L-C [MVP] wrote:
>> RS 2005 is faster but the following technique will work. You need to
>> export
>> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
>> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
>> have a link they click on and use the following URL to export in CSV.
>> Order
>> of magnitudes faster.
>> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
>> report
>> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> When you play with this you might decide to design a report just for
>> exporting.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <jason.harris@.gmail.com> wrote in message
>> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
>> > We're trying to do an export to excel of a large amount of data.
>> >
>> > When we do 100 even 4000 rows it happens in a decent amount of time.
>> >
>> > However, when we try to export say 35,000 rows it never finishes and
>> > appears to hang after a long amount of time (15-20 minutes).
>> >
>> >
>> > Does anyone have any tips for optimizing or troubleshooting this issue.
>> >
>> > We're using SQL Server 2000 and Reporting Services 2000.
>> >
>> > Thanks,
>> > Jason
>> >
>
Sunday, March 25, 2012
Exporting data while people are using the site?
I'm exporting a large file from a large Production database (SQL).
Users are currently updating and inserting new records.
Does the export take a snapshot of the data when it starts ? or will
it freeze the database so all udpates/inserts happen after the export
completes?
Also, how do I get the delta of data that wasn't exported, if I want
to get the difference the next day?
Thank yoU!!!"Angel Cat" <kittycatangel@.hotmail.com> wrote in message
news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> I'm exporting a large file from a large Production database (SQL).
> Users are currently updating and inserting new records.
Is it 24hr updating or is there a break during the night?
> Does the export take a snapshot of the data when it starts ? or will
> it freeze the database so all udpates/inserts happen after the export
> completes?
Snapshot.
> Also, how do I get the delta of data that wasn't exported, if I want
> to get the difference the next day?
Comparitive reporting on some key field to provide stats.
OTOH these import tasks can be automated and scheduled.
If the database shows no activity at 3:00am daily, do it then.|||THe database is in use 24 hours a day. I need to take a dump of the
data and and make some updates but what happens when a user is updting
while I take a dump. Does DTS freeze the data when I'm exporting?
ALso, is there a way I can take the difference easily?
Thank yoU!
"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message news:<38sBb.46396$aT.11510@.news-server.bigpond.net.au>...
> "Angel Cat" <kittycatangel@.hotmail.com> wrote in message
> news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> > I'm exporting a large file from a large Production database (SQL).
> > Users are currently updating and inserting new records.
> Is it 24hr updating or is there a break during the night?
> > Does the export take a snapshot of the data when it starts ? or will
> > it freeze the database so all udpates/inserts happen after the export
> > completes?
> Snapshot.
> > Also, how do I get the delta of data that wasn't exported, if I want
> > to get the difference the next day?
> Comparitive reporting on some key field to provide stats.
> OTOH these import tasks can be automated and scheduled.
> If the database shows no activity at 3:00am daily, do it then.|||"Angel Cat" <kittycatangel@.hotmail.com> wrote in message
news:9b08c59d.0312100813.124f1c34@.posting.google.c om...
> THe database is in use 24 hours a day. I need to take a dump of the
> data and and make some updates but what happens when a user is updting
> while I take a dump. Does DTS freeze the data when I'm exporting?
Effectively it does a snapshot.
> ALso, is there a way I can take the difference easily?
The quantitative difference can be approximated in the first
instance by publishing the rowcount of the table "as at snapshot"
as distinct from the rowcount of the realtime SQL table which
can be easily obtained dynamically between loads. This will
represent (net) rows to be added/deleted.
Operationally this will imply part of the export process
will be to write the "last record included" type of thing
to some local table.
The quantitative differences are more difficult issues to deal
with but obviously you need to decide what elements of the
data you wish to keep track of during inter-load build-ups.
These you can add to the rowcount info if/when req'd.
Another idle thought here includes a master-publication
register accessible to the distribution group (who are getting
your export file) which is updated at every publication with
relevant information, and coupled with a realtime query into
SQL to compare nowtime with last publication.
> Thank yoU!
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:<38sBb.46396$aT.11510@.news-server.bigpond.net.au>...
> > "Angel Cat" <kittycatangel@.hotmail.com> wrote in message
> > news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> > > I'm exporting a large file from a large Production database (SQL).
> > > Users are currently updating and inserting new records.
> > Is it 24hr updating or is there a break during the night?
> > > Does the export take a snapshot of the data when it starts ? or will
> > > it freeze the database so all udpates/inserts happen after the export
> > > completes?
> > Snapshot.
> > > Also, how do I get the delta of data that wasn't exported, if I want
> > > to get the difference the next day?
> > Comparitive reporting on some key field to provide stats.
> > OTOH these import tasks can be automated and scheduled.
> > If the database shows no activity at 3:00am daily, do it then.|||Angel Cat,
It was my understanding that SQL 7 and 2000 have a snapshot of the data "as
of" the end time of the databases.
If you start your backup at 6pm and users are inserting and updating, and
your database backup finishes at 6:30pm, your backup will be as of 6:30pm.
MSSQL 6.5 had a snapshot of the data at 6pm. Assuming the example above.
Oscar...
"Angel Cat" <kittycatangel@.hotmail.com> wrote in message
news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> I'm exporting a large file from a large Production database (SQL).
> Users are currently updating and inserting new records.
> Does the export take a snapshot of the data when it starts ? or will
> it freeze the database so all udpates/inserts happen after the export
> completes?
> Also, how do I get the delta of data that wasn't exported, if I want
> to get the difference the next day?
> Thank yoU!!!
Exporting data from SQL table to Excel File - How to delete rows before inserting new
Hi,
Create a view that limits number of exported rows and use it in DTS package instead of a table.
Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?
What i want is to delete the rows in the destination file before inserting new records.
Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.
Thanks a lot.
Quote:
Originally Posted by LimaCharlie
Hi,
Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?
What i want is to delete the rows in the destination file before inserting new records.
Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.
Thanks a lot.
Create a view that limits number of exported rows and use it in DTS package instead of a table.
Good Luck.|||hi iburyak,
just the same, the records are being appended to the excel file. what i want is to delete the rows in the destination file before inserting new records.
thanks.|||Did you create a view that removes unwanted records?
Execute a view on a Server side and make sure this is the data you want to export, don't create a view to select * from table_name it will not correct a problem.
You need to give me more information then just I want to delete records.
It is a wrong approach to load all records and delete on destination side you need to load only necessary records.|||Hi Iburyak,
The Question is... this excel file is getting populated everyday and everyday before porting data to the excel the old data has to be erased and new data filled up.
Any help on that.
Thanks in advance..
Exporting data from SQL table to Excel File - How to delete rows before insertin
Hi,
Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?
What i want is to delete the rows in the destination file before inserting new records.
Thanks a lot.Is it possibe to create a link to the Excel and run a delete statement from the SQL Server side?
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=135&rl=1
ddave|||LimaCharlie, take a look at this post:
http://www.dbforums.com/showthread.php?t=981661
Cheers
kbk|||hi kbk,
that was my first design: i drop then recreate the table and it was working properly. but the problem is, the users are not permitted to drop & create tables in the database (only me & the DB admin are allowed to do this). so what i did was create a temporary table, from this temp table i export the data to excel. is there any other way so i can delete the rows before inserting new?
thanks a lot.|||What i want is to delete the rows in the destination file before inserting new records.
I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.
1. Create an activeX script as below: -This will remove the worksheet.
Function Main()
Dim srccsvfile
Dim objExcel
Dim objWorkbook, objWorksheet
srccsvfile = "C:\Test.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.displayalerts = False
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet = objWorkbook.WorkSheets("New_Table")
objWorksheet.Activate
objWorksheet.Delete 'this is removing the worksheet instead of rows
objWorkbook.Save 'you must save the change otherwise in trouble
objExcel.Workbooks.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function
2. Create Execute SQL task as below: - create the worksheet again
CREATE TABLE `New_Table` (
`OfficePK` VarChar (8) ,
`DealerGroupFK` VarChar (4) ,
`StateManagerGroupFK` VarChar (20) ,
`OfficeType` VarChar (1) ,
`OfficeName` VarChar (255) ,
`OfficeAddress1` VarChar (255) ,
`OfficeAddress2` VarChar (255) ,
`OfficeSuburb` VarChar (255) ,
`OfficeState` VarChar (255) ,
`OfficePostCode` VarChar (255) ,
`OfficeCountry` VarChar (255) ,
`OfficePhone` VarChar (255) ,
`RIOfficeID` VarChar (50) ,
`RIFranchiseName` VarChar (255) ,
`VPServerIP` VarChar (50) ,
`LastReceived` DateTime
)
3. Then use data transform as you are doing now.
Make sure that the destination table name is New_Table and the connection is Excel.|||Hi TerryP,
I'll try this.
Thank you very much. =)sql
Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?
What i want is to delete the rows in the destination file before inserting new records.
Thanks a lot.Is it possibe to create a link to the Excel and run a delete statement from the SQL Server side?
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=135&rl=1
ddave|||LimaCharlie, take a look at this post:
http://www.dbforums.com/showthread.php?t=981661
Cheers
kbk|||hi kbk,
that was my first design: i drop then recreate the table and it was working properly. but the problem is, the users are not permitted to drop & create tables in the database (only me & the DB admin are allowed to do this). so what i did was create a temporary table, from this temp table i export the data to excel. is there any other way so i can delete the rows before inserting new?
thanks a lot.|||What i want is to delete the rows in the destination file before inserting new records.
I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.
1. Create an activeX script as below: -This will remove the worksheet.
Function Main()
Dim srccsvfile
Dim objExcel
Dim objWorkbook, objWorksheet
srccsvfile = "C:\Test.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.displayalerts = False
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet = objWorkbook.WorkSheets("New_Table")
objWorksheet.Activate
objWorksheet.Delete 'this is removing the worksheet instead of rows
objWorkbook.Save 'you must save the change otherwise in trouble
objExcel.Workbooks.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function
2. Create Execute SQL task as below: - create the worksheet again
CREATE TABLE `New_Table` (
`OfficePK` VarChar (8) ,
`DealerGroupFK` VarChar (4) ,
`StateManagerGroupFK` VarChar (20) ,
`OfficeType` VarChar (1) ,
`OfficeName` VarChar (255) ,
`OfficeAddress1` VarChar (255) ,
`OfficeAddress2` VarChar (255) ,
`OfficeSuburb` VarChar (255) ,
`OfficeState` VarChar (255) ,
`OfficePostCode` VarChar (255) ,
`OfficeCountry` VarChar (255) ,
`OfficePhone` VarChar (255) ,
`RIOfficeID` VarChar (50) ,
`RIFranchiseName` VarChar (255) ,
`VPServerIP` VarChar (50) ,
`LastReceived` DateTime
)
3. Then use data transform as you are doing now.
Make sure that the destination table name is New_Table and the connection is Excel.|||Hi TerryP,
I'll try this.
Thank you very much. =)sql
Thursday, March 22, 2012
Exporting Data from Reporting Services into Multiple Excel Worksheets
Hi,
Please help me.
I need to export around 1 million records to excel. As we know that there is a limit of approx 65,000 rows in one worksheet so the exporting functionality is failing. One way is that I should be able to configure the Reporting Service in such a way so that once 65,000 records gets populated in the first worksheet, then the rest 35,000 records gets popultaed in the second worksheet.
I am not able to find out how this can be achieved in Reporting Services. Please help me in this.
Thanks in advance.
Regards
Raman
Hello Raman,
Take a look at this link, in the 'Page Breaks' section, it may help you.
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
It requires that you set a group with a group expression on X number of rows, then you page break on the group.
Hope this helps.
Jarret
Exporting CR from VB
am trying t export a report from CR ver 7.0 from VB 6.0
the report has 16000 rows, but its exporting only 8000+ records...
no error messages.
can anyone help me out.r u exporting to excel, if yes
try using
Report.Export
With Report.ExportOptions
.ExcelExportAllPages
End With
****where report is object of CRAXDRT.report
Dim Report As CRAXDRT.Report|||thanks sraheem for ur help.
yeah am trying to export to excel only.
i tried CRAXDRT
but i coud not find the option .ExcelExportAllPages with ExportOptions|||so sad to know that noone is there with a solution.
i thot its a simple problem and i didnt knew to solve it.
if anyone finds it out,...please let me know,sql
the report has 16000 rows, but its exporting only 8000+ records...
no error messages.
can anyone help me out.r u exporting to excel, if yes
try using
Report.Export
With Report.ExportOptions
.ExcelExportAllPages
End With
****where report is object of CRAXDRT.report
Dim Report As CRAXDRT.Report|||thanks sraheem for ur help.
yeah am trying to export to excel only.
i tried CRAXDRT
but i coud not find the option .ExcelExportAllPages with ExportOptions|||so sad to know that noone is there with a solution.
i thot its a simple problem and i didnt knew to solve it.
if anyone finds it out,...please let me know,sql
Friday, February 24, 2012
Export to excel > 65000 rows
Hi
We are trying to export some data from SQL server
into an excel sheet. The data records will be like
more than 1 lakh records. I noted that Excel has
a constraint of not creating rows more than 65,000.
In this case we need to create multiple workbooks
in the excel sheet.
Could you please help me how to creat multiple
workbooks thru DTS in run time - if it is >65,000?
ThanksI don't know of a way to do without a bit of scripting.
You could output all rows to a csv, then run vbscript that uses the excel object to create a new workbook and create multiple sheets (tabs - this is what you mean, right?), iterating through the csv.
Will people actually be looking at the data, row after row in excel? Why not use an access database? Excel wasn't designed for such large amounts of data, so using it for such a thing might not be a good idea in the first place.
Eric|||Why does it have to Excel?
Is someone going to look at the Data in Excel?
Who's gonna look at multiple tabs of 65,000 rows?
That's what the database is for
What are you ultimatley trying to do?
We are trying to export some data from SQL server
into an excel sheet. The data records will be like
more than 1 lakh records. I noted that Excel has
a constraint of not creating rows more than 65,000.
In this case we need to create multiple workbooks
in the excel sheet.
Could you please help me how to creat multiple
workbooks thru DTS in run time - if it is >65,000?
ThanksI don't know of a way to do without a bit of scripting.
You could output all rows to a csv, then run vbscript that uses the excel object to create a new workbook and create multiple sheets (tabs - this is what you mean, right?), iterating through the csv.
Will people actually be looking at the data, row after row in excel? Why not use an access database? Excel wasn't designed for such large amounts of data, so using it for such a thing might not be a good idea in the first place.
Eric|||Why does it have to Excel?
Is someone going to look at the Data in Excel?
Who's gonna look at multiple tabs of 65,000 rows?
That's what the database is for
What are you ultimatley trying to do?
Sunday, February 19, 2012
export table from one database to another on different server
i've one dev and one prod, one of the table in dev database having 70
millions records and need to export to one of the table in prod
database. both machine got 2 nic, one 10/100 mbps and the other 1gbps
speed. databases on raid5.
the transmission between two servers is very slow, it's transmitting
1000 rows per 4 secs. sql 2000 with sp3 on both machines, i'm using
dts. please advice to speed up.
millions records and need to export to one of the table in prod
database. both machine got 2 nic, one 10/100 mbps and the other 1gbps
speed. databases on raid5.
the transmission between two servers is very slow, it's transmitting
1000 rows per 4 secs. sql 2000 with sp3 on both machines, i'm using
dts. please advice to speed up.
thanks,
PatA fast way to transfer data between 2 SQL Servers is via DTS. You can
specify the 'fast 'load' and 'table lock options to maximize performance
if the target table has no indexes. Also, run the package directly on
the source or destination server rather than an intermediate box.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------
"Pat" <murali_12180@.yahoo.com> wrote in message
news:2a1ff510.0309120503.149eccd4@.posting.google.c om...
> i've one dev and one prod, one of the table in dev database having 70
> millions records and need to export to one of the table in prod
> database. both machine got 2 nic, one 10/100 mbps and the other 1gbps
> speed. databases on raid5.
> the transmission between two servers is very slow, it's transmitting
> 1000 rows per 4 secs. sql 2000 with sp3 on both machines, i'm using
> dts. please advice to speed up.
> thanks,
> Pat
Friday, February 17, 2012
Export SSRS Report to Excel - 65000 lines are limitation?
Currenly work with dataset which has 100000 records, when i export ssrs
report to excel it displayed error message "can't export more than 65000
lines"
I m blocked !!!!I believe that Excel 2007 now has a limit of 1 000 000 rows by 16 384
columns.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Nilesh" <Nilesh@.discussions.microsoft.com> wrote in message
news:1791E023-4FC2-4296-8D02-32F4586538B4@.microsoft.com...
> Currenly work with dataset which has 100000 records, when i export ssrs
> report to excel it displayed error message "can't export more than 65000
> lines"
> I m blocked !!!!
>
report to excel it displayed error message "can't export more than 65000
lines"
I m blocked !!!!I believe that Excel 2007 now has a limit of 1 000 000 rows by 16 384
columns.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Nilesh" <Nilesh@.discussions.microsoft.com> wrote in message
news:1791E023-4FC2-4296-8D02-32F4586538B4@.microsoft.com...
> Currenly work with dataset which has 100000 records, when i export ssrs
> report to excel it displayed error message "can't export more than 65000
> lines"
> I m blocked !!!!
>
Subscribe to:
Posts (Atom)