Will A Single Row Be Locked Or The Entire Table?
Feb 19, 2006
I am giving the following statement outside the begin tran block in a stored procedure:
select * from table1 with (holdlock) where column1 = @colValue
I have the following two questions regarding above situation:
Being outside the begin tran block, will the select statement cause a lock to be held or it has to be within the begin tran block for the lock to be held?
Will the select statement hold a lock only on the row or rows matching the where clause, or it may lock a page or even the entire table in this situation?
Thanks
View 3 Replies
ADVERTISEMENT
Nov 16, 2015
I usually go for the largest datafile and then query in-there...But now I need to automate it for several instances... I need to be able with one script quickly retrieve the top 5 largest tables for the entire instance,not by database...
View 3 Replies
View Related
Aug 30, 2015
Would this go a long way towards securing the data?
Database.Store(this)
Database.Delete(this)
Database.Load(this)
Where "this" is a single object mapped to a single stored procedure.
Two parameters are recommended: one to identify user, and one to specify an "action" or different SP.
View 2 Replies
View Related
Oct 20, 2006
I have gotten my production version of SQL locked into single user mode. We recently upgraded to a clustered configeration and when I went to pause the system to get control I was told Pause was not available in a clustered environment. I restarted the service with -m [single user] but couldn't run Enterprise Manager as it appeared to take the only user slot and not give me one. Then I hit on startingup with Query Analyzer and that worked but now how do I get it back into multi-user mode. I placed the -m in the settings using Enterprise manager; could I stop the service and restart at the cmd prompt using -f [minimal config] and get Enterprise manager running? Any assistance appreciated.
View 4 Replies
View Related
Jan 10, 2002
Hi,
When I am deleting records from a table, I am not able to select or insert records from the table. This particular table needs to be used by more users and very frequently used.
Could anyone please help with ideas.
Thanks in advance
John Jayaseelan
View 5 Replies
View Related
Nov 18, 2005
I can't open a table that has 5 records. Is there any kill processes that can unlock the table for me?
View 2 Replies
View Related
May 16, 2006
Hi! I've some data in a table, and I want to add these records to another table, but I tried yet with select ... Into ... and I lose all the data from this one. I need to ADD the data from table to table. Any suggestion?
Thx
View 3 Replies
View Related
Jul 23, 2005
Hi,I need to lock a table so that Inserts are prevented as well as deleted andupdates. At present I'm thinking this might do it:SELECT * FROM myTable WITH(UPLOCK)but then again I'm not sure whether this will cover the insert case.Thanks,Robin
View 5 Replies
View Related
Mar 13, 2008
How can I do in order that a table show all its rows in a page?And if the table can only show a number of rows cause arrive to the end of the page show the table in the next page?
Thanks!
View 5 Replies
View Related
Sep 18, 2006
Hullo folks, I'm having what I assume is a fairly mundane security issue.
I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.
Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.
Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?
View 7 Replies
View Related
Aug 17, 1999
How can I instruct SQL Server to keep entire table in memory? ie the memory pages should not be swapped to HD.
View 2 Replies
View Related
Jul 20, 2005
I'm a newbie to script writing. I'm trying to write a script to copy alldata from a table to the same table in a 2nd database. Both databases areon the same server and are identical in design. I can do this with DTS butwanted a script I could email to a user to run in Query Analyzer.Example:Copy entire table called 'Customers' in the 'Data01' database totable 'Customers' in the 'Data02' databaseI want to overwrite all data in the destination table.Thanks
View 1 Replies
View Related
Mar 5, 2008
I have table in my SQL database, I want to copy(or insert) one record(one entire row using primary key, which is auto)
I am thinking something like this
Insert Into Table1 (a, b, c,d,e) values(select a,b,c,d,e from Table1 where Primarykey=1 or any number)
telll me how do I do that
maxs
View 4 Replies
View Related
Sep 20, 2007
The problem that I am having is that I am not getting the results from an entire table, just a small subset. The sql command is "Select * from login", which has always implied to me, get everything, to end of table, or, until I tell you to stop. I am getting exactly, 10 items as input, and then it stops. I am including the code below in the hopes that someone can spot what it is that I am doing wrong.
The purpose of the routine is to convert a user login database into a new, expanded format.
TIA, Tom
Imports Common.Utility.Utils
Partial Class UserDatabaseConversion
Inherits System.Web.UI.Page
Dim sqlConn As SqlConnection = getSQLConnection(ConfigurationManager.AppSettings("utilityDbName"))
Dim sqlConnN As SqlConnection = getSQLConnection(ConfigurationManager.AppSettings("utilityDbName"))
Dim sqlStringOld As String = "Select * from login"
Dim sqlStringNew As String = "usp_UT_AddNewUser"
Dim buildCnt As Integer = 0
Dim oldUserCnt As Integer = 0
Dim newUserCnt As Integer = 0
Dim oLocation As String = Nothing
Dim errors As Integer = 0
Dim mesg As String = Nothing
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
getOldUsers()
End Sub
Sub getOldUsers()
Dim odate As Date = Date.Today
Dim sqlCmdOld As SqlCommand = New SqlCommand()
sqlCmdOld.CommandText = sqlStringOld
sqlCmdOld.CommandType = CommandType.Text
sqlCmdOld.Connection = sqlConn
Dim dbReader As SqlDataReader = Nothing
Try
sqlConn.Open()
dbReader = sqlCmdOld.ExecuteReader()
If dbReader.HasRows Then
Do While dbReader.Read()
oldUserCnt += 1
Select Case IsDBNull(dbReader.Item("reg_date"))
Case True
buildNewUser(dbReader.Item("name"), dbReader.Item("password"), dbReader.Item("email"), dbReader.Item("type"), dbReader.Item("org"), dbReader.Item("occupation"), dbReader.Item("location"), odate)
Case False
buildNewUser(dbReader.Item("name"), dbReader.Item("password"), dbReader.Item("email"), dbReader.Item("type"), dbReader.Item("org"), dbReader.Item("occupation"), dbReader.Item("location"), dbReader.Item("reg_date"))
End Select
Loop
End If
Catch ex As Exception
Finally
sqlConn.Dispose()
End Try
If errors = 0 Then
lblMesg.Text = "Conversion completed succesfully.<br />Users In: " & oldUserCnt & " , Users Out:" & newUserCnt
Else
lblMesg.Text = "Conversion comleted with errors.<br />Users In: " & oldUserCnt & " , Users Out:" & newUserCnt & "<br />"
lblMesg.Text += mesg
End If
End Sub
Sub buildNewUser(ByVal oname As String, ByVal opassword As String, _
ByVal oemail As String, ByVal otype As String, _
ByVal oorg As String, ByVal oocc As String, _
ByVal oloc As String, ByVal oregdate As Date)
Dim sqlCmdNew As SqlCommand = New SqlCommand()
sqlCmdNew.CommandText = sqlStringNew
sqlCmdNew.CommandType = CommandType.StoredProcedure
sqlCmdNew.Connection = sqlConnN
Dim userName As String = Nothing
Dim firstName As String = Nothing
Dim middleName As String = Nothing
Dim lastName As String = Nothing
Dim title As String = Nothing
Dim emailAddress As String = Nothing
Dim passWord As String = Nothing
Dim userType As String = Nothing
Dim org As String = Nothing
Dim occ As String = Nothing
Dim loc As String = Nothing
Dim rdate As Date = Nothing
oLocation = oloc
' user name will be the first part of the email address
emailAddress = oemail
Dim uname() As String = oemail.Split("@")
' extend the length of the user name to at least 5 characters
Select Case uname(0).Length
Case Is > 4
userName = uname(0)
Case Is = 4
userName = uname(0) & "1"
Case Is = 3
userName = uname(0) & "12"
Case Is = 2
userName = uname(0) & "123"
Case Is = 1
userName = uname(0) & "1234"
End Select
' check for suffixes
Dim suffix() As String = oname.Split(",")
Select Case suffix.Length > 1
Case True
title = suffix(1)
End Select
' split names out
Dim names() As String = suffix(0).Split(" ")
' possibly 3 components to the name
Select Case names.Length
Case Is = 1
firstName = names(0)
Case Is = 2
firstName = names(0)
lastName = names(1)
Case Is = 3
firstName = names(0)
middleName = names(1)
lastName = names(2)
End Select
' setup password. must be at least 6 characters.
' if less, extend to 6.
Select Case opassword
Case Is > 5
passWord = opassword
Case Is = 5
passWord = opassword & "1"
Case Is = 4
passWord = opassword & "12"
Case Is = 3
passWord = opassword & "123"
Case Is = 2
passWord = opassword & "1234"
Case Is = 1
passWord = opassword & "12345"
End Select
' user type, organization and occupation
userType = otype
org = oorg
occ = oocc
' registration date
Select Case IsDBNull(oregdate) Or IsNothing(oregdate)
Case True
rdate = Today.Date
Case False
rdate = oregdate
End Select
' try to extract the location
' get the country
Dim country As String = getCountry(oloc)
' get the state
Dim state As String = getState(oloc)
' only thing left is the city
Dim city() As String = oloc.Split(",")
' add parameter values
sqlCmdNew.Parameters.AddWithValue("@NamePrefix", System.DBNull.Value)
sqlCmdNew.Parameters.AddWithValue("@FirstName", replaceSingleQuote(firstName))
Select Case IsNothing(middleName)
Case True
sqlCmdNew.Parameters.AddWithValue("@MiddleName", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@MiddleName", replaceSingleQuote(middleName))
End Select
Select Case IsNothing(lastName)
Case True
sqlCmdNew.Parameters.AddWithValue("@LastName", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@LastName", replaceSingleQuote(lastName))
End Select
sqlCmdNew.Parameters.AddWithValue("@UserName", replaceSingleQuote(userName))
Select Case IsNothing(title)
Case True
sqlCmdNew.Parameters.AddWithValue("@NameTitle", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@NameTitle", title)
End Select
sqlCmdNew.Parameters.AddWithValue("@Password", replaceSingleQuote(passWord))
sqlCmdNew.Parameters.AddWithValue("@EmailAddress", emailAddress)
sqlCmdNew.Parameters.AddWithValue("@PasswordHint", userName)
Select Case isEmpty(org)
Case True
sqlCmdNew.Parameters.AddWithValue("@Organization", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@Organization", replaceSingleQuote(org))
End Select
Select Case isEmpty(occ)
Case True
sqlCmdNew.Parameters.AddWithValue("@Occupation", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@Occupation", replaceSingleQuote(occ))
End Select
sqlCmdNew.Parameters.AddWithValue("@Address1", System.DBNull.Value)
sqlCmdNew.Parameters.AddWithValue("@Address2", System.DBNull.Value)
Select Case isEmpty(city(0))
Case True
sqlCmdNew.Parameters.AddWithValue("@City", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@City", replaceSingleQuote(city(0)))
End Select
sqlCmdNew.Parameters.AddWithValue("@ZipCode", System.DBNull.Value)
sqlCmdNew.Parameters.AddWithValue("@PostalCode", System.DBNull.Value)
Select Case IsNothing(state)
Case True
sqlCmdNew.Parameters.AddWithValue("@State", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@State", state)
End Select
sqlCmdNew.Parameters.AddWithValue("@Other", System.DBNull.Value)
Select Case IsNothing(country)
Case True
sqlCmdNew.Parameters.AddWithValue("@Country", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@Country", country)
End Select
sqlCmdNew.Parameters.AddWithValue("@GeneralEmail", False)
sqlCmdNew.Parameters.AddWithValue("@FundRaiseEmail", False)
sqlCmdNew.Parameters.AddWithValue("@GeneralUSPSMail", False)
sqlCmdNew.Parameters.AddWithValue("@FundRaiseUSPSMail", False)
sqlCmdNew.Parameters.AddWithValue("@DateAdded", rdate)
sqlCmdNew.Parameters.AddWithValue("@DateUpdated", rdate)
sqlCmdNew.Parameters.AddWithValue("@Active", True)
sqlCmdNew.Parameters.AddWithValue("@UserType", userType)
Dim iRows As Integer = 0
' try to insert the row into the table
Try
sqlConnN.Open()
iRows = sqlCmdNew.ExecuteNonQuery()
If iRows <> 1 Then
errors += 1
mesg += "<br />User: " & firstName & " " & lastName & " was not added.<br />"
End If
newUserCnt += 1
Catch ex As Exception
Dim exMsg As String = ex.Message.ToString()
writeApplicationLog(exMsg, ConfigurationManager.AppSettings("UtilityDBName"))
errors += 1
mesg += "<br />User: " & firstName & " " & lastName & " was not added due to " & exMsg & "<br />"
Finally
sqlConnN.Close()
sqlCmdNew.Dispose()
End Try
End Sub
Function getState(ByVal loc As String) As String
Dim state As String = Nothing
If loc.Contains("NY") Then
state = "NY"
ElseIf loc.Contains("WA") Then
state = "WA"
ElseIf loc.Contains("PA") Then
state = "PA"
ElseIf loc.Contains("AZ") Then
state = "AZ"
ElseIf loc.Contains("MI") Then
state = "MI"
ElseIf loc.Contains("WI") Then
state = "WI"
ElseIf loc.Contains("CT") Then
state = "CT"
ElseIf loc.Contains("NC") Then
state = "NC"
ElseIf loc.Contains("CA") Then
state = "CA"
ElseIf loc.Contains("NY") Then
state = "NJ"
End If
Return state
End Function
Function getCountry(ByVal cntry As String) As String
Dim country As String = "US"
If cntry.Contains("Canada") Then
country = "CA"
End If
Return country
End Function
End Class
View 1 Replies
View Related
Feb 11, 2008
Hello,
I have a table that I want to cache. So, if this is my query "SELECT * FROM myTable" that I create the cache from, how can I filter the data?
I really need to cache the whole table, since there are a myriad of different statements being executed against the table, so just caching a specific query won't do.
I've found the best approach to make a question is writing it in code, so how can I do the following:
SELECT ColNames FROM MyTableWhichShouldNowBeCached WHERE whereColumns=someParams.
ColNames and someParams make up for a variation of about to 120 different queries. I use SQL 2005 express edition with advanced services.
Cheers!
/Eskil
View 2 Replies
View Related
May 26, 2000
Hi Guys!
This SQL statement, though carefully written to delete only selected rows, deletes the entire A_Shift_Times table:
DELETE FROM A_Shift_Times
WHERE EXISTS (
SELECT 1
FROM
Users
WHERE
(A_Shift_Times.time_in >= CONVERT(DATETIME, '2000-05-29 00:00:00', 102)) AND
((Users.User_Password LIKE N'mrr%') OR (Users.User_Password LIKE N'work%')))
What gives? What am I doing wrong here?
View 1 Replies
View Related
Aug 19, 2002
Hi everybody,
We are using SQl-2000 Sp2 clustured on two compaq servers
We have to modify our database shema every month.
We are using sp_repladdcolum and sp_repldropcolum to
modify table without snapshot.
Owever I would like to add/delete entire table in shema
database without using snapshot ?
Is it possible ?
How to do ?
Thanks.
Fabio
View 1 Replies
View Related
Oct 21, 2014
I convert EBCDIC file to text file at C:orderorder.txt. There are about 1400000 rows. The length of line is 1500.
How to insert entire line into one column of the table? (only one column, named as [LineDetail])
View 1 Replies
View Related
Sep 18, 2013
I'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.
View 6 Replies
View Related
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Feb 9, 2012
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
View 5 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 Replies
View Related
Sep 29, 2014
i have 6 table in SQL Server and i have created one view and create single table by linking all the table,now i want to join two column like
Column A and Column B = Column C
e.g
A B C
Atul Jadhav Atuljadhav
Vijay vijayvijay
in above exambe column A having firstName and Column B having second name and i want to join this two column in C column "atuljadhav" and if column B is blank then it join A value tow timestriger code as it is auto update column and every time (update, append, modify, delete) it should be update automatic
View 5 Replies
View Related
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
Sep 28, 1999
I've managed to BCP in a single table form a backup DAT file into a database, but it took WAY to long (1 hour+ on a meaty server) and I can't understand why.
The table only had a few rows of data and only had a few small dependancy tables.
The table has a primary key, hence an index so the BCP becomes a logged operation but it still should not take this long.
Here is the BCP command line that I ran :-
bcp <dbname>.dbo.<tablename> in <DAT filename> /U sa /P /S <server> /m 1 /n
Could anyone please shed some light on this. Is there anyway of 'fast' bcp'ing this table into the database overwriting the existing one.
Failing that, is there any way of scripting the transfer of a table from one server to another.
Many thanks.
Dave
View 2 Replies
View Related
Mar 18, 2002
Does anyone know of a way to restore a single table using SQL 7.0? I know that I can build a dummy database and do a database restore, then copy the table from database to database. But....was wondering if there is a way to restore a single table from a backup. Is losing the ability to restore a single table one of the 'features' of 7.0?
View 1 Replies
View Related
Feb 15, 2001
Does anyone know if I can restore an individual table in SQL 7.0?
I know I can DTS from a copy of the database but how can I restore a table directly from a backup file.
Thanks
Ben
View 2 Replies
View Related
Oct 13, 2000
How can I restore just one table from the full backup? It was very easy with 6.5 but I am not sure whether it's possible with 7.0 and if it is possible, how to do it.
Thanks in advance.
View 2 Replies
View Related
Mar 15, 1999
I thought I had posted this question already, but didn't see it in
the list. I apologize if this is a repost.
I am running SQL Server 6.5 SP 4.
I am attempting to load a single table from backup, but continually get
the error about schemas not matching. Interestingly, it comes back with
a status 4, and not the status 3 indicating a mismatch on Ansi_Padding.
The statement I am using is
load table demhist from internal_tape with file=5,nounload
I have tried creating the table from scratch and using select * into...
I have tried both above with both settings of Ansi Padding. The table
I am trying to load contaings char columns which allow nulls. I have
experimented with loading 2 other tables. One of the other tables loads
and one doesn't. The one which does not load also has char columns which
allow nulls, while the one that does load does not have char columns
which allow nulls.
Is it a known problem or limitation on the table load that it cannot reload
tables that contain char columns which allow nulls?
View 4 Replies
View Related
Dec 15, 1998
Hi!
This morning, I needed to restore 1 table to a database because it had
accidentally been deleted (don't ask). Each time I tried to restore the
single table, I would select TOOLS| DATABASE BACKUP/RESTORE| RESTORE
tab from Enterprise Manager, then I select SINGLE TABLE, but am only
given the existing table names as choices to restore. This happens when
I select to restore 'FROM DEVICE' as well.
Am I doing something wrong or is this how MS SQL6.5 is supposed to
work? Am I only able to restore a corrupt table and not a missing
table? I ended up restoring the entire database...
Thanks for your help!
Toni Eibner
View 2 Replies
View Related
Sep 10, 2001
It appears that in SQL 2000 restoring a single table is a bit more tedious and less user friendly as in earlier versions (6.5). Can anyone explain in a nutshell the easiest/quickest way to restore a single table from a backup?
Thanks,
Christine
View 1 Replies
View Related
Nov 12, 2004
Hi all !
I have a table with no keys (temp table) which looks like this :
col1|col2|col3
001|A|.087
001|B|.032
001|C|.345
002|A|.324
002|B|.724
003|A|.088
003|C|.899
001|A|.087
001|A|.234
001|B|.032
As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work.
Anyone have an idea how I can do it ?
View 7 Replies
View Related
Apr 16, 2008
Is there a tool out there to backup only one table at time
in SQL Server 2005 ?
View 9 Replies
View Related
