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

2005 SP2 Database Services failure

I applied SP2 to my XP box and am getting a Failure on the Database Services
component. Everything else went fine. I have protocols Shared Memory, Named
Pipes (turned off trying to resolve failure) and TCP/IP enabled. VIA is
disabled. The only 2 services prior to SP2 install running are the Main
Server process & Sql Browser - both are set to localsystem account.
I have included the error from "View Summary."
It talks about a connection getting closed.
Any help would be apprecieated.
Thanks, Glen
Error Description :
----
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has
encountered the following problem: [Microsoft][SQL Native Client]TCP
Provider: An existing connection was forcibly closed by the remote host.
. To continue, correct the problem, and then run SQL Server Setup again.Hi Glen
I am not sure why the connection was forced to close, if you are running AV
software you may want to suspend it during the installation, but then I
would expect you to be having problems with this during your normal use if
that was the case!!
John
"Glen M" <Glen@.newsgroup.nospam> wrote in message
news:6A9F5F90-6C7B-4C34-BB54-F917BCB41F64@.microsoft.com...
>I applied SP2 to my XP box and am getting a Failure on the Database
>Services
> component. Everything else went fine. I have protocols Shared Memory,
> Named
> Pipes (turned off trying to resolve failure) and TCP/IP enabled. VIA is
> disabled. The only 2 services prior to SP2 install running are the Main
> Server process & Sql Browser - both are set to localsystem account.
> I have included the error from "View Summary."
> It talks about a connection getting closed.
> Any help would be apprecieated.
> Thanks, Glen
> Error Description :
> ----
> Product : Database Services (MSSQLSERVER)
> Product Version (Previous): 3042
> Product Version (Final) :
> Status : Failure
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
> Error Number : 29537
> Error Description : MSP Error: 29537 SQL Server Setup has
> encountered the following problem: [Microsoft][SQL Native Client]TCP
> Provider: An existing connection was forcibly closed by the remote host.
> . To continue, correct the problem, and then run SQL Server Setup again.
>|||Hi,
I understand that your SQL Server 2005 SP2 setup program failed due to the
TCP provider error.
If I have misunderstood, please let me know.
For further research, I would like to collect the following information
from you:
1. Your SQL Server 2005 SP2 setup log files which are located in
%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix;
2. Your SQL error logs which are located in %ProgramFiles%\Microsoft SQL
Server\MSSQL(.x)\MSSQL\LOG.
Please first mail me (changliw_at_microsoft_dot_com) an email response and
then I will create a public workspace for your uploading your files.
Also I would like to ask you two questions:
1. Could your SQL Server database service be started with TCP protocol now?
You can first check which TCP ports are configured for your SQL Server from
SQL Server Configuration Manager. After that, run "netstat -nba
>C:\Tcpport.log" from command line to check which TCP port your SQL Server
is listening.
2. Can you connect to your SQL Server instance at your client via
sqlcmd.exe?
For example: sqlcmd -Stcp:<servername>\<instancename> -E
3. What is the edition of your SQL Server 2005?
Look forward to your response.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Glen,
I have not received your response. Could you please let me know the issue
status? If you have any questions or concerns, please feel free to let me
know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

2005 SP1 (remote Reporting Services upgrade failures)

Why can it never be easy? I applied SP1 to one of our 2005 database servers and upgraded all components successfully. However when trying to install on a stand alone Reporting Services instance that uses the above database server I can't get past the Login verification. Windows or Sql...Per the ReadMe I am using an acccount that is administrator on both servers and sysadmin in sql, also am running the exe as same admin account.

There are no logs under %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files but I found this error in the hotfix log.

[SQL Native Client]TCP Provider: The requested address is not valid in
its context.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.

Verified that the SQL Server allows remote connections (TCP\IP is enabled, Named Pipes is not); even started the Browser sevice that hasn't been running and still get the same error.

Will the steps outlined by Lukasz at http://blogs.msdn.com/lukaszp/archive/2006/05/18/601340.aspx solve my problem or will I still need to upgrade the RS files somehow? I think I belong to 'Sometimes does not work" #3. The Native Client upgrade runs fine by itself.

Advice and commiserations appreciated.

AB

Solved error above by recreating the client alias to the sql server. The port designation had disappeared even though it was default port.

Now am experiencing locked file errors - SQL Reporting Services service is disabled and the web site is stopped. What other services need to be stopped? Do services on the remote sql server also need to be stopped?

AB

2005 SP1

Why can it never be easy? I applied SP1 to one of our 2005 database servers and upgraded all components successfully. However when trying to install on a stand alone Reporting Services instance that uses the above database server I can't get past the Login verification. Windows or Sql...Per the ReadMe I am using an acccount that is administrator on both servers and sysadmin in sql, also am running the exe as same admin account.

There are no logs under %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files but I found this error in the hotfix log.

[SQL Native Client]TCP Provider: The requested address is not valid in
its context.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.

Verified that the SQL Server allows remote connections (TCP\IP is enabled, Named Pipes is not); even started the Browser sevice that hasn't been running and still get the same error.

Will the steps outlined by Lukasz at http://blogs.msdn.com/lukaszp/archive/2006/05/18/601340.aspx solve my problem or will I still need to upgrade the RS files somehow? I think I belong to 'Sometimes does not work" #3. The Native Client upgrade runs fine by itself.

Advice and commiserations appreciated.

AB

Solved error above by recreating the client alias to the sql server. The port designation had disappeared even though it was default port.

Now am experiencing locked file errors - SQL Reporting Services service is disabled and the web site is stopped. What other services need to be stopped? Do services on the remote sql server also need to be stopped?

AB