You have helped me resolved my previous problem with the LIKE statement, and now I'm running into this TEXT STRING problem that I desperately need your help and guidance again.
The following is the set of various descriptions in a PRODUCT_DESC field. I need to be able to calculate the Squared Meter of these products. As you can see, I need to be able to extract the part in the middle (like 2x60YD, etc.) for each record and perform some sort of calculation and conversion. The problem is that I can't find a way to do this effectively. Can someone please help me? Thanks.
I am trying to build a simple search engine using Sql Server 2000 to scan information about approximatelly 20.000 products.
Heres what I am doing:
I created a table called keywords that contains a reference for each product.
keyword -> varchar(100) items -> Text
keyword data example:
[keyword] [items] car 1, 3, 5, 7 blue 3, 5 compact 1,7
I am not using clustered index.
To search basically I run the "AND" or "OR" to select the keywords I want to target. I need to run another select that would compare the data in the items field depending of the condition selected. If "AND" clause is used I would need to compare all the items that contains the same reference, for example:
looking for car compact using "AND" clause result = 1
looking for car compact using "OR" clause result = 1,3,5,7
There is no table that holds references. The items are stored in a text field in the keyword table. I can compare data using script like AsP spliting the items by comma or space, but that can be too slow and use up a lot of RAM. Another solution would be to use a table to hold the references but that would affect performance dramatically because of the large number of records created and storage space used. One example, if I have 60.000 keywords and each keyword has an avereage of 200 references, I would have to generate 12 milion records.
I want to know if there is a function or routine in SQL server to compare matched references on the fly in the server between two or more fields and how should I do it.
In addition, in this scenario, how should a clustered index help?
I am trying to build a query on a SQL2000 text field which contains XMLstring. The query is like "select requestnumber from history whererequestnumber is like '%re1%'". As you can see in the following samplerecords, the xml string has database structure and the requestnumber isa node of the XML. I wonder if it is possible to have SQL server parsethis field and allow me to do the query. If not, any suggestion wouldbe appreciated as to how to store XML data in SQL2000. I am not sureif I misused the SQL2000 XML feature correctly. So far I pass the rawquery result to ADO and manipulate it in XMLDOM.The table is to capture history of changes in any record in mydatabase. So I need to keep it simple so any record from any table canbe stored in here. The structure of the table is like this:sysObjectNumber(int, not null)recordKeyValues(char(30), not null)archiveTime(datetime, not null)history(text, null)A sample record would be like the following:sysObjectNumber recordKeyValues arvhiveTime History=============== =============== =========== =======1728725211 ABC 2005-03-25 8:09:56.700<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:01:14.407<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</usedby></history>1728725211 ABC 2005-03-28 11:46:12.723<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:46:35.273<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</usedby></history>
I have a table with the following structure for which I execute a SELECT statment against to retrieve the value of a particular NoteText field. Every time I execute the select the Notes are truncated in the returned recordset and I cannot get it to return the full value. I have tried various options, Cast/Convert, Set TEXTSIZE, etc but to no avail. Advice?
CREATE TABLE [dbo].[SOOrderNote_T] ( [SOOrderNote_TSK] [int] NOT NULL , [SOOrder_TSK] [int] NOT NULL , [PXNoteTypeSK] [int] NOT NULL , [NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--My Select which returns truncated notes Select NoteText From SOOrderNote_T where SOOrder_TSK = XYZ
This seems so simple but I can't find any examples to do it..
I have the following in a text file "Lastname, Firstname MI" and I want to select each part separetly.. What would be the SELECT statement to get the Lastname up to the comma, then the Firstname after the comma, then the MI after the last space..
Thanks for the help, and any reference to learn more.
I have a small problem that I can't figure out. Here's my table:
---------------------------------------- Members ---------------------------------------- ID | Name | Image ----------------------------------------
When a user signs up to become a member, they can upload an image, which will be stored under Image. It will also be stored in a folder. For example, if the user with the ID of 14 uploads an image, it will be stored in 'livefiles/Members/14/image.jpg'. If the user does not upload an image, NULL is stored under Image, and the user has a default image, which is stored under 'livefiles/MembersDefault/1/image.jpg'.
What I want to do is return the path of where the image is in the folders. Basically...
If Image is not null SELECT 'livefiles/Members/' + ID + '/' + Image AS ImagePath If Image is null SELECT 'livefiles/MembersDefault/1/image.jpg'
I'd rather not do this with an IF statement, I'm doing a lot of other SELECTS, and I don't want to have to copy all that code. Here's what I tried...
COALESCE (NULLIF('livefiles/Members/' + CONVERT(varchar(20), ID) + '/' + Image, 'livefiles/Members/' + ID + '/'), 'livefiles/MembersDefault/1/image.jpg') AS ImagePath
but that gave me an error saying: Conversion failed when converting the varchar value 'livefiles/Members/' to data type int.
HI All. I'm trying to tweak the Transfer Logins task to exclude Windows Logins that are local to the Server (e.g. servernameusername) which obviously can't be transferred off the server. Annoying that we have a couple of local Logins on this system instead of all Domain Groups, but we're stuck with them due to firewall issues, and a policy excluding SQL Logins.
My idea is to create a text file as part of my Package that lists Logins to be Excluded From the Transfer - I think I then need to create a New File Connection to the Text File as a Connection Manager, then somehow get that data into a Variable, and then use an Expression to populate the 'LoginsList' Collection from syslogins where loginame not equal to logins in my textfilevariable?
Or maybe I'm over complicating this, and there's an easier solution? Lots of info in Books Online about Expressions and Variables, but having trouble finding examples that I can use. As a DBA, this is my first foray into SSIS, and as you can possibly tell, I'm floundering....
I'm coming from ASP and I used to have a global connection string accessible to all of my ASP pages that I'd use for all of my data access. This was really convenient because I could easily switch to a backup or local data source for testing/debug by changing the connection string in one place.
Now, I'm using ASP.NET with Web Matrix and I love the drag and drop functionality but its dropping my connection string all over the place. How can I do this and keep my connection string in one spot so I can have the same convenience of switching data sources.
I'm creating an SSIS package, a Data Flow Task. I have used OLEDB Source connected to a SQL Server Destination. Now in my OLEDB Source, I have this SQL statement
SELECT FirstName, LastName, Age FROM Employees WHERE (Age > 10) AND (Age < 95)
But what I want is to have the last name and first name concatenated and in proper case(capitalize first letter of the firstname and surname). I also want to TRIM or remove the blank spaces of the field in my SQL statement. How I be able to do this?
I tried using proper(), trim() and ucase() like in MSAccess but no success.
We've just patched our Dev server, and, of our 3 servers (Dev, Test, Prod), we see major changes in the output of a raw data import process that runs nightly. Each night we import tables from a Remedy helpdesk system running on Oracle and place each ticket into a row on a table, tracking the changes and history of the ticket, etc. This includes tracking when supervisor groups are changed during the course of a ticket (ie Helpdesk to Data Comms to Billing etc). Now, after SP1, the results on Dev are skewed with partial strings showing in the From and To fields, broken in odd places (like the middle of words).
Has anyone noticed any changes in which post-SP1 SQL Server 2005 processes strings? Does it automatically trim spaces or convert NULLs etc?
The data import should be identical between Dev and the other servers.
Everything is set up the way it should be, as far as we can tell. However, when we drop in a SQL data source and try to configure, it, we get a blank box for choosing the connection string, and it won't let us make a new one.
Has anyone encountered this before? If so, how can we fix it?
Hello guys, I am a hobbyist programmer and now that I have started asp.net, I was wondering about the correct way to enter data into a table. For example, i have a table called players with a field called status. What should I code the status as? "Active" or "Retired" or 1 and 0? I thought using 1 and 0 would be better as far as database size, but it is pretty difficult to understand how to modify the gridview. I come from PHP and everything is a lot more accesible there as far as modification of output. Thanks in advance!
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
I am building a data warehouse. Some of the data comes from an AS 400 EPR system. I used the OLEDB connector when first pulling the data into SQL Server doing simple import data from table option. That worked great for getting the initial data load into SQL Server and creating the base SQL Server tables although it was excruciatingly slow (that was probably due to the transport from the AS 400).
Now, I need to get new records that are added to the AS400 side of things on a daily basis. For that, I was trying to use the OLEDB AS400 connector. However, I found that the OLEDB connector wouldn't work when I was trying to specify an SQL Statement for what to get; i.e., a simple query like Select * from TWLDAT.STKT where BYSDAT >= '2005-01-27' would simply not work. Found articles here explaining that it is probably a problem on the AS400 side of things and where people recommended using an ADO ODBC data reader source for this type of thing. So, I'm trying to implement that. However, I have a huge problem with it.
The original tables that got created were mapped to use NVARCHAR fields for character data. When the ADO ODBC data reader source accesses the AS400 data, it insists on interpreting the string type fields as being unicode strings and giving it a data type of DT_WSTR when what I need it to have is a plain old DT_STR data type. When the strings are interpreted as unicode strings, they cannot be converted in a way that allows the NVARCHAR fields to be filled with the data. The exact error message I get for all the fields that should wind up being nvarchar fields is as follows:
Column "BYStOK" cannot convert between unicode and non-unicode string data types.
Okay, so I try to change the data types in the ADO ODBC data reader to be plain DT_STR data types and I cannot do so.
Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?
Thanks in advance for any info. By the way, if there is a better way than the ADO ODBC data source to get at this data when I need to specify an SQL command, I would love to hear about it. Not wild about using ODBC in the OLEDB age.
Since my foxpro OLE driver has been rendered useless by service pack 1 for sql server 2005 I am forced to use the .net data provider for odbc.
I am importing a number of tables.. each time I add the DataReader Source to the dataflow and connected it to the OLE DB Destination I get a load of the good old "cannot convert between unicode and non-unicode string data types" errors...
So I'm having to do derived column transforms, for each and every column that it coughs up on.
Using expressions like (DT_STR,30,1252)receivedby to convert the "recievedby" column to a DT_STR,
Some of these tables have 100 string columns.. so I'm getting a bit sick of the drudgery of adding all these derivations...
Is there any way to tell this provider to stop deciding that the strings in the foxpro tables are unicode?
I am having data where there are empty string in the business keys which should be used for Slowly changing dimesnion type 2, how do i over come this as due to empty strings i am getting new rows even though the rows havent really changed.
example of data is name and salary are business keys
name salary age address dev 23 klddldldlk sdfg 24 34 kdlddlkd
when the same is given as input the row dev 23 klddldldlk is coming as anew row where it already exists how do i over come this
I've never used SQL for anything but simple copies and queries, and now I need to do something that's probably simple, as well, but I don't know how to start. I need to update values in a text field with new values from another table. I can do it individually with an update statement (Set [field1] = 'newvalue' where [field1] = 'oldvalue'). But I have 400 different values and a bunch of different tables that need to be updated, and can't imagine that's the only solution. I can make up a simple table that will have all the old values in one column, and the new values in the next. I need a statement that look at the old value, then fetch the new value from the table I made, and replace the old value with the new.
I've looked at various help files and tried to search for a solution elsewhere, but I'm not coming up with anything. I guess I just don't know where to look. Thanks in advance for any help you can give me.
I get this error: The data types text and text are incompatible in the equal to operator. when trying to execute this query SELECT id FROM users WHERE username=@userName
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA 116-NY, 258-NJ, 254-PA, 245-DC, 298-AL How do I get the values in the below format NY,CA,GA --- each state to be followed with comma and the next state NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Hi,I have a problem to insert(update) a long text (more than 64K) intoSQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.MSDN says: "When the ntext, text, and image data values get larger,however, they must be handled on a block-by-block basis. BothTransact-SQL and the database APIs contain functions that allow applications towork with ntext, text, and image data block by block." Could somebodygive me an example how to do this, please.Thank you
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?
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
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
I have a pivot table that connects to our data warehouse via a PowerPivot connection. The data contains a bunch of comment fields that are each between 250 and 500 characters. I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
I have a repository with about 10,000 records. These records are selectable by searching by author surname, this works fine, but. the powers that be would like the data to be searchable by phrase. The db lives on a commercial host where I'm unable to create full text search catalogues.
I'd like to be able to return all results that contain both terms, or either if someone types in (for example) Breast Cancer. The search will only select from the the one field which will contain complex phrases such as "Second-line treatment of postmenopausal women with advanced breast carcinoma"
Any help on creating a select query that will do this will be greatly appreciated...
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 All,We're running SQL Server 2000, SP3.I have a stored procedure that consists of a single Select statement.It selects a bunch of columns one of which is a column of data typeTEXT.SP takes 30 sec to run which causes timeouts on the Front End.When I comment out the Text column from the select it only takes 1Sec.Is there anything I can do about it? I know I can't index a Textcolumn. It's also not used in the where clause, so no need forFull-Text Search.But we absolutely have to have it in the Select clause.Thanks for the help in advance.~Narine
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish Even after this it is not working, So provided select on schema also. used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
