Hi Folks,
Is it possible to add <a href=""http://www.yahoo.com"">xxxxxx</a> tag in varchar column as a text and display it as html hyperlink xxxxx (hyperlink).
Thanks in advance
Regards
I dont know,how can I get column description from Sql Server 2005 and use it in vb.net 2005? would you please help me solve it?
View 1 Replies View RelatedDear all,
I want to store a hyperlnk in the MS-SQL table as in MS Access.I could not find any datatype for storing the hyperlink in MS-Sql. So Kindly please help me to store hyperlink in the MS-SQL Table.
Graceson
I would like to change the output of a column into a hyperlink however I am not sure that is possible without some further post-processing. I am aware I would have to do:
Code:
select
'<a href="' + URL + '" target="_blank" nav="web">' + tbl_ID.ID + '</a>'
from tbl_ID
however this obviously just exports the text
<a href="[URL]" target="_blank" nav="web">[ID]</a>
This would work fine if I was exporting it into some sort of HTML table however this doesn't work with excel for example. Is there a way to make this possible or would I have to create a macro in excel to add the url with the ID separately?
Hi all,
View 5 Replies View Related
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
Hi,I am writing a SP in MSSQL. One of the parameted is VARCHAR typewhich have comma seperated INT vlaues. I want to use this varible inWHERE clause against INT type coloum. how do I achive this.eg. DECLARE @CompanyTypeIDs VARCHAR(200)SET @CompanyTypeIDs = '1,3,2'Currently I am using it asSELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)CompanyTypeID is of INT type.But I want to replace the consts with the Parameter passed as belowSELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN(@CompanyTypeIDs) - but this is not giving any result.Please let me know the solution.Many Thanks,Mahesh
View 10 Replies View RelatedCan't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.
I tried using isnumeric, but it also allows those with periods, comma's etc.
Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.
As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.
I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...
Hi,I have a varchar column with values like:000 BNP=Item one HOP=Item two LLT=Item three001 LLT=Item one002 BNP=Item one LLT=Item twoI want to parse this rows into a table similar to the following output:000 BNP=Item one000 HOP=Item two000 LLT=ItemThree001 LLT=Item one002 BNP=Item one002 LLT=Item twoHas anybody done a similar assignment? Can you share me your solution?Thank you much!Edgar J.
View 4 Replies View RelatedIn an effort to improve the speed of queries against my main table,I'll be indexing a column whose data type is varchar(50).Would I be better off (better performance) if I changed the column'sdata type to some numeric type? I would have to update the column'sdata to accomodate this, but I would do it if this offers aperformance gain.-- Bill
View 6 Replies View RelatedHi,
I am dumping varchar(40) column from a OLE DB Source to Excel file but it is failing due this error
[Excel Destination [239]] Error: Column "ItemDescription" cannot convert between unicode and non-unicode string data types.
How can I resolve this issue?
Thanks
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Code:
Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type
From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID
Where D.SchoolID = @DojoID
and C.Days like'%' + @Days + '%'
Union
Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type
From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID
Where D.SchoolID = @DojoID and @Date
Between E.EventStartDate and E.EventEndDate
how can i cast it correctly?
I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example
1.0.0
1.0.1
1.1.0
1.2.1
2.0.0
2.1.1
etc
These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.
The below TSQL query takes about 19 minutes to run
SELECT count(distinct SessionID) ) FROM SessionTracker
Sessionid is a varchar(138)
Through SQL profiler - I notice implicit conversion taking place when running distinct option.
Select count(sessionid) from sessiontracker
- runs in milliseconds.
Is there a preferred way to order a varchar column numerically (for thosethat are numeric), then alphanumerically for all others?I've tried:ORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN CONVERT(Float,<column_name>) ELSE 999999999 END;andORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN 0 ELSE 1 END, CASE WHENIsNumeric(<column_name>) = 1 THEN CONVERT(Float, <column_name>) ELSE 999999END, <column_name>;Neither of these however give the desired results.Any ideas?Thanks,Frank
View 4 Replies View RelatedI have a column that I do not want any whitespace in whatsoever. I'mwondering how do enforce this a DDL level instead of in 40 millionseat-of-the-pants after-the-fact computer programs accessing thedatabase.Regards,Terrence
View 5 Replies View RelatedHi mates,
I have a Table:Test with column text:varchar(255). I want get rows where text length to be longer than 100. Is it possible?
Thx in advance,
Is there any way i could apply encryption on a varchar type column without changing its datatype to varbinary.
View 6 Replies View RelatedHi all,
I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example
Admit = Admit
Accept = Admit then Accept
Withdraw Accept = Admit, Accept, then Withdraw
Decline = Admit then Decline
As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits:
Admit
Accept
Withdraw
That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns?
Thanks for any help or suggestions you have.
how to put multiple lines in a varchar(MAX) column?
when I cut&paste it only pastes up to the first newline
CTRL/ENTER does not work (like it does in an Access memo column for example)
there must be a way to put multiple lines
help will be appreciated
I have a password column that needs to be converted from varchar to varbinary. Can anybody provide me a proper CONVERT statement syntax for it?
View 12 Replies View Related My table has a column named [Liability] varchar datatype which has the data in the format
(
3535.00,
00393.99,
00Loan,
0.00,
.00
*.00
)
I want to trim the leading zeros so that the output should be(trim only the leading zeros)
(
3535.00,
393.99,
Loan,
0.00,
.00
*.00
)
Can someone show my the sql statement for this?
Thanks.
Hi
How do i remove Carriage return in a varchar column?
Thanks
I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.
Thanks.
create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')
I want the output as
GTT taken
Check sugar today
I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?
View 1 Replies View RelatedI want to just retrieve the numbers left of the space in this address column what can I use? The Address column is Varchar.
Address:
6501 Red Hook Place #201
32 Orchard St
1 Hardy Rd ste 210
379 E Center St.
170 Bridge St
399 Interpace Pkwy
1800 Valley Rd
2210 Harding Hwy
41 Maple Ct
Expected Results:
Address
6501
32
1
379
170
399
1800
2210
41
I tried using this:
Left([ADDRESS], Charindex(' ',[ADDRESS] - 1)) as 'Address'
But i received an error:
Conversion failed when converting the nvarchar value '6501 Red Hook Place #201' to data type int.
I have a table . It has a nullable column called AccountNumber, whichis of varchar type. The AccountNumber is alpha-numeric. I want to takedata from this table and process it for my application. Before doingthat I would like to filter out duplicate AccountNumbers. I get most ofthe duplicates filtered out by using this query:select * from customerswhere AccountNumber NOT IN (select AccountNumber from customers whereAccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]> 1)[/color]But there are few duplicate entries where the actual AccountNumber issame, but there is a trailing space in first one, and hence thisduplicate records are not getting filtered out. e.g"abc123<white-space>" and "abc123" are considered two different entriesby above query.I ran a query like :update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)But even after this query, the trailing space remains, and I am notable to filter out those entries.Am I missing anything here? Can somebody help me in making sure Ifilter out all duplicate entries ?Thanks,Rad
View 3 Replies View RelatedIF I have a table like the below one and i have to insert a number value which is inserted as varchar in an int column then what is expected behavior of this statements .
create
table stud
(id
int)
insert
into stud
values ('1').
I thought it should fail but it succeeds...
My table has a column named [Liability] varchar datatype which has the data in the format
(
3535.00,
00393.99,
00Loan,
0.00,
.00
*.00
)
I want to trim the leading zeros so that the output should be(trim only the leading zeros)
(
3535.00,
393.99,
Loan,
0.00,
.00
*.00
)
Can someone show my the sql statement for this?
Thanks.
I have a DB named zCIFRecord with a column named CIFUpdateDate which is of datatype varchar. The data is a date MM/DD/YYYY 01/30/2008, this is al that is in this column. I can search this colum for individual dates and for a range of dates. My problem is with a range of dates that is not within the same year, such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '12/01/2007' and '01/30/2008'
will return nothing because it seems to only search on the 12 then the 01 then the 2008. this search can be performed properly on dates within the same year such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '01/01/2008' and '01/30/2008'
will return the proper values because now all the numbers are in correct order. How can i create a search that lets me perform the first query as well as the second query. I tried to convert to a float but you cant convert a varchar to a float.
Dear All,
I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '??????' instead of showing the encrypted value that I sent with the insert statement.3
............................ Can anyone tell me how to get rid of this?
Thanks and regards,
Z Z.
I have a table with number and varchar columns. The last insert statement has 1 inserted.
The select statement should retrieve
a b
1 1
CREATE TABLE [dbo].[test1](
[a] [int] NULL,
[b] [varchar](10) NULL
) ON [PRIMARY]
insert into test1 values (1,'a')
insert into test1 values (2,'b')
insert into test1 values (4,'d')
insert into test1 values (12,'x')
insert into test1 values (15,NULL)
insert into test1 values (1,1)