|
Thanks Andy, that looked like a good solution. However due to a Microsoft bug, I can't use the HOLDLOCK keyword with READPAST as I kept getting the following message: "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels."
The bug is confirmed in this KB article: http://support.microsoft.com/kb/171322[^]
|
|
|
|
|
Your error message infers that your isolation level is not "committed read" (hence my warning about this in my original response). The MS article infers that you are trying to set the isolation-level in the "GetNext" SP, after the transaction has been started. If you are using ADO then the connection's "IsolationLevel" property should be set to "adXactReadCommitted" before the transaction starts.
You need to be really careful about the isolation level and the scope of your transaction otherwise you will find that your separate processes will have to wait for each over to complete - if you do it really wrong then you could find that parallel processing actually takes longer than using just a single process.
The technique that I specified should work even if you don't use the "READPAST" hist specified (so long as you don't start a transaction in your client-side code until AFTER you have called my SP.
Hope that helps.
Andy
|
|
|
|
|
is it possible ,on every 00 hrs a stored procedure or trigger fire automatically to update certain values of atable automatically.
|
|
|
|
|
This is normally solved by a stored procedure that is executed from a scheduler daily at 00hs.
Habetis bona deum
|
|
|
|
|
but i am building a website, how can i do there.
|
|
|
|
|
The schedule runs in the database server, so the type of application does not matter.
For example if you database is MS SQL Server 2000 on Windows Server you create the stored procedure in the database.
Then go to Enterprise Manager->[You SQL Server Group]->[You Database]->Administration->SQL Server Agent->Jobs
Right Click -> add New Job: Enter a name
go to tab steps-> New: Enter a name
Select -> Transact SQL Command (or something like)
Fill in the sp call
Go to the next tab
Click New Schedule
Click Change
Select daily
Set the desired time
Accept all dialogs
Hope it helps
Habetis bona deum
|
|
|
|
|
Hi
One of my clients is running a program with an MS Access database - when logged in as administrator, it seems to work fine; but as any other user, he is unable to run the application... The folder where the database is has full rights to "Everyone". I don't know if this is a permissions issue - haven't worked all that much on Access. Can anybody point me towards a solution to this?
I am searching on Google too, btw. 
Thanks in advance
Chandra
|
|
|
|
|
What is the exact error message you are getting.
Also check whether you are able to open the database from MS Access by logging as the user from which you are trying to run the application
Regards
KP
|
|
|
|
|
Yes, he is able to open the database from outside the application.
Thank you
Chandra
|
|
|
|
|
Could somebody please enlighten me as to how I can create a login for my laptop user (ABRAXAS\Brady) on my dekstop SQL Server, THELEMA? I can't see ABRAXAS from THELEMA at all in the SQL Login dialogue, but I can see shares on it in explorer.
modified on Monday, February 25, 2008 8:06 AM
|
|
|
|
|
Brady,
Unless both systems are part of the same domain, you would not see your laptop on the desktop's SQL Enterprise Mananger.
You could simply create a SQL Login as opposed to a Windows Authentication login. You may need to enable this on the deskop SQL Server if SQL Logins are turned off.
You can also use what is called a mirrord account. If the laptop has a user named Brady, and the desktop has a user named Brady, and they both has the "same" password, SQL Server will allow the laptop access to the same SQL Server databases that the deskop Brady user has.
modified 27-Feb-21 21:01pm.
|
|
|
|
|
|
ABRAXAS? Bit of a Santana fan there Brad?
C# has already designed away most of the tedium of C++.
|
|
|
|
|
Yes and no. I am a literally a bit of a Santana fan, but small enough of a bit to not know Abraxas through him. I used the name in its mythical context.
|
|
|
|
|
Hi All,
Any body help me to clear my doubt.
I have stored a string value in SQL Server2005 using varbinary datatype. I cant get the that string value from SQL Server. How can i get that varbinary in C# application and convert that to string?
That value stored in SQLServer2005 Table
Table Name:tblName
Fields: male CHAR(1),pg CHAR(1),status varbinary(10)
INSERT INTO tblName VALUES('Y','Y',convert(varbinary(8),'Office'))
I want status field value("Office") in my WebForm using C# code
Please help me
Thanks a lot advance
Regards,
R.Tamizh
tamizhms@gmail.com
|
|
|
|
|
This article[^] should provide the solution to your question.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
can i use the table datatype in stored procedure sql server?
actually i have few rows in the gridview asp.net 2.0 , i want those rows data in the stored procedure.
is there any table like datatype available?
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Sonia Gupta wrote: is there any table like datatype available?
Yes, table variables are available.
Declare @table_name Table(column_name1 datatype,column_name2 datatype)
Ref: Table[^]
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Hi,
I am creating a procedure in which I want to check that if a database is already exists then drop the database and create the new database and if the database is not exist then just create the database.
How can I do this.
Plz help me....
Thanks in Advance
Alok...
|
|
|
|
|
You can check database existence like this:
if exists (select * from sys.databases where name = 'demo')
// database exists, drop it and create new
else
// database doesn't exist so just create it
|
|
|
|
|
|
You are welcome
|
|
|
|
|
I am attempting to get a result set from a query and it is not how I expected. (Go figure!)
Tables Structure:
Production: Date, WOID (FK)
WorkOrder: WOID (PK), ProdID (FK)
Product: ProdID (PK), PCatID (FK)
Category: PCatID (PK), Category
FormTrans: Date, WOID (FK), Kgs
I want to return results in the following Table structure:
Date, CategoryA, CategoryC, CategoryC,..., CategoryN, Date Kgs, Ave, Running Total
I have tried the following:
DECLARE @Start DateTime,
@End DateTime
SET @Start = '01/01/2007'
SET @End = '12/31/2007'
Select Distinct(pr.[Date],
Case When pc.Category IN ('Mini','Maxi','OSCS')
Then SUM(ft.Kgs)
Else 0
End As 'DUCT',
Case When pc.Category IN ('DSPETG','DSPVC')
Then SUM(ft.Kgs)
ELSE 0
END AS 'DS",
SUM(ft.Kgs) As 'Kgs' -- Date Kgs
-- Average for distinct date required here?
-- Running Total for Dates here?
FROM Production pr
JOIN WorkOrder wo
ON pr.WOID = wo.WOID
JOIN Product p
ON wo.ProdID = p.ProdID
JOIN ProdCategory pc
ON p.PCatID = pc.PCatID
JOIN Formula f
ON wo.FormulaID = f.FormulaID
JOIN FormTrans ft
ON ft.FormulaID = f.FormulaID
WHERE pr.[Date] BETWEEN @Start AND @End
GROUP BY pr.[Date],pc.Category
ORDER BY pr.Date
1, When I run this I am getting data but a single line for each category calculation per date and not a single date row for each date with multiple results on the same row.
2, How to implement the average, and running total columns?
I hope I have explained myself clearly.  
Any help is most appreciated.
Andy.
I don't speak Idiot - please talk slowly and clearly
modified on Monday, February 25, 2008 1:29 AM
|
|
|
|
|
I don't have enough information to help you - I would need to understand the relationships between tables - however one hint I will give is that I notice that there are no right/left outer joins.
I have rarely seen queries with so many inner joins and none of them being left/right outer joins.
By using inner joins you are restricting your data set - are you sure you want to do this?
One suggestion:
Rewrite the query one join at a time - this way you can check at each join if the results are what you expect.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Thanks Guy.
I will play around with the Join clauses - I am not too flash on Outer Joins (as you could probable tell)
By the way, All tables are related via Keys.
Ie: WorkOrder PK is WOID, Category PK is PCatID etc...
Where these are found in other tables, they are foreign keys.
Cheers
Andy
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
