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 on DTS-Openquery timeout on linked server

I have:
- One linked server
- One DTS with an OpenQuery on linked server
I set the Connection Timeout and General Timeout on the DTS Connection to 0
I set the Command Timeout on DTS Step to 0
I set the Connection Timeout and Query Timeout on the Linked server to 0
Always I receive a provider timeout after 10 minutes.
This is the query:
INSERT INTO openquery(history,'select * from
HistoryContact.dbo.AnomalContact')
SELECT Contact.*
FROM Contact INNER JOIN openquery(storico,'select * from
HistoryContact.dbo.HistoryContact) as CS
ON (Contact.ID = CS.ID) WHERE Contact.STATE<>'0'
Anyone know why?
Thank you very much.
RobertoRoberto
Do you have ping to remote/linked servers?
"Roberto Rasto" <rastoroberto@.hotmail.it> wrote in message
news:ds4pga$bpj$1@.nnrp.ngi.it...
>I have:
> - One linked server
> - One DTS with an OpenQuery on linked server
> I set the Connection Timeout and General Timeout on the DTS Connection to
> 0
> I set the Command Timeout on DTS Step to 0
> I set the Connection Timeout and Query Timeout on the Linked server to 0
> Always I receive a provider timeout after 10 minutes.
> This is the query:
> INSERT INTO openquery(history,'select * from
> HistoryContact.dbo.AnomalContact')
> SELECT Contact.*
> FROM Contact INNER JOIN openquery(storico,'select * from
> HistoryContact.dbo.HistoryContact) as CS
> ON (Contact.ID = CS.ID) WHERE Contact.STATE<>'0'
> Anyone know why?
> Thank you very much.
> Roberto
>|||Yes, a small query is executed.
The query below need approximately 30 minutes normally, so I need an
infinity timeout.
Ho can I force this timeout?
Thanks.
Roberto
"Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
news:%23KLCltlKGHA.344@.TK2MSFTNGP11.phx.gbl...
> Roberto
> Do you have ping to remote/linked servers?
>
> "Roberto Rasto" <rastoroberto@.hotmail.it> wrote in message
> news:ds4pga$bpj$1@.nnrp.ngi.it...
>sql

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim Wile
Jim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:
> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed on sum query

i have two table, one containing banking files header (a) information and a
second table containing the banking transactions (b) they are linked by a
LedgerKey field
The banking transaction my have a trans code of either 99 0r 17. Im trying
to get the sum of the header files where transaction trans code = 99, as i
need to knw what the client credit is. There can be several Header records,
but none of the transaction codes are mixed ie one file header will only
have transcode 99 or transcode 17
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from Table1 a
JOIN Table2 b
on a.LedgerKey = b.LedgerKey
where a.Licence = 123456
AND b.TransCode = '99'
a.TransValue varchar(11)
a.LedgerKey Varchar(12)
a.Licence Varchar(6)
b.LedgerKey varchar(12)
b.TransCode varchar (2)
in this instance there are 7 records in table b and im getting a SUM result
of 7 x a.TransValue instread if 1 X a.TransValue
Im a bit reluctant to add the ddl as it wil take a few hours to create
sample date, as for ovious reasons i can not post up live account details.Peter Newman wrote:
> i have two table, one containing banking files header (a)
> information and a second table containing the banking transactions
> (b) they are linked by a LedgerKey field
> The banking transaction my have a trans code of either 99 0r 17. Im
> trying to get the sum of the header files where transaction trans
> code = 99, as i need to knw what the client credit is. There can be
> several Header records, but none of the transaction codes are mixed
> ie one file header will only have transcode 99 or transcode 17
> Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
> from Table1 a
> JOIN Table2 b
> on a.LedgerKey = b.LedgerKey
> where a.Licence = 123456
> AND b.TransCode = '99'
> a.TransValue varchar(11)
> a.LedgerKey Varchar(12)
> a.Licence Varchar(6)
> b.LedgerKey varchar(12)
> b.TransCode varchar (2)
> in this instance there are 7 records in table b and im getting a SUM
> result of 7 x a.TransValue instread if 1 X a.TransValue
> Im a bit reluctant to add the ddl as it wil take a few hours to create
> sample date, as for ovious reasons i can not post up live account
> details.
A few hours? It took me five minutes to simulate your situation:
LedgerKey Varchar(12),
TransValue varchar(11),
Licence Varchar(6))
create table #trans (
LedgerKey varchar(12),
TransCode varchar (2))
insert into #header
select 'abc','1000.00','123456'
insert into #trans
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
go
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from #header a
WHERE EXISTS (Select * FROM #trans b
where a.LedgerKey = b.LedgerKey and
a.Licence = 123456
AND b.TransCode = '99')
drop table #header
drop table #trans
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Help needed on SQLServer , Error 18456

Hi All,

I have tried accessing a remote database in one of by stored procs using linked servers and also using OpenDataSource method.
In both the cases , I am getting login failed error.

Following is the stored proc :

CREATE PROCEDURE TEST AS

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=blrkec3432s;User ID=xyz;Password=xyz').LMC.dbo.STATE
GO

It works fine if the userid is 'sa'

Could anyone please tell me the reason for this.

Thanks,
ShanthiResolution from SQLMAG link (http://www.winnetmag.com/SQLServer/Article/ArticleID/8992/8992.html)

Help needed !!!! the colors used by a bar chart in SQL Server 2005 Reporting Services.

I have created a linked report in which I have two screen:

First Screen: Clicking on the bar of particular country the details of that particular country should be visible (the second screen)

Second Screen: When I clicked on a bar. All the count values are very well correct. In first chart Count of values under one bar "Pending decision"(yellow) was 337 for a country and in second screen its 337 again(shown in light green bar).

<!--[if !vml]--><!--[endif]-->

Problem: My problem is to make it sure that the colors that the first screen is having for particular status (as shown in the legend) should remain same in second screen also. For example for a status say "pending decision" the chart is using yellow color, so in screen two as well it must be shown with yellow color and not with lany other color. Can anyone help me in this context. Is there any way to customize colors used by bar chart. Please note that the question is not about using appropriate color scheme(palette) the question is how to "capture/ Re-use/ customize/ pass as a parameter" the colors used by a bar chart in SQL Server 2005 Reporting Services.

This may help you.

http://www.cubido.at/Default.aspx?tabid=176&EntryID=29

cheers,

Andrew