Assignment Question, Hit Road Block.
Mar 14, 2008I am new to sql.
Question1: How do i run a CHECK against serveral words.
e.g. check("name" is either bill or timmy or sally or jessy)
Question2: What is the best variable to use for time.
ADVERTISEMENT
T-SQL (SS2K8) :: Why Block Scope Variables Exist Outside Of Block
Dec 3, 2014I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
Easy Road To The Web?
Dec 22, 2007Hi!
Short version:
What would you recommend using as developer tools to make a fairly complex DB appear on the web?
Currently I'm thinking of SQL2005 Express Edition and Visual Web Developer 2005EE combined with either VB2005EE or C#2005EE. Seems like a rather steep lerning curve though since I'm used to Pascal and an old old query language called RDO that came with digital RDB on VMS.
Thing is, I know what I want to do, I have disigned the table layout and I know just about what I want it to look like. "All" that's left is to make it presentable... :-)
/Jens
PS: Do you know of any example that would show how to work with two (three) tables in a many-to-many relationship with e.g. VB2005EE and SQLserver2005EE?
Creating A Fork In The Road
Jun 14, 2007Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?
Question the second: After I aggregate my records (down one of the paths), I need to store some columns as xml. Is there a tool for this?
Thanks for all your help!
Jim Work
Do You Know The Road To Dynamic Excel SSIS Goodness?
Dec 13, 2007Hi all,
I did a few searches but did not find this specific scenario. Can anyone state with confidence whether this is possible (and if so, how)? Scenario:
-One table with a couple million rows (one column indicates which Country the record belongs to)
-Need to create an Excel 2007 file dynamically (for each Country) using SSIS 2005. Filename should include the Country Name (Sweden_Affliliated.xlsx). I have a table that contains a distinct list of all the countries. Each worksheet will have the same structure / schema across all files.
What seems to be working:
**I understand how to use an Execute SQL task to get the list of Country Names and bind to an object variable.
**I understand how to set variable mappings for a String variable to contain the "current Country" in a ForEach Loop.
**I understand how to set the OLE DB Data Flow Source to use a SQL command from a String variable that has the CountryName dynamically embedded within it.
**I understand I need to convert my varchar to Unicode using a Data Conversion task in my scenario.
**I understand that in order to write to an Excel 2007 file I need to use an OLE DB Destination with an Extended Property value of "Excel 12.0" and the ServerName property should contain a path to the file with no quotation marks.
Problems I have:
**OLE DB Destination: How do I set up the mappings when the file does not exist yet?
What I want to avoid, is having to create a template source XLSX file and using a File Copy task (I have gone this route before, but it would be best if I did not require a template). Is there a way to configure the SSIS package without using a File Copy Task? Creating the Excel file on the fly?
Thanks for any assistance in advance,
Brian
Variable Assignment
Jul 21, 1999How do you assign the value returned by a stored procedure to a declared variable and then display the value of that declared variable?
The following does not work:
declare @variable int
set @variable = stored_procedure
...
...
show value of @variable?? <HOW>
Sorry for the stupid questions but I am new to SQL in general.
Need Urgent Help In Assignment Please!!
Apr 15, 2004I want to know the answer of this question please as i have to submit my assignment as soon as possible. The assignmet question is" We use Composite Index on a table. Which of the following statements will speed up and slow down the operations respectively:
1-Select 2-Insert 3-Modify 4-Delete "
So is there anyone who can help me in this? i would appreciate that! :)
Thanks
Variable Assignment
Apr 7, 2008Is it possible to set the value for several variables from one select query?
View 1 Replies View RelatedNeed Help With SQL Server Assignment!
Dec 6, 2007Hi everyone. This is my first time here, and I am really in need of some help! I am a senior Information Technology Major and I am in my first SQL course where I have to write a report on a company that uses SQL Server. I haven't been able to find any companies in my area, and need to find a company to write my paper on as soon as possible. I just have a set of brief questions; they are as follows (and I understand, for security reasons, if you are unable to answer certain questions, do not feel obligated to do so!):
Basics:
A brief overview of your company:
What is your position?
What the SQL Server database is used for?
Implementation:
Which version of MS SQL Server is used?
Type of instance(s) is(are) used?
Which authentication mode is used?
Do you utilize SQL Server Dynamic Disk Space Management, and if so, how?
How is data security maintained?
What type of encryption has been implemented?
Maintenance:
What type of backups are completed and how often?
How would the database would be restored in the event of data loss?
How do you preserve data integrity within the database?
Do you utilize any monitoring & troubleshooting tools to assess SQL Server performance (SQL Server Profiler, System Monitor, Database Engine Tuning Advisor, etc).
I greatly appreciate anyone willing to help me with my paper. Thank you!
Lynette
Variable Assignment
Apr 7, 2008Is it possible to set the value for several variables from one select query?
View 4 Replies View RelatedDynamic Database Assignment???
Jan 25, 2001I have an application that is developed to support a customer per database. All the data is unique to that customer and is physically partitioned from other customers. Also, I have a database that has common tables to all customers. I use stored procedures to access all data. I would like to keep from duplicating all the stored procedures (since the meat of them stays the same) because of the database references.
Is there any way to use the "USE <database>" functionality in the stored procedures to switch context dynamically without having to reference the unique databases?
Thank You,
Brian
Parameter Assignment In DTS SQL Task
Sep 13, 2006Greetings and salutations brilliant people!
I have a problem that I can't find any information on and I bet someone here has hit the wall on this.
I have 2 sql server 2000 servers runnng sql std edition. One has 8.00.2039 SP4 and one has 8.00.818 SP3. The server with SP4 will not let me use a parameter in the DTS SQL task. I get the infamous Access Violation Error. If I replicate the SQL task on the SP3 server I have no problem works great and life is good.
Can anyone tell me if thre is a hotfix for this or something? I've googled this to death and tried various possible fixes with no positive result. Any help would be greatly appreciated!!
Thanks for viewing! :)
Inline Variable Assignment
Jan 22, 2004I have to write a query for printing multiple barcodes, depending on the quantity of items that came in the store, based on the order number.
DECLARE @num INT
SELECT BarCodes.BarCode, BarCodes.ArticleID, ArticlesTrafic.DocumentID, ArticlesTrafic.TrafficQuantity
FROM BarCodes INNER JOIN
Articles ON BarCodes.ArticleID = Articles.ArticleID INNER JOIN
getAutoNumberTable(@num) ON @num=ArticlesTrafic.TrafficQuantity
WHERE (ArticlesTrafic.DocumentID = @Param2)
The thing i would like to do, is somehow assign a value to @num and pass it to the getAutoNumberTable stored procedure, which generates a table of consequtive numbers, so that each record is displayed multiple times. Is it even possible to do it without using temp tables and loops?
Variable Assignment In Cursor Declaration
Jul 5, 2006Hi,
here is the code segment below;
...
DECLARE find_dates CURSOR FOR
SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC'
EXEC (@SQL)
but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.
thanks
Local Variable Assignment In CREATE TRIGGER
Mar 7, 2006Hi Guys,
i'm batttling with the below Trigger creation
__________________________________________________ _
CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS
BEGIN
DECLARE @v_object_key VARCHAR(80)
DECLARE @v_object_name VARCHAR(40)
DECLARE @v_object_verb VARCHAR(40)
DECLARE @v_datetime DATETIME
SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!!
SET @v_object_name = 'FochiniTable'
SET @v_object_verb = 'Create'
SET @v_datetime = GETDATE()
IF ( USER <> 'webuser' )
INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment)
VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE')
END
________________________________________________
i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id
and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]
any help will be appreciated
lehare.
Help On Script Component Assignment Of Output Variable
Sep 7, 2005Hi all,
View 9 Replies View RelatedReporting Services: Role Assignment Without Using Report Manager
Jul 20, 2005Hello,I'm having some problems using the Report Manager, but I *really* needto make a role assignment now. Can't wait to have the problem withReport Manager solved.So, I would like to make this role assignment directly in theReportServer database, using the SP SetPolicy or some other SP.I'm looking for the equivalent of making the "New Role Assignment" andthenGroup or user name: EveryoneRole "Browser" checkedOKCan anyone tell me exactly how to do this?Thanks in advance,Filipe HenriquesPS: By the way, the problem I've got using the Report Manager is thatalways appear "The request failed with HTTP status 404: Not Found.",although I can see perfectly the Report Server page.Does anyone knows what's the problem?
View 3 Replies View RelatedThere Is Nothing Happen After Click Finish Checkout On My Shopping Cart Assignment
Mar 16, 2008Hi all,
There is nothing happen when I finished my checkout process, I expect the data will be saved to order and orderitem table in my SQL database, but no data found on order and orderitem table and no error messages display during operation!!!
Below is my checkout.aspx.vb code, the whole code line number around 138, I captured the part from 1~ 64 line number, I suspect line 35 - 48 have a problem, can somebody help me, many thanks.
1 Imports System
2 Imports System.Data.SqlClient
3 Imports SW.Commerce
4 Partial Class CheckOut
5 Inherits System.Web.UI.Page
6 Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
7 If Not Page.IsPostBack Then
8 If Profile.Cart Is Nothing Then
9 NoCartlabel.Visible = True
10 Wizard1.Visible = False
11 End If
12 If User.Identity.IsAuthenticated Then
13 Wizard1.ActiveStepIndex = 1
14 Else
15 Wizard1.ActiveStepIndex = 0
16 End If
17 End If
18 End Sub
19 Sub chkUseProfileAddress_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
20
21 ' fill the delivery address from the profile, but only if it’s empty
22 ' we don’t want to overwrite the values
23
24 If chkUseProfileAddress.Checked AndAlso txtName.Text.Trim() = "" Then
25 txtName.Text = Profile.Name
26 txtAddress.Text = Profile.Address
27 txtcity.Text = Profile.City
28 txtCountry.Text = Profile.Country
29 End If
30 End Sub
31 Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs)
32
33 ' Insert the order and order lines into the database
34
35 Dim conn As SqlConnection = Nothing
36 Dim trans As SqlTransaction = Nothing
37 Dim cmd As SqlCommand
38 Try
39 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("swshop").connectionstring)
40 conn.Open()
41 trans = conn.BeginTransaction
42 cmd = New SqlCommand()
43 cmd.Connection = conn
44 cmd.Transaction = trans
45
46 ' set the order details
47
48 cmd.CommandText = "INSERT INTO Order(MemberName, OrderDate, Name, Address1, Address2, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @city, @Country, @Total)"
49 cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50)
50 cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime)
51 cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50)
52 cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255)
53 cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 15)
54 cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50)
55 cmd.Parameters.Add("@Total", Data.SqlDbType.Money)
56 cmd.Parameters("@MemberName").Value = User.Identity.Name
57 cmd.Parameters("@OrderDate").Value = DateTime.Now()
58 cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text
59 cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text
60 cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text
61 cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text
62 cmd.Parameters("@Total").Value = Profile.Cart.Total
63 Dim OrderID As Integer
64 OrderID = Convert.ToInt32(cmd.ExecuteScalar())
Below is checkout.aspx1 <%@ Import Namespace ="System.Data.SqlClient"%>
2 <%@ Import Namespace ="SW.Commerce"%>
3 <%@ Page Language="VB" MasterPageFile="~/SWSHOP.master" AutoEventWireup="false" CodeFile="CheckOut.aspx.vb" Inherits="CheckOut" title="Untitled Page" %>
4
5 <%@ Register Src="SWShoppingCart.ascx" TagName="SWShoppingCart" TagPrefix="uc1" %>
6 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
7
8 <asp:Label id="NoCartlabel" runat="server" visible="false">
9 There are no items in your cart. Visit the shop to buy items.
10 </asp:Label>
11
12 <div style="float:right">
13 </div>
14
15
16 <asp:Wizard ID="Wizard1" runat="server" ActiveStepIndex="1" Width="274px">
17 <WizardSteps>
18 <asp:WizardStep runat="server" Title="Login">
19 <asp:Login ID="Login1" runat="server">
20 </asp:Login>
21 </asp:WizardStep>
22 <asp:WizardStep runat="server" Title="Delievery Address">
23 <asp:checkbox id="chkUseProfileAddress" runat="server" autopostback="True"
24 text="Use membership address"
25 OnCheckedChanged="chkUseProfileAddress_CheckedChanged"></asp:checkbox><br />
26 <table border=�0�>
27 <tr><td>Name</td><td><asp:textbox id="txtName" runat="server" /></td></tr>
28 <tr><td>Address</td><td><asp:textbox id="txtAddress" runat="server" /></td></tr>
29 <tr><td>City</td><td><asp:textbox id="txtcity" runat="server" /></td></tr>
30 <tr><td>
31 Country</td><td><asp:textbox id="txtCountry" runat="server" /></td></tr>
32 </table>
33 </asp:WizardStep>
34 <asp:WizardStep runat="server" Title="Payment">
35 <asp:DropDownList id="lstCardType" runat="server">
36 <asp:ListItem>MasterCard</asp:ListItem>
37 <asp:ListItem>Visa</asp:ListItem>
38 </asp:DropDownList>
39 <br />
40 Card Number: <asp:Textbox id="txtNumber" runat="server" Text="0123456789" ReadOnly="True"/>
41 <br />
42 Expires: <asp:textbox id="txtExpiresMonth" runat="server" columns="2" />
43 /
44 <asp:textbox id="txtExpiresYear" runat="server" columns="4" />
45 </asp:WizardStep>
46 <asp:WizardStep runat="server" Title="confirmation">
47 <uc1:SWShoppingCart ID="SWShoppingCart1" runat="server" />
48 <br />
49 <br />
50 Please confirm amount you wish to have deduct from your credit card.
51 </asp:WizardStep>
52 <asp:WizardStep runat="server" Title="Complete">
53 Thank you for your order.</asp:WizardStep>
54 </WizardSteps>
55 </asp:Wizard>
56 </asp:Content>
57 <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder3" Runat="Server">
58 <asp:LoginView ID="LoginView1" Runat="server">
59 <AnonymousTemplate>
60 <asp:passwordrecovery id="PasswordRecovery1" runat="server" />
61 </AnonymousTemplate>
62 </asp:LoginView>
63 </asp:Content>
Error 409: The Assignment Operator Operation Could Not Take A Text Data Type As An Argument
Aug 2, 2004How can I make it work when I need to pull out a field which is text type using a stored procedure? Please help!!!Thanks
I am getting the following error
Error 409: The assignment operator operation could not take a text data type as an argument
===========my sp=================================
CREATE PROCEDURE [dbo].[sp_SelectABC]
(@a varchar(50) output,
@b text output
)
AS
set nocount on
select @a=name, @b= description from ABC
GO
Parent Package Variable Assignment Issue In Child Package.
Dec 26, 2006
We
have one main package from which 7 other child packages are called. We are using
ParentPackage variables to assign values for variables and database connections.
While the values from ParentPackage variable get assigned to some of the
packages properly, to others it doesn€™t assign the value.
For example:
Except for one of the packages the database connection string gets assigned
properly to all other packages.
Similarly, in another package one of the
variables doesn€™t get assigned. In other packages it is assigned properly.
We
have checked all the other property values and they are exactly the same.
We cannot make any head or tail of this erratic behavior.
Please Help.
Pl/Sql Block?
Apr 8, 2008I've written a couple blocks but I have no idea when it comes to this one.
Create a PL/SQL block to retrieve the last name and department ID if each employee from the EMPLOYEES table
for those employees whose EMPLOYEE_ID is less than 114. From the values retreived from the employees table, populate two PL/SQL tables,
one to store the records of the employee last names and the other to store the records of their department IDs.
Using a loop, retreive the employee name information and salary infromation from the PL/SQL tables and doisplay it in the window,
using DBMS_OUTPUT.PUT_LINE. Display these details for the first 15 employees in the PL/SQL tables.
Any help helps
AES (128 Bit Block) : Can I Use It?
May 18, 2006If I've got a 64-bit OS such as Windows XP Professional x64 Edition running and I have the SQL Server 2005 Express Edition, is AES permittable or does TRIPLEDES still need to be used?
View 1 Replies View RelatedDatabase Block
Apr 10, 2008Hello,
I have been experiencing a lot of blocking activity in my database. My trace results show that the following extended stored procedures sp_prepexec and sp_cursorfetch are executed by both the user causing the block and the user tha is blocked. Any ideas as to how to rectify this situation will be appreciated.
Regards,
Albert
Try.. Catch Block
Dec 11, 2006HI,
i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin
begin try
insert into dbo.Test values (1,'aaa')
-- here we are inserting int value in identity field...
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Whereas the following block works fine and the Catch block executes.
Begin
begin try
Select 1/0
--This causes an error.
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Any idea why is it so?
How To Check Block
Mar 5, 2008Can anyone guide me to write check block in SQL Server?
I Mean if blocking occurs, just inform people.
Table Block
Mar 18, 2008Hi there! I need such thing: I have a data in Table A. When I get this data, I also increment this data by one, what I want is block Table A in order to other people will not get access to data.. I think about transactions, but are transactions reaaly blocks a table's content?
P.S. For example in MySQL there are Lock table command for blocking table for reading/writing. I need such thing/
Thanks
Process Block Itself
Jul 3, 2007Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
How Do We Use Block Cursor
Oct 25, 2007
hi there,
to retrieve more then one record with a cursor we can use block cursor, so we have to set the cursor attribute SQL_ATTR_ROWSET_SIZE to some number...i am not getting any code example on this.
do anyone know this???
Try Catch Block
Apr 12, 2006Hi:
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated.
I am unable to understand as to why the CATCH block is not executed when an INSERT is made
On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2
Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions:
Warnings or informational messages with a severity of 10 or lower.
Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb
go
create table t1 (a int)
create table t2 (b int)
create table t3 (c int)
Begin tran
Insert into t1 values (1)
Insert into t2 values (2)
Insert into t3 values (3)
IF @@error <> 0
Rollback tran
else
commit tran
-------------------------------------------------------------
Select * from t1
Select * from t2
Select * from t3
-------------------------------------------------------------
Drop table t3
-------------------------------------------------------------
Set xact_abort on
Begin try
Begin tran insertNow
Insert into t1 values (1)
Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3)
commit tran insertNow
End try
Begin Catch
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION insertNow
END;
-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION insertNow
END
End Catch
BLock Records Using UPDLOCK
Oct 2, 2006Hi all,
here my question :
I have 2 applications.
connection 1.
one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files.
After having did this, we add 1 at grp field.
we insert into orv the record max(grp) + 1
connection 2.
an other application could insert at the same time record in this table orv with same parameters.
my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2.
I believe I need to use HOLDLOCK, UPDLOCK.
but I have not the habitude to use them.
Can I do this ?
connection 1
select max(grp) from orv WITH HOLDLOCK
connection 2
what should I use to avoid lock when I need to insert into orv. ?
thanks for your quick answer
End A Blocking Process (Was Block)
Feb 11, 2008Ho do I clear a kill process blocking others to run
View 3 Replies View RelatedDo A Lot Of Linked Tables Cause Block?
May 18, 2004Hello, everyone:
There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks.
ZYT
Writer Block Reader, Reader Block Writer...
Sep 27, 2005hi,
i've performed a test on 2 machines based on the topic above, but it doesn't seem like blocking each other... This is the context...
I've created a table call TEST_DEL i.e.
quote:CREATE TABLE test_del
(v_id INT,
desc CHAR(3)
)
In machine 1, i'm login as USER 1 and try to insert a set of records into the table
quote:
Machine 1
~~~~
BEGIN
DECLARE @li_num int,
@li_start int
SET @li_num = 100000
SET @li_start = 1
WHILE @li_start < @li_num
BEGIN
INSERT INTO test_del VALUES (@li_start, 'zzz', 'xxx')
IF (@li_start > @li_num)
BREAK
ELSE
SET @li_start = @li_start + 1
CONTINUE
END
END
In machine 2, i login in as USER 2 to retrieve the records at the same time...
quote:
SELECT * FROM test_del
but, the system still allow me to retrieve the records at the same time... May i know when will "Writer block reader, reader block writer" occur and in what situation
Thanks in advance