A doubt about this: "Could not find stored Procedure"

Hi Everybody!

I have a problem in my SQL Server 2000 SP1 Database Called "Embossamento". When I run the following comand in the Query Analyzer, I have this error message :

command: exec dbcc_all_dbreindex
message: Could not find stored procedure 'dbcc_all_dbreindex'.

Otherwise, in the same server, but in another Database called "Autorizacao" I execute this dbcc_all_dbreindex with no problems.
Does anybody know why this happens?
I will be waiting for some reply, ok?
Thanks,This must be a custom stored procedure that uses the dbcc dbreindex command - check out the database, Autorizacao, and look for your stored procedures under that database.|||Ok! Thanks!
It worked! I just realized that my database did not have the procedure dbcc_all_dbreindex.
Now it's ok...

A domain error occurred. from SQL statement, only when above 10

Attempting to run a query that is looping through cursor, anything over
choosing the TOP 10 from a table for the cursor results in a
"A domain error occurred."
error message? below 10 all runs fine. What is this message caused by?It usually happens when performing mathematical functions
and using values outside of acceptable ranges.
-Sue
On Thu, 20 Nov 2003 11:51:01 -0600, "Kory"
<kory@.removeme-mlsc.com> wrote:
>Attempting to run a query that is looping through cursor, anything over
>choosing the TOP 10 from a table for the cursor results in a
>"A domain error occurred."
>error message? below 10 all runs fine. What is this message caused by?
>

a Distinct Query

I have 2 tables as following :

tbl_Articles
3 ArticleID int
0 AuthorID int
0 ArticleTitle
0 ArticleText
0 ArticleDate

tbl_Authors
3 AuthorID
0 AuthorFullName
0 AuthorEmail
0 AuthorDescription
0 AuthorImage

I want to write a query to see the Authors and their last articles with no distinct values.
Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate

If anyone knows the solution i will be glad .
Thanks from now onselect AuthorImage, AuthorFullName, ArticleTitle, ArticleDate = aDate
from tbl_Authors a
inner join (
select AuthorID, aDate = max(ArticleDate)
from tbl_Articles) x
on a.AuthorID = x.AuthorID
inner join tbl_Articles b
on x.AuthorID = b.AuthorID
and x.aDate = b.ArticleDate|||another version:select AuthorImage
, AuthorFullName
, ArticleTitle
, ArticleDate
from tbl_Authors AUTH
inner
join tbl_Articles ART
on AUTH.AuthorID
= ART.AuthorID
where ART.ArticleDate
= ( select max(ArticleDate)
from tbl_Articles
where AuthorID
= AUTH.AuthorID )

A DB block during stored procedure excecution...

Hi all,
I'd like to ask if is there any deference between executing a stored
procedure with 'exec' command in the Query analyzer and executing the code o
f
the stored procedure in Query analyzer too. I mean that I copied the source
of stored procedure in a window of the analyzer, I declare the parameters of
stored procedure as variables and I set the same values into them and I just
run the source...
When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
the parameters sometimes it occures a block... When I run the source
declaring the parameters as variables etc, all are fine and I have never any
block... How is it possible? Is there any idea?
Thanks in advance..Christos,
What do you mean by block?
See if this helps:
[url]http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/a5517668
94ed8781? q=%22what+is%22%2B%22parameter+sniffing%
22&hl=en&lr=&ie=UTF-8&rnum=1[/url
]
AMB
"Christos" wrote:

> Hi all,
> I'd like to ask if is there any deference between executing a stored
> procedure with 'exec' command in the Query analyzer and executing the code
of
> the stored procedure in Query analyzer too. I mean that I copied the sourc
e
> of stored procedure in a window of the analyzer, I declare the parameters
of
> stored procedure as variables and I set the same values into them and I ju
st
> run the source...
> When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
> the parameters sometimes it occures a block... When I run the source
> declaring the parameters as variables etc, all are fine and I have never a
ny
> block... How is it possible? Is there any idea?
> Thanks in advance..
>|||I mean that stored procedure never ends, so it is blocking other users from
using the same tables etc (the sp updates data in some tables...)|||Christos,
If the sp never ends could be because another process is blocking the
resources needed by the sp and it is waiting, or the workload is heavy.
You can use EM (Management - Current Activity) or execute sp_who2 from QA to
see the processes and locks. You can also use Profiler to trace locks.
AMB
"Christos" wrote:

> I mean that stored procedure never ends, so it is blocking other users fro
m
> using the same tables etc (the sp updates data in some tables...)|||The strange in this situation is that when I have a block during the sp
execution, if I kill the process of sp and I try again to run it separetely
with the same parameters in the Q Analyzer's environment, the blocking
happens again. If I 'export' the source code in the analyzer's window and ru
n
it again using the parameters as variables it finishes imediatelly without
any problem...sql

A critical SQL Server problem

Hi,

Once, I wanted to attach a database to SQL Server by means of "CREATE DATABASE ... FOR ATTACH" command, in Query Analizer.

I encountered an error and attaching database failed.
(The error was something like this : "the phisical file 'FILENAME' maybe is incorrect")

But after going to Enterprice Manager, I realised that a database whitout a name was created.

Since then, I wasn't able to do any process in SQL Server whitout either not recieving an error or failing the process.

I decided to uninstall and reinstall SQL Server again.
But after uninstalling, whenever I want to reinstall SQL Server I get this error :

"A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup."

Which restarting does not help.

Now :

1- What was the reason of creation a null-named database, and how can I prevent it next time?

2- What should I do to Install SQL Server again?

Ooh, that doesn't sound healthy at all. I think your best bet would've been to have dropped the database using something really similar to the script you used to create it.

Now that you've tried to uninstall SQL Server, I think things have got worse. You may need us to find registry entries for you to play with, and things like that (but I don't know them off the top of my head). If this doesn't help, you may find yourself needing to rebuild your system. :(

Rob|||

I tried to drop database in diferent ways but they didn't work. That was the reason of why

I unistalled the SQL Server. And I don't know how to deal with registry for this problem.

|||Very carefully, go through this article. I hope it helps:

http://support.microsoft.com/kb/312995

A critical SQL Server problem

Hi,

Once, I wanted to attach a database to SQL Server by means of "CREATE DATABASE ... FOR ATTACH" command, in Query Analizer.

I encountered an error and attaching database failed.
(The error was something like this : "the phisical file 'FILENAME' maybe is incorrect")

But after going to Enterprice Manager, I realised that a database whitout a name was created.

Since then, I wasn't able to do any process in SQL Server whitout either not recieving an error or failing the process.

I decided to uninstall and reinstall SQL Server again.
But after uninstalling, whenever I want to reinstall SQL Server I get this error :

"A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup."

Which restarting does not help.

Now :

1- What was the reason of creation a null-named database, and how can I prevent it next time?

2- What should I do to Install SQL Server again?

Ooh, that doesn't sound healthy at all. I think your best bet would've been to have dropped the database using something really similar to the script you used to create it.

Now that you've tried to uninstall SQL Server, I think things have got worse. You may need us to find registry entries for you to play with, and things like that (but I don't know them off the top of my head). If this doesn't help, you may find yourself needing to rebuild your system. :(

Rob|||

I tried to drop database in diferent ways but they didn't work. That was the reason of why

I unistalled the SQL Server. And I don't know how to deal with registry for this problem.

|||Very carefully, go through this article. I hope it helps:

http://support.microsoft.com/kb/312995

