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 make a autonumber field in my table!

Hi
im new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql.

Ive tried setting my primary key field touniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!

hope someone can help!
thanks
SQL Server uses IDENTITY for the auto number, in SQL Server it is a property to the column it is not a column, there are many IDENTITY in SQL Server what you need is the property. The Uniqueidentifier is different that uses a GUID which is a 16bytes Binary data type in SQL Server while IDENTITY is INT. Run a search for SET IDENTITY property in SQL Server BOL (books online). Hope this helps.|||thanks didnt know it was that easy!

How do i limit number of rows in a table based on dates?

Hi,
My application is written in c# and uses sql Express to store
information relating to what time employees clock in/out of work each
day.
How can i restrict the amount of rows in my attendance database table
so that entries older than 3 months are automatically deleted from the
database?
Thanks in advance!
KHi
I'd create a stored procedure that does deletion and then create a
job(weekly...) that will run this SP
<kls.systems@.btinternet.com> wrote in message
news:1176719432.941536.219550@.o5g2000hsb.googlegroups.com...
> Hi,
> My application is written in c# and uses sql Express to store
> information relating to what time employees clock in/out of work each
> day.
> How can i restrict the amount of rows in my attendance database table
> so that entries older than 3 months are automatically deleted from the
> database?
> Thanks in advance!
> K
>|||Thanks for the help uri, I thought that sql express couldnt use
scheduled jobs though as it doesnt use sql agent?
Thanks again.|||Ah I see, did not read properly your post
Perhaps here you will find some info
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
<kls.systems@.btinternet.com> wrote in message
news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
> Thanks for the help uri, I thought that sql express couldnt use
> scheduled jobs though as it doesnt use sql agent?
> Thanks again.
>|||How about using a .sql script file, execute it using SQLCMD (in a .bat file) and use the Windows
scheduler to execute that .bat file?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<kls.systems@.btinternet.com> wrote in message
news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
> Thanks for the help uri, I thought that sql express couldnt use
> scheduled jobs though as it doesnt use sql agent?
> Thanks again.
>|||On 16 Apr, 12:22, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ah I see, did not read properly your post
> Perhaps here you will find some info
> Automating Database maintenance in SQL 2005 Express Edition Part Ihttp://www.sqldbatips.com/showarticle.asp?ID=27
> Automating Database maintenance in SQL 2005 Express Edition Part IIhttp://www.sqldbatips.com/showarticle.asp?ID=29
> <kls.syst...@.btinternet.com> wrote in message
> news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
>
> > Thanks for the help uri, I thought that sql express couldnt use
> > scheduled jobs though as it doesnt use sql agent?
> > Thanks again.- Hide quoted text -
> - Show quoted text -|||Thanks very much for all of your help, I'll try setting up a windows
scheduled job to run an sql script to do this as suggested in the
articles you provided links for.
I just thought there would have been a quick and easy setting within
management studio express i could configure to do it but nevermind!
Thanks very much again for your help Uri, very much appreciated.

How do I join two tables to get a row count?

I have two tables: Thread and Reply and they both have a field called UserID

I need to know the number of rows in both tables where UserID="Chris"

I can do this with two stored procedures and add the results together:

SELECT COUNT(*) FROM Thread WHERE Thread.UserID='Chris'

SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris'

but there must be a better way. Can this be written as one stored procedure with some sort of join?

Thanks, Chris

Try something like this:

select
(select count(user_id) from table1 where userID = 'blah' )
+ (select count(user_id) from table2 where userID = 'blah' )
as totalCount

|||

you can do one stored proc, get the counts into 2 different variables and send them out as OUTPUT parameters, so you get all the data in 1 trip.

You can also do it in one query as follows:

