2005Express trying to enable Full text indexing.

We have an installation of SQL2005 Express that was loaded via the "SQL2005Express with advanced services" download .exe.

Full text indexing wasn't enabled during installation.

It says to go to add/remove programs. Going that route when it goes to install FTI it says that it can't find the installation files in what looks to be the origonal "TEMP" folder created and removed during installation.

If I run the program installer from the download again it states that SQL is already installed and to use "add/remove programs".

We are stuck at this point. Is there an ISO available that could be burned to disk?

Thanks in advance.

We've seen this error a few times during Express reinstalls and I haven't heard of a nice solution. One option is to backup your databases, uninstall your Express instance and reinstall it with FTI enabled.

Thanks,
Sam Lester (MSFT)

|||

What you should be able to do is run the original program installer from a command line with the "\X" switch. This will display a dialog that will allow you to extract the files inside the package to wherever you want. You should be able to put them anywhere you like on you local hard drive. Then when you use Add/Remove programs to maintain your Express instance, and it asks for the files, make sure you browse to the "Setup" directory underneath where you extracted the bits. That should work without you having to uninstall your existing instance.

|||This will also be documented in the requirementssqlexp2005.htm file located at the root of the folder where you extracted out the bits. It is under the section titled "Modifying SQL Server Express Installed Components".|||After unpackaging using /x I get the following error, when trying to add FTI via the "add remove programs".

"The file G:\myfilepath is not a valid installation package for the product Microsoft SQL Server 2005 (SQLEXPRESS). Try to find the installation package 'sqlrun_sql.msi' in a folder in which you can install Microsoft SQL Server 2005 (SQLEXPRESS)."

The file is there in the folder.

Any other ideas short of uninstalling/reinstalling?|||

You need to make sure to browse to the "Setup" folder underneath where you extracted. If the setup folder is not there, than the extraction may not be correct. G:\myfilepath should contain a setup.exe file, and then a setup folder. In the setup folder there should be a bunch of MSIs, one of which is sqlrun_sql.msi.

Also, make sure the Sqlexpr.exe you are running to extract is the same as what was used initially to install. If the editions don't match than you could see that error as well. For example, don't mix and match SQL Express with SQL Express with Advanced Services.

|||I did point to the file in the setup folder (it was there).

This is the same .exe that was downloaded one month ago.|||I just tried this out myself and it does appear to work, so hopefully this should be easy to resolve. Could you post two logs so I can see what might be failing for you? Both the latest *_WI.log and *_SQL.log files would be needed (located in %programfiles%\microsoft sql server\90\setup bootstrap\log), thank you!|||

Sorry, I'm new here and didn't realize you can't post attachments. Here is what I need from the *_WI.log. Search for this string:

Doing action: ResolveSource

And then copy and paste the contents of the log up until this string:

ResolveSource. Return value

Let's just start with that, and then see if I need the *_SQL.log.

2005 Internet Replication

Is there any experience with SQL Server 2005 replication?
I want to do replication via Internet without implement VPN or physical
connection to SQL Server.
I think that this is imposible with SQL Server 2000 but watch a presentation
on TECHED which
says that this is possible with SQL Server 2005 ?
Grigoris,
I have set this up in a test environment and you're correct - using SQL
2005, it is now possible to use merge replication over HTTPS. Although you
need to be comfortable issuing certificates, maintaining virtual directories
etc, the guidelines in BOL are quite thorough and helpful. There is a
mistake in some documentation, and HTTP is not supported, so you have to
deal with certificates at some stage. Have a look at BOL for the full info,
and if you have any questions, probably the best place for the moment is the
beta newsgroups:
http://communities.microsoft.com/new...2005&slcid=us.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I see. Well they (Microsoft) keep to do things complicated.
I mean you need IIS, sertificates, http and ftp not supported and so on...
Well this is step forward but..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eUAEsp0ZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Grigoris,
> I have set this up in a test environment and you're correct - using SQL
> 2005, it is now possible to use merge replication over HTTPS. Although you
> need to be comfortable issuing certificates, maintaining virtual
> directories etc, the guidelines in BOL are quite thorough and helpful.
> There is a mistake in some documentation, and HTTP is not supported, so
> you have to deal with certificates at some stage. Have a look at BOL for
> the full info, and if you have any questions, probably the best place for
> the moment is the beta newsgroups:
> http://communities.microsoft.com/new...2005&slcid=us.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
Simon
But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:

> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>
|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:

> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon
|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
|||I guess I understated that some. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:

> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views
?
Si
"Simon" wrote:

> In SQL 2005 I have a server with a username of JM. This username created a
nd
> maintains a database. When I login via query analyser I can`t run queries
on
> a table unless I qualify it JM.tablename. How do I alter the login to ensu
re
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>|||I guess I understated that some. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> > In SQL 2005 I have a server with a username of JM. This username created
> > and
> > maintains a database. When I login via query analyser I can`t run queries
> > on
> > a table unless I qualify it JM.tablename. How do I alter the login to
> > ensure
> > i can query just on tablename ?
> >
> > Simon
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
--
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run queries
>> on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>|||I guess I understated that some:). I always tell people to qualify objects
no matter what as well.
--
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>> It is a good idea to always schema qualify all objects anyway.
>> --
>> Andrew J. Kelly SQL MVP
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run
>> queries on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>>
>

2005 Fastest communications

Hey,

We do 99.9% inserts. We have a C++ app that communicates with the DBs via ODBC.

Using Sql 2000, it works well. We have another faster server to test with Sql 2005. For some reason, 2005 does not come close to handling the inserts as well as 2000. I've done the statistics and other 2005 config changes. Both boxes are quads with hyper threading. I know it's not the box b/c the 2005 box ran great with Sql 2000.

Is there any reason Sql 2005 would not communicate with our app as well as Sql 2000 would? I'm running out of things to consider. I've done the MAX DOP, etc, it has not mattered. It doesn't compare. We do very rapid inserting.

Thanks!

If you are using same client stack against different servers (2005 vs. 2000), engine guys might find an answer to your question.

2005 Endpoints question.........

We have a in house web app that will need to consume a web service via
javascript, so using 2005's new feature, I created an endpoint &
exposed a stored procedure to use the Native XML web service
functionality. The procedure works fine, and I've tested behind our
firewall. But the database sits in a highly protected domain, behind a
firewall. The web app will sit outside the firewall, in the DMZ. My
questions are...
A) I'm assuming we'll need to open the firewall to communication on the
port specified in the endpoint for the web app to communicate with?
Any security risks here?
B) Who do I grant connect on the endpoint to? The web app is not in
the same domain, so I can't give the connect permission to a domain
user.'
C) More javascript related than SQL....Javascript runs on the client
right? So will all the web browsers out there be attempting to execute
this web service call? Or is it my web server trying to execute the
web service call?Sorry to repost, but what do folks think?
CoreyB wrote:
> We have a in house web app that will need to consume a web service via
> javascript, so using 2005's new feature, I created an endpoint &
> exposed a stored procedure to use the Native XML web service
> functionality. The procedure works fine, and I've tested behind our
> firewall. But the database sits in a highly protected domain, behind a
> firewall. The web app will sit outside the firewall, in the DMZ. My
> questions are...
> A) I'm assuming we'll need to open the firewall to communication on the
> port specified in the endpoint for the web app to communicate with?
> Any security risks here?
> B) Who do I grant connect on the endpoint to? The web app is not in
> the same domain, so I can't give the connect permission to a domain
> user.'
> C) More javascript related than SQL....Javascript runs on the client
> right? So will all the web browsers out there be attempting to execute
> this web service call? Or is it my web server trying to execute the
> web service call?|||You will have to open the firewall on the port that is specified for your
HTTP endpoint. The only security risk is in being able to execute the
procedures you are exposing as well as Denial of Service. Other than that,
users can not get to anything else...provided you have ensured things are
locked down otherwise.
You grant connect authority to the user that will be connecting to the
endpoint. That can be a physical user or done via certificate exchange.
I have no idea on the Javascript question.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1144370588.206677.316450@.j33g2000cwa.googlegroups.com...
> We have a in house web app that will need to consume a web service via
> javascript, so using 2005's new feature, I created an endpoint &
> exposed a stored procedure to use the Native XML web service
> functionality. The procedure works fine, and I've tested behind our
> firewall. But the database sits in a highly protected domain, behind a
> firewall. The web app will sit outside the firewall, in the DMZ. My
> questions are...
> A) I'm assuming we'll need to open the firewall to communication on the
> port specified in the endpoint for the web app to communicate with?
> Any security risks here?
> B) Who do I grant connect on the endpoint to? The web app is not in
> the same domain, so I can't give the connect permission to a domain
> user.'
> C) More javascript related than SQL....Javascript runs on the client
> right? So will all the web browsers out there be attempting to execute
> this web service call? Or is it my web server trying to execute the
> web service call?
>

2005 + Alias not working

I've just built our new 2005 cluster and I'm having some issues with
connectivity via a client network alias. It's a 2 node active/active 64 bit
Ent Edit, sp1, 32gb ram. Both nodes are listening on port 1433 with both tcp
and named pipes (although we never use named pipes)
Node Names are:
vdb1
vdb2\ins2
Most everything is working like a champ. Except setting up a local alias
from my machine.
I setup an alias that connects to vdb2\ins2. The alias name is vdb2, port is
1433. I cannot get sqlwb to connect via the alias.
Error message pop up is "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 no
t
allow remote connections. (Provider: TCP Provider, error: 0 - No Connection
could be made because the target machine actively refused it) (Microsoft SQL
Server, Error 10061).
I can connect directly to vbdb2\ins2 (not using the alias) without failure.
The reason why this is important is I have lots of linked server code on
vdb1 that references vdb2.dbname.dbo.tablename. I would have to touch a lo
t
of code (probably 100 stored procs) to be [vdb2\ins2].dbname.dbo.tablena
me.
The aliases are working in 2000 on our current production boxes, but it
doesn't work on 2005. Any ideas?I am having this exact same problem. Does anyone have any ideas'
*** Sent via Developersdex http://www.codecomments.com ***

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.