Hello,
I have got a very strange problem...
In VB6, in the beginning of my application, I make a
ListAvailableSQLServers to see what MSDE servers are on the network for
my application.
The problem is that if a computer is disconnected from the network, but
has MSDE working (a laptop) then the ListAvailableSQLServers will return
no server, even if there is a local server.
Is there something to do to have it?
PS : In VB6, I use
dim ListSvr as collection
Set ListSvr = SQLDMO.ListAvailableSQLServers
and the count is 0
I have also tried with
Dim oServer As New SQLDMO.SQLServer2
Dim oNameList As SQLDMO.NameList
Set oNameList = oServer.Application.ListAvailableSQLServers '
and oNameList.Count is still 0
Can you please help me?
Thank you
Marc Allard
Allcomp
hi Marc,
"Allcomp" <marc@.nospam.allcomp.be> ha scritto nel messaggio
news:41d95753$0$329$ba620e4c@.news.skynet.be
> Hello,
> I have got a very strange problem...
> In VB6, in the beginning of my application, I make a
> ListAvailableSQLServers to see what MSDE servers are on the network
> for my application.
> The problem is that if a computer is disconnected from the network,
> but has MSDE working (a laptop) then the ListAvailableSQLServers will
> return no server, even if there is a local server.
> Is there something to do to have it?
> PS : In VB6, I use
> dim ListSvr as collection
> Set ListSvr = SQLDMO.ListAvailableSQLServers
> and the count is 0
> I have also tried with
> Dim oServer As New SQLDMO.SQLServer2
> Dim oNameList As SQLDMO.NameList
> Set oNameList = oServer.Application.ListAvailableSQLServers '
> and oNameList.Count is still 0
>
can you please verify your local MSDE instance has network protocols enabled
via Server Network Utility (svrnetcn.exe)?
if network protocols are disabled (default for MSDE installation), the
relative instance will not be enlisted in the broadcast call for server
enumeration..
as regard ListAvailableServer method provided by SQL-DMO object model, I've
found during my (little) experience, this sort of rules:
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
The latest problem has been added by Windows XP service pack 2, which
implements a strong protection of the local computer closing all ports for
incoming and outgoing connections, requiring to manually open the desired IP
port in order to allow external remote connections
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello,
My enabled networks protocols are Named pipes and Tcp/IP
With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
have no server). I have exactly the same error with Osql -L
I use WinXP SP2 without firewall for my tests.
With SQL Server 2000, a Win98 computer is in the list
I have also seen something strange...
I make the ListAvailableSQLServers
If I have a return count of 0, then I use
the object
SQLDMO.SQLServer2
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
will return the local computer when no network is enabled.
Thank you
Marc Allard
Allcomp
Andrea Montanari wrote:
> hi Marc,
> "Allcomp" <marc@.nospam.allcomp.be> ha scritto nel messaggio
> news:41d95753$0$329$ba620e4c@.news.skynet.be
>
> can you please verify your local MSDE instance has network protocols enabled
> via Server Network Utility (svrnetcn.exe)?
> if network protocols are disabled (default for MSDE installation), the
> relative instance will not be enlisted in the broadcast call for server
> enumeration..
> as regard ListAvailableServer method provided by SQL-DMO object model, I've
> found during my (little) experience, this sort of rules:
> ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
> libraries installed by Mdac;
> this is a mechanism working in broadcast calls, which result never are
> conclusive and consistent, becouse results are influenced of various
> servers's answer states, answer time, etc.
> Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
> broadcast, on which SQL Servers respond (Default protocol for SQL Server
> 7.0), while in SQL Server 2000 the rules changed, because the default client
> protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
> broadcast, listening on port 1434:
> which is using a UDP broadcast on port 1434, if instance do not listen or
> not respond on time they will not be part of the enumeration.
> Some basic rules for 7.0 are:
> - SQL Servers have to be running on Windows NT or Windows 2000 and have to
> listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
> show up, because they do not listen on Named Pipes.
> - The SQL Server has to be running in order to respond on the broadcast.
> There is a gray window of 15 minutes after shutdown, where a browse master
> in the domain may respond on the broadcast and answer.
> - If you have routers in your network, that do not pass on NetBIOS
> broadcasts, this might limit your scope of the broadcast.
> - Only servers within the same NT domain (or trust) will get enumerated.
> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
> default protocol has been changed to be TCP/IP sockets and instead of a
> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
> still applies roughly.
> - SQL Server that are running
> - SQL Server that listening on TCP/IP
> - Running on Windows NT or Windows 2000 or Windows 9x
> - If you use routers and these are configured not to pass UDP broadcasts,
> only machines within the same subnet show up.
> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
> .ListAvailableServer method to work properly, becouse precding release of
> Sql-DMO Components of Sql Server 2000 present a bug in this area.
> Courtesy of Mr. Gert E.R. Drapers
> further Information at
> http://sqldev.net/misc.htm
> The Service Pack 3a introduced some new amenity in order to prevent MSDE
> 2000 to be hit by Internet worms like Slammer and Saphire virus and to
> increase security, so that Microsoft decided to default for disabling
> SuperSockets Network Protocols on new MSDE 2000 installation.
> Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
> UDP port 1434 when they are configured to not listen on any network
> protocols. This will stop enlisting these servers.
> The latest problem has been added by Windows XP service pack 2, which
> implements a strong protection of the local computer closing all ports for
> incoming and outgoing connections, requiring to manually open the desired IP
> port in order to allow external remote connections
|||If you have no network this is expected, since the enumeration is based on a
UDP (TCP) broadcast. Since this is a broadcast it normally only sees
computers in the same subnet, since most routers are configured not to pass
on UDP broadcast requests.
Besides that there is a response time issue. After the request is issued the
client only waits for a certain period of time on UDP replies from the
servers that received the UDP broadcast request.
On the local machine the API does a Registry scan, which is why local
services should show up when no network is enabled. MSDE has different
Registry entries that is why these do not show.
If you want to use the same API as SQL-DMO you need to use the
ListSQLSrv.exe tool http://sqldev.net/misc/ListSQLSvr.htm EnumSQLSvr, uses
the OLE-DB based API, which theoretically should provide the same results,
but you never know.
On Win98 I think they only enumerate local instances as far as I know.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Allcomp" <marc@.nospam.allcomp.be> wrote in message
news:41da4402$0$316$ba620e4c@.news.skynet.be...[vbcol=seagreen]
> Hello,
> My enabled networks protocols are Named pipes and Tcp/IP
> With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
> have no server). I have exactly the same error with Osql -L
> I use WinXP SP2 without firewall for my tests.
> With SQL Server 2000, a Win98 computer is in the list
> I have also seen something strange...
> I make the ListAvailableSQLServers
> If I have a return count of 0, then I use
> the object
> SQLDMO.SQLServer2
> Set oServer = New SQLDMO.SQLServer2
> Set ListSvr = oServer.ListInstalledInstances
> will return the local computer when no network is enabled.
>
> Thank you
> Marc Allard
> Allcomp
>
>
>
>
> Andrea Montanari wrote:
|||Hello,
In fact, I was thinking that when I remove the computer from the
network, it is able to see itself (by UDP) you can answer to yourself
(but it seem that it is not possible)?
So I think I will have to list all the local instances if I can see no
network.
PS : Win 98 can see any server (local or networked).
Thank you
Marc Allard
Allcomp
Gert E.R. Drapers wrote:
> If you have no network this is expected, since the enumeration is based on a
> UDP (TCP) broadcast. Since this is a broadcast it normally only sees
> computers in the same subnet, since most routers are configured not to pass
> on UDP broadcast requests.
> Besides that there is a response time issue. After the request is issued the
> client only waits for a certain period of time on UDP replies from the
> servers that received the UDP broadcast request.
> On the local machine the API does a Registry scan, which is why local
> services should show up when no network is enabled. MSDE has different
> Registry entries that is why these do not show.
> If you want to use the same API as SQL-DMO you need to use the
> ListSQLSrv.exe tool http://sqldev.net/misc/ListSQLSvr.htm EnumSQLSvr, uses
> the OLE-DB based API, which theoretically should provide the same results,
> but you never know.
> On Win98 I think they only enumerate local instances as far as I know.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Allcomp" <marc@.nospam.allcomp.be> wrote in message
> news:41da4402$0$316$ba620e4c@.news.skynet.be...
>
>
|||If you use SQLBrowseConnect directly (not SQL-DMO) you can point it at a
single machine, which would give you this.
If you try the ListSQLSvr.exe tool with -S <your machine name> -X it will
give you a listing of your local servers only.
If you install a loopback adapter the UDP broadcast will work locally.
Good to know that Win98 does local and remote, I never touch that OS, since
NT was introduced I left DOS behind me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Allcomp" <marc@.nospam.allcomp.be> wrote in message
news:41da6e27$0$338$ba620e4c@.news.skynet.be...[vbcol=seagreen]
> Hello,
> In fact, I was thinking that when I remove the computer from the network,
> it is able to see itself (by UDP) you can answer to yourself (but it seem
> that it is not possible)?
> So I think I will have to list all the local instances if I can see no
> network.
> PS : Win 98 can see any server (local or networked).
> Thank you
> Marc Allard
> Allcomp
> Gert E.R. Drapers wrote:
|||Hello,
Thank you for your help
In fact, I must use Win98 because a lot of my custommers use Win98 and
don't want to change.
Marc Allard
Allcomp
Gert E.R. Drapers wrote:
> If you use SQLBrowseConnect directly (not SQL-DMO) you can point it at a
> single machine, which would give you this.
> If you try the ListSQLSvr.exe tool with -S <your machine name> -X it will
> give you a listing of your local servers only.
> If you install a loopback adapter the UDP broadcast will work locally.
> Good to know that Win98 does local and remote, I never touch that OS, since
> NT was introduced I left DOS behind me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Allcomp" <marc@.nospam.allcomp.be> wrote in message
> news:41da6e27$0$338$ba620e4c@.news.skynet.be...
>
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Friday, March 30, 2012
Problem with linking servers
Hi.
I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQL
Server' to link one to the other and also vise versa.
Each server has two users permissioned.
My problem is when ever I try to do something that does a remote write I get
the follow error message
Microsoft OLE DB Provider for SQL Server error '80040e14'
[OLE/DB provider returned message: Cannot start more transactions on this
session.]
Also
when I try and manually run a stored procedure, I get:
Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.
(1 row(s) affected)
(1 row(s) affected)
(50 row(s) affected)
Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
65
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
nested transaction was required because the XACT_ABORT option was set to
OFF.
[OLE/DB provider returned message: Cannot start more transactions on this
session.]
Can anyone suggest how I resolve this.
FYI
Database Creation Script: (both database are the same scripts but db names
are changed)
CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' , SIZE
= 2000, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
exec sp_dboption N'msmprim', N'autoclose', N'false'
GO
exec sp_dboption N'msmprim', N'bulkcopy', N'false'
GO
exec sp_dboption N'msmprim', N'trunc. log', N'false'
GO
exec sp_dboption N'msmprim', N'torn page detection', N'true'
GO
exec sp_dboption N'msmprim', N'read only', N'false'
GO
exec sp_dboption N'msmprim', N'dbo use', N'false'
GO
exec sp_dboption N'msmprim', N'single', N'false'
GO
exec sp_dboption N'msmprim', N'autoshrink', N'false'
GO
exec sp_dboption N'msmprim', N'ANSI null default', N'false'
GO
exec sp_dboption N'msmprim', N'recursive triggers', N'false'
GO
exec sp_dboption N'msmprim', N'ANSI nulls', N'false'
GO
exec sp_dboption N'msmprim', N'concat null yields null', N'false'
GO
exec sp_dboption N'msmprim', N'cursor close on commit', N'false'
GO
exec sp_dboption N'msmprim', N'default to local cursor', N'false'
GO
exec sp_dboption N'msmprim', N'quoted identifier', N'false'
GO
exec sp_dboption N'msmprim', N'ANSI warnings', N'false'
GO
exec sp_dboption N'msmprim', N'auto create statistics', N'true'
GO
exec sp_dboption N'msmprim', N'auto update statistics', N'true'
GO
User Creation Script:
/****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/
use [msmprim]
GO
declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'
declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)
BEGIN
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')
select @.loginlang = @.@.language
exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
END
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)
EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
exec sp_addrolemember N'db_datareader', @.sqlLoginName
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
exec sp_addrolemember N'db_datawriter', @.sqlLoginName
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
exec sp_addrolemember N'db_owner', @.sqlLoginName
GO
use [msmprim]
GO
declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'
declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)
BEGIN
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')
select @.loginlang = @.@.language
exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
END
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)
EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
exec sp_addrolemember N'db_datareader', @.sqlLoginName
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
exec sp_addrolemember N'db_datawriter', @.sqlLoginName
/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
exec sp_addrolemember N'db_owner', @.sqlLoginName
GOI have got a little further, by adding 'SET XACT_ABORT ON' I have mangaged
to get the remote write to work.
Can someone tell me what the implication of setting 'SET XACT_ABORT ON' is
and what effect will it have on my SPs. Will they still roll back as
before?
Sample SP
procedure [msm].ams_Insert_Audit
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strSCRIPT varchar(128),
@.strLOGINID varchar(32),
@.strVFEID varchar(16),
@.strBILLORGID varchar(16),
@.strRETAILERID varchar(16),
@.strLOCATIONID varchar(16),
@.strPOSTID varchar(16),
@.strXACTIONID varchar(16),
@.strCODE varchar(64),
@.strDATA varchar(1024)
as
declare @.strStep varchar(32)
declare @.trancount int
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'
if (@.trancount = 0)
begin tran ams_Insert_Audit
else
save tran ams_Insert_Audit
SET XACT_ABORT ON
set @.strStep = 'Writing MSM Audit'
insert into
HAN.msmprim.msm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)
set @.strStep = 'Writing PSM Audit'
insert into
ACSPSM.psmprim.psm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)
if (@.@.error <> 0)
begin
rollback tran ams_Insert_Audit
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : Error Occured'
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it
if (@.trancount = 0)
commit tran ams_Insert_Audit
return 0
Regards
Steve
"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkeqcl$h14$1@.titan.btinternet.com...
> Hi.
> I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM',
N'SQL
> Server' to link one to the other and also vise versa.
> Each server has two users permissioned.
> My problem is when ever I try to do something that does a remote write I
get
> the follow error message
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
> Also
> when I try and manually run a stored procedure, I get:
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (50 row(s) affected)
> Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
> 65
> Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
> nested transaction was required because the XACT_ABORT option was set to
> OFF.
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
>
> Can anyone suggest how I resolve this.
>
>
> FYI
> Database Creation Script: (both database are the same scripts but db names
> are changed)
> CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
> N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
> (NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' ,
SIZE
> = 2000, FILEGROWTH = 10%)
> COLLATE Latin1_General_CI_AS
> GO
> exec sp_dboption N'msmprim', N'autoclose', N'false'
> GO
> exec sp_dboption N'msmprim', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'msmprim', N'trunc. log', N'false'
> GO
> exec sp_dboption N'msmprim', N'torn page detection', N'true'
> GO
> exec sp_dboption N'msmprim', N'read only', N'false'
> GO
> exec sp_dboption N'msmprim', N'dbo use', N'false'
> GO
> exec sp_dboption N'msmprim', N'single', N'false'
> GO
> exec sp_dboption N'msmprim', N'autoshrink', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'msmprim', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'msmprim', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'msmprim', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'msmprim', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'msmprim', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'msmprim', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'msmprim', N'auto update statistics', N'true'
> GO
>
>
> User Creation Script:
> /****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO
problem with linked servers and INSERT
Hello
I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.
Here's the code:
-------
create table #b (type int,label varchar(100),x int,y int,so int)
declare @.arg nvarchar(100)
set @.arg = 'BLANKET.MaalGrupper.ID38'
declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( '' + @.arg + '' )'' )'
insert #b exec (@.tsql)
drop table #b
--------
I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@.arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.
Can anybody help with this very irritating problem?
Thanks
MNJdoesn't anybody have an idea of might be wrong?|||Below is working for me (SQL2000)
-- on linked server
CREATE FUNCTION getit (@.id int=null)
RETURNS TABLE
AS
RETURN (SELECT *
FROM sysobjects WHERE id = coalesce(@.id,id))
go
select * from getit(null)
-------------
create table #b (label varchar(100))
insert #b
select * from openquery(linked,'select name from testDB.dbo.getit(null)')
declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery(linked,''select name from testDB.dbo.getit(null)'')'
insert #b exec (@.tsql)|||Do you have Distributed Transaction Coordinator running on both machines?|||Hm, the first select works for me, the second doesn't. And I need to be able to specify a parameter.
DTC is running on both machines.
MNJ|||Have you set any special environment variables or anything like that?
MNJ|||Have you set any special environment variables or anything like that?
MNJ
Nothing special...sql
I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.
Here's the code:
-------
create table #b (type int,label varchar(100),x int,y int,so int)
declare @.arg nvarchar(100)
set @.arg = 'BLANKET.MaalGrupper.ID38'
declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( '' + @.arg + '' )'' )'
insert #b exec (@.tsql)
drop table #b
--------
I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@.arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.
Can anybody help with this very irritating problem?
Thanks
MNJdoesn't anybody have an idea of might be wrong?|||Below is working for me (SQL2000)
-- on linked server
CREATE FUNCTION getit (@.id int=null)
RETURNS TABLE
AS
RETURN (SELECT *
FROM sysobjects WHERE id = coalesce(@.id,id))
go
select * from getit(null)
-------------
create table #b (label varchar(100))
insert #b
select * from openquery(linked,'select name from testDB.dbo.getit(null)')
declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery(linked,''select name from testDB.dbo.getit(null)'')'
insert #b exec (@.tsql)|||Do you have Distributed Transaction Coordinator running on both machines?|||Hm, the first select works for me, the second doesn't. And I need to be able to specify a parameter.
DTC is running on both machines.
MNJ|||Have you set any special environment variables or anything like that?
MNJ|||Have you set any special environment variables or anything like that?
MNJ
Nothing special...sql
Problem with Linked Servers
I am trying to link a sql 2000 server to a sql 2005 Server. I setup the link to the sql 2000 box from the sql 2005 (x64, Enteprrise) box using SSMS. Both boxes have the same Windows domain account that I'm using for auithentication. (I also selected the Impersonate option in SSMS) When I run a simple query on the sql 2005 box referencing the sql 2000 box ( select * from sql2000Box.DBName.dbo.tblName) I get the error message:
OLE DB provider "SQLNCLI" for linked server "sql2000Box" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "sql2000Box" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "sql2000Box".
Help!.
TIA,
Barkingdog
I called Microsoft on this one. They recommended that I enable RPC OUT on the server trying to link to the linked server. When I did I got another error message that appears explicilty in this article:
"You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server"
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I will see if this solution works.
Barkingdog
Problem with linked servers
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561632
I have two servers that are linked together ServerA and ServerB. I can do
selects from remote tables on both servers with no problems. The problem
that i am running into is when I am doing an insert into a local table via a
join with a local and remote table.
Both Win2k3 server with SQL 2000 SP3
ie (I know this code isnt correct, just an example)
insert into ServerA.DB.dbo.table
select *
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
I can run the selects just fine, but when adding the inserts, it craps out.
----
---
this is the error that i am getting
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].try this, it might help
select *
into #tmpData
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
insert into ServerA.DB.dbo.table
select *
from
#tmpData
remember that 'select *' shouldn't be used in production code.
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> I have two servers that are linked together ServerA and ServerB. I can do
> selects from remote tables on both servers with no problems. The problem
> that i am running into is when I am doing an insert into a local table via
a
> join with a local and remote table.
> Both Win2k3 server with SQL 2000 SP3
> ie (I know this code isnt correct, just an example)
> insert into ServerA.DB.dbo.table
> select *
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> I can run the selects just fine, but when adding the inserts, it craps
out.
> ----
---
> this is the error that i am getting
> The operation could not be performed because the OLE DB provider
'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561776
I had given thought to the temp table, but i would like to know if there is
configuration problem with my server keeping me from doing this.
TIA
Kevin E.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4360865d$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> try this, it might help
> select *
> into #tmpData
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> insert into ServerA.DB.dbo.table
> select *
> from
> #tmpData
> remember that 'select *' shouldn't be used in production code.
>
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> a
> out.
> ---
> 'SQLOLEDB'
>
I have two servers that are linked together ServerA and ServerB. I can do
selects from remote tables on both servers with no problems. The problem
that i am running into is when I am doing an insert into a local table via a
join with a local and remote table.
Both Win2k3 server with SQL 2000 SP3
ie (I know this code isnt correct, just an example)
insert into ServerA.DB.dbo.table
select *
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
I can run the selects just fine, but when adding the inserts, it craps out.
----
---
this is the error that i am getting
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].try this, it might help
select *
into #tmpData
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
insert into ServerA.DB.dbo.table
select *
from
#tmpData
remember that 'select *' shouldn't be used in production code.
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> I have two servers that are linked together ServerA and ServerB. I can do
> selects from remote tables on both servers with no problems. The problem
> that i am running into is when I am doing an insert into a local table via
a
> join with a local and remote table.
> Both Win2k3 server with SQL 2000 SP3
> ie (I know this code isnt correct, just an example)
> insert into ServerA.DB.dbo.table
> select *
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> I can run the selects just fine, but when adding the inserts, it craps
out.
> ----
---
> this is the error that i am getting
> The operation could not be performed because the OLE DB provider
'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561776
I had given thought to the temp table, but i would like to know if there is
configuration problem with my server keeping me from doing this.
TIA
Kevin E.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4360865d$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> try this, it might help
> select *
> into #tmpData
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> insert into ServerA.DB.dbo.table
> select *
> from
> #tmpData
> remember that 'select *' shouldn't be used in production code.
>
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> a
> out.
> ---
> 'SQLOLEDB'
>
Problem with linked servers
Hi,
we are trying to get some data from a linked server via an ODBC connection.
This server has 3 intances of the same software, we load 2 tables from each
installation. From one instance we get both tables, from the two others we
can only acces one of the two.
I explain that only because I want to make clear the the ODBC driver
"normally" should be able to be used with Linked servers.
When we try to load the two other tables, we get an error 7317:
OLE/DB provider returned an invalid schema definition
The ODBC driver is based on the SIMBA ODBC framework.
Any idea what I can do? What's about the "schema definition"?
Thanks,
Thomas1. Can you do simple select like "SELECT * from
servername.dbname.owner_name.table_name"
2. Does the account that has been used in defining the linked server has
proper security on the linked server tables.
"Thomas Pagel" <tpagel@.software4you.com> wrote in message
news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we are trying to get some data from a linked server via an ODBC
connection.
> This server has 3 intances of the same software, we load 2 tables from
each
> installation. From one instance we get both tables, from the two others we
> can only acces one of the two.
> I explain that only because I want to make clear the the ODBC driver
> "normally" should be able to be used with Linked servers.
> When we try to load the two other tables, we get an error 7317:
> OLE/DB provider returned an invalid schema definition
> The ODBC driver is based on the SIMBA ODBC framework.
> Any idea what I can do? What's about the "schema definition"?
>
> Thanks,
>
> Thomas
>|||Ammar,
1.) This works with some of the tables, others don't
2.) The queries work well if I direclty use the ODBC driver i.e. with DTS
with the same user and it has all rights, too. So this couldn't be a
security issue...
Thanks,
Thomas
"Ammar" <ammar.ansari@.anthem.com> schrieb im Newsbeitrag
news:%23CeduY4VDHA.2544@.tk2msftngp13.phx.gbl...
> 1. Can you do simple select like "SELECT * from
> servername.dbname.owner_name.table_name"
> 2. Does the account that has been used in defining the linked server has
> proper security on the linked server tables.
>
> "Thomas Pagel" <tpagel@.software4you.com> wrote in message
> news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we are trying to get some data from a linked server via an ODBC
> connection.
> > This server has 3 intances of the same software, we load 2 tables from
> each
> > installation. From one instance we get both tables, from the two others
we
> > can only acces one of the two.
> >
> > I explain that only because I want to make clear the the ODBC driver
> > "normally" should be able to be used with Linked servers.
> >
> > When we try to load the two other tables, we get an error 7317:
> >
> > OLE/DB provider returned an invalid schema definition
> >
> > The ODBC driver is based on the SIMBA ODBC framework.
> >
> > Any idea what I can do? What's about the "schema definition"?
> >
> >
> > Thanks,
> >
> >
> > Thomas
> >
> >
>
we are trying to get some data from a linked server via an ODBC connection.
This server has 3 intances of the same software, we load 2 tables from each
installation. From one instance we get both tables, from the two others we
can only acces one of the two.
I explain that only because I want to make clear the the ODBC driver
"normally" should be able to be used with Linked servers.
When we try to load the two other tables, we get an error 7317:
OLE/DB provider returned an invalid schema definition
The ODBC driver is based on the SIMBA ODBC framework.
Any idea what I can do? What's about the "schema definition"?
Thanks,
Thomas1. Can you do simple select like "SELECT * from
servername.dbname.owner_name.table_name"
2. Does the account that has been used in defining the linked server has
proper security on the linked server tables.
"Thomas Pagel" <tpagel@.software4you.com> wrote in message
news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we are trying to get some data from a linked server via an ODBC
connection.
> This server has 3 intances of the same software, we load 2 tables from
each
> installation. From one instance we get both tables, from the two others we
> can only acces one of the two.
> I explain that only because I want to make clear the the ODBC driver
> "normally" should be able to be used with Linked servers.
> When we try to load the two other tables, we get an error 7317:
> OLE/DB provider returned an invalid schema definition
> The ODBC driver is based on the SIMBA ODBC framework.
> Any idea what I can do? What's about the "schema definition"?
>
> Thanks,
>
> Thomas
>|||Ammar,
1.) This works with some of the tables, others don't
2.) The queries work well if I direclty use the ODBC driver i.e. with DTS
with the same user and it has all rights, too. So this couldn't be a
security issue...
Thanks,
Thomas
"Ammar" <ammar.ansari@.anthem.com> schrieb im Newsbeitrag
news:%23CeduY4VDHA.2544@.tk2msftngp13.phx.gbl...
> 1. Can you do simple select like "SELECT * from
> servername.dbname.owner_name.table_name"
> 2. Does the account that has been used in defining the linked server has
> proper security on the linked server tables.
>
> "Thomas Pagel" <tpagel@.software4you.com> wrote in message
> news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we are trying to get some data from a linked server via an ODBC
> connection.
> > This server has 3 intances of the same software, we load 2 tables from
> each
> > installation. From one instance we get both tables, from the two others
we
> > can only acces one of the two.
> >
> > I explain that only because I want to make clear the the ODBC driver
> > "normally" should be able to be used with Linked servers.
> >
> > When we try to load the two other tables, we get an error 7317:
> >
> > OLE/DB provider returned an invalid schema definition
> >
> > The ODBC driver is based on the SIMBA ODBC framework.
> >
> > Any idea what I can do? What's about the "schema definition"?
> >
> >
> > Thanks,
> >
> >
> > Thomas
> >
> >
>
Problem with linked server on itself
HI ALL
I got SQL Server 2000 SQL1 and in linked servers i make server SQL2 witch looking at itseft (SQL1)...looks like loopback
I need this for debuging. In real system i got 2 servers
When i start transaction in server SQL01 and do some work with linked server SQL2 i got message
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
All options for distributing is on
When i do the same in real system with 2 different servers all do fine
Do this mean that in one server this is not work
Sample
BEGIN TRANSACTION
INSERT INTO SQL1.Base.dbo.Table(Field)
SELECT some
FROM LocalTable
COMMIT TRANSACTIO
Thank U ALL
In Russian
Ð?Ñ?ивеÑ? вÑ?ем.
СобÑ?Ñ?венно еÑ?Ñ?Ñ? Ñ?еÑ?веÑ? SQL 2000 и наÑ?Ñ?Ñ?оеннÑ?й на нем линкед Ñ?еÑ?веÑ?, коÑ?оÑ?Ñ?й Ñ?моÑ?Ñ?еÑ? на Ñ?ебÑ? Ñ?амого, но Ñ?еÑ?ез него Ñ?Ñ?аеÑ?Ñ?Ñ? дÑ?Ñ?гаÑ? база. Ð?Ñ?е Ñ?Ñ?о нÑ?жно длÑ? оÑ?ладки. Ð? Ñ?еалÑ?ной Ñ?иÑ?Ñ?еме Ñ?Ñ?о два Ñ?азнÑ?Ñ? Ñ?еÑ?веÑ?а. Ð?Ñ?е опÑ?ии по вÑ?Ñ?ким кооÑ?динаÑ?оÑ?ам и диÑ?Ñ?Ñ?иб Ñ?Ñ?анзакÑ?иÑ?м Ñ?Ñ?оÑ?Ñ?.
Ð?Ñ?и попÑ?Ñ?ке запÑ?Ñ?ка Ñ?Ñ?анзакÑ?ии вÑ?лезаеÑ? меÑ?ага
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
Ð?Ñ?ли Ñ?Ñ?о два Ñ?азнÑ?Ñ? Ñ?еÑ?вака, Ñ?о вÑ?е Ñ?абоÑ?аеÑ? ок. Чо за на? Ð?Ñ?еÑ?еднаÑ? Ñ?иÑ?а Ð?ила, Ñ?ипа Ñ?ам Ñ? Ñ?абой не можеÑ'
Ð?аÑ?анее Ñ?паÑ?ибо.This is not supported.
This is from http://support.microsoft.com/?id=306215:
Check whether the object on the destination server refers back to the
first server. This is what is known as a loopback situation. This is
not supported, as documented in SQL Server Books Online. For more
information, visit the following Microsoft Web site:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_12_2kvm.asp:
Loopback Linked Servers
Rand
This posting is provided "as is" with no warranties and confers no rights.
I got SQL Server 2000 SQL1 and in linked servers i make server SQL2 witch looking at itseft (SQL1)...looks like loopback
I need this for debuging. In real system i got 2 servers
When i start transaction in server SQL01 and do some work with linked server SQL2 i got message
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
All options for distributing is on
When i do the same in real system with 2 different servers all do fine
Do this mean that in one server this is not work
Sample
BEGIN TRANSACTION
INSERT INTO SQL1.Base.dbo.Table(Field)
SELECT some
FROM LocalTable
COMMIT TRANSACTIO
Thank U ALL
In Russian
Ð?Ñ?ивеÑ? вÑ?ем.
СобÑ?Ñ?венно еÑ?Ñ?Ñ? Ñ?еÑ?веÑ? SQL 2000 и наÑ?Ñ?Ñ?оеннÑ?й на нем линкед Ñ?еÑ?веÑ?, коÑ?оÑ?Ñ?й Ñ?моÑ?Ñ?еÑ? на Ñ?ебÑ? Ñ?амого, но Ñ?еÑ?ез него Ñ?Ñ?аеÑ?Ñ?Ñ? дÑ?Ñ?гаÑ? база. Ð?Ñ?е Ñ?Ñ?о нÑ?жно длÑ? оÑ?ладки. Ð? Ñ?еалÑ?ной Ñ?иÑ?Ñ?еме Ñ?Ñ?о два Ñ?азнÑ?Ñ? Ñ?еÑ?веÑ?а. Ð?Ñ?е опÑ?ии по вÑ?Ñ?ким кооÑ?динаÑ?оÑ?ам и диÑ?Ñ?Ñ?иб Ñ?Ñ?анзакÑ?иÑ?м Ñ?Ñ?оÑ?Ñ?.
Ð?Ñ?и попÑ?Ñ?ке запÑ?Ñ?ка Ñ?Ñ?анзакÑ?ии вÑ?лезаеÑ? меÑ?ага
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
Ð?Ñ?ли Ñ?Ñ?о два Ñ?азнÑ?Ñ? Ñ?еÑ?вака, Ñ?о вÑ?е Ñ?абоÑ?аеÑ? ок. Чо за на? Ð?Ñ?еÑ?еднаÑ? Ñ?иÑ?а Ð?ила, Ñ?ипа Ñ?ам Ñ? Ñ?абой не можеÑ'
Ð?аÑ?анее Ñ?паÑ?ибо.This is not supported.
This is from http://support.microsoft.com/?id=306215:
Check whether the object on the destination server refers back to the
first server. This is what is known as a loopback situation. This is
not supported, as documented in SQL Server Books Online. For more
information, visit the following Microsoft Web site:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_12_2kvm.asp:
Loopback Linked Servers
Rand
This posting is provided "as is" with no warranties and confers no rights.
Problem with Linked Server
Hi,
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
Lalit
I posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
Lalit
I posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit
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,
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...
which
query:
that
the
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...
which
query:
that
the
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:|||Problem resolved by using sp_addlinkedserver instead of Enterprise Manager.
> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>
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
Hi,
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
LalitI posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit
I have two sql server 7.0 servers and Linked servers are created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the same time using
trigger on that table record is added in a table which resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this process.
Any solutions?
LalitI posted this problem yesterday.
this problem is resloved by installing service pack 4 on
both sql 7.0 servers.
Hi,
I have two sql server 7.0 servers and Linked servers are
created using Ole
db provider for SQL Server.
When record is added in a table in server1 then at the
same time using
trigger on that table record is added in a table which
resides in server2.
At this time I am getting following error.
SqlDumpExceptionHandler: Process 11 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. Sql server is terminating this
process.
Any solutions?
Lalit
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
>
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
>
Monday, March 12, 2012
Problem with executing Distributed Transactions on Linked Servers
I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.
Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default...;en-us;306212. Check it, it
might help you.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>
|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:
> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.
Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default...;en-us;306212. Check it, it
might help you.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>
|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:
> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS
Problem with executing Distributed Transactions on Linked Servers
I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212. Check it, it
might help you.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:
> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212. Check it, it
might help you.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:
> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS
Wednesday, March 7, 2012
Problem with DTC on Win2K3 server and cluster
I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?
I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?
I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Problem with DTC on Win2K3 server and cluster
I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Problem with DTC on Win2K3 server and cluster
I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Problem with Drop Table and Field name chage
Hi!
I had two servers of SQL 2000 with distributor and publisher mode for replications. I wanted to change one field name of a table, I did the following:
1. stopped publishing,
2. Stopped distribution
3.Re-started the servers.
But when I want to save the changed field name i am getting this error
"/*
Saturday, October 18, 2003 12:59:08 PM
User: sa
Server: SSD-SQL2
Database: PAIFORWEB
Application: MS SQLEM - Data Tools
*/
'CC02' table
- Unable to rename column from 'INWARD' to 'INWARDxyz'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the table because it is published for replication.
"
I am getting the same error while dropping the database toout when I checked the replication there is no distribution and publication is existing.
Please help, it is most urgent to me.
Thanks in advance.
Sabyasachi Sensearch for "manually removing replication" in google prior to proceeding with object rename.
I had two servers of SQL 2000 with distributor and publisher mode for replications. I wanted to change one field name of a table, I did the following:
1. stopped publishing,
2. Stopped distribution
3.Re-started the servers.
But when I want to save the changed field name i am getting this error
"/*
Saturday, October 18, 2003 12:59:08 PM
User: sa
Server: SSD-SQL2
Database: PAIFORWEB
Application: MS SQLEM - Data Tools
*/
'CC02' table
- Unable to rename column from 'INWARD' to 'INWARDxyz'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the table because it is published for replication.
"
I am getting the same error while dropping the database toout when I checked the replication there is no distribution and publication is existing.
Please help, it is most urgent to me.
Thanks in advance.
Sabyasachi Sensearch for "manually removing replication" in google prior to proceeding with object rename.
Subscribe to:
Posts (Atom)