Field Selection

I'm pretty new to Crystal and I'm stumped at the following issue:

I need to select a sub-set of a dataset. In this case I need to select fields with a status of "Requested", from a list of fields that include "Accepted", "Requested", "Declined". This part is easy, but it gets complicated (for me):

Then I have to use those fields filtered by "Requested" to find, within the last 180 days, if any clients have "Accepted" and/or "Requested" and/or "Declined" (i.e. status = any).

This is a two staged selection process, and I'm fine with the code for the 180 days selection. It almost seems to be a contradiction in that I need to filter by "Requested" in the first intance, and in the second I need to then show returns (for all Status types), but the initial filter means I can not do this.

Another way of looking at it is:

1. I want to select X1 from X(1,2,3,4,5), then
2. I want to use X1 to determine if there have been cases of X(1,2,3,4,5) in the last 6 months.

Any help is muchly appreciated.

Cheers.

Mat.any ideas? I'm desperate.

Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are not
> directly dependent, just somewhat later the log begins growing, but no error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
> > - In the meanwhile it appears that DB log begins growing: the things are
> > not
> > directly dependent, just somewhat later the log begins growing, but no
> > error
> > is found in SQL errorlog.
> > - In which way may the two things (overflow and log grow) be related?
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
> > - What really happens on SQL server when data overflow occurs? How does
it
> > handle?
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field names must be CLS-compliant identifiers.

While creating a report using 'CreateReport' API method for Report Services
2000, I am getting the following error.
//
A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
--> A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
//
The error occurs due to the space on the computed field name. If I
concatinate the words with '_' (for example) then I do not get an error.
I would like know how can I create report that has field with the name like
â'Count Borrower Nameâ', etc?
Thanks.
-SueThe CLS-compliance rule for field names comes from the fact that in vast
majority of scenarios fields in expressions are referenced using the
following syntax: =Fields!FieldName.Value
If a field name is not CLS-compliant (for example "Field Name") then the
syntax above would result in VB compilation error: =Fields!Field Name.Value
is not a valid VB snippet.
RDL does not support field names with spaces. Although if you data source
returns non-CLS-compliant name you designer will build
<Field name="non_CLS_Compliant_Name">
<DataField>non CLS Compliant Name</DataField>
</Field>
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E82BA30A-FD8B-48E9-BF24-28EA9408301D@.microsoft.com...
> While creating a report using 'CreateReport' API method for Report
Services
> 2000, I am getting the following error.
> //
> A field in the data set 'MainDataSet' has the name 'Count Borrower Name'.
> Field names must be CLS-compliant identifiers.
> --> A field in the data set 'MainDataSet' has the name 'Count Borrower
Name'.
> Field names must be CLS-compliant identifiers.
> //
>
> The error occurs due to the space on the computed field name. If I
> concatinate the words with '_' (for example) then I do not get an error.
> I would like know how can I create report that has field with the name
like
> 'Count Borrower Name', etc?
> Thanks.
> -Sue
>

'field name' is not a recognized OPTIMIZER LOCK HINTS option

I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
when running the following query:
select
(SELECT
listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
FROM tablea Emp INNER JOIN Tableb HR
ON Emp.col1= HR.col1)
The function DateList2 is defined as so:
create FUNCTION [dbo].[DateList]
(
@.date1 smalldatetime,
@.date2 smalldatetime,
@.date3 smalldatetime,
@.date4 smalldatetime,
@.date5 smalldatetime
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT @.date1 as ListDate UNION
SELECT @.date2 as ListDate UNION
SELECT @.date3 as ListDate UNION
SELECT @.date4 as ListDate UNION
SELECT @.date5 as ListDate
)
The intent is to grab the highest date form a set of 5 dates that come from
either tablea or tableb and pass it as ChangeDate.
Any assistance would be appreciated.
Thanks,
DaveHi Dave
My guess is that the parser thinks DateList2 is a table, not a function, so
the parens following the name are thought to be a hint.
User defined functions must be qualified with their owner name to help the
parser distinguish them from tables. For example, if the owner of the
function is dbo, you can do this:
select
( SELECT
listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
...
HTH
Kalen Delaney, SQL Server MVP
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
> when running the following query:
> select
> (SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
> The function DateList2 is defined as so:
> create FUNCTION [dbo].[DateList]
> (
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
> RETURNS TABLE
> AS
> RETURN
> (
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
> The intent is to grab the highest date form a set of 5 dates that come
> from
> either tablea or tableb and pass it as ChangeDate.
> Any assistance would be appreciated.
> Thanks,
> Dave|||karen,
Thanks for responding. I did put dbo. and it still didn't work. It is like
the compiler is getting lost.
Dave
"Kalen Delaney" wrote:

> Hi Dave
> My guess is that the parser thinks DateList2 is a table, not a function, s
o
> the parens following the name are thought to be a hint.
> User defined functions must be qualified with their owner name to help th
e
> parser distinguish them from tables. For example, if the owner of the
> function is dbo, you can do this:
> select
> ( SELECT
> listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> ....
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>
>|||One more thing. I even hardcoded dates, '1/1/2006' or passed null and it wor
ks.
"Dave Sundell" wrote:
> karen,
> Thanks for responding. I did put dbo. and it still didn't work. It is like
> the compiler is getting lost.
> Dave
> "Kalen Delaney" wrote:
>|||Dave,
In SQL Server 2000, you cannot pass a column to a table-valued
function. SQL Server 2000 can't handle a correlated table source,
but SQL Server 2005 can, if you use the APPLY operator.
It doesn't look to me as though what you are doing will work
anyway, since dbo.DateList may contain as many as 5 rows, and
if it does contain more than one, the sub-SELECT will raise an
error.
Try something like this if you are using 2000.
select
thisCol, thatCol,
(
select top 1 listdate from (
(select ejDateBeg as listdate union all
select epDateBeg union all select eeDateBeg
union all select euDateMod union all select HRTimeStamp
) as T
order by listdate desc
) as ChangeDate
from tablea as Emp ...
If this doesn't work (and I vaguely recall some quirks in
SQL Server 2000 when things are nested this deeply), please
post the CREATE TABLE statements and sample data sufficient
to reproduce the scenario (http://www.aspfaq.com/etiquette.asp?id=5006)
Steve Kass
Drew University
Dave Sundell wrote:

>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
>when running the following query:
>select
>(SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
>euDateMod, HRTimeStamp) AS ChangeDate
>FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
>The function DateList2 is defined as so:
>create FUNCTION [dbo].[DateList]
>(
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
>RETURNS TABLE
>AS
>RETURN
>(
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
>The intent is to grab the highest date form a set of 5 dates that come from
>either tablea or tableb and pass it as ChangeDate.
>Any assistance would be appreciated.
>Thanks,
>Dave
>|||You cannot pass columns as params for a Table valued UDF.
For fixing it, since you need only one value from that function, you can use
a scalar valued function.
Your query (as you have provided) will however work is SQL Server 2005 as
long as the UDF returns only one row.
Maybe you would want to go through this discussion :)
http://groups.google.co.in/group/mi...4b44dbec88e80d6
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thanks to all (Karen, Steve, OmniBuzz) that responded. You were all a big he
lp.
Dave
"Omnibuzz" wrote:

> You cannot pass columns as params for a Table valued UDF.
> For fixing it, since you need only one value from that function, you can u
se
> a scalar valued function.
> Your query (as you have provided) will however work is SQL Server 2005 as
> long as the UDF returns only one row.
> Maybe you would want to go through this discussion :)
> http://groups.google.co.in/group/mi...4b44dbec88e80d6
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Field Format Expression

I'm attempting to pass format values to a report field from a Db. I've tried
two approaches by placing the following in the format expression:
1.
=iif(Fields!type.Value="p","p",iif(Fields!type.Value=n,"n",iif(Fields!type.Value=n00,"n00",iif(Fields!type.Value=n0,"n0","n00"))))
2.
=Fields!type.Value
The first one always defaults to the false side of the equation and the
second overwrites my values with format values (n, n00, p).
How can make this work? Any help is appreciated...
DavidThe expressions should be entered into the Format property using the Property
sheet. Your data should not be overwritten.
First, you should have double quotes around the type values:
iif(Fields!type.Value="n",...). Secondly, I think you mean "N2" rather than
"N00" (number with two decimal places). Otherwise your first expression
should work if that is the logic you want.
Charles Kangai, MCT, MCDBA
"DJONES" wrote:
> I'm attempting to pass format values to a report field from a Db. I've tried
> two approaches by placing the following in the format expression:
> 1.
> =iif(Fields!type.Value="p","p",iif(Fields!type.Value=n,"n",iif(Fields!type.Value=n00,"n00",iif(Fields!type.Value=n0,"n0","n00"))))
> 2.
> =Fields!type.Value
> The first one always defaults to the false side of the equation and the
> second overwrites my values with format values (n, n00, p).
> How can make this work? Any help is appreciated...
> David|||Thanks for the reply! I've made the change...placing quotes around the value,
but the format still defaults to the falseside of the expression.
=iif(Fields!type.Value="p","p",iif(Fields!type.Value="n","n",iif(Fields!type.Value="n2","n2",iif(Fields!type.Value="n0","n0","n0"))))
any ideas?
"Charles Kangai" wrote:
> The expressions should be entered into the Format property using the Property
> sheet. Your data should not be overwritten.
> First, you should have double quotes around the type values:
> iif(Fields!type.Value="n",...). Secondly, I think you mean "N2" rather than
> "N00" (number with two decimal places). Otherwise your first expression
> should work if that is the logic you want.
> Charles Kangai, MCT, MCDBA
> "DJONES" wrote:
> > I'm attempting to pass format values to a report field from a Db. I've tried
> > two approaches by placing the following in the format expression:
> >
> > 1.
> > =iif(Fields!type.Value="p","p",iif(Fields!type.Value=n,"n",iif(Fields!type.Value=n00,"n00",iif(Fields!type.Value=n0,"n0","n00"))))
> >
> > 2.
> > =Fields!type.Value
> >
> > The first one always defaults to the false side of the equation and the
> > second overwrites my values with format values (n, n00, p).
> >
> > How can make this work? Any help is appreciated...
> >
> > David|||Did you remember that it does a case sensitive comparison? You probably
should be capitalizing your format values. The values used should definitely
be capitalized. At the very least, you should have
=iif(Fields!type.Value="p","P",iif(Fields!type.Value="n","N",iif(Fields!type.Value="n2","N2",iif(Fields!type.Value="n0","N0","N0"))))
You should additionally check the case of the database values to see whether
your comparions are working OK.
Charles Kangai, MCT, MCDBA
Charles Kangai, MCT, MCDBA
"DJONES" wrote:
> Thanks for the reply! I've made the change...placing quotes around the value,
> but the format still defaults to the falseside of the expression.
> =iif(Fields!type.Value="p","p",iif(Fields!type.Value="n","n",iif(Fields!type.Value="n2","n2",iif(Fields!type.Value="n0","n0","n0"))))
> any ideas?
> "Charles Kangai" wrote:
> > The expressions should be entered into the Format property using the Property
> > sheet. Your data should not be overwritten.
> > First, you should have double quotes around the type values:
> > iif(Fields!type.Value="n",...). Secondly, I think you mean "N2" rather than
> > "N00" (number with two decimal places). Otherwise your first expression
> > should work if that is the logic you want.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "DJONES" wrote:
> >
> > > I'm attempting to pass format values to a report field from a Db. I've tried
> > > two approaches by placing the following in the format expression:
> > >
> > > 1.
> > > =iif(Fields!type.Value="p","p",iif(Fields!type.Value=n,"n",iif(Fields!type.Value=n00,"n00",iif(Fields!type.Value=n0,"n0","n00"))))
> > >
> > > 2.
> > > =Fields!type.Value
> > >
> > > The first one always defaults to the false side of the equation and the
> > > second overwrites my values with format values (n, n00, p).
> > >
> > > How can make this work? Any help is appreciated...
> > >
> > > David|||Good point, but still no luck.
Is there a way that I can just insert my Db field value in as the format
expression?
"Charles Kangai" wrote:
> Did you remember that it does a case sensitive comparison? You probably
> should be capitalizing your format values. The values used should definitely
> be capitalized. At the very least, you should have:
> =iif(Fields!type.Value="p","P",iif(Fields!type.Value="n","N",iif(Fields!type.Value="n2","N2",iif(Fields!type.Value="n0","N0","N0"))))
> You should additionally check the case of the database values to see whether
> your comparions are working OK.
> Charles Kangai, MCT, MCDBA
>
> Charles Kangai, MCT, MCDBA
> "DJONES" wrote:
> > Thanks for the reply! I've made the change...placing quotes around the value,
> > but the format still defaults to the falseside of the expression.
> >
> > =iif(Fields!type.Value="p","p",iif(Fields!type.Value="n","n",iif(Fields!type.Value="n2","n2",iif(Fields!type.Value="n0","n0","n0"))))
> >
> > any ideas?
> >
> > "Charles Kangai" wrote:
> >
> > > The expressions should be entered into the Format property using the Property
> > > sheet. Your data should not be overwritten.
> > > First, you should have double quotes around the type values:
> > > iif(Fields!type.Value="n",...). Secondly, I think you mean "N2" rather than
> > > "N00" (number with two decimal places). Otherwise your first expression
> > > should work if that is the logic you want.
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > > "DJONES" wrote:
> > >
> > > > I'm attempting to pass format values to a report field from a Db. I've tried
> > > > two approaches by placing the following in the format expression:
> > > >
> > > > 1.
> > > > =iif(Fields!type.Value="p","p",iif(Fields!type.Value=n,"n",iif(Fields!type.Value=n00,"n00",iif(Fields!type.Value=n0,"n0","n00"))))
> > > >
> > > > 2.
> > > > =Fields!type.Value
> > > >
> > > > The first one always defaults to the false side of the equation and the
> > > > second overwrites my values with format values (n, n00, p).
> > > >
> > > > How can make this work? Any help is appreciated...
> > > >
> > > > David|||For me, it just works doing =Fields!Type.Value. However, in my table the
formats are in capital letters.
Charles Kangai, MCT, MCDBA
"DJONES" wrote:
> Good point, but still no luck.
> Is there a way that I can just insert my Db field value in as the format
> expression?
> "Charles Kangai" wrote:
> > Did you remember that it does a case sensitive comparison? You probably
> > should be capitalizing your format values. The values used should definitely
> > be capitalized. At the very least, you should have:
> > =iif(Fields!type.Value="p","P",iif(Fields!type.Value="n","N",iif(Fields!type.Value="n2","N2",iif(Fields!type.Value="n0","N0","N0"))))
> >
> > You should additionally check the case of the database values to see whether
> > your comparions are working OK.
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "DJONES" wrote:
> >
> > > Thanks for the reply! I've made the change...placing quotes around the value,
> > > but the format still defaults to the falseside of the expression.
> > >
> > > =iif(Fields!type.Value="p","p",iif(Fields!type.Value="n","n",iif(Fields!type.Value="n2","n2",iif(Fields!type.Value="n0","n0","n0"))))
> > >
> > > any ideas?
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > The expressions should be entered into the Format property using the Property
> > > > sheet. Your data should not be overwritten.
> > > > First, you should have double quotes around the type values:
> > > > iif(Fields!type.Value="n",...). Secondly, I think you mean "N2" rather than
> > > > "N00" (number with two decimal places). Otherwise your first expression
> > > > should work if that is the logic you want.
> > > >
> > > > Charles Kangai, MCT, MCDBA
> > > >
> > > > "DJONES" wrote:
> > > >
> > > > > I'm attempting to pass format values to a report field from a Db. I've tried
> > > > > two approaches by placing the following in the format expression:
> > > > >
> > > > > 1.
> > > > > =iif(Fields!type.Value="p","p",iif(Fields!type.Value=n,"n",iif(Fields!type.Value=n00,"n00",iif(Fields!type.Value=n0,"n0","n00"))))
> > > > >
> > > > > 2.
> > > > > =Fields!type.Value
> > > > >
> > > > > The first one always defaults to the false side of the equation and the
> > > > > second overwrites my values with format values (n, n00, p).
> > > > >
> > > > > How can make this work? Any help is appreciated...
> > > > >
> > > > > David|||Thanks for helping me through this! It finally worked!
It turned out to be a field length issue on the the SQL side. A TRIM
function made it work. Ultimately, I went back to the SQL table and corrected
the field length.
David
"Charles Kangai" wrote:
> For me, it just works doing =Fields!Type.Value. However, in my table the
> formats are in capital letters.
> Charles Kangai, MCT, MCDBA
> "DJONES" wrote:
> > Good point, but still no luck.
> >
> > Is there a way that I can just insert my Db field value in as the format
> > expression?
> >
> > "Charles Kangai" wrote:
> >
> > > Did you remember that it does a case sensitive comparison? You probably
> > > should be capitalizing your format values. The values used should definitely
> > > be capitalized. At the very least, you should have:
> > > =iif(Fields!type.Value="p","P",iif(Fields!type.Value="n","N",iif(Fields!type.Value="n2","N2",iif(Fields!type.Value="n0","N0","N0"))))
> > >
> > > You should additionally check the case of the database values to see whether
> > > your comparions are working OK.
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > > "DJONES" wrote:
> > >
> > > > Thanks for the reply! I've made the change...placing quotes around the value,
> > > > but the format still defaults to the falseside of the expression.
> > > >
> > > > =iif(Fields!type.Value="p","p",iif(Fields!type.Value="n","n",iif(Fields!type.Value="n2","n2",iif(Fields!type.Value="n0","n0","n0"))))
> > > >
> > > > any ideas?
> > > >
> > > > "Charles Kangai" wrote:
> > > >
> > > > > The expressions should be entered into the Format property using the Property
> > > > > sheet. Your data should not be overwritten.
> > > > > First, you should have double quotes around the type values:
> > > > > iif(Fields!type.Value="n",...). Secondly, I think you mean "N2" rather than
> > > > > "N00" (number with two decimal places). Otherwise your first expression
> > > > > should work if that is the logic you want.
> > > > >
> > > > > Charles Kangai, MCT, MCDBA
> > > > >
> > > > > "DJONES" wrote:
> > > > >
> > > > > > I'm attempting to pass format values to a report field from a Db. I've tried
> > > > > > two approaches by placing the following in the format expression:
> > > > > >
> > > > > > 1.
> > > > > > =iif(Fields!type.Value="p","p",iif(Fields!type.Value=n,"n",iif(Fields!type.Value=n00,"n00",iif(Fields!type.Value=n0,"n0","n00"))))
> > > > > >
> > > > > > 2.
> > > > > > =Fields!type.Value
> > > > > >
> > > > > > The first one always defaults to the false side of the equation and the
> > > > > > second overwrites my values with format values (n, n00, p).
> > > > > >
> > > > > > How can make this work? Any help is appreciated...
> > > > > >
> > > > > > David

Field Exists?

I'm looking for the best way (and most efficient) to check to see if a field
exists.
I have the following, but am not sure if it's the best way.
IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
AND Name='Fieldname')
Is there a better way?
Also curious if there's a syntax for doing it that works with more than just
SQL Server (possibly MySQL and/or Oracle)?Generic != most efficient.
The query you posted seems OK. Note that if you have a view named tablename
with a column named
Fieldname, you would get a hit. Same if you have a stored procedure named ta
blename with a parameter
named Fieldname. So consider filtering on xtype column as well.
If you want generic, use the INFORMATION_SCHEMA.COLUMNS view. This is ANSI S
QL standard, if the
other RDBMS follows the standard, the same query should work. This is probab
ly less efficient, so
check the view definition (it is in master for SQL Server 2000) and see what
it looks like.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:471B3ADA-139D-4FA8-919C-7DE096F8DDF2@.microsoft.com...
> I'm looking for the best way (and most efficient) to check to see if a fie
ld
> exists.
> I have the following, but am not sure if it's the best way.
> IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
> AND Name='Fieldname')
> Is there a better way?
> Also curious if there's a syntax for doing it that works with more than ju
st
> SQL Server (possibly MySQL and/or Oracle)?
>|||Another option is to use the metadata functions like COL_LENGTH or
COLUMNPROPERTY. Use any argument and if it returns NULL then you can
conclude the column does not exist. See SQL Server Books Online for the
various arguments you can use for these functions.
Anith|||"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:471B3ADA-139D-4FA8-919C-7DE096F8DDF2@.microsoft.com...
> I'm looking for the best way (and most efficient) to check to see if a
> field
> exists.
> I have the following, but am not sure if it's the best way.
> IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
> AND Name='Fieldname')
> Is there a better way?
> Also curious if there's a syntax for doing it that works with more than
> just
> SQL Server (possibly MySQL and/or Oracle)?
>
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.TABLE_NAME = 'tablename'
AND c.COLUMN_NAME = 'columnname'
AND t.TABLE_TYPE = 'BASE TABLE'
You can use this query. It will ensure that you don't have a conflict with
a view as well.
Rick Sawtell
MCT, MCSD, MCDBA

Field Error

I am getting the following error:
The group expression for the list â'Titleâ' refers to the field â'Title.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope.
When I reference the stored procedure in my report, I do not get a field
list. I believe it is because I am trying to build a report that references a
stored procedure using dynamic sql, and I am told that it has to be dynamic
sql by the DBA.
If I am correct in this, question I have is how do I build a report
referencing dynamic sql without getting this error? The stored procedure
works and returns a resultset.You need to manually add the fields to your field list. You can do this by
right clicking with your mouse over the field list.
"Wannabe" wrote:
> I am getting the following error:
> The group expression for the list â'Titleâ' refers to the field â'Title.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope.
> When I reference the stored procedure in my report, I do not get a field
> list. I believe it is because I am trying to build a report that references a
> stored procedure using dynamic sql, and I am told that it has to be dynamic
> sql by the DBA.
> If I am correct in this, question I have is how do I build a report
> referencing dynamic sql without getting this error? The stored procedure
> works and returns a resultset.
>|||That was easy!!! Thanks a lot...I never would have found that.
"Mike Collins" wrote:
> You need to manually add the fields to your field list. You can do this by
> right clicking with your mouse over the field list.
> "Wannabe" wrote:
> > I am getting the following error:
> >
> > The group expression for the list â'Titleâ' refers to the field â'Title.
> > Report item expressions can only refer to fields within the current data set
> > scope or, if inside an aggregate, the specified data set scope.
> >
> > When I reference the stored procedure in my report, I do not get a field
> > list. I believe it is because I am trying to build a report that references a
> > stored procedure using dynamic sql, and I am told that it has to be dynamic
> > sql by the DBA.
> >
> > If I am correct in this, question I have is how do I build a report
> > referencing dynamic sql without getting this error? The stored procedure
> > works and returns a resultset.
> >

Few Question Regarding C#

Hi All
I have following questions regarding C# Assembly and Threading.
Let me know the precise answer or lead me to the proper materials.
1. Is memory leakeage possible in .Net Manager Code ?
2. Is memory leakage possible in .Net Unmanaged Code ?
3. How can I find the what % of memory is being used by DLL at run time ?
4. What is difference between Sunchronous processing and Async
processing in .Net ? How can I achieve it ?
5. Can any one lead me towards Multithreading GUI development in Winforms ?
6. Difference between Delegate and Event ?
7. Is there any specific Design Patterns specifically for WinForms ?
Awaiting reply
Thanks
Silent Ocean
hi....
wrong ng?
:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Silent Ocean wrote:
> Hi All
> I have following questions regarding C# Assembly and Threading.
> Let me know the precise answer or lead me to the proper materials.
> 1. Is memory leakeage possible in .Net Manager Code ?
> 2. Is memory leakage possible in .Net Unmanaged Code ?
> 3. How can I find the what % of memory is being used by DLL at run
> time ? 4. What is difference between Sunchronous processing and Async
> processing in .Net ? How can I achieve it ?
> 5. Can any one lead me towards Multithreading GUI development in
> Winforms ? 6. Difference between Delegate and Event ?
> 7. Is there any specific Design Patterns specifically for WinForms ?
> Awaiting reply
> Thanks
> Silent Ocean

Few Question regarding C#

Hi All
I have following questions regarding C# Assembly and Threading.
Let me know the precise answer or lead me to the proper materials.
1. Is memory leakeage possible in .Net Manager Code ?
2. Is memory leakage possible in .Net Unmanaged Code ?
3. How can I find the what % of memory is being used by DLL at run time ?
4. What is difference between Sunchronous processing and Async
processing in .Net ? How can I achieve it ?
5. Can any one lead me towards Multithreading GUI development in Winforms ?
6. Difference between Delegate and Event ?
7. Is there any specific Design Patterns specifically for WinForms ?
Awaiting reply
Thanks
Silent Ocean
A C# group would probably be a better place to post this
question. Try the following group:
microsoft.public.dotnet.languages.csharp
-Sue
On Tue, 26 Jul 2005 23:01:01 -0500, Silent Ocean
<silentocean555@.yahoo.com> wrote:

>Hi All
>I have following questions regarding C# Assembly and Threading.
>Let me know the precise answer or lead me to the proper materials.
>1. Is memory leakeage possible in .Net Manager Code ?
>2. Is memory leakage possible in .Net Unmanaged Code ?
>3. How can I find the what % of memory is being used by DLL at run time ?
>4. What is difference between Sunchronous processing and Async
>processing in .Net ? How can I achieve it ?
>5. Can any one lead me towards Multithreading GUI development in Winforms ?
>6. Difference between Delegate and Event ?
>7. Is there any specific Design Patterns specifically for WinForms ?
>Awaiting reply
>Thanks
>Silent Ocean
sql

Few Question regarding C#

Hi All
I have following questions regarding C# Assembly and Threading.
Let me know the precise answer or lead me to the proper materials.
1. Is memory leakeage possible in .Net Manager Code ?
2. Is memory leakage possible in .Net Unmanaged Code ?
3. How can I find the what % of memory is being used by DLL at run time ?
4. What is difference between Sunchronous processing and Async
processing in .Net ? How can I achieve it ?
5. Can any one lead me towards Multithreading GUI development in Winforms ?
6. Difference between Delegate and Event ?
7. Is there any specific Design Patterns specifically for WinForms ?
Awaiting reply
Thanks
Silent OceanA C# group would probably be a better place to post this
question. Try the following group:
microsoft.public.dotnet.languages.csharp
-Sue
On Tue, 26 Jul 2005 23:01:01 -0500, Silent Ocean
<silentocean555@.yahoo.com> wrote:

>Hi All
>I have following questions regarding C# Assembly and Threading.
>Let me know the precise answer or lead me to the proper materials.
>1. Is memory leakeage possible in .Net Manager Code ?
>2. Is memory leakage possible in .Net Unmanaged Code ?
>3. How can I find the what % of memory is being used by DLL at run time ?
>4. What is difference between Sunchronous processing and Async
>processing in .Net ? How can I achieve it ?
>5. Can any one lead me towards Multithreading GUI development in Winforms ?
>6. Difference between Delegate and Event ?
>7. Is there any specific Design Patterns specifically for WinForms ?
>Awaiting reply
>Thanks
>Silent Ocean

Few question in C#

Hi All
I have following questions regarding C# Assembly and Threading.
Let me know the precise answer or lead me to the proper materials.
1. Is memory leakeage possible in .Net Manager Code ?
2. Is memory leakage possible in .Net Unmanaged Code ?
3. How can I find the what % of memory is being used by DLL at run time ?
4. What is difference between Sunchronous processing and Async
processing in .Net ? How can I achieve it ?
5. Can any one lead me towards Multithreading GUI development in Winforms ?
6. Difference between Delegate and Event ?
7. Is there any specific Design Patterns specifically for WinForms ?
Awaiting reply
Thanks
Silent Ocean
Hi
You may want to ask this in one of the microsoft.public.donet.* groups and
not here in the SQL Server one.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Silent Ocean" <silentocean555@.yahoo.com> wrote in message
news:Ong9REmkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have following questions regarding C# Assembly and Threading.
> Let me know the precise answer or lead me to the proper materials.
> 1. Is memory leakeage possible in .Net Manager Code ?
> 2. Is memory leakage possible in .Net Unmanaged Code ?
> 3. How can I find the what % of memory is being used by DLL at run time ?
> 4. What is difference between Sunchronous processing and Async processing
> in .Net ? How can I achieve it ?
> 5. Can any one lead me towards Multithreading GUI development in Winforms
> ?
> 6. Difference between Delegate and Event ?
> 7. Is there any specific Design Patterns specifically for WinForms ?
> Awaiting reply
> Thanks
> Silent Ocean

Fetching duplicate rows uisng IN clause

I have two rows in table1 and these two rows are then duplicated in
table2 making four rows. I then try and use the following query to
select the four rows from table2.
select * from Product P
where P.ProductVersionID in (select ProductVersionID from PartSet where
SetID = ?)
Running the above query only return the two rows from Product instead
of the four rows that exist in SetPart.
Now if I run the query below I get back the four rows that I want.
select * from Product P
inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
SP.SetID = ?
Can someone please explain to me why the query with the IN clause
doesn't return the four rows that I want?
DML:
CREATE TABLE SetPart (
SetID int NOT NULL,
ProductVersionID int NOT NULL,
ProductTypeID int NOT NULL
)
GO
CREATE TABLE Product (
ProductVersionID int NOT NULL,
ProductName varchar (20) NOT NULL
)
GOmohaaron@.gmail.com wrote:
> I have two rows in table1 and these two rows are then duplicated in
> table2 making four rows. I then try and use the following query to
> select the four rows from table2.
> select * from Product P
> where P.ProductVersionID in (select ProductVersionID from PartSet where
> SetID = ?)
> Running the above query only return the two rows from Product instead
> of the four rows that exist in SetPart.
> Now if I run the query below I get back the four rows that I want.
> select * from Product P
> inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
> SP.SetID = ?
> Can someone please explain to me why the query with the IN clause
> doesn't return the four rows that I want?
Because IN is not a JOIN. IN just determines whether a row (or rows)
exists in the subquery. Apparently you want the join instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Can someone please explain to me why the query with the IN clause doesn't
IN() implies only the distinct rows in a subquery result. It is different
from an INNER JOIN which returns all rows based on the column used in the
join. If the columns participating in the INNER JOIN are not unique
duplicates can occur in the result.
In general, keyless tables are practically useless and it is recommended
that every table must have a column or set of columns that can uniquely
identify a row in the table.
Anith

Feedback on CHANGE TRACKING with SCHEDULE

Hi,
I am searching concrete feedback on SQL Server 2000 SP3 Server
that have in place FTS catalog population with the following options:
- CHANGE TRACKING
- SCHEDULE
Many thanks.
Change tracking with update index in background will continually poll the tables which you are full text indexing and index changes.
Some business requirements require that you update the index at discrete intervals; ie once a minute, hour, day. There are normally two reasons for this 1) performance, 2) recovery.
You may get better performance on heavily updated tables by scheduling the indexing of the changes. For recovery, sometimes you need to synchronize your catalog updates with backups, transaction log dumps etc. There have also been problems with clustering and change tracking, where you failover to a new node, and suddenly your catalog is read only - ie subsequent updates are not processed. With update index at scheduled intervals this problem can be avoided.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jean-Marc Pugin" <jmpugin@.mobileworkers.com> wrote in message news:Owl82rc3EHA.2804@.TK2MSFTNGP15.phx.gbl...
Hi,
I am searching concrete feedback on SQL Server 2000 SP3 Server
that have in place FTS catalog population with the following options:
- CHANGE TRACKING
- SCHEDULE
Many thanks.
|||Hi,
I have seen that during schedule definition you can define the starting
hour.
Is it possible to define a Time Interval during which catalog update
will occur i.e. only from 2 to 3 AM.
It is because on a Production Server those heavy computations are better
achieved during batch windows.
Thanks
_____
From: Hilary Cotter [mailto:hilary.cotter@.gmail.com]
Posted At: jeudi 9 dcembre 2004 13:46
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Feedback on CHANGE TRACKING with SCHEDULE
Subject: Re: Feedback on CHANGE TRACKING with SCHEDULE
Change tracking with update index in background will continually poll
the tables which you are full text indexing and index changes.
Some business requirements require that you update the index at discrete
intervals; ie once a minute, hour, day. There are normally two reasons
for this 1) performance, 2) recovery.
You may get better performance on heavily updated tables by scheduling
the indexing of the changes. For recovery, sometimes you need to
synchronize your catalog updates with backups, transaction log dumps
etc. There have also been problems with clustering and change tracking,
where you failover to a new node, and suddenly your catalog is read only
- ie subsequent updates are not processed. With update index at
scheduled intervals this problem can be avoided.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jean-Marc Pugin" <jmpugin@.mobileworkers.com> wrote in message
news:Owl82rc3EHA.2804@.TK2MSFTNGP15.phx.gbl...
Hi,
I am searching concrete feedback on SQL Server 2000 SP3 Server
that have in place FTS catalog population with the following options:
- CHANGE TRACKING
- SCHEDULE
Many thanks.
|||It is. Right click on your table, select Full Text Index Table, select change tracking, then right click on your table again, and Full Text Index Table, and schedules. Select New Table Schedule, Update Index, Reoccuring, occurs every 1 hour, and then fill in 2 for the starting time and 3 for the ending time.
This will do a single update index of all the changes tracked for the entire day.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jean-Marc Pugin" <jmpugin@.mobileworkers.com> wrote in message news:eMOncPf3EHA.1396@.tk2msftngp13.phx.gbl...
Hi,
I have seen that during schedule definition you can define the starting hour.
Is it possible to define a Time Interval during which catalog update will occur i.e. only from 2 to 3 AM.
It is because on a Production Server those heavy computations are better achieved during batch windows.
Thanks

From: Hilary Cotter [mailto:hilary.cotter@.gmail.com]
Posted At: jeudi 9 dcembre 2004 13:46
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Feedback on CHANGE TRACKING with SCHEDULE
Subject: Re: Feedback on CHANGE TRACKING with SCHEDULE
Change tracking with update index in background will continually poll the tables which you are full text indexing and index changes.
Some business requirements require that you update the index at discrete intervals; ie once a minute, hour, day. There are normally two reasons for this 1) performance, 2) recovery.
You may get better performance on heavily updated tables by scheduling the indexing of the changes. For recovery, sometimes you need to synchronize your catalog updates with backups, transaction log dumps etc. There have also been problems with clustering and change tracking, where you failover to a new node, and suddenly your catalog is read only - ie subsequent updates are not processed. With update index at scheduled intervals this problem can be avoided.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jean-Marc Pugin" <jmpugin@.mobileworkers.com> wrote in message news:Owl82rc3EHA.2804@.TK2MSFTNGP15.phx.gbl...
Hi,
I am searching concrete feedback on SQL Server 2000 SP3 Server
that have in place FTS catalog population with the following options:
- CHANGE TRACKING
- SCHEDULE
Many thanks.

Feature request

Hi,
It would be very nice if the following feature would somehow be implemented
in future releases:
- A report with two datasets
- A list on the report consuming records from dataset 1
- A list inside the existing list consuming records from dataset 2 with a
filter defined that allows an expression on a value from the current record
in dataset 1
This would allow us to create a master-detail report consuming two different
databases/queries without using sub-reports.
Thanks,
ErikThank you for your input. We have considered adding join functionality, but
have not scheduled the work for a particular release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Erik Tamminga" <newsgroups@.NeOtammiSnPgAaM.nl> wrote in message
news:cbkt5q$m1m$1@.news5.tilbu1.nb.home.nl...
> Hi,
> It would be very nice if the following feature would somehow be
implemented
> in future releases:
> - A report with two datasets
> - A list on the report consuming records from dataset 1
> - A list inside the existing list consuming records from dataset 2 with a
> filter defined that allows an expression on a value from the current
record
> in dataset 1
> This would allow us to create a master-detail report consuming two
different
> databases/queries without using sub-reports.
> Thanks,
> Erik
>|||Hi,
Yes, reporting services almost contains all the required functionality, but
when defining the filter on the second list (bound to the second dataset)
you don't have the option to match a field from the current record on the
first/parent list.
Erik
"chanley54" <chanley54@.discussions.microsoft.com> schreef in bericht
news:2717F0E8-2847-4DD7-A29F-81B2459F318F@.microsoft.com...
> Erik,
> It already supports multiple datasets. On the data region, click the
drop-down and you should see "New Dataset". If you want your other dataset
to use a different datasource simply add another datasource to the project.
> I believe that once you do this you will also not have an issue with
section 2 of your feature request. HTH
> "Erik Tamminga" wrote:
> > Hi,
> >
> > It would be very nice if the following feature would somehow be
implemented
> > in future releases:
> >
> > - A report with two datasets
> > - A list on the report consuming records from dataset 1
> > - A list inside the existing list consuming records from dataset 2 with
a
> > filter defined that allows an expression on a value from the current
record
> > in dataset 1
> >
> > This would allow us to create a master-detail report consuming two
different
> > databases/queries without using sub-reports.
> >
> > Thanks,
> >
> > Erik
> >
> >
> >|||Yes, this is heterogenous join. While it is simple to design, it is pretty
complex to make it perform. It is high on the list for a future release.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Erik Tamminga" <newsgroups@.NeOtammiSnPgAaM.nl> wrote in message
news:cbkt5q$m1m$1@.news5.tilbu1.nb.home.nl...
> Hi,
> It would be very nice if the following feature would somehow be
> implemented
> in future releases:
> - A report with two datasets
> - A list on the report consuming records from dataset 1
> - A list inside the existing list consuming records from dataset 2 with a
> filter defined that allows an expression on a value from the current
> record
> in dataset 1
> This would allow us to create a master-detail report consuming two
> different
> databases/queries without using sub-reports.
> Thanks,
> Erik
>|||Hi Brian,
If only I could allow the filter to evaluate fields of the current record of
other datasets .... (doesn't sound too hard to implement).
You'll find me among the people waiting for the future release! But, good
job so far!
Erik
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> schreef in bericht
news:uYLtvH0XEHA.2408@.tk2msftngp13.phx.gbl...
> Yes, this is heterogenous join. While it is simple to design, it is pretty
> complex to make it perform. It is high on the list for a future release.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Erik Tamminga" <newsgroups@.NeOtammiSnPgAaM.nl> wrote in message
> news:cbkt5q$m1m$1@.news5.tilbu1.nb.home.nl...
> > Hi,
> >
> > It would be very nice if the following feature would somehow be
> > implemented
> > in future releases:
> >
> > - A report with two datasets
> > - A list on the report consuming records from dataset 1
> > - A list inside the existing list consuming records from dataset 2 with
a
> > filter defined that allows an expression on a value from the current
> > record
> > in dataset 1
> >
> > This would allow us to create a master-detail report consuming two
> > different
> > databases/queries without using sub-reports.
> >
> > Thanks,
> >
> > Erik
> >
> >
>

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA

Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
statements as an alternative to strict UPDATES to TEXT data type attributes.
Sincerely,
Anthony Thomas

"Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA

Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
|||this could be to do with the following
1) the collation on your database is/was different to the server collation
2) is the column in question text(this data type could cause the issue
mentioned) and not ntext
can you confirm that the above is not true?
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uyDxQIX7EHA.2572@.tk2msftngp13.phx.gbl...
> Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
> statements as an alternative to strict UPDATES to TEXT data type
attributes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
> news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
> Hi,
> I am receiving the following error when trying to use an 'instead of'
update
> trigger.
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> It looks like something to do with calling stored procedures with text
> fields. The following test case illustrates the problem:
> CREATE TABLE A
> (Field1 int,
> Field2 text)
> GO
>
> CREATE TRIGGER A_UpdateTrig
> ON A
> INSTEAD OF UPDATE
> AS
> UPDATE A
> SET Field1 = i.Field1,
> Field2 = i.Field2
> FROM
> A JOIN inserted i ON (A.Field1 = i.Field1)
>
> GO
>
> INSERT INTO A VALUES (1, 'aaa')
> INSERT INTO A VALUES (2, 'bbb')
> GO
> CREATE PROCEDURE UpdateA
> @.Field1 int,
> @.Field2 text
> AS
>
> UPDATE A
> SET Field2 = @.Field2
> WHERE
> Field1 = @.Field1
> return 1
> GO
>
> -- Error occurs here:
> exec UpdateA 2, N'cccc'
>
> -- No error occurs here:
> DROP TRIGGER A_UpdateTrig
> exec UpdateA 2, N'ddd'
> DROP TABLE A
> DROP PROCEDURE UpdateA
>
> --
> Running this produces the following result:
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
> Any ideas would be appreciated. BTW select @.@.version returns:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
|||Hi Michael,
I alredy apply the hotfix in my test machine but still I received the error :
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 91 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
What else can I do?
Thanks in advance :=)

Quote:

Originally posted by Michael Cheng [MSFT]
Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionChec
kForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/defaul...scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
statements as an alternative to strict UPDATES to TEXT data type attributes.
Sincerely,
Anthony Thomas
"Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionChec
kForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)|||this could be to do with the following
1) the collation on your database is/was different to the server collation
2) is the column in question text(this data type could cause the issue
mentioned) and not ntext
can you confirm that the above is not true?
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uyDxQIX7EHA.2572@.tk2msftngp13.phx.gbl...
> Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
> statements as an alternative to strict UPDATES to TEXT data type
attributes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
> news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
> Hi,
> I am receiving the following error when trying to use an 'instead of'
update
> trigger.
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> It looks like something to do with calling stored procedures with text
> fields. The following test case illustrates the problem:
> CREATE TABLE A
> (Field1 int,
> Field2 text)
> GO
>
> CREATE TRIGGER A_UpdateTrig
> ON A
> INSTEAD OF UPDATE
> AS
> UPDATE A
> SET Field1 = i.Field1,
> Field2 = i.Field2
> FROM
> A JOIN inserted i ON (A.Field1 = i.Field1)
>
> GO
>
> INSERT INTO A VALUES (1, 'aaa')
> INSERT INTO A VALUES (2, 'bbb')
> GO
> CREATE PROCEDURE UpdateA
> @.Field1 int,
> @.Field2 text
> AS
>
> UPDATE A
> SET Field2 = @.Field2
> WHERE
> Field1 = @.Field1
> return 1
> GO
>
> -- Error occurs here:
> exec UpdateA 2, N'cccc'
>
> -- No error occurs here:
> DROP TRIGGER A_UpdateTrig
> exec UpdateA 2, N'ddd'
> DROP TABLE A
> DROP PROCEDURE UpdateA
>
> --
> Running this produces the following result:
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCh
eckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
> Any ideas would be appreciated. BTW select @.@.version returns:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
--
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
statements as an alternative to strict UPDATES to TEXT data type attributes.
Sincerely,
Anthony Thomas
"Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)|||this could be to do with the following
1) the collation on your database is/was different to the server collation
2) is the column in question text(this data type could cause the issue
mentioned) and not ntext
can you confirm that the above is not true?
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uyDxQIX7EHA.2572@.tk2msftngp13.phx.gbl...
> Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
> statements as an alternative to strict UPDATES to TEXT data type
attributes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
> news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
> Hi,
> I am receiving the following error when trying to use an 'instead of'
update
> trigger.
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> It looks like something to do with calling stored procedures with text
> fields. The following test case illustrates the problem:
> CREATE TABLE A
> (Field1 int,
> Field2 text)
> GO
>
> CREATE TRIGGER A_UpdateTrig
> ON A
> INSTEAD OF UPDATE
> AS
> UPDATE A
> SET Field1 = i.Field1,
> Field2 = i.Field2
> FROM
> A JOIN inserted i ON (A.Field1 = i.Field1)
>
> GO
>
> INSERT INTO A VALUES (1, 'aaa')
> INSERT INTO A VALUES (2, 'bbb')
> GO
> CREATE PROCEDURE UpdateA
> @.Field1 int,
> @.Field2 text
> AS
>
> UPDATE A
> SET Field2 = @.Field2
> WHERE
> Field1 = @.Field1
> return 1
> GO
>
> -- Error occurs here:
> exec UpdateA 2, N'cccc'
>
> -- No error occurs here:
> DROP TRIGGER A_UpdateTrig
> exec UpdateA 2, N'ddd'
> DROP TABLE A
> DROP PROCEDURE UpdateA
>
> --
> Running this produces the following result:
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
> Any ideas would be appreciated. BTW select @.@.version returns:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>

Fatal Error 7105 ... text, ntext, or image node does not exist

I'm getting the following in error in Enterprise Manager:
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is already
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
Thanks
I have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), slot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist

Fatal Error 7105 ... text, ntext, or image node does not exist

I'm getting the following in error in Enterprise Manager:
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is alread
y
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
ThanksI have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), sl
ot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist

Fatal Error 7105 ... text, ntext, or image node does not exist

I'm getting the following in error in Enterprise Manager:
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is already
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
ThanksI have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), slot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist