Bind DataSource at runtime and Adding fields [A little help please]

hi,

I am using Crystal Reports 8.5 and Visual Biasic 6.0, the technique that I
was using was I connect the database using the ODBC to the crytal reports and design my report by draging the fields to the reports.

In my new assignment I have to bind a ADODB recordset to the report at
run time.

I got 2 Problems.

01. How can I bind a ADODB recordset to the report at run time
02. After binding the recordset how can I add the fileds to the report
content area

A small help on this please.

Thank you.Dim conn As ADODB.Connection 'CONNECTION TO BROKER QUERIES
Dim rs As ADODB.Recordset 'HOLDS ALL DATA RETURNED FROM QUERY
Dim crystal As CRAXDRT.Application 'LOADS REPORT FROM FILE
Dim Report As CRAXDRT.Report 'HOLDS REPORT

Set conn = New ADODB.Connection
conn.Open "Provider=MSDAORA.1;User ID=scott;Data Source=qb;Persist Security Info=False", "sebsv4", "sebsv4pwd" 'THESE OPTION VALUES ARE BEST FOR VB

Set rs = New ADODB.Recordset
rs.Open q, conn, adOpenStatic, adLockReadOnly

Set crystal = New CRAXDRT.Application 'MANAGES REPORTS

Set Report = crystal.OpenReport(App.Path & "\report1.rpt") 'OPEN OUR REPORT

Report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET
Report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET

CRViewer1.ReportSource = Report 'LINK VIEWER TO REPORT
CRViewer1.ViewReport 'SHOW REPORT|||thanks for the quick reply sraheem

another small problem,

if I am using the report designer in VB like that, when I have draged a field name in to the report
it shows the field name as

field:<TableName>.<ColumnName>

now If I am doing the data binding at run time how can I add the recordset columns in to the report, since there is no data property on report fileds

Thanx.|||just capture report sections like below and add fields...

Dim crxdetail As CRAXDDRT.Section 'Section Decleration
Dim crxPageHeader As CRAXDDRT.Section 'Section Decleration
Dim crxPageFooter As CRAXDDRT.Section 'Section Decleration

Set crxdetail = Report.Sections.Item("D") 'Detail Section
Set crxPageHeader = Report.Sections.Item("PH") 'Detail Section
Set crxPageFooter = Report.Sections.Item("PF") 'Detail Section

crxdetail.AddFieldObject "{e." & rs(i).Name & "}", 1000, 0

where e is the report command object name or table name in your case.

Big Mistake? - Indexes with includes on partition tables

I recently tried adding include columns to two indexes, one on each of two
partitioned tables. The tables are modest-sized, fact tables used for
aggregation. Each week, we slide the partitions.
The slide process is normally very fast. However, after adding include
columns to the indexes, the command to split the range in the partition
functions resulted in what appears to be runaway processes that gobbled up
system resources and maxxed out the transaction log. In fact, even after
emptying the tables of data and dropping the indexes, this happened. It was
only after I deleted the index partition scheme that the problem went away.
So... is there a problem with creating indexes with include columns on
partitioned tables where the partitions will slide? Is there something
exponential that goes on?
Thanks
Thanks,
CGW
I can't think of why simply including columns would show down the split.
Can you please post the DDL, including partition schemes and functions?
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>I recently tried adding include columns to two indexes, one on each of two
> partitioned tables. The tables are modest-sized, fact tables used for
> aggregation. Each week, we slide the partitions.
> The slide process is normally very fast. However, after adding include
> columns to the indexes, the command to split the range in the partition
> functions resulted in what appears to be runaway processes that gobbled up
> system resources and maxxed out the transaction log. In fact, even after
> emptying the tables of data and dropping the indexes, this happened. It
> was
> only after I deleted the index partition scheme that the problem went
> away.
> So... is there a problem with creating indexes with include columns on
> partitioned tables where the partitions will slide? Is there something
> exponential that goes on?
> Thanks
> --
> Thanks,
> CGW
|||That could ba a lot of work for both of us. Are you just mildly curious, or
do you think you could really puzzle it out?
Thanks,
CGW
"Dan Guzman" wrote:

