about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

AARGGHH Fulltext indexing woes...

I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ----
---
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ----
---
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
> dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied =
> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
--
>
>
>We're feverishly in QA mode here getting ready to rollout production to
SQL2K5...
There is this one query which is returning the following error:
Msg 7617, Level 16, State 1, Line 1
Query does not reference the full-text indexed table or indexed view.
I'm really at a lost here. What is wrong with this first query'
I don't actually do select tblStock.* in the production version but it
succeeds/fails consistently either way so to shorten it up....
Here is the DDL used to create the catalog...
----
----
CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc, strImagePath,
strSampleMedia, strCreators) KEY INDEX PK_tblStock;
ALTER FULLTEXT INDEX ON tblStock ENABLE;
----
----
Here is the query that does NOT work (followed by one that does...)
SELECT DISTINCT tblStock.*
FROM dbo.tblStock LEFT OUTER JOIN
dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
dbo.tblCategories ON
dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
LEFT OUTER JOIN
dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
dbo.tblToolTypes.lngToolTypePK
WHERE (dbo.tblPriceFile.dtmDateApplied =
(SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
(dbo.tblStock.blnActiveItem = 1)
and freetext(tblStock.*,'marriage')
----
(the next one works)
select distinct * from tblStock where freetext(tblStock.*,'marriage')
----
--|||I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ----
---
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ----
---
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
> dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied =
> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
--
>
>
>

A trick query.

Hi all...

I would like to know how it is possible to make my problem below all in ONE
query or stored procedure.

I select some rows from a table where the resultset is one column with some
values. Lets say 1, 4 and 7:

row val
1 1
2 4
3 7

Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)

Lets say the other table looks like this:

id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'

in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in column
'b'.

Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in the
table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row shall
be deleted.

Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.

I hope that it is understandable and someone can help.

- rick -So ,basically, you want to have all values from the table a and matching
values (if they exist) from table b? How does this work for you:

select
Table1.val,
5 as CriteriaValue, --probably variable...
OtherTable.id,
OtherTable.Text
from
Table1
left join OtherTable on table1.val = OtherTable.a and OtherTable.b = 5

Now, if this query works, you can delete all values from OtherTable that
have a value b = 5 and id not in the select. You can insert the rows
returned by query if you add filter WHERE otherTable.ID is null.
If this doesnt work for you, please elaborate...

MC

"Rick" <rickcool22@.hotmail.comwrote in message
news:45d71f80$0$90276$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

Hi all...
>
I would like to know how it is possible to make my problem below all in
ONE query or stored procedure.
>
I select some rows from a table where the resultset is one column with
some values. Lets say 1, 4 and 7:
>
row val
1 1
2 4
3 7
>
Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)
>
Lets say the other table looks like this:
>
id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'
>
in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in
column 'b'.
>
Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in
the table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row
shall be deleted.
>
Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.
>
I hope that it is understandable and someone can help.
>
- rick -
>

