How do I mimic autoNumber for non-identity columns?

Assume I have an inventory system used by several customers. Each customer
wants each item in their inventory to have a number, and they don't want any
gaps.
So, there's an Item table that has information about each item, as well as
the customer the item belongs to. When an item is inserted, I cannot use an
Identity column to autonumber the ItemId because if a customer inserts an
item, then a second customer inserts a hundred items, then the original
customer inserts another item, there is a gap of one hundred items from the
perspective of the original customer. This isn't desired behavior.
I need a way to do a per-customer autonumber, but I don't know how to do
this without running into concurrency problems.Greg, If I understood you properly you need
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
WHERE custid=.....
INSERT INTO AnothetTable VALUES (@.max_item)
COMMIT TRAN
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
> Assume I have an inventory system used by several customers. Each
> customer
> wants each item in their inventory to have a number, and they don't want
> any
> gaps.
> So, there's an Item table that has information about each item, as well as
> the customer the item belongs to. When an item is inserted, I cannot use
> an
> Identity column to autonumber the ItemId because if a customer inserts an
> item, then a second customer inserts a hundred items, then the original
> customer inserts another item, there is a gap of one hundred items from
> the
> perspective of the original customer. This isn't desired behavior.
> I need a way to do a per-customer autonumber, but I don't know how to do
> this without running into concurrency problems.|||If Table can be the same as AnotherTable, I think this could work. So,
assuming ItemNumber is the column I want to mimic autonumber on, we'd have:
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(ItenNumber),0) FROM Inventory WITH
(UPDLOCK,HOLDLOCK)
WHERE custid=4
INSERT INTO Inventory VALUES (@.max_item + 1)
COMMIT TRAN
Would that work? Are UPDLOCK and HOLDLOCK merely hints? What if the hints
get ignored?
Thanks.
"Uri Dimant" wrote:

> Greg, If I understood you properly you need
> DECLARE @.max_item
> BEGIN TRAN
> SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
> WHERE custid=.....
> INSERT INTO AnothetTable VALUES (@.max_item)
> COMMIT TRAN
>
>
> "Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
> news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
>
>

How do I loop thru a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

SELECT id, label, url, sort
FROM mainNav
ORDER BY sort

Set @.tc = @.@.rowcount

while @.i <= @.tc

begin
Set @.i = (@.i + 1)

/* Select for submenu items*/
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end

RETURNI'm thinking that what you really want to do is perform a join and then handle the presentation stuff on the client side. I can't think of any valid reason why you would want to write your stored proc in the manner you have outlined above.

SELECT
m.id as MainID,
m.label as MainLabel,
m.url as MainUrl,
m.sort as MainSort,
s.id,
s.label,
s.url,
s.sort
FROM
mainNav m inner join SubNav s
ORDER BY
m.sort,
s.sort

Regards,

hmscott|||Yeah, you definitely seem hazy on the SQL concept. What is the output format you want for your dropdown list? Give us a sample of the data you want to display.|||The objective is to return a .Net dataset that contains a series of recordsets . The first recordset is the items contained in the top bar of the menu site. The subsequest recordsets contain the submenus for each item in the top menu.

The challenge is that it's not a simple binding issue once the data is returned from the stored proc. Some items in the top bar may not have a submenu and therefore require different html and javascript.

Inorder to render a lightweight, css-based (and W3C compliant) menu system, I need to determine at runtime which items have submenus and which don't

- items with no submenu need to have code that only closes other menus
- items with submenus need to have the closing code and also code to open there respective submenu.

A working (static) example would be something like http://peelcas.org/home/index.aspx

In order to pull this off, I have a c# routine that loops thru the dataset and renders the html.

Here's the c# code

///////////////////////////
// build global menu system
///////////////////////////

string sqlConnstring = ConfigurationManager.ConnectionStrings["sqlConnString"].ConnectionString;
DataSet NavData = new DataSet();
NavData = SqlHelper.ExecuteDataset(sqlConnstring, CommandType.StoredProcedure, "OPA_GetMenuItems");
//Response.Write(NavData.Tables.Count.ToString());

int i = 1; // counter for looping thru tables collection
int tc = NavData.Tables.Count - 1;
System.Text.StringBuilder sbNavLinks = new StringBuilder();
System.Text.StringBuilder sbSubLinks = new StringBuilder();
string url;
//string webSectionName;
string label;
string anchorId;
string menuId;