a count query

Hi all,
I need to built an efficient query that would be able to tell me
the amount of times that the field 'mark' has an 'x'. It should count
only one time for a given id. In the following example for id 23 the
count should be only 1 even though it occurs more than once.
At the end, the total count for the example should be 4
instead of 6.
Table A
id mark
== =====
23 x
23 x
25 x
27 x
27
27 x
30
31 x
31
35
Thanks in advance,
CarlosCarlos wrote:
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>
SELECT DISTINCT
id,
CASE WHEN mark = 'x' THEN 1 ELSE 0 END AS markcount
FROM table|||Try,
select count(distinct [id]) from tableA where mark = 'x'
AMB
"Carlos" wrote:

>
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>|||CREATE TABLE #tbl
(
id INT,
Mark char(1)
);
SET NOCOUNT ON;
INSERT #tbl
SELECT 23,'x'
UNION ALL SELECT 23,'x'
UNION ALL SELECT 25,'x'
UNION ALL SELECT 27,'x'
UNION ALL SELECT 27,''
UNION ALL SELECT 27,'x'
UNION ALL SELECT 30,''
UNION ALL SELECT 31,'x'
UNION ALL SELECT 31,''
UNION ALL SELECT 35,'';
SELECT MarkCount = SUM(c)
FROM
(
SELECT
id,
c = MAX(CASE mark WHEN 'x' THEN 1 ELSE 0 END)
FROM #tbl
GROUP BY id
) x
DROP TABLE #tbl;
"Carlos" <ch_sanin@.yahoo.com> wrote in message
news:%23ODaJQ%23jGHA.1324@.TK2MSFTNGP04.phx.gbl...
>
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>|||Tracy McKibben wrote:
> Carlos wrote:
> SELECT DISTINCT
> id,
> CASE WHEN mark = 'x' THEN 1 ELSE 0 END AS markcount
> FROM table
Sorry, copy/pasted the wrong block from QA... What I meant to post was:
SELECT DISTINCT
id,
1 AS markcount
FROM table
WHERE mark = 'x'sql

a copy of SQLFile.sql please :: MS SQL 2005

when trying New Query in MS SQL 2005 management studio I get the following error :
cannot find the file
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbe nchprojectitems\Sql\SQLFile.sql

is someone could paste me here the content of SQLFile.sql ?

thank youan emty file !
it works

a complex query needs a solution

I need help with determining which salespeople should be
assigned bonuses based off a relationship between the
salesperson and their manager and also the relationship
between the salesperson and the customer. Below are 3
tables, the salesperson table which contains the
salesperson's number and a joblevel column. The joblevel
you can think of 10 = manager and 5 = worker. There are
more levels but just assume the higher the number the
higher the level of the person. Next is the customer
table. After that is the customer to salesrep assignment
table. If you read the first insert, we insert for
customer 779 the rep 5000 who happens to be a manager, a
date of 1/1/04, and an ownership of 1 (100%). That means
that the rep 5000 would get 100% of his bonus based on the
revenue that customer 779 generates. Next you see for the
same customer 5002 (a subordinate) is inserted. After
that you see at 3/1/04 a new subordinate level person
(level = 5) gets inserted. This means that at 1/1/04
subordinate 5002 is assigned to cusomter 779 but at 3/1/04
subordinate 5003 is assigned to customer 779. Since no
subordinate is assigned at 7/1/04, then we conclude that
5003 is still assigned to 779 at that date.
Skipping down we can se for customer 716 that 2 level=5
subordinates are assigned but one's ownership is 40% and
the other's is 60%. the next month 2 new subordinates are
rotated in. Don't worry about the insert logic to this
table. If 2 subordinates are split on a customer and one
gets replaced the next month, the other gets replaced too
so that in one month the total for each joblevel = 1 (100%)
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
create table customer (custno int not null primary key)
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership int check (ownership between 0 and 1),
primary key (custno, salesrepno, dateassigned))
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004')
insert customer_salesrep values (879, 5002, '1/1/2004')
insert customer_salesrep values (879, 5001, '7/1/2004')
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
I want to run a query where I pass in the manager level
number (in this case 5000) and a date. I want to know who
(the subordinate) is assigned to the customer at any date
I pass in. If another manager is assigned to a customer
(like 5009) then I don't want to see the customer
information for him.
If I run the query at 1/1/2004 for the manager 5000, I
should get in the results
779 5002 1
716 5005 .4
716 5006 .6
879 5002 1
If I run the query at 3/1/2004 for the manager 5000, I
should get in the results
779 5003 1
716 5007 .4
716 5008 .6
879 5002 1
If I run the query at 7/1/2004 for the manager 5000, I
should get in the results (notice customer 879 drops off
because another manager takes over the relationship)
779 5003 1
213 5004 1
716 5007 .4
716 5008 .6
Please let me know if you need any more information"Jerry Fortaine" <anonymous@.discussions.microsoft.com> wrote in message
news:26b001c50d4d$8823af10$a401280a@.phx.gbl...
>I need help with determining which salespeople should be
> assigned bonuses based off a relationship between the
> salesperson and their manager and also the relationship
> between the salesperson and the customer. Below are 3
> tables, the salesperson table which contains the
> salesperson's number and a joblevel column. The joblevel
> you can think of 10 = manager and 5 = worker. There are
> more levels but just assume the higher the number the
> higher the level of the person. Next is the customer
> table. After that is the customer to salesrep assignment
> table. If you read the first insert, we insert for
> customer 779 the rep 5000 who happens to be a manager, a
> date of 1/1/04, and an ownership of 1 (100%). That means
> that the rep 5000 would get 100% of his bonus based on the
> revenue that customer 779 generates. Next you see for the
> same customer 5002 (a subordinate) is inserted. After
> that you see at 3/1/04 a new subordinate level person
> (level = 5) gets inserted. This means that at 1/1/04
> subordinate 5002 is assigned to cusomter 779 but at 3/1/04
> subordinate 5003 is assigned to customer 779. Since no
> subordinate is assigned at 7/1/04, then we conclude that
> 5003 is still assigned to 779 at that date.
> Skipping down we can se for customer 716 that 2 level=5
> subordinates are assigned but one's ownership is 40% and
> the other's is 60%. the next month 2 new subordinates are
> rotated in. Don't worry about the insert logic to this
> table. If 2 subordinates are split on a customer and one
> gets replaced the next month, the other gets replaced too
> so that in one month the total for each joblevel = 1 (100%)
> create table salesrep (salesrepno int not null primary
> key, joblevel int not null)
> insert salesrep values (5000, 10)
> insert salesrep values (5001, 10)
> insert salesrep values (5002, 5)
> insert salesrep values (5003, 5)
> insert salesrep values (5004, 5)
> insert salesrep values (5005, 5)
> insert salesrep values (5006, 5)
> insert salesrep values (5007, 5)
> insert salesrep values (5008, 5)
> insert salesrep values (5009, 10)
> insert salesrep values (5010, 5)
> create table customer (custno int not null primary key)
> insert customer values (234)
> insert customer values (332)
> insert customer values (213)
> insert customer values (716)
> insert customer values (879)
> insert customer values (267)
> create table customer_salesrep (
> custno int not null foreign key references customer,
> salesrepno int not null foreign key references salesrep,
> dateassigned datetime not null,
> ownership int check (ownership between 0 and 1),
> primary key (custno, salesrepno, dateassigned))
> insert customer_salesrep values (779, 5000, '1/1/2004', 1)
> insert customer_salesrep values (779, 5002, '1/1/2004', 1)
> insert customer_salesrep values (779, 5003, '3/1/2004', 1)
> insert customer_salesrep values (332, 5000, '1/1/2004', 1)
> insert customer_salesrep values (213, 5000, '7/1/2004', 1)
> insert customer_salesrep values (213, 5004, '7/1/2004', 1)
> insert customer_salesrep values (716, 5000, '1/1/2004', 1)
> insert customer_salesrep values (716, 5005, '1/1/2004', .4)
> insert customer_salesrep values (716, 5006, '1/1/2004', .6)
> insert customer_salesrep values (716, 5007, '3/1/2004', .4)
> insert customer_salesrep values (716, 5008, '3/1/2004', .6)
> insert customer_salesrep values (879, 5000, '1/1/2004')
> insert customer_salesrep values (879, 5002, '1/1/2004')
> insert customer_salesrep values (879, 5001, '7/1/2004')
> insert customer_salesrep values (267, 5009, '1/1/2004', 1)
> insert customer_salesrep values (267, 5010, '1/1/2004', 1)
> I want to run a query where I pass in the manager level
> number (in this case 5000) and a date. I want to know who
> (the subordinate) is assigned to the customer at any date
> I pass in. If another manager is assigned to a customer
> (like 5009) then I don't want to see the customer
> information for him.
>
> If I run the query at 1/1/2004 for the manager 5000, I
> should get in the results
> 779 5002 1
> 716 5005 .4
> 716 5006 .6
> 879 5002 1
>
> If I run the query at 3/1/2004 for the manager 5000, I
> should get in the results
> 779 5003 1
> 716 5007 .4
> 716 5008 .6
> 879 5002 1
> If I run the query at 7/1/2004 for the manager 5000, I
> should get in the results (notice customer 879 drops off
> because another manager takes over the relationship)
> 779 5003 1
> 213 5004 1
> 716 5007 .4
> 716 5008 .6
> Please let me know if you need any more information
-- Customer and sales rep along with job level of rep
CREATE VIEW AccountReps (custno, salesrepno, joblevel, dateassigned, ownersh
ip)
AS
SELECT CSR.custno, CSR.salesrepno, SR.joblevel, CSR.dateassigned, CSR.owners
hip
FROM customer_salesrep AS CSR
INNER JOIN
salesrep AS SR
ON CSR.salesrepno = SR.salesrepno
-- For each customer account, all overlaps between a manager's and
-- worker's tenure
CREATE VIEW AccountAssignments
(custno, manager, manager_ownership,
manager_dateassigned, manager_dateunassigned,
worker, worker_ownership, worker_dateassigned, worker_dateunassigned)
AS
SELECT M.custno, M.salesrepno, M.ownership,
M.dateassigned, M.dateunassigned,
W.salesrepno, W.ownership, W.dateassigned, W.dateunassigned
FROM (SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R1.joblevel = R2.joblevel AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel = 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS M
INNER JOIN
(SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R2.joblevel < 10 AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel < 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS W
ON W.dateassigned < M.dateunassigned AND
W.dateunassigned > M.dateassigned AND
W.custno = M.custno
-- For manager 5000 on 20040101
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040101' AND
worker_dateunassigned > '20040101' AND
manager_dateassigned <= '20040101' AND
manager_dateunassigned > '20040101'
ORDER BY custno, worker
-- For manager 5000 on 20040301
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040301' AND
worker_dateunassigned > '20040301' AND
manager_dateassigned <= '20040301' AND
manager_dateunassigned > '20040301'
ORDER BY custno, worker
-- For manager 5000 on 20040701
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040701' AND
worker_dateunassigned > '20040701' AND
manager_dateassigned <= '20040701' AND
manager_dateunassigned > '20040701'
ORDER BY custno, worker
-- Note that we can check assignments on an arbitrary date
-- For manager 5000 on 20040515
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040515' AND
worker_dateunassigned > '20040515' AND
manager_dateassigned <= '20040515' AND
manager_dateunassigned > '20040515'
ORDER BY custno, worker
JAG|||Hi Jerry. I see that John already posted an answer to your question. I
took a look and came up with something a bit different. I think that the
problem lies in that you have relationships and data that are implicit in
your table schema. It may make for faster queries if you explicitly set an
owning manager to each line item, rather than calculate it each time you ran
your query. Right now this ownership is burried in the ordering of the
rows which is a bit tricky. It may be possible to create an indexed view
with this information in it. Maintaining "reportsto" relationships would
also make the queries a bit cleaner.
Here is my attempt:
--use master
--go
--DROP DATABASE complexquery
--go
create database complexquery
go
use complexquery
go
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
go
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
go
--delete from salesrep
create table customer (custno int not null primary key)
go
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
insert customer values (779) -- bug!bug! added value
go
--drop table customer_salesrep
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership decimal(5,2) check (ownership between 0 and 1), -- bug!bug! int
truncation, changed to decimal
primary key (custno, salesrepno, dateassigned))
go
delete from customer_salesrep
go
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004', 1)
insert customer_salesrep values (879, 5002, '1/1/2004', 1)
insert customer_salesrep values (879, 5001, '7/1/2004', 1)
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
go
--drop function utvf_whichboss
CREATE FUNCTION utvf_whichboss(@.lookupdate datetime) returns table as return
--
-- This function returns the list of customer accounts
-- that are owned by a given manager at any time. Caller
-- still needs to find the maximum dateassigned since all
-- previous owning managers will also show up.
SELECT
csr.salesrepno AS mgrno, csr.dateassigned, csr.custno
FROM
customer_salesrep csr
JOIN salesrep sr ON sr.salesrepno = csr.salesrepno
WHERE
sr.joblevel = 10
AND dateassigned <= @.lookupdate
GO
--drop function utvf_subs
CREATE FUNCTION utvf_subs( @.mgrno int, @.lookupdate datetime) RETURNS TABLE
AS RETURN
--
-- this function returns all of the rows that match a given mgrno and within
-- the lookupdate range. Caller still must find the max(dateassigned) since
-- it does not track duplicates (change of ownership)
SELECT csr2.custno, csr2.salesrepno, csr2.ownership, csr2.dateassigned
FROM
customer_salesrep csr2
join salesrep sr2 on sr2.salesrepno = csr2.salesrepno
WHERE csr2.custno IN
(
SELECT b.custno
FROM utvf_whichboss(@.lookupdate) b
WHERE b.dateassigned = (
SELECT max(dateassigned)
FROM utvf_whichboss(@.lookupdate)
WHERE custno = b.custno
)
AND b.mgrno = @.mgrno
)
AND sr2.joblevel = 5
AND csr2.dateassigned <= @.lookupdate
GO
--drop procedure usp_getsubs
CREATE PROCEDURE usp_getsubs
@.mgrno INT,
@.lookupdate DATETIME
AS
--
-- This sproc takes all of the rows that belong to
-- a given manager at a certain time and prunes out the
-- duplicates (i.e. customers who's owning subordinate have
-- changed.) It should only return the current subordinate
SELECT s.custno, s.salesrepno, s.ownership
FROM utvf_subs(@.mgrno, @.lookupdate) s
WHERE
s.dateassigned = (
SELECT MAX(dateassigned)
FROM utvf_subs(@.mgrno, @.lookupdate)
WHERE custno = s.custno
)
ORDER BY s.dateassigned
GO
-- Sample tvf output: note that all of the
-- rows are present, even though the ownership
-- has changed to a new subordinate.
SELECT * FROM utvf_subs(5000, '3/1/2004')
GO
-- sample sproc output: these are the samples in your
-- original mail. They all output as you described
-- but I could not preserve your ordering since it does
-- not appear to be encoded in your data anywhere
EXEC usp_getsubs 5000, '1/1/2004'
GO
EXEC usp_getsubs 5000, '3/1/2004'
GO
EXEC usp_getsubs 5000, '7/1/2004'
GO

a complex query

I have the following SQL 2000 database table:
NEWS (IDNews, Country, PublishDate, Title)

I have to get a dataset containing only one record for each country, having most recent publish date.
Any suggestions? Thanks.SELECT TOP 1 IDNews, Country, PublishDate, Title From News Group By Country Order By PublishDate DESC?|||Try this:

select IDNews, Country, PublishDate, Title
from NEWS
where IDNews in
(
select
max(IDnews)
from news n
join
(select
country, max(publishdate) as publishdate
from news
group by country) md
on n.country = md.country
and n.publishdate = md.publishdate
group by n.country
)|||

select
 news.*
from
 news
 inner join
 (select
 country, max(publishdate) as publishdate
 from
 news
 group by
 country
 ) as TMP
 on news.country = TMP.country
 and news.publishdate = TMP.publishdate

sql

A Common Query Question!

Hi Guys,

I have a query that gives me multiple rows for every customer, where as my requirement is to have one row for each customer. Below are the details:

Customers Table

Cust_ID int (PK)

Cust_Name varchar(255)

Subscription_Status int

Cust_Subscription_Audit Table

ID Identity(PK)

Cust_ID int

Old_Subscription_Status int

New_Subscription_Status int

Audit_Date DateTime

Logins Table (one customer may have multiple logins )

ID Identity (PK)

Cust_ID int

login name varchar(256)

email_ID varchar(256)

F_Name varchar(50)

L_name varchar(50)

role int

Last_Modified DateTime

INDEXES

Logins Table

PK__Clustered Ascending

No Index on Audit Table

Customers Table

PK__Clustered_ Ascending

Below is the query that is being used to extract the data

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID,MAX(CSA.Audit_Date)

FROM Customers C

INNERJOIN Cust_Subscription_Audit CSA ON C.Cust_ID= CSA.Cust_ID

INNERJOIN Logins L ON L.Cust_ID= C.Cust_ID

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUPBY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

This gives me multiple rows for a customer. What I am looking for is

1) One row for each customer.(Query 1)

2) If possible can pick the row of my choice Like log in that was added first or last based on login or Modified date in login table.(Query 2)

3) If possible can pick the row by row number i.e. if there are multiple logins for a customer then the 2nd login. (Query 3)

Your help will be appreciated.

Thanks

-Leo

Can you post some DDL, including constraints and indexes, sample data and expected result, please?

AMB

|||

Hi ,

Thanks for your prompt reply. Here is the Sample Data

Customers Table

Cust_ID Cust_Name SubsStatus

1 abc corp. 7

2 Adventure Works Inc 1

3 MicroCorn Inc 2

4 Fabrikam 7

5 Goleeeee Inc 5

Logins Table

ID Cust ID Login Email f_Name l_Name Role Date_Modified

1 1 rob1 rob1@.hotmail.com Rob Haany 1 2006-02-28 13:24
2 2 scot1 scot1@.yahoo.com Scot claudia 1 2006-05-24 12:30
3 3 scot2 scot2@.hotmail.com Scot Roy 1 2006-05-24 12:30
4 4 rob NULL RON HARTON 1 2006-08-22 15:26
5 4 qsir qsir@.hotmail.com Q Sir 1 2006-09-17 12:23
6 5 nzaar nzaar@.hotmail.com nad zaar 3 2006-09-17 12:23
7 5 jluk jluk@.hotmail.com Ron Richard 1 2006-09-17 12:23
8 2 drase draze@.hotmail.com Dino Mosoli 1 2006-08-22 15:26
9 2 dlins David Lin 3 2006-12-28 13:24
10 4 dmay NULL David May 1 2006-05-24 12:30

Role 1 = admin

Role 3 = Primary Contact

ID Cust_ID Old_Subscription_Status New_Subscription_Status Audit_Date

1 1 3 7 2006-09-24 12:30

2 2 0 1 2006-03-24 02:30

3 3 1 2 2006-08-24 10:30

4 4 1 7 2006-11-24 12:30

5 5 3 5 2006-09-24 12:30

6 4 3 1 2006-10-24 12:30

7 3 0 1 2006-06-24 12:30

8 1 0 1 2006-02-24 12:30

9 1 1 3 2006-07-24 12:30

10 5 1 3 2006-07-24 12:30

The required result is

Cust_ID Cust_Name F_name L_Name Email Audit Date

1 abc Corp Rob Haany rob1@.hotmail.com 2006-09-24 12:30

2 Adventure Works Inc Scot claudia scot1@.yahoo.com 2006-03-24 02:18

3 MicroCorn Inc Scot Roy scot2@.hotmail.com 2006-08-24 10:28

4 Fabrikam Q Sir qsir@.hotmail.com 2006-11-24 12:30

5 Goleeee Inc Ron Richard jluk@.hotmail.com 2006-09-24 12:30

Right now what is happening is threre are more than one records for Cust_ID = 2. The basic question is (Query 1) is how can we restrict the result set to only one row per Cust_ID. So my other questions (Query 2, Query3) were how can we choose one of these records based on some criteria e.g. since more than one records are there for Cust_ID = 2, and I wana choose the one that is with earliest Date_Modified or 2nd earliest Modified date.

Thanks,

-Leo.

|||

hi Leo,

If you are using SQL server 2005 Try this:

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

CROSS APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID ) L

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||

Thank you very much for this nice hint. I tweeked the query little bit and it woked for me here is the modified query.

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

OUTER APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID And [Role] = 1 ) L

WHERE C.Subscription_Status >=1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||do you need OUTER APPLU here? OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function (pretty much like OUTER JOIN in case of normal table). So if there is no match in table 'Logins' the value of L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID will be NULL.

A clause of the query contained only ignored words

Using FULL-Text search in SQL server and fornt end is VB6.0
My SQL statement is:
"Select English,Japanese from (select English,Japanese from S_R_J_E
where CONTAINS(English,'WWW')) as EJ where English='WWW' "
But it cause problem when exact match for "WWW" is not found in
database and error message is shown:
"A clause of the query contained only ignored words."
I already known that it caused for noise.* files.
But how I can solve it.
Pls help.
Molay Ash
Molay
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message336819.htmlI don't do a lot of fulltext, but try this and see if it works any better
Select English,Japanese from S_R_J_E
where CONTAINS(English,'WWW')) and English='WWW'
It seems kind of strange that you would need to search for the word WWW with
fulltext when you are requiring that the entire field be EXACTLY 'WWW'.
You might also try the containstable command instead of contains... (Docd
in books on line)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Molay" <Molay.10qdal@.mail.mcse.ms> wrote in message
news:Molay.10qdal@.mail.mcse.ms...

quote:

---

> Using FULL-Text search in SQL server and fornt end is VB6.0
> My SQL statement is:
> "Select English,Japanese from (select English,Japanese from S_R_J_E
> where CONTAINS(English,'WWW')) as EJ where English='WWW' "
> But it cause problem when exact match for "WWW" is not found in
> database and error message is shown:
> "A clause of the query contained only ignored words."
> I already known that it caused for noise.* files.
> But how I can solve it.
> Pls help.
> Molay Ash
>
> Molay
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message336819.html
>

---

A clause of the query contained only ignored words

Hi all,
I am getting this error when my query contains a word like "of":
"A clause of the query contained only ignored words."
The SQL statement uses the CONTAINS clause. This used to work fine on SQL
Server 2000, but I recently switched to SQL Server 2003 and can't get it to
work. I don't want to have any ignored words. I tried to rename all noise.*
files into _noise.* and then rebuilt the Full-Text Catalog, but that did not
fix it.
Anything else I need to do to get rid of all noise words?
Thanks.
Abe
http://www.indexserverfaq.com/noise.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Abe Simpson" <abe@.simpson.com> wrote in message
news:%23QMJJHpGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am getting this error when my query contains a word like "of":
> "A clause of the query contained only ignored words."
> The SQL statement uses the CONTAINS clause. This used to work fine on SQL
> Server 2000, but I recently switched to SQL Server 2003 and can't get it
to
> work. I don't want to have any ignored words. I tried to rename all
noise.*
> files into _noise.* and then rebuilt the Full-Text Catalog, but that did
not
> fix it.
> Anything else I need to do to get rid of all noise words?
> Thanks.
> Abe
>
|||Able,
Could you post the full output of the following SQL code as it would be most
helpful information in helping you.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
You are getting this error because "of" is considered a noise or stop word
that is maintained in the language-specific noise word files under
\FTDATA\SQLServer\Config\noise.* where * is a three-letter code for your
FT-enable column's "Language for Word Breaker". Specifically, noise.enu = US
English and you can edit this file with notepad.exe and remove "of" and
other words and single letters and digits, but leave at least a single
space, if you decide to remove all noise words. Note, you will have to stop
the "Microsoft Search" (MSSearch.exe) service before saving the file and
then run a Full Population on all of your FT Catalog for the change to be
effective.
Note, there is no such version as "SQL Server 2003". Are you perhaps
refereing to SQL Server 2005 (codename Yukon)?
If so, the noise word files are maintained in a different location
(\MSSQL.1\MSSQL\Binn\FTERef) and with different file names, such as
noiseENU.txt for US English. You will also need to stop a different service
MSFTESQL and then run a Full Population. If you are not using SQL Server
2005, could you post the full output of SELECT @.@.version ?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Abe Simpson" <abe@.simpson.com> wrote in message
news:#QMJJHpGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am getting this error when my query contains a word like "of":
> "A clause of the query contained only ignored words."
> The SQL statement uses the CONTAINS clause. This used to work fine on SQL
> Server 2000, but I recently switched to SQL Server 2003 and can't get it
to
> work. I don't want to have any ignored words. I tried to rename all
noise.*
> files into _noise.* and then rebuilt the Full-Text Catalog, but that did
not
> fix it.
> Anything else I need to do to get rid of all noise words?
> Thanks.
> Abe
>
sql

a clause of the query contained only ignored words

Hi,
I don't want any noise words at all for my current application, so have
cleared out the noise.enu file so it is 0 bytes and re-built the catalog
I am pretty sure it is working ok because I can search on 'the' and 'of' etc
which I couldn't do before. however, if I try to search on a single letter,
I still get the old 'only ignored words' error message.
am I hitting on some different limitation here, or is there anything else
that could cause this problem?
thanks in advance
Andy
noise.enu is for us english. noise.eng is for the Queens English.
Which word breaker are you using?
Did you edit the noise word lists found in c:\Program Files\Microsoft SQL
Server\MSSQL\FTData\SQLServer\config?
You have to stop MSSearch, make your edits and then restart MSSearch.
Note, to find these noise words in your searches you will have to rebuild
your catalog.
A freetext search will not generate this error.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:TdQIc.2174$322.25169020@.news-text.cableinet.net...
> Hi,
> I don't want any noise words at all for my current application, so have
> cleared out the noise.enu file so it is 0 bytes and re-built the catalog
> I am pretty sure it is working ok because I can search on 'the' and 'of'
etc
> which I couldn't do before. however, if I try to search on a single
letter,
> I still get the old 'only ignored words' error message.
> am I hitting on some different limitation here, or is there anything else
> that could cause this problem?
> thanks in advance
> Andy
>
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OQoGdaNaEHA.3016@.tk2msftngp13.phx.gbl...
> noise.enu is for us english. noise.eng is for the Queens English.
> Which word breaker are you using?
>
I wish I knew ;) - is there any way to find out?
when I select @.@.language it returns "us_english", but the full-text indexed
columns are actually SQL_Latin1_General_CP1253_CI_AI (greek). There is a
small story behind this which may be relevant:
Most of the columns in the database are SQL_Latin1_General_CP1_CI_AS and the
values contain some french acccented characters. When we first built the
text index we wanted it to be accent-insensitive but this is apparently only
possible in the sharepoint version of FTS not in the SQL server version. As
a workaround we created a separate copy of the data to be indexed which uses
the greek character set. Just before the index population, we copy the data
from the CP1 columns into the CP1253 columns which has the effect of
removing all the accents, so the only characters left in AFAIK are us
english ones.
do you think this could be related to the problem?

> Did you edit the noise word lists found in c:\Program Files\Microsoft SQL
> Server\MSSQL\FTData\SQLServer\config?
>
yes

> You have to stop MSSearch, make your edits and then restart MSSearch.
> Note, to find these noise words in your searches you will have to rebuild
> your catalog.
>
I did all this.
The thing is, before I did this change, a search for "the" would give the
same error but now it works correctly, so I'm pretty sure I edited the
correct noiselist and rebuilt the catalog correctly.
[vbcol=seagreen]
> A freetext search will not generate this error.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:TdQIc.2174$322.25169020@.news-text.cableinet.net...
> etc
> letter,
else
>
|||in your full text enabled database issue a
sp_MShelpcolumns 'FullTextIndexedTableName'
the last column will give you a number 1033 is for US_English
You can always issue a
exec master..xp_MSFullText
to get a list of the languages and the LocaleID's which are the numbers
returned in the last column of the sp_MShelpcolumns call.
From what you describe about the history of your database it might account
for this behavior. To verify this you will probably have to DTS the table to
another table in this database which uses the SQL_Latin1_General_CP1_CI_AS
collation. Then try to reproduce these results with this table when you FTI
it.
Purely for diagnostic reasons could you run this on your SQL Server:
Set objSearchAdmin=CreateObject("MSSearch.Admin")
Set infoNT=CreateObject("WinNTSystemInfo")
wscript.echo"Host Name: "& objSearchAdmin.HostName
objSearchAdmin.HostName =InfoNT.ComputerName
Set objApplications=objSearchAdmin.Applications
wscript.echo "Number of Search Applications serviced by this Build Server: "
& objApplications.Count
for each application in objApplications
wscript.echo "Application Name: " & application.name
next
Purely for diagnostic purposes could you run this script on your SQL Server
Set objSearchAdmin=CreateObject("MSSearch.Admin")
Set infoNT=CreateObject("WinNTSystemInfo")
wscript.echo"Host Name: "& objSearchAdmin.HostName
objSearchAdmin.HostName =InfoNT.ComputerName
Set objApplications=objSearchAdmin.Applications
wscript.echo "Number of Search Applications serviced by this Build Server: "
& objApplications.Count
for each application in objApplications
wscript.echo "Application Name: " & application.name
next
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:fjYIc.2573$SP6.30587016@.news-text.cableinet.net...
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OQoGdaNaEHA.3016@.tk2msftngp13.phx.gbl...
> I wish I knew ;) - is there any way to find out?
> when I select @.@.language it returns "us_english", but the full-text
indexed
> columns are actually SQL_Latin1_General_CP1253_CI_AI (greek). There is a
> small story behind this which may be relevant:
> Most of the columns in the database are SQL_Latin1_General_CP1_CI_AS and
the
> values contain some french acccented characters. When we first built the
> text index we wanted it to be accent-insensitive but this is apparently
only
> possible in the sharepoint version of FTS not in the SQL server version.
As
> a workaround we created a separate copy of the data to be indexed which
uses
> the greek character set. Just before the index population, we copy the
data[vbcol=seagreen]
> from the CP1 columns into the CP1253 columns which has the effect of
> removing all the accents, so the only characters left in AFAIK are us
> english ones.
> do you think this could be related to the problem?
SQL[vbcol=seagreen]
> yes
rebuild[vbcol=seagreen]
> I did all this.
> The thing is, before I did this change, a search for "the" would give the
> same error but now it works correctly, so I'm pretty sure I edited the
> correct noiselist and rebuilt the catalog correctly.
>
have[vbcol=seagreen]
catalog[vbcol=seagreen]
'of'
> else
>
|||Hilary,
sorry the turnaround on this is so slow but this isn't my day job so I have
to work odd hours at it.
I did the MShelpcolumns and it returned 1033 as expected. I also ran the VBS
script and it said there was just one search application, namely SQL Server
More interestingly, I created a couple of test tables, one with CP1253 and
one with the default CP1, in a brand new database and new full text catalog.
both tables exhibited the same symptoms as my original problem i.e. I can
search for 'the' but not 'a'
I then tried the same process on a different machine (again with an empty
noise.enu) and both searches worked i.e. the result I was looking for.
I noticed that the original machine was only on SQL 2000 SP2, so I upgraded
it to SP3. However, this doesn't seem to have fixed the problem.
so at the moment I have two machines, the non-working one is win2k server
sp4 running sql server 2000 standard sp3, and the working one is winxp sp1
running sql server 2000 developer sp3. both are using us english word
breaker and an empty noise.enu file.
do you have any idea what other difference might be causing this problem?
The non-working machine has quite a lot of other stuff installed on it but
nothing that I can think would interfere
Andy
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23%239dvOSaEHA.752@.TK2MSFTNGP09.phx.gbl...
> in your full text enabled database issue a
> sp_MShelpcolumns 'FullTextIndexedTableName'
> the last column will give you a number 1033 is for US_English
> You can always issue a
> exec master..xp_MSFullText
> to get a list of the languages and the LocaleID's which are the numbers
> returned in the last column of the sp_MShelpcolumns call.
> From what you describe about the history of your database it might account
> for this behavior. To verify this you will probably have to DTS the table
to
> another table in this database which uses the SQL_Latin1_General_CP1_CI_AS
> collation. Then try to reproduce these results with this table when you
FTI
> it.
> Purely for diagnostic reasons could you run this on your SQL Server:
> Set objSearchAdmin=CreateObject("MSSearch.Admin")
> Set infoNT=CreateObject("WinNTSystemInfo")
> wscript.echo"Host Name: "& objSearchAdmin.HostName
> objSearchAdmin.HostName =InfoNT.ComputerName
> Set objApplications=objSearchAdmin.Applications
> wscript.echo "Number of Search Applications serviced by this Build Server:
"
> & objApplications.Count
> for each application in objApplications
> wscript.echo "Application Name: " & application.name
> next
>
> Purely for diagnostic purposes could you run this script on your SQL
Server
> Set objSearchAdmin=CreateObject("MSSearch.Admin")
> Set infoNT=CreateObject("WinNTSystemInfo")
> wscript.echo"Host Name: "& objSearchAdmin.HostName
> objSearchAdmin.HostName =InfoNT.ComputerName
> Set objApplications=objSearchAdmin.Applications
> wscript.echo "Number of Search Applications serviced by this Build Server:
"[vbcol=seagreen]
> & objApplications.Count
> for each application in objApplications
> wscript.echo "Application Name: " & application.name
> next
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:fjYIc.2573$SP6.30587016@.news-text.cableinet.net...
> indexed
> the
> only
> As
> uses
> data
> SQL
> rebuild
the
> have
> catalog
> 'of'
>
|||Did you run a full population?
Just to make sure I have this correct, you can now search on 'a' and 'the'
without getting the 'a clause in the query only contained ignored words.',
but you can't get hits to rows that contain 'a' and 'the'?
Also you should not have a 0 byte noise word file. It should contain 1 byte
or a blank space.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:yphJc.3299$AU1.39183567@.news-text.cableinet.net...
> Hilary,
> sorry the turnaround on this is so slow but this isn't my day job so I
have
> to work odd hours at it.
> I did the MShelpcolumns and it returned 1033 as expected. I also ran the
VBS
> script and it said there was just one search application, namely SQL
Server
> More interestingly, I created a couple of test tables, one with CP1253 and
> one with the default CP1, in a brand new database and new full text
catalog.
> both tables exhibited the same symptoms as my original problem i.e. I can
> search for 'the' but not 'a'
> I then tried the same process on a different machine (again with an empty
> noise.enu) and both searches worked i.e. the result I was looking for.
> I noticed that the original machine was only on SQL 2000 SP2, so I
upgraded[vbcol=seagreen]
> it to SP3. However, this doesn't seem to have fixed the problem.
> so at the moment I have two machines, the non-working one is win2k server
> sp4 running sql server 2000 standard sp3, and the working one is winxp sp1
> running sql server 2000 developer sp3. both are using us english word
> breaker and an empty noise.enu file.
> do you have any idea what other difference might be causing this problem?
> The non-working machine has quite a lot of other stuff installed on it but
> nothing that I can think would interfere
> Andy
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23%239dvOSaEHA.752@.TK2MSFTNGP09.phx.gbl...
account[vbcol=seagreen]
table[vbcol=seagreen]
> to
SQL_Latin1_General_CP1_CI_AS[vbcol=seagreen]
> FTI
Server:[vbcol=seagreen]
> "
> Server
Server:[vbcol=seagreen]
> "
a[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
apparently[vbcol=seagreen]
version.[vbcol=seagreen]
which[vbcol=seagreen]
Files\Microsoft[vbcol=seagreen]
MSSearch.[vbcol=seagreen]
> the
and[vbcol=seagreen]
single[vbcol=seagreen]
anything
>
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eyOzj5gaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Did you run a full population?
> Just to make sure I have this correct, you can now search on 'a' and 'the'
> without getting the 'a clause in the query only contained ignored words.',
> but you can't get hits to rows that contain 'a' and 'the'?
> Also you should not have a 0 byte noise word file. It should contain 1
byte
> or a blank space.
YES!! YES!! YES!! YES!! YES!! YES!! YES!! YES!! YES!! YES!! YES!! YES!!
:-))))))))))))))))))))))))))))))))))))))))))))))))
sorry - excluse my julilation - that was the solution.
my noise file was 0 bytes long. putting a blank space in the noise file and
re-indexing causes it to work correctly.
Just to recap on the symptoms as the previous email maybe wasn't clear
enough. with a 0 byte noise file, it's possible to correctly search on 'the'
or any normal noise word, but searching for a single letter term gave the
error about 'a clause of the query contained only ignored words'. With a
noise file containing a blank like, a search for a single letter works
correctly. Everything about the character sets/word breakers and the service
pack was irrelevant in this case.
Thank you very much for your help in this case - I don't know how I would
have found it otherwise. Now I know what to search for, I have found a bit
of other discussion about this topic on the net.
Andy

> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>

A clause of the query contained only ignored words

Using FULL-Text search in SQL server and fornt end is VB6.0
My SQL statement is:
"Select English,Japanese from (select English,Japanese from S_R_J_E
where CONTAINS(English,'WWW')) as EJ where English='WWW' "
But it cause problem when exact match for "WWW" is not found in
database and error message is shown:
"A clause of the query contained only ignored words."
I already known that it caused for noise.* files.
But how I can solve it.
Pls help.
Molay Ash
Molay
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message336819.htmlI don't do a lot of fulltext, but try this and see if it works any better
Select English,Japanese from S_R_J_E
where CONTAINS(English,'WWW')) and English='WWW'
It seems kind of strange that you would need to search for the word WWW with
fulltext when you are requiring that the entire field be EXACTLY 'WWW'.
You might also try the containstable command instead of contains... (Docd
in books on line)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Molay" <Molay.10qdal@.mail.mcse.ms> wrote in message
news:Molay.10qdal@.mail.mcse.ms...
> Using FULL-Text search in SQL server and fornt end is VB6.0
> My SQL statement is:
> "Select English,Japanese from (select English,Japanese from S_R_J_E
> where CONTAINS(English,'WWW')) as EJ where English='WWW' "
> But it cause problem when exact match for "WWW" is not found in
> database and error message is shown:
> "A clause of the query contained only ignored words."
> I already known that it caused for noise.* files.
> But how I can solve it.
> Pls help.
> Molay Ash
>
> Molay
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message336819.html
>

A challenging query about grouping and order

Greetings !!

I am facing a challenge and hope some one can help me with the query.

I have a school. School have classrooms. Classrooms have students. Classrooms are divided into various sections (Section A, Section B and so on) .Every student is allocated a rollnumber in that section. When a student is transfered from one section to the other, there are gaps in the roll number of other students of the same section. For eg:

Section A

Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3

Student 4 Roll no 4 and so on.

Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A

So what should happen is that roll no need to be grouped together based on the section and the rest be readjusted accordingly. So when a student is removed, the roll nos of other students need to be adjusted accordingly. In our case, student 3 will get a roll no of 2, student 4 will have roll no of 3 and so on.

How do I write a query for this. I need to run this as a batch process every fortnight.

Thanks

Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.

Solution for SQL Server 2005,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

;WITH CTE

as

(

Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo

from #data

)

Update

CTE

Set

RollNo = NewRollNo

Where

RollNo <> NewRollNo

|||

For SQL Server 2000,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

Update #data

Set

RollNo = (Select Count(*) From #data Sub

Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)

|||

Thanks Manivannan for the solution. I will try the solution and revert back.

Regards,

Lalit

|||

Hi,

Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.

I hope you help me out. Thanks a ton Manivannan.

|||The above query is capable to handle multiple calssrooms also..

A challenging query about grouping and order

Greetings !!

I am facing a challenge and hope some one can help me with the query.

I have a school. School have classrooms. Classrooms have students. Classrooms are divided into various sections (Section A, Section B and so on) .Every student is allocated a rollnumber in that section. When a student is transfered from one section to the other, there are gaps in the roll number of other students of the same section. For eg:

Section A

Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3

Student 4 Roll no 4 and so on.

Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A

So what should happen is that roll no need to be grouped together based on the section and the rest be readjusted accordingly. So when a student is removed, the roll nos of other students need to be adjusted accordingly. In our case, student 3 will get a roll no of 2, student 4 will have roll no of 3 and so on.

How do I write a query for this. I need to run this as a batch process every fortnight.

Thanks

Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.

Solution for SQL Server 2005,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

;WITH CTE

as

(

Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo

from #data

)

Update

CTE

Set

RollNo = NewRollNo

Where

RollNo <> NewRollNo

|||

For SQL Server 2000,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

Update #data

Set

RollNo = (Select Count(*) From #data Sub

Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)

|||

Thanks Manivannan for the solution. I will try the solution and revert back.

Regards,

Lalit

|||

Hi,

Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.

I hope you help me out. Thanks a ton Manivannan.

|||The above query is capable to handle multiple calssrooms also..

A Challenging Query

Hi EveryBody,
Here is a very interesting SQL which I failed to solve.
I have two tables called Main and Notes.Table notes has id field which is
primary key of the Main table and foreign key in notes table. Notes table has
an identity column called NotesId.
What I have to do is that I have to show all data of main table but they
will be ordered depending upon a particular value of the note field in the
notes table. What I mean is : say there is ID 100,101,102,103 existing in the
main table. They may have several entries in the notes table and some of
those entries containing say "Desired match" in their note field.
What I want : in time of selection those entries who have "Desired Match" in
their notes field they will be coming first in their chronological order.
But their are some constraints : you cannot use any group by or distinct
clause in the query. and the resultant data cannot have any duplicate rows
Here is what I tried :
select top 100 percent main.*,notes.note,notes.date
from main left join notes on main.id=notes.id
order by convert(
numeric,
case
when notes.note like('Desired%') then '100000'
else
'500'
end
) desc,notes.date asc
But problem is that this result set contains duplicate data
Any new or better Idea.
Thanks
Kaushik
On Wed, 23 Mar 2005 22:23:01 -0800, Kaushik wrote:

>Hi EveryBody,
>Here is a very interesting SQL which I failed to solve.
(snip)
Hi Kaushik,
I love solving SQL challenges. But I'm not quite as good at trying to
understand verbose descriptions. I'll refer you to a website that
explains what information you should include in a post in order for us
to help you: www.aspfaq.com/5006. If you follow the guidelines given
there, I'll probably be able to help you.

>But their are some constraints : you cannot use any group by or distinct
>clause in the query. and the resultant data cannot have any duplicate rows
I understand the need to eliminate duplicate rows, but the constraints
to not use GROUP BY or DISTINCT makes no sense to me. Can you explain
the reason for this restriction? Because I really don't understand why
any SQL coder would voluntarily part with part of the tools he needs to
do his job.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

A Bug With Running Total Query?

