MS SQL Server :: Is It Possible To Prevent Databases From Being Copied?

Different Results Same Query Between Original And Copied Db
Hi all,

I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong.

Has anybody seen this behavior?

Thanks for your help

Luis Torres

Replication SPs Copied W/CRLF In Names
Hi folks -

New DBA (longtime developer) here. I've just noticed something strange on a couple of my databases (SQL 2k). There are some stored procs that have a CRLF or CR+CRLF prefixing the name (they show up as unprintable-char black squares in EM).

They all appear to be replication-related SPs (e.g. ). The names are as follows:
sp_MSdel_<replicated_table_name>
sp_MSins_<replicated_table_name>
sp_MSupd_<replicated_table_name>

There are corresponding normally-named SPs. Not all the replication-related SPs have been copied this way. The create date of all the new SPs is the same, and is (IIRC, I wasn't involved then...) the date of the SP4 application to the server.

Anyone ever seen this? More importantly, is there a way to determine if these SPs are being used? I'd like to delete them if I can, but not until I'm sure of what's happened. TIA for any input.

Char Varchar Not Copied While Transferring

Hi,

I have the following problem.

I am using DTS connecting SQL SERVER 2005 and Sybase9. I successfully copy from  Sybase9 to SQL SERVER 2005.

Now when i am copying from  SQL SERVER 2005 to  Sybase9 char and varchar types are not copied. The data types are the same as well as data type length. Integers, numeric and date values are copied. I have tried data conversion but that did not help.

sql server 2005 collation is Greek_CI_AI and Sybase9 collection is 1253ELL

Any solution how to solve loosing char and varchar types when copying from   SQL SERVER 2005 to  Sybase9 ?

Note: I connected to Sybase using System DSN

Regards,
Vasilis

Copied Database Maintains Original Name In Backups
I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?Here is the BACKUP script:BACKUPdatabase vgs_prod TODISK='\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK' with COPY_ONLYHere is the messages I received from this BACKUP:Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec). I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above. Thanks,

One Table Is Ultra Slow At Being Copied, Others Are Fine
This is a really really odd problem.

Here's the situation. I've got a DB with several tables. Let's pick out two of those tables from the bunch.. they both have approx 2500 rows in them. I take one and duplicate it (right click, All Tasks->Export Data, copy tables, etc) and it duplicates just fine into another DB.

Now, the second table.. takes FOREVER. Yet it has the same amount of rows in it! And this is a flat table export.. I'm not including dependent objects or anything like that.

Not to mention this is slow, but when I use Red-Gate SQL Data Compare, it gets stuck when it hits this specific table.

Anyone have any idea what would cause this?

SSIS Error With Duplicate IDs Of Copied Packages
Hi,

I recently encountered an error when I created several copies of one package.

It's always nearly the same package with small modifications. I call this packages from a parent package which is part of our datawarehouseing-framework.

The problem is, when copying a packages or using a packages as template the packages' IDs and Task's-IDs are the same. And this isn't only an issue concerning logging!! :

When the parent package calls one of the copied packages the first task is executed in every package parallely. Furthermore ... when I for example set a breakpoint on a data transformation task in one of the packages, the breakpoint is set in all packages on the same task! This is resulting in strange errors because the tasks-states and variable values seem to get mixed up.

Unfortunately there is only a possibility to change the package's ID, but the IDs of tasks are readonly!

One solution is, to create a new package and copy all the tasks to the new package which creates new IDs, but doing so, I have to manually recreate a long list of variables, all the configurations, all the connection-managers once again. Furthermore I loose the layout of tasks.

I found some posts about it here

http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/6f85a31ea190608a/0eae312aa8440cf8?lnk=gst&q=pitfall&rnum=1&hl=de#0eae312aa8440cf8 or

http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/760093d58bf6ccb5/32ced2f2020ef3f7?lnk=st&q=data+flow+task+id+copy&rnum=2&hl=de#32ced2f2020ef3f7

saying the issue will be fixed by SP2, but now I don't see any comment on it in the CTP of Service Pack 2.

Is there any solution to this problem or official roadmap about a fix from Microsoft??

Greetings Monte

Backup Files In Append Mode To Be Copied.

Hi,

I have a database on sqlserver 2005.I usually take a full db backup and sequential transaction log backups and append each of the backups it to a backup file.Now if i want to copy my second transaction log file to a specific folder on my server.Do we have any procedure to do it.

Regards
Arvind L

Merge Replication Issue With Records Not Being Copied To Subscriber
I have an issue with merge replication between sql2005 sp2 and spl2000 sp4

the merge replication runs fine to 6 other sql servers

but the sql2000 sp4 server has a lot of locking going on, but the merge replication doesnt fail it just loses several hundred records without notifying me

I have to manually copy the missing records every week or so

I have dropped the replication and rebuilt it twice but it still happens

Report Security - Parent Folder Not Copied To Child
Hi Guys,

In the reproting services web page I have inserted different users who can run reports. I have home page and sub folders for each company department.

only problem is when I add a new user I have to add him/her to all the sub folders one by one (although the user is inserted in the parent folder as well)

What am I doing wrong?

Cheers

Sonny

Identity Field Settings Not Copied By Import Wizard (2005)
It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column

[ref] [int] IDENTITY ( 1 , 1 ) NOT NULL,

When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be

[ref] [int] NOT NULL,

instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column.

-Eric

SQLEXPRESS Backup File Losing &&"NETWORK SERVICE&&" User When Copied
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:

srv = New Server("MYPCSQLEXPRESS")

db = srv.Databases("washmaster")

Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)

Dim recoverymod As RecoveryModel

recoverymod = db.DatabaseOptions.RecoveryModel

rs.NoRecovery = False

rs.Devices.Add(bdi)

rs.Database = "washmaster"

rs.ReplaceDatabase = True

srv.KillAllProcesses("washmaster")

rs.SqlRestore(srv)

This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:

....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)

The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.

How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?

Thanks,
SJonesy

SQL Injection - How To Prevent It?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add

db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);

Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie

My C# update method in the DAL (still working on the code)

private static bool Update(AvatarImageInfo avatarImage)
        {
            //Invoke a SQL command and return true if the update was successful.
            db.ExecuteNonQuery("syl_AvatarImageUpdate",
                avatarImage.AvatarImageID,
                avatarImage.DateAdded,
                avatarImage.ImageName,
                avatarImage.ImagePath,
                avatarImage.IsApproved);

            return true;
        }

I am using stored procedures to access the data in the database.

My update stored proc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate]
    @AvatarImageID          int,
      @DateAdded              datetime,
      @ImageName              nvarchar(64),
      @ImagePath              nvarchar(64),
      @IsApproved             bit
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN TRY
            UPDATE      [syl_AvatarImages]
            SET
                      [DateAdded]       = @DateAdded,
                        [ImageName]       = @ImageName,
                        [ImagePath]       = @ImagePath,
                        [IsApproved]      = @IsApproved
            WHERE [AvatarImageID] = @AvatarImageID
            RETURN
      END TRY
      BEGIN CATCH
            --Execute LogError SP
            EXECUTE [dbo].[syl_LogError];
            --Being in a Catch Block indicates failure.
            --Force RETURN to -1 for consistency (other return values are generated, such as -6).
            RETURN -1
      END CATCH
END

Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas

Prevent Sql Job Failure.
Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.

How To Prevent Db Deletion
Hi

I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.

Any help/direction here would be appreciated.

Best Way To Prevent Deadlocks
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks.

Exception type: SqlException
Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In general, what's the best way to resolve this ?

Should I see if I can apply "WITH (NOLOCK)" to my data ?

Any suggestions are greatly appreciated !

thanks again!
mike123

Prevent Duplicate Insert
How can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.

Prevent Duplication On UPDATE
Hello
I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling:
UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside'
The above code correctly updated the spelling error, but it also inserted a new row with the corrected data.
So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows.
What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them.
Thanks
Lynn

How Do I: Prevent Race Condition?
I want to be able to read and update a value in the database without entering a race condition.
For example:
User #1 reads a row from the database, changes a value then writes the value back.
User #2 reads the same row AFTER user #1 has read it, but BEFORE user #1 writes it back. User #2 then changes the value and writes it back, overwriting the value that user #1 wrote.
I thought I could do this with transactions, but it just makes user #2 wait until user #1 is done writing before user #2 can write. It doesn't stop user #2 from reading while user #1 has it out.
Does that make sense?

Prevent Duplicate Records
I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.

How To Prevent SQL Injection Attacks
Hi,

On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches

The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg%

There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk

I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable.

Can anyone help, perhaps point me in the direction of furthur resources on the subject?

Thanks

Ben

What Are Sql Injection Attacks And How To Prevent?
this is a question I put in the sql community in microsoft, but havent be answered in full

------------

I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp

ex:

create proc ex
@orden varchar(100)
@criterio varchar(100)

as
declare consulta varchar(4000)

set consulta=N'select pais from paises where '+@criterio' order by '+@orden