// build main nav bar
sbNavLinks.Append("<div id=\"navBar\"><ul>\r");
while (i < tc)
{
label = NavData.Tables[0].Rows[i].ItemArray[1].ToString();
anchorId = NavData.Tables[0].Rows[i].ItemArray[3].ToString();
url = NavData.Tables[0].Rows[i].ItemArray[2].ToString();

// no submenu items for this web section therefore...
// ...create main nav bar link with global menu closing javascript only
if (NavData.Tables[i].Rows.Count == 0)
{
sbNavLinks.Append("<li><a href=\""
+ url + "\" onmouseover=\"P7_autoLayers(0);\">"
+ label + "</a></li>\r");
}
// has submenu items therefore...
// ...create main navbar link with submenu opening javascript
else
{
// main navbar link
sbNavLinks.Append("<li><a href=\""
+ url + "\""
+ " id=\"Anchor" + anchorId + "\""
+ " onmouseover=\"P7_autoLayers(0,'subMenu" + anchorId + "');"
+ "P7_Snap('Anchor" + anchorId + "','subMenu" + anchorId + "',0,24);"
+ "\">"
+ label + "</a></li>\r");
}
i++;
}
sbNavLinks.Append("</ul></div>\r");
navBar.Text = sbNavLinks.ToString();

// build submenus

int j = 0; // counter for looping thru rows in current table
int rc; // row count of current table
i = 1;
while (i < tc)
{
if (NavData.Tables[i].Rows.Count > 0)
{
// extract menu id for use in div id

menuId = NavData.Tables[i].Rows[0].ItemArray[4].ToString();
//Response.Write(webSectionSort);

sbSubLinks.Append("<div id=\"subMenu" + menuId + "\" style=\"position:absolute; z-index:" + menuId + "; visibility: hidden;\">\r");
sbSubLinks.Append(" <div class=\"subButton\">\r");
sbSubLinks.Append(" <ul>\r");

rc = NavData.Tables[i].Rows.Count -1;

while (j <= rc)
{
// extract data for this link
label = NavData.Tables[i].Rows[j].ItemArray[1].ToString();
url = NavData.Tables[i].Rows[j].ItemArray[0].ToString();

sbSubLinks.Append("<li><a href=\""
+ url + "\" class=\"subButton\">"
+ label + "</a></li>\r");
j++;
}

sbSubLinks.Append("</ul></div></div>\r\r");
}
i++;
j = 0;
}
subNavBar.Text = sbSubLinks.ToString();
NavData.Dispose();

Yes I too dislike all this looping business, but I don't have a way of binding and achieving the desired results...|||What about a left join, instead of an inner join. With a left join, you could easily determine the MainMenu items with no submenus (the submenu fields would be null).

You would still loop through it on the web server.

Alternatively, you could use the FOR XML clause to create an XML string (which you would have to wrap inside of xml tags to make compliant).

Regards,

hmscott|||I'm not a C coder. You need to give us the layout of the recordset that you want to get from the server. We can help you with that, and from there on its up to you.|||Thanks for the offer...

I think hmscott's suggestion of a Left Join might just work, so I will groove on that for a while. As much as you're not a C# guy, I'm not a Sql guy, so I often just need a nudge in the right directlon..

Thanks againsql

How do I loop through a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause usesnot the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDUREdbo.OPA_GetMenuItems
AS
Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = @.i)
ORDER BYmainNavId, sort
end

RETURN

Here's one way: You could get the resultset into a table variable. Add an additional column in the table variable and mark it off as processed after each record.

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

DECLARE @.t tabke (id int, label varchar(100), url varchar(100), sort varchar(100), Processed char(1) )
DECLARE @.minid int

INSERT INTO @.t
SELECT
id, label, url, sort, 'N'
FROM
mainNav
ORDER BY
sort

WHILE EXISTS (SELECT 1 FROM @.t WHERE Processed = 'N')
BEGIN
--Get the first record
SELECT
@.minid = id
FROM
@.t
WHERE
Processed = 'N'
ORDER BY
id

--your processing code
/*
Select for submenu items
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end
*/

--make sure you mark the record as Processed
UPDATE
@.t
SET
Processed = 'Y'
WHERE
id = @.minid

END

|||

Depends on if you want the items to come back in one or multiple recordsets.

Here's a single recordset:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav
UNION
SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's two recordsets:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav

SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's multiple recordsets:

Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = (SELECT TOP 1 FROM (SELECT TOP @.i id,sort FROM mainNav ORDER BY sort,id) ORDER BY sort DESC,id DESC))
ORDER BYsort
end

You can also do it using a cursor if you want, which would probably be easier/faster if you have a LOT of menu items, but I'm guessing that since it's a menu, you are only talking about 5-20 items and not thousands.

|||

Basic cursor logic works like this, but the syntax is NOT correct, I haven't used a cursor in a LONG time:

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @.id
WHILE (@.@.FETCHSTATUS<>-2)
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav WHEREmainNavID=@.id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @.id
END
CLOSE MyCursor
DEALLOCATE MyCursor

That should be pretty close, all except for the READ NEXT FROM... I forget the command/syntax, that definately is not correct. And the cursor declaration is a bit off, but close.

|||

The multiple recordsets approach is the one I'm after - I need an .Net data to spit out the menus back in C#. I tryed your example and it did not compile - Sql came back with:

Incorrect Syntax near keyword 'From'.
Line 24: incorrect syntax:near'@.i'.

any ideas?

|||Ah, you must be using SQL Server 2000, yeah, that's not valid on 2000, only 2005. I'm not sure what your code looks like, but I would do the single resultset approach. Just loop through the resultset, and whatever you would do when you get a new resultset, just do it when the mainNav field changes value.|||

Thanks for your help. I actually took a different direction and managed to get around the problem with a left join...

Thanks again...

How do I locate a Database on SQL Express from Access 2003.

Access 2003 Data exported to SQL Express , great.

How do I then link the tables to the new database, I only seem to be able to link to system tables etc ?

Any ideas ?

Hi,

I assume that you are trying to link external table(s) and going through "ODBC Databases"? Then you probably have an ODBC DSN defined?

I've done that and my user table is neatly sean in the list together with the system views, etc. What you are probably experiencing is a misalignment of permissions. The following chain should be unbroken in order for you to see the user tables:

(a) Client Application security context (in this case - MSAccess)

(b) ODBC DSN connection settings - are you using SQL Authentication?

(c) SQL Server account (SQL or NT authentication)

(d) Database user associated with the account

(e) Appropriate schema/object permissions

Check each of these and see if everything is in order. You could also experiment with the SQL Management Studio or SQL Query Analyzer with the credentials used in MSAccess and see if the table(s) would be visible.

HTH,

Jivko Dobrev - MSFT

--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Many thanks, sorted, hadn't selected database from options for users.

Thanks again Racing Snake

How do I locate a Database on SQL Express from Access 2003.

Access 2003 Data exported to SQL Express , great.

How do I then link the tables to the new database, I only seem to be able to link to system tables etc ?

Any ideas ?

Hi,

I assume that you are trying to link external table(s) and going through "ODBC Databases"? Then you probably have an ODBC DSN defined?

I've done that and my user table is neatly sean in the list together with the system views, etc. What you are probably experiencing is a misalignment of permissions. The following chain should be unbroken in order for you to see the user tables:

(a) Client Application security context (in this case - MSAccess)

(b) ODBC DSN connection settings - are you using SQL Authentication?

(c) SQL Server account (SQL or NT authentication)

(d) Database user associated with the account

(e) Appropriate schema/object permissions

Check each of these and see if everything is in order. You could also experiment with the SQL Management Studio or SQL Query Analyzer with the credentials used in MSAccess and see if the table(s) would be visible.

HTH,

Jivko Dobrev - MSFT

--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Many thanks, sorted, hadn't selected database from options for users.

Thanks again Racing Snake

sql

How do I know if there is a duplicate value in database

hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?i am not sure how u r performing the check and what is bothering u. however, u can create a unique index on that field and fire the insert without any check. a duplicate value will produce an error.|||Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?
If for some strange reason the unique index (actually this should be the primary key) is not an option for you, why do you "go through the whole table"?.
A simple

SELECT couint(*) from user_profile WHERE username = 'input_value';

will also tell you if there is another row with that username.|||hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?

SELECT username, count(*)
FROM tblLogins
GROUP BY username
HAVING COUNT(*) > 1|||INSERT INTO Table(Collist) SELECT values
SELECT @.error = @.@.ERROR
IF @.@.ERROR <> 0
BEGIN
Error Handling|||hmmmm..so which way is the fastest and most efficient?|||I'd say the most efficient would be the unique index (or primary key) option, because, with correct error handling, not only can you prevent the problem from occurring, you can also provide a meaningful error message to users.sql

How do I know if my sql server needs more memroy ?

Hi,
We have an OLTP system with 600+ connections, 30 gb database running on a
4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL Server (usin
g
fixed memory). SQL Server is on its own dedicated machine.
Our clients are complaining about performance. Disk utilization is around
50%.Memory util always says 90% since I've set it to use 'fixed' memory. Ho
w
do I tell if the sql server needs more memory ?
TIA
MOThe following describes how to use Performance Monitor and performance
related SQL Server Profiler traces. In regard to available memory, keep in
mind that it is normal and beneficial for SQL Server to dynamically buffer
almost all the memory allocated to it, and it's excessive memory paging and
a poor cache hit ratio that you want to look for.
SQL Server 2000 Administrator's Pocket Consultant: Profiling and Monitoring
http://www.microsoft.com/technet/pr...s/c10ppcsq.mspx
SQL Server 2000 Operations Guide: Monitoring and Control
http://www.microsoft.com/technet/pr...in/sqlops5.mspx
Chapter 14 - Improving SQL Server Performance
http://msdn.microsoft.com/library/d...
etchapt14.asp
"MO" <MO@.discussions.microsoft.com> wrote in message
news:1E532BC5-08AC-4651-A9E5-A313E3C80C0A@.microsoft.com...
> Hi,
> We have an OLTP system with 600+ connections, 30 gb database running on a
> 4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL Server
> (using
> fixed memory). SQL Server is on its own dedicated machine.
> Our clients are complaining about performance. Disk utilization is around
> 50%.Memory util always says 90% since I've set it to use 'fixed' memory.
> How
> do I tell if the sql server needs more memory ?
> TIA
> MO|||MO wrote:
> Hi,
> We have an OLTP system with 600+ connections, 30 gb database running
> on a 4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL
> Server (using fixed memory). SQL Server is on its own dedicated
> machine.
> Our clients are complaining about performance. Disk utilization is
> around 50%.Memory util always says 90% since I've set it to use
> 'fixed' memory. How do I tell if the sql server needs more memory ?
> TIA
> MO
You also need to look at your disk subsytem and how you are using it
with SQL Server. The wrong RAID solution or poor placement of data, log,
and tempdb can all play into bad overall performance. As always, I first
recommend you performance tune your SQL. Bad SQL = Bad performance,
regardless of how much hardware you throw at it.
David Gugick - SQL Server MVP
Quest Software|||Ditto with that bad sql. I now have to go back fix all my bad SQL that I
wrote few years ago. The performance has been improving and no new hardware
upgraded was needed.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uC6D8RDKGHA.312@.TK2MSFTNGP09.phx.gbl...
> MO wrote:
> You also need to look at your disk subsytem and how you are using it with
> SQL Server. The wrong RAID solution or poor placement of data, log, and
> tempdb can all play into bad overall performance. As always, I first
> recommend you performance tune your SQL. Bad SQL = Bad performance,
> regardless of how much hardware you throw at it.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||In addition to upgrading hardware or optimizing your queries / transactions,
you may want to even consider architectural changes to your database model.
If your OLTP system is currently the data source for data mining, financial
reporting, or other types of resource intensive queries that would block
daily operations type transactions, then perhaps the queries could instead
be performed against a standby or reporting server.
http://vyaskn.tripod.com/maintainin..._sql_server.htm
Understanding and Resolving SQL Server Blocking Problems:
http://support.microsoft.com/defaul...kb;EN-US;224453
Is it really necessary to retain 30 GB of (historical?) data in an online
transaction processing (OLTP) system? If this is an operational system, then
perhaps you only need the current month's worth of data, and the remaining
data can be migrated to an operational data store (ODS) database or server.
The Operational Data Store:
http://www.dmreview.com/article_sub.cfm?articleId=469
Using Partitions in a Microsoft SQL Server 2000 Data Warehouse
http://msdn.microsoft.com/library/d...nDW.
htm
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
"MO" <MO@.discussions.microsoft.com> wrote in message
news:1E532BC5-08AC-4651-A9E5-A313E3C80C0A@.microsoft.com...
> Hi,
> We have an OLTP system with 600+ connections, 30 gb database running on a
> 4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL Server
> (using
> fixed memory). SQL Server is on its own dedicated machine.
> Our clients are complaining about performance. Disk utilization is around
> 50%.Memory util always says 90% since I've set it to use 'fixed' memory.
> How
> do I tell if the sql server needs more memory ?
> TIA
> MO

How do I find out what database Im using?

Hello all!

Quick question: How do I find out what database I'm currently using? I can't seem to find an appropriate system stored proc in BOL. For example:

use XYZ
go

[SQL statement(s) in question]
go

RESULTS:

database
----------
XYZ

Thanks in advance for your help!
Catselect db_name()|||Thanks a bunch!
Cat

How do I find IDENTITY columns on Table using T-SQL

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

Found it, a little obscure:

SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]

Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?

|||I posted that sometime ago:

SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'

Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME

HTH, Jens Suessmeyer.

http://www.sqlserver2005.,de

|||

Here is some more ( in technicolor ;-) )

USE northwind
GO

DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO

DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Denis the SQL Menace

http://sqlservercode.blogspot.com/