Ater table transactional replication

I have SQL Server 2000 transactional replication and it
works fine.
I need to break the replication to apply the vendor script
to the database. The vendor will modify database
objects.
I delete the transaction replication subscription.
I tried to alter a table and received an error stating
that the table could not be altered because it was
replicated.
What is the easiest procedure so that can apply the vendor
script.
Thanks,
Mark
Mark,
it depends on your particular modification, but in most cases you could use
sp_repladdcolumn and sp_repldropcolumn. Using these procedures there's no
need to drop the subscription. For more radical changes, you could also drop
the article from the publication and add it back in.
HTH,
Paul Ibison
|||And for your specific one, run sp_removedbreplication. If for some reason
it still gives you that error, turn on allow updates, update the replinfo
column in sysobjects and set it to 0, turn off allow updates.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||This seems a little drastic just to modify the schema. I think Paul's
response is more appropriate.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

ASP/SQL Query Build - Myth Breaking

Hi Guys

I don't want to keep asking for your help all the time on each individual
query, so could you please help me to break the myths on the following:

1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.
ACCOUNTS includes all of the usual details for customers and suppliers, eg
the account code (as ACCOUNTID), the account name (as NAME), etc.
ACCOUNTBUDGET basically holds a transaction line for each month and it's
corresponding year to store the turnover for that month, eg one row contains
ACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.

Now a lot of the SQL 6.5 tables that I deal with are in this vein and the
usual query is that I want to list all of the ACCOUNTIDs and NAMEs that
exist in the ACCOUNTS table and then show for example what their TURNOVER is
for a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGET
table.

Now if I do a quick query using MS Query all I get are rows that have
related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I have
specified say a certain PERIOD and YEAR.

The main point of my current reporting problem is that I want to show all
the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
particular PERIOD and YEAR.

I'm positive that I have to create a 2 step query/join, but I don't know how
to do it. What is the method? People in this NG, can rattle one up in
seconds, but I just don't see the logic. Can you help me with this query
and let me know how you manage to fathom it.

2) Are there any good web sites that explain in kiddie form how to do this
sort of thing?

I really appreciate your help on this.

Regards

LaphanPut your Financial Calendar into a table and join that into your query.

CREATE TABLE Accounts (accountno INTEGER PRIMARY KEY, accountname
VARCHAR(30) NOT NULL UNIQUE)

CREATE TABLE FinancialCalendar (yearno INTEGER CHECK (yearno BETWEEN 2000
AND 2100), periodno INTEGER CHECK (periodno BETWEEN 1 AND 12), PRIMARY KEY
(yearno,periodno))

CREATE TABLE Budgets (accountno INTEGER REFERENCES Accounts (accountno),
yearno INTEGER, periodno INTEGER, amount NUMERIC(10,2) NOT NULL, FOREIGN KEY
(yearno,periodno) REFERENCES FinancialCalendar (yearno,periodno))

SELECT A.accountno, A.accountname, C.yearno, C.periodno,
COALESCE(B.amount,0) AS amount
FROM FinancialCalendar AS C
CROSS JOIN Accounts AS A
LEFT JOIN Budgets AS B
ON A.accountno = B.accountno
AND B.yearno = C.yearno
AND B.periodno = C.periodno
WHERE C.yearno BETWEEN 2001 AND 2004

I recommend Celko's SQL Puzzles and Answers if you want to expand your
repertoire of query techniques:
http://tinyurl.com/353dw

--
David Portas
SQL Server MVP
--