I have a SQL 2000 server I want to link to an Exchange Server. Can I used
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFleming
You can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming
|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
>
>
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Friday, March 30, 2012
Link to Exchange
I have a SQL 2000 server I want to link to an Exchange Server. Can I used
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the A
D
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing o
ut
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
>
>
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the A
D
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing o
ut
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
>
>
Link to Exchange
I have a SQL 2000 server I want to link to an Exchange Server. Can I used
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
--
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> > I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> > linked servers to access the Exchange database? I do not want to have to
> > import my email addresses into a SQL table for updates.
> >
> > Thank you,
> > JLFleming
>
>
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
--
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> > I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> > linked servers to access the Exchange database? I do not want to have to
> > import my email addresses into a SQL table for updates.
> >
> > Thank you,
> > JLFleming
>
>
Wednesday, March 28, 2012
Link Table
Ronia,
this could be achieved using replication (and triggers,
linked servers, DTS...). It largely depends on latency -
the delay between the change made on A and it appearing
on B, and whether the data in B is updatable. If you can
give me a bit more info on these matters, then we'll be
in a better position to advise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Data won't be update by table B. Update will only done via Table A.
so it is only a read only .
There's a replication mode which fit with this scen.
But i am not familiar with replication. and i will ask me not to use system
account with it, I don't know how to do it.
Thanks.
Ronia.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ?
news:8aae01c4d17a$1828d140$a601280a@.phx.gbl ?...
> Ronia,
> this could be achieved using replication (and triggers,
> linked servers, DTS...). It largely depends on latency -
> the delay between the change made on A and it appearing
> on B, and whether the data in B is updatable. If you can
> give me a bit more info on these matters, then we'll be
> in a better position to advise.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Transactional replication would be best for your
scenario. Replication uses agents which are essentially
jobs run by the sql server agent. For the security
requirements have a look in books on line
for 'replication, security', 'Role Requirements'.
To run replication you won't need the sysadmin account
but to set it up, configure it and monitor it, you will.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ronia,
Why dont you create a view in your database "B"?
Use B
Go
Create view T
As
Select * From A.dbo.T
Go
This will let you read the data from the database A, table T.
Sriram
"Ronia" <Ronia@.mail.com> wrote in message news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...
> Hi Paul,
> Data won't be update by table B. Update will only done via Table A.
> so it is only a read only .
> There's a replication mode which fit with this scen.
> But i am not familiar with replication. and i will ask me not to use system
> account with it, I don't know how to do it.
> Thanks.
> Ronia.
|||hi Sriram,
Will there be any preformance issue.. when doing this? However is they
are located within the same server is that performance will not be an
issue?...
compare with Transactional replication which will be better?
Thanks
Ronia
"Sriram" <srivish@.gmail.com> ?
news:25274ca3.0411241354.5e914fc2@.posting.google.c om ?...
> Hi Ronia,
> Why dont you create a view in your database "B"?
> Use B
> Go
> Create view T
> As
> Select * From A.dbo.T
> Go
> This will let you read the data from the database A, table T.
> Sriram
>
> "Ronia" <Ronia@.mail.com> wrote in message
news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
system[vbcol=seagreen]
|||It depends...
If you require a copy of the data, so eg you can do
reports on it, while not affecting - blocking - users who
are making updates on the main table, then replication
would be suitable. It can also be used to effectively
load balance database access if the databases exist on
different drives. Also, so people use snapshot
replication to have a copy of a table that users can play
around with and modify the data indiscriminantly, knowing
that all the changes will later be overwritten.
So, what is your requirement for a copy of the data? If
it doesn't fit into one of these categories, then a cross-
database view may indeed be the solution.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
this could be achieved using replication (and triggers,
linked servers, DTS...). It largely depends on latency -
the delay between the change made on A and it appearing
on B, and whether the data in B is updatable. If you can
give me a bit more info on these matters, then we'll be
in a better position to advise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Data won't be update by table B. Update will only done via Table A.
so it is only a read only .
There's a replication mode which fit with this scen.
But i am not familiar with replication. and i will ask me not to use system
account with it, I don't know how to do it.
Thanks.
Ronia.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ?
news:8aae01c4d17a$1828d140$a601280a@.phx.gbl ?...
> Ronia,
> this could be achieved using replication (and triggers,
> linked servers, DTS...). It largely depends on latency -
> the delay between the change made on A and it appearing
> on B, and whether the data in B is updatable. If you can
> give me a bit more info on these matters, then we'll be
> in a better position to advise.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Transactional replication would be best for your
scenario. Replication uses agents which are essentially
jobs run by the sql server agent. For the security
requirements have a look in books on line
for 'replication, security', 'Role Requirements'.
To run replication you won't need the sysadmin account
but to set it up, configure it and monitor it, you will.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ronia,
Why dont you create a view in your database "B"?
Use B
Go
Create view T
As
Select * From A.dbo.T
Go
This will let you read the data from the database A, table T.
Sriram
"Ronia" <Ronia@.mail.com> wrote in message news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...
> Hi Paul,
> Data won't be update by table B. Update will only done via Table A.
> so it is only a read only .
> There's a replication mode which fit with this scen.
> But i am not familiar with replication. and i will ask me not to use system
> account with it, I don't know how to do it.
> Thanks.
> Ronia.
|||hi Sriram,
Will there be any preformance issue.. when doing this? However is they
are located within the same server is that performance will not be an
issue?...
compare with Transactional replication which will be better?
Thanks
Ronia
"Sriram" <srivish@.gmail.com> ?
news:25274ca3.0411241354.5e914fc2@.posting.google.c om ?...
> Hi Ronia,
> Why dont you create a view in your database "B"?
> Use B
> Go
> Create view T
> As
> Select * From A.dbo.T
> Go
> This will let you read the data from the database A, table T.
> Sriram
>
> "Ronia" <Ronia@.mail.com> wrote in message
news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
system[vbcol=seagreen]
|||It depends...
If you require a copy of the data, so eg you can do
reports on it, while not affecting - blocking - users who
are making updates on the main table, then replication
would be suitable. It can also be used to effectively
load balance database access if the databases exist on
different drives. Also, so people use snapshot
replication to have a copy of a table that users can play
around with and modify the data indiscriminantly, knowing
that all the changes will later be overwritten.
So, what is your requirement for a copy of the data? If
it doesn't fit into one of these categories, then a cross-
database view may indeed be the solution.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
link sql servers using enterprise manage
Hi
Can anyone explain me the options for linked servers in the enterprise
manager.
I've tried to link another sql server but i'm gettng errors.> Can anyone explain me the options for linked servers in the enterprise
> manager.
Check the "Configuring Linked Servers" topic in Books OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_1_server_4uuq.htm).
> I've tried to link another sql server but i'm gettng errors.
Any specific errors?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||do u know the ip address of your second server if yes then enter the name or
the ipaddress then give the username as u use for your local server and admi
n
as username for remote server and give the password as your local server
password
and in the server option use select the above five options
and set ok
it will work for u but keep one thing in mind that u can not see the tables
or views u can only execute the hetrogenious queries.
from
sufian
"Dejan Sarka" wrote:
> Check the "Configuring Linked Servers" topic in Books OnLine
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\
ad
> minsql.chm::/ad_1_server_4uuq.htm).
>
> Any specific errors?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
Can anyone explain me the options for linked servers in the enterprise
manager.
I've tried to link another sql server but i'm gettng errors.> Can anyone explain me the options for linked servers in the enterprise
> manager.
Check the "Configuring Linked Servers" topic in Books OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_1_server_4uuq.htm).
> I've tried to link another sql server but i'm gettng errors.
Any specific errors?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||do u know the ip address of your second server if yes then enter the name or
the ipaddress then give the username as u use for your local server and admi
n
as username for remote server and give the password as your local server
password
and in the server option use select the above five options
and set ok
it will work for u but keep one thing in mind that u can not see the tables
or views u can only execute the hetrogenious queries.
from
sufian
"Dejan Sarka" wrote:
> Check the "Configuring Linked Servers" topic in Books OnLine
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\
ad
> minsql.chm::/ad_1_server_4uuq.htm).
>
> Any specific errors?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
Monday, March 26, 2012
Link Servers with Delegation
I am trying to implement a linked server that uses integrated authentication on a 64 bit Wndows 2003 SP1 server. I have both Sql Server 2005 and Sql Server installed, and have successfully created database link that is able to use double hop authentiction on the Sql Server 2005 instance. I am unable to do the same usign the Sql Server 2000 instance. Does anyone know if double hop uathentication using Kerberos is supported on Sql Server 2000. The linked server on Sql Server 2005 is created using this syntax
"EXEC sp_addlinkedserver @.server=’LinkedServer’,
@.srvproduct=''",
@.provider='SQLNCLI',
@.datasrc=’SQLB’,--the data source
@.provstr="Integrated Security=SSPI; "
"exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'"
SPN's and domain accounts have been created as documented and those same accounts are used in both the Sql Server 2005 and Sql Server 2000 instances.
The error message going from a Sql Server 2000 or 2005 client, to the Sql Server 2000 instance that has the linked server using the SQLNCLI provider is
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' reported an error. Authentication failed.
[OLE/DB provider returned message: Communication link failure]
[OLE/DB provider returned message: Named Pipes Provider: No process is on the other end of the pipe.
]
[OLE/DB provider returned message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
OLE DB error trace [OLE/DB Provider 'SQLNCLI' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
If I use the Sql Server 2000 OLEDB provider when creating the link I get this error
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
The same link using the SQLNCLI provider in Sql Server 2005
works and I am able to use double hop authentication.
My question is , does anyone know if double hop authentication is supported using a Sql Server 2000 linked server?
http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/ms189580.aspx
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Link servers through proxy
My main development computer, XP Pro, (call it DEV) has 2 Ethernet
adapters. Adapter A goes out to a router, and can see the world, and
can see a remote SQL server (SQLREMOTE) that I have access to.
I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number.
Adapter B on my DEV computer is cabled directly to my SQL test computer
(SQLTEST) which only has one network adapter. On my DEV computer, using
the same three tools, I can see the databases on the SQLTEST computer
just fine.
This has worked for years. The DEV computer runs ProxyPlus to give the
SQLTEST computer some access to the Internet... for Windows Update and
such.
After all that was set up and working fine, I recently got a VPN
connection to the same network that the SQLREMOTE server lives on.
(Actually there are three SQL servers on that remote system.) This
might be an irrelevant detail.
Now I would like to link the two SQL servers together. I need to
occasionally get data from SQLREMOTE into SQLTEST. Since my DEV
computer can see both, it seems like this is doable.
I have read BOL and searched Google and haven't figured out how to do
this yet.
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
Q2: Do I need a "route" statement on SQLTEST so that it can at least
ping the remote IP address that the SQLREMOTE server lives on?
Q3: Knowing "where" things actually run is a little confusing. When I
have a query window open on SQLREMOTE, it seems as if I am "at" the
remote location: I can't do a Bulk Insert using local drive letters,
for example. This is never made quite clear in the documentation that I
have read.
(If I use the tool SQLDelta on my DEV computer, I can link to both
SQLTEST and SQLREMOTE and synchronize data or schemas. This apparently
makes two separate connections and acts as the middleman.)
Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
Thanks for any advice or pointers.
David WalkerHi David,
I understand that you would like to fetch data from SQLREMOTE into SQLTEST
via your DEV computer since it can see both SQLREMOTE and SQLTEST. You
would like to know how to do that.
If I have misunderstood, please let me know.
You can use DTS to export the data from SQLREMOTE to SQLTEST. Open your SQL
Server Enterprise Manager, expand your server and Data Transfermation
Services, create a new package,
drag two Microsoft OLE DB Provider for SQL Server, one for SQLREMOTE as
data source, the other for SQLTEST as data destination, and use Transform
Data Task to link them. After you define the Transform Data Task to
transfer which tables, you can save the package execute it.
Also, if the business logic is complex, you may write a T-SQL statement to
transfer the data. First run sp_addlinkedserver and sp_addlinkedsrvlogin to
add SQLREMOTE and SQLTEST as linked servers in your SQL Server on the DEV
computer. After that, you can query the remote tables on SQLREMOTE and
SQLTEST from DEV like this:
INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3 FROM
SQLREMOTE.testdb.dbo.tableA
Ofcourse, you can also use OPENQUERY for distributed queries. For more
information, please refer to SQL Server BOL.
For your three questions,
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
You just need register a linked server via sp_addlinkedserver and
sp_addlinkedsrvlogin in your SQL Server on DEV. Is this your meanning of
replacing "remote servers"?
Q2: Do I need a "route" statement on SQLTEST so that it can at least ping
the remote IP address that the SQLREMOTE server lives on?
No need. SQLTest has no need to know the existance of SQLREMOTE since DEV
knows.
Q3: Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
No.
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.
========================================
==============|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:zmDIGfjsHHA.2528@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> I understand that you would like to fetch data from SQLREMOTE into
> SQLTEST via your DEV computer since it can see both SQLREMOTE and
> SQLTEST. You would like to know how to do that.
> If I have misunderstood, please let me know.
>
[...]
> Also, if the business logic is complex, you may write a T-SQL
> statement to transfer the data. First run sp_addlinkedserver and
> sp_addlinkedsrvlogin to add SQLREMOTE and SQLTEST as linked servers in
> your SQL Server on the DEV computer. After that, you can query the
> remote tables on SQLREMOTE and SQLTEST from DEV like this:
> INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3
> FROM SQLREMOTE.testdb.dbo.tableA
>
This is the approach I want to take.
BUT, I don't have a SQL server on the DEV computer -- I'm just running
the SQL client tools on the DEV server.
> 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
> ========================================
=============
I would like to know how to get the SQLTEST SQL server to see the
SQLREMOTE SQL server.
If you could help me with that, it would be great.
Thanks.
David Walker|||Hi David,
Sorry for the misunderstanding.
I had noticed that you said:
"I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number."
I had thought that there was a SQL Server on your DEV computer.
If you do not want to install a SQL Server on your DEV computer, you need
to at least allow one direction of the network communications accessible
from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume that you can
access SQLREMOTE from SQLTest, you can run the T-SQL statement from SQLTest
to fetch data from SQLREMOTE.
Of course you can also develop an application for extracting data from
SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to know
each other.
Appreciate your understanding that our SQL Server newsgroup is focused on
break/fix issue of SQL Server. It seemed that this is a network
configuration issue, if my suggestions here are not your appropriate for
your situation, I recommend that you contact your network administrator or
have a new post at the community microsoft.public.windows.server.networking
for the most appropriate assistance.
If you have any other questions or concerns, please feel free to let us
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.
========================================
==============|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:MbNRiawsHHA.3896@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> Sorry for the misunderstanding.
> I had noticed that you said:
> "I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
> Management Studio to access several database on the SQLREMOTE server
> (default instance) using TCP/IP and an alias that I have set up, with
> a specific port number."
> I had thought that there was a SQL Server on your DEV computer.
> If you do not want to install a SQL Server on your DEV computer, you
> need to at least allow one direction of the network communications
> accessible from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume
> that you can access SQLREMOTE from SQLTest, you can run the T-SQL
> statement from SQLTest to fetch data from SQLREMOTE.
> Of course you can also develop an application for extracting data from
> SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to
> know each other.
> Appreciate your understanding that our SQL Server newsgroup is focused
> on break/fix issue of SQL Server. It seemed that this is a network
> configuration issue, if my suggestions here are not your appropriate
> for your situation, I recommend that you contact your network
> administrator or have a new post at the community
> microsoft.public.windows.server.networking for the most appropriate
> assistance.
> If you have any other questions or concerns, please feel free to let
> us know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
Thanks, I got the connection to work by the correct setup of the port
forwarding through the proxy server on my DEV machine.
I have a somewhat-related problem which I will put in a new post...
since I don't think it's a proxy issue.
Thanks for your help.
David Walker
adapters. Adapter A goes out to a router, and can see the world, and
can see a remote SQL server (SQLREMOTE) that I have access to.
I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number.
Adapter B on my DEV computer is cabled directly to my SQL test computer
(SQLTEST) which only has one network adapter. On my DEV computer, using
the same three tools, I can see the databases on the SQLTEST computer
just fine.
This has worked for years. The DEV computer runs ProxyPlus to give the
SQLTEST computer some access to the Internet... for Windows Update and
such.
After all that was set up and working fine, I recently got a VPN
connection to the same network that the SQLREMOTE server lives on.
(Actually there are three SQL servers on that remote system.) This
might be an irrelevant detail.
Now I would like to link the two SQL servers together. I need to
occasionally get data from SQLREMOTE into SQLTEST. Since my DEV
computer can see both, it seems like this is doable.
I have read BOL and searched Google and haven't figured out how to do
this yet.
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
Q2: Do I need a "route" statement on SQLTEST so that it can at least
ping the remote IP address that the SQLREMOTE server lives on?
Q3: Knowing "where" things actually run is a little confusing. When I
have a query window open on SQLREMOTE, it seems as if I am "at" the
remote location: I can't do a Bulk Insert using local drive letters,
for example. This is never made quite clear in the documentation that I
have read.
(If I use the tool SQLDelta on my DEV computer, I can link to both
SQLTEST and SQLREMOTE and synchronize data or schemas. This apparently
makes two separate connections and acts as the middleman.)
Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
Thanks for any advice or pointers.
David WalkerHi David,
I understand that you would like to fetch data from SQLREMOTE into SQLTEST
via your DEV computer since it can see both SQLREMOTE and SQLTEST. You
would like to know how to do that.
If I have misunderstood, please let me know.
You can use DTS to export the data from SQLREMOTE to SQLTEST. Open your SQL
Server Enterprise Manager, expand your server and Data Transfermation
Services, create a new package,
drag two Microsoft OLE DB Provider for SQL Server, one for SQLREMOTE as
data source, the other for SQLTEST as data destination, and use Transform
Data Task to link them. After you define the Transform Data Task to
transfer which tables, you can save the package execute it.
Also, if the business logic is complex, you may write a T-SQL statement to
transfer the data. First run sp_addlinkedserver and sp_addlinkedsrvlogin to
add SQLREMOTE and SQLTEST as linked servers in your SQL Server on the DEV
computer. After that, you can query the remote tables on SQLREMOTE and
SQLTEST from DEV like this:
INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3 FROM
SQLREMOTE.testdb.dbo.tableA
Ofcourse, you can also use OPENQUERY for distributed queries. For more
information, please refer to SQL Server BOL.
For your three questions,
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
You just need register a linked server via sp_addlinkedserver and
sp_addlinkedsrvlogin in your SQL Server on DEV. Is this your meanning of
replacing "remote servers"?
Q2: Do I need a "route" statement on SQLTEST so that it can at least ping
the remote IP address that the SQLREMOTE server lives on?
No need. SQLTest has no need to know the existance of SQLREMOTE since DEV
knows.
Q3: Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
No.
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.
========================================
==============|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:zmDIGfjsHHA.2528@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> I understand that you would like to fetch data from SQLREMOTE into
> SQLTEST via your DEV computer since it can see both SQLREMOTE and
> SQLTEST. You would like to know how to do that.
> If I have misunderstood, please let me know.
>
[...]
> Also, if the business logic is complex, you may write a T-SQL
> statement to transfer the data. First run sp_addlinkedserver and
> sp_addlinkedsrvlogin to add SQLREMOTE and SQLTEST as linked servers in
> your SQL Server on the DEV computer. After that, you can query the
> remote tables on SQLREMOTE and SQLTEST from DEV like this:
> INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3
> FROM SQLREMOTE.testdb.dbo.tableA
>
This is the approach I want to take.
BUT, I don't have a SQL server on the DEV computer -- I'm just running
the SQL client tools on the DEV server.
> 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
> ========================================
=============
I would like to know how to get the SQLTEST SQL server to see the
SQLREMOTE SQL server.
If you could help me with that, it would be great.
Thanks.
David Walker|||Hi David,
Sorry for the misunderstanding.
I had noticed that you said:
"I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number."
I had thought that there was a SQL Server on your DEV computer.
If you do not want to install a SQL Server on your DEV computer, you need
to at least allow one direction of the network communications accessible
from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume that you can
access SQLREMOTE from SQLTest, you can run the T-SQL statement from SQLTest
to fetch data from SQLREMOTE.
Of course you can also develop an application for extracting data from
SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to know
each other.
Appreciate your understanding that our SQL Server newsgroup is focused on
break/fix issue of SQL Server. It seemed that this is a network
configuration issue, if my suggestions here are not your appropriate for
your situation, I recommend that you contact your network administrator or
have a new post at the community microsoft.public.windows.server.networking
for the most appropriate assistance.
If you have any other questions or concerns, please feel free to let us
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.
========================================
==============|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:MbNRiawsHHA.3896@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> Sorry for the misunderstanding.
> I had noticed that you said:
> "I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
> Management Studio to access several database on the SQLREMOTE server
> (default instance) using TCP/IP and an alias that I have set up, with
> a specific port number."
> I had thought that there was a SQL Server on your DEV computer.
> If you do not want to install a SQL Server on your DEV computer, you
> need to at least allow one direction of the network communications
> accessible from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume
> that you can access SQLREMOTE from SQLTest, you can run the T-SQL
> statement from SQLTest to fetch data from SQLREMOTE.
> Of course you can also develop an application for extracting data from
> SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to
> know each other.
> Appreciate your understanding that our SQL Server newsgroup is focused
> on break/fix issue of SQL Server. It seemed that this is a network
> configuration issue, if my suggestions here are not your appropriate
> for your situation, I recommend that you contact your network
> administrator or have a new post at the community
> microsoft.public.windows.server.networking for the most appropriate
> assistance.
> If you have any other questions or concerns, please feel free to let
> us know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
Thanks, I got the connection to work by the correct setup of the port
forwarding through the proxy server on my DEV machine.
I have a somewhat-related problem which I will put in a new post...
since I don't think it's a proxy issue.
Thanks for your help.
David Walker
Link servers through proxy
My main development computer, XP Pro, (call it DEV) has 2 Ethernet
adapters. Adapter A goes out to a router, and can see the world, and
can see a remote SQL server (SQLREMOTE) that I have access to.
I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number.
Adapter B on my DEV computer is cabled directly to my SQL test computer
(SQLTEST) which only has one network adapter. On my DEV computer, using
the same three tools, I can see the databases on the SQLTEST computer
just fine.
This has worked for years. The DEV computer runs ProxyPlus to give the
SQLTEST computer some access to the Internet... for Windows Update and
such.
After all that was set up and working fine, I recently got a VPN
connection to the same network that the SQLREMOTE server lives on.
(Actually there are three SQL servers on that remote system.) This
might be an irrelevant detail.
Now I would like to link the two SQL servers together. I need to
occasionally get data from SQLREMOTE into SQLTEST. Since my DEV
computer can see both, it seems like this is doable.
I have read BOL and searched Google and haven't figured out how to do
this yet.
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
Q2: Do I need a "route" statement on SQLTEST so that it can at least
ping the remote IP address that the SQLREMOTE server lives on?
Q3: Knowing "where" things actually run is a little confusing. When I
have a query window open on SQLREMOTE, it seems as if I am "at" the
remote location: I can't do a Bulk Insert using local drive letters,
for example. This is never made quite clear in the documentation that I
have read.
(If I use the tool SQLDelta on my DEV computer, I can link to both
SQLTEST and SQLREMOTE and synchronize data or schemas. This apparently
makes two separate connections and acts as the middleman.)
Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
Thanks for any advice or pointers.
David Walker
Hi David,
I understand that you would like to fetch data from SQLREMOTE into SQLTEST
via your DEV computer since it can see both SQLREMOTE and SQLTEST. You
would like to know how to do that.
If I have misunderstood, please let me know.
You can use DTS to export the data from SQLREMOTE to SQLTEST. Open your SQL
Server Enterprise Manager, expand your server and Data Transfermation
Services, create a new package,
drag two Microsoft OLE DB Provider for SQL Server, one for SQLREMOTE as
data source, the other for SQLTEST as data destination, and use Transform
Data Task to link them. After you define the Transform Data Task to
transfer which tables, you can save the package execute it.
Also, if the business logic is complex, you may write a T-SQL statement to
transfer the data. First run sp_addlinkedserver and sp_addlinkedsrvlogin to
add SQLREMOTE and SQLTEST as linked servers in your SQL Server on the DEV
computer. After that, you can query the remote tables on SQLREMOTE and
SQLTEST from DEV like this:
INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3 FROM
SQLREMOTE.testdb.dbo.tableA
Ofcourse, you can also use OPENQUERY for distributed queries. For more
information, please refer to SQL Server BOL.
For your three questions,
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
You just need register a linked server via sp_addlinkedserver and
sp_addlinkedsrvlogin in your SQL Server on DEV. Is this your meanning of
replacing "remote servers"?
Q2: Do I need a "route" statement on SQLTEST so that it can at least ping
the remote IP address that the SQLREMOTE server lives on?
No need. SQLTest has no need to know the existance of SQLREMOTE since DEV
knows.
Q3: Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
No.
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.
================================================== ====
|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:zmDIGfjsHHA.2528@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> I understand that you would like to fetch data from SQLREMOTE into
> SQLTEST via your DEV computer since it can see both SQLREMOTE and
> SQLTEST. You would like to know how to do that.
> If I have misunderstood, please let me know.
>
[...]
> Also, if the business logic is complex, you may write a T-SQL
> statement to transfer the data. First run sp_addlinkedserver and
> sp_addlinkedsrvlogin to add SQLREMOTE and SQLTEST as linked servers in
> your SQL Server on the DEV computer. After that, you can query the
> remote tables on SQLREMOTE and SQLTEST from DEV like this:
> INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3
> FROM SQLREMOTE.testdb.dbo.tableA
>
This is the approach I want to take.
BUT, I don't have a SQL server on the DEV computer -- I'm just running
the SQL client tools on the DEV server.
> 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
> ================================================== ===
I would like to know how to get the SQLTEST SQL server to see the
SQLREMOTE SQL server.
If you could help me with that, it would be great.
Thanks.
David Walker
|||Hi David,
Sorry for the misunderstanding.
I had noticed that you said:
"I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number."
I had thought that there was a SQL Server on your DEV computer.
If you do not want to install a SQL Server on your DEV computer, you need
to at least allow one direction of the network communications accessible
from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume that you can
access SQLREMOTE from SQLTest, you can run the T-SQL statement from SQLTest
to fetch data from SQLREMOTE.
Of course you can also develop an application for extracting data from
SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to know
each other.
Appreciate your understanding that our SQL Server newsgroup is focused on
break/fix issue of SQL Server. It seemed that this is a network
configuration issue, if my suggestions here are not your appropriate for
your situation, I recommend that you contact your network administrator or
have a new post at the community microsoft.public.windows.server.networking
for the most appropriate assistance.
If you have any other questions or concerns, please feel free to let us
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.
================================================== ====
|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:MbNRiawsHHA.3896@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> Sorry for the misunderstanding.
> I had noticed that you said:
> "I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
> Management Studio to access several database on the SQLREMOTE server
> (default instance) using TCP/IP and an alias that I have set up, with
> a specific port number."
> I had thought that there was a SQL Server on your DEV computer.
> If you do not want to install a SQL Server on your DEV computer, you
> need to at least allow one direction of the network communications
> accessible from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume
> that you can access SQLREMOTE from SQLTest, you can run the T-SQL
> statement from SQLTest to fetch data from SQLREMOTE.
> Of course you can also develop an application for extracting data from
> SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to
> know each other.
> Appreciate your understanding that our SQL Server newsgroup is focused
> on break/fix issue of SQL Server. It seemed that this is a network
> configuration issue, if my suggestions here are not your appropriate
> for your situation, I recommend that you contact your network
> administrator or have a new post at the community
> microsoft.public.windows.server.networking for the most appropriate
> assistance.
> If you have any other questions or concerns, please feel free to let
> us know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
Thanks, I got the connection to work by the correct setup of the port
forwarding through the proxy server on my DEV machine.
I have a somewhat-related problem which I will put in a new post...
since I don't think it's a proxy issue.
Thanks for your help.
David Walker
adapters. Adapter A goes out to a router, and can see the world, and
can see a remote SQL server (SQLREMOTE) that I have access to.
I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number.
Adapter B on my DEV computer is cabled directly to my SQL test computer
(SQLTEST) which only has one network adapter. On my DEV computer, using
the same three tools, I can see the databases on the SQLTEST computer
just fine.
This has worked for years. The DEV computer runs ProxyPlus to give the
SQLTEST computer some access to the Internet... for Windows Update and
such.
After all that was set up and working fine, I recently got a VPN
connection to the same network that the SQLREMOTE server lives on.
(Actually there are three SQL servers on that remote system.) This
might be an irrelevant detail.
Now I would like to link the two SQL servers together. I need to
occasionally get data from SQLREMOTE into SQLTEST. Since my DEV
computer can see both, it seems like this is doable.
I have read BOL and searched Google and haven't figured out how to do
this yet.
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
Q2: Do I need a "route" statement on SQLTEST so that it can at least
ping the remote IP address that the SQLREMOTE server lives on?
Q3: Knowing "where" things actually run is a little confusing. When I
have a query window open on SQLREMOTE, it seems as if I am "at" the
remote location: I can't do a Bulk Insert using local drive letters,
for example. This is never made quite clear in the documentation that I
have read.
(If I use the tool SQLDelta on my DEV computer, I can link to both
SQLTEST and SQLREMOTE and synchronize data or schemas. This apparently
makes two separate connections and acts as the middleman.)
Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
Thanks for any advice or pointers.
David Walker
Hi David,
I understand that you would like to fetch data from SQLREMOTE into SQLTEST
via your DEV computer since it can see both SQLREMOTE and SQLTEST. You
would like to know how to do that.
If I have misunderstood, please let me know.
You can use DTS to export the data from SQLREMOTE to SQLTEST. Open your SQL
Server Enterprise Manager, expand your server and Data Transfermation
Services, create a new package,
drag two Microsoft OLE DB Provider for SQL Server, one for SQLREMOTE as
data source, the other for SQLTEST as data destination, and use Transform
Data Task to link them. After you define the Transform Data Task to
transfer which tables, you can save the package execute it.
Also, if the business logic is complex, you may write a T-SQL statement to
transfer the data. First run sp_addlinkedserver and sp_addlinkedsrvlogin to
add SQLREMOTE and SQLTEST as linked servers in your SQL Server on the DEV
computer. After that, you can query the remote tables on SQLREMOTE and
SQLTEST from DEV like this:
INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3 FROM
SQLREMOTE.testdb.dbo.tableA
Ofcourse, you can also use OPENQUERY for distributed queries. For more
information, please refer to SQL Server BOL.
For your three questions,
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
You just need register a linked server via sp_addlinkedserver and
sp_addlinkedsrvlogin in your SQL Server on DEV. Is this your meanning of
replacing "remote servers"?
Q2: Do I need a "route" statement on SQLTEST so that it can at least ping
the remote IP address that the SQLREMOTE server lives on?
No need. SQLTest has no need to know the existance of SQLREMOTE since DEV
knows.
Q3: Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
No.
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.
================================================== ====
|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:zmDIGfjsHHA.2528@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> I understand that you would like to fetch data from SQLREMOTE into
> SQLTEST via your DEV computer since it can see both SQLREMOTE and
> SQLTEST. You would like to know how to do that.
> If I have misunderstood, please let me know.
>
[...]
> Also, if the business logic is complex, you may write a T-SQL
> statement to transfer the data. First run sp_addlinkedserver and
> sp_addlinkedsrvlogin to add SQLREMOTE and SQLTEST as linked servers in
> your SQL Server on the DEV computer. After that, you can query the
> remote tables on SQLREMOTE and SQLTEST from DEV like this:
> INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3
> FROM SQLREMOTE.testdb.dbo.tableA
>
This is the approach I want to take.
BUT, I don't have a SQL server on the DEV computer -- I'm just running
the SQL client tools on the DEV server.
> 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
> ================================================== ===
I would like to know how to get the SQLTEST SQL server to see the
SQLREMOTE SQL server.
If you could help me with that, it would be great.
Thanks.
David Walker
|||Hi David,
Sorry for the misunderstanding.
I had noticed that you said:
"I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number."
I had thought that there was a SQL Server on your DEV computer.
If you do not want to install a SQL Server on your DEV computer, you need
to at least allow one direction of the network communications accessible
from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume that you can
access SQLREMOTE from SQLTest, you can run the T-SQL statement from SQLTest
to fetch data from SQLREMOTE.
Of course you can also develop an application for extracting data from
SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to know
each other.
Appreciate your understanding that our SQL Server newsgroup is focused on
break/fix issue of SQL Server. It seemed that this is a network
configuration issue, if my suggestions here are not your appropriate for
your situation, I recommend that you contact your network administrator or
have a new post at the community microsoft.public.windows.server.networking
for the most appropriate assistance.
If you have any other questions or concerns, please feel free to let us
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.
================================================== ====
|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:MbNRiawsHHA.3896@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> Sorry for the misunderstanding.
> I had noticed that you said:
> "I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
> Management Studio to access several database on the SQLREMOTE server
> (default instance) using TCP/IP and an alias that I have set up, with
> a specific port number."
> I had thought that there was a SQL Server on your DEV computer.
> If you do not want to install a SQL Server on your DEV computer, you
> need to at least allow one direction of the network communications
> accessible from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume
> that you can access SQLREMOTE from SQLTest, you can run the T-SQL
> statement from SQLTest to fetch data from SQLREMOTE.
> Of course you can also develop an application for extracting data from
> SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to
> know each other.
> Appreciate your understanding that our SQL Server newsgroup is focused
> on break/fix issue of SQL Server. It seemed that this is a network
> configuration issue, if my suggestions here are not your appropriate
> for your situation, I recommend that you contact your network
> administrator or have a new post at the community
> microsoft.public.windows.server.networking for the most appropriate
> assistance.
> If you have any other questions or concerns, please feel free to let
> us know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
Thanks, I got the connection to work by the correct setup of the port
forwarding through the proxy server on my DEV machine.
I have a somewhat-related problem which I will put in a new post...
since I don't think it's a proxy issue.
Thanks for your help.
David Walker
Link server not working thru Windows authentication - "Login failed
I've two SQL server 2005 servers running under the same domain and the
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql
LINK SERVER BAD PERFORMANTCE
Hi
Run sp_updatestats (on both servers) and see what is going on
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eoueGxomGHA.1912@.TK2MSFTNGP02.phx.gbl...
>A SP with join's between tables of 2 SQL 2000 linked server used to work
> relatively quick.
> Since a few weeks the same SP that runs on +/- the same amount of data
> takes
> much more time (43 sec).
> When i copied the DB so that the SP should run on the same server it takes
> only 13 seconds.
> Probably the customer installed on the server but obviously they deny that
>
>A SP with join's between tables of 2 SQL 2000 linked server used to work
relatively quick.
Since a few weeks the same SP that runs on +/- the same amount of data takes
much more time (43 sec).
When i copied the DB so that the SP should run on the same server it takes
only 13 seconds.
Probably the customer installed on the server but obviously they deny that
|||Hi
Run sp_updatestats (on both servers) and see what is going on
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eoueGxomGHA.1912@.TK2MSFTNGP02.phx.gbl...
>A SP with join's between tables of 2 SQL 2000 linked server used to work
> relatively quick.
> Since a few weeks the same SP that runs on +/- the same amount of data
> takes
> much more time (43 sec).
> When i copied the DB so that the SP should run on the same server it takes
> only 13 seconds.
> Probably the customer installed on the server but obviously they deny that
>
>
Run sp_updatestats (on both servers) and see what is going on
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eoueGxomGHA.1912@.TK2MSFTNGP02.phx.gbl...
>A SP with join's between tables of 2 SQL 2000 linked server used to work
> relatively quick.
> Since a few weeks the same SP that runs on +/- the same amount of data
> takes
> much more time (43 sec).
> When i copied the DB so that the SP should run on the same server it takes
> only 13 seconds.
> Probably the customer installed on the server but obviously they deny that
>
>A SP with join's between tables of 2 SQL 2000 linked server used to work
relatively quick.
Since a few weeks the same SP that runs on +/- the same amount of data takes
much more time (43 sec).
When i copied the DB so that the SP should run on the same server it takes
only 13 seconds.
Probably the customer installed on the server but obviously they deny that
|||HiRun sp_updatestats (on both servers) and see what is going on
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eoueGxomGHA.1912@.TK2MSFTNGP02.phx.gbl...
>A SP with join's between tables of 2 SQL 2000 linked server used to work
> relatively quick.
> Since a few weeks the same SP that runs on +/- the same amount of data
> takes
> much more time (43 sec).
> When i copied the DB so that the SP should run on the same server it takes
> only 13 seconds.
> Probably the customer installed on the server but obviously they deny that
>
>
Monday, March 12, 2012
Limiting size of a table in SQL server
hi,
im doing network monitoring app where basically i run a checks on servers every few minutes and log the data to a table. Naturally the table can get big, quite quickly. What I want is to be able to overwrite the table data at the start of each new day. Alternatively, rollup the data into a daily or weekly packets and then overwrite table data. How do i do this?Using a scheduled job in SQL Server would likely be the easiest. Create the code to do what you want in a stored procedure, and then schedule the stored procedure.
In Enterprise Manager, expand the database till you see Management, expand that and right click on Jobs below it and select New Job to create and schedule the job.
Subscribe to:
Comments (Atom)