2 Parter: Constraints And Text Fields
Apr 17, 2001
in SQL 7, can i set up a constraint or rule where a data entered in [field 1, table a, database 1] has to exist in [field 3, table a, database 2]?
i know i can't set up a relationship, and i haven't been able to get a constraint or rule to work either.
if i can't do the above, what is the recomended way to do this?
i don't want to, nor should i have to duplicate the table.
also, when importing data from one sql server to another, any field that was a data type of text came across as null?
any help would be great
thanks
spiralstairs
View 1 Replies
ADVERTISEMENT
Nov 17, 2005
Ok, so I've broken down and decided to write myself an invoicing
program. I'd like to use GUID fields as the Primary Keys in my
table. Now, I basicly bill for two seperate items:
Deliverables and Services.
So, my Layout's gonna look something like
Create Table Invoice(
ID UniqueIdentifier Primary Key,
-- Other Data
);
Create Deliverable(
ID uniqueidentifier Primary Key,
ParentInvoice uniqueidentifier,
-- Other data);
--...
Im sure there are probems with that as it's written feel free to edify me as to what they are.
my questions are such:
1) Does a uniqueidentifier field automagically get a value?
2) If not how do I generate one in TSQL?
3) If so, what do I use to store my Foreign Keys.
4) How do I declare my Foreign key constraints?
View 12 Replies
View Related
Sep 7, 2007
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
View 2 Replies
View Related
Jan 22, 2008
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
View 9 Replies
View Related
Sep 25, 2007
I have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.
View 2 Replies
View Related
Jan 9, 2007
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
View 3 Replies
View Related
Jan 17, 2008
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
View 7 Replies
View Related
Mar 2, 1999
Hey everyone,
using SQL 6.5 sp_makewebtask in a stored procedure I want to create a
press-info file.
My query calls four fields, a datum field, two varchar fields and a text
field, it's
the field "PrText" in my example:
EXEC sp_makewebtask @outputfile='F:EntwicklungRedaktionZeitwenden27.ht m',
@query='SELECT Datum, Ort, Headline, PrText
FROM news
WHERE ID = 27',
@templatefile='F:EntwicklungRedaktionZeitwendenTem plates.htm'
My question: Am I limited to 255 characters in this case? After 255
characters the text is cut off and
I have only a truncated text fragment.
Is there a possibility to get the whole text without using @blobfmt (I don't
want to use this parameter because
I want to have the datum data, the varchar headline AND the text in ONE
HTML-file.)
I have tried the same as dynamic query in ASP where I have no problem to get
the whole content of the text field, but I want
to create a static file because this information will be read often, but
won't be updated frequently
and I don't want to charge the server resources to much.
Has anybody an idea? Any help would be appreciated.
Thank You
Dorothee Wiethoff
View 1 Replies
View Related
Sep 23, 2004
I'm unable to run a Union query with text fields in it. I get the following error:
'The text, ntext, or image data type cannot be selected at DISTINCT'
Is there no way to run a UNion statement for two tables that have text fields in them??? Please help.
View 4 Replies
View Related
Mar 16, 2006
The Text datatype can hold several million characters
The size of a record can be no more than 8 KB in normal data.
Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.
Why is SQL Server 2000 doing this and more importantly how to resolve?
View 8 Replies
View Related
May 10, 2004
Hi, I'm sure that this is probably a really easy question to most of you, but I seem to be spending ages on it as I am new to SQL. how do I add the carraige return char between joining two text fields ie.
a_blah.blah+CHAR(13)+b_blah.blah
Thanks for your help.
View 14 Replies
View Related
Mar 29, 2006
New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
Please advise - thanks!
View 8 Replies
View Related
Jul 23, 2005
I've just gotten my data into SQL Server, and changed the connection stringon the program.There was a memo field (Access 2000) and we put that in a text field (with adefault length of 16?).Now, when displaying the data online (through asp pages), there are bigwhite-space gaps in the pages (that display the memo field data), imageshave a "double" url(http://www.mysite.com/thisfolder/ht...thelinksite.com)there are lots of place with " ".The field data has html tags in it, if that has anything to do with it.It seems like I've heard that there are issues with text fields. Is thiswhat they meant? Is there anything I can do?Thanks, JA
View 4 Replies
View Related
Jul 20, 2005
I am trying to update text fields where the field only has the value"closed." So a field that has closed and anything else will not beupdated.update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed_')update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed%') and len(convert(varchar(7000),notes)) = 7Neither code works and i get this error: "Subquery returned more than1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.The statement has been terminated."What's another way to make the update?
View 1 Replies
View Related
Dec 10, 2007
I have the following data.
CustomerID Address
1 Spencer St
2 Frank Way
3 Jolly Dr
and i need to extract the address as 2 seperate fileds like so,
CustomerID Street Suffix
1 Spencer St
2 Frank Way
3 Jolly Dr
keep in mind that the Customer table has 2 million records.
View 5 Replies
View Related
Dec 18, 2007
Hi All,
I am trying to get at some text fields from an AS400(JBA) system. the problem i'm having is that for each new line of text on AS400 it creates a new row with a line number associated in the SQL table. When trying to export to excel this causes problems because i need the text in one cell not spread over 15/20 lines.
I need to create a query/function that will concatenate the text lines together. I'm fairly new to T-SQL so could do with some help.
Example:-
ID TxtLn Text
R262965
1
Text 1
R262965
2
Text 2
R262965
3
Text 3
What i'm aiming for is:-
ID ConcatText
R262965 Text1 Text2 Text3
Can anyone please help me or guide me towards a starting point.
Thanks
View 2 Replies
View Related
Nov 14, 2006
Hi,
I am saving a couple of paragraphs of text into a varchar(max) field in a SQL database table. But when I try to use display the information again, then all the linefeed and return characters seem to be removed. How do I keep this text formating in the database field?
I am sure that I am missing something simple here, but I can not figure out what. Can someone please help with some advise?
Thanks
RegardsJan
View 6 Replies
View Related
Jul 31, 2007
I have three user tables (identical structure) that holds customer information. Each has about 1200 records, give or take a dozen, 99% of which are identical. However, there are a few records in each table that differ. Im trying to select the differences in these tables using a query.Each customer has a custmer ID, but cust_id 1234 in table a may not be the same customer as cust_id 1234 in table b. Therefore, i am trying to compare using the cuLoginName field which is the username/email address text field. This is what differentiates the records.
I have tried using
select cust_id, cuLoginNamefrom tblCustomerAwhere NOT exists (select cuLoginName from tblCustomerB)
to get the different records between the two tables (i.e. those in A but not in B), but even though there are two users in A that do not appear in B, the results are coming back with nothing. I am guessing this is a problem comparing on text fields?
What is an effective way around this problem?
Thanks
View 3 Replies
View Related
May 30, 2004
Hi,
I am trying to do a bulk insert using a text file that is comma delimited.
In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.
Is there a way to tell the BULK INSERT statement not to treat these as delimiters?
thanks,
sivilian
View 2 Replies
View Related
Oct 11, 2000
Hi:
I'm trying to transfer a table from SQL Server 7 database to another SQL server 7 database on
another server. This table has a text field with lots of data (~.5-1 G). I'm using the export wizard
and the transfer appears to complete successfully, but when I view it, the text field data has
been truncated.
Any ideas?
Thanks, Nicole Lane
View 1 Replies
View Related
Dec 23, 1998
I have been trying to write a query that will allow me to search through text fields. This is a problem because SQL doesn't let me use any functions on TEXT datatypes.
When I was using access I did it like this:
where upper(searchtext) like ('%SEARCHSTRING%')
However UPPER doesn't work on text fields (I want the search to be case insensitive)
I tried this:
where patindex(searchtext, '%SEARCHSTRING%') <> 0
but that is not case-insensitive...
Help me SQL gurus, you are my only hope
View 1 Replies
View Related
Jun 25, 2006
Hi
I defined the following table in MSSQL 2000
sql Code:
Original
- sql Code
CREATE TABLE saved_query (
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(32) NOT NULL,
query_text VARCHAR(2048) NOT NULL
)
CREATE TABLE saved_query ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(32) NOT NULL, query_text VARCHAR(2048) NOT NULL)
and whenever I insert or update the query_text field, it's always truncated to 255 characters. Why? I clearly specified a larger field size. I also tried defining query_text as a "TEXT" but got the same result.
Thanks for any help
Ken
View 2 Replies
View Related
Oct 11, 2005
I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.
For example
Code:
SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'
FROM Table1
However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.
View 2 Replies
View Related
Oct 25, 2004
I'm using a view where I do a few things, including merge two short text fields into one. Just wanted to know if there was an alternative way of doing this:
Example:
SELECT first_name + ' ' + last_name AS full_name
FROM names
It works - but it seems rather blah. Is there something that'd be more "proper"?
View 3 Replies
View Related
May 29, 2008
I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '
' WHERE Column_TEXT = 'Some text'
I also tried
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'
But I keep getting the error
The data types text and varchar are incompatible in the equal to operator.
Help!!
Thanks in advance
Mangala
View 3 Replies
View Related
Oct 12, 2006
Hello,
I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.
Something like - Note: the codes below doesn't work!!
DECLARE @ResultID as int
DECLARE @AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA
FETCH NEXT FROM _rows INTO @AccID
WHILE (@@fetch_status <> -1)
BEGIN
UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @AccID)
WHERE AccID = @AccID
FETCH NEXT FROM _rows INTO @AccID
END
Thanks in advance
View 8 Replies
View Related
Jan 1, 2007
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
View 7 Replies
View Related
May 26, 2006
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
View 1 Replies
View Related
Feb 8, 2007
I am using MS SQL Server 2000.
I have 4 text fields
1. event begin date
2. event begin time
3. event end date
4. event end time
I have to find the day and time difference in months days hours and minutes between the event begin date & time and the event end date & time – The event begin date and event end date are char(8) & event begin time and event end time are char(4) – with military time. I would need to combine the event begin date & event begin time to get the event begin date & time and the same for the event end date & time.
If the values of the 4 text fields are
event begin date = ‘20070207’, event begin time = ‘2015’, event end date = ‘20070208’, event end time = ‘0105’, the difference should be 0 month 0 day 4 hours 50 minutes.
Can somebody please help with MS SQL Server 2000 syntax?
View 1 Replies
View Related
Apr 5, 2001
I have an idea to use LIKEW opeartor (with te wildcards) to match large (>10Kb) text fields of 'text' and 'ntext' types. Are there known problems here?
Thanks
View 1 Replies
View Related
Sep 5, 2014
I have a text field that I need to parse out into 4 fields.
30.125x23.625-18.875x25.375
6.1875X19.375-2.4375x15.625
0X0-0x0
26.875X11.375-11.125x22.875
0X0-0x0
0X0-0x0
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
26.625X14.875-11.125x22.875
26.625X14.875-11.125x22.875
I need to 26.625X14.875-11.125x22.875 should be 26.625 then 14.875 then 11.125 then 22.875
View 5 Replies
View Related
Dec 14, 2014
How can we create a DB for a single attribute such as ORDER DETAILS, CASH RECEIPT, TAX INVOICE having more than 2 text fields.
Also, in every form attribute such as order id is not present - in order identify the same as a primay key. So, which other attributes or fields can be considered as a primary key.
View 5 Replies
View Related
Sep 12, 2006
As we all know, there is a 8060 bytes size limit on SQL Server rows. Ihave a table which requires a number of text fields (5 or 6). Each ofthese text fields should support a max of 4000 characters. We currentlystore the data in varchar columns, which worked fine untill ourappetite for text fields increased to the current requirement of 5, 6fields of 4000 characters size. I am given to review a design, whichesentially suggests moving the text columns to a separate TextFieldstable. The TextFields table will have two columns - a unique referenceand a VARCHAR (4000) column, thus allowing us to crossreference withthe original record. My first impresion is that I'd rather use the SQLServer 'text' DB type instead, which would allow me the samefunctionality with much less effort and possibly better performance.Can anyone advise on advantages and disadvantages of the two optionsand what the best practice in this case would be.Any advise will be well appreciated.Tzanko
View 7 Replies
View Related