SELECT COUNT(*) as Count1, (SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris') as Count2

FROM Thread WHERE Thread.UserID='Chris'

|||

Darrell,

That worked great. Thanks,

Chris

How do I in Crystal 10

Using CRYSTAL 10.
A field titled INVOICE NUMBER always has *0000000 at the end. These are extraneous characters I do not need on the report. How do I remove them from the Invoice Number field. The field length is 25; and, again, I do not need the last eight characters.If it is a character field then u can use SQL expression , and builtin function substring.sql

How do I identify a command in a job step?

Hi
I'm having a job that runs a number of steps. One of them runs the CmdExec
"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
how do I find out which one it is? I've looked a bit around to see if I can
find a table where I can find the number and then maybe see the name of the
job, but with no luck.
Can any of you help with this?
Regards
SteenWhen the DTSRun command uses /~Z, it means the command to
run the DTS package is encrypted. Run a trace or Profiler
when the job runs. This will allow you to get the package
name or package guid.
-Sue
On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Hi
>I'm having a job that runs a number of steps. One of them runs the CmdExec
>"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
>how do I find out which one it is? I've looked a bit around to see if I can
>find a table where I can find the number and then maybe see the name of the
>job, but with no luck.
>Can any of you help with this?
>
>Regards
>Steen
>|||Ok...found some more info about it in BOL (...just have to look for the
right thing..:-)..). The /Z argument apparently tells that the command line
is encrypted. That's fine, but...I still need to find out which command it's
running. Any change of doing that?
Steen
"Steen Persson" <SPE@.REMOVEdatea.dk> skrev i en meddelelse
news:O5Z2ZETVEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm having a job that runs a number of steps. One of them runs the CmdExec
> "DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> how do I find out which one it is? I've looked a bit around to see if I
can
> find a table where I can find the number and then maybe see the name of
the
> job, but with no luck.
> Can any of you help with this?
>
> Regards
> Steen
>|||Thanks Sue
I've just tried to run a trace, but I must admit that my knowledge in
running traces might not be good enough, since I can't find any references
in there that indicates which package is being run.
Are there any easy or "obvious" data to look for to find out which package
is being executed?
Regards
Steen
"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
> When the DTSRun command uses /~Z, it means the command to
> run the DTS package is encrypted. Run a trace or Profiler
> when the job runs. This will allow you to get the package
> name or package guid.
> -Sue
> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
> <SPE@.REMOVEdatea.dk> wrote:
> >Hi
> >
> >I'm having a job that runs a number of steps. One of them runs the
CmdExec
> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> >how do I find out which one it is? I've looked a bit around to see if I
can
> >find a table where I can find the number and then maybe see the name of
the
> >job, but with no luck.
> >
> >Can any of you help with this?
> >
> >
> >Regards
> >Steen
> >
>|||Steen,
Capturing SQL:StmtCompleted or SP:Completed will give you
TextData values along the lines of:
exec msdb..sp_get_dtspackage N'YourPackage', null, null
-Sue
On Fri, 18 Jun 2004 15:26:26 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Thanks Sue
>I've just tried to run a trace, but I must admit that my knowledge in
>running traces might not be good enough, since I can't find any references
>in there that indicates which package is being run.
>Are there any easy or "obvious" data to look for to find out which package
>is being executed?
>Regards
>Steen
>"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
>news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
>> When the DTSRun command uses /~Z, it means the command to
>> run the DTS package is encrypted. Run a trace or Profiler
>> when the job runs. This will allow you to get the package
>> name or package guid.
>> -Sue
>> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
>> <SPE@.REMOVEdatea.dk> wrote:
>> >Hi
>> >
>> >I'm having a job that runs a number of steps. One of them runs the
>CmdExec
>> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
>but
>> >how do I find out which one it is? I've looked a bit around to see if I
>can
>> >find a table where I can find the number and then maybe see the name of
>the
>> >job, but with no luck.
>> >
>> >Can any of you help with this?
>> >
>> >
>> >Regards
>> >Steen
>> >
>

How do I go to line number referenced in SQL error message

I get a SQL error message like:
Server: Msg 7391, Level 16, State 1, Procedure sp_MyProc, Line 205
So I open up the stored proc with sp_helptext and select Edit | GoTo Line
205 in the results pane. Given the error, I would expect to see a linked
server reference on this line. However, I am on BEGIN of an IF statement
block.
How can I relate the actual stored proc line number with the line number
referenced in the error message?It is normally off by one or two lines, but begin counting at the create
proc command, NOT at the beginning of your script which might have (If
exists, drop, etc.)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Dave" <dave@.nospam.ru> wrote in message
news:ePOQEdTQFHA.2348@.tk2msftngp13.phx.gbl...
> I get a SQL error message like:
> Server: Msg 7391, Level 16, State 1, Procedure sp_MyProc, Line 205
> So I open up the stored proc with sp_helptext and select Edit | GoTo Line
> 205 in the results pane. Given the error, I would expect to see a linked
> server reference on this line. However, I am on BEGIN of an IF statement
> block.
> How can I relate the actual stored proc line number with the line number
> referenced in the error message?
>|||You can use the Debugger in Query Analyzer. Right-click on the SP in
the Object Browser and click Debug.
--
David Portas
SQL Server MVP
--

