How To Prevent System Form Locking A Table In Case Of Large Insertion

How To Prevent Table-locking With Multiple Queue Readers
 

In a situation where messages are coming in faster than they can be processed, at what point will service broker start up another queue_reader? Also, how do you prevent table locking if part of the processing of that message involves inserting or updating data in a table? We are experiencing this problem because of the high number of messages coming through, and I'm not sure what the best solution is - does service broker have some built-in support for preventing contention on a table when multiple readers are running? Or maybe a pattern that can be used to get around it?

View Replies !

---

How To Prevent The Insertion Of The Same Entry Of An Unique Value Into The Database
how to prevent the insertion of the same entry of an unique value into the database?
assume that I have a primary key username " abc"  in the database
and then I insert the "abc" again ,the debuggging error msg pops out saying the primary key cannot be duplicated..
how can I do an if--else statement to check the database against this unintentional inputs of the same unique data " abc"???
 

View Replies !

---

SQL Server How To Prevent Locking To Do Read-Only Reports
Need some information regarding SQL Server locking.Requirement is to run SQL Statements for reports (read-only) that willnot lock-out other people trying to update (write lock). For this, wetried the following to make the db connection not deny write locks(our report code will not have a read-only lock).ADODB.ConnectionObj.Mode =adModeShareDenyNoneBut this made our connection bumped whenever someone tried to update.Tried enabling dirty reads by doing:ADODB.ConnectionObj.IsolationLevel =adXactBrowseEven this made our connection bumped whenever someone tried to update.Is there a way to achieve what is needed without having a seperatedatabase for reports?

View Replies !

---

System Drive Problem Caused By Query Against Large Table
Hi, all experts here,

 

Thank you very much for your kind attention.

 

It's so frustrated that I dont really knwo what is going on and why is that and I have tried ages to try to figure it out and nothing really helps.

 

I have already moved the data files of tempdb database to a drive with enough space (many GB space left still), but then again I got the problem which run out all of the system drive space when I run a query against a large table? Why is that? And how to figure it out?

 

Please help me and thanks a lot in advance for your kind advices and help and I am looking forward to hearing from you shortly.

 

With best regards,

 

Yours sincerely,

 

 

View Replies !

---

A More Complicated Case Of Insertion Filtering By More Than Two Fields
Hi,

I posted a problem some hours ago. I found that the solution that l was given by Karolyn was great, but at that time I didn't realize that my problem was a little bit more complicated. I'll rephrase my problem:

I need to insert some registers in a table. These registers have three fileds: col1, col2 and col3. I don't want to insert a register if in the table already exists a row with the col1, col2 and col3 combination of that register. These fields are PK, but I don't want to get errors. The problem is that I'm inserting a field that belongs also to the destination table. How can I filter a "destination" table by two fields in this case?

This the table1:

create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col1, col2, col3)
)


Here's my "insert" code:

INSERT INTO table1
SELECT table2.col1, table3.col2, table1.col3
FROM table2, table3
WHERE table2.col1 = table3.col1


The third field in the SELECT now refers also to table1. Witch conditions should I add to avoid repetitions in table1 (avoiding also erroing)

Thanks

Federico

View Replies !

---

Record Locking/form Recordsource Options
Newbie question: what should the recordsource of a form (in Access2000) be set to after upsizing to MSDE/SQL Server? Should I be using views of all records (equivalent to using the whole table)? Should I be using stored procedures which retrieve one record at a time, so as to minimize traffic and record conflict?

View Replies !

---

Prevent Backup And Restore On Different System
 

How do I prevent someone from backing up the data / copying the mdf and then mounting it from a different server or instance to gain access to the DB.
 
I do not have control over the physical file.
 
Basically, I want to make sure that only authorized applications and users can access the data.

View Replies !

---

How To Prevent System Administrator To View And Edit A Database Structure And Data
I represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine.

My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine.

Our database structure is a trade secret and we cant reveal the structure to the client.

please answer this question by email to me at farhandotcom@gmail.com

Thanks & Regards
Farhan

View Replies !

---

