10 shop different IP address - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

10 shop different IP address

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-28 : 02:19:43

We have 9 shop installed POS system and how to get the 10 shop of daily sales transaction by use of different shop IP Address ? I don't want to repeat typing 9 time for change IP address as well.

select * from xsoheader inner join
xsodetail.memonum = xsoheader.memonum where xshopcode='%00*'

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-28 : 05:02:17

use linked server

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-29 : 03:10:33

how to auto change the IP address to connection different shop using in SQL query command ?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-29 : 09:13:34

1. Use linked servers for each remote db
2. Use a synonym to refer to the server in the query
3. In a while loop, change the synonym and run the query.

e.g.

declare @i int = 1, @n int = 10
while @i <= @j begin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-29 : 09:13:34

1. Use linked servers for each remote db
2. Use a synonym to refer to the server in the query
3. In a while loop, change the synonym and run the query.

e.g.


declare @i int = 1, @n int = 10
while @i <= @j begin

if exists (select 1 from sys.synonyms where name = N'mysynonym')
    drop synonym mysynonym

if @i = 1
    create synonym mysynonym for server1.db.schema.table
else if @i = 2
    create synonym mysynonym for server2.db.schema.table
-- etc. --
select ...
from mysynonym
...
set @i += 1
end

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-30 : 02:12:01

I would like to give my db name is hkoffice and each shop have two table name is sales_header and sales_detail , then how to using above query, pls give me sample . if some shop the vpn disconnect , if it is display prompt message in which shop name ? Thanks

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-30 : 02:31:22

I want use above of query and combine in below query. how to do writing ? The desire result is sum up all the 10 shop the total daily sales amount and save in new table. Please edit my query. Thanks

declare @i int = 1, @n int = 10
while @i <= @j begin

if exists (select 1 from sys.hkoffice where name = N'mysynonym')
select sum(saleamt) from xsoheader inner join xsodetail. xsodetail.memono = xsoheader.memono where xsoheader.voidflag='N'
else select "VPN disconnect "+shopcode
if @i = 1
create shopcode[i]_and_sales for server1.db.schema.table
else if @i = 2
create shopcode[2]_and_sales for server2.db.schema.table

insert into All_shop_new_sales

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-30 : 04:27:08

how to auto link with different IP address shop using above of query and sum up the shop sales amount with save into new table ?

shop A ip address. 192.168.2.1 , sales_header & sales_detail Sql table
shop B ip address. 192.3.23.11 , sales_header & sales_detail sql table
shop c ip address. 192.168.44.122 , sales_header & sales_detail sql table
shop d ip
shop e ip
shop f ip

Office Database base 192.123.2.1 and new all_shop_new_sales table .

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-30 : 09:09:56

1. add linked servers to all the remote servers. one linked server per ip address
2. Initialize a variable to sum up the sales amounts. e.g. declare @sumsales decimal = 0;
3. add the amounts from each remote server. e.g.


set @sumsales += (
    select sum(saleamt) from linkedserver1.salesdb.xsoheader  as h 
    inner join linkedserver1.salesdb.xsodetail d on h.memono = d.memono 
    where h.voidflag='N')
set @sumsales += (
    select sum(saleamt) from linkedserver2.salesdb.xsoheader  as h 
    inner join linkedserver2.salesdb.xsodetail d on h.memono = d.memono 
    where h.voidflag='N')
... etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources