Dear all,Tables: COMPANY: COM_ID, COM_NAME, .....PROPERTY: PRP_ID, PRP_NAME, PRP_DATATYPE_ID, PRP_DEFAULT_VALUE( nvarchar)COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUE(nvarchar)Use: Without adding new field the user can add new properties to thecompanies just by adding a new property in table PROPERTY and mapping thenew property to a companie. CPRP_VALUE contains all kind of datatypes but isstored as text.Problem: when I query the database ( SP, views, etc) I have problems withfloats and date bacause in the interface ( Access2000.adp) :[color=blue]> the float-format is 0,11 and in TSQL is 0.11>the date-format is DD/MM/YYYY and in TSQL it is YYYY-MM-DD[/color]Can I convert the data within the Stored Procedure for selecting and sortingon those fields.for example: How to sort on CPRP_VALUE containing floatvalues stored asnvarchar ??The client wants to stay with the comma-format because it is common-usedhere.The format can be TSQL-format in the resultset of a Stored Procedure withoutchanging the format in the database because this is used for the output tothe clients.[color=blue][color=green][color=darkred]>>> Same problem with dates !!![/color][/color][/color]thanks,Filip
i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...
and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ? if any article to read more about these thing,, can you refere to me...
I’ve created a script which will do the following.
Update fields in a database which contain instances of an order number. An order number is defined as a 10 digit numeric sting which beings with 998. The first 3 digits of the order number need to change from 998 to 999. There are two types of fields to update: document number fields which may contain 1 instance of the order number and free text fields which may contain multiple instances of an order number.
I created a function which accepts the text to be updated, the text to find and the text to replace it with. The function then loops through the sting for instances of 998. For each instance it finds it checks to ensure that it is at the start of a 10 digit string which contains only numeric numbers – if this is the case then it will update the 998 to be 999. If the field is free text then it will continue to loop through the string (it will skip forward 10 digits for every order number found) until the end. If the field is not free text then it will exit the script after the first instance found which matches the above criteria, if any.
Need achieving this via set processing and not having to loop through every line. I’ve included the function below and some test data.
--Create the function we will call in order to do the replace if object_id(N'OrderReplace',N'FN') is not null drop function dbo.OrderReplace; go create function dbo.OrderReplace ( @Textnvarchar(4000),
I've got an nvarchar(max) column that I need to transform with some simple text processing: insert some markup at the very beginning, and insert some markup just before a particular regular expression is matched (or at the end, if no match is found).
Since the SSIS expression language doesn't support anything like this, is a Script Component the only way to go? Does Visual Basic .NET provide regular expression matching?
From the BOL, what I understood is, Text Datatype can accept more characters than 'char' & 'varchar' datatypes. There is no limitation like 8000 characters(what we hv in 'char' & 'varchar').
But I am surprised I am not able to add more than 8000 characters to my text datatype. Can anybody guide me how to do this?. Any help is appreciated.
i have a column type text i want to perform split functionality that is not currenctly availablein mssql server 2000.
to achieve this i have to used len() , left() right() and substring() functions incase of varchar datatype.. but len(), right() and left() functions are not supported for text datatype....
I have to create Sum() in SSRS for my report. My field data type is TEXT and i can not create sum function in ssrs. I can not make change to data type since it is linked table in access database. Is there any way to sum text data type???.
I have a problem within a procedure I am working on. I would like toprocess every column in a table in a cursor. Now to my problem, Ican't save a column of type text into a cursor variable. It's notallowed to use datafields of type text in this context. I also thinkthat I runned in to a simular kind of problem when trying to use textcolumns in triggers.Is there a way to evade this problem or is the only solutions to putthe text colum value into a varchar(8000)?Regards,Jenny
Hi, I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000. The string I need to import is composed by 5 different several blocks and looks like:
The detail of the SQL string is at: http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
I am trying to implement OJ's suggestion: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1 to use multi - batch processing to import the string to SQL SERVER, something like:
Code Snippet Dim SqlCnt, cmd1, cmd2, cmd3 'set the properties and open a connection
cmd1="use my_db" cmd2="create table mytb" cmd3="insert into mytb"
Hello, I am writing a sproc and am getting this error: Any ideas? Thanks!!Msg 402, Level 16, State 1, Procedure InsertUserPreferences, Line 18The data types text and text are incompatible in the equal to operator.-------------------------------------------------------------------------------------------------------------------create procedure InsertUserPreferences(@PublisherServer text)asbeginif exists(Select Preference_StringList from USER_Preference where Preference_StringList = @PublisherServer)begin--UPDATEexec dbo.uProc_USER_Preference end
I need to replicate a table with datetime,char and text datatypes.Is it possible to replicate text datatypes?What is the alternate if not?It should be merge replication.Please reply. Thanks.
I am trying to use the TEXT datatype for a column to hold large amounts of ascii data.
Creating the table from script I use: create table mytable ( firstcolvarchar(30) not null, secondcolTEXT not null ) go
The field will not accept strings in length over 16 chars. I looked at the table design in the enterprise manager and it shows 16 chars as the width.
I tried creating the table with TEXT(32000) but I get "The size (32000) given to the column 'secondcol' exceeds the maximum. The largest size allowed is 8000." I thought that TEXT fields are what you use when the length is over 8000. What do I need to do to enable this type of data storage? thanks in advance.
OK, I`ve been researching the use of the TEXT datatype all day and would like opinions on what I`ve found.
First, a little background. I have been tasked with writing an ASP application to handle the display of FAQs for a company`s products. I would like to store all info in a table much like
faqID int question TEXT answer TEXT
Simple enough, right? I then tried to create a stored procedure to add a new FAQ and all hell broke loose. ASP would not pass anything larger than 255 chars to the stored procedure.
I read in the "ADO and SQL Server Developer`s Guide" from Microsoft about using varchar datatypes of 255 chars (instead of TEXT) and chunking large text up to fit in these smaller datatypes. This seems like a lot of work.
I also read in "Inside SQL Server 6.5" that "The text datatype is sometimes awkward to work with. Many functions don`t operate against text, stored procedures are limited in what they can do with text, and some tools don`t deal with it well." (page 632). This statement concerns me greatly. How are stored procedures limited in dealing with TEXT? Do the standard SQL UPDATE and INSERT commands work or must READTEXT and UPDATETEXT be used instead?
I guess my question is, what is the best way to accomplish this? I have a feeling that others have had to do this before. Is SQL Server not meant to handle large textual objects? Is chunking the best way to go? Will version 7.0 handle this scenario better?
I'm trying to do a simple search for any record that has an empty 'dsc' field. There isn't a NULL character in there, just a SPACE character. The 'dsc' field is a description field so it's datatype is TEXT.
Code:
SELECT id FROM leads WHERE dsc<>''
Normally I can do a VARCHAR(50) like that and get the results I'm looking for. When I do this on a TEXT datatype I get this error:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I I tried a couple of things, but it's not getting me the correct results.
Code:
SELECT id FROM leads WHERE dsc NOT LIKE '% %' SELECT id FROM leads WHERE dsc NOT LIKE '% ' SELECT id FROM leads WHERE dsc NOT LIKE ' %'
Please shed some light on this.. thanks!
Edited @ 12:08 PM After some research I found an unconventional way of beating the system. If you have a more conventional way I would still like to see it. A quick fix is using DATALENGTH() function to compare the data. Basically if there are any characters besides NULL and space then the data length will be more than zero.
I have a number of bit datatypes ( Boat types: Cruiser, Sportfisher, Megayacht, Sailboat) that I would like to place in a text box and do away with the individual selections. For instance, some marinas cater to "Cruiser", "Megayacht" and "Sailboat" while others include the "Sportfisher" also and there are many other combinations of vessels. I am stumped at how to write a query that takes the existing "True" values for each boat type and places them in a text box in the form of " Cruisers, Megayachts, Sailboats" .
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.
I have the following form in this adress:(it's framework 1.1 asp.net vb.net)http://admin.artemrede.pt/login.aspx?ReturnUrl=%2fdefault.aspxthe utilizador is: testepalavra passe is: 12345I'm using some text datatypes and some very big varchars the problem is that when you try to add a new record or to edit a existing record, if for example in one of the very big varchars or text, textboxes, several lines of text, It only puts in the database the 2 first words that you write. I used the VS debug and apparently everything looks ok......(if it would I probably hadn't this error....) Dim OurConnection As SqlConnectionOurConnection = New SqlConnection(conn_default)Dim OurCommand As SqlCommandOurCommand = New SqlCommand("Insert Into espectaculo (foto_destaque, thumb, area_prog, nome_espectaculo, coord, nome_comp, duracao, f_etaria, sinopse, iterancia, ficha, bio_interv, bio_comp, link_comp, notas_imprensa) Values (@fotod, @thumb, @areap, @ne, @coord, @nc, @duracao, @fe, @sinopse, @it, @ficha, @bioI, @bioC, @link, @notasp)", OurConnection)If (foto_destaque.Text = "") ThenOurCommand.Parameters.Add("@fotod", SqlDbType.VarChar, 12).Value = " "ElseOurCommand.Parameters.Add("@fotod", SqlDbType.VarChar, 12).Value = foto_destaque.TextEnd IfIf (thumb.Text = "") ThenOurCommand.Parameters.Add("@thumb", SqlDbType.VarChar, 12).Value = " "ElseOurCommand.Parameters.Add("@thumb", SqlDbType.VarChar, 12).Value = thumb.TextEnd IfIf (area_prog.Text = "") ThenOurCommand.Parameters.Add("@areap", SqlDbType.VarChar, 50).Value = " "ElseOurCommand.Parameters.Add("@areap", SqlDbType.VarChar, 50).Value = area_prog.TextEnd IfIf (nome_esp.Text = "") ThenOurCommand.Parameters.Add("@ne", SqlDbType.VarChar, 100).Value = " "ElseOurCommand.Parameters.Add("@ne", SqlDbType.VarChar, 100).Value = nome_esp.TextEnd IfIf (coord.Text = "") ThenOurCommand.Parameters.Add("@coord", SqlDbType.VarChar, 100).Value = " "ElseOurCommand.Parameters.Add("@coord", SqlDbType.VarChar, 100).Value = coord.TextEnd IfIf (nome_comp.Text = "") ThenOurCommand.Parameters.Add("@nc", SqlDbType.VarChar, 50).Value = " "ElseOurCommand.Parameters.Add("@nc", SqlDbType.VarChar, 50).Value = nome_comp.TextEnd IfIf (duracao.Text = "") ThenOurCommand.Parameters.Add("@duracao", SqlDbType.VarChar, 25).Value = " "ElseOurCommand.Parameters.Add("@duracao", SqlDbType.VarChar, 25).Value = duracao.TextEnd IfIf (faixa.Text = "") ThenOurCommand.Parameters.Add("@fe", SqlDbType.VarChar, 50).Value = " "ElseOurCommand.Parameters.Add("@fe", SqlDbType.VarChar, 50).Value = faixa.TextEnd IfIf (sinopse.Text = "") ThenOurCommand.Parameters.Add("@sinopse", SqlDbType.VarChar, 8000).Value = " "ElseOurCommand.Parameters.Add("@sinopse", SqlDbType.Text, 16).Value = sinopse.TextEnd IfIf (itener.Text = "") ThenOurCommand.Parameters.Add("@it", SqlDbType.VarChar, 200).Value = " "ElseOurCommand.Parameters.Add("@it", SqlDbType.VarChar, 200).Value = itener.TextEnd IfIf (ficha.Text = "") ThenOurCommand.Parameters.Add("@ficha", SqlDbType.Text, 16).Value = " "ElseOurCommand.Parameters.Add("@ficha", SqlDbType.Text, 16).Value = ficha.TextEnd IfIf (bio_interv.Text = "") ThenOurCommand.Parameters.Add("@bioI", SqlDbType.Text, 16).Value = " "ElseOurCommand.Parameters.Add("@bioI", SqlDbType.Text, 16).Value = bio_interv.TextEnd IfIf (bio_comp.Text = "") ThenOurCommand.Parameters.Add("@bioC", SqlDbType.Text, 16).Value = " "ElseOurCommand.Parameters.Add("@bioC", SqlDbType.Text, 16).Value = bio_comp.TextEnd IfIf (linkComp.Text = "") Or (linkComp.Text = "http://") ThenOurCommand.Parameters.Add("@link", SqlDbType.VarChar, 100).Value = " "ElseOurCommand.Parameters.Add("@link", SqlDbType.VarChar, 100).Value = linkComp.TextEnd IfIf (notas_press.Text = "") ThenOurCommand.Parameters.Add("@notasp", SqlDbType.VarChar, 5000).Value = " "ElseOurCommand.Parameters.Add("@notasp", SqlDbType.VarChar, 5000).Value = notas_press.TextEnd If OurConnection.Open()OurCommand.ExecuteNonQuery()OurConnection.Close()carrega()'grid1.DataBind()End IfEnd Sub Sub carrega()conn_default = ConfigurationSettings.AppSettings("ArtemredeConnection")Dim OurConnection As SqlConnectionOurConnection = New SqlConnection(conn_default)OurConnection.Open() Dim OurCommand As SqlCommandDim SelectCommand As StringSelectCommand = "select id_espectaculo, area_prog, nome_espectaculo, nome_comp from espectaculo"OurCommand = New SqlCommand(SelectCommand, OurConnection) Dim Select_DataAdapter As New SqlDataAdapter(OurCommand)Dim Select_DataSet As New DataSet'Dim SP_DataTable_Rowcount As IntegerSelect_DataAdapter.Fill(Select_DataSet, "Espectaculos")grid1.DataSource = Select_DataSetgrid1.DataBind()OurConnection.Close() End Sub
I've created a stored procedure that converts an input string in richtext format (input as type TEXT) to plain text. I would like to be able to return this newly converted string, but I need to have some way of storing it in a local variable. My problem is that since I can't use the TEXT datatype as a local variable, I have no way of storing the large amounts of text I converted within the procedure. The VARCHAR(8000) just isn't large enough for my purposes. Anyone have any suggestions on how to go about doing this?
I require a column to be created with CHECK constraint NOT NULL which has TEXT datatype. But SQL Server will not allow CHECK constraints for the columns which has TEXT datatype. How can I solve this problem ? Is there any other alternative for this ?
Hello. I using a simply SELECT statement to retrieve some data from aSQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXTbecause the amount of data. Anyway, my SQL statements worked just finewhen I was using VARCHAR, but now since I am using TEXT, I am onlyreceiving part of the content back. Do I have to do some sort ofspecial Casting or something if I want to get all the content back?It's over 8,000 characters. Thank you very much. I have racking mybrain on this for a while.
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
I have a table with one column of text data type. This table contains around 1200 records(each row is 60 characters only). My task is, I have to append all these records into a single record(Concatenation of records from other table) to another table.
For that I have created table(destination table) with one column of text datatype. Using the 'UPDATETEXT' function I am able to append 133 records(each row of 60 characters) from the other table into a single row of my destination table. After that I am not able to append my records further. It is giving the following error.
" Server: Msg 7135, Level 16, State 4, Procedure gene1_proc, Line 26 Deletion length 60 is not in the range of available text, ntext, or image data. The statement has been terminated. " Why I have created 'text' instead of char or varchar datatype is, it can accept more than 8000 characters. But here in my case it is not accepting more than 8000 characters. The problem is coming from 134 (133*60 = 7980 characters) records onwards.
That's right. My developers want full-text searching implemented on an encrypted XML datatype...on SQL Server Express, no less. Whether this can be done seems to boil down to four questions: 1) Can the XML datatype be encrypted? 2) Can the XML datatype be full-text indexed? 3) Can encrypted data be full-text indexed? 4) Can encrypted XML data be full-text indexed? So I'm diving into the documentation now, but if anybody out there already knows whether the answer to any of these questions is "No", please save me some time. Oh, and they also want to substitute potato nuggets for fries, and biggie-size the whole thing.
