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)

Invoice dimension - best way to model it

We have a system that will have ~15 million invoices per year. We would like to track lots of very unique attributes of each invoice (invoice number, etc). Currently we are using a junk dimension ([Dim Invoice Attribute]) that contains 4 or 5 of the attributes, but our changes will require going to the grain of the invoice table. I have read having a physical dimension table that has a 1:1 cardinality to the fact table isn't a good idea (strictly because of the size of the table and the operation of joining fact to dimension). Currently we have 2 or 3 attributes of the invoice in the fact table (degenerate dimensions) in order to fulfill relational queries for tying things back to the MDX results.

My question is what is the best way to model this? I have read both sides of the story - create an invoice dimension table, and store everything there with keys relating back to the fact table, and also store all of the attributes in the fact table and create a named query in the .dsv that selects out only the attributes for the [Dim Invoice] dimension, thus saving space and join energies during processing.

Can anyone shed some light on this?

Thank you in advance,

John Hennesey

Typically in this situation, you have a fact table that represents the invoicing process. One of the dimensions is the invoice which will most likely have a one-to-one relationship with the fact. Does this describe your model?

Kimball would describe this as a degenerate dimension and would recommend you move the dimension into the fact table. SSAS provides support for this through a fact dimension. (See Books Online topic "Dimension Relationships" for a more detailed description.)

So this works if we're talking about a fact dimension with just a few attributes. After that, the model starts to look a little sloppy. I would look at the cardinality of some of these other attributes. If the cardinality is relatively low, you may want to move these into their own dimensions or if the cardinality is very low lump them into a junk dimension.

The other thing I would consider is the user's experience. How often will users be accessing these other dimensions created to hold those attributes? If these are presented separate from the Invoice dimension, would these seem weird or be cumbersome for your user? Do you plan on providing user-hierarchies that role your invoice number up to these attributes? If not, then I'd recommend going ahead with moving them out.

From a performance standpoint, I don't think your design choice has too big an impact on SSAS. If you figure that every attribute hierarchy is given its own storage and the only thing that would be different internally would be the relationship maps. The impact of having or not having these depends on how you intend to use your attributes.

So, I guess my general recommendation would be keep to standard, Kimball principles and put an emphasis on end-user experience. If you run into a performance problems down the road, you should be able to easily tweak the model.

If others folks have thoughts on this, esp regarding how the SSAS engine handles this stuff, please, please chime in.

Thanks,
Bryan

|||

I agree with the approach of keeping the invoice attributes in the fact table.

One issue is the number of invoices(metadata) that will be downloaded to the client when they use the invoice dimension. This will be a really slow process if you show to many invoice members and the solution is to create artificial levels above the invoice number. I build the first level with the first character in the invoice number using the TSQL-left function in the data source view.. A guide line is to have no more than 10-15 groups on each level. On the leaf level you have have larger groups.

I have asked the SSAS2005 development team about any performance issues with building a fact table dimension and they have no negative impact.

HTH

Thomas Ivarsson

|||

Interesting - I have heard arguments on both sides of the argument. To implement a fact table dimension, it would be using a named query that goes against the fact table, right? Is there a special way to set up a fact table dimension (any attributes of the dimension itself?) I have also read in just about every article it is necessary to set up a hierarchy to limit the number of members returned to the client - indeed a very good idea.

Thank you all for your input,

John Hennesey

|||

Hello John! Or use named queries in the fact table for each artifical level in the invoice hierarchy/dimension.

Good luck!

/Thomas

Inventory system.. Reads and writes

How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>

Inventory system.. Reads and writes

How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>> How does one go about building a highly transactional inventory system
>> where one goes about searching , adding and decrementing inventory..?
>> Can one share their high level architectural design ?
>> I am afraid of intensive blocking . When one goes about buying an item
>> from an inventory, how do you prevent others from not seeing it or even
>> buying it ?
>> Would really love to hear how this is implemented ?
>>
>>
>

Inventory system.. Reads and writes

How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?
Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>
|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>
sql

Inventory Stock, Triggers vs Views/SP

Hello..

I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?

1st Design

PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price

PURCHASE TABLE
PurchaseID
Date
...etc...

PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...

and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE

2nd Design

PRODUCT TABLE
ItemID
ItemName
Price
...etc...

INVENTORY TABLE
ItemID
QtyBegin
...etc...

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...

and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:

QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ......

And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.

According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?

Thanks a lot.Hi

I would suggest to have design 2 as my option. Performance without reliability is of no use. There is no meaning in giving a wrong information to the user.

In the first design, you are storing the stock of each item whenever there is a movement of the product, either inward or outward. As per your design, let us assume, initially you are having a product, Product A, with no stock. Now you make a purchase on 01/23/2007 for quantity 50. Now the QtyOnHand will have value 50. Suppose you make a sales on 01/25/2007 for 30, as per your design you will update the QtyonHand field, which will now become 20. Now, if I need to get the stock of Product A on 01/04/2007, I cannot use the value in the field QtyonHand, which will be wrong. In this case it will be 20. But actually it must be 50. So we need to calculate the stock by adding the inwards and deducting the outwards. Also if you allow backdate billing, stock updation will become a serious issue.

In the second design, I accept that the performance will be lesser than the first one. But by careful designing that can also be solved. For eg, having a seperate table which contains a date field, Product key and QtyonHand fields. For each purchase, sales and other product movement transactions, you can update this table for each day. There can be several other methods as well. If you come across please intimate me as well.

with regards

Abdur Raoof M|||For what it's worth...
No matter which solution you choose, don't forget to leave room to account for stock transactions that are not
related to sales and receipts, such as damage or shrink.
Invariably, Beginning inventory-sales+purchases<>actual ending inventory.|||@.Abdul Raoof

I very much agree that the 2nd one is far more reliable, but I am still wondering how much more do I have to pay for the reliability? I have done the 2nd one before, and the difference to load the 'item' table without calculating qty compared to load a view that calculates the QtyOnHand with many tables related (although there are only 2 tables with more than 30000 rows) is about 1 sec.

And thanks for minding me the problem of the 1st design, never thought of that before.

@.RedNeckGeek
I think I have included Adjustment Table if that's what you mean?
Beginning inventory-sales+purchases-adjustment = actual ending inventory :)

Thank you Guys.

Anyone? Please... feel free to comment...

Invalid object name while reading data out of an SQL Database

Hi all,
I'm a complete newbie on ASP.Net.

I want to get some data out of a SQLserver Database running on my system with SQL Server 2005 Express. The name of the Database is 'tempdb' and the table is called "Members". the SQLServer runs as Local System with the Windows account.

When I try to open the site, I always get the same error:
Invalid object name 'Members'

I don't know what to do anymore. I read a post, where anybody set the rights for the owner, but my database is running with the Windows account.

Here is the Code of the page so far:

<%@. Page Language="VB" Debug="True" Strict="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SQlClient" %>
<script runat="server">
Sub Page_Load (ByVal Sender As Object, _
 ByVal E As EventArgs)
 Dim connStr As String
 connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
 connStr += "database=tempdb;"
 connStr += "Truster_Connection=yes"
 Dim conn As New SQLConnection(connStr)
 conn.Open()
 Dim sql As String
 sql = "SELECT COUNT (*) FROM Members"
 Dim cmd As New SQLCommand(sql, conn)
 Dim ergebnis As String
 ergebnis = cmd.ExecuteScalar().toString()
 Dim t As String
 t = "Die Tabelle Members hat " & _
 ergebnis & " Zeilen. <br>" & _
 "Das Kommando lautet: " & _
 cmd.CommandText & "<br>" & _
 "Der Kommandotyp ist: " & _
 cmd.CommandType
 ausgabe.innerHTML = t
End Sub
</script>
<html><head><title>
Demo zu SQLCommand.ExecuteScalar
</title></head>
<body>
<h3>Demo zu SQLCommand.ExecuteScalar</h3>
<p runat="server" id="ausgabe" />
</body></html>

Thanks for your help an sorry for my english.

Greets
Flash_Prince

The problem is objects in tempdb is valid for limited time so your object the Member table does not exist. Microsoft provided the tempdb for SQL Server to be used to proccess complex queries and test queries so when you create a table if you did not drop it SQL Server will drop it at some point. Try the links below to download sample databases you can create and use with SQL Server 2005. The databases comes with tables but you can create your own tables so you can add the member table to any of the three database. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

|||

The problem is, that i want to use the database without entering the path of the database file in the sourcecode. When I use the nordwind database, all the sql queries are working very well. But if I use the Nordwind Database, I only know to connect by OleDB.

Can you tell me how to use a database in the way decribed above with a sql connection?

I hope, this is described understandable, cause my english is not the best.

|||

You need to create connection string and most of the information you need is covered in the two links below. Hope this helps.

http://www.connectionstrings.com

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

|||The problem is in your connection string. You have a typo: "Truster_Connection=yes" should be "Trusted_Connection=yes".

Invalid object name 'syspublications'

Before reinstalling sql server and restoring old system databases I
forgot to delete and replication that was no longer needed. Now,
Expired Subscriptions Clean Up job fails with the following error:
Error 208: Invalid object name 'syspublications'. I don't need this
replication and would like to delete that completed but everytime I
try I get the same error message.
Please advise how can I delete all manually.
Thank you,
T.
Locate the problem database. do this sp_replicationdboption 'Problem
Database','Publish','false'
http://www.zetainteractive.com - Shift Happens!
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
"T" <T@.discussions.microsoft.com> wrote in message
news:20736A29-69AF-4B7E-8BB8-C5B1BF5313CF@.microsoft.com...
> Before reinstalling sql server and restoring old system databases I
> forgot to delete and replication that was no longer needed. Now,
> Expired Subscriptions Clean Up job fails with the following error:
> Error 208: Invalid object name 'syspublications'. I don't need this
> replication and would like to delete that completed but everytime I
> try I get the same error message.
> Please advise how can I delete all manually.
> Thank you,
> T.
|||This is what I get:
Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
"Hilary Cotter" wrote:

> Locate the problem database. do this sp_replicationdboption 'Problem
> Database','Publish','false'
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "T" <T@.discussions.microsoft.com> wrote in message
> news:20736A29-69AF-4B7E-8BB8-C5B1BF5313CF@.microsoft.com...
>
>
|||is this table there? if not run this script in the problem database.
We may need to go back and forth to add the necessary objects a few times.
CREATE TABLE [dbo].[syspublications](
[description] [nvarchar](255) NULL,
[name] [sysname] NOT NULL,
[pubid] [int] IDENTITY(1,1) NOT NULL,
[repl_freq] [tinyint] NOT NULL,
[status] [tinyint] NOT NULL,
[sync_method] [tinyint] NOT NULL,
[snapshot_jobid] [binary](16) NULL,
[independent_agent] [bit] NOT NULL,
[immediate_sync] [bit] NOT NULL,
[enabled_for_internet] [bit] NOT NULL,
[allow_push] [bit] NOT NULL,
[allow_pull] [bit] NOT NULL,
[allow_anonymous] [bit] NOT NULL,
[immediate_sync_ready] [bit] NOT NULL,
[allow_sync_tran] [bit] NOT NULL,
[autogen_sync_procs] [bit] NOT NULL,
[retention] [int] NULL,
[allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
[snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
[alt_snapshot_folder] [nvarchar](255) NULL,
[pre_snapshot_script] [nvarchar](255) NULL,
[post_snapshot_script] [nvarchar](255) NULL,
[compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
[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,
[allow_dts] [bit] NOT NULL DEFAULT ((0)),
[allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
[centralized_conflicts] [bit] NULL,
[conflict_retention] [int] NULL,
[conflict_policy] [int] NULL,
[queue_type] [int] NULL,
[ad_guidname] [sysname] NULL,
[backward_comp_level] [int] NOT NULL DEFAULT ((10)),
[allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
[min_autonosync_lsn] [binary](10) NULL,
[replicate_ddl] [int] NULL DEFAULT ((1)),
[options] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
http://www.zetainteractive.com - Shift Happens!
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
"T" <T@.discussions.microsoft.com> wrote in message
news:1C8FA2D9-4811-4975-81C8-EE744C25C7D4@.microsoft.com...[vbcol=seagreen]
> This is what I get:
> Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
> Invalid object name 'syssubscriptions'.
>
> "Hilary Cotter" wrote:
|||Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
"Hilary Cotter" wrote:

> is this table there? if not run this script in the problem database.
> We may need to go back and forth to add the necessary objects a few times.
> CREATE TABLE [dbo].[syspublications](
> [description] [nvarchar](255) NULL,
> [name] [sysname] NOT NULL,
> [pubid] [int] IDENTITY(1,1) NOT NULL,
> [repl_freq] [tinyint] NOT NULL,
> [status] [tinyint] NOT NULL,
> [sync_method] [tinyint] NOT NULL,
> [snapshot_jobid] [binary](16) NULL,
> [independent_agent] [bit] NOT NULL,
> [immediate_sync] [bit] NOT NULL,
> [enabled_for_internet] [bit] NOT NULL,
> [allow_push] [bit] NOT NULL,
> [allow_pull] [bit] NOT NULL,
> [allow_anonymous] [bit] NOT NULL,
> [immediate_sync_ready] [bit] NOT NULL,
> [allow_sync_tran] [bit] NOT NULL,
> [autogen_sync_procs] [bit] NOT NULL,
> [retention] [int] NULL,
> [allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
> [snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
> [alt_snapshot_folder] [nvarchar](255) NULL,
> [pre_snapshot_script] [nvarchar](255) NULL,
> [post_snapshot_script] [nvarchar](255) NULL,
> [compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
> [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,
> [allow_dts] [bit] NOT NULL DEFAULT ((0)),
> [allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
> [centralized_conflicts] [bit] NULL,
> [conflict_retention] [int] NULL,
> [conflict_policy] [int] NULL,
> [queue_type] [int] NULL,
> [ad_guidname] [sysname] NULL,
> [backward_comp_level] [int] NOT NULL DEFAULT ((10)),
> [allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
> [min_autonosync_lsn] [binary](10) NULL,
> [replicate_ddl] [int] NULL DEFAULT ((1)),
> [options] [int] NOT NULL DEFAULT ((0))
> ) ON [PRIMARY]
>
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "T" <T@.discussions.microsoft.com> wrote in message
> news:1C8FA2D9-4811-4975-81C8-EE744C25C7D4@.microsoft.com...
>
>
sql

Invalid object name sysperfinfo

When I execute this statement through ASP.NET

select cntr_value FROM sysperfinfo

I get this error,

System.Data.SqlClient.SqlException: Invalid object name 'sysperfinfo'.

Any ideas why?

It sounds to me like your connectionstring (the string that points to the database which you are querying) is pointing to a database that does not have a table entitled 'sysperfinfo'|||

To add to the previous post some things have changes about sysperfinfo. Try the links below for more. Hope this helps.

sysperfinfo

In SQL Server 2005,sysperfinfo returns abigint value for thecntr_value column. Modify applications that usesysperfinfo to make sure that they can handle thebigint values of thecntr_value column.

In SQL Server 2005,sysperfinfo is a compatibility view. You should use thesys.dm_os_performance_counters dynamic management view instead.

http://msdn2.microsoft.com/en-us/library/ms143179.aspx

http://www.sqlservercentral.com/columnists/jsack/troubleshootingsqlserverwiththesysperfinfotable.asp

|||

Maybe you're in the wrong database, try:

select cntr_value FROM master..sysperfinfo

Invalid authorization specification

MS OLE DB Provider for SQL Server Error: Invalid authorization specification
This is a basic 3-tier system, front end, data broker, database. The front
end software is written in VB 6. There are many front end users. This
error is coming up on one computer only. No users can access the database
through the software from this machine. But, all users can access the
database through the software from all other machines. This machine is WinXP.
Any ideas where to look? The software and connection code is the same on
all machines. What could have changed on this one machine (because it did
work before) that would cause only it to stop working properly?
Thanks.
Does your application use a DSN to connect to SQL Server? If so ensure this
DSN is setup correctly on the client machine.
This error is similar to the "login failed for user <usr>" Thus you should
troubleshoot it similarly. For instrance, try connecting from this client
using both SQL and NT authentication. To simplify troubleshooting, try
taking your application out of the picture by testing UDL file connections
and from odbc administrator(test dsn). Is the same error displayed when
testing connectivity using these client tools?
Also see KB: How to troubleshoot connectivity issues in SQL Server 2000 -
ID: 827422 9available at http://support.microsoft.com)
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Invalid authorization specification

MS OLE DB Provider for SQL Server Error: Invalid authorization specification
This is a basic 3-tier system, front end, data broker, database. The front
end software is written in VB 6. There are many front end users. This
error is coming up on one computer only. No users can access the database
through the software from this machine. But, all users can access the
database through the software from all other machines. This machine is WinX
P.
Any ideas where to look? The software and connection code is the same on
all machines. What could have changed on this one machine (because it did
work before) that would cause only it to stop working properly?
Thanks.Does your application use a DSN to connect to SQL Server? If so ensure this
DSN is setup correctly on the client machine.
This error is similar to the "login failed for user <usr>" Thus you should
troubleshoot it similarly. For instrance, try connecting from this client
using both SQL and NT authentication. To simplify troubleshooting, try
taking your application out of the picture by testing UDL file connections
and from odbc administrator(test dsn). Is the same error displayed when
testing connectivity using these client tools?
Also see KB: How to troubleshoot connectivity issues in SQL Server 2000 -
ID: 827422 9available at http://support.microsoft.com)
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx