3 tables wildcard searches

Hi,

I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.

There are 3 tables involved, the jobs, the applicants and the applications.

I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.

There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.

My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?

So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?

Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.

So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.

Thanks in advance.Can you post the URL for this assignment, or at least scan in the page(s) you got in class if they aren't available online?

-PatP|||If I understand the question correctly, something like this might work:

create proc usp_search(@.searchstring varchar(100))
as
select * -- replace with required cols
from applicants
join applications on applicants.applicantid = applications.applicantid
join jobs on applications.jobid = jobs.jobid
where forename like @.searchstring + '%' -- Auto Wild Card (Begins With)
or surname like @.searchstring + '%'
or jobtitle like @.searchstring + '%'
or jobref = @.searchstring
or applicantid = @.searchstring

3 tables ...

Hey
I have 3 tables :
Jobs table
jobID
jobname
JobQuestion
id
jobID
questionID
points
Question
questionID
questionText
I'd like to join this tables so that I have all names of jobs and points for
given question. It doesn't matter if the question has it's row in
jobQuestion table. I tried to use left / right joins but when I use WHERE
QuestionID = xxx I got only row where they exists in jobsQuestions Table.
Now it looks like this:
Select Job.Name, JobQuestion.Points
FROM Job Left Join Job.JobId = JobQuestion.JobId
WHERE JobQuestion.QuestionID = @.someParam
I don't use in this join 3 table but maybe I should but I don't know how to
join them so they work as I wrote above.
JarodCould you please so kind to provide us DDL for a better comprehension?
Thanks,
"Jarod" wrote:

> Hey
> I have 3 tables :
> Jobs table
> jobID
> jobname
> JobQuestion
> id
> jobID
> questionID
> points
> Question
> questionID
> questionText
> I'd like to join this tables so that I have all names of jobs and points f
or
> given question. It doesn't matter if the question has it's row in
> jobQuestion table. I tried to use left / right joins but when I use WHERE
> QuestionID = xxx I got only row where they exists in jobsQuestions Table.
> Now it looks like this:
> Select Job.Name, JobQuestion.Points
> FROM Job Left Join Job.JobId = JobQuestion.JobId
> WHERE JobQuestion.QuestionID = @.someParam
> I don't use in this join 3 table but maybe I should but I don't know how t
o
> join them so they work as I wrote above.
> Jarod
>|||> Could you please so kind to provide us DDL for a better comprehension?
> Thanks,
>
I wrote this in that way because the tables are a bit more complicated and
field names aren't written in English so let's stay with this form.
Jarod|||Is this what you're after...
select * from Question a
LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
LEFT JOIN Jobs c on b.JobID = c.JobID
WHERE a.QuestionID = 1
HTH. Ryan
"Jarod" <blueice@.NOSPAM.gazeta.pl> wrote in message
news:%2308KwhRHGHA.2912@.tk2msftngp13.phx.gbl...
> I wrote this in that way because the tables are a bit more complicated and
> field names aren't written in English so let's stay with this form.
> Jarod|||> select * from Question a
> LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
> LEFT JOIN Jobs c on b.JobID = c.JobID
> WHERE a.QuestionID = 1
>
And the problem is when I do somethink like this I got only one row which
exist in JobQuestion table. And what I wanted is all rows from Job table and
values for them from jobQuestion table. To be more precise Names of the Job
and Points ;)
Jarod|||"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uQaBhmRHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Is this what you're after...
> select * from Question a
> LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
> LEFT JOIN Jobs c on b.JobID = c.JobID
> WHERE a.QuestionID = 1
OR a.Question IS NULL
When I added OR a.Question IS NULL it works ;)
Jarod|||Better move the a.Question = 1 to the join:
...
LEFT JOIN JobQuestion b on (a.QuestionID = b.QuestionID) and (a.Question = 1
)
...
There was a discussion on this issue a few days ago:
http://msdn.microsoft.com/newsgroup...2430&sloc=en-us
I believe you should be aware of potential issues.
ML
http://milambda.blogspot.com/

2005 Workgroup - 'Create failed for jobstep' error creating backup

Hello,
I am running into an error when creating maintenence jobs in SQL Management
Studio. I can create the new jobs and specify all the options, but when I
attempt to save the job I receive the following error:
"Create failed for JobStep 'jobname'" or
"Create failed for Job 'jobname"
Here's my setup:
SQL 2005 Workgroup Edition SP1
Windows SBS 2003 SP1
I saw a Microsoft KB article, and several forums which confirm this, stating
that SQL Server 2005 Integration Services (SSIS) need to be installed for
maintenance jobs to be created successfully. However, and here's the catch
22, SSIS is not an available installation options in SQL Server 2005
Workgroup edition. So... how do I go about resolving this issue when the
recommended fix is not available for my version of SQL 2005?
Thanks for any help!Sorry, meant to include this in the original post... here is the MS KB
article I was referring to:
http://support.microsoft.com/kb/909036
"Epsil0n" wrote:
> Hello,
> I am running into an error when creating maintenence jobs in SQL Management
> Studio. I can create the new jobs and specify all the options, but when I
> attempt to save the job I receive the following error:
> "Create failed for JobStep 'jobname'" or
> "Create failed for Job 'jobname"
> Here's my setup:
> SQL 2005 Workgroup Edition SP1
> Windows SBS 2003 SP1
> I saw a Microsoft KB article, and several forums which confirm this, stating
> that SQL Server 2005 Integration Services (SSIS) need to be installed for
> maintenance jobs to be created successfully. However, and here's the catch
> 22, SSIS is not an available installation options in SQL Server 2005
> Workgroup edition. So... how do I go about resolving this issue when the
> recommended fix is not available for my version of SQL 2005?
> Thanks for any help!

