putting names of objects to control-flow loop creating objects

please help newbie

I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?

belss you for helpfireball wrote:

Quote:

Originally Posted by

please help newbie
>
I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?
>
>
>
belss you for help

Firstly the obvious question: Why? Where are the names from these
objects coming from? If you can write a query to extract the names then
you could just use Query Analyzer or Management Studio to paste those
names into an editable script and then run the script directly. That
way there is no need for a loop.

If you must do it programmatically then you'll have to do something
with dynamic SQL. See:

http://sommarskog.se/dynamic_sql.html
Personally I'd say that if you have so many schemas that you need a
loop to create them then you definitely have too many schemas... or you
are using them in a highly unconventional manner.

--
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/...US,SQL.90).aspx
--|||Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal w

Quote:

Originally Posted by

Where are the names from these objects coming from

from text file I got

Quote:

Originally Posted by

if you have so many schemas that you need a loop

first idea is to build scripts in engineer maneer (reusing code blocks - not
to copy them - so managing any changes is better)

second reason - I got my analysis data model in RaRose, which doesn't really
support automatization of creating data model. I use creating
quasi-sqlserver scripts, so I have object names and so. It's not really
large amount of objects (a few schemas, about 100 tables) - but it does
change any time.
Sorry if it sounds a bit werid :-) I'm a newbie

third idea was to create that way descriptions to my obiects (tables,
attributes) in a loop (having descriptions in xls file, for example)

All hints will be appeciated.

ps:

Quote:

Originally Posted by

then you definitely have too many schemas...

you are definitely right.

Quote:

Originally Posted by

are using them in a highly unconventional manner.

like, let's say - to make some perfiormance tests/statistics? (I really
don't do so :-))|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal

Quote:

Originally Posted by

>Where are the names from these objects coming from

>
from text file I got

But text file is not SQL, but you have to transform it to SQL?

Doing this from SQL is not really fun. If you are on SQL 2005, you
could do this through the CLR, but you would still go through hoops.
Do this from a client application: Perl, VBscript or whatever your
favourite may be.

--
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|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci

Quote:

Originally Posted by

But text file is not SQL

well, the point is to put it into script.sql any way which I will be able to
fetch it into my loop|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci
>

Quote:

Originally Posted by

>But text file is not SQL

>
well, the point is to put it into script.sql any way which I will be
able to fetch it into my loop

Without having seen your file, it's difficult to tell, but it does not sound
as if trying to read it from SQL is a very good idea. You probably much
better off doing this in a client language.

--
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

Putting @Parameters in Stored Procedures

I am creating a stored Procedure and I am getting an error which relates toDATENAME.

SELECTCOUNT(*)AScalls,DATENAME(@.varDate, CALLSTARTTIME)AS 'Total Calls'

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS='1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <=@.EndDate

sql doesn't like: DATENAME( @.varDate, CallStartTime)
sql works fine if I change @.varDate into 'yy', 'mm', 'dd', or 'wk'
Since I do not want to make 5 unique Stored Proc just because of @.varDate....

Is there any way to work around this problem?

Well, you could do something like this:

DECLARE @.varDate VARCHAR(10)
SELECT @.varDate = 'mm'
SELECT
OrderDate,
CASE @.varDate
WHEN 'mm' THEN DATENAME(mm,OrderDate)
WHEN 'yy' THEN DATENAME(yy,OrderDate)
WHEN 'dw' THEN DATENAME(dw,OrderDate)
END -- added this as an EDIT to my post
FROM Orders

|||I tried,

ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.VarDate as char,
@.StartDate as char,
@.EndDate as char
)
AS
SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME) AS 'Year'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)
SQL wouldn't allow me to save. What did I do wrong?
|||Somehow when I coped and pasted my test code I left out an END, sorry about that!
Try it like this:

SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME)ENDAS 'Year'

FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)

Also, in the future, you need to be more specific than "SQL wouldn'tallow me to save". Exact error messages go a long way to gettingexact help.
|||thanks for the advice.
got another error:
ADO error: Column 'Call_LOG_MASTER.CALLSTARTTIME' is invalid in theselect list because it is not contained in either an aggregate functionor the GROUP BY clause.