> I can't think of why simply including columns would show down the split.
> Can you please post the DDL, including partition schemes and functions?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>
|||> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
I'm quite interested in this problem and would like to try to help you
figure out what's going on. However, I'll need DDL to do so since I can't
think of anything obvious based on the information given. The only causes I
can think of isn't related to included columns.
One gotcha I've seen is that splitting a partition function will change all
the related partition schemes. If you have multiple tables using the same
scheme, all corresponding partitions will be split. I've run into an issue
where a staging table (with same scheme) wasn't empty when the function was
split so a lot of data needed to be moved in the staging table partitions
even though no data needed to be moved into the newly created partition of
the main table.
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:00984490-298D-43AD-B820-BAA95680012F@.microsoft.com...[vbcol=seagreen]
> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
> --
> Thanks,
> CGW
>
> "Dan Guzman" wrote:

Big Mistake? - Indexes with includes on partition tables

I recently tried adding include columns to two indexes, one on each of two
partitioned tables. The tables are modest-sized, fact tables used for
aggregation. Each week, we slide the partitions.
The slide process is normally very fast. However, after adding include
columns to the indexes, the command to split the range in the partition
functions resulted in what appears to be runaway processes that gobbled up
system resources and maxxed out the transaction log. In fact, even after
emptying the tables of data and dropping the indexes, this happened. It was
only after I deleted the index partition scheme that the problem went away.
So... is there a problem with creating indexes with include columns on
partitioned tables where the partitions will slide? Is there something
exponential that goes on?
Thanks
--
Thanks,
CGWI can't think of why simply including columns would show down the split.
Can you please post the DDL, including partition schemes and functions?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>I recently tried adding include columns to two indexes, one on each of two
> partitioned tables. The tables are modest-sized, fact tables used for
> aggregation. Each week, we slide the partitions.
> The slide process is normally very fast. However, after adding include
> columns to the indexes, the command to split the range in the partition
> functions resulted in what appears to be runaway processes that gobbled up
> system resources and maxxed out the transaction log. In fact, even after
> emptying the tables of data and dropping the indexes, this happened. It
> was
> only after I deleted the index partition scheme that the problem went
> away.
> So... is there a problem with creating indexes with include columns on
> partitioned tables where the partitions will slide? Is there something
> exponential that goes on?
> Thanks
> --
> Thanks,
> CGW|||That could ba a lot of work for both of us. Are you just mildly curious, or
do you think you could really puzzle it out?
--
Thanks,
CGW
"Dan Guzman" wrote:
> I can't think of why simply including columns would show down the split.
> Can you please post the DDL, including partition schemes and functions?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
> >I recently tried adding include columns to two indexes, one on each of two
> > partitioned tables. The tables are modest-sized, fact tables used for
> > aggregation. Each week, we slide the partitions.
> >
> > The slide process is normally very fast. However, after adding include
> > columns to the indexes, the command to split the range in the partition
> > functions resulted in what appears to be runaway processes that gobbled up
> > system resources and maxxed out the transaction log. In fact, even after
> > emptying the tables of data and dropping the indexes, this happened. It
> > was
> > only after I deleted the index partition scheme that the problem went
> > away.
> >
> > So... is there a problem with creating indexes with include columns on
> > partitioned tables where the partitions will slide? Is there something
> > exponential that goes on?
> >
> > Thanks
> > --
> > Thanks,
> >
> > CGW
>|||> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
I'm quite interested in this problem and would like to try to help you
figure out what's going on. However, I'll need DDL to do so since I can't
think of anything obvious based on the information given. The only causes I
can think of isn't related to included columns.
One gotcha I've seen is that splitting a partition function will change all
the related partition schemes. If you have multiple tables using the same
scheme, all corresponding partitions will be split. I've run into an issue
where a staging table (with same scheme) wasn't empty when the function was
split so a lot of data needed to be moved in the staging table partitions
even though no data needed to be moved into the newly created partition of
the main table.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:00984490-298D-43AD-B820-BAA95680012F@.microsoft.com...
> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
> --
> Thanks,
> CGW
>
> "Dan Guzman" wrote:
>> I can't think of why simply including columns would show down the split.
>> Can you please post the DDL, including partition schemes and functions?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "CGW" <CGW@.discussions.microsoft.com> wrote in message
>> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>> >I recently tried adding include columns to two indexes, one on each of
>> >two
>> > partitioned tables. The tables are modest-sized, fact tables used for
>> > aggregation. Each week, we slide the partitions.
>> >
>> > The slide process is normally very fast. However, after adding include
>> > columns to the indexes, the command to split the range in the partition
>> > functions resulted in what appears to be runaway processes that gobbled
>> > up
>> > system resources and maxxed out the transaction log. In fact, even
>> > after
>> > emptying the tables of data and dropping the indexes, this happened. It
>> > was
>> > only after I deleted the index partition scheme that the problem went
>> > away.
>> >
>> > So... is there a problem with creating indexes with include columns on
>> > partitioned tables where the partitions will slide? Is there something
>> > exponential that goes on?
>> >
>> > Thanks
>> > --
>> > Thanks,
>> >
>> > CGWsql

