Newbie needs help with homework due today

Hi Folks,
Please be gentle as this newbie is in a beginners SQL class and is stuck on the homework assignment. I would be very grateful for any help I can get.

System: MS Access2000

Problem: To write an SQL statement that will write the results of a UNION query to a new table in my database.

Where am I at? I have written the UNION query & it does return the results I expect. When I modify the query (by adding INTO Newtable) to write the result set to the new table, I get an error, "An action query cannot be used as a row source"

Code I'm using:
SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
INTO Newtable
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Exempt'
UNION
SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Non-exempt';

Additional Info: If I just do one part of the compound query, I can write records to Newtable with no problem.

HEre is what my instructor says on the matter:
I've had some questions about how to integrate the UNION query with the SELECT...INTO statement. So here's some syntax information. I hope it helps.

In simple terms, the syntax for the SELECT ... INTO is

SELECT fieldlist INTO newtablename FROM recordsource

Where fieldlist has the list of new field names for your table. You will need to make sure that the recordsource returns the same number of fields.

newtablename is the name you want the new table to have

recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

Example:

SELECT ItemName, LunchPrice INTO LunchMenu
FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)
:( :(

I seem to be having a problem with syntax because the query works without the INTO part and the writing of records works if I don't try to use the UNION SELECT statement.

Any ideas?Just look more closely at the syntax definition/example:

recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

Example:

SELECT ItemName, LunchPrice INTO LunchMenu
FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)

So, you could try this:

SELECT * INTO Newtable
FROM (
SELECT Employees_TBL.FirstName, Employees_TBL.LastName
, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
AND JobTitle_TBL.Status = 'Exempt'
UNION
SELECT Employees_TBL.FirstName, Employees_TBL.LastName
, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
AND JobTitle_TBL.Status = 'Non-exempt');

:cool:
DISCLAIMER: This is just a suggestion due to the fact I know very little MS Access! :o

Newb question

If I am using Ado.Net from a .Net Class library hosted outside SqlServer to do Sends and Receives do I have to use anything else other than queues? The reason I am asking is because I already have an abstraction layer for a couple of our queueing systems and I was hoping to put SSB in there as well but the abstraction layers' primitive is the Queue not Conversation/Dialog/Service, etc.

Thanks

CostasWhile you could build a layer that exposes just queues, you will soon discover that it will not be possible to expose all the richness of Service Broker (i.e. conversations, conversation group locking, etc) in that manner. But if you need to keep the existing interface in order to port your app over, you could certainly build a layer based on Queues like you describe.|||Rushi, thanks. The reason I asked is that almost all the examples I've seen use conversation where using the queues. Can you point me to an example where I can do send & receive solely with queues?

Thanks

Costas|||OK, I am getting the idea that I cannot just "send" a message to a queue. I think I have to create a queue and a service to go with it. Now, if my client needs to "send" on a queue, can I fake it with 1 stored procedure that takes the service name as a parameter?

Does this look reasonable?

create message type
[http://www.myblobcom/msg/BlobMsg]
validation = NONE;

create contract [http://www.myblobcom.com/contract/SaveBlob/v1.0]
(
[http://www.myblobcom.com/msg/BlobMsg] sent by initiator
);

create queue [Blob Queue];

create service [BlobSvc]
on queue [Blob Queue]
(
[http://www.myblobcom.com/contract/SaveBlob/v1.0]
);

CREATE PROCEDURE SaveBlob (

@.service varchar(36),

@.queue varchar(36),

@.payload VARBINARY(MAX))
AS BEGIN
declare @.dh uniqueidentifier;

-- HERE IS WHERE I HAVE MY CONCEPTUAL PROBLEM
-- HOW DO I BEGIN A DIALOG WITH A STORED PROCEDURE
-- CALLED BY AN EXTERNAL ADO.NET CLIENT?
-- begin dialog @.dh
-- from service [Inventory Client]
-- to service 'Inventory'
on contract [http://www.myblobcom.com/contract/SaveBlob/v1.0]
with encryption=off;

send on conversation @.dh message type [http://www.myblobcom/msg/BlobMsg] (@.payload );

end

Thanks

Costas|||Hi, can someone tell me if I am on the right track?

Thanks

Costas|||

Uncomment the begin dialog lines. Use the @.service parameter as the to service in that statement. That will allow you to begin a dialog and send a message in your stored proc. This proc can be called from ADO.Net.

|||

Rushi, thanks, I am not sure what to do with the [from service]. What's the from service set to when you're called from an extarnal ADO.NET client?

Cheers

Costas

|||

Dialog conversations are persistent sessions between two services -- the initiator and the target service. When beginning a dialog, you must specify the initiator service in the from argument and the target service in the to argument. Even if you use dialogs for one-way messaging, you still need a 'from' service where errors could be sent back as reponses. Each service has its own queue which serves as a backing store where messages are delivered until the service receives them.

Refer to Books Online to understand more about Service Broker architecture. You could also get a copy of Roger Wolter's book on Service Broker.

|||Rushi, thanks, I think that's where my ability to comprehend the service broker external call breaks down. All samples, articles, etc discuss it from the point of view of internal services talking to each other through dialogs which is not what I want to do or is it? Are you implying that my "from" service is really just a placeholder in my case just so that the dialog can take place? So, I'd call the stored proc from ado.net which will send a message in the queue from what I can call the "externalactivationservice" and the message will be sent to my handle blob service.

I am going to give it a shot, thanks

Costas