How do I get the SqlDataSource to look at the textbox

I am working with a Detailsview. I am displaying information from a number of different tables, and so when I configure the datasource I have to specify a custom sql statement. I can get it to display the right columns, but I cannot seem to be able to use a where statement.

I am wanting to display the info in the Detailsview based on what is entered into a textbox, but I get an error when I try to add in the where statement. Can someone tell me what I am missing here?

Thanks

Just add WHEREsomefield=@.textboxvalue in your SQL Query. Then (if in VS 2005), hit the refresh parameters button, and choose where to pull @.textboxvalue from (Parameter type would be control, and the ControlID would be Textbox1).|||

Thanks. It was the @. that I was missing.

|||Binding an SQL WHERE clause to a free-text field to return just one record sounds to me rather dodgy, as it indicates that the database design could be poor. Normally a DetailsView is used as part of a master-detail design and should be bound to the primary key of the master table.

How do I get the row number?

I have tryed to find a way to include the row number in a querry but without result.

I have a table select some rows ordered and now I will add a column called rank

How do I get the rownumber in the field rank ?

Thanks in advance

Jan OThe way I'd suggest assigning a row number to your resultset is to INSERT your resultset into a temporary table or table variable which has an IDENTITY column, something like this:

-- set up a table variable to hold the resultset
DECLARE @.myTable (Rank int identity(1,1) primary_key,
 ColumnA varchar(20),
 ColumnB varchar(20)
 )
-- insert the resultset into the table variable
INSERT INTO
 @.myTable
(
 ColumnA,
 ColumnB
)
SELECT
 ColumnA,
 ColumnB
FROM
 someTable
ORDER BY
 someCriteria
-- return the resultset to the calling program in rank order, including the rank column
SELECT
 Rank,
 ColumnA,
 ColumnB
FROM
 @.myTable
ORDER BY
 Rank

Terri|||Thanks,

Have hoped not to do it so, a lot of other calculation dependences.

Jan|||Well, an alternative (which performs poorly) would be at the very bottom of this link:Returning a Row Number in a Query It involves using a subquery for every row in your resultset and could be horrendous performance-wise.

Can you just assign the rank in the front end?

Terri|||I agree with Terri. If possible, assign in the front end. I have used the temp table solution before, as well. It really depends on why you need to have the rownumber. If it is just for display purposes, it should be no problem to create on the front end when binding your resultset.|||Thanks again.

I have started to solve the "problem" in the front end.
I have a datagrid with paging so I have a few lines left.
Got it working whitout paging , but I had prefered asystem rownr from SQL.

But you cant get everything for free :)

Thanks
Jan

How do I get number of fields returned by query?

Hi.

I am trying to get the results of a dynamic sql statement into a #table, in order to filter them. Given that I don't know how many fields will be returned, how do I accomplish this?

I believe I need to create the #table in advance, and then run the dynamic string as part of an insert [eg 'insert into #table exec (@.sql)' ], but in order to do this I need to know how many fields are going to be returned.

The results might also be returned by a procedure rather than a simple SQL statement, so I can't just parse @.sql to get the fields.

As an example,

declare @.sql nvarchar(200)

select @.sql = 'select "a" as ColA, "b" as ColB'

exec sp_executesql @.sql

returns two columns of data.

I think I need to get the results, count the fields, create the table and finally re-run the query with an insert to poulate the table. So how do I count the columns? And for bonus points, how do I get the column names?

Many thanks,

Neil Jones

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..)

|||

Thanks for your reply

Nitin Khurana wrote:

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

The problem here is that I can't edit the query. All I know at runtime is that it's a dynamic sql statement, which could be a simple select, or an execution of a stored procedure

Nitin Khurana wrote:

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

My question is, how do I get the number of columns?

Nitin Khurana wrote:

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..

Unfortunately it's not the number of rows that is the problem.

Cheers,

Neil Jones

|||

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

|||

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

|||

Try this:

SELECT * INTO #Temp
FROM (<Your SQL Query>) as T;

Note: If the columns returned by the query aren't properly named (no column name), this will not work. Otherwise the table will be created automatically with the column names from the query. Once the table ist created, you can access the Information about the columns from system views:

USE tempdb
GO
SELECT COUNT(*) FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name like '#Test%');

This will work with MS SQL Server 2005

Regards,

Paddy

|||

Here you go with a complete sample when having the statement as a variable:

USE AdventureWorks
GO

SET NOCOUNT ON;
GO

-- Create a Table for Testing
CREATE TABLE Test
( col1 INT NOT NULL
, col2 VARCHAR(20) NOT NULL
);
GO

-- Some useful Information
INSERT INTO Test VALUES (1, 'Test1');
INSERT INTO Test VALUES (2, 'Test2');
INSERT INTO Test VALUES (3, 'Test3');
INSERT INTO Test VALUES (4, 'Test4');
INSERT INTO Test VALUES (5, 'Test5');
INSERT INTO Test VALUES (6, 'Test6');
INSERT INTO Test VALUES (7, 'Test7');
INSERT INTO Test VALUES (8, 'Test8');
INSERT INTO Test VALUES (9, 'Test9');
INSERT INTO Test VALUES (10, 'Test10');
GO

-- The dynamic sql statement
DECLARE @.sql NVARCHAR(200);
SET @.sql = 'SELECT col2, col1 FROM Test';

-- Extend the statement for creating a temporary table (Note: Use Global Temporary Table (##TableName))
DECLARE @.sql2 NVARCHAR(250)
set @.sql2 = 'SELECT * INTO ##Test FROM (' + @.sql + ') AS T;';

-- Run the extended Statement
EXEC(@.sql2);
GO

-- View the result
SELECT * FROM ##Test;
GO

-- Get Information about the Temporary Table
USE tempdb
GO

-- Column Count
SELECT COUNT(*) FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- Names of Columns
SELECT name FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- CleanUp
USE AdventureWorks
GO

-- Drop the temporary Table
DROP TABLE ##Test;
GO

-- Drop the Test Data Table
DROP TABLE Test;
GO

SET NOCOUNT OFF;

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of you table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of your table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Lucky P wrote:

Here you go with a complete sample when having the statement as a variable:

That looks great, thanks!

I think I'm going to have to accept that it's not possible with all dynamic sql statements, such as "exec xp_proc1", and take what I can.

Cheers,

Neil

How do I get a count of each set of results?

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.

i.e.

Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2

Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...

Is there straightforward way of doing this in SQL?

ThanksIn SQL Server 2005:

select
[Type],
count() over (partition by [Type]) as [Count]
from T

In SQL Server 2000:

select
[Type],
(select count(*)
from T as Tcopy
where Tcopy.[Type] = T.[Type]
) as [Count]
from T

(both solutions untested - for a better chance at tested
solutions, include create table and insert statements that
can be cut and pasted into a query editor.)

Steve Kass
Drew University

kasterborus@.yahoo.com wrote:

Quote:

Originally Posted by

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.
>
i.e.
>
Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2
>
Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...
>
Is there straightforward way of doing this in SQL?
>
Thanks
>

How do I get a count of all records returned.

I'm trying to put the total number of records returned from from a query in the bottom of our report. I don't want to do a count(*) in my sql stmt.

thanks.

Hello,

Try this in your table footer:

=CountRows()

Hope this helps.

Jarret

|||putting =CountRows() in my footer give me 1. What may I be missing here?|||

Use =CountRows("DataSet1") where DataSet1 is the name of your dataset that is bound to your table.

Shyam

|||

Try this:

=countDistinct(Fields!name.Value)

It works for me.

How do I generate auto increment number in SQL Express?

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?Thanks a lot.For your first question: In SQL Server, you can use data type int as your identity column and assign this column as indentity field from the property window. It will work like autonumber field in Access.|||

cckiat:

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)? Thanks a lot.

IDENTITY is the auto increament in SQL Server it is a property of the column, the second one you described is a SEQUENCE it is in Oracle not SQL Server but it is similar to IDENTITY. Both are defined by ANSI SQL but Microsoft and Oracle choose to implement one and not the other. But you can use GUID in SQL Server to generate Unique numbers but it is a 16bytes Binary data type so use it with care. Hope this helps.

How do I dynamically change the "TOP X" portion of a SELECT

I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error.

@.TopX int ( or varchar)

SELECT @.TopX CompanyName, Amount FROM Sales Where....

Why will this not work?

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

DECLARE @.stmtvarchar(500)DECLARE @.top varchar(5)SET @.top ='10'SET @.stmt ='SELECT TOP ' + @.top +' * FROM [Products]'EXEC(@.stmt)

You can dynamically create a sql statement:|||

Upgraded to 2005 and that worked perfectly...

Thanks!

How do I display a count?

I want to display the number of records for each state in a database. I'm using a strongly types dataset. In my method, I have the following

SELECT COUNT(ID) AS iTotal, STATE
FROM members
WHERE (Suspend = 0)
GROUP BY STATE
ORDER BY STATE

In the code behind of my page I have

Dim mateAdapter As New WAPTableAdapters.membersTableAdapter
Dim mates As WAP.membersDataTable
Dim mate As WAP.membersRow

mates = mateAdapter.GetDataState

For Each mate In mates
Select Case mate.STATE
Case "AK"
LabelAK.Text = mate.ID.
End Select
Next

What shouldLabelAK.Text = mate.ID. be for me to be able to display the number of records that have 'AK' in the state field?

Diane

I think if you use

LabelAK.Text = mate.iTotal

you'll get what you're looking for.

|||

That's what i thought, but it doesn't like it.

Description:Anerror occurred during the compilation of a resource required to servicethis request. Please review the following specific error details andmodify your source code appropriately.

Compiler Error Message:BC30456: 'iTotal' is not a member of 'WAP.membersRow'.

But it is in the dataset, and i can get a preview of the data in the dataset. But it doesn't like it in the page.

Diane

|||

Try mate.iTotal.ToString

I had some state and zipcode data around so I put this together.

webform:

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Test.aspx.vb" Inherits="Test" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:Label id="LabelMO" runat="server" Text="Label"></asp:Label> </div> </form></body></html>

code behind:

Imports WAPTableAdaptersPartialClass TestInherits System.Web.UI.PageProtected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs) _Handles Me.LoadDim mateAdapterAs New ZipcodesTableAdapterDim matesAs WAP.ZipcodesDataTableDim mateAs WAP.ZipcodesRow mates = mateAdapter.GetDataStateFor Each mateIn matesSelect Case mate.StateCase"MO" LabelMO.Text = mate.iTotal.ToStringEnd Select Next End SubEnd Class

GetDataStat method:

selectcount(Zipcode)as iTotal, Statefrom ZipcodesGroup by StateOrder by State

|||

I have exactly what you do, and I'm getting the error that iTotal is not a member of the row. There must be something I'm overlooking that I've got wrong. But when i compare mine with yours, I don't see a difference.

Diane

|||

That is very odd, I know how you feel though. Sometimes what seems like it should be so easy is infuriatingly difficult.

Post your code so I can play around with it, or you can send it to me at eterry28 @. gmail . com

|||

Thank youeterry28

I did post my code in the first post.

Diane

|||

I resolved my problem by returning the state field in all rows and changing my code to Inmates.Compute("count(STATE)", "STATE='VT'").ToString

Thanks for your help

Diane

How do I determine the IP number and TCP Port of a SQL instance

I'm trying to determine the specific IP address and the TCP port on which a SQL server is communicating.

As far as I can tell, I can't use SMO for this, since I need a method that is compatible with both SQL 2005 AND SQL 2000. Is there a work around or can I use DMO?

Since my target is to be able to use this code against multipel servers, it would be nice to find the method with the least overhead possible. I would also like to avoid being forced to read registries remotely.

--
Jakob BindsletLook at the ServerIPAddress and IPAddressProperty values within the Microsoft.SqlServer.Management.Smo.Wmi namespace.|||
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/19/Default.aspx

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||According to BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/6527388b-26b5-4ebb-8a52-0cf4415f9134.htm) the Microsoft.SqlServer.management.Smi.Wmi namespace isn't supported on SQL Server 2000 and below.|||

I don't see why the SQL Server version has any bearing on what information WMI will provide. I'm trying to work out the details but have run into a bit of confusion regarding the IP Address, but I'm getting the Port information back. I've blogged about it at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/11/30/25785.aspx if you're interested.

|||

Hi Jagoop,

Did you find a way to retrieve this information for both SQL 2000 as well as SQL 2005 instances?

Thanks,

|||AS WMI is not supported you will have to use the registry directly:

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

string SQLServerKey = @."SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib";

RegistryKey rk = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "JensOne");

