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
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?
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.
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.
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.
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.
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.
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.
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?
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.
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?
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
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?
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.
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
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
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.
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
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.
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"?
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)
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
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?
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?
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?
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.
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
