Concatenation error in SQL Server 2005 database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Concatenation error in SQL Server 2005 database

Author

Topic

tajuddin
Starting Member

1 Post

Posted - 2009-03-05 : 00:28:18

Hi All,
I am trying to create a custom view on a table but getting error message. My table structure is -

CREATE TABLE [dbo].[business](
	[bs_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[Business_name] [varchar](60) NOT NULL,
	[Proprietor] [varchar](50) NULL,
	[Address1] [varchar](250) NOT NULL,
	[City] [varchar](50) NOT NULL,
	[Country] [varchar](50) NOT NULL,
	[Telephone1] [varchar](50) NOT NULL,
	[Telephone2] [varchar](50) NULL,
	[Fax] [varchar](50) NULL,
	[Email] [varchar](50) NULL,
	[Website] [varchar](50) NULL,
	[Comments] [varchar](200) NULL,
	[last_update] [datetime] NULL,
	[create_date] [datetime] NULL,
	[address2] [varchar](150) NULL,
 CONSTRAINT [PK_business] PRIMARY KEY CLUSTERED )

Now I am trying to create a view like below -


CREATE  VIEW [dbo].[BusinessView2]
AS
SELECT bs_ID, Proprietor, Business_Name, 
 Address1, City, Country, Telephone1, Telephone2, Email, Website,fax,
 ISNULL(Address1, '') + ',' + ISNULL(City, '') + ',' + ISNULL(Country, '') as FullAddress,
  ISNULL('Tel:'+ telephone1, '') + ' ' + ISNULL(Telephone2, '')+ ',' + ' '+ 'Fax:' + ISNULL(fax, '') + ',' + ' ' + ISNULL('Email:' + Email, ' ')   + ' ' + ' ' +  'Web:' + ISNULL(website, '') as contacts
FROM dbo.business

Getting Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.this error message.

I have tried following and that works fine. As soon as I add the address1 and address2 does not work.

alter VIEW [dbo].[BusinessView]
AS
SELECT bs_ID, Proprietor, Business_Name, 
address1, address2, Country + Telephone1 +' ,' + Telephone2+Email + Website as FullAddress,fax  FROM dbo.business

Can anybody help me please...

Regards,

M Tajuddin
Web: http://tajuddin.whitepagesbd.com

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-03-06 : 23:29:39

small change in constraint specification.
CONSTRAINT PK_business PRIMARY KEY (bs_id) )
(or)
CONSTRAINT PK_business PRIMARY KEY CLUSTERED
(
bs_id ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

There is no error in Create view script.

quote:

CONSTRAINT [PK_business] PRIMARY KEY CLUSTERED )

isk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources