Clean up replication data

I have a develop machine for our web site. On this machine I have a copy of a database from the production site. The database on the production site is being replicated. When I copied the database to the develop machine and started testing some new developments I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'

This tells me that some replication data is also copied to the develop database.

Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)You could try to remove replication by going under Tools(in EM)-->Replication-->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools-->Replication.
Or you may try the script below:

USE [DBNAME]
GO

-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'

DECLARE @.TriggerName varchar(100), @.TriggerID INT, @.TableName varchar(100)

DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @.TriggerName)
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO

-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @.ConstName varchar(100), @.ConstID INT, @.TableName varchar(100)

SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'

DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @.TableName + ' DROP CONSTRAINT ' + @.ConstName)
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @.TableName varchar(100), @.TableID INT

SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050

DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.TableID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @.TableName)
FETCH NEXT FROM cur INTO @.TableName, @.TableID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @.ProcName varchar(100), @.ProcID INT

SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0

DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @.ProcName)
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @.ViewName varchar(100), @.ViewID INT

SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'

DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @.ViewName)
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'

DECLARE @.DefaultName varchar(100), @.IndexName varchar(100), @.TableName varchar(100), @.ColName varchar(50)

DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.DefaultName)
EXECUTE ('DROP INDEX ' + @.TableName + '.' + @.IndexName)
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
END
CLOSE cur
DEALLOCATE cur
GO|||I've been trying to delete the conflict tables left over after EXEC sp_removereplication

Your script did it!

Clean Install Windows Auth Error

Hi,

I cannot log in to SQL Server 2005 Dev Edition in my local machine using Windows Authentication. The server returned "Login failed..." when connecting with SQL Server Management Studio.

I have not change anything since installation of this server.

This problem happens in RTM and SP1 versions, both running on Windows Vista RTM.

Anyone having this kind of problem too? Any solution? I'm guessing it's Vista-related.

True, this is something of Vista. http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx

Sorry for not reading it first.

Checking to see if SQL Server on machine is up and running

Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon Whale
Hi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>

Checking to see if SQL Server on machine is up and running

Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>

Checking to see if SQL Server on machine is up and running

Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>

check sql server edition

is there a way to get the edition(e.g. sqlexpress,enterprise etc...) of an installed sql server instance in your local machine programatically?

Code Snippet

select serverproperty('Edition')

HTH!|||alright! thanks man! but is there some other way... without executing a query from the server?|||

I guess another way would be to query the registry for the information. Looking around there appears to be that information in the registry key for SQL2005:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\Edition

HTH!

|||

i've checked my registry... but it seems its not correct to read the registry when checking an installed sqlserver edition... it's not accurate..my machine have some previous sqlserver installations before and my registry looks like this..

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\Edition\Developer Edition

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Setup\Edition\DeveloperEdition

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.3\Setup\Edition\Express Edition

...

...

......

.........

............

...............

but there is only one developer edition instance installed in my machine...

|||

Hi,

I guess your registry showing is correct.

During SQL Server Setup, an instance ID is generated for each server component. The server components in this SQL Server release are the Database Engine, Analysis Services, and Reporting Services. The instance ID is in the format MSSQL.n, where n is the ordinal number of the component being installed. The instance ID is used in the file directory and the registry root.

The first instance ID generated is MSSQL.1; ID numbers are incremented for additional instances as MSSQL.2, MSSQL.3, and so on. If gaps occur in the ID sequence due to uninstalls, ID numbers are not generated to fill them. The most recently installed instance always has the highest instance ID number.

Server components are installed in directories with the format <instanceID>\<component name>. For example, a default or named instance with the Database Engine, Analysis Services, and Reporting Services would have the following default directories:

<Program Files>\Microsoft SQL Server\MSSQL.1\MSSQL\ for the Database Engine

<Program Files>\Microsoft SQL Server\MSSQL.2\OLAP\ for Analysis Services

<Program Files>\Microsoft SQL Server\MSSQL.3\RS\ for Reporting Services