I have a table with a TEXT column and this column contains an XML document. I'm developing a TSQL stored procedure which reads the content of this column and accesses the values in the XML elements but I have a lot of problems...
1) How can I read the text column and store the value in a variable? I tried
declare @a varchar(2048)
set @a = (SELECT TEXT_COLUMN FROM MY_TABLE)
but it returns
Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
I tried also with the READTEXT function but I just can't find how to store data read in a variable...
2) which length should I use for the VARCHAR variable which will store the data? Is it possible not to specify a length with SQL Server 7 or 2000?
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'. ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
When I import data from MS Access databases, some fields appear as "<Long Text>" in SQL. I cannot edit these fields in SQL. I have to export the table to Access, update the field, and then import again.
There's got to be an easier way. What am I missing here?
Hi, I am mapping the .csv files to a destination table in sql server. The names of each column in the .csv file is the same as the ones in the destination table. In the destination table there is an extra field i.e. FileTypeField
Here is the question; When transfering the data from .csv to the destination table i.e flat file source to oledb destination, I would like to place a text into the field FileTypeField I do not see an option to do this. Can you help please? Thanks
HiI have to create an XML file based on a SQL SERVER 2005 table.Everything works fine:SELECT 1 as Tag, NULL as Parent, Ex_Id as [Exam!1!Ex_Id], NULL as [Ex_Title!2!!cdata] FROM Exams WHERE ex_State = 'P' UNION ALL SELECT 2 as Tag, 1 as Parent, Ex_Id, ex_Title FROM Exams WHERE ex_State = 'P' order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata] FOR XML EXPLICIT, ROOT('Exams') BUT when i add another column, a text column, I get into trouble: SELECT 1 as Tag, NULL as Parent, Ex_Id as [Exam!1!Ex_Id], NULL as [Ex_Title!2!!cdata], NULL as [Ex_Situation!3!!cdata] FROM Exams WHERE ex_State = 'P' UNION ALL SELECT 2 as Tag, 1 as Parent, Ex_Id, ex_Title, ex_Situation FROM Exams WHERE ex_State = 'P' order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata] FOR XML EXPLICIT, ROOT('Exams') The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosurePlease help me....i'm desperate :(
Hi I had a text type not null column which i wanted to change to a null column.Writing a simple alter statement gave me an eror cannot change text type column so i tried to rename the original column create a new column with the same name and allowing nulls on it and then copying the contents of the renamed column to the new column and finally deleting the renamed column. EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'ALTER TABLE TableName ADD ColumnName text NULLUPDATE TableName SET ColumnName = ColumnName_oldALTER TABLE TableName DROP COLUMN ColumnName_old However when i tried to execute these statements in query analyser on the Update statement it gave me the error that ColumnName_old does not exist. However then I tried to execute these queries one by one I was able to do that. Can anybody tell me whats causing the queries to not be executed all at once without giving the ColumnName_old does not exist error cause I wanted to run them on live dbs.
how can i update a column with datatype of text with a combination of columns having a datatype of float? do i convert the float columns to varchar/char/?? and/or can i convert the column i am updating?
What would the syntax be to insert a column to the right of this one, and extract the first 8 digits from the data in the DATEID column and insert that into the new column DATE, therefore making it easier for me to query against an actual date?
Hi all i want to remove text from my column name using query. for example i have the product name like "silver 8' trampoline pack " i need to remove "silver 8' " and want to display only trampoline pack similarly if I have product name like "gold 8' trampoline pack" i need to display only trampoline pack. can anybody help me in this regard?
Hello,If I create a table with two columns of type "Text" will that cause anyissues? If my memory serves me correctly I thought I read a while backthat is it standard practice to only have one "Text" column per table.Any input would be appreciated.Thanks,Rob Panosh
I need to setup repliaction between A and B. I have high transaction /min count. Some tables have TEXT columns. This is what i am planning to do:
Example: table name is CREDITS
vertical partition the table into two. First table (will be called CREDITS_PRI) holds PK column and non TEXT columns, second table (will be called CREDITS_SEC) holds PK column and TEXT columns.
Create a view called CREDITS with INSTEAD OF triggers for inserts, updates and deletes. So far so good. Then setup replication with immediate updating subscription between A and B for tables called ..._PRI and merge replication between A and B for tables called ..._SEC.
Would this work? How do other companies handle this? thank you in advance for pointing me into the right direction.
I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.
Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?
Hi, We have moved our Access 2003 database to SQL Server 2005. A number of the original tables contained MEMO fields and these became nvarchar(MAX) fields in SS 2005. We continue to use the original Access application and therefore have linked the tables to the Access application via ODBC connection. However, the fields that were originally Memo in Access native tables are brought over as TEXT fields (255 char limit) by the linking process.
Is there a way I can link the tables and have those big text fields be Memo fields in the linked tables?? Or is there some way to work around this? (Moving to Access 2007 is not an option at this time.) I would really appreciate any suggestions! Thanks.
The query fails to execute and returns an error: String[1]: the Size property has an invalid size of 0. If I change the SqlDbType.Text parameter type to SqlDBType.Varchar, 100 (or any other fixed varchar length), it works but limits the length my unlimited field text. Any suggestions on how I can use db type text or varchar(max)? The field I need to retrieve is string characters of unlimited length and hence the datatype varchar(max).
Hi,How do we use like when we have a column of type TextSelect * from where myColumn LIKE 'prefix%'where myColumn is of type Text in SQL server 2005Thanks.
hi i have a table with a column which contains a large paragraph of data. i need to search for a pattern within this text and update it.
for example the column contains 4096 char. within this text, i search for a pattern"qwerty" and convert it to "asdfgh". any ideas how this can be done? i'm using sql server 2000 i have to write something like UPDATE [DB].[dbo].[Table] SET [Column1] = '.............', WHERE .......... any help will be appreciated.
I m using SQL Server 2000. I have Tabel named Topic and have a column name lineage. lineage has data like following: //////546707//546707//546707/43213/ Now I want to get records who has only one "/" in it's crreponding lineage column. Can somone tell me how to do that in SQL Server 2000? Thanks Khushbu
Hi Everyone, I have a simple webform with a text box with multiline enabled and a submit button, in vb.net. Inside the text box, when you are typing text with let's say adding carriage returns, spaces etc. How to i save the text to a database? By that, i mean, how to i declare the column in my database, varchar, text ...? Also, when retrieving the saved text back to the textbox, will it keep the formatting because that's what i am trying to do? Or, what is the best way to keep text formatting in a database? Thanks for the help, Kevin
Hi DBAs, I am very new in SQL server. I created a table where one column is varchar(8000). But when I am trying to insert value from enterprise manager this column cann't accept a long text value. I counted that its' capacity is 1012 charecters. I have tried a lot but don't know how to solve this. I really need help from you. Pls help.
A developer created a stored procedure that search in a huge table in a column with ‘like’ statement. I know that the best solution in most case is use of full-text.
But the content of this specific column is a XML data and Full-text don´t find words as desired.
For example: Table content: ID = 1 DsColumn = ´<Name>BETH</Name>´
select * from tbResp where DsColumn like '%BETH %' Results: IDDsColumn --- --------------------------------- 1<Name>BETH</Name> 1 row(s) affected
select * from tbResp where contains(DsColumn, ' BETH ')
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.
Hi Everyone, I´m new to this group, I´m trying to write a text filesadding content from a text column (more than 8000 characters), I foundcode how to write files and it works but i have the problem when addedthe text column to the body of the file.any idea? tip? thanks in advance! Pablo.
