Help on linked server

Hi All,
I need your expertise comments for using linked server in WAN
environment. I need to suggest it to my client.
Here is the scenario:
My client is having 20 retail shops across US. He got a Central office
located in OHIO state.
All the stores are connected through WAN.
Now we are developing a transaction system for store's. All stores are
going to use MSDE.
One Enterprise SQL server is going to be installed in central office.
In this scenario will linked server work for distributing transaction
across stores?.
For example,
The gift card transaction happened in one store should be immediately
updated (or in frequent interval)
to central server. This enables other store to pickup the transaction
on need basis.
I used linked server in LAN environment and it works. But I could not
think of WAN environment.
What ports need to be opened for these SQL transaction?
What environement is required to enable linked server (including
security)?
How do I create an environment in my Office itself, so that I will be
confirtable on suggesting it or any other way to acheive the immediate
update and retrieval (other than replication).
Please help.
With thanks,
VishHi
Your best bet would be SQL Server Replication. The central SQL Server would
pull data from the MSDE instances.
Have a look at the links off
http://www.microsoft.com/technet/pr...crosoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vish" <vichumca@.gmail.com> wrote in message
news:1127131645.102228.245660@.f14g2000cwb.googlegroups.com...
> Hi All,
> I need your expertise comments for using linked server in WAN
> environment. I need to suggest it to my client.
> Here is the scenario:
> My client is having 20 retail shops across US. He got a Central office
> located in OHIO state.
> All the stores are connected through WAN.
> Now we are developing a transaction system for store's. All stores are
> going to use MSDE.
> One Enterprise SQL server is going to be installed in central office.
> In this scenario will linked server work for distributing transaction
> across stores?.
> For example,
> The gift card transaction happened in one store should be immediately
> updated (or in frequent interval)
> to central server. This enables other store to pickup the transaction
> on need basis.
> I used linked server in LAN environment and it works. But I could not
> think of WAN environment.
> What ports need to be opened for these SQL transaction?
> What environement is required to enable linked server (including
> security)?
> How do I create an environment in my Office itself, so that I will be
> confirtable on suggesting it or any other way to acheive the immediate
> update and retrieval (other than replication).
>
> Please help.
> With thanks,
> Vish
>|||Dear Mike,
Replication is a good choice. But I am not going to replicate
everything. Only customized transactions are used.
Please reply
Regards
Vish
*** Sent via Developersdex http://www.codecomments.com ***|||you can select which articles to replicate
"viswanathan somasundaram" <vichumca@.rediffmail.com> wrote in message
news:O0pJfxRvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>
> Dear Mike,
> Replication is a good choice. But I am not going to replicate
> everything. Only customized transactions are used.
> Please reply
> Regards
> Vish
> *** Sent via Developersdex http://www.codecomments.com ***

Help on linked server

Hi All,
I need your expertise comments for using linked server in WAN
environment. I need to suggest it to my client.
Here is the scenario:
My client is having 20 retail shops across US. He got a Central office
located in OHIO state.
All the stores are connected through WAN.
Now we are developing a transaction system for store's. All stores are
going to use MSDE.
One Enterprise SQL server is going to be installed in central office.
In this scenario will linked server work for distributing transaction
across stores?.
For example,
The gift card transaction happened in one store should be immediately
updated (or in frequent interval)
to central server. This enables other store to pickup the transaction
on need basis.
I used linked server in LAN environment and it works. But I could not
think of WAN environment.
What ports need to be opened for these SQL transaction?
What environement is required to enable linked server (including
security)?
How do I create an environment in my Office itself, so that I will be
confirtable on suggesting it or any other way to acheive the immediate
update and retrieval (other than replication).
Please help.
With thanks,
VishHi
Your best bet would be SQL Server Replication. The central SQL Server would
pull data from the MSDE instances.
Have a look at the links off
http://www.microsoft.com/technet/prodtechnol/sql/2000/technologies/replictn.mspx
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vish" <vichumca@.gmail.com> wrote in message
news:1127131645.102228.245660@.f14g2000cwb.googlegroups.com...
> Hi All,
> I need your expertise comments for using linked server in WAN
> environment. I need to suggest it to my client.
> Here is the scenario:
> My client is having 20 retail shops across US. He got a Central office
> located in OHIO state.
> All the stores are connected through WAN.
> Now we are developing a transaction system for store's. All stores are
> going to use MSDE.
> One Enterprise SQL server is going to be installed in central office.
> In this scenario will linked server work for distributing transaction
> across stores?.
> For example,
> The gift card transaction happened in one store should be immediately
> updated (or in frequent interval)
> to central server. This enables other store to pickup the transaction
> on need basis.
> I used linked server in LAN environment and it works. But I could not
> think of WAN environment.
> What ports need to be opened for these SQL transaction?
> What environement is required to enable linked server (including
> security)?
> How do I create an environment in my Office itself, so that I will be
> confirtable on suggesting it or any other way to acheive the immediate
> update and retrieval (other than replication).
>
> Please help.
> With thanks,
> Vish
>

Help on linked server

Hi All,
I need your expertise comments for using linked server in WAN
environment. I need to suggest it to my client.
Here is the scenario:
My client is having 20 retail shops across US. He got a Central office
located in OHIO state.
All the stores are connected through WAN.
Now we are developing a transaction system for store's. All stores are
going to use MSDE.
One Enterprise SQL server is going to be installed in central office.
In this scenario will linked server work for distributing transaction
across stores?.
For example,
The gift card transaction happened in one store should be immediately
updated (or in frequent interval)
to central server. This enables other store to pickup the transaction
on need basis.
I used linked server in LAN environment and it works. But I could not
think of WAN environment.
What ports need to be opened for these SQL transaction?
What environement is required to enable linked server (including
security)?
How do I create an environment in my Office itself, so that I will be
confirtable on suggesting it or any other way to acheive the immediate
update and retrieval (other than replication).
Please help.
With thanks,
Vish
Hi
Your best bet would be SQL Server Replication. The central SQL Server would
pull data from the MSDE instances.
Have a look at the links off
http://www.microsoft.com/technet/pro.../replictn.mspx
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vish" <vichumca@.gmail.com> wrote in message
news:1127131645.102228.245660@.f14g2000cwb.googlegr oups.com...
> Hi All,
> I need your expertise comments for using linked server in WAN
> environment. I need to suggest it to my client.
> Here is the scenario:
> My client is having 20 retail shops across US. He got a Central office
> located in OHIO state.
> All the stores are connected through WAN.
> Now we are developing a transaction system for store's. All stores are
> going to use MSDE.
> One Enterprise SQL server is going to be installed in central office.
> In this scenario will linked server work for distributing transaction
> across stores?.
> For example,
> The gift card transaction happened in one store should be immediately
> updated (or in frequent interval)
> to central server. This enables other store to pickup the transaction
> on need basis.
> I used linked server in LAN environment and it works. But I could not
> think of WAN environment.
> What ports need to be opened for these SQL transaction?
> What environement is required to enable linked server (including
> security)?
> How do I create an environment in my Office itself, so that I will be
> confirtable on suggesting it or any other way to acheive the immediate
> update and retrieval (other than replication).
>
> Please help.
> With thanks,
> Vish
>
|||Dear Mike,
Replication is a good choice. But I am not going to replicate
everything. Only customized transactions are used.
Please reply
Regards
Vish
*** Sent via Developersdex http://www.codecomments.com ***
|||you can select which articles to replicate
"viswanathan somasundaram" <vichumca@.rediffmail.com> wrote in message
news:O0pJfxRvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>
> Dear Mike,
> Replication is a good choice. But I am not going to replicate
> everything. Only customized transactions are used.
> Please reply
> Regards
> Vish
> *** Sent via Developersdex http://www.codecomments.com ***

Help obtain a window of rows from a table

