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 isnull. Need to substitute null if a var is null and compare it to null and return

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @.inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off? Thank you

set ansi_nulls off

go

declare

@.inFileName VARCHAR (100),

@.inFileSize INT,

@.Id int,

@.inlanguageid INT,

@.inFileVersion VARCHAR (100),

@.ExeState int

set @.inFileName = 'A0006337.EXE'

set @.inFileSize = 28796

set @.Id= 1

set @.inlanguageid =null

set @.inFileVersion =NULL

set @.ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @.inFileName

AND DR.FileSize =@.inFileSize

AND DR.FileVersion = isnull(@.inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@.inlanguageid,null)

AND DR.[ID]= @.ID

)

go

set ansi_nulls on

well actually you dont need to change the setting

if you're up to something like this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

|||

There is a slight problem with this. If the right side is null, it will evaluate to -1. If the left side is not null, it will evaluate to value stored in the table. It's VERY likely that the value in the table won't be -1. So the condition will be false. But, in actuality, it should be true, correct? Shouldn't it be like this?

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,isnull(DR.FileVersion,-1))

Thank you

|||

with this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

the ending equation would be

and (-1 = -1) which evaluates to true.

meaning null=null

remember that this equation resides in the "where clause" and not on the

select clause. if you want to have it returned you must

place a "case clause" in the select statement to evaluate this

nevertheless this clause must still exist in the where clause

to include the nulls

sql

Problem with INSERT Trigger

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.

Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.

In case I didn't describe it clearly:

Call storedproc1

storedproc1 inserts a record into table1

table1 has an INSERT trigger

expected behavior is: storedproc1 returns value1 and value2

actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2(timothy.alvis@.gmail.com) writes:

Quote:

Originally Posted by

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.
>
Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.
>
In case I didn't describe it clearly:
>
Call storedproc1
>
storedproc1 inserts a record into table1
>
table1 has an INSERT trigger
>
expected behavior is: storedproc1 returns value1 and value2
>
actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2

Returns? This needs some clarification. A stored procedure can return
data in three different ways:
o Result set
o Output parameters
o Return value. as in EXEC @.ret = some_sp

Which do you mean?

Overall, it would help if you posted the code of the procedure, so we know
what you are talking about. Please also include the output when run the
procedure in Query Analyzer.

--
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

Problem with IF

It is a procedure that does the paging on the friends table, the variable @.PageWay is the ordering that the return table have to appear, ASC or DESC.

Why it's accusing error on if clause? "if(@.PageWay= 1)"

createProcedure teste2

@.user_idint,

@.PageIndexint,

@.PageSizeint,

@.PageOrdervarchar(10),

@.PageWaybit

As

Begin

Declare @.FirstRowint,

@.LastRowint,

@.Recordsint,

@.Paginasfloat,

@.Pagesint

Select @.FirstRow=( @.PageIndex- 1)* @.PageSize+ 1,

@.LastRow= @.PageSize+(@.PageIndex- 1)* @.PageSize;

With invitationas

(

Select*,

Row_Number()over(orderby friend_idasc)as RowNumber

from friendswhere [user_id]=(@.user_id)and invited=(1)

)

if(@.PageWay= 1)

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationendasc,

casewhen @.PageOrder='e_mail'then e_mailendasc

End

else

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationenddesc,

casewhen @.PageOrder='e_mail'then e_mailenddesc

End

Set @.Records=(SelectCount(*)as'amigos'From friendswhere [user_id]=(@.user_id)and invited=(1))

Set @.Paginas=(Convert(Float,@.Records)/Convert(Float,@.PageSize))

Set @.Pages=Ceiling(@.Paginas)

return @.Pages

End

Go

Thank you very much.

I think as you must put a semicolon before you start WITH statement, you must put something after.|||

I discovered the error. After you define CTE you must use it in the next statement, otherwise you will got a message error. So I just put the following query before the If statement "Select Count(*) from invitation", and worked just fine.

There is the message when you put a Select query that not use the CTE,

Msg 422, Level 16, State 4, Procedure teste2, Line 30

Common table expression defined but not used.

Thank you very much, for had seemed my post.

Problem with Group Totals and Counts

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

Hi Maria,

Nice to see a well articulated problem for a change

What you describe sounds a bit strange so let me tell you what I would expect to see happening and please correct me if I'm wrong.

You say that you have 234 row coming back, in which there are 23 unique accountID's. By adding a list and grouping it by accountID I would expect to see one of 2 results depending on where the expression resides:

In a textbox inside the list I would expect to see 1. This is because you are groupping by this field

In a textbox outside the list I'd expect to see 23

|||

Adam - thank you so much for responding.

I tried what you suggested and unfortunately it did not work. Let me elaborate on a couple of things - maybe it will help.

-If I remove all of the filters in the group section, then I get all 234 rows displayed. And the total is correct as 234.

-When I add the filter in I get the only 23 rows displayed but the count STAYS as 234. So it's almost as though it does not take the filter into consideration when doing the distinct count.

It is very very strange as it defies all logic for me!

|||

Can you elaborate further please. Can you please post the following:

the Group expression for your list control

the filter applied to the list

the textbox expression that displays the incorrect result

|||

Hi Adam,

(1) The actual group expression for my list control is =Fields!ACCT_UNIT.Value

(2) The filter applied to the list is the following:

=sum(Fields!CMCM.Value) <= =-3.0

(3) The textbox expression for the field that displays the incorrect result:

=CountDistinct(Fields!ACCT_UNIT.Value,"list4")

Thanks!

Maria

|||

Ok I figured something out...

If I add filter to the list - the total changes. But the totals don't change if I add a filter on the group level. I HAVE to add my filter on the group level, because one of them is an aggregate and you cannot have aggregates in your list filters unless it's a group filter.

|||Why not define it at both levels?|||

I thought of that...but I can't because one of my filters must be an aggregate and you cannot have aggregates in data region or data set filters.

I realize there is no other way around this within SRS but is there any way around this programmatically? Code in the report?

Problem with Group Totals and Counts

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

Hi Maria,

Nice to see a well articulated problem for a change

What you describe sounds a bit strange so let me tell you what I would expect to see happening and please correct me if I'm wrong.

You say that you have 234 row coming back, in which there are 23 unique accountID's. By adding a list and grouping it by accountID I would expect to see one of 2 results depending on where the expression resides:

In a textbox inside the list I would expect to see 1. This is because you are groupping by this field

In a textbox outside the list I'd expect to see 23

|||

Adam - thank you so much for responding.

I tried what you suggested and unfortunately it did not work. Let me elaborate on a couple of things - maybe it will help.

-If I remove all of the filters in the group section, then I get all 234 rows displayed. And the total is correct as 234.

-When I add the filter in I get the only 23 rows displayed but the count STAYS as 234. So it's almost as though it does not take the filter into consideration when doing the distinct count.

It is very very strange as it defies all logic for me!

|||

Can you elaborate further please. Can you please post the following:

the Group expression for your list control

the filter applied to the list

the textbox expression that displays the incorrect result

|||

Hi Adam,

(1) The actual group expression for my list control is =Fields!ACCT_UNIT.Value

(2) The filter applied to the list is the following:

=sum(Fields!CMCM.Value) <= =-3.0

(3) The textbox expression for the field that displays the incorrect result:

=CountDistinct(Fields!ACCT_UNIT.Value,"list4")

Thanks!

Maria

|||

Ok I figured something out...

If I add filter to the list - the total changes. But the totals don't change if I add a filter on the group level. I HAVE to add my filter on the group level, because one of them is an aggregate and you cannot have aggregates in your list filters unless it's a group filter.

|||Why not define it at both levels?|||

I thought of that...but I can't because one of my filters must be an aggregate and you cannot have aggregates in data region or data set filters.

I realize there is no other way around this within SRS but is there any way around this programmatically? Code in the report?

problem with GROUP BY and SUM

I'm having a problem with GROUP BY. There are multiple records for each registration.registrationid returned, and I need it to only return one record for each registration.registrationid and to add up all the TotalDollarsUsed for that registration.registrationid.

What ever I do I can't get it to SUM up the TotalDollarsUsed columns for each registrationid.

Does anyone have any ideas?

DECLARE@.SiteID int,
@.StartDate datetime,
@.EndDate datetime

SET @.SiteID = 2216
SET @.StartDate = '9/1/2003'
SET @.EndDate = '3/31/2004'

SELECT registration.registrationid, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI, [Session].Hours,
DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) AS TimeMinutes,
SUM(CASE [Session].Timebased WHEN 1 THEN DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) * (Rate / 60)
ELSE Hours * Rate
END) AS TotalDollarsUsed
FROM Attendance
INNER JOIN Registration ON Attendance.RegistrationID = Registration.RegistrationID
INNER JOIN [Session] ON Attendance.SessionID = [Session].SessionID
GROUP BY Registration.RegistrationID, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI,
[Session].Timebased, [Session].Hours, [Session].Rate, Attendance.AttendanceDate, Attendance.TimeOut, Attendance.TimeIn, Registration.SiteID
HAVING (NOT (Session.Rate IS NULL))
AND (Attendance.AttendanceDate BETWEEN @.startdate AND @.enddate)
AND LinkID IN (SELECT LinkID FROM Registration WHERE withdrawdate IS NULL AND SiteID = @.SiteID)
AND SiteID = @.SiteID
ORDER BY Registration.LinkID

The results returned from this script are like this:

RegistrationID, TotalDollarsUsed
1001, 200
1001, 100
1001, 50
4005, 200
4005, 200Your SELECT statement is returning more than RegistrationID and TotalDollarsUsed columns. Your indicated result shows that for RegistrationID 1001 there are 3 records with unidentical value for a particular column that is not yet clear from your code. Eliminating such a column from the result may be an option. Post your full result to facilitate better resolution to the problem.

