This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,City, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label app. Show all posts
Showing posts with label app. Show all posts
Friday, March 30, 2012
query question
This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,Ci
ty, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,Ci
ty, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, March 23, 2012
Query Plan Guides don't seem to work
We have a couple of ad hoc queries in our app that we need to force parameterization. There queries can't be put in sp's because they come from a 3rd party app. The goal is to reduce the amount of cache being accumulated. We are on sql server 2005. The queries in question look like this:
select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'
When i query syscacheobjects i see thousands of compiled plans for that single query with different literal values in the WHERE clause. I want to force parameterization and make it reuse one plan. Seems to best way to do this is to turn FORCED PARAMETERIZATION on for the db or to create plan guides. Both don't seem to be working.
I turned FORCED PARAMETERIZATION on for the db, then cleared proc cache (DBCC FREEPROCCACHE). I query syscacheobjects and it's empty. I run a test script with 10 queries similiar to the one above, passing a different literal into the where clause. I would expect there to be only one compiled plan for the paramitized version of the query but again there are 10 compiled plans for that query.
I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:
DECLARE @.stmt nvarchar(max);
DECLARE @.params nvarchar(max);
EXEC sp_get_query_template
N'select * from TABLE1 where SEQUENCE_NUMBER = ''%''',
@.stmt OUTPUT,
@.params OUTPUT;
EXEC sp_create_plan_guide
N'Templat1',
@.stmt,
N'TEMPLATE',
NULL,
@.params,
N'OPTION(PARAMETERIZATION FORCED)';
I again clear the cache and syscacheobjects is empty. I run my test script. This time i can a row for the plan guide BUT there is still 10 rows for the queries in my test script. I expected to just see the row for the plan guide, indicating that it's using that compiled plan for all the queries but this isn't the case.
Has anyone used query plan guides? Is my testing correct, should i expect LESS rows to accumulate in syscacheobjects? I can query sys.plan_guides and see that my plan guide is created and enabled but from my tests it doesn't seem like it's being used when i run my tests. Any advise on how to get this going?
thanks,
Dave
|||thanks for the response hunchback! I AM seeing an entry in syscacheobjects where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'Did you see any entry where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'?
If yes, then it is working. Check the number for column [usecounts], it should be 10.
If you are not qualifying the table with the schema / owner, then the plan generated will not be share among multiple users. You can check column [uid] and see if it is different from -2.
AMB
Below are the results of:
select cacheobjtype, objtype, usecounts, sql from syscacheobjects
where sql like '%TABLE1%' order by sql
cacheobjtype objtype usecount sql
Compiled Plan Prepared 10 (@.0 varchar(8000))select * from dbo.TABLE1 where SEQUENCE_NUMBER = @.0
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '1'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '10'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '2'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '3'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '4'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '5'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '6'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '7'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '8'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '9'
Any idea why is it compiling a plan for each query even though it's using the Plan Guide? When i run Profiler and check out the ExplainPlan XML i don't see any references to the TemplatePlan which was also making me think that it wasn't using the plan guide.
|||
What about the [usecounts] associated to the ('Compiled Plan', 'Prepared')?
SQL Server has to compile the Adhoc query anyway, in order to get the query tree and be able to separate the constant values that will be pass to the parameters. Based on the resources available and cost of compilation, SQL Server could save or not those compiled plans.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Plan Cache Concepts Explained
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/08/plan-cache-concepts-explained.aspx
AMB
|||I'm running into the same behavior as tenatiousd is reporting.I create the template plan guide and setup tests to watch SQL Server behavior (syscacheobjects). When I execute a SQL statement that matches the plan guide, it seems to be "referencing" the Prepared Plan (refcount goes up), but creates an Ad Hoc compiled plan and (re)uses that instead. Everything else that matters for plan resuse is the same (user id, execution context, qualified table names, etc.), but it doesn't reuse the Prepared Plan defined from the template plan guide. If I change the parameters, it references the Prepared Plan, but compiles a new Ad Hoc plan and continues to reuse the ad hoc plan over and over (as long as the parameters are the same).
The resulting behavior is that the Prepared Plan that matches the SQL statement is not reused and the plan cache would continue to grow with new ad hoc plans being added as the parameter values change.
Why isn't the Prepared Plan being reused? Will the Prepared Plan only be reused if there is enough pressure on the Plan Cache and it can't/won't store the ad hoc plan?
|||
Hi Nickvalo,
What about [usecounts] for the "Prepared" execution plan, is it being incremented?
AMB
|||The first time I execute a SQL statement that has a template plan guide (after issuing a DBCC freeproccache), this is what happens:1. The usecount for the Prepared Plan goes up by one and it's refcount goes up by 2 (or 3).
2. The usecount for the ad hoc plan goes up by one (a new one gets created for each unique set of parameter values).
Then if I execute that same SQL statement (same parameter values) 5 times in a row (without clearing the plan cache), the usecount for the *ad hoc* plan goes up by 5. So, essentially it appears that the prepared plan is used somehow the first time, but so is an ad hoc plan for that same statement ... then only the ad hoc plan is used when the same parameters are used in the SQL statement. My expectation was that there would not be an ad hoc plan prepared and cached if the template plan guide matched the SQL statement and forced parameterization.
|||
Nickvalo,
I which somebody from the Microsoft SQL Server group, in charge of "plan guides", could answer this question.
My guess is that the cost of generating an execution plan for that statement is low and the resources available are high, so putting the plan in the cache can help. See if you can download a stress tool and do the test with more connections (create more strees, consume more, and reduce resources available).
Support Tools Available For Stress Testing & Performance Analysis
http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en
AMB
|||Hello,
maybe this page: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
especially the Appendix A section can help you to find out why there is no parametrisation
regards,
Peter
sqlQuery Plan Guides don't seem to work
We have a couple of ad hoc queries in our app that we need to force parameterization. There queries can't be put in sp's because they come from a 3rd party app. The goal is to reduce the amount of cache being accumulated. We are on sql server 2005. The queries in question look like this:
select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'
When i query syscacheobjects i see thousands of compiled plans for that single query with different literal values in the WHERE clause. I want to force parameterization and make it reuse one plan. Seems to best way to do this is to turn FORCED PARAMETERIZATION on for the db or to create plan guides. Both don't seem to be working.
I turned FORCED PARAMETERIZATION on for the db, then cleared proc cache (DBCC FREEPROCCACHE). I query syscacheobjects and it's empty. I run a test script with 10 queries similiar to the one above, passing a different literal into the where clause. I would expect there to be only one compiled plan for the paramitized version of the query but again there are 10 compiled plans for that query.
I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:
DECLARE @.stmt nvarchar(max);
DECLARE @.params nvarchar(max);
EXEC sp_get_query_template
N'select * from TABLE1 where SEQUENCE_NUMBER = ''%''',
@.stmt OUTPUT,
@.params OUTPUT;
EXEC sp_create_plan_guide
N'Templat1',
@.stmt,
N'TEMPLATE',
NULL,
@.params,
N'OPTION(PARAMETERIZATION FORCED)';
I again clear the cache and syscacheobjects is empty. I run my test script. This time i can a row for the plan guide BUT there is still 10 rows for the queries in my test script. I expected to just see the row for the plan guide, indicating that it's using that compiled plan for all the queries but this isn't the case.
Has anyone used query plan guides? Is my testing correct, should i expect LESS rows to accumulate in syscacheobjects? I can query sys.plan_guides and see that my plan guide is created and enabled but from my tests it doesn't seem like it's being used when i run my tests. Any advise on how to get this going?
thanks,
Dave
|||thanks for the response hunchback! I AM seeing an entry in syscacheobjects where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'Did you see any entry where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'?
If yes, then it is working. Check the number for column [usecounts], it should be 10.
If you are not qualifying the table with the schema / owner, then the plan generated will not be share among multiple users. You can check column [uid] and see if it is different from -2.
AMB
Below are the results of:
select cacheobjtype, objtype, usecounts, sql from syscacheobjects
where sql like '%TABLE1%' order by sql
cacheobjtype objtype usecount sql
Compiled Plan Prepared 10 (@.0 varchar(8000))select * from dbo.TABLE1 where SEQUENCE_NUMBER = @.0
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '1'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '10'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '2'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '3'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '4'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '5'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '6'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '7'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '8'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '9'
Any idea why is it compiling a plan for each query even though it's using the Plan Guide? When i run Profiler and check out the ExplainPlan XML i don't see any references to the TemplatePlan which was also making me think that it wasn't using the plan guide.
|||
What about the [usecounts] associated to the ('Compiled Plan', 'Prepared')?
SQL Server has to compile the Adhoc query anyway, in order to get the query tree and be able to separate the constant values that will be pass to the parameters. Based on the resources available and cost of compilation, SQL Server could save or not those compiled plans.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Plan Cache Concepts Explained
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/08/plan-cache-concepts-explained.aspx
AMB
|||I'm running into the same behavior as tenatiousd is reporting.I create the template plan guide and setup tests to watch SQL Server behavior (syscacheobjects). When I execute a SQL statement that matches the plan guide, it seems to be "referencing" the Prepared Plan (refcount goes up), but creates an Ad Hoc compiled plan and (re)uses that instead. Everything else that matters for plan resuse is the same (user id, execution context, qualified table names, etc.), but it doesn't reuse the Prepared Plan defined from the template plan guide. If I change the parameters, it references the Prepared Plan, but compiles a new Ad Hoc plan and continues to reuse the ad hoc plan over and over (as long as the parameters are the same).
The resulting behavior is that the Prepared Plan that matches the SQL statement is not reused and the plan cache would continue to grow with new ad hoc plans being added as the parameter values change.
Why isn't the Prepared Plan being reused? Will the Prepared Plan only be reused if there is enough pressure on the Plan Cache and it can't/won't store the ad hoc plan?
|||
Hi Nickvalo,
What about [usecounts] for the "Prepared" execution plan, is it being incremented?
AMB
|||The first time I execute a SQL statement that has a template plan guide (after issuing a DBCC freeproccache), this is what happens:1. The usecount for the Prepared Plan goes up by one and it's refcount goes up by 2 (or 3).
2. The usecount for the ad hoc plan goes up by one (a new one gets created for each unique set of parameter values).
Then if I execute that same SQL statement (same parameter values) 5 times in a row (without clearing the plan cache), the usecount for the *ad hoc* plan goes up by 5. So, essentially it appears that the prepared plan is used somehow the first time, but so is an ad hoc plan for that same statement ... then only the ad hoc plan is used when the same parameters are used in the SQL statement. My expectation was that there would not be an ad hoc plan prepared and cached if the template plan guide matched the SQL statement and forced parameterization.
|||
Nickvalo,
I which somebody from the Microsoft SQL Server group, in charge of "plan guides", could answer this question.
My guess is that the cost of generating an execution plan for that statement is low and the resources available are high, so putting the plan in the cache can help. See if you can download a stress tool and do the test with more connections (create more strees, consume more, and reduce resources available).
Support Tools Available For Stress Testing & Performance Analysis
http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en
AMB
|||Hello,
maybe this page: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
especially the Appendix A section can help you to find out why there is no parametrisation
regards,
Peter
Saturday, February 25, 2012
Query notifications and replication
Anyone using query notifications with replication? We have a replicated db
(transactional one way) and I have to turn service broker on for an app that
will use query notifications and I was wondering what effect that will have
on replication.
Thanks in advance.
According to my understanding of NS, apart from the resultant use of system
resources, there isn't really any overlap. There could be an impact if
Notification Services is polling the same tables on the subscriber that are
being replicated to - the usual blocking that would occur with any such
queries.
Paul Ibison
(transactional one way) and I have to turn service broker on for an app that
will use query notifications and I was wondering what effect that will have
on replication.
Thanks in advance.
According to my understanding of NS, apart from the resultant use of system
resources, there isn't really any overlap. There could be an impact if
Notification Services is polling the same tables on the subscriber that are
being replicated to - the usual blocking that would occur with any such
queries.
Paul Ibison
Query Notification question
I am having issues getting a SqlDependency proof of concept app to work. I
took a step back and performed the MSDN lab titled SQL Server and ADO.NET
(LabB), located here:
http://msdn.microsoft.com/vstudio/t...l/default.aspx. It contain
s
an example using SqlDependency to monitor a simple SELECT statement on the
AdventureWorks Person.Contact table. This example works fine (after I add
the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
constructor and destructor, respectively. I have one issue: when I revise
the table involved in the SELECT statement to include a computed field (I
created a FullName column in the Person.Contact table), the SqlDependency's
OnChange event fires repeatedly. The following items show in the
SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source: Statement.
Subscription occurs repeatedly and I'm in an infinite loop.
I can't seem to find a rule that says I cannot use a computed field in a
table invloved in my SQL statement for use with SqlDependency. Can anyone
confirm that computed fields are NOT allowed when desinging a query for use
with query notification?SQL Server BOL has a list of what can't be used in a statement with Query
Notifications. I don't remember computed column, but do remember than all
aggregates other than SUM are forbidden. Don't forget that you must be two
part object names too. If you use an invalid query you should get a
notification with a reason of "invalid query". Have a look at the fields in
your notification when you receive it.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>I am having issues getting a SqlDependency proof of concept app to work. I
> took a step back and performed the MSDN lab titled SQL Server and ADO.NET
> (LabB), located here:
> http://msdn.microsoft.com/vstudio/t...l/default.aspx. It
> contains
> an example using SqlDependency to monitor a simple SELECT statement on the
> AdventureWorks Person.Contact table. This example works fine (after I add
> the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
> constructor and destructor, respectively. I have one issue: when I revise
> the table involved in the SELECT statement to include a computed field (I
> created a FullName column in the Person.Contact table), the
> SqlDependency's
> OnChange event fires repeatedly. The following items show in the
> SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source:
> Statement.
> Subscription occurs repeatedly and I'm in an infinite loop.
> I can't seem to find a rule that says I cannot use a computed field in a
> table invloved in my SQL statement for use with SqlDependency. Can anyone
> confirm that computed fields are NOT allowed when desinging a query for
> use
> with query notification?|||Thanks for the reply. I've looked at the BOL, and I can find no specifc
mention of computed fields being "against the rules." The value of the Info
member in the SqlNotificationEventArgs is Query, which means "A SELECT
statement that cannot be notified or was provided." according to MSDN2. The
value of the Type member is Subscribe, which means "There was a failure to
create a notification subscription. Use the SqlNotificationEventArgs object'
s
SqlNotificationInfo item to determine the cause of the failure." This leads
me to believe the same SELECT statement which works on a table with no
computed field suddenly fails the criteria check after I add the computed
field. I guess at this point I am just looking for confirmation that
computed fields are not allowed if you want to use Query Notifications.
Thanks again,
Chris
"Bob Beauchemin" wrote:
> SQL Server BOL has a list of what can't be used in a statement with Query
> Notifications. I don't remember computed column, but do remember than all
> aggregates other than SUM are forbidden. Don't forget that you must be two
> part object names too. If you use an invalid query you should get a
> notification with a reason of "invalid query". Have a look at the fields i
n
> your notification when you receive it.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>
>|||Hi Chris,
That would just about clinch it as an invalid query for me... I don't
remember computed field being against the rules, but you're being told that
it is. What's the computed field look like exactly?
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
> Thanks for the reply. I've looked at the BOL, and I can find no specifc
> mention of computed fields being "against the rules." The value of the
> Info
> member in the SqlNotificationEventArgs is Query, which means "A SELECT
> statement that cannot be notified or was provided." according to MSDN2.
> The
> value of the Type member is Subscribe, which means "There was a failure to
> create a notification subscription. Use the SqlNotificationEventArgs
> object's
> SqlNotificationInfo item to determine the cause of the failure." This
> leads
> me to believe the same SELECT statement which works on a table with no
> computed field suddenly fails the criteria check after I add the computed
> field. I guess at this point I am just looking for confirmation that
> computed fields are not allowed if you want to use Query Notifications.
> Thanks again,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Bob,
Here's the computed field formula for the FullName column I added to the
Person.Contact table:
(ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
')+isnull([LastName],'')))
I would like to add that the SELECT statement I'm using with the
SqlDependency object is NOT referencing the computed field. The statement i
s
(right out of the MSDN lab):
SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
What bothers me is that the computed field is deterministic. The only
variable changing in the scenario is the addition of this computed field,
which is NOT being referenced in my SELECT statement. If you remove the
computed field, the example again works flawlessly.
Thanks for the replies,
Chris
"Bob Beauchemin" wrote:
> Hi Chris,
> That would just about clinch it as an invalid query for me... I don't
> remember computed field being against the rules, but you're being told tha
t
> it is. What's the computed field look like exactly?
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
>
>|||Yep, at first glance I don't see anything wrong with this. Let me try it out
and experiment with some permutations. It may take a few minutes.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
> Bob,
> Here's the computed field formula for the FullName column I added to the
> Person.Contact table:
> (ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
> ')+isnull([LastName],'')))
> I would like to add that the SELECT statement I'm using with the
> SqlDependency object is NOT referencing the computed field. The statement
> is
> (right out of the MSDN lab):
> SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
> What bothers me is that the computed field is deterministic. The only
> variable changing in the scenario is the addition of this computed field,
> which is NOT being referenced in my SELECT statement. If you remove the
> computed field, the example again works flawlessly.
> Thanks for the replies,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Hi Chris,
I just tried this. Worked fine for me here (don't ya just hate that answer).
I tried with your expression, only thing I added was the alias "as Fullname"
but even without the alias, worked fine. Worked OK, I caught the
notification and processed it. Send me mail (by figuring out email from the
obfuscated version) and I'll send you the code.
What version are you using? I'm using the RTM version.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> Yep, at first glance I don't see anything wrong with this. Let me try it
> out and experiment with some permutations. It may take a few minutes.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
>|||Bob,
If I had a dollar for every time I told QA "it works on my machine" I'd be
wildy rich.
I've read that the connection used to send the SQL statement must adhere to
some standards as well, this might be an issue as I can't think of what else
is different between my setup and yours. I am using the latest and greatest
releases of Visual Studio 2005 and SQL server 2005, no Beta stuff here. I'l
l
let you know how it works.
Thanks for the help,
Chris
"Bob Beauchemin" wrote:
> Hi Chris,
> I just tried this. Worked fine for me here (don't ya just hate that answer
).
> I tried with your expression, only thing I added was the alias "as Fullnam
e"
> but even without the alias, worked fine. Worked OK, I caught the
> notification and processed it. Send me mail (by figuring out email from th
e
> obfuscated version) and I'll send you the code.
> What version are you using? I'm using the RTM version.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
> news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
>
>|||I need some help with a formula for the Formula Field(Computed Column)
In the Table Designer there is a column field called Formula. I have success
fully used this column on a couple of occasions, but I am stuck this time.
I have a column called Quantity (Char) and a column called NN (Char). When Q
uantity = 0 I want NN to = Complete. When Quantity is <> 0 I want NN to =
Incomplete.
My Formula for NN, in the Formula Field, is;
(CASE WHEN [Quantity] = 0 THEN Complete ELSE Incomplete END)
I have also tried ;
IIF([Quantity]="0", "Complete", "Incomplete")
Neither work.
Robert
took a step back and performed the MSDN lab titled SQL Server and ADO.NET
(LabB), located here:
http://msdn.microsoft.com/vstudio/t...l/default.aspx. It contain
s
an example using SqlDependency to monitor a simple SELECT statement on the
AdventureWorks Person.Contact table. This example works fine (after I add
the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
constructor and destructor, respectively. I have one issue: when I revise
the table involved in the SELECT statement to include a computed field (I
created a FullName column in the Person.Contact table), the SqlDependency's
OnChange event fires repeatedly. The following items show in the
SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source: Statement.
Subscription occurs repeatedly and I'm in an infinite loop.
I can't seem to find a rule that says I cannot use a computed field in a
table invloved in my SQL statement for use with SqlDependency. Can anyone
confirm that computed fields are NOT allowed when desinging a query for use
with query notification?SQL Server BOL has a list of what can't be used in a statement with Query
Notifications. I don't remember computed column, but do remember than all
aggregates other than SUM are forbidden. Don't forget that you must be two
part object names too. If you use an invalid query you should get a
notification with a reason of "invalid query". Have a look at the fields in
your notification when you receive it.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>I am having issues getting a SqlDependency proof of concept app to work. I
> took a step back and performed the MSDN lab titled SQL Server and ADO.NET
> (LabB), located here:
> http://msdn.microsoft.com/vstudio/t...l/default.aspx. It
> contains
> an example using SqlDependency to monitor a simple SELECT statement on the
> AdventureWorks Person.Contact table. This example works fine (after I add
> the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
> constructor and destructor, respectively. I have one issue: when I revise
> the table involved in the SELECT statement to include a computed field (I
> created a FullName column in the Person.Contact table), the
> SqlDependency's
> OnChange event fires repeatedly. The following items show in the
> SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source:
> Statement.
> Subscription occurs repeatedly and I'm in an infinite loop.
> I can't seem to find a rule that says I cannot use a computed field in a
> table invloved in my SQL statement for use with SqlDependency. Can anyone
> confirm that computed fields are NOT allowed when desinging a query for
> use
> with query notification?|||Thanks for the reply. I've looked at the BOL, and I can find no specifc
mention of computed fields being "against the rules." The value of the Info
member in the SqlNotificationEventArgs is Query, which means "A SELECT
statement that cannot be notified or was provided." according to MSDN2. The
value of the Type member is Subscribe, which means "There was a failure to
create a notification subscription. Use the SqlNotificationEventArgs object'
s
SqlNotificationInfo item to determine the cause of the failure." This leads
me to believe the same SELECT statement which works on a table with no
computed field suddenly fails the criteria check after I add the computed
field. I guess at this point I am just looking for confirmation that
computed fields are not allowed if you want to use Query Notifications.
Thanks again,
Chris
"Bob Beauchemin" wrote:
> SQL Server BOL has a list of what can't be used in a statement with Query
> Notifications. I don't remember computed column, but do remember than all
> aggregates other than SUM are forbidden. Don't forget that you must be two
> part object names too. If you use an invalid query you should get a
> notification with a reason of "invalid query". Have a look at the fields i
n
> your notification when you receive it.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>
>|||Hi Chris,
That would just about clinch it as an invalid query for me... I don't
remember computed field being against the rules, but you're being told that
it is. What's the computed field look like exactly?
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
> Thanks for the reply. I've looked at the BOL, and I can find no specifc
> mention of computed fields being "against the rules." The value of the
> Info
> member in the SqlNotificationEventArgs is Query, which means "A SELECT
> statement that cannot be notified or was provided." according to MSDN2.
> The
> value of the Type member is Subscribe, which means "There was a failure to
> create a notification subscription. Use the SqlNotificationEventArgs
> object's
> SqlNotificationInfo item to determine the cause of the failure." This
> leads
> me to believe the same SELECT statement which works on a table with no
> computed field suddenly fails the criteria check after I add the computed
> field. I guess at this point I am just looking for confirmation that
> computed fields are not allowed if you want to use Query Notifications.
> Thanks again,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Bob,
Here's the computed field formula for the FullName column I added to the
Person.Contact table:
(ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
')+isnull([LastName],'')))
I would like to add that the SELECT statement I'm using with the
SqlDependency object is NOT referencing the computed field. The statement i
s
(right out of the MSDN lab):
SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
What bothers me is that the computed field is deterministic. The only
variable changing in the scenario is the addition of this computed field,
which is NOT being referenced in my SELECT statement. If you remove the
computed field, the example again works flawlessly.
Thanks for the replies,
Chris
"Bob Beauchemin" wrote:
> Hi Chris,
> That would just about clinch it as an invalid query for me... I don't
> remember computed field being against the rules, but you're being told tha
t
> it is. What's the computed field look like exactly?
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
>
>|||Yep, at first glance I don't see anything wrong with this. Let me try it out
and experiment with some permutations. It may take a few minutes.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
> Bob,
> Here's the computed field formula for the FullName column I added to the
> Person.Contact table:
> (ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
> ')+isnull([LastName],'')))
> I would like to add that the SELECT statement I'm using with the
> SqlDependency object is NOT referencing the computed field. The statement
> is
> (right out of the MSDN lab):
> SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
> What bothers me is that the computed field is deterministic. The only
> variable changing in the scenario is the addition of this computed field,
> which is NOT being referenced in my SELECT statement. If you remove the
> computed field, the example again works flawlessly.
> Thanks for the replies,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Hi Chris,
I just tried this. Worked fine for me here (don't ya just hate that answer).
I tried with your expression, only thing I added was the alias "as Fullname"
but even without the alias, worked fine. Worked OK, I caught the
notification and processed it. Send me mail (by figuring out email from the
obfuscated version) and I'll send you the code.
What version are you using? I'm using the RTM version.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> Yep, at first glance I don't see anything wrong with this. Let me try it
> out and experiment with some permutations. It may take a few minutes.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
>|||Bob,
If I had a dollar for every time I told QA "it works on my machine" I'd be
wildy rich.
I've read that the connection used to send the SQL statement must adhere to
some standards as well, this might be an issue as I can't think of what else
is different between my setup and yours. I am using the latest and greatest
releases of Visual Studio 2005 and SQL server 2005, no Beta stuff here. I'l
l
let you know how it works.
Thanks for the help,
Chris
"Bob Beauchemin" wrote:
> Hi Chris,
> I just tried this. Worked fine for me here (don't ya just hate that answer
).
> I tried with your expression, only thing I added was the alias "as Fullnam
e"
> but even without the alias, worked fine. Worked OK, I caught the
> notification and processed it. Send me mail (by figuring out email from th
e
> obfuscated version) and I'll send you the code.
> What version are you using? I'm using the RTM version.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
> news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
>
>|||I need some help with a formula for the Formula Field(Computed Column)
In the Table Designer there is a column field called Formula. I have success
fully used this column on a couple of occasions, but I am stuck this time.
I have a column called Quantity (Char) and a column called NN (Char). When Q
uantity = 0 I want NN to = Complete. When Quantity is <> 0 I want NN to =
Incomplete.
My Formula for NN, in the Formula Field, is;
(CASE WHEN [Quantity] = 0 THEN Complete ELSE Incomplete END)
I have also tried ;
IIF([Quantity]="0", "Complete", "Incomplete")
Neither work.
Robert
Subscribe to:
Posts (Atom)