Dear all,
I have a SQL Server 2000 in my head office, it has several databases.
However, one of the databases is maintained by a colleague of a regional
office. He has the all the authority (read / write) to work on this database
.
But there is a problem, we have developed some DTS packages in head office
and this colleague is going to make his own packages. We would like to have
a
control that this colleague is not able to access our DTS packages (not even
read), he can only opens his own the DTS packages.
Is there any control to achieve this purpose? Please advise. Thanks a lot.
IvanOpen the packages in design mode, use "save as" and set the owner and user
password.
AMB
"Ivan" wrote:
> Dear all,
> I have a SQL Server 2000 in my head office, it has several databases.
> However, one of the databases is maintained by a colleague of a regional
> office. He has the all the authority (read / write) to work on this databa
se.
> But there is a problem, we have developed some DTS packages in head office
> and this colleague is going to make his own packages. We would like to hav
e a
> control that this colleague is not able to access our DTS packages (not ev
en
> read), he can only opens his own the DTS packages.
> Is there any control to achieve this purpose? Please advise. Thanks a lot.
> Ivan
>|||Hi, Alejandro
I'm afrais that does not solve the problem. I still keep seeing the DTS's
created by others.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B1A8ECBE-047C-4095-889A-A7DA9C4BFD04@.microsoft.com...[vbcol=seagreen]
> Open the packages in design mode, use "save as" and set the owner and user
> password.
>
> AMB
> "Ivan" wrote:
>
database.[vbcol=seagreen]
office[vbcol=seagreen]
have a[vbcol=seagreen]
even[vbcol=seagreen]
lot.[vbcol=seagreen]|||Hi Uri,
It is allowed that the colleague in the regional office to create his own
DTS. I just don't want to view the DTS created in my head office.
Besides setting password in each DTS package, is there any other way?
Thanks.
Ivan
"Uri Dimant" wrote:
> Hi, Alejandro
> I'm afrais that does not solve the problem. I still keep seeing the DTS's
> created by others.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:B1A8ECBE-047C-4095-889A-A7DA9C4BFD04@.microsoft.com...
> database.
> office
> have a
> even
> lot.
>
>
Showing posts with label office. Show all posts
Showing posts with label office. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
HOW TO CONNECTED MS SQL 2005?
I had just install MS SQL 2005 at my server computer ( OS windows 2000
server sp4). I have LAN in office with workgroup, and I have a problem
that I can't connect to MS SQL 2005 from client mechine. Previously, I
used MS SQL 2000 server.
Please anybody can help me to resolve this problem?
Regard's
Rudi H
Try this:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"RUDI HARSONO" <binapusat@.nusa.net.id> wrote in message
news:%23k1wt7LJHHA.780@.TK2MSFTNGP03.phx.gbl...
>I had just install MS SQL 2005 at my server computer ( OS windows 2000
>server sp4). I have LAN in office with workgroup, and I have a problem that
>I can't connect to MS SQL 2005 from client mechine. Previously, I used MS
>SQL 2000 server.
> Please anybody can help me to resolve this problem?
> Regard's
> Rudi H
|||Arnie Rowland wrote:
> Try this:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
>
thanks for your help...
server sp4). I have LAN in office with workgroup, and I have a problem
that I can't connect to MS SQL 2005 from client mechine. Previously, I
used MS SQL 2000 server.
Please anybody can help me to resolve this problem?
Regard's
Rudi H
Try this:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"RUDI HARSONO" <binapusat@.nusa.net.id> wrote in message
news:%23k1wt7LJHHA.780@.TK2MSFTNGP03.phx.gbl...
>I had just install MS SQL 2005 at my server computer ( OS windows 2000
>server sp4). I have LAN in office with workgroup, and I have a problem that
>I can't connect to MS SQL 2005 from client mechine. Previously, I used MS
>SQL 2000 server.
> Please anybody can help me to resolve this problem?
> Regard's
> Rudi H
|||Arnie Rowland wrote:
> Try this:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
>
thanks for your help...
HOW TO CONNECTED MS SQL 2005?
I had just install MS SQL 2005 at my server computer ( OS windows 2000
server sp4). I have LAN in office with workgroup, and I have a problem
that I can't connect to MS SQL 2005 from client mechine. Previously, I
used MS SQL 2000 server.
Please anybody can help me to resolve this problem?
Regard's
Rudi HTry this:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/defaul...kb;EN-US;914277
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"RUDI HARSONO" <binapusat@.nusa.net.id> wrote in message
news:%23k1wt7LJHHA.780@.TK2MSFTNGP03.phx.gbl...
>I had just install MS SQL 2005 at my server computer ( OS windows 2000
>server sp4). I have LAN in office with workgroup, and I have a problem that
>I can't connect to MS SQL 2005 from client mechine. Previously, I used MS
>SQL 2000 server.
> Please anybody can help me to resolve this problem?
> Regard's
> Rudi H|||Arnie Rowland wrote:
> Try this:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/defaul...kb;EN-US;914277
>
thanks for your help...
server sp4). I have LAN in office with workgroup, and I have a problem
that I can't connect to MS SQL 2005 from client mechine. Previously, I
used MS SQL 2000 server.
Please anybody can help me to resolve this problem?
Regard's
Rudi HTry this:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/defaul...kb;EN-US;914277
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"RUDI HARSONO" <binapusat@.nusa.net.id> wrote in message
news:%23k1wt7LJHHA.780@.TK2MSFTNGP03.phx.gbl...
>I had just install MS SQL 2005 at my server computer ( OS windows 2000
>server sp4). I have LAN in office with workgroup, and I have a problem that
>I can't connect to MS SQL 2005 from client mechine. Previously, I used MS
>SQL 2000 server.
> Please anybody can help me to resolve this problem?
> Regard's
> Rudi H|||Arnie Rowland wrote:
> Try this:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/defaul...kb;EN-US;914277
>
thanks for your help...
Monday, March 26, 2012
How to connect to Analysis Services with Excel 2007 Data Mining Add In?
I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?
|||
|||
|||
|||
the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.
In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)
|||The localhost server name was rejected.|||
The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.
Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx
|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.
|||
Can you check to see if the server's started?
Go to the start menu, right-click on "My Computer" and select "Manage"
Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.
|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||
Yes,
However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)
|||I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?
After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?
Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.
|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."
|||
There are a few possible reasons:
- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")
- is AS running as the default instance or as a named instance?
In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"
A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"
If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"
|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||
You can download freely an evaluation version from
http://www.microsoft.com/sql/downloads/trial-software.mspx
sqlHow to connect to Analysis Services with Excel 2007 Data Mining Add In?
I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?
the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.
In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)
|||The localhost server name was rejected.|||The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.
Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx
|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.|||
Can you check to see if the server's started?
Go to the start menu, right-click on "My Computer" and select "Manage"
Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.
|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||Yes,
However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)
|||I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?
After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?
Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.
|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."|||
There are a few possible reasons:
- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")
- is AS running as the default instance or as a named instance?
In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"
A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"
If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"
|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||
You can download freely an evaluation version from
http://www.microsoft.com/sql/downloads/trial-software.mspx
How to connect to Analysis Services with Excel 2007 Data Mining Add In?
I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?
the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.
In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)
|||The localhost server name was rejected.|||The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.
Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx
|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.|||
Can you check to see if the server's started?
Go to the start menu, right-click on "My Computer" and select "Manage"
Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.
|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||Yes,
However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)
|||I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?
After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?
Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.
|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."|||
There are a few possible reasons:
- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")
- is AS running as the default instance or as a named instance?
In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"
A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"
If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"
|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||
You can download freely an evaluation version from
http://www.microsoft.com/sql/downloads/trial-software.mspx
Friday, March 23, 2012
How to connect test to production system
I am looking for guidance. I do SQL and other development on 2
computers, and I work remotely from my company's home office. (We use
SQL 2000 and 2005 both.)
My "main" computer has a VPN to the home office. Outside of that, and
before the VPN was set up, I had a TCP connection to ONE of the SQL
server instances at the home office... that specific destination port is
filtered so that only my TCP source IP address can connect to it.
(Now I use the VPN to connect to the home office Exchange server and
shared folders.)
My own "SQL server" computer is connected by Ehternet, ONLY to my
"main" computer. The main computer is obviously multi-homed, connecting
to the SQL computer and also to the outside world through a DSL router
(which is what the VPN connection now uses).
The SQL client on my main computer can see all of the SQL servers at the
home office. When I run the SQL client running on the SQL server
computer, it can only see the server that we had the TCP port set up
for.
The guidance is needed for the following... most of the views and stored
procs that I develop, get the data from the database that I keep a local
copy of, and there is a copy at the home office.
A few of the view and procs need to access data on some of the other SQL
servers at the home office. While my main computer can connect to those
servers, I have trouble setting them up as linked servers from my local
SQL server.
I also would like for the views and procs in my copy of the main
database here to look *exactly* like the views and procs in the database
at the home office... but there are instances of this:
Select * from [server\instance].dbname.dbo.table where...
And server\instance need to be different between the local developement
database and the production database at the home office.
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
2) How do people develop views and stored procs on test systems, when
the call-out to the linked server might need a different server name in
production?
Sorry for such a long question, but I wanted to list all of the info
that might be pertinent.
Thanks.
David Walker
(I asked a specific but related question in the General group, and got
an answer that I couldn't quite do what I thought I could do. This
question is the general case, and I'm loking for advice.)
I would post this to microsoft.public.sqlserver.programming
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"DWalker" <none@.none.com> wrote in message
news:O8XxVY62HHA.1164@.TK2MSFTNGP02.phx.gbl...
>I am looking for guidance. I do SQL and other development on 2
> computers, and I work remotely from my company's home office. (We use
> SQL 2000 and 2005 both.)
> My "main" computer has a VPN to the home office. Outside of that, and
> before the VPN was set up, I had a TCP connection to ONE of the SQL
> server instances at the home office... that specific destination port is
> filtered so that only my TCP source IP address can connect to it.
> (Now I use the VPN to connect to the home office Exchange server and
> shared folders.)
> My own "SQL server" computer is connected by Ehternet, ONLY to my
> "main" computer. The main computer is obviously multi-homed, connecting
> to the SQL computer and also to the outside world through a DSL router
> (which is what the VPN connection now uses).
> The SQL client on my main computer can see all of the SQL servers at the
> home office. When I run the SQL client running on the SQL server
> computer, it can only see the server that we had the TCP port set up
> for.
> The guidance is needed for the following... most of the views and stored
> procs that I develop, get the data from the database that I keep a local
> copy of, and there is a copy at the home office.
> A few of the view and procs need to access data on some of the other SQL
> servers at the home office. While my main computer can connect to those
> servers, I have trouble setting them up as linked servers from my local
> SQL server.
> I also would like for the views and procs in my copy of the main
> database here to look *exactly* like the views and procs in the database
> at the home office... but there are instances of this:
> Select * from [server\instance].dbname.dbo.table where...
> And server\instance need to be different between the local developement
> database and the production database at the home office.
> 1) Given that I now have a VPN, and the "main" computer is multihomed,
> should I be able to link the database on my local SQL server to the
> databases at the home office?
> 2) How do people develop views and stored procs on test systems, when
> the call-out to the linked server might need a different server name in
> production?
> Sorry for such a long question, but I wanted to list all of the info
> that might be pertinent.
> Thanks.
> David Walker
>
> (I asked a specific but related question in the General group, and got
> an answer that I couldn't quite do what I thought I could do. This
> question is the general case, and I'm loking for advice.)
|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in news:O#W35g72HHA.536
@.TK2MSFTNGP06.phx.gbl:
> I would post this to microsoft.public.sqlserver.programming
>
Thanks. If I can't get things connected the way Charles Wang suggested,
I'll do that.
David
computers, and I work remotely from my company's home office. (We use
SQL 2000 and 2005 both.)
My "main" computer has a VPN to the home office. Outside of that, and
before the VPN was set up, I had a TCP connection to ONE of the SQL
server instances at the home office... that specific destination port is
filtered so that only my TCP source IP address can connect to it.
(Now I use the VPN to connect to the home office Exchange server and
shared folders.)
My own "SQL server" computer is connected by Ehternet, ONLY to my
"main" computer. The main computer is obviously multi-homed, connecting
to the SQL computer and also to the outside world through a DSL router
(which is what the VPN connection now uses).
The SQL client on my main computer can see all of the SQL servers at the
home office. When I run the SQL client running on the SQL server
computer, it can only see the server that we had the TCP port set up
for.
The guidance is needed for the following... most of the views and stored
procs that I develop, get the data from the database that I keep a local
copy of, and there is a copy at the home office.
A few of the view and procs need to access data on some of the other SQL
servers at the home office. While my main computer can connect to those
servers, I have trouble setting them up as linked servers from my local
SQL server.
I also would like for the views and procs in my copy of the main
database here to look *exactly* like the views and procs in the database
at the home office... but there are instances of this:
Select * from [server\instance].dbname.dbo.table where...
And server\instance need to be different between the local developement
database and the production database at the home office.
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
2) How do people develop views and stored procs on test systems, when
the call-out to the linked server might need a different server name in
production?
Sorry for such a long question, but I wanted to list all of the info
that might be pertinent.
Thanks.
David Walker
(I asked a specific but related question in the General group, and got
an answer that I couldn't quite do what I thought I could do. This
question is the general case, and I'm loking for advice.)
I would post this to microsoft.public.sqlserver.programming
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"DWalker" <none@.none.com> wrote in message
news:O8XxVY62HHA.1164@.TK2MSFTNGP02.phx.gbl...
>I am looking for guidance. I do SQL and other development on 2
> computers, and I work remotely from my company's home office. (We use
> SQL 2000 and 2005 both.)
> My "main" computer has a VPN to the home office. Outside of that, and
> before the VPN was set up, I had a TCP connection to ONE of the SQL
> server instances at the home office... that specific destination port is
> filtered so that only my TCP source IP address can connect to it.
> (Now I use the VPN to connect to the home office Exchange server and
> shared folders.)
> My own "SQL server" computer is connected by Ehternet, ONLY to my
> "main" computer. The main computer is obviously multi-homed, connecting
> to the SQL computer and also to the outside world through a DSL router
> (which is what the VPN connection now uses).
> The SQL client on my main computer can see all of the SQL servers at the
> home office. When I run the SQL client running on the SQL server
> computer, it can only see the server that we had the TCP port set up
> for.
> The guidance is needed for the following... most of the views and stored
> procs that I develop, get the data from the database that I keep a local
> copy of, and there is a copy at the home office.
> A few of the view and procs need to access data on some of the other SQL
> servers at the home office. While my main computer can connect to those
> servers, I have trouble setting them up as linked servers from my local
> SQL server.
> I also would like for the views and procs in my copy of the main
> database here to look *exactly* like the views and procs in the database
> at the home office... but there are instances of this:
> Select * from [server\instance].dbname.dbo.table where...
> And server\instance need to be different between the local developement
> database and the production database at the home office.
> 1) Given that I now have a VPN, and the "main" computer is multihomed,
> should I be able to link the database on my local SQL server to the
> databases at the home office?
> 2) How do people develop views and stored procs on test systems, when
> the call-out to the linked server might need a different server name in
> production?
> Sorry for such a long question, but I wanted to list all of the info
> that might be pertinent.
> Thanks.
> David Walker
>
> (I asked a specific but related question in the General group, and got
> an answer that I couldn't quite do what I thought I could do. This
> question is the general case, and I'm loking for advice.)
|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in news:O#W35g72HHA.536
@.TK2MSFTNGP06.phx.gbl:
> I would post this to microsoft.public.sqlserver.programming
>
Thanks. If I can't get things connected the way Charles Wang suggested,
I'll do that.
David
How to connect test to production system
I am looking for guidance. I do SQL and other development on 2
computers, and I work remotely from my company's home office. (We use
SQL 2000 and 2005 both.)
My "main" computer has a VPN to the home office. Outside of that, and
before the VPN was set up, I had a TCP connection to ONE of the SQL
server instances at the home office... that specific destination port is
filtered so that only my TCP source IP address can connect to it.
(Now I use the VPN to connect to the home office Exchange server and
shared folders.)
My own "SQL server" computer is connected by Ehternet, ONLY to my
"main" computer. The main computer is obviously multi-homed, connecting
to the SQL computer and also to the outside world through a DSL router
(which is what the VPN connection now uses).
The SQL client on my main computer can see all of the SQL servers at the
home office. When I run the SQL client running on the SQL server
computer, it can only see the server that we had the TCP port set up
for.
The guidance is needed for the following... most of the views and stored
procs that I develop, get the data from the database that I keep a local
copy of, and there is a copy at the home office.
A few of the view and procs need to access data on some of the other SQL
servers at the home office. While my main computer can connect to those
servers, I have trouble setting them up as linked servers from my local
SQL server.
I also would like for the views and procs in my copy of the main
database here to look *exactly* like the views and procs in the database
at the home office... but there are instances of this:
Select * from [server\instance].dbname.dbo.table where...
And server\instance need to be different between the local developement
database and the production database at the home office.
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
2) How do people develop views and stored procs on test systems, when
the call-out to the linked server might need a different server name in
production?
Sorry for such a long question, but I wanted to list all of the info
that might be pertinent.
Thanks.
David Walker
(I asked a specific but related question in the General group, and got
an answer that I couldn't quite do what I thought I could do. This
question is the general case, and I'm loking for advice.)I would post this to microsoft.public.sqlserver.programming
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"DWalker" <none@.none.com> wrote in message
news:O8XxVY62HHA.1164@.TK2MSFTNGP02.phx.gbl...
>I am looking for guidance. I do SQL and other development on 2
> computers, and I work remotely from my company's home office. (We use
> SQL 2000 and 2005 both.)
> My "main" computer has a VPN to the home office. Outside of that, and
> before the VPN was set up, I had a TCP connection to ONE of the SQL
> server instances at the home office... that specific destination port is
> filtered so that only my TCP source IP address can connect to it.
> (Now I use the VPN to connect to the home office Exchange server and
> shared folders.)
> My own "SQL server" computer is connected by Ehternet, ONLY to my
> "main" computer. The main computer is obviously multi-homed, connecting
> to the SQL computer and also to the outside world through a DSL router
> (which is what the VPN connection now uses).
> The SQL client on my main computer can see all of the SQL servers at the
> home office. When I run the SQL client running on the SQL server
> computer, it can only see the server that we had the TCP port set up
> for.
> The guidance is needed for the following... most of the views and stored
> procs that I develop, get the data from the database that I keep a local
> copy of, and there is a copy at the home office.
> A few of the view and procs need to access data on some of the other SQL
> servers at the home office. While my main computer can connect to those
> servers, I have trouble setting them up as linked servers from my local
> SQL server.
> I also would like for the views and procs in my copy of the main
> database here to look *exactly* like the views and procs in the database
> at the home office... but there are instances of this:
> Select * from [server\instance].dbname.dbo.table where...
> And server\instance need to be different between the local developement
> database and the production database at the home office.
> 1) Given that I now have a VPN, and the "main" computer is multihomed,
> should I be able to link the database on my local SQL server to the
> databases at the home office?
> 2) How do people develop views and stored procs on test systems, when
> the call-out to the linked server might need a different server name in
> production?
> Sorry for such a long question, but I wanted to list all of the info
> that might be pertinent.
> Thanks.
> David Walker
>
> (I asked a specific but related question in the General group, and got
> an answer that I couldn't quite do what I thought I could do. This
> question is the general case, and I'm loking for advice.)|||Hi David,
Since your post has a good many of information, I would like to first
confirm with you if I understand your environment:
1. Your main computer connected to your home office network via VPN;
2. Your local SQL Server computer connected to your main computer via
Ethernet (Local Area Network); It did not directly connect to your home
office network;
3. Your local SQL Server could connect to a remote SQL Server in your home
office network with a specific destination TCP port opened.
Also, I have two questions and appreciate your confirmation since I have
not totally understood your scenario:
1. What is the relationship between your main computer and your local SQL
Server computer?
2. Which computer is your local copy of the main database located on, the
main computer or the local SQL Server computer?
For your two questions,
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
Did you mean databases in different server instances here? If the databases
at the home office were all in the remote SQL Server with specific TCP port
opened, your local SQL Server should be able to connect to the databases in
the remote SQL Server instance.
If you meant that you would like to access the databases in different SQL
Server instances (may be located on different servers) at your home office
via linked server via your local SQL Server, you may try adding your main
server as the gateway of your local SQL Server computer to see if it helps.
Anyway, you need to ensure that the physical connections from your local
SQL Server computer to your remote servers could be established.
If the physical network connections could be established, you can add the
linked servers with SQL authentication (if Windows Authentication could not
be established).
2) How do people develop views and stored procs on test systems, when the
call-out to the linked server might need a different server name in
production?
You may try adding some server aliases on your test systems. These server
aliases have the same names as the server names in production.
For example, your test SQL Server instance name is "Server1\SQL2K", while
your production SQL Server instance name is "ProductServer\SQLServer". You
can first run cliconfg.exe (SQL 2000 client) from command line or SQL
Server Configuration Manager (SQL 2005 client) on your client computer to
configure a server alias "ProductServer\SQLServer" which represents your
test SQL Server instance "Server1\SQL2K" with a specific protocol (TCP/IP
or Named Pipes).
After that, you can run:
Sp_addlinkedserver 'ProductServer\SQLSERVER'
Go
Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
Go
Then you can run the following statement to query the northwind database in
your local test SQL Server instance "Server1\SQL2K":
SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
For configure server alias on a client computer, you may also refer to:
How to connect to a named instance of SQL Server 2005 or SQL Server 2000 by
using the client tools in the earlier version of SQL Server
http://support.microsoft.com/kb/265808/en-us
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
========================================
=============
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.
========================================
==============|||Answers inline.
changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:M55syrX3HHA.4100@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> Since your post has a good many of information, I would like to first
> confirm with you if I understand your environment:
> 1. Your main computer connected to your home office network via
> VPN; 2. Your local SQL Server computer connected to your main
> computer via Ethernet (Local Area Network); It did not directly
> connect to your home office network;
> 3. Your local SQL Server could connect to a remote SQL Server in
> your home office network with a specific destination TCP port opened.
> Also, I have two questions and appreciate your confirmation since I
> have not totally understood your scenario:
> 1. What is the relationship between your main computer and your
> local SQL Server computer?
They are both XP pro, connected with an Ethernet crossover cable, in the
same workgroup.
> 2. Which computer is your local copy of the main database located
> on, the main computer or the local SQL Server computer?
The local copy of the main database is on the SQL server computer. My
"main" computer is only a client, not running a SQL server.
> For your two questions,
> 1) Given that I now have a VPN, and the "main" computer is
> multihomed, should I be able to link the database on my local SQL
> server to the databases at the home office?
> Did you mean databases in different server instances here? If the
> databases at the home office were all in the remote SQL Server with
> specific TCP port opened, your local SQL Server should be able to
> connect to the databases in the remote SQL Server instance.
> If you meant that you would like to access the databases in different
> SQL Server instances (may be located on different servers) at your
> home office via linked server via your local SQL Server, you may try
> adding your main server as the gateway of your local SQL Server
> computer to see if it helps. Anyway, you need to ensure that the
> physical connections from your local SQL Server computer to your
> remote servers could be established.
> If the physical network connections could be established, you can add
> the linked servers with SQL authentication (if Windows Authentication
> could not be established).
> 2) How do people develop views and stored procs on test systems,
> when the call-out to the linked server might need a different server
> name in production?
> You may try adding some server aliases on your test systems. These
> server aliases have the same names as the server names in production.
> For example, your test SQL Server instance name is "Server1\SQL2K",
> while your production SQL Server instance name is
> "ProductServer\SQLServer". You can first run cliconfg.exe (SQL 2000
> client) from command line or SQL Server Configuration Manager (SQL
> 2005 client) on your client computer to configure a server alias
> "ProductServer\SQLServer" which represents your test SQL Server
> instance "Server1\SQL2K" with a specific protocol (TCP/IP or Named
> Pipes). After that, you can run:
> Sp_addlinkedserver 'ProductServer\SQLSERVER'
> Go
> Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
> Go
> Then you can run the following statement to query the northwind
> database in your local test SQL Server instance "Server1\SQL2K":
> SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
> For configure server alias on a client computer, you may also refer
> to: How to connect to a named instance of SQL Server 2005 or SQL
> Server 2000 by using the client tools in the earlier version of SQL
> Server http://support.microsoft.com/kb/265808/en-us
> Hope this helps. If you have any other questions or concerns, please
> feel free to let me know.
Thanks for the info. I will look into all of it.
I didn't know that I could make an alias which covers both the server
name AND the instance name. In fact, when I tried it, I got "SQL Server
does not exist or access denied." I'll make sure I have the parameters
right, and try again.
David
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> 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. ========================================
==============
>
>
>
>|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in news:O#W35g72HHA.536
@.TK2MSFTNGP06.phx.gbl:
> I would post this to microsoft.public.sqlserver.programming
>
Thanks. If I can't get things connected the way Charles Wang suggested,
I'll do that.
David|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:M55syrX3HHA.4100@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
[snip]
> You may try adding some server aliases on your test systems. These
> server aliases have the same names as the server names in production.
> For example, your test SQL Server instance name is "Server1\SQL2K",
> while your production SQL Server instance name is
> "ProductServer\SQLServer". You can first run cliconfg.exe (SQL 2000
> client) from command line or SQL Server Configuration Manager (SQL
> 2005 client) on your client computer to configure a server alias
> "ProductServer\SQLServer" which represents your test SQL Server
> instance "Server1\SQL2K" with a specific protocol (TCP/IP or Named
> Pipes). After that, you can run:
> Sp_addlinkedserver 'ProductServer\SQLSERVER'
> Go
> Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
> Go
> Then you can run the following statement to query the northwind
> database in your local test SQL Server instance "Server1\SQL2K":
> SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
>
Charles,
If I use the sp_addlinkedserver and sp_addlinkedsrvlogin, then I *don't*
need to use cliconfig first. Adding the linked server does the whole
thing. I got it to work this way.
Thanks.
David|||Hi David,
Thanks for your updating and response.
I am glad to hear that you used sp_addlinkedserver and sp_addlinkedsrvlogin
to resolve this issue. Yes, sp_addlinkedserver also has the function to
create an alias for a SQL Server instance. You can specify an alias for the
parameter @.server and specify the real server instance name for the
parameter @.datasrc. Personally I would like to use cliconfg since it allows
me to create an alias with a specific communication protocol. Sometimes it
is very helpful for resolving connection issues, especially for TCP/IP
connection when the SQL Server instance is listenning on a specific or
dynamic TCP port not 1433. That is why I recommended that you used it in my
last reply.
Since this issue has been resolved, now I would like check with you to see
whether you have any other questions or concerns. Please feel free to let
us know if you have.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
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.
========================================
==============
computers, and I work remotely from my company's home office. (We use
SQL 2000 and 2005 both.)
My "main" computer has a VPN to the home office. Outside of that, and
before the VPN was set up, I had a TCP connection to ONE of the SQL
server instances at the home office... that specific destination port is
filtered so that only my TCP source IP address can connect to it.
(Now I use the VPN to connect to the home office Exchange server and
shared folders.)
My own "SQL server" computer is connected by Ehternet, ONLY to my
"main" computer. The main computer is obviously multi-homed, connecting
to the SQL computer and also to the outside world through a DSL router
(which is what the VPN connection now uses).
The SQL client on my main computer can see all of the SQL servers at the
home office. When I run the SQL client running on the SQL server
computer, it can only see the server that we had the TCP port set up
for.
The guidance is needed for the following... most of the views and stored
procs that I develop, get the data from the database that I keep a local
copy of, and there is a copy at the home office.
A few of the view and procs need to access data on some of the other SQL
servers at the home office. While my main computer can connect to those
servers, I have trouble setting them up as linked servers from my local
SQL server.
I also would like for the views and procs in my copy of the main
database here to look *exactly* like the views and procs in the database
at the home office... but there are instances of this:
Select * from [server\instance].dbname.dbo.table where...
And server\instance need to be different between the local developement
database and the production database at the home office.
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
2) How do people develop views and stored procs on test systems, when
the call-out to the linked server might need a different server name in
production?
Sorry for such a long question, but I wanted to list all of the info
that might be pertinent.
Thanks.
David Walker
(I asked a specific but related question in the General group, and got
an answer that I couldn't quite do what I thought I could do. This
question is the general case, and I'm loking for advice.)I would post this to microsoft.public.sqlserver.programming
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"DWalker" <none@.none.com> wrote in message
news:O8XxVY62HHA.1164@.TK2MSFTNGP02.phx.gbl...
>I am looking for guidance. I do SQL and other development on 2
> computers, and I work remotely from my company's home office. (We use
> SQL 2000 and 2005 both.)
> My "main" computer has a VPN to the home office. Outside of that, and
> before the VPN was set up, I had a TCP connection to ONE of the SQL
> server instances at the home office... that specific destination port is
> filtered so that only my TCP source IP address can connect to it.
> (Now I use the VPN to connect to the home office Exchange server and
> shared folders.)
> My own "SQL server" computer is connected by Ehternet, ONLY to my
> "main" computer. The main computer is obviously multi-homed, connecting
> to the SQL computer and also to the outside world through a DSL router
> (which is what the VPN connection now uses).
> The SQL client on my main computer can see all of the SQL servers at the
> home office. When I run the SQL client running on the SQL server
> computer, it can only see the server that we had the TCP port set up
> for.
> The guidance is needed for the following... most of the views and stored
> procs that I develop, get the data from the database that I keep a local
> copy of, and there is a copy at the home office.
> A few of the view and procs need to access data on some of the other SQL
> servers at the home office. While my main computer can connect to those
> servers, I have trouble setting them up as linked servers from my local
> SQL server.
> I also would like for the views and procs in my copy of the main
> database here to look *exactly* like the views and procs in the database
> at the home office... but there are instances of this:
> Select * from [server\instance].dbname.dbo.table where...
> And server\instance need to be different between the local developement
> database and the production database at the home office.
> 1) Given that I now have a VPN, and the "main" computer is multihomed,
> should I be able to link the database on my local SQL server to the
> databases at the home office?
> 2) How do people develop views and stored procs on test systems, when
> the call-out to the linked server might need a different server name in
> production?
> Sorry for such a long question, but I wanted to list all of the info
> that might be pertinent.
> Thanks.
> David Walker
>
> (I asked a specific but related question in the General group, and got
> an answer that I couldn't quite do what I thought I could do. This
> question is the general case, and I'm loking for advice.)|||Hi David,
Since your post has a good many of information, I would like to first
confirm with you if I understand your environment:
1. Your main computer connected to your home office network via VPN;
2. Your local SQL Server computer connected to your main computer via
Ethernet (Local Area Network); It did not directly connect to your home
office network;
3. Your local SQL Server could connect to a remote SQL Server in your home
office network with a specific destination TCP port opened.
Also, I have two questions and appreciate your confirmation since I have
not totally understood your scenario:
1. What is the relationship between your main computer and your local SQL
Server computer?
2. Which computer is your local copy of the main database located on, the
main computer or the local SQL Server computer?
For your two questions,
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
Did you mean databases in different server instances here? If the databases
at the home office were all in the remote SQL Server with specific TCP port
opened, your local SQL Server should be able to connect to the databases in
the remote SQL Server instance.
If you meant that you would like to access the databases in different SQL
Server instances (may be located on different servers) at your home office
via linked server via your local SQL Server, you may try adding your main
server as the gateway of your local SQL Server computer to see if it helps.
Anyway, you need to ensure that the physical connections from your local
SQL Server computer to your remote servers could be established.
If the physical network connections could be established, you can add the
linked servers with SQL authentication (if Windows Authentication could not
be established).
2) How do people develop views and stored procs on test systems, when the
call-out to the linked server might need a different server name in
production?
You may try adding some server aliases on your test systems. These server
aliases have the same names as the server names in production.
For example, your test SQL Server instance name is "Server1\SQL2K", while
your production SQL Server instance name is "ProductServer\SQLServer". You
can first run cliconfg.exe (SQL 2000 client) from command line or SQL
Server Configuration Manager (SQL 2005 client) on your client computer to
configure a server alias "ProductServer\SQLServer" which represents your
test SQL Server instance "Server1\SQL2K" with a specific protocol (TCP/IP
or Named Pipes).
After that, you can run:
Sp_addlinkedserver 'ProductServer\SQLSERVER'
Go
Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
Go
Then you can run the following statement to query the northwind database in
your local test SQL Server instance "Server1\SQL2K":
SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
For configure server alias on a client computer, you may also refer to:
How to connect to a named instance of SQL Server 2005 or SQL Server 2000 by
using the client tools in the earlier version of SQL Server
http://support.microsoft.com/kb/265808/en-us
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
========================================
=============
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.
========================================
==============|||Answers inline.
changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:M55syrX3HHA.4100@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> Since your post has a good many of information, I would like to first
> confirm with you if I understand your environment:
> 1. Your main computer connected to your home office network via
> VPN; 2. Your local SQL Server computer connected to your main
> computer via Ethernet (Local Area Network); It did not directly
> connect to your home office network;
> 3. Your local SQL Server could connect to a remote SQL Server in
> your home office network with a specific destination TCP port opened.
> Also, I have two questions and appreciate your confirmation since I
> have not totally understood your scenario:
> 1. What is the relationship between your main computer and your
> local SQL Server computer?
They are both XP pro, connected with an Ethernet crossover cable, in the
same workgroup.
> 2. Which computer is your local copy of the main database located
> on, the main computer or the local SQL Server computer?
The local copy of the main database is on the SQL server computer. My
"main" computer is only a client, not running a SQL server.
> For your two questions,
> 1) Given that I now have a VPN, and the "main" computer is
> multihomed, should I be able to link the database on my local SQL
> server to the databases at the home office?
> Did you mean databases in different server instances here? If the
> databases at the home office were all in the remote SQL Server with
> specific TCP port opened, your local SQL Server should be able to
> connect to the databases in the remote SQL Server instance.
> If you meant that you would like to access the databases in different
> SQL Server instances (may be located on different servers) at your
> home office via linked server via your local SQL Server, you may try
> adding your main server as the gateway of your local SQL Server
> computer to see if it helps. Anyway, you need to ensure that the
> physical connections from your local SQL Server computer to your
> remote servers could be established.
> If the physical network connections could be established, you can add
> the linked servers with SQL authentication (if Windows Authentication
> could not be established).
> 2) How do people develop views and stored procs on test systems,
> when the call-out to the linked server might need a different server
> name in production?
> You may try adding some server aliases on your test systems. These
> server aliases have the same names as the server names in production.
> For example, your test SQL Server instance name is "Server1\SQL2K",
> while your production SQL Server instance name is
> "ProductServer\SQLServer". You can first run cliconfg.exe (SQL 2000
> client) from command line or SQL Server Configuration Manager (SQL
> 2005 client) on your client computer to configure a server alias
> "ProductServer\SQLServer" which represents your test SQL Server
> instance "Server1\SQL2K" with a specific protocol (TCP/IP or Named
> Pipes). After that, you can run:
> Sp_addlinkedserver 'ProductServer\SQLSERVER'
> Go
> Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
> Go
> Then you can run the following statement to query the northwind
> database in your local test SQL Server instance "Server1\SQL2K":
> SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
> For configure server alias on a client computer, you may also refer
> to: How to connect to a named instance of SQL Server 2005 or SQL
> Server 2000 by using the client tools in the earlier version of SQL
> Server http://support.microsoft.com/kb/265808/en-us
> Hope this helps. If you have any other questions or concerns, please
> feel free to let me know.
Thanks for the info. I will look into all of it.
I didn't know that I could make an alias which covers both the server
name AND the instance name. In fact, when I tried it, I got "SQL Server
does not exist or access denied." I'll make sure I have the parameters
right, and try again.
David
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> 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. ========================================
==============
>
>
>
>|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in news:O#W35g72HHA.536
@.TK2MSFTNGP06.phx.gbl:
> I would post this to microsoft.public.sqlserver.programming
>
Thanks. If I can't get things connected the way Charles Wang suggested,
I'll do that.
David|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:M55syrX3HHA.4100@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
[snip]
> You may try adding some server aliases on your test systems. These
> server aliases have the same names as the server names in production.
> For example, your test SQL Server instance name is "Server1\SQL2K",
> while your production SQL Server instance name is
> "ProductServer\SQLServer". You can first run cliconfg.exe (SQL 2000
> client) from command line or SQL Server Configuration Manager (SQL
> 2005 client) on your client computer to configure a server alias
> "ProductServer\SQLServer" which represents your test SQL Server
> instance "Server1\SQL2K" with a specific protocol (TCP/IP or Named
> Pipes). After that, you can run:
> Sp_addlinkedserver 'ProductServer\SQLSERVER'
> Go
> Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
> Go
> Then you can run the following statement to query the northwind
> database in your local test SQL Server instance "Server1\SQL2K":
> SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
>
Charles,
If I use the sp_addlinkedserver and sp_addlinkedsrvlogin, then I *don't*
need to use cliconfig first. Adding the linked server does the whole
thing. I got it to work this way.
Thanks.
David|||Hi David,
Thanks for your updating and response.
I am glad to hear that you used sp_addlinkedserver and sp_addlinkedsrvlogin
to resolve this issue. Yes, sp_addlinkedserver also has the function to
create an alias for a SQL Server instance. You can specify an alias for the
parameter @.server and specify the real server instance name for the
parameter @.datasrc. Personally I would like to use cliconfg since it allows
me to create an alias with a specific communication protocol. Sometimes it
is very helpful for resolving connection issues, especially for TCP/IP
connection when the SQL Server instance is listenning on a specific or
dynamic TCP port not 1433. That is why I recommended that you used it in my
last reply.
Since this issue has been resolved, now I would like check with you to see
whether you have any other questions or concerns. Please feel free to let
us know if you have.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
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
How to configure a Database both, as a Publisher and a Subscriber ?
Hi ,
I am using merge replication wherein we have the following architecture.
We have three different databases. One DB acts as the back office server. The second acts as the desktop DB and the last one is a mobile DB i.e. SQLCE DB.
Data needs to be synchronized between the back office server and the desktop. The SQL CE database connects to the desktop for data synchronization. i.e.
I want to push and pull data from the mobile DB to the desktop DB. Hence, here the Desktop DB acts as the Publisher, and the SQL CE DB acts as subscriber.
Further, the desktop DB needs to push and pull data from the back office DB. Hence, in this case, the Desktop DB acts as the subscriber to the Server, and the back office DB acts as publisher.
Merge replication is not allowing me to make the desktop DB as publisher as well as the subscriber at the same time.
Is there any way we can accomplish this?
Thanks in advance.
Regards,
Pushkar.Hi,
Open the SQL Enterprise manager and select the distributor SQL server.
Select the replication option in the tools menu.
Select the option Configure publishers, subscribers and distributors.
Remember to declare all the involved SQL servers on the Distributor (!) via the Client Network Utility (a small program which is installed together with SQL server)
Success, VincentJS
I am using merge replication wherein we have the following architecture.
We have three different databases. One DB acts as the back office server. The second acts as the desktop DB and the last one is a mobile DB i.e. SQLCE DB.
Data needs to be synchronized between the back office server and the desktop. The SQL CE database connects to the desktop for data synchronization. i.e.
I want to push and pull data from the mobile DB to the desktop DB. Hence, here the Desktop DB acts as the Publisher, and the SQL CE DB acts as subscriber.
Further, the desktop DB needs to push and pull data from the back office DB. Hence, in this case, the Desktop DB acts as the subscriber to the Server, and the back office DB acts as publisher.
Merge replication is not allowing me to make the desktop DB as publisher as well as the subscriber at the same time.
Is there any way we can accomplish this?
Thanks in advance.
Regards,
Pushkar.Hi,
Open the SQL Enterprise manager and select the distributor SQL server.
Select the replication option in the tools menu.
Select the option Configure publishers, subscribers and distributors.
Remember to declare all the involved SQL servers on the Distributor (!) via the Client Network Utility (a small program which is installed together with SQL server)
Success, VincentJS
Subscribe to:
Posts (Atom)