Manually changing flat file source has no effect

I have a data flow that reads from a flat file source, goes through one data transformation component to change from unicode to normal text and writes the data to a SQL Server table. This has been working fine throughout development using a specific source file as input. I have now manually changed the path and name of the input source file in the connection manager to point to a new file and the task continues to process the old text file.

I open the connection manager and check its properties and preview the data and it all looks fine - it is finding the new file. I also edit the flat file source component and preview the data and it shows the data from the new file. I run the data flow by right-clicking and selecting Execute Container and it continually reads the old file and processes it! (I do the right-click thing because this is just one small part of a larger package.)

This has got to be a bug, but just where I wonder. Anyone ever see this before? I'm going to try to run the entire package in debug mode, instead of right-clicking, next and see if that's any different. Anyone have any ideas on how to force a refresh of the necessary internal components to make it read the new file? All the external properties point to the new file, but it's not being read.

Joe

Update:

I ran the entire package and found no difference in execution - it's still reading the wrong input file. I then deleted and recreated the connection manager, again specifying the new file to read. It still continued to read the old file. I deleted the flat file source component and recreated it, specifying the latest connection manager (twice, since I must have pointed it at the wrong one the first time and it read a completely different file). I still have the same problem of it reading the wrong input file. I don't know what else to recreate that would have any effect. Does anyone have any ideas? I need to change the pointer to different files multiple times and have it read several different input files. This has got to work somehow. Any help is appreciated.

Joe

Perhaps you have a Package Configuration enabled that you have forgotten about? Have you ever run the package configuration wizard on this package? Do you see anything in the log or progress report to suggest that a config is being loaded? What do you see in the progress window?

When you deleted and recreated the connection manager, did you create a new name for the connection manager, and then change the source adapter to use the new connection manager? That would throw off any configurations.

Finally, you could try renaming the original source file - the package may fail, but the specific point of failure may tell you more.

Donald

|||

Thanks for your comments, Donald. I ran it again this morning and got the same error, but, as suggested, I checked the Output log and did indeed see a reference to a configuration file. I would have said that I had no configuration set up for this package, since I only started looking into configurations a couple of weeks ago. However, I was out of the office for a week and I must have gotten further than I thought, because there was a configuration set up for this package. My memory is already starting to deteriate.

Sorry for taking your time, but I sure do appreciate your help.

Joe

Manipulating Xml using Sql Server

Hi.
I need some help please...
There is a field, type text, in a table, that conatins xml
document.
Is it possible, using sql server to do the following?
1. Get the Xml from that field
2. Search the xml for a specific element ( by using its
name )
3. Search in this element, a certain attribute.
4. If the attribute exists, modify its value.
5. Save it bacl to the table
I guess that I am looking for some kind of an XML DOM
inside Sql server.
Is it possible?
Thanks a lot
RoyAt the server level SQL provides very limited support for manipulating the
XML DOM.
A better option is to use client side libraries like SQLXML to manipulate
DOM objects.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Roy" <roy@.smsolutions.co.nz> wrote in message
news:07ce01c3b30a$3d9cffd0$a401280a@.phx.gbl...
> Hi.
> I need some help please...
> There is a field, type text, in a table, that conatins xml
> document.
> Is it possible, using sql server to do the following?
> 1. Get the Xml from that field
> 2. Search the xml for a specific element ( by using its
> name )
> 3. Search in this element, a certain attribute.
> 4. If the attribute exists, modify its value.
> 5. Save it bacl to the table
> I guess that I am looking for some kind of an XML DOM
> inside Sql server.
>
> Is it possible?
> Thanks a lot
> Roy
>|||"Roy" <roy@.smsolutions.co.nz> wrote in message
news:07ce01c3b30a$3d9cffd0$a401280a@.phx.gbl...
Roy, you can use sp_OA procs to instantie XML Dom object. The basic idea is
to create stored proc that receives xml data through text type input
parameter. now you can (using sp_OA) create xmldocument object and work with
it. It's complicated, and i'm not sure if this is the best way to do it (or
even recomended), but you can do it this way.
Regards,
Tomislav Kralj
MCSD/.NET, MCDBA
tomislav.kralj1@.zg.tel.hr

Manipulating Text,nText data types filed in tsql

I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..

EXEC sp_executesql@.Statement,N'@.param1 varchar(3),@.param2 varchar(1)',@.param1,@.param2,
GO

As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".

DECLARE@.Statement varbinary(16)
SELECT@.Statement = TEXTPTR(Statement)
FROM table1
READTEXT table1.statement@.Statement 0 16566

So far so good, the issue is how to convert @.Statment varbinary to nText to get it passed in sp_executesql.

Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.

I would appreciate if any body respond to this.

Yes, the limitation of using NTEXT as variable causes the problem. I know a workaround, but... you still need EXEC to warp the execution of sp_executesql. Read this:

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
This works, because the @.stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2),
@.mycnt int
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT @.cnt = COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
INSERT #result (cnt)
EXEC('DECLARE @.cnt int
EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2),
@.cnt int OUTPUT'',
@.state = ''' + @.state + ''',
@.cnt = @.cnt OUTPUT
SELECT @.cnt')
SELECT @.mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.

So you can break the NTEXT statement from your table into several NVARCHAR(4000) strings, then pass the strings as parameter for sp_executesql. The original wonderful artilc can be found here:

http://www.sommarskog.se/dynamic_sql.html

|||Thanks, I had to break the query into pieces to get this working.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

Manipulating a text box in custom code.

Hi there,

A simple question I hope. I have got a textbox on a report and I'm trying to populate it by calling a custom assembly. I know I can reference it directly in the textbox (this works) but I am trying to do this from the code block. The following code didn't work:

Protected Overrides Sub OnInit()
ReportItems!textbox2.Value = POCCustomAssembly.CustAssembly.Hello()
End Sub

The TextBox is called textbox2 and the custom assembly simply returns a string.

I get an error message "The is an error on line 1 of custom code: [BC30469] Reference to a non-shared member requires an object reference".

What am I doing wrong?

Hi,

I know you submitted this months ago...but I was wondering if you found the solution to your problem? I essentially am trying to do the exact same thing and can't figure out the error. I would appreciate any help.

|||

The error message you're getting suggests that the code wouldn't even work in the textbox value property, although the message could be wrong and misleading.

Is the method you have in your assembly "Shared" (VB) or "static" (c#)?

If not, try to make it shared/static. If that's not possible to do,

you can add objects to the report in Report > Report Properties > References > Classes

add the class name and your desired instance name for the object. Then you can use this instance name in your custom code.

|||

Thank you for the responose. Here is my code:

I basically wrote an assembly to calculate percentages and handle situations to divide by zero.

namespace Calculations

{

public class calcPercentage

{

public decimal Percentage(decimal decValueOne, decimal decValueTwo)

{

decimal decPercentage = 0;

if (decValueOne == 0 || decValueTwo == 0)

{

decPercentage = 0;

}

else

{

decPercentage = (decValueOne - decValueTwo) / decValueOne;

}

return decPercentage;

}

}

}

Then in my textbox I put:

=Calculations.CalcPercentage.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS"))

I referenced the assembly and added it to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies and I still get that error message so I do not know what the problem is.

Then after carefully reading your response, I realized I didn't write "static" in my method even though that's what I meant it to be. Now the program builds successfully but when I run it and try to produce the PDF file it catches on error when it tries to render it as a PDF. Now to figure out that error...which I will save for tomorrow....I must have screwed something up in the process.

Thanks so much for you help! If you have any suggestions about my next error...i'm all ears!

|||Also, when I removed the assembly from my report and reran it they worked fine...so if you have any suggestions let me know.|||

I cannot get the report to render to design mode while using a custom assembly. As soon as I remove the assembly, it works just fine. can anyone suggest what might be the problem? All my code is listed above and what steps I have already taken.

Thanks!

|||

Any custom assemblies used for SSRS must be declared SHARED for VB or STATIC for C#.

So for instance:

vb sample:

Public Class Class1

Public Shared Function CallMeIshmal() as string

Return "Call Me Ishaml"

End Function

Any other functions or subs must also be declared as Shared or Static.

You also need to either add the compiled dll to the GAC or copy it to the reportserver and reportbuilder directories.

You may also need to modify the reportserver.config file for permissions.

this link:

http://support.microsoft.com/default.aspx/pwebcst

has a list of webcasts, the first one is about using custom assemblies in SSRS>

Daryl

Manipulating a text box in custom code.

Hi there,

A simple question I hope. I have got a textbox on a report and I'm trying to populate it by calling a custom assembly. I know I can reference it directly in the textbox (this works) but I am trying to do this from the code block. The following code didn't work:

Protected Overrides Sub OnInit()
ReportItems!textbox2.Value = POCCustomAssembly.CustAssembly.Hello()
End Sub

The TextBox is called textbox2 and the custom assembly simply returns a string.

I get an error message "The is an error on line 1 of custom code: [BC30469] Reference to a non-shared member requires an object reference".

What am I doing wrong?

Hi,

I know you submitted this months ago...but I was wondering if you found the solution to your problem? I essentially am trying to do the exact same thing and can't figure out the error. I would appreciate any help.

|||

The error message you're getting suggests that the code wouldn't even work in the textbox value property, although the message could be wrong and misleading.

Is the method you have in your assembly "Shared" (VB) or "static" (c#)?

If not, try to make it shared/static. If that's not possible to do,

you can add objects to the report in Report > Report Properties > References > Classes

add the class name and your desired instance name for the object. Then you can use this instance name in your custom code.

|||

Thank you for the responose. Here is my code:

I basically wrote an assembly to calculate percentages and handle situations to divide by zero.

namespace Calculations

{

public class calcPercentage

{

public decimal Percentage(decimal decValueOne, decimal decValueTwo)

{

decimal decPercentage = 0;

if (decValueOne == 0 || decValueTwo == 0)

{

decPercentage = 0;

}

else

{

decPercentage = (decValueOne - decValueTwo) / decValueOne;

}

return decPercentage;

}

}

}

Then in my textbox I put:

=Calculations.CalcPercentage.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS"))

I referenced the assembly and added it to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies and I still get that error message so I do not know what the problem is.

Then after carefully reading your response, I realized I didn't write "static" in my method even though that's what I meant it to be. Now the program builds successfully but when I run it and try to produce the PDF file it catches on error when it tries to render it as a PDF. Now to figure out that error...which I will save for tomorrow....I must have screwed something up in the process.

Thanks so much for you help! If you have any suggestions about my next error...i'm all ears!

|||Also, when I removed the assembly from my report and reran it they worked fine...so if you have any suggestions let me know.|||

I cannot get the report to render to design mode while using a custom assembly. As soon as I remove the assembly, it works just fine. can anyone suggest what might be the problem? All my code is listed above and what steps I have already taken.

Thanks!

|||

Any custom assemblies used for SSRS must be declared SHARED for VB or STATIC for C#.

So for instance:

vb sample:

Public Class Class1

Public Shared Function CallMeIshmal() as string

Return "Call Me Ishaml"

End Function

Any other functions or subs must also be declared as Shared or Static.

You also need to either add the compiled dll to the GAC or copy it to the reportserver and reportbuilder directories.

You may also need to modify the reportserver.config file for permissions.

this link:

http://support.microsoft.com/default.aspx/pwebcst

has a list of webcasts, the first one is about using custom assemblies in SSRS>

Daryl

Manipulate Data

How can I manipulate the data in a column to get only the
numbers and leave the rest (Either on the select or remove
the text and leave the numbers in the column)?
Column with the data like:
346876 Error
432422 Warning
233556 Error
445332 Error
564445 Error
124345 Warning
995445 Info
Thanks for the help.
select * from bla where column1 like
'[0-9][0-9][0-9][0-9][0-9][0-9]%'
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.
|||If the data is always in this format, you can do:
SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM table
You might consider storing the two data elements separate since, apparently,
they are idependently relevant...
http://www.aspfaq.com/
(Reverse address to reply.)
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.
|||Thanks Chris.........but the number of digits can vary
(4,5,6,7,8,9,10)

>--Original Message--
>select * from bla where column1 like
>'[0-9][0-9][0-9][0-9][0-9][0-9]%'
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
the[vbcol=seagreen]
remove
>
>.
>
|||Thanks Aaron......That is what I wanted...

>--Original Message--
>If the data is always in this format, you can do:
>SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM
table
>You might consider storing the two data elements separate
since, apparently,
>they are idependently relevant...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
the[vbcol=seagreen]
remove
>
>.
>

Manipulate Data

How can I manipulate the data in a column to get only the
numbers and leave the rest (Either on the select or remove
the text and leave the numbers in the column)?
Column with the data like:
346876 Error
432422 Warning
233556 Error
445332 Error
564445 Error
124345 Warning
995445 Info
Thanks for the help.select * from bla where column1 like
'[0-9][0-9][0-9][0-9][0-9][0-9]%'
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.|||If the data is always in this format, you can do:
SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM table
You might consider storing the two data elements separate since, apparently,
they are idependently relevant...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.|||Thanks Chris.........but the number of digits can vary
(4,5,6,7,8,9,10)
>--Original Message--
>select * from bla where column1 like
>'[0-9][0-9][0-9][0-9][0-9][0-9]%'
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
>> How can I manipulate the data in a column to get only
the
>> numbers and leave the rest (Either on the select or
remove
>> the text and leave the numbers in the column)?
>> Column with the data like:
>> 346876 Error
>> 432422 Warning
>> 233556 Error
>> 445332 Error
>> 564445 Error
>> 124345 Warning
>> 995445 Info
>> Thanks for the help.
>
>.
>|||Thanks Aaron......That is what I wanted...
>--Original Message--
>If the data is always in this format, you can do:
>SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM
table
>You might consider storing the two data elements separate
since, apparently,
>they are idependently relevant...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
>> How can I manipulate the data in a column to get only
the
>> numbers and leave the rest (Either on the select or
remove
>> the text and leave the numbers in the column)?
>> Column with the data like:
>> 346876 Error
>> 432422 Warning
>> 233556 Error
>> 445332 Error
>> 564445 Error
>> 124345 Warning
>> 995445 Info
>> Thanks for the help.
>
>.
>

Managing ntext, text with a long text data

Hi,
I have a problem to insert(update) a long text (more than 64K) into
SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
MSDN says: "When the ntext, text, and image data values get larger,
however, they must be handled on a block-by-block basis. Both
Transact-
SQL and the database APIs contain functions that allow applications to

work with ntext, text, and image data block by block." Could somebody

give me an example how to do this, please.
Thank youThere are examples under UPDATETEXT and WRITETEXT in Books Online - do
these cover what you're trying to do? The MSSQL Resource Kit also has a
whole chapter on working with BLOBs, including a number of examples
using TSQL and ADO:

http://www.microsoft.com/technet/pr...art3/c1161.mspx

Simon|||igorsl (igorsl@.yahoo-dot-com.no-spam.invalid) writes:
> I have a problem to insert(update) a long text (more than 64K) into
> SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
> MSDN says: "When the ntext, text, and image data values get larger,
> however, they must be handled on a block-by-block basis. Both
> Transact-
> SQL and the database APIs contain functions that allow applications to
> work with ntext, text, and image data block by block." Could somebody
> give me an example how to do this, please.

I believe this limitation is in the client API rather than in T-SQL
itself. (Altough inserting a 1MB value through a plain INSERT is not
that performant.) Which API are you using?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp