How to approach (Trigger-Stored procedure )

Hi All

I need opinions on how to approach my task.
I currently have 3 tables: the master table, the archive and a temp
table.
MASTER: has 3 fields ProductID and ProductNo and Released
ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
TEMP: ProductID, ProductNo, SoldDate

I have a trigger on the master table upon deletion to archive. This is
triggered from a seperate routine from a vb app to delete a record
real time.

CREATE Trigger Archive_Proc On dbo.MASTER
For Delete
As
Declare @.iDate As DateTime
Set @.iDate = GetDate()

If @.@.RowCount = 0 Return
set Nocount on
Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select ProductID, ProductNo, @.iDate from deleted

My problem is that I have a temp table that gets filled from a
seperate transaction.It needs to be matched against the master table
then deleted at both master and temp. but the issue is that the temp
table contains its own SoldDate value that needs to be archived.

Q 1: if I use a stored proc. how do i pass the SoldDate value to the
trigger as Triggers dont use GVs.
Q 2: How do I set up the stored procedure to delete with multiple
tables. I can get it to UPDATE but not delete...

CREATE PROCEDURE COMPARESOLD
@.Pool Smallint
AS
Set NoCount on

Update MASTER
Set Released = 2
From TEMP, MASTER
Where TEMP.ProductNo = MASTER.ProductNo
AND TEMP.ProductID = MASTER.ProductID
AND INVENTORY.Released = 1

hopefully someone can lead me to the right direction...
ThanksHi

I have not picked up the exact relationship between temp and the Master
table. Posing correct DDL (using the scripting options in Enterprise Manager
or the QA Object browser) and example data as inserts statements go a long
way to remove this sort of ambiguity.

It seems that you should have a FK from the TEMP table to the Master that
cascades on delete.

It may also be possible to change the trigger so that it picks up the
SoldDate from TEMP such as:

Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select d.ProductID, d.ProductNo, ISNULL(t.SoldDate,@.iDate)
from deleted d LEFT JOIN Temp t on d.ProductID = t.ProductID

You may want something slightly different if there are multiple TEMP
records.

John

"Alvin" <josesievert@.earthlink.net> wrote in message
news:cc27243c.0311061338.45d42f52@.posting.google.c om...
> Hi All
> I need opinions on how to approach my task.
> I currently have 3 tables: the master table, the archive and a temp
> table.
> MASTER: has 3 fields ProductID and ProductNo and Released
> ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
> TEMP: ProductID, ProductNo, SoldDate
> I have a trigger on the master table upon deletion to archive. This is
> triggered from a seperate routine from a vb app to delete a record
> real time.
> CREATE Trigger Archive_Proc On dbo.MASTER
> For Delete
> As
> Declare @.iDate As DateTime
> Set @.iDate = GetDate()
> If @.@.RowCount = 0 Return
> set Nocount on
> Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
> Select ProductID, ProductNo, @.iDate from deleted
>
> My problem is that I have a temp table that gets filled from a
> seperate transaction.It needs to be matched against the master table
> then deleted at both master and temp. but the issue is that the temp
> table contains its own SoldDate value that needs to be archived.
> Q 1: if I use a stored proc. how do i pass the SoldDate value to the
> trigger as Triggers dont use GVs.
> Q 2: How do I set up the stored procedure to delete with multiple
> tables. I can get it to UPDATE but not delete...
> CREATE PROCEDURE COMPARESOLD
> @.Pool Smallint
> AS
> Set NoCount on
> Update MASTER
> Set Released = 2
> From TEMP, MASTER
> Where TEMP.ProductNo = MASTER.ProductNo
> AND TEMP.ProductID = MASTER.ProductID
> AND INVENTORY.Released = 1
>
> hopefully someone can lead me to the right direction...
> Thanks

How to apply complex constraints

Hi all!
I want to create a constraint that uses data from other tables,
specifically i want to make sure that a varchar has exactly the length
specified in an integer-column in a table that I pointed out with a
foreign key.

I would like this to be solved something like this:

create table string_size_limits
(
row_id INTEGER PRIMARY KEY
string_size INTEGER
)

create table strings
(
string_size_limit_row_id INTEGER
REFERENCES string_size_limits(row_id)
string varcher(50)
CONSTRAINT check_string_size CHECK ?
)

Is it possible to solve this problem without SP using above model?
Is it possible to solve this problem with SP using above model?
Must the above problem be solved using triggers?

Any help appreciated"Jon" <jonsjostedt@.hotmail.com> wrote in message
news:9f379edc.0503150952.59caa802@.posting.google.c om...
> Hi all!
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
> I would like this to be solved something like this:
> create table string_size_limits
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
> create table strings
> (
> string_size_limit_row_id INTEGER
> REFERENCES string_size_limits(row_id)
> string varcher(50)
> CONSTRAINT check_string_size CHECK ?
> )
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?
> Any help appreciated

A CHECK constraint can only access data in the table it's created on, but in
your example, what is the purpose of Row_ID as a primary key? In other
words, what is the difference between these two limits:

insert into string_size_limits select 1, 5
insert into string_size_limits select 2, 5

Is the second string size limit somehow different because its row_id is
different? If the primary key of your limits table was just the string size
itself, then the foreign key could be used in the CHECK constraint:

create table dbo.StringSizes (
StringSize int not null,
constraint PK_StringSizes primary key (StringSize)
)

create table dbo.Strings
(
StringSize int not null,
String varchar(50) not null,
constraint PK_Strings primary key (String),
constraint FK_Strings_StringSizes foreign key (StringSize)
references StringSizes (StringSize),
constraint CHK_StringLength check (len(String) = StringSize)
)

insert into dbo.StringSizes select 3
insert into dbo.StringSizes select 5

insert into dbo.Strings select 3, 'Jon'
insert into dbo.Strings select 3, 'John' -- Fails
insert into dbo.Strings select 5, 'Check'
insert into dbo.Strings select 5, 'Cheque' -- Fails

If that doesn't help, I suggest you give some more details on Row_Id, and
also working CREATE TABLE and INSERT statements. But if you can't use the
string size limit itself in the foreign key, a trigger is the most likely
alternative.

Simon|||Jon (jonsjostedt@.hotmail.com) writes:
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
> I would like this to be solved something like this:
> create table string_size_limits
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
> create table strings
> (
> string_size_limit_row_id INTEGER
> REFERENCES string_size_limits(row_id)
> string varcher(50)
> CONSTRAINT check_string_size CHECK ?
> )
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?

The problem does not need be solved with triggers, but that's the best
solution.

The alternative is to write a UDF which access the string_size_limits
table, and then you CHECK constraint would read:

CHECK (len(string) = dbo.maxlen(row_id))

The reason you should not do this, is because the performance penalty
can be severe. I remember that I played with this once, and added a
constraint with a UDF to the copy of an existing table. I then inserted
all 24000 rows into that table. Instead of two seconds it took 30!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In full SQL-92, you can write such a CHECK() constraint, but not in SQL
Server yet. You would have to use a trigger and get away from
declarative code.

However, why are you putting metadata into the database in violation of
basic design principles? This is sooo wrong.|||"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de
news:1110989814.440585.47880@.o13g2000cwo.googlegro ups.com...
> In full SQL-92, you can write such a CHECK() constraint, but not in SQL
> Server yet. You would have to use a trigger and get away from
> declarative code.
> However, why are you putting metadata into the database in violation of
> basic design principles? This is sooo wrong.

Why is this so wrong? How about a link to thoes basic design principles?
Where do you think metadata should be stored?

How to add reference to SSIS script task

Hi ALL

I cannot add the reference to the following:
"Imports Microsoft.AnalysisServices.AdomdClient"
in SSIS script task. All the objects that i declared become underlined because there is no reference to the above library.
When i go in SSIS script task and open the code to add the reference it does not the list the above library.
I am able to add reference in Visualstudio but not in SSIS.

Please adivse, it is very critical and urgent for my task

Thanks.

Does this help?

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx

Ed

|||

As Ed says, you will need to copy the DLL. Note that you only need to do this at design-time so you won't need to do it on your production server.

-Jamie

|||

Hi Jamie / Ed

Thanks for the reply, it did work for me. Appreciate your help.

Thanks

How to add new columns to the Table

Hi all

I am new to crystal and i am working on Crystal 9

How do i add columns to the table referenced in the connection of the report..

Let me make it clear.Assume that i have a table with 10 columns and i had created the report by connecting to the DB and run the report...

Now i add another 3 columns to that table and i need to have these 3 columns also in the report..Since we dont have option of editing SQL Query in Crystal 9

Can any one tell me whether is there any option such that when ever there are changes in the table they get reflected automatically to the report i mean when i open the report and click on the table can i see 13 columns instead of 10 and if not then how do i acheive that with out deleting the previous connection bcos there are lots of fileds refred in the report using the first 10 columns..

Thanks in Advance

MoaOpen the Report and Do verify Database. Now it will be updated with latest structure

How to Add Crystal Reports 9 into VB 6 Project

Hi! All!

I can't add Crystal Report 9 into VB 6 Project.

I already Create Crystal Report 9 and I try to add Crystal Reports controls into VB 6 application Project -- > components & reference also.

What are all the controls(dll or ocx) to be added in VB application components & reference for import Crystal Reports 9 into VB 6 Application.

Can any one tell me how to add Crystal Reports 9 into VB 6.

Thanks

- RajaExtracted from --> http://www.mahipalreddy.com/cr/crcodes1.htm
'Code by Mahipal Padigela
'Add a Command button to your Form(Form1)
'Add a Form(Form2) to the project and add a 'Crystal Report Viewer Control 9' from Project-->Components to it.
'Reference 'Crystal Reports 9 ActiveX Designer Run Time Library' from Project-->References
'Replace "C:\myCRReport.rpt" with your CR Report file path
'Replace the value of strSelectionfrm variable with your selection formula
'Paste the following in to the code window of your form

Private Sub Command1_Click()
Dim crApp As CRAXDRT.Application
Dim Report As CRAXDRT.Report
Dim strSelectionfrm As String
Set crApp = New CRAXDRT.Application
Set Report = crApp.OpenReport("C:\myCRReport.rpt")

'You don't need the next line of code if you are using a MS Access databse, but if you are using
'any other databse then Crystal reports expects you to pass Logon credentials before opening the report. So replace the
'DSN name, Databse name, user name, password with yours. Though the next line of code is passing Logon info to
'only the first table in the Tables array of the report, in theory, it should work fine as crystal passes the same info to any other
'tables in the report.For more info on passing Logon credentials to each and every table in the main report as well as all subreports,
'please refer to my article programmatically exporting Crystal Reports to PDF and other formats

Report.Database.Tables(1).SetLogOnInfo "DSN name", "Databse name", "user name", "password"

'Replace the value of strSelectionfrm variable with your selection formula
strSelectionfrm = "{Expected_Sales.Dept}='Analyst Group' and {Expected_Sales.Qtr}='Qtr1'"
Report.RecordSelectionFormula = strSelectionfrm
Report.DiscardSavedData
Form2.CRViewer91.ReportSource = Report
Form2.CRViewer91.ViewReport
Form2.Show
End Sub|||Tq for reply.

I already add Crystal Reports controls in Project components & also Reference.

But still I can't add Crystal Report into my VB 6 Project.

I attach with screen shots for adding components & references for Crystal Reports 9 controls.

http://www.dev-archive.com/forum/attachment.php?attachmentid=&stc=1

After add these controls we can directly add crystal report from Project Menu (like Add Crystal Report).

Is it true or not?

If it is true there is no more option to add Crystal Report in Project Menu.

I can't use Crystal Reports coding like

Dim crApp As CRAXDRT.Application
Dim Report As CRAXDRT.Report
Dim strSelectionfrm As String
Set crApp = New CRAXDRT.Application
Set Report = crApp.OpenReport("C:\myCRReport.rpt")

in my Project.

Can you please tell me what's wrong in my project.

Is Crystal Reports 9 Problems or VB 6 Problems?

Thanks.

- Raja|||either you i havent understood exactly what you are trying to ask,
but if no,
then you can just add .rpt file of the Crystal Reports,like this..
right click Project in solution Explorer, Add->File-> and just choose your .rpt file. VB6 will attatch the .rpt file as .dsr file.and you can directly use its relative path,by creating instance of that .dsr file.thats it.|||Tq for reply.

I already add .rpt file into my VB project. But it is saved as in module(Not in .dsr file).

Is any other crystal controls needed to add in VB Project?

I can try to Reinstall Crystl Reports 9 & add controls into VB Project again.

Still I can't find out any other controls related to Crystal Report Designer or Viewer in my VB tool box controls.

What I need to change for add Crystal Reports 9 into my VB project?

Can anyone send more details about these problem.

Thanks.

- Raja

How to add a image to the ReportVeiwer

Good day all

I have the vwd 2005 express and am playing around with the report veiwer.

How can i put the image on my report ( as a header) . I have draged the image control from the toolbox onto my Report.rdlc form , but it,s not the same as the image control in the toolbox for the "aspx" forms. I cannot right click and set a image URL.

There is a backgroundImage property but the wording is unfamilier to me eg, Source.. External , Value... MIMEType etc.

Can anyone help with getting the image into the report?

Thanks

Rob

Hi Rob,

The only way to add image in the report is by using the image control, and yes its different from the one for the aspx pages.

When you place a image control on the form an image wizard will be opened where you can choose whether you want the image to be embadded into the report itself/use a common image for a project/image stored in database/use an image hosted on net.

Just select the appropriate option and select the image. For more information check these links -

http://msdn2.microsoft.com/en-us/library/ms156388.aspx

http://msdn2.microsoft.com/en-us/library/ms156482.aspx