How To Solve Or Minimize Locking In This Case?
If batch processing and normal transactions need to run in parallel, how can I solve or minimize locking issue? Since batch processing will use PAG or TAB most of the time, how can other users to access data at that time?


Thank you for any suggestion.

View Replies !

---

Tempdb - Locking System Tables
I have some stored procs that have temp table creations in them using "create table #tmptable...". I have noticed that when running the procs, sp_lock shows tempdb system tables being locked. I totally expect this. But, put a PowerBuilder front end on, and I get another situation. Calling the stored procs from PowerBuilder causes the same locks to occur, but they are not released after the data is displayed through the application. Furthermore, this blocks other users from creating anything in tempdb until the user logs off of the application and ends the connection to the database.

please help - is there something I am missing in my procedures, or is this a PowerBuilder issue?

Thanks!

Dean

View Replies !

---

Select Into And Locking System Tables -- Am I In Trouble?
I am doing a ‘Select into’ to make a table from at another table which has as many as 130 millions rows (its well indexed). The new table will most often have about 1000 rows. (During the running of the app, the app will be making many new tables, since hopefully this will be a ‘popular’ item by the users. After the users ‘use’ them, they are dropped.)

I know that locks are held on various system tables (including sysobjects) during this ‘select into’ process. Are they held for the entire process?

What should I be concerned about doing ‘select into’?

Thanks for any help,

Judith

View Replies !

---

How To Create A System Type Table/ Change User Table To System Table.
Is there any Posibility to change a User Table to System Table.

How to create one system table.

I am in Big mess that One of the Table I am using is in System Type.

I cant Index the same. Is there any Mistake we can change a user table to system table.....

View Replies !

---

Issue With File System Task And Folder Locking
I'm having an issue where a folder that I'm accessing in an SSIS package gets locked up and cannot be deleted by my package later in the process.

1) I use a File System task to rename the folder. At this point, it appears to become locked and cannot be deleted.

2) Later I attempt to delete the folder using another File System task object. I get the error '[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process."'.

My package accesses the folder via a drive mapping. This seems to be part of the problem, as it's possible to delete the folder directly but not via the drive mapping. Even once the package completes it's still not possible to delete the folder in Windows Explorer via the drive mapping.

View Replies !

---

System.Windows.Form Assembly Is Needed On Script Component Task?
That affects only interface design... so that it doesn't exists at all in a SSIS.

What is it for??

Let me know your view on this or any clarification.

View Replies !

---

Insertion Od Data In The Table
Hi Friends,I have 3 labels Steet,City,Pincode and 3 textboxes related to the labels and one button as nae 'Address'I gave the data for Street:abc,City:xyz,Pincode:123 and have to insert into the table.I created one table in the database with table name Adreess and column address varchar(100)but after giving the values in the textboxes and clicked on the button its throwing the exception i.e System.Data.SqlClient.SqlException: The name "abcxyz123" is not permitted in
this context. Valid expressions are constants, constant expressions, and (in
some contexts) variables. Column names are not permitted.I wrote the code like following protected void Button1_Click(object sender, EventArgs e)    {        string street = txtStreetNo.Text;        string city = txtCity.Text;        string pincode = txtPincode.Text;        string com = street + city+pincode;        conn.Open();        SqlDataAdapter daInsert = new SqlDataAdapter("insert into Address values(" + com.ToString() + ")", conn);        daInsert.SelectCommand.ExecuteNonQuery();--->here its giving the exception        conn.Close();        Response.Write("the values are inserted");            }Please any one tell me am I did the code write or not if its not please give any suggetionsthanksGeeta  

View Replies !

---

Insertion Into Partitioned Table
hello
i want to ask if the insertion of a record into a partion is slower than insertion it into a non partitioned table or not?
cuz sql has to decide to wich partion the record has to insert according to the partitioning key and is this decesion process is making insertion slower ?

View Replies !

---

System.OverflowException: Value Was Either Too Large Or Too Small For An Int32.
System.OverflowException: Value was either too large or too small for an
Int32. Why does this error originate in the following line?"SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM Contacts", conn)........ ..........DataSetContacts.ContactsRow row = ds.Contacts.NewContactsRow();..................row["ContactNumber"] = Convert.ToInt32(txtContactNo.Text);" ContactNumber field is SqlDbType.Int. 

View Replies !

---

Insertion And Updates On 20.000.000 Tuples Table.
Hi,I have a table with 20.000.000 of tuples.I have been monitoring the performance of the insertion and updates,but not convince me at all.The table have 30 columns, what and 12 of it, are calcultated column.The test that i do was this:1 Insertion with all the columns and calculing the calcultated columnsin the insertion sentence.1 insertion and all the columns calculated in @vars..1 insertion with the basic fields, and 10 updates.And the result was that the last test was the most performant.What is your opinion?

View Replies !

---

Trigger To Create A Table On A Row Insertion
Hi there

I have a relatively noobish query and I am hoping to get a solution to it.

Heres the query in a nutshell.

I have a 'Type' table which has a 'TypeName' varchar attribute. So when I do a row insert into this Type table, I want a new table created with the value I insert into the 'TypeName' column as the table name.

For example If i insert 'xyz' into the 'Type' table for the 'TypeName' column. I wish for a trigger to fire which will create a table 'xyz' with some set attributes. I am really new to SQL Server and my preliminary googling left me disheartened with the results. So here I am.

I hope I was clear in the way I expressed my doubt and also that the people here might be able to help me out in this quest.

View Replies !

---

Master/Detail Table Insertion.
Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

View Replies !

---

Multiple Table Insertion Using SQLBulkCopy
 I want to insert data into Header and Details table simultaneously using SQLBulkCopy.  Header table contains an Identity columns and Details table contains Foreign Key to this identity column in Header Table.I want to use DataTable as datasource in SQLBulkCopy.Can any body help on this

View Replies !

---

Multiple Table Insertion Using SQLBulkCopy
 

I want to insert data into Header and Details table simultaneously using SQLBulkCopy.  Header table contains an Identity columns and Details table contains Foreign Key to this identity column in Header Table.I want to use DataTable as datasource in SQLBulkCopy.Can any body help on this
 
thanks
 
 
 

View Replies !

---

Temp Table Insertion Problem
This is my code through which I insert the data into herm_Gen_Liab_Pre table.set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER proc [dbo].[sample]

--@policyno varchar(9),-- FOR MOLD COVERAGE

--@formno varchar(6) -- FOR MOLD COVERAGE

as

TRUNCATE TABLE herm_Gen_Liab_Pre

SET ANSI_WARNINGS OFF

SET ARITHABORT OFF

DECLARE @genliabpre TABLE

( accmnth varchar(2),

accyear varchar (4),

treffmnth varchar (2),

treffyr varchar (4),

trexmnth varchar (2),

trexyr varchar (4),

statecode varchar (2),

typeofpolicy varchar(2),

exposure varchar(7),

annualstatementlineofbusinesscode varchar(3),

PremiumRecordId varchar (14),

Sublinecode varchar (3),

classcode varchar (5),

incemnth varchar (2),

inceyr varchar (4),

territorycode varchar(3)

--policyno varchar(9),-- for mold coverage

--formno varchar(6), -- for mold coverage

--moldcoverage varchar(1) -- mold coverage value

);

insert into @genliabpre (accmnth,accyear,treffmnth,treffyr,trexmnth,trexyr,

statecode,typeofpolicy,exposure,annualstatementlineofbusinesscode,PremiumRecordId,

Sublinecode,classcode,incemnth,inceyr,territorycode)

select datepart(m,p.EntryDate),

datepart(yy,p.EntryDate),

datepart(m,p.PremiumEffectiveDate),

datepart(yy,p.PremiumEffectiveDate),

datepart(m,p.PolicyExpirationDate),

datepart(yy,p.PolicyExpirationDate),

p.statecode,p.policytypecode,p.ExposureLimitAmt,s.lineofbusinesscode,

s.invoiceno,s.sublinecode,s.ClassCode,

datepart(m,p.PolicyOriginalEffectiveDate),

datepart(yy,p.PolicyOriginalEffectiveDate),s.TerritoryCode

from hermitage.dbo.premiumdirect as p join hermitage.dbo.premiumstatdirect as s