A transport-level error has occurred when sending the request to the server. (provider: TC

when i enter the user name, Password and click enter in my site

it showing below error..

A transport-level error has occurred when sending the request to the server.

(provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

What may be the problem..

This does not sound like a SQL Server problem, it's more likely something is acting up in the network.

Have a look at 2 links below if it still fail after few retries:
http://www.mcse.ms/message2316445.html
http://channel9.msdn.com/ShowPost.aspx?PostID=243278

|||

i am accessing the site through internet..

if i reload the error page it loading successfully..

sql

A significant part of sql server process memory has been paged out

On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?

Error:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.

This means that Windows has decided to move SQL Server memory out of RAM and stored it in disk until it's needed again. This article has some more information on how to avoid this:

http://support.microsoft.com/kb/918483/en-us

You also mentioned that SQL Server has restarted after this. Could you please post the last 10 lines or so of errorlog when this occurred?

Thanks,

Fabricio.

|||

I did look at that article but I don't think that will work for SQL Standard Edition.

Note SQL Server 2005 Enterprise Edition is the only edition that is designed to use lock pages in memory.

|||

In that case, try to identify what's causing Windows to page SQL Server out to disk. SQL Server tries to fill up the available memory on the machine. When you have other apps running on the same machine, they may use a lot of memory and push SQL Server out to the swap file. SQL Server will detect this and trim its memory, but in some rare cases it can't release enough memory or can't release fast enough and may end up being swapped out. This message is to notify you that such event happened and that SQL Server's memory is being fetched from disk, causing some temporary slowness.

Please, let me know if you need help figuring out what app is using memory on the machine.

Thanks,

Fabricio.

|||

I believe the KB article is incorrect stating it’s only valid for x64 Enterprise Edition. I encountered the same problem you reported and the Lock Pages In Memory policy work just fine with x64 standard edition.

|||

This article explains the problem I was having:

http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx

The system was running Windows 2003 SP1. Have upgraded to Windows 2003 SP2 and have not had the problem since.

|||

Kmoskal,

The KB article is correct. Only SQL Server Enterprise will lock pages in memory. You may set the priviledge with any other edition, but it will have no effect in SQL Server. I guess you just have been lucky (and SQL Server has been doing a great job keeping itself in RAM) :-)

Thanks,

Fabricio.

A Sample of an MDX query?

Hello,

I have a cube that includes a measure called student count that is pivoted as data below, row called student statuses and column being a time dimension. The one thing left that I would like to accomplish is to create two extra calculated measures called Difference and %Changed using MDX or any other method? Does anyone has some MDX samples or ideas how I could calculate those two columns in blue in my SQL Server Analysis 2005 cube?

Any help is greatly appreciated!

Spring Semester 2006Spring Semester 2005Difference%ChangeUndergraduates Count490045004008.89%Graudates Count100090010011.11%

You can use Business Intelligence Development Studio to add your calculated measures. Start BIDS in Program Files->Microsoft Sql Server 2005->Sql Server Business Intelligence Development Studio and open your database. Double click on the cube and after the cube is opened, click on to the 3rd tab which says "Calculations" to add new calculated members. For more information, check http://msdn2.microsoft.com/en-us/library/ms169748.aspx.

HTH,

Chu

A question about connecting with SQL server - Help

Hi -Please read below - to help me - thanks

May i explain what i want to do, im a webmaster of my friend's server, i would like to know how i can acess the SQL Server of my friend to put on the homepage ( im doing the home page), so its a remote access, i think, then i want to access a Database the server, i try many times but not happens on the page, it shows blank, im using two programs the ''Web matrix'' and ''Dreamweaver'', please one month ago im trying do to this but it never fuction.

Database name: MuOnline
Table: MEMB_STATS
Column: memb stats

Please i must know it... Thank you !www.connectionstrings.com has a section on SQL Server on the front page, expand it and it will show connection strings and the code to use them.|||i think this site too complex|||i think the site complex, so can u read my case, and say what type of conecction I must use:

please over!

- type of connections:

ODBC
OLE DB, OleDbConnection
sqlconnection
data shape|||SqlConnection. You need to know whether you will use integrated security, or a username and password. No one can tell you which you should use, the type you use is up to how your sql server is set up.|||thanks, but i have 5 questions to do, but if possible can i send a emal to you? i need to know some questions that i dont know....answer me if can i

Thanks otherwise!|||when i finish the connection, how can i do to acess a especifc table or column?
In my case to show who is online in the sql server.|||my second question is; Are the ID and Pass, needed? Or if i want?|||No, I don't think I will help you with your homework. If you have questions, post them here and we will see what we can do.|||This depends upon how your database was set up by whoever installed it. Do you have a username and password? If so, use that.|||http://www.dotnetjunkies.com/quickstart/aspplus/doc/webdataaccess.aspx#adoplus|||douglas thanks!

when i finish the connectinstring, and the conection to the tables...nothing more to do?
i have to do this page on a .ASP page??

Thanks otherwise.|||This is an ASP.NET forum, and I have been pointing you to resources got use with ASP.NET, not classic ASP.

If this IS ASP.NET, after this code:

Dim myConnection As New SqlConnection( _
"server=(local)\NetSDK;database=pubs;Integrated Security=SSPI")
Dim myCommand As New SqlDataAdapter("select * from Authors", myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds, "Authors")

you can check:

ds.Tables[0].Rows[0]["Name Of Your Field"].ToString() to get a string representation of the field named.|||thanks so much!

The sql server dont have username and pass.
---------------------------
Dim myConnection As New SqlConnection( _
"server=(local)\NetSDK;database=pubs;Integrated Security=SSPI")
Dim myCommand As New SqlDataAdapter("select * from Authors", myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds, "Authors")

ds.Tables[0].Rows[0]["Name Of Your Field"].ToString()
----------------------------
can i use this to acess my sql server??|||''pubs''= the name of the database
''server'' = DSN
'' Authors'' = table
second ''Authors''= name of column
''Name of Your field''= name of column]

IS it correct?
if it isnt correct over for me please.

-----------------
Dim myConnection As New SqlConnection( _
"server=(local)\NetSDK;database=pubs;Integrated Security=SSPI")
Dim myCommand As New SqlDataAdapter("select * from Authors", myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds, "Authors")

ds.Tables[0].Rows[0]["Name Of Your Field"].ToString()

A Query Help!

I am trying to use table 'Account' and table 'Balance' to get the ideal
result table listed below, but my query is too cumbersome. If an
account opened 10 years ago, I would have 12*10=120 rows in my query.
Is there any simplied way to do this, like a do-loop thing? Thanks a
million!

Table Account
account_noopen_date
11111/1/2000
22222/1/2002

Table Balance
account_nobalbal_date
1111501/31/2000
11111002/29/2000
11111503/31/2000
11112004/30/2000
11112505/31/2000
11113006/30/2000
11113507/31/2000
11114008/31/2000
22225002/29/2002
22226003/31/2002
22227004/30/2002
22228005/31/2002
22229006/30/2002

Ideal Result Table
account_noopen_datemonth0_balmonth1_balmonth2_balmonth3_balmonth4_bal
11111/1/200050100150200250
22222/1/2002500600700800900

--My Query--
SELECT a.account_no
,a.open_date
,sum(case datediff(mm, a.open_date, b.bal_date) when 0 then b.bal else
0 end) as 'month0_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 1 then b.bal else
0 end) as 'month1_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 2 then b.bal else
0 end) as 'month2_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 3 then b.bal else
0 end) as 'month3_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 4 then b.bal else
0 end) as 'month4_bal'
FROM account as a
LEFT OUTER JOIN balance as b
on a.account_no=b.account_no
GROUP BY a.account_no, a.open_date
ORDER BY a.account_no, a.open_date(rong.guo@.gmail.com) writes:
> I am trying to use table 'Account' and table 'Balance' to get the ideal
> result table listed below, but my query is too cumbersome. If an
> account opened 10 years ago, I would have 12*10=120 rows in my query.
> Is there any simplied way to do this, like a do-loop thing? Thanks a
> million!

That's indeed how cross-tab queries usually looks like.

You may be interested in checking out RAC, which is a third-party tool
which is good for dynamic crosstabs. Check out http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I will look into it...

A little script help

Using the data below as an example I am looking for help with script
that will return all rows of data where neither Field A or B are not 0
or Null

NameAB
John2
John1
John0
John
Ste1
Ste
Paul5
Paul
Paul0

Regards,
CiarnDo you really mean where EITHER A or B are not 0 or not NULL? Try:

WHERE A>0 OR B>0

conversely:

WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL

--
David Portas
SQL Server MVP
--|||I tried your suggestions without success.
Using the data above, I want to return.

Name A B
John 2
John 1
Ste 1
Paul 5

Regards,
Ciarn|||The following should work:

WHERE ISNULL(A, 0) <> 0 OR ISNULL(B, 0) <> 0

-Tom.|||This is where it helps if you include CREATE TABLE and INSERT
statements with your question. The following works for me:

CREATE TABLE YourTable (name VARCHAR(10), a INTEGER NULL, b INTEGER
NULL /* PRIMARY KEY ? UNSPECIFIED */)

INSERT INTO YourTable (name,a,b)
SELECT 'John', 2 , NULL UNION ALL
SELECT 'John', NULL, 1 UNION ALL
SELECT 'John', 0 , NULL UNION ALL
SELECT 'John', NULL, NULL UNION ALL
SELECT 'Ste', NULL, 1 UNION ALL
SELECT 'Ste', NULL, NULL UNION ALL
SELECT 'Paul', 5 , NULL UNION ALL
SELECT 'Paul', NULL, NULL UNION ALL
SELECT 'Paul', NULL, 0

SELECT name, a, b
FROM YourTable
WHERE A>0 OR B>0

Result:

name a b
---- ---- ----
John 2 NULL
John NULL 1
Ste NULL 1
Paul 5 NULL

What did you do differently and what result did you get?

Does this table have a primary key? It should do, and it helps if you
specify the key when you post a question.

--
David Portas
SQL Server MVP
--|||Perfect.
Cheers|||For clarity sake try:

WHERE ISNULL(A,0) <> 0
AND ISNULL(B,0) <> 0

GeoSynch

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113296365.931060.144050@.f14g2000cwb.googlegr oups.com...
> Do you really mean where EITHER A or B are not 0 or not NULL? Try:
> WHERE A>0 OR B>0
> conversely:
> WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL
> --
> David Portas
> SQL Server MVP
> --