|
Let's see if I can explain myself  I have an Employees table with PK ID_employee. Then I have a Zones table with fields ID_zone, Description, and ID_employee (That's the employee that has been assigned that zone.)
I have another table, Receipts, where I have a list of receipts to collect for the week, with fields ID_receipt, ID_week (PK key into another table), ID_zone, ID_employee.
The idea is that before printing the receipts, I can change the employee assigned to the zone, and have it automatically changed for every receipt. So, after updating my Zones table, I can execute an UPDATE like this:
<code>DECLARE @@ID_employee int;
SELECT @@ID_employee = ID_employee FROM Zones WHERE ID_zone=@ID_zone;
UPDATE Receipts SET ID_employee=@@ID_employee WHERE ID_zone=@ID_zone AND ID_week=@ID_week; (I know I could put both lines into one with something like a UPDATE Receipts SET ID_employee=(SELECT ID_employee FROM Zones... but for readability I left it as two queries.)
My question is how to automatically excute this UPDATE for all the record in Zones that match a certain criteria (specifically, that have not been marked as inactive -- another column.)
[EDIT: I just remembered about WHERE @ID_zone IN (SELECT ...) but in this case that could be added to the WHERE clause of the UPDATE, but how do I get the employee ID for the SET clause??]
Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
It looks like you are wanting to iterrate over these two queries until all the updates are done. Is that right? If so, then you can use cursors for that. However, as you have already pointed out a "set based" (as in the mathematical concept) way of doing it you should use that way because that is how SQL Server runs most efficiently. A database system is designed to run set based queries very efficiently, let it do what it is good at.
Your efficient code doesn't look unreadable too me - SQL is a different language. German looks unreadable to me. Spanish looks oddly structured to me, but with some effort I can understand it. English appears to me to be the language everyone should speak because it is so easy to understand. However, I would guess your perspective on that is completely different. It is the same with set based languages (like SQL) Vs. Procedural languages (like C#/VB.NET). If I want to communicate effectively to a database I use a set based language. If I want to communicate effectively to an operating system I use a procedural language and I don't try to coax one style on to the other.
I hope this helps.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
It looks like you are wanting to iterrate over these two queries until all the updates are done.
Basically, what I want to do is this (in a C#-like pseudocode):
foreach(Zone zone in Zones)
{
UPDATE Receipts SET ID_employee = zone.Employee
WHERE Receipts.ID_zone = zone.ID;
}In my edition to the other post, I pointed that I could do something like:
UPDATE Receipts SET ID_employee=????? WHERE ID_zone IN (SELECT ID_zone FROM Zones WHERE Active=1) My only problem is how to get the ID_employee from the current Zones record.
You mention cursos, but I've never used them before (I don't even know what they are). I'll look them up in Google in the meantime.
Thanks for your reply,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis Alonso Ramos wrote:
You mention cursos, but I've never used them before (I don't even know what they are).
Don't even learn - They are an abominable evil. Actually, they have their uses, but if you prefer the procedural style of programming then it is too easy to use a cursor. Cursors are slow and clunky. I re-wrote some code a little while ago that replaced a cursor with a set based update. With the cursor it took 20 minutes. With the set based update it took 7 seconds. That is incredibly inefficient. For a handful of rows you might not spot the difference (either way is sub-second), but once a system is in productiona and the customer is hurling a many rows more rows at the server then it starts to make a lot of difference.
I think this should work:
UPDATE Receipts
SET ID_employee=zone.Employee
FROM Receipts, Zones
WHERE Receipts.ID_zone = Zones.ID
AND Zones.Active = 1
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Cool!! it worked! 
Thanks!! I guess this is the reason of those "Colin please help me!" posts: you know a lot and always are willing to share! Thank you very much, really!
And talking about cursors, it they are the devil, why were they invented then? I suppose there is a situation where they are indeed the best choice.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I'm glad it worked out.
Luis Alonso Ramos wrote:
And talking about cursors, it they are the devil, why were they invented then?
There are situations when they are the only solution. However, it is too easy to fall into the trap of using cursors when a set based query would be better.
I remember when I first learned about them and I started thinking that it was easier to think about the problem using cursors (since they are just like for loops but in SQL it is easy to think about how to filter rows one at a time in the loop) than proper SQL set based queries. Set based queries are harder - It takes me longer to write a few lines of SQL than a few lines of C#, but it is worth it for the better results. (I'm not saying C# is bad - they are good for different things and I am really looking forward to being able to write stored procedures in C# in SQL Server 2005)
I think I gave the example of reducing a 20 min process to 7 seconds switching from cursors to set based - so you see how much better a little extra thought is worth.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hi,
i am very new to the MS SQL Server 2000 Reporting Services. i have made a few reports, and in each case made a dataset from the Report Designer view in VS.NET 2003.
now my question is that how am i suppose to provide the datasource through code at runtime? is it possible in Reporting Services as was in the case of Crystal Reports? and if there is a way, what is it?
can anyone guide me through the procedure?
thanx in advance
☺«««DTA»»»☺
|
|
|
|
|
you can use SP with parameters as queries in the RDL and your SP may return a record set that depends on parameters you pass to. you can pass those parameters to the msrs report thru ReportingService.Render() webmethod.
check the followin
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_ak_1xfd.asp[^]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.asp[^]
http://www32.brinkster.com/srisamp/sqlArticles/article_41.htm[^]
probaly you work on a huge project that has many many sps and some of them use temporary tables or returns multiple recordset then you are not able to use those sps in the msrs because as a rule those sps does not have a predifined data scheme. may be you applcation has eiter many webservices or data access components that provied data that could be shown in reports then maybe it makes sense to make an extension to the msrs that is tied with your application. so that you can reuse existing code that retrives data from the db. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_8p85.asp[^] it looks like it's easy to make
|
|
|
|
|
thanx for the help!
☺«««DTA»»»☺
|
|
|
|
|
I've managed to work with large xml generated using SQLXML. What i need now is to save the XML generated using SQLXML to a file on my disk as soon as it is generated in my SQL query. Can u guys suggest me a technique wherein i can just save the xml generated to a file. In the usual case i have to wait for the XML to generate which in my case takes atleast 2 minutes and then save it as an XML file.I want that this should happen in my query itself. this means first the XML data should be saved to a file and then i should be able to see it.
Regards
Wilbur J. Pereira
"If they love me let them, for the heck of it.If they don't, who da heck cares about it"
|
|
|
|
|
OK - I have a database with a table [User permissions] which controls what the user of the print monitoring application is allowed to do (i.e. see only own documewnts, or all etc.)
When a user submits a print job their name is passed to the database by the print monitoring service. If they have not been added to this table then they are added with the minimum permissions. I would like this step to also set up their database log in so have set a trigger thus:-
<br />
<br />
CREATE TRIGGER User_Permissions_Insert<br />
ON dbo.[User Permissions]<br />
FOR INSERT<br />
AS<br />
<br />
DECLARE @DOMAIN VARCHAR(200),<br />
@FULL_USERNAME VARCHAR(255)<br />
<br />
<br />
SELECT @DOMAIN = ISNULL([Parameter Value],'')<br />
FROM dbo.[PUMA Parameters]<br />
WHERE [Parameter Name] = 'NT Domain'<br />
<br />
IF @DOMAIN = ''<br />
SELECT @FULL_USERNAME = [User Name]<br />
FROM inserted<br />
ELSE<br />
SELECT @FULL_USERNAME = LTRIM(@domain) + '\' + Ltrim([User Name])<br />
FROM inserted<br />
<br />
<br />
-- Add the NT login to the database logins list<br />
EXECUTE sp_grantlogin @FULL_USERNAME<br />
<br />
-- And grant them access to the current database<br />
EXECUTE sp_grantdbaccess @FULL_USERNAME<br />
<br />
<br />
RETURN
However when I cause the trigger to fire I get the error message:-
The procedure 'sp_grantlogin' cannot be executed within a transaction.
Any ideas how I would go about doing this, or is a trigger the wrong solution for this case?
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
A trigger is really the wrong solution for this. You'll never be able to avoid the transaction because, even if not in an explicit transaction created with BEGIN TRANSACTION, a trigger executes in the context of the statement that caused it to fire, which is implicitly transactional.
I'd argue that triggers are for two purposes - for enforcing consistency (although you should normally use a constraint for that), and for selective denormalization - the trigger updates the denormalized data as the normalized data is updated. For example, updating an 'order total' field of an order record when order line records are inserted, updated or deleted. They're also useful for trapping and redirecting attempted updates to a view - this is really another case of handling denormalization or partitioning.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Agreed - I have decided to use an NT Group to adminsiter permissions to the database. Thus membership of teh group becomes a LAN team job 
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Hello,
I am using C# and creating a ASP.Net application that uses SQL Server 2000. I have customers that have to be input by a user. etc, name, address, e-mail. But I don't want the user to enter a customer ID number. I want to be able to automatically generate a unique number, so the user does not have to input one themselfs. In MS Access you had auto number that did this for you. Is there something that can be used in SQL Server.
Many thanks in advance,
Steve
|
|
|
|
|
You can use IDENTITY columns, or you could use the uniqueidentifier data type, which is a GUID. Generate a new GUID using the NEWID function.
The former is more human-friendly, but does have the property that, if a clustered index is built on the identity column, new records are placed on the last page, which can lead to contention for that page. The GUIDs, being essentially random, in general avoid this problem.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi, I dont know if you have solved your problem yet, but this is possible in SQL. Is the customerID you mention an int type?
|
|
|
|
|
Hello,
The problem has been solved now.
There are 2 ways to do this either using the GUID, which will generate a randon number or setting the SQL datatype to an Int and setting its property to Identifier. I have done it using the latter.
Many thanks for all your help,
Steve
|
|
|
|
|
hi guys i've posted this on both the SQL and ASP.Net forums cause i ain't sure which one i should use so sorry about clogging up the message board.
Basicly i'm a newbie with ASP.net and i was looking for somw help with this query
i got a site and as part of the functionality i need to create a page where the user can create their own SQL statements to query a database (obc one thats connected to the web page). I got fairly basic knowledge when it comes to asp.net but i know how to access stored procedures and basic DB conectivity, but i've know idea how to do it when the user is meant to define the whole query from multiple tables (if possible) i know its asking a lot but any advice no matter how small would be appriciated...oh and while i remember its an Access DB im connecting to.
|
|
|
|
|
I'd suggest avoiding this approach at all costs, because it means your user can essentially enter *anything*, which makes it VERY insecure.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
ok thats cool, could u suggest how it could be done anyway (its for a project you see and it has to allow this kind of reporting or something similar, the project will never go online anyway)
|
|
|
|
|
|
k any suggestions as to what i can do instead?
|
|
|
|
|
One system I built a couple of years ago gives very strict access to the account the ASP.NET web application was running in. It had SELECT access only on the tables is absolutely needed. Then the query was built using a standard strucutre (obviously this might be a little restrictive) whereby the user could only select valid table names and so on - no free text box. When all this information was posted back the details were checked again before permitting the query to run.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
k i guess i'll try that thanks for the advice
|
|
|
|
|
Hi there,
I've got a table which acts as a repository for searchable text data (lets call it MyInfoTable). Through a User Interface (Web) I'd like to let the user allow searching the Content field (type text) of MyInfoTable. The user can add various terms which are then used to build the query.
A couple of questions arise:
1. How can I build a injection-safe query which makes use of the WHERE clause and adds all the search terms into it. I'd like to do it through a stored proc but how can I specify a collection of params (one for each search term) to pass into it?
2. Is there a best-practise regarding searches on midium to large datasets? Things I have to take care of, etc.?
Sorry, if this questions are quite dumb, but I've never seen a fulltext search implemented and just trying to get started... 
Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
