Solved

sql 2008 create view b using linkes server

Posted on 2011-09-16
7
348 Views
Last Modified: 2012-05-12
CREATE VIEW [dbo].[apple]
as
    select
        xx.ProdNo as productNo,
        xx.Descr as Descr
   from server.Mydatabase.dbo.AllProd xx
    COLLATE Danish_Norwegian_CI_AS ASC;
GO
I get the following error "Incorrect syntax near the keyword 'COLLATE'."

Create view thru a LINKED SERVER
0
Comment
Question by:shanj
  • 3
  • 3
7 Comments
 
LVL 6

Expert Comment

by:dan_mason
ID: 36548603
What about this?
CREATE VIEW [dbo].[apple] 
as
    SELECT
        xx.ProdNo as productNo COLLATE Danish_Norwegian_CI_AS,
        xx.Descr as Descr COLLATE Danish_Norwegian_CI_AS
   FROM server.Mydatabase.dbo.AllProd xx
 GO

Open in new window

0
 
LVL 6

Expert Comment

by:dan_mason
ID: 36548719
The setting up a linked server is not too hard, by the way. Using SQL Server Management Studio, in the Object Explorer, right-click Server Objects, select New, and select Linked Server. The boxes are fairly self-explanatory hopefully, but let us know if you need further advice.
0
 

Author Comment

by:shanj
ID: 36548882
hi dan!
  problem is not linked servers.
i want to create a view with collation via a linked server is the problem.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:shanj
ID: 36548915
dan the create view gave me the same error-.
Incorrect syntax near the keyword 'COLLATE'.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36549005
what about:
CREATE VIEW [dbo].[apple] 
as
    SELECT
        xx.ProdNo COLLATE Danish_Norwegian_CI_AS as productNo ,
        xx.Descr COLLATE Danish_Norwegian_CI_AS as Descr 
   FROM server.Mydatabase.dbo.AllProd xx
 GO

Open in new window

0
 
LVL 6

Expert Comment

by:dan_mason
ID: 36549115
Yep, thanks angelIII : precisely what I meant to write! Stupidly I didn't spot the "as" in the code.
0
 

Author Closing Comment

by:shanj
ID: 36553686
thanks angelll  it worked
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now