Thursday, March 22, 2012
buggy sql like command with wildcards to xml
create procedure MKR
@.mkrvar nvarchar(50)
AS
SELECT *
FROM QUADRA
WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
GO
so i call it like this on my browser :
http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
is not working...oh !
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
LIKE '%57%')FOR XML NESTED&root=root
it doesnt work too
so i trought that might be something buggy...donno...so i tried this
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
XML RAW&root=root
ok...it doesnt work...why ?
but this work
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR XML
RAW&root=root
so...why i cant use wildcards to generate an xml ?
i need a fix or a workarround for this asap, if someone could help :D
thanks
max
% is an escape character in a URL. For example you will see spaces
converted to %20.
Try %%57%% instead.
"Antonio Max" <maxspam@.bol.com.br> wrote in message
news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
>I have this stored procedure, witch works nice under the query mode
> create procedure MKR
> @.mkrvar nvarchar(50)
> AS
> SELECT *
> FROM QUADRA
> WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
> GO
> so i call it like this on my browser :
> http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
> is not working...oh !
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
> LIKE '%57%')FOR XML NESTED&root=root
> it doesnt work too
> so i trought that might be something buggy...donno...so i tried this
>
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
> XML RAW&root=root
> ok...it doesnt work...why ?
>
> but this work
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR
> XML
> RAW&root=root
> so...why i cant use wildcards to generate an xml ?
> i need a fix or a workarround for this asap, if someone could help :D
> thanks
> max
>
>
|||thanks a lot
forgot this;;.. eerr
anyway...it looks like this now
http://mywebsite/?sql=SELECT%20*%20F...DRA%20WHERE%20(QUADRA%20LIKE%20
'%2557%25')%20FOR%20XML%20RAW&root=root
where %25 is the % sign on url encoding char table
thanks
max
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:#T#EIIzKEHA.892@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> % is an escape character in a URL. For example you will see spaces
> converted to %20.
> Try %%57%% instead.
>
> "Antonio Max" <maxspam@.bol.com.br> wrote in message
> news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
9)[vbcol=seagreen]
FOR
>
BugCheck in SQL Server 2000 SP3a?
I'm seeing a dump file generated about every 4 minutes. The top of the dump
is:
* BEGIN STACK DUMP:
* 09/19/04 12:40:38 spid 0
*
* Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
Line
0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000190
Anyone from Microsoft interested in the rest of the dump?
Regards,
Jonathan
http://kerblog.com/earlyedition/archive/2004/09/22/176.aspxJonathan,
Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
can check the SQL Server 2000 hotfixes released by Microsoft since then to
see if your case is covered. If not, you can report this to MS by phoning in
an incident to Microsoft PSS.
Ron
--
Ron Talmage
SQL Server MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> When running a particular really badly written query on SQL Server 2000
SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the
dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx|||Thanks, Ron.
Am I going to be charged for reporting this bug?
Regards,
Jonathan
"Ron Talmage" wrote:
> Jonathan,
> Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
> can check the SQL Server 2000 hotfixes released by Microsoft since then to
> see if your case is covered. If not, you can report this to MS by phoning in
> an incident to Microsoft PSS.
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> > When running a particular really badly written query on SQL Server 2000
> SP3a,
> > I'm seeing a dump file generated about every 4 minutes. The top of the
> dump
> > is:
> >
> > * BEGIN STACK DUMP:
> >
> > * 09/19/04 12:40:38 spid 0
> >
> > *
> >
> > * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> > Line
> > 0+00000000)
> >
> > * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> >
> > * Access Violation occurred reading address 00000190
> >
> >
> > Anyone from Microsoft interested in the rest of the dump?
> >
> > Regards,
> >
> > Jonathan
> >
> > http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx
>
>|||Hi, Jonathan
Thanks for your posting!
From your descriptions, I understood that you wonder whether it will be a
free case if you raise this your dump files to us checking it is an known
issue for us. Correct me if I was wrong.
Based on my scope, if this is confirmed to be an known issue and no hotfix
or workaround is available, it will be definitely a free case. If there is
an hotfix or workaround found to resolved this, I am afraid it will not be
a refund. Moreover, just asking for hotfix from an KB will also be free
case.
FYI, if you want to know more detailed information about this, you can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Jonathan,
I am sorry to say that checking dumps file is not supported in the
newsgroup and this kind of issues can be caused by various factors, it is
difficult to locate the root cause in a newsgroup thread. If you want to
find the root cause of that, you will have to turn to PSS for help.
Thank you for your patience and understanding.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Mingqing,
Thanks for your reply! You understand my question well. I didn't see any
matching hotfix, but is it possible/advisable for me to post my dump file
here and have it checked?
Thank you,
Jonathan
""Mingqing Cheng [MSFT]"" wrote:
> Hi, Jonathan
> Thanks for your posting!
> From your descriptions, I understood that you wonder whether it will be a
> free case if you raise this your dump files to us checking it is an known
> issue for us. Correct me if I was wrong.
> Based on my scope, if this is confirmed to be an known issue and no hotfix
> or workaround is available, it will be definitely a free case. If there is
> an hotfix or workaround found to resolved this, I am afraid it will not be
> a refund. Moreover, just asking for hotfix from an KB will also be free
> case.
> FYI, if you want to know more detailed information about this, you can
> contact Microsoft Product Support directly to discuss additional support
> options you may have available, by contacting us at 1-(800)936-5800 or by
> choosing one of the options listed at
> http://support.microsoft.com/default.aspx?scid=sz;en-us;top
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Well, I just found KB article 875445, which is a hotfix that seems to fix my
core dump. Let's see...
-- J
http://kerblog.com/earlyedition/archive/2004/09/27/184.aspx
"Jonathan Levine" wrote:
> When running a particular really badly written query on SQL Server 2000 SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx|||Hi Jonathan,
I would strongly recommend you open a new case with PSS asking for that
htofix. PSS will make sure that hotfix is suitable for your issue. You can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top.
Anyway, asking a hotfix will be a free case. You are welcome to post the
result here let us know whether that hotfix resolved your issue. If not,
you could continue discussing it with PSS to find the root cause.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
""Mingqing Cheng [MSFT]"" wrote:
> I would strongly recommend you open a new case with PSS asking for that
> htofix. PSS will make sure that hotfix is suitable for your issue.
Thank you, I already did so and got the hotfix.
Regards,
Jonathan
BugCheck in SQL Server 2000 SP3a?
I'm seeing a dump file generated about every 4 minutes. The top of the dump
is:
* BEGIN STACK DUMP:
* 09/19/04 12:40:38 spid 0
*
* Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
Line
0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000190
Anyone from Microsoft interested in the rest of the dump?
Regards,
Jonathan
http://kerblog.com/earlyedition/arch...09/22/176.aspx
Jonathan,
Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
can check the SQL Server 2000 hotfixes released by Microsoft since then to
see if your case is covered. If not, you can report this to MS by phoning in
an incident to Microsoft PSS.
Ron
Ron Talmage
SQL Server MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> When running a particular really badly written query on SQL Server 2000
SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the
dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/arch...09/22/176.aspx
|||Thanks, Ron.
Am I going to be charged for reporting this bug?
Regards,
Jonathan
"Ron Talmage" wrote:
> Jonathan,
> Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
> can check the SQL Server 2000 hotfixes released by Microsoft since then to
> see if your case is covered. If not, you can report this to MS by phoning in
> an incident to Microsoft PSS.
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> SP3a,
> dump
>
>
|||Hi, Jonathan
Thanks for your posting!
From your descriptions, I understood that you wonder whether it will be a
free case if you raise this your dump files to us checking it is an known
issue for us. Correct me if I was wrong.
Based on my scope, if this is confirmed to be an known issue and no hotfix
or workaround is available, it will be definitely a free case. If there is
an hotfix or workaround found to resolved this, I am afraid it will not be
a refund. Moreover, just asking for hotfix from an KB will also be free
case.
FYI, if you want to know more detailed information about this, you can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default...d=sz;en-us;top
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Mingqing,
Thanks for your reply! You understand my question well. I didn't see any
matching hotfix, but is it possible/advisable for me to post my dump file
here and have it checked?
Thank you,
Jonathan
""Mingqing Cheng [MSFT]"" wrote:
> Hi, Jonathan
> Thanks for your posting!
> From your descriptions, I understood that you wonder whether it will be a
> free case if you raise this your dump files to us checking it is an known
> issue for us. Correct me if I was wrong.
> Based on my scope, if this is confirmed to be an known issue and no hotfix
> or workaround is available, it will be definitely a free case. If there is
> an hotfix or workaround found to resolved this, I am afraid it will not be
> a refund. Moreover, just asking for hotfix from an KB will also be free
> case.
> FYI, if you want to know more detailed information about this, you can
> contact Microsoft Product Support directly to discuss additional support
> options you may have available, by contacting us at 1-(800)936-5800 or by
> choosing one of the options listed at
> http://support.microsoft.com/default...d=sz;en-us;top
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Jonathan,
I am sorry to say that checking dumps file is not supported in the
newsgroup and this kind of issues can be caused by various factors, it is
difficult to locate the root cause in a newsgroup thread. If you want to
find the root cause of that, you will have to turn to PSS for help.
Thank you for your patience and understanding.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Well, I just found KB article 875445, which is a hotfix that seems to fix my
core dump. Let's see...
-- J
http://kerblog.com/earlyedition/arch...09/27/184.aspx
"Jonathan Levine" wrote:
> When running a particular really badly written query on SQL Server 2000 SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/arch...09/22/176.aspx
|||Hi Jonathan,
I would strongly recommend you open a new case with PSS asking for that
htofix. PSS will make sure that hotfix is suitable for your issue. You can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
Anyway, asking a hotfix will be a free case. You are welcome to post the
result here let us know whether that hotfix resolved your issue. If not,
you could continue discussing it with PSS to find the root cause.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
""Mingqing Cheng [MSFT]"" wrote:
> I would strongly recommend you open a new case with PSS asking for that
> htofix. PSS will make sure that hotfix is suitable for your issue.
Thank you, I already did so and got the hotfix.
Regards,
Jonathan
Tuesday, March 20, 2012
Bug?
The value expression for the query parameter â'@.homebaseâ' refers to a
non-existing report parameter â'homebase_1â'
I made some changes in the dataset (1 of them) and it starts giving me this
error for no reason although the same parameter @.homebase is referred in more
than 5 datasets...
Any help is appreciated!
ThanksI got this before and did this...
1) Copy query in dataset
2) Delete dataset
3) re-create dataset
4) run-query in data view and it will re-build columns.
This is what I always do when I do anything to the columns.
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:Asim@.discussions.microsoft.com:
> Is there a bug in SQL Server Reporting Services which produces an error
> like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
> this
> error for no reason although the same parameter @.homebase is referred in
> more
> than 5 datasets...
> Any help is appreciated!
> Thanks|||Click on the ..., go the parameters tab and remap your query parameter to
the report parameter. Sometimes RS gets confused.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:1C6BF53F-97E6-4678-B76F-66D0BB655A3F@.microsoft.com...
> Is there a bug in SQL Server Reporting Services which produces an error
like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
this
> error for no reason although the same parameter @.homebase is referred in
more
> than 5 datasets...
> Any help is appreciated!
> Thanks
>|||Yep - bug
Lost over 2 days development time re-creating reports
NICE ONE MICROSOFT !
From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Another option could have been to hand edit the rdl file to correct the
problem (this is an XML file).
We have done this once to use an option that the UI didn't offered in the
first release of Reporting Services...
--
Patrice
"om" <om_optical@.hotmail.com> a écrit dans le message de news:
1df92cf4-df9e-4b38-905c-79df77ba241f@.developmentnow.com...
> Yep - bug
> Lost over 2 days development time re-creating reports
> NICE ONE MICROSOFT !
>
> From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
Bug: Query Analyser (german): Cost, percentage, .. in graphical execution plan are wrong
there is an anoying bug in the Query Analyser (german) version 8.00.760:
In the graphical execution plan costs, percentage, ... are wrong!
If I use set showplan_all on, I will get the right values.
I guess it might be the german decimal character!
If I change the country settings to english(usa) everything ist ok.
Couldn't find this on the msdn side.
Is there any bugfix.
markusi think this applies to all europeans versions that
use "," in place of the english "."
it is possible the numerical values are generated by sql
server, and the graphical display by QA, which may be
handling the european convention
-sorry don't know of fix, i mostly work with US settings
>--Original Message--
>Hi to all,
>there is an anoying bug in the Query Analyser (german)
version 8.00.760:
>In the graphical execution plan costs, percentage, ...
are wrong!
>If I use set showplan_all on, I will get the right values.
>I guess it might be the german decimal character!
>If I change the country settings to english(usa)
everything ist ok.
>Couldn't find this on the msdn side.
>Is there any bugfix.
>markus
>
>.
>|||I reported this as a bug back in April but I don't believe there is a fix
available.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Markus Wimplinger" <markus.wimplinger@.cds.co.at> wrote in message
news:%23jZ5F6JmDHA.2488@.TK2MSFTNGP12.phx.gbl...
Hi to all,
there is an anoying bug in the Query Analyser (german) version 8.00.760:
In the graphical execution plan costs, percentage, ... are wrong!
If I use set showplan_all on, I will get the right values.
I guess it might be the german decimal character!
If I change the country settings to english(usa) everything ist ok.
Couldn't find this on the msdn side.
Is there any bugfix.
markus
Bug: default value showing as query based when it is not (report m
The default value is not queried, this is just set to "=1".
When the report is deployed, the parameter in report manager is showing as
'query based' which is wrong.
This is a pain in the ass as when I try linking to this report, I am not
able to set the value to something else.If you use a query to specify a range of parameters then it seems to show
default as 'query based' in the report manager.
What I have discovered is that if you change something to do with the
parameters on the report and redeploy, you can pick up the changes in the
linked report by repointing the linked report at the same report, this forces
some kind of refresh to occur and although very annoying, is easier than
recreating the linked report.
<despair>Thanks to MS for providing a UI of such high quality.</despair>
"adolf garlic" wrote:
> I have a report which uses a query to populate a parameter.
> The default value is not queried, this is just set to "=1".
> When the report is deployed, the parameter in report manager is showing as
> 'query based' which is wrong.
> This is a pain in the ass as when I try linking to this report, I am not
> able to set the value to something else.
bug: data-driven subscription generates deadlock in ReportServer D
underlying query is fairly expensive, so I want to cache the dataset every
morning after I expire yesterday's cache. So I set up a data-driven
subscription to run every morning and execute all the parameter combinations
and deliver it to the Null delivery extension. The problem is that when the
scheduled time rolls around, it tries to run all 50 parameter combinations at
once. (Actually, it looks like it runs 2 or 3 combinations a second without
waiting for the first one to finish.) The query of the data sources run fine,
but I get a deadlock when Reporting Services tries to write the dataset to
the cache. Somewhere between 10% and 50% of the 50 fail every day. The logs
show a standard deadlock error. The error is below.
Is there any config file setting to tell RS to retry writing to the cache a
few times before giving up because of a deadlock? Is this possibly a SP2 fix?
Or is there any way to tell Reporting Services to run the 50 executions
serially instead of concurrently? Or do I need to investigate creating some
custom code to execute and cache the reports on schedule (so I can make it
execute them serially)?
Please let me know if you need more info... or if you need something from
the SQL Server logs. Thanks in advance.
--
ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
132) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Guid
reportId, ReportSnapshot snapshotData, DateTime executionDateTime, DateTime&
expirationDateTime)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
-- End of inner exception stack trace --Hi,
Try setting MaxQueueThreads to 1 in the RSReportServer.config file. You may
want to escalate this to the RS PSS since it looks like a bug to me.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:516B456B-0E41-46AA-B775-D3BA1B13C1F0@.microsoft.com...
> I've got a report that can have about 50 possible parameter combinations.
The
> underlying query is fairly expensive, so I want to cache the dataset every
> morning after I expire yesterday's cache. So I set up a data-driven
> subscription to run every morning and execute all the parameter
combinations
> and deliver it to the Null delivery extension. The problem is that when
the
> scheduled time rolls around, it tries to run all 50 parameter combinations
at
> once. (Actually, it looks like it runs 2 or 3 combinations a second
without
> waiting for the first one to finish.) The query of the data sources run
fine,
> but I get a deadlock when Reporting Services tries to write the dataset to
> the cache. Somewhere between 10% and 50% of the 50 fail every day. The
logs
> show a standard deadlock error. The error is below.
> Is there any config file setting to tell RS to retry writing to the cache
a
> few times before giving up because of a deadlock? Is this possibly a SP2
fix?
> Or is there any way to tell Reporting Services to run the 50 executions
> serially instead of concurrently? Or do I need to investigate creating
some
> custom code to execute and cache the reports on schedule (so I can make it
> execute them serially)?
> Please let me know if you need more info... or if you need something from
> the SQL Server logs. Thanks in advance.
> --
> ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
> 132) was deadlocked on lock resources with another process and has been
> chosen as the deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Gu
id
> reportId, ReportSnapshot snapshotData, DateTime executionDateTime,
DateTime&
> expirationDateTime)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
> -- End of inner exception stack trace --
>|||That did it. Thanks SO much for your response. Now the executions of each
individual parameter combination run serially and don't cause deadlocks.
I did some research on that config file setting you suggested, Teo. Here's
what I found:
MaxQueueThreads
Specifies the maximum number of threads dedicated to polling the event table
in the report server database.
Range: 0 to max integer.
The default is 0.
So if I understand correctly, this setting will ONLY affect the number of
threads used to process events like the fulfilling of subscriptions. Is that
correct? It will not impact performance of multiple users executing and
viewing reports on demand and at the same time, right?|||This is my understanding as well but verify it using the Performance
Monitor.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:C9EF39EE-1638-47E9-AE5F-24896700A73D@.microsoft.com...
> That did it. Thanks SO much for your response. Now the executions of each
> individual parameter combination run serially and don't cause deadlocks.
> I did some research on that config file setting you suggested, Teo. Here's
> what I found:
> MaxQueueThreads
> Specifies the maximum number of threads dedicated to polling the event
table
> in the report server database.
> Range: 0 to max integer.
> The default is 0.
> So if I understand correctly, this setting will ONLY affect the number of
> threads used to process events like the fulfilling of subscriptions. Is
that
> correct? It will not impact performance of multiple users executing and
> viewing reports on demand and at the same time, right?
Monday, March 19, 2012
BUG with Join hints
Query 1
select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, getdate()), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
This query gives an error:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN
Second query is not really what I want but it illustrate that it works fine when getdate() is not used.
Query 2
select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, A.date_enrolled), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
Is there some problem with using function getdate() ? It works fine in SQL Server 2000
This problem occurs on the SQL Server 2005 SP2 ( 9.00.3050.00 (X64) )
and (9.00.2050)
This problem has to do with some subtle differences between the way SQL Server 2000 handles non-deterministic functions and the way SQL Server 2005 handles it. You can get around it by rewriting your query as follows:
Workaround
with CurrentDate(d) AS (select getdate() d)
select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE(A.date_withdrawn, (select d from CurrentDate)), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
Hope that helps.
|||Thanks.The workaround worked. Finally the query behaves the way it is supposed to. Instead of running for hours it finishes in 12 seconds.
I thought that it has something to do with non deterministic nature of the getdate() but still not sure why would it matter in this case. This is the probe of the hash join, it needs to be scanned only once anyway.
Would that be fixed in SP3 or it works like that by design?
|||
I'm glad that worked. We're digging into it further in the query optimizer team to see why exactly the original query failed. I personally doubt that this will change in SP3 (the risk of changing plans in a service pack is too great) but hopefully we will fix this in future releases.
Thanks for the feedback.
Bug with GROUPING keyword?
I have an aggregated query (A) that works fine that uses inner joins.
As soon as the joins are changed to left joins (query B) the following
error occurs:
The grouping aggregate operation cannot take a uniqueidentifier data
type as an argument.
This makes sense b/c a uniqueidentifier is being passed to GROUPING()
but it DOESN'T make sense that I dont get this error from both queries.
Perhaps SQL Server is optimizing it out of the query?
In an attempt to work around this, I've modified all references to the
uniqueidentifier to perform a cast to varchar(36) (see query C). This
seems reasonable given that this same technique allows you to use other
aggregate functions such as count() with uniqueidentifier columns. SQL
Server returns a different error however:
A grouping function argument does not match any of the expressions in
the GROUP BY clause.
I cannot find a reasonable explaination for this error since the
expressions are indeed in the GROUP BY clause.
The 3 queries are below. Please note that they are *generated* by a
report builder application and I'm looking for a generalized fix that
can be made to the sql generator. In general, the queries may have N
number of columns being grouped on so the HAVING statement is a
requirement in order to only include the grand total and details. I've
verified this behavior with SQL 2000 SP3 Standard Edition and Developer
Edition.
Any insights into this behavior and/or suggestions for workarounds are
greatly appreciated!
-- Query A: Works fine
select
(select Code from EnumValue where Id=s.GuidColumn) s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
cast(case when grouping(s.GuidColumn) = 1 then 1 else 0 end as bit)
IsTotalsRow
from
Student s join
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) join
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
s.GuidColumn
with rollup
having
grouping(s.GuidColumn) in (0, 1)
order by
IsTotalsRow, (select Code from EnumValue where Id=s.GuidColumn) asc
-- Query B: Changed to use LEFT JOIN
-- ERROR:
-- Server: Msg 8161, Level 16, State 1
-- A grouping function argument does not match any of the expressions
in the GROUP BY clause.
select
(select Code from EnumValue where Id=s.GuidColumn) s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
cast(case when grouping(s.GuidColumn) = 1 then 1 else 0 end as bit)
IsTotalsRow
from
Student s LEFT JOIN
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) LEFT JOIN
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
s.GuidColumn
with rollup
having
grouping(s.GuidColumn) in (0, 1)
order by
IsTotalsRow, (select Code from EnumValue where Id=s.GuidColumn) asc
-- Query C: Changed to use LEFT JOIN and cast uniqueidentifier to
varchar(36)
-- ERROR:
-- Server: Msg 8161, Level 16, State 1
-- A grouping function argument does not match any of the expressions
in the GROUP BY clause.
select
(select Code from EnumValue where Id=cast(s.GuidColumn as
varchar(36))) s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
cast(case when grouping( cast(s.GuidColumn as varchar(36)) ) = 1 then
1 else 0 end as bit) IsTotalsRow
from
Student s LEFT JOIN
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) LEFT JOIN
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
cast(s.GuidColumn as varchar(36))
with rollup
having
grouping(cast(s.GuidColumn as varchar(36))) in (0, 1)
order by
IsTotalsRow, (select Code from EnumValue where Id=cast(s.GuidColumn as
varchar(36))) asc>> The grouping aggregate operation cannot take a uniqueidentifier data
type as an argument. <<
Since you are using proprietary code, there is no standard or even
reasonable behavior required of them. Without DDL, all we can see is
that you are using the non-relational and expensive uniqueidentifier
data type and have violations of ISO-11179 naming rules.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Here is the DDL for the 3 tables involved in the queries:
CREATE TABLE EnumValue (
ID uniqueidentifier NOT NULL,
DisplayValue varchar (512) NOT NULL,
Code varchar (8) NULL
)
GO
CREATE TABLE Student (
ID uniqueidentifier NOT NULL,
GuidColumn uniqueidentifier NULL
)
GO
CREATE TABLE Test (
ID uniqueidentifier NOT NULL,
StudentID uniqueidentifier NOT NULL,
AdministrationID uniqueidentifier NOT NULL,
ELALevel uniqueidentifier NULL
)
GO
ALTER TABLE EnumValue ADD
CONSTRAINT PK_EnumValue PRIMARY KEY CLUSTERED (ID)
GO
ALTER TABLE Student ADD
CONSTRAINT DF_Student_Id DEFAULT (newid()) FOR ID,
CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (ID)
GO
ALTER TABLE Test ADD
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (ID)
GO
ALTER TABLE Test ADD
CONSTRAINT FK_Test_EnumValue FOREIGN KEY (ELALevel) REFERENCES
EnumValue (ID),
CONSTRAINT FK_Test_Student FOREIGN KEY (StudentID) REFERENCES Student
(ID)
GO
--CELKO-- wrote:
> type as an argument. <<
> Since you are using proprietary code, there is no standard or even
> reasonable behavior required of them. Without DDL, all we can see is
> that you are using the non-relational and expensive uniqueidentifier
> data type and have violations of ISO-11179 naming rules.
>
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>>> Since you are using proprietary code, there is no standard or even
Rubbish, Books online contains the product definition and behaviour
definitions.
Try reading it.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:ejOxnTtFGHA.1676@.TK2MSFTNGP09.phx.gbl...
> type as an argument. <<
> Since you are using proprietary code, there is no standard or even
> reasonable behavior required of them. Without DDL, all we can see is
> that you are using the non-relational and expensive uniqueidentifier
> data type and have violations of ISO-11179 naming rules.
>
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Which release and which version of the proprietary feature? They do
change, often. See BIT, *=, ALL() and so forth. Tony, do you put in
a comment that " this proprietary code worked for Foobar_SQL, release
12.3 and might now work in later or prior releases" like you shoudl in
your code?|||After speaking with Microsoft, one workaround they identified is to
move the CAST operation in query C into a view and then reference the
cast'ed column:
create view Stu (ID, GuidColumn) as
(select ID, cast(GuidColumn as varchar(36)) from Student)
Its a bit odd to me that moving this expression into a view is works
but having it inline doesn't!
Also, on a side note, moving the CAST into a UDF does NOT solve the
problem:
CREATE FUNCTION dbo.GuidToStr(@.id uniqueidentifier)
RETURNS varchar(36)
AS
BEGIN
return cast(@.id as varchar(36))
END|||There is no need, its all documented in books online, and if you had
bothered to check and research it then you would have also have found that
too.
Even the bits that are depreciated are documented so when you do your
upgrade you know to check, if you are bothered, check out the SQL Server
2005 mgiration tool that does all this for you.
Are you trying to tell me that between ansi 92 and 99 and 2003 there are no
depreciated items? Well, wake up - there are. Only, where is that
documented? Are there tools to show you the differences? Where you warned in
ansi 92 that something was going to be depreciated in ansi 2003 - Microsoft
has been telling us for 3 releases that the *= stuff is going.
Its about time you actually started reading product documentation before you
post here.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137016404.040788.127160@.g43g2000cwa.googlegroups.com...
> Which release and which version of the proprietary feature? They do
> change, often. See BIT, *=, ALL() and so forth. Tony, do you put in
> a comment that " this proprietary code worked for Foobar_SQL, release
> 12.3 and might now work in later or prior releases" like you shoudl in
> your code?
>|||The final workaround was to restructure the query to union in the
totals row which removes the GROUPING() operator and WITH ROLLUP from
it. This also prevents you from having to create special views with
the casts in them as described in the prior workaround.
Example:
select
(select Code from EnumValue where Id=s.GuidColumn)
s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
0 IsTotalsRow
from
Student s LEFT JOIN
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) LEFT JOIN
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
s.GuidColumn
UNION ALL
select
null s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
1 IsTotalsRow
order by
IsTotalsRow, (select Code from EnumValue where Id=s.GuidColumn)
asc
Bug report RS with SP1
I have the following query. if I use @.Shipping instead of @.CustomerShipTo as
a parameter name, at the preview stage, I receive @.StartDate not defined
error.
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
To)
GROUP BY ProductType,Product,UnitofMeasure,DDate
Regards,
CemSome more information about the parameter bug.
When I add the parameter manually (Report > Report Parameters), the query
below blows at run time, works perfect at query designer.
The way I get around this problem was to define the parameter in the result
query first. The moment I add it to the function below, it shows up in
report parameters. After configuration works perfect.
/*Main query*/
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
,@.BrandName,@.CanSize)
GROUP BY ProductType,Product,UnitofMeasure,DDate
/*Parameter query*/
SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM fn_CreateCanList()
UNION
SELECT Null , '--Select All--'
Regards,
Cem
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>
> Microsoft gang,
> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
as
> a parameter name, at the preview stage, I receive @.StartDate not defined
> error.
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
>
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> To)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> Regards,
> Cem
>|||Thanks. We'll take a look and see if we can repro here.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Could you send me definitions of your functions fn_CreateBlankSalesCases and
fn_CreateCanList?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Lev,
Below you may find the functions you requested. Thanks.
Regards,
Cem
CREATE FUNCTION fn_CreateBlankSalesCases (
@.StartDate as DateTime,
@.EndDate as DateTime,
@.Broker as VarChar(50),
@.Customer as VarChar(50),
@.CustomerShip as VarChar(50),
@.BrandName as VarChar(50),
@.CanSize as VarChar(50))
RETURNS @.DateTable TABLE (
ProductType VarChar(150),
Product VarChar(150),
TotalCases Int,
UnitofMeasure VarChar(10),
DDate DateTime)
/*Table created in memory*/
AS
BEGIN
DECLARE @.StartTempDate as DateTime
DECLARE @.ProductType as VarChar(150)
DECLARE @.Product as VarChar(150)
DECLARE @.TotalCases as Int
DECLARE @.UnitofMeasure as VarChar(10)
DECLARE @.TDate as DateTime
SET @.StartTempDate = @.StartDate
/* String manupilation for the brandname */
If (@.CanSize IS NULL)
SET @.CanSize = '%'
ELSE
SET @.CanSize = '%' + @.CanSize + '%'
If (@.BrandName IS NULL)
SET @.BrandName = '%'
ELSE
SET @.BrandName = @.BrandName + '%'
DECLARE SalesActualProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesActualProducts
FETCH NEXT FROM
SalesActualProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,@.TotalCases,@.UnitofMeasure,@.TDate)
FETCH NEXT FROM SalesActualProducts INTO @.ProductType, @.Product,
@.TotalCases, @.UnitofMeasure, @.TDate
END
CLOSE SalesActualProducts
DECLARE SalesProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesProducts
FETCH NEXT FROM
SalesProducts INTO @.ProductType, @.Product, @.TotalCases, @.UnitofMeasure,
@.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
WHILE (@.StartDate < @.EndDate)
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,0,@.UnitofMeasure,@.StartDate)
SET @.StartDate = DATEADD(month, 1, @.StartDate)
END
SET @.StartDate = @.StartTempDate
FETCH NEXT FROM SalesProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
END
CLOSE SalesProducts
/*Return Table*/
RETURN
END
CREATE FUNCTION fn_CreateCanList()
RETURNS
@.CanTable TABLE (
CanSize VarChar(150),
CanSizeOld VarChar(150)
)
/*Table created in memory*/
AS
BEGIN
DECLARE @.CanSizeTemp as VarChar(50)
DECLARE CanSizeList CURSOR FOR
SELECT DISTINCT can_size
FROM can_typ1
WHERE can_size IS NOT NULL
/*Start to insert data into the table */
OPEN CanSizeList
FETCH NEXT FROM
CanSizeList INTO @.CanSizeTemp
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF CHARINDEX('/',@.CanSizeTemp) > 0
INSERT INTO @.CanTable
VALUES(RIGHT(RTRIM(@.CanSizeTemp),LEN(@.CanSizeTemp)-CHARINDEX('/',@.CanSizeTem
p)) ,@.CanSizeTemp)
ELSE
INSERT INTO @.CanTable VALUES(@.CanSizeTemp, @.CanSizeTemp)
FETCH NEXT FROM CanSizeList INTO @.CanSizeTemp
END
CLOSE CanSizeList
/*Return Table*/
RETURN
END
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:uQ8Q1hnfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Could you send me definitions of your functions fn_CreateBlankSalesCases
and
> fn_CreateCanList?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> >
> >
> > Some more information about the parameter bug.
> >
> > When I add the parameter manually (Report > Report Parameters), the
query
> > below blows at run time, works perfect at query designer.
> >
> > The way I get around this problem was to define the parameter in the
> > result
> > query first. The moment I add it to the function below, it shows up in
> > report parameters. After configuration works perfect.
> >
> > /*Main query*/
> > SELECT
> > ProductType,Product,
> > SUM(TotalCases) as TotalCases,
> > UnitofMeasure,
> > DatePart(yyyy, DDate) as YDate,
> > DateName(mm,DDate) as MDate
> >
> > FROM
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> > ,@.BrandName,@.CanSize)
> > GROUP BY ProductType,Product,UnitofMeasure,DDate
> >
> > /*Parameter query*/
> > SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> > fn_CreateCanList()
> > UNION
> > SELECT Null , '--Select All--'
> >
> > Regards,
> > Cem
> >
> > "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> > news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
> >>
> >>
> >> Microsoft gang,
> >>
> >> I have the following query. if I use @.Shipping instead of
@.CustomerShipTo
> > as
> >> a parameter name, at the preview stage, I receive @.StartDate not
defined
> >> error.
> >>
> >> SELECT
> >> ProductType,Product,
> >> SUM(TotalCases) as TotalCases,
> >> UnitofMeasure,
> >> DatePart(yyyy, DDate) as YDate,
> >> DateName(mm,DDate) as MDate
> >>
> >> FROM
> >>
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> >> To)
> >> GROUP BY ProductType,Product,UnitofMeasure,DDate
> >>
> >> Regards,
> >> Cem
> >>
> >>
> >
> >
>
BUG or not?
on the generated exec plan. I managed to reproduce it on different servers
and databases.
Heres the code, the trigger should generate new number max(Broj)+1 but the
result here is 1 because join in the selec max() part returns empty result
set.
If you substitute part in the join on n.prefix = i.prefix with n.prefix =
convert(varchar(10), i.prefix) then the trigger works.
Notice that the convert function doesnt actually do anything, its there as
an example, feel free to use any method to force different exec plan.
if exists(select * from sysobjects where xtype='U' and name='tbl')
drop table tbl
go
create table tbl (
[id] int identity(1,1) not null primary key clustered,
prefix varchar(10) not null,
broj int null
)
go
insert tbl (prefix, broj) values( '2006/01-', 54)
insert tbl (prefix, broj) values( '2006/01-', 53)
insert tbl (prefix, broj) values( '2006/01-', 52)
insert tbl (prefix, broj) values( '2006/01-', 51)
insert tbl (prefix, broj) values( '2006/01-', 50)
insert tbl (prefix, broj) values( '2006/01-', 49)
insert tbl (prefix, broj) values( '2006/01-', 48)
insert tbl (prefix, broj) values( '2006/01-', 47)
insert tbl (prefix, broj) values( '2006/01-', 46)
insert tbl (prefix, broj) values( '2006/01-', 45)
insert tbl (prefix, broj) values( '2006/01-', 44)
insert tbl (prefix, broj) values( '2006/01-', 43)
insert tbl (prefix, broj) values( '2006/01-', 42)
insert tbl (prefix, broj) values( '2006/01-', 41)
insert tbl (prefix, broj) values( '2006/01-', 40)
insert tbl (prefix, broj) values( '2006/01-', 39)
insert tbl (prefix, broj) values( '2006/01-', 38)
insert tbl (prefix, broj) values( '2006/01-', 37)
insert tbl (prefix, broj) values( '2006/01-', 36)
go
if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
drop trigger tr_tbl_ins
go
create trigger tr_tbl_ins on tbl
for insert
as
update tbl
set
broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
n.prefix = i.prefix),0)+1
where
[id] = (select [id] from inserted)
go
insert into tbl (prefix)
values ('2006/01-')
select * from tbl order by broj
-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
returns NULL valueForgot to add, its for SQL Server 2005, SQL Server 2000 works as
expected...
Marko
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqJ6IuJVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
>
> if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
> go
> create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
> go
> insert tbl (prefix, broj) values( '2006/01-', 54)
> insert tbl (prefix, broj) values( '2006/01-', 53)
> insert tbl (prefix, broj) values( '2006/01-', 52)
> insert tbl (prefix, broj) values( '2006/01-', 51)
> insert tbl (prefix, broj) values( '2006/01-', 50)
> insert tbl (prefix, broj) values( '2006/01-', 49)
> insert tbl (prefix, broj) values( '2006/01-', 48)
> insert tbl (prefix, broj) values( '2006/01-', 47)
> insert tbl (prefix, broj) values( '2006/01-', 46)
> insert tbl (prefix, broj) values( '2006/01-', 45)
> insert tbl (prefix, broj) values( '2006/01-', 44)
> insert tbl (prefix, broj) values( '2006/01-', 43)
> insert tbl (prefix, broj) values( '2006/01-', 42)
> insert tbl (prefix, broj) values( '2006/01-', 41)
> insert tbl (prefix, broj) values( '2006/01-', 40)
> insert tbl (prefix, broj) values( '2006/01-', 39)
> insert tbl (prefix, broj) values( '2006/01-', 38)
> insert tbl (prefix, broj) values( '2006/01-', 37)
> insert tbl (prefix, broj) values( '2006/01-', 36)
> go
> if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
> drop trigger tr_tbl_ins
> go
> create trigger tr_tbl_ins on tbl
> for insert
> as
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
> n.prefix = i.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> go
>
> insert into tbl (prefix)
> values ('2006/01-')
> select * from tbl order by broj
> -- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
> returns NULL value
>|||It sure looks like a bug to me.
The UPDATE in the trigger can be simplified somewhat. There is no
need to include another instance of tbl in the subquery, it can be
written as a corellated subquery. This version did not have the
problem:
update tbl
set
broj = coalesce((select max(n.broj) from tbl n
where n.prefix = tbl.prefix),0)+1
where
[id] = (select [id] from inserted)
Of course you must already realize that the trigger will provide
duplicate values if more than one row is INSERTED at a time. This is
considered a vulnerability, and I think the trigger should have a test
that enforces that there is only one row in INSERTED.
Roy Harvey
Beacon Falls, CT
On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
>Heres the code, the trigger should generate new number max(Broj)+1 but the
>result here is 1 because join in the selec max() part returns empty result
>set.
>If you substitute part in the join on n.prefix = i.prefix with n.prefix =
>convert(varchar(10), i.prefix) then the trigger works.
>Notice that the convert function doesnt actually do anything, its there as
>an example, feel free to use any method to force different exec plan.
>
>if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
>go
>create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
>go
>insert tbl (prefix, broj) values( '2006/01-', 54)
>insert tbl (prefix, broj) values( '2006/01-', 53)
>insert tbl (prefix, broj) values( '2006/01-', 52)
>insert tbl (prefix, broj) values( '2006/01-', 51)
>insert tbl (prefix, broj) values( '2006/01-', 50)
>insert tbl (prefix, broj) values( '2006/01-', 49)
>insert tbl (prefix, broj) values( '2006/01-', 48)
>insert tbl (prefix, broj) values( '2006/01-', 47)
>insert tbl (prefix, broj) values( '2006/01-', 46)
>insert tbl (prefix, broj) values( '2006/01-', 45)
>insert tbl (prefix, broj) values( '2006/01-', 44)
>insert tbl (prefix, broj) values( '2006/01-', 43)
>insert tbl (prefix, broj) values( '2006/01-', 42)
>insert tbl (prefix, broj) values( '2006/01-', 41)
>insert tbl (prefix, broj) values( '2006/01-', 40)
>insert tbl (prefix, broj) values( '2006/01-', 39)
>insert tbl (prefix, broj) values( '2006/01-', 38)
>insert tbl (prefix, broj) values( '2006/01-', 37)
>insert tbl (prefix, broj) values( '2006/01-', 36)
>go
>if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
>drop trigger tr_tbl_ins
>go
>create trigger tr_tbl_ins on tbl
>for insert
>as
>update tbl
>set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
>n.prefix = i.prefix),0)+1
>where
> [id] = (select [id] from inserted)
>go
>
>insert into tbl (prefix)
>values ('2006/01-')
>select * from tbl order by broj
>-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
>returns NULL value
>|||This is not the real code, it is simplified as musch as possible to show a
point. And, yes, there are ways to fix this but thats not the point. Now,
where should I report this?
MC
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
> It sure looks like a bug to me.
> The UPDATE in the trigger can be simplified somewhat. There is no
> need to include another instance of tbl in the subquery, it can be
> written as a corellated subquery. This version did not have the
> problem:
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n
> where n.prefix = tbl.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> Of course you must already realize that the trigger will provide
> duplicate values if more than one row is INSERTED at a time. This is
> considered a vulnerability, and I think the trigger should have a test
> that enforces that there is only one row in INSERTED.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
> wrote:
>|||I could see that it was a simplified example.
The way I always did this was to open a case with Microsoft. This
cost some $$$ up front, but it was always refunded in the long run
when it proved to be a bug. Haven't had to do that in some years
though.
Roy
On Fri, 31 Mar 2006 14:21:39 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>This is not the real code, it is simplified as musch as possible to show a
>point. And, yes, there are ways to fix this but thats not the point. Now,
>where should I report this?
>
>MC|||> Now,
> where should I report this?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uiyBBvLVGHA.2156@.tk2msftngp13.phx.gb
l...
> This is not the real code, it is simplified as musch as possible to show a
> point. And, yes, there are ways to fix this but thats not the point. Now,
> where should I report this?
>
> MC
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
>|||Maybe you should report a bug:
http://lab.msdn.microsoft.com/produ...ck/Default.aspx
ML
http://milambda.blogspot.com/|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I have a case when the same query returns two different results, depending
> on the generated exec plan. I managed to reproduce it on different servers
> and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
Definitely a bug, and you should report it on
http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
exists there as well.
The bug appears to related to the virtual inserted table. I rewrote
the MAX query in a way which is more palatable to me:
select max(n.broj)
from tbl n
where exists(select * from inserted i where n.prefix = i.prefix)
However, it still returns NULL.
If I do:
SELECT * INTO #inserted FROM tbl
and then use #inserted in the trigger, I get the correct result.
Note: while the use if SELECT INTO is palatable, since you don't
have to copy the table definition in the trigger, my recommendation
is to use a table variable instead (which only should the columns
actually referenced the trigger). I did use SELECT INTO for a while,
but I found that it gave us performance problems.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've reported the bug, however I cant seem to check the status. It seems
that part of the site is down at the moment. If you want to look at it, i
created it under 'mculo'
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9797F2B151D23Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
> Definitely a bug, and you should report it on
> http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
> done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
> exists there as well.
> The bug appears to related to the virtual inserted table. I rewrote
> the MAX query in a way which is more palatable to me:
> select max(n.broj)
> from tbl n
> where exists(select * from inserted i where n.prefix = i.prefix)
> However, it still returns NULL.
> If I do:
> SELECT * INTO #inserted FROM tbl
> and then use #inserted in the trigger, I get the correct result.
> Note: while the use if SELECT INTO is palatable, since you don't
> have to copy the table definition in the trigger, my recommendation
> is to use a table variable instead (which only should the columns
> actually referenced the trigger). I did use SELECT INTO for a while,
> but I found that it gave us performance problems.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I've reported the bug, however I cant seem to check the status. It seems
> that part of the site is down at the moment. If you want to look at it, i
> created it under 'mculo'
Yeah, I also had problems with the site yesterday. I guess it will come back
later today. Keep in mind that it is still only six o'clock in the morning
in Redmond.
But I did see your bug Saturday. As you had failed to attach(*) the repro,
I augmented the bug with the information from the newsgroup.
(*) It's very easy to go wrong, due to the poor inteface on the site. First
browse the file path, and then press Add Attachment. Took me some time to
figure that out the first time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Sunday, March 11, 2012
BUG in SSIS OLE DB Source Build Query
Can anyone help me with this ?
I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :
OLE DB Source Editor Details:
OLE DB Connection Manager - Oracle Source
Data Access Mode - SQL Command
SQL Command Text -
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)
SSIS parses this query succesfully but when i build the query it shows query
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)
Please note :- SYSDATE IN " "
This query returns no Result Set.
Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,i don't find any such function in oracle, getdate() is there in MS SQL Server.
Thanks.
|||
I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))
Always becomes
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))
Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,
There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.
BUG in SSIS OLE DB Source Build Query
Can anyone help me with this ?
I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :
OLE DB Source Editor Details:
OLE DB Connection Manager - Oracle Source
Data Access Mode - SQL Command
SQL Command Text -
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)
SSIS parses this query succesfully but when i build the query it shows query
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)
Please note :- SYSDATE IN " "
This query returns no Result Set.
Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,i don't find any such function in oracle, getdate() is there in MS SQL Server.
Thanks.
|||
I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))
Always becomes
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))
Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,
There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.
BUG in SSIS OLE DB Source Build Query
Can anyone help me with this ?
I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :
OLE DB Source Editor Details:
OLE DB Connection Manager - Oracle Source
Data Access Mode - SQL Command
SQL Command Text -
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)
SSIS parses this query succesfully but when i build the query it shows query
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)
Please note :- SYSDATE IN " "
This query returns no Result Set.
Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,i don't find any such function in oracle, getdate() is there in MS SQL Server.
Thanks.
|||
I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))
Always becomes
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))
Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,
There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.
BUG in SSIS OLE DB Source Build Query
Can anyone help me with this ?
I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :
OLE DB Source Editor Details:
OLE DB Connection Manager - Oracle Source
Data Access Mode - SQL Command
SQL Command Text -
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)
SSIS parses this query succesfully but when i build the query it shows query
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)
Please note :- SYSDATE IN " "
This query returns no Result Set.
Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,i don't find any such function in oracle, getdate() is there in MS SQL Server.
Thanks.
|||
I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))
Always becomes
SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))
Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,
There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.
Bug in SELECT TOP (100) PERCENT with ORDER BY in SQLExpress?
SELECT TOP (100) PERCENT ClientSurname, ClientName
FROM dbo.Client
ORDER BY ClientSurname, ClientName
The query returns always assorted data ignoring the ORDER BY keyword, no matter if the query is invoked directly from Management Studio Express CTP as a View, Table-valued function or called from an Access ADP project. The result is always assorted.
Now an interesting thing is that the syntax below returns always an expected order:
SELECT TOP (99) PERCENT
SELECT TOP 10000
Am I missing something or is it a bug in SQLExpress?I never use the brackets (parenthesis) around the percentage. Maybe your second example is just using the top 10000 (not the 99%) and sorting your recs as you expect.
Keeping in mind that I know nothing of this SQLExpress :)|||TOP is a function, not a predicate as it used to be in 2K.
Thursday, March 8, 2012
Bug in Query Analyzer
SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
You will get the expected results if you choose "Results in text", but
if you choose "Results in grid" you will get nothing in column A and
'1' in column B.
Is there anyone at MS reading this ?
Is there any other place I should report this bug ?
Razvan Socol
I can report this as a bug. It does appear taht the grid does not return
the correct information, or atleast does not interpret it correctly.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
ASCII '1'.
Now you know that 0x01 is the character that QA uses to separate columns
into the grid.
So, the grid has two columns defined by the select, but when populating the
columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
final column of data not to have a home.
So, is this a bug? If Microsoft is willing to think so, that is great, but
I would consider this a behavior instead of a bug.
Russell Fields
"Razvan Socol" <rsocol@.fx.ro> wrote in message
news:60f52b8b.0406090141.7a9f61d7@.posting.google.c om...
> Execute this in Query Analyzer:
> SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
> You will get the expected results if you choose "Results in text", but
> if you choose "Results in grid" you will get nothing in column A and
> '1' in column B.
> Is there anyone at MS reading this ?
> Is there any other place I should report this bug ?
> Razvan Socol
|||I definitely think that it's a bug. I specifically simplified the
query to this form (the situation I encountered was more complex). I
understood all that you explained before I posted, but it's good that
you did explain it, so that other readers understand.
Razvan
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote:
> This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
> ASCII '1'.
> Now you know that 0x01 is the character that QA uses to separate columns
> into the grid.
> So, the grid has two columns defined by the select, but when populating the
> columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
> final column of data not to have a home.
> So, is this a bug? If Microsoft is willing to think so, that is great, but
> I would consider this a behavior instead of a bug.
> Russell Fields
Wednesday, March 7, 2012
bug found in query
i found a strange bug. maybe it's not a bug, but it sure seems like one
to me. these tables are from a database for a cots product called
facility center.
the "bug" occurs down at the very bottom when you run the queries
specified. see my comments down there.
just to explain a little,
spbldinf_code is a building name
spbldrom_code is a room number
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spbldrom__spcharge]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[spcharge] DROP CONSTRAINT spbldrom__spcharge
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spbldrom__code]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[sprmutil] DROP CONSTRAINT spbldrom__code
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spbldrom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[spbldrom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spcharge]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[spcharge]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprmutil]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sprmutil]
GO
CREATE TABLE [dbo].[spbldrom] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[splocation_code] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spbldloc_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[saclass_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[saclstyp_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[description] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[phone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[area] [decimal](12, 4) NULL ,
[notes] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[occ_vac] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[storageyn] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[location_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[subloc_type] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spanlink_num] [int] NULL ,
[haz_mat] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reservable_flag] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[spcharge] (
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spcharge_seq_num] [int] NOT NULL ,
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[area] [decimal](12, 4) NULL ,
[pct] [decimal](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spanlink_num] [int] NULL ,
[owner_sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sprmutil] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[sprmutil_seq_num] [int] NOT NULL ,
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[spprgrm_code] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[spromuse_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[pct] [numeric](12, 4) NULL ,
[area] [numeric](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spromsub_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spanlink_num] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[spbldrom] ADD
CONSTRAINT [DF__spbldrom__area__6FE99F9F] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spbldrom__reserv__18427513] DEFAULT (0) FOR
[reservable_flag],
CONSTRAINT [xpkspbldrom] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spbldrom_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF226spbldrom] ON [dbo].[spbldrom]([spbldinf_code],
[spbldloc_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF228spbldrom] ON [dbo].[spbldrom]([spbldinf_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF229spbldrom] ON [dbo].[spbldrom]([spbldinf_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF433spbldrom] ON [dbo].[spbldrom]([saclass_code],
[saclstyp_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF877spbldrom] ON [dbo].[spbldrom]([splocation_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [DF__spcharge__area__2CBDA3B5] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spcharge__pct__2DB1C7EE] DEFAULT (0) FOR [pct],
CONSTRAINT [xpkspcharge] PRIMARY KEY NONCLUSTERED
(
[sporg_code],
[spcharge_seq_num],
[spbldinf_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF236spcharge] ON [dbo].[spcharge]([sporg_code]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF274spcharge] ON [dbo].[spcharge]([spbldinf_code],
[spbldrom_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF275spcharge] ON [dbo].[spcharge]([spbldinf_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF276spcharge] ON [dbo].[spcharge]([spbldinf_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [XPKsprmutil] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spfloors_code],
[spbldrom_code],
[sprmutil_seq_num]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF1040sprmutil] ON [dbo].[sprmutil]([sporg_code]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [spbldrom__spcharge] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [spbldrom__code] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '1', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '2', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '3', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '4', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '5', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '6', '1')
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '1', 'org', 1)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '2', 'org', 2)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '3', 'org', 3)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '4', 'org', 4)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '1', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '2', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '3', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '4', '1', 1)
go
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
those two queries work properly. rooms 5 and 6 are returned because
they do not exist in the spcharge and/or sprmutil table.
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', NULL, 'org', 10)
set concat_null_yields_null off
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
the two queries correctly return rooms 5 and 6.
set concat_null_yields_null on
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
now the query for spcharge returns nothing. the one row with a null
value in the spcharge.spbldrom_code has prevented the query from
returning anything when it should still return rooms 5 and 6. i realize
that the concat_null_yields_null value is different, but imho it should
still return rooms 5 and 6.
if you run
set concat_null_yields_null on
select spbldinf_code + spbldrom_code from spcharge
set concat_null_yields_null off
select spbldinf_code + spbldrom_code from spcharge
you get record sets returned for both queries. the null value returned
in the top one apparently makes the not in clause in the previous
queries fail to work properly.
is there any way to report this to microsoft without paying for a
support call?> select spbldinf_code, spbldrom_code from spbldrom
> where spbldinf_code + spbldrom_code not in
> (select spbldinf_code + spbldrom_code from spcharge)
> go
> now the query for spcharge returns nothing. the one row with a null
> value in the spcharge.spbldrom_code has prevented the query from
> returning anything when it should still return rooms 5 and 6.
This isn't a bug it's standard SQL behaviour.
Because the subquery
(select spbldinf_code + spbldrom_code from spcharge)
includes a NULL, the predicate NOT IN returns UNKNOWN for every row in the
outer query, therefore nothing is returned. This is logical enough - if a
value in the set is UNKNOWN then you can't know whether any given value is
NOT IN the set.
I think the result you are looking for is this:
SELECT spbldinf_code, spbldrom_code
FROM spbldrom
WHERE spbldinf_code + spbldrom_code
NOT IN
(SELECT spbldinf_code + spbldrom_code
FROM spcharge
WHERE spbldinf_code + spbldrom_code IS NOT NULL)
--
David Portas
SQL Server MVP
--|||David Portas wrote:
> This is logical enough - if a
> value in the set is UNKNOWN then you can't know whether any given value is
> NOT IN the set.
that doesn't make any sense.
set A = 1, 2, 3
set B = 1, 2, NULL
if i'm trying to find all in A that aren't in B, then since
A.3 <> B.1 and
A.3 <> B.2 and
A.3 <> B.NULL
A.3 is not in set B
makes much more sense than saying "i can't figure out whether or not 3 is in
set B" when it's perfectly clear that there is no 3 in set B.|||> A.3 <> B.1 and
> A.3 <> B.2 and
> A.3 <> B.NULL
Yes, that's the logic. But the result of that last comparison is UNKNOWN not
TRUE (anything compared to NULL is UNKNOWN). Therefore the result of the NOT
IN predicate is also UNKNOWN. This is a common "gotcha" in SQL.
--
David Portas
SQL Server MVP
--