Query longer in SP: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

Query Processor Error

I get the following message when I'm inserting a row into a table. The
statement is very simple, but there is a large amount of data going into one
column.
Internal Query Processor Error: The query processor ran out of stack space
during query optimization
All the KB articles I've found so far refer to queries that have a large
number of elements in an IN clause, or a CASE statement with a large number
of WHEN clauses - niether of which fit this scenario.
Has anyone else had this problem and found a way around it. I can't
replicate the problem on any of our development or test servers - it only
happens on the production server. All servers are the same spec - W2K
Server and SQL Server 2000 - all service packed up.
Any help appreciated.
Cheers,
CameronCameron,
Can you provide more information? What is the structure of the table,
and what is the insert statement, or at the least, what form does it have -
insert .. values, insert into .. select? Are there any triggers on the
table?
What indexes are on the table? Are any of the tables involved actually
views?
It's hard to suggest a way around a problem with this little information
about what you are trying to do.
-- Steve Kass
-- Drew University
-- Ref: 20CAE9D5-49D4-43CF-A330-EE3EB766EF1A
cj wrote:
>I get the following message when I'm inserting a row into a table. The
>statement is very simple, but there is a large amount of data going into one
>column.
>Internal Query Processor Error: The query processor ran out of stack space
>during query optimization
>All the KB articles I've found so far refer to queries that have a large
>number of elements in an IN clause, or a CASE statement with a large number
>of WHEN clauses - niether of which fit this scenario.
>Has anyone else had this problem and found a way around it. I can't
>replicate the problem on any of our development or test servers - it only
>happens on the production server. All servers are the same spec - W2K
>Server and SQL Server 2000 - all service packed up.
>Any help appreciated.
>Cheers,
>Cameron
>
>|||even though the statement may be simple, inserts could
have complex parsing for NULL conditions etc
the standard windows program defaults to 1MB stack size.
however, i believe this is reduced to 256K on sql server
for performance reasons.
in either Visual Studio C/C++ or the Windows Server EE
Customer Support Diagnostic, there are the utilities
editbin.exe and imagecfg.exe that lets you reconfigure the
stack size (sizes are in hex)
but i would try modifying your insert statement before
modifying the sql server binary, since this needs to be
redone every hotfix, sp etc
>--Original Message--
>I get the following message when I'm inserting a row into
a table. The
>statement is very simple, but there is a large amount of
data going into one
>column.
>Internal Query Processor Error: The query processor ran
out of stack space
>during query optimization
>All the KB articles I've found so far refer to queries
that have a large
>number of elements in an IN clause, or a CASE statement
with a large number
>of WHEN clauses - niether of which fit this scenario.
>Has anyone else had this problem and found a way around
it. I can't
>replicate the problem on any of our development or test
servers - it only
>happens on the production server. All servers are the
same spec - W2K
>Server and SQL Server 2000 - all service packed up.
>Any help appreciated.
>Cheers,
>Cameron
>
>.
>|||I would try DBCCs on the database in question, and if that didn't help, I
would call product support services and get them to help you out if you can
afford it. That sounds bad.
And don't cross post!
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"cj" <smuffnstuff@.hotmail.com> wrote in message
news:O7Ptg8aoDHA.3612@.TK2MSFTNGP11.phx.gbl...
> I get the following message when I'm inserting a row into a table. The
> statement is very simple, but there is a large amount of data going into
one
> column.
> Internal Query Processor Error: The query processor ran out of stack space
> during query optimization
> All the KB articles I've found so far refer to queries that have a large
> number of elements in an IN clause, or a CASE statement with a large
number
> of WHEN clauses - niether of which fit this scenario.
> Has anyone else had this problem and found a way around it. I can't
> replicate the problem on any of our development or test servers - it only
> happens on the production server. All servers are the same spec - W2K
> Server and SQL Server 2000 - all service packed up.
> Any help appreciated.
> Cheers,
> Cameron
>|||What is this 1MB stack space ?
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3a1bf$bba5da20$a401280a@.phx.gbl...
> even though the statement may be simple, inserts could
> have complex parsing for NULL conditions etc
> the standard windows program defaults to 1MB stack size.
> however, i believe this is reduced to 256K on sql server
> for performance reasons.
> in either Visual Studio C/C++ or the Windows Server EE
> Customer Support Diagnostic, there are the utilities
> editbin.exe and imagecfg.exe that lets you reconfigure the
> stack size (sizes are in hex)
> but i would try modifying your insert statement before
> modifying the sql server binary, since this needs to be
> redone every hotfix, sp etc
> >--Original Message--
> >I get the following message when I'm inserting a row into
> a table. The
> >statement is very simple, but there is a large amount of
> data going into one
> >column.
> >
> >Internal Query Processor Error: The query processor ran
> out of stack space
> >during query optimization
> >
> >All the KB articles I've found so far refer to queries
> that have a large
> >number of elements in an IN clause, or a CASE statement
> with a large number
> >of WHEN clauses - niether of which fit this scenario.
> >
> >Has anyone else had this problem and found a way around
> it. I can't
> >replicate the problem on any of our development or test
> servers - it only
> >happens on the production server. All servers are the
> same spec - W2K
> >Server and SQL Server 2000 - all service packed up.
> >
> >Any help appreciated.
> >
> >Cheers,
> >
> >Cameron
> >
> >
> >.
> >|||Have you got an insert trigger on the table? What are your settings for
nested and recursive triggers?
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"cj" <smuffnstuff@.hotmail.com> wrote in message
news:O7Ptg8aoDHA.3612@.TK2MSFTNGP11.phx.gbl...
> I get the following message when I'm inserting a row into a table. The
> statement is very simple, but there is a large amount of data going into
one
> column.
> Internal Query Processor Error: The query processor ran out of stack space
> during query optimization
> All the KB articles I've found so far refer to queries that have a large
> number of elements in an IN clause, or a CASE statement with a large
number
> of WHEN clauses - niether of which fit this scenario.
> Has anyone else had this problem and found a way around it. I can't
> replicate the problem on any of our development or test servers - it only
> happens on the production server. All servers are the same spec - W2K
> Server and SQL Server 2000 - all service packed up.
> Any help appreciated.
> Cheers,
> Cameron
>

Query Problems - Server Configuration?

Hi All,
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format = 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id =s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path =f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format ='zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id
> And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000,
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large
values, the insert/update operation will fail.
> Can Any one Help to fix this SQL Enterprise Manager?
>|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not configurable. Work with
the vendor of the app and see what can be done to handle this. One possible thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that the intermediate steps
of the query processing creates some work table for which the row length exceeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...
> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that generates a
DQL Query (SQL like Query) on the underlying database. As per the error description, it looks like,
I have some Server configuration problem like
> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> > Hi All,
> >> I was running the following Query
> >> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path
FROM
> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%')
AND
> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id => s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path => f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format => 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and (
language_code='en_US'
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from
dm_relation r
> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where
sys1.language_code
> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label ='Active' ) )))
> ORDER BY s.i_chronicle_id,s.r_object_id
> >> And the Error: "A database error has occurred during the creation of a cursor ('
STATE=01000,
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length
is 900
> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination
of large
> values, the insert/update operation will fail.
> >> Can Any one Help to fix this SQL Enterprise Manager?
> >

Query Problems - Server Configuration?

quote:

> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,[/
color]

c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label,
f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_syso
bject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/acceler
a.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r
_object_id =
s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Act
ive' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT
EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_ch
ronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_fold
er_path =
f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format =
'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full
_format =
'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and
( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pare
nt_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF'
and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 wh
ere sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_
label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id

quote:

> And the Error: "A database error has occurred during the creation of a cursor (' STAT
E=01000,

CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key
length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For s
ome combination of large
values, the insert/update operation will fail.

quote:

> Can Any one Help to fix this SQL Enterprise Manager?
>

|||Hi,
The Query is generated by Documentum. As you know Documentum is a middleware
that generates a DQL Query (SQL like Query) on the underlying database. As
per the error description, it looks like, I have some Server configuration p
roblem like
1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
2) Maximum key length = 900 bytes
what do these signify? How to increase these from EM?
-- Tibor Karaszi wrote: --
You could try adding the ROBUST PLAN hint to your query. Read more about it
in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...

quote:

> Hi All,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label,
f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_syso
bject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/acceler
a.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r
_object_id =
s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Act
ive' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT
EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_ch
ronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_fold
er_path =
f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format =
'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full
_format =
'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and
( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pare
nt_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF'
and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 wh
ere sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_
label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id[QUOTE]
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key
length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For s
ome combination of large
values, the insert/update operation will fail.[QUOTE]
>

|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not
configurable. Work with
the vendor of the app and see what can be done to handle this. One possible
thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that
the intermediate steps
of the query processing creates some work table for which the row length exc
eeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...

quote:

> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that gener
ates a

DQL Query (SQL like Query) on the underlying database. As per the error desc
ription, it looks like,
I have some Server configuration problem like

quote:

> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more ab
out it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...>
ic.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_fold
er_path

FROM

quote:

> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f,
dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/con
tact/%')

AND

quote:

> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id A
ND sr.r_object_id =
> s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label
= 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss')
OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id =
s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w
.r_folder_path =
> f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_f
ormat = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w
.i_full_format =
> 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ([/color
]

language_code='en_US'

quote:
[color=darkr
ed]
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id fro
m

dm_relation r

quote:

> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATI
ON_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where[/colo
r]

sys1.language_code

quote:

> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label =[/
color]

'Active' ) )))

quote:

> ORDER BY s.i_chronicle_id,s.r_object_id
STATE=01000,[QUOTE]
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length[/c
olor]

is 900

quote:

> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combi
nation

of large[QUOTE]
> values, the insert/update operation will fail.

Wednesday, March 28, 2012

Query problems

I am running a the following query:

SELECT [LICENSE_STATE], [LICENSE_PLATE], [TICKET_NUMBER], [DATE_ISSUED], [TIME_ISSUED], [MAKE_TYPE]
FROM dmv;

When I run the query I get multiple violations on an individual license plate. I only want it to display the license plate once even if it has multiple violations. I thought I could use a distinct statement to filter out the multiple plate entries but it either filters the plates and does not show any of the other information or it doesn't filter the results at all.

Here is the distict satement I am using that filters out everything except for the plates.

SELECT DISTINCT dmv.LICENSE_PLATE
FROM
(
SELECT [LICENSE_STATE], [LICENSE_PLATE], [TICKET_NUMBER], [DATE_ISSUED], [TIME_ISSUED], [MAKE_TYPE]
FROM dmv
);

Any help would be appreciated.Never Mind I figured it out

query problem (lack of knowledge on my part)

Hello,

I've been trying for a couple of days now with the following problem in SQL

Ive a table called tblRresults that has the following

Date - just a regular date

OfficeLocation - just a unique id such as a zip code

SampleType - contains things like wall, floor, beam

SampleResult - "Detected" or "NotDetected"

When populated the table can look like

12/12/05 99505 Wall Detected

12/12/05 99505 Wall NotDetected

10/04/05 99211 Beam Detected

10/04/05 99111 Floor NotDetected

What I want is (I guess) a crosstab query that produces the following

Date Location SampleType CountDetected CountNotDetected

12/12/05 99505 Wall 1 1

10/04/05 99211 Beam 2 0 (or null would be ok)

10/04/05 99111 Floor 0 2

Any help gratefully accepted, this has been driving me mental!

thanks

Select Date,Location,SampleType,SUM(CASE WHEN SampleResult='Detected' THEN 1 ELSE 0 END) AS CountDetected,SUM(CASE WHEN SampleResult='NotDetected' THEN 1 ELSE 0 END) AS CountNotDetected

FROM table

GROUP BY Date,Location,SampleType

|||thanks very much! - I had no idea CASE existed

Query problem - TIPOS IN ()

Hi,

I have the following query

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE CONSTELEFONICA
@.E1 VARCHAR(50),
@.TIPOS VARCHAR(50),
@.PERINI DATETIME,
@.PERFIM DATETIME,
@.PERINI2 DATETIME,
@.PERFIM2 DATETIME
AS
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI as DATA_INICIAL, @.PERFIM as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI AND @.PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA IN (@.TIPOS)
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI2 as DATA_INICIAL, @.PERFIM2 as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI2 AND @.PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA NOT IN (@.TIPOS)
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

And from my application I call the query like...

EXEC CONSTELEFONICA '01133722000', 'CONUR, LOCAL', '20060205','20060304 23:59:59.997', '20060203','20060302 23:59:59.997'

But it doesn't work because the CLASSIFICA_TELEFONICA NOT IN (@.TIPOS) does not recognise as CLASSIFICA_TELEFONICA NOT IN ('CONUR', 'LOCAL') how can I make this query ?

Thanks

You can't do it like that, read Arrays and Lists in SQL Server by SQL Server MVP Erland Sommarskog (http://www.sommarskog.se/arrays-in-sql.html)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

I understood but I don't know how to apply in my query can you help me ?

Thanks

|||

I tried doing this

SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI as DATA_INICIAL, @.PERFIM as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@.TIPOS) I ON T.CLASSIFICA_TELEFONICA = I.str WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI AND @.PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI2 as DATA_INICIAL, @.PERFIM2 as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@.TIPOS) I ON T.CLASSIFICA_TELEFONICA <> I.str WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI2 AND @.PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA

But I got the message that CHARLIST_TO_TABLE_SP is not a valid function...but in the reference site it's a procedure...and I've created it

|||Look for the function: iter_charlist_to_table for the 2000 way to do it. That was the 7.0 method, since we didn't have functions back then.sql

Query problem

Hi,

I've got the following problem:

In a table named "Commission" are all commissions of sales people per month listed.
Now I have to calculate and update the following:
I have to sum up the commissions (data type money) by month (smallint) and salesmanID(int). If the monthly sum is getting negative (yes, can happen!), I have to set it back to "0" for any sales record in the table "Commission".

I tried with views and subsets of select - statements, but I did not find a solution and I don't want to use MDX-statements instead in a cube later.

Has any brain a solution for me available ?

Thx a lot

dajmSummarizing Data Using COMPUTE and COMPUTE BY

In books online

Basically you can follow pretty much any select with a COMPUTE directive and pull up any aggregate you want.|||Originally posted by HanafiH
Summarizing Data Using COMPUTE and COMPUTE BY

In books online

Basically you can follow pretty much any select with a COMPUTE directive and pull up any aggregate you want.

Sorry, but COMPUTE is not helping me as I have to compare the result with `0`and to update the same fact table . Any ideas ?

Thx.|||use a cursor

Originally posted by dajm
Hi,

I've got the following problem:

In a table named "Commission" are all commissions of sales people per month listed.
Now I have to calculate and update the following:
I have to sum up the commissions (data type money) by month (smallint) and salesmanID(int). If the monthly sum is getting negative (yes, can happen!), I have to set it back to "0" for any sales record in the table "Commission".

I tried with views and subsets of select - statements, but I did not find a solution and I don't want to use MDX-statements instead in a cube later.

Has any brain a solution for me available ?

Thx a lot

dajm|||set based solutions are almost always better than cursor solutions

update the Commission table for each month/salesman where total commissions for the month are negative:update Commission
set commissions = 0
from Commission as table1
inner
join (
select themonth
, salesmanID
from Commission
group
by themonth
, salesmanID
having sum(commissions) < 0
) as table2
on table1.themonth = table2.themonth
and table1.salesmanID = table2.salesmanID(caution: untested, but it should work)

rudy
http://r937.com/|||try CASE along with COMPUTE statement.|||Super solution. Thx a lot...

dajm

Originally posted by r937
set based solutions are almost always better than cursor solutions

update the Commission table for each month/salesman where total commissions for the month are negative:update Commission
set commissions = 0
from Commission as table1
inner
join (
select themonth
, salesmanID
from Commission
group
by themonth
, salesmanID
having sum(commissions) < 0
) as table2
on table1.themonth = table2.themonth
and table1.salesmanID = table2.salesmanID(caution: untested, but it should work)

rudy
http://r937.com/

Query Problem

Ok,

I know that this is supposed to be simple, but I cannot remember how to get around it. How does one get around the following problem?

update sources
Set Reference = 'Coaching Women's Soccer'
where Refsymbol = 'w'

How does one get around extra quotation mark (the apostrophe in Women's).When you insert into database, you replace a single quatation mark with 2 single quotation marks:


' -> ''

When you want to show the record on a web page, you reverse the procedure:


'' -> '

You should write a function to handle this and other characters like e.g. < and > throughout your application.

Hth.|||Thanks :)

Query Problem

Query problem

I am trying the following query in and oleDbCommand:
SELECT PartLocations.LocationName, Sum(PartsJournal.Quantity) AS
SumOfQuantity, PartsJournal.PartsLotNumber
FROM PartLocations INNER JOIN PartsJournal ON PartLocations.LocationID
= PartsJournal.LocationID
GROUP BY PartLocations.LocationName, PartsJournal.PartsLotNumber,
PartsJournal.PartNumber, PartsJournal.LocationID
HAVING (((Sum(PartsJournal.Quantity))>0) AND
((PartsJournal.PartsLotNumber) Is Not Null) AND
((Min(PartsJournal.Date))>'5/1/2001') AND
((PartsJournal.PartNumber)='030020') AND
((PartsJournal.LocationID)<>1))
ORDER BY PartsJournal.PartNumber, MIN(PartsJournal.Date);

It generates an excpetion. If I take out the MIN(PartsJournal.Date) in
the ORDER BY clause it works fine. But when I check the query as is
in SQL Query Analyzer it runs fine with the MIN(PartsJournal.Date).
Why the difference?First, clean up the posting. DATE is a reserved word in SQL as well as
too vague to be a name. All those silly parens did was make the code
almost impossible to read. You need to learn about alias names for
tables, ISO-8601 for dates and ISO-11179 for data element names. Is
this what you meant to post? This looked like machine generated code,
not something meant for a human programmer to maintain. Try this
version:

SELECT L1.location_name, J1.parts_lot_nbr,
SUM(J1.part_qty) AS total_qty,
MIN(J1.foo_date) AS first_foo_date
FROM PartLocations AS L1,
PartsJournal AS J1
WHERE P1.location_id = J1.location_id
AND J1.parts_lot_nbr IS NOT NULL
AND J1.part_nbr = '03*002 0'
AND J1.location_id <> 1
GROUP BY L1.location_name, J1.parts_lot_nbr
HAVING SUM(J1.parts_qty) > 0
AND MIN(J1.foo_date) > '2001-05-01'
ORDER BY J1.parts_lot_nbr, first_foo_date;

The extra columns in your original GROUP BY would work, but would
produce rows without complete information. A typo?

>> It generates an excpetion. If I take out the MIN(J1.foo_date) in
the ORDER BY clause it works fine. But when I check the query as is
in SQL Query Analyzer it runs fine with the MIN(J1.foo_date). Why the
difference? <<

Dialect versus Standards. In Standard SQL, the ORDER BY clause is part
of a cursor. The cursor uses the result set of the query and converts
it into a sequential file structure for the host language. It can only
use column names that appear in the SELECT list. This is one reason
why I tell people not to write proprietary code.|||Thank you very much for the help.
--CELKO-- wrote:
> First, clean up the posting. DATE is a reserved word in SQL as well
as
> too vague to be a name.

Yes, sadly this is as a column name throughout the database tables and
I am reluctant to change it do to the amount of coding that would be
required to be changed and since I have not found any queries which
give problems due to this.

All those silly parens did was make the code
> almost impossible to read.

Acutally I didn't post the actual code, but did post a machine
generated version that I was playing around with for testing. The
acutal code wasn't quite as bad. The difficulty I have is that the
actual code was in vb and is terrible to read:
"SELECT TOP 1 PartLocations.LocationName, Sum(PartsJournal.Quantity),"
_
& " PartsJournal.PartsLotNumber, MIN(PartsJournal.Date) AS Minofdate" _
...etc

When coding is there a method to keep code such as above more readable?

>You need to learn about alias names for

I'll definitely keep this in mind. I ocassionaly use aliases but often
feel that even though it keeps the query shorter it actually makes it
harder to read. For instance it took me a little while to realize you
used P1 by mistake in your post instead of L1.

> tables, ISO-8601 for dates and ISO-11179 for data element names.

Thanks - I just studied both and will try to incorporate some of that
into all future designs. I only wish I had the time to redo what we
have already setup.

> this what you meant to post? This looked like machine generated
code,
> not something meant for a human programmer to maintain. Try this

No I didn't per above.

> version:
> SELECT L1.location_name, J1.parts_lot_nbr,
> SUM(J1.part_qty) AS total_qty,
> MIN(J1.foo_date) AS first_foo_date
> FROM PartLocations AS L1,
> PartsJournal AS J1
> WHERE P1.location_id = J1.location_id
> AND J1.parts_lot_nbr IS NOT NULL
> AND J1.part_nbr = '03*002 0'
> AND J1.location_id <> 1
> GROUP BY L1.location_name, J1.parts_lot_nbr
> HAVING SUM(J1.parts_qty) > 0
> AND MIN(J1.foo_date) > '2001-05-01'
> ORDER BY J1.parts_lot_nbr, first_foo_date;
> The extra columns in your original GROUP BY would work, but would
> produce rows without complete information. A typo?

Not a typo, just a mistake.

> >> It generates an excpetion. If I take out the MIN(J1.foo_date) in
> the ORDER BY clause it works fine. But when I check the query as is
> in SQL Query Analyzer it runs fine with the MIN(J1.foo_date). Why the
> difference? <<
> Dialect versus Standards. In Standard SQL, the ORDER BY clause is
part
> of a cursor. The cursor uses the result set of the query and
converts
> it into a sequential file structure for the host language. It can
only
> use column names that appear in the SELECT list. This is one reason
> why I tell people not to write proprietary code.

Your query worked. After some playing around I found that the fix to
my query was to move most of my HAVING clause to a WHERE clause. I'm
still not quite sure I understand what was wrong with having it there.
Can you explain? The final query with minimum change that worked is:

SELECT TOP 1 PartLocations.LocationName, Sum(PartsJournal.Quantity),
PartsJournal.PartsLotNumber, MIN(PartsJournal.Date) AS Minofdate
FROM PartLocations, PartsJournal
WHERE PartLocations.LocationID = PartsJournal.LocationID
AND PartsJournal.PartNumber = '030020'
AND PartsJournal.PartsLotNumber IS NOT NULL
AND PartsJournal.LocationID <> 1
GROUP BY PartsJournal.PartsLotNumber, PartLocations.LocationName
HAVING MIN(PartsJournal.Date) > '5/1/2001'
AND Sum(PartsJournal.Quantity) > 0
ORDER BY PartsJournal.PartsLotNumber, Minofdate|||>> After some playing around I found that the fix to
my query was to move most of my HAVING clause to a WHERE clause. I'm
still not quite sure I understand what was wrong with having it there.
Can you explain? <<

Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group. In your case, youhad columns that were in violation of
rule (c) above.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.

As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.|||Thanks again. I noticed in the query you posted that you did not
include INNER JOIN notation but just did the join through the where
clause. Isn't the ANSI standard to include the JOIN instructions in
the table selection?|||One other question I wondered if you had any insight into? When does
it make sense to do the query definiton on the server side and just
reference this view from code. For example in my query above I could
have created a parameter query on the server and just provided the
parameters from code. Would this be superior? Should I do this for
all queries? It certainly seems as though it would make for more
readable code and testing of the queries would be better.|||(signaturefactory@.signaturefactory.com) writes:
> Thanks again. I noticed in the query you posted that you did not
> include INNER JOIN notation but just did the join through the where
> clause. Isn't the ANSI standard to include the JOIN instructions in
> the table selection?

Both:

SELECT ...
FROM a, b
WHERE a.col = b.col

and

SELECT ...
FROM a
JOIN b ON a.col = b.col

is compliant with ANSI standards. However, if you need to do an outer
join, you can only use the latter form. So many people feel that they
could just as well go with the latter form always. It's also good because
it separates the join conditions from the filter conditions. That for
also makes it a little harder to write uninteded cross-joins:

SELECT ...
FROM a, b, c
WHERE a.col = b.col

Oops! Forgot to take "c" out of the query.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(signaturefactory@.signaturefactory.com) writes:
> I am trying the following query in and oleDbCommand:
> SELECT PartLocations.LocationName, Sum(PartsJournal.Quantity) AS
> SumOfQuantity, PartsJournal.PartsLotNumber
> FROM PartLocations INNER JOIN PartsJournal ON PartLocations.LocationID
>= PartsJournal.LocationID
> GROUP BY PartLocations.LocationName, PartsJournal.PartsLotNumber,
> PartsJournal.PartNumber, PartsJournal.LocationID
> HAVING (((Sum(PartsJournal.Quantity))>0) AND
> ((PartsJournal.PartsLotNumber) Is Not Null) AND
> ((Min(PartsJournal.Date))>'5/1/2001') AND
> ((PartsJournal.PartNumber)='030020') AND
> ((PartsJournal.LocationID)<>1))
> ORDER BY PartsJournal.PartNumber, MIN(PartsJournal.Date);
> It generates an excpetion. If I take out the MIN(PartsJournal.Date) in
> the ORDER BY clause it works fine. But when I check the query as is
> in SQL Query Analyzer it runs fine with the MIN(PartsJournal.Date).
> Why the difference?

And the text of the exception was? I have no idea why the query
failed, but if problem is in SQLOLEDB or OleDb client, it does not help
us if you keep the error message a secret.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(signaturefactory@.signaturefactory.com) writes:
> One other question I wondered if you had any insight into? When does
> it make sense to do the query definiton on the server side and just
> reference this view from code. For example in my query above I could
> have created a parameter query on the server and just provided the
> parameters from code. Would this be superior? Should I do this for
> all queries? It certainly seems as though it would make for more
> readable code and testing of the queries would be better.

In many shops, direct SQL statements from the client are not permitted,
but all calls have to be made from stored procedures. There are a
couple of advantages with this. For a longer discussion of these
advantages, see the first section "Why Stored Procedures" in my
article on dynamic SQL on http://www.sommarskog.se/dynamic_sql.html.

The drawback with stored procedures is that for applications that
have a moderate amount of SQL code, having all the SQL code in the
application, makes deployment a little easier, since there is one
less tier to install to. This breaks down, though, if there plentyful
of chages to the tables, since in this case you have a deployemnt
to do on the SQL Server side anyway.

As for whether you should write:

sql = "SELECT ... FROM tbl WHERE col = '" & Value & "'"

sql = "SELECT ... FROM tbl WHERE col = ?"

and supply a value for ? with OleDbParameters, there is little to
discussion: you should to the latter. Then the client library handles
format of difficult data types such as dates and decimal values that
are subject different interpretation because of regional settings. But
the most important issue is something known as SQL injection. What if
Value above contains "'-- DROP TABLE tbl"? Don't laugh, this is a very
popular technique to gain access to systems.

Finally, since you are using SQL Server, why do you use OleDB client
and not SqlClient? If your app needs to connect to other engines,
for instance Oracle or Access, that's a good reason to use OleDB
Client, but else go for SqlClient.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you very much.

>In many shops, direct SQL statements from the client are not
permitted,
>but all calls have to be made from stored procedures. There are a
>couple of advantages with this. For a longer discussion of these
>advantages, see the first section "Why Stored Procedures" in my
>article on dynamic SQL on http://www.sommarskog.se/dynamic_sql.html.

I am a definite convert on this. I am not goiing to allow direct SQL
statements any longer. I have been working with this today and the
advantages of stored procedures are very apparent. Fortunately
deployment is not an issue for our application.

>Finally, since you are using SQL Server, why do you use OleDB client
>and not SqlClient? If your app needs to connect to other engines,
>for instance Oracle or Access, that's a good reason to use OleDB
>Client, but else go for SqlClient.

This was a legacy app that was orginally written for access. We will
definitely use SqlClient in the future. Thanks.|||(signaturefactory@.signaturefactory.com) writes:
> I am a definite convert on this. I am not goiing to allow direct SQL
> statements any longer. I have been working with this today and the
> advantages of stored procedures are very apparent. Fortunately
> deployment is not an issue for our application.

Glad to hear that you got the message!

> This was a legacy app that was orginally written for access. We will
> definitely use SqlClient in the future. Thanks.

OK, that's at least a half-good reason.

For ADO .Net 1.1 the advantages with SqlClient over OleDb Client is
moderate. You can expect some better performance, and there are a
few issues with OleDb Client that SqlClient does not have.

For ADO .Net 2.0, currently in beta, the picture changes, as SqlClient
gets more features that is not in OleDB client.

Thankfully, porting is fairly easy as a large part of the work is a
search/replace thing. The one other difference is that with OleDb Client
you use ? as placeholder for parameters, with SqlClient, you use named
parameters like in T-SQL:

SELECT ... FROM tbl WHERE col = @.value

instead of

SELECT ... FROM tbl WHERE col = ?