SELECT
CASE @.varDate
when 'yy' Then DATEPART(yy, CALLSTARTTIME) END as 'Year',
COUNT(DATEPART(yy, CALLSTARTTIME)) AS 'Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)|||

I think I found the problem,

ALTER PROCEDUREdbo.GET_TOTALCALLS

(

@.varDateas varchar(255),

@.StartDateas datetime,

@.EndDateas datetime

)

AS

SELECT

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

END,

Count(*)as'Total Calls'

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <= @.EndDate)

GROUP BY

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

End

ORDER BY

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

End

RETURN

However, the column can't be named this way. My new question is how do I renamed the datepart column by case?
Namely,
when @.varDate = 'Year', the column name should be 'Year',
when @.varDate = 'Month', the column name should be as 'Month', etc..

|||still stuck on this problem, could use some help if possible =)|||If that is your requirement, then I would scrap what you have and just do something like this:
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS
IF @.varDate = 'Year'
BEGIN
SELECT
DATEPART(yy, CALLSTARTTIME) AS Year,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY 1
END
IF @.varDate = 'Month'
BEGIN
SELECT
DATEPART(mm, CALLSTARTTIME) AS Month,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(mm, CALLSTARTTIME)
ORDER BY 1
END
-- etc
RETURN

|||yes, thank you. That will work fine

Push subscription failing

SQL Server Enterprise Manager encountered errors creating push subscriptions
for the following Subscribers:
JANUS: Error 208: Invalid object name 'sysextendedarticlesview'.
The error above shows up each time that I attempt to push the subscription.
THe view sysextendedarticlesview exist in the publisher database. DOes
anybody know how to fix this thing? THanks
issue this on the subscriber in the subscription database.
create view dbo.sysextendedarticlesview
as
select * from sysarticles
union all
select artid, NULL, creation_script, NULL, description,
dest_object, NULL, NULL, NULL, name, objid, pubid,
pre_creation_cmd, status, NULL, type, NULL,
schema_option, dest_owner from sysschemaarticles
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:OKRoJYKsEHA.2788@.TK2MSFTNGP09.phx.gbl...
> SQL Server Enterprise Manager encountered errors creating push
subscriptions
> for the following Subscribers:
> JANUS: Error 208: Invalid object name 'sysextendedarticlesview'.
> The error above shows up each time that I attempt to push the
subscription.
> THe view sysextendedarticlesview exist in the publisher database. DOes
> anybody know how to fix this thing? THanks
>
sql

Pull subscription initialisation

Hi there all

I have a problem creating a pull merge subscription on a server that's outside of our firewall. All standard ports are blocked so MS UNC connections are not possible which SQL 2000 uses by default when creating a subscription. Equally FTP is out = insecure.

I found a procedure to that said create the snapshot of the publication, create a backup of the published database, restore that backup to the remote server as a subscription db and then sync using merge. Didn't work, failed (several times).

Can anyone enlighten me as to what I'm doing wrong or indeed if I'm doing anything correctly!