RegistryKey protocols = rk.OpenSubKey(SQLServerKey);

Console.WriteLine(SQLServerKey);

foreach(string s in protocols.GetSubKeyNames())

{

RegistryKey rkprotocol = protocols.OpenSubKey(s);

Console.WriteLine("\tProtocol: {0}",rkprotocol.Name );

foreach (string ValueName in rkprotocol.GetValueNames())

{

Console.WriteLine("\t\t[Type]:Value: [{0}]:{1}",ValueName, rkprotocol.GetValue(ValueName));

}

}

HTH, Jens K. Suessmeyer.

http.//www.sqlserver2005.de

|||

I'd prefer to use DMO, instead of using the registry for this. It will also take care of the instance names for you.

See http://msdn2.microsoft.com/en-us/library/ms134942.aspx (Registry2).

Note you need to have the updated DMO version installed that ships with SQL Server 2005 and is also available as a redist as well (see the FAQ of this group) -- that is, if you plan to use it with SQL Server 2000 and 2005.

I need to lookup, but if I recall correctly it will work for SQL Server 2005 as well, as we aimed at making the port of SQL-DMO clients from 2000 to 2005 totally transparent.

|||

Thanks Jens,

If you have multiple ip addresses listening on different ports, the above registry location doesn't show that information. I tried searching for these values in the registry but couldn't find it anywhere. I have only seen this informaiton ouput to the SQL Server logs during the startup process. Is it possible to retrieve this information?

PK

|||

This is in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp

(For the first instance)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How do I determine the IP number and TCP Port of a SQL instance

I'm trying to determine the specific IP address and the TCP port on which a SQL server is communicating.

As far as I can tell, I can't use SMO for this, since I need a method that is compatible with both SQL 2005 AND SQL 2000. Is there a work around or can I use DMO?

Since my target is to be able to use this code against multipel servers, it would be nice to find the method with the least overhead possible. I would also like to avoid being forced to read registries remotely.

--
Jakob BindsletLook at the ServerIPAddress and IPAddressProperty values within the Microsoft.SqlServer.Management.Smo.Wmi namespace.|||
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/19/Default.aspx

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||According to BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/6527388b-26b5-4ebb-8a52-0cf4415f9134.htm) the Microsoft.SqlServer.management.Smi.Wmi namespace isn't supported on SQL Server 2000 and below.|||

I don't see why the SQL Server version has any bearing on what information WMI will provide. I'm trying to work out the details but have run into a bit of confusion regarding the IP Address, but I'm getting the Port information back. I've blogged about it at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/11/30/25785.aspx if you're interested.

|||

Hi Jagoop,

Did you find a way to retrieve this information for both SQL 2000 as well as SQL 2005 instances?

Thanks,

|||AS WMI is not supported you will have to use the registry directly:

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

string SQLServerKey = @."SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib";

RegistryKey rk = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "JensOne");

RegistryKey protocols = rk.OpenSubKey(SQLServerKey);

Console.WriteLine(SQLServerKey);

foreach(string s in protocols.GetSubKeyNames())

{

RegistryKey rkprotocol = protocols.OpenSubKey(s);

Console.WriteLine("\tProtocol: {0}",rkprotocol.Name );

foreach (string ValueName in rkprotocol.GetValueNames())

{

Console.WriteLine("\t\t[Type]:Value: [{0}]:{1}",ValueName, rkprotocol.GetValue(ValueName));

}

}

HTH, Jens K. Suessmeyer.

http.//www.sqlserver2005.de

|||

I'd prefer to use DMO, instead of using the registry for this. It will also take care of the instance names for you.

See http://msdn2.microsoft.com/en-us/library/ms134942.aspx (Registry2).

Note you need to have the updated DMO version installed that ships with SQL Server 2005 and is also available as a redist as well (see the FAQ of this group) -- that is, if you plan to use it with SQL Server 2000 and 2005.

I need to lookup, but if I recall correctly it will work for SQL Server 2005 as well, as we aimed at making the port of SQL-DMO clients from 2000 to 2005 totally transparent.

|||

Thanks Jens,

If you have multiple ip addresses listening on different ports, the above registry location doesn't show that information. I tried searching for these values in the registry but couldn't find it anywhere. I have only seen this informaiton ouput to the SQL Server logs during the startup process. Is it possible to retrieve this information?

PK

|||

This is in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp

(For the first instance)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de