I should also add that OleDb Client has one feature that SqlClient does
not: OleDB Client has methods for dealing with recordsets from ADO, which
is useful if you have an app with a mix of native code and .Net code of
legacy reasons.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||No, that is optional and I find it to be confusing and crowded most of
the time, You have to use it for OUTER JOINs, tho. Think about trying
to write this with infixed operators:

SELECT *
FROM T1, T2, T3
WHERE T1.a BETWEEN T2.b AND T3.c;

Do you see how the "between-ness" is hidden from the reader?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> No, that is optional and I find it to be confusing and crowded most of
> the time, You have to use it for OUTER JOINs, tho. Think about trying
> to write this with infixed operators:
> SELECT *
> FROM T1, T2, T3
> WHERE T1.a BETWEEN T2.b AND T3.c;

Yes, the good thing about that is just that. That you have to think.
Because while the above may make sense at first glance, well, assuming
that T1, T2 and T3 have hundred rows each, what real business problem
would be described this way?

(Of course, if at least one of T2 and T3 are one-row tables, then above
makes perfect sense. But then you could easily write it with JOIN operators
as well.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>.. what real business problem would be described this way? <<

1) Make T1, T2 and T3 into subqueries on a Sales table

Let T1 = My sales in month (m)
T2 = Worst sales for month (m) by someone else
T3 = Best sales for month (m) by someone else

Now do a little math in the SELECT to see where I fall in the range for
each month as a percentage.

2) Make T2 and T3 the same look-up table for postal code ranges (the
ZIP code in the US is a geographical hierarchy, so this works; I do not
know about Sweden) -- T1.zip_code BETWEEN T2.low_zip_code AND
T2.high_zip_code. This is VERY common in reports that use any kind of
report hierarchy.

Another example of the ease of reading the "old way"

SELECT *
FROM T1, T2. T3
WHERE 42 IN (T1.a, T2.b, T3.c);

Versus somethging like this:

SELECT *
FROM T1
INNER JOIN
T2
ON (1 = 1) -- actually a cross join in disguise
INNER JOIN
T3
ON 42 IN (T1.a, T2.b, T3.c);

ORM recognized that there are n-ary relationships where (n > 2), but
when you have an infixed notation, you are stuck with binary
relationships. Newbies like this notation for two reasons:

1) It looks like ACCESS output and that is where they started
2) It looks like an ER diagram, rather than an ORM diagram.|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>>.. what real business problem would be described this way? <<
> 1) Make T1, T2 and T3 into subqueries on a Sales table
> Let T1 = My sales in month (m)
> T2 = Worst sales for month (m) by someone else
> T3 = Best sales for month (m) by someone else
> Now do a little math in the SELECT to see where I fall in the range for
> each month as a percentage.

SELECT ...
FROM (SELECT m, sales)
FROM tbl
WHERE salesperson = @.me
GROUP BY m) me
JOIN (SELECT m, sales = MIN(sales)
FROM tbl
WHERE salesperson <> @.me) bad OM me.m = bad.m
JOIN (SELECT m, sales = MAX(sales)
FROM tbl
WHERE salesperson <> @.me) good OM me.m = good.m
WHERE me.sales BETWEEN AND bad.sales AND good.sales

Call it cheating if you like. I say that the BETWEEN condition is a
filter condition - not a join condition.

> 2) Make T2 and T3 the same look-up table for postal code ranges (the
> ZIP code in the US is a geographical hierarchy, so this works; I do not
> know about Sweden) -- T1.zip_code BETWEEN T2.low_zip_code AND
> T2.high_zip_code. This is VERY common in reports that use any kind of
> report hierarchy.

Either T1 or T2 are single-row tables, or, I suspect, there is some
more join condition.

> Another example of the ease of reading the "old way"
> SELECT *
> FROM T1, T2. T3
> WHERE 42 IN (T1.a, T2.b, T3.c);
> Versus somethging like this:
> SELECT *
> FROM T1
> INNER JOIN
> T2
> ON (1 = 1) -- actually a cross join in disguise
> INNER JOIN
> T3
> ON 42 IN (T1.a, T2.b, T3.c);

You can write:

SELECT ...
FROM T1
CROSS JOIN T2
CROSS JOIN T3
WHERE 42 IN (T1.a, T2.b, T3.c)

And I have might have written a few queries like this. The situations
where the JOIN condition does not fit JOIN ... ON are so rare, that
the extra CROSS JOIN is not a problem. And you see very clear that the
cross join is intended.

Now, if only this had been outlawed:

SELECT *
FROM A
JOIN B ON A.col = A.col

I would have been saved some stupid errors.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 26, 2012

Query problem

Hi all,

I am trying to write a query to do the following in SQL server but am
struggling:

I have a table with a int number field in it and I want to find out
the lowest unused number in the table. Unfortunately, the numbers are
not necesarily sequential (due to deletes). So I may have the
following records:

num
--
1
2
3
4
6
8
9
10

I want a query to return me the value 5 - i.e. the lowest unused int
in the table (assuming counting starts at 1). I know I could do this
using a temporary table containing a full sequence of possible numbers
(given some ceiling) using something like:
select min(num) from temptable where num not in (select num from
maintable)
This would however mean creating a temporary table which I am trying
to avoid. I could also use a cursor to itterate through the records
above to find the lowest unused number but it seems overkill.

I'm sure there must be a way of doing this using a simple (possibly
nested) query. Any ideas?

Thanks in advance

--JamesSELECT COALESCE(MIN(T1.num)+1,1)
FROM YourTable AS T1
LEFT JOIN YourTable AS T2
ON T1.num = T2.num - 1
WHERE T2.num IS NULL

--
David Portas
SQL Server MVP
--|||David,
Just what I needed - cheers - nice bit of SQL too!

Regards

--James|||SELECT MIN(F1.num + 1)
FROM Foobar AS F1
UNION
VALUE (0) -- needs to be SELECT 0 in dialect
WHERE (T1.num +1)
NOT IN (SELECT num FROM Foobar);

This will give you an answer on the high end if the sequence is
complete.sql

Query problem

Hi,

I've got a problem with the following query, i'd like to write this in SQL for reporting services.

if ($par == "Ok"){
$query .= ' and b.date is not null '
}

if ($par == "NOK"{
$query = 'and b.date is null'
}

How should I do this? If my parameter is OK, the date should be filled in. Otherwise...

THX

You can do something like this

select .... where @.par = 'Ok' AND b.date is not null

union

select .... where @.par = 'NOK' AND b.date is null

I haven't tested it but it should work. The idea being that only one side of the union can have a result because only one side's where clause will evaluate to true.

Query problem

I have the following tables in a mailng list management program:
CREATE TABLE [dbo].[tabMailGroupSubscribers] (
[MailSubscriberID] [uniqueidentifier] NOT NULL ,
[MailGroupID] [uniqueidentifier] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailGroups] (
[MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[MemberOf] [uniqueidentifier] NULL ,
[GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[DirectLink] [bit] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Problem:
How can I dispaly a list containing all the mailgroups in tabMailgroups, and
have the ability to see if a subscriber from tabMailSubscribers has
subscribed to a specific group, and wich not?
Something like this...
CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
end
Return
Only problem is this is not working.
Thanks for any helpUse [tabMailGroupSubscribers].[MailGroupID] in the CASE expression.
...
(
CASE
WHEN [tabMailGroupSubscribers].[MailGroupID] IS NULL THEN 0
ELSE 1
END
) AS Subscribed
...
AMB
"HB" wrote:

> I have the following tables in a mailng list management program:
> CREATE TABLE [dbo].[tabMailGroupSubscribers] (
> [MailSubscriberID] [uniqueidentifier] NOT NULL ,
> [MailGroupID] [uniqueidentifier] NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tabMailGroups] (
> [MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [MemberOf] [uniqueidentifier] NULL ,
> [GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [DirectLink] [bit] NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tabMailSubscribers] (
> [MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [HotelID] [int] NOT NULL ,
> [Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Problem:
> How can I dispaly a list containing all the mailgroups in tabMailgroups, a
nd
> have the ability to see if a subscriber from tabMailSubscribers has
> subscribed to a specific group, and wich not?
> Something like this...
> CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
> @.MailSubscriberID varchar(50)
> AS
> Begin
> SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
> tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
> tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
> tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
> AS Subscribed
> FROM tabMailGroups LEFT OUTER JOIN
> tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
> tabMailGroupSubscribers.MailGroupID
> WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
> end
> Return
> Only problem is this is not working.
>
> Thanks for any help
>
>|||Hi AMB
This does not seem to do the trick.
It is still not returning the correct results. There is records outstanding
from the results.
Regards
Hennie
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F1B1C8B2-62E6-4AF7-821A-F6DE740C0D10@.microsoft.com...
> Use [tabMailGroupSubscribers].[MailGroupID] in the CASE expression.
> ...
> (
> CASE
> WHEN [tabMailGroupSubscribers].[MailGroupID] IS NULL THEN 0
> ELSE 1
> END
> ) AS Subscribed
> ...
>
> AMB
> "HB" wrote:
>

Query problem

hello,
I have written following query to retrive the values
select a.Comp_name,b.desig_name,c.Ind_name,d.Function_name from
Company_Mst a,Designation_Mst b,Industry_Mst c, Function_Mst d where
a.Company_id=1 and b.desig_id=778 and Industry_id=147 and
Function_id=1;
when i execute this query i am getting following result
Comp_name |Desig_name |Ind_name| Function_name
Microsoft | Manager| IT | Programming
i want to write such query when i give any id value as 0 then it
should return me blank record for that perticular id , means as
follows
select a.Comp_name,b.desig_name,c.Ind_name,d.Function_name from
Company_Mst a,Designation_Mst b,Industry_Mst c, Function_Mst d where
a.Company_id=0 and b.desig_id=778 and Industry_id=147 and
Function_id=0;
Comp_name Desig_name Ind_name Function_name
|Manager | IT
(above is Required output)
as i am using and operator the condition is not fulfilling because id
0 is not in table. i ve also used or operator but it is giving me all
possible combinations.
so please any body tell me query so that i will get only one record as
i required.
AjayI'm not certain if I've understood you correctly but here goes. To
return a row of nulls whenever there is no matching row:
SELECT MAX(A.comp_name), MAX(B.desig_name),
MAX(C.ind_name), MAX(D.function_name)
FROM Company_Mst A, Designation_Mst B, Industry_Mst C, Function_Mst D
WHERE A.company_id = @.company_id
AND B.desig_id = @.desig_id
AND C.industry_id = @.industry_id
AND D.function_id = @.function_id
GROUP BY A.comp_name, B.desig_name, C.ind_name, D.function_name ;
if you only want to return this "blank" row when one of your criteria
is null then add:
HAVING 0 IN (@.company_id, @.desig_id, @.industry_id, @.function_id)
OR A.comp_name IS NOT NULL
(that's assuming comp_name is not nullable - please post DDL and sample
data in future so that we don't have to guess)
If you mean you want a dynamic search condition, see:
http://www.sommarskog.se/dyn-search.html
David Portas
SQL Server MVP
--|||CORRECTION. Assuming only one matching row:
SELECT MAX(A.comp_name), MAX(B.desig_name),
MAX(C.ind_name), MAX(D.function_name)
FROM Company_Mst A, Designation_Mst B, Industry_Mst C, Function_Mst D
WHERE A.company_id = @.company_id
AND B.desig_id = @.desig_id
AND C.industry_id = @.industry_id
AND D.function_id = @.function_id
HAVING 0 IN (@.company_id, @.desig_id, @.industry_id, @.function_id)
OR MAX(A.comp_name) IS NOT NULL ;
David Portas
SQL Server MVP
--
OR MAX(A.comp_name) IS NOT NULL
David Portas
SQL Server MVP
--

Query problem

I have the following tables in a mailng list management program:
CREATE TABLE [dbo].[tabMailGroupSubscribers] (
[MailSubscriberID] [uniqueidentifier] NOT NULL ,
[MailGroupID] [uniqueidentifier] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailGroups] (
[MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[MemberOf] [uniqueidentifier] NULL ,
[GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[DirectLink] [bit] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Problem:
How can I dispaly a list containing all the mailgroups in tabMailgroups, and
have the ability to see if a subscriber from tabMailSubscribers has
subscribed to a specific group, and wich not?
Something like this...
CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
end
Return
Only problem is this is not working.
Should I rateher change my table design to do this sort query, I'm not sure.
Thanks for any helpThe problem is that your where clause is making your LEFT OUTER JOIN
behave as an INNER JOIN. Try something like this:
CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID,
tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
end
Return
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au
*** Sent via Developersdex http://www.examnotes.net ***|||Hi William, thanks, it is working great.
Hennie
"William Bartholomew" <william(at)bartholomew[dot]id{dot}au> wrote in
message news:#RoA3QzOFHA.1564@.TK2MSFTNGP14.phx.gbl...
> The problem is that your where clause is making your LEFT OUTER JOIN
> behave as an INNER JOIN. Try something like this:
> CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
> @.MailSubscriberID varchar(50)
> AS
> Begin
> SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
> tabMailGroupSubscribers.MailSubscriberID,
> tabMailGroups.GroupEmailAddress,
> tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
> tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
> AS Subscribed
> FROM tabMailGroups LEFT OUTER JOIN
> tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
> tabMailGroupSubscribers.MailGroupID
> AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
> end
> Return
>
> Regards,
> William D. Bartholomew
> http://blog.bartholomew.id.au
> *** Sent via Developersdex http://www.examnotes.net ***

Query Problem

I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:
> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
> >I have the following query that is giving me an error that I can't quite
> >figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
> >Internal SQL Server error. I think it is related to the sub-query or the
> >group by. The sub-query executes successfully if I run it by itself.
> >
> > Any Ideas as to what I can try?
> >
> > Thanks
> >
> > Jeff
> >
> >
> >
> > Declare @.StartDate VarChar(12)
> > SET @.StartDate = '04/07/03'
> >
> > SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> > @.StartDate as StartDate
> > FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> > CASE
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> > DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> > /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> > And DateAdd(Day, 13, @.StartDate) Then 4 */
> > End AS Period
> > FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> > WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> > AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> > AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> > AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> > AND A.EMPLOYEEID = B.EMPLOYEEID
> > AND B.PayRuleName <> 'All Exempt'
> > AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate)
> > GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> > GROUP BY Plant, Period
> > ORDER BY Plant, Period DESC
> >
>
>

Query Problem

Hi, I have a problem with the WHERE IN statement
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> The IN clause will treat @.Id as a single value. If the list size is fixed,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > Hi, I have a problem with the WHERE IN statement
> >
> > The following statement works ok.
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN
> > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> >
> > But this statement throws a error : Conversion failed when converting
> > from a character string to uniqueidentifier.
> >
> >
> > declare @.Id varchar(4000)
> > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> >
> > Anyone have any Ideas?
> >
> > Thanks
> >
> > Toby
> >
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> >
> > The IN clause will treat @.Id as a single value. If the list size is fixed,
> > you can specify multiple parameters:
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> >
> > See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> > solutions. You also have additional XML options in SQL 2005.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Tobi" <toby.riley@.gmail.com> wrote in message
> > news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > > Hi, I have a problem with the WHERE IN statement
> > >
> > > The following statement works ok.
> > >
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN
> > > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> > >
> > > But this statement throws a error : Conversion failed when converting
> > > from a character string to uniqueidentifier.
> > >
> > >
> > > declare @.Id varchar(4000)
> > > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> > >
> > > Anyone have any Ideas?
> > >
> > > Thanks
> > >
> > > Toby
> > >
> >
> >
> >

query problem

hi i have the following query and 2 problems with it.
EventView is a view.
1. if pregnancy.dischargedate is null i want to display EventValue.
(Select ISNULL(pregnancy.dischargedate, EventValue)
works fine when discharge date is null it takes the value of dischargedate1.
but the problem is if discharge date has a value, it still displays null.
2. and i want to use dischargedate1 Between '1.12.1990' AND '12.12.2004'
it says invalid columnname dischargedate1.
select
Pregnancy.DischargeDate,
(Select ISNULL(pregnancy.dischargedate, EventValue)
From EventView
--I have a lot of where clauses and processing here.
))AS DischargeDate1
From Patient INNER JOIN Pregnancy ON Patient.Patientid = Pregnancy.Patientid
INNER JOIN Baby ON Baby.Pregnancyno = Pregnancy.Pregnancyno
where DischargeDate1 Between '1.1.1990' AND '1.1.2004'
thnx.Frazer,
if you do Select ISNULL(pregnancy.dischargedate,
EventValue), pregnancy.dischargedate, EventValue you
should see that when dischargedate is null then the
EventValue is returned, otherwise the dischargedate is
returned. Is this what you require? Your message is a
little confusing: 'it takes the value of dischargedate1' -
which is the expression alias.
For your second point, you can't refer to the expression
alias dischargedate1 in a where clause - you'll need to
repeat the expression instead.
HTH,
Paul Ibison

Friday, March 30, 2012

Query Processor Error

Query Problems - Server Configuration?

Query Problems - Server Configuration?

Wednesday, March 28, 2012

Query problems

query problem (lack of knowledge on my part)

Query problem - TIPOS IN ()

Query problem

Query Problem

Query Problem

Query problem

Monday, March 26, 2012

Query problem

Query problem

Query problem

Query problem

Query problem

Query Problem

Query Problem

query problem

Query Problem

Query longer in SP

Blog Archive

About Me