1. How can I know exactlly what row is locked? Is data in "resource"
column of sp_lock usable? For example, resource = 03000d8f0ecc
511400DB S
GRANT
51142775760271KEY(03000d8f0ecc)
XGRANT
51142775760271PAG1:1112
IXGRANT
51142775760270TAB
IXGRANT
511855753430TAB
ISGRANT
2. Is there any equivalent of ORACLE's "rowid" pseudocolumn? How can I
uniquelly identify some row in any given table ( which may not have
primary key defined )?
In SQL7 books online its written about RID ( row identifier) Is there any method to use this RID in programming? Also it stated that the option for row level lock and table level lock can be given explicitly through system procedures. I would like to know how to use these options.
I am trying to update a SQL db record with ADO commands from an asp page thru a stored proc using the RowID and it is not working. RowID is the Identity seed record.
Here is the stored proc:
CREATE PROCEDURE [sp_Update_tblECMTimeTrackingMain] @RowID int, @StartTime datetime, @EndTime datetime, @TransxStatus varchar(50) AS Begin UPDATE [tblECMTimeTrackingMain] SET FStartTime = @StartTime, FEndTime = @EndTime, TransxStatus = @TransxStatus where RowID = @RowID End GO
I'm new to Sql Server. I have a jdbc program which gets data from tables in a Sql Server database and inserts it into the corresponding tables in an Oracle database. This program is supposed to run in an infinite loop. On every run of the program, it should get the rows added after the last run. Is there any way I could get the rowid of the last record? I know that there is no visible rowid in Sql Server. Can anyone please suggest a way around this problem? It would be a great help.
I want to fetch the records from the table which does not have Id column and also I don't want to use the temp tables, as my table is having thousands of records then it will create temp and all the records will be added to that temp table which will consume a lot of time so I want to fetch the records depending upon the rowid maintained by the sql please revert with ur valuable answers thankx in advance
Hello gays,I am using Oracle and there is one rowid field ButI donot know RowId Field available in Sql Server Or NotIf Yes then give me reply how to use it ?If No then Give me replay What is alternate of Rowid?
In Oracle we have a datatype called 'ROWID' - Oracle uses this datatype to store the address (rowid) of every row in the database. Do we have any equivalent datatype in SQLServer similar to this ?
I have an easy question. In Oracle I can retrieve a column named "ROWID" which returns an unique identifier of the row in the database. I want to have the same element in SQL Server.
Greetings all, I am tring to capture the ID of a newly inserted record from a form to a label that I will reference in a reciept page. I intend to pass the rowid to retrieve record information on other pages. The insert suceeds... I just need to capture the auto generated ID for the new row to a label on the page post onclick. Any thoughts?
Dim MySQL As String = "Insert into dropkick (name, status, payroll, unit, contactnumber, email, equipment, issue, timein) values (@name, @status, @payroll, @unit, @contactnumber, @email, @equipment, @issue, @timein)"Dim myConn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString) Dim Cmd As New SqlCommand(MySQL, myConn)Cmd.Parameters.Add(New SqlParameter("@payroll", txt_payroll.Text)) Cmd.Parameters.Add(New SqlParameter("@name", txt_name.Text))Cmd.Parameters.Add(New SqlParameter("@status", "W")) Cmd.Parameters.Add(New SqlParameter("@unit", txt_dept.Text))Cmd.Parameters.Add(New SqlParameter("@contactnumber", txt_cell.Text)) Cmd.Parameters.Add(New SqlParameter("@email", txt_email.Text))Cmd.Parameters.Add(New SqlParameter("@equipment", txt_equipment.Text)) Cmd.Parameters.Add(New SqlParameter("@issue", txt_issue.Text))Cmd.Parameters.Add(New SqlParameter("@timein", lbl_datetime.Text)) myConn.Open() Cmd.ExecuteNonQuery() Label1.Visible = "true" Page.DataBind() myConn.Close() Label1.Text = "Your data has been received!" ''LABEL TO BE POPULATED WITH ID OF NEW RECORD lbl_id.Text = ID
I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.
We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:
AAAA.1234 /* (account number)*/ 1/1/2015 $150 First Transaction 1/3/2015 $24.233 Second Transaction BBBB.5678 1/1/2015 $350 Third Transaction 1/3/2015 $24.233 Fourth Transaction
My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.
Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.
I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:
AAAA.1234 1/1/2015 $150 First Purchase AAAA.1234 1/3/2015 $24.233 Second Purchase BBBB.5678 1/1/2015 $350 Third Purchase BBBB.5678 1/3/2015 $24.233 Fourth Purchase
My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM GenericTable WHERE RowHeader LIKE '____.____%'
Results look like this:
But every time I run the routine, I get different numbers!
Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.
TRUNCATE TABLE GenericImportTable; ALTER TABLE GenericImportTable DROP COLUMN RowID; BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt' WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6) ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM GenericImportTable WHERE RowHeader LIKE '____.____%'
I have one query which uses a join query to gather all the projects that should show up in someone's list over a period of time (returns and id (int) and name (varchar) paired dataset). I want to do a separate query that takes that list and selects all projects (same paired set ... id and name) EXCEPT where it matches an id on a row of the given result set. The one query looks like this ..DECLARE @startDate datetimeDECLARE @endDate datetimeDECLARE @userId UNIQUEIDENTIFIERSELECT @startDate = ppStartDate FROM ppTablewhere payPeriodID = @payPeriodIDSELECT @endDate = ppEndDate FROM ppTable WHERE payPeriodID = @payPeriodIDSELECT @userId = userID FROM usersTable WHERE userName = @userNameSELECT DISTINCT p.projectID, p.projectNameFROM projectsTable pLEFT JOIN projectMemberhsip m ON m.ProjectId = p.ProjectIdLEFT JOIN timeEntryTable t ON t.ProjectID = p.ProjectIdWHERE t.TimeEntryUserId = @userID AND t.TimeEntryDate >= @startDate AND t.TimeEntryDate <= @endDateORm.UserId = @userID I want to get the same selection from projectsTable WHERE it's not anything from this result set.Haven't been able to get it by modifying the WHERE logic. Is there a way to select all WHERE id != (resultSet from this SELECT)? TIA!
Hi everyone, I have a question about SQL Server 2005. I have written an ASP.Net 2.0 Web Application and it is using SQL Server 2005 as Database. In the last few days I noticed that the app is down sometimes. To analyze the problem I looked at the activity monitor in SQL Management Studio. I can see there approximately 170 processinfos. I want to describe the column values of the process infos: Process-ID: Unique ID and a red down-showing-arrow-icon User: My UserDatabase: My DatabaseStatus: sleepingCommand: AWAITING COMMANDApplication: .Net SqlClient Data Provider When I click Locks by Object, I can see the IDs of the Processinfos. Again I will show some colums:Type: DATABASERequirementtype: LOCKRequirementstate: GRANTOwnertype: SHARED_TRANSACTION_WORKSPACEDatabase: My Database So my question is, does this mean, that i have locked the db? How are they handled? For example I have a windows service, which is doing checks in db every 10 seconds. I can see, that each check generates a new processinfo? Is this usual, or am I doing something wrong? Thnaks for help,Byeee
When I run a select statement : select 'X' from table1 where c1 = condition locking on indexes behaves as expected
However if I run select 1 from table1 where c1 = condition locking on indexes goes wild locking pages and rows on indexes that are not even referenced in the query. Any ideas Why?
Hello All, I'm just migrating from oracle to SQL.Can anybody tell me that how effectively I can use Row level locking in SQL? If tow users are attemping to Moify same record how i can deal it in Back end(SQL)? Thanks in Advance. Suchee
i have an application in production(sql 6.5 ) which causes locking which times out my other processes , iwant to capture time the locking takes place i have found in bol that i can get time deadlock occurs using trace flag 3605 in sql7.0 ,if i have to use trace flag is it ok with dbcc traceon or -T option in startup is recommended. any advice would be appreciated tia ram
I have used DTS in the past to copy information in certain tables in production over the top of those same tables in test. However, the process is now failing. Does DTS require an exclusive lock on the source table, as well as the destination table during the export process? Will shared locks on the table I need to copy prevent DTS from completing the process?
We are running out of locks while updating a particular table (table name = history, rows = 25,000,000) in SQL Server 6.5.
LE threshold maximum is set to 200. LE threshold minimum is set to 20. LE threshold percentage is set to 0.
Locks is set to 0.
I have also included the stored procedure, which we use to update the history table.
As you can see, from the first four lines, we ran this SP 4 times processing around 6 million rows at a time. It runs out of locks once it is around 5.5 to 6.5 million rows. Is there a way of locking the table so that this SP can be run just once which will effectively process all the 26 million rows in one go?
Any help will be greatly appreciated.
Winston
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 5635993 and 12000000) --declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 12000001 and 19000000) declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno > 19000000)
open minihist fetch next from minihist into @huin,@huan,@hmailingdate while (@@fetch_status <> -1) begin
if (@@fetch_status <> -2) begin
select @mailtot = 1 select @mail12m = 0
/*** Get the gender ***/ select @sex = gender from name where uin = @huin
/*** Calculate if mailed in the last twelwe months ***/ if (@hmailingdate <> null) and (@hmailingdate > '19980524') select @mail12m = @mail12m +1
/*** Get info for this uan from address_summary ***/ select @mailtot = (@mailtot+mailed_total), @mail12m = (@mail12m+mailed_12months), @lastday = last_date from address_summary where uan = @huan
/*** Insert a row into address_summary if doesn't exist ***/ IF @@rowcount = 0
