Help! Linked Server between 6.5 and 2000 error

Hi
I have 2 servers: Server A: SQL 6.5 and Server B: SQL 2000.
on Server A, I added Server B as a remote server and added a remote login
(trust) from Server B via Enterprise Manager.
on Server B, I added Server A as a linked server and add remote login (the
same login as server A and password) via Enterprise manager.
On Server B, I can make RPC on Server A. But on Server A, when I do the
following: serverB.mydb.dbo.sp_helpuser, I got error: Error:18483 at Line:0
Message:Could not connect to server 'ServerB' because 'mylogin' is not
defined as a remote login at the server.
What is the problem here? I actually already have sa have the same setting
between A and B. It works both ways. Why the one I just added does not work?
Need help!
Julia
> Hi
> I have 2 servers: Server A: SQL 6.5 and Server B: SQL 2000.
> on Server A, I added Server B as a remote server and added a remote login
> (trust) from Server B via Enterprise Manager.
> on Server B, I added Server A as a linked server and add remote login
(the
> same login as server A and password) via Enterprise manager.
> On Server B, I can make RPC on Server A. But on Server A, when I do the
> following: serverB.mydb.dbo.sp_helpuser, I got error: Error:18483 at
Line:0
> Message:Could not connect to server 'ServerB' because 'mylogin' is not
> defined as a remote login at the server.
> What is the problem here? I actually already have sa have the same
setting
> between A and B. It works both ways. Why the one I just added does not
work?
> Need help!
> Julia
SQL 6.5 connecting to SQL 2000 won't work. SQL 2000 did not exist when SQL
6.5 was designed. Hence, SQL 6.5 does not support SQL Server 2000 as a
remote server.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Help! Linked Server between 6.5 and 2000 error

Hi
I have 2 servers: Server A: SQL 6.5 and Server B: SQL 2000.
on Server A, I added Server B as a remote server and added a remote login
(trust) from Server B via Enterprise Manager.
on Server B, I added Server A as a linked server and add remote login (the
same login as server A and password) via Enterprise manager.
On Server B, I can make RPC on Server A. But on Server A, when I do the
following: serverB.mydb.dbo.sp_helpuser, I got error: Error:18483 at Line:0
Message:Could not connect to server 'ServerB' because 'mylogin' is not
defined as a remote login at the server.
What is the problem here? I actually already have sa have the same setting
between A and B. It works both ways. Why the one I just added does not wor
k?
Need help!
Julia> Hi
> I have 2 servers: Server A: SQL 6.5 and Server B: SQL 2000.
> on Server A, I added Server B as a remote server and added a remote login
> (trust) from Server B via Enterprise Manager.
> on Server B, I added Server A as a linked server and add remote login
(the
> same login as server A and password) via Enterprise manager.
> On Server B, I can make RPC on Server A. But on Server A, when I do the
> following: serverB.mydb.dbo.sp_helpuser, I got error: Error:18483 at
Line:0
> Message:Could not connect to server 'ServerB' because 'mylogin' is not
> defined as a remote login at the server.
> What is the problem here? I actually already have sa have the same
setting
> between A and B. It works both ways. Why the one I just added does not
work?
> Need help!
> Julia
--
SQL 6.5 connecting to SQL 2000 won't work. SQL 2000 did not exist when SQL
6.5 was designed. Hence, SQL 6.5 does not support SQL Server 2000 as a
remote server.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Help! Dates and SQL

Hi
I would like to create a SP where it will populate TableA based from TableB.
TableB will be populated on a monthly basis using a DTS and within that I
would like to run the SP to populate TableA.

Can someone here please help me create the sql statements as a starting
point.

TIA!
Bob

TableB (source)
from_date to_date curr_code ex_rate
1/1/2004 1/10/2004 CAD .75000
1/11/2004 1/16/2004 CAD .74321
1/17/2004 2/4/2004 CAD .72222
2/5/2004 2/20/2004 CAD .71111
2/21/2004 2/28/2004 CAD .77888
3/1/2004 3/3/2004 CAD .79002
3/4/2004 3/14/2004 CAD .76803
3/15/2004 3/23/2004 CAD .70022
3/24/2004 4/2/2004 CAD .73365
etc...

