Showing posts with label implement. Show all posts
Showing posts with label implement. Show all posts

Tuesday, March 27, 2012

delete constraint

how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.INSTEAD OF DELETE triggers|||so u mean using triggers instead of delete cascade? i can check if that record exists in other tables using triggers and take necessary action but i want to return relevant message to user if it couldn't be deleted. how can i do that?|||Have a look at RAISERROR|||i was able to manage the following code but couldn't get the message displayed when user tries to delete the record. if i run the stored proc from query analyzer, i get the following error message but not in my application. i have tried catching any exceptions using ex.Message but can't get the error. so how can i get the error displayed when user violates the delete constraint? i have used ExecuteNonQuery command.


IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR ('Cannot delete this record. Make sure that this record is not used in other tables',9,1)
RETURN
END
|||Try ON DELETE {NO ACTION and ON UPDATE {NO ACTION, this will not allow Deletes and Updates. Hope this helps.

Kind regards,
Gift Peddie|||but i want the message to be returned if it couldn't be deleted so that relevant message can be displayed to the user.|||CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON UPDATE NO ACTION,
qty_ordered int)
GO

Run a search in the BOL(books online) for Cascade Delete, the following code is from the BOL it means deletes or updates will fail with an error message. Hope this helps.

Kind regards,
Gift Peddie|||but how can we be sure about the source of error this way? with raiserror as i have done, i can get the message if there are other errors as well. else i don't get any error message. isn't that suupposed to return error message?|||The error from NO ACTION is ANSI SQL from DRI(Declarative Referential Integrity) rules but if you prefer Raise Error you can use it run a search for raise error in the BOL(books online). Hope this helps.

Kind regards,
Gift Peddie|||

I do it this way, for now. Probably better way would be to make Delete function that returns an error message rather then raising new exception. Maybe even make a some kind of class that interprets these error numbers and returns standard error message.

In SqlDataProvider for module

PublicOverridesSub DeleteClientDepartmentsItem(ByVal itemIDAsInteger)

Try

SqlHelper.ExecuteNonQuery(_connectionString, _databaseOwner & _objectQualifier & _

"esr_ClientDepartments_Delete", itemID)

Catch exAs SqlException

If ex.Number = 547Then

ThrowNew Exception("This record cannot be deleted due to its association with other records.")

EndIf

EndTry

EndSub

'in the page:

Try

Dim cdcAsNew ClientDepartmentsController

Dim departmentIdAsInteger =CType(dgDepartments.DataKeys(e.Item.ItemIndex),Integer)

cdc.Delete(departmentId)

BinddgDepartments()

Catch exAs Exception

DotNetNuke.UI.Skins.Skin.AddModuleMessage(Me, ex.Message, Skins.Controls.ModuleMessage.ModuleMessageType.YellowWarning)

EndTry

Friday, February 24, 2012

default values

Hi All,
I'm trying to implement a merge replication of our database, every thing seems to be just fine to this moment except that at the subscriber fields with default values have lost those values probably during the snapshot intialization process. I want to k
now if it is possible to prevent this type of behaviour. Thanks for your help.
Defaults on tables on the publisher will be replicated to the Subscriber.
If you have different defaults on the Subscriber that you wish to be
preserved you have to, right click on your publication, select publication
properties, click on the articles, tab, click on the browse button (the
three ellipses to the left of your table), and select the snapshot tab. In
the name conflicts select the Keep existing table intact option.
Regenerate your snapshot and restart your merge agent to correct your
problem.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Oussama Albairat" <Oussama Albairat@.discussions.microsoft.com> wrote in
message news:174B6D16-9FC0-4D4A-80EE-3C5C85599359@.microsoft.com...
> Hi All,
> I'm trying to implement a merge replication of our database, every thing
seems to be just fine to this moment except that at the subscriber fields
with default values have lost those values probably during the snapshot
intialization process. I want to know if it is possible to prevent this type
of behaviour. Thanks for your help.
|||What do you mean by "lost those values"? The data no longer exists in the row(s)? Or the Default constraint is no longer applied?
If the latter, ensure that you include constraints in your article publication. If the former, you'll need to find WHY the data is not being propogated (perhaps a 'NOT FOR REPLICATION' explicit declaration in the table definition? etc.)
X
"Oussama Albairat" wrote:

> Hi All,
> I'm trying to implement a merge replication of our database, every thing seems to be just fine to this moment except that at the subscriber fields with default values have lost those values probably during the snapshot intialization process. I want to
know if it is possible to prevent this type of behaviour. Thanks for your help.
|||Thanks Guys for answering so promptly,
Like Hilary said Defaults are replicated to subscriber, that's what I noticed in my case too.
But, user defined defaults are ignored by the replication process.
So these defaults end up being removed from the final schema,
after running snapshot initialization for the subscriber. And I still don't know why.
Do I have to reset all fields defaults manually at the subscriber? (which is not the best deployment strategy)
if you have any better idea to recover the defaults as well, please share it.
Thanks again.
"Hilary Cotter" wrote:

> Defaults on tables on the publisher will be replicated to the Subscriber.
> If you have different defaults on the Subscriber that you wish to be
> preserved you have to, right click on your publication, select publication
> properties, click on the articles, tab, click on the browse button (the
> three ellipses to the left of your table), and select the snapshot tab. In
> the name conflicts select the Keep existing table intact option.
> Regenerate your snapshot and restart your merge agent to correct your
> problem.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Oussama Albairat" <Oussama Albairat@.discussions.microsoft.com> wrote in
> message news:174B6D16-9FC0-4D4A-80EE-3C5C85599359@.microsoft.com...
> seems to be just fine to this moment except that at the subscriber fields
> with default values have lost those values probably during the snapshot
> intialization process. I want to know if it is possible to prevent this type
> of behaviour. Thanks for your help.
>
>
|||In merge replication AFAIK it isn't possible to not replicate the defaults
constraints so perhaps you are referring to default objects? If so, you
could redefine them as default constraints, or you could use a script to be
run after the snapshot ('postscript').
TIA,
Paul Ibison
|||Hi All,
What I noticed in merge replication is when using a user defined data type system converts it back to native SQL data type during replication. so fields at subscriber are no longer of the same type as in publisher. eg. if you define a data type Boolean
as bit and your field named "IsOk" is Boolean at Publisher. After replication IsOk becomes bit at subscriber. now if Boolean is bound to a default value "False" at publisher, after replication field IsOk will not be attached to default constraint at subs
criber because it's data type has changed to bit. And that's what made my default constraints to disappear.
Thanks.
|||Oussama,
on the article properties have a look at the snapshot tab. By default it is
set to convert user-defined datatypes to base types (check-box at the
bottom) which is why you lose your bound default. In your case if this is
unchecked there will be an initialization error produced from the merge
agent unless the user-defined datatype preexists on the subscriber. You
could create it and bind the default to it in a script to be run before the
snapshot is applied (snapshot tab of the publication properties), then it
should all work ok.
HTH,
Paul Ibison
|||Paul,
Thanks for your advise. I unchecked that check-box, and after
initialization defaults are recovered as well. Thanks so much.
"Paul Ibison" wrote:

> Oussama,
> on the article properties have a look at the snapshot tab. By default it is
> set to convert user-defined datatypes to base types (check-box at the
> bottom) which is why you lose your bound default. In your case if this is
> unchecked there will be an initialization error produced from the merge
> agent unless the user-defined datatype preexists on the subscriber. You
> could create it and bind the default to it in a script to be run before the
> snapshot is applied (snapshot tab of the publication properties), then it
> should all work ok.
> HTH,
> Paul Ibison
>
>

Sunday, February 19, 2012

Default value for a feild

I would like have the default value for a feild to be the result of a call to

System.Web.Security.

Membership.GeneratePassword(12,4)

How do I implement this?

In a trigger? Do you have sample code?

Thank you very much

Hi,

I think I know what you are asking, and my guess is thatyou may be trying to combine two parts of a solution into one. The default value for a column is a value set for every row in a table, until it is changed by entering another value. So that default value is stored in the database for each row, just as every other column value in the BD. The only difference is that a deafult value is inserted into place in that column row intersection when ever a new row is being entered. The rest of the column values for that row are entered at the time of the creation of that new row. when all the column values have been entered and there are no valiadtion problems, that new row is added to the DB as a transaction (so the complete row is entered - or not entered, as unit).

What your question seems to suggest, is that some value be dynamically selected or calculated, and that value entered into a row of data as a new row is being filled out for entry. That wouldn't be a default value in a DB, but could be a default value in the application that you use to access the DB to enter new data rows in the DB. Do you see the distinction? Onethe DB default value is there whenever and however a new row is added to the DB. The application default value is placed in position to be entered as a column value in a new row for the DB,by the application;as this is an ASP.Net forum, probably by an ASP.Net web app.

If what you want is that a column value in the database (that has just been entered via some application that is used to enter new rows into the DB), be changed (in the DB), whenever a new row of data is entered - and have that value drawn from a call or other source dynamically; then a trigger would be one way to do it. A trigger always works as the result of some specific action (such as the entry of a new row of data). Another way to do it would be to do the call as part of the application - rather than by the DBMS. You can write the app any way you want. You can have it show the value to the user doing the entry, or hide it, or even change whatever they enter to the dynamic value after they enter data, but before the DB is updated with the new row.

The most important thing is that you have clear in your mind what data you want stored in the DB, and which parts of the data entry process you want done in an application outside the DB, and what you want the database management system to take care of. Because data integrity is so important to most databases, there may be DB schema restrictions for data entry/removal/updates. Be very careful about selecting methodes that would circumvent those safegards. BRN..

|||

Please help with the following scenario:

A gridview has its datasource bound to an object datasource made of a list of blank data entities such as date, name, amount, etc... The user goes in the grid and enter data in each cell which is a texbox. Now, right after the first row is filled, how can data of that row be put in the next row and display as default data as the cursor moves into it? (All data entered will be submitted by a click of a button at the bottom of the page.) Thanks.

Tuesday, February 14, 2012

default schema for NT-group not possible

We have a new development SQL2005 server.
To implement secutiry on this new server we decided to grant rights to NT-groups.
We defined an NT-group Programmersmembers and gave this group the necessary rights.
The problem now is when a member of this NT-group creates a table (this group is dbo in the development database) everything works fine as expected.
But when a member of this group tries to make a new table through 'Microsoft SQL server management studio' they receive the error that there is no default schema available.
But it seems to be impossible to map a default schema to an NT-Group.

=> Does this means we have no other way than to give each individual programmer the necessary rights and assign them to a default schema.
It is no option to force our programmers to create each table through a script on the development database.

Or is there another option ?

For the general issue of setting a schema for a group, see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1.

If you have a limited number of operations that you want your users to perform, you could expose them through stored procedures that use the EXECUTE AS to run under a context with a well defined default schema. See http://msdn2.microsoft.com/en-us/library/ms188354.aspx for more information on EXECUTE AS.

Thanks
Laurentiu

|||

Setting a default schema for a group is not supported in SQL Server. The root cause of the problem in this case seems to be a bug in Microsoft SQL Server management studio that is already in our radar.

For more details, you can see the original discussion for this bug at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1 .

I hope this information can help you.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Just to clarify the SQL Server Management Studio bug. If you create the table directly using TSQL, the operation should succeed and create an implicit user. Management Studio's bug is that it requires the user to be already created.

-Raul Garcia

SDE/T

SQL Server Engine

-

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

|||

Raul & Laurentiu

thx for the explination and the link to the other thread