HELP! Non-Replicated DB thinks its a Publisher

I hosed myself, please help me somebody.

We have a development server (SQL7) where the database is a restored copy of
production (where we use replication, but not in the development
environment).

While we rebuild the server recently (so we could move off of NT onto 2000),
everything looked to be working fine.

But I recently, messed up and did a massive update filling the transaction
log of the development server. Now I can't update data in development.

I tried to do my usual:

BACKUP LOG LAMS WITH Truncate_only

only to get a message:

The log was not truncated because records at the beginning of the log are
pending replication. Ensure the Log Reader Agent is running or use
sp_repldone to mark transactions as distributed.

Did a little research into BOL and tried to call the following two procs:

exec sp_repldone
@.xactid = null ,
@.xact_seqno = null ,
@.reset = 1

exec sp_repltrans

Both of which gave me an error of :
The database is not published.

Which it isn't as we don't run replication in the development environment.

I stopped and restarted the SQL Service (as that has cleared issues in the
past), but didn't reboot the server.

How can I make the database not think It's replicated (or that its caught
up?).

I was thinking, would I be even be able to do another restore of a more
recent copy of Production?

Sincerely ...
Depressed in DelawareNever Mind, I restored development from a more recent copy of production.

Is there a command to fix a SQL 7 DB when it gets confused about
replication?

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:DD3Bd.1194718$Gx4.69619@.bgtnsc04-news.ops.worldnet.att.net...
>I hosed myself, please help me somebody.
> We have a development server (SQL7) where the database is a restored copy
> of production (where we use replication, but not in the development
> environment).
> While we rebuild the server recently (so we could move off of NT onto
> 2000), everything looked to be working fine.
> But I recently, messed up and did a massive update filling the transaction
> log of the development server. Now I can't update data in development.
> I tried to do my usual:
> BACKUP LOG LAMS WITH Truncate_only
> only to get a message:
> The log was not truncated because records at the beginning of the log are
> pending replication. Ensure the Log Reader Agent is running or use
> sp_repldone to mark transactions as distributed.
> Did a little research into BOL and tried to call the following two procs:
> exec sp_repldone
> @.xactid = null ,
> @.xact_seqno = null ,
> @.reset = 1
> exec sp_repltrans
> Both of which gave me an error of :
> The database is not published.
> Which it isn't as we don't run replication in the development environment.
> I stopped and restarted the SQL Service (as that has cleared issues in the
> past), but didn't reboot the server.
> How can I make the database not think It's replicated (or that its caught
> up?).
> I was thinking, would I be even be able to do another restore of a more
> recent copy of Production?
> Sincerely ...
> Depressed in Delaware

Help! MDF and LDF is there but database is gone!

I have been using SQL Server 2000 for a while now but am not an expert on it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
--
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)Perhaps you detached the database. Check out sp_attach_db in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
I have been using SQL Server 2000 for a while now but am not an expert on
it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with
no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
--
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)|||Oops! I accidentally posted same question 3x! Message said question did not
post previously. I deleted the other two on my profile just now, sorry.
Anyway, Tom I did not detach but I think the Copy Database Wizard does that
as part of the process, right? Sometimes our network hiccups and programs
could not access the sql server... would intermittent network disconnects
cause this too? I will look up in BOL and get back. Thanks.
"Tom Moreau" wrote:
> Perhaps you detached the database. Check out sp_attach_db in the BOL.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> I have been using SQL Server 2000 for a while now but am not an expert on
> it.
> I used the Database Copy Wizard to "copy" about 5 databases to a new server
> --at the same time. I have copied several other databases previously with
> no
> problems using this. This time, however, the databases were copied to the
> new server but it disappeared on the "source" server. I tried copying them
> back into the source server but I get a name conflict error which, from
> research, meant the mdf and ldf files of the disappearing db's are still
> there! Can you please tell me how I can restore these databases? Thank you
> very much.
> --
> _________________
> Freedom is nothing else but a chance to be better. (Albert Camus)
>|||sp_attach_db worked! I am concluding that somewhere along the "Copy Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you :)
"betterbest" wrote:
> Oops! I accidentally posted same question 3x! Message said question did not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:
> > Perhaps you detached the database. Check out sp_attach_db in the BOL.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> > news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> > I have been using SQL Server 2000 for a while now but am not an expert on
> > it.
> > I used the Database Copy Wizard to "copy" about 5 databases to a new server
> > --at the same time. I have copied several other databases previously with
> > no
> > problems using this. This time, however, the databases were copied to the
> > new server but it disappeared on the "source" server. I tried copying them
> > back into the source server but I get a name conflict error which, from
> > research, meant the mdf and ldf files of the disappearing db's are still
> > there! Can you please tell me how I can restore these databases? Thank you
> > very much.
> > --
> > _________________
> > Freedom is nothing else but a chance to be better. (Albert Camus)
> >
> >|||It sounds odd that the Copy Database Wizard would do such a thing. You may
want to use the profiler to verify that it is doing that. It doesn't seem
like it should.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:3BFB67FB-21B1-4935-AA30-7BC4FADBA343@.microsoft.com...
sp_attach_db worked! I am concluding that somewhere along the "Copy
Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you :)
"betterbest" wrote:
> Oops! I accidentally posted same question 3x! Message said question did
> not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does
> that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:
> > Perhaps you detached the database. Check out sp_attach_db in the BOL.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> > news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> > I have been using SQL Server 2000 for a while now but am not an expert
> > on
> > it.
> > I used the Database Copy Wizard to "copy" about 5 databases to a new
> > server
> > --at the same time. I have copied several other databases previously
> > with
> > no
> > problems using this. This time, however, the databases were copied to
> > the
> > new server but it disappeared on the "source" server. I tried copying
> > them
> > back into the source server but I get a name conflict error which, from
> > research, meant the mdf and ldf files of the disappearing db's are still
> > there! Can you please tell me how I can restore these databases? Thank
> > you
> > very much.
> > --
> > _________________
> > Freedom is nothing else but a chance to be better. (Albert Camus)
> >
> >

Help! MDF and LDF is there but database is gone!

I have been using SQL Server 2000 for a while now but am not an expert on it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)
Perhaps you detached the database. Check out sp_attach_db in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
I have been using SQL Server 2000 for a while now but am not an expert on
it.
I used the Database Copy Wizard to "copy" about 5 databases to a new server
--at the same time. I have copied several other databases previously with
no
problems using this. This time, however, the databases were copied to the
new server but it disappeared on the "source" server. I tried copying them
back into the source server but I get a name conflict error which, from
research, meant the mdf and ldf files of the disappearing db's are still
there! Can you please tell me how I can restore these databases? Thank you
very much.
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)
|||Oops! I accidentally posted same question 3x! Message said question did not
post previously. I deleted the other two on my profile just now, sorry.
Anyway, Tom I did not detach but I think the Copy Database Wizard does that
as part of the process, right? Sometimes our network hiccups and programs
could not access the sql server... would intermittent network disconnects
cause this too? I will look up in BOL and get back. Thanks.
"Tom Moreau" wrote:

> Perhaps you detached the database. Check out sp_attach_db in the BOL.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "betterbest" <betterbest@.discussions.microsoft.com> wrote in message
> news:A2D36B41-619D-4032-BA7C-4E6141BDC422@.microsoft.com...
> I have been using SQL Server 2000 for a while now but am not an expert on
> it.
> I used the Database Copy Wizard to "copy" about 5 databases to a new server
> --at the same time. I have copied several other databases previously with
> no
> problems using this. This time, however, the databases were copied to the
> new server but it disappeared on the "source" server. I tried copying them
> back into the source server but I get a name conflict error which, from
> research, meant the mdf and ldf files of the disappearing db's are still
> there! Can you please tell me how I can restore these databases? Thank you
> very much.
> --
> _________________
> Freedom is nothing else but a chance to be better. (Albert Camus)
>
|||sp_attach_db worked! I am concluding that somewhere along the "Copy Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you
"betterbest" wrote:
[vbcol=seagreen]
> Oops! I accidentally posted same question 3x! Message said question did not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:
|||It sounds odd that the Copy Database Wizard would do such a thing. You may
want to use the profiler to verify that it is doing that. It doesn't seem
like it should.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"betterbest" <betterbest@.discussions.microsoft.com> wrote in message
news:3BFB67FB-21B1-4935-AA30-7BC4FADBA343@.microsoft.com...
sp_attach_db worked! I am concluding that somewhere along the "Copy
Database
Wizard" process the database was not re-attached for several possible
reasons. Thank you so much. I'll let Santa do his part on you
"betterbest" wrote:
[vbcol=seagreen]
> Oops! I accidentally posted same question 3x! Message said question did
> not
> post previously. I deleted the other two on my profile just now, sorry.
> Anyway, Tom I did not detach but I think the Copy Database Wizard does
> that
> as part of the process, right? Sometimes our network hiccups and programs
> could not access the sql server... would intermittent network disconnects
> cause this too? I will look up in BOL and get back. Thanks.
> "Tom Moreau" wrote:

Help! - BCP / Bulk Copy

I need top update several tables with new values from an external text file. That would notrmally be a no brainer, (even for me) :) But, what is important this time around, is I can NOT fire the triggers, or every user would get notified of a 150,000 updates.

The text file contains 4 fields
KEYNUM|UpdateField1|UpdateField2|Class

Class will be either 1 or 2, if class = 1 then table 1 contains the record, if class = 2 then table 2 contains the record...

Any help, as always is greatly appreciatedHi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?|||Originally posted by bmalar
Hi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?

That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)|||Originally posted by GregCrossan
That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)

Sorry I didn't read your post correctly.. I thought you were inserting new records.

Help!

Hello,
I am trying to copy user logins from one database to another on the same
instance of SQL.
I used DTS to export the data to the new database, when I should have used
the backup option.
I'm in a bet of trouble. Now the users can not access the copied database.
I followed the suggestion below:
But I get the error when I run the script. Server: Msg 195, Level 15, State
10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : April 12, 2006
Revision : 5.0
Any help would appreciated. My neck is on the line
TerrySearch the archives for the name of the procedures. The KB by mistake includ
es code that only run on
2005, but there has been several postings with the original proc, that runs
on 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terry" <tgwillett@.cox.net> wrote in message news:pgc2g.56484$gE.47798@.dukeread06...eagreen">
> Hello,
> I am trying to copy user logins from one database to another on the same
> instance of SQL.
> I used DTS to export the data to the new database, when I should have used
> the backup option.
> I'm in a bet of trouble. Now the users can not access the copied database.
> I followed the suggestion below:
> But I get the error when I run the script. Server: Msg 195, Level 15, Stat
e
> 10, Procedure sp_help_revlogin, Line 52
> 'LOGINPROPERTY' is not a recognized function name.
> How to transfer logins and passwords between instances of SQL Server
> View products that this article applies to.
> Article ID : 246133
> Last Review : April 12, 2006
> Revision : 5.0
> Any help would appreciated. My neck is on the line
> Terry
>|||Thank You
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O7%23iSQeZGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Search the archives for the name of the procedures. The KB by mistake
> includes code that only run on 2005, but there has been several postings
> with the original proc, that runs on 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:pgc2g.56484$gE.47798@.dukeread06...
>

Help!

Hello,
I am trying to copy user logins from one database to another on the same
instance of SQL.
I used DTS to export the data to the new database, when I should have used
the backup option.
I'm in a bet of trouble. Now the users can not access the copied database.
I followed the suggestion below:
But I get the error when I run the script. Server: Msg 195, Level 15, State
10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : April 12, 2006
Revision : 5.0
Any help would appreciated. My neck is on the line :)
TerrySearch the archives for the name of the procedures. The KB by mistake includes code that only run on
2005, but there has been several postings with the original proc, that runs on 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terry" <tgwillett@.cox.net> wrote in message news:pgc2g.56484$gE.47798@.dukeread06...
> Hello,
> I am trying to copy user logins from one database to another on the same
> instance of SQL.
> I used DTS to export the data to the new database, when I should have used
> the backup option.
> I'm in a bet of trouble. Now the users can not access the copied database.
> I followed the suggestion below:
> But I get the error when I run the script. Server: Msg 195, Level 15, State
> 10, Procedure sp_help_revlogin, Line 52
> 'LOGINPROPERTY' is not a recognized function name.
> How to transfer logins and passwords between instances of SQL Server
> View products that this article applies to.
> Article ID : 246133
> Last Review : April 12, 2006
> Revision : 5.0
> Any help would appreciated. My neck is on the line :)
> Terry
>|||Thank You
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O7%23iSQeZGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Search the archives for the name of the procedures. The KB by mistake
> includes code that only run on 2005, but there has been several postings
> with the original proc, that runs on 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:pgc2g.56484$gE.47798@.dukeread06...
>> Hello,
>> I am trying to copy user logins from one database to another on the same
>> instance of SQL.
>> I used DTS to export the data to the new database, when I should have
>> used
>> the backup option.
>> I'm in a bet of trouble. Now the users can not access the copied
>> database.
>> I followed the suggestion below:
>> But I get the error when I run the script. Server: Msg 195, Level 15,
>> State
>> 10, Procedure sp_help_revlogin, Line 52
>> 'LOGINPROPERTY' is not a recognized function name.
>> How to transfer logins and passwords between instances of SQL Server
>> View products that this article applies to.
>> Article ID : 246133
>> Last Review : April 12, 2006
>> Revision : 5.0
>> Any help would appreciated. My neck is on the line :)
>> Terry
>>
>

Help with update trigger

Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO

How can I turn this into a trigger for automatic updates?

Thanks for any help.rdraider (rdraider@.sbcglobal.net) writes:
> I know squat about triggers so was hoping somebody could point me in the
> right direction. I wanted to copy an email address field from a
> salesman table to a note field in a customer table. Seems easy enough
> for a one time update. But I would like to add a trigger to auto-update
> the customer table anytime an email address changes in the saleman table
> or a new salesman record is added.
> Here's my update script (this copies the salesman email address to each of
> his customers)
> UPDATE CUSTOMERS
> SET NOTE_5 = SALESMAN.EMAIL_ADDR
> FROM CUSTOMERS INNER JOIN
> SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
>
> How can I turn this into a trigger for automatic updates?

CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.

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