I can create a remote desktop (Term Server) connection to the remote and another back to the publisher (from the subscriber's desktop) and both connections are using SSH tunnels. SQL Server uses a non standard port to communicate over the firewall. The remote server sits behind another firewall/router with port redirection to it's private address. Each server has the other registered and there are no comms problems and indeed there are other replicated dbs between them.

Both servers are win 2003 and the remote (subscriber) is R2 version, both with SQL 2000 Server Std patched up to SP3. (if this has any bearing on the solution which I presume there is)

Many thanks for listening and I hope a few of you can answer as well

CraigI have a problem creating a pull merge subscription on a server that's outside of our firewall. All standard ports are blocked
Default SQL Server port is 1433. You need to ask your System Administrator to set appropriate access for MS SQL Server at both end.

Check your server is enabled for subscription on publisher / distributor server.

These are basic check system, could be other reasons but you need to start from bottom.|||Hi there

Yep, I'm the SysAdmin, that's why I'm fielding the question. We purposefully changed the default port as a security measure. Duplication and general SQL functions work perfectly on the chosen port. Ports are blocked and changed because communication is over the internet. The problem is that when you launch an initialisation of a subscriber SQL uses the windows default port 445 for mapped drives, etc to copy down snapshot.pre and the other scripts. This port is is blocked, again this is a security measure.

What I need to do is find a way to apply the snapshot (after copying it from the publisher) to the Subscriber without using a network or internetwork connection and then the subscriber can sync with the Publisher

I hope this clarifies the problem|||The problem is that when you launch an initialization of a subscriber SQL uses the windows default port 445 for mapped drives, etc to copy down snapshot.pre and the other scripts. This port is is blocked, again this is a security measure.

As far as I knew, regarding firewall you can set particular port access for particular application in your firewall. Even you can configure particular IP access for port, but this all depend on your firewall configuration options.

Without providing adequate access privilege, you could not expect any transaction on network.

What I need to do is find a way to apply the snapshot (after copying it from the publisher) to the Subscriber without using a network or internetwork connection and then the subscriber can sync with the Publisher

Get the copy of snapshot, dump it & set snapshot file location from subscription properties.

But I feel it's not the correct way, manual setups could be disturb frequently.|||Hi Rajesh

I've managed to do it and syncronisation is working. The process as I managed to do it was

Create a subscription in the publisher
create a backup of the subscription
copy this backup to the subscriber
restore the subscription over a previously created database making the DB larger enough that SQL doesn't have to grow the DB during initialisation
copy the snapshot to a folder on the subscriber ensuring that the complete path below \\%publisher%\%replfolder%\ was as it exists on the publisher (because this appears to be encoded into the snapshot)
Create the subscription on the subscriber
Change the snapshot location on the subscriber to point to the local snapshot
Launch sincronisation
Change the subscriber snapshot location back to the publisher default (v. important)

and it worked

to test we added some data to the publication and sincronised from the subscriber because it's a pull sub.

and then cleaned up ready to populate the database with it's full content

We've create the subscription as a data-empty database because there's some 11 Gb of data to prepopulate the db for use and we'll sincronise later using a modified agent to cope with the longer sincronisation times (and not get timeout errors)

The normally impossible was acheived

Pull subscription creation error

Upon creating a new Pull Subscription on a Subscriber, I kept on getting
the error message "SQL Server enterprise Manager could not create a pull
subscription to publication 'XXXX' Error 14234: The specified '@.server' is
invalid(valid values are returned by sp_helpserver). Where did I go wrong
and what is the problem? Thanks.
Georgi,
you could check that the server names haven't been altered:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you. I checked but both publisher and subscriber have server names
other than NULL ...
Let me explain more my situation.
I want to make replication of database over internet, i want to use VPN. I
already setuped the VPN service so it gives IP-s that are not within the
range of the local network and are fixed for the server and for the
connections.
I was trying to make push subscription because it seemed to me that it's
easier to implement. So the subscription was successefuly added , the
snapshot agent generated snapshot but then this snapshot was not
automatically applied on Subscriber and the distributor agent cried out that
"objects are missing at subscriber". I don't know how to apply the
subscription manually, i tried to change it's path via Alternate
subscription location but it didn't helped. So i abandoned the push
subscription and now i'm trying to make pull subscription. I don't know if
i'm not missing someting of the security privilegies of the logins used, or
maybe must declare the servers in Remote Servers?
I'm quite confused with all this Replication stuff, i'm still new to it.
I've looked to the Replication section in SQL Server online but i can't find
solutions of this delicate problems (maybe it's written somewhere i'm still
reading ). Can you give me link(s) to some other comprehensive sources of
information, maybe good HOWTOS?
Thank you in advance
Georgi Peshterski
"Paul Ibison" <Paul.Ibison@.pygmalion.com> wrote in message
news:eRZf%23mUjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Georgi,
> you could check that the server names haven't been altered:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Published tables locked

Hi,
I am using SQL 2000 standard edition. When i do a
snapshot replication, all the published tables are locked
while creating the snapshot. Result is i cannot do a
snapshot during work hours.
My understanding was that there is an improvement in SQL
Server 2000 where by tables do not get locked while
creating the snapshot.
Can someone throw some light on this.
thanks
venk
Nonblocking snapshots are only available when you use transactional. Why?
I really don't know, but that is the restriction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Publish Subscription replication - Side effects?

Hi Guys,
I've been given the task of creating a disaster recovery "replica" of a
production SQL server and keeping it current.
(SQL 2000)
At first we thought of using scripts to do a backup/restore method.
But a couple of the databases are upwards of a hundred gigabytes...
A bit hard on drive space.
I thought a better method would be to replicate the databases using the
Publish and "push subscriptions".
I'm not very knowledgeable about SQL management, However with the help of
Google and a little time,
I mamnaged to make it work experimentally with the Northwind database.
But that's tiny compared to the live DBs
My question is:
Am I likely to see any 'unforseen' side effects of replicating by this
method?
Thanks,
Jim
To keep maximum performance on your main SQL Server I would consider
"pulling" the transactionally replicated data.
This article from Microsoft has alot of great tips on how to maximize
replication performance.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
If you have money for a nice SAN unit you can check out Split Mirroring.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspx

/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:epEJFv0QIHA.2376@.TK2MSFTNGP02.phx.gbl...
> Hi Guys,
> I've been given the task of creating a disaster recovery "replica" of a
> production SQL server and keeping it current.
> (SQL 2000)
> At first we thought of using scripts to do a backup/restore method.
> But a couple of the databases are upwards of a hundred gigabytes...
> A bit hard on drive space.
> I thought a better method would be to replicate the databases using the
> Publish and "push subscriptions".
> I'm not very knowledgeable about SQL management, However with the help of
> Google and a little time,
> I mamnaged to make it work experimentally with the Northwind database.
> But that's tiny compared to the live DBs
> My question is:
> Am I likely to see any 'unforseen' side effects of replicating by this
> method?
> Thanks,
> Jim
>
|||Thanks Warren!
I'll give it a go.
Unfortunately the client is 'cheap' and wants to spend as little as
possible.
Getting to be par for the course these days...
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:elyRBZ5QIHA.4180@.TK2MSFTNGP06.phx.gbl...
> To keep maximum performance on your main SQL Server I would consider
> "pulling" the transactionally replicated data.
> This article from Microsoft has alot of great tips on how to maximize
> replication performance.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
> If you have money for a nice SAN unit you can check out Split Mirroring.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspx
>
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:epEJFv0QIHA.2376@.TK2MSFTNGP02.phx.gbl...
>

Publications

Can someone tell me about creating a publication?From BOL:

Publication
A publication is a collection of one or more articles from one database. This grouping of multiple articles makes it easier to specify a logically related set of data and database objects that you want to replicate together.

Article
An article is a table of data, a partition of data, or a database object that is specified for replication. An article can be an entire table, certain columns (using a vertical filter), certain rows (using a horizontal filter), a stored procedure or view definition, the execution of a stored procedure, a view, an indexed view, or a user-defined function.

HTH|||Thanks. I can't see too much in my books.|||Use BOL (Books online) as much as you can, it really helps on definiton type things (and more) if you don't have it you can download it here:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp?

Publication w/o logreader agent. Is it possible?

Hi,
Is it possible to create a publication without creating a logreader agent at
the same time? I'm on MS SQL Server 2000 SP3a.
-- Many thanks, Oskar
Oskar - if you are talking about transactional replication, this is an
integral part of the functioning. I'm interested in why would you like to
avoid the logreader agent?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, transactional is what I am talking about. I am interested in this
because I want to have development databases published but I do not want to
have any real replication going on there. This is needed because we must have
identical "change management" in development and production environments.
Looks like I have partly solved this problem by making each development
server a publisher and a distributor, and regenerating the publication from a
script. I even could do without the snapshot agent, but getting rid of the
logreader seems to be tricky, if not impossible.
-- Thanks, Oskar
"Paul Ibison" wrote:

> Oskar - if you are talking about transactional replication, this is an
> integral part of the functioning. I'm interested in why would you like to
> avoid the logreader agent?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Oskar,
don't know if this helps, but until the snapshot has run, the log reader
isn't moving any commands to the distribution database. To prevent it
parsing the transaction log you could simply stop it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That's exactly what I did. I just don't like to stop it manually everytime
the development database gets refreshed - its job name and id changes at
every such refreshing.
-- Oskar
"Paul Ibison" wrote:

> Oskar,
> don't know if this helps, but until the snapshot has run, the log reader
> isn't moving any commands to the distribution database. To prevent it
> parsing the transaction log you could simply stop it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||I'd add a piece of code at the end of the script which stops and disables
the job - that way you don't need to worry about it afterwards.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Publication script error

Hello,
I am creating Publication through sql script. I have created Publication on
my machine using wizard and after that I created the script for the
publication. When I am running this script on another sql server it is giving
me an error saying Distributor not configured properly. What I need to do to
remove this error? Can this problem be solved by running some sql script?
Becoz I am creating all this through Installshield. So I need the script of
this. Please Help...
Sounds like replication is not installed on this server. Go to tools,
replication, enable replication and follow the prompts accepting the
defaults.
"Subin" <Subin@.discussions.microsoft.com> wrote in message
news:567DC900-D413-4769-916C-E24FF90B9A0E@.microsoft.com...
> Hello,
> I am creating Publication through sql script. I have created Publication
> on
> my machine using wizard and after that I created the script for the
> publication. When I am running this script on another sql server it is
> giving
> me an error saying Distributor not configured properly. What I need to do
> to
> remove this error? Can this problem be solved by running some sql script?
> Becoz I am creating all this through Installshield. So I need the script
> of
> this. Please Help...

Publication creating bcp files on server

using publication from server one to server two. On server one, I have several directories with bcp files. Can the older files and directories be deleted?

Hi Paul

Are these bcp files being created by merge/tran replication? If so here is a detailed reply from Raymond Mak from a previous post specifically for tran replication. Post back here if you need any further help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=265814&SiteID=1

Cleanup of snapshot files for transactional\snapshot replication is actually handled automatically by the 'distribution cleanup agent' which is basically a SQLServerAgent job that runs the sp_MSdistribution_cleanup stored procedure periodically at the distribution database. Thus, you may want to check the job history of the cleanup agent and see if it had any troubles removing snapshot folders or if it is running at all. And if you find that the distribution cleanup agent had trouble cleaning up the snapshot folders, it is more than likely that either the SQL Server service account or the xp_cmdshell proxy account lacks sufficient privilege to remove the snapshot folders. Now, I suspect that what I have said so far has very little to do with your particular scenario but instead I am guessing that one of your publications has the immediate_sync (select immediate_sync from syspublications at publisher database to confirm) set to 1. Normally, if none of your publications has the immediate_sync property set to 1, the distribution cleanup agent will remove the snapshot folder as soon as the snapshot data has been distributed to all applicable subscribers. However, if any of your publications has the immediate_sync property set to 1, the snapshot files for that publication may stay around until the max distribution retention (default 72 hours) is past. This is because immediate_sync = 1 means that snapshot data should be made available for initializing any new subscriptions added after the snapshot has been generated. The SQL2005 create publication wizard will enable the immediate_sync publication if you check the "Generate snapshot immediately ... " check-box and that may explain how some of your publications have the immediate_sync property set to 1 in the first place.

Hope that helps.

Nabila Lacey

Public Role Permissions

I have a system that before I arrived, they granted all permissions to all
objects to the public role instead of creating there own. While in the
process of trying to fix this we discovered that all users through the publi
c
role have the ability to create objects.
Ultimately what I need to know is how do I revoke or remove permissions to
the public role?Hi,
You will need to create new roles and assign the required permissions the
role. After that assign the approprate roles to users.
Once you are done with that then you could use ROVOKE command remove the
permissions from Public role. The only way to rovoke
Public role rights is to Rove the permissions.
Thanks
Hari
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:43353E72-E45B-4048-BA73-E84000DFEA4D@.microsoft.com...
>I have a system that before I arrived, they granted all permissions to all
> objects to the public role instead of creating there own. While in the
> process of trying to fix this we discovered that all users through the
> public
> role have the ability to create objects.
> Ultimately what I need to know is how do I revoke or remove permissions to
> the public role?