Big Mistake? - Indexes with includes on partition tables

I recently tried adding include columns to two indexes, one on each of two
partitioned tables. The tables are modest-sized, fact tables used for
aggregation. Each week, we slide the partitions.
The slide process is normally very fast. However, after adding include
columns to the indexes, the command to split the range in the partition
functions resulted in what appears to be runaway processes that gobbled up
system resources and maxxed out the transaction log. In fact, even after
emptying the tables of data and dropping the indexes, this happened. It was
only after I deleted the index partition scheme that the problem went away.
So... is there a problem with creating indexes with include columns on
partitioned tables where the partitions will slide? Is there something
exponential that goes on?
Thanks
--
Thanks,
CGWI can't think of why simply including columns would show down the split.
Can you please post the DDL, including partition schemes and functions?
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>I recently tried adding include columns to two indexes, one on each of two
> partitioned tables. The tables are modest-sized, fact tables used for
> aggregation. Each week, we slide the partitions.
> The slide process is normally very fast. However, after adding include
> columns to the indexes, the command to split the range in the partition
> functions resulted in what appears to be runaway processes that gobbled up
> system resources and maxxed out the transaction log. In fact, even after
> emptying the tables of data and dropping the indexes, this happened. It
> was
> only after I deleted the index partition scheme that the problem went
> away.
> So... is there a problem with creating indexes with include columns on
> partitioned tables where the partitions will slide? Is there something
> exponential that goes on?
> Thanks
> --
> Thanks,
> CGW|||That could ba a lot of work for both of us. Are you just mildly curious, or
do you think you could really puzzle it out?
--
Thanks,
CGW
"Dan Guzman" wrote:

> I can't think of why simply including columns would show down the split.
> Can you please post the DDL, including partition schemes and functions?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>|||> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
I'm quite interested in this problem and would like to try to help you
figure out what's going on. However, I'll need DDL to do so since I can't
think of anything obvious based on the information given. The only causes I
can think of isn't related to included columns.
One gotcha I've seen is that splitting a partition function will change all
the related partition schemes. If you have multiple tables using the same
scheme, all corresponding partitions will be split. I've run into an issue
where a staging table (with same scheme) wasn't empty when the function was
split so a lot of data needed to be moved in the staging table partitions
even though no data needed to be moved into the newly created partition of
the main table.
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:00984490-298D-43AD-B820-BAA95680012F@.microsoft.com...[vbcol=seagreen]
> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
> --
> Thanks,
> CGW
>
> "Dan Guzman" wrote:
>