how to configure a server as distributor

When configuring my SQL Server as a distributor I get the following
error:
Error 14113:Could not execute 'copy "
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\DISTMDL.MDF"
"C:\Program Files\Microsoft SQL Server\MSSQL\DATA\distribution.MDF"'.
Check
'instdist.out' in the install directory
I could not find a file named instdist.out in the install directory.
Please Help!!
Thanks.
The setup will require you to be a system administrator - are you logged on
as one?
Also has the distribution database been created?
Also, check that you have sufficient room for a copy of the distribution
database.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I've logged in as system administrator.
distmodel database is also there and have sufficient space on my disk.
but still the problem has not been solved.
please show me a way.
thanx
jeff
Paul Ibison wrote:
> The setup will require you to be a system administrator - are you logged on
> as one?
> Also has the distribution database been created?
> Also, check that you have sufficient room for a copy of the distribution
> database.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I'd set it up manually and see where the issue is:
sp_adddistributor
sp_adddistributiondb
sp_adddistpublisher
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||First disable distribution from the Replication menu
Then try below commands in query analyser
sp_helpserver ( to see server name)
sp_dropserver 'SERVERNAME','DROPLOGINS'
sp_addserver 'SERVERNAME','LOCAL'
Then try creating Distribution again
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u7Y6pBCBHHA.3540@.TK2MSFTNGP03.phx.gbl...
> I'd set it up manually and see where the issue is:
> sp_adddistributor
> sp_adddistributiondb
> sp_adddistpublisher
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Take the database distmodel to Offline
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

How to compare structure of two database ?

I have one database

and I create new database by copy everything from first db

I then alter some column on new database

and now I would like to compare two database

How can I do it ?

There is the manul way which I am sure you know of because like most of use we don't keep track of the changes that we make until time comes to roll the thing out.

There is a great good called RedGate SQL Compare (http://red-gate.com/products/SQL_Compare/index.htm) the tools are sort of expensive but well worth the money. Also if you are interested Microsoft is developing a product call Visual Studio for Database Developers that can be found on the MSDN site. Which esentially does the same thing and can be found on MSDN subscription download too.

|||I would write a script looking at syscolumns and sysobjects from both DB's and look where they do not match, this should get you changes to the DB.|||That is basically what Red Hat does, but it takes it a step furthor and generate the change script for you. I personally think that is worth 250.00|||

FYI, here is a simple script I'm using:

/*--Compare the differences in table schemas between 2 databases

--*/
/*--sample to call this stored procedure
exec p_comparestructure 'pubs','northwind'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO

create proc p_comparestructure
@.dbname1 varchar(250), --the name of the database to be comapred
@.dbname2 varchar(250) --the name of the database to be comapred
as
create table #tb1(TableName1 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))

create table #tb2(TableName2 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))

--Get the schema of database1
exec('insert into #tb1 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname1+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname1+'..syscolumns a
left join'+@.dbname1+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname1+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname1+'..syscomments e on a.cdefault=e.id
left join'+@.dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')

--Get the schema of database2
exec('insert into #tb2 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname2+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname2+'..syscolumns a
left join'+@.dbname2+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname2+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname2+'..syscomments e on a.cdefault=e.id
left join'+@.dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where TableName2=a.TableName1)
select Result=case when a.TableName1 is null and b.Ordinal=1 then 'Table：'+b.TableName2+' absent in'+@.dbname1
when b.TableName2 is null and a.Ordinal=1 then 'Table :'+a.TableName1+' absent in'+@.dbname2
when a.ColumnName is null and exists(select 1 from #tb1 where TableName1=b.TableName2) then @.dbname1+' ['+b.TableName2+'] doesn''t have column：'+b.ColumnName
when b.ColumnName is null and exists(select 1 from #tb2 where TableName2=a.TableName1) then @.dbname2+' ['+a.TableName1+'] doesn''t have column：'+a.ColumnName
when a.Iden<>b.Iden then 'Different identities'
when a.PrimaryKey<>b.PrimaryKey then 'Different Primary Key constraints'
when a.Type<>b.Type then 'Different column data types'
when a.Bytes<>b.Bytes then 'Bytes'
when a.Length<>b.Length then 'Different Lengths'
when a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint then 'Different in BitsAfterDecimalPoint'
when a.AllowNulls<>b.AllowNulls then 'Different in AllowNulls options'
when a.DefaultValue<>b.DefaultValue then 'Different default values'
when a.ColumnDescription<>b.ColumnDescription then 'Different Column Descriptions'
else '' end,
*
from #tb1 a
full join #tb2 b on a.TableName1=b.TableName2 and a.ColumnName=b.ColumnName
where a.TableName1 is null or a.ColumnName is null or b.TableName2 is null or b.ColumnName is null
or a.Iden<>b.Iden or a.PrimaryKey<>b.PrimaryKey or a.Type<>b.Type
or a.Bytes<>b.Bytes or a.Length<>b.Length or a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint
or a.AllowNulls<>b.AllowNulls or a.DefaultValue<>b.DefaultValue or a.ColumnDescription<>b.ColumnDescription
order by isnull(a.TableName1,b.TableName2),isnull(a.Ordinal,b.Ordinal)--isnull(a.ColumnName,b.ColumnName)
go

How to close all open "handles" to a SQL Server 2005 express DB?

Hello all!
I'm trying to create an application which makes a copy of the database file it is using on application start and can restore it at any time, if needed (little project about SQL injection, so I need the copy ;-) ). This works fine, but as soon as I make my first connection to the db (connection_object.open()) and close it again I can not access the file anymore, because it is "in use by another process". How can I release all handles from the database, so that I can replace the DB file with it's backup copy?

Thank you

KoljaYou have to kill connection instead of just closing.

Shot two bulets in his head and call GC to finish him.

|||Ahm...
cal me a noob, but I disposed it and set the variable to null... doesn't work
What do you mean : Call GC?