MAX and DATEDIFF: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

MDF - Basic Question

Is it possible to 'query' an mdf file using MS Jet Provider or
somethnig like jet provider without having to 'attach' the mdf as a
database on SqlServer. Thanks.No. You must have a SQL Server engine to connect to it. You could install
MSDE (SQL Server 2000) or SQL Server Express (SQL Server 2005) and use it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1148033398.353253.150440@.i40g2000cwc.googlegroups.com...
Is it possible to 'query' an mdf file using MS Jet Provider or
somethnig like jet provider without having to 'attach' the mdf as a
database on SqlServer. Thanks.

mdb query to adp function conversion failed

Good evening everybody! This is my first post here, necessary because I have recently upgraded from Access *.MDB to Access *.ADP :)

I have a problem with a query that does not appear to have converted correctly. Below is the SQL of the *.mdb and the *.adp. I would be very grateful if somebody could indicate the correct format for the ADP code. I cant find an example in the help file :(

Mdb Query SQL ==========================
SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=[Forms]![frmMainQuote]![QuoteLoadSize]));

Adp Function SQL =========================
ALTER FUNCTION qryGetTonne1 (@.Forms___frmMainQuote___CboLorr varchar (255)
)
RETURNS TABLE
AS RETURN (SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=@.Forms___frmMainQuote_ __CboLorr)))

Any comment gratefully receivedUp-sizing problems

I still havent found the correct syntax for a query in an adp file. However I did find two more upsizing problems for which I have found a cure, these can be seen in the attached Access 2000 *.mdb file. Run it in mdb then run the upsize wizard and note the problems.

Change True to 1 and False to 0
The first upsizing problem I had was that in an *.mdb file you can use True or False in an SQL statement. However when you upsize you need to change the True to 1, and the False to 0. It might pay, when you are designing your database to think about using 1 and 0 to indicate true or false situations.

Using forms collection can cause a problem
The other problem related nicely to the query problem mentioned above. I found that some code in my form frmSeeTown did not work because the SQL contained a reference to a combo box via the forms collection in the form of:--

[Forms]![frmSeeTown]![cboSelectCounty]

This relates to my query problem because the query with the problem also contained a reference to a combo box through the forms collection. Therefore I am reasonably sure that you cannot reference a combo box this way in an SQL string in an Access Project file. However Im right at the bottom of the learning curve so please educate me.

Now down to the problem!
If you look at the attached *.mdb file you will see that there are three functions behind the form frmSeeTown they are called:-

fBuildStr1() Assigns data from the combo box with me.cboSelectCounty
fBuildStr2() Assigns data using the forms collection (Dont work in *.asp)
fBuildStr3() Assigns data from a variable.

To view the effect of the different methods change the called function name from fBuildStr3 to fBuildStr2

The following NOTES have been extracted from the form frmSeeTown module
'Both of these functions "fBuildStr1" and "fBuildStr2" work OK in an mdb, but "fBuildStr2" won't work in an adp.
'So there are 4 possible options ------

'1) It maybe I'm using the wrong syntax? -- can't find anything in the help? So I think it's something else.
'2) Reference the combo box control with the Me. function. -- Doing it this way could be a problem with sub-forms
'3) Assign the combo box value to variable and use that in the SQL -- this has got to be the way to do it :)
'4) Something else, embarrassingly simple that I don't know about yet :) Hell that's why I'm posting...

'Since writing the above, I have now added a further function fBuildStr3 which assigns the combo box value to a variable and then uses that variable in the SQL code. This appears to work OK.. :||||Found this just now!!!

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=AxxessXP&Number=137340&Forum=AxxessXP&Words=access%20project&Match=Entire%20Phrase&Searchpage=0&Limit=25&Old=allposts&Main=137340&Search=true#Post137340

So I'm outa the woods at last (Famous last words) :) :)

Whos a happy bunny then!!!|||After several days of head scratching, Ive discovered that my original suspicion that there was a problem with the syntax was correct. If you look at the two lines of code below taken from an SQL statement both line 1) and line 2) work in Access mdb format, but only line 2) works in Access adb format.

1) "WHERE (((tlkpUkTowns.TownOnlyCounty) = [Forms]![frmSeeTown]![cboSelectCounty] )) ORDER BY tlkpTowns.TownTown"

2) "WHERE (((tlkpUkTowns.TownOnlyCounty) = " & Forms![frmSeeTown].cboSelectCounty & ")) ORDER BY tlkpTowns.TownTown"

As you can see the SQL in line one is incorrectly formatted. It appears that the SQL engine for an mdb file is more forgiving, and corrects minor mistakes in the SQL string automatically. So I am inferring from this that an adp file uses a different engine to handle the SQL statements. This would make sense, as there are differences in the SQL between the two access formats. However I say again I am very low down on the learning curve and would welcome any input.sql

Friday, March 23, 2012

MCP Exam Study Query

Your question is not very clear.
You can join on any two columns with similar datatypes.
There does not have to be an explicit Parent-Child (PK-FK) relationship.
However, without a relationship, the question is: would the data provide any
useful information? And the answer is: it depends.
If that helps, then I was lucky. If not, please ask again, offering some
information about what you are seeking.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm currently studying for MCP exam 70-229. I'm on the database logical
> modeling section, and have come up with this question:
> In a non-identifying relationship, how would a join work, or is the
> term used to describe a situation where a relationship is at fault?
> Many Thanks,
> Simon.
>Hi Arnie,
Thanks for the post, I do understand that a relatioship can be either
explicit or implicit, but I'm not sure this is what the question refers to,
to bo honest, I'm not 100% sure what the question means myself!

Heres an excerpt from the book I'm working from:
"A relationship is said to be identifying if the primary key of a child
entity contains all the attributes of a foreign key. If the primary key of
the child entity does not contain all the attributes of a foreign key, the
the relationship is non-identifying"
The more I read that statement, the more confused I get! 8o)
Any ideas?
Simon.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uTMqldWlGHA.884@.TK2MSFTNGP05.phx.gbl...
> Your question is not very clear.
> You can join on any two columns with similar datatypes.
> There does not have to be an explicit Parent-Child (PK-FK) relationship.
> However, without a relationship, the question is: would the data provide
> any useful information? And the answer is: it depends.
> If that helps, then I was lucky. If not, please ask again, offering some
> information about what you are seeking.
> Regards,
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
>|||Hi All,
I'm currently studying for MCP exam 70-229. I'm on the database logical
modeling section, and have come up with this question:
In a non-identifying relationship, how would a join work, or is the term
used to describe a situation where a relationship is at fault?
Many Thanks,
Simon.|||Your question is not very clear.
You can join on any two columns with similar datatypes.
There does not have to be an explicit Parent-Child (PK-FK) relationship.
However, without a relationship, the question is: would the data provide any
useful information? And the answer is: it depends.
If that helps, then I was lucky. If not, please ask again, offering some
information about what you are seeking.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm currently studying for MCP exam 70-229. I'm on the database logical
> modeling section, and have come up with this question:
> In a non-identifying relationship, how would a join work, or is the
> term used to describe a situation where a relationship is at fault?
> Many Thanks,
> Simon.
>|||Hi Arnie,
Thanks for the post, I do understand that a relatioship can be either
explicit or implicit, but I'm not sure this is what the question refers to,
to bo honest, I'm not 100% sure what the question means myself!

MCP Exam Study Query

Hi All,
I'm currently studying for MCP exam 70-229. I'm on the database logical
modeling section, and have come up with this question:
In a non-identifying relationship, how would a join work, or is the term
used to describe a situation where a relationship is at fault?
Many Thanks,
Simon.Your question is not very clear.
You can join on any two columns with similar datatypes.
There does not have to be an explicit Parent-Child (PK-FK) relationship.
However, without a relationship, the question is: would the data provide any
useful information? And the answer is: it depends.
If that helps, then I was lucky. If not, please ask again, offering some
information about what you are seeking.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm currently studying for MCP exam 70-229. I'm on the database logical
> modeling section, and have come up with this question:
> In a non-identifying relationship, how would a join work, or is the
> term used to describe a situation where a relationship is at fault?
> Many Thanks,
> Simon.
>|||Hi Arnie,
Thanks for the post, I do understand that a relatioship can be either
explicit or implicit, but I'm not sure this is what the question refers to,
to bo honest, I'm not 100% sure what the question means myself! :)
Heres an excerpt from the book I'm working from:
"A relationship is said to be identifying if the primary key of a child
entity contains all the attributes of a foreign key. If the primary key of
the child entity does not contain all the attributes of a foreign key, the
the relationship is non-identifying"
The more I read that statement, the more confused I get! 8o)
Any ideas?
Simon.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uTMqldWlGHA.884@.TK2MSFTNGP05.phx.gbl...
> Your question is not very clear.
> You can join on any two columns with similar datatypes.
> There does not have to be an explicit Parent-Child (PK-FK) relationship.
> However, without a relationship, the question is: would the data provide
> any useful information? And the answer is: it depends.
> If that helps, then I was lucky. If not, please ask again, offering some
> information about what you are seeking.
> Regards,
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> I'm currently studying for MCP exam 70-229. I'm on the database logical
>> modeling section, and have come up with this question:
>> In a non-identifying relationship, how would a join work, or is the
>> term used to describe a situation where a relationship is at fault?
>> Many Thanks,
>> Simon.
>>
>sql

Wednesday, March 21, 2012

maybe is a stupid question.. about CASE

I have a little query in access ..now i must write it in T-Sql

the original...
(MSG.IDLINGUA = IIF( EXISTS
(SELECT * FROM ESMESSAGGI AS MSG WHERE MSG.IDLINGUA = [@.idLingua] AND MSG.IDMESSAGGIO = BITS.IDDESCR),
[@.idLingua], LDEF.IDLINGUA))

I can't be able to assign a select value at my field... IDLINGUA to perform another select..
Of course I have used the "select case when exists"

Anyone could be help me ?Post the code you tried that failed.|||By the reference on your where clause to a table or alias called "Bits", it seems this is part of a larger query.
Please, post your whole query.

Monday, March 19, 2012

Maximum value of mulitple columns

Hi all

Using sql server 2005, Im trying in a query to get the maximum value of multiple columns of a table for each of its records.

What im trying to get is the last date an index was used using the table sys.dm_db_usage_stats using the date fields (last_user_seek, last_user_update...).

I looked around the forum for a solution but those i found dont seem to apply really easily to a query.

Anyone got a suggestion?

Dale:

There have been a number of discussions about a similar issue; give a look to this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=733186&SiteID=1

|||

Dale L.,

Try using new operators UNPIVOT and "CROSS APPLY".

Code Snippet

create table dbo.t1 (

pk int not null identity unique,

c1 int,

c2 int,

c3 int

)

insert into dbo.t1(c1, c2, c3) values(1, 2, 3)

insert into dbo.t1(c1, c2, c3) values(4, 6, 5)

insert into dbo.t1(c1, c2, c3) values(9, 7, 8)

select

a.pk,

b.max_value

from

dbo.t1 as a

cross apply

(

select

max(unpvt.[value]) as max_value

from

(

select

t.pk,

t.c1 as [1],

t.c2 as [2],

t.c3 as [3]

from

dbo.t1 as t

where

t.pk = a.pk

) as pvt

unpivot

([value] for [col] in ([1], [2], [3])) as unpvt

group by

unpvt.pk

) as b

drop table dbo.t1

AMB

Maximum Value

Hey guys, I have this query which tries to get the maximum values from a table.

Code Snippet

SELECT scbcrse_subj_code,

MAX(scbcrse_eff_term), scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code

FROM Courses

WHERE scbcrse_csta_code = ''A''

GROUP BY scbcrse_subj_code, SCBCRSE_CRSE_NUMB, scbcrse_coll_code, scbcrse_csta_code, scbcrse_title

ORDER BY scbcrse_subj_code

Sample Table

scbcrse_subj_code scbcrse_eff_term scbcrse_crse_numb scbcrse_coll_code scbcrse_title ACCT 200620 4010 SB Advanced Accounting ACCT 200530 4010 SB Financial Accounting IV

Now, there is a column which is called scbcrse_title which shows the title of the course, which in this case on the table the titles are different. One is called Advanced Accounting and the other is called Financial Accounting IV. My question is, how can I only show the highest number on the scbcrse_eff_term? The problem occurs when there are different titles.

Does scbcrrse_title need to be part of the group by? Can you just group on crse_numb, coll_code, and subj_code only ? Then subselect the title using the three group by values where eff_term is max? Something like the below (i did not check the code for errors sorry)

Code Snippet

SELECT C.scbcrse_subj_code,

C.scbcrse_crse_numb,

C.scbcrse_coll_code,

C.scbcrse_csta_code,

MAX(C.scbcrse_eff_term),

(SELECT scbcrse_title

FROM Courses

WHERE scbcrse_subj_code = C.scbcrse_subj_code

AND scbcrse_crse_numb = C.scbcrse_crse_numb

AND scbcrse_coll_code = C.scbcrse_coll_code

AND scbcrse_csta_code = C.scbcrse_csta_code

AND scbcrse_eff_term) = (SELECT MAX(C.scbcrse_eff_term) FROM Courses WHERE C.Cscbcrse_csta_code = 'A' GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code ) AS [Title]

FROM Courses C

WHERE C.Cscbcrse_csta_code = 'A'

GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code

ORDER BY scbcrse_subj_code

|||Thanks Dave, I'll check the code to see if it works. And yes, the GROUP BY command does force me to include scbcrse_title.

|||

Maybe this:

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Diango,

You can do this without a GROUP BY as follows:

SQL Server 2000

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code = ''A''
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

In other words, choose all rows for Courses where the table does not contain a later (measured by eff_term) row with the same (subj_code, crse_numb, coll_code) combination. This assumes you want one result row for each (subj_code, crse_numb, coll_code) combination, and you can adjust the inner WHERE clause if this is not the set of columns that you want one row for each combination of.

In SQL Server 2005, you have another option:

SQL Server 2005

WITH Courses_ranked AS (
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code,
RANK() OVER (
PARTITION BY scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code
ORDER BY scbcrse_eff_term DESC
) AS rk
)
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses_ranked
WHERE rk = 1

Both solutions will return the "latest" row (or rows in the case of ties for latest) for each combination (scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code) in the table.

Steve Kass
Drew University
http://www.stevekass.com
|||The first solution almost worked but it had the problem that when there was a code that only had one value, it got omitted. The second solution I have never done before so I'm having some problems implementing it.|||I notice I left the = 'A' out of the inner query in the first solution. That could be it, but it could also be something about your data that you didn't mention. If you post some sample data and your adapted query that fails, I can take a look.

SK
|||That worked, you rock! As soon as I added the ''A'' in the inner join, it came back with the correct result.|||Steve, I don't mean to be a pain in the ass, but I have a new issue. I'm still a bit of a noob so bare with me. Your code worked great by the way, it did the job as intended. New update is required which I wasn't aware. I want to take into account those courses that do have '' I '' as well as the maximum value on the scbcrse_eff_term. I can do this easily by simply removing the WHERE clause where scbcrse_csta_code = ''A'' on both occasions. The thing is, for those courses that have the maximum value which have an '' I '' I need those courses removed completly. ( I don't mean delete those records that '' I '' , I just mean Omitting them some how.|||I hope I'm understanding, because you didn't give any specific example.

You want to see the latest row for each (subject code, course number, college code) from among those rows with csta code either 'A' or 'I', only if that latest row happens to be one of the 'A' rows. Note that if 'A' and 'I' are the only possible values of csta_code, you don't have to say WHERE scbcrse_csta_code IN ('A','I'). I think this will do it.

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code IN ('A','I')
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
AND C.scbcrse_csta_code IN ('A','I')
)
AND Courses.scbcrse_csta_code = 'A'
ORDER BY scbcrse_subj_code
|||

Sorry, I should have explained myself a little better. Ok, this is your code:

AND C.scbcrse_csta_code = ''A''
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

From the original code, we had a clause WHERE = ''A''. By having those two clauses, it returned the top value from scbcrse_eff_term Where scbcrse_csta_code = A, agreed? I found out later that the original requirement was wrong. The correct requirement was, get the highest scbcrse_eff_term as long as the class is active, or otherwise known as having a csta_code of ''A''. I know it sounds the same but let me explain further.

For example:

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

From the original code, it should return the second row with a value of 200023 because it's the highest row that has a csta_code of ''A''. Here is where it changes, on the example, since the highest value for the class is 221457 and has a csta_code of '' I '' the class has become inactive and therefore ACCT 4041 must no longer show up in our query. So in essence, every class that has the highest scbcrse_eff_term with a value of csta_code '' I '' should not show up at all in our query return. So ACCT 4041 would not show up on our list.

|||

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Dale, that's not working for me. I'm getting some errors when I try to implement it. I get the error of it's not a group by function. I also think that the code would return the maximum value of the courses that have a csta_code value of ''A'', not taking into account '' I ''. Which I do want to take into account '' I '' , but if the maximum scbcrse_eff_term of a course has a csta_code of '' I '' the class should be omitted completely from the result. It's not just eliminating all the '' I '', but it's eliminating all the courses from the list that has a maximum scbcrse_eff_term with scbcrse_csta_code of '' I ''. I don't know if that makes sense to you.|||

See if this is better.

I forgot the group by in the subquery.

This will gather up all the courses with the most current date, then only keep the ones that are A.

My understanding from what you've been saying is that you want to disregard all entries for the class if its current row is an I.

This should do just that.

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

GROUP BY scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||

It doesn't bring back the desired result. Let me show you what I mean.

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

This is an example of a class that has gone inactive. This table has been poorly designed, which is why it's so challenging to create the proper effect. As you can see from this class, it has become inactive. When we run your query, it returns for example ACCT 4041 200023 and a code of A. The desired result is that this course doesn't come back at all because the maximum value for it is 221457 and because it has a scbcrse_csta_code of '' I ''.

Steve's code is great because it really can eliminate duplicates and it also brings the highest value of a course no matter what csta_code it has, because I removed the two WHERE clauses. Now, from the result set, I need to remove the courses that have a maximum value with a csta_code of '' I '' combination.

Maximum SQL Query lenght

Hi all!

Does anybody know what is subject?

Thanks!

This is batch size from ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-d1a74dd824d7.htm

Monday, March 12, 2012

Maximum Numbers of Tables and colums to access in select statement

I want to get the
Maximum Numbers of Tables and colums to access in select query statement

Regards
AruneshCitations from SQL Books Online:columns: The maximum number of expressions that can be specified in the select list is 4096

maximum number of tables in a query (260) was exceeded

I am getting the following error message from a view I have created. I
would think it was a design issue but it appeared to be working at one time.
When it was reopened I received the following error:
--Could not allocate ancillary table for view or function resolution. The
maximum number of tables in a query (260) was exceeded.
Any ideas on how to correct this? Thanks.
DonHi
If there are 260 tables in the query then you will need to think about the
design. You may have subsequently changed a view to include more tables it
was working previously.
John
<dbj> wrote in message news:uj39oq$UFHA.3188@.TK2MSFTNGP09.phx.gbl...
>I am getting the following error message from a view I have created. I
>would think it was a design issue but it appeared to be working at one
>time. When it was reopened I received the following error:
> --Could not allocate ancillary table for view or function resolution.
> The maximum number of tables in a query (260) was exceeded.
> Any ideas on how to correct this? Thanks.
> Don
>

Maximum number of databases used for each query in 2005?

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
MikeHoly Moly, what kind of queries are you writing!?|||Hi, I have the same problem with 2000. I am about to upgrade to 2005 and I am very concerned about this because our database requirements are rapidly growing.

Thanks,
benn

Quote:

Originally Posted by Michael.Suarez@.gmail.com

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
Mike

Friday, March 9, 2012

Maximum length of return in Query Analyzer

I am executing a SELECT statement that has about 500 characters of literal characters concatenated with the contents of a field from a table. I am then storing the result to be run as dynamic SQL. I am finding that when run this as select statement in query analyzer, the last part of the literal gets truncated. When I run it as a cursor and store it in a varchar(1000) variable and print the variable everything works fine. In addition when I put the select statement in a stored procedure and return this to a ADO recordset, the resultset is fine as well. But running the stored procedure in query analyzer truncates the results as well. The issue seems to be getting the results of the SELECT in query analyzer. Even running the stored procedure in the SQL area of Enterprise Manager returns a proper result. Has anyone heard of a maximum return from a select in query analyzer?Yes, check out: Tools, Options, the Results-tab. "Maximum characters per column".|||Yes, and I should probably post a FAQ entry for it.

1. Shift-Ctrl-o for options
2. Click the Results tab
3. Near the middle, Maximum characters per column..

-PatP|||Manj Tak
Danke Sha
merci beaucoup
mucho gracias|||Yes, and I should probably post a FAQ entry for it.

-PatP

Can you put one on there for making DBA coffee?

1. 4 heaping tbsp coffee grounds/8oz water
2. Pinch of salt
3. Add water tapped from boiler/radiator
4. Brew in pot that's never been washed using yesterday's filter
5. Serve in cup that's never been washed

Thanks,

hmscott

Maximum Length Issue

Hi ,
I perform the query show below to find the duplicate records within the
table itself
"SELECT f_table.flt_Id, f_table.Psg_Id, f_table.Flt_dt_Id
FROM
[SELECT
Count(F_Sgmt_History.flt_Id),
Count(F_Sgmt_History.Psg_Id),
Count(F_Sgmt_History.Flt_dt_Id),
F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id, F_Sgmt_History.Flt_dt_Id
FROM F_Sgmt_History
GROUP BY F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id,
F_Sgmt_History.Flt_dt_Id
HAVING (((Count(F_Sgmt_History.flt_Id))>1) AND
((Count(F_Sgmt_History.Psg_Id))>1) AND
((Count(F_Sgmt_History.Flt_dt_Id))>1))]. AS f_table INNER JOIN F_Sgmt_History
ON (f_table.Flt_dt_Id = F_Sgmt_History.Flt_dt_Id) AND (f_table.Psg_Id =
F_Sgmt_History.Psg_Id) AND (f_table.flt_Id = F_Sgmt_History.flt_Id)"
But I get the following error
"Server: Msg 103, Level 15, State 7, Line 3
The identifier that starts with 'SELECT
Count(F_Sgmt_History.flt_Id),
Count(F_Sgmt_History.Psg_Id),
Count(F_Sgmt_History.Flt_dt_Id),
F_Sgmt_History.flt_Id, ' is too long. Maximum length is 128.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'. "
I am not sure wat's wrong with the statment , please help
Travis Tan
instead of '[' ,']' (square baracket ) use '(' ,')' and do not use '.'
before 'AS'.
your problem will be solved
"Travis" ?? ????:

> Hi ,
> I perform the query show below to find the duplicate records within the
> table itself
> "SELECT f_table.flt_Id, f_table.Psg_Id, f_table.Flt_dt_Id
> FROM
> [SELECT
> Count(F_Sgmt_History.flt_Id),
> Count(F_Sgmt_History.Psg_Id),
> Count(F_Sgmt_History.Flt_dt_Id),
> F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id, F_Sgmt_History.Flt_dt_Id
> FROM F_Sgmt_History
> GROUP BY F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id,
> F_Sgmt_History.Flt_dt_Id
> HAVING (((Count(F_Sgmt_History.flt_Id))>1) AND
> ((Count(F_Sgmt_History.Psg_Id))>1) AND
> ((Count(F_Sgmt_History.Flt_dt_Id))>1))]. AS f_table INNER JOIN F_Sgmt_History
> ON (f_table.Flt_dt_Id = F_Sgmt_History.Flt_dt_Id) AND (f_table.Psg_Id =
> F_Sgmt_History.Psg_Id) AND (f_table.flt_Id = F_Sgmt_History.flt_Id)"
> But I get the following error
> "Server: Msg 103, Level 15, State 7, Line 3
> The identifier that starts with 'SELECT
> Count(F_Sgmt_History.flt_Id),
> Count(F_Sgmt_History.Psg_Id),
> Count(F_Sgmt_History.Flt_dt_Id),
> F_Sgmt_History.flt_Id, ' is too long. Maximum length is 128.
> Server: Msg 156, Level 15, State 1, Line 3
> Incorrect syntax near the keyword 'AS'. "
> I am not sure wat's wrong with the statment , please help
> --
> Travis Tan

maximum joins

hello,
is there a restriction how many joins a query can have?
I am asking, because we have a query generator and probably we
will reach such a limit.
we have a star design.
one table in the center and probably thousand around this center table.
now we need to join theses tables generically.
thank you.
michael
256
http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:D3F4C674-D21D-4CE2-B183-8A48B3D5D4F4@.microsoft.com...
> hello,
> is there a restriction how many joins a query can have?
> I am asking, because we have a query generator and probably we
> will reach such a limit.
> we have a star design.
> one table in the center and probably thousand around this center table.
> now we need to join theses tables generically.
> thank you.
> michael
>
|||According to Books Online the limit is 256 tables per query (can't say I've
ever put this to the test).
By "star design" do you mean a dimensional star schema? If so, you may want
to rethink what you are trying to do. There doesn't seem much point creating
a star schema if you want to do relational queries which then become so
complex. 1000 dimension tables seems pretty exceptional too!
David Portas
SQL Server MVP

maximum joins

hello,
is there a restriction how many joins a query can have?
I am asking, because we have a query generator and probably we
will reach such a limit.
we have a star design.
one table in the center and probably thousand around this center table.
now we need to join theses tables generically.
thank you.
michael256
http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:D3F4C674-D21D-4CE2-B183-8A48B3D5D4F4@.microsoft.com...
> hello,
> is there a restriction how many joins a query can have?
> I am asking, because we have a query generator and probably we
> will reach such a limit.
> we have a star design.
> one table in the center and probably thousand around this center table.
> now we need to join theses tables generically.
> thank you.
> michael
>|||According to Books Online the limit is 256 tables per query (can't say I've
ever put this to the test).
By "star design" do you mean a dimensional star schema? If so, you may want
to rethink what you are trying to do. There doesn't seem much point creating
a star schema if you want to do relational queries which then become so
complex. 1000 dimension tables seems pretty exceptional too!
David Portas
SQL Server MVP
--

maximum joins

hello,
is there a restriction how many joins a query can have?
I am asking, because we have a query generator and probably we
will reach such a limit.
we have a star design.
one table in the center and probably thousand around this center table.
now we need to join theses tables generically.
thank you.
michael256
http://www.aspfaq.com/2345
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:D3F4C674-D21D-4CE2-B183-8A48B3D5D4F4@.microsoft.com...
> hello,
> is there a restriction how many joins a query can have?
> I am asking, because we have a query generator and probably we
> will reach such a limit.
> we have a star design.
> one table in the center and probably thousand around this center table.
> now we need to join theses tables generically.
> thank you.
> michael
>|||According to Books Online the limit is 256 tables per query (can't say I've
ever put this to the test).
By "star design" do you mean a dimensional star schema? If so, you may want
to rethink what you are trying to do. There doesn't seem much point creating
a star schema if you want to do relational queries which then become so
complex. 1000 dimension tables seems pretty exceptional too!
--
David Portas
SQL Server MVP
--

Wednesday, March 7, 2012

Maximum date with a twist

Please help.
I have this query which works fine to get the maximum create date between 6
tables:
create table #temp1 (create_date datetime,call_stat char(30))
insert into #temp1 (create_date,call_stat)
(SELECT create_dt,'Disposition' as call_stat FROM tblotherdisposition where
scp_seqno = @.scpseqno
union all
SELECT create_dt,'Pending' as call_stat FROM Tblpendings where scp_seqno =
@.scpseqno
union all
SELECT create_dt,'Referred to CompanyA' as call_stat FROM
tblreferredtoCompanyA where scp_seqno = @.scpseqno
union all
SELECT create_dt,'Referred to CompanyB' as call_stat FROM
TblReferredToCompanyB where scp_seqno = @.scpseqno
union all
SELECT create_dt,'Appointment' as call_stat FROM t_appointment where
scp_seqno = @.scpseqno
union all
SELECT create_dt,'Unknown' as call_stat FROM tblsurvey where scp_seqno =
@.scpseqno)
select call_stat as status from #temp1
where create_date = (select max(create_date) from #temp1)
**Recent testing shows that the Appointment create date is always less than
the
create date of tblsurvey by a few minutes. When this happens, I still want
call status = Appointment rather than "Unknown". How can I change my query
to reflect this discovery.
TIA,
--
LynPlease post table structures with sample data along with expected results
for others to better understand you requirements. For details refer to:
www.aspfaq.com/5006
Do you just want to display call_stat as 'Appointment'? Then you can use a
CASE directly in your SELECT list. If you are concerned about the create_dt
value, then you can write something along the lines of:
SELECT TOP 1 call_stat
FROM ( < your union-ed query > ) D
ORDER BY create_date DESC
If you are using the #temp table, use your subquery like:
WHERE create_date = ( SELECT MAX( create_date )
FROM #temp1 WHERE call_stat <> 'Unknown' ) ;
If this has to be done in the inner query, use a CASE expression like:
MAX( CASE WHEN call_stat <> 'unknown' THEN create_date )
Anith|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
There is a common newbiew design flaw called attribute splitting. You
take an attribute and make each value into a separate table. It is
like using a card sorter back when we had punch card (aka "unit
record") data systems.
CREATE TABLE Survey
(create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
SOX audit rule!
call_status CHAR(30) NOT NULL -- code is too long!
CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
to Company B', 'Appointment', 'Unknown')),
..);
Now the query is simple:
SELECT create_dt, call_stat
FROM Survey
WHERE scp_seq = @.my_scpseq;
But the code implies that this is really screwed up. What you probably
want is a history under the contorl a transition table. Go to
DBAzine.com to learn what a transition constraint table is; I have a
short article there. Google aroudn here for history table.
You are at the point in your education where you still put those
stupid, redudant "tbl-" prefixes on table names. Do not expect
learning how to do it right is going to be quick or easy. And you all
you will get on Newsgroup is stinking dirty kludges.|||I am sorry, I forget to mention that yoru call_status needs to be put
into a well-designed encoding scheme. Get a copy of SQL PROGRAMMING
STYLE for details.|||Please share some details. Heck, if you share some of the details here it
might make a few more people buy your book. Shoot, I might even buy a copy
or two. :) As it stands now, with the title in all caps I halfway expect
that the book will yell at me for 272 pages. If I want yelled at for a few
hours, I will call my wife stupid and it won't cost 30 buck:)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139275655.490414.101310@.f14g2000cwb.googlegroups.com...
>I am sorry, I forget to mention that yoru call_status needs to be put
> into a well-designed encoding scheme. Get a copy of SQL PROGRAMMING
> STYLE for details.
>|||> CREATE TABLE Survey
> (create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
> SOX audit rule!
> call_status CHAR(30) NOT NULL -- code is too long!
> CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
> to Company B', 'Appointment', 'Unknown')),
> ..);
Where is the PRIMARY KEY?
Again, another fundemental mistake.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139275517.755904.323520@.o13g2000cwo.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> There is a common newbiew design flaw called attribute splitting. You
> take an attribute and make each value into a separate table. It is
> like using a card sorter back when we had punch card (aka "unit
> record") data systems.
> CREATE TABLE Survey
> (create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
> SOX audit rule!
> call_status CHAR(30) NOT NULL -- code is too long!
> CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
> to Company B', 'Appointment', 'Unknown')),
> ..);
> Now the query is simple:
> SELECT create_dt, call_stat
> FROM Survey
> WHERE scp_seq = @.my_scpseq;
> But the code implies that this is really screwed up. What you probably
> want is a history under the contorl a transition table. Go to
> DBAzine.com to learn what a transition constraint table is; I have a
> short article there. Google aroudn here for history table.
> You are at the point in your education where you still put those
> stupid, redudant "tbl-" prefixes on table names. Do not expect
> learning how to do it right is going to be quick or easy. And you all
> you will get on Newsgroup is stinking dirty kludges.
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uOb4zp5KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> If I want yelled at for a few hours, I will call my wife stupid and it
> won't cost 30 buck:)
How much does a dozen roses go for these days...|||Thank you for the input. I would have appreciated your help without
sabotaging
my coding and throwing insults at me. I posted here to understand where I
am doing wrong. You might be an excellent SQL programmer but your character
needs a lot of fine tuning.
--
lyn
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> There is a common newbiew design flaw called attribute splitting. You
> take an attribute and make each value into a separate table. It is
> like using a card sorter back when we had punch card (aka "unit
> record") data systems.
> CREATE TABLE Survey
> (create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
> SOX audit rule!
> call_status CHAR(30) NOT NULL -- code is too long!
> CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
> to Company B', 'Appointment', 'Unknown')),
> ...);
> Now the query is simple:
> SELECT create_dt, call_stat
> FROM Survey
> WHERE scp_seq = @.my_scpseq;
> But the code implies that this is really screwed up. What you probably
> want is a history under the contorl a transition table. Go to
> DBAzine.com to learn what a transition constraint table is; I have a
> short article there. Google aroudn here for history table.
> You are at the point in your education where you still put those
> stupid, redudant "tbl-" prefixes on table names. Do not expect
> learning how to do it right is going to be quick or easy. And you all
> you will get on Newsgroup is stinking dirty kludges.
>|||Lyn,
Don't take it personally, Celko attacks everyone like that. If you can wade
through the insults you can often find some useful suggestions. Truth is,
this post was actually quite civil, I truely think he was trying to be nice.
The "newbie design flaw" and "stupid, redudant "tbl-" prefixes" comments are
just part of how he talks here.
"stinking dirty kludges" refers to the solutions that you will get from
others on the newsgroup. I may be able to post one myself, if I can quite
figure out what you need, although I suspect it will involve a couple
subqueries with exists or not exists.
One simple solution is to select each date as a seperate column (outer joins
on scp_seqno), then you will be able to compare all the columns at once to
determine which is the maximum based on whether one column or another is
populated.
He is correct that the database design could probably use some changes.
Whatever help you get here will solve the problem for now, but at the risk
of leaving more complicated code to work with later.
"Lyn" <pablomb@.optonline.net> wrote in message
news:B7C59EFA-7B3B-43BB-BEB6-207A7966F326@.microsoft.com...
> Thank you for the input. I would have appreciated your help without
> sabotaging
> my coding and throwing insults at me. I posted here to understand where I
> am doing wrong. You might be an excellent SQL programmer but your
character
> needs a lot of fine tuning.
> --
> lyn
>
> "--CELKO--" wrote:
>|||Thanks Jim but if someone wanted to teach instead of insult -they
are more likely to learn and be inspired to change rather than kick them
when they are already down.
Anyway, what I'm trying to do is here:
I wanted to get the call status from the 6 tables where create date is the
latest.
Parent/Main table is - tblSurvey
The rest of the tables are just child/related tables.
In Access, I have the main form bound to tblSurvey
and the rest of the subforms are either - Appointment,Pending,
Other Disposition, Referred to CompanyA or Referred to CompanyB.
When a dept. gets a call they either select from one of the subforms
and then I write the call status in tblSurvey.
If the subforms are not filled in - it will be "Unknown".
In the case of Appointments, tblAppointments gets committed first before
tblsurvey by a few minutes or even seconds. We want the status to be as
"Appointment" instead of "Unknown".
tblSurvey:
PK - scp_seqno
create_dt
call_status
other fields
tblAppointments
Pk-Appt_seqno
FK-scp_seqno
create_dt
other fields
tlbPendings
pk-Pending_seqno
fK - scp_seqno
create_dt
other fields
tblReferredtoCompanyA
Pk-companyA_seqno
fk- scp_seqno
create_dt
other fields
and the same goes for the rest of the child tables.
Hopefully this is clear. Thanks for any ideas.
"Jim Underwood" wrote:

> Lyn,
> Don't take it personally, Celko attacks everyone like that. If you can wa
de
> through the insults you can often find some useful suggestions. Truth is,
> this post was actually quite civil, I truely think he was trying to be nic
e.
> The "newbie design flaw" and "stupid, redudant "tbl-" prefixes" comments a
re
> just part of how he talks here.
> "stinking dirty kludges" refers to the solutions that you will get from
> others on the newsgroup. I may be able to post one myself, if I can quite
> figure out what you need, although I suspect it will involve a couple
> subqueries with exists or not exists.
> One simple solution is to select each date as a seperate column (outer joi
ns
> on scp_seqno), then you will be able to compare all the columns at once to
> determine which is the maximum based on whether one column or another is
> populated.
> He is correct that the database design could probably use some changes.
> Whatever help you get here will solve the problem for now, but at the risk
> of leaving more complicated code to work with later.
> "Lyn" <pablomb@.optonline.net> wrote in message
> news:B7C59EFA-7B3B-43BB-BEB6-207A7966F326@.microsoft.com...
> character
>
>

maximum age

hi,
was wondering any knows the synatax for a query to find the maximum age in a table. in SSH secure Shell
cheersWell, SQL would do that as

SELECT MAX(age) FROM your_table

but I have no idea how to incorporate it with the SSH secure Shell.

Maximum # of tables in a T-SQL query

KB article 828269, and its companion 818406, say that that they increase
the allowable number of tables and views references in a T-SQL query
from 256 to 260.
Is this for real, or am I missing something here? This hotfix touches a
whole slew of DDLs and other parts of SQL, and only gives you the
ability to use 4 additional tables in a query?
The article says this:
"The maximum number of tables that can be referred in a SELECT Transact-
SQL statement is 256. This limit is documented under the "Maximum
Capacity Specifications" section of the SQL Server Books Online. This
limit includes views and the tables that are referenced in the SELECT
statement. Also, the limit includes the tables and the views that are
referenced by a view that is included in the query. Therefore, if a
table or a view is referenced more than one time in the query, each
reference to the table or the view (or the tables and views that are
referenced in the view) counts against this limit.
With this fix, the maximum limit of the number of tables or views that
are referenced in the query is increased to 260. Therefore, you may
still receive the error message that is mentioned in the "Symptoms"
section of this article."
It seems like I am missing something important; it's a little odd to
make a big fix for a really small improvement.
Thanks.
David WalkerHi David,
You are really a good observer to the KB articles and you are right that
the improvement of the limitation of the numbers of referenced tables and
views from 256 to 260 is not the goal for this hotfix.
In the KB 818406, it is said 'This bug occurs in Microsoft SQL Server 2000
build 613. If you have installed a fix after you installed SQL Server 2000
Service Pack 2 (SP2), you may see this bug. ' However, before install the
fix, you will not encounter the error message described in KB 818406, which
is:
Server: Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The
maximum number of tables in a query (256) was exceeded."
When you have too many views and even when not exceeding the 256 limit.
So, this fix is to resolve this problem. When you installed the fix and you
views and tables are not beyond the 256( this time 260, not to this problem
but it maybe an unintened improvement :-) ), you will not met this problem
any more.
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi David,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if you still have some question about it besides
my answer. For any more question, please post your message here
and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

Friday, March 30, 2012

MDF - Basic Question

mdb query to adp function conversion failed

Friday, March 23, 2012

MCP Exam Study Query

MCP Exam Study Query

Wednesday, March 21, 2012

maybe is a stupid question.. about CASE

Monday, March 19, 2012

Maximum value of mulitple columns

Maximum Value

Maximum SQL Query lenght

Monday, March 12, 2012

Maximum Numbers of Tables and colums to access in select statement

maximum number of tables in a query (260) was exceeded

Maximum number of databases used for each query in 2005?

Friday, March 9, 2012

Maximum length of return in Query Analyzer

Maximum Length Issue

maximum joins

maximum joins

maximum joins

Wednesday, March 7, 2012

Maximum date with a twist

maximum age

Maximum # of tables in a T-SQL query

Maximum # of columns for FullText engine ?

MAX and DATEDIFF

Blog Archive

About Me