How to create second publisher record on the server?

Hello...
What we are trying to accomplish is to be able to replicate from Sql Server 2000 to some laptops running MSDE across the internet. These laptops are NOT part of our domain. I have set up a publication to allow for anonymous pull subscriptions for transa
ctional replication. Everything works fine now...except that for my test I had to setup/configure the laptop with a Hosts and LMHosts file so that it could resolve the server's name from its fully qualified name. This will be a pain for us to implement
and deploy....as we will have hundreds of client machines connecting to our server. The problem has to do with a sql server table that stores the name of the server publisher & distributor.
I noticed (via the sql server profiler tool) that when replication begins, there is some intial login/handshaking that occurs and one of the principal commands that gets executed is something like: exec sp_helpdistpublisher N'ServerName'. This stored pr
oc queries the MSDistPublisher table in the MSDB database. The stored proc returns a single row of data which includes our server's name...as it has been setup...it's machine name. This table does not include (obviously) a record that has the server's fu
lly qualified domain name (ServerName.Domain.Com).
My question is: is there a way to add an additional row of data and use the fully qualified domain name?
thanks for any help.
- DW
in your application, can't you hard code the publisher name with the FQDN
for the publisher/distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DW" <DW@.discussions.microsoft.com> wrote in message
news:62391DE0-137B-46EE-9024-9E679829FD84@.microsoft.com...
> Hello...
> What we are trying to accomplish is to be able to replicate from Sql
Server 2000 to some laptops running MSDE across the internet. These laptops
are NOT part of our domain. I have set up a publication to allow for
anonymous pull subscriptions for transactional replication. Everything
works fine now...except that for my test I had to setup/configure the laptop
with a Hosts and LMHosts file so that it could resolve the server's name
from its fully qualified name. This will be a pain for us to implement and
deploy....as we will have hundreds of client machines connecting to our
server. The problem has to do with a sql server table that stores the name
of the server publisher & distributor.
> I noticed (via the sql server profiler tool) that when replication begins,
there is some intial login/handshaking that occurs and one of the principal
commands that gets executed is something like: exec sp_helpdistpublisher
N'ServerName'. This stored proc queries the MSDistPublisher table in the
MSDB database. The stored proc returns a single row of data which includes
our server's name...as it has been setup...it's machine name. This table
does not include (obviously) a record that has the server's fully qualified
domain name (ServerName.Domain.Com).
> My question is: is there a way to add an additional row of data and use
the fully qualified domain name?
> thanks for any help.
> - DW

How to create named query across 2 different data sources?

I have a scenario where i need to create a named query between 2 tables are in 2 difference data sources. Iam not sure how to create one for this case. The named view UI forces to pick one data source and validates both the table against it. ( in my case the second table belongs to a different data source). I even tried using the data souce name as prefix and forming the query directly and nothing seems to work even though the documentation clearly mentions that we can create named queries across multiple data source. Any ideas on how to do this? Do i have to use a OpenRowset and directly try to use the second table from a different data source?I ran into the same problem, where my lookup tables were in one database (SQLServer 2k5) and my fact tables were in another database (SQLServer 2k). I ended up copying my lookup tables over to the 2k machine, and eliminating one of my datasources.

What i wanted to try (and maybe you could) was create a linked server between the 2 database servers, and just reference the other machine in your named query.

I never got around to it, because my DBA said there was some problem (pre SP2 i think) with creating linked servers between 2k and 2k5.

Anyway, if you can create the linked servers, i'd give it a go.

Csql

How to count across multiple tables in a DB?

Thank you in advance for your assitance. I am trying to write a query that will query multiple tables for the same column. All the tables have thsi column "szF11". I am wanting something similar to this:

Code Snippet

SELECT count(ulID)

FROM (dbo.F_ACCOU_Data UNION dbo.F_AGNCY_Data UNION dbo.F_APPEA_Data UNION etc.....)

WHERE szF11 = ' '

Note: ulID is the name of a column that every table has and szF11 is also in every table.

Pseudo Code: I want to count how many ulID's (if there is a row then something is in the ulID column it is never blank) in all the tables that are listed that have a blank in the szF11 column.

I am getting a very cryptic error message and of course I can't find anything in the documentation to help me understand the error.

Thanks,

Erik

You have a few options.

You could do use a derived table:

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
UNON ALL
SELECT uID, szF11
FROM F_APPEA_Data
UNION
etc....) AS Tbl
WHERE sF11 = ''

A variation on this could be that you include the WHERE clause for each table within Tbl

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
WHERE sF11 = ''
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
WHERE sF11 = ''
etc...) AS Tbl

Another option would be to include the tablename in the derived table so you can find out how many rows per table

Code Snippet

SELECT TableName, COUNT(uID)
FROM
(SELECT uID, 'F_ACCOU_Data' AS TableName, sF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, 'F_AGNCY_Data', sF11
FROM F_AGNCY_Data
etc...
) AS tbl
WHERE sF11 = ''
GROUP BY TableName

HTH!

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***
"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
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
|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> 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
> --
|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>
|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***
|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
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
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> 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
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.developersdex.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
--
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
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > We got a production database that gets
> >
> > tables added to it daily.
> > So, every single day we transfer those new
> >
> > tables via DTS to five (5) other servers!
> >
> > Can we accomplish the same thing using a
> >
> > 'Select' statement?
> > I found no way to use the 'select' stmt
> >
> > across servers.
> >
> > I'm not conversant with TSQL (yet!) and
> >
> > would very much appreciate any help you can
> >
> > give me.
> >
> > Thank you very much
> >
> > mike
> >
> >
> > Mike
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> 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
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:
> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
> > "mike" <-nospam@.yahoo.com> wrote in message
> > news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > We got a production database that gets
> > >
> > > tables added to it daily.
> > > So, every single day we transfer those new
> > >
> > > tables via DTS to five (5) other servers!
> > >
> > > Can we accomplish the same thing using a
> > >
> > > 'Select' statement?
> > > I found no way to use the 'select' stmt
> > >
> > > across servers.
> > >
> > > I'm not conversant with TSQL (yet!) and
> > >
> > > would very much appreciate any help you can
> > >
> > > give me.
> > >
> > > Thank you very much
> > >
> > > mike
> > >
> > >
> > > Mike
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> >
> >
> > Did I understand you correctly? Your database schema changes DAILY? I know
> > nothing of your application obviously but new tables daily sounds like you
> > have some major issues that ought to be better fixed by a different
> > architecture or a better change control process.
> >
> > The best way to replicate schema changes is to script them and then apply
> > the scripts. Or invest in a tool like RedGate SQL Compare
> > (http://www.red-gate.com/). Don't you do that anyway in order to test the
> > changes?
> >
> > --
> > 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
> > --
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
--
Adam J Warne, MCDBA
"mike" wrote:
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
>