Invoice numbering system

I am creating an invoicing structure in our database. Our system allows
multiple companies to be setup within the one database. Each company should
have seperate invoice number sequences. E.g.
Company One's last invoice number was 10000
Company Two generates a new invoice. They should be given either 10000 or a
number in a completely different range. But they should not be given 10001
(in company one's sequence).
Has anyone any suggestions on how to create a system for generating invoice
numbers. Two users should never be given the same invoice number (within
the same company). There should never be any gaps in invoice numbers (e.g.
Company One next invoice number after 10000 must be 10001 and then 10002
etc).
I need a guaranteed system that will generate an invoice number using the
rules above.
Thanks,
ChrisIf I was to generate my next number using something such as this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
BY InvoiceNo DESC
This approach seems to work, except for when I generate my first invoice
because the select clause returns nothing. So I tried this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo FROM (
SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialNo
= 5
UNION
SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
This seems to work. Does this sound a good approach for generating invoice
numbers that are unique to a company? And does this guarantee two users
cannot be given the same number?
Thanks,
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I am creating an invoicing structure in our database. Our system allows
>multiple companies to be setup within the one database. Each company
>should have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
> a number in a completely different range. But they should not be given
> 10001 (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating
> invoice numbers. Two users should never be given the same invoice number
> (within the same company). There should never be any gaps in invoice
> numbers (e.g. Company One next invoice number after 10000 must be 10001
> and then 10002 etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>|||put it on separate tables
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Chris" wrote:

> I am creating an invoicing structure in our database. Our system allows
> multiple companies to be setup within the one database. Each company shou
ld
> have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
a
> number in a completely different range. But they should not be given 1000
1
> (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating invoic
e
> numbers. Two users should never be given the same invoice number (within
> the same company). There should never be any gaps in invoice numbers (e.g
.
> Company One next invoice number after 10000 must be 10001 and then 10002
> etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>
>|||Chris ,
why dont u try describing the column as identity using newid function to
give to unque number everytime
"Chris" wrote:

> If I was to generate my next number using something such as this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDE
R
> BY InvoiceNo DESC
>
> This approach seems to work, except for when I generate my first invoice
> because the select clause returns nothing. So I tried this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerial
No
> = 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
> This seems to work. Does this sound a good approach for generating invoic
e
> numbers that are unique to a company? And does this guarantee two users
> cannot be given the same number?
> Thanks,
> Chris
>
> "Chris" <cw@.community.nospam> wrote in message
> news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
>|||On Wed, 21 Sep 2005 13:29:44 +0100, Chris wrote:

>If I was to generate my next number using something such as this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
>BY InvoiceNo DESC
>
>This approach seems to work, except for when I generate my first invoice
>because the select clause returns nothing. So I tried this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialN
o
>= 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
>This seems to work. Does this sound a good approach for generating invoice
>numbers that are unique to a company? And does this guarantee two users
>cannot be given the same number?
Hi Chris,
This one is easier, and probably quicker as well:
INSERT INTO Invoices (InvoiceNo)
SELECT COALESCE(MAX(InvoiceNo),0) + 1
FROM Invoices
WHERE CompanySerialNo = 5
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,
That's more readable!
Chris

> Hi Chris,
> This one is easier, and probably quicker as well:
> INSERT INTO Invoices (InvoiceNo)
> SELECT COALESCE(MAX(InvoiceNo),0) + 1
> FROM Invoices
> WHERE CompanySerialNo = 5
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Invalid OS SP when installing sql beta 2005 IA64

When I run setup I get an error stating the OS is not at the required Servic
e
Pack. I looked over the requirements and Data Center with a build over 1138
was suffecient. I am running Data Center IA64, and I am trying to install
2005 beta dev IA64.Can you please post to the SQL Server 2005 newsgroups?
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"cw" <cw@.discussions.microsoft.com> wrote in message
news:980CBE28-C7A1-4499-BE40-209D849C49EB@.microsoft.com...
> When I run setup I get an error stating the OS is not at the required
Service
> Pack. I looked over the requirements and Data Center with a build over
1138
> was suffecient. I am running Data Center IA64, and I am trying to install
> 2005 beta dev IA64.

Invalid OS SP when installing sql beta 2005 IA64

When I run setup I get an error stating the OS is not at the required Service
Pack. I looked over the requirements and Data Center with a build over 1138
was suffecient. I am running Data Center IA64, and I am trying to install
2005 beta dev IA64.Can you please post to the SQL Server 2005 newsgroups?
http://www.aspfaq.com/sql2005/show.asp?id=1
--
http://www.aspfaq.com/
(Reverse address to reply.)
"cw" <cw@.discussions.microsoft.com> wrote in message
news:980CBE28-C7A1-4499-BE40-209D849C49EB@.microsoft.com...
> When I run setup I get an error stating the OS is not at the required
Service
> Pack. I looked over the requirements and Data Center with a build over
1138
> was suffecient. I am running Data Center IA64, and I am trying to install
> 2005 beta dev IA64.

Invalid OS SP when installing sql beta 2005 IA64

When I run setup I get an error stating the OS is not at the required Service
Pack. I looked over the requirements and Data Center with a build over 1138
was suffecient. I am running Data Center IA64, and I am trying to install
2005 beta dev IA64.
Can you please post to the SQL Server 2005 newsgroups?
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"cw" <cw@.discussions.microsoft.com> wrote in message
news:980CBE28-C7A1-4499-BE40-209D849C49EB@.microsoft.com...
> When I run setup I get an error stating the OS is not at the required
Service
> Pack. I looked over the requirements and Data Center with a build over
1138
> was suffecient. I am running Data Center IA64, and I am trying to install
> 2005 beta dev IA64.

Invalid object name when trying to access sql server through vb ap

Hi,
We're in the process of migrating our live server (Windows NT Server 4.0 sql
6.5) to windows 2003 and sql server 2000.
I've setup a test server running windows 2000 and sql server 6.5, i've
copied the database across to the dev server and fixed the orphaned logins
and made sure the database is consistent which has come out fine.
when we try to acess the database with our vb app we get the error invalid
object name dbo.table name, i think this is something to do with permissions
so i granted myself select access onto the problem table but i still get the
same error, i then granted myself select access onto all the tables and i
still get the same error.
is there another permission that i need to enable?
played around with restoring the msdb database from the live server to the
dev server then going in on single user mode to restor the master from the
live server to the dev server but it all goes pear shaped and i've got to
completely re-install 6.5
any help/advice would be appreciated
thanks
lavan
hi don't worry, i've solved it, i forget to se tthe default database so the
app was going straight to master.
cheers anyway
"lavann" wrote:

> Hi,
> We're in the process of migrating our live server (Windows NT Server 4.0 sql
> 6.5) to windows 2003 and sql server 2000.
> I've setup a test server running windows 2000 and sql server 6.5, i've
> copied the database across to the dev server and fixed the orphaned logins
> and made sure the database is consistent which has come out fine.
> when we try to acess the database with our vb app we get the error invalid
> object name dbo.table name, i think this is something to do with permissions
> so i granted myself select access onto the problem table but i still get the
> same error, i then granted myself select access onto all the tables and i
> still get the same error.
> is there another permission that i need to enable?
> played around with restoring the msdb database from the live server to the
> dev server then going in on single user mode to restor the master from the
> live server to the dev server but it all goes pear shaped and i've got to
> completely re-install 6.5
> any help/advice would be appreciated
> thanks
> lavan

Invalid object name when trying to access sql server through vb ap

Hi,
We're in the process of migrating our live server (Windows NT Server 4.0 sql
6.5) to windows 2003 and sql server 2000.
I've setup a test server running windows 2000 and sql server 6.5, i've
copied the database across to the dev server and fixed the orphaned logins
and made sure the database is consistent which has come out fine.
when we try to acess the database with our vb app we get the error invalid
object name dbo.table name, i think this is something to do with permissions
so i granted myself select access onto the problem table but i still get the
same error, i then granted myself select access onto all the tables and i
still get the same error.
is there another permission that i need to enable?
played around with restoring the msdb database from the live server to the
dev server then going in on single user mode to restor the master from the
live server to the dev server but it all goes pear shaped and i've got to
completely re-install 6.5
any help/advice would be appreciated
thanks
lavanhi don't worry, i've solved it, i forget to se tthe default database so the
app was going straight to master.
cheers anyway
"lavann" wrote:
> Hi,
> We're in the process of migrating our live server (Windows NT Server 4.0 sql
> 6.5) to windows 2003 and sql server 2000.
> I've setup a test server running windows 2000 and sql server 6.5, i've
> copied the database across to the dev server and fixed the orphaned logins
> and made sure the database is consistent which has come out fine.
> when we try to acess the database with our vb app we get the error invalid
> object name dbo.table name, i think this is something to do with permissions
> so i granted myself select access onto the problem table but i still get the
> same error, i then granted myself select access onto all the tables and i
> still get the same error.
> is there another permission that i need to enable?
> played around with restoring the msdb database from the live server to the
> dev server then going in on single user mode to restor the master from the
> live server to the dev server but it all goes pear shaped and i've got to
> completely re-install 6.5
> any help/advice would be appreciated
> thanks
> lavan

Invalid object name 'dbo.sysmergepublications'

I am working with SQL Server 2005 and having problems with the publisher.
After I installed SQL and setup my database, I setup the server as both the
distributor and the publisher. Then I setup a merge publication that worked
fine with SQL Mobile 2005, I didn't have any problems with synchronization
and everything seemed fine. I deleted that publication and went through the
New Publication Wizard to create a new publication, but at the end I get the
following message:
TITLE: New Publication Wizard
SQL Server could not create publication 'MRSTest'.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'dbo.sysmergepublications'.
Publication 'MRSTest' does not exist.
Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&ProdVer=09.00.
1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
I looked and sure enough all those tables were gone out of my database under
System Tables. I found a stored procedure to recreated all those tables,
and although it did recreate them, it didn't fix the problem. I have trid
to disable Publisher but I get the following error:
TITLE: Publisher Properties
An error occurred while saving publication database.
Do you want to continue saving other changes?
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06 &EvtSrc=Microsoft.
SqlServer.Management.UI.
PubshrPropertiesErrorSR&EvtID=ErrorSavingPubDB&Lin kId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'dbo.sysmergesubscriptions'.
Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&ProdVer=09.00.
1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
This happened one other time when I deleted a publication and I ended up
reinstalling SQL. This is just a test database so it really isn't a big
deal, it's just time consuming. Could this be happening because I am in
there a lot changing the Publication and Article properties? I will be
moving this to a production server soon and although I doubt I will be
deleting any Publications, just in case I really need to know how to fix it.
Any help would really be appreciated in fixing this on my development server
along with how to prevent it from happening in the future.
Thanks,
Heather
Can you create a new publication with a different name?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Heather B." <hbaker@.pro-merchnospam.com> wrote in message
news:975c64b3907e4dbb9353419093e55256@.ureader.com. ..
>I am working with SQL Server 2005 and having problems with the publisher.
> After I installed SQL and setup my database, I setup the server as both
> the
> distributor and the publisher. Then I setup a merge publication that
> worked
> fine with SQL Mobile 2005, I didn't have any problems with synchronization
> and everything seemed fine. I deleted that publication and went through
> the
> New Publication Wizard to create a new publication, but at the end I get
> the
> following message:
> TITLE: New Publication Wizard
> --
> SQL Server could not create publication 'MRSTest'.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Invalid object name 'dbo.sysmergepublications'.
> Publication 'MRSTest' does not exist.
> Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
> For help, click: http://go.microsoft.com/fwlink?
> ProdName=Microsoft+SQL+Server&ProdVer=09.00.
> 1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
> I looked and sure enough all those tables were gone out of my database
> under
> System Tables. I found a stored procedure to recreated all those tables,
> and although it did recreate them, it didn't fix the problem. I have trid
> to disable Publisher but I get the following error:
> TITLE: Publisher Properties
> --
> An error occurred while saving publication database.
> Do you want to continue saving other changes?
> For help, click: http://go.microsoft.com/fwlink?
> ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06 &EvtSrc=Microsoft.
> SqlServer.Management.UI.
> PubshrPropertiesErrorSR&EvtID=ErrorSavingPubDB&Lin kId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Invalid object name 'dbo.sysmergesubscriptions'.
> Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
> For help, click: http://go.microsoft.com/fwlink?
> ProdName=Microsoft+SQL+Server&ProdVer=09.00.
> 1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
> This happened one other time when I deleted a publication and I ended up
> reinstalling SQL. This is just a test database so it really isn't a big
> deal, it's just time consuming. Could this be happening because I am in
> there a lot changing the Publication and Article properties? I will be
> moving this to a production server soon and although I doubt I will be
> deleting any Publications, just in case I really need to know how to fix
> it.
>
> Any help would really be appreciated in fixing this on my development
> server
> along with how to prevent it from happening in the future.
> Thanks,
> Heather
|||No, it doesn't matter what I name it I cannot create another merge
publication. I can create a snapshot publication, but that doesn't really
help.
|||You shouldn't have to reinstall. You mention "I found a stored procedure to
recreated all those tables, and although it did recreate them, it didn't fix
the problem"
What was this proc and where did you find it?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Heather B." <hbaker@.pro-merchnospam.com> wrote in message
news:04535b948bf74617a5bdfcf4fba91355@.ureader.com. ..
> No, it doesn't matter what I name it I cannot create another merge
> publication. I can create a snapshot publication, but that doesn't really
> help.
|||I found this in a forum somewhere, when I ran it all the tables seemed to be
there, but when I tried to create a new publication I still go an error.
This time it would say something about a field missing, I don't remember
which field but I do remember that I looked in the table and it was there.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[sp_MScreate_mergesystables] as
/* This is to make sure that the varbinary columns do not get padded */
set ANSI_PADDING off
DECLARE @.exist bit
DECLARE @.validsubs int
select @.exist = 1
begin tran
save transaction MScreate_mergesystables
exec dbo.sp_MScheckvalidsystables @.validsubs output
if @.validsubs = 0
exec dbo.sp_MSdrop_mergesystables
if not exists (select * from sysobjects where name = 'sysmergepublications')
begin
raiserror('Creating table sysmergepublications',0,1)
create table dbo.sysmergepublications
(publishersysname NOT NULL default @.@.servername,
publisher_dbsysname NOT NULL default db_name(),
namesysnameNOT NULL,
descriptionnvarchar(255) NULL,
retentionintNULL,
publication_type tinyintNULL,
pubid uniqueidentifier NOT NULL,
designmasteriduniqueidentifier NULL,
parentiduniqueidentifier NULL,
sync_modetinyintNULL,
allow_pushintNULL,
allow_pullintNULL,
allow_anonymousintNULL,
centralized_conflictsintNULL,
statustinyintNULL,
snapshot_readytinyintNULL,
enabled_for_internet bit NOT NULL default 0,
dynamic_filtersbitNOT NULL default 0,
-- portable snapshot support
snapshot_in_defaultfolder bit NOT NULL default
1,
alt_snapshot_folder nvarchar(255) NULL,
-- Pre/post - snapshot commands
pre_snapshot_script nvarchar(255) NULL,
post_snapshot_script nvarchar(255) NULL,
-- Snapshot compression
compress_snapshot bit NOT NULL default
0,
-- Post 7.0 Ftp support
ftp_address sysname NULL,
ftp_port int NOT NULL default
21,
ftp_subdirectory nvarchar(255) NULL,
ftp_login sysname NULL default
N'anonymous',
ftp_password nvarchar(524) NULL,
conflict_retentionintNULL,
keep_before_valuesintNULL default 0,
allow_subscription_copybit NULL default 0,
allow_synctoalternatebit NULL default 0,
validate_subscriber_infonvarchar(500)NULL,
ad_guidnamesysnameNULL,
backward_comp_levelintnot NULL default 10, --7.0 RTM
max_concurrent_mergeintnot NULL default 0,
max_concurrent_dynamic_snapshots int not NULL default
0
)
if @.@.ERROR <> 0
goto Error
CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications
ON sysmergepublications(pubid)
if @.@.ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject sysmergepublications
if @.@.ERROR <> 0
goto Error
-- grant select on sysmergepublications to public
end
if not exists (select * from sysobjects where name = 'MSmerge_errorlineage')
begin
raiserror('Creating table MSmerge_errorlineage',0,1)
create table dbo.MSmerge_errorlineage (
tablenickint NOT NULL,
rowguiduniqueidentifier NOT NULL,
lineagevarbinary(255)
)
exec dbo.sp_MS_marksystemobject MSmerge_errorlineage
if @.@.ERROR <> 0
goto Error
create unique clustered index uc1errorlineage on
MSmerge_errorlineage(tablenick, rowguid)
if @.@.ERROR <> 0
goto Error
--grant select on MSmerge_errorlineage to public
end
-- this table exists at distribution/db and subscriber databse both
if not exists (select * from sysobjects where name =
'MSrepl_identity_range')
begin
raiserror('Creating table MSrepl_identity_range',0,1)
create table dbo.MSrepl_identity_range (
objidint not NULL primary key,
next_seedbigint NULL, --resource control
pub_rangebigint NULL, --publisher range
rangebigint NULL, -- set by sp_addmergearticle
max_identitybigint NULL, --resource control
thresholdintNULL,--in percentage, set by sp_addmergearticle
current_maxbigint NULL--max value for current check constraint,set
by sp_addmergearticle
)
exec dbo.sp_MS_marksystemobject MSrepl_identity_range
if @.@.ERROR <> 0
goto Error
--grant select on MSrepl_identity_range to public
end
if not exists (select * from sysobjects where name = 'sysmergearticles')
begin
raiserror('Creating table sysmergearticles',0,1)
create table dbo.sysmergearticles (
namesysnameNOT NULL,
type tinyint NULL,
objidintNOT NULL,
sync_objidintNOT NULL,
view_type tinyintNULL,
artiduniqueidentifierNOT NULL,
description nvarchar(255) NULL,
pre_creation_commandtinyintNULL,
pubiduniqueidentifier NOT NULL,
nicknameintNOT NULL,
column_trackingintNOT NULL,
statustinyintNULL,
conflict_tablesysnameNULL,
creation_scriptnvarchar(255)NULL,
conflict_scriptnvarchar(255)NULL,
article_resolvernvarchar(255)NULL,
ins_conflict_procsysnameNULL,
insert_procsysnameNULL,
update_procsysnameNULL,
select_procsysnameNULL,
schema_option binary(8) NULL,
destination_objectsysnameNOT NULL,
destination_ownersysnameNULL,
resolver_clsidnvarchar(50)NULL,
subset_filterclause nvarchar(1000) NULL,
missing_col_countintNULL,
missing_colsvarbinary(128)NULL,
excluded_colsvarbinary(128)NULL,
excluded_col_countintnot NULL default 0,
columns varbinary(128) NULL,
resolver_infonvarchar(255)NULL,
view_sel_procnvarchar(290)NULL,
gen_curintNULL,
vertical_partitionintnot NULL default 0,
identity_supportintnot NULL default 0,
before_image_objidintNULL,
before_view_objidintNULL,
verify_resolver_signature intNULL,
allow_interactive_resolverbitNOT NULL default 0,
fast_multicol_updateprocbitNOT NULL default 0,
check_permissionsintNOT NULL default 0,
maxversion_at_cleanupintNOT NULL default 1,
published_in_tran_pubbitNOT NULL default 0
-- Note: Please update sysmergeextendedarticlesview
whenever
-- there is a schema change in sysmergearticles
)
if @.@.error<>0
goto Error
else
begin
create unique clustered index uc1sysmergearticles
on sysmergearticles(artid, pubid)
if @.@.ERROR <> 0
goto Error
if not exists (select * from sysindexes where name =
'nc1sysmergearticles')
begin
create nonclustered index nc1sysmergearticles on
sysmergearticles(nickname)
if @.@.ERROR <> 0
return 1
end
end
exec dbo.sp_MS_marksystemobject sysmergearticles
if @.@.ERROR <> 0
goto Error
grant select(nickname,gen_cur,maxversion_at_cleanup) on sysmergearticles
to public
end
if not exists (select * from sysobjects where name =
'sysmergeschemaarticles')
begin
-- The extended merge articles table is for storing
-- schema only article information.
-- The current supported schema only articles are
-- stored procs and views.
raiserror('Creating table sysmergeschemaarticles',0,1)
create table dbo.sysmergeschemaarticles
( name sysname NOT NULL,
type tinyint NULL,
objid int NOT NULL,
artid uniqueidentifier NOT NULL,
description nvarchar(255) NULL,
pre_creation_command tinyint NULL,
pubid uniqueidentifier NOT NULL,
status tinyint NULL,
creation_script nvarchar(255) NULL,
schema_option binary(8) NULL,
destination_object sysname NOT NULL,
destination_owner sysname NULL
-- Note: Please update sysmergeextendedarticlesview
whenever
-- there is a schema change in sysmergeschemaarticles
)
if @.@.error<>0
goto Error
else
begin
create unique clustered index uc1sysmergeschemaarticles
on sysmergeschemaarticles(artid, pubid)
if @.@.ERROR <> 0
goto Error
end
exec dbo.sp_MS_marksystemobject sysmergeschemaarticles
if @.@.ERROR <> 0
goto Error
end
if exists (select * from sysobjects where name =
'sysmergeextendedarticlesview')
begin
drop view dbo.sysmergeextendedarticlesview
end
exec ('create view dbo.sysmergeextendedarticlesview
as
select name, type, objid, sync_objid, view_type, artid,
description, pre_creation_command, pubid,
nickname, column_tracking, status, conflict_table, creation_script,
conflict_script, article_resolver,
ins_conflict_proc, insert_proc, update_proc, select_proc,
schema_option, destination_object,
resolver_clsid, subset_filterclause, missing_col_count, missing_cols,
columns, resolver_info,
view_sel_proc, gen_cur, excluded_cols, excluded_col_count,
vertical_partition, identity_support,
destination_owner, before_image_objid, before_view_objid,
verify_resolver_signature,
allow_interactive_resolver, fast_multicol_updateproc,
check_permissions, maxversion_at_cleanup,
published_in_tran_pub
from sysmergearticles
union all
select name, type, objid, NULL, NULL, artid, description,
pre_creation_command, pubid,
NULL, NULL, status, NULL, creation_script, NULL, NULL,
NULL, NULL, NULL, NULL, schema_option, destination_object,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
destination_owner, NULL, NULL, NULL,
0, 0, 0, NULL, 0
from sysmergeschemaarticles
go')
if @.@.error <> 0
goto Error
exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview
if not exists (select * from sysobjectswhere name =
'sysmergesubscriptions')
begin
raiserror('Creating table sysmergesubscriptions',0,1)
create table dbo.sysmergesubscriptions
(
subid uniqueidentifier NOT NULL,
partnerid uniqueidentifier NOT NULL,
datasource_typeintNOT NULL,
datasource_pathnvarchar(255)NULL,
srvid intNOT NULL,
db_name sysname NOT NULL
constraint unique_pubsrvdb unique nonclustered (pubid, srvid,
db_name),
pubiduniqueidentifier NULL,
status tinyint NOT NULL,
subscriber_typeintNOT NULL,
subscription_typeintNOT NULL,
priorityrealNOT NULL,
sync_type tinyintNOT NULL,-- 1 = automatic 2 = no sync
descriptionnvarchar(255)NULL,
login_namesysnameNOT NULL,
last_validateddatetimeNULL,
subscriber_serversysnameNULL,
use_interactive_resolverbitNOT NULL default 0,
publicationsysname NULL,
distributorsysname NULL,
validation_levelintnot NULL default 0,
resync_genintnot NULL default -1,
attempted_validatedatetimeNULL,
last_sync_datedatetimeNULL,
last_sync_statusintNULL,
last_sync_summarysysnameNULL
)
if @.@.error<>0
goto Error
else
begin
create unique clustered index uc1sysmergesubscriptions
on sysmergesubscriptions (subid)
if @.@.ERROR<>0
goto Error
create index nc2sysmergesubscriptions on sysmergesubscriptions (srvid,
db_name)
if @.@.ERROR<>0
goto Error
end
exec dbo.sp_MS_marksystemobject sysmergesubscriptions
if @.@.ERROR <> 0
goto Error
--grant select on sysmergesubscriptions to public
end
if not exists (select * from sysobjectswhere name = 'MSmerge_replinfo')
begin
raiserror('Creating table MSmerge_replinfo',0,1)
create table dbo.MSmerge_replinfo
(
repid uniqueidentifier NOT NULL,
replnicknameintNOT NULL,
recgenint NULL,
recguiduniqueidentifier NULL,
sentgenint NULL,
sentguiduniqueidentifier NULL,
schemaversionint NULL,
schemaguiduniqueidentifier NULL,
merge_jobidbinary(16)NULL,
snapshot_jobidbinary(16)NULL
)
if @.@.ERROR <> 0
goto Error
else
begin
create unique clustered index uc1MSmerge_replinfo
on MSmerge_replinfo (repid)
if @.@.ERROR <> 0
goto Error
end
exec dbo.sp_MS_marksystemobject MSmerge_replinfo
if @.@.ERROR <> 0
goto Error
--grant select on MSmerge_replinfo to public
end
if not exists (select * from sysobjectswhere name = 'MSmerge_tombstone')
begin
raiserror('Creating table MSmerge_tombstone',0,1)
create table dbo.MSmerge_tombstone
(
rowguiduniqueidentifier rowguidcol NOT NULL,
tablenickintNOT NULL,
typetinyintNOT NULL,
lineagevarbinary(249)NOT NULL,
generationintNOT NULL,
reasonnvarchar(255)NOT NULL,
)
if @.@.ERROR <> 0
goto Error
else
begin
create unique clustered index uc1MSmerge_tombstone
on MSmerge_tombstone (tablenick DESC, rowguid)
if @.@.ERROR <> 0goto Error
create index nc2MSmerge_tombstone
on MSmerge_tombstone (generation)
if @.@.ERROR <> 0goto Error
end
exec dbo.sp_MS_marksystemobject MSmerge_tombstone
if @.@.ERROR <> 0
goto Error
--grant select on MSmerge_tombstone to public
end
if not exists (select * from sysobjectswhere name = 'MSmerge_contents')
begin
raiserror('Creating table MSmerge_contents',0,1)
create table dbo.MSmerge_contents
(
tablenickintNOT NULL,
rowguiduniqueidentifier rowguidcol NOT NULL,
generationintNOT NULL,
partchangegenintNULL,
joinchangegenintNULL,
lineagevarbinary(249)NOT NULL,
colv1varbinary(2048)NULL,
)
if @.@.ERROR <> 0
goto Error
else
begin
create unique clustered index uc1SycContents on
MSmerge_contents(tablenick, rowguid)
if @.@.ERROR <> 0 goto Error
create index nc2MSmerge_contents on MSmerge_contents(generation)
if @.@.ERROR <> 0 goto Error
create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
if @.@.ERROR <> 0 goto Error
create index nc4MSmerge_contents on MSmerge_contents(rowguid)
if @.@.ERROR <> 0 goto Error
end
exec dbo.sp_MS_marksystemobject MSmerge_contents
if @.@.ERROR <> 0
goto Error
--grant select on MSmerge_contents to public
end
if not exists (select * from sysobjectswhere name = 'MSmerge_genhistory')
begin
raiserror('Creating table MSmerge_genhistory',0,1)
create table dbo.MSmerge_genhistory
(
guidsrc uniqueidentifier NOT NULL,
guidlocal uniqueidentifier NOT NULL,
pubiduniqueidentifier NULL,
generation intNOT NULL,
art_nickintNULL,
nicknamesvarbinary(1000)NOT NULL,
coldate datetimeNOT NULL
)
if @.@.ERROR <> 0
goto Error
create clustered index c1MSmerge_genhistory on
MSmerge_genhistory(generation)
if @.@.ERROR <> 0
goto Error
create unique index unc1MSmerge_genhistory on MSmerge_genhistory(guidsrc,
pubid)
if @.@.ERROR <> 0
goto Error
create index nc2MSmerge_genhistory on MSmerge_genhistory(guidlocal)
if @.@.ERROR <> 0
goto Error
CREATE INDEX nc4MSmerge_genhistory ON MSmerge_genhistory(coldate)
if @.@.ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSmerge_genhistory
if @.@.ERROR <> 0
goto Error
--grant select on MSmerge_genhistory to public
end
if not exists (select * from sysobjectswhere name =
'MSmerge_delete_conflicts')
begin
raiserror('Creating table MSmerge_delete_conflicts',0,1)
create table dbo.MSmerge_delete_conflicts
(
tablenickintNOT NULL,
rowguiduniqueidentifier rowguidcol NOT NULL,
origin_datasourcenvarchar(255)NULL,
conflict_typeint NULL,
reason_codeint NULL,
reason_textnvarchar(720) NULL,
pubiduniqueidentifier NULL,
create_timedatetime not null default getdate()
)
if @.@.ERROR <> 0
goto Error
else
begin
create clustered index uc1MSmerge_delete_conflicts on
MSmerge_delete_conflicts(tablenick, rowguid)
if @.@.ERROR <> 0
goto Error
end
CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_delete_conflicts
ON MSmerge_delete_conflicts(tablenick, rowguid, origin_datasource)
if @.@.ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSmerge_delete_conflicts
if @.@.ERROR <> 0
goto Error
--grant select on MSmerge_delete_conflicts to public
end
if not exists (select * from sysobjectswhere name = 'sysmergeschemachange')
begin
raiserror('Creating table sysmergeschemachange',0,1)
create table dbo.sysmergeschemachange
(
pubiduniqueidentifier NOT NULL,
artiduniqueidentifier NULL,
schemaversion int NOT NULL,
schemaguid uniqueidentifier NOT NULL,
schematypeint NOT NULL,
schematextnvarchar(2000) NOT NULL
)
if @.@.ERROR <> 0
goto Error
else
begin
create unique clustered index schemachangeversion on
sysmergeschemachange(schemaversion, pubid)
if @.@.ERROR <> 0
goto Error
end
exec dbo.sp_MS_marksystemobject sysmergeschemachange
if @.@.ERROR <> 0
goto Error
--grant select on sysmergeschemachange to public
end
if not exists (select * from sysobjects where name =
'sysmergesubsetfilters')
begin
raiserror('Creating table sysmergesubsetfilters',0,1)
create table dbo.sysmergesubsetfilters (
filtername sysname NOT NULL,
join_filteridintidentity NOT NULL,
pubiduniqueidentifierNOT NULL,
artiduniqueidentifierNOT NULL,
art_nicknameintNOT NULL,
join_articlenamesysname NOT NULL,
join_nickname intNOT NULL,
join_unique_keyintNOT NULL,
expand_procsysnameNULL,
join_filterclause nvarchar(1000) NULL
)
if @.@.ERROR <> 0
goto Error
CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters
ON sysmergesubsetfilters(join_filterid, pubid)
if @.@.ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject sysmergesubsetfilters
if @.@.ERROR <> 0
goto Error
--grant select on sysmergesubsetfilters to public
end
if @.@.error <> 0
goto Error
if not exists (select * from sysobjects where name =
'MSdynamicsnapshotviews')
begin
raiserror('Creating table MSdynamicsnapshotviews',0,1)
create table dbo.MSdynamicsnapshotviews (
dynamic_snapshot_view_name sysname primary key
)
if @.@.ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews
end
if not exists (select * from sysobjects where name =
'MSdynamicsnapshotjobs')
begin
raiserror('Creating table MSdynamicsnapshotjobs',0,1)
create table dbo.MSdynamicsnapshotjobs (
id int identity,
name sysname not null unique,
pubid uniqueidentifier not null,
job_id uniqueidentifier not null,
dynamic_filter_login sysname null,
dynamic_filter_hostname sysname null,
dynamic_snapshot_location nvarchar(255) not null
)
if @.@.ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs
create unique clustered index uciMSdynamicsnapshotjobs on
dbo.MSdynamicsnapshotjobs(job_id, pubid)
if @.@.ERROR <> 0
goto Error
end
if @.@.error <> 0
goto Error
if not exists (select * from sysobjects where name =
'MSmerge_altsyncpartners')
begin
raiserror('Creating table MSmerge_altsyncpartners',0,1)
create table dbo.MSmerge_altsyncpartners (
subid uniqueidentifier not null,
alternate_subid uniqueidentifier not null,
descriptionnvarchar(255)NULL
)
if @.@.ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners
create unique clustered index uciMSmerge_altsyncpartners on
dbo.MSmerge_altsyncpartners(subid, alternate_subid)
if @.@.ERROR <> 0
goto Error
end
if exists (select * from master..sysobjects where type='P' and
name='sp_MScreatedebuginfrastructure')
begin
declare @.retval int
exec @.retval= dbo.sp_MScreatedebuginfrastructure
if @.@.ERROR <> 0 or @.retval <> 0 goto Error
end
commit transaction
return (0)
Error:
if @.@.trancount > 0
begin
ROLLBACK TRANSACTION MScreate_mergesystables
COMMIT TRANSACTION
end
RAISERROR (20008, 16, -1)
return (1)
|||This looks like its for SQL 2000 and not for SQL 2005. Do you have any
backups you can restore?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Heather B." <hbaker@.pro-merchnospam.com> wrote in message
news:3758aadfdace47689a27696ac35b5ffd@.ureader.com. ..
>I found this in a forum somewhere, when I ran it all the tables seemed to
>be
> there, but when I tried to create a new publication I still go an error.
> This time it would say something about a field missing, I don't remember
> which field but I do remember that I looked in the table and it was there.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER procedure [dbo].[sp_MScreate_mergesystables] as
> /* This is to make sure that the varbinary columns do not get padded */
> set ANSI_PADDING off
> DECLARE @.exist bit
> DECLARE @.validsubs int
> select @.exist = 1
> begin tran
> save transaction MScreate_mergesystables
> exec dbo.sp_MScheckvalidsystables @.validsubs output
> if @.validsubs = 0
> exec dbo.sp_MSdrop_mergesystables
> if not exists (select * from sysobjects where name =
> 'sysmergepublications')
> begin
> raiserror('Creating table sysmergepublications',0,1)
> create table dbo.sysmergepublications
> ( publisher sysname NOT NULL default @.@.servername,
> publisher_db sysname NOT NULL default db_name(),
> name sysname NOT NULL,
> description nvarchar(255) NULL,
> retention int NULL,
> publication_type tinyint NULL,
> pubid uniqueidentifier NOT NULL,
> designmasterid uniqueidentifier NULL,
> parentid uniqueidentifier NULL,
> sync_mode tinyint NULL,
> allow_push int NULL,
> allow_pull int NULL,
> allow_anonymous int NULL,
> centralized_conflicts int NULL,
> status tinyint NULL,
> snapshot_ready tinyint NULL,
> enabled_for_internet bit NOT NULL default 0,
> dynamic_filters bit NOT NULL default 0,
> -- portable snapshot support
> snapshot_in_defaultfolder bit NOT NULL
> default
> 1,
> alt_snapshot_folder nvarchar(255) NULL,
> -- Pre/post - snapshot commands
> pre_snapshot_script nvarchar(255) NULL,
> post_snapshot_script nvarchar(255) NULL,
> -- Snapshot compression
> compress_snapshot bit NOT NULL
> default
> 0,
> -- Post 7.0 Ftp support
> ftp_address sysname NULL,
> ftp_port int NOT NULL
> default
> 21,
> ftp_subdirectory nvarchar(255) NULL,
> ftp_login sysname NULL default
> N'anonymous',
> ftp_password nvarchar(524) NULL,
> conflict_retention int NULL,
> keep_before_values int NULL default 0,
> allow_subscription_copy bit NULL default 0,
> allow_synctoalternate bit NULL default 0,
> validate_subscriber_info nvarchar(500) NULL,
> ad_guidname sysname NULL,
> backward_comp_level int not NULL default 10, --7.0 RTM
> max_concurrent_merge int not NULL default 0,
> max_concurrent_dynamic_snapshots int not NULL
> default
> 0
> )
> if @.@.ERROR <> 0
> goto Error
> CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications
> ON sysmergepublications(pubid)
> if @.@.ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject sysmergepublications
> if @.@.ERROR <> 0
> goto Error
> -- grant select on sysmergepublications to public
> end
> if not exists (select * from sysobjects where name =
> 'MSmerge_errorlineage')
> begin
> raiserror('Creating table MSmerge_errorlineage',0,1)
> create table dbo.MSmerge_errorlineage (
> tablenick int NOT NULL,
> rowguid uniqueidentifier NOT NULL,
> lineage varbinary(255)
> )
> exec dbo.sp_MS_marksystemobject MSmerge_errorlineage
> if @.@.ERROR <> 0
> goto Error
> create unique clustered index uc1errorlineage on
> MSmerge_errorlineage(tablenick, rowguid)
> if @.@.ERROR <> 0
> goto Error
> --grant select on MSmerge_errorlineage to public
> end
> -- this table exists at distribution/db and subscriber databse both
> if not exists (select * from sysobjects where name =
> 'MSrepl_identity_range')
> begin
> raiserror('Creating table MSrepl_identity_range',0,1)
> create table dbo.MSrepl_identity_range (
> objid int not NULL primary key,
> next_seed bigint NULL, --resource control
> pub_range bigint NULL, --publisher range
> range bigint NULL, -- set by sp_addmergearticle
> max_identity bigint NULL, --resource control
> threshold int NULL, --in percentage, set by sp_addmergearticle
> current_max bigint NULL --max value for current check constraint,set
> by sp_addmergearticle
> )
> exec dbo.sp_MS_marksystemobject MSrepl_identity_range
> if @.@.ERROR <> 0
> goto Error
> --grant select on MSrepl_identity_range to public
> end
> if not exists (select * from sysobjects where name = 'sysmergearticles')
> begin
> raiserror('Creating table sysmergearticles',0,1)
>
> create table dbo.sysmergearticles (
> name sysname NOT NULL,
> type tinyint NULL,
> objid int NOT NULL,
> sync_objid int NOT NULL,
> view_type tinyint NULL,
> artid uniqueidentifier NOT NULL,
> description nvarchar(255) NULL,
> pre_creation_command tinyint NULL,
> pubid uniqueidentifier NOT NULL,
> nickname int NOT NULL,
> column_tracking int NOT NULL,
> status tinyint NULL,
> conflict_table sysname NULL,
> creation_script nvarchar(255) NULL,
> conflict_script nvarchar(255) NULL,
> article_resolver nvarchar(255) NULL,
> ins_conflict_proc sysname NULL,
> insert_proc sysname NULL,
> update_proc sysname NULL,
> select_proc sysname NULL,
> schema_option binary(8) NULL,
> destination_object sysname NOT NULL,
> destination_owner sysname NULL,
> resolver_clsid nvarchar(50) NULL,
> subset_filterclause nvarchar(1000) NULL,
> missing_col_count int NULL,
> missing_cols varbinary(128) NULL,
> excluded_cols varbinary(128) NULL,
> excluded_col_count int not NULL default 0,
> columns varbinary(128) NULL,
> resolver_info nvarchar(255) NULL,
> view_sel_proc nvarchar(290) NULL,
> gen_cur int NULL,
> vertical_partition int not NULL default 0,
> identity_support int not NULL default 0,
> before_image_objid int NULL,
> before_view_objid int NULL,
> verify_resolver_signature int NULL,
> allow_interactive_resolver bit NOT NULL default 0,
> fast_multicol_updateproc bit NOT NULL default 0,
> check_permissions int NOT NULL default 0,
> maxversion_at_cleanup int NOT NULL default 1,
> published_in_tran_pub bit NOT NULL default 0
> -- Note: Please update sysmergeextendedarticlesview
> whenever
> -- there is a schema change in sysmergearticles
> )
> if @.@.error<>0
> goto Error
> else
> begin
> create unique clustered index uc1sysmergearticles
> on sysmergearticles(artid, pubid)
> if @.@.ERROR <> 0
> goto Error
> if not exists (select * from sysindexes where name =
> 'nc1sysmergearticles')
> begin
> create nonclustered index nc1sysmergearticles on
> sysmergearticles(nickname)
> if @.@.ERROR <> 0
> return 1
> end
> end
> exec dbo.sp_MS_marksystemobject sysmergearticles
> if @.@.ERROR <> 0
> goto Error
> grant select(nickname,gen_cur,maxversion_at_cleanup) on sysmergearticles
> to public
> end
> if not exists (select * from sysobjects where name =
> 'sysmergeschemaarticles')
> begin
> -- The extended merge articles table is for storing
> -- schema only article information.
> -- The current supported schema only articles are
> -- stored procs and views.
> raiserror('Creating table sysmergeschemaarticles',0,1)
> create table dbo.sysmergeschemaarticles
> ( name sysname NOT NULL,
> type tinyint NULL,
> objid int NOT NULL,
> artid uniqueidentifier NOT NULL,
> description nvarchar(255) NULL,
> pre_creation_command tinyint NULL,
> pubid uniqueidentifier NOT NULL,
> status tinyint NULL,
> creation_script nvarchar(255) NULL,
> schema_option binary(8) NULL,
> destination_object sysname NOT NULL,
> destination_owner sysname NULL
> -- Note: Please update sysmergeextendedarticlesview
> whenever
> -- there is a schema change in sysmergeschemaarticles
> )
> if @.@.error<>0
> goto Error
> else
> begin
> create unique clustered index uc1sysmergeschemaarticles
> on sysmergeschemaarticles(artid, pubid)
> if @.@.ERROR <> 0
> goto Error
> end
> exec dbo.sp_MS_marksystemobject sysmergeschemaarticles
> if @.@.ERROR <> 0
> goto Error
> end
> if exists (select * from sysobjects where name =
> 'sysmergeextendedarticlesview')
> begin
> drop view dbo.sysmergeextendedarticlesview
> end
> exec ('create view dbo.sysmergeextendedarticlesview
> as
> select name, type, objid, sync_objid, view_type, artid,
> description, pre_creation_command, pubid,
> nickname, column_tracking, status, conflict_table, creation_script,
> conflict_script, article_resolver,
> ins_conflict_proc, insert_proc, update_proc, select_proc,
> schema_option, destination_object,
> resolver_clsid, subset_filterclause, missing_col_count, missing_cols,
> columns, resolver_info,
> view_sel_proc, gen_cur, excluded_cols, excluded_col_count,
> vertical_partition, identity_support,
> destination_owner, before_image_objid, before_view_objid,
> verify_resolver_signature,
> allow_interactive_resolver, fast_multicol_updateproc,
> check_permissions, maxversion_at_cleanup,
> published_in_tran_pub
> from sysmergearticles
> union all
> select name, type, objid, NULL, NULL, artid, description,
> pre_creation_command, pubid,
> NULL, NULL, status, NULL, creation_script, NULL, NULL,
> NULL, NULL, NULL, NULL, schema_option, destination_object,
> NULL, NULL, NULL, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL,
> destination_owner, NULL, NULL, NULL,
> 0, 0, 0, NULL, 0
> from sysmergeschemaarticles
> go')
> if @.@.error <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview
> if not exists (select * from sysobjects where name =
> 'sysmergesubscriptions')
> begin
> raiserror('Creating table sysmergesubscriptions',0,1)
>
> create table dbo.sysmergesubscriptions
> (
> subid uniqueidentifier NOT NULL,
> partnerid uniqueidentifier NOT NULL,
> datasource_type int NOT NULL,
> datasource_path nvarchar(255) NULL,
> srvid int NOT NULL,
> db_name sysname NOT NULL
> constraint unique_pubsrvdb unique nonclustered (pubid, srvid,
> db_name),
> pubid uniqueidentifier NULL,
> status tinyint NOT NULL,
> subscriber_type int NOT NULL,
> subscription_type int NOT NULL,
> priority real NOT NULL,
> sync_type tinyint NOT NULL, -- 1 = automatic 2 = no sync
> description nvarchar(255) NULL,
> login_name sysname NOT NULL,
> last_validated datetime NULL,
> subscriber_server sysname NULL,
> use_interactive_resolver bit NOT NULL default 0,
> publication sysname NULL,
> distributor sysname NULL,
> validation_level int not NULL default 0,
> resync_gen int not NULL default -1,
> attempted_validate datetime NULL,
> last_sync_date datetime NULL,
> last_sync_status int NULL,
> last_sync_summary sysname NULL
> )
> if @.@.error<>0
> goto Error
> else
> begin
> create unique clustered index uc1sysmergesubscriptions
> on sysmergesubscriptions (subid)
> if @.@.ERROR<>0
> goto Error
> create index nc2sysmergesubscriptions on sysmergesubscriptions (srvid,
> db_name)
> if @.@.ERROR<>0
> goto Error
> end
> exec dbo.sp_MS_marksystemobject sysmergesubscriptions
> if @.@.ERROR <> 0
> goto Error
> --grant select on sysmergesubscriptions to public
> end
> if not exists (select * from sysobjects where name = 'MSmerge_replinfo')
> begin
> raiserror('Creating table MSmerge_replinfo',0,1)
> create table dbo.MSmerge_replinfo
> (
> repid uniqueidentifier NOT NULL,
> replnickname int NOT NULL,
> recgen int NULL,
> recguid uniqueidentifier NULL,
> sentgen int NULL,
> sentguid uniqueidentifier NULL,
> schemaversion int NULL,
> schemaguid uniqueidentifier NULL,
> merge_jobid binary(16) NULL,
> snapshot_jobid binary(16) NULL
> )
> if @.@.ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index uc1MSmerge_replinfo
> on MSmerge_replinfo (repid)
> if @.@.ERROR <> 0
> goto Error
> end
> exec dbo.sp_MS_marksystemobject MSmerge_replinfo
> if @.@.ERROR <> 0
> goto Error
> --grant select on MSmerge_replinfo to public
> end
> if not exists (select * from sysobjects where name = 'MSmerge_tombstone')
> begin
> raiserror('Creating table MSmerge_tombstone',0,1)
> create table dbo.MSmerge_tombstone
> (
> rowguid uniqueidentifier rowguidcol NOT NULL,
> tablenick int NOT NULL,
> type tinyint NOT NULL,
> lineage varbinary(249) NOT NULL,
> generation int NOT NULL,
> reason nvarchar(255) NOT NULL,
> )
> if @.@.ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index uc1MSmerge_tombstone
> on MSmerge_tombstone (tablenick DESC, rowguid)
> if @.@.ERROR <> 0 goto Error
> create index nc2MSmerge_tombstone
> on MSmerge_tombstone (generation)
> if @.@.ERROR <> 0 goto Error
> end
> exec dbo.sp_MS_marksystemobject MSmerge_tombstone
> if @.@.ERROR <> 0
> goto Error
> --grant select on MSmerge_tombstone to public
> end
> if not exists (select * from sysobjects where name = 'MSmerge_contents')
> begin
> raiserror('Creating table MSmerge_contents',0,1)
> create table dbo.MSmerge_contents
> (
> tablenick int NOT NULL,
> rowguid uniqueidentifier rowguidcol NOT NULL,
> generation int NOT NULL,
> partchangegen int NULL,
> joinchangegen int NULL,
> lineage varbinary(249) NOT NULL,
> colv1 varbinary(2048) NULL,
> )
> if @.@.ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index uc1SycContents on
> MSmerge_contents(tablenick, rowguid)
> if @.@.ERROR <> 0 goto Error
> create index nc2MSmerge_contents on MSmerge_contents(generation)
> if @.@.ERROR <> 0 goto Error
> create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
> if @.@.ERROR <> 0 goto Error
> create index nc4MSmerge_contents on MSmerge_contents(rowguid)
> if @.@.ERROR <> 0 goto Error
> end
> exec dbo.sp_MS_marksystemobject MSmerge_contents
> if @.@.ERROR <> 0
> goto Error
> --grant select on MSmerge_contents to public
> end
> if not exists (select * from sysobjects where name = 'MSmerge_genhistory')
> begin
> raiserror('Creating table MSmerge_genhistory',0,1)
> create table dbo.MSmerge_genhistory
> (
> guidsrc uniqueidentifier NOT NULL,
> guidlocal uniqueidentifier NOT NULL,
> pubid uniqueidentifier NULL,
> generation int NOT NULL,
> art_nick int NULL,
> nicknames varbinary(1000) NOT NULL,
> coldate datetime NOT NULL
> )
> if @.@.ERROR <> 0
> goto Error
> create clustered index c1MSmerge_genhistory on
> MSmerge_genhistory(generation)
> if @.@.ERROR <> 0
> goto Error
> create unique index unc1MSmerge_genhistory on MSmerge_genhistory(guidsrc,
> pubid)
> if @.@.ERROR <> 0
> goto Error
> create index nc2MSmerge_genhistory on MSmerge_genhistory(guidlocal)
> if @.@.ERROR <> 0
> goto Error
> CREATE INDEX nc4MSmerge_genhistory ON MSmerge_genhistory(coldate)
> if @.@.ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject MSmerge_genhistory
> if @.@.ERROR <> 0
> goto Error
> --grant select on MSmerge_genhistory to public
> end
> if not exists (select * from sysobjects where name =
> 'MSmerge_delete_conflicts')
> begin
> raiserror('Creating table MSmerge_delete_conflicts',0,1)
> create table dbo.MSmerge_delete_conflicts
> (
> tablenick int NOT NULL,
> rowguid uniqueidentifier rowguidcol NOT NULL,
> origin_datasource nvarchar(255) NULL,
> conflict_type int NULL,
> reason_code int NULL,
> reason_text nvarchar(720) NULL,
> pubid uniqueidentifier NULL,
> create_time datetime not null default getdate()
> )
> if @.@.ERROR <> 0
> goto Error
> else
> begin
> create clustered index uc1MSmerge_delete_conflicts on
> MSmerge_delete_conflicts(tablenick, rowguid)
> if @.@.ERROR <> 0
> goto Error
> end
> CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_delete_conflicts
> ON MSmerge_delete_conflicts(tablenick, rowguid, origin_datasource)
> if @.@.ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject MSmerge_delete_conflicts
> if @.@.ERROR <> 0
> goto Error
> --grant select on MSmerge_delete_conflicts to public
> end
> if not exists (select * from sysobjects where name =
> 'sysmergeschemachange')
> begin
> raiserror('Creating table sysmergeschemachange',0,1)
> create table dbo.sysmergeschemachange
> (
> pubid uniqueidentifier NOT NULL,
> artid uniqueidentifier NULL,
> schemaversion int NOT NULL,
> schemaguid uniqueidentifier NOT NULL,
> schematype int NOT NULL,
> schematext nvarchar(2000) NOT NULL
> )
> if @.@.ERROR <> 0
> goto Error
> else
> begin
> create unique clustered index schemachangeversion on
> sysmergeschemachange(schemaversion, pubid)
> if @.@.ERROR <> 0
> goto Error
> end
> exec dbo.sp_MS_marksystemobject sysmergeschemachange
> if @.@.ERROR <> 0
> goto Error
> --grant select on sysmergeschemachange to public
> end
> if not exists (select * from sysobjects where name =
> 'sysmergesubsetfilters')
> begin
> raiserror('Creating table sysmergesubsetfilters',0,1)
>
> create table dbo.sysmergesubsetfilters (
> filtername sysname NOT NULL,
> join_filterid int identity NOT NULL,
> pubid uniqueidentifier NOT NULL,
> artid uniqueidentifier NOT NULL,
> art_nickname int NOT NULL,
> join_articlename sysname NOT NULL,
> join_nickname int NOT NULL,
> join_unique_key int NOT NULL,
> expand_proc sysname NULL,
> join_filterclause nvarchar(1000) NULL
> )
> if @.@.ERROR <> 0
> goto Error
> CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters
> ON sysmergesubsetfilters(join_filterid, pubid)
> if @.@.ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject sysmergesubsetfilters
> if @.@.ERROR <> 0
> goto Error
> --grant select on sysmergesubsetfilters to public
> end
> if @.@.error <> 0
> goto Error
> if not exists (select * from sysobjects where name =
> 'MSdynamicsnapshotviews')
> begin
> raiserror('Creating table MSdynamicsnapshotviews',0,1)
> create table dbo.MSdynamicsnapshotviews (
> dynamic_snapshot_view_name sysname primary key
> )
> if @.@.ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews
> end
> if not exists (select * from sysobjects where name =
> 'MSdynamicsnapshotjobs')
> begin
> raiserror('Creating table MSdynamicsnapshotjobs',0,1)
> create table dbo.MSdynamicsnapshotjobs (
> id int identity,
> name sysname not null unique,
> pubid uniqueidentifier not null,
> job_id uniqueidentifier not null,
> dynamic_filter_login sysname null,
> dynamic_filter_hostname sysname null,
> dynamic_snapshot_location nvarchar(255) not null
> )
> if @.@.ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs
> create unique clustered index uciMSdynamicsnapshotjobs on
> dbo.MSdynamicsnapshotjobs(job_id, pubid)
> if @.@.ERROR <> 0
> goto Error
>
> end
> if @.@.error <> 0
> goto Error
> if not exists (select * from sysobjects where name =
> 'MSmerge_altsyncpartners')
> begin
> raiserror('Creating table MSmerge_altsyncpartners',0,1)
> create table dbo.MSmerge_altsyncpartners (
> subid uniqueidentifier not null,
> alternate_subid uniqueidentifier not null,
> description nvarchar(255) NULL
> )
> if @.@.ERROR <> 0
> goto Error
> exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners
> create unique clustered index uciMSmerge_altsyncpartners on
> dbo.MSmerge_altsyncpartners(subid, alternate_subid)
> if @.@.ERROR <> 0
> goto Error
>
> end
>
> if exists (select * from master..sysobjects where type='P' and
> name='sp_MScreatedebuginfrastructure')
> begin
> declare @.retval int
> exec @.retval= dbo.sp_MScreatedebuginfrastructure
> if @.@.ERROR <> 0 or @.retval <> 0 goto Error
> end
> commit transaction
> return (0)
> Error:
> if @.@.trancount > 0
> begin
> ROLLBACK TRANSACTION MScreate_mergesystables
> COMMIT TRANSACTION
> end
> RAISERROR (20008, 16, -1)
> return (1)
|||No, this was just a test/develope server so I can set it back up by
reinstalling SQL. But this is the second time it's happened, I can
duplicate it easily, I would just like to find out what's causing it so that
it doesn't happen on our production server.
|||Then definitely post something to reproduce it. I've hit this same thing
several times in the past and hav never been able to reliably reproduce it.
I can get it to reoccur, but not reproduce when I want it to. It is related
to doing many cycles of putting replication in and taking it back out. I've
always fixed this by going in and explicitly recreating the table(s). BOL
has all of the column definitions listed.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Heather B." <hbaker@.pro-merchnospam.com> wrote in message
news:0a44768a00d845729724b964d5f84646@.ureader.com. ..
> No, this was just a test/develope server so I can set it back up by
> reinstalling SQL. But this is the second time it's happened, I can
> duplicate it easily, I would just like to find out what's causing it so
> that
> it doesn't happen on our production server.
|||Try running this stored procedure against each database that you have
deleted merge replication from:
Use [Your DataBase Name]
EXEC sp_removedbreplication
Go
|||Try running the following against every database where you have deleted
Merge Replication:
Use [DataBaseName]
EXEC sp_removedbreplication
go
*** Sent via Developersdex http://www.codecomments.com ***
sql

Invalid Object Name

I have merge replication setup on MY SLQ server 2000 service pack 2 server.

I was having no issue with replication for the past three month however now I am getting this error when I try to update a record in the database.

In\valid Object Nmae 'ctsv_.....'

I am not sure what is wong with it. Any help or suggestion.

Thanks

Tanweer

you have to let us know what changes were made to your publication. Do you know if the object does in fact exist?|||

No structure changes were made however our system regenerate sanpshot weekly should I turned it off.

Thanks

Tanweer

|||If this worked previously, but not now, something must have changed. Either that or you should describe your scenario in more detail. Does the object exist in question exist?

Invalid Object Name

I have merge replication setup on MY SLQ server 2000 service pack 2 server.

I was having no issue with replication for the past three month however now I am getting this error when I try to update a record in the database.

In\valid Object Nmae 'ctsv_.....'

I am not sure what is wong with it. Any help or suggestion.

Thanks

Tanweer

you have to let us know what changes were made to your publication. Do you know if the object does in fact exist?|||

No structure changes were made however our system regenerate sanpshot weekly should I turned it off.

Thanks

Tanweer

|||If this worked previously, but not now, something must have changed. Either that or you should describe your scenario in more detail. Does the object exist in question exist?sql

Invalid Instance Name while trying to install MSDE

I am running XP SP2. I have downloaded MSDE (SQL Sever 2000) to put on my
laptop for training purposes. When I try to load the setup program I get an
error message saying "The Instance Name Specified Is Invalid" and then when I
acknowledge the message the install exits. I have searched for and tried to
understand the post that seem to relate to my problem but I am just not
knowledgable enough to understand what to do. If someone would please tell me
(in baby steps) how to fix this problem so I can install MSDE then I would
really appreciate it. If you need ANY additional information please feel free
to contact me and I will respond immediately. Your time and attention is very
much appreciated.
Thank you for your help...premium001
Read into the readme file in your extracted folder.
If there is an instance of SQL Server already installed, the MSDE should be
named.
"premium001" wrote:

> I am running XP SP2. I have downloaded MSDE (SQL Sever 2000) to put on my
> laptop for training purposes. When I try to load the setup program I get an
> error message saying "The Instance Name Specified Is Invalid" and then when I
> acknowledge the message the install exits. I have searched for and tried to
> understand the post that seem to relate to my problem but I am just not
> knowledgable enough to understand what to do. If someone would please tell me
> (in baby steps) how to fix this problem so I can install MSDE then I would
> really appreciate it. If you need ANY additional information please feel free
> to contact me and I will respond immediately. Your time and attention is very
> much appreciated.
> --
> Thank you for your help...premium001
|||hi,
premium001 wrote:
> I am running XP SP2. I have downloaded MSDE (SQL Sever 2000) to put
> on my laptop for training purposes. When I try to load the setup
> program I get an error message saying "The Instance Name Specified Is
> Invalid" and then when I acknowledge the message the install exits. I
> have searched for and tried to understand the post that seem to
> relate to my problem but I am just not knowledgable enough to
> understand what to do. If someone would please tell me (in baby
> steps) how to fix this problem so I can install MSDE then I would
> really appreciate it. If you need ANY additional information please
> feel free to contact me and I will respond immediately. Your time and
> attention is very much appreciated.
as <zhaounknown> already wrote, please read the proviede ReadmeFile... it
explains you have to provide a set of parameters to the setup.exe boostrap
installer..
typically you have to provide the SAPWD="your strong password" as you are
forced to protect "sa" login ...
you can specify if you like to enable network connections
(DISABLENETWORKPROTOCOLS=0), if you like to permit standard SQL Server
authenticated connections (SECURITY MODE=SQL) as the default does not permit
them to be authenticated, or if you like to install a named instance
(INSTANCENAME="the name").. and please enable verbose logging of the
installation task providing the /L*v "c:\Msde.txt" parameter, which results
in an about 2Mb text file you can inspect for
RETURN VALUE 3
entries indicating troubles... about 10/20 lines before and/or after each
entry some (sometime cryptic) description of the problem will be logged..
so, if you like to install a named instance (named "MSDE") with enabled
networkprotocols and enabling SQL Server authenticated connections you have
to provide something similar to
C:\MSDERelaA\>setup.exe INSTANCENAME="MSDE" SECURITY MODE=SQL
DISABLENETWORKPROTOCOLS=0 SAPWD="your strong password" /L*v "c:\Msde.txt"
all on the same line from the command promt
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Invalid Descriptor Index

Hi,
I have setup snapshot pull replication between SQL2000 (publisher) and
MSDE2000 (subscriber).
The following error "Invalid Descriptor Index" is occuring on the
subscriber. Would someone please tell me what this error means and how to
get around it?
Thanks
reapply sp3 on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mm" <postto@.news.com> wrote in message
news:u8luytqlEHA.2492@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have setup snapshot pull replication between SQL2000 (publisher) and
> MSDE2000 (subscriber).
> The following error "Invalid Descriptor Index" is occuring on the
> subscriber. Would someone please tell me what this error means and how to
> get around it?
> Thanks
>

invalid character value on 7000th record

Our developers are trying to pinpoint why a function keeps bombing out
(email below). The database was created using the same setup as other
dbs, none of which have had this problem. I ran a trace, which showed
several Sort Warnings before the process stopped, but no error
messages. The process seems to be a complex query for data, which is
then loaded into a table.
Any suggestions?
I am trying to debug a problem with some data conversion (from a dbf
file into a SQL table) For some reason we get this once we have loaded
our 7000th record. It is not a problem with the record, not always the
same one, has something to do with the limit. Not sure why 7000, but
always crashes there. I have tried everything on the code side.
Is there any setting in SQL that my enforce some limits on data loading
or on the store call, maybe something odd with this table?
"Underlying DBMS error[Microsoft OLE DB Provider for SQL Server:
Invalid character value for cast specification. (.dbo.a109)]"hi,
If you try to move that table using an ETL tool as DTS, inside the pump you
can define how many error as maximum you want to pass.
"naomi" wrote:

> Our developers are trying to pinpoint why a function keeps bombing out
> (email below). The database was created using the same setup as other
> dbs, none of which have had this problem. I ran a trace, which showed
> several Sort Warnings before the process stopped, but no error
> messages. The process seems to be a complex query for data, which is
> then loaded into a table.
> Any suggestions?
>
> I am trying to debug a problem with some data conversion (from a dbf
> file into a SQL table) For some reason we get this once we have loaded
> our 7000th record. It is not a problem with the record, not always the
> same one, has something to do with the limit. Not sure why 7000, but
> always crashes there. I have tried everything on the code side.
> Is there any setting in SQL that my enforce some limits on data loading
> or on the store call, maybe something odd with this table?
> "Underlying DBMS error[Microsoft OLE DB Provider for SQL Server:
> Invalid character value for cast specification. (.dbo.a109)]"
>

Intersecting Subtotals

I am trying to eliminate the value displayed in an intersecting
subtotal in a matrix. I have a subtotal setup for both the row and
column where the total of the column totals across the bottom isn't
appropriate to be totaled.
Is anyone aware of a way to identify this intersecting cell and change
it to a non-display field or to change the text color to white so it
isn't visible?
ThanksThis will identify all 4 versions of a cell:
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
You can use it to change the background colour too. I have a matrix with
both row and column subtotal, and like you, I can't get the calculation to
work out for the intercepting cell. My cell expression looks like this:
=iif(InScope("matrix2_DateWeek_Year"),
iif(InScope("matrix2_DateWeek_Week"),
SUM(Fields!Measures_Billable_Hours.Value),
SUM(Fields!Measures_Billable_Hours.Value)),
iif(InScope("matrix2_DateWeek_Week"),
(First(Fields!Measures_Billable_Hours.Value, "matrix2_DateWeek_Week") -
Last(Fields!Measures_Billable_Hours.Value, "matrix2_DateWeek_Week")),
"x"))
The x shows up in the bottom right corner.
Kaisa M. Lindahl
"GregR" <grinard@.mesanetworks.net> wrote in message
news:1137777096.339565.269760@.z14g2000cwz.googlegroups.com...
>I am trying to eliminate the value displayed in an intersecting
> subtotal in a matrix. I have a subtotal setup for both the row and
> column where the total of the column totals across the bottom isn't
> appropriate to be totaled.
> Is anyone aware of a way to identify this intersecting cell and change
> it to a non-display field or to change the text color to white so it
> isn't visible?
> Thanks
>

Internet Replication

I am attempting to setup a publication where I'm replicating date between 2
sql 2000 servers via the internet. The snapshots are configured to be written
to ftp to be downloaded by the untrusted subscribers via ftp. I'm having
troubles with not having access to the distributor, this wasnt an issue until
I restricted access to RPC on the firewall seperating the
distributor/publication and the subscriber. The subscribers get the error
'the process could not connect to Distributor <SERVER>'
Is there a way to give access the distributor via ftp (or some other way)
and how is this done?
Do particular types of replication require access to the distributor (can I
get away with no distributor access)?
If I setup up an updating subscription, what is the best way to achieve an
updating subscription over the internet without a vpn?
Any help would be greatly appreciated.
-James
Nobody can help?
"James" wrote:

> I am attempting to setup a publication where I'm replicating date between 2
> sql 2000 servers via the internet. The snapshots are configured to be written
> to ftp to be downloaded by the untrusted subscribers via ftp. I'm having
> troubles with not having access to the distributor, this wasnt an issue until
> I restricted access to RPC on the firewall seperating the
> distributor/publication and the subscriber. The subscribers get the error
> 'the process could not connect to Distributor <SERVER>'
> Is there a way to give access the distributor via ftp (or some other way)
> and how is this done?
> Do particular types of replication require access to the distributor (can I
> get away with no distributor access)?
> If I setup up an updating subscription, what is the best way to achieve an
> updating subscription over the internet without a vpn?
> Any help would be greatly appreciated.
> -James