Parent Table Control

Hi All,
I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
UPDATED, DELETED.
If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
I would like know if have way to I specifique that DELETE can be used only
in 'TPARENT'
If user try use DELETE in CLILD he receive a error, user can use DELETE
only in TPARENT.
I did try use triger, but if I try DELET of TPARENT I also receive the
error, I want receive erro on;y if I try use DELETE on CLILD
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
AND type = 'TR')
DROP TRIGGER BlockDeleteOnDomiciliosBancarios
GO
CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
ON DomiciliosBancarios
FOR DELETE
AS
BEGIN
ROLLBACK TRANSACTION
PRINT ('No possvel apagar de DomiciliosBancarios')
END
can anyone help-me -- Thanks
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPAI VALUES ('Test 01')
INSERT INTO TPAI VALUES ('Test 02')
INSERT INTO TPAI VALUES ('Test 03')
---
CREATE TABLE CLILD
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to delete
rows in TPARENT table. However, I am not sure what's the exact error
message when "If user try use DELETE in CLILD he receive a error, user can
use DELETE only in TPARENT." Would you please help describe it further? If
I have misunderstood your concern, please feel free to point it out.
Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
CLILD table's related rows will also be deleted when you are deleting
TPARENT rows.
Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
while leave the rows in CLILD. The related rows in CLILD will be also
deleted. If you want to delete rows in TPARENT and not delete rows in
CLILD, you must specify your won trigger to accomplish this instead of
using FOREIGN KEY.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Here is a suggestion: Use both a hidden table and a public view for
CLILD. Put the referential integrity on the hidden table, and put
an INSTEAD OF trigger on the view that generates an error and
does not delete anything. If you need to, roll back the transaction
in the trigger also, but since it is an INSTEAD OF trigger, there is
no DELETE to roll back. Here is a repro:
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPARENT VALUES ('Test 01')
INSERT INTO TPARENT VALUES ('Test 02')
INSERT INTO TPARENT VALUES ('Test 03')
-- Hidden table with foreign key constraint
CREATE TABLE CLILD_hidden
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)
go
-- view that is used externally as the table
create view CLILD as
select TPARENT from CLILD_hidden
go
insert into CLILD VALUES ('Test 01')
insert into CLILD VALUES ('Test 02')
insert into CLILD VALUES ('Test 03')
go
-- do not allow delete from the view
CREATE TRIGGER BlockDeleteOnCLILD
ON CLILD INSTEAD OF DELETE
AS
-- ROLLBACK TRANSACTION -- if necessary for other reasons
PRINT ('No possvel apagar de CLILD')
go
select * from CLILD
go
delete from TPARENT
where TPARENT = 'Test 01'
go
select * from CLILD
go
delete from CLILD
where TPARENT = 'Test 02'
go
delete from TPARENT
where TPARENT = 'Test 03'
go
select * from CLILD
go
-- drop view CLILD
-- drop table CLILD_hidden, TPARENT
-- Steve Kass
-- Drew University
ReTF wrote:

>Hi All,
>I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
>UPDATED, DELETED.
>If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
>I would like know if have way to I specifique that DELETE can be used only
>in 'TPARENT'
>If user try use DELETE in CLILD he receive a error, user can use DELETE
>only in TPARENT.
>I did try use triger, but if I try DELET of TPARENT I also receive the
>error, I want receive erro on;y if I try use DELETE on CLILD
>IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
> AND type = 'TR')
> DROP TRIGGER BlockDeleteOnDomiciliosBancarios
>GO
>CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
>ON DomiciliosBancarios
>FOR DELETE
>AS
>BEGIN
> ROLLBACK TRANSACTION
> PRINT ('No possvel apagar de DomiciliosBancarios')
>END
>can anyone help-me -- Thanks
>CREATE TABLE TPARENT
>(
> CONSTRAINT pk_TPARENT
> PRIMARY KEY(TPARENT),
> TPARENT CHAR(30)
> NOT NULL
> )
>INSERT INTO TPAI VALUES ('Test 01')
>INSERT INTO TPAI VALUES ('Test 02')
>INSERT INTO TPAI VALUES ('Test 03')
>---
>CREATE TABLE CLILD
>(
> CONSTRAINT fk_CLILD
> FOREIGN KEY(TPARENT )
> References TPARENT (TPARENT )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> TPARENT CHAR(30)
> NOT NULL
> )
>
>|||Hi,
I want block if the user try DELETE of CLILD, the user can DELETE only of
TPARENT .
Because CLILD has ON DELETE/UPDATE CASCADE, when TPARENT (row) is deleted in
CLILD this also deleted.
But if user try use DELETE direct in CLILD the user must receive a error,
the user can only use DELETE in TPARENT no in childs.
sorry about my english, this is not my native language, if you don't
understand let-me know and I will explain again. Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> escreveu na mensagem
news:azlw3bxlFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know how to delete
> rows in TPARENT table. However, I am not sure what's the exact error
> message when "If user try use DELETE in CLILD he receive a error, user
> can
> use DELETE only in TPARENT." Would you please help describe it further? If
> I have misunderstood your concern, please feel free to point it out.
> Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
> CLILD table's related rows will also be deleted when you are deleting
> TPARENT rows.
> Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
> while leave the rows in CLILD. The related rows in CLILD will be also
> deleted. If you want to delete rows in TPARENT and not delete rows in
> CLILD, you must specify your won trigger to accomplish this instead of
> using FOREIGN KEY.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi,
Thanks for your reply.
I understood you request as below
1) User is not able to delete from CLILD table
2) User could delete from TPARENT table
3) When rows in TPARENT table is deleted, remain the related rows in CLILD
table.
If I have misunderstood your concern, please feel free to point it out.
To accomplish this, you cannot use Foreign Key in your tables. As I have
said before, you'd better create your own triggers to do so.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Paremeter Control Greyed out

I have a balance sheet report with only one parameter, Month.
The parameter is a query and the default for this parameter is a query to
determine the current month.
We schedule this report to run every hour because managers don't like to
wait 5 minutes for a report to generate. The problem is, we can't change the
parameter because it is greyed out. Is there a setting that would allow
people to choose a new month from the drop-down control even if the report is
on a schedule?You should be able to go into Report Manager and select Prompt User
checkbox. Or you can set the PromptUser property in the RDL to true.
--
| Thread-Topic: Paremeter Control Greyed out
| thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==| X-WBNR-Posting-Host: 12.38.198.125
| From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
| Subject: Paremeter Control Greyed out
| Date: Tue, 3 May 2005 07:30:19 -0700
| Lines: 10
| Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a balance sheet report with only one parameter, Month.
|
| The parameter is a query and the default for this parameter is a query to
| determine the current month.
|
| We schedule this report to run every hour because managers don't like to
| wait 5 minutes for a report to generate. The problem is, we can't change
the
| parameter because it is greyed out. Is there a setting that would allow
| people to choose a new month from the drop-down control even if the
report is
| on a schedule?
||||You are refering to PROPERTIES>PARAMETERS for the report itself correct?
It's already checked.
""Brad Syputa - MS"" wrote:
> You should be able to go into Report Manager and select Prompt User
> checkbox. Or you can set the PromptUser property in the RDL to true.
> --
> | Thread-Topic: Paremeter Control Greyed out
> | thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==> | X-WBNR-Posting-Host: 12.38.198.125
> | From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
> | Subject: Paremeter Control Greyed out
> | Date: Tue, 3 May 2005 07:30:19 -0700
> | Lines: 10
> | Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have a balance sheet report with only one parameter, Month.
> |
> | The parameter is a query and the default for this parameter is a query to
> | determine the current month.
> |
> | We schedule this report to run every hour because managers don't like to
> | wait 5 minutes for a report to generate. The problem is, we can't change
> the
> | parameter because it is greyed out. Is there a setting that would allow
> | people to choose a new month from the drop-down control even if the
> report is
> | on a schedule?
> |
>

ParameterValueClass for setting Parameters Property of ReportViewer

I am trying to set the ReportViewer (the one that runs as a .net control)
Parameters property, which is looking for an array of ParameterValue
objects. Where is the ParameterValue object defined? I cannot find it in
the ReportViewer, or the Web Services. Any ideas how to pass a set of
parameneters to the Parameters property of the ReportViewer?
Thanx, BobAre you referring to the ReportViewer sample control that ships with
Reporting Services?
The Parameters property of the control refers specifically to the parameters
area of the toolbar which can display input fields for report parameters.
This is not a property that can be used to pass report parameters. The
ReportViewer sample utilizes the report server built in parameters support.
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"Bob Feller" <bob@.nospam.morningdew.net> wrote in message
news:eoz$ml2aEHA.1644@.tk2msftngp13.phx.gbl...
> I am trying to set the ReportViewer (the one that runs as a .net control)
> Parameters property, which is looking for an array of ParameterValue
> objects. Where is the ParameterValue object defined? I cannot find it in
> the ReportViewer, or the Web Services. Any ideas how to pass a set of
> parameneters to the Parameters property of the ReportViewer?
> Thanx, Bob
>

Parameters in Reporting Services...

Is it possible to control a parameter type as being single vs. multi-valued
based on the value selected in the previous parameter?
Thank you
Ramdaskeep it multi - but only show one value to select if previous parameter
selection so indicates.
"Ram" wrote:
> Is it possible to control a parameter type as being single vs. multi-valued
> based on the value selected in the previous parameter?
> Thank you
> Ramdas|||Hi,
Thanks for the tip. How would i show only one value based on the previous
parameter selection.
Thank you
Ramdas
"Jimbo" wrote:
> keep it multi - but only show one value to select if previous parameter
> selection so indicates.
>
>
> "Ram" wrote:
> > Is it possible to control a parameter type as being single vs. multi-valued
> > based on the value selected in the previous parameter?
> >
> > Thank you
> > Ramdas|||use a stored procedure to populate your select list - one of the parameters
for this stored procedure would indicate whether the return list will be
multiple records or a single record
this parameter would be set by user selection before being passed to the
stored procedure
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> Hi,
> Thanks for the tip. How would i show only one value based on the previous
> parameter selection.
> Thank you
> Ramdas
> "Jimbo" wrote:
>> keep it multi - but only show one value to select if previous parameter
>> selection so indicates.
>>
>>
>> "Ram" wrote:
>> > Is it possible to control a parameter type as being single vs.
>> > multi-valued
>> > based on the value selected in the previous parameter?
>> >
>> > Thank you
>> > Ramdas|||Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.
"Jim" wrote:
> use a stored procedure to populate your select list - one of the parameters
> for this stored procedure would indicate whether the return list will be
> multiple records or a single record
> this parameter would be set by user selection before being passed to the
> stored procedure
>
>
>
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> > Hi,
> > Thanks for the tip. How would i show only one value based on the previous
> > parameter selection.
> >
> > Thank you
> > Ramdas
> >
> > "Jimbo" wrote:
> >
> >> keep it multi - but only show one value to select if previous parameter
> >> selection so indicates.
> >>
> >>
> >>
> >>
> >> "Ram" wrote:
> >>
> >> > Is it possible to control a parameter type as being single vs.
> >> > multi-valued
> >> > based on the value selected in the previous parameter?
> >> >
> >> > Thank you
> >> > Ramdas
>
>

Parameters

As a new person to SQL RS, With the report date parameters, can you have a
date control to choose from the calendar?Hi,
With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
Jan Pieter Posthuma
"Mike R" wrote:
> As a new person to SQL RS, With the report date parameters, can you have a
> date control to choose from the calendar?
>
>|||"Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> wrote in message
news:E705C246-22A2-4FC7-B60D-1E7465CB03F6@.microsoft.com...
> Hi,
> With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
> Jan Pieter Posthuma
> "Mike R" wrote:
>> As a new person to SQL RS, With the report date parameters, can you have
>> a
>> date control to choose from the calendar?
>>
Thanks, Whens SRS2005 available?|||November is the promised release date.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike R" <news@.mikeread.freeserve.co.uk> wrote in message
news:d9lvqc$6vj$1$8302bc10@.news.demon.co.uk...
> "Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> wrote in message
> news:E705C246-22A2-4FC7-B60D-1E7465CB03F6@.microsoft.com...
>> Hi,
>> With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
>> Jan Pieter Posthuma
>> "Mike R" wrote:
>> As a new person to SQL RS, With the report date parameters, can you have
>> a
>> date control to choose from the calendar?
>>
> Thanks, Whens SRS2005 available?
>

Parameterized configuratiion in SSIS

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package. That you can control via a parameter in some way?

For example, one configuration for each country.

Thankful for any help!

//Patrick

Assuming you're kicking off the package with a SQL Agent job, you may find the following code we're using helpful. I have highlighted the section dealing with assigning a configuration file programatically. In your case you can modify the code to point to different configuration files depending on a country parameter.

