Confused About Using Lock Hints?
Mar 19, 2006
I noticed that the online books say the following:
Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary.
Also, at another place in online books, it says:
The table hints are ignored if the table is not accessed by the query plan.
From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?
View 24 Replies
ADVERTISEMENT
Dec 3, 2007
Do you know of a way, in the OLE DB Command (Executing a sql command for each record), to specify a lock hint?
Example: in my component, I want to update a table but I want to specify a ROWLOCK or UPDLOCK hint.
When I try using this sql statement in the 'SqlCommand' property:
UPDATE DIM_Accounts WITH (UPDLOCK)
SET COL1 = ''
SSIS gives a syntax error.
Do you know any workarounds?
Thanks!
View 7 Replies
View Related
Feb 2, 2007
I 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
View 11 Replies
View Related
Apr 7, 2004
Hi
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
View 1 Replies
View Related
Jun 16, 2004
Is there anything equivalent available in SQL Server for Oracle HINTS ?
eg : Oracle query
select /* + INdex(sno index1) */ sno from test_table
Thanks,
Sam
View 2 Replies
View Related
Jul 23, 2005
I am kind of confused about the way SQL Server 2000 handles the hintsthat users supply with their SQL statements.[color=blue]>From BOL, it seems that one can specify them with "WITH (...)" clauses[/color]in SQL statements known as table hints. Sometimes, multiple uses ofthis form in a statement is OK. Then there is the OPTION clause forspecifying statement hints. However, the documentation on OPTIONsection discourages their use.Being relatively new to SQL Server and still learning about it, what isthe general practice? Use hints or not? And if so, how (through WITHor OPTION clauses)?Cheers!
View 5 Replies
View Related
Sep 12, 2005
My experience with query hints are that they are just that, a hint. What I don't understand is when does SQL decide to ignore your hint?
View 5 Replies
View Related
May 15, 2001
I am running SQL7 SP2 and and noticing table the query processor table scans when I ussue a between 'date1' and 'date2' instead of using the datetime index. If I put in the index hint (index = ix_datetimeXXXX) the query runs fine. My question is does this index hint restict the use of other indexes in the query and secondly how can I specify multiple index hints? Thanks in advance.
View 1 Replies
View Related
Sep 29, 2003
Please advise.
Whilst running a query I recieved the error below.
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.
What is ROBUST PLAN hint?.
Help Appreciated.
View 6 Replies
View Related
May 1, 2007
Hi
My VB.Net (with SQL Server backend) application currently allows more than one user to look at a particular record at the same time. This is not a problem unless both those users also try to update that record as well. One user's changes then overwrite the other's.
I've been reading up on locking hints but my database knowledge is a little scant and I'm also rather dense and need things spelling out for me!! So I have a few questions that I hope someone can help with:
If I add an updlock to my update SQL statement, this would allow both users to view the record but would only allow one user's changes through. Is that correct?
For the other user, would SQL Server return an error message that I can use to tell the user that their update has not worked?
Would I have to get my VB.NET application to re-get the record information so that the user who's update failed can see the changes made by the other user and reapply their own changes?
Does the updlock become unlocked once the record is updated or do I need to specifically unlock it somehow?
Thanks!
View 20 Replies
View Related
Apr 20, 2007
I am having problems with doing what seams to be a very easy query. For some reason the SQL Server is trying to do nested loops instead of hash join. I tried to force the use of the hash join using the join hint.
Query 1
select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, getdate()), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
This query gives an error:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN
Second query is not really what I want but it illustrate that it works fine when getdate() is not used.
Query 2
select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, A.date_enrolled), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
Is there some problem with using function getdate() ? It works fine in SQL Server 2000
This problem occurs on the SQL Server 2005 SP2 ( 9.00.3050.00 (X64) )
and (9.00.2050)
View 3 Replies
View Related
Sep 9, 1998
Hi,
Why SQL server dose work as follows when I dose not provide any join hints?
It looks like HASH join is the best plan, but SQL dose not. What kind of
JOIN method is used by SQL optimizer?
Thanks in advance,
Wonhyuk William Chung
wonhyukc@usa.net
MCSE/ MCT
-----------
use northwind
go
select orderid, CompanyName --productname,
from orders o inner join customers c on o.customerID = c.CustomerID
/*
Table `Orders`. Scan count 91, logical reads 184, physical reads 0,
read-ahead reads 0.
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
.0553
*/
select orderid, CompanyName --productname,
from orders o inner hash join customers c on o.customerID = c.CustomerID
/*
hash
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/
select orderid, CompanyName --productname,
from orders o inner merge join customers c on o.customerID = c.CustomerID
/*
merge
Table `Customers`. Scan count 1, logical reads 4, physical reads 1,
read-ahead reads 3.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/
select orderid, CompanyName --productname,
from orders o inner loop join customers c on o.customerID = c.CustomerID
/*
loop
Table `Customers`. Scan count 830, logical reads 1681, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 5, physical reads 0, read-ahead
reads 0.
.116
*/
View 1 Replies
View Related
Sep 3, 1999
I need to use two hints (INDEX=indexname) and (NOLOCK).
I've tried
(INDEX=indexname),(NOLOCK)
(INDEX=indexname,NOLOCK)
(INDEX=indexname)(NOLOCK)
and nothing works.
Thanks
View 1 Replies
View Related
Aug 5, 1999
While investigating performance problems within an application recently I carried out some tests using SET SHOWPLAN ON.
I had a query like this within a stored procedure:
SELECT MAX(X) FROM Y WHERE Z LIKE @MYVAR
Where @MYVAR was passed in. I discovered that SQL Server did a Table Scan even when Z had an index on it. A problem with 200,000 rows!
If I said
SELECT MAX(X) FROM Y WHERE Z LIKE 'HELLO%'
(i.e., used a constant instead of a variable) SQL Server did use the index correctly and did not do a table scan.
I got around this by rewriting my statement:
SELECT MAX(X) FROM Y (INDEX=MYINDEX) WHERE Z LIKE @MYVAR
in other words by manually specifying the index I had created on the Z column.
Hope this helps someone.
View 2 Replies
View Related
Feb 9, 2008
hi all,
I'm trying to run queries on relatively small tables (a few hundred thousand rows) with subqueries of counts per primary key columns as such:
(ColA in tableA is the primary key)
select * from tableA p
where exists (select 1 from ( select ColA, count(1) cnt
from TableA
group by ColA
having count(1)>1 ) t
where t.ColA= p.ColA)
order by some_col
my problem is that sqlserver 2005 sp5 does not materialize the internal subquery properly, or execute it beforehand and it gets confused as heck and pegs the CPUs at 100% forever.
What hints can I use to solve this issue?
I've tried to use ..... "with ...." to prepare/materialize the table upfront, no luck, one version of statement pegged one cpu at 100%, while the other statement pegged ALL cpu's at 100% -- don't remember which.
My only solution right now was to create these subqueries as PHYSICAL tables -- and this would solve the problem but that would entail creating a lot of un-necessary objects.
thanks much for any feedback!!
Cos
View 5 Replies
View Related
May 22, 2006
Hi,
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.
View 9 Replies
View Related
Jul 23, 2001
Hello.
There is a trace flag that tells SQL Server to ignore index hinting in incoming queries. I'm having a Monday morning problem and I can't remember the trace number nor find it in my notes. Can anyone else come up with it?
Thanks in advance,
-darin
View 1 Replies
View Related
Aug 18, 2006
have a dts package that does txt -> sql server.
i have 200 txt files with the same exact format.
just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.
say:
exec SP_loadTXT txt1
or should i use bulk insert?
any approaches are fine. any suggestions are fine too.
View 14 Replies
View Related
Dec 3, 2007
I've got a SELECT WITH (UPDLOCK, ROWLOCK) WHERE followed by an UPDATE WHERE statement. The results of the SELECT statement are deserialized in C# and updates are made to the deserialized object. Then the object is serialized back into the table with the UPDATE statement. I've got this code running within a transaction scope with the ReadCommited isolation level.
My service receives requests to update data and the requests can come in on different threads. What I'm seeing, is that once in a while, the log messages from my application indicate that two different threads are able to issue the above SELECT statement and both are receiving results. This is a problem since the thread that issues the last UPDATE will overwrite the changes made by the first. Each thread has its own connection and transaction scope.
I've researched all over the place and have tried a few different things, but all things point to the fact that query hints are just hints and that SQL may or may not pay attention to them. If that's the case, how am I suppose to perform a SELECT with the intention of updating so that no one else can do the same? I haven't tried table level locking, but I'd really like to avoid that if possible.
-Mike
View 4 Replies
View Related
May 10, 2006
Hi 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".
View 1 Replies
View Related
Jul 14, 2006
Hi,
Using Merge replication, I have a table that is filtered using the HOST_NAME() function. The filter also makes use of a function (as the HOST_NAME() is overriden to return some complex data).
Everything replicates and filters just fine. but when I add a join filter on a different table (the join filter is a simple foreign key join) I get the following error when the snapshot agent is run:
Message: Conflicting locking hints are specified for table "fn_GetIDList". This may be caused by a conflicting hint specified for a view.
Command Text: sp_MSsetup_partition_groups
Parameters: @publication = test1
fn_GetIDList is the function used in the original filter.
Thanks for any help
Graham
View 6 Replies
View Related
Aug 23, 2007
I am trying to use hints and they don't seem to be working.
Something like:
select * from TABLE_NAME with (tablock,xlock)
fails.
Documentation clearly states that this is supported (TABLOCK, NOLOCK, XLOCK, etc.), so I must be missing something simple.
Please let me know how do I use Hints in SQL Server 2005 Compact.
Thanks.
PS.
I am using C#. It doesn't work in my code when I use
SqlCeDataReader rs = cmd.ExecuteReader();
And it doesn't work from inside the VS 2005 "query tool" or whatever it is called, when you do "Connect to Database" and so on.
View 1 Replies
View Related
Jul 25, 2006
Hi,
I would like to implement a kind of standard packages which can be used in all other processes and will be started using the variables.
But I do not know where to store these kind of packages in "best practise", because we
- would like to use them in Dev and in "Real" also without having to change something in the other processes
- we are storing the packages in the folders of the package store
and as far as I understood I would have to share the package store to all developers though that they would be able to do this?
Then I would better choose another folder with defined access rights I think...
Or would it be better to spend some time in developing a custom component?
But this component would work with recordsets rather than the standard data flow elemtents and therefor I would expect a leak of performance...
Or is it possible to do "trasnformation" from a packae to a custom component?
Thanks in advice!
cheers,
Markus
View 2 Replies
View Related
Apr 25, 2008
This article instructed me on how to process rows from a table used as a data queue for multiple processes.
http://www.mssqltips.com/tip.asp?tip=1257
I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio).
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
COMMIT TRANSACTION
This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue. However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want)
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --is blocked until connection 1 commits transaction
COMMIT TRANSACTION
How do I prevent blocking when using these locking hints with ORDER BY?
thanks
View 6 Replies
View Related
Oct 26, 2006
Greetings,
I have two tables:
CustomerOrder
----
ID
CustomerID
StatusID
CustomerOrderDetail
----
ID
Order_ID
StockID
Quantity
CustomerOrderDetail table has clustered unique index for ID and non-clustered for Order_ID
SQL Server 2005 is using table scan for CustomerOrderDetail table When I user the following query:
select
cod.*
from CustomerOrder co
inner join CustomerOrderDetail cod ON cod.Order_ID = co.ID
where
co.StatusID = 8 -- Pending
Both of the tables are pretty big, detail table has more than million records, so scanning the table is a bad idea.
When I specify hint to use index then sql seeks, but how do I make SQL server to use index automatically? I don't want to use hints in my queries.
Thanks!
View 4 Replies
View Related
May 23, 2001
Hi,
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
View 1 Replies
View Related
Sep 13, 2004
Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
View 2 Replies
View Related
Jan 4, 2007
Hi,
I will give someone a script that creates a database using :
create database mydatabase
my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737
go
use mosaikDB737
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileListInput](
[FileName] [char](50) NULL
) ON [PRIMARY]
END
use master
select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb')
select * from mosaikDB737.dbo.DBlistOutput
View 5 Replies
View Related
Dec 15, 2007
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems
FROM Table1 Inner join on table1.assettag = table2.assettag
where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed
thank you for any information or help.
View 3 Replies
View Related
Mar 5, 2008
Hi,
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
mySqlConnection.Close()
End If
End Try
End Sub
View 1 Replies
View Related
Aug 29, 2007
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
View 3 Replies
View Related
May 30, 2006
I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
View 3 Replies
View Related
Mar 13, 2000
Hello,
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is
successful, my return value is coming back correctly (to whatever i set it)....but if there is an error
such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP.
It comes back blank. (The literature I've read says that processing should continue in the SP, so you
can perform error processing...is that right?)
I set the return var in my ASP as:
objCommand.Parameters.Append objCommand.CreateParameter("return",_
adInteger,adParamReturnValue,4)
and read it back as:
strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah
if @@error = 0
return(100)
else
return(200)
(Idon't ever get back "200")
Any ideas???
Thanks for your help.
View 1 Replies
View Related
