Running 2 queries inside third query

Good morning, all.. your mission today (if you accept it) is to
decipher what I am trying to do and offer assistance to the poster.
(me!) I have searched newsgroups but can find no topics similar to my
needs.
Background:
Trying to create a 'dqy' (MS Query) file for distribution to field
personnel. Initial dqy files (when double-clicked) open Excel, connect
to database, and display the data based on the dqy's SQL.
Database structure is:
tblUser.PKID = tblUser_Role.User_ID_FK
tblUser_Role.Role_ID_FK = tblRole.PHID
tblRole_Course.Role_ID_FK = tblRole.PHID
tblRole_Course.Course_ID_FK = tblCourse.PKID
[Don't blame me... I didn't create this!]
Since other user's data requirements are more involved, I created an
Access database that has tables corresponding to the actual data
source. I 'develop' my queries in Access, copy the SQL into Notepad,
add appropriate connection information, and save file as ####.dqy.
This procedure has worked until now.
I cannot get the SQL for QueryA to work when copied into the dqy file.
QueryA works fine in Access!!! QueryA is composed of
qryUserAndTheirRoles and qryRolesAndTheirCourses.
Here is SQL for QueryA:
SELECT DISTINCTROW qryUserAndTheirRoles.PKID,
qryUserAndTheirRoles.Name, qryUserAndTheirRoles.LastName,
qryUserAndTheirRoles.Agency_ID_FK, qryRolesAndTheirCourses.Name
FROM qryUserAndTheirRoles INNER JOIN qryRolesAndTheirCourses ON
qryUserAndTheirRoles.Role_ID_FK = qryRolesAndTheirCourses.Role_ID_FK
SQL for qryUserAndTheirRoles:
SELECT tblUser.PKID, tblUser.Name, tblUser.LastName,
tblUser.Agency_ID_FK, tblUser_Role.Role_ID_FK
FROM tblUser LEFT JOIN tblUser_Role ON tblUser.PKID =
tblUser_Role.User_ID_FK
WHERE (((tblUser.Agency_ID_FK)=13))
SQL for qryRolesAndTheirCourses:
SELECT tblRole_Course.Role_ID_FK, tblCourse.Name
FROM tblCourse LEFT JOIN tblRole_Course ON tblCourse.PKID =
tblRole_Course.Course_ID_FK
I have been unable to find ANY reference source that describes usage
and syntax. Therefore, I have tried MANY combinations of syntax and
structure. None worked. Is there anyone in this esteemed group who
can modify QueryA -- replacing references to the other queries with
working SQL -- so that the code works when placed in a dqy file'
I bow to your expertise... and await salvation! Thanks
gary b
sfrvn AT earthlink.net<sfrvn@.earthlink.net> wrote in message
news:1133362941.170208.75080@.o13g2000cwo.googlegroups.com...
> Good morning, all.. your mission today (if you accept it) ...
I'm still waiting for this message to auto-destruct.sql

Run child package independently [FOUND IT]

Hi there

I have a parent package which execute a child package. But when the child package is run from the parent not all of the tools inside is executed. I have a variable "ExecuteStep" from the parent that gives information about which tools, that needs to be executed.

BUT

The child package should be able to run independently also. In this case all tools inside the package should execute.
This is however not possible because my variable "ExecuteStep" does not exist inside the package, it is inheritage from my parent.

If I create a variable with exactly the same name inside my child package, I can run the child independently, but when executed from the parent the value from the parent package is now overwritten by the child variable.

How can I get around this problem?

bjarneThe solution was just in front of me all the time.

As "Package Configuration" I was using the XML file I had generated with the Parent variables.

Instead I should have been using "Parent package variable", this way all works fine.

I found it with a help from Kirk Haselden's blog

http://sqljunkies.com/WebLog/knight_reign/comments/5366.aspx

Bjarne

Run child package independently

Hi there

I have a parent package which execute a child package. But when the child package is run from the parent not all of the tools inside is executed. I have a variable "ExecuteStep" from the parent that gives information about which tools, that needs to be executed.

BUT

The child package should be able to run independently also. In this case all tools inside the package should execute.
This is however not possible because my variable "ExecuteStep" does not exist inside the package, it is inheritage from my parent.

If I create a variable with exactly the same name inside my child package, I can run the child independently, but when executed from the parent the value from the parent package is now overwritten by the child variable.

How can I get around this problem?

bjarneThe solution was just in front of me all the time.

As "Package Configuration" I was using the XML file I had generated with the Parent variables.

Instead I should have been using "Parent package variable", this way all works fine.

I found it with a help from Kirk Haselden's blog

http://sqljunkies.com/WebLog/knight_reign/comments/5366.aspx

Bjarne

Run a SP with a datetime interval from a table

Hello,

I have something i called a deletelist, inside this list is items that is waiting to be deleted.

The deletelist looks like this:
username nvarchar(20),
itemtype int,
deletedate datetime

The SP that runs once everyday and deletes post is calledData_DeleteFromDeleteList.

Inside of this SP i have afew delete functions like this one:
DELETEFROM reg_userWHERE(username=(SELECT usernameFROM data_deletelistWHERE(deletedate<getdate()AND itemtype= 0)))

Now i want to add a new delete function to this list, this function is to execute a SP when the itemtype occurs, like this:

SELECTEXEC [dbo].[Data_CompletlyDeleteAUser] @.UserName= username, @.QResult= @.QResultOUTPUTFROM data_deletelistWHERE(username=(SELECT usernameFROM data_deletelistWHERE(deletedate<getdate()AND itemtype= 2)))

any idea of how i can rewrite this function so it works??
in this SP im not using the @.QResult for anything, thats in another SP

At the end of this SP, i just delete all the old records from the deletelist like this,

DELETEFROM data_deletelistWHERE(deletedate<getdate())

I don't think there's a way to do something like this without a cursor, and those should be avoided if possible. You can do a couple of things here

1.) maybe you can change the proc Data_CompletelyDeleteAUser to be Data_CompletelyDeleteAllUsersWithItemType and pass it 2, then in your proc, you would do whatever it does for ONE user, but for all of them that match that type from the deletelist table.

2.) you can get rid of the proc altogether (if nothing else is using it) and jsut embed the logic into the main SP, which will allow for the operation to be set based.

You can use the cursor if this proc will run once nightly and there aren't too many records, but if 1 or 2 works for you, I'd go with those above all else.

Once 2008 comes out we'll be able to pass Tabular data to stored procedures so this won't be an issue anymore.

--D

|||

The problem with combining the procedures is that, the Data_CompletelyDeleteAUser contains over 1000 lines of code =/

run a procedure, or query every 5 hours??

its possible make a procedure or something inside the sql server to run every 5 hour?? to make a update of a table
You can use the SQL jobs
Tools | Jobs scheduling
regards
HTH|||didn′t now, allways learning thanks

Run .exe

Hi, how are you doing?
Can I run a .exe inside a SP? Because before sending emails it needs to
authenticate, so I did a Delphi .exe !
MS SQL 2000
Thanks!Yes, by using xp_cmdshell. However this is a really ugly thing, and whatever is performed will
probably be lost after the scope of xp_cmdshell.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paulo" <prbspfc@.uol.com.br> wrote in message news:ub89Zq7gIHA.3352@.TK2MSFTNGP04.phx.gbl...
> Hi, how are you doing?
> Can I run a .exe inside a SP? Because before sending emails it needs to authenticate, so I did a
> Delphi .exe !
> MS SQL 2000
> Thanks!
>|||"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ub89Zq7gIHA.3352@.TK2MSFTNGP04.phx.gbl...
> Hi, how are you doing?
> Can I run a .exe inside a SP? Because before sending emails it needs to
> authenticate, so I did a Delphi .exe !
>
Yes you can do this, but don't.
There's a lot of things that can go wrong here, including xp_cmdshell
hanging and tying up resources.
And then of course there's just the time involved.
> MS SQL 2000
> Thanks!
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Run .exe

Hi, how are you doing?
Can I run a .exe inside a SP? Because before sending emails it needs to
authenticate, so I did a Delphi .exe !
MS SQL 2000
Thanks!
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ub89Zq7gIHA.3352@.TK2MSFTNGP04.phx.gbl...
> Hi, how are you doing?
> Can I run a .exe inside a SP? Because before sending emails it needs to
> authenticate, so I did a Delphi .exe !
>
Yes you can do this, but don't.
There's a lot of things that can go wrong here, including xp_cmdshell
hanging and tying up resources.
And then of course there's just the time involved.

> MS SQL 2000
> Thanks!
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html