push subscription: production environment

may I push a subscription to a server while that 'machine' is running the
application which is accessing the database?
You can, but its not advisable. When you send your subscription the data in
the tables is replaced unless you are doing the nosync option. This
replacement can involve dropping the table and recreating it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:e8qmPZP%23EHA.1392@.tk2msftngp13.phx.gbl...
> may I push a subscription to a server while that 'machine' is running the
> application which is accessing the database?
>

pulling all dates within a date range

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:

declare @.Sdate as datetime
declare @.Edate as datetime

set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'

select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)

this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

thanks in advanceOn 6 Jul 2006 14:14:40 -0700, rugger81 wrote:

Quote:

Originally Posted by

>I am currently working in the sql server 2000 environment and I want to
>write a function to pull all dates within a given date range. I have
>created several diferent ways to do this but I am unsatisfied with
>them. Here is what I have so far:

(snip)

Hi rugger81,

http://www.aspfaq.com/show.asp?id=2519
--
Hugo Kornelis, SQL Server MVP|||rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:
>
declare @.Sdate as datetime
declare @.Edate as datetime
>
set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'
>
select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)
>
this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

If I understand this correctly, given the sample data you want

2006-01-07, 2006-01-08, ... 2006-12-30, 2006-12-31

The best is simply to create a table of dates. Here is a script that
create our dates table:

TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)

-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks guys, I'll do just that. The idea of creating a date table
crossed my mind before, but I like to do things dynamically. Now that
I think of it however, a date table for the any time frame I would need
would still be relatively small and would save alot of time.

Erland Sommarskog wrote:

Quote:

Originally Posted by

rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:

declare @.Sdate as datetime
declare @.Edate as datetime

set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'

select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)

this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

>
If I understand this correctly, given the sample data you want
>
2006-01-07, 2006-01-08, ... 2006-12-30, 2006-12-31
>
The best is simply to create a table of dates. Here is a script that
create our dates table:
>
>
>
TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
>
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
>
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
>
>
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

publishing a ssrs2005 reprot on a 2000 system

Hello everyone!
Does anyone know if you can depoly a report created in SSRS2005 environment
on a SQL Server 2000 report server?
Thanks!
AmedeoHi
No, the RDL is different between the 2 versions.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amedeo Feroce" <aferoce@.akdhc.com> wrote in message
news:O8Obr1klGHA.5044@.TK2MSFTNGP02.phx.gbl...
> Hello everyone!
> Does anyone know if you can depoly a report created in SSRS2005
> environment on a SQL Server 2000 report server?
> Thanks!
> Amedeo
>

Publisher/Subscriber role

Can a replica database be publisher and at the same time subscriber. If so,
Is Merge replication OK for applying to go in this environment and how should
I configure the replicas as a subscribers or publishers?
Thanks for your answers.
Car.
yes this will work, its called republishing or hierarchies. Have a look at
this link for more info.
http://msdn2.microsoft.com/en-us/library/ms152553.aspx
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Car" <Car@.discussions.microsoft.com> wrote in message
news:5BFA3BA9-9DFE-4B86-A6D1-4FB5EEC89895@.microsoft.com...
> Can a replica database be publisher and at the same time subscriber. If
> so,
> Is Merge replication OK for applying to go in this environment and how
> should
> I configure the replicas as a subscribers or publishers?
> Thanks for your answers.
> Car.
|||Here is information on how to set it all up:
http://www.replicationanswers.com/Republishing2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Publisher, Distribution, Subscriber all on one SQL Server?

Should this work? In production we have one server acting as the
publisher/distributer, and then another as the subscriber.
In the test environment, I'd like to have all of this on the same
server. Will this work? I've run into what I hope is a minor gotcha
(running the drop replication scripts created by enterprise manager
doesn't work, exec sp_dropsubscription returns "The remote server is
not defined as a subscription server").
thanks for any hints.
Sylvia
Hey. I've the same setup at my company for testing. It works fine. I have 2
instances of SQL, one acting as a subscriber and the other acting as a
publisher/distributor for transactional and merge replication. I've 3
publications so far.
For your structure, how is it defined? On your publication, have you defined
the same server as a publisher as well as a subcriber?
|||I even do pull subscriptions using FTP on a single server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1130800544.680463.133070@.z14g2000cwz.googlegr oups.com...
> Should this work? In production we have one server acting as the
> publisher/distributer, and then another as the subscriber.
> In the test environment, I'd like to have all of this on the same
> server. Will this work? I've run into what I hope is a minor gotcha
> (running the drop replication scripts created by enterprise manager
> doesn't work, exec sp_dropsubscription returns "The remote server is
> not defined as a subscription server").
> thanks for any hints.
> Sylvia
>

Publisher Issues

Right i have two sql 2005 standard edition boxes within a Windows 2003 R2 active directory environment. All updates sp1 etc.

I have an mssql service account setup in AD which all the mssql services on both servers startup with.
I went to setup replication between the two servers for one db.
Firstly i setup distribution no problems no errors.

Then i went to setup the publication, all went well no errors in management studio gui but in the application event logs i got the following:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 14151
Date: 05/10/2006
Time: 12:37:43
User: DNETWORK\mssqlservice
Computer: SGC
Description:
Replication-Replication Transaction-Log Reader Subsystem: agent SGC-MerakDB-2 failed. Executed as user: dnetwork\sqlrep. A required privilege is not held by the client. The step failed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 47 37 00 00 12 00 00 00 G7......
0008: 04 00 00 00 53 00 47 00 ....S.G.
0010: 43 00 00 00 0d 00 00 00 C.......
0018: 64 00 69 00 73 00 74 00 d.i.s.t.
0020: 72 00 69 00 62 00 75 00 r.i.b.u.
0028: 74 00 69 00 6f 00 6e 00 t.i.o.n.
0030: 00 00

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 14151
Date: 05/10/2006
Time: 12:37:51
User: DNETWORK\mssqlservice
Computer: SGC
Description:
Replication-Replication Snapshot Subsystem: agent SGC-MerakDB-Merak Mail DB-2 failed. Executed as user: dnetwork\sqlrep. A required privilege is not held by the client. The step failed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 47 37 00 00 12 00 00 00 G7......
0008: 04 00 00 00 53 00 47 00 ....S.G.
0010: 43 00 00 00 0d 00 00 00 C.......
0018: 64 00 69 00 73 00 74 00 d.i.s.t.
0020: 72 00 69 00 62 00 75 00 r.i.b.u.
0028: 74 00 69 00 6f 00 6e 00 t.i.o.n.
0030: 00 00

I've tried all sorts to get this to work, the sqlrep user is db_owner for the distro db, ive tried the sqlrep user as domain admin to see if it was a system priv issue, no luck :(

Anyone have any ideas?

Is the account dnetwork\sqlrep the same as SQL Server service account? You can try to add dnetwork\sqlrep to windows user group SQLServer2005SQLAgentUser$<MachineName>$MSSQLSERVER, SQLServer2005MSSQLUser$<MachineName>$MSSQLSERVER, then restart SQL Server service, or reboot the machine to see if the problem goes away. Also can you let us know if you have changed SQL Server service account after installation? If so, it has to be done through SQL Server configuration manager.

Thanks,

Zhiqiang Feng

|||

I tried what you suggested, no luck,

theres a service account for mssql which i named mssqlservice :)
That was set during setup and never changed.

sqlrep was created for replication only, i had already added it to the groups you suggested with no luck. The same error etc.

So i uninstalled then reinstalled mssql from scratch no problems this time.

I believe it was down to a active directory issue when we had to run gpofix it may have altered something down the line. The issue was effecting both servers so i assume it was caused by the active directory issue.

Bit strange though but its sorted now, well so far so good :)