How to select/use two DBO for CommandText

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

SQL Server Development (2000)

How to select/use two DBO for CommandText

Author

Topic

jenny10254
Starting Member

2 Posts

Posted - 2010-05-03 : 04:49:44

Hi,

I would like to combine two commandtexts into one

text1.CommandText = "SELECT Id, Heading as Head FROM dbo.Modul"

text2.CommandText = "SELECT Id, Caption as Head FROM dbo.Menu"

I want to combine these two into:

text3.CommandText = "SELECT Id, Heading as Head FROM dbo.Modul" + "SELECT Id, Caption as Head FROM dbo.Menu"

I tried "UNION ALL":

text3.CommandText = "SELECT Id, Heading as Head FROM dbo.Modul UNION ALL SELECT Id, Caption as Head FROM dbo.Menu"

But it didn't work as I receive an error msg:

"Microsoft OLE DB Provider for SQL Server error '80040e14'

Cannot resolve collation conflict for column 2 in SELECT statement."

I tried using Create View, Create Procedure none of them worked.

At the end in order to use UNION ALL and fix collation conflict, I used:

ALTER TABLE dbo.Modul
ALTER COLUMN Id int COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER COLUMN Heading nvarchar COLLATE SQL_Latin1_General_CP1_CI_AS

ALTER TABLE dbo.Menu
ALTER COLUMN Id bigint COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER COLUMN Caption nvarchar COLLATE SQL_Latin1_General_CP1_CI_AS

But this didn't work either. I receive another error:

"Microsoft VBScript compilation error '800a0401'

Expected end of statement "

How can I combine them? Any ideas??

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 09:56:22

what about this?

text3.CommandText = "SELECT Id, Heading COLLATE DATABASE_DEFAULT as Head FROM dbo.Modul UNION ALL SELECT Id, Caption COLLATE DATABASE_DEFAULT as Head FROM dbo.Menu"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jenny10254
Starting Member

2 Posts

Posted - 2010-05-03 : 15:04:15

quote:
Originally posted by visakh16

what about this?

text3.CommandText = "SELECT Id, Heading COLLATE DATABASE_DEFAULT as Head FROM dbo.Modul UNION ALL SELECT Id, Caption COLLATE DATABASE_DEFAULT as Head FROM dbo.Menu"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

You are perfect!!!

I asked this question to many people. No answer so far..

Your answer is perfect. It works perfectly.

Thank you sooo much..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 04:25:53

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources