Hi,
I have two sql servers A and B linked together as LINKED SERVERS. A is
within the domain and B is outside the domain. They were working perfectly
fine a week back but now I can access A from B but I can't access B from A,
although B comes up under the Linked Servers list on A. What can be wrong?
Thanks in advance.Well
Something has changed since last week. Perhaps try to re-create a linked
server on server A to server B
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
> Hi,
> I have two sql servers A and B linked together as LINKED SERVERS. A is
> within the domain and B is outside the domain. They were working perfectly
> fine a week back but now I can access A from B but I can't access B from
> A,
> although B comes up under the Linked Servers list on A. What can be wrong?
> Thanks in advance.|||I have already done that. It does come up on the list of linked servers but
when I click on the tables icon it says "Server Not Accessible".
"Uri Dimant" wrote:
> Well
> Something has changed since last week. Perhaps try to re-create a linked
> server on server A to server B
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
>
>
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Wednesday, March 28, 2012
Linked Server Problem
We migrated our production SQL Server 2000 database to a new machine and all seems to be working well except for our linked server on our SQL Server 2005 instance that points to our SQL Server 2000 database that was just moved. We get the following error when we try to query a table on the 2000 database. I have read this article in the Books online but that does not seem to apply to us ( http://msdn2.microsoft.com/en-us/library/ms175496.aspx ).
OLE DB provider "SQLNCLI" for linked server "SQLPRD-DBS" returned message "Communication link failure".
Msg 233, Level 16, State 1, Line 0
Named Pipes Provider: No process is on the other end of the pipe.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'sa1'.
Anyone have any ideas?
Thanks!!!
could be a permission problem with the linked server login
make use of the useslelf option of the addsqlserver login
to properly map users to the linked server
[ , [ @.useself = ] 'useself' ]
[ , [ @.locallogin = ] 'locallogin' ]
[ , [ @.rmtuser = ] 'rmtuser' ]
[ , [ @.rmtpassword = ] 'rmtpassword' ]
Arguments
[ @.rmtsrvname = ] 'rmtsrvname'
Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.
[ @.useself = ] 'useself'
Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE.
A value of true specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.
[ @.locallogin = ] 'locallogin'
Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.
[ @.rmtuser = ] 'rmtuser'
Is the user name used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.
[ @.rmtpassword = ] 'rmtpassword'
Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.
examples
A. Connecting all local logins to the linked server by using their own user credentials
The following example creates a mapping to make sure that all logins to the local server connect through to the linked server Accounts by using their own user credentials.
B. Connects a specific login to the linked server by using different user credentials
The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts
by using the login MaryP and password d89q3w4u.
Linked server problem
Hi,
I have two sql servers A and B linked together as LINKED SERVERS. A is
within the domain and B is outside the domain. They were working perfectly
fine a week back but now I can access A from B but I can't access B from A,
although B comes up under the Linked Servers list on A. What can be wrong?
Thanks in advance.
Well
Something has changed since last week. Perhaps try to re-create a linked
server on server A to server B
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
> Hi,
> I have two sql servers A and B linked together as LINKED SERVERS. A is
> within the domain and B is outside the domain. They were working perfectly
> fine a week back but now I can access A from B but I can't access B from
> A,
> although B comes up under the Linked Servers list on A. What can be wrong?
> Thanks in advance.
|||I have already done that. It does come up on the list of linked servers but
when I click on the tables icon it says "Server Not Accessible".
"Uri Dimant" wrote:
> Well
> Something has changed since last week. Perhaps try to re-create a linked
> server on server A to server B
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
>
>
sql
I have two sql servers A and B linked together as LINKED SERVERS. A is
within the domain and B is outside the domain. They were working perfectly
fine a week back but now I can access A from B but I can't access B from A,
although B comes up under the Linked Servers list on A. What can be wrong?
Thanks in advance.
Well
Something has changed since last week. Perhaps try to re-create a linked
server on server A to server B
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
> Hi,
> I have two sql servers A and B linked together as LINKED SERVERS. A is
> within the domain and B is outside the domain. They were working perfectly
> fine a week back but now I can access A from B but I can't access B from
> A,
> although B comes up under the Linked Servers list on A. What can be wrong?
> Thanks in advance.
|||I have already done that. It does come up on the list of linked servers but
when I click on the tables icon it says "Server Not Accessible".
"Uri Dimant" wrote:
> Well
> Something has changed since last week. Perhaps try to re-create a linked
> server on server A to server B
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
>
>
sql
Monday, March 26, 2012
Linked server OPENQUERY, error 'The name <> is not a valid identif
Hi,
I am working on exporting data from a remote MSSQL db using la inked server.
Both SQL server 2000.
So far I have a SELECT against a linked server with a hard coded value in
the WHERE clause and it works fine:
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
I try to put this exact query into a variable and run it:
declare @.tsql varchar(8000)
SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
EXEC @.tsql
I get an error:
The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identifie
r.
The query specified in the error runs just fine on its own (see my first
step).
I searched the web and tech groups for answers but can't find any
information on this error. Please help.
Thank you, A"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> EXEC @.tsql
Add some parens:
EXEC (@.tsql)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Can you try using:
...
exec (@.tsql)
AMB
"Alexis" wrote:
> Hi,
> I am working on exporting data from a remote MSSQL db using la inked serve
r.
> Both SQL server 2000.
> So far I have a SELECT against a linked server with a hard coded value in
> the WHERE clause and it works fine:
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
> I try to put this exact query into a variable and run it:
> declare @.tsql varchar(8000)
> SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
> EXEC @.tsql
> I get an error:
> The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identif
ier.
> The query specified in the error runs just fine on its own (see my first
> step).
> I searched the web and tech groups for answers but can't find any
> information on this error. Please help.
> Thank you, A
>
I am working on exporting data from a remote MSSQL db using la inked server.
Both SQL server 2000.
So far I have a SELECT against a linked server with a hard coded value in
the WHERE clause and it works fine:
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
I try to put this exact query into a variable and run it:
declare @.tsql varchar(8000)
SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
EXEC @.tsql
I get an error:
The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identifie
r.
The query specified in the error runs just fine on its own (see my first
step).
I searched the web and tech groups for answers but can't find any
information on this error. Please help.
Thank you, A"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> EXEC @.tsql
Add some parens:
EXEC (@.tsql)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Can you try using:
...
exec (@.tsql)
AMB
"Alexis" wrote:
> Hi,
> I am working on exporting data from a remote MSSQL db using la inked serve
r.
> Both SQL server 2000.
> So far I have a SELECT against a linked server with a hard coded value in
> the WHERE clause and it works fine:
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
> I try to put this exact query into a variable and run it:
> declare @.tsql varchar(8000)
> SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
> EXEC @.tsql
> I get an error:
> The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identif
ier.
> The query specified in the error runs just fine on its own (see my first
> step).
> I searched the web and tech groups for answers but can't find any
> information on this error. Please help.
> Thank you, A
>
Linked server on a different domain
Hello,
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
BigeHi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
[vbcol=seagreen]
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want t
o
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
>
>|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, the
n
> it would tend to imply that it was configuration of the linked server is t
he
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want
to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via a
n
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
>|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
>
>|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using
4
> part naming.
> John
> "Bige" wrote:
>|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
>
>
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
BigeHi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
[vbcol=seagreen]
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want t
o
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
>
>|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, the
n
> it would tend to imply that it was configuration of the linked server is t
he
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want
to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via a
n
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
>|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
>
>|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using
4
> part naming.
> John
> "Bige" wrote:
>|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
>
>
Linked server on a different domain
Hello,
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
BigeHi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > Hello,
> >
> > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> >
> > I use SQL authentication, and both are working on TCPIP port 1433.
> >
> > It doesn't work, it says "Access denied or not existant Sql"...but the
> > credentials are right.
> >
> > I'm connected through a VPN channel (CISCO client vpn connector) with
> > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > connected to 2005 and it works fine.
> >
> > What it might be? The port is fixed. Do I need to open something else?
> >
> > Thanx,
> > Bige
> >|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
> > Have you tried pass through authentication? This is where you have two NT
> > accounts with the same account names and passwords in both domains? If you
> > use the security context of the one account it should work against the
> > server in the other domain.
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Bige" <ivan.pololi@.interah.com> wrote in message
> > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > Hello,
> > >
> > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > >
> > > I use SQL authentication, and both are working on TCPIP port 1433.
> > >
> > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > credentials are right.
> > >
> > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > connected to 2005 and it works fine.
> > >
> > > What it might be? The port is fixed. Do I need to open something else?
> > >
> > > Thanx,
> > > Bige
> > >
>|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, then
> it would tend to imply that it was configuration of the linked server is the
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via an
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
> > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> >
> > I don't want to use NT accounts, I need to use SQL authentication.
> >
> > About the ports are the same and both use protocol TCPIP...if I
> > connect a SQL 2000 to 2005
> > in the same domain with my SQL credentials it works.
> >
> > The problem is when I try to connect from another network in VPN
> > mode...
> >
> > Thanks for your help, I appreciate it.
> > Bige
> >
> > Hilary Cotter ha scritto:
> >
> > > Have you tried pass through authentication? This is where you have two NT
> > > accounts with the same account names and passwords in both domains? If you
> > > use the security context of the one account it should work against the
> > > server in the other domain.
> > >
> > > --
> > > Hilary Cotter
> > > Director of Text Mining and Database Strategy
> > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > >
> > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > positions, strategies or opinions.
> > >
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > > Looking for a FAQ on Indexing Services/SQL FTS
> > > http://www.indexserverfaq.com
> > >
> > >
> > >
> > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > Hello,
> > > >
> > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > >
> > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > >
> > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > credentials are right.
> > > >
> > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > connected to 2005 and it works fine.
> > > >
> > > > What it might be? The port is fixed. Do I need to open something else?
> > > >
> > > > Thanx,
> > > > Bige
> > > >
> >
> >|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
> > Hi
> >
> > Connecting with a SQL connection should not matter about the servers being
> > on different domains. Can you connect with Query Analyser, if you can, then
> > it would tend to imply that it was configuration of the linked server is the
> > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > check out http://support.microsoft.com/kb/287932 and
> > http://support.microsoft.com/kb/306199/
> >
> > With a VPN there may be delays introduced in the connection, so increasing
> > timeouts may be necessary, also check that you can access the server via an
> > IP address, if this works there may be a DNS issue.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > >
> > > I don't want to use NT accounts, I need to use SQL authentication.
> > >
> > > About the ports are the same and both use protocol TCPIP...if I
> > > connect a SQL 2000 to 2005
> > > in the same domain with my SQL credentials it works.
> > >
> > > The problem is when I try to connect from another network in VPN
> > > mode...
> > >
> > > Thanks for your help, I appreciate it.
> > > Bige
> > >
> > > Hilary Cotter ha scritto:
> > >
> > > > Have you tried pass through authentication? This is where you have two NT
> > > > accounts with the same account names and passwords in both domains? If you
> > > > use the security context of the one account it should work against the
> > > > server in the other domain.
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Director of Text Mining and Database Strategy
> > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > >
> > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > positions, strategies or opinions.
> > > >
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > http://www.indexserverfaq.com
> > > >
> > > >
> > > >
> > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > Hello,
> > > > >
> > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > >
> > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > >
> > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > credentials are right.
> > > > >
> > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > connected to 2005 and it works fine.
> > > > >
> > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > >
> > > > > Thanx,
> > > > > Bige
> > > > >
> > >
> > >
>|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using 4
> part naming.
> John
> "Bige" wrote:
> > Yes, I can connect using Query Analyser.
> >
> > I created the linked server using EM; here it returns "SQL server not
> > existant" when I try to list tables or views...
> >
> > I tried to change the timeout but didn't worked...and it is not a DNS
> > problem
> >
> > About the microsoft kbs were not really helpful...any clues?
> >
> > Thanx,
> > Bige
> >
> > John Bell ha scritto:
> >
> > > Hi
> > >
> > > Connecting with a SQL connection should not matter about the servers being
> > > on different domains. Can you connect with Query Analyser, if you can, then
> > > it would tend to imply that it was configuration of the linked server is the
> > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > check out http://support.microsoft.com/kb/287932 and
> > > http://support.microsoft.com/kb/306199/
> > >
> > > With a VPN there may be delays introduced in the connection, so increasing
> > > timeouts may be necessary, also check that you can access the server via an
> > > IP address, if this works there may be a DNS issue.
> > >
> > > John
> > >
> > > "Bige" wrote:
> > >
> > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > >
> > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > >
> > > > About the ports are the same and both use protocol TCPIP...if I
> > > > connect a SQL 2000 to 2005
> > > > in the same domain with my SQL credentials it works.
> > > >
> > > > The problem is when I try to connect from another network in VPN
> > > > mode...
> > > >
> > > > Thanks for your help, I appreciate it.
> > > > Bige
> > > >
> > > > Hilary Cotter ha scritto:
> > > >
> > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > accounts with the same account names and passwords in both domains? If you
> > > > > use the security context of the one account it should work against the
> > > > > server in the other domain.
> > > > >
> > > > > --
> > > > > Hilary Cotter
> > > > > Director of Text Mining and Database Strategy
> > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > >
> > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > positions, strategies or opinions.
> > > > >
> > > > > Looking for a SQL Server replication book?
> > > > > http://www.nwsu.com/0974973602.html
> > > > >
> > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > http://www.indexserverfaq.com
> > > > >
> > > > >
> > > > >
> > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > Hello,
> > > > > >
> > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > >
> > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > >
> > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > credentials are right.
> > > > > >
> > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > connected to 2005 and it works fine.
> > > > > >
> > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > >
> > > > > > Thanx,
> > > > > > Bige
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
> > Hi
> >
> > That implies it is the configuration of the linked server that may not be
> > working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> > Analyser and if they don't give an error see if you try run a query using 4
> > part naming.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > Yes, I can connect using Query Analyser.
> > >
> > > I created the linked server using EM; here it returns "SQL server not
> > > existant" when I try to list tables or views...
> > >
> > > I tried to change the timeout but didn't worked...and it is not a DNS
> > > problem
> > >
> > > About the microsoft kbs were not really helpful...any clues?
> > >
> > > Thanx,
> > > Bige
> > >
> > > John Bell ha scritto:
> > >
> > > > Hi
> > > >
> > > > Connecting with a SQL connection should not matter about the servers being
> > > > on different domains. Can you connect with Query Analyser, if you can, then
> > > > it would tend to imply that it was configuration of the linked server is the
> > > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > > check out http://support.microsoft.com/kb/287932 and
> > > > http://support.microsoft.com/kb/306199/
> > > >
> > > > With a VPN there may be delays introduced in the connection, so increasing
> > > > timeouts may be necessary, also check that you can access the server via an
> > > > IP address, if this works there may be a DNS issue.
> > > >
> > > > John
> > > >
> > > > "Bige" wrote:
> > > >
> > > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > > >
> > > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > > >
> > > > > About the ports are the same and both use protocol TCPIP...if I
> > > > > connect a SQL 2000 to 2005
> > > > > in the same domain with my SQL credentials it works.
> > > > >
> > > > > The problem is when I try to connect from another network in VPN
> > > > > mode...
> > > > >
> > > > > Thanks for your help, I appreciate it.
> > > > > Bige
> > > > >
> > > > > Hilary Cotter ha scritto:
> > > > >
> > > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > > accounts with the same account names and passwords in both domains? If you
> > > > > > use the security context of the one account it should work against the
> > > > > > server in the other domain.
> > > > > >
> > > > > > --
> > > > > > Hilary Cotter
> > > > > > Director of Text Mining and Database Strategy
> > > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > > >
> > > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > > positions, strategies or opinions.
> > > > > >
> > > > > > Looking for a SQL Server replication book?
> > > > > > http://www.nwsu.com/0974973602.html
> > > > > >
> > > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > > http://www.indexserverfaq.com
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > > Hello,
> > > > > > >
> > > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > > >
> > > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > > >
> > > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > > credentials are right.
> > > > > > >
> > > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > > connected to 2005 and it works fine.
> > > > > > >
> > > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > > >
> > > > > > > Thanx,
> > > > > > > Bige
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
BigeHi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > Hello,
> >
> > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> >
> > I use SQL authentication, and both are working on TCPIP port 1433.
> >
> > It doesn't work, it says "Access denied or not existant Sql"...but the
> > credentials are right.
> >
> > I'm connected through a VPN channel (CISCO client vpn connector) with
> > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > connected to 2005 and it works fine.
> >
> > What it might be? The port is fixed. Do I need to open something else?
> >
> > Thanx,
> > Bige
> >|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
> > Have you tried pass through authentication? This is where you have two NT
> > accounts with the same account names and passwords in both domains? If you
> > use the security context of the one account it should work against the
> > server in the other domain.
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Bige" <ivan.pololi@.interah.com> wrote in message
> > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > Hello,
> > >
> > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > >
> > > I use SQL authentication, and both are working on TCPIP port 1433.
> > >
> > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > credentials are right.
> > >
> > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > connected to 2005 and it works fine.
> > >
> > > What it might be? The port is fixed. Do I need to open something else?
> > >
> > > Thanx,
> > > Bige
> > >
>|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, then
> it would tend to imply that it was configuration of the linked server is the
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via an
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
> > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> >
> > I don't want to use NT accounts, I need to use SQL authentication.
> >
> > About the ports are the same and both use protocol TCPIP...if I
> > connect a SQL 2000 to 2005
> > in the same domain with my SQL credentials it works.
> >
> > The problem is when I try to connect from another network in VPN
> > mode...
> >
> > Thanks for your help, I appreciate it.
> > Bige
> >
> > Hilary Cotter ha scritto:
> >
> > > Have you tried pass through authentication? This is where you have two NT
> > > accounts with the same account names and passwords in both domains? If you
> > > use the security context of the one account it should work against the
> > > server in the other domain.
> > >
> > > --
> > > Hilary Cotter
> > > Director of Text Mining and Database Strategy
> > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > >
> > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > positions, strategies or opinions.
> > >
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > > Looking for a FAQ on Indexing Services/SQL FTS
> > > http://www.indexserverfaq.com
> > >
> > >
> > >
> > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > Hello,
> > > >
> > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > >
> > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > >
> > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > credentials are right.
> > > >
> > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > connected to 2005 and it works fine.
> > > >
> > > > What it might be? The port is fixed. Do I need to open something else?
> > > >
> > > > Thanx,
> > > > Bige
> > > >
> >
> >|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
> > Hi
> >
> > Connecting with a SQL connection should not matter about the servers being
> > on different domains. Can you connect with Query Analyser, if you can, then
> > it would tend to imply that it was configuration of the linked server is the
> > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > check out http://support.microsoft.com/kb/287932 and
> > http://support.microsoft.com/kb/306199/
> >
> > With a VPN there may be delays introduced in the connection, so increasing
> > timeouts may be necessary, also check that you can access the server via an
> > IP address, if this works there may be a DNS issue.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > >
> > > I don't want to use NT accounts, I need to use SQL authentication.
> > >
> > > About the ports are the same and both use protocol TCPIP...if I
> > > connect a SQL 2000 to 2005
> > > in the same domain with my SQL credentials it works.
> > >
> > > The problem is when I try to connect from another network in VPN
> > > mode...
> > >
> > > Thanks for your help, I appreciate it.
> > > Bige
> > >
> > > Hilary Cotter ha scritto:
> > >
> > > > Have you tried pass through authentication? This is where you have two NT
> > > > accounts with the same account names and passwords in both domains? If you
> > > > use the security context of the one account it should work against the
> > > > server in the other domain.
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Director of Text Mining and Database Strategy
> > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > >
> > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > positions, strategies or opinions.
> > > >
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > http://www.indexserverfaq.com
> > > >
> > > >
> > > >
> > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > Hello,
> > > > >
> > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > >
> > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > >
> > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > credentials are right.
> > > > >
> > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > connected to 2005 and it works fine.
> > > > >
> > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > >
> > > > > Thanx,
> > > > > Bige
> > > > >
> > >
> > >
>|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using 4
> part naming.
> John
> "Bige" wrote:
> > Yes, I can connect using Query Analyser.
> >
> > I created the linked server using EM; here it returns "SQL server not
> > existant" when I try to list tables or views...
> >
> > I tried to change the timeout but didn't worked...and it is not a DNS
> > problem
> >
> > About the microsoft kbs were not really helpful...any clues?
> >
> > Thanx,
> > Bige
> >
> > John Bell ha scritto:
> >
> > > Hi
> > >
> > > Connecting with a SQL connection should not matter about the servers being
> > > on different domains. Can you connect with Query Analyser, if you can, then
> > > it would tend to imply that it was configuration of the linked server is the
> > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > check out http://support.microsoft.com/kb/287932 and
> > > http://support.microsoft.com/kb/306199/
> > >
> > > With a VPN there may be delays introduced in the connection, so increasing
> > > timeouts may be necessary, also check that you can access the server via an
> > > IP address, if this works there may be a DNS issue.
> > >
> > > John
> > >
> > > "Bige" wrote:
> > >
> > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > >
> > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > >
> > > > About the ports are the same and both use protocol TCPIP...if I
> > > > connect a SQL 2000 to 2005
> > > > in the same domain with my SQL credentials it works.
> > > >
> > > > The problem is when I try to connect from another network in VPN
> > > > mode...
> > > >
> > > > Thanks for your help, I appreciate it.
> > > > Bige
> > > >
> > > > Hilary Cotter ha scritto:
> > > >
> > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > accounts with the same account names and passwords in both domains? If you
> > > > > use the security context of the one account it should work against the
> > > > > server in the other domain.
> > > > >
> > > > > --
> > > > > Hilary Cotter
> > > > > Director of Text Mining and Database Strategy
> > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > >
> > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > positions, strategies or opinions.
> > > > >
> > > > > Looking for a SQL Server replication book?
> > > > > http://www.nwsu.com/0974973602.html
> > > > >
> > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > http://www.indexserverfaq.com
> > > > >
> > > > >
> > > > >
> > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > Hello,
> > > > > >
> > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > >
> > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > >
> > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > credentials are right.
> > > > > >
> > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > connected to 2005 and it works fine.
> > > > > >
> > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > >
> > > > > > Thanx,
> > > > > > Bige
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
> > Hi
> >
> > That implies it is the configuration of the linked server that may not be
> > working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> > Analyser and if they don't give an error see if you try run a query using 4
> > part naming.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > Yes, I can connect using Query Analyser.
> > >
> > > I created the linked server using EM; here it returns "SQL server not
> > > existant" when I try to list tables or views...
> > >
> > > I tried to change the timeout but didn't worked...and it is not a DNS
> > > problem
> > >
> > > About the microsoft kbs were not really helpful...any clues?
> > >
> > > Thanx,
> > > Bige
> > >
> > > John Bell ha scritto:
> > >
> > > > Hi
> > > >
> > > > Connecting with a SQL connection should not matter about the servers being
> > > > on different domains. Can you connect with Query Analyser, if you can, then
> > > > it would tend to imply that it was configuration of the linked server is the
> > > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > > check out http://support.microsoft.com/kb/287932 and
> > > > http://support.microsoft.com/kb/306199/
> > > >
> > > > With a VPN there may be delays introduced in the connection, so increasing
> > > > timeouts may be necessary, also check that you can access the server via an
> > > > IP address, if this works there may be a DNS issue.
> > > >
> > > > John
> > > >
> > > > "Bige" wrote:
> > > >
> > > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > > >
> > > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > > >
> > > > > About the ports are the same and both use protocol TCPIP...if I
> > > > > connect a SQL 2000 to 2005
> > > > > in the same domain with my SQL credentials it works.
> > > > >
> > > > > The problem is when I try to connect from another network in VPN
> > > > > mode...
> > > > >
> > > > > Thanks for your help, I appreciate it.
> > > > > Bige
> > > > >
> > > > > Hilary Cotter ha scritto:
> > > > >
> > > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > > accounts with the same account names and passwords in both domains? If you
> > > > > > use the security context of the one account it should work against the
> > > > > > server in the other domain.
> > > > > >
> > > > > > --
> > > > > > Hilary Cotter
> > > > > > Director of Text Mining and Database Strategy
> > > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > > >
> > > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > > positions, strategies or opinions.
> > > > > >
> > > > > > Looking for a SQL Server replication book?
> > > > > > http://www.nwsu.com/0974973602.html
> > > > > >
> > > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > > http://www.indexserverfaq.com
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > > Hello,
> > > > > > >
> > > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > > >
> > > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > > >
> > > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > > credentials are right.
> > > > > > >
> > > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > > connected to 2005 and it works fine.
> > > > > > >
> > > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > > >
> > > > > > > Thanx,
> > > > > > > Bige
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
Linked server on a different domain
Hello,
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
Bige
Hi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>
|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegr oups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>
|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
[vbcol=seagreen]
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegr oups.com...
|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
>
>
|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, then
> it would tend to imply that it was configuration of the linked server is the
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via an
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
>
>
|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using 4
> part naming.
> John
> "Bige" wrote:
|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
>
>
sql
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
Bige
Hi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>
|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegr oups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>
|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
[vbcol=seagreen]
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegr oups.com...
|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
>
>
|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, then
> it would tend to imply that it was configuration of the linked server is the
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via an
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
>
>
|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
[vbcol=seagreen]
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using 4
> part naming.
> John
> "Bige" wrote:
|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
>
>
sql
Linked Server not working
I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
fine but suddenly one day things stopped working when I have tested the
connection for link server. It failed and gave me this error. What could be
the possible error . The server which is linked is a remote server running
Informix Database.
"The test connection to the linked server failed."
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(String
cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.Te stConnection()
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ob jectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "INFORMIX_LIBRARY".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
categories.". (.Net SqlClient Data Provider)
Nadia
http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
> fine but suddenly one day things stopped working when I have tested the
> connection for link server. It failed and gave me this error. What could
> be the possible error . The server which is linked is a remote server
> running Informix Database.
>
>
> "The test connection to the linked server failed."
> ===================================
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(String
> cmd)
> at Microsoft.SqlServer.Management.Smo.LinkedServer.Te stConnection()
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ob jectExplorer.LinkedServerConnectionTest.Invoke()
> ===================================
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "INFORMIX_LIBRARY".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
> categories.". (.Net SqlClient Data Provider)
>
>
|||i tried to open the page and read the script. I am a novice to this SQL
2005. I tried to create a query and paste your code into it and tried to
execute it. it gave me the error. I also wanted to know where is stored
procedure located which you were talking about ?..
Kindly help thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Nadia
> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>
|||Nadia
What's error?
>. I also wanted to know where is stored procedure located which you were
>talking about ?..
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>i tried to open the page and read the script. I am a novice to this SQL
>2005. I tried to create a query and paste your code into it and tried to
>execute it. it gave me the error. I also wanted to know where is stored
>procedure located which you were talking about ?..
> Kindly help thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks for a prompt reply Uri.
I also wanted to add the the linkied server is linked to a source .The
source it is linked is a System DSN which is linking is a remote server
running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
the driver that is the cause of error or what ?
And the error is
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'IF@.statusval'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
> Nadia
> What's error?
>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>
|||Nadia
I have not played with Informix database, but have you tried DTS/SSIS (I
assume you have installed Client Access)
See , if you get the error right now
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Thanks for a prompt reply Uri.
>
> I also wanted to add the the linkied server is linked to a source .The
> source it is linked is a System DSN which is linking is a remote server
> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
> the driver that is the cause of error or what ?
>
> And the error is
> Msg 102, Level 15, State 1, Line 9
> Incorrect syntax near 'IF@.statusval'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>
>
|||The linked server is a part of DTS package . I came to know about the linked
server only after the DTS execution was failed and i came to know that the
linked server is not wroking that is why the error is coming. and rest you
know . i just thought that might i used a 64 bit driver and i could do the
trick what you say ? Actually i also havent palyed with the informix
server and the DTS package was doing it tranpsarently. but some how this
linked server is stopped and i came here for support. And i am still clue
less that what could be the case ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O6wSjBogIHA.1132@.TK2MSFTNGP06.phx.gbl...
> Nadia
> I have not played with Informix database, but have you tried DTS/SSIS (I
> assume you have installed Client Access)
> See , if you get the error right now
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
>
Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
fine but suddenly one day things stopped working when I have tested the
connection for link server. It failed and gave me this error. What could be
the possible error . The server which is linked is a remote server running
Informix Database.
"The test connection to the linked server failed."
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(String
cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.Te stConnection()
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ob jectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "INFORMIX_LIBRARY".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
categories.". (.Net SqlClient Data Provider)
Nadia
http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
> fine but suddenly one day things stopped working when I have tested the
> connection for link server. It failed and gave me this error. What could
> be the possible error . The server which is linked is a remote server
> running Informix Database.
>
>
> "The test connection to the linked server failed."
> ===================================
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(String
> cmd)
> at Microsoft.SqlServer.Management.Smo.LinkedServer.Te stConnection()
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ob jectExplorer.LinkedServerConnectionTest.Invoke()
> ===================================
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "INFORMIX_LIBRARY".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
> categories.". (.Net SqlClient Data Provider)
>
>
|||i tried to open the page and read the script. I am a novice to this SQL
2005. I tried to create a query and paste your code into it and tried to
execute it. it gave me the error. I also wanted to know where is stored
procedure located which you were talking about ?..
Kindly help thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Nadia
> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>
|||Nadia
What's error?
>. I also wanted to know where is stored procedure located which you were
>talking about ?..
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>i tried to open the page and read the script. I am a novice to this SQL
>2005. I tried to create a query and paste your code into it and tried to
>execute it. it gave me the error. I also wanted to know where is stored
>procedure located which you were talking about ?..
> Kindly help thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks for a prompt reply Uri.
I also wanted to add the the linkied server is linked to a source .The
source it is linked is a System DSN which is linking is a remote server
running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
the driver that is the cause of error or what ?
And the error is
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'IF@.statusval'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
> Nadia
> What's error?
>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>
|||Nadia
I have not played with Informix database, but have you tried DTS/SSIS (I
assume you have installed Client Access)
See , if you get the error right now
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Thanks for a prompt reply Uri.
>
> I also wanted to add the the linkied server is linked to a source .The
> source it is linked is a System DSN which is linking is a remote server
> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
> the driver that is the cause of error or what ?
>
> And the error is
> Msg 102, Level 15, State 1, Line 9
> Incorrect syntax near 'IF@.statusval'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>
>
|||The linked server is a part of DTS package . I came to know about the linked
server only after the DTS execution was failed and i came to know that the
linked server is not wroking that is why the error is coming. and rest you
know . i just thought that might i used a 64 bit driver and i could do the
trick what you say ? Actually i also havent palyed with the informix
server and the DTS package was doing it tranpsarently. but some how this
linked server is stopped and i came here for support. And i am still clue
less that what could be the case ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O6wSjBogIHA.1132@.TK2MSFTNGP06.phx.gbl...
> Nadia
> I have not played with Informix database, but have you tried DTS/SSIS (I
> assume you have installed Client Access)
> See , if you get the error right now
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
>
Linked Server not working
I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
fine but suddenly one day things stopped working when I have tested the
connection for link server. It failed and gave me this error. What could be
the possible error . The server which is linked is a remote server running
Informix Database.
"The test connection to the linked server failed."
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "INFORMIX_LIBRARY".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
categories.". (.Net SqlClient Data Provider)Nadia
http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
> fine but suddenly one day things stopped working when I have tested the
> connection for link server. It failed and gave me this error. What could
> be the possible error . The server which is linked is a remote server
> running Informix Database.
>
>
> "The test connection to the linked server failed."
> ===================================> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
> cmd)
> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
> ===================================> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "INFORMIX_LIBRARY".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
> categories.". (.Net SqlClient Data Provider)
>
>|||i tried to open the page and read the script. I am a novice to this SQL
2005. I tried to create a query and paste your code into it and tried to
execute it. it gave me the error. I also wanted to know where is stored
procedure located which you were talking about ?..
Kindly help thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Nadia
> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
>> fine but suddenly one day things stopped working when I have tested the
>> connection for link server. It failed and gave me this error. What could
>> be the possible error . The server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
>> linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
>> categories.". (.Net SqlClient Data Provider)
>>
>>
>|||Nadia
What's error?
>. I also wanted to know where is stored procedure located which you were
>talking about ?..
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>i tried to open the page and read the script. I am a novice to this SQL
>2005. I tried to create a query and paste your code into it and tried to
>execute it. it gave me the error. I also wanted to know where is stored
>procedure located which you were talking about ?..
> Kindly help thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
>> fine but suddenly one day things stopped working when I have tested the
>> connection for link server. It failed and gave me this error. What could
>> be the possible error . The server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load
>> locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>|||Thanks for a prompt reply Uri.
I also wanted to add the the linkied server is linked to a source .The
source it is linked is a System DSN which is linking is a remote server
running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
the driver that is the cause of error or what ?
And the error is
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'IF@.statusval'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
> Nadia
> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked is
>> a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load
>> locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>|||Nadia
I have not played with Informix database, but have you tried DTS/SSIS (I
assume you have installed Client Access)
See , if you get the error right now
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Thanks for a prompt reply Uri.
>
> I also wanted to add the the linkied server is linked to a source .The
> source it is linked is a System DSN which is linking is a remote server
> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
> the driver that is the cause of error or what ?
>
> And the error is
> Msg 102, Level 15, State 1, Line 9
> Incorrect syntax near 'IF@.statusval'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable to
>> load locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>>
>|||The linked server is a part of DTS package . I came to know about the linked
server only after the DTS execution was failed and i came to know that the
linked server is not wroking that is why the error is coming. and rest you
know . i just thought that might i used a 64 bit driver and i could do the
trick what you say ? Actually i also havent palyed with the informix
server and the DTS package was doing it tranpsarently. but some how this
linked server is stopped and i came here for support. And i am still clue
less that what could be the case ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O6wSjBogIHA.1132@.TK2MSFTNGP06.phx.gbl...
> Nadia
> I have not played with Informix database, but have you tried DTS/SSIS (I
> assume you have installed Client Access)
> See , if you get the error right now
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> Thanks for a prompt reply Uri.
>>
>> I also wanted to add the the linkied server is linked to a source .The
>> source it is linked is a System DSN which is linking is a remote server
>> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is
>> it the driver that is the cause of error or what ?
>>
>> And the error is
>> Msg 102, Level 15, State 1, Line 9
>> Incorrect syntax near 'IF@.statusval'.
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable
>> to load locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>>
>>
>
Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
fine but suddenly one day things stopped working when I have tested the
connection for link server. It failed and gave me this error. What could be
the possible error . The server which is linked is a remote server running
Informix Database.
"The test connection to the linked server failed."
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "INFORMIX_LIBRARY".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
categories.". (.Net SqlClient Data Provider)Nadia
http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
> fine but suddenly one day things stopped working when I have tested the
> connection for link server. It failed and gave me this error. What could
> be the possible error . The server which is linked is a remote server
> running Informix Database.
>
>
> "The test connection to the linked server failed."
> ===================================> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
> cmd)
> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
> ===================================> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "INFORMIX_LIBRARY".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
> categories.". (.Net SqlClient Data Provider)
>
>|||i tried to open the page and read the script. I am a novice to this SQL
2005. I tried to create a query and paste your code into it and tried to
execute it. it gave me the error. I also wanted to know where is stored
procedure located which you were talking about ?..
Kindly help thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Nadia
> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
>> fine but suddenly one day things stopped working when I have tested the
>> connection for link server. It failed and gave me this error. What could
>> be the possible error . The server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
>> linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
>> categories.". (.Net SqlClient Data Provider)
>>
>>
>|||Nadia
What's error?
>. I also wanted to know where is stored procedure located which you were
>talking about ?..
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>i tried to open the page and read the script. I am a novice to this SQL
>2005. I tried to create a query and paste your code into it and tried to
>execute it. it gave me the error. I also wanted to know where is stored
>procedure located which you were talking about ?..
> Kindly help thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
>> fine but suddenly one day things stopped working when I have tested the
>> connection for link server. It failed and gave me this error. What could
>> be the possible error . The server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load
>> locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>|||Thanks for a prompt reply Uri.
I also wanted to add the the linkied server is linked to a source .The
source it is linked is a System DSN which is linking is a remote server
running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
the driver that is the cause of error or what ?
And the error is
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'IF@.statusval'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
> Nadia
> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked is
>> a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load
>> locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>|||Nadia
I have not played with Informix database, but have you tried DTS/SSIS (I
assume you have installed Client Access)
See , if you get the error right now
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Thanks for a prompt reply Uri.
>
> I also wanted to add the the linkied server is linked to a source .The
> source it is linked is a System DSN which is linking is a remote server
> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
> the driver that is the cause of error or what ?
>
> And the error is
> Msg 102, Level 15, State 1, Line 9
> Incorrect syntax near 'IF@.statusval'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable to
>> load locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>>
>|||The linked server is a part of DTS package . I came to know about the linked
server only after the DTS execution was failed and i came to know that the
linked server is not wroking that is why the error is coming. and rest you
know . i just thought that might i used a 64 bit driver and i could do the
trick what you say ? Actually i also havent palyed with the informix
server and the DTS package was doing it tranpsarently. but some how this
linked server is stopped and i came here for support. And i am still clue
less that what could be the case ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O6wSjBogIHA.1132@.TK2MSFTNGP06.phx.gbl...
> Nadia
> I have not played with Informix database, but have you tried DTS/SSIS (I
> assume you have installed Client Access)
> See , if you get the error right now
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> Thanks for a prompt reply Uri.
>>
>> I also wanted to add the the linkied server is linked to a source .The
>> source it is linked is a System DSN which is linking is a remote server
>> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is
>> it the driver that is the cause of error or what ?
>>
>> And the error is
>> Msg 102, Level 15, State 1, Line 9
>> Incorrect syntax near 'IF@.statusval'.
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable
>> to load locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>>
>>
>
Friday, March 23, 2012
Linked Server Limit in SQL Server 2000
I am about to start working on a project where I would be required to
dynamically create linked servers during the execution of an
application. One of the requirements is for the Link Servers to be
created and dropped before and after the retrieval of the data. My
question is about any type of cap on the number of linked servers SQL
Server 2000 can have registered at any single time. If I find out that
there is some type of cap, I would need to look into another way to
deal with my linked server needs. Thanks for any help/information you
might be able to provide.TBlair (tblair23221@.yahoo.com) writes:
> I am about to start working on a project where I would be required to
> dynamically create linked servers during the execution of an
> application. One of the requirements is for the Link Servers to be
> created and dropped before and after the retrieval of the data. My
> question is about any type of cap on the number of linked servers SQL
> Server 2000 can have registered at any single time. If I find out that
> there is some type of cap, I would need to look into another way to
> deal with my linked server needs. Thanks for any help/information you
> might be able to provide.
dynamically create linked servers during the execution of an
application. One of the requirements is for the Link Servers to be
created and dropped before and after the retrieval of the data. My
question is about any type of cap on the number of linked servers SQL
Server 2000 can have registered at any single time. If I find out that
there is some type of cap, I would need to look into another way to
deal with my linked server needs. Thanks for any help/information you
might be able to provide.TBlair (tblair23221@.yahoo.com) writes:
> I am about to start working on a project where I would be required to
> dynamically create linked servers during the execution of an
> application. One of the requirements is for the Link Servers to be
> created and dropped before and after the retrieval of the data. My
> question is about any type of cap on the number of linked servers SQL
> Server 2000 can have registered at any single time. If I find out that
> there is some type of cap, I would need to look into another way to
> deal with my linked server needs. Thanks for any help/information you
> might be able to provide.
I have no idea whether there are any restriction, but since a linked
server is really just an alias which is being looked up in sysservers
at run time, it is not likely that there should be an issue.
But, hm, what is there is a numeric id in sysservers? Ah, there is,
and it's smallint. So that gives you an upper limit of 32767 linked
servers. Hm, if you add and remove servers constantly, could you still
run out of ids? I tested by dropping a server with id = 5 (I have 19
linked servers), and adding a new. The new server got id = 5, so gaps
are being filled.
Looks like you will be able to sleep well at night.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Monday, March 19, 2012
Linked Server Fails after MDAC 2.8 upgrade
We installed the MDAC 2.8 upgrade and our existing
Microsoft OLE DB Provider for Oracle stopped working.
Other providers were fine, i.e. SQL Server (linking to
another mssql server).
Has anyone had this issue? Or does anyone have any
ideas as to what changed in 2.8 that would affect this
linked server?
Thanks in advance,
MarkI think that may be something related with distributed transactions.
I have some applications that stopped to work when I upgrade to MDAC 2.8.
Trying to solve this I discover that just returning the sql server odbc
driver files to version 2.7 the applications could work correctly... anyway
I think that is a bug in MDAC 2.8
Agrinei
"Mark D" <anonymous@.discussions.microsoft.com> wrote in message
news:816301c4076c$80de9630$a601280a@.phx.gbl...
> We installed the MDAC 2.8 upgrade and our existing
> Microsoft OLE DB Provider for Oracle stopped working.
> Other providers were fine, i.e. SQL Server (linking to
> another mssql server).
> Has anyone had this issue? Or does anyone have any
> ideas as to what changed in 2.8 that would affect this
> linked server?
> Thanks in advance,
> Mark
Microsoft OLE DB Provider for Oracle stopped working.
Other providers were fine, i.e. SQL Server (linking to
another mssql server).
Has anyone had this issue? Or does anyone have any
ideas as to what changed in 2.8 that would affect this
linked server?
Thanks in advance,
MarkI think that may be something related with distributed transactions.
I have some applications that stopped to work when I upgrade to MDAC 2.8.
Trying to solve this I discover that just returning the sql server odbc
driver files to version 2.7 the applications could work correctly... anyway
I think that is a bug in MDAC 2.8
Agrinei
"Mark D" <anonymous@.discussions.microsoft.com> wrote in message
news:816301c4076c$80de9630$a601280a@.phx.gbl...
> We installed the MDAC 2.8 upgrade and our existing
> Microsoft OLE DB Provider for Oracle stopped working.
> Other providers were fine, i.e. SQL Server (linking to
> another mssql server).
> Has anyone had this issue? Or does anyone have any
> ideas as to what changed in 2.8 that would affect this
> linked server?
> Thanks in advance,
> Mark
Linked Server Error
Hello,
I have installed IBM AS/400 Client Access on my machine and then added a new
ODBC Data Source.
I have been working with this ODBC connection on several DTS packages and
everything has worked just fine.
Right now, I need to access some information on my AS/400 server from
several stored procedures on my SQL Server. I already configured a Linked
Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
Driver.
When I click the Tables folder under the new linked server, I can browse
through all the tables on the 400 Database. But when I execute any query
(using OPENQUERY or 4-part names in a normal query) I get the following erro
r:
****************************************
***+++
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32 bits)] Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
0x80004005: ].
****************************************
**
Why is this happening? What is the error? Is there anything I am doing
wrong? I have been "fighting" with this problem for several hours.
Thanks a lot, in advance, for all the help you can give me.Gabriel --
See if this thread helps at all:
http://www.mcse.ms/message979460.html
same thread, different format:
http://www.newsarch.com/archive/new...
54.html
Gabriel Giraldo wrote:
> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>|||It has been awhile since I worked with the AS/400. Based on the error
message, I would look at the client software installed on the SQL end. The
second thing to look at is the syntax used to attempt to get data. As long a
s
the SQL is fully ANSI standard, you should be fine on both ends; if not, the
likelihood of error increases.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Gabriel Giraldo" wrote:
> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>
I have installed IBM AS/400 Client Access on my machine and then added a new
ODBC Data Source.
I have been working with this ODBC connection on several DTS packages and
everything has worked just fine.
Right now, I need to access some information on my AS/400 server from
several stored procedures on my SQL Server. I already configured a Linked
Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
Driver.
When I click the Tables folder under the new linked server, I can browse
through all the tables on the 400 Database. But when I execute any query
(using OPENQUERY or 4-part names in a normal query) I get the following erro
r:
****************************************
***+++
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32 bits)] Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
0x80004005: ].
****************************************
**
Why is this happening? What is the error? Is there anything I am doing
wrong? I have been "fighting" with this problem for several hours.
Thanks a lot, in advance, for all the help you can give me.Gabriel --
See if this thread helps at all:
http://www.mcse.ms/message979460.html
same thread, different format:
http://www.newsarch.com/archive/new...
54.html
Gabriel Giraldo wrote:
> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>|||It has been awhile since I worked with the AS/400. Based on the error
message, I would look at the client software installed on the SQL end. The
second thing to look at is the syntax used to attempt to get data. As long a
s
the SQL is fully ANSI standard, you should be fine on both ends; if not, the
likelihood of error increases.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Gabriel Giraldo" wrote:
> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>
Monday, March 12, 2012
Linked server DTS issue
Hi need help, I am using Linked server to connect to a oracle server and transferring the data to the local tables, it is working fine if the join transaction is not selected, if we select it it fails with the following error: "MSDASQL was unable to run distributed transaction", the join transaction unit is required as I need to rollback the deletion of data from the tables in the first step of package. Any help will be appreciated. Transaction coordinator is running.My Oracle knowledge is pretty thin but I am betting the MSDTC does not work with Oracle. I would google it.
Wednesday, March 7, 2012
Linked Server ?
Hi,
When working queries with link server is it possible to
invoke user defined functions from linked server?
Thanks,
SachiYes, you can invoke a user defined function from linked
server. Though, I am not sure if Table-valued Functions
can be invoked from linked server.
>--Original Message--
>Hi,
> When working queries with link server is it possible to
>invoke user defined functions from linked server?
>
>Thanks,
>Sachi
>.
>
When working queries with link server is it possible to
invoke user defined functions from linked server?
Thanks,
SachiYes, you can invoke a user defined function from linked
server. Though, I am not sure if Table-valued Functions
can be invoked from linked server.
>--Original Message--
>Hi,
> When working queries with link server is it possible to
>invoke user defined functions from linked server?
>
>Thanks,
>Sachi
>.
>
Linked Server : Jobs Failing
Hi,
when i run the following st using linked server its working fine but when same pasted in job and created a job on server A the job failed giving the below error message..
error :
servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
sql st
from server A where linked server has been created with server B)
exec server b.northwind.dbo.temp1
i have used 'useself' as the security option and set other options as default.
both the server are supporting named pipes protocol..!!!
pls help
Sanjay
Hi
If the error message includes the account with which you are trying to access the server, you do not have permission to log in to the server.
Verify the job owner permissions on the linked server, or change the job owner.
Jakus Andras MCDBA
"Sanjay" wrote:
> Hi,
> when i run the following st using linked server its working fine but when same pasted in job and created a job on server A the job failed giving the below error message..
> error :
> servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
> sql stfrom server A where linked server has been created with server B)
> exec server b.northwind.dbo.temp1
> --
> i have used 'useself' as the security option and set other options as default.
> both the server are supporting named pipes protocol..!!!
> pls help
> --
> Sanjay
when i run the following st using linked server its working fine but when same pasted in job and created a job on server A the job failed giving the below error message..
error :
servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
sql st
from server A where linked server has been created with server B)exec server b.northwind.dbo.temp1
i have used 'useself' as the security option and set other options as default.
both the server are supporting named pipes protocol..!!!
pls help
Sanjay
Hi
If the error message includes the account with which you are trying to access the server, you do not have permission to log in to the server.
Verify the job owner permissions on the linked server, or change the job owner.
Jakus Andras MCDBA
"Sanjay" wrote:
> Hi,
> when i run the following st using linked server its working fine but when same pasted in job and created a job on server A the job failed giving the below error message..
> error :
> servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
> sql st
from server A where linked server has been created with server B)> exec server b.northwind.dbo.temp1
> --
> i have used 'useself' as the security option and set other options as default.
> both the server are supporting named pipes protocol..!!!
> pls help
> --
> Sanjay
Linked Server : Jobs Failing
Hi,
when i run the following st using linked server its working fine but when sa
me pasted in job and created a job on server A the job failed giving the bel
ow error message..
error :
servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
sql stfrom server A where linked server has been created with server B)
exec server b.northwind.dbo.temp1
i have used 'useself' as the security option and set other options as defaul
t.
both the server are supporting named pipes protocol..!!!
pls help
--
SanjayHi
If the error message includes the account with which you are trying to acces
s the server, you do not have permission to log in to the server.
Verify the job owner permissions on the linked server, or change the job own
er.
Jakus Andras MCDBA
"Sanjay" wrote:
> Hi,
> when i run the following st using linked server its working fine but when
same pasted in job and created a job on server A the job failed giving the b
elow error message..
> error :
> servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
> sql stfrom server A where linked server has been created with server B)
> exec server b.northwind.dbo.temp1
> --
> i have used 'useself' as the security option and set other options as defa
ult.
> both the server are supporting named pipes protocol..!!!
> pls help
> --
> Sanjay
when i run the following st using linked server its working fine but when sa
me pasted in job and created a job on server A the job failed giving the bel
ow error message..
error :
servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
sql st
from server A where linked server has been created with server B)exec server b.northwind.dbo.temp1
i have used 'useself' as the security option and set other options as defaul
t.
both the server are supporting named pipes protocol..!!!
pls help
--
SanjayHi
If the error message includes the account with which you are trying to acces
s the server, you do not have permission to log in to the server.
Verify the job owner permissions on the linked server, or change the job own
er.
Jakus Andras MCDBA
"Sanjay" wrote:
> Hi,
> when i run the following st using linked server its working fine but when
same pasted in job and created a job on server A the job failed giving the b
elow error message..
> error :
> servera$'. [SQLSTATE 28000] (Error 18456). The step failed.
> sql st
from server A where linked server has been created with server B)> exec server b.northwind.dbo.temp1
> --
> i have used 'useself' as the security option and set other options as defa
ult.
> both the server are supporting named pipes protocol..!!!
> pls help
> --
> Sanjay
Linked server (DB2)
Hello,
I'm trying to create a linked server for the last week and it's still
not working. I'm using "Microsoft OLE DB Provider for DB2". Here is the
error that i have:
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The specified network name
is no longer available.)
And here is the provider string that i use:
EXEC sp_addlinkedserver @.server = 'DB2SRV',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.catalog = 'DB2T',
@.provider = 'DB2OLEDB',
@.provstr =
'NetLib=TCPIP;NetAddr=10.10.10.10;NetPort=1234;nit Cat=DB2T;Default
Schema=admin;PkgCol=admin;IsoLvl=NC;CCSID=37;PCCod ePage=1252;BinAsChar=NO;Data
Source=DB2T'
Thank you !
Hi Mike
I have never use this provider, but I have noticed that you have nitCat in
the connection string instead of InitCat.
John
"Mike" wrote:
> Hello,
> I'm trying to create a linked server for the last week and it's still
> not working. I'm using "Microsoft OLE DB Provider for DB2". Here is the
> error that i have:
> Msg 64, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server. (provider: TCP Provider, error: 0 - The specified network name
> is no longer available.)
> And here is the provider string that i use:
> EXEC sp_addlinkedserver @.server = 'DB2SRV',
> @.srvproduct = 'Microsoft OLE DB Provider for DB2',
> @.catalog = 'DB2T',
> @.provider = 'DB2OLEDB',
> @.provstr =
> 'NetLib=TCPIP;NetAddr=10.10.10.10;NetPort=1234;nit Cat=DB2T;Default
> Schema=admin;PkgCol=admin;IsoLvl=NC;CCSID=37;PCCod ePage=1252;BinAsChar=NO;Data
> Source=DB2T'
> Thank you !
>
I'm trying to create a linked server for the last week and it's still
not working. I'm using "Microsoft OLE DB Provider for DB2". Here is the
error that i have:
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The specified network name
is no longer available.)
And here is the provider string that i use:
EXEC sp_addlinkedserver @.server = 'DB2SRV',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.catalog = 'DB2T',
@.provider = 'DB2OLEDB',
@.provstr =
'NetLib=TCPIP;NetAddr=10.10.10.10;NetPort=1234;nit Cat=DB2T;Default
Schema=admin;PkgCol=admin;IsoLvl=NC;CCSID=37;PCCod ePage=1252;BinAsChar=NO;Data
Source=DB2T'
Thank you !
Hi Mike
I have never use this provider, but I have noticed that you have nitCat in
the connection string instead of InitCat.
John
"Mike" wrote:
> Hello,
> I'm trying to create a linked server for the last week and it's still
> not working. I'm using "Microsoft OLE DB Provider for DB2". Here is the
> error that i have:
> Msg 64, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server. (provider: TCP Provider, error: 0 - The specified network name
> is no longer available.)
> And here is the provider string that i use:
> EXEC sp_addlinkedserver @.server = 'DB2SRV',
> @.srvproduct = 'Microsoft OLE DB Provider for DB2',
> @.catalog = 'DB2T',
> @.provider = 'DB2OLEDB',
> @.provstr =
> 'NetLib=TCPIP;NetAddr=10.10.10.10;NetPort=1234;nit Cat=DB2T;Default
> Schema=admin;PkgCol=admin;IsoLvl=NC;CCSID=37;PCCod ePage=1252;BinAsChar=NO;Data
> Source=DB2T'
> Thank you !
>
Subscribe to:
Posts (Atom)