TableA (destination):
date curr_code ex_rate
1/2004 CAD 0.738477 calculation:(.75000+
..74321+.72222) / 3
2/2004 CAD 0.737403
(.72222+.71111+.77888) / 3
3/2004 CAD 0.74798
(.79002+.76803+.70022+.73365) / 4
etc.."B" <no_spam@.no_spam.com> wrote in message
news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> Hi
> I would like to create a SP where it will populate TableA based from
> TableB.
> TableB will be populated on a monthly basis using a DTS and within that I
> would like to run the SP to populate TableA.
> Can someone here please help me create the sql statements as a starting
> point.
> TIA!
> Bob
>
> TableB (source)
> from_date to_date curr_code ex_rate
> 1/1/2004 1/10/2004 CAD .75000
> 1/11/2004 1/16/2004 CAD .74321
> 1/17/2004 2/4/2004 CAD .72222
> 2/5/2004 2/20/2004 CAD .71111
> 2/21/2004 2/28/2004 CAD .77888
> 3/1/2004 3/3/2004 CAD .79002
> 3/4/2004 3/14/2004 CAD .76803
> 3/15/2004 3/23/2004 CAD .70022
> 3/24/2004 4/2/2004 CAD .73365
> etc...
> TableA (destination):
> date curr_code ex_rate
> 1/2004 CAD 0.738477 calculation:(.75000+
> .74321+.72222) / 3
> 2/2004 CAD 0.737403
> (.72222+.71111+.77888) / 3
> 3/2004 CAD 0.74798
> (.79002+.76803+.70022+.73365) / 4
> etc..
>

Here's one possible solution. In future, please post CREATE TABLE and INSERT
statements for your tables and data - other people can then simply cut and
paste into Query Analyzer, and we don't have to guess about data types,
keys, constraints etc.:

http://www.aspfaq.com/etiquette.asp?id=5006

Note that 'date' is a reserved keyword, so you should avoid using it as a
column name - I've used start_of_month instead. See "Reserved Keywords" in
Books Online.

Simon

create table b (
from_date datetime not null,
to_date datetime not null,
curr_code char(3) not null,
ex_rate decimal(6,5) not null,
constraint pk_b primary key (from_date, to_date, curr_code)
)
go
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040101', '20040110', 'CAD', 0.75000)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040111', '20040116', 'CAD', 0.74321)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040117', '20040204', 'CAD', 0.72222)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040205', '20040220', 'CAD', 0.71111)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040221', '20040228', 'CAD', 0.77888)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040301', '20040303', 'CAD', 0.79002)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040304', '20040314', 'CAD', 0.76803)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040315', '20040323', 'CAD', 0.70022)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040324', '20040402', 'CAD', 0.73365)
go

select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate) as
'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime)
) dt
join b
on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
group by dt.start_of_month
go

drop table b
go|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
>> Hi
>> I would like to create a SP where it will populate TableA based from
>> TableB.
>> TableB will be populated on a monthly basis using a DTS and within that I
>> would like to run the SP to populate TableA.
>>
>> Can someone here please help me create the sql statements as a starting
>> point.
>>
>> TIA!
>> Bob
>>
>>
>> TableB (source)
>> from_date to_date curr_code ex_rate
>> 1/1/2004 1/10/2004 CAD .75000
>> 1/11/2004 1/16/2004 CAD .74321
>> 1/17/2004 2/4/2004 CAD .72222
>> 2/5/2004 2/20/2004 CAD .71111
>> 2/21/2004 2/28/2004 CAD .77888
>> 3/1/2004 3/3/2004 CAD .79002
>> 3/4/2004 3/14/2004 CAD .76803
>> 3/15/2004 3/23/2004 CAD .70022
>> 3/24/2004 4/2/2004 CAD .73365
>> etc...
>>
>> TableA (destination):
>> date curr_code ex_rate
>> 1/2004 CAD 0.738477 calculation:(.75000+
>> .74321+.72222) / 3
>> 2/2004 CAD 0.737403
>> (.72222+.71111+.77888) / 3
>> 3/2004 CAD 0.74798
>> (.79002+.76803+.70022+.73365) / 4
>> etc..
>>
>>
>>
>>
> Here's one possible solution. In future, please post CREATE TABLE and
> INSERT statements for your tables and data - other people can then simply
> cut and paste into Query Analyzer, and we don't have to guess about data
> types, keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go

<snip
Oops - the query I posted before won't handle multiple currencies correctly.
This version should.

select dt.start_of_month, dt.curr_code, avg(b.ex_rate) as 'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', curr_code, count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime),
curr_code
) dt
join b
on dt.curr_code = b.curr_code and
(
dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
)
group by dt.start_of_month, dt.curr_code

Simon|||The solution you recently posted is exactly what I am looking for.

And I am aware that "date" is a reserved word, i quickly sent the original
post out of haste.

Many thanks for your time!
Bob

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> > Hi
> > I would like to create a SP where it will populate TableA based from
> > TableB.
> > TableB will be populated on a monthly basis using a DTS and within that
I
> > would like to run the SP to populate TableA.
> > Can someone here please help me create the sql statements as a starting
> > point.
> > TIA!
> > Bob
> > TableB (source)
> > from_date to_date curr_code ex_rate
> > 1/1/2004 1/10/2004 CAD .75000
> > 1/11/2004 1/16/2004 CAD .74321
> > 1/17/2004 2/4/2004 CAD .72222
> > 2/5/2004 2/20/2004 CAD .71111
> > 2/21/2004 2/28/2004 CAD .77888
> > 3/1/2004 3/3/2004 CAD .79002
> > 3/4/2004 3/14/2004 CAD .76803
> > 3/15/2004 3/23/2004 CAD .70022
> > 3/24/2004 4/2/2004 CAD .73365
> > etc...
> > TableA (destination):
> > date curr_code ex_rate
> > 1/2004 CAD 0.738477 calculation:(.75000+
> > .74321+.72222) / 3
> > 2/2004 CAD 0.737403
> > (.72222+.71111+.77888) / 3
> > 3/2004 CAD 0.74798
> > (.79002+.76803+.70022+.73365) / 4
> > etc..
> Here's one possible solution. In future, please post CREATE TABLE and
INSERT
> statements for your tables and data - other people can then simply cut and
> paste into Query Analyzer, and we don't have to guess about data types,
> keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go
> select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate)
as
> 'ex_rate'
> from
> (
> select cast(convert(char(6), from_date, 112) + '01' as datetime) as
> 'start_of_month', count(*) as 'num'
> from b
> group by cast(convert(char(6), from_date, 112) + '01' as datetime)
> ) dt
> join b
> on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
> datetime)
> or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
> datetime)
> group by dt.start_of_month
> go
> drop table b
> go

HELP!

hi!
I have a table who name is table0. its have a values like colum0: 01 colum1
: d01 and other table(this name is table1) have linked values to 01 and
d01. I want to update d01 value in table1, look from table0. I have
32000-35000 value in table0 and table1.So how can I do it?
Thanks,,,,,,
<m_guner18@.hotmail.com> wrote in message news:...
> hi!
> I have a table who name is table0. its have a values like colum0: 01
colum1
> : d01 and other table(this name is table1) have linked values to 01 and
> d01. I want to update d01 value in table1, look from table0. I have
> 32000-35000 value in table0 and table1.So how can I do it?
> Thanks,,,,,,
>
|||update table0
set colum0= 01, colum1= d01
from table0,table1
where table1.column0=table0.column0 and table1.column1=table0.column1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"m_guner18@.hotmail.com" wrote:

> hi!
> I have a table who name is table0. its have a values like colum0: 01 colum1
> : d01 and other table(this name is table1) have linked values to 01 and
> d01. I want to update d01 value in table1, look from table0. I have
> 32000-35000 value in table0 and table1.So how can I do it?
> Thanks,,,,,,
>
>