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
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
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.)|||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 19, 2012
How to configure new development workstation to use VS2005 with SSRS?
Just getting started using SSRS (SQL Server Reporting Services) 2005 in production and I've got what should be a simple question.
How do I configure a development workstation to be able to develop SSRS reports which will run on a networked server running SQL Server 2005?
Here is my problem...
I installed VS2005 (Visual Studio Pro 2005) and SQL Server 2005 workstation components (with SQL Server SP2) on the workstation but the "Business Intelligence Projects", "Business Intelligence Wizards", and "Report Projects" are missing from the New > Project types. How do I get them added?
I can access the network SQL Server and the http://<Server>/Reports from the workstation.
On the SQL Server I can develop & deploy reports using BIDS (Business Intelligence Development Studio), on the server.
But on the workstation I cannot create a new Report Project nor can I open an existing reporting project (rptproj) or solution. (I get the error that rptproj is not intalled and that I should install the associated program...which is what?)
WORKSTATION:
Windows Vista Business with all the latest updates.
Visual Studio Pro 2005 with SP1 & Vista SP1 Update installed.
SQL Server Workstation Components (and Reporting Services components) & SP2 update installed.
NETWORK SERVER:
Windows Server 2003 StandardSQL Server 2005 Standard with SP2 & Reporting Services installed.
This should be a simple question, but I'm suprised that it is not working after installing what seemed like the appropriate components from the SQL Server 2005 disk.
Any suggestions on how to get the SSRS projects to show up and work in the Visual Studio 2005 on the development workstation?
Thanks!
-Erik
Within the SQL 2005 install program you have to go through an advanced install and select the development components to be installed as I believe they do not get installed by default.
|||I used the "Advanced" option when I installed.
But I just went back again to see it. Reporting Services is greyed out (I assume because it is already installed).
Now when I try though, I get the following messages and cannot install it.
Name: Microsoft SQL Server 2005 Tools Express Edition
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
TITLE: Microsoft SQL Server Setup
None of the selected features can be installed or upgraded. Setup cannot proceed since no effective change is being made to the machine. To continue, click Back and then select features to install. To exit SQL Server Setup, click Cancel.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=SQLSetup90&EvtType=28108
BUTTONS:
OK
So I'm inclined to think the Reporting Services components are already installed. Is there something I can check to verify if that is true? If not, why is it greyed out? In the advanced section I can check the Reporting Services component, but it won't let me install it, as it looks from above that setup believes it is already installed. Is there perhaps a registry key I need to manually delete, if it failed to install but left some key indicating it is installed? (It doesn't seem like this should be so hard to get going.)
Any other suggestions?
Thanks!
-Erik
|||In what order did you install VS Pro 2005 and SQL (including RS components)?
It sounds as though when you installed the RS components, the installation didn't recognize that VS was there, so it didn't integrate itself with VS. Or, if RS came first, that when when you installed VS it didn't "see" the RS components on the box.
I'm going to guess this is a Vista-related problem. Something didn't have permissions to look at something else, in the registry or wherever, when it installed and missed a stitch as a result.
I guess it could also be a mismatched edition problem. IOW, the Express version of the components don't talk to the Pro version of Studio. Studio Pro comes with the Reporting Services stuff (see http://msdn2.microsoft.com/en-us/vstudio/aa700921.aspx) so probably the SQL components install would think it's already there.
Two things to check:
Is it possible (check your Start menu carefully) that there are *two* shortcuts to different VS versions? (this happened to me once but I couldn't tell you why)
Since your Visual Studio version is Pro, can you try adding features to it to get the Reporting (or Business Intelligence) stuff to show up, rather than trying to do it through the SQL install?
|||Order of Installation:
Visual Studio 2005 Professional Edition first. (Used Custom Install and chose ALL features.)
Installed the VS 2005 SP1 update and the VS 2005 SP1 Update for Vista.
Then since I still couldn't see the SSRS projects, I installed SQL Server workstation components. (Advanced install to ensure SSRS features were included...but it seems the SSRS items were perceived as already being there.)
|||>>Permissions:
I have Admin rights on the box, so if there is a permission issue, it is not for lack of admin rights.
<,
I'm sorry, Erik, but you may be making an assumption here that is inappropriate on Vista. When you did the install, did you Run As Administrator? It doesn't matter that you have Admin rights on the box in some cases unfortunately.
>>Are the SSRS items a different VS install that I have to download and install from some MS download website? Like an add-on feature?
I didn't think so, with the Pro version, but that is not the version I have so I don't know for sure <s>. At work where I only have the Visual Web Developer Express and VB Express versions, I do have the BI components installed as a separate item from a separate add-on download. I think I got it from here http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_ReportAddin.msi
I guess the problem might be that the SQL Server Express version that got installed with VS is the problem? There is an update to SQL Server Express with "Advanced Services" that installs "a limited version of Reporting Services" http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=en
Do you have this?
I'm sorry I can't help you from my own experience, my edition here is "Team Edition for Developers".
But I just checked with my husband, who has the Pro version on his laptop -- he says he installed the BI pieces from SQL Server Developer Edition on top of Visual Studio install. So clearly he didn't get it from Pro.
>L<
|||If you are running VS2005 pro with Vista and trying to run reporting services there are two layer of issues, one correct features for reporting services in IIS 7 and two correct permissions to run VS2005 for web development in Vista because IIS main use is web development. The first link covers needed IIS 7 configuration for reporting services and the second covers permission and IIS issues in Vista by the Web development team at Microsoft.
One more thing VS2005 pro comes with a free developer edition you can install that as a named instance locally do the correct configuration for remote connection in configuration manager and surface area configuration tool and just use the production to run the stored procs and copy your code over to production as needed. There are about four files to copy the RDL and the related solution files you will see them under reports in your project, it makes development much easier. The only issue will be to not use Enterprise edition specific features in your development since production is standard. Hope this helps.
http://blogs.msdn.com/bwelcker/archive/2007/03/19/candy-apple-grey-installing-ssrs-and-windows-vista.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2086062&SiteID=1
|||Hmmm...still not working. (Thanks for the helpfull links though.)
I enabled IIS as indicated in the first link. Then I opened VS2005 Pro and still did not find the Business Intelligence projects or Reporting Services project types listed when selecting new > project. Next I tried opening an existing *.rptproj project which I had already developed on the SQL Server in it's Business Intelligence Development Studio and it gave me the following error on the development workstation,
"The application for project 'L:\<path>\<filename>.rptproj' is not installed. Make sure the application for the project type (.rptproj) is installed."
It does not appear that enabling the IIS helped or made a difference. I still cannot open an rptproj project and I cannot create a new Reporting Services project type on the development workstation. Did I miss something?
Any other suggestions or ideas are greatly appreciated.
Thanks!
-Erik
|||I did not add that because I assumed you will take my advice about installing the developer edition locally because I run the most expensive VS2005 called Team Suites but I still need to install the developer edition of SQL Server before getting BI development related features because I think BI development outside of the VS2005 database developer edition require SQL Server 2005 local installation. Why the VS2005 for BI development does not include the .NET languages, that is the reason to do database development in Team Suites I had to download a separate Addon.
http://msdn2.microsoft.com/en-us/vstudio/aa700921.aspx
That already installed when I did the full install of VS2005 Pro. In fact, during the install of the SQL Server 2005 workstation components, it asked if I wanted to install the workstation components to the SQLExpress instance or to the default instance. (I chose the default instance.)
I feel like I'm missing something obvious here. Please let me know if you see what I'm missing.
Still welcoming suggestions and associated questions. 
Thanks!
-Erik
|||(That already installed when I did the full install of VS2005 Pro. )
No VS2005 just install Express I was telling you to install the developer edition locally and enable all the features needed for remote connection in in configuration manager go to sql server services and turn on reporting services, browser services and in sql native client configuration go to client protocols and enable both named pipes and tcp/ip. Then go to surface area configuration tool to surface area configuration for services and connection in database engine click on remote connection and enable both local and remote connection and click on using both tcp/ip and named pipes. When you have enabled all you can do reporting services development.
|||I'm not at all arguing with you, but am I understanding this correctly?
So, if I want to use Visual Studio 2005 Professional on a development workstation to make SQL Server Reporting Services reports which will be hosted and used on a networked SQL Server 2005 Standard server, I have to also purchase and install SQL Server 2005 Developer edition to install on the development workstation?
Is that correct?
Is there no other way to use VS2005 Pro to build SSRS reports which will be run on the SQL Server 2005 Standard server? If the answer is no, then can anyone suggest an alternative?
If the goal is just to be able to develop the SSRS reports on the development workstation and deploy them for use on the SQL 2005 Server, then what do I need to do that? Are there any other alternatives? If there are additional scenarios, I'd appreciate it if someone (MICROSOFT?) could list the available software scenarios that would enable someone to be able to develop SSRS reports on a development workstation, for deployment and use on a SQL 2005 Standard Server. Is there a link somewhere that explains this? I couldn't seem to find it spelled out well anywhere.
For comparison... If I have Business Objects Enterprise or Crystal Reports Server, I know I need Crystal Reports Pro or Developer, etc. on the development workstation for developing the reports -- which will be deployed to the server. I realize that with SSRS there is more than one tool that can develop the reports, but I've not seen it clearly explained what tools a development workstation needs in order to write SSRS reports for deployment and use on the SQL 2005 (Standard) Server.
Can someone please enlighten me? Multiple scenarios would be appreciated too.
Thanks much!
-Erik
|||On the server:
Windows server
SQL Server
IIS
.net
Reporting services
(of course, the above could be split into a multiple server deployment)
On the developer workstation:
windows
.net
Visual Studio
Report designer
So the question is, what is report designer? It's a plug in that adds report design capabilities to Visual Studio. How do you install it? You get the cd that has reporting services, and run a custom install. Part of the install is a choice to install the client tools. You do not need sql server / reporting services on the developer workstation, you just need the sql server / reporting services cd to do the install of the client tools.
Of course, if you wanted another answer, you could always install BIDS (business intelligence design studio, it's also on the sql server cds) intead of VS and report designer, as it essentially is BOTH in one package. That's for folks who want to do report design but NOT fun things like asp.net, and who also don't want to pay the licensing fees for a full copy of Visual Studio.
Mike G.
|||Your assumption is not correct because VS2005 pro comes with free SQL Server developer edition but it comes locked down like the Express you just need to use the steps I posted to get it to work like the standard and Enterprise. One more thing the developer edition comes with no deployment restriction so it is under $40 on th web, no RDBMS vendor gives you that option.Friday, February 24, 2012
How to comair DB objects in Development server with Production server
I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksThere are many database comparison tools in the market. For example:
redgate.com
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||http://www.aspfaq.com/2209
Also, think about WinDiff if you have Visual Studio installed...
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksThere are many database comparison tools in the market. For example:
redgate.com
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||http://www.aspfaq.com/2209
Also, think about WinDiff if you have Visual Studio installed...
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
Sunday, February 19, 2012
How to Choose Method to Transfer Data from Production to Reporting database
My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
Thanks
To determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegr oups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>
|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.
|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegro ups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
Thanks
To determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegr oups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>
|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.
|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegro ups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
How to Choose Method to Transfer Data from Production to Reporting database
My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
ThanksTo determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegroups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
>
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegroups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
ThanksTo determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegroups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
>
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegroups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
Subscribe to:
Posts (Atom)