Any idea why I cannot see (in Enterprise Manager) all the coulmns of a selected table? All I get is * all columns - not each one, meaning I cannot select individual columns in my new view. Could it be the version? I am running SQL 7.0 SP1
Thanks
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.
Can I do that??
Any reply showing me the right way is appricited.
-Thanks,
Digs
hi all
Using Sql Server 2005 / Reporting Services
I created a view in sql server management Studio but can't see it in Reporting Services.
I appear to have the same security permissions?
what else am I missing?
thanks
jewel
I have a matrix report that I am using with MonthName(Fields!Month.Value) across the top of the report. However, when there is no data under the column, the month is skipped. So, for example, I get this:
January March
Actual Goal Actual Goal
25 25 30 35
I have tried adding
iif(Sum(Fields!Total.Value, "DatasetName")> 0, Sum(Fields!Total.Value, "DatasetName"),"N/A")
But it still is not showing up.
Any ideas?
Thanks,
SHP
why wouldn't an upstream column show up in the merge editor?
I can see the column in the metadata by clicking the green arrow going into the merge, but it doesn't not show up as a selectable value in any of the drop downs.
this is true for input1 and input2 in the merge
anyone know how I can force it to show up?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
hi,i have an sql database on my server on the world wide web.i can make a connection to the database in visual web developer and all the tables etc are shown in the 'database explorer' of visual web developerwhen i make the query in visual web developer it does retrieve the data froom the remote server database when i 'test query'.... so looking great!the connection string in the webconfig file is left as the default when i run the prgram on my LOCAL HOST with the inbult server that comes with visual web developer it runs fine......so.....running on my local pc it will connect to the database on my www host server and does display the data from that database.i need to change the webconfig files path to the connection by default it is |DataDirectory|if i change this to the path i suspect is correct as this is the connection i use to the database C:Inetpubvhostsarcvillage.comhttpdocsApp_DataTestdatabase.mdfwhen i change the path in webconfig then it stops working on my localhost and the error when i ftp it to my host is:Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. any ideas?
View 2 Replies View RelatedWhen using Sql Server Enterprise Manager and viewing a Database / Tables section, most of the tables if not all have a create date of 11/5/2004.Except for one, DNN_Users, has a creation date of 7/10/2007What factor could have caused that create date to have changed?What factors go into the date being set on that column in the database design? Does the date get updated say if I were go go in and change a datatype in a table?
View 5 Replies View RelatedSELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
[Code] ....
In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?
1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M
2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30
3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999
I have get 3 columns in a select query. how can show these 3 columns in row wise.
View 2 Replies View Relatedi created a layout in visual studio 2005 with the proper dimensions to print on an Avery 5160 label sheet (3 labels across and 10 down)
in the Body properties, i set Columns to 3, ColumnSpacing to 0.125", and Size 2.625" (W) & 1" (H).
i created a list table with a text box of the same dimension as the Size in the Body.
the thing is when i go to preview the report in visual studio, it just shows all the data but only in one column, but if i click the Print Layout button, it shows all 3 columns, then when i print it, it works! so i assumed that this was just the way visual studio handled the data. no big deal...
the problem is when i upload this to the reporting services on our local intranet and run it from the reporting services, it shows only one column (the left most only) and prints that way too; it's almost like i need a Print Layout button on the reporting services (which there isn't one).
is there something that i need to set so that it will print in 3-column form from the reporting services so people can print on label sheets?
i hope this makes sense. also, i tested this on the same printer just to make sure it wasn't hardware.
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
View 2 Replies View RelatedHi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
View 1 Replies View RelatedI'm having an odd issue with SQL Server. What happens is the following: I create view1. I then create view2 which uses view1. I then create view3 which uses view2. Then, if I make a change in View1, my other views are messed up. What happens is that columns in view3 (and view2) will be switched. Therefore, if a crystal report for example references view3, I'll suddenly get an exception due to a field in the cyrstal report thinking it's going to get an integer but gets a date instead. It's as if the columns were moved around. Is this a known issue with SQL Server 2000? Has someone else had that here? Has someone resolved that here? thx.
View 2 Replies View RelatedHi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help
Create View BONUS_PAYOUTS As
Alter Table Employee
Add EMP_BONUS Decimal(6,2)
Update Employee
Set EMP_BONUS = EMP_PCT / 500000
Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS
From Employee
Inner Join Position On Employee.POS_ID = Position.POS_ID
I have a view that I created from 4 SQL tables in order to query data for a report. I can't change the format of columns in the original table but would like to format the columns in the view as a different data type.
The original table has the values formated as varchar, the info in the columns is numbers and I would like to have the values changed to decimal when the view is queried.
Is this even possible? Any help would be appreciated.
Hi All,
I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?
Does anyone have idea how to join table that can reflect both left and right join?
Table A Table B
Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5
A Jan 5 xxx A Jan 1 yyy
B Feb 3 C Mar 4
B Mar 4 C Apr 3
C Apr 2 D May 2
E Mar 1
Result Table
Column1 Column2 Column3 Column4 Column 5
A Jan 6 (= 5+1) xxx yyyy
B Feb 3
B Mar 4
C Mar 4
C Apr 5 (= 2+3)
D May 2
E Mar 1
So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).
Thanks for the help.
Hi,
I'm setting up Transaction Replication b/w SQL Server 2K and SQL Server 2K5.
I have published Tables, Views and SPs as articles.
One of the views has more than 300 columns. So when i try to replicate it, I'm encountering the
following error Message.
"Error 20068: The article cannot be created on table because it has more than 255 columns."
When a view can be created with more than 255 cokumns, why the problem arises when we replicate
it?
Can any one help me on this?
Thanks,
SBR.
I try to create a view in Access Project but in design view I only see "all columns" in a table. The individual columns are not listed. This happens only with a certain SQL Server database. Any suggestions ?
View 1 Replies View RelatedWrite a select Statement that selects all of the columns for the catalog view that returns information about foreign keys. How many foreign keys are defined in the AP database?
View 4 Replies View RelatedI need to create a view that counts in one of the columns.
The tricky part for me is that it should only count to three, then start on 1 again and count to three again.
Example
1 - car
2 - bike
3 - Motorbike
1 - Boat
2 - Airplane
3 - Motorboat
1 - Bicycle
I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as
select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted
from dbo.fielddata
GO
When I add this code in a view and try to save . . .SELECT TOP 610 *FROM dbo.Master INNER JOINdbo.TypeByCase ON dbo.TypeByCase.CaseNum = dbo.Master.CaseNumIt gives the error:ODBC error: [Microsoft][ODBC SQL Server Driver]Column names in eachview or function must be unique. Column name 'CaseNum' in view orfunction 'dbo.BobView1' is specified more than once.Any idea why?Thanks,RBollinger
View 1 Replies View RelatedHello,I'm somewhat of a newbie and I need to create a view with a columnthat is not derived from any other tables. What I want is for thisfield to be an auto-increment field or some kind of UID. Can anyonequickly tell me how to do this.Here is my dilemma. I need to pull data from a unique data set from atable which does not have a primary key, and none exists in its data.Please tell me how to put this data in a view(or another table) sothat I can pull it out with unique records. It doesnt matter what theunique field consists of.Thanks
View 3 Replies View RelatedEven though I select "Column Names" in Design View when creating a query (or view), only "* (All Columns)" appears in the table box.
In InfoPath, when I connect a combo-box, err drop down box, to the database, I am unable to connect directly to a table... no tables are shown. If I select a different database, these problems do not exist.
I can not find any setting to allow these columns to be shown in the design view or any setting that will "expose" the tables in InfoPath.
I tried creating a new database and exporting the data, tables and data, from the troubled DB to the new DB; however, the new DB exhibited the same behaviour. The system tables, Master and Model, have the same behaviour. Please help me with your ideas and suggestions... thank you very much for your time.
This database was upsized from Access 2003 to SQL Server 2000 SP4.
rogge
I would like to alter view to add columns. My base table is populated by application and the base table name is always change. Is there any way to add some extra columns to this view.
One way is to create a sp to add columns and use the view inside the sp. But I would like to know is there any way to alter the view.
I have a view with 2 columns and 2 rows(No 1). I want to show the result with 3 columns in 1 row(No 2).
No1:
The view result :
ID | SubjectID
-- ---------
13279 | 900
13279 | 910
No2:
I want to show that result in this structure :
ID | SubjectID | SubjectID2
-- --------- ----------
13279 | 900 | 910
I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.
Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .
The view is called TEMP_EXPORT_1
I can either use the code below to return a list of columns that I want removed:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'
Or I can use the code below to return the list of columns that I want to keep:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'
Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...
Therefore I tried the following but I'm not sure of the syntax:
ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1
Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.
how do i view all columns from all tables in my databse which have the name 'ename'.
Ashley Rhodes
I have a view that is using UNION ALL to combine common fields of two tables, this is my statement:
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_FRAME_T
UNION ALL
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_ATM_T
This works fine, but I would also like some fields that do not match to appear in the view. It is OK if the value is null for the rows of data from the other table that doesn't have the columns.
The other columns are called DLCI from CT_FRAME_T and then VPI, VCI from CT_ATM_T.
My view would then return ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, DLCI (where applicable), VPI and VCI (where applicable). Is this possible?
hello all,
i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'
below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ...
View definition includes no output columns or no items in the FROM clause.
A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.
query:
select
Case_CaseId,
Logged,
CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting,
CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating,
CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected,
CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved,
CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved,
CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed
from
(
SELECT
Case_CaseId,
MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged,
MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting,
MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,
AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved,
MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved,
MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed
FROM
CaseProgress
GROUP BY Case_CaseId
) as temp
order by Case_CaseId
I have a table like the followingField1 Field2 Field3------ ------- ------x1 y1 z1x1 y2 z2x1 y3 z3x1 y4 z4x2 y1 z5x2 y2 z6x2 y3 z7x2 y4 z8x3 y1 z9............and so onI want to create a view with x1, x2, x3.. as uniquerecords; y1, y2, y3.... as fields; and z1, z2, z3.... as the valuesWhen I doCREATE VIEW xyz (y1, y2, y3, y4) ASSELECT field1 ,( SELECT field3 FROM table WHERE field2 = 'y1'),( SELECT field3 .....FROM tableI get the error that the sql query creates duplicate values. I think Imay have to do a join using distinct values of field1. I was lookingfor some guidance with the join.Thanks for your help in advance(using SQLSERVER 2000)
View 2 Replies View RelatedHi,
I have a dataflow task with two components: OLE DB source --> Flat File destination
In the OLE DB source, I am accessing a view, not a table.
However, when I attempt to map the view's input columns to the flat file destination columns, the screen is completely blank. There's none of the usual drop downs that let you select the column name, nor can I drag and drop the input column from the upper pane to the lower pane.
Is there a trick to doing this?
Thanks