on p.invoiceno = s.invoiceno

where

s.lineofbusinesscode in ('052','152','170','171','175','180')--,'192','194') -- general liability

AND P.STATECODE IN ('NY','NJ','PA','RI','GA')

and p.entrydate between '01/01/2004' and '12/31/2007'

order by p.entrydate

----------------------------INSERT MOLD COVERAGE--------------------------------

/*

SET @policyno = (select cast(pd.policyno as varchar(9))

from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf

on pd.policyno = pf.policyno

where pd.entrydate between '01/01/2004' and '12/31/2007'

and pf.formno in ('CG2167','CG3131')

and exists

( select policyno from HERMITAGE.DBO.premiumdirect p join

HERMITAGE.DBO.premiumstatdirect ps

on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)

where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')

and ps.sublinecode in ('334','335','336','337','338','339'))

)

 

SET @FORMNO = (select PF.FORMNO

from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf

on pd.policyno = pf.policyno

where pd.entrydate between '01/01/2004' and '12/31/2007'

and pf.formno in ('CG2167','CG3131')

and exists

( select policyno from HERMITAGE.DBO.premiumdirect p join

HERMITAGE.DBO.premiumstatdirect ps

on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)

where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')

and ps.sublinecode in ('334','335','336','337','338','339'))

)

*/

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

insert into herm_Gen_Liab_Pre (

AccountingDate,

TransactionEffectiveDate,

TransactionExpirationDate,

StateCode,

typeofpolicy,

exposure,

AnnualStatementLineOfBusinessCode,

PremiumRecordId,

SublineCode,

ClassificationCode,

InceptionDate,

territorycode

)

select accmnth= case accmnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end + + SUBSTRING(accyear,4,1),

treffmnth=case treffmnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end + + substring(treffyr,3,2),

trexmnth = case trexmnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end+ + substring(trexyr,3,3),statecode,

typeofpolicy,exposure,AnnualStatementLineOfBusinessCode,premiumrecordid,Sublinecode,

classcode,incemnth = case incemnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end+ + substring(inceyr,3,2),TerritoryCode

from @genliabpre

exec genliab_state

update herm_Gen_Liab_Pre

set territorycode = '999' where statecode = '31'

 

update herm_Gen_Liab_Pre

set TransactionTypecode ='1'

----------------------STATE EXCEPTION INDICATOR CODE-----------------------------

update herm_Gen_Liab_Pre

set StateExceptionIndicator = '1' where sublinecode = '325' and classificationcode in ('99935','99936') and statecode = '20'

update herm_Gen_Liab_Pre

set StateExceptionIndicator = '1' where sublinecode = '334'

and classificationcode in ('01412','01418','05113','05114','05118','05123','05124'

,'05125','05213','05223','05224','60010','60011','62003','63010','63011'

,'63012','63013','64500') and statecode = '20'

update herm_Gen_Liab_Pre

set StateExceptionIndicator = '1' where sublinecode in ('345','347') and classificationcode = '80050' and statecode = '20'

select * from herm_Gen_Liab_Pre
 
 The problem is there is a column in this table called  riskidCoPaymentPercentageMoldDamageCoverageCode and I have to insert value '1'  into that column based on this select query
 

select cast(pd.policyno as varchar(9)),pf.formno

from premiumdirect pd join policyforms pf on pd.policyno = pf.policyno

where pd.entrydate between '01/01/2004' and '12/31/2007'

and pf.formno in ('CG2167','CG3131')

and exists

( select policyno from premiumdirect p join premiumstatdirect ps

on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)

where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')

and ps.sublinecode in ('334','335','336','337','338','339'))

order by pd.entrydate
 
How can i insert implement this. I tried temp variable, was successful partially, so need a better approach to it.
 
Chintan
 

View Replies !

---

How To Restrict Data Insertion Upto 50 MB In A Table
how to restrict data insertion upto 50 MB in a table?

View Replies !

---

Global Variable Value Lost During Insertion In A Table
Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

View Replies !

---

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

View Replies !

---

How To Prevent Doublicate Entry To A Table
hi, If I have a table which contains customer names. I want to have some kind of process to validate any new insert record into the customer table. so if the inserted new customer already exist in the table I will get a message that say " Sorry , this customer name is already in the system " .... how can I do that,

I am using sql server 2000. thanks

Ali

View Replies !

---

Referencing To System.Data.SqlServerCe Causes Large Files To Deploy To Emulator
Hello everyone,

This is my first time posting here, I hope this questions has not been asked before. I tried to search for it but I came not with nothing.

Recreating the error :

I am using VS2005. I created a Pocket PC 2003 project.
I have downloaded the SQL Server Compact Edition and installed it. I get the System.Data.SqlServerCe.dll file from the installation directory.
I reference to that DLL using Add Reference in VS2005.

Build it. In the Bin folder, a long list of files suddenly appears.

System.data.dll
System.data.oracleClient.dll
system.web.dll
system.enterpriseservices.dll
system.enterpriseservices.wrapper.dll
system.transactions.dll
and the rest of your original files in Bin

The worst of it all, all of these files are deployed into the Emulator! Causing it to run out of memory and unable to deploy.

 

Something is not right here, I just cannot figure it out! If this happens, each mobile devices can hold one applications. Thats not the way it should be, right?

If you have solved this before, do help. I am at my wits end at the moment.

Thanking you in advance.

Sincerely,
Lasker

View Replies !

---

Conditional Insertion && Updation In Destination Table (SSIS)
HI,
    How to create package in SSIS by applying the business Logic like if the record already exist it should be and update else it should be an insert in the destination table. how to achive this funcality in SQL SERVER 2005 (Business Intelligence studion).

Thanks & Regards

Nagaraj.S

 

  

View Replies !

---

Prevent Other Users From Changing Data Of A Table
hi..

How do i prevent other users from changing the data of my tables? Means one can change data using only my login rest others cannot even DBA or also from server administrator

View Replies !

---

Need Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table
I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other'  populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.

View Replies !

---

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?
Hi, all experts here,

 

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.   

 

With best regards,

 

Yours sincerely,

 

 

View Replies !

---

Table Locking
I want to insert values into a table and the same time select a valuefrom the same table.I insert a record of a parent type and use a function to create asubscriberType for the record.Next i insert a record of child type and want to select thesubscriberType from the parent record.This do not work when i useINSERT TABLESELECT ,Name,"function to generate subscribertype",.............I DO NOT WANT TO USE CURSOR!!

View Replies !

---

Locking Table In A SP
I have a SP for an e-commerce site that creates an order doing the following fashion:

begin
-Fill out some variables
*Critical Section
-Create Order number
-While loop select on order table and recreate if ordernumber already exists
-Insert into order table
*End Critical Section
-Insert into order lines table, address table, etc.
commit

The problem is the Create Order number and While loop used to be at the top and occasionally I would get duplicate order numbers if two submitted at the same time. I rearranged it to the above and it happens less now.

My question is if there is a way to put an XLOCK on the orders table only during the critical section lines. Also my understanding is that once the insert into orders is done that the server will hold a lock until the commit in case of rollback.? I don't want to XLOCK the whole SP if I don't have to. It is quick though.

Thanks

View Replies !

---

Table Locking
I need 2 unique sequential numbers to be stored in the same table. Obviously, for the first number I can use an auto-number field. For the second number, I would like to use a 2nd table with just 1 field that would only ever store 1 record. The idea being that if a user needs to generate a new unique number, they lock the 2nd table, lookup the current value, store the value in a variable for use, add 1 to the value of the number in the table, then unlock the table.

Presumably, I'd use something like this:

SELECT OrderNo
FROM tblUniqueOrderNumber (WITH TABLOCK)

Does this lock the table so no other users can read/write the table? If so, how do I unlock the table once I've updated OrderNo? What happens if another user then reads the table while it has a TABLOCK applied? I'm using SQL Server 2005 and the database is a multi-user database. I have a feeling I'm not going about this the best way possible, but I'd like to know how the table lock works eitherway.

View Replies !

---

Table Locking
hi

can i lock my table while doing an updation so that all others in n/w can insert into the table only after my operation is completed?

if a user attempts to save/update the 'locked'table, will he get an error message or will his application (using ADO) will wait till the table gets unlocked?

View Replies !

---

Locking A Table
Can any one please tell me how to lock & unlock a table a from inside a store procedure using T-SQL
thanks

View Replies !

---

Table Locking?
Gurus,

I am trying to execute this stored procedure when I try to change all occurences of a field in a table.

(
@Dept char(8),
@DDept char(8)

)
As
Set NoCOUNt On

Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO

The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.

The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.

Is there a way around it, without doing major surgery on the VB code?

Thanks.

Sam

View Replies !

---

Locking The Whole Table
Hello,
I am working on the implementation of a database for my company and I have
a simple (I hope) question to ask.
I have the following stored procedure that will be running when I want
(actually when I want to delete a customer).
Do not mind if you do not understand what this procedure does... ;-)
Actually the Level8View is a VIEW of a nested table
(CustomerData->CustomerData).

CREATE Procedure DeleteCustomer
@ClientID INT
AS
UPDATE Level8View
SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level1=@ClientID
UPDATE Level8View
SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level2=@ClientID
UPDATE Level8View
SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level3=@ClientID
UPDATE Level8View
SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level4=@ClientID
UPDATE Level8View
SET Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level5=@ClientID
UPDATE Level8View
SET Level6=Level7, Level7=Level8
WHERE Level6=@ClientID
UPDATE Level8View
SET Level7=Level8
WHERE Level7=@ClientID
DELETE FROM Customers
WHERE ClientID=@ClientID
GO

I checked this procedure and works perfectly.
What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
view (Level8View) before the Update statements and unlock it after the
delete statements.
I do not want to do a Row by Row lock, or Page by Page lock since the updates
in this table occur in the whole table and during that operation I do not
want other threads to issue SELECT, INSERT or UPDATE statements.

Can someone answer me how I can lock and unlock the whole view or table
please?

I will appreciate it for your answer. Thanks.

Yours, sincerely
Efthymios Kalyviotis
ekalyviotis@comerclub.gr

View Replies !

---

Locking Table
hi,
I am running a sql job that select data from one database (multiple table) and inserts into another database (1 table)

I have a problem with the way it is locking the tables I am selecting from !

my query is like :

insert into testdb.dbo.tabletest

select * from
livedb.dbo.tablex
inner join liveb.dbo.table1 on tablex.id=table1.xid


It seems to be locking the table and blocking other users from accessing the table.

suppose my question is why is the select locking the table ?

View Replies !

---

Table Locking
I have a batch process that runs on a table for several minutes. While the batch is running, an insert to that table gets hung until the batch completes. This does not occur in the full version, the insert does not have to wait. Is there something I can set in my batch to not lock the table?

RLiss

View Replies !

---

Table To Repopulate A Form
Making a form where a patient will fill out check boxes and after submission would like to present their submitted form back if they choose the option and be able to edit the data entered. What I am wanting to do is repopulate the form, but the checkboxes will need a database with this information that will then go to a php script and take the values entered into the various checkboxes and check those values from the database that were entered origionally. So lets say the database haspid - int(5) auto_increment primary keypatient_first - varchar(30) first namepatient_last - varchar(50) last namearthritis - bool or what?copd - bool or what?tin ear -bool or what?What would be the best to check against to repopulate does a true false make sense or should it either be NULL or the actual condition (just kidding about the tin ear)?

View Replies !

---

One Form To Update Different SQL Tables Based Upon Form Selection
Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris

View Replies !

---

Large Table-Table Partition, View Or Other Method?
Hi everyone,

I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?

Can you please give some suggestions? It will be very helpful if you can post some references or examples.

Thank you!

View Replies !

---

How Do I Find Out Who's Locking A Table? And Than Do Something About It?
So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction
 Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?
 

View Replies !

---

How To? - OpenQuery Table Locking
We have a MS SQL database with an Oracle linked server  'ALTTEST'
 We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction?
I've tried applying the code below, but it doesn't work.
 Any help appreciated.
BEGIN TRAN
SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX)
COMMIT
 

View Replies !

---