I am creating a Metadata management application for a business intelligence platform on SQL Server 2005.
For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department.
In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column.
(I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's)
Aliases are easy enough to obtain:
select object_id, column_id, name
from sys.columns
where object_id = object_id('SomeViewsName');
As are the 'physical' columns:
SELECT
t.object_id as TABLE_OID,
c.column_id as COLUMN_OID,
c.name
FROM
sys.views v
JOIN sys.sql_dependencies d
ON d.object_id = v.object_id
JOIN .sys.objects t
ON t.object_id = d.referenced_major_id
JOIN sys.columns c
ON c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
WHERE
d.class < 2 AND
v.name = 'SomeViewsName';
As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition.
Ultimately I'd like to get this result:
Alias used in View, physical table's object id, physical column's id
I have created a view and one of the columns in the view has an alias assigned to it.
I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup from which table each column in the view orginated from except for the column that has an alias assigned to it.
Is there any other way to lookup a column that has an alias assigned to it?
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
I have added alias datatypes using sp_addtype and would like to view the properties defined in it. please let me knowhow to view the properties of them and also is there a way to list all alias datatypes existing in the current database along with their properties.
hopefully, someone has already solved this problem
I have been assigned an application that maps data from access databases into a view in Sql Server 2005. A vb6 application does a select on the view. The recordset is then modified by the application, and eventually, the original tables will be updated with the new data. Some of the fields in the view are the result of calculations ( i.e. adding multiple varchar fields together to produce one complex varchar result ). Any of the fields that are calculated in the manner previously described have the adFldUnknownUpdatable bit set in the attributes of the recordset, and consequently, you get a -2147217887 error ( multiple-step operation .... ) when you try to change the value of the field in the recordset. Does anyone know how to get around this problem?
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int) As Begin Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1' print @Sel Exec Sp_executesql @Sel End
--Execute Mypro 1,4 --->>Here I Executed Error Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between 1 and (1+4)-1 Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM Procedure successfully created but giving error while Excuting'. Please anybody give reply Thanks
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
What is the significance of phisical IO in current activity panel.One of my production servers has for SQL server alert engine the phisical IO as 6089908.Is it very high? Thanks!
Hello, first time here, first time messing with SQL.
When creating a table in SQLServer2005 can you specify a column to be for a derived attribute. example a column monthlysalary and create a column that is a derived attribute - yearly salary. Is this done when creating the table and how is the syntax ?
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.
I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS 2 ( 3 SELECT 4 Id, 5 Name, 6 Created, 7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers, 8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber' 9 FROM Projects p 10 ) 11 SELECT * 12 FROM ProjectList 13 WHERE RowNumber BETWEEN 50 AND 60;
This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error: Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'. I read this in the documentation: When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this
Hi All, I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.
e.g. Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;
In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.
My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?
Is there a way to use a column alias in an another calculation within thesame query? Since I am using some long and complex logic to compute total1and total2, I don't want to repeat the same logic to compute the ratio ofthose two columns. I know that I can do a nested query, but that seems toolengthy as well since I actually have many, many columns.selecttotal1 = sum(case(long complex logic)),total2 = sum(case(another long complex logic)),ratio = total1/total2
Hi, I have formatted my server because of serious problem and i did not backup my database. I have only a phisical copy of the disk containing data on another disk. :( How I can recover my db? Thank you in advance.
What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions:
when pricelist = 1
when pricelist = 2
when pricelist = 3
pricelist -------- 1 2 3
Price -------- 912 -- (linked with 1) 234 -- (linked with 3) 56 -- (linked with 2) 3245 -- (linked with 3) 234 -- (linked with 1) 65 -- (linked with 2)
these 2 columns are in the same table^^
so what i want my query to generate is:
Price1 -------- 912 234
Price2 -------- 56 65
Price3 -------- 234 3245
Any help is apprecieated, thanks
if the above does not make sense to you maybe this will: "can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"
Hi,I got 'Invalid Column Name NewCol1' when I query the following:Select col1, col2, (some calculation from the fields) as NewCol1,(some calculation from the fields) as NewCol2,NewCol1 = NewCol2 fromTable1 inner join Table2 inner join Table3....Where.....Basically, I want to find out if NewCol1 = NewCol2 after thecalculationAny advice?Thanks in advance. Your help would be greatly appreciated.Wanda
I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement ..........End Function
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query.
I have a copy of class schedules with only students that are taking half of a full year class in a separate table. The table lists the term that the students are taking so I joined that table to the actual class schedule table via the code below. The values are 1 & 2 and if it's null (not taking half of a full year class) it's a 9. So now I only need 9s and 2s to bell pulled from the script below. How do I go about doing that since HLF_Term is not a real column?
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code: SELECT CASE WHEN <CONDITION> THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE)) ELSE 0 END AS CurrentDateAdj, Year(CurrentDateAdj) + '_' + 'some text and processing') FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
I have a program that connects to SQLServer 2000 through ADO connection.
the program executes the following query:
SELECT ax.AccNo, (SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID) As Product FROM dbo.History h LEFT OUTER JOIN dbo.AccXRef ax ON h.ID= ax.ID LEFT OUTER JOIN dbo.States ON h.[HistoryItemsub-Type] = dbo.States.Type LEFT OUTER JOIN dbo.CustXRef cx ON h.CustomerNo = cx.CustomerNo WHERE HistoryItemDate <= getdate() ORDER BY HistoryItemDate ASC
This query works in th program and in Query Analyer on my machine. However, On a different Machine (and different SQLServer) the query works in Query Analyser but does not work in the program, the following exception is thrown:
The column prefix 'h' does not match with a table name or alias name used in the query
I want to create column name dynamically in select list.
create table #temp(name varchar(10), sale int) insert into #temp values('john',1000) insert into #temp values('Mike',500) insert into #temp values('Abhas',200) select name,sale as sale from #temp
Now i want change column alias only , not value. I need to concatenate sale with year value of getdate(). i.e. column header concatenation only, not a output value.