Apply Lock On A Row?
Sep 12, 2013I want to apply lock on a row, where by when another application is trying to access the locked row the application will automatically move to the next row instead of waiting for the locked row
View 3 Replies
ADVERTISEMENT
CROSS APPLY Vs OUTER APPLY Example Messed Up?
Nov 27, 2007Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END
and then run a sample T-SQL statement
Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
Error: A Deadlock Was Detected While Trying To Lock Variable X For Read Access. A Lock Could Not Be Acquired After 16 Attempts
Feb 2, 2007I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"
From progress:
Error: The Script returned a failure result.
Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
Row Lock Versus Page Lock In SQL 2000.
Apr 7, 2004Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Please advice. Thanks a bunch.
Pankaj
ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.
May 22, 2006Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List
May 10, 2006Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
How To Lock A Table So Others Cannot Lock It
May 23, 2001Hi,
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
Thank you for any help
How To Apply Hot Fix?
Mar 26, 2007I have a sql server problem for which the solution is at,
http://support.microsoft.com/kb/889521
The article talks about a hot fix, but there is no link for download the hot fix.
Can anyone have a look and guide me.
Thanks
------------------------
I think, therefore I am - Rene Descartes
Does SETROWCOUNT 10 Apply To Only The Sp In Which It Used Or Outside It Also?
May 23, 2007I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.
Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?
Using Like And Apply To 3 Columns
Sep 7, 2007I have a table called Courses with columns "courseCode" "courseName" and "TeacherName"Im passing in a value @prefixText ...I have the following...Select courseModuleCode from courses Where courseModuleCode like @prefixTextwhat I actually want to do is return one list, i.e. one column that will do the same for "courseName" and "TeacherName" aswell based on what the @prefix tag is...so i will end up with one column that may contains some or all of the following courseModuleCode, courseName, TeacherName. Im basically implementing a search on this table and want the users to search by any of the column nameshope this makes sense.
View 6 Replies View RelatedDTS Globalvariable Not Apply ......
Jun 24, 2004I am new to DTS.
In ExecuteSQLTask:
want the original sql statement of <<backup database databaeABC to backup_databaseABC with init>> to be
backup database ? to 'backup_' + ? with init
But it looks the parameter could not place a globalVariable to it and parse it with error.
One alternate way probably need to create a proc to pass a parm to it....
Also, how could pass a globalVariable to a FTP task?
thanks
-D
Best Way To Apply SP3 In Replication
Aug 27, 2004We have a Pub, Dist and Sub Server all running under SP2 with worm update and are looking to apply SP3a . Replication is transactional with heavy usage . What is the best sequence to apply SP3
can it be spread out in 3 weekends or has to be done all together ?
Thanks
Sp3a Does Not Seem To Apply
Feb 26, 2004I am trying to install sql server sp3a. I checked the version before and after the sp is applied, and it shows:
8.00.194
Now, according to the sp3a readme:
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp
the following versions should show:
SQL Server 2000 Original Release 8.00.194 RTM
Database Components SP1 or Desktop Engine SP1 8.00.384 SP1
Database Components SP2 or Desktop Engine SP2 8.00.534 SP2
Database Components SP3 or SP3a, or Desktop Engine SP3 or SP3a 8.00.760 SP3
This would imply that after installing sp3a and restarting, the version reported should now be 8.00.760. However, it still shows 8.00.194. So it appears to have not really installed, althought the sp3a installation ran without incident. So, how can I get the sp3a to not just act like it installed, but actually get it to install? Also, I have shut down all mssql services and rebooted, applied patch, and rebooted again. Doesn't help.
Also, if it is really installing, is there another way to tell?
Anyway, I really need to be able to install sp3a to see if it fixes another problem I'm having. Unfortunetly, since I can't even tell if the sp3a is installing, I can't tell if it really doesn't help or not. Thanks.
Tony F
Cross Apply
May 22, 2008What is Cross Apply, when it will be used ?
View 2 Replies View RelatedWhere To Apply My SQL Knowledge...
Jul 22, 2007Hello everyone...
i am new to Structured Query Language. I am learning it for the past 6 months form a book by myself. I don't have a teacher available to me. Now when i have finished that with all of the exercises completed. i want to make sure what i've learned is i've really learned it. the problem is that there is no such organization here that i join and have some hand on practical. So now i am asking u guys to help me. Tell me some way so that i make sure of whatever knowledge i have about SQL.
don't worry be happy
Which Hotfix To Apply?
Oct 14, 2007We would like to apply a hotfix to solve the issue that brought our server down. The hotfix we are trying to apply is in Build 3175. But the latest Cumulative Update 3 Package is Build 3186. Should we apply 3175? Or we can go for the latest build 3186? Which is recommended?
View 6 Replies View RelatedApply Top && Group By
Nov 20, 2007I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.
Thanks
Apply SP2 After Applying CTP?
Feb 20, 2007Should I apply SP2 even though I have already installed the community preview?
View 1 Replies View RelatedHow Sql Connection Apply With Class
Apr 17, 2008HI,
I am going to make a big web application. so im going to use sql connection with class for example i have 100 web pages and i will make a slq connection in class.vb
Class1.vb(see the below code)
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasicPublic Class Class1
Private conn As New SqlConnection("Data Source=DANISHSQLEXPRESS;Initial Catalog=ARBASHHR.MDF;Integrated Security=True")Private cmd As New SqlCommand()
Private da As New SqlDataAdapter(cmd)
End Class
default.aspx(here is asp.net page i want to use the sql connection from that class.)
but this below page saying that Name "cmd" is not declared and connection
Try
cmd.Connection = conn
conn.Open()Dim ds As New DataSet
cmd.CommandText = "SELECT * FROM Users where (UserName = N'" & TextBox1.Text & "')" 'And (Password = N '" & TextBox2.Text & "')"da.Fill(ds, "data")
conn.Close()
For Each dt As DataTable In ds.Tables
For Each dr As DataRow In dt.RowsTextBox3.Text = dr.Item("Password")
Next drNext dt
If TextBox2.Text = TextBox3.Text ThenResponse.Redirect("Welcome.aspx")
Else
Label3.Text = "Invalid Username/password"
End IfCatch ex As Exception
' MsgBox(ex.Message) '("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error")
End Try
End Sub
please let me know where im doing mistake.....
Thank in Advance
Permissions / Apply Button
Aug 20, 1999Is anyone else having trouble with the Apply button under the permissions tab?
I can change permissions, click apply, the changes are shown, but when I access the database, the changes arn't made. If I close the permissions windows and re open the changes arn't made.
Will SP1 fix this?(it's not listed in the fix text file)
Apply Your Thought On Char To Int
Feb 7, 2004HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
Cross Apply And Newid
Apr 28, 2008Hi,
Why am I getting a different numbers of distinct ids in those queries?
USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';
select locations.*, T.c.value('.','nvarchar(max)') from
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
) as locations cross apply @myXml.nodes('(/lol)') T(c);
select mytable.* , T.c.value('.','nvarchar(max)') from
(
select newid() as Id
union
select newid()
) as mytable cross apply @myXml.nodes('(/lol)') T(c);
Thanks,
Victor
CROSS APPLY Equivalent
Apr 29, 2008I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
How I Apply Unique Constraint
Mar 2, 2007how can i apply unique cosntraint in an existing table's column
i want to do it like this as below
sql server enterprise manager ==> DB==>table (R.click)==>design table
How To Apply Complex Constraints
Jul 23, 2005Hi all!I want to create a constraint that uses data from other tables,specifically i want to make sure that a varchar has exactly the lengthspecified in an integer-column in a table that I pointed out with aforeign key.I would like this to be solved something like this:create table string_size_limits(row_id INTEGER PRIMARY KEYstring_size INTEGER)create table strings(string_size_limit_row_id INTEGERREFERENCES string_size_limits(row_id)string varcher(50)CONSTRAINT check_string_size CHECK ???)Is it possible to solve this problem without SP using above model?Is it possible to solve this problem with SP using above model?Must the above problem be solved using triggers?Any help appreciated
View 4 Replies View RelatedCannot Apply SP4 On Msde 2000 SP3
Apr 2, 2007I try applying the SP 4 on the default instance of the msde 2000 and when it stops after a minute generates the log and says it was successful but no upgrade is done. I use the following command setup /upgradesp sqlrun SECURITYMODE=SQL BLANKSAPWD=1 DISABLENETWORKPROTOCOLS=0 /L*v C:MSDELog.log
On the same machine I has installed also SQLExpress.
I stop the sql services before issuing the command.
Any Ideas?
Patch Apply Question
Feb 11, 2008 I got 4 sql2k5 servers with service patch version as following:
Microsoft SQL Server 2005 - 9.00.2221.00 (Intel X86) Feb 9 2007 17:30:38
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 2)
Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86) May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT
5.2 (Build 3790: Service Pack 2)
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT
5.2 (Build 3790: Service Pack 2)
Can anyone please advise me what is the easiest way to upgrade them to the
most updated patch version? i.e. update path.
Meanwhile, what is the best practice to apply patches? Do you go to
microsoft website and check it out daily and apply it or other tricks?
Please advise. Thanks.
CROSS APPLY Equivalent
Apr 29, 2008
I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
CROSS APPLY Sp_addextendedproperty
Nov 7, 2007I am trying to add Extended properties to each of the columns in my table('MyTable')to the columns
DECLARE @NewTableName as nvarchar(128);
SET @NewTableName = 'MyTable'
SELECT ColumnName FROM(
SELECT c.name as ColumnName
FROM syscolumns c
INNER JOIN sysobjects o
ON o.id = c.id
WHERE o.name = @NewTableName) as T
OUTER APPLY
sp_addextendedproperty( 'Caption', ColumnName,
'user', 'dbo',
'table', @NewTableName,
'column', ColumnName)
I am getting an invalid object name 'sp_addextendedproperty' error.
Jaime
Cross Apply And Newid
Apr 28, 2008I've been trying to figure out why these two return a different amount of distinct ids...
Is that a bug in optimization?
Code Snippet
USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';
WITH locations as
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
)
select locations.*, T.c.value('.','nvarchar(max)') from locations cross apply @myXml.nodes('(/lol)') T(c);
with mytable as
(
select newid() as Id
union
select newid()
)
select mytable.* , T.c.value('.','nvarchar(max)') from mytable cross apply @myXml.nodes('(/lol)') T(c);
Apply Substring On Decimal
Feb 11, 2008
Hi,
I have data about amount which is Decimal in my Excel file . The data is like this: $100.24. So In my SQLServer, I declared Amount column as Decimal. Before doing the exporting this data into SQLServer table, I need to remove $ symbol from the amounts. I applied like this:
Substring([Amount], 1, Length([Amount]-1). It is giving error : Incorrect format or data invalid when I entered this expression in Derived column. How to remove $ sign and store the remaining part from the Amount field?
Thanks in advance
How To Selectively Apply A Filter
Mar 20, 2007Based on some report parameters value?
My dataset to display is coming from a stored procedure. Now based on a report parameter which user selects, I want to filter the results being displayed but only if user selects certain value in the parameter drop down. I tried using iif statement and I can use it to filter dataset based on parameter but can't get how NOT to apply the filter for a particular value of the user selected parameter.
Hope I am clear.
Transact SQL :: How To Apply Condition
Jul 24, 2015I have an order with the time 08/03/2015 7:30 08/03/2015 10:30 ..I have availability with the time 08/03/2015 07:00:00 to 08/03/2015 19:30:00...Here the availability falls for 3 hours only..My requirement is that For order if availability< 4hours i don't want to show employee.How to apply the condition.
View 7 Replies View Related