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

2k Replication errs .... Error 2812

Hey,
Having some probs w replication between 2 2k srvs running sql2k.

The dist agent is experiencing errors........

"Could not find stored procedure 'sp_MSins_tester'

Error number: 2812

Initially it gave an error of 18452 on dist agent of Could not connect.
I updated the id/pw for the subscriber and that went away and generated the above.

Any thoughts on what I should try next?

--mattHi Matt ,

from what I have experienced (both errors) you will have to create a stored procedure for you dist agent , that you should solve the problem.you might want to check sql books online there is more info there.

Hope this helps in some way..

Regards
Burner|||What did you end up doing to get by this problem ?|||Which service pack do you have installed for sql server 2000 (on both sql servers) ?

2601 Error When Creating or Altering a SPROC SS2005

I get the following error(in profiler) when creating or altering a stored
procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
To duplicate create a table (table1) start profiler, add errors and
warnings, start the trace, create the proc below
create procedure test
As
select * from table1
select * from table1
go
The number of exceptions you see in the profiler trace is the same as the
number of columns on table1. Is this some kind of database engine problem
where the engine is trying to insert/update sys.sql_dependencies?I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
(object doesn't exist).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
>I get the following error(in profiler) when creating or altering a stored
> procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> To duplicate create a table (table1) start profiler, add errors and
> warnings, start the trace, create the proc below
> create procedure test
> As
> select * from table1
> select * from table1
> go
> The number of exceptions you see in the profiler trace is the same as the
> number of columns on table1. Is this some kind of database engine problem
> where the engine is trying to insert/update sys.sql_dependencies?
>|||Tibor,
Thanks for the reply. I went back and tested this and it happens in my
production version:
9.00.1399.06 RTM Enterprise Edition (64-bit)
But it does not happen in my development version:
9.00.2047.00 SP1 Developer Edition
Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
and see if it goes away.
Thanks
"Tibor Karaszi" wrote:
> I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> (object doesn't exist).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> >I get the following error(in profiler) when creating or altering a stored
> > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> >
> > To duplicate create a table (table1) start profiler, add errors and
> > warnings, start the trace, create the proc below
> >
> > create procedure test
> > As
> > select * from table1
> > select * from table1
> > go
> >
> > The number of exceptions you see in the profiler trace is the same as the
> > number of columns on table1. Is this some kind of database engine problem
> > where the engine is trying to insert/update sys.sql_dependencies?
> >
> >
>
>|||Upgraded to:
9.00.2047.00 SP1 Enterprise Edition (64-bit)
Still has the same issue. Can anyone else duplicate this issue?
Thanks
"hpux9@.nospam.nospam" wrote:
> Tibor,
> Thanks for the reply. I went back and tested this and it happens in my
> production version:
> 9.00.1399.06 RTM Enterprise Edition (64-bit)
> But it does not happen in my development version:
> 9.00.2047.00 SP1 Developer Edition
> Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
> and see if it goes away.
> Thanks
> "Tibor Karaszi" wrote:
> > I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> > (object doesn't exist).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> > >I get the following error(in profiler) when creating or altering a stored
> > > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> > >
> > > To duplicate create a table (table1) start profiler, add errors and
> > > warnings, start the trace, create the proc below
> > >
> > > create procedure test
> > > As
> > > select * from table1
> > > select * from table1
> > > go
> > >
> > > The number of exceptions you see in the profiler trace is the same as the
> > > number of columns on table1. Is this some kind of database engine problem
> > > where the engine is trying to insert/update sys.sql_dependencies?
> > >
> > >
> >
> >
> >

208 error in stored procedure

Finding 208 errors in profiler trace while executing a
stored procedure yet the results look okay. Anyone run
across this before?Could be a variety of things, see http://tinyurl.com/swjv
"George H" <anonymous@.discussions.microsoft.com> wrote in message
news:037b01c39e5c$b55ebad0$a301280a@.phx.gbl...
> Finding 208 errors in profiler trace while executing a
> stored procedure yet the results look okay. Anyone run
> across this before?|||Look at KB article 243352. This will tell you how to tun it off.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"George H" <anonymous@.discussions.microsoft.com> wrote in message
news:037b01c39e5c$b55ebad0$a301280a@.phx.gbl...
> Finding 208 errors in profiler trace while executing a
> stored procedure yet the results look okay. Anyone run
> across this before?

20598 MSrepl_errors information required

I'd like to know how to know what replication stored procedure and parameters
to that is being run for a 20598 error I'm receiving.
in MSrepl_errors, for all these 20598 errors
error_text is: "The row was not found at the Subscriber when applying the
replicated command."
xact_seqno is: 0x0000... (ZERO)
so there seems to be no chance to use sp_browsereplcmds <xact_seqno>
My configuration: Transactional "nosync" replication
SQL Server 2000 SP4
The Distribution Agent Profile has the following:
-SkipError 2601:2627:20598
This is due to the fact my Publisher has a 2 year history of data and we're
replicating new data to a fresh DB . Deletes/Updates are not found, hence
the 20598 errors.
However, I'd like to know what data is erroring out. I will likely be able
to reduce these errors to a minimum but don't have any information on them to
investigate.
Suggestions?
Jack Griffith
Enable logging as per this kb article.
http://support.microsoft.com/kb/312292
Note that it appears that you are trying to update a row on the subscriber
which has already been deleted.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jack Griffith" <JackGriffith@.discussions.microsoft.com> wrote in message
news:188524CC-3706-4D41-B916-319D5C0715F0@.microsoft.com...
> I'd like to know how to know what replication stored procedure and
> parameters
> to that is being run for a 20598 error I'm receiving.
>
> in MSrepl_errors, for all these 20598 errors
> error_text is: "The row was not found at the Subscriber when applying the
> replicated command."
> xact_seqno is: 0x0000... (ZERO)
> so there seems to be no chance to use sp_browsereplcmds <xact_seqno>
> My configuration: Transactional "nosync" replication
> SQL Server 2000 SP4
> The Distribution Agent Profile has the following:
> -SkipError 2601:2627:20598
> This is due to the fact my Publisher has a 2 year history of data and
> we're
> replicating new data to a fresh DB . Deletes/Updates are not found, hence
> the 20598 errors.
> However, I'd like to know what data is erroring out. I will likely be
> able
> to reduce these errors to a minimum but don't have any information on them
> to
> investigate.
> Suggestions?
> --
> Jack Griffith
|||Updating a row that has been deleted is indicated by the xact_seqno being 0x00?
Jack Griffith
"Hilary Cotter" wrote:

> Enable logging as per this kb article.
> http://support.microsoft.com/kb/312292
> Note that it appears that you are trying to update a row on the subscriber
> which has already been deleted.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Jack Griffith" <JackGriffith@.discussions.microsoft.com> wrote in message
> news:188524CC-3706-4D41-B916-319D5C0715F0@.microsoft.com...
>
>
|||- First of all. Thank you! I really appreciate your quick response to my
initial request. I applied the suggestion from the knowledgebase article,
heeding the warning regarding the impact of logging.
- The net result was that I was able to identify and resolve one of the
sources of my errors which reduced about 90% of those occuring. I'm left
with 10% unsolved and no practical way to identify them.
==> My question is:
Why is the xact_seqno in MSrepl_errors = 0x00 when it should be ( I think!):
0x0004c6c4000006570007
If MSrepl_errors could the text of the error, and xact_seqno, it would take
huge burdon off, as I cannot afford to run the logging given the onerous load.
More details are below to explain this question.
- The impact of error logging as per your suggestion was huge! In one hour,
my log filled up with about 50Mb's and the transaction latency went from
1,500 ms to 1,500,000ms.
- As soon as I started to log the activity, there were no errors. I was
thinking it was a classic "Heisenberg uncertainty principle" situation, but
in the last couple of minutes of the hour, I got 2 errors.
- Had to stop logging at that point. I reset the logging level to '0' which
should log errors only. However, it didn't seem to do that.
- I inspected the error logs and was a bit misled. You have to be careful
to group all of the error activity together in spite of the other unrelated
transaction activity that is interleaved. It's somewhat logical but
disorienting.
- OK the two errors were attributed to UPDATE statements to a table for
which the rows are non-existent in the Subscriber side. It was not
unexpected in this case.
- What is unexpected, is the inability of the MSrepl_errors table to track
the information. I say this because I find the following information in the
log:
============= start of error information =================
Last transaction timestamp: 0x0004c6c400000656000200000000
Transaction seqno: 0x0004c6c4000006570007
--^^^^^^^^^^^^^^^^^
Command Id: 1
Partial: 0
Type: 0
Command: [4/1/2007 6:42:46 PM]<SQL Server name>.<DB Name>:
{...several CALL sp_MSins<no problem table>...}
{CALL to sp_MSupd<culprit table> }
^^^^^^^^^^^^^^^^^^^^^^^^
{...several CALL sp_MSins<no problem table>...}
Skipped 1 error(s) when applying transactions at the Subscriber.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[4/1/2007 6:42:46 PM]<SQL Server name>.<DB Name>: update
MSreplication_subscriptions set transaction_timestamp = ?, "time" = ? where
publisher = ? and publisher_db = ? and independent_agent = 0 and
subscription_type = 0
Last transaction timestamp: 0x0004c6c400000657000700000000
Transaction seqno: 0x0004c6c4000006590009
Command Id: 1
Partial: 0
Type: 0
Command: [4/1/2007 6:42:46 PM]<SQL Server name>.distribution: {call
sp_MSadd_distribution_history(13, 3, ?, ?, 92518, 92518, 0.00, 0x01, 1, ?,
0, 0x01, 0x00)}
The commit timestamp value is: 0x0004c6c400000654000600000000
if exists (select * from sysobjects where name = 'syspublications') if
exists( select * from syspublications where repl_freq = 0) exec
sp_replsetoriginator ?, ?
ErrorId = 974, SourceTypeId = 5
ErrorCode = '20598'
ErrorText = 'The row was not found at the Subscriber when applying the
replicated command.'
[4/1/2007 6:42:46 PM]<SQL Server name>.distribution: {call
sp_MSadd_repl_error(974, 0, 5, ?, N'20598', ?)}
=================== end of error stream ====================
- So to repeat: why is the xact_seqno in MSrepl_errors = 0x00 when it
should be ( I think!): 0x0004c6c4000006570007
Bug?
Jack Griffith

2005: calling .NET procedure

Hi,
I am learning SQL Server 2005. I need to call .NET assembly procedure
from T-SQL.
Here's part of my assembly:

using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace DemoSQLServer
{
public sealed class Demo
{
[SqlProcedure(Name="PodajKsiki")]
public static void PodajKsiki()
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Ksiki");
SqlDataReader dr = cmd.ExecuteReader();
SqlContext.Pipe.Send(dr);
}
...
}
}

I have created assembly in Object Explorer (Programmability /
Assemblies).
How to call procedure? I tried:
exec DemoSQLServer.PodajKsiki
but I got a message:

Could not find stored procedure 'DemoSQLServer.PodajKsiki'.

Please help.
Thank you.
/RAM/On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:

Quote:

Originally Posted by

>Hi,
>I am learning SQL Server 2005. I need to call .NET assembly procedure
>from T-SQL.
>Here's part of my assembly:

(snip)

Quote:

Originally Posted by

>I have created assembly in Object Explorer (Programmability /
>Assemblies).

Hi RAM,

How did your CREATE ASSEMBLY statement look? Can you post it?

--
Hugo Kornelis, SQL Server MVP|||On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
>

Quote:

Originally Posted by

>>Hi,
>>I am learning SQL Server 2005. I need to call .NET assembly procedure
>>from T-SQL.
>>Here's part of my assembly:

>(snip)

Quote:

Originally Posted by

>>I have created assembly in Object Explorer (Programmability /
>>Assemblies).

>
>Hi RAM,
>
>How did your CREATE ASSEMBLY statement look? Can you post it?

I haven't used CREATE ASSEMBLY. I simply right-clicked
Programmability/Assemblies, and chosen "New Assembly...".
Please help
/RAM/|||On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
>

Quote:

Originally Posted by

>>Hi,
>>I am learning SQL Server 2005. I need to call .NET assembly procedure
>>from T-SQL.
>>Here's part of my assembly:

>(snip)

Quote:

Originally Posted by

>>I have created assembly in Object Explorer (Programmability /
>>Assemblies).

>
>Hi RAM,
>
>How did your CREATE ASSEMBLY statement look? Can you post it?

I scripted:

CREATE ASSEMBLY [DemoSQLServer]
AUTHORIZATION [dbo]
FROM ...
WITH PERMISSION_SET = SAFE|||On Tue, 11 Jul 2006 14:22:00 +0200, R.A.M. wrote:

Quote:

Originally Posted by

>On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALIDwrote:
>

Quote:

Originally Posted by

>>On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
>>

Quote:

Originally Posted by

>>>Hi,
>>>I am learning SQL Server 2005. I need to call .NET assembly procedure
>>>from T-SQL.
>>>Here's part of my assembly:

>>(snip)

Quote:

Originally Posted by

>>>I have created assembly in Object Explorer (Programmability /
>>>Assemblies).

>>
>>Hi RAM,
>>
>>How did your CREATE ASSEMBLY statement look? Can you post it?

>
>I scripted:
>
>CREATE ASSEMBLY [DemoSQLServer]
>AUTHORIZATION [dbo]
>FROM ...
>WITH PERMISSION_SET = SAFE

Hi RAM,

Thanks. Unfortunately, I now realise that I forgot to ask to post the
CREATE PROCEDURE statement you used as well - my apologies.

To cut this short, I'll just post my assumption: I _think_ that yoru
CREATE PROCEDURE statement looks like this:

CREATE PROCEDURE [PodajKsiki]
AS
EXTERNAL NAME DemoSQLServer.[DemoSQLServer.Demo].[PodajKsiki]
go

If this is indeed hoow yoou created the stored procedure, then you use
the following syntax to call it:

EXEC [PodajKsiki]

(or, if you want to follow best practice and explicitly add the schema:
EXEC dbo.[PodajKsiki] - but of course, then you'd add an explicit
schema on the various CREATE statements as well).

(Note - I used copy and paste for the procedure name since some of the
characters appear to be from a character set that's not installed on my
computer - I hope the procedure name looks right to you!)

(Second note - I chose safety first and enclosed the stored procedure's
name between bracktes, since I don't know if the actual characters that
look like on my computer are valid or not in an identifier. You can
always try to use PodajKsiki instead of [PodajKsiki]).

--
Hugo Kornelis, SQL Server MVP|||>CREATE PROCEDURE [PodajKsiki]

Quote:

Originally Posted by

>AS
>EXTERNAL NAME DemoSQLServer.[DemoSQLServer.Demo].[PodajKsiki]
>go

Thanks.

I cannot run procedure because of the followinf error:

Execution of user code in the .NET Framework is disabled. Enable "clr
enabled" configuration option.

I couldn't find the option in Configuration Manager nor Management
Studio, neigher a .config file. Do you know how to enable the option?
Thank you very much!
/RAM/|||sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO|||I have one more question - concernign functions.
I've written function:

[SqlFunction(Name="GetCurrentDateText")]
public static SqlString GetCurrentDateText()
{
DateTime dt = new DateTime();
return dt.ToString();
}

And I have tried to create a function:

CREATE FUNCTION GetCurrentDateText RETURNS nvarchar(MAX) AS EXTERNAL
NAME DemoSQLServer.DemoGetCurrentDateText

(according to Books Online) But I recive an error:

Incorrect syntax near 'RETURNS'.

I cannot guess what is wrong. Could you help me please?
Thank you!
/RAM/|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

I have one more question - concernign functions.
I've written function:
>
[SqlFunction(Name="GetCurrentDateText")]
public static SqlString GetCurrentDateText()
{
DateTime dt = new DateTime();
return dt.ToString();
}
>
And I have tried to create a function:
>
CREATE FUNCTION GetCurrentDateText RETURNS nvarchar(MAX) AS EXTERNAL
NAME DemoSQLServer.DemoGetCurrentDateText
>
(according to Books Online) But I recive an error:
>
Incorrect syntax near 'RETURNS'.
>
I cannot guess what is wrong. Could you help me please?

Parentheses are mandatory, even for parameterless funtions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

2005 Stored Procedure Question - How to pass in additional LIKEconditions

Hi Paparush,

I believe the problem is because you're mixing up dynamic SQL with
static SQL.
The @.SearchTerm variable that you are building up can not be used to
specify additional LIKE criteria for the Subject column, each LIKE
condition needs to be individually coded into your static SQL.

The alternative is to build the entire SELECT as dynamic SQL. This
provides additional flexibility, but there are performance and
security implications when doing this (google SQL injection).

Good luck!
JOn Dec 17, 10:33 am, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Paparush,
>
I believe the problem is because you're mixing up dynamic SQL with
static SQL.
The @.SearchTerm variable that you are building up can not be used to
specify additional LIKE criteria for the Subject column, each LIKE
condition needs to be individually coded into your static SQL.
>
The alternative is to build the entire SELECT as dynamic SQL. This
provides additional flexibility, but there are performance and
security implications when doing this (google SQL injection).
>
Good luck!
J

Hi J,
Thanks for the reply.

Yes..the dynamic SQL is the approach I've been trying all morning and
I've gotten it to work. I have to build the entire SELECT string in
my app's code, based on the search term the user enters and then stuff
this into @.SearchTerm and pass this string into the code below.

declare @.SearchTerm nvarchar(max)

EXEC sp_ExecuteSQL @.Search

2005 clr returning xmldocument

I would like to return an xmldocument from a 2005 vb clr stored procedure.

This is my definition for the stored procedure. passing in a string, return xmldoc.

Can I not return an xmldoc as output? The solution will build, but not run.

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Sub SP_Transform(ByVal cc As String, <Out()> ByVal RetValue As XmlDocument)

Error 1 Column, parameter, or variable #2: Cannot find data type XmlDocument. SqlServerProject1

Interesting question. I'll have to try it.

My gut, however, is that you should just return the XML data using the new Xml Data Type in SQL Server 2005 and just use a DataReader like dr.GetSqlXml(index) in ADO.NET to read the XML and load it into an XmlDocument.

I wrote an example of that here-

Reading XML Data Type into XmlDocument Using ADO.NET - SQL Server 2005 Tutorials

Regards,

Dave

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.

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.googlegr oups.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.

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.