2005 Management Studio: Jobs Details (summary page)

I'm missing the info I got from EM on jobs, in the summary page, e.g. when a
job was last run, success/failure, scheduled, enabled, etc.
I don't see any way to add these columns on the summary page in Managment
Studio, am I missing something, or is this info not available there?
Thanks
Tom
TomT wrote:
> I'm missing the info I got from EM on jobs, in the summary page, e.g. when a
> job was last run, success/failure, scheduled, enabled, etc.
> I don't see any way to add these columns on the summary page in Managment
> Studio, am I missing something, or is this info not available there?
> Thanks
> Tom
Tom,
Go to Object Explorer - SQL Server Agent - Jobs. Here you can right
click on the job and get the job history, properties etc. Pretty much
the same as you're used to on EM but it looks a bit different.
HTH
Regards
Steen
|||Hi Tom,
In SQL Server 2005, we use Job Activity Monitor to see following items
Name - Name of the job.
Enabled - Whether the job is enabled (yes) or not enabled (no).
Status - Current status of the job.
Last Run Outcome - Job status when last run.
Last Run - Date and time job was last run.
Next Run - Date and time the job is next scheduled to run.
Category - The job category assigned to the job.
Runnable - Yes if the job can be run; No if the job cannot be run.
Scheduled - Yes if the job is assigned to a job schedule; No if the job has
no schedule.
For more detailed information, check BOL or MSDN topic
Job Activity Monitor
http://msdn2.microsoft.com/en-us/library/ms188272.aspx
How to: View Job Activity (SQL Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms187449.aspx
Monitoring Job Activity
http://msdn2.microsoft.com/en-us/library/ms188272.aspx
How to: Set Up the Job History Log (SQL Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms175072.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

2005 - linked server query

I had a database, stored procedure, and related job in 2000 that
checked other servers (via linked server functionality) for failed
jobs. Works fine in 2000. I migrated the database over to my local
2005 instance, and now the query does not work any longer. The below
query returns the error below. Both the 2000 instance and the 2005
instance use Windows Authentication. Does anyone have any ideas?
SELECT 'LinkedServername',
j.job_id,
j.name,
jh.sql_message_id,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time
FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
[LinkedServername].[msdb].[dbo].[sysjobs] j
WHERE jh.run_status = 0
and jh.job_id = j.job_id
and Not Exists (
Select *
From failedjobs f
Where jh.run_status = 0
and jh.job_id = j.job_id
and jh.job_id = f.job_id
and jh.run_date = f.run_date
and jh.run_time = f.run_time)
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
does not exist or the current user does not have permissions on that
table.Anyone?|||Hi
Do you have permissions on the DB and table? Security has been tightened up,
so things that worked in 2000 may not work in 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131561766.037644.219120@.z14g2000cwz.googlegroups.com...
>I had a database, stored procedure, and related job in 2000 that
> checked other servers (via linked server functionality) for failed
> jobs. Works fine in 2000. I migrated the database over to my local
> 2005 instance, and now the query does not work any longer. The below
> query returns the error below. Both the 2000 instance and the 2005
> instance use Windows Authentication. Does anyone have any ideas?
>
> SELECT 'LinkedServername',
> j.job_id,
> j.name,
> jh.sql_message_id,
> jh.sql_severity,
> jh.message,
> jh.run_date,
> jh.run_time
> FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
> [LinkedServername].[msdb].[dbo].[sysjobs] j
> WHERE jh.run_status = 0
> and jh.job_id = j.job_id
> and Not Exists (
> Select *
> From failedjobs f
> Where jh.run_status = 0
> and jh.job_id = j.job_id
> and jh.job_id = f.job_id
> and jh.run_date = f.run_date
> and jh.run_time = f.run_time)
> Msg 7314, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
> not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
> does not exist or the current user does not have permissions on that
> table.
>|||Using SQL authentication, I'm dbo/sa on everything. In windows
authentication, I'm in a SQL DBA group which is aliased over to
sa/dbo/Sys Admin. I include both b/c this stored procedure goes out to
lots of linked servers and some are windows, some are sql. It works on
my 2000 instance - same stored procedure, same query, same linked
servers.