Showing posts with label environment. Show all posts
Showing posts with label environment. Show all posts

Friday, March 30, 2012

Problem with linked server

Here's the situation: My production environment has two SQL Servers 2000,
call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
On S1, I have created a linked server for S2. Thus, on S1, I can write:
SELECT * FROM S2.myDB.dbo.myTable
and everything is happy.
My development environment is slightly different. I have a single machine
running a single instance of SQL Server 2000 (acually, MSDE). I have made
local copies of the tables from both S1 and S2 in my local database. In
order to be able to copy, without modification, the stored procedures I
write on my development machine, I need to be able to access the tables in
my database as either:
myDB.dbo.myTable
OR
S2.myDB.dbo.myTable
To this end, I created a linked database on my development machine, but
instead of selecting type SQL Server (which forces the linked server name
and the hostname to be the same), I selected Microsoft OLE DB provider for
SQL Server (which allows specifying different hostname and data source
names). I specified my hostname as the data source and named the linked
server S2. SQL server dutifully made the linked server. Note that I have
confirmed the creation of the linked server by running sp_helpserver, which
does show both the local server (identified by the machine name) and the
linked server (identified by the assigned name) [but see more info below].
Despite this seeming success, I have been unable to do anything with this
linked server. Under the security tab, I selected 'Be made using this
security context', and entered 'sa' and its password. I then ran the query:
SELECT * FROM S2.someDB.dbo.myTable
and received: SQL Server does not exist or access denied.
Some additional information:
1- On the development machine, sqlservr runs under the SYSTEM context
2- Looking in the table master.dbo.sysservers, I saw the following
srvname datasource srvnetname
S1 S1 S1
S2 S2 S2
when running the query, it takes a while to respond, thus I am thinking that
it is searching the net for a server named S2 instead of using itself as the
datasource. If this is so, what can I do differently to resolve this
problem?
Gerry Roston
Pair of Docs Consulting
gerry@.pairofdocs.netGerald,
quote:

>what can I do differently to resolve this problem?

Isn't a second instance for the dev box the most logical, desirable
solution?
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
quote:

> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,

which
quote:

> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the

query:
quote:

> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking

that
quote:

> it is searching the net for a server named S2 instead of using itself as

the
quote:

> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>
|||Problem resolved by using sp_addlinkedserver instead of Enterprise Manager.
I suspect that the problem is due to a bug in the documentation and a bug in
Enterprise Manager. Reviewing the document shows that the string 'SQL
Server' can be used as the product_name along with SQLOLEDB as the
provider_name. Trying this with sp_addlinkedserver yields the error message:
You cannot specify a provider or any properties for product 'SQL Server'.
but doing the same from Enterprise Manager results in success, even though
the entry in master.dbo.sysservers is incorrect.
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
quote:

> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,

which
quote:

> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the

query:
quote:

> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking

that
quote:

> it is searching the net for a server named S2 instead of using itself as

the
quote:

> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>

Problem with linked server

Here's the situation: My production environment has two SQL Servers 2000,
call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
On S1, I have created a linked server for S2. Thus, on S1, I can write:
SELECT * FROM S2.myDB.dbo.myTable
and everything is happy.
My development environment is slightly different. I have a single machine
running a single instance of SQL Server 2000 (acually, MSDE). I have made
local copies of the tables from both S1 and S2 in my local database. In
order to be able to copy, without modification, the stored procedures I
write on my development machine, I need to be able to access the tables in
my database as either:
myDB.dbo.myTable
OR
S2.myDB.dbo.myTable
To this end, I created a linked database on my development machine, but
instead of selecting type SQL Server (which forces the linked server name
and the hostname to be the same), I selected Microsoft OLE DB provider for
SQL Server (which allows specifying different hostname and data source
names). I specified my hostname as the data source and named the linked
server S2. SQL server dutifully made the linked server. Note that I have
confirmed the creation of the linked server by running sp_helpserver, which
does show both the local server (identified by the machine name) and the
linked server (identified by the assigned name) [but see more info below].
Despite this seeming success, I have been unable to do anything with this
linked server. Under the security tab, I selected 'Be made using this
security context', and entered 'sa' and its password. I then ran the query:
SELECT * FROM S2.someDB.dbo.myTable
and received: SQL Server does not exist or access denied.
Some additional information:
1- On the development machine, sqlservr runs under the SYSTEM context
2- Looking in the table master.dbo.sysservers, I saw the following
srvname datasource srvnetname
S1 S1 S1
S2 S2 S2
when running the query, it takes a while to respond, thus I am thinking that
it is searching the net for a server named S2 instead of using itself as the
datasource. If this is so, what can I do differently to resolve this
problem?
--
Gerry Roston
Pair of Docs Consulting
gerry@.pairofdocs.netYou could try adding a host entry for the linked server
with the same ip address as the development server using a
different name.
i.e.
192.168.1.1 development
192.168.1.1 Dev_Link
This will allow you to add the linked server as another
sql server.
Shane
>--Original Message--
>Here's the situation: My production environment has two
SQL Servers 2000,
>call them S1 and S2. S1 also hosts IIS and my ASP.NET
application.
>On S1, I have created a linked server for S2. Thus, on
S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
>and everything is happy.
>My development environment is slightly different. I have
a single machine
>running a single instance of SQL Server 2000 (acually,
MSDE). I have made
>local copies of the tables from both S1 and S2 in my
local database. In
>order to be able to copy, without modification, the
stored procedures I
>write on my development machine, I need to be able to
access the tables in
>my database as either:
>myDB.dbo.myTable
>OR
>S2.myDB.dbo.myTable
>To this end, I created a linked database on my
development machine, but
>instead of selecting type SQL Server (which forces the
linked server name
>and the hostname to be the same), I selected Microsoft
OLE DB provider for
>SQL Server (which allows specifying different hostname
and data source
>names). I specified my hostname as the data source and
named the linked
>server S2. SQL server dutifully made the linked server.
Note that I have
>confirmed the creation of the linked server by running
sp_helpserver, which
>does show both the local server (identified by the
machine name) and the
>linked server (identified by the assigned name) [but see
more info below].
>Despite this seeming success, I have been unable to do
anything with this
>linked server. Under the security tab, I selected 'Be
made using this
>security context', and entered 'sa' and its password. I
then ran the query:
>SELECT * FROM S2.someDB.dbo.myTable
>and received: SQL Server does not exist or access denied.
>Some additional information:
>1- On the development machine, sqlservr runs under the
SYSTEM context
>2- Looking in the table master.dbo.sysservers, I saw the
following
>srvname datasource srvnetname
>S1 S1 S1
>S2 S2 S2
>when running the query, it takes a while to respond, thus
I am thinking that
>it is searching the net for a server named S2 instead of
using itself as the
>datasource. If this is so, what can I do differently to
resolve this
>problem?
>--
>Gerry Roston
>Pair of Docs Consulting
>gerry@.pairofdocs.net
>
>.
>|||Gerald,
>what can I do differently to resolve this problem?
Isn't a second instance for the dev box the most logical, desirable
solution?
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,
which
> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the
query:
> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking
that
> it is searching the net for a server named S2 instead of using itself as
the
> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>|||Problem resolved by using sp_addlinkedserver instead of Enterprise Manager.
I suspect that the problem is due to a bug in the documentation and a bug in
Enterprise Manager. Reviewing the document shows that the string 'SQL
Server' can be used as the product_name along with SQLOLEDB as the
provider_name. Trying this with sp_addlinkedserver yields the error message:
You cannot specify a provider or any properties for product 'SQL Server'.
but doing the same from Enterprise Manager results in success, even though
the entry in master.dbo.sysservers is incorrect.
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,
which
> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the
query:
> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking
that
> it is searching the net for a server named S2 instead of using itself as
the
> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>

Tuesday, March 20, 2012

Problem with Forecasting with Excel 2007 DM Add-in using drill-through data from SSAS 2005!

I’m having a problem with Excel 2007 DM and SQL 2005 and I hope someone out there has a solution.

Consider the following environment:

Windows XP SP2 or Windows Vista, Excel 2007, Data Mining Add-in, SSAS 2005 (with session mining models enabled, an AdventureWorksDW cube deployed and drill-through actions available).

Now take the following steps:

1. In Excel 2007 set up a connection to SSAS

2. Connect to the cube and create a new pivot table report (drag and drop whatever you like)

3. Right-click on one of the cell values in the data region and either select a drill-through action, or, select Show Details in the context menu

4. Ensure that you have at least 10 detailed records that are generated on a new worksheet page; you should have a time-based column in your detailed records

5. Select the table of detailed data, then select the Analyze tab (within the Table Tools grouping) which appears in the topmost menu above the ribbon

6. Click the Forecast button in the ribbon and choose both the field which you want to predict as well as the time-based column (from step 4) as well as the number of time periods to forecast

7. Finally click OK.

1. Having followed these steps on both WinXP SP2 and Vista, I keep coming across the exception: HResult:0x800A03EC. Any ideas as to why this exception pops up? If I was using a normal table of data (which was not generated from a Show Details or drill-through action), then the Forecast button works fine.

I googled it and thought the localization settings for SSAS 2005 and Excel 2007 needed to be the same (initially they weren’t). I’ve tried removing the auto-filters which appear atop each column in the detailed data table prior to clicking the Forecast button, and, I’ve also tested for a series of data across a number of time periods with the same result.

Also, a colleague of mine discovered that the column headers that appear by default from a drill-through start with "$[", and, in removing them the Forecast function appears to work.

I would have thought there would be a seamless transition in Excel 2007 between data retrieved from a cube and the DM Add-in featueres (or at the very least, a more meaningful exception message than the one presented).

Is there something I’ve missed, or, is there a KB article I haven’t come across yet? As I know for a fact that the problem is reproducible, is there a fix to this problem on its way to us? Is there a useful workaround that doesn't require manual intervention?

Thanks,

A.

Thanks for the detailed repro - we'll look into it.|||

We have fixed this for future versions of the add-in.

To workaround the bug, please remove [ and ] from the column names.

Thanks a lot for the detailed repro.

Problem with Forecasting with Excel 2007 DM Add-in using drill-through data from SSAS 2005!

I’m having a problem with Excel 2007 DM and SQL 2005 and I hope someone out there has a solution.

Consider the following environment:

Windows XP SP2 or Windows Vista, Excel 2007, Data Mining Add-in, SSAS 2005 (with session mining models enabled, an AdventureWorksDW cube deployed and drill-through actions available).

Now take the following steps:

1. In Excel 2007 set up a connection to SSAS

2. Connect to the cube and create a new pivot table report (drag and drop whatever you like)

3. Right-click on one of the cell values in the data region and either select a drill-through action, or, select Show Details in the context menu

4. Ensure that you have at least 10 detailed records that are generated on a new worksheet page; you should have a time-based column in your detailed records

5. Select the table of detailed data, then select the Analyze tab (within the Table Tools grouping) which appears in the topmost menu above the ribbon

6. Click the Forecast button in the ribbon and choose both the field which you want to predict as well as the time-based column (from step 4) as well as the number of time periods to forecast

7. Finally click OK.

1. Having followed these steps on both WinXP SP2 and Vista, I keep coming across the exception: HResult:0x800A03EC. Any ideas as to why this exception pops up? If I was using a normal table of data (which was not generated from a Show Details or drill-through action), then the Forecast button works fine.

I googled it and thought the localization settings for SSAS 2005 and Excel 2007 needed to be the same (initially they weren’t). I’ve tried removing the auto-filters which appear atop each column in the detailed data table prior to clicking the Forecast button, and, I’ve also tested for a series of data across a number of time periods with the same result.

Also, a colleague of mine discovered that the column headers that appear by default from a drill-through start with "$[", and, in removing them the Forecast function appears to work.

I would have thought there would be a seamless transition in Excel 2007 between data retrieved from a cube and the DM Add-in featueres (or at the very least, a more meaningful exception message than the one presented).

Is there something I’ve missed, or, is there a KB article I haven’t come across yet? As I know for a fact that the problem is reproducible, is there a fix to this problem on its way to us? Is there a useful workaround that doesn't require manual intervention?

Thanks,

A.

Thanks for the detailed repro - we'll look into it.|||

We have fixed this for future versions of the add-in.

To workaround the bug, please remove [ and ] from the column names.

Thanks a lot for the detailed repro.

Problem with Forecasting with Excel 2007 DM Add-in using drill-through data from SSAS 2005!

I’m having a problem with Excel 2007 DM and SQL 2005 and I hope someone out there has a solution.

Consider the following environment:

Windows XP SP2 or Windows Vista, Excel 2007, Data Mining Add-in, SSAS 2005 (with session mining models enabled, an AdventureWorksDW cube deployed and drill-through actions available).

Now take the following steps:

1. In Excel 2007 set up a connection to SSAS

2. Connect to the cube and create a new pivot table report (drag and drop whatever you like)

3. Right-click on one of the cell values in the data region and either select a drill-through action, or, select Show Details in the context menu

4. Ensure that you have at least 10 detailed records that are generated on a new worksheet page; you should have a time-based column in your detailed records

5. Select the table of detailed data, then select the Analyze tab (within the Table Tools grouping) which appears in the topmost menu above the ribbon

6. Click the Forecast button in the ribbon and choose both the field which you want to predict as well as the time-based column (from step 4) as well as the number of time periods to forecast

7. Finally click OK.

1. Having followed these steps on both WinXP SP2 and Vista, I keep coming across the exception: HResult:0x800A03EC. Any ideas as to why this exception pops up? If I was using a normal table of data (which was not generated from a Show Details or drill-through action), then the Forecast button works fine.

I googled it and thought the localization settings for SSAS 2005 and Excel 2007 needed to be the same (initially they weren’t). I’ve tried removing the auto-filters which appear atop each column in the detailed data table prior to clicking the Forecast button, and, I’ve also tested for a series of data across a number of time periods with the same result.

Also, a colleague of mine discovered that the column headers that appear by default from a drill-through start with "$[", and, in removing them the Forecast function appears to work.

I would have thought there would be a seamless transition in Excel 2007 between data retrieved from a cube and the DM Add-in featueres (or at the very least, a more meaningful exception message than the one presented).

Is there something I’ve missed, or, is there a KB article I haven’t come across yet? As I know for a fact that the problem is reproducible, is there a fix to this problem on its way to us? Is there a useful workaround that doesn't require manual intervention?

Thanks,

A.

Thanks for the detailed repro - we'll look into it.|||

We have fixed this for future versions of the add-in.

To workaround the bug, please remove [ and ] from the column names.

Thanks a lot for the detailed repro.

Friday, March 9, 2012

Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
--
mfg
Marc EggenbergerSomeone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Member.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is what
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:
> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>

Wednesday, March 7, 2012

problem with distributed trans 2000 to 2005

I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...

Linked Server name: SQL2005

From the SQL Server 2000 instance in Query Analyzer...

-- this works fine
Delete from [SQL2005].myDB.dbo.myTbl

-- this just hangs
Begin Tran
Delete from [SQL2005].myDB.dbo.myTbl

It doesn't make any difference what provider I use on the Linked Server.

I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.

We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.

Any suggestions?

CilvePerhaps your DTC service is not running.

I have these 2 related links:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1
http://support.microsoft.com/kb/899191

By the way, if you intend to migrate for an old SQL2000 DB to a new SQL2005 DB, why not to backup the old one and restore it on your SQL2005 server?|||The DTC service is runnnig on both servers.

Actually, the problem isn't limited to Deletes within a distributed transaction... The problem is the distributed transaction itself. I repeated the test doing an insert across the servers. The insert works on its own but within a distributed transaction, it hangs.

I have restored the database onto the SQL Server 2005 box to migrate them. I was refering to application DTS packages not working on the 2000 server because of the distributed transaction problem.

Thanks,
Clive