Problem with left join

Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksBetter if you post some ddl, sample data and expected result.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>|||Aleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>

problem with install, but can't uninstall

There is a problem with my SQL server desktop installation. The message says
corrupt or did not install properly & that I shoud uninstall & try again. The
problem is that there is no place to uninstall that i can find. What should I
do? I am using win xp pro sp2 and was trying to install it with business
contact manager. It is not listed in the add/remove programs. I can uninstall
Business contact manager, but not the sql desktop engine.
hi,
webweaver6 wrote:
> There is a problem with my SQL server desktop installation. The
> message says corrupt or did not install properly & that I shoud
> uninstall & try again. The problem is that there is no place to
> uninstall that i can find. What should I do? I am using win xp pro
> sp2 and was trying to install it with business contact manager. It is
> not listed in the add/remove programs. I can uninstall Business
> contact manager, but not the sql desktop engine.
to manually uninstall MSDE you can have a look at
http://support.microsoft.com/default...;EN-US;Q290991 ..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That seemed to have worked. The uninstall error message is gone. I have
reinstalled businss contact manager and now within outlook get the error
message of "mapi was unable to load the information service dccmsp.dll" so
this is not yet working. What else can I do?
Thanks.
"Andrea Montanari" wrote:

> hi,
> webweaver6 wrote:
> to manually uninstall MSDE you can have a look at
> http://support.microsoft.com/default...;EN-US;Q290991 ..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
webweaver6 wrote:
> That seemed to have worked. The uninstall error message is gone. I
> have reinstalled businss contact manager and now within outlook get
> the error message of "mapi was unable to load the information service
> dccmsp.dll" so this is not yet working. What else can I do?
mapi error, not SQL error... unfortunately I can not help here :D:D:D
try Outlook ng or the solutions (if any) listed in
http://groups.google.it/groups?hl=it...22&sa=N&tab=wg

thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Problem with install if machine.config username and password are stored in registry

Hi,
For information.
It looks like the SQL Reporting Services install doesn't work properly
if you have followed Microsoft's advice and have stored the server's
.net credentials (in the machine.config <processModel> element) in the
registry using aspnet_setreg. (You would do this to remove the plain
text entries from the machine.config file).
I have tried this several times and each time, the install just reads
the username and password entries as text, and can't deal with the
fact that these entries are actually pointing to the registry for the
credentials.
When you get to the Service Account screen on the install wizard, the
section entitled "The Report Server Web service will run under the
following account:" shows the following:
registy:HKLM\SOFTWARE\...\ASPNET_SETREG,userName
(or whatever the location is to your encrypted machine.config
settings).
The install starts after you've completed all of the screens in the
wizard, but then can't start the ReportServer web service
(unsurprisingly!).
The only way that I can see to get round this is to change the
machine.config processModel section before the SQL Reporting Services
install so that the userName and password entries are present in
plaintext in the file. This isn't great - particularly if your
entries have been stored in the registry because it's impossible to
find out what the password is if you haven't got a note of it (and
maybe only the Security team were allowed to have the password, etc.).
It also means that there's a possibility that .net things will stop
working on your server while you're doing the install (ie. just
because you don't really want to have to change those entries, for
risk of typos, etc.).
I hope these comments are useful.
Regards,
Rich WFor info, the same is true for when you install SQL Reporting Services
SP1 - ie. you have to change the machine.config processModel username
and password attributes so that they are not being read from the
registry.
Regards,
Rich

Problem with dynamic sql statement

Can someone tell me why this is not executing properly. No error
messages, just no rows returned. I need to have an output parameter
and a return value.
Thanks in advance
Julie Barnet
CREATE PROCEDURE dbo.sel_LookupChar
(
@.Lookup_Value NVarChar(30),
@.Lookup_Field NVarChar(30),
@.Lookup_Table NVarChar(30),
@.MyOutput nVarChar(100) OUTPUT
)
AS
Declare @.SqlStr VarChar(1000)
Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
From " + @.Lookup_Table + " Where " + @.Lookup_Field
Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
Exec(@.SqlStr)
return @.@.rowcount
GOUse ' not " for string delimiters.
Also, try PRINT @.SqlStr instead of EXEC, and show us the result.
"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0308270858.4563cc29@.posting.google.com...
> Can someone tell me why this is not executing properly. No error
> messages, just no rows returned. I need to have an output parameter
> and a return value.
> Thanks in advance
> Julie Barnet
> CREATE PROCEDURE dbo.sel_LookupChar
> (
> @.Lookup_Value NVarChar(30),
> @.Lookup_Field NVarChar(30),
> @.Lookup_Table NVarChar(30),
> @.MyOutput nVarChar(100) OUTPUT
> )
> AS
> Declare @.SqlStr VarChar(1000)
> Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
> From " + @.Lookup_Table + " Where " + @.Lookup_Field
> Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
>
> Exec(@.SqlStr)
> return @.@.rowcount
> GO

Problem with db connection

My application works properly with development area (VS 2005) , When I created virtual directory and call using browser (IE 7.0) , error occurs exacly the same below

ERROR:

Cannot open user default database. Login failed.
Login failed for user 'CBOZLAGAN\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'CBOZLAGAN\ASPNET'.

Source Error:

Line 4932: this.Adapter.SelectCommand.Parameters[2].Value = ((int)(PROFIL));Line 4933: neuron.KULLANICI_TANIMLARIDataTable dataTable = new neuron.KULLANICI_TANIMLARIDataTable();Line 4934: this.Adapter.Fill(dataTable);Line 4935: return dataTable;Line 4936: }

Connection String is below

"Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\neuron2007_AJAX\\App_Data\\neuron_Data.MDF;Integrated Security=True;User Instance=True";

How can I solve this problem?

Thanks

Did you grant the ASPNET user account permission for the database?

Jeff

|||

Are you publishing to a remote server and are you publishing a copy of the local database that you are connecting to in that connection string?

Did you grant the correct permissions for Integrated Security?

|||

Yes BRCK231\ASPNET has rights below

- db_datareader

- db_datawriter

But connection is done with user BORINTERNAL\CBOZLAGAN for BRCK231\SQLEXPRESS sqlexpress 2005 server

|||

Is the website set up (via IIS) to accept anonymous connections? If not, please give that a try.

Mark

|||

Did you figure it out yet?

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia