how to find vacant slot of rack

Dear All,

i want to find no of empty rack(two dimensional ) . i am using sql2000

i have a rack of two dimensional where every slot is recognized by rowno and columnno now in every slot i placed item (captured by itemcode). nOw i want to find slot do not assing any item
please give me some idea

there is rackmst( where i define max_no_row and max_no_cols).

Please help

thanks

I too have the same doubt and i'll be thankful to u if u forward ur answers|||

Perhaps something like this would help:

CREATE TABLE MyRack
( RowNo int,
ColNo int,
Slot varchar(10)
PRIMARY KEY ( RowNo, ColNo )
)

INSERT INTO MyRack VALUES ( 1, 1, 'a25' )
INSERT INTO MyRack VALUES ( 1, 2, 'a24' )
INSERT INTO MyRack VALUES ( 1, 3, 'a23' )
INSERT INTO MyRack VALUES ( 1, 4, 'a28' )
INSERT INTO MyRack VALUES ( 2, 1, NULL )
INSERT INTO MyRack VALUES ( 2, 2, 'b25' )
INSERT INTO MyRack VALUES ( 2, 3, 'c25' )
INSERT INTO MyRack VALUES ( 2, 4, 'd25' )
INSERT INTO MyRack VALUES ( 3, 1, NULL )
INSERT INTO MyRack VALUES ( 3, 2, 'h25' )
INSERT INTO MyRack VALUES ( 3, 3, 'j25' )
INSERT INTO MyRack VALUES ( 3, 4, 'k25' )

SELECT TOP 1
RowNo,
ColNo
FROM MyRack
WHERE Slot IS NULL
ORDER BY
RowNo,
ColNo

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_nullcount_of_not_nullcount(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!
<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegr oups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.
|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:

>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
>count(f2( fieldx ) ) AS count_of_not_null,
>count(*)
>from some_table
>
>that would return one row that would look like:
>
>count_of_nullcount_of_not_nullcount(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!
|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegr oups.com...
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.
sql

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_null count_of_not_null count(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:

>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
>from some_table
>
>that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_null count_of_not_null count(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:
>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
>from some_table
>
>that would return one row that would look like:
>
>count_of_null count_of_not_null count(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> > Dear experts,
> >
> > I am finding a LOT of rows with NULL columns in the Sybase
> > tables I'm querying.
> >
> > Say, there is a table, with 100 rows.
> > 25 rows are NULL
> > 75 rows are NOT NULL.
> >
> >
> > What I'm trying to eliminate is:
> >
> > select count(*)
> > from some_table
> > where fieldx is null
> >
> > and then running the next query:
> >
> > select count(*)
> > from some_table
> > where fieldx is NOT null
> >
> >
> > What functions can I use to run a query such as:
> >
> > select count(f1( fieldx ),) AS count_of_null,
> > count(f2( fieldx ) ) AS count_of_not_null,
> > count(*)
> > from some_table
> >
> >
> > that would return one row that would look like:
> >
> >
> > count_of_null count_of_not_null count(*)
> >
> > 25 75 100
> >
> >
> >
> > I know there is the ISNULL function. But that converts the NULL
> > to an actual number. Could I use other functions in conjunction with
> > it?
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.

How to find out wether a row has been replicated?

Dear ppl,

In Merge Replication SQL Server2005, what is the easiest way of finding out wether a row on the publisher database has ever been replicated to subscribers?

Regards

Nabeel-

The procedure below should get you started on being able to tell if a row has been sent to a subscriber, all you need to do is provide the rowguid and publication name.

use <PUB_DB_NAME>

go

create procedure ASubHasIt(@.pubname sysname, @.row uniqueidentifier)

as

declare @.tablenick int

declare @.maxsentgen int

select @.maxsentgen = max(sentgen), @.tablenick = max(nickname) from (sysmergesubscriptions sms join sysmergepublications smp on sms.pubid = smp.pubid) join sysmergearticles sma on sma.pubid=smp.pubid where smp.name='PubName' and sms.sentgen IS NOT NULL

if exists (select * from MSmerge_genhistory gh join MSmerge_contents mc on mc.generation = gh.generation where gh.generation > @.maxsentgen and mc.rowguid = @.row)

begin

print 'Row ' + CONVERT(nvarchar(max), @.row) + ' has NOT been sent to a subscriber'

end

else

begin

print 'Row ' + CONVERT(nvarchar(max), @.row) + ' has been sent to a subscriber'

end

go

Example:

exec ASubHasIt @.pubname='PubName', @.row='8B348C04-B3A9-DB11-AEA6-000BDBD0506C'

Hope this helps!

-Phil Piwonka

|||excellent...cheers mate :)

How to find out the total number of commited transactions on SQL2k?

Dear all,

Previously I posted this issue on SSIS newsgroups and I did not obtain any response so that I'll do here.

Using this system function (::fn_log(null,null)) you can find out how many transactions have been confirmed in your .LDF. Although by means of another system function you can see further information, such as statitical.

Any help would be very appreciated.

Thanks in advance and regards,

sp_monitor among them

How to find out the SQL server version?

Dear Friends,
I have SQL Script...
How to find out the Sql Server version?
PLease let me know...
Thanks,
ArthiTo get edition on a SQL 2k box - select serverproperty('edition')

To get service pack level on SQL2k box - select serverproperty('productlevel')

For SQL 7 box - select @.@.version

HTH|||Thanks for your reply...

I have only the SQL Script...

How to find out the SQL Server version...

Thanks,
Arthi|||You have only the SQL script...what does that mean?

Do you mean you have some SQL code and you are trying to figure out what version it was written for? If so that will be difficult as most code that will work for SQL 6.5 will work on SQL 2000 but there are additional keywords for SQL2K and SQL 7 that would not work for 6.5. If this is what you are looking for, feel free to post your code and see if anyone can figure it out, if I am still not understanding your question, please provide more details for what you need.|||Like he said .. very hard to look and tell from scripts.Unless you mean SQL Query tool .. you can use Select @.@.version command

How to find out the PK from a table?

Dear all experts,

I need to find out some property of a tableAs following sql statement:
select mtable.name,mcolumn.name,mtype.name,mcolumn.is_identity
from sys.tables mtable, sys.all_columns mcolumn, sys.types mtype
where mcolumn.object_id = mtable.object_id
and mcolumn.system_type_id = mtype.system_type_id
and mtable.name in ('TestTable')
order by mtable.name,mcolumn.column_id

But there is no PK information.
Follwing sql statement shows the PK name of a table, but no composite info(which fields):
select mtable.name,mkey.name
from sys.key_constraints mkey, sys.tables mtable
where mkey.parent_object_id = mtable.object_id
and mkey.type = 'PK'
and mkey.name like 'TestTable'

How can I find which fields are PK in a table?thanks...
-Winson

try using

sp_pkeys 'tablename'

|||

And I try to write another way as follow:

select mtable.name as tablename,mcolumn.name as fieldname,mtype.name as fieldtype,mcolumn.is_identity,mindex.index_id as is_pk
from sys.tables mtable inner join sys.all_columns mcolumn on mtable.object_id = mcolumn.object_id
inner join sys.types mtype on mcolumn.system_type_id = mtype.system_type_id
left join sys.index_columns mindex on mcolumn.object_id = mindex.object_id and mcolumn.column_id = mindex.column_id
where mtable.name in ('TestTable')
order by mtable.name,mcolumn.column_id,mindex.key_ordinal

|||You can also query the system view INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAIN_TYPE = 'PRIMARY KEY' joining on the table name.|||

Also, take a look at

http://msdn2.microsoft.com/en-us/library/ms345522.aspx#_FAQ16

How to find IP address or Cluster Name of SQL server 2000

Dear All,
Here is the scenario:
We have production and We have a DR Site.
The database is getting replicated from production sit to DR site using
log shipping.
Now the issue is:
We have unique transaction id for each transaction. For the
transactions being made at DR site, we want to have separate series.
Application uses stored proc to generate new transaction id id at
production. We want same stored procedure to handle this situation by
identifying SQL server instance name OR Cluster Name OR IP address and
generate the transaction id based on the findings.
Can anybody help me, how to find either of the three.
Thanks in advance.
AmarHi,

> identifying SQL server instance name OR Cluster Name OR IP address and
Have a look at SERVERPROPERTY() function in Books OnLine.
Robert
<emailtoamar@.gmail.com> wrote in message
news:1143535977.511229.222400@.i40g2000cwc.googlegroups.com...
> Dear All,
> Here is the scenario:
> We have production and We have a DR Site.
> The database is getting replicated from production sit to DR site using
> log shipping.
> Now the issue is:
> We have unique transaction id for each transaction. For the
> transactions being made at DR site, we want to have separate series.
> Application uses stored proc to generate new transaction id id at
> production. We want same stored procedure to handle this situation by
> identifying SQL server instance name OR Cluster Name OR IP address and
> generate the transaction id based on the findings.
> Can anybody help me, how to find either of the three.
> Thanks in advance.
> Amar
>

how to find empty slot in a rack

Dear All,

i want to find no of empty rack(two dimensional ). i am using sql2000

i have a rack of two dimensional where every slot is recognized by rowno and columnno now in every slot i placed item (captured by itemcode). nOw i want to find slot do not assing any item
please give me some idea

there is rackmst( where i define max_no_row and max_no_cols).

Please help

thanks

I am assuming that the array is regular (all rows have the same numbert of slots). There are two ways:

Method 1:

Write a cursor to loop through the rows. For each row, loop through the columns using an IF NOT EXISTS on the contents table.

Method 2:

Create row and column tables and do a SELECT on them with a cartesian join and qualify this with a WHERE ROW.ID + COLUMN..ID NOT IN (SELECT ROW_ID + COLUMN_ID FROM CONTENTS)

(Your SQL query will need to use CONVERT to convert the Int cvalues of the Id columns to VARCHAR before concatenating them)

HTH