Using SQL Server 2005
Query Result From 1 Table with 3 Cols:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
7/1/2005 415.0000 830.0000
8/1/2005 415.0000 1245.0000
9/1/2005 415.0000 1660.0000
10/1/2005 415.0000 2075.0000
11/1/2005 415.0000 2490.0000
12/1/2005 415.0000 2905.0000
1/1/2006 415.0000 3320.0000
2/1/2006 415.0000 3735.0000
3/1/2006 415.0000 4150.0000
4/1/2006 415.0000 4565.0000
5/1/2006 415.0000 4980.0000
Query:
SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
RunningTotal
FROM AmountTable AS Table1 INNER JOIN
AmountTable AS Table2 ON Table1.AmountDate >=
Table2.AmountDate
GROUP BY Table1.AmountDate, Table1.Amount
ORDER BY Table1.AmountDate
The problem is with the 7/1 date sum. It only added 100 and not 415
Does anybody see a problem I did or is this something I should post to MSDN
Product FeedBack?
The query was a bit more complex when I unioned 2 tables and the amount was
not coming out right and I broke it down very simply and found this to be a
n
issue.
On a side note relating to the union relation and doing a running total, I
only had 1 Amount in the other table for now and for some reason, that wasn'
t
summing with the rest of the set.
SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount
INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
CROSS JOIN
(SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
ORDER BY AmountTable1.AmountDate
Produces:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
6/3/2005 10.0000 20.0000*****From Table1
7/1/2005 415.0000 1245.0000
8/1/2005 415.0000 1660.0000
9/1/2005 415.0000 2075.0000
10/1/2005 415.0000 2490.0000
11/1/2005 415.0000 2905.0000
12/1/2005 415.0000 3320.0000
1/1/2006 415.0000 3735.0000
2/1/2006 415.0000 4150.0000
3/1/2006 415.0000 4565.0000
4/1/2006 415.0000 4980.0000
5/1/2006 415.0000 5395.0000
6/1/2006 415.0000 5810.0000
So that 6/3 Amount from Table1 is excluded from summing with the rest of the
set.
I thought it would have been included with the Union.
Any input is welcome,
NathanHi
This should be posted to the beta newsgroups along with version number, DDL
and sample data so that the problem can be recreated.
http://communities.microsoft.com/ne...onalserver.tsql
John
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:C771A09A-69DA-49D0-B71C-5C541E15EC51@.microsoft.com...
> Using SQL Server 2005
> Query Result From 1 Table with 3 Cols:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 7/1/2005 415.0000 830.0000
> 8/1/2005 415.0000 1245.0000
> 9/1/2005 415.0000 1660.0000
> 10/1/2005 415.0000 2075.0000
> 11/1/2005 415.0000 2490.0000
> 12/1/2005 415.0000 2905.0000
> 1/1/2006 415.0000 3320.0000
> 2/1/2006 415.0000 3735.0000
> 3/1/2006 415.0000 4150.0000
> 4/1/2006 415.0000 4565.0000
> 5/1/2006 415.0000 4980.0000
> Query:
> SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
> RunningTotal
> FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
> Table2.AmountDate
> GROUP BY Table1.AmountDate, Table1.Amount
> ORDER BY Table1.AmountDate
> The problem is with the 7/1 date sum. It only added 100 and not 415
> Does anybody see a problem I did or is this something I should post to
> MSDN
> Product FeedBack?
> The query was a bit more complex when I unioned 2 tables and the amount
> was
> not coming out right and I broke it down very simply and found this to be
> an
> issue.
> On a side note relating to the union relation and doing a running total, I
> only had 1 Amount in the other table for now and for some reason, that
> wasn't
> summing with the rest of the set.
> SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
> FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate,
> Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
> WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
> GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
> ORDER BY AmountTable1.AmountDate
> Produces:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 6/3/2005 10.0000 20.0000*****From Table1
> 7/1/2005 415.0000 1245.0000
> 8/1/2005 415.0000 1660.0000
> 9/1/2005 415.0000 2075.0000
> 10/1/2005 415.0000 2490.0000
> 11/1/2005 415.0000 2905.0000
> 12/1/2005 415.0000 3320.0000
> 1/1/2006 415.0000 3735.0000
> 2/1/2006 415.0000 4150.0000
> 3/1/2006 415.0000 4565.0000
> 4/1/2006 415.0000 4980.0000
> 5/1/2006 415.0000 5395.0000
> 6/1/2006 415.0000 5810.0000
> So that 6/3 Amount from Table1 is excluded from summing with the rest of
> the
> set.
> I thought it would have been included with the Union.
> Any input is welcome,
> Nathan|||
Nathan wrote:

>Using SQL Server 2005
>Query Result From 1 Table with 3 Cols:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>7/1/2005 415.0000 830.0000
>8/1/2005 415.0000 1245.0000
>9/1/2005 415.0000 1660.0000
>10/1/2005 415.0000 2075.0000
>11/1/2005 415.0000 2490.0000
>12/1/2005 415.0000 2905.0000
>1/1/2006 415.0000 3320.0000
>2/1/2006 415.0000 3735.0000
>3/1/2006 415.0000 4150.0000
>4/1/2006 415.0000 4565.0000
>5/1/2006 415.0000 4980.0000
>Query:
>SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
>RunningTotal
>FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
>Table2.AmountDate
>GROUP BY Table1.AmountDate, Table1.Amount
>ORDER BY Table1.AmountDate
>
Nathan,
The results are correct for this query. You are grouping by and summing
the same column: Table1.Amount. Table2 only determines how many times
each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
Table1.Amount:
Table1.Amount*(number of rows in the table with earlier or the same date).
Note that your "running sums" are not that at all. They are multiples:
730*1 = 730
415*2 = 830
415*3 = 1245
415*4 = 1660
...
415*12 = 4980
If you want running sums, you will need SUM(Table2.Amount).
Steve Kass
Drew University

>The problem is with the 7/1 date sum. It only added 100 and not 415
>Does anybody see a problem I did or is this something I should post to MSDN
>Product FeedBack?
>The query was a bit more complex when I unioned 2 tables and the amount was
>not coming out right and I broke it down very simply and found this to be
an
>issue.
>On a side note relating to the union relation and doing a running total, I
>only had 1 Amount in the other table for now and for some reason, that wasn
't
>summing with the rest of the set.
>SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
>FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmou
nt
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
>WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
>GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
>ORDER BY AmountTable1.AmountDate
>Produces:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>6/3/2005 10.0000 20.0000*****From Table1
>7/1/2005 415.0000 1245.0000
>8/1/2005 415.0000 1660.0000
>9/1/2005 415.0000 2075.0000
>10/1/2005 415.0000 2490.0000
>11/1/2005 415.0000 2905.0000
>12/1/2005 415.0000 3320.0000
>1/1/2006 415.0000 3735.0000
>2/1/2006 415.0000 4150.0000
>3/1/2006 415.0000 4565.0000
>4/1/2006 415.0000 4980.0000
>5/1/2006 415.0000 5395.0000
>6/1/2006 415.0000 5810.0000
>So that 6/3 Amount from Table1 is excluded from summing with the rest of th
e
>set.
>I thought it would have been included with the Union.
>Any input is welcome,
>Nathan
>|||AAAAHHHHHH!!!!
I can't believe I didn't catch that one. I was working on the original issue
for so long that ended up going blind.
Kudos for the good catch Steve
Nathan
"Steve Kass" wrote:

>
> Nathan wrote:
>
> Nathan,
> The results are correct for this query. You are grouping by and summing
> the same column: Table1.Amount. Table2 only determines how many times
> each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
> Table1.Amount:
> Table1.Amount*(number of rows in the table with earlier or the same date).
> Note that your "running sums" are not that at all. They are multiples:
> 730*1 = 730
> 415*2 = 830
> 415*3 = 1245
> 415*4 = 1660
> ...
> 415*12 = 4980
>
> If you want running sums, you will need SUM(Table2.Amount).
> Steve Kass
> Drew University
>
>

A binary field problem

why does mssql encrypt or convert the values (entered through sql query into a binary field) into hexadecimal values. When I query and print them from php, it displays some unicode characters. How do I solve this?Hi
shaktishakya
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.