Problem with formatting with CR and LF in a string

Hi,

I'm trying to use carriage return (CR) and line feed (LF) to format a string for use with the msdb.dbo.sp_send_dbmail stored procedure. My goal is to have several lines of text delineated with CR LF. However, it appears that SQL Server 2005 is replacing the CR LF with 2 spaces. The snipped below demonstrates this.

declare @.msg nVarChar(100)
set @.msg = 'this is before the cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'and this is after the cr'
select @.msg

If I copy the text returned by the select into a hex editor what I see for the portion of the string "cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'a" is:

63 72 20 20 0B 0C 20 0E 0F 61

I was expecting:

63 72 20 0A 0B 0C 0D 0E 20 61

This is what leads me to believe the CR and LF are being replaced with spaces as it shows hex 20 (SPACE) instead of hex 0A (LF) and hex 0D (CR).

Can someone explain to me how to make this do what I want it to?

Thanks
John

Given this simple test:

CREATE TABLE dbo.CRLF(id int,crlf nvarchar(100))

DECLARE @.msg nVarChar(100)

SET @.msg = 'this is before the cr ' + char(10)+ char(13) +'and this is after the cr'

INSERT INTO dbo.CRLF(id,crlf)VALUES(1, @.msg )

SELECT * FROM dbo.CRLF

I get this if I copy the results from Query Editor:

idcrlf

1this is before the crand this is after the cr

And this ouput put if I Open Table in Object Explorer:

1this is before the cr

and this is after the cr

|||I'm unable to duplicate your results. I get a single line when opening the table in the Object Explorer.

If you add a DECLARE @.LongMsg and set @.LongMsg = @.Msg + @.Msg + @.Msg and then use @.LongMsg as the @.body parameter of the msdb.dbo.sp_send_dbmail your email will be a single long line with no CR LF showing in the text. This is basically what I'm trying to do.

Perhaps there is a server setting somewhere that needs to be tweaked on our server.

John|||

Ya it true but you see 2 box kind of symbol when you opened

the table trough Object explore, select the option “Result to Text” in the

Query panel and select the row then output shows like this.

(1 row(s)

affected)

idcrlf

--

1This is 1 msg

and this is

2 msg

this is 3

msg

(1 row(s)

affected)

|||Ok, after changing the option I can see the CR in the results. Thanks for showing me something I didn't know.

However, I'm still having problems with the CR in the email text. Here's another snippit from the stored procedure:

Declare @.Message varchar(200)
Declare @.MessageList varChar(max)

SET @.MessageList = 'Daily Summary Report' + @.CR
SET @.MessageList = @.MessageList+'New Items'+ @.CR
SET @.MessageList = @.MessageList+'--'+ @.CR
DECLARE c2 CURSOR FOR
select MSG from Email.Messages
where Event_ID = @.EventID
OPEN c2
FETCH NEXT FROM c2
INTO @.Message
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.MessageList = @.MessageList + @.Message + @.CR
FETCH NEXT FROM c2
INTO @.Message
END
CLOSE c2
DEALLOCATE c2

This builds the body text of the email in the variable @.MessageList. It always puts a CR at the end of the line for the SET @.MessageList lines that are outside of the loop.

Inside the loop, if the message type has messages like "Added UPC 123456123456" it works fine with a CR at the end of every line.

If the message type has messages like "FAILED Client1 20050122 Source TYPE 2: 12 COLUMNS SEMI COLON DELIMITED production" it does not put a CR at the end of each line. If I put CR twice at the end of the line I do get two CR's in the email.

I'm stumped. At this point I have put in an if statement to put 2 CR's at the end of the event types that are not gettting the single CR and that seems to work, but I'm one of those types that like to understand why things work or don't work.

Any more thoughts? And thanks for your help.

John|||

Hmmm, I tried deleting a post I just made in this thread, because it seemed no longer relevant after John had made a simultaneous post, and it appears to have deleted John's new post, too.

I think that is a bug in the FORUM software, since I chose only my post when I selected "Delete".

Sorry!

Dan

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.
Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.
|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:
> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
> > When in em, I try to open a table and return all rows, I get a weird error:
> > "The query cannot be executed because some o fthe files are missing or not
> > registered."
> > Also, I cannot connect to the database with a dataadapter in vs (2003)
> > I can, however, execute an SQL in Query Analyzer.
> > I have reinstalled several times. Some of the Google articles suggest that
> > it might be MDAC. I cannot install MDAC either. It says that the operation
> > is not supported by the OS (xp Pro) The components are already installed.
> > Any input would be greatly appreciated.

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error
:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest tha
t
> it might be MDAC. I cannot install MDAC either. It says that the operatio
n
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installatio
n
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
>

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