Item not linked to dataset, and "table1" issue

Hi--

I have a report project in VS (2003). I have a report in that project that was working properly. I copied objects from that report to a new one and created a new dataset for the new report (under it's Data tab). Though both datasets (for each of the reports) seem healthy, i.e., they produce data when run, I am now experiencing the following problems:

1. In both reports, the datasets seem to be unlinked, somehow. I know this by right clicking on a field of the report, going to expressions, then selecting the Fields node. Instead of listing fields, as used to be the case, it now says "Report item not linked to a dataset."

2. If I attempt to preview either of the reports, I get an error that reads, "The table 'table1' refers to an invalid dataset name 'MedMgmt.' MedMgmt is the dataset name of the first report. I changed it to another name, but I still get the same message.

I have clicked and right-clicked everywhere and have not found a way to re-link the dataset to the report. Why Reporting Services can't figure out that the dataset in the report's Data tab is the dataset to link to is a point of deep annoyance, but whatever... If anyone can please help me solve these problems, I would be very grateful! Thank you!

I have exactly the same problem. Only way around it (for me) so far has been to build the report all over and let the wizard link it, then copy the elements 1 by 1 from original... real pain...

There's GOT to be a better way?

|||

I think i know what it is. What is this object you are looking to link? If its a table, List, or Matrix : Right click the object (make sure the whole object is selected and click properties. if properties slides out Scroll down to "Dataset Name", there should be a drop down which all the datasets in your report. Select the one you are grabbing you attributes from. The reason your getting that error is, the object is linked to dataset names from your other report (since you copied them over). So you need to change the dataset names for each object. This is why it shows the name of your dataset from your other report.

IF the properties box opens up for the table/matrix/list , then you should see (3 textboxes down) "Dataset Name". if its empty or has something in it...i would click the dropdown anyways, and make sure the right dataset it selected.

I hope this helps!

Item not linked to dataset, and "table1" issue

Hi--

I have a report project in VS (2003). I have a report in that project that was working properly. I copied objects from that report to a new one and created a new dataset for the new report (under it's Data tab). Though both datasets (for each of the reports) seem healthy, i.e., they produce data when run, I am now experiencing the following problems:

1. In both reports, the datasets seem to be unlinked, somehow. I know this by right clicking on a field of the report, going to expressions, then selecting the Fields node. Instead of listing fields, as used to be the case, it now says "Report item not linked to a dataset."

2. If I attempt to preview either of the reports, I get an error that reads, "The table 'table1' refers to an invalid dataset name 'MedMgmt.' MedMgmt is the dataset name of the first report. I changed it to another name, but I still get the same message.

I have clicked and right-clicked everywhere and have not found a way to re-link the dataset to the report. Why Reporting Services can't figure out that the dataset in the report's Data tab is the dataset to link to is a point of deep annoyance, but whatever... If anyone can please help me solve these problems, I would be very grateful! Thank you!

I have exactly the same problem. Only way around it (for me) so far has been to build the report all over and let the wizard link it, then copy the elements 1 by 1 from original... real pain...

There's GOT to be a better way?

|||

I think i know what it is. What is this object you are looking to link? If its a table, List, or Matrix : Right click the object (make sure the whole object is selected and click properties. if properties slides out Scroll down to "Dataset Name", there should be a drop down which all the datasets in your report. Select the one you are grabbing you attributes from. The reason your getting that error is, the object is linked to dataset names from your other report (since you copied them over). So you need to change the dataset names for each object. This is why it shows the name of your dataset from your other report.

IF the properties box opens up for the table/matrix/list , then you should see (3 textboxes down) "Dataset Name". if its empty or has something in it...i would click the dropdown anyways, and make sure the right dataset it selected.

I hope this helps!

issues in Linked server to Informix IDS

I have used the ole db services for odbc to utilise a informix odbc driver t
o
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.Look up in SQL Server Books Online Linked Servers OLEDB Provider Properties.
If you misset these, then SQL Server will have to copy all data to a temp
table before applying any filters and/or rollup aggregations. Basically,
you want to offload as much work as possible to the data provider for which
you are extracting data.
To modify these properties, initiate the creation of a new linked server for
the same OLEDB provider type, then select the provider properties button.
After setting the properties, and saving, then you can cancel the creation
of that specific linked server.
Provider properties are set server-wide for linked servers of that provider
type.
Keep in mind, however, that if you ever join a table from a linked server
and the local server, there will have to be some sort of data copy into a
temp table in order to hash the join. There is no way around this as the
data must be gathered collectively to a single host to process. You want to
process as much pre-filtering on the invidual host prior to joining the
remaining result sets.
Sincerely,
Anthony Thomas
"CPiO" <CPiO@.discussions.microsoft.com> wrote in message
news:76C6DE2C-F066-41A3-9245-39B3742DB9B2@.microsoft.com...
I have used the ole db services for odbc to utilise a informix odbc driver
to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.|||Hi Anthony,
Thankyou for your reply, as suggested I have tried modding the properties
behind the ole provider for odbc but this has made no difference.
All that we are doing is creating a view inside a SQL dbase which is looking
at a table in the IDS system.
THEN
We are then firing a selective query to that view where we are seeing 3-4
mins of wait time. Again access into that table is fine via query and odbc.
Any more ideas would be greatly apreciated.
James
"CPiO" wrote:

> I have used the ole db services for odbc to utilise a informix odbc driver
to
> create my linked server in SQL server. The Informix IDS server in questio
n
> is v9.4 on Suse Linux Enterprise V8.
> If I use the informix ODBC via Query in Excel I can retrieve data from the
> IDS databases with no problem, it is very quick.
> If I create a view in SQL server (which uses the linked server) to look at
> the same data in the IDS databases the same data will take an avaerage of
> 50seconds to come back.
> I am lost, please advise.
> Could it be the way in which I have setup the linked server, msdtc, tempdb
> etc etc.

issues in Linked server to Informix IDS

I have used the ole db services for odbc to utilise a informix odbc driver to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.
Look up in SQL Server Books Online Linked Servers OLEDB Provider Properties.
If you misset these, then SQL Server will have to copy all data to a temp
table before applying any filters and/or rollup aggregations. Basically,
you want to offload as much work as possible to the data provider for which
you are extracting data.
To modify these properties, initiate the creation of a new linked server for
the same OLEDB provider type, then select the provider properties button.
After setting the properties, and saving, then you can cancel the creation
of that specific linked server.
Provider properties are set server-wide for linked servers of that provider
type.
Keep in mind, however, that if you ever join a table from a linked server
and the local server, there will have to be some sort of data copy into a
temp table in order to hash the join. There is no way around this as the
data must be gathered collectively to a single host to process. You want to
process as much pre-filtering on the invidual host prior to joining the
remaining result sets.
Sincerely,
Anthony Thomas

"CPiO" <CPiO@.discussions.microsoft.com> wrote in message
news:76C6DE2C-F066-41A3-9245-39B3742DB9B2@.microsoft.com...
I have used the ole db services for odbc to utilise a informix odbc driver
to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.
|||Hi Anthony,
Thankyou for your reply, as suggested I have tried modding the properties
behind the ole provider for odbc but this has made no difference.
All that we are doing is creating a view inside a SQL dbase which is looking
at a table in the IDS system.
THEN
We are then firing a selective query to that view where we are seeing 3-4
mins of wait time. Again access into that table is fine via query and odbc.
Any more ideas would be greatly apreciated.
James
"CPiO" wrote:

> I have used the ole db services for odbc to utilise a informix odbc driver to
> create my linked server in SQL server. The Informix IDS server in question
> is v9.4 on Suse Linux Enterprise V8.
> If I use the informix ODBC via Query in Excel I can retrieve data from the
> IDS databases with no problem, it is very quick.
> If I create a view in SQL server (which uses the linked server) to look at
> the same data in the IDS databases the same data will take an avaerage of
> 50seconds to come back.
> I am lost, please advise.
> Could it be the way in which I have setup the linked server, msdtc, tempdb
> etc etc.

issues in Linked server to Informix IDS

I have used the ole db services for odbc to utilise a informix odbc driver to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.Look up in SQL Server Books Online Linked Servers OLEDB Provider Properties.
If you misset these, then SQL Server will have to copy all data to a temp
table before applying any filters and/or rollup aggregations. Basically,
you want to offload as much work as possible to the data provider for which
you are extracting data.
To modify these properties, initiate the creation of a new linked server for
the same OLEDB provider type, then select the provider properties button.
After setting the properties, and saving, then you can cancel the creation
of that specific linked server.
Provider properties are set server-wide for linked servers of that provider
type.
Keep in mind, however, that if you ever join a table from a linked server
and the local server, there will have to be some sort of data copy into a
temp table in order to hash the join. There is no way around this as the
data must be gathered collectively to a single host to process. You want to
process as much pre-filtering on the invidual host prior to joining the
remaining result sets.
Sincerely,
Anthony Thomas
"CPiO" <CPiO@.discussions.microsoft.com> wrote in message
news:76C6DE2C-F066-41A3-9245-39B3742DB9B2@.microsoft.com...
I have used the ole db services for odbc to utilise a informix odbc driver
to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.|||Hi Anthony,
Thankyou for your reply, as suggested I have tried modding the properties
behind the ole provider for odbc but this has made no difference.
All that we are doing is creating a view inside a SQL dbase which is looking
at a table in the IDS system.
THEN
We are then firing a selective query to that view where we are seeing 3-4
mins of wait time. Again access into that table is fine via query and odbc.
Any more ideas would be greatly apreciated.
James
"CPiO" wrote:
> I have used the ole db services for odbc to utilise a informix odbc driver to
> create my linked server in SQL server. The Informix IDS server in question
> is v9.4 on Suse Linux Enterprise V8.
> If I use the informix ODBC via Query in Excel I can retrieve data from the
> IDS databases with no problem, it is very quick.
> If I create a view in SQL server (which uses the linked server) to look at
> the same data in the IDS databases the same data will take an avaerage of
> 50seconds to come back.
> I am lost, please advise.
> Could it be the way in which I have setup the linked server, msdtc, tempdb
> etc etc.

Issues copying data to linked server

I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.

I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):

Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:

EXEC procStmtDataToReno 'B'

and some snippets of the SP:

CREATE PROCEDURE [dbo].[procStmtDataToReno]

@.CoCode as varchar(2)

AS

--various declarations and such

DELETE
FROM [10.1.1.4].OnlineAR.dbo.StatementData
WHERE CompanyID = @.CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )

--code to build temp table

INSERT INTO [10.1.1.4].OnlineAR.dbo.StatementData
SELECT * FROM #Statement
GO

TIAI normally would create a stored procedure on the remote server side and call it via openquery() or sp_executesql. That is the *fastest* way to do and DML.

Anyway, I suggest you take a look at the following article for guidance on resolving 7391 error.

http://support.microsoft.com/kb/306212|||Thanks for that. Because some companies worked, my suspicion all along was that it was "data" related rather than "process" related. I finally stumbled into the answer late last night. I had tried changing the SP to simply select the records, and it ran fine from Query Analyzer. I changed the SP back to an INSERT and executed it again from QA, and it failed, but I finally got a message that made sense:

Server: Msg 8152, Level 16, State 4, Procedure procStmtDataToReno, Line 389
String or binary data would be truncated.
The statement has been terminated.

My problem was as simple as tracking down the field that was wider on the source side than it was on the destination side. The process now works fine. I just didnt know what tool to use to get better info on what was going wrong.

I am interested in your recommendation to set it up on the remote side. I would have thought it was better to have the SP on the source side, so it would only have to transfer the results of the process over the wire. If the SP were on the destination side, wouldnt it have to bring all the data across to work with?|||yes, you have to pass the data to the remote server in order for it to be processed.

e.g.

@.sql='exec my_sp ' + @.inputvar

exec linked.db..sp_executesql @.sql

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody ha
s
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal
.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine ca
nnot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my deskto
p
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file
,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody h
as
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great dea
l.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through N
T
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through
NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa'
,
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server
)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
Christine
A few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>
|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>
|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:
>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:
> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have a linked server setup to connect to an access file from a filer
> >server. If I am on the server computer logged in using Windows NT
> >authencation, the connections works. However, if I use a native SQL login
> >(with sys admin rights) it gave the following error:
> >
> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
> >
> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> >
> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
> >open the file '\\filer-la03p\Mbs\Credit Mortgage
> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
> >exclusively by another user, or you need permission to view its data.]
> >
> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> >IDBInitialize::Initialize returned 0x80004005: ].
> >
> >The linked server has security mapping as remote user:"admin" with password
> >null.
> >I also have noticed that if I connect to the server using EM from my desktop
> >using NT authencation, the error ocurrs as well. In this case I will say it
> >is a "double-hopping" issue. But why when using a native SQL account will
> >have a problem?
> >
> >From my understanding when using linked server to connect to an access file,
> >it is using the service account that starts SQL Server. I had setup all
> >privilages required for the SQL Service account. And as I inicated earlier,
> >it connects ok if I am at the server using NT authencation to log in.
> >
> >I rearch online and it seems like I am not the only one who has problems
> >with linked server for access from other computers. Unfortunately, nobody has
> >given the resolutions that works for the requester.
> >
> >Someone please help, we are a microsoft shop and everything is microsoft.
> >This problem has stopped our development for the business units a great deal.
> >Our clients were just bought in to convert a lot of their stuff to SQL
> >Server. Now they are going to back off!
> >
> >Thanks if someone can respond quickly.
> >
> >Christine
> >
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>> A few suggestions:
>> --Make sure that the Access mdb (and mdw) reside on a file share that
>> the Windows account has read/write/delete ACLs on for the files and
>> folders.
>> --Make sure that the Access mdb is totally unsecured (check the owner
>> property -- it should NOT say 'unknown').
>> --Make sure that the login you are using is Admin with an empty string
>> (''), not null.
>> --Use filemon (www.sysinternals.com) to check to see if SQLS is
>> getting access to the files/folders.
>> FWIW, linked servers to Access are a real PITA. You'd have a much
>> easier time if you worked it the other way around, linking to SQL
>> Server from the Access .mdb.
>> --Mary
>> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
>> C@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >I have a linked server setup to connect to an access file from a filer
>> >server. If I am on the server computer logged in using Windows NT
>> >authencation, the connections works. However, if I use a native SQL login
>> >(with sys admin rights) it gave the following error:
>> >
>> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>> >
>> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>> >
>> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>> >open the file '\\filer-la03p\Mbs\Credit Mortgage
>> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>> >exclusively by another user, or you need permission to view its data.]
>> >
>> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>> >IDBInitialize::Initialize returned 0x80004005: ].
>> >
>> >The linked server has security mapping as remote user:"admin" with password
>> >null.
>> >I also have noticed that if I connect to the server using EM from my desktop
>> >using NT authencation, the error ocurrs as well. In this case I will say it
>> >is a "double-hopping" issue. But why when using a native SQL account will
>> >have a problem?
>> >
>> >From my understanding when using linked server to connect to an access file,
>> >it is using the service account that starts SQL Server. I had setup all
>> >privilages required for the SQL Service account. And as I inicated earlier,
>> >it connects ok if I am at the server using NT authencation to log in.
>> >
>> >I rearch online and it seems like I am not the only one who has problems
>> >with linked server for access from other computers. Unfortunately, nobody has
>> >given the resolutions that works for the requester.
>> >
>> >Someone please help, we are a microsoft shop and everything is microsoft.
>> >This problem has stopped our development for the business units a great deal.
>> >Our clients were just bought in to convert a lot of their stuff to SQL
>> >Server. Now they are going to back off!
>> >
>> >Thanks if someone can respond quickly.
>> >
>> >Christine
>> >
>>

Issue gettin a sql agent task to stop.

Russell,
do any of the steps invoke DTC or use linked servers?
Russell wrote:
> I Have a daily task that runs a stored procedure to import some orders us
ing
> a call to a third party exe, then to copy and rename a couple of logs and
> then use XPSMTP to send the logs to a couple of people. It has done all o
f
> it except the sending of the e-mail.
> I have noticed this morning that the run of the job from friday was still
> running. So I have killed the process which now says it is rolling back, s
o
> far it has been rolling back for 3 hours. and it does not do much data
> manipulation. If I use kill with status I get:
> SPID 61: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> But the rollback still runs, are ther any executables that SQL uses to run
> extended stored procedures that I could kill that might release this threa
d?
> Regards
> RussellThe job has 3 steps, the first runs a DTS which copies some rows from a
remote server, the second job executes an exe on remote server, the third
step runs a stored procedure which copies a file from a remote server and
renames it, also it runs an ISQL statement to put the output to a file,
lastly it e-mails the 2 files to myself and the finance director. The only
part of the whole process it has not done is to send the e-mail
"shane" wrote:

> Russell,
> do any of the steps invoke DTC or use linked servers?
>
> Russell wrote:
>|||I Have a daily task that runs a stored procedure to import some orders usin
g
a call to a third party exe, then to copy and rename a couple of logs and
then use XPSMTP to send the logs to a couple of people. It has done all of
it except the sending of the e-mail.
I have noticed this morning that the run of the job from friday was still
running. So I have killed the process which now says it is rolling back, so
far it has been rolling back for 3 hours. and it does not do much data
manipulation. If I use kill with status I get:
SPID 61: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
But the rollback still runs, are ther any executables that SQL uses to run
extended stored procedures that I could kill that might release this thread?
Regards
Russell|||Russell,
do any of the steps invoke DTC or use linked servers?
Russell wrote:
> I Have a daily task that runs a stored procedure to import some orders us
ing
> a call to a third party exe, then to copy and rename a couple of logs and
> then use XPSMTP to send the logs to a couple of people. It has done all o
f
> it except the sending of the e-mail.
> I have noticed this morning that the run of the job from friday was still
> running. So I have killed the process which now says it is rolling back, s
o
> far it has been rolling back for 3 hours. and it does not do much data
> manipulation. If I use kill with status I get:
> SPID 61: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> But the rollback still runs, are ther any executables that SQL uses to run
> extended stored procedures that I could kill that might release this threa
d?
> Regards
> Russell|||The job has 3 steps, the first runs a DTS which copies some rows from a
remote server, the second job executes an exe on remote server, the third
step runs a stored procedure which copies a file from a remote server and
renames it, also it runs an ISQL statement to put the output to a file,
lastly it e-mails the 2 files to myself and the finance director. The only
part of the whole process it has not done is to send the e-mail
"shane" wrote:

> Russell,
> do any of the steps invoke DTC or use linked servers?
>
> Russell wrote:
>|||Ive usually come across this problem when there is a distributed
transaction involved.
stop and restart MS DTC, and see if this helps
also try running a dbcc opentran on the database in question, to see if
there are any other transacations which may be interfering..
worst case scenario youll have to stop and restart the sql services
Russell wrote:[vbcol=seagreen]
> The job has 3 steps, the first runs a DTS which copies some rows from a
> remote server, the second job executes an exe on remote server, the third
> step runs a stored procedure which copies a file from a remote server and
> renames it, also it runs an ISQL statement to put the output to a file,
> lastly it e-mails the 2 files to myself and the finance director. The only
> part of the whole process it has not done is to send the e-mail
> "shane" wrote:
>|||Hi Shane,
There are no open transactions and I have restarted the DTC service. So it
looks like I will have to try stopping and starting SQL on the server.
"shane" wrote:

> Ive usually come across this problem when there is a distributed
> transaction involved.
> stop and restart MS DTC, and see if this helps
> also try running a dbcc opentran on the database in question, to see if
> there are any other transacations which may be interfering..
> worst case scenario youll have to stop and restart the sql services
>
> Russell wrote:
>|||Ive usually come across this problem when there is a distributed
transaction involved.
stop and restart MS DTC, and see if this helps
also try running a dbcc opentran on the database in question, to see if
there are any other transacations which may be interfering..
worst case scenario youll have to stop and restart the sql services
Russell wrote:[vbcol=seagreen]
> The job has 3 steps, the first runs a DTS which copies some rows from a
> remote server, the second job executes an exe on remote server, the third
> step runs a stored procedure which copies a file from a remote server and
> renames it, also it runs an ISQL statement to put the output to a file,
> lastly it e-mails the 2 files to myself and the finance director. The only
> part of the whole process it has not done is to send the e-mail
> "shane" wrote:
>|||Hi Shane,
There are no open transactions and I have restarted the DTC service. So it
looks like I will have to try stopping and starting SQL on the server.
"shane" wrote:

> Ive usually come across this problem when there is a distributed
> transaction involved.
> stop and restart MS DTC, and see if this helps
> also try running a dbcc opentran on the database in question, to see if
> there are any other transacations which may be interfering..
> worst case scenario youll have to stop and restart the sql services
>
> Russell wrote:
>