Hi guys,
I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
View 5 Replies View RelatedIs it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric
View 3 Replies View RelatedIn SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?
Example:
I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.
I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.
BTW - this would be a stored procedure...
Any suggestions would be greatly appreciated.
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.
View 2 Replies View RelatedDoes anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
Any help would be much appreciated.
Jim Plant
Does anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
Any help would be much appreciated.
Jim Plant
Hi!
I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:
Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34
Incorrect syntax near the keyword 'option'.
Here is the function:
create FUNCTION [dbo].[ExpandedCTE]
(
@p_id int
)
RETURNS TABLE
AS
RETURN
(
with tbl_cte (id, tbl_id, lvl)
as
(
select
id, tbl_id, 0 lvl
from
tbl
where
id = @p_id
union all
select
t.id, t.tbl_id, lvl + 1
from
tbl_cte
inner join tbl t
on rnr.tbl_id = tbl_cte.id
)
select
id, tbl_id, lvl
from
tbl_cte
option (maxrecursion 0)
)
Please help!
Alexander.
P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.
There are 2 tables which need to have data inserted into them for auditing purposes. The number of inserts per minute seems be at least 50-100. How to reduce locks during inserts.
There are 2 tables
Table1
ID - Surrogate Key/identity Column
SomeColumn1
SomeColumn2
SomeColumn3
SomeColumn4_timestamp
clustered index on ID column
Table2
ID Column ..... there's a call to get id from SCOPE_IDENTITY()
SomeColumn1
SomeColumn2
clustered index on ID column
NC idx on SomeColum1
NC idx on SomeColum2
A Sproc has the following code:
I changed the names to protect the innocent
CREATE PROCEDURE [dbo].[logging_sp]
@Audit BIGINT,
@varT1_1 NVARCHAR (50),
@varT1_2 NVARCHAR (64),
@varT1_3 INT,
@VarTime DATETIME,
@varT2_1 NVARCHAR (50),
@varT2_2 NVARCHAR (1024),
[code]....
We have a SSIS package which loads the data from csv files to DB. It only loads the new entries ie if the row already exists in the tables than it doesn't insert it. For this we load the CSV to temp tables for respective schemas and than those are compared with base tables of respective schemas and inserted new rows. For this we use Merge statement.
View 1 Replies View RelatedSSIS Newbie Question:
I have a simple Control Flow setup that checks to see if a particular table exists. If the table does not exists, the table is created in an alternate path, if it does exist, the table is truncated before moving to a file import Data Flow that uses an OLE DB Destination to output the imported data.
My problem is, that I get OLE DB package errors if the table the OLE DB Destination Container references does not exist when I load the package.
How can I over come this issue? I need to be able to dynamically create the table in an earlier step, then use that table to import data into in a later step in the workflow.
Is there a switch I can use to turn off checking in the OLE DB Destination Container so that it will allow me to hook up the table creation step?
Seems like this would be a common task...
Steps:
1. Execute SQL Task to see if the required table exists
2. Use expresions to test a variable to check the results of step 1
3. If table exists, truncate the table and reload it from file in Data Flow using OLE DB Destination
4. If table does not exist, 1st create it, then follow the normal Data Flow
Can someone help me with this?
Signed: Clueless with a deadline approaching...
command.CommandText = “SELECT UserName from Users WHERE UserID = “ = userID
Executing this command returns one table with one column with one row. What is the syntax for getting that value into a variable? I can get the information into a dataSet but I can’t get it out. Should I be using a dataSet for this operation?
The rest of the code so far:
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.TableMappings.Add("Table", "Users");
dataSet = new DataSet();
dataAdapter.Fill(dataSet);
Hi,
i m pretty new to this forum and c#.net
i m doin a project in c#.net
I have four values in my datarow array
for example
DataRow[] cmb;
cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows
for(i=0;i<cmb.Length;i++)
{
cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow'
}
How to assign my session values to them.
I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help
me.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
In my BLL I have a method that adds a new row to a table in the database...
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] public bool AddContact(string firstname, string lastname, string middleinit, bool active, Guid uid, bool newsletter) { ContactsDAL.tblContactsDataTable contacts = new ContactsDAL.tblContactsDataTable(); ContactsDAL.tblContactsRow contact = contacts.NewtblContactsRow();
contact.FirstName = firstname; contact.LastName = lastname; contact.MiddleName = middleinit; contact.Active = active; contact.UserID = uid; contact.Newletter = newsletter;
contacts.AddtblContactsRow(contact); int rowsAffected = Adapter.Update(contact);
return rowsAffected == 1; }
The primary key in this table is a BigInt set as an identity column....How do I capture the value of the primary key that gets created when the new row is added?
Hi,I have a client/server app. that uses a windows service for the server and asp.net web pages for the client side. My server class has 3 methods that Fill, Add a new record and Update a record. The Fill and Add routines work as expected but unfortunately the update request falls at the 1st hurdle.I pass two params to the remote(server) method for the update, one is the unique ID and the other is a string that is the name of the table in the database. See code below. I need the SelectedRow method to return a datarow that will then populate textbox's on another page. When the method is called I get an 'internal system error.....please turn on custom errors in the web.config file on the server for more info.(unfortunately my server is not s web server so I don't have a web.config file!!).Can anyone see anything obvious.Cheers. >>Calling routine:Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadSystem.Threading.Thread.CurrentThread.CurrentCultu re = New CultureInfo("en-GB")hsc = CType(Activator.GetObject(GetType(IHelpSC), _"tcp://192.168.2.3:1234/HelpSC"), IHelpSC)Dim drEdit As DataRowDim intRow As Integer = CInt(Request.QueryString("item"))strDiscipline = Request.QueryString("discipline")drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote methodstrRecord = drEdit.Item(0)txtLogged.Text = drEdit(1)txtEngineer.Text = drEdit.Item(3)End SubRemote Class Function:Public Function SelectedRow(ByVal id As Integer, ByVal discipline As String) As System.Data.DataRow Implements IHelpSC.SelectedRowstrDiscipline = Trim(discipline)Dim cmdSelect As SqlCommand = sqlcnn.CreateCommandDim drResult As DataRowDim strQuery As String = "SELECT * FROM " & strDiscipline & _" WHERE CallID=" & idcmdSelect.CommandType = CommandType.TextcmdSelect.CommandText = strQuerysqlda = New SqlDataAdaptersqlda.SelectCommand = cmdSelectds = New DataSetsqlda.Fill(ds, "Results")drResult = ds.Tables(0).Rows(0)Return drResultEnd Function
View 3 Replies View RelatedI have in SQL Table1 the following values in Field1:
ABC
ABc
AbC
abc
aBc
abC Select Field1 From Table1 Where Field1 = "AbC"
I get all six records, not just the one record (AbC) that I want to retrieve. How do I tell the select statement to be case sensitive?
my asp.net statement is given below DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'")[0]; i m using datarowso i need a helpi have quary but i don;t know how can use DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'where CONVERT(binary(5),Userid)=CONVERT(binary(5),'" + txtUserId.Text + "')'" )[0]; this give me error "Where condiction need operator"waiting for reply
Hi. I am new to ADO.NET and I can't seem to figure out how to populate a row that is a layer lower than the instantiated row. I am populating and XML file called Users.xml:
Code:
<user>
<registerDate>9/6/2005</registerDate>
<firstName>TempUser</firstName>
<lastName>TempUser</lastName>
<emailAddress>TempUser</emailAddress>
<password>5F4DCC3B5AA765D61D8327DEB882CF99</password>
<securityQuestion>TempUser</securityQuestion>
<securityAnswer>TempUser</securityAnswer>
<zipCode>TempUser</zipCode>
<uniqueID>TempUser</uniqueID>
<alternateEmail>TempUser</alternateEmail>
<gender>TempUser</gender>
<industry>TempUser</industry>
<occupation>TempUser</occupation>
<jobtitle>TempUser</jobtitle>
<maritalstatus>TempUser</maritalstatus>
<birthDay>
<month>TempUser</month>
<day>TempUser</day>
<year>TempUser</year>
</birthDay>
<homelocation>...
and, i am using the below code to access and populate the rows based on the users registration input:
Code:
Dim NewLogin As Data.DataRow = LoginDS.Tables(0).NewRow
I am able to access all the rows that are one layer into the xml file with the following code:
Code:
NewLogin("someNode") = _someNode.Text
but, how do i populate nodes that are "further down" such as "birthDay". Do i have to reinstantiate NewLogin as ...Tables(1).NewRow? I have tried various forms of this and it doesnt work. Suggestions appreciated... thanks.
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
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
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
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.
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?
Can any one please tell me how to lock & unlock a table a from inside a store procedure using T-SQL
thanks
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 ?
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 1 Replies View RelatedHeys
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key
of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table
- call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable
and update it
the row is in the database
but in vb the datarow primary key is not filled in!
anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
thx a million in advance!
I will attempt to explain my situation more clearly.
I need to get data from a data source using a DataFlow Task (which pushes the DataSet into a Variable) and process the data row by row in a ForNext (ADO Enumerated) Container. I don't want to map the columns into variables because the number of columns and the data types vary considerably and I want to make my package as "generic" as possible.
Is there a way, in Script, to read the current row of the ForEach Container from into an ADO DataRow so that thie names and values of each column can be accessed?
I know how to read the entire DataSet object from a Variable into an ADO DataSet and iterate through the rows in the normal way but this doesn't suit my purpose. It would be really useful if there was a way to do somehing similar with the current DataRow in the ForEach Container.
To explain what I am doing, the idea is to use the Column Names and Values for each row to construct an xml fragement, store it in a string Variable and (in the next step) use the Web Services Task to call a Web Method with the xml fragment (from the Variable) as one of the inputs.
A less attarctive alernative would be to use a Scipt outside a ForEach Container and loop through the rows of teh DataTable as descibed above and perhaps call the Web Service Task from teh Script. The proble is that I don't know how to do this either and it woudl be much "neater" anyway to use the ForEach Container.
Any ideas?
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
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?
Hi,
im developing an application for my university final year project and i want to lock the table before the query executes to ensure that no other changes are allowed (sorry if im confusing)
So my question , really does anyone know how to do this and is it possible ?, for example in coldfusion a query is surrounded with cflock or something similiar.
Appreciate any and all help guys
Many thanks in advance
andy
Hi
Is a TABLOCK HOLDLOCK in a transaction the best way for locking a table for a brief period of time?
We need to get generate a unique serial number each time a certificate record is printed. We have in place a field called Field_SERIAL_NUMBER for each certificate record, which may be printed 1 time or 100 times. Each time it prints, we will insert a unique number into the Field_SERIAL_NUMBER field.
This is what I'm thinking to approach this.
- Create a new table called Table_SERIAL with one field called Field_NUMBER which has only 1 record set to 1.
- Every time a certificate is printed, we
1. Begin transaction
2. Select @number = Field_NUMBER from Table_SERIAL with a TABLOCK HOLDLOCK
3. Insert that number into the Field_SERIAL_NUMBER field in the certificate table
4. Increment @number by 1
5. Update Field_NUMBER with @number in Table_SERIAL
6. Unlock table
So, everytime the Field_NUMBER is pulled, it should be unique and incremented by 1.
Does this approach sound good?
Joyce
4. Increment
Hi,
is there anyway i can run delete statements on an audit table that will not lock the whole table?
As it is an audit table, when i run the delete query, the whole application freezes until the delete query is complete.
the process is taking several minutes as the table is approx 40M rows, the where clause in the delete is indexed but thats it really.
any thoughts greatly appreciated by my system users!