create table with dynamic constraint

I am trying to create a table with the type of constraint I don't see in any
of the help resources.
Say the basic table structure for table t1 is (colType int, colDesc
varchar(10), colMiscellaneous varchar(100))
I want to limit the combination colType-colDesc thusly:
If the combination is new, it's okay.
If the combination is exactly the same as one previously used, it's okay.
If the colDesc is the same as one previously entered, but the colType is
different, the constraint is violated and the insert or update operation
aborts.
Is this even doable? I've used multi-column constraints before, but not in
this way.
Thanks in advance,
DaveIn t-SQL, you cannot have a query expression in a CHECK constraint, so
multi-row checks are not easy to implement declaratively. You can have a
scalar UDF in certain cases, but it might fail for UPDATE operations. So one
option is to use a trigger like:
CREATE TRIGGER trg ON t1 FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT <> 0 RETURN
IF EXISTS ( SELECT * FROM inserted i
WHERE EXISTS ( SELECT * FROM t1
WHERE t1.type = i.type
AND t1.descr <> i.descr )
) ROLLBACK
... -- add any error messages if needed.
Anith|||>> IF @.@.ROWCOUNT <> 0 RETURN
should be = 0 to see if there are any rows affected
Anith|||Dave,
I think an easier solution here would be to maintain two tables:
create table cols (
colType int not null primary key,
colDesc varchar(10)
)
create table colMisc (
colType int not null references cols(colType),
colMiscellaneous varchar(100)
)
This enforces the data integrity you want:
A single colType cannot have more than one description
A colMiscellaneous value must be associated with a colType and colDesc
You could preserve an interface like you have by creating a view to match
your current table, on which there is an INSTEAD OF trigger to perform
the one or two insert statements needed for each addition of a
colMiscellaneous
value. It may not seem like less work to do this, but it avoids what you're
awkwardly doing now, which is storing facts like "the description of
column #N
is blahblah" once for every colMiscellaneous value there happens to be
for that
column.
In the long run, what you're doing will likely get you into trouble that
you have to solve with more awkwardness, like by adding DISTINCT
to queries that shouldn't need it.
Steve Kass
Drew University
Dave wrote:

>I am trying to create a table with the type of constraint I don't see in an
y
>of the help resources.
>Say the basic table structure for table t1 is (colType int, colDesc
>varchar(10), colMiscellaneous varchar(100))
>I want to limit the combination colType-colDesc thusly:
>If the combination is new, it's okay.
>If the combination is exactly the same as one previously used, it's okay.
>If the colDesc is the same as one previously entered, but the colType is
>different, the constraint is violated and the insert or update operation
>aborts.
>Is this even doable? I've used multi-column constraints before, but not in
>this way.
>Thanks in advance,
>Dave
>
>|||That is an excellent point, and one that I had considered. However, there
really are only three columns, this is just an ancillary table of about 50
rows that will not get many hits, and there will be only one routine for
each of the operations (SELECT, INSERT, UPDATE, & DELETE). I was also just
curious how I would accomplish such a task.
I do know enough about normalization to recognize your solution is
theoretically better; in this case I think the fewer tables factor will
outweigh the drawbacks you point out.
Thanks,
Dave
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23S36LBWbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> Dave,
> I think an easier solution here would be to maintain two tables:
> create table cols (
> colType int not null primary key,
> colDesc varchar(10)
> )
> create table colMisc (
> colType int not null references cols(colType),
> colMiscellaneous varchar(100)
> )
> This enforces the data integrity you want:
> A single colType cannot have more than one description
> A colMiscellaneous value must be associated with a colType and colDesc
> You could preserve an interface like you have by creating a view to match
> your current table, on which there is an INSTEAD OF trigger to perform
> the one or two insert statements needed for each addition of a
> colMiscellaneous
> value. It may not seem like less work to do this, but it avoids what
you're
> awkwardly doing now, which is storing facts like "the description of
> column #N
> is blahblah" once for every colMiscellaneous value there happens to be
> for that
> column.
> In the long run, what you're doing will likely get you into trouble that
> you have to solve with more awkwardness, like by adding DISTINCT
> to queries that shouldn't need it.
> Steve Kass
> Drew University
> Dave wrote:
>
any

Create table from dynamic SP

Hi Everybody
I have a dynamic Sp that return data ( crossta)
I need create a table from SP result.
How can I do that.
Thanks
Pablo Salazar.
Pablo,
possible ways:
1. use (global) temp table
2. use an inline table type variable
3. use insert into your temptable
execute (your proc)
Quentin
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>
|||http://www.sommarskog.se/share_data.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>

Create table from dynamic SP

Hi Everybody
I have a dynamic Sp that return data ( crossta)
I need create a table from SP result.
How can I do that.
Thanks
Pablo Salazar.Pablo,
possible ways:
1. use (global) temp table
2. use an inline table type variable
3. use insert into your temptable
execute (your proc)
Quentin
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>|||http://www.sommarskog.se/share_data.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>

Create table from dynamic SP

Hi Everybody
I have a dynamic Sp that return data ( crossta)
I need create a table from SP result.
How can I do that.
Thanks
Pablo Salazar.Pablo,
possible ways:
1. use (global) temp table
2. use an inline table type variable
3. use insert into your temptable
execute (your proc)
Quentin
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>|||http://www.sommarskog.se/share_data.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>sql

Create Global Variable like VB... can it be done?

I just want to store the value of a parameter in a global variable that all my reports in the same project can use.

My goal is to create a dynamic query. For example:

Company Name: Widgets inc.

Divisions: Sales, Service, Tech, Accounting

I have a matrix and when I click on the more information button it goes to another report. I want the next report to know what division is currently selected in the dropdown parameter. So, being a VB programmer, I thought I could store parameter1.division.value into a global variable and update the variable whenever the parameter changes.

This way, on the next report, my query's where statement is the global variable.

@.GlobalVariable = parameter1.division.value

Select name, address, phone FROM Employee WHERE division = @.GlobalVariable

I am using Visual Studio to design this project although I would prefer to use VB or ASP. But this is my only stumbling block right now. Everything else is complete.

Please let me know if anyone can help.

Thanks.

John

There is not a concept of a global variable for multiple reports that I know of.

You could potentially do something with a table that could store the global value by user id & identifier, and retrieve the variable from the table. Or you could setup a web service to get/set the variable.

You may be able to add a reference to a DLL in every report, and share between them, though I woud think it would be destroyed after the report session times out.

http://www.codeproject.com/dll/data_seg_share.asp

cheers,

Andrew

|||

Looks like you need a report parameter. You don't need to show it to the user, but just default it.

Create Dynamic Table of Contents and Indexes

I was wondering if someone can tell me if there is a way to create a
dynamic table of contents and index for a report that is run.
For example, I need to create a directory of physicians based on a search
query of our database. So each report run would contain a different list of
doctors and locations, and on each query of the report the doctors would
show up on different pages of the report. I want to be able to create a
table of contents and an index for the report that will list the page
number the doctor first shows up on.
With MS Access VBA, I was able to do this by using the print event, in
which I would add each record to the access database table which contains
the table of contents, while the report was printing and therefore creating
the table of contents.
Is there a way (similiar) with SQL RS that I can accomplish this task?
--
Message posted via http://www.sqlmonster.comNot currently. The closest thing available right now is a Document Map.
This creates a bunch of bookmarks when you export to PDF, so that would
create an indexed report/PDF for you.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff005f8e72c242a99456548fc63e62f0@.SQLMonster.com...
>I was wondering if someone can tell me if there is a way to create a
> dynamic table of contents and index for a report that is run.
> For example, I need to create a directory of physicians based on a search
> query of our database. So each report run would contain a different list
> of
> doctors and locations, and on each query of the report the doctors would
> show up on different pages of the report. I want to be able to create a
> table of contents and an index for the report that will list the page
> number the doctor first shows up on.
> With MS Access VBA, I was able to do this by using the print event, in
> which I would add each record to the access database table which contains
> the table of contents, while the report was printing and therefore
> creating
> the table of contents.
> Is there a way (similiar) with SQL RS that I can accomplish this task?
> --
> Message posted via http://www.sqlmonster.com|||Thanks for your response Jeff. Do you know if Crystal Reports or any other
reporting solution can handle the task of creating a Table of Contents and
document Index?
--
Message posted via http://www.sqlmonster.com|||Crystal Reports can do it, but it has to put it at the last page (it
generates the page numbers as it goes and accumulates them for display when
finished printing the records). Not an ideal solution for a TOC.
Brian Bischof
www.CrystalReportsBook.com
"Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> Thanks for your response Jeff. Do you know if Crystal Reports or any other
> reporting solution can handle the task of creating a Table of Contents and
> document Index?
> --
> Message posted via http://www.sqlmonster.com|||This is a multipart message in MIME format
--Xananews.1.2.3
I was given a 'sleazy hack' to do this in RS, it requires the report to
be run twice once so it can work out the page numbers, second time it
can use them in the table of contents. If the table is going at the end
you may be able to change to a run once version! Good luck!
I never said it would be easy!
I've attached the file, and copied it below for those viewing via web.
-- --
Unfortunately, there's currently no good way to build a table of
contents.
And even adding defined execution order isn't going to help, since
pagination happens as a completely separate step from the rest of the
report. This is because you can request the same report with different
page
size/margin settings (and even an entirely different rendering target,
which
could radically change pagination). Under normal circumstances, we don't
want to force reexecution of the entire report just because you change
your
margins.
To handle table of contents type operations, we're going to (eventually)
have to implement an entirely new mode wherein we know that the body of
the
report contains references to page numbers, which will force the entire
report to reexecute if pagination information changes.
In the mean time, there's no general solution for the table of contents
problem.
But... Since you're doing this in a batch once a month, you may not
need a
fully general solution.
Without further ado, here's Sleazy Hack #792 ("Simulating Table of
Contents
in Batch Reporting Scenarios"):
1. Write a custom assembly containing a class that can write rows to a
TableOfContents table in your database.
Have shared methods for both initializing the TOC and writing a new
row
to the TOC.
Note: Don't forget to give it database permissions in the CAS file
when
you deploy it to your server.
See the documentation for details on custom assemblies.
2. In the Code section of the report, call the TOC initialization in
the
OnInit() event.
3. At the start of each section you want to appear in your TOC, put a
hidden textbox that contains the label you want to appear in your TOC.
4. In the page header, put a hidden textbox with something like the
following expression:
=Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Value
& ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
Texbox1, Textbox2 and Textbox3 are the names of the hidden textboxes
from step 3 (since only one will appear on any given page, the rest
will be
empty)
5. Add a dataset to your report which selects from the TableOfContents
table
6. Display the results of the TOC data set in a table at the end of
your
report*
7. Run the report twice. The first time will initialize the TOC. The
second time will use the values from the previous run.
* If you want it at the beginning, you'll either need to run the report
three times (the first to get some rows into the TOC table, the second
to
get the numbers populated correctly and the third to use those numbers)
or
you'll need to make sure the TOC table has the right number of rows to
begin
with (perhaps by not emptying it from the previous month) otherwise your
page numbers will be incorrect due to the TOC pushing things around.
This
isn't an issue if your TOC is only one page long and has PageBreakAtEnd,
however.
-- ---
Brian Bischof wrote:
> Crystal Reports can do it, but it has to put it at the last page (it
> generates the page numbers as it goes and accumulates them for
> display when finished printing the records). Not an ideal solution
> for a TOC.
>
> Brian Bischof
> www.CrystalReportsBook.com
>
> "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > Thanks for your response Jeff. Do you know if Crystal Reports or
> > any other reporting solution can handle the task of creating a
> > Table of Contents and document Index?
> >
> > --
> > Message posted via http://www.sqlmonster.com
--Xananews.1.2.3
Content-Type: Application/Octet-Stream; name=Sleazy Hack #792.txt
Content-Transfer-Encoding: base64
VW5mb3J0dW5hdGVseSwgdGhlcmUncyBjdXJyZW50bHkgbm8gZ29vZCB3YXkgdG8gYnVpbGQgYSB0
YWJsZSBvZiBjb250ZW50cy4NCg0KQW5kIGV2ZW4gYWRkaW5nIGRlZmluZWQgZXhlY3V0aW9uIG9y
ZGVyIGlzbid0IGdvaW5nIHRvIGhlbHAsIHNpbmNlDQpwYWdpbmF0aW9uIGhhcHBlbnMgYXMgYSBj
b21wbGV0ZWx5IHNlcGFyYXRlIHN0ZXAgZnJvbSB0aGUgcmVzdCBvZiB0aGUNCnJlcG9ydC4gIFRo
aXMgaXMgYmVjYXVzZSB5b3UgY2FuIHJlcXVlc3QgdGhlIHNhbWUgcmVwb3J0IHdpdGggZGlmZmVy
ZW50IHBhZ2UNCnNpemUvbWFyZ2luIHNldHRpbmdzIChhbmQgZXZlbiBhbiBlbnRpcmVseSBkaWZm
ZXJlbnQgcmVuZGVyaW5nIHRhcmdldCwgd2hpY2gNCmNvdWxkIHJhZGljYWxseSBjaGFuZ2UgcGFn
aW5hdGlvbikuIFVuZGVyIG5vcm1hbCBjaXJjdW1zdGFuY2VzLCB3ZSBkb24ndA0Kd2FudCB0byBm
b3JjZSByZWV4ZWN1dGlvbiBvZiB0aGUgZW50aXJlIHJlcG9ydCBqdXN0IGJlY2F1c2UgeW91IGNo
YW5nZSB5b3VyDQptYXJnaW5zLg0KVG8gaGFuZGxlIHRhYmxlIG9mIGNvbnRlbnRzIHR5cGUgb3Bl
cmF0aW9ucywgd2UncmUgZ29pbmcgdG8gKGV2ZW50dWFsbHkpDQpoYXZlIHRvIGltcGxlbWVudCBh
biBlbnRpcmVseSBuZXcgbW9kZSB3aGVyZWluIHdlIGtub3cgdGhhdCB0aGUgYm9keSBvZiB0aGUN
CnJlcG9ydCBjb250YWlucyByZWZlcmVuY2VzIHRvIHBhZ2UgbnVtYmVycywgd2hpY2ggd2lsbCBm
b3JjZSB0aGUgZW50aXJlDQpyZXBvcnQgdG8gcmVleGVjdXRlIGlmIHBhZ2luYXRpb24gaW5mb3Jt
YXRpb24gY2hhbmdlcy4NCg0KSW4gdGhlIG1lYW4gdGltZSwgdGhlcmUncyBubyBnZW5lcmFsIHNv
bHV0aW9uIGZvciB0aGUgdGFibGUgb2YgY29udGVudHMNCnByb2JsZW0uDQoNCkJ1dC4uLiBTaW5j
ZSB5b3UncmUgZG9pbmcgdGhpcyBpbiBhIGJhdGNoIG9uY2UgYSBtb250aCwgeW91IG1heSBub3Qg
bmVlZCBhDQpmdWxseSBnZW5lcmFsIHNvbHV0aW9uLg0KDQpXaXRob3V0IGZ1cnRoZXIgYWRvLCBo
ZXJlJ3MgU2xlYXp5IEhhY2sgIzc5MiAoIlNpbXVsYXRpbmcgVGFibGUgb2YgQ29udGVudHMNCmlu
IEJhdGNoIFJlcG9ydGluZyBTY2VuYXJpb3MiKToNCg0KMS4gIFdyaXRlIGEgY3VzdG9tIGFzc2Vt
Ymx5IGNvbnRhaW5pbmcgYSBjbGFzcyB0aGF0IGNhbiB3cml0ZSByb3dzIHRvIGENClRhYmxlT2ZD
b250ZW50cyB0YWJsZSBpbiB5b3VyIGRhdGFiYXNlLg0KICAgIEhhdmUgc2hhcmVkIG1ldGhvZHMg
Zm9yIGJvdGggaW5pdGlhbGl6aW5nIHRoZSBUT0MgYW5kIHdyaXRpbmcgYSBuZXcgcm93DQp0byB0
aGUgVE9DLg0KICAgIE5vdGU6ICBEb24ndCBmb3JnZXQgdG8gZ2l2ZSBpdCBkYXRhYmFzZSBwZXJt
aXNzaW9ucyBpbiB0aGUgQ0FTIGZpbGUgd2hlbg0KeW91IGRlcGxveSBpdCB0byB5b3VyIHNlcnZl
ci4NCiAgICBTZWUgdGhlIGRvY3VtZW50YXRpb24gZm9yIGRldGFpbHMgb24gY3VzdG9tIGFzc2Vt
YmxpZXMuDQoyLiAgSW4gdGhlIENvZGUgc2VjdGlvbiBvZiB0aGUgcmVwb3J0LCBjYWxsIHRoZSBU
T0MgaW5pdGlhbGl6YXRpb24gaW4gdGhlDQpPbkluaXQoKSBldmVudC4NCjMuICBBdCB0aGUgc3Rh
cnQgb2YgZWFjaCBzZWN0aW9uIHlvdSB3YW50IHRvIGFwcGVhciBpbiB5b3VyIFRPQywgcHV0IGEN
CmhpZGRlbiB0ZXh0Ym94IHRoYXQgY29udGFpbnMgdGhlIGxhYmVsIHlvdSB3YW50IHRvIGFwcGVh
ciBpbiB5b3VyIFRPQy4NCjQuICBJbiB0aGUgcGFnZSBoZWFkZXIsIHB1dCBhIGhpZGRlbiB0ZXh0
Ym94IHdpdGggc29tZXRoaW5nIGxpa2UgdGhlDQpmb2xsb3dpbmcgZXhwcmVzc2lvbjoNCiAgICA9
Q29kZS5NeVRPQ0NsYXNzLkFkZFRvVE9DKEdsb2JhbHMuUGFnZU51bWJlcixSZXBvcnRJdGVtcyFU
ZXh0Ym94MS5WYWx1ZQ0KJiBSZXBvcnRJdGVtcyFUZXh0Ym94Mi5WYWx1ZSAmIFJlcG9ydEl0ZW1z
IVRleHRib3gzLlZhbHVlKQ0KICAgIFRleGJveDEsIFRleHRib3gyIGFuZCBUZXh0Ym94MyBhcmUg
dGhlIG5hbWVzIG9mIHRoZSBoaWRkZW4gdGV4dGJveGVzDQpmcm9tIHN0ZXAgMyAoc2luY2Ugb25s
eSBvbmUgd2lsbCBhcHBlYXIgb24gYW55IGdpdmVuIHBhZ2UsIHRoZSByZXN0IHdpbGwgYmUNCmVt
cHR5KQ0KNS4gIEFkZCBhIGRhdGFzZXQgdG8geW91ciByZXBvcnQgd2hpY2ggc2VsZWN0cyBmcm9t
IHRoZSBUYWJsZU9mQ29udGVudHMNCnRhYmxlDQo2LiAgRGlzcGxheSB0aGUgcmVzdWx0cyBvZiB0
aGUgVE9DIGRhdGEgc2V0IGluIGEgdGFibGUgYXQgdGhlIGVuZCBvZiB5b3VyDQpyZXBvcnQqDQo3
LiAgUnVuIHRoZSByZXBvcnQgdHdpY2UuICBUaGUgZmlyc3QgdGltZSB3aWxsIGluaXRpYWxpemUg
dGhlIFRPQy4gIFRoZQ0Kc2Vjb25kIHRpbWUgd2lsbCB1c2UgdGhlIHZhbHVlcyBmcm9tIHRoZSBw
cmV2aW91cyBydW4uDQoNCiogSWYgeW91IHdhbnQgaXQgYXQgdGhlIGJlZ2lubmluZywgeW91J2xs
IGVpdGhlciBuZWVkIHRvIHJ1biB0aGUgcmVwb3J0DQp0aHJlZSB0aW1lcyAodGhlIGZpcnN0IHRv
IGdldCBzb21lIHJvd3MgaW50byB0aGUgVE9DIHRhYmxlLCB0aGUgc2Vjb25kIHRvDQpnZXQgdGhl
IG51bWJlcnMgcG9wdWxhdGVkIGNvcnJlY3RseSBhbmQgdGhlIHRoaXJkIHRvIHVzZSB0aG9zZSBu
dW1iZXJzKSBvcg0KeW91J2xsIG5lZWQgdG8gbWFrZSBzdXJlIHRoZSBUT0MgdGFibGUgaGFzIHRo
ZSByaWdodCBudW1iZXIgb2Ygcm93cyB0byBiZWdpbg0Kd2l0aCAocGVyaGFwcyBieSBub3QgZW1w
dHlpbmcgaXQgZnJvbSB0aGUgcHJldmlvdXMgbW9udGgpIG90aGVyd2lzZSB5b3VyDQpwYWdlIG51
bWJlcnMgd2lsbCBiZSBpbmNvcnJlY3QgZHVlIHRvIHRoZSBUT0MgcHVzaGluZyB0aGluZ3MgYXJv
dW5kLiAgVGhpcw0KaXNuJ3QgYW4gaXNzdWUgaWYgeW91ciBUT0MgaXMgb25seSBvbmUgcGFnZSBs
b25nIGFuZCBoYXMgUGFnZUJyZWFrQXRFbmQsDQpob3dldmVyLg0KDQo=--Xananews.1.2.3--|||You would think that with day and age, Microsoft would be smart enough to
figure out that people might want to create a table of contents.
Thanks Chris, I'll give it a try.
--
Message posted via http://www.sqlmonster.com|||Yeah, but I cut the MS guys some slack on RS features. They have created a
tremendous reporting platform from the ground up in just a couple of years.
I hear they were going to release it with SQL 2005, but bumped it up due to
early demand. Gotta like that. In any case, I'm having fun with it. All
in all, even with its limitations, it's better than any other reporting
solution I've used to date.
</rahrah>
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b01f2ab3065d4d0bb7b57874d7543d59@.SQLMonster.com...
> You would think that with day and age, Microsoft would be smart enough to
> figure out that people might want to create a table of contents.
> Thanks Chris, I'll give it a try.
> --
> Message posted via http://www.sqlmonster.com|||I second that. I ditched the last CR report from our organisation two
weeks ago. I even had a pint or two to celebrate!
I always thought CR was overpriced for what it was, funnily enough now
I don't use it I seem to be getting free upgrades from them! Do you
think they are a little worried? ;)
Chris
Jeff A. Stucker wrote:
> Yeah, but I cut the MS guys some slack on RS features. They have
> created a tremendous reporting platform from the ground up in just a
> couple of years.
> I hear they were going to release it with SQL 2005, but bumped it up
> due to early demand. Gotta like that. In any case, I'm having fun
> with it. All in all, even with its limitations, it's better than any
> other reporting solution I've used to date.
> </rahrah>|||Chris...
Do you have some sample code or can you point me to something in the online
reference on how to add rows to a table from a custom class.
Dave
"Chris McGuigan" wrote:
> I was given a 'sleazy hack' to do this in RS, it requires the report to
> be run twice once so it can work out the page numbers, second time it
> can use them in the table of contents. If the table is going at the end
> you may be able to change to a run once version! Good luck!
> I never said it would be easy!
> I've attached the file, and copied it below for those viewing via web.
> -- --
> Unfortunately, there's currently no good way to build a table of
> contents.
> And even adding defined execution order isn't going to help, since
> pagination happens as a completely separate step from the rest of the
> report. This is because you can request the same report with different
> page
> size/margin settings (and even an entirely different rendering target,
> which
> could radically change pagination). Under normal circumstances, we don't
> want to force reexecution of the entire report just because you change
> your
> margins.
> To handle table of contents type operations, we're going to (eventually)
> have to implement an entirely new mode wherein we know that the body of
> the
> report contains references to page numbers, which will force the entire
> report to reexecute if pagination information changes.
> In the mean time, there's no general solution for the table of contents
> problem.
> But... Since you're doing this in a batch once a month, you may not
> need a
> fully general solution.
> Without further ado, here's Sleazy Hack #792 ("Simulating Table of
> Contents
> in Batch Reporting Scenarios"):
> 1. Write a custom assembly containing a class that can write rows to a
> TableOfContents table in your database.
> Have shared methods for both initializing the TOC and writing a new
> row
> to the TOC.
> Note: Don't forget to give it database permissions in the CAS file
> when
> you deploy it to your server.
> See the documentation for details on custom assemblies.
> 2. In the Code section of the report, call the TOC initialization in
> the
> OnInit() event.
> 3. At the start of each section you want to appear in your TOC, put a
> hidden textbox that contains the label you want to appear in your TOC.
> 4. In the page header, put a hidden textbox with something like the
> following expression:
> =Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Value
> & ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
> Texbox1, Textbox2 and Textbox3 are the names of the hidden textboxes
> from step 3 (since only one will appear on any given page, the rest
> will be
> empty)
> 5. Add a dataset to your report which selects from the TableOfContents
> table
> 6. Display the results of the TOC data set in a table at the end of
> your
> report*
> 7. Run the report twice. The first time will initialize the TOC. The
> second time will use the values from the previous run.
> * If you want it at the beginning, you'll either need to run the report
> three times (the first to get some rows into the TOC table, the second
> to
> get the numbers populated correctly and the third to use those numbers)
> or
> you'll need to make sure the TOC table has the right number of rows to
> begin
> with (perhaps by not emptying it from the previous month) otherwise your
> page numbers will be incorrect due to the TOC pushing things around.
> This
> isn't an issue if your TOC is only one page long and has PageBreakAtEnd,
> however.
> -- ---
>
> Brian Bischof wrote:
> > Crystal Reports can do it, but it has to put it at the last page (it
> > generates the page numbers as it goes and accumulates them for
> > display when finished printing the records). Not an ideal solution
> > for a TOC.
> >
> >
> > Brian Bischof
> > www.CrystalReportsBook.com
> >
> >
> >
> > "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> > news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > > Thanks for your response Jeff. Do you know if Crystal Reports or
> > > any other reporting solution can handle the task of creating a
> > > Table of Contents and document Index?
> > >
> > > --
> > > Message posted via http://www.sqlmonster|||Never mind I misread your solution the first time.
Dave
"Dlloyd" wrote:
> Chris...
> Do you have some sample code or can you point me to something in the online
> reference on how to add rows to a table from a custom class.
> Dave
> "Chris McGuigan" wrote:
> > I was given a 'sleazy hack' to do this in RS, it requires the report to
> > be run twice once so it can work out the page numbers, second time it
> > can use them in the table of contents. If the table is going at the end
> > you may be able to change to a run once version! Good luck!
> > I never said it would be easy!
> >
> > I've attached the file, and copied it below for those viewing via web.
> >
> > -- --
> >
> > Unfortunately, there's currently no good way to build a table of
> > contents.
> >
> > And even adding defined execution order isn't going to help, since
> > pagination happens as a completely separate step from the rest of the
> > report. This is because you can request the same report with different
> > page
> > size/margin settings (and even an entirely different rendering target,
> > which
> > could radically change pagination). Under normal circumstances, we don't
> > want to force reexecution of the entire report just because you change
> > your
> > margins.
> > To handle table of contents type operations, we're going to (eventually)
> > have to implement an entirely new mode wherein we know that the body of
> > the
> > report contains references to page numbers, which will force the entire
> > report to reexecute if pagination information changes.
> >
> > In the mean time, there's no general solution for the table of contents
> > problem.
> >
> > But... Since you're doing this in a batch once a month, you may not
> > need a
> > fully general solution.
> >
> > Without further ado, here's Sleazy Hack #792 ("Simulating Table of
> > Contents
> > in Batch Reporting Scenarios"):
> >
> > 1. Write a custom assembly containing a class that can write rows to a
> > TableOfContents table in your database.
> > Have shared methods for both initializing the TOC and writing a new
> > row
> > to the TOC.
> > Note: Don't forget to give it database permissions in the CAS file
> > when
> > you deploy it to your server.
> > See the documentation for details on custom assemblies.
> > 2. In the Code section of the report, call the TOC initialization in
> > the
> > OnInit() event.
> > 3. At the start of each section you want to appear in your TOC, put a
> > hidden textbox that contains the label you want to appear in your TOC.
> > 4. In the page header, put a hidden textbox with something like the
> > following expression:
> >
> > =Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Value
> > & ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
> > Texbox1, Textbox2 and Textbox3 are the names of the hidden textboxes
> > from step 3 (since only one will appear on any given page, the rest
> > will be
> > empty)
> > 5. Add a dataset to your report which selects from the TableOfContents
> > table
> > 6. Display the results of the TOC data set in a table at the end of
> > your
> > report*
> > 7. Run the report twice. The first time will initialize the TOC. The
> > second time will use the values from the previous run.
> >
> > * If you want it at the beginning, you'll either need to run the report
> > three times (the first to get some rows into the TOC table, the second
> > to
> > get the numbers populated correctly and the third to use those numbers)
> > or
> > you'll need to make sure the TOC table has the right number of rows to
> > begin
> > with (perhaps by not emptying it from the previous month) otherwise your
> > page numbers will be incorrect due to the TOC pushing things around.
> > This
> > isn't an issue if your TOC is only one page long and has PageBreakAtEnd,
> > however.
> >
> > -- ---
> >
> >
> > Brian Bischof wrote:
> >
> > > Crystal Reports can do it, but it has to put it at the last page (it
> > > generates the page numbers as it goes and accumulates them for
> > > display when finished printing the records). Not an ideal solution
> > > for a TOC.
> > >
> > >
> > > Brian Bischof
> > > www.CrystalReportsBook.com
> > >
> > >
> > >
> > > "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> > > news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > > > Thanks for your response Jeff. Do you know if Crystal Reports or
> > > > any other reporting solution can handle the task of creating a
> > > > Table of Contents and document Index?
> > > >
> > > > --
> > > > Message posted via http://www.sqlmonster|||Hi,
I haven't actually used this hack so I don't have an RDL file for you.
Try Tobby Lee, he said he was going to try it out.
If you are stuck creating the custom assembly, have a look at this site;
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx?Pending=true
There are loads of ways to add rows to tables in VB (sorry I don't do
C).
I prefer sending raw SQL commands, but that's just the way I am!
If you're using old ADO (2.6 - 2.8) you will need a connection object
first then you can create a command object and issue a SQL 'insert'
statement. Below is an example BUT it is not 'best practice' nor
particularly efficient in the form I have given you. It should be
enough to get you going though, I'd suggest you get a book on the lines
of SQL for Developers or on ADO.Net (or earlier);
You will need to add a reference to an ADO namespace to your project
for this to work. ADO.Net is significantly different to previous
versions of ADO.
Heres the code;
Public Sub AddLine(Value1 As String, Value2 As String)
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
'Open a connection to the database
'
oCon.ConnectionString = "Provider=SQLOLEDB; Data Source=YourServer;
InitialCatalog=YourDB; User Id=yourid; Password=yourpassword"
oCon.Open
'Set up a command to do the INSERT
'
Set oCon.CommandText = "Insert Into YourTable (Fieldname1,
Fieldname2) Values ('" & Value1 & "','" & Value2 & "')"
'Actually INSERT the record
oCon.Execute
oCon.Close
Set oCon = Nothing
End Sub
Hope it helps ...
Chris
Dlloyd wrote:
> Chris...
> Do you have some sample code or can you point me to something in the
> online reference on how to add rows to a table from a custom class.
> Dave
> "Chris McGuigan" wrote:
> > I was given a 'sleazy hack' to do this in RS, it requires the
> > report to be run twice once so it can work out the page numbers,
> > second time it can use them in the table of contents. If the table
> > is going at the end you may be able to change to a run once
> > version! Good luck! I never said it would be easy!
> >
> > I've attached the file, and copied it below for those viewing via
> > web.
> >
> > -- --
> >
> > Unfortunately, there's currently no good way to build a table of
> > contents.
> >
> > And even adding defined execution order isn't going to help, since
> > pagination happens as a completely separate step from the rest of
> > the report. This is because you can request the same report with
> > different page
> > size/margin settings (and even an entirely different rendering
> > target, which
> > could radically change pagination). Under normal circumstances, we
> > don't want to force reexecution of the entire report just because
> > you change your
> > margins.
> > To handle table of contents type operations, we're going to
> > (eventually) have to implement an entirely new mode wherein we know
> > that the body of the
> > report contains references to page numbers, which will force the
> > entire report to reexecute if pagination information changes.
> >
> > In the mean time, there's no general solution for the table of
> > contents problem.
> >
> > But... Since you're doing this in a batch once a month, you may not
> > need a
> > fully general solution.
> >
> > Without further ado, here's Sleazy Hack #792 ("Simulating Table of
> > Contents
> > in Batch Reporting Scenarios"):
> >
> > 1. Write a custom assembly containing a class that can write rows
> > to a TableOfContents table in your database.
> > Have shared methods for both initializing the TOC and writing a
> > new row
> > to the TOC.
> > Note: Don't forget to give it database permissions in the CAS
> > file when
> > you deploy it to your server.
> > See the documentation for details on custom assemblies.
> > 2. In the Code section of the report, call the TOC initialization
> > in the
> > OnInit() event.
> > 3. At the start of each section you want to appear in your TOC,
> > put a hidden textbox that contains the label you want to appear in
> > your TOC. 4. In the page header, put a hidden textbox with
> > something like the following expression:
> >
> > =Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Va
> > lue & ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
> > Texbox1, Textbox2 and Textbox3 are the names of the hidden
> > textboxes from step 3 (since only one will appear on any given
> > page, the rest will be
> > empty)
> > 5. Add a dataset to your report which selects from the
> > TableOfContents table
> > 6. Display the results of the TOC data set in a table at the end of
> > your
> > report*
> > 7. Run the report twice. The first time will initialize the TOC.
> > The second time will use the values from the previous run.
> >
> > * If you want it at the beginning, you'll either need to run the
> > report three times (the first to get some rows into the TOC table,
> > the second to
> > get the numbers populated correctly and the third to use those
> > numbers) or
> > you'll need to make sure the TOC table has the right number of rows
> > to begin
> > with (perhaps by not emptying it from the previous month) otherwise
> > your page numbers will be incorrect due to the TOC pushing things
> > around. This
> > isn't an issue if your TOC is only one page long and has
> > PageBreakAtEnd, however.
> >
> > -- ---
> >
> >
> > Brian Bischof wrote:
> >
> > > Crystal Reports can do it, but it has to put it at the last page
> > > (it generates the page numbers as it goes and accumulates them for
> > > display when finished printing the records). Not an ideal solution
> > > for a TOC.
> > >
> > >
> > > Brian Bischof
> > > www.CrystalReportsBook.com
> > >
> > >
> > >
> > > "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> > > message news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > > > Thanks for your response Jeff. Do you know if Crystal Reports or
> > > > any other reporting solution can handle the task of creating a
> > > > Table of Contents and document Index?
> > > >
> > > > --
> > > > Message posted via http://www.sqlmonster

Create Dynamic Table of Contents and Indexes

I was wondering if someone can tell me if there is a way to create a dynamic table of contents and index for a report that is run.

For example, I need to create a directory of physicians based on a search query of our database. So each report would contain a list of doctors and locations, but on each query of the report the doctors would show up on different pages of the report. I want to be able to create a table of contents and an index for the report that will list the page number the doctor first shows up on.

With MS Access VBA, I was able to do this by using the print event, in which I would add each record to the access database table which contains the table of contents, while the report was printing and therefore creating the table of contents.

Is there a way (similiar) with SQL RS that I can accomplish this task?

My reports have lots of pages, about 3-20 pages per report, so I too thought about including a table of contents.

Anyone found a solution yet?

Create Dynamic table from CSV

Hi,

I'm trying to import a csv file directly to my database every month. The contents of the file stays the same, however the format of the columns may vary. For example, 1 month I can have the following:

Time, Probe1, Probe2, Probe3, Probe4

Whereas the next month I can have something like this

Time, Probe2, Probe3, Probe1, Probe4

The "Time" column will always be on the left side, but the probes may vary in their placement.

I'm importing this csv to a temp table, where I then run a query to select any new data and enter that in my main tables. The problem is that when i import the csv, if the placement of the columns has changed, the data gets entered in the wrong locations.

Is there any way to create this temp table dynamically, based on what the header columns of the csv file are?

Any help appreciated!

I think the best approach would be to configure your flat file connection manager to read each row as a single column. This column would then be input to a script transformation component which would parse the line into columns and, based on the column order in the header row, put the values into the appropriate output columns.
|||Here is a code sample for a transformation script to illustrate the idea. The Row.Line is the input column that contains an entire line of a text file. This line is taken and split into an array by the comma delimiter. The first time we see the line, it is a header with column names. A boolean flag is used to detect the header condition and we save the column names in a variable. For every subsequent row, we iterate through the column names and match them to an output column that has been defined for the script, assigning the value parsed from the file into the correct column. Using this method, the order of the columns in the text file is irrelevant.

Code Snippet

Dim Line As String = Row.Line
Dim LineTokens As String() = Line.Split(","c)

If IsHeader Then
HeaderColumns = LineTokens
IsHeader = False
Else
' match input columns to output columns
With Output0Buffer
.AddRow()

For i As Integer = 0 To HeaderColumns.Length - 1
Select Case HeaderColumns(i)
Case "Time"
.Time = LineTokens(i)
Case "Probe1"
.Probe1 = LineTokens(i)
Case "Probe2"
.Probe2 = LineTokens(i)
Case "Probe3"
.Probe3 = LineTokens(i)
Case "Probe4"
.Probe4 = LineTokens(i)
End Select
Next

End With
End If

|||

Thanks a lot for the help...

Looks like it'll solve my problem

Thanks!

|||Just make sure you revist the thread and marks it as answered |||

Ok, Since i'm not very proficient with SSIS...

here's what I have going right now.

Under Data Flow, I have a Flat File Source which uses my Flat File connection manager...there's only 1 column, just like you said.

Then I have it going to the transformation script component.

Under Input Columns, I only have Column 0, and its output alias is the same.

Under Inputs & Outputs, I didn't change anything...I didn't add any output columns since I want this to be done dynamically...

Then for the script,

I put in the script you gave me, and this is where i'm having problems...

I renamed Row.Line to Row.Column0,

but there's still a few undefined...such as IsHeader, HeaderColumns, and OutputBuffer.

Where do I define these?!

Thanks!

|||IsHeader and HeaderColumns are global variables that I didn't show in the example. They are shown below would go right under "Inherits UserComponents" in the script.

Code Snippet

Dim IsHeader As Boolean = True
Dim HeaderColumns() As String

Output0Buffer (don't forget the "0") is built for you to the definition that you supply on "Inputs and Outputs". This object will have the same name as your output (without spaces), but with "Buffer" appended to it. You should have one now, but likely just forgot to add the "0" (the default output is "Output 0" ).

You can't leave your output columns blank as they can't be created dynamically. You said your columns were constant, but could appear in different orders in the source. The script will worry about the order of the input columns, but you still need to define the constant output columns.

|||

Beauty.

It's working now.

Thanks a lot for the help.

Create dynamic reports

Hi,

I receive an xml object that represent just about any kind of a table containing unknown amount of text fields, and need to create a Table report out of it, the header fields are not pre-determined.

Is this possible with Crystal reports and in particular in the .Net free addition of Crystal reports ?

if so - HOW ? please explain as articulately as possible

Thanks
. .
|
~

NimCo.You can try using CDO (Crystal Data Objects) to pass info to Crystal Reports. Basically, you populate an array and pass the array to the report.

Look here for an example of CDO in VB 6, maybe you can adapt it to your needs...

http://www.dev-archive.com/forum/showthread.php?s=&threadid=281658&highlight=cdo

Create Dynamic Report

Mr. Babu,

How can I manipulate the query from VB depending on the input entered by the user. In this case I can make one report only and modify the query and pass it to the report.
Otherwise for each user input I will have call a different report.
If possible do send me an example.

Thanks,

SatishHave you try to pass parameters to your report?|||I have tried parameters and it works, but I would like to chang the sql
i.e. select * from .... where empno= variable

regards,
Satish|||This may be more trouble than it's worth...

You could change your report so that VB does all the database queries and passes a recordset to your report.

Example 1:

Dim CrApp1 As New CRPEAuto.Application
Dim CrRep As CRPEAuto.Report
Dim CrDB As CRPEAuto.Database
Dim CrTables As CRPEAuto.DatabaseTables
Dim CrTable As CRPEAuto.DatabaseTable

'open report
Set CrRep = CrApp1.OpenReport("C:\Report.rpt")

'set the database object to the reports database
Set CrDB = CrRep.Database

'Set the databasetables object
Set CrTables = CrDB.Tables

'set the databasetable object to the first table in the report
Set CrTable = CrTables(1)

'sets ADO recordset as the data for the first table
CrTable.SetPrivateData 3, rsData

'preview the report with the ADO recordset as the data
CrRep.Preview

Example 2 (uses RDC, which I like better):

Dim Report As New CrystalReport1

Report.Database.SetDataSource rsData, 3, 1

CRViewer1.ReportSource = Report
CRViewer1.ViewReport

In Example 2, CrystalReport1 is the dsr file created by clicking 'Project', 'Add Crystal Report 8.5' in the VB IDE. CRViewer1 is the Crystal Report Viewer component.

Both of these examples were created using CR 8.5 and VB 6.|||This is in reference to your reply.

In Example 2, CrystalReport1 is the dsr file created by clicking 'Project', 'Add Crystal Report 8.5' in the VB IDE.

When I click Project, I cannot see "Add Crystal Report 8.5".
How do I get it.

Regards,
Satish|||I am not getting CRPEAuto method. What reference has to be made.

what does crpeauto stand for?|||My References for the 'CRPEAuto' Method says 'Crystal Report Engine 8 Object Library'

As for the 'Add Crystal Report 8.5', do you have Crystal Reports Developer Edition installed on your development machine? If so, what version? I think RDC was made avaiable in either version 8 or 8.5 and higher.

You can try to do a search on Crystal's website:
http://support.businessobjects.com/search/advsearch.asp|||how do I get the reference to "'Crystal Report Engine 8 Object Library'" in my computer.|||I'm using version 8.5 Developer Edition. When I installed it, it placed all the necessary dlls on my comuter for me.

Click 'Project', 'References', and check 'Crystal Report Engine 8 Object Library'

Create Dynamic Query based on Report Parameter

I have one Report parameter "Query1". I want to create Dynamic Query, How can I do this?

ex. If I select Station in Query1 parameter then report should generate by Station Dimenstion attribute

[Dim Station].[Station Name].[Station Name].ALLMEMBERS

If I select Free Test in Query1 parameter then report should generate by Free Test Dimension attribute

[Dim Free Test].[Free Test].[Free Test].ALLMEMBERS

My existin query is below:

SELECT NON EMPTY ([Measures].[Total Test Count]) ON COLUMNS,

NON EMPTY STRTOSET(@.Query1) * [Dim OverallResult].[Overall Result].[Overall Result] ON ROWS

FROM [OLAP Test Cube]

Report Parameter Name: Query1

Available Non Queried value in Report Parameter is below:

Lable Value

Station [Dim Station].[Station Name].[Station Name]

Free Test [Dim Free Test].[Free Test].[Free Test]

Default Value is below:

Non-Queried value: [Dim Free Test].[Free Test].[Free Test]

I want to Change Column Alias also through Select MDX Query.

let me know if anybody know this?

anyone know about this?|||

This http://www.databasejournal.com/features/mssql/article.php/10894_3504651 article should give you an idea how to use parameters building MDX queries in Reporting Services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

this is not working for me. any other solution?

in this example they added Dimension member in filter area. I need different dimension in filter area.

Create Dynamic Query based on Report Parameter

I have one Report parameter "Query1". I want to create Dynamic Query, How can I do this?

ex. If I select Station in Query1 parameter then report should generate by Station Dimenstion attribute

[Dim Station].[Station Name].[Station Name].ALLMEMBERS

If I select Free Test in Query1 parameter then report should generate by Free Test Dimension attribute

[Dim Free Test].[Free Test].[Free Test].ALLMEMBERS

My existin query is below:

SELECT NON EMPTY ([Measures].[Total Test Count]) ON COLUMNS,

NON EMPTY STRTOSET(@.Query1) * [Dim OverallResult].[Overall Result].[Overall Result] ON ROWS

FROM [OLAP Test Cube]

Report Parameter Name: Query1

Available Non Queried value in Report Parameter is below:

Lable Value

Station [Dim Station].[Station Name].[Station Name]

Free Test [Dim Free Test].[Free Test].[Free Test]

Default Value is below:

Non-Queried value: [Dim Free Test].[Free Test].[Free Test]

I want to Change Column Alias also through Select MDX Query.

let me know if anybody know this?

anyone know about this?|||

This http://www.databasejournal.com/features/mssql/article.php/10894_3504651 article should give you an idea how to use parameters building MDX queries in Reporting Services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

this is not working for me. any other solution?

in this example they added Dimension member in filter area. I need different dimension in filter area.

create dymanic dts

i can writing dynamic dts package . but not working.

code is here :

Public goPackageOld As New Package
Public goPackage As Package2

Public Sub RunDTS()
Dim goPackage As Package2
goPackage = CType(goPackageOld, Package2)
goPackage.Name = "DTS3"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = CType(2, DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = CType(4096, DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0

Dim oConnProperty As OleDBProperty
'

' create package connection information

'

Dim oConnection As Connection2
'- a new connection defined below.

oConnection = CType(goPackage.Connections.New("DTSFlatFile"), Connection2)

oConnection.ConnectionProperties.Item("Data Source").Value = "C:\hede\50.txt"
oConnection.ConnectionProperties.Item("Mode").Value = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value = "|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value() = True
oConnection.ConnectionProperties.Item("Max characters per delimited column").Value = 8000
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\hede\50.txt"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))

oConnection = CType(goPackage.Connections.New("SQLOLEDB"), Connection2)
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value() = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "**"
oConnection.ConnectionProperties.Item("Data Source").Value = "(local)"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.UserID = "**"
oConnection.Password = "**"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "**"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))
oConnection = Nothing

'

' create package steps information

'

Dim oStep As Step2
Dim oPrecConstraint As PrecedenceConstraint

oStep = CType(goPackage.Steps.New, Step2)
oStep.Name = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.Description = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.ExecutionStatus = CType(1, DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to [(local)].[dbo].[111] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = CType(3, DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
goPackage.SaveToSQLServer("(local)", "**", "**", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "")
Try
goPackage.Execute()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

not have error. try clause is running.but not correct result.

thx..

The Package.Execute method will only thow an exception if it cannot run the package at all, virtual impossible to get. It does not throw an exception if the package fails, as that is still a valid execution.

To capture details of any errors that happend within the package, use the package events provider.

HOW TO: Handle Data Transformation Services Package Events in Visual C# .NET
(http://support.microsoft.com/kb/319985/en-us)

|||

ok i done.

but it didnt have error.

created package in sql server.

but double click on package have error =

Error Source: Microsoft Data Transformation Services(DTS) Package
Error Description: Task 'Copy Data from C:\hede\hede.txt to [(local)].[dbo].[111] Task' was not found.

i want to here : can i do writing Transformation Task Name = Task

Create DTS PACKAGE programmatic

Hi guys..!!

i am working on Dynamic creation of DTS-packages in C#.NET 2005(sql server 200)

but i not must create package in Sql Server.
but i cant...

can i do ?
any ideas ?

thx...

DTS has a Save as VB option. This is a great way of getting sample code, so try that on a demo package. VB to VB.Net has some differences, see this link for some tips.

Converting a DTS Package from Visual Basic 6.0 to Visual Basic .Net
(http://www.sqldts.com/default.aspx?264)

VB.Net to C# should not be hard, plenty of tools that do this.

Hopefully when you view the VB sample code you will get a feel for the object model and how it is used, and going forward you can just write the C# directly.

There is a DTS specific newsgroup (microsoft.public.sqlserver.dts) which may be worth search and posting on, as it is DTS not SSIS focused.

|||

but this source not contain code for vb or vb.net
no problem is writing vb or vb.net

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

thx...

|||

You appear to be repeating yourself, so can we try again...

Mehmet Metin Altuntas wrote:

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

To create a DTS package dynamically in dotnet you need to write some code. DTS uses an object model, have you added a reference?

Mehmet Metin Altuntas wrote:

but this source not contain code for vb or vb.net

What is not source code? I suggested using the Save as VB option to generate some sample code. If you already know how to use the DTS object model in code, what are you asking? If you do not know how to use the DTS object model in code, try generating some sample code as a guide.

Mehmet Metin Altuntas wrote:

no problem is writing vb or vb.net

Sorry, that does not make sense.

The Save as VB option wil give you VB code. The link told you how to convert this to VB.net. There are tools that convert VB.net to C#. It may be a three stage process but it will give you C# code. Once you are familiar with this you can write c# directly, but to HELP you LEARN how to use it in code, Save as VB is a start point.

Surely some code, even VB is easier to learn from than no code at all. There is no Save As C# option, so learn from the VB.

Any help?

Create Cursor with Dynamic SQL

I am attempting to create a store procedure that contains a cursor. I would like to Dynamically create the cursor and I am having difficulty.

Code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

CURSOR cur_gl_bucket
IS
SELECT <column name >
FROM ledger_stat
WHERE <statement>

BEGIN

Select <column name> from table;

OPEN Cursor cur_gl_bucket with retrieved column name.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

The <column name> is selected from a table based on the parameters passed into the procedure. I would then like to OPEN the cursor with the retrieved column name dynamically imbedded in the Select statment for the cursor.

I successfully use DBMS_SQL.PARSE and EXECUTE later in the procedure but have been unable to get it to work when I open the cursor. I guess the question is can this be done, and if so what is the syntax for doing this.....

Regards
Patrick McCarthy
mailto: patrick_mccarthy@.canmail.comLooks like you are on Oracle?

create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

TYPE refcur IS REF CURSOR;
cur_gl_bucket refcur;

v_column_name VARCHAR2(30);

BEGIN

Select <column name> INTO v_column_name from table;

OPEN cur_gl_bucket FOR 'SELECT '||v_column_name||' FROM ledger_stat WHERE <statement>';