Hello
im trying to connect to a remote server over an internet vpn connection
i asked admin of the remote location to open port 1433 in his firewall, he
did and im able to register the server successfully by IP address on
enterprise manager
i can also make a connection with query analyzer with no problem and select
from tables normally
however when i try to establish a linked server using OLEDB for SQL server ,
connection fails , properties of the connection is ,
product name: empty
data source: the IP address of the server
provider string and catalog: empty
security: no rows in local server login to remote server login mapping but
chose last option in
for a login not defined in the list above connection will: i chose
be made using this security context and i wrote sa and password i used
before in registering server as a test
what is wrong , does linking a server require opening additional ports in
firewall ' which query analyzer does not need if server is registered
normally not linked?
any help very appreciated
BassamBassam (bassam@.nptco.com.eg) writes:
> im trying to connect to a remote server over an internet vpn connection
> i asked admin of the remote location to open port 1433 in his firewall,
> he did and im able to register the server successfully by IP address on
> enterprise manager i can also make a connection with query analyzer with
> no problem and select from tables normally
> however when i try to establish a linked server using OLEDB for SQL
> server , connection fails , properties of the connection is ,
> product name: empty
> data source: the IP address of the server
> provider string and catalog: empty
And the error message is?
You could consider defining a provider string for the linked server. That's
all I can suggest without knowledge about the error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Error msg is :
SQL Server does not exist or access denied.
i tried to define a provider string also by format
srever=ip address;database=xx;user id=xx ; password=xx
but did not work either
question is do i have to open port UDP 1434 also in the firewall ?
Regards
Bassam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760ACD558EC9Yazorman@.127.0.0.1...
> Bassam (bassam@.nptco.com.eg) writes:
> And the error message is?
> You could consider defining a provider string for the linked server.
> That's
> all I can suggest without knowledge about the error.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Bassam (bassam@.nptco.com.eg) writes:
> Error msg is :
> SQL Server does not exist or access denied.
> i tried to define a provider string also by format
> srever=ip address;database=xx;user id=xx ; password=xx
> but did not work either
> question is do i have to open port UDP 1434 also in the firewall ?
I don't think that would matter.
Is SQL Server running on the same machine as you run QA/EM from? If not,
maybe the remote DBA only opened the firewall for one IP address?
Under which Windows account do SQL Server run? You can check this in
Computer Management/Services. If SQL Server is running under LocalSystem
try changing it to domain account, and restart SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Wednesday, March 28, 2012
Linked Server PROBLEM For MySQL Database
I created a ODBC DSN on my Windows Server for a remote UNIX box MySQL database. The TEST button returns the 'successful' message for the MySQL databaser. Also, when I use an ODBC DSNless string I found, that also successfully connects when I use it in some ASP pages.
However, I can not get a successful connection in the Enterprise Manager trying to use the DSN I created nor the DSNless string I used in the ASP pages. Can anyone tell me how to config the Enterprise Manager Linked Server settings listed below to connect to the MySQL database(s).
I added a new linked server, and selected the Microsoft OLEDB Provider for ODBC Drivers as the Provider name.
Does anyone know how do I configure the items below:
On the General tab:" option.
Product Name: xxxxxxxxxxxxxxxxxx
Data Source: xxxxxxx
Provider String: xxxxxxxx
Location: xxxxxxx
Catalog: xxxxx
On the Security tab:" option.
Remote Login: xxxxxx
With password: xxxxxx
Thanks.I found a wonderful solution to this problem...
http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.html
In case the link is gone, here is the way to create a Linked Server to a MySQL database, in a nutshell...
1) Create a System DSN on the server running MSSQL, connecting to the MySQL database. Let's call it <the_new_mysql_dsn>.
2) Run the following stored procedure, with appropriate arguments...
EXEC sp_addlinkedserver @.server='<linked_server_label>', @.srvproduct='MySQL', @.provider='MSDASQL', @.datasrc='<the_new_mysql_dsn>'
-------
You can then open up ol' Enterprise Manager and see the new linked server added in. Right click on it to see the Properties. On mine, the settings showed up as...
Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: MySQL
Data source: <the_new_mysql_dsn>
Everything else is blank.
-------
Incidentally, just to try it out, I deleted my new linked server, and then added a new one, using the following settings...
Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Provider string: DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=<mysql_database>;SERVER=<mysql_server>;UID=;PORT=;OPTION=0;STMT=;
And it works as well (the connection string was generated within Visual Interdev). I am using no passwords yet, I'm still in the early development phases, but this should handle the basics of connecting to a MySQL database from SQL Server.
However, I can not get a successful connection in the Enterprise Manager trying to use the DSN I created nor the DSNless string I used in the ASP pages. Can anyone tell me how to config the Enterprise Manager Linked Server settings listed below to connect to the MySQL database(s).
I added a new linked server, and selected the Microsoft OLEDB Provider for ODBC Drivers as the Provider name.
Does anyone know how do I configure the items below:
On the General tab:" option.
Product Name: xxxxxxxxxxxxxxxxxx
Data Source: xxxxxxx
Provider String: xxxxxxxx
Location: xxxxxxx
Catalog: xxxxx
On the Security tab:" option.
Remote Login: xxxxxx
With password: xxxxxx
Thanks.I found a wonderful solution to this problem...
http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.html
In case the link is gone, here is the way to create a Linked Server to a MySQL database, in a nutshell...
1) Create a System DSN on the server running MSSQL, connecting to the MySQL database. Let's call it <the_new_mysql_dsn>.
2) Run the following stored procedure, with appropriate arguments...
EXEC sp_addlinkedserver @.server='<linked_server_label>', @.srvproduct='MySQL', @.provider='MSDASQL', @.datasrc='<the_new_mysql_dsn>'
-------
You can then open up ol' Enterprise Manager and see the new linked server added in. Right click on it to see the Properties. On mine, the settings showed up as...
Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: MySQL
Data source: <the_new_mysql_dsn>
Everything else is blank.
-------
Incidentally, just to try it out, I deleted my new linked server, and then added a new one, using the following settings...
Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Provider string: DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=<mysql_database>;SERVER=<mysql_server>;UID=;PORT=;OPTION=0;STMT=;
And it works as well (the connection string was generated within Visual Interdev). I am using no passwords yet, I'm still in the early development phases, but this should handle the basics of connecting to a MySQL database from SQL Server.
linked server problem
i linked to a remote server (sql 2000) using sp_addlinkedserver
when i tried to insert data in a table in the remote server it is throwing the following error.
i used transaction in the query.
The operation could not be performed because OLE DB
provider "SQLNCLI" for linked server "server1" was unable to begin a
distributed transaction. - Export Operation Failed
please find me way
thx in advance..
This is not a SQL Server Integration Services issue, so I have moved it to a more appropriate forum. Linked servers are in the are data access/engine not part of the SSIS toolset.
The error seems a bit sparse, I would have expected a bit more about why it cannot create a distributed transaction. Since the distributed transaction appears to be the issue, have you tested this outside of a linked server? There are plenty of KB articles on http://support.microsoft.com that discuss distributed transaction issues, try searching there. I have found the DTCPing, WINRM and DTCTester tools useful for diagnosing such issues, again search the KB or them.
sqllinked server problem
Hi all, I am hitting up against a wall when trying to execute a stored proc
running on a remote linked sql server. I have verified that stored proc
executes fine locally on the remote server. The remote server name is
"brserver-i". Here is what I get:-
exec BRSERVER-I.master.dbo.sp_dothework
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'I'.
Any ideas?Try this.
exec [BRSERVER-I].master.dbo.sp_dothework
<param@.community.nospam> wrote in message
news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hi all, I am hitting up against a wall when trying to execute a stored
> proc running on a remote linked sql server. I have verified that stored
> proc executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>|||Do not use "-" to name databases, I have found errors with other application
s
trying to access a database like this one.
exec [BRSERVER-I].master.dbo.sp_dothework
go
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sure. Qualify the object using the four-part name.
insert into server_name.database_name.owner.object_name
select ...
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Sure. Qualify the object using the four-part name.
> insert into server_name.database_name.owner.object_name
> select ...
>
> AMB
> "param@.community.nospam" wrote:
>|||Looks like this worked too:-
exec "BRSERVER-I".master.dbo.sp_dothework
thanks!
"ykchakri" <ykchakri@.hotmail.com> wrote in message
news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
> Try this.
> exec [BRSERVER-I].master.dbo.sp_dothework
> <param@.community.nospam> wrote in message
> news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
>|||But in order it to work you should have the setting of SET QUOTED_IDENTIFIER
set to ON.
Example:
SET QUOTED_IDENTIFIER OFF
go
use "master"
go
SET QUOTED_IDENTIFIER ON
go
AMB
"param@.community.nospam" wrote:
> Looks like this worked too:-
> exec "BRSERVER-I".master.dbo.sp_dothework
> thanks!
> "ykchakri" <ykchakri@.hotmail.com> wrote in message
> news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
>
>|||You are correct. thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:928D7DC3-D605-429E-AF7E-BA3921057E7F@.microsoft.com...
> But in order it to work you should have the setting of SET
> QUOTED_IDENTIFIER
> set to ON.
> Example:
> SET QUOTED_IDENTIFIER OFF
> go
> use "master"
> go
> SET QUOTED_IDENTIFIER ON
> go
>
> AMB
>
> "param@.community.nospam" wrote:
>
running on a remote linked sql server. I have verified that stored proc
executes fine locally on the remote server. The remote server name is
"brserver-i". Here is what I get:-
exec BRSERVER-I.master.dbo.sp_dothework
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'I'.
Any ideas?Try this.
exec [BRSERVER-I].master.dbo.sp_dothework
<param@.community.nospam> wrote in message
news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hi all, I am hitting up against a wall when trying to execute a stored
> proc running on a remote linked sql server. I have verified that stored
> proc executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>|||Do not use "-" to name databases, I have found errors with other application
s
trying to access a database like this one.
exec [BRSERVER-I].master.dbo.sp_dothework
go
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sure. Qualify the object using the four-part name.
insert into server_name.database_name.owner.object_name
select ...
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Sure. Qualify the object using the four-part name.
> insert into server_name.database_name.owner.object_name
> select ...
>
> AMB
> "param@.community.nospam" wrote:
>|||Looks like this worked too:-
exec "BRSERVER-I".master.dbo.sp_dothework
thanks!
"ykchakri" <ykchakri@.hotmail.com> wrote in message
news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
> Try this.
> exec [BRSERVER-I].master.dbo.sp_dothework
> <param@.community.nospam> wrote in message
> news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
>|||But in order it to work you should have the setting of SET QUOTED_IDENTIFIER
set to ON.
Example:
SET QUOTED_IDENTIFIER OFF
go
use "master"
go
SET QUOTED_IDENTIFIER ON
go
AMB
"param@.community.nospam" wrote:
> Looks like this worked too:-
> exec "BRSERVER-I".master.dbo.sp_dothework
> thanks!
> "ykchakri" <ykchakri@.hotmail.com> wrote in message
> news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
>
>|||You are correct. thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:928D7DC3-D605-429E-AF7E-BA3921057E7F@.microsoft.com...
> But in order it to work you should have the setting of SET
> QUOTED_IDENTIFIER
> set to ON.
> Example:
> SET QUOTED_IDENTIFIER OFF
> go
> use "master"
> go
> SET QUOTED_IDENTIFIER ON
> go
>
> AMB
>
> "param@.community.nospam" wrote:
>
linked server problem
linked to a remote server (sql 2000) using sp_addlinkedserver
when i tried to insert data in a table in the remote server it is throwing the following error.
i used transaction in the query.
The operation could not be performed because OLE DB
provider "SQLNCLI" for linked server "server1" was unable to begin a
distributed transaction. - Export Operation Failed
please find me way
thx in advance..
Let's try the data access forum.sql
Monday, March 26, 2012
Linked Server Port 1433 Alt
I have a Sql Server 2k that needs a linked server set up to connect to a
remote SQL Server 2k. The remote server is behind a firewall, and I don't
want to open up port 1433 to the whole world.
What other options are there to set up a linked server that is secure?
What is the recommended solution?
You could run SQL Server on a different port, and open that up in your
firewall instead.
Jamie Clancy
Ulysses Systems, London
"Dan" wrote:
> I have a Sql Server 2k that needs a linked server set up to connect to a
> remote SQL Server 2k. The remote server is behind a firewall, and I don't
> want to open up port 1433 to the whole world.
> What other options are there to set up a linked server that is secure?
> What is the recommended solution?
>
remote SQL Server 2k. The remote server is behind a firewall, and I don't
want to open up port 1433 to the whole world.
What other options are there to set up a linked server that is secure?
What is the recommended solution?
You could run SQL Server on a different port, and open that up in your
firewall instead.
Jamie Clancy
Ulysses Systems, London
"Dan" wrote:
> I have a Sql Server 2k that needs a linked server set up to connect to a
> remote SQL Server 2k. The remote server is behind a firewall, and I don't
> want to open up port 1433 to the whole world.
> What other options are there to set up a linked server that is secure?
> What is the recommended solution?
>
Linked Server Port 1433 Alt
I have a Sql Server 2k that needs a linked server set up to connect to a
remote SQL Server 2k. The remote server is behind a firewall, and I don't
want to open up port 1433 to the whole world.
What other options are there to set up a linked server that is secure?
What is the recommended solution?You could run SQL Server on a different port, and open that up in your
firewall instead.
Jamie Clancy
Ulysses Systems, London
"Dan" wrote:
> I have a Sql Server 2k that needs a linked server set up to connect to a
> remote SQL Server 2k. The remote server is behind a firewall, and I don't
> want to open up port 1433 to the whole world.
> What other options are there to set up a linked server that is secure?
> What is the recommended solution?
>
remote SQL Server 2k. The remote server is behind a firewall, and I don't
want to open up port 1433 to the whole world.
What other options are there to set up a linked server that is secure?
What is the recommended solution?You could run SQL Server on a different port, and open that up in your
firewall instead.
Jamie Clancy
Ulysses Systems, London
"Dan" wrote:
> I have a Sql Server 2k that needs a linked server set up to connect to a
> remote SQL Server 2k. The remote server is behind a firewall, and I don't
> want to open up port 1433 to the whole world.
> What other options are there to set up a linked server that is secure?
> What is the recommended solution?
>
linked server permissions
I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
Dan D.
If you are using linked server, check this out....
http://databasejournal.com/features/...le.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.
|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features/...le.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
Dan D.
If you are using linked server, check this out....
http://databasejournal.com/features/...le.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.
|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features/...le.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
linked server permissions
I have a query on a web site that I'm trying to run. I get this error: Acces
s
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features...cle.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Acc
ess
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features...cle.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
>
s
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features...cle.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Acc
ess
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features...cle.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
>
linked server permissions
I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features/mssql/article.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
> If you are using linked server, check this out....
> http://databasejournal.com/features/mssql/article.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
> > I have a query on a web site that I'm trying to run. I get this error: Access
> > to the remote server is denied because no login-mapping exists.
> >
> > I can run the query as myself (sysadmin). The user that has problems only
> > has db_datareader and db_datawriter permissions. Does this user need
> > different permissions?
> >
> > Thanks,
> > --
> > Dan D.
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features/mssql/article.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
> If you are using linked server, check this out....
> http://databasejournal.com/features/mssql/article.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
> > I have a query on a web site that I'm trying to run. I get this error: Access
> > to the remote server is denied because no login-mapping exists.
> >
> > I can run the query as myself (sysadmin). The user that has problems only
> > has db_datareader and db_datawriter permissions. Does this user need
> > different permissions?
> >
> > Thanks,
> > --
> > Dan D.
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 null
If I remote on to sql05 and open management studio
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476[ /url]
BUTTONS:
OK
Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476[ /url]
BUTTONS:
OK
Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
linked server null
If I remote on to sql05 and open management studio
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
http://go.microsoft.com/fwlink?Prod...54&LinkId=20476
BUTTONS:
OK
--Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
http://go.microsoft.com/fwlink?Prod...54&LinkId=20476
BUTTONS:
OK
--Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Monday, March 12, 2012
Linked Server CPU hog?
We currently use linked servers at our location for our main SQL server to
link to Oracle, other SQL servers and a remote DB2 (via Informix driver) via
linked servers.
Our DBA has just informed us that he will be cutting all linked servers in
due course as they are causing a CPU problem.
In a nutshell, we were told, that when a transaction across a linked server
connection fails, it also appears to hold open the connection and continues
to use resources that can only be reclaimed by restarting the server.
We currently have SQL2K SP4 installed and are due to go to SQL2K5 later this
year.
Has anyone else come across this problem by chance? Is there a resolution in
2K or has it been resolved in 2K5.
Any information would be of value as we are currently faced with re-writing
our applications that use the linked servers.
Thanks,you use msdtc?
use tg4sql?
why transation fail?
Aleksandar Grbic
"Logicalman" wrote:
> We currently use linked servers at our location for our main SQL server to
> link to Oracle, other SQL servers and a remote DB2 (via Informix driver) v
ia
> linked servers.
> Our DBA has just informed us that he will be cutting all linked servers in
> due course as they are causing a CPU problem.
> In a nutshell, we were told, that when a transaction across a linked serve
r
> connection fails, it also appears to hold open the connection and continue
s
> to use resources that can only be reclaimed by restarting the server.
> We currently have SQL2K SP4 installed and are due to go to SQL2K5 later th
is
> year.
> Has anyone else come across this problem by chance? Is there a resolution
in
> 2K or has it been resolved in 2K5.
> Any information would be of value as we are currently faced with re-writin
g
> our applications that use the linked servers.
> Thanks,
>
link to Oracle, other SQL servers and a remote DB2 (via Informix driver) via
linked servers.
Our DBA has just informed us that he will be cutting all linked servers in
due course as they are causing a CPU problem.
In a nutshell, we were told, that when a transaction across a linked server
connection fails, it also appears to hold open the connection and continues
to use resources that can only be reclaimed by restarting the server.
We currently have SQL2K SP4 installed and are due to go to SQL2K5 later this
year.
Has anyone else come across this problem by chance? Is there a resolution in
2K or has it been resolved in 2K5.
Any information would be of value as we are currently faced with re-writing
our applications that use the linked servers.
Thanks,you use msdtc?
use tg4sql?
why transation fail?
Aleksandar Grbic
"Logicalman" wrote:
> We currently use linked servers at our location for our main SQL server to
> link to Oracle, other SQL servers and a remote DB2 (via Informix driver) v
ia
> linked servers.
> Our DBA has just informed us that he will be cutting all linked servers in
> due course as they are causing a CPU problem.
> In a nutshell, we were told, that when a transaction across a linked serve
r
> connection fails, it also appears to hold open the connection and continue
s
> to use resources that can only be reclaimed by restarting the server.
> We currently have SQL2K SP4 installed and are due to go to SQL2K5 later th
is
> year.
> Has anyone else come across this problem by chance? Is there a resolution
in
> 2K or has it been resolved in 2K5.
> Any information would be of value as we are currently faced with re-writin
g
> our applications that use the linked servers.
> Thanks,
>
Linked Server CPU hog?
We currently use linked servers at our location for our main SQL server to
link to Oracle, other SQL servers and a remote DB2 (via Informix driver) via
linked servers.
Our DBA has just informed us that he will be cutting all linked servers in
due course as they are causing a CPU problem.
In a nutshell, we were told, that when a transaction across a linked server
connection fails, it also appears to hold open the connection and continues
to use resources that can only be reclaimed by restarting the server.
We currently have SQL2K SP4 installed and are due to go to SQL2K5 later this
year.
Has anyone else come across this problem by chance? Is there a resolution in
2K or has it been resolved in 2K5.
Any information would be of value as we are currently faced with re-writing
our applications that use the linked servers.
Thanks,you use msdtc?
use tg4sql?
why transation fail?
Aleksandar Grbic
"Logicalman" wrote:
> We currently use linked servers at our location for our main SQL server to
> link to Oracle, other SQL servers and a remote DB2 (via Informix driver) via
> linked servers.
> Our DBA has just informed us that he will be cutting all linked servers in
> due course as they are causing a CPU problem.
> In a nutshell, we were told, that when a transaction across a linked server
> connection fails, it also appears to hold open the connection and continues
> to use resources that can only be reclaimed by restarting the server.
> We currently have SQL2K SP4 installed and are due to go to SQL2K5 later this
> year.
> Has anyone else come across this problem by chance? Is there a resolution in
> 2K or has it been resolved in 2K5.
> Any information would be of value as we are currently faced with re-writing
> our applications that use the linked servers.
> Thanks,
>
link to Oracle, other SQL servers and a remote DB2 (via Informix driver) via
linked servers.
Our DBA has just informed us that he will be cutting all linked servers in
due course as they are causing a CPU problem.
In a nutshell, we were told, that when a transaction across a linked server
connection fails, it also appears to hold open the connection and continues
to use resources that can only be reclaimed by restarting the server.
We currently have SQL2K SP4 installed and are due to go to SQL2K5 later this
year.
Has anyone else come across this problem by chance? Is there a resolution in
2K or has it been resolved in 2K5.
Any information would be of value as we are currently faced with re-writing
our applications that use the linked servers.
Thanks,you use msdtc?
use tg4sql?
why transation fail?
Aleksandar Grbic
"Logicalman" wrote:
> We currently use linked servers at our location for our main SQL server to
> link to Oracle, other SQL servers and a remote DB2 (via Informix driver) via
> linked servers.
> Our DBA has just informed us that he will be cutting all linked servers in
> due course as they are causing a CPU problem.
> In a nutshell, we were told, that when a transaction across a linked server
> connection fails, it also appears to hold open the connection and continues
> to use resources that can only be reclaimed by restarting the server.
> We currently have SQL2K SP4 installed and are due to go to SQL2K5 later this
> year.
> Has anyone else come across this problem by chance? Is there a resolution in
> 2K or has it been resolved in 2K5.
> Any information would be of value as we are currently faced with re-writing
> our applications that use the linked servers.
> Thanks,
>
Linked server connection
I use a system with one main server (SQL 2005) and three other remote linked
servers. I need to know if one of remote server is running or not to avoid m
y
procedure fails. My procedure contains remote query and distributred
transaction. If one of the remote server is not running and i know, i can
avoid failure of my procedure.
How can i know remote server state (is running or not)?
ThanksYou could do a simple SELECT on a table on linked server. See if you get a
response
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> I use a system with one main server (SQL 2005) and three other remote
linked
> servers. I need to know if one of remote server is running or not to avoid
my
> procedure fails. My procedure contains remote query and distributred
> transaction. If one of the remote server is not running and i know, i can
> avoid failure of my procedure.
> How can i know remote server state (is running or not)?
> Thanks|||If i run a SELECT i've got this error:
...
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
I don't want my procedure fails with error if one server is not running.
How can i catch this error or knowing if my server is running or not?
Thanks
"Jack Vamvas" wrote:
> You could do a simple SELECT on a table on linked server. See if you get a
> response
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> linked
> my
>
>|||Trap the exception in a TRY...CATCH block.
You could also design a CLR procedure to check for servers via SMO.
ML
http://milambda.blogspot.com/|||I need to do this all in my SQL stored procedure, not from a C# class or cod
e.
I've got a sp that run query on linked server, begin a distributed
transaction and inf ok commit. If one of the linked server is not running th
e
SP fails. I need to control this and to need to know if the server runs
before run the query and the transaction, but inside my SP.
Tanks.
"ML" wrote:
> Trap the exception in a TRY...CATCH block.
> You could also design a CLR procedure to check for servers via SMO.
>
> ML
> --
> http://milambda.blogspot.com/
servers. I need to know if one of remote server is running or not to avoid m
y
procedure fails. My procedure contains remote query and distributred
transaction. If one of the remote server is not running and i know, i can
avoid failure of my procedure.
How can i know remote server state (is running or not)?
ThanksYou could do a simple SELECT on a table on linked server. See if you get a
response
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> I use a system with one main server (SQL 2005) and three other remote
linked
> servers. I need to know if one of remote server is running or not to avoid
my
> procedure fails. My procedure contains remote query and distributred
> transaction. If one of the remote server is not running and i know, i can
> avoid failure of my procedure.
> How can i know remote server state (is running or not)?
> Thanks|||If i run a SELECT i've got this error:
...
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
I don't want my procedure fails with error if one server is not running.
How can i catch this error or knowing if my server is running or not?
Thanks
"Jack Vamvas" wrote:
> You could do a simple SELECT on a table on linked server. See if you get a
> response
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> linked
> my
>
>|||Trap the exception in a TRY...CATCH block.
You could also design a CLR procedure to check for servers via SMO.
ML
http://milambda.blogspot.com/|||I need to do this all in my SQL stored procedure, not from a C# class or cod
e.
I've got a sp that run query on linked server, begin a distributed
transaction and inf ok commit. If one of the linked server is not running th
e
SP fails. I need to control this and to need to know if the server runs
before run the query and the transaction, but inside my SP.
Tanks.
"ML" wrote:
> Trap the exception in a TRY...CATCH block.
> You could also design a CLR procedure to check for servers via SMO.
>
> ML
> --
> http://milambda.blogspot.com/
Linked server config prob
SQL 7.0
Trying to create a linked server, but I always get the
error message below. I've created a successful ODBC
connection to the remote server and I use that name in
the config process.
"Error 6: Specified SQL Server not found."
Any ideas?
Thanks,
Don"Don" <ddachner@.hotmail.com> wrote in message
news:a85701c3eb7e$01fc1450$a601280a@.phx.gbl...
Are you creating a linked server from SQL Server v7 to SQL Server v7? If so,
what security mode is in place and does the account have proper access?
Another possibility is name resolution -- try using the IP address instead
of the NetBIOS name.
Steve|||Yes, trying to link two v7 servers.
The security mode is SQL/Windows on both.
I'm using the sa account which is the same on both.
I've tried using the IP address, which didn't give an
error message, but when I tried a query using the 4 part
name, I get a syntax error on the dots of the servername,
i.e, 123.111.333.555.DBNAME.dbo.TABLENAME there's a
syntax error "near" where the "." is of the 123.111 part
of the name so i gave up on that approach.
Don
SQL Server v7? If so,
proper access?
IP address instead
Trying to create a linked server, but I always get the
error message below. I've created a successful ODBC
connection to the remote server and I use that name in
the config process.
"Error 6: Specified SQL Server not found."
Any ideas?
Thanks,
Don"Don" <ddachner@.hotmail.com> wrote in message
news:a85701c3eb7e$01fc1450$a601280a@.phx.gbl...
quote:
> SQL 7.0
> Trying to create a linked server, but I always get the
> error message below. I've created a successful ODBC
> connection to the remote server and I use that name in
> the config process.
> "Error 6: Specified SQL Server not found."
Are you creating a linked server from SQL Server v7 to SQL Server v7? If so,
what security mode is in place and does the account have proper access?
Another possibility is name resolution -- try using the IP address instead
of the NetBIOS name.
Steve|||Yes, trying to link two v7 servers.
The security mode is SQL/Windows on both.
I'm using the sa account which is the same on both.
I've tried using the IP address, which didn't give an
error message, but when I tried a query using the 4 part
name, I get a syntax error on the dots of the servername,
i.e, 123.111.333.555.DBNAME.dbo.TABLENAME there's a
syntax error "near" where the "." is of the 123.111 part
of the name so i gave up on that approach.
Don
quote:
>--Original Message--
>"Don" <ddachner@.hotmail.com> wrote in message
>news:a85701c3eb7e$01fc1450$a601280a@.phx.gbl...
>Are you creating a linked server from SQL Server v7 to
SQL Server v7? If so,
quote:
>what security mode is in place and does the account have
proper access?
quote:
>Another possibility is name resolution -- try using the
IP address instead
quote:
>of the NetBIOS name.
>Steve
>
>.
>
Friday, March 9, 2012
Linked server communication
Hi everyone,
I've got problem querying remote tables via a specific linked server.
The server from which I execute the query is an SQLServer2005 and the linked server is an SQLServer2000.
If I do select * from <linked server>.<database name>.<db owner>.<Table1> after a while I get the following error message
"Server: Msg 10054, Level 16, State 1, Line 0, TCP Provider: An existing connection was forcibly closed by the remote host."
I execute the query using profiler and I got the following profiler error: 'OLE DB provider "Unknown" for linked server "(null)" supported the schema lock interface, but returned "0x80040e96" for "ReleaseSchemaLock".' (The query returns a subset of records before it is forcibly stopped)
The strange thing is that i used to run the same query a week or two ago and faced no problem.
Please help.
Linked server communication
Hi everyone,
I've got problem querying remote tables via a specific linked server.
The server from which I execute the query is an SQLServer2005 and the linked server is an SQLServer2000.
If I do select * from <linked server>.<database name>.<db owner>.<Table1> after a while I get the following error message
"Server: Msg 10054, Level 16, State 1, Line 0, TCP Provider: An existing connection was forcibly closed by the remote host."
I execute the query using profiler and I got the following profiler error: 'OLE DB provider "Unknown" for linked server "(null)" supported the schema lock interface, but returned "0x80040e96" for "ReleaseSchemaLock".' (The query returns a subset of records before it is forcibly stopped)
The strange thing is that i used to run the same query a week or two ago and faced no problem.
Please help.
Linked server behavior not funny at all
Hi All
My procedure uses remote database and remote server name as parameters. Using these parameter the proc builds a dynamic sql which should insert records into a table on local server. This should be so simple but it isn't.
The process freezes when I am running it from query analyzer.
When I kill it, it remains in the killed/ rollback state indefinitely. You cannot stop sql server service now as spid 3 blocks on this process. So the only recourse is to kill sqlserver process from taskmanager or restart the server. horrid!!!
I can run the dynamic sql (being executed in the proc) in query analyzer with the login used for linking the servers.
What is happening here?
Thanks for reading and your help
Arun
PS
From my experience over the last 2-3 days stress testing linked servers, it appears that MS has spent minimal time testing linked server functionality. It works when I do select * from srv2.db2.dbo.tbl but anything more, it falls apart.
is it also your experience?
Hi,
Could you please paste the code of the stored proc? As I understand it, you
are trying to pass a remote server and remote db name to a stored proc, and
that will take the data located on that remote data source and insert it
into a local table on the local server?
How long have you waited for the unresponsive behaviour?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Hi Vikram
Thanks for your response. Since posting, I have changed the process to avoid the linked server by putting data locally (delivery pressure). So I don't have the code snippets ready at hand. I will create new ones and post them.
But this is basically what I was trying.
The insert takes less than no time when done locally. When I do it across linked servers, it wasn't done in 7 minutes and I killed it. There were no records in the destination table.
-- dynamic sql in proc
select * from srv2.db.dbo.tbl a where fld3 = (select max(fld3) from srv2.db.dbo.tbl where fld1 = a.fld1 and fld2 = a.fld2)
proc is fired from local server
insert tbl2 exec proc @.srv, @.db
Arun
"Vikram Jayaram [MS]" wrote:
> Hi,
> Could you please paste the code of the stored proc? As I understand it, you
> are trying to pass a remote server and remote db name to a stored proc, and
> that will take the data located on that remote data source and insert it
> into a local table on the local server?
> How long have you waited for the unresponsive behaviour?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
My procedure uses remote database and remote server name as parameters. Using these parameter the proc builds a dynamic sql which should insert records into a table on local server. This should be so simple but it isn't.
The process freezes when I am running it from query analyzer.
When I kill it, it remains in the killed/ rollback state indefinitely. You cannot stop sql server service now as spid 3 blocks on this process. So the only recourse is to kill sqlserver process from taskmanager or restart the server. horrid!!!
I can run the dynamic sql (being executed in the proc) in query analyzer with the login used for linking the servers.
What is happening here?
Thanks for reading and your help
Arun
PS
From my experience over the last 2-3 days stress testing linked servers, it appears that MS has spent minimal time testing linked server functionality. It works when I do select * from srv2.db2.dbo.tbl but anything more, it falls apart.
is it also your experience?
Hi,
Could you please paste the code of the stored proc? As I understand it, you
are trying to pass a remote server and remote db name to a stored proc, and
that will take the data located on that remote data source and insert it
into a local table on the local server?
How long have you waited for the unresponsive behaviour?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Hi Vikram
Thanks for your response. Since posting, I have changed the process to avoid the linked server by putting data locally (delivery pressure). So I don't have the code snippets ready at hand. I will create new ones and post them.
But this is basically what I was trying.
The insert takes less than no time when done locally. When I do it across linked servers, it wasn't done in 7 minutes and I killed it. There were no records in the destination table.
-- dynamic sql in proc
select * from srv2.db.dbo.tbl a where fld3 = (select max(fld3) from srv2.db.dbo.tbl where fld1 = a.fld1 and fld2 = a.fld2)
proc is fired from local server
insert tbl2 exec proc @.srv, @.db
Arun
"Vikram Jayaram [MS]" wrote:
> Hi,
> Could you please paste the code of the stored proc? As I understand it, you
> are trying to pass a remote server and remote db name to a stored proc, and
> that will take the data located on that remote data source and insert it
> into a local table on the local server?
> How long have you waited for the unresponsive behaviour?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
Subscribe to:
Posts (Atom)