------------

I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp

now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable

what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)?

-----

They told me this coding is not good (dynamic sql) because it can give control to the user?

I ask, how does it give control to use? what ar sql injection attack and how to prevent them?

I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good

is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right?

can u help me?

Prevent DELETE And/or UPDATE
Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO

Prevent One Single Row From Being Updated
I am doing customization for microsoft POS. I manually added a recordto a table. The manage and maintenance of this table are done by POS,and user can update the contents of this table. Is there any way I canlock this single row at database level to prevent it from being deletedor changed by user? I am using SQL 2000 and vb.net.Thanks.Leanne

Prevent Trigger From Firing
Hi,

I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.

Any Ideas..

How To Prevent Displaying Names Of DB?
I get an issue, that is: Once i expand databases from SQL Enterprise Manager, it takes a long time to wait displaying all names of databases. How should i do to prevent displaying all names of databases?

Trigger To Prevent Duplicates
Hi all,

I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key.

If I create a table with 2 columns

CREATE TABLE Table1
(CentreCHAR(10),
Month CHAR(3)
)

Then create a trigger to prevent duplicates

CREATE TRIGGER trigger_Check_Duplicates
ON Table1
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table.
DECLARE @IsDuplicate INTEGER
-- Check if row exists
SELECT @IsDuplicate = 1
FROM Inserted i, Table1 t
WHERE t.Centre = i.Centre
AND t.Month = i.Month
IF (@IsDuplicate = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END

Then insert a row into the new table (no other data is in there)

INSERT Table1
VALUES('0691040176','AUG')

I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image.

Please help!!!

Thanks

Neill

Prevent ODBC By USER/APP
Is there a way to monitor all ODBC connections to a MSSQL server and prevent a username/ODBC combination. My problem is that we have many frontends for viewing reports, but we managed them all and users are not allow to make their own connections. Some users now uses MSAccess over ODBC to draw their own reports - they have all the permissions as neededd by the other apps.

thanx

How To Prevent Doublicate Name Entry
hi I have a table I need to have a process which prevent a user from entering a name value( company Name ) in a field. how can I do that .

Ahmed

Prevent SQL/CLR Auto Deploy
Hi,

I have a solution (VS 2005, C#) that includes a SQL 2005 CLR project.

When I run the solution (in debug mode, and with a test harness in a different project in the solution set as the startup project) the IDE often wants to deploy my CRL assembly.

How do I tell it NOT to try to do the deployment?

(i.e. to only do it when I right-click the project and tell it to).

Cheers,

Is That Possible To Prevent Modifying The Name Of An IDTSInputColumn90?
Hi,

I'm building a custom SSIS data flow component and I create myself input and output columns from a custom property of my pipeline component. That's why I don't want the user to modify the name of the input and output columns by using the advanced editor.

Is there a way either to make input/output column properties - at least the name - readonly or to override any PipelineComponent method to throw an exception like we do when we want to prevent the user from adding/removing input/output colulmns with methods like PipelineComponent.DeleteOutputColumn?

Thanks for your help,

David

Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime.

I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted.

If the procedure was to be run a second time in hour window I do not want any Status to be inserted.

Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00.

Does anyone know if there is anyway I can gaurd against this?

Prevent MS Access To Connect
Hello

I'm facing a dilemma. I have quite a lot of users who need read access to data for analysis purposes. By granting them read access you also give them the option to connect to the database using MS Access which puts locks on the data.

First I thought of generating stored procedures for all tables within a database those users need to query but when users execute a stored procedure with the name of the table, they get a resultset of all the data. They cannot filter the data by for example using select top 100* from usp_table where name like '%worf%'

I'm sure I'm not the only one here but I don't see any alternative then grant them those rights.

Thanks!
Best Regards,
Worf

How To Prevent A Distributed Transaction?
I have a stored proc that performs some validations and returns a result set. I catch the result set in a calling procedure as follows:

declare @tblErr (ErrType varchar(20), ErrMsg varchar(255))
insert @tblErr (ErrType, ErrMsg)
exec dbo.spMyProc ...(args)

Within the def of spMyProc there is a cross-server call:

create procedure dbo.spMyProc (...params...)

as
begin

declare @tblErr (ErrType varchar(20), ErrMsg varchar(255))

...(do checking, inserts to @tblErr)...

-- check other server
exec @n = otherserver.mydb.dbo.spCheck ...(args)
if @n != 0
insert @tblErr values ('someerr', 'somemsg')
...etc.
select ErrType, ErrMsg from @tblErr
end

Apparently, the implicit transaction of the outer Insert statement is causing a distributed transaction to be started. There is no 'begin tran', distributed or non, and the foreign sp has no data-modifying statements. The call to spMyProc, if done from a query window, works fine, returning the desired resultset. When called from the outer proc, the call fails because the servers are not configured for distributed transactions. When performed without the cross-server call, using a copy of the remote db attached to the local server, the outer proc works fine.

I do not want a distributed transaction. How can I prevent one? The local server is 2005 Standard on Server 2003, and the linked server is SQL2000 on Windows 2000 Server.

Prevent Email Authentication
Due to the 'interesting' set up of AD, email etc we are unable to send reports as attachments.

It would appear that it is trying to authenticate the user to see if they have permission to view the report, as though they were viewing it in real time.

Is there any way to override this behaviour?

If not then all we can do is email them a link to the report, which sort of defeats the object.

How To Prevent Ordering Of Records....
hello,

i use VC# and sql server 2005.

i create a table using the following command :

create table history(name varchar(20) primary key);

I insert records in a particular order.After all the records are inserted and when I try to retrieve the records, all of them are sorted in a ascending order.Is there any way to prevent them from being sorted.

pls reply asap.....

- Sweety

How Can I Prevent That All Data Can Be Seen With Notepad?
Hello,

is there a way to say to SQL Server to make the data not readable?

Regards
Markus

How To Prevent My Co-Workers From Accessing My SQL
Hi all

In our office we have simple Network (LAN) and for some reason in each workgroup Computer in this network we have installed SQL-Server 2000.

So far everything was ok but recently i noticed that my co-workers can easily access my Sql Server Instance through their Enterprise manager installed on their computers and then they can open my tables,view...and manipulate other objects.

for better illustration i have created a Flash Movie from this process , please check the following Link :

http://www.Parsisoftco.com/movie/Sql/Sql.htm

How can i prevent others from doing so?
Any help greatly Appreciated.
Kind Regards.

Prevent From Copying Database ?
Hi,

How can prevent from stoping sql-server sevice and copying database,
note user has a access right to pc/server.

Kind Regards,
sasan.

How Can I Prevent My Co-Workers From Accessing My SQL Instance..
Hi all
In our office we have simple Network (LAN) and for some reason in each workgroup Computer in this network we have installed SQL-Server 2000.
So far everything was ok but recently i noticed that my co-workers can easily access my Sql Server Instance through their Enterprise manager installed on their computers and then they can open my tables,view...and manipulate other objects.
for better illustration i have created a Flash Movie from this process , please check the following Link :
http://www.Parsisoftco.com/movie/Sql/Sql.htm
How can i prevent others from doing so?
Any help greatly Appreciated.
Kind Regards.

How To Prevent A Second Entry Being Inserted With Primary Key Value? (C#)
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database?
My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e)
2 {
3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text;
4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue;
5 try
6 {
7 srcAddSkill.Insert();
8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score.";
9 lblErrorMessage.Visible = true;
10 txtSkillName.Text = "";
11 }
12 catch (Exception ex)
13 {
14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message;
15 lblErrorMessage.Visible = true;
16 }

How Can I Prevent From Inserting Duplicate Data?

I have a table storing only 2 FKs, let's say PID, MID
Is there any way that I can check distinct data before row is added to this table?
For example, current data is
PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449
If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue?

Thanks.

Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.

The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.

Here is a sample of some data:

User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3

Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:

User1 Group1
User2 Group2
User1 Group1

I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.

Is there a simpler way to prevent duplicate entries in a table using sql?

Thanks a lot,

Chris

What Type Of Constraint To Prevent Duplicates
I am trying to either write a trigger or a check constraint to preventduplicates in my table. There are two columns I need to look at for theduplicates and only one combo value for both columns is allowed in thetable. For e.g.Column Serial can have only one '123456' value with testresult value as'PASS'. This serial can be in the table many times with any other comboso for e.g.The table could contain 100 entries for serial column value '123456'with testresult value 'FAIL', 'PENDING' etc.** TESTED SCHEMA BELOW **-- create tableCREATE TABLE bstresult(ID int IDENTITY (1, 1) NOT NULL ,serial char (10) NULL ,testresult char (10) NULL)-- Insert valid valuesinsert into bstresult values ('123456','PASS')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','PENDING')-- insert invalid value this should failinsert into bstresult values ('123456','PASS')If I simply create a unique constraint on both columns it will notallow the FAIL combo or PENDING combo with the same serial which I needto allow.Appreciate your help.