Hi,
I have a client program in vb.net that access a SQL server database. Each
time the client program need some data it retrieve the whole table, so it is
pretty slow.
I wonder if it is possible, for the client, to retrieve only a window of
rows around the actual value he is using. That is, if he is actually in row
4000 he will retrieve from row 3000 to 5000 but not the complete table. If
this is possible what I need is something like:
1) The client send SQL-Server a string with the actual ordering and the ID
of the actual row.
2) SQL-Server order the table following the order specified in the string
send by the client.
3) Using the ordered table SQL-Server "find" the ID of the actual row.
4) SQL-Server return a number of rows before and after the Id of the actual
row (no idea how to do this).
Any help.
Thanks,
JamesHi James,
Yes - its basically paging.
The basics are this...
declare @.results table (
idrow int not null identity,
yourresultcol1...
yourresultcol2...
)
insert @.results ( yourresultscol1, yourresultscol2 )
select yourresultscol1, yourresultscol2
from table...
where ...
order by ...
select *
from @.results
where idrow between @.start and @.finish
I know its not a complete working example but does that give you enough
idea?
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||Sorry, i forgot to mention, in SQL 2005 its a whole lot easier.
We have the rownumber() function and cte that does it all for us - there are
some really useful examples in bol.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uhnuUbNHGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi James,
> Yes - its basically paging.
> The basics are this...
> declare @.results table (
> idrow int not null identity,
> yourresultcol1...
> yourresultcol2...
> )
> insert @.results ( yourresultscol1, yourresultscol2 )
> select yourresultscol1, yourresultscol2
> from table...
> where ...
> order by ...
> select *
> from @.results
> where idrow between @.start and @.finish
> I know its not a complete working example but does that give you enough
> idea?
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "James" <info@.pricetech.es> wrote in message
> news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>|||James
I think Tom Moreau had already answered the same or almost the same question
a few days ago. Pls search on internet
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||James wrote:
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
is
> pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in ro
w
> 4000 he will retrieve from row 3000 to 5000 but not the complete table. If
> this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the actua
l
> row (no idea how to do this).
> Any help.
> Thanks,
> James
Take a look at:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

help needed for a query.

Hi

MailMethodId Is an integer, and it has 2 possible values 1 which means 'Mail to Participant' or 2 which means 'Mail To client'

select

cp.PlanId,

cp.ClientPlanId,

psi.MailMethodId,

psi.StatementTypeId,

cp.PlanName,

cp.ClientId ,

c.ClientName

from ClientPlan cp

Join PlanStatementInfo psi on cp.PlanId = psi.PlanId

Join Client c on cp.ClientId = c.ClientId

Where cp.ClientId = @.ClientId

Union

Select

cp.PlanId,

cp.ClientPlanId,

2,

1,

cp.PlanName,

cp.ClientId,

c.ClientName

From

ClientPlan cp

innerjoin Client c on cp.ClientId = c.ClientId

where

cp.ClientId = @.ClientId

and

cp.PlanId NotIN

(Select psi.PlanId from PlanStatementinfo psi)

So how can i get the Mail methodId to display 'Mail to Participant' if the data is 1 and 'Mail to Client' if the Data is 2

i tried doing this

case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,

instead of psi.MailmethodId, but i am getting a error message that says

Syntax error converting the varchar value 'Mail to Participant' to a column of data type int.

Any Help will be appreciated

Regards

KAren

Karen:

My knee-jerk reaction to this is that you probably have an incompatibility between how the "MailMethodID" column is used in each separate SELECT that comprises the UNION. Try it like this:

Code Snippet

select
cp.PlanId,
cp.ClientPlanId,
case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,
psi.StatementTypeId,
cp.PlanName,
cp.ClientId ,
c.ClientName
from ClientPlan cp
Join PlanStatementInfo psi on cp.PlanId = psi.PlanId
Join Client c on cp.ClientId = c.ClientId
Where cp.ClientId = @.ClientId
Union
Select
cp.PlanId,
cp.ClientPlanId,
'Mail to Client',
1,
cp.PlanName,
cp.ClientId,
c.ClientName
From
ClientPlan cp
inner join Client c on cp.ClientId = c.ClientId
where
cp.ClientId = @.ClientId
and
cp.PlanId Not IN
(Select psi.PlanId from PlanStatementinfo psi)

|||

Thanks a lot Ken, that worked.

Help me: About Sql server Internet merge Replication

i am working on merge replication over internet
i configure each and everything as Paul told in artical
i configure TCP\IP port at client network utility as 1433, also server at
server network utility as 1433
also FTP port at publisher properties is 21
but it is not working
it show error as "sql server not exist or access denied"
i configure client computer using client network utility,
server computer by server network utility
also proper publication and subcriber and ftp root
please solve my problem
need u'r kind help
thanking you
amy
I'm still pretty new at some of this replication stuff. But I went and
pinged the subscriber from the publisher and vice versa. Then I set up
Aliases through the configuration tool using TCP\IP and setting the alias
name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
know if this is helpful, but it sounds like to me that your servers just
can't see one another and an alias, using TCP/IP or even a linkedserver may
help.
Good Luck!!
Scott E. Hunley (MCAD)
Measure Twice, Cut Once!!!
"amy" wrote:

> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy
|||thanks for reply
please tell me how to set up
Aliases through the configuration tool using TCP\IP
i done pinging from subsciber from publisher and also publisher to subsciber
thanking you
amy
"Scott Hunley" wrote:
[vbcol=seagreen]
> I'm still pretty new at some of this replication stuff. But I went and
> pinged the subscriber from the publisher and vice versa. Then I set up
> Aliases through the configuration tool using TCP\IP and setting the alias
> name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
> know if this is helpful, but it sounds like to me that your servers just
> can't see one another and an alias, using TCP/IP or even a linkedserver may
> help.
> Good Luck!!
> --
> Scott E. Hunley (MCAD)
> Measure Twice, Cut Once!!!
>
> "amy" wrote:
|||If you were able to ping the machines successfully from one another then
that's a good sign. Now, in the Configuration Tool:
1. Right-Click the Alias node on the tree.
2. Enter the Name of the server you are communicating with (make sure these
are exactly the same.
3. Enter a port number, by default SQL uses 1433
4. choose TCP/IP
5. Enter the IP address of the server your communication with.
That should give you a way of communicating to the server.
Try that and let me know.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"amy" wrote:
[vbcol=seagreen]
> thanks for reply
> please tell me how to set up
> Aliases through the configuration tool using TCP\IP
> i done pinging from subsciber from publisher and also publisher to subsciber
> --
> thanking you
> amy
>
> "Scott Hunley" wrote:
|||I got the same error all morning while working with my Replication problems.
I got past this error by going into the properties for the publication and
adding my IUSR_MACHINENAME account to the publication access list. It started
working like a champ. This is with SQL 2005, I'm not sure of how to do it
with SQL 2000.
Hope this helps.
"amy" wrote:

> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy

help me with a bit of a practice issue...

Ok.

I recently started developing a web site for a client using storefront.net and ms sql server.

the db schema of storefront.net has autonumbers as the PKs for the products table (even though the products table contains an additional field for product_number.)

So here's my dilemma if you care to read:

I typically develop local, deploy remote (after testing). I have a local SQL server, and then the remote SQL server.

When I'm developing for this project, I'll insert data such as products to the products table (sometimes several times while i'm working out routines to import data to the products table.) this has the effect of creating a unique ID for each product based upon SQL auto-incrementing INTs.

This StoreFront.net (SF.NET) has another table that is a lookup table. For each part number, it has a corresponding categoryID number.

Now, if i have product_ID 1234, and I set the category ID to say 10 and get it working on my local box, every thing is fine.

Here's where the problem comes in: When I use DTS to transfer the database during remote deployment, each product is inserted into the remote DBs products table and gets a NEW product id. Same with the categories.

This has the effect of breaking the relationships. (SF.NET has no ref integrity nor relationships defined in the db.) let's say my product_id 1234 gets put into the remote copy, it'll get a new product_ID (PK). let's say it's now 5775. now my category ID will also get a new value. so my data is now not related.

I don't know how to handle this situation. The unique IDs generated on my local sql will nearly almost always be different from those generated on the remote db.

How do i handle this situatoin is my question? advice, guys?why are you using the product_id in the relation...shouldn't you be relating the product_number to the categoryId... rather ? which will stay fixed... ?|||well, not to be a smart alec, but duh. that's what should be done.

problem is the team who wrote this SF.NET app don't do that. they use the productID autonumber and categoryID autonumber.

so for example, when you use their merchant tools to set a product into a category, behind the scenes they are using the autonumber id of the product and not the REAL product number. same with category ID.|||I personally prefer the way StoreFront has implemented a surrogate key. This is somewhat of a religious topic in that proponents of each method are pretty adamant that "their" way is right :-)

Anyway, with DTS there is an option to "Enable identity insert" on the Options tab. Turning this on should cure your problem.

Terri

Help me to understand!

Hi All,

I need to build a report which is showing client who did not order since particular period, but have ordered within the past 3 months.

Is anyone can help me to understand the technique to get that data?

Thanks in advance.

hi Ron,

you need help in query or designing the reports.

query i think will be like this

select CustomerName from Customers Where CustomerId not in (select distinct customerid from Orders where OrdDate between @.fromdt and @.todt)

and CustomerId in (Select distinct customerid from Orders where orddate betwen getdate() and dateadd(m,-3,getdate())

hope it works.

regards,

satish

|||

Thanks Satish,

It works.

|||

Hi Satish,

Now my problem is that I want just showing the customer with the last invoice date. So something like this:

CustName Inv.Date
MER01 09/06/2006
JUN01 09/30/2006

Right now, I got multiple item with the same customer since their invoice date is different. I try using MAX(InvoiceDate), but
it seems did not work out.

Any idea...

|||I solved the problem. Thanks.|||

hi ronn

see if following helps out in case you want separate query if you want with earlier one let me know but first try if you can fight a bit on your own :)

select top 1 CustomerName, InvDate from Customers Inner Join Invoice on Invoice.CustomerID = Customer.CustomerID

group by CustomerName, InvDate

order by InvDate Desc

regards,

satish

help me gauge the effort in my first db setup

Hello there... Happy New Year!

A client of mine, a group of 4 doctors, are interested in implementing a small office network in which they will switch to a digital filing system. I have recommended a Microsoft Server 2003 system, therefore the patient information will be stored in the SQL server.

I have never administered, administrated?? (see, I really need help here!) a database, but have built small databases from scratch in C and with Access, so I feel confident that I could set up a system for them. I need to have some kind of estimate, both a fee and implementation schedule. Can you help me get a handle on the scope of this?

Many thanks,
BrynYou Access experience will give you a leg up on the learning curve for developing a SQL Server database, but you will still have a lot to learn about administration. Security (which I think would be important for a doctors office) is much different, but is also much better and much simpler in SQL server than in Access. You will also need to write your SQL queries, so I hope that you are somewhat fluent in that and have occasionally looked at the SQL code that Access creates. Be aware that there are some subtle differences between SQL Server SQL and MS Access SQL.

Your best for a small application like this is to create it as an MS Access ADP project. This will allow you to use Access as an efficient interface to SQL Server. This is not the best solution for large applications, but for what you are describing it should be fine. If they want to scale it up in the future, they can drop the Access interface and still retain the SQL Server database with a new interface.

I can't, of course, give you an estimate on how long it will take you to complete it. Personally, since you are learning on the job, I think you should quote them based on how long it would take to develop the database in MS Access and consider any additional time as training and career development.

blindman|||You have many client tools in sql server that you need to research:

Enterprise Manager - This is a gui interface to manage your sql server instances.
Query Analyzer - Interface to run/test transact-sql statements.
BOL - Books Online - This is the online reference guide to sql server - You will use this frequently as you work with sql server. (Almost) Any question you have, go here first.
Profiler - captures events from sql server - allowing you to debug/analyze a problem/performance.

For books, look for books by Ken Henderson, Mark Spenik and Staneks pocket admin.