|
Hello
I'm trying to use a query as a source for another query but I did'nt find the syntax to do it and I do not even know if it is possible
So I'm using a temporary table as below
Is it the right way ?
Is it another way to do it ?
CREATE TEMPORARY TABLE `tempo` <br />
SELECT DISTINCT<br />
`tours`.`dag`,<br />
`tours`.`addrID`,<br />
`tours`.`contID`,<br />
`tours`.`chauffeurID`,<br />
`tours`.`order1`<br />
FROM<br />
`tours`<br />
WHERE<br />
`tours`.`dag` = '2' AND<br />
`tours`.`chauffeurID` = '8'<br />
;<br />
SELECT DISTINCT<br />
`tours`.`dag`,<br />
`tours`.`contID`,<br />
`tours`.`chauffeurID`<br />
FROM<br />
`tempo`<br />
INNER JOIN tours ON `tempo`.`contID` = `tours`.`contID`
|
|
|
|
|
In place of a table name in the from clause a View or inline query can be used like this ....
SELECT *<br />
FROM<br />
(SELECT ColP, ColQ, ColR FROM TableA)a<br />
INNER JOIN<br />
(SELECT ColS, ColT, ColU FROM TableA)b<br />
ON a.ColP = b.ColS;
hope this is what you are trying for
Regards
KP
|
|
|
|
|
I have recently inherited a large set of databases, and have noticed that several of the dbo users have no login names associated with them. This limits several things (like assigning a database owner). Is there a way to set this value through a query, or do I have to recreate the database and import the data?
Thanks,
Leo T. Smith
Program/Analyst Supervisor
|
|
|
|
|
I've not get exactly what you are looking for...
however check whether sp_adduser and sp_addlogin solves your problem.
Regards
KP
|
|
|
|
|
I have an application which selects one record at a time from a SQL SERVER 7.0 database. After doing some business logic, it's updates the same record with an status flag change.
I need to run the application on 3 different machines concurrently. When I perform my SELECT of one row, how do I lock the selected record to prevent the same record being read by each instance of the application?
Thanks in advance.
Cheers
Adrian
|
|
|
|
|
I normally do something like this:
create procedure dbo.GetNextAvailable
as begin
declare @id int
set nocount on
--Start transaction so that locking works.
begin tran
--Get Id of next record to process. The "holdlock" hint tells SQL
--Server to place a lock on the row until the end of the transaction.
--The "readpast" hint tells SQL-Server to ignore a record if it is
--locked by one of your other two processes. Note that this only works
--for "read committed" isolation.
select top 1 @id = MyId from MyTable with (holdlock, readpast)
where status = 'READY'
order by MyId
--Mark the record as "in-progress" so that no-one else can pick-up
--the current record.
update MyTable set status = 'IN PROGRESS'
where MyId = @id
and status = 'READY'
--Complete the transaction.
commit trans
--Return the record to the front-end for processing. If no records
--returned then there are no records left to process.
select * from MyTable where MyId = @id
return(0)
end You could potentially move the transaction outside of the stored procedure. I normally update the status to "SUCCESS" or "FAILURE" after I have completed processing of the record. If the record processing is substantial then I normally also maintain start and end times for each record.
Regards
Andy
|
|
|
|
|
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...
|
|
|
|