UNION On Two Views (Collation Issue)
Jan 31, 2008
Hi folks,
I'm having issues trying to create a union using two Views I created.
View 1:
SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id,
ExDescription.Surname + '. ' + ExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year,
dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMember
FROM MONDAS.Reaseheath.dbo.ExDescription AS ExDescription INNER JOIN
dbo.Development INNER JOIN
dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id ON
ExDescription.AccountCode = dbo.Development.Related_Staff_Id COLLATE Latin1_General_CI_AI
View 2:
SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id,
dbo.NonStaffExDescription.Surname + ', ' + dbo.NonStaffExDescription.PreferredName AS StaffMember, dbo.Events.Event_type,
dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMember
FROM dbo.Development INNER JOIN
dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id INNER JOIN
dbo.NonStaffExDescription ON dbo.Development.Related_Staff_Id = dbo.NonStaffExDescription.AccountCode
In the first view I am linking to another server, which is why I have had to change the collation, but the second view all the tables are in the same database hence the collation is the same.
But when I'm trying to do a UNION between the two I get another collation error, but can't figure out how to change the collation like I did in the first view.
Heres the UNION query:
SELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMember
FROM dbo.DevView1
UNION
SELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMember
FROM dbo.DevView2
Can anyone help?
Thanks
View 3 Replies
ADVERTISEMENT
Oct 31, 2007
For a Union All of Views where both views look at the same openrowset file I get
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
This probably occurs because the first view keeps the file open.
Any workarounds on this?
View 4 Replies
View Related
Aug 29, 2007
I have a union view that unions 36 tables which have a date constraint set on the datetime field, the data for which is is same for all records within the respective tables. The purpose of this is to allow users to go straight to the month that they require. This works fine with raw sql queries using hardcoded dates , the query plan shows that only the required tables are accessed. However, if i create a lookup table (this is for business objects), join the union view date field to date field and use a between criteria, it ignores the constraint and scans all 36 tables in the view. Changing the between to = shows that the constaint is used. Does anyone have any ideas here , i seem to have tried everything
Mark
View 4 Replies
View Related
Sep 17, 2007
Hey
I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:
I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.
so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.
What shall a do?
Thanks for all help
View 10 Replies
View Related
Feb 5, 2007
hi frends.i have such a great problem.i want to use union in indexedview and i must have to use it. because i cant make a one tablebecause in those two tables there are 2,000,000 records entereddaily.So please give me suggestion. can also give me alternate ofusing UNION in indexed view.and i want to use idexed view only becausei will do searching afterwards.Regards,Mr.MirzaSoftware Engineer.
View 1 Replies
View Related
Oct 8, 2007
I'm recreating many of my DBA scripts that no longer work in 2005 due to the rework of system tables. It's a risk I lived with knowing that someday the system tables would change. I'm now encountering collation problems, which I do not understand. I know how to fix the problem, but I don't know why the collation issues exist in the first place.
Run the following command.
Select * From sys.all_objects a JOIN master..spt_values b on a.type = b.type
You will receive the following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
Now run sp_help 'sys.all_objects' and look at the collation defintion for columns "type" and "type_desc". In my environment they have a collation of Latin1_General_CI_AS_KS_WS. This is different then the overall default collation of SQL_Latin1_General_CP1_CI_AS, thus causing the error.
My question is why did Microsoft need to make this collation different for these columns?
Any ideas?
Thanks, Dave
View 3 Replies
View Related
May 1, 2007
Hello,
I've restored a SQL Server 2000 database with a Latin1_General_BIN collation from a .dmp file to a SQL Server 2005 server with a default collation of SQL_Latin1_General_CP1_CI_AS. When I try to change the database collation I get hundreds of the following error:
The object 'CK_PM10200_GLPOSTD_00AF8CF' is dependent on database collation. So, in this case, is it even possible to change the collation if there are objects in the database that are dependent on it?
Thanks,
Bruce
View 7 Replies
View Related
Sep 15, 2014
I changed the default collation of a database and every table within that except sysDiagrams , which I can't even through the designer .
View 9 Replies
View Related
Jun 12, 2014
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
View 4 Replies
View Related
Oct 15, 2015
I am using SQL Server 2008. In ServerProperty function, there are two properties called “Collation” and “CollationID”. In some cases, I will only know the CollationID. Is it possible get the collation name from the CollationID? Is there a function called CollationNameFromID?
View 1 Replies
View Related
Apr 29, 2008
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
View 3 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Oct 16, 2015
I am using SQL Server 2008. In ServerProperty function, there are two properties called “Collation” and “CollationID”. In some cases, I will only know the CollationID. Is it possible get the collation name from the CollationID? Is there a function called CollationNameFromID?
View 2 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Nov 6, 2006
Hi all,
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
Any idea why this could happen ?
View 18 Replies
View Related
Jun 28, 2007
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
View 3 Replies
View Related
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
Mar 14, 2006
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies
View Related
Mar 17, 2008
Hi:
I have a website and related database in English version, now I am trying to start with other language, such as Chinese.
The first problem I am facing is: search a user name when user name is Chinese:
For example: English version: SELECT u_name FROM Users WHERE u_name = 'eric', it will return a value, but if I type: SELECT u_name FROM Users WHERE u_name = '艾瑞克', even if the table cell has the 艾瑞克 record, it won't return anything.
Search online, there are a lot of articles, since I am pretty new for this, can you let me know where to start? How to change the collation for the existing db, do I need to create stored procedure for the search?
I would like to solve this problem start from my MSsql 2005 database. If that works fine, then I will go to my web application.
Thanks a lot.
James
View 2 Replies
View Related
Feb 19, 2005
hi..
how to change collation name of database without create new database...
i have a database. it's collation name XX but i want to change it YY...
how to change it?
View 1 Replies
View Related
Jun 7, 2004
Hi ,
I would know what is the simplest (and the more reliable) method to convert an entire db from a collation to another...
Thanks
:confused:
View 6 Replies
View Related
Oct 15, 2015
I am using SQL Server 2008. In ServerProperty function, there are two properties called "Collation" and "CollationID". In some cases, I will only know the CollationID. Is it possible get the collation name from the CollationID? Is there a function called CollationNameFromID?
View 1 Replies
View Related
Feb 20, 2004
Is there any way, I can query across all objects in a given database for what is the current collation name for each column in an object?
View 1 Replies
View Related
Aug 31, 2006
Hi,
Could anyone please advise me how to change the collation name of a SQL 2000 server?
Thanks
View 3 Replies
View Related
Sep 5, 2006
dear experts
while i'm learning BCP from books online, i got a doubt.
copying data between different collations.
what exactly the meaning of collation?
i tried in BOL. but because i'm a junior, i didnt got the good idea about collation.
please explain me
thankyou friends
View 7 Replies
View Related
Aug 16, 2007
Hi,
I am just trying to understand about how collation and unicode work in SQL Server. My database's collation is Latin1_General, and I set one column in one table to have collation Cyrillic_General.
Then in Windows PC where I am running an application that is connected to my database, I set Mongolian Language from Regional Settings. When I typed using Mongolian Keyboards and save the characters into database, then I can retrieve the cyrillic characters back into my application.
However, when I checked into database using Query Analyzer, those characters are saved in the column, that has collation Cyrillic_General and with nchar data type, apparently using codepage 1252, my database default codepage. My application and my database server are located in two different machines and I don't install Mongolian language in my database server. So that when other web application reads those characters from that column, even my browser has been set to use Cyrillic encoding, still it showed as characters from codepage 1252.
Could someone explain me what actually happens here?
Thanks very much before.
Nico.A.
View 4 Replies
View Related
Jul 20, 2005
Hello AllI'm a bit confused about collation settings so needed some info. onit. My database server is currently using SortOrder asLatin1-General, case-insensitive, accent-sensitive,kanatype-insensitive, width-insensitive for Unicode Data, SQL ServerSort Order 52 on Code Page 1252 for non-Unicode DataI have a table:CREATE TABLE [PD_RUSS3].[pdtable_185]([iso_area_indx_no] [int] NULL ,[lineid_1] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[iso_type] [smallint] NULL) ON [PRIMARY]I have a nonclustered, UNIQUE index located on PRIMARY oniso_area_indx_no, lineid_1, iso_typeHowever I'm able to load a file with these entries:45 '16-XX-WCS' 145 '16-xx-WCS' 1I thought that this would fail because of the above settings. Pleaseenlighten me on what I'm missing here..??thanksSunitJoin Bytes!
View 1 Replies
View Related
Jun 16, 2007
Hi,I have SQL Server 2005 database with SQL_Latin1_General_CP1_CI_AS collation, and there is a little problem when inserting Cyrillic text. It works fine when I use SQL Server Management Studio to open specific table and insert new row manually. Problem appears when I use SQL queries. For example: INSERT INTO Customer (ID, Name) VALUES (1, "Владимир"). ID is type of int, and Name is nvarchar.When I execute the query, value of ID is OK (it is 1), but insted of "Владимир" the value of Name is "????????" (only question marks).I am not sure now, if I should change database collation(and what to put), or I should change column type of "Name" column.
View 5 Replies
View Related
Oct 28, 2007
why do i get collation conflict when i used temp table ??Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.i solved it by using COLLATE Latin1_General_CI_AS (the column name)will i have collation conflicts again when i put my web app on a web hosting company??
View 3 Replies
View Related
Jan 22, 2008
Which collation format should I use?
SQL_Latin1_General_CI_AS, SQL_Latin1_General_CP1_CI_AS, or Latin1_General_CI_AS
I noticed that my development server has a different collation setting to the production server.
development. SQL_Latin1_General_CP1_CI_ASproduction. Latin1_General_CI_AS
Both these servers are SQL Server 2005 Express and I typically use varchar or text fields for storing the data.
I've been told that the difference between them is that the former uses code page 1252, as specified here: http://www.microsoft.com/globaldev/reference/sbcs/1252.mspx
However when I compare that to, say, the page of symbols one gets using the 'Insert, Symbol' command via microsoft word I find quite a disagreement. It also differs from the 'View, Clip Library' list provided by TextPad.
The data in my database uses non-ASCII characters (or extended ASCII, if you prefer). The administrators of my CMS will sometimes copy and paste or write European spellings for company names etc. When they do that they're likely to resort to Word's Insert Symbol command. When I get this data and show it on a web page I want to specify utf-8 endcoding.
Which of these SQL server collations should I be using. I suspect that I should be using SQL_Latin1_General_CI_AS. Essentially, I want the charater as seen in the textbox of the CMS to look identical to that displayed on a web page which has the following meta tag set: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
The aforementioned CMS administrators are entering data on Windows XP PCs running asp.net. They may get the accented characters from several different sources (MS Word Insert Symbol or cut and paste from a web page), but they can agree with me that the symbol should display in the text box of the CMS identically to that on the web site.
Can someone put me right on this?
View 2 Replies
View Related
May 30, 2008
what's the difference between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AI What the SQL and CP1 mean in SQL_Latin1_General_CP1_CI_AS?and which one should i use?
View 1 Replies
View Related
Dec 3, 2004
Hello:
I have a database in SQL with the following collate name: SQL_Latin1_General_CP1_CI_AS... I am trying to change the accent sensetive to accent insensitive... how would I do this? I tried re-installing the SQL and setting the default to CI_AI, but since the database that is backed up uses CI_AS, the DB settings overrides the default settings...
Any suggestions?
View 1 Replies
View Related
Nov 13, 2002
I require the command syntax to return the collation properties for a database at table and column level in both SQL7 and in 2000 for an entire database.
Hopefully for all tables and for all columns in the db.
Does any one have the script for this command????
If you do let me know.
Problem as below.
On one server (ServerA) a query works. 2000 server that may have been upgraded from versions 6.5 - 7.0 - 2000
Collation = SQL_Latin1_General_CP1_CI_AS, Sort order=52
On server B the query doesn't work but has same Collation = SQL_Latin1_General_CP1_CI_AS, Sort order=52.
I restored the databases from Server A onto Server B and ran the same query and it fell over with this error message.
Server: Msg 451, Level 16, State 2, Procedure Bills, Line 3
Cannot resolve collation conflict for column 2 in CREATE VIEW statement.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'Bills' because of binding errors.
View 1 Replies
View Related