Text And Image Functions

Feb 10, 2007

Hi ,

I am trying to write some code that Fetch,Add & updates a text field of a table in my SQL Server 2000 database.

Does anybody have any sample statements that will help me update that field
with larger amounts of data?

limitation:
Columns with TEXT data type do not support commonly used string functions such as
LEN, LEFT, RIGHT, and so on.

Can u tell me function for :
To get length of data of text type
To Add/Update string to it
To search string in it
To ltrim / rtrim the value..

Thanks
Yogesh

View 3 Replies


ADVERTISEMENT

SQL Server 2008 :: Using String Functions On Text

May 26, 2015

I've below value in a column with data type - TEXT

QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06

This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.

I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)...

View 5 Replies View Related

How To Put Both Text And Image Into SQL 2000?

Aug 26, 2005

how to put both  text and image into SQL 2000?

View 1 Replies View Related

CHECKSUM Replacement For IMAGE And TEXT

Aug 22, 2006

With this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70328
I started to thinkn about Microsoft really calculated checksum value.

This code is 100% compatible with MS original. That is, the result is identical.
You can use it "as is", or you can use it to see that MS function does not produce that unique values one could expect.

With text/varchar/image data, call with SELECT BINARY_CHECKSUM('abcdefghijklmnop'), dbo.fnPesoBinaryChecksum('abcdefghijklmnop')
With integer data, call with SELECT BINARY_CHECKSUM(123), dbo.fnPesoBinaryChecksum(CAST(123 AS VARBINARY))
I haven't figured out how to calculate checksum for integers greater than 255 yet.CREATE FUNCTION dbo.fnPesoBinaryChecksum
(
@Data IMAGE
)
RETURNS INT
AS

BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT

SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0

WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ SUBSTRING(@Data, @Index, 1),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1

IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296

RETURN @SUM
ENDActually this is an improvement of MS function, since it accepts TEXT and IMAGE data.CREATE FUNCTION dbo.fnPesoTextChecksum
(
@Data TEXT
)
RETURNS INT
AS

BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT

SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0

WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ ASCII(SUBSTRING(@Data, @Index, 1)),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1

IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296

RETURN @SUM
END
Peter Larsson
Helsingborg, Sweden

View 6 Replies View Related

Scripting Text, Image Columns

Jul 20, 2005

We have remote users running MSDE entering information into adatabase. To send the data back to the home office, we've written someroutines that export the data into SQL Scripts in text files:DELETE <table> where KeyID=<x>INSERT INOT <table> (fields) VALUES (fields).We then zip up these scripts, and either email or ftp the ZIPs back tothe home office, where a program opens them up and simply "runs" theSQL statements.The new version of my database application contains both TEXT fields(containing rich text) and IMAGE fields (containing file attachmentslike JPGs or excel spreadsheets). I'm worried that our SQL Scripterengine (which we really like) is going to choke on the text and imagefields. Can anyone suggest a similar method to send the SQL dataaround?thanksmatt tag

View 4 Replies View Related

Update Of A Text/image And A Clustering Key

Aug 29, 2006

Hello Everyone,

We receive this error with ADO.NET (on a SQL Server 2000):

The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at ?OnExecuteNonQuery@dtbmsq_Statement_c@@$$FUAE?AW4dtb_Result_t@@PAUdtbsql_Connection_ip@@PAH@Z(dtbmsq_Statement_c* , dtbsql_Connection_ip* Connection, Int32* NbRowsAffected) in s:ogl20061srcodbmsqdtbmsqdtbmsq_statement_c.cpp:line 598

N.B. The update concerned only update one row.

Is it a known restriction? Is it a restriction from ADO.NET or from the database server?

Is it documented somewhere?

Regards.

Carl

View 6 Replies View Related

Help With Streaming Text File Saved As Image

Feb 8, 2008

Hello everyone.
I have an interesting problem. We have a SQL database with a field that is an image type. In it are records where the image field are text files. What I would like to do is look at these files and stream them line by line and do some processes for each line read.
 So let's say a client machine uploaded a file called myfile.txt into this database.
I would like the asp.net (vb) application tkae this file and read each line and do some processing.
I looked at memorystream and streamreader but just can not figure this out. Can you please help? Thank you Kameron

View 2 Replies View Related

How Can I Do Text Serach In Field With Datatype Image

Jan 18, 2005

i write a small web application to save word files in sql server and i store it as an image datatype
i need to do operation such as search within these files for any word

is there any One Can help me with that ???????????????????????????? Important

View 2 Replies View Related

Clustered Index And Image/text Data

Nov 20, 1998

If I have a table that I want to create a clustered index on. For example sake, say that I have 2 columns in the table. Col1 is char(2), col2 is text (or image). If I create a clustered index on col1, the database needs 1.2% times the size of the table to create the clustered index. Does this include the size of col2, being that text and image data is stored in a separate page chain....?

thanks for your time!

Tim

View 1 Replies View Related

Converting Binary Image To Readable Text

Sep 18, 2005

I have a table that contains the following two columns:BITS (image(16))BIT_LENGTH (int(4))When I look at the table, I see "OLE Object" in the BITS column. Whatsyntax should I use in a SELECT statement to convert the binary imageinfo contained in "BITS" into simple text that I can read? What roledoes the BIT_LENGTH field play?

View 1 Replies View Related

Text Doesn't Support Rtrim,ltrim Etc Functions...which Is Other Better Way To Save A Long String

Nov 10, 2007



Hi

I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.

If I try to use text datatype..It doesn't support rtrim, stuff functions etc...

So could any one suggest me a best way to save a long string without any restriction of size...

My front end is C#.Net and Back End is SQL SERVER 2000

Thanks in advance

View 1 Replies View Related

How To Set Up Search On Text Stored In An Image Type Of Data?

May 31, 2007

I am saving large text document in an image type of column in a SQL Server 2000 table.
How will I set up searching of words/ phrases for data stored in this column?

View 3 Replies View Related

Full-text Indexing Of An Image Column With A .PDF File

Mar 16, 2005

Hi,

I am having difficulty in indexing a Table when it contains a PDF file in an Image column. Basically I have a table with a unique id, some other fields, and an Image column. The Image column contains uploaded files from a web application. Full-text indexing works fine for all types of files that I have uploaded into this table which includes (.doc, .xls, .ppt) but I get the following message in my Event Viewer if one of thee files is a PDF file.

One or more documents stored in the Image columns with extension '.pdf' did not get full-text indexed because loading the filter failed with error '1x01'.

I have searched Microsoft's site and implemented their prescribed fixes but none has worked including installation of SP3 of Sql Server and/or modifying the registry key to run the Sql server indexing procedure as a single threaded process.

Has anyone experienced this problem? Please help.

Thanks
Kamal

View 1 Replies View Related

The Insert/update Of A Text Or Image Did Not Succeed (was SQL Error)

Jan 17, 2005

First I recieve this error when I upload a photo and click on the save button.
'ODBC - update on a linked table 'PersonMisc' failed.'

Then this error appears after I click ok.

[Microsoft][ODBC SQL Server Driver][SQL Server] The READTEXT and WRITETEXT statements cannot be used with views[#285][Microsoft][ODBC SQL Server Driver] Warning: Partial insert/update The insert/update of a text or image did not succeed.[#0].

Is there a size limit on the pictures I am uploading?

View 2 Replies View Related

Trim Text Data Stored In Image Column

Mar 13, 2012

I need to trim (removing white spaces) the data or text in a column which is of type IMAGE.

I would like to achieve this through a PL/SQL procedure or function.

View 14 Replies View Related

Text, Ntext, Or Image Node Does Not Exist Error

Apr 17, 2006

Hai Friends...

I am using sql server 2000. i have one table with columns

empid notnull
empfname not null
empmname null
emplname not null

while i am writing a simple procedure to get the employee middel name(empmname) it is giving the error message like this


Server: Msg 7105, Level 22, State 6, Procedure emp_statistics, Line 11
Page (1:70), slot 18 for text, ntext, or image node does not exist.

Connection Broken



plase help me Urgent

Thanks and Regards

Krishna Mangamuri

View 8 Replies View Related

DB2 CLOBs And BLOBs To SQL Server TEXT And IMAGE Via SSIS

Aug 13, 2007

I'm trying to write SSIS packages to import DB2 6000 tables to SQL Server 2005 tables. The package vendor has defined CLOB and BLOB datatypes for columns in the DB2 tables, and going through the Data Flow tasks from OLE DB for DB2 to OLE DB for SQL Server, it tries to map CLOBs to TEXT columns, and BLOBs to IMAGE columns. The vendor has no conversion utility available, so I have to write the SSIS packages. However, the task generates a goodly number of errors just trying to open the OLE DB DB2 dataset. Errors like the following:


Error: 0xC0202009 at Set 5 1 2, DB2 vendor_master [7127]: An OLE DB error has occurred. Error code: 0x80040E21.


An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".


Error: 0xC0208265 at Set 5 1 2, DB2 vendor_master [7127]: Failed to retrieve long data for column "COMPETITOR_COMMENT".


Error: 0xC020901C at Set 5 1 2, DB2 vendor_master [7127]: There was an error with output column "COMPETITOR_COMMENT" (8866) on output "OLE DB Source Output" (7138). The column status returned was: "DBSTATUS_UNAVAILABLE".


Error: 0xC0209029 at Set 5 1 2, DB2 vendor_master [7127]: The "output column "COMPETITOR_COMMENT" (8866)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "COMPETITOR_COMMENT" (8866)" specifies failure on error. An error occurred on the specified object of the specified component.


Error: 0xC0047038 at Set 5 1 2, DTS.Pipeline: The PrimeOutput method on component "DB2 vendor_master" (7127) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.


Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.


Error: 0xC0047039 at Set 5 1 2, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.


Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Information: 0x40043008 at Set 5 1 2, DTS.Pipeline: Post Execute phase is beginning.

In this case, 'COMPTETITOR_COMMENT' is defined as a CLOB(2 M) column format in the DB2 database table, and the output format in the SQL Server 2005 table is defined as a TEXT column.

Any assistance would be appreciated.

View 1 Replies View Related

Replicate Ntext Text Image In Transaction Replication

May 16, 2007

Hi!

i have a problem with my replication.

I have two SQL 2005 SP2 Server and want to use the transaction replication with updateable subscriber. Now the problem is that i can do any changes on the master server. But if i want to change a record on the subscriber which contains a ntext, text, oder image column - then i geht the error that the field will be NULL on the master.



Is there any solution to fix this problem? I dont wan`t to change the datatype vom ntext to varchar(max) !!



greetings

Holger

View 3 Replies View Related

SQL ERROR - I Need DISTINCT But Can Use It With Image,ntext, Text - How To Work Aroud???

Oct 10, 2006

SQL ERROR - I need DISTINCT but can use it with image,ntext, text - How To work around???  ! more - How to Work around - MIN() I cant use it when having text in SELECT statement  The text, ntext, or image data type cannot be selected as DISTINCT.



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

Exception Details: System.Data.SqlClient.SqlException: The text, ntext, or image data type cannot be selected as DISTINCT.

Source Error:





An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below. 

View 2 Replies View Related

Reporting Services :: Show Image Once User Clicks Text Box?

Nov 19, 2015

I tried creating Toggle Option over a Text box to Show the Image on Click but Since the Toggle refreshes the entire report user faces performance issue. Is there any other option to avoid the Toggle Refreshing the report or to show an image on Click of an Text Box?

View 3 Replies View Related

Using Inserted / Deleted Tables With Text / NText / Image Data Type

Oct 6, 2004

Hi folks,

Table:

a int,
b int,
c int,
d text

I need to change my AFTER - Trigger from this (example!):

select * into #ins from inserted

to something like

select *(without Text / nText / image -columns) into #ins from inserted.

So I tried to build a string like this: (using INFORMATIONSCHEMES)

select @sql = 'select a,b,c into #ins from inserted'
exec(@sql)

a,b,c are not of Text, nText or Image datatype.

After executing the trigger, I get an error, that inserted is unknown.

Does anyone know how to solve this ?

Thx.

View 5 Replies View Related

XML DataSource, Text Dataset And Image From Database Using Visual Studio 2K5 And SSRS 2K5

Feb 19, 2008

"Short" Story Version:

I have a VS 2005 Web Appplication solution that uses SQL Server Reporting Services 2005. One of the projects is a Report Server Project. In this project I have an RDL file that has:
1.)a non-shared XML DataSource (no credentials),
2.)a DataSet with a Command Type of Text and a Query String that is as follows:
<Query>
<XmlData>
<Root>
<LOAD LOAD_ID_PREFIX="" LOAD_ID="" TRUCK="" DIAGRAM=""/>
</Root>
</XmlData>
</Query>


3.)a field that comes from the Database that is of VarBinary(Max) that is a JPEG Image

What happens is, is that by doing #2 above, at design time I can create tables and hook into my datasets and get the report all designed out the way I want. Then at runtime, I have a Stored Procedure that uses the "FOR XML AUTO" option so that I can represent my results from the database in an XML fashion which will allow me to replace the XML Schema in between the Root tags above (in #2) with my actual data (which still has the same schema). I then save the modified RDL to a database table field and move on to happily ever after......in this case, not. In this case, since an VarBinary is involved, unless I want to get a pointer back for the Diagram Column, I have to use the "FOR XML AUTO, BINARY BASE64" option. This returns an Encoded string to represent the Binary value that was in the Database....and this is the partial source of my angst....read on.

My approach that I described above worked flawlessly in all cases where an image wasn't involved. This report is the only report in the whole system I am working on that will have an image and it is the last one being done, so that's why I didn't run into this earlier.

I have tried all kinds of things at different levels, but the thing that I have tried that seemed to me to have the most potential was to use Embedded Code to take the Encoded ASCII Value and convert into something binary that the SSRS Reporting Engine likes (so my image will show), but I have been unsuccessful thus far. The error message I keep getting now from my Embedded Function is: "Invalid length for a Base-64 char array", which I have not been able to resolve thus far....and I do know that the Binary value in the database is good becuase I can see it in pages in my application otherwise.

This aside, is there anyone out there that knows how to take the value of a VarBinary(Max) field and insert it into the Text (XML) DataSet in the markup of an RDL file....and have it appear correctly when the report is rendered in something like the standard Report Viewer Control (or even when being previewed at design time inside of Visual Studio for that matter)?

Just to be clear, the root of my problem, as I see and understand it, is that I have a VarBinary value in a database that is being converted to a string (ASCII...I am presuming) due to how I am writing my stored procedure and then needs to go back to something else binary that the SSRS Engine will accept so that I can see my Image in the report when I render it.

I am wishful that someone out there understands my problem and has run into this and has a solution......but I am wishful of winning the lottery, too, and that hasn't happened yet...............

Thanks.

View 4 Replies View Related

Page (1:404399), Slot 5 For Text, Ntext, Or Image Node Does Not Exist.

Apr 7, 2008

I wonder if anyone can help.

I do not understand the error message but I do know it is associated with a specific table and one of its indexes. If I try to drop the index that is the error message I get.

I have copied the data from the table to a new table and rebuilt the indexes on the new table and everything is working fine.

The bad table is now renamed to myTable_BAD. I now want to delete it but can't as everytime I enter
drop table myTable_BAD

I get Page (1:404399), slot 5 for text, ntext, or image node does not exist.

So here is my question

How can I delete this Bad table?

View 9 Replies View Related

Reporting Services :: Show Text And Embedded Image In SSRS TextBox

Oct 7, 2015

I'm developing an SSRS report using VS2013 Premium with SSDT - BI v12.0.2430.0. SSRS is hosted on a SQL 2014 instance.The underlying database table has a varchar(max) field which stores text and embedded image data from a third-party application.  I can extract this text, save it as an html file and open that in a browser.  It renders complete with any embedded image.Meanwhile, the report textbox has its Placeholder Properties, Markup type set to HTML.  Yet when I run the report, the text is displayed but any embedded image is not.Is there any approach I can take, perhaps transforming the varchar(max) data to another format in the query for my dataset, that can show the text and any embedded image in my report?  Or is it that the TextBox control is limited, not unreasonably, to text only?  If so, is there another control I could use, perhaps custom or third-party?

View 7 Replies View Related

SSRS Report Designer Background Image Does NOT Align With Text In Preview

Sep 12, 2007

Hi,


I'm using the SSRS report designer within VS2005 and I have a .rdl report that I have placed a background form as an image background. I then have text placed on top of the form, the problem is that everything is aligned within the report designer layout view but when I go to view the report in report preview the text no longer remains vertically aligned within the background form.


This is very frustrating, and almost makes it impossible to marry up a form with report data. Any help would be appreciated.

View 5 Replies View Related

How To Convert To Regular Text, Data Stored In Image Data Type Field ????

Jul 20, 2005

Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD

View 1 Replies View Related

Move Text Data (not A File) Into An Image Data Type

Mar 11, 2008



The ERP manufacturer used an image data type to store large text data fields. I am trying to move these data types from one database to another database using either Sql Queries or MS Access. I can cast them as an 8000 char varchar to read them directly but have no luck importing into these image data fields.

Access and Crystal are not able to read these fields directly.

Any suggestions? Most information about these fields has to do with loading files but I am just moving data.

Thanks,

Ray

View 1 Replies View Related

Sending Uploaded Image To Data Access Class When Storing Image In SQL Server 2005

Apr 20, 2007

I am using the 3-tiered architecture design (presentation, business laws, and data acess layers). I am stuck on how to send the image the user selects in the upload file control to the BLL and then to the DAL because the DAL does all the inserts into the database. I would like to be able to check the file type in the BLL to make sure the file being uploaded is indeed a picture. Is there a way I can send the location of the file to the BLL, check the filetype, then upload the file and have the DAL insert the image into the database? I have seen examples where people use streams to upload the file directly from their presentation layer, but I would like to keep everything seperated in the three classes if possible. I also wasn't sure what variable type the image would be in the function in the BLL that receive the image from the PL. If there are any examples or tips anyone can give me that would be appreciated.

View 2 Replies View Related

SQL Server 2005: CLR Functions Vs SQL Functions

May 26, 2006

I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID

View 9 Replies View Related

How To Save Image In Sql Server And Display That Image In Datagrid??

Jun 27, 2007

Hay Friend's
Can u plese send me the way how to save image in sql server and display that images in datagrid or other control also like Image control or Image control Button?? Plese send the coding in C#.
 Thank's
Amit

View 5 Replies View Related

How To Store Image In Image Field In Sql Server 2000

Jul 12, 2007



hi all,

i have created a table with image field in it. Now i just want to store a jpeg file in it but not finding any way to do so.



how can i store any image ? what are the steps???????





thanx in advance



View 5 Replies View Related

HOW To Retrieve An Image From Sql Server And Display It In ASP.net Using Imagemap Or Image ?

Jul 6, 2006

Ok, the problem is that , i have a field called "Attach" in sql of type image, when selecting it , the field is getting data of type BYTE(). which am being unable to display them on an Image on the panel.

using the following vb.net code:

'Dim sel2 As String

'Dim myCom As SqlCommand

'Dim conn As New SqlConnection

'Dim drr As SqlDataReader

'Dim image As System.Drawing.Image

'sel2 = "select * from attach where att_desc = '" & DropDownList1.SelectedItem().Text & "' and doc_code = " & w_doc_code & " and subcode = " & w_doc_subcode & " and doc_num= " & w_doc_num & " "

'conn.ConnectionString = ("server=developer01;uid=sa;password=aims;database=DVPSOC;timeout=45")

'myCom = New SqlCommand(sel2, conn)

'conn.Open()

'drr = myCom.ExecuteReader()

'If drr.Read Then

' Me.ImageMap1.ImageUrl = drr.Item("attach")

'End If

'conn.Close()

Am getting an exeption on the following line Me.ImageMap1.ImageUrl = drr.Item("attach")

saying: Conversion from type 'Byte()' to type 'String' is not valid.

knowing that i tried converting using ToString but it's not getting any output then.

thanks for your help.

View 4 Replies View Related

How Image Display From SqlServer To Image Control

Feb 13, 2007

I have learned lots of informative thing from your forums. I have little problem regarding “Display image from SQL Server on ASP.NET� I have done it and image display on my page from SQL Server. I have cleared you here I have adopt two different methods which are following for displaying picture.

1.Response.BinaryWrite(rd("picture"))
2.image.Save(Response.OutputStream, ImageFormat.Jpeg)

but in both above methods I have faced little problem when image display on my page all other information can not display and I also want to display picture on my specific location on the page. My second question is can use any web control like “Image1� to display image from SQL Server where my pictures are stored.

Hope you will help me.

Thanks and regards


Aftab Abbasi

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved