Transact SQL :: Text Data Type Cannot Be Compared With Distinct

Oct 9, 2015

Field is not listed as text in any of the databases it is a varchar(255) - and that can be changed if that is what causes the issue.  

But here is my syntax which produces the error Msg 421, Level 16, State 1, Procedure, Line 2

The text data type cannot be selected as DISTINCT because it is not comparable.

DECLARE @c NVARCHAR(MAX)
WITH c1 AS (
SELECT [abcd] AS table_name
FROM [intranet].[dbo].[soccerfieldinfo]
where [abcd] IS NOT NULL
), c2 AS (
SELECT Row_Number() OVER (ORDER BY table_name) AS r

[Code] ....

View 3 Replies


ADVERTISEMENT

The Text Data Type Cannot Be Selected As DISTINCT Because It Is Not Comparable.

May 28, 2008

I'm having a problem building a query for an application I'm writing. Basically, there are a bunch of records in a table, and some of them may have the same identifier code. I only want one of each identifier code. The table looks like this:

Memo
---------
Msg_Identifier varchar(60)
Memo_ID int (pk)
Subject varchar(64)
Message text
Priority int
Deleted int


My query:

SELECT DISTINCT Msg_Identifier, Memo_ID, Subject, Message, Priority FROM Memo WHERE Deleted = '0'



When I run this query, I get "The text data type cannot be selected as DISTINCT because it is not comparable."

I only want Msg_Identifier to be the distinct field, and it's obviously not set up as text. I've used SELECT DISTINCT in the past but I don't ever recall hitting an issue like this. What's the best way to do it?

View 8 Replies View Related

Ntext Data Type Cannot Be Selected As DISTINCT

Mar 3, 2008

Hello,
When I am using DISTINCT in the SELECT clause for a table having a column of datatype "ntext" in SQL 2000 as well SQL 2005, it fails giving the error as "ntext data type cannot be selected as DISTINCT". This is coz
"The text, ntext, or image data type cannot be selected as DISTINCT."

But I am able to insert into a temporary table having a column whose datatype is nvarchar) using this SELECT clause in SQL 2000. Please advice me how this happens. If I am not worng, implicit conversion has happened. But this insert fails raising the same error in SQL 2005. Please help me to find the difference
thanks in advance!

View 3 Replies View Related

Converting Numeric Data Type To Text Data Type

Jul 20, 2005

Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin

View 1 Replies View Related

Convert Text Data Type To Smalldatetime Data Type

Oct 9, 2007

I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible?
I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.


SELECT rsbirthday CAST(rsbirthday AS smalldatetime)

FROM panelists


the error returned is "incorrect syntax near 'rsbirthday'.

I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.

Thanks for the help!

View 10 Replies View Related

Transact SQL :: Inserting Distinct Data From One Table To Another

Aug 19, 2015

I have 2 tables: Source Table - IncidentDimNew and Destination Table - IncidentDimNew with identical columns: Id and CreatedDate but the data is different.

I would like to insert distinct data from those 2 columns of 1st table into the same 2 columns into the 2nd table but I onlt want to replace the values in the 2nd table where the Created Date is > 2015-04

I compiled the code in order to get the information I want from 2 tables:

Source Table
SELECT
COUNT(Id),
LEFT(CONVERT(VARCHAR(10)), CreatedDate, 126), 7)
FROM
IncidentDimNew

[Code] ...

This is the code I wrote in order to do it
                     
INSERT INTO IncidentDim 
[Id]
      ,[CreatedDate]
     SELECT
[Id]
,[CreatedDate]

FROM
IncidentDimNew

where left(CONVERT(VARCHAR(10), CreatedDate, 126),7) > '2015-04'

GO

But what it does it's gives the SUM of the values in the corresponding rows where the values are not null which I don't want.

So  how do I leave the values as is from table IncidentDim and insert new values as it is from IncidentDimNew where the Created Date is > 2015-04?

View 2 Replies View Related

Truncated Text In Text Data Type

Jun 1, 2001

We have a text field which is being written to from a java app through JDBC-ODBC. But the data seems to be trucated in the DB. How do we store all the data in this field (the text being stored can be quite large) without it being truncated?

View 1 Replies View Related

Text Is Getting Cut After 1.4k Letters In Text Data Type

Oct 26, 2007

Hello all,
started working on a website in ASP that use mssql, i am trying to store large ammount of text, for now its just 10kk letters, the data type is set to text in the mssql db, but when i read it back from the db, i only get 7.7k letters, i know its not an ASP problem cause i print the var holding the content before storing it in the db.

my question is, is there a way to change the text length limit in mssql, or use another data type?

Thanks,
Gilad Darshan


EDIT: forgot its saving the html source with the style and not only the words. fixed to the right amount of letters

View 2 Replies View Related

Transact SQL :: Strategy To Translate Column Data Into Distinct Rows

Aug 27, 2015

I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:

PrimaryID       SKUChange              DateChange         LocationIdChange        StateChange
10003             TRUE                       FALSE                  TRUE                          FALSE
etc...

The output I'd like to see would be like this:

PrimaryID        Field Changed          Previous Value      New Value
10003             SKUName                 SKU12345           SKU56789
10003             LocationId                 Den123               NYC987
etc...

The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.

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

Text Data Type

Aug 30, 2005

This is a newbie question.
How many character does a text data type of MSSQL can contain ?
Thanks a lot.

View 2 Replies View Related

Text Data Type

Jun 17, 2002

Hi All,
In the SQL Server documentation, it is said that, the text data type will contain upto 2GB size. I assumed it will save upto 2GB. But what happened is, it saves only 255 characters all others were truncated. Is there anything wrong with me? Or the wrong is with the SQL Server?

Thanks,
Amjath

View 2 Replies View Related

Text Data Type

Mar 17, 2007

visual studio 2005 what is the differences between text, varchar and nvarchar data types in ms sql server? when and when not to use them?

View 4 Replies View Related

Text Data Type

Mar 23, 2008

I'm having a bit of a problem with this data type. It is supposed to be able to store about 2,147,483,647 characters, but when I enter more than about 1,834 characters no more charaters can be stored.

Has anyone experienced this and knows how to allow up to the 2,147,483,647 characters. Is it anything to do with another SQL Server setting or variable that needs to be changed

Thank you in advance for helping on this.

Regards

Steve Fouracre

Steve Fouracre

View 4 Replies View Related

Why Does Data Type Text Allow Max. 64 Characters?

Dec 25, 2006

hi all
does anybody know why the fields of my db with the type "text" can store max. 64 characters? i thought fields of the type "text" could save unlimited characters. is it any wrong setting?i'm using visual web developer with sql server express

View 2 Replies View Related

Data Type Varchar And Text

Oct 27, 2007

I encounter this particular error.
Exception Details: System.Data.SqlClient.SqlException: The data types varchar and text are incompatible in the equal to operator.
Line 21:             Dim reader As SqlDataReader = command.ExecuteReader() 
This is the first time I'm trying out with MS SQL so I'm abit lost. I hope my code is correct and I've did a little search. I did not set "Text" in my database, I use int and varchar. Here's the affected part of my code and the database. Dim password As String = ""
Dim querystring As String = "SELECT Password FROM Member WHERE Username = @username"

'Dim conn as SqlConnection
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("mainconnect").ConnectionString)
Dim command As New SqlCommand(querystring, conn)
command.Parameters.Add("@username", SqlDbType.Text)
command.Parameters("@username").Value = txtLogin.Text
conn.Open()

Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
password = reader("Password").ToString()
End While

reader.Close()

End Using
 
My database:
User_ID int(4)
Username varchar(50)
Password varchar(255)
Email varchar(50)
 
Any ideas?

View 2 Replies View Related

TEXT Data Type In SQL Server

Aug 20, 2004

Sorry if this is a dumb question.

I have a SQL Server TEXT Data Type field which stores a long description.
However when I update it from C# (ASP.net) through a textbox, it seems to cut off a part of the data if the number of characters is a big size (+- more than 577 characters).

I pass the data from then textbox to a string, but as soon as I run the Create/Update stored procedure that gets passed the string field, it seems to chop off the string.

What am I doing wrong?
Should one rather use a SQL Server string field for descriptions (+-1000 characters long) or should one use the TEXT field for this purpose?

Which one's performance is better?

View 1 Replies View Related

Text Data Type And Performance

Dec 13, 2005

I was wondering if someone could clear this up for me...
I have a table that will be used to store information about products that I will be selling on a new website. I would like to store each product in a table which will include a description of around 1000 words. I was deliberating over whether to store this chunk of text in a column with the data type set as 'text', or to store the text in a seperate txt file on the server. The search facility on the site will not be required to query this text so which would offer the best in terms of performance?

View 3 Replies View Related

Using Group By For Text Data Type

May 22, 2006

Hi,I have a problem on an old app. This application is a Help Desk, any Computer related problem (Motherboard Burn Out, Software Crash, dll) on my company will be recorded using this Help Desk application.One month ago, some programmer found a bug in this program lead to many duplicate data on database. But too late, This program has entered production stage and used for more than 2 years. This bug only happen at some condition so I found out after 2 years running (when my boss reported there are some duplicate data on "helpDesk" table).The bug is solved but helpDesk table need some "cleaning" operation. Here is structure of that table :helpDesk Table- id (primary key, integer auto increment)- reported_by (varchar 200)- problem_title (varchar 200)- problem_description (text)- date_summited (datetime)well usualy if I want to find some duplicate data I wil type :SELECT     MAX(uid) AS uid, reported_by, problem_title, COUNT(uid) AS total_duplicateFROM         problemsGROUP BY problem_title, problem_description,uidHAVING      (COUNT(uid) > 1)But that query will not work because problem_description data type is "text" which not support "group by" statement.Any other Idea how to locate and cleaning up this duplicate data?thanks

View 2 Replies View Related

Data Type For MP3's And Long Text

Jul 11, 2001

Hello,
I would like to know as to what data type is the best if I want to store MP3's and large amount of text in a SQL server.
Please let me know about the data type for both the tasks.
The table for MP3 is different than the table for large text (eg . saving somebody's resume)
Please do let me know.

Thank you,

John

View 2 Replies View Related

Trigger And Text Data Type

Dec 6, 2000

Hi All,
I have the code below on Sql 2k table with the column Descriptivetext defined as
Text Data Type. The table has Path and Page as the PK
I need to set a trigger to update Desciptivetext in other rows with the same path
when a one of them chnages.
Whenever I run the code, no row is updated

CREATE TABLE [dbo].[AB] (
[PATH] [varchar] (255) NOT NULL PRIMARY KEY CLUSTERED ,
[PAGE] [varchar] (60) NOT NULL PRIMARY KEY CLUSTERED ,
[DESCRIPTIVETEXT] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER [Update_Staging_Test] ON [dbo].[AB]
INSTEAD OF UPDATE
AS

Declare @TextPointer as Varbinary (16), @NewTextPointer as Varbinary (16), @Path
varchar(255), @Page as varchar(60), @DescriptiveText as varchar(7000)
If Update(DescriptiveText)
--Get the Path of the record from Inserted
Select @Page = Page, @Path = Path , @DescriptiveText = Cast(DescriptiveText as
Varchar(7000))
from Inserted


UPDATEAB
SETDescriptiveText = Cast (@DescriptiveText as Text)
WHEREPath = @Path




Rgds
GERI

View 5 Replies View Related

Text Data Type In Trigger

Feb 22, 2007

Hi,

I am using following query to get insereted records from a table, which is containing a column, having text datatype.

'SELECT * INTO #TEMP FROM INSERTED'

Then obviously it gives error.

Wat if i want to get all the columns from inserted table excluding the text datatype column, without giving the column names.

Thanks & Regards.
Shabber.

View 1 Replies View Related

Problem With Using A 'text' Data Type.

May 18, 2004

I am using a 'text' data type in a table and am having trouble adding data to it. I'm using an MS Access ADP project front end for my forms. I want to copy and paste from a text editor such as NotePad to a 'text' data type field in a form. There appears to be a limit that the field will accept which is much below the 2GB size that is allowed for the field. I get the following error message: "The text is too long to be edited." Once I get this error I can add characters from the keyboard but eventually the field will not accept any more characters. Typing or pasting does nothing. I can delete. So I've hit a limit but I'm not sure if it is in Access or SQL Server.

Any help would be appreciated.

Dennis Geasan

View 2 Replies View Related

Text Data Type Problem

Oct 12, 2007

I have a table named 'Documents' with a field named 'DocumentData' which is of the datatype Text.

I am using the following statements in a stored procedure which on execution gives errors:


DECLARE @DocData VARCHAR(100)

DECLARE @OrderID INT

IF ISNULL(@DocData, '') <> ''

BEGIN



IF EXISTS( SELECT DocumentData from Documents where OrderID=@OrderID and DocumentData = @DocData)

RaisError ('Document ignored.', 16, 1)






The errors that i am getting are the following :

Msg 279, Level 16, State 3, Procedure SPDocs_LoadData, Line 41
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Msg 306, Level 16, State 1, Procedure SPDocs_LoadData, Line 41
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Can anyone tell me how to rectify this? Basically i am trying to find out whether @DocData passed in the stored procedure matches the value in the table.

Thanks.

View 6 Replies View Related

SQL Server 'text' Data Type. Why Is Length = 16??

May 23, 2004

i need to store quite along description in the database, which in anyother database i would choose the data type 'text', however, can someone tell me why the length is set to just 16 in sql server..... i have seen a text field with far greater than 16 chars, set at length 16, so what does this length of 16 mean?????
also what is the max length of the text field?

thanks

View 2 Replies View Related

Using Text Data Type In Dynamic Sql Sproc

Jul 20, 2005

hi allthis is my first post to this group, so pls bear with me while i tryto make some sense.i am trying to create a sproc which uses dynamic sql to target aparticuar table eg. '[dbo].[' + @tableID + '_articles']' and performsome actions.i am using @tableID [int] as a passes parameter for the sproc.everything seems to work fine until i try and manipulate a parameterwhich is of text data type.the error occurs when i try to build the dynamic sql string and appendthe text type variable.eg.CREATE PROCEDURE [procArticlesInsert](@siteID [int],@strShortTitle [varchar](40),@strLongTitle [varchar](60),@strShortContent [text],@strLongContent [text],@intSectionID [int],@intTemplateID [int],@intStatusID [int])ASDECLARE @strSQL varchar (1000)DECLARE @strSiteID varchar (10)SET @strSiteID = CAST(@siteID AS varchar)SET @strSQL = ('INSERT INTO [' + @strSiteID + '_articles] ' +'( [dateEntered], ' +'[shortTitle], ' +'[longTitle], ' +'[shortContent], ' +'[longContent], ' +'[sectionID], ' +'[templateID], ' +'[statusID]) ' +'VALUES ' +'(' + CAST(GETDATE() AS VARCHAR) + ', ' +'''' + @strShortTitle + ''', ' +'''' + @strLongTitle + ''', ' +''''@strShortContent , ' +'@strLongContent , ' +CAST(@intSectionID AS VARCHAR) + ', ' +CAST(@intTemplateID AS VARCHAR) + ', ' +CAST(@intStatusID AS VARCHAR) + ')')GOi could cast the text fields (@strShortContent , @strLongContent) tovarchar, but the restriction of 8000 characters will not go down sonicely.if anyone has any ideas or alternatives to what i am trying toachieve, i would love to hear from you.thanksadrian.

View 5 Replies View Related

Breaking Out Data From A Text Field Type

Jul 20, 2005

In my database there is a text field type that is used to enter streetaddress. This address could be a few lines long, each line with acarriage return at the end.Is there a way to search for these carriage returns and break out whatis in each line seperately?Thanks.Mike

View 1 Replies View Related

Using TEXT Data Type With Carriage Return

Jul 29, 2007

Hi,

I'm using quite odd combination of technology for my project, I'm using PHP and MSSQL 2000, at one certain page, I want to insert to a table where one of the column is TEXT data type, and I want to get the value from the TEXTAREA at the page, of course, with carriage return captured, I manage to get it done in MySQL, where it automatically store the carriage return keyed in by user at the TEXTAREA, while for MSSQL I no luck in finding solution for this, is there any settings I can set or I need to convert the carriage return keystroke to HTML tag at my PHP?

Thanks

View 1 Replies View Related

Inserting The Data Into Text Data Type

Jan 22, 2001

Hi everybody,

In our datbase we have a table with text data type.Help me if anybody knows how to insert text data into text data type of sql server.

i am able to modify and retrive but i am not able to insert text. please if u have idea, please give me reply asap.

Thanks,
Giri

View 1 Replies View Related

Transact SQL :: Converting Data Type Nvarchar To Bigint

Apr 17, 2015

This is a common error for SQL Server, but I got it in a uncommon way.I have a table called - tblIDNumber where there are two columns - IDN_Number [NVarchar(200)] and Temp [BigInt]

If I run,
SELECT  *
FROM    dbo.tblIDNumber
WHERE   IDN_IDNumberTypeStaticValue = 33
        AND IDN_Removed = 0
        AND CAST(IDN_Number AS BIGINT) = 1

SQL Server give me the error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.

I first thought IDN_Number in type 33 has characters, but it doesn't, becasue the below query works!!!

UPDATE dbo.tblIDNumber
SET Temp = CAST(IDN_Number AS BIGINT)
WHERE  IDN_IDNumberTypeStaticValue = 33
        AND IDN_Removed = 0

To workaround, I ran the query,

UPDATE dbo.tblIDNumber
SET IDN_Number = '123'
WHERE  IDN_IDNumberTypeStaticValue = 33
        AND IDN_Removed = 0

and then I ran the first query, and SQL Server does NOT give me the same error - Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.

Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause?????

finally, I found the root cause to be an index that the first query uses :

CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber]
(
[IDN_Removed] ASC,
[IDNumberCode] ASC
)
INCLUDE ( [IDN_Number],
[IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

If I remove the index, the first query works without the error.

View 10 Replies View Related

Transact SQL :: Load JSON Type Data To Server

Jan 15, 2012

Tried loading JSON data to Sql Server  ? Sample format is given below..Don't see any easy way doing it except writing some C# code deserialize it.
 
[ {
  "name" : "peter_2.jpg",
  "createdDate" : 1259728960000,
  "lastModifiedDate" : 1308174976000,
  "Secondary" : [ {
    "Id" : 106275817,
    "Sid" : 1
   
[code]...

View 8 Replies View Related

Transact SQL :: Change Data Type In All Fields In Database

Sep 25, 2015

I want change all field in database to new datatype.I want change data from Small Integer to Integer but there are the relation in each table.

View 3 Replies View Related







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