|
Using Microsoft SQL 2005 Express I need to be able to prevent the same stored procedure from running more than once at the same time so that if the stored procedure is in progress it does not run again.
Although nothing will break if this occurs the second run will be redundant. I would like to ensure that any unnecessary work performed by the SQL Server is prevented. The Stored Procedure in question performs some housekeeping functionality that has a non-deterministic duration.
I would like to do this all within SQL 2005 Express.
Any help is appreciated. Thank you in advance for your time.
Ant.
I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!
- David Walliams (Little Britain)
|
|
|
|
|
Hi,
You could use locking for this. Create a small table and insert a single row in it. In the beginning of the procedure, update the row in the table to acquire lock and in the end of the procedure commit your changes. For example:
CREATE TABLE ProcedureRun (LastRun datetime);
INSERT INTO ProcedureRun (LastRun) VALUES (NULL);
CREATE PROCEDURE SomeProcedure AS
BEGIN
UPDATE ProcedureRun SET LastRun = GETDATE();
... do some stuff...
COMMIT;
END;
Hope this helps,
Mika
|
|
|
|
|
Thank you Mika, I have decided to take your advise. The concept of creating a "control table" seems a good one.
Ant.
I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!
- David Walliams (Little Britain)
|
|
|
|
|
You're welcome 
Mika
|
|
|
|
|
hi all,
To extract SQLSERVER.exe, we run SQLEXPR /X from the command line which prompt the user to specify the Loaction where he want to extract the files.but i want to extract this file programmatically. so how can perform this task in vb.net ? I know shell command but i want to skip the Step, in which it asks from user for location of the extract files.
|
|
|
|
|
hi all,
I want to store SQL server Express programmaticaly. so first i want to check whether the particular instance is already installed or not. How can i perform this task ?
|
|
|
|
|
I have a telephone call record billing application, with a table CallRecords containing the calls. The column BillItemID is set when the call is "billed" to a bill detail table: the BillItems table.
All good so far, and I wrote and update and delete trigger on CallRecords so that they could not be modified once the BillItemID was set - this prevents some bad application code modifying the data which underlies the bill.
Problem is I now have the requirement from the business to rerate a bill if the wrong rates were applied, and to allow an itemised report on this. The practical upshot is I have to credit the original bill, rerate and reassign the call records to a new BillItem and rebill them. Of course the trigger prevents this, and for now I have some code that disables the trigger, modifies the call Records and re-enables the trigger - which is of course horrible and not a good idea.
What I need to figure out is some way of reassigning and updating the call records while retaining the effect of the trigger - and I'd like some ideas from out there!
Howard
'Howard
|
|
|
|
|
You may want to approach this like a checkbook. Meaning, store a series of transactions where you add the numbers up to determine the current balance. The important thing to remember is that you need 2 dates on the transaction; Posted Date and Effective Date. The Posted date is the date that the transaction was entered into the system and the Effective Date is the date that the transaction should be considered. So you may have received money in your bank account on Monday but your data entry clerk didn't enter it into the system until Thursday. You can then generate queries that show the balance "as of" a given date. This is a classic situation. This will also allow you to have a history of the transactions.
Give it some thought.
David
|
|
|
|
|
Thanks for the reply - I do have all that billing transaction/integrity logic on the system - the bill items are assigned to a Bill, the bill has a bill date when it's posted etc. The triggers for that level don't affect the CallRecords table.
In this instance the original Bill and the BillItems would remain, but the CallRecords that originally underlied the BillItem need to be moved out.
I was trying to figure out a way to write the CallRecords trigger thus:
if (update CallRecords)
where (change is a valid move)
permit
else
rollback
The problem is how does a trigger determine this is a valid update (e.g. to move the call records to a new billitem), as opposed to an invalid one.
I think that the case here is that we can modify the call record if we are ALSO modifying the BillItemID to a new value - this is permitted.
'Howard
|
|
|
|
|
Hi,
If I understood your question correctly, you want to allow or prevent modifications on a row with BillItemID based on the values with earlier situation and new situation. If this is correct you should fetch old situation from table name deleted and new situation from table name inserted in update trigger. After that make relevant comparison for values and decide if the change allowed. These virtual tables are automatically managed by SQL Server.
Mika
|
|
|
|
|
Hi,
I'm trying to find some software that will simulate a SQL server on my computer so I can test an application.
Does Microsoft SQL allow you to do this?
Any recommendations on a good (and easy to use) package?
Cheers,
|
|
|
|
|
|
or you can get the developer version of SQL Server, it is a full version that only allows 5 connections.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi all,
i installed SQL Server in silent mode as following
str = "C:\SQLEXPRFiles\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=Instance1 SECURITYMODE=SQL SAPWD=password DISABLENETWORKPROTOCOLS=0 "<br />
<br />
Shell(str, AppWinStyle.Hide, True)
After that i want to attach database to this server as following
Dim ComputerName As String = My.Computer.Name<br />
<br />
Dim MainServer As Server = New Server(ComputerName & "\Instance1")<br />
With MainServer.ConnectionContext<br />
.LoginSecure = False<br />
.Login = "sa"<br />
.Password = password <br />
.Connect()<br />
If MainServer.Databases.Contains(mydatabase) Then<br />
Else<br />
Dim logstr As String<br />
Dim datastr As String<br />
datastr = Application.StartupPath & "\mydatabase.mdf"<br />
logstr = Application.StartupPath & "\mydatabase_log.ldf"<br />
'Attach the database<br />
Dim sc As StringCollection<br />
sc = New StringCollection<br />
sc.Add(datastr)<br />
sc.Add(logstr)<br />
Try<br />
MainServer.AttachDatabase(mydatabase, sc, ComputerName & "\Owner")<br />
MsgBox("Database Attached")<br />
Catch ex As Exception<br />
MsgBox(ex.Message.ToString)<br />
End Try<br />
End If<br />
.Disconnect()<br />
.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled<br />
<br />
End With
but MainServer.AttachDatabase statement give error on VISTA but successfully run on XP . Inner exception is {"An exception occurred while executing a Transact-SQL statement or batch."}
any body have idea where i am wrong ?
modified on Friday, August 8, 2008 5:07 AM
|
|
|
|
|
Hi all,
Am running a open row set query in my "sql server management studio express".
Am getting an error "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".".
I think the open row set stored procedure. will work only in 32 bit.
So,
I want to know whether
1)How cam i know my "SQL server ' is 32bit or 64bit?
2)My OS is Windows xp in my computer?Could that be the reason?
3)How can i know whther Windows Xp is 32 bit or 64bit?
Any infprmation will be helpful
Thanking You
|
|
|
|
|
new2pgrmg wrote: Am getting an error "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".".
Your error is something else. Your linked server configuration has some problem. Does it have data transfer access?
new2pgrmg wrote: 1)How cam i know my "SQL server ' is 32bit or 64bit?
In management studio, execute @@Version, this will tell you if SQL Server is 32 bit or 64 bit.
|
|
|
|
|
hi all,
the following statement Installed the SQL Server with SQL AUthentication mode with USER 'SA' when we run this command in command promopt
C:\SQLEXPRFiles\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=myInstance SECURITYMODE=SQL SAPWD=mypassword DISABLENETWORKPROTOCOLS=0
but i want to create A different login(instead of 'sa') so what Parameter is required with above statement.
|
|
|
|
|
My friends & I have a problem with our one of the table of our SQL Data...
One of our Table Column shows like this when we view it in sql :
ChaInvent
<binary>
We dont know how to read & edit inside that CharInvent..??
That ChaInvent has all the information we need because that is place where are all the Inventory on the Game of the Character stored like weapons, armors, potion, ..etc..haysss..Any Help here?
Are there any script or program that can read & interpret those data readable by Human?? I tried to search all the Info here but NO LUCK...
|
|
|
|
|
you always get binary data when running a select.
It depends on the client how to process it, mostly it becomes a array of bytes.
You can show it by storing it to file or convert it to strings to show it in a memo or listbox.
You probably used the sql studio manager for viewing data,
this is not showing you binary data.
greetz
kurt
|
|
|
|
|
...."You can show it by storing it to file or convert it to strings to show it in a memo or listbox"...
How?? we don't know, Please Help us...
|
|
|
|
|
Or you can write a program in you're language. for example C#
or build a query like this..
EXEC master.dbo.sp_configure 'show advanced options',1 RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE
declare @query varchar(500)
set @query = 'BCP "SELECT * FROM YourTable" queryout "C:\YourTable.txt" -c -T -C850 -S(local)\SQLEXPRESS -Usa -Psa'
EXEC master..xp_cmdshell @query
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE
Be sure you're sql server has rights to write on the HD. (set local system account of you're SQL service)
greetz
Kurt
|
|
|
|
|
I heard that is not possible to disable database attach. Is that accurate?
Better way is to encrypt data. But ... encrypt is about string isn´t it? how can i encript other types of data? if it´s an int the database structure won´t accept other type, do you copy me?
id|f_name|l_name|age|
11 john silva 29
24 glen berger 45
39 dan smith 34
40 luiz owen 41
id|f_name|l_name|age|
d22wfd kfvjff fgvfrer feef
5rfrfb 37bfjkr r43hifi85 kfnrk3ifn
rrui8 r44r 4rh4i ni43i
What do you think?
nelsonpaixao@yahoo.com.br
|
|
|
|
|
nelsonpaixao wrote: What do you think?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
i want to retrieve creationDate and time from a file on the server HD
by using a query.
anyone any idea how i can do this?
thx
Kurt
|
|
|
|
|
Hi,
First thing that comes in mind is to create a small assembly using c#. Implement a method to extract file info you need and then install and register it to SQL Server. After that you can use this custom function in t-sql.
Mika
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