Hope this helps.

ALTER PROCEDURE [dbo].[UTIL_StartPackageWithDate]

(

@.Packagename VARCHAR(255),

@.InventoryPeriodDate datetime

)

AS

BEGIN

PRINT suser_sname()

DECLARE @.ReturnCode INT

SET @.ReturnCode = 0

DECLARE @.jobId BINARY(16)

DECLARE @.JobName varchar(255)

DECLARE @.cmdline varchar(MAX)

DECLARE @.PackageNameRoot varchar(255)

-- get root of package name

DECLARE @.I int

DECLARE @.X INT

DECLARE @.Done BIT

SET @.Done = 0

SET @.I = 0

WHILE @.Done = 0

BEGIN

SET @.X = CHARINDEX('\',@.PackageName,@.I+1)

IF @.X = 0

BEGIN

SET @.Done = 1

END ELSE BEGIN

SET @.I = @.X

END

END

SET @.PackageNameRoot = SUBSTRING(@.PackageName,@.I+1,LEN(@.PackageName)-@.I)

Print @.PackageNameRoot

SET @.JobName = N'StartPackage_' + @.PackageNameRoot

SET @.cmdline = N'/SQL "' + @.PackageName + '" /SERVER "' + @.@.ServerName + '" /MAXCONCURRENT " 1 " /CHECKPOINTING OFF '

--+ ' /LOGGER "DTS.LogProviderTextFile.1";"packagelog.log" '

--+ ' /CONNECTION "PackageLog.log";"O:\Shared\Logs\' + + @.PackageNameRoot + '.log"'

+ ' /CONFIGFILE "O:\Shared\Configuration\PackageConfig.dtsConfig" '

+ ' /SET "\Package.variables[InventoryPeriodDate]";"' + CONVERT(varchar(10),@.InventoryPeriodDate,120) + '"'

PRINT @.cmdline

IF EXISTS( SELECT * FROM msdb.dbo.sysjobs_view WHERE [name] = @.JobName)

BEGIN

-- exists, so, delete the steps

SELECT @.JobID = job_id

FROM msdb.dbo.sysjobs_view

WHERE [name] = @.JobName

EXECUTE msdb.dbo.sp_update_jobstep

@.job_id = @.JobID,

@.step_id = 1,

@.subsystem=N'SSIS',

@.proxy_name=N'SSISProxy',

@.command = @.cmdLine

END

ELSE

BEGIN

-- Create the job

EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=@.JobName,

@.enabled=1,

@.notify_level_eventlog=0,

@.notify_level_email=0,

@.notify_level_netsend=0,

@.notify_level_page=0,

@.delete_level=0,

@.description=N'THIS JOB IS MAINTAINED AUTOMATICALLY, DO NOT MANUALLY ALTER',

@.category_name=N'[Uncategorized (Local)]',

@.owner_login_name=N'ardent',

@.job_id = @.jobId OUTPUT

-- create the new step

EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'StartPackage',

@.step_id=1,

@.cmdexec_success_code=0,

@.on_success_action=1,

@.on_success_step_id=0,

@.on_fail_action=2,

@.on_fail_step_id=0,

@.retry_attempts=0,

@.retry_interval=0,

@.os_run_priority=0,

@.subsystem=N'SSIS',

@.command=@.cmdLine,

@.proxy_name=N'SSISProxy',

--@.output_file_name=N'C:\datetestlog',

@.flags=0

EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1

EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'

END

EXEC msdb.dbo.sp_start_job @.job_id = @.JobID

END

--select * from msdb.dbo.sysjobs_view

--exec msdb.dbo.sp_help_job @.job_name = 'StartPackage_datetest'

|||

Patrick B wrote:

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package.

Yes, that's possible.

Patrick B wrote:

That you can control via a parameter in some way?

Sorry, not quite sure what you mean by this. Can you explain your scenario?

Patrick B wrote:

For example, one configuration for each country.

Do you mean that you want to selectively choose which configuration to apply? There are ways to do that and Johan alluded to one way of accomplishing it above. Basically instead of defining the configuration files at design-time, you tell the package at execution-time which configuration file to use. I'm guessing that there is more than one way of skinning this particular cat though.

Is this what you mean?

-Jamie