Be careful when implementing views (from SQL Server 97/2K). SQL Server
stores the metadata on the view at creation (or the last time it was
saved). This means if you have:
SELECT * FROM table1
it will put all the fields of table1 in the view's metadata. If you
then change table1 and add (for example) another field, this field
will not be visible in the view until you open it in design view and
click save (to update it).
Please, could anyone tell me how to get information from which attribute of which table is the attribute from the view derived(it could also be a complex expression, not just attribute)through querying system tables or views(INFORMATION_SCHEMA, preferably if possible, because it's standard)
i'm using MSSQL2005.
Thank you!
Hi
I add extended properties to table in sql server 2005, for example i add Caption property to every column.
In case I generate a datasource from table in sql server in visual studio 2005, Is there a way that "Caption" property in datatable to be filled from that metadata, or metadata is useless for dataset.
Every time i had to fill manually the caption property of datatable generated .
I have a problem retrieving data from a linked SQL Server 6.5 server in version 7. I have set-up the server as a linked server and most operations occur with no problem. With some tables, though, I get the following error when I attempt to query any column of data:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
This occurs consistently for a subset of tables. I cannot see any pattern to the configuration of these tables that would make them any different to those where a query succeeds.
Any ideas?
Regards
Philippe
I'm curious if there is a way to gather any metadata about a query that was just ran, or about a dynamic query passed in as a parameter, such as being able to "dynamically" return a list of columns in the query, along the lines of:
SELECT * FROM [myTable];
SELECT @@COLUMNS --Expecting this to return a recordset of columns for the previously run query
I'm not talking about querying metadata about the tables themselves, I'm really looking for what "metadata" might be available about the query itself.
This would be useful for me to dynamically generate some code for ad-hoc queries with a stored procedure call, or to simply return a list of just column names only without the data.
Is this at all possible in SQL Server?
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 RelatedI can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.
I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.
I tried
SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')
but I got back
Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.
I have my SQL Server installation on drive c: and the databases on anotherdrive. My c drive got corrupted and I am planning to restore it from an oldbackup. This means drive c will not be up to date.Is there anything that SQL Server saves in the registry or in its homefolder that changes frequently? I am hoping that nothing does which meansthe old backup will be good enough.Although I think the default databases are in the c drive, I will try toget them back from a recent backup.John Dalberg
View 1 Replies View RelatedHi,
Can someone tell me where I can download the "SQL Server 2005 BI Metadata Samples toolkit"? Seems the old ulr doesn't work right now.
Thanks in advance.
Mike
While starting querying with sql server mdx query i m getting error loading metadata:no cubes found.
View 6 Replies View RelatedI am tasked with truncating and reloading tables from one server to another. Company policy prevents cross-server queries, but allows SSIS packages with cross-server connections. I am doing this for about 25 tables. I have the table names in a single table & I have created an FEL to execute tasks against each table one-by-one. It works fine to truncate all the tables. I run into issues, though, with the DataFlowTask. I'm able to tell it which server & table to dynamically connect from and to, but it doesn't know how to map the metadata. They're the exact same columns and field names in both source & destination.
View 9 Replies View RelatedSSIS package meta data information is needed to covert SSIS into a Stored Proc.
100s of table mapping reside within SSIS, what tool/method would get those information so that they copied and pasted to SP to speed up efforts.
Today, I download SQL Server 2005 Business Intelligence Metadata Samples Toolkit from Microsoft download center, per readme.txt instructions, build the project, but there was an error as below:
Error 1 The project could not be deployed to the 'localhost' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0
After then, I just build the project,but didn't deploy it, then I try to launch
DependencyAnalyzer console application, there was also an there:
Error occurred: A connection cannot be made. Ensure that the server is running.
Are you success on this Toolkit?
Thank you in advance.
I have multiple xml data file in a directory say C:XMLData abc1.xml, abc2.xml, abc3.xml etc.
Need to loop through each file in ssis with Foreach loop container, and get the file name say abc1, and load the data of abc1.xml to abc1 table in sql server DB.
Next iteration will pick up the abc2.xml and find the abc2 table in sql server DB then insert the data in abc2 table.
While each iteration, xml source should also point each xsd file correspondingly.
Tables are already created in DB
I solved my problem up to getting the file name from ech iteration and assigned file name to variable, in oledb destination data access mode I select Table or view name variable, then corresponding table will get selected for data insertation.
Just wanted to know how can I read each xsd file for each xml data files while iteration.
I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location
2. Create a table according to that xml data file metada
3. load data on newly created table.
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.
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 RelatedHello.
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.
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.
hi all,
i have a deployed on the report server. is there any way i can retrive the metadata(xml) from report server using C#.
Also wanted to run the reoprt from server and get it's output (data) in XML.
please help.
Regards...
girija shankar
Hi,
I've been using stored procedures to do queries including inner joins etc. I have always known about views but don't understand why they are used, what is there purpose, they seem the same as sql queries, is that true?
Is it more effiicient to use views rather than stored procedures to do your queries?
Really I just want to know why I would ever want to use views instead of stored procedures to do my queries?
Thanks in advance.
Need a Clarification in SQL Server 2000.
I would like to know if its possible to create a view of a table residing in a SQL server which is not the one in which we r currently working in
Desc:
1) Say i have 2 SQL servers( different Boxes) A & B
I am working in ' B ', can i have a view of a table residing in SQL server 'A' in my system( Server 'B')
2) Leaving out the network uncertainities, will such a setup give any performance issue when the view is for a table that captures daily transactions running into several
thousands of records
3) Is ther any other Alternative to access tables in another SQL sever without going for Views?
Plz find out and reply ASAP.
I know that stored procedures(sql server) caches stored procedures in memory where it keeps the compiled execution plan in memory, how does it work with the views does sql server store /cache the views. Just wondering Thanks
View 2 Replies View RelatedHi there,First thing off, I'd like to know what Views are useful for? I thing it can speeds select query containing multiple join, but I'm not even sure if that's true.Second, assuming that Views speeds thing up for SELECT query, can it be used by NHibernate when restoring object from their persistence store (in this case, SQL 2k5) ? If not, can they be (views) of any use if we use NHibernate?Third, would it be better to create our crystal report using Views to improve performance?Thanks for anyone who participates in this thread! :-)
View 1 Replies View RelatedHi There,
Is it possible to make views which takes Data from Multiple Servers?
NOTE: The view is very complex because it uses FOUR Diff. statements
joined together with UNION clause.
One more Q.
If my Query Analizer Displays following on execution of query.
" Internal Query Processor Error: The query processor could not produce a query plan."
What should be my approach to rectify the problem.
Any help would really be appreciated.
zak
I have two databases on my Server - database1 and database2.
Database1 has a view on tables on database2 :
create view myview as select * from database2.dbo.tablename
(because all tables in database2 are owned by dbo)
and myview on database1 is owned by dbo.
when a user on database1 - lets say user1 tries to select rows from myview,
we get an error saying that user1 is not a valid user on database2.
it only allows user1 to select rows from myview if i make user1 a user on database2
as well, which is not what i want.
Can someone please point me in the right direction.
i would like to be able to select rows from a view which is based on tables in another database.
thanks
simran
not sure if this is by default or restricted,
but I can't view the sql server logs using ctp
an error is generated saying the tsql could not be retrieved or something, very long message to post here
is this normal?
TIA
--------------------
keeping it simple...
Hi,I am working with several tables and views. My goal is to create a viewwith critical reporting data from these tables and views. I havemanaged to get the majority of data but am having difficultly with thefinal step.For a record in the master dataview, add additional record informationby appending data from another view based on the first 2 letters of thedocument number and the document number i.eDocument numberSC11111DN22222SI33333For SC11111 look up data in Sales Credit table/view for doument SC11111and append to that lineFor DN22222 look up data in Delivery Note table/view for documentDN22222and append to that lineFor SI33333 look up data in Sales Invoice table/view for documentSI33333 and append to that lineAny help appreciated.Thank you in advance,Raj
View 1 Replies View RelatedCan i show my view data on a web page is it possible.How can i show the data if it is too large.
View 1 Replies View Relatedhi all,
i had a view in my project, i am inserting a record in to that view(View contains a identity key for a column), with in stored procedure i am inserting a record i am not passing the identity key value to the insert statement. The record is getting inserted,Based on the identity key(i am getting the identity key value with Scope_Identity()) and with that value i am inserting records into another two tables.In this scenario every thing is working fine.
but now i am trying to place a trigger(instead of insert trigger) on the view, when i placed,it is not inserting record into first table,so i am not able to get the identity value of that record and process failed.
how can this achived, let me know.
Hi,
I have two servers that i want to create a SQL RS report on.
On one server there is an HR database with our staff details, on the other server there is a database of assets.
In order to report on the assets assigned to each user i am thinking that i will have to :
1) link the servers
2) create a view in the HR database exposing the fields needed
3) create a view in the assets database exposing the assets information joined to the view from the other server
4) create my reports on the view on the assets server.
is this right or am i barking up the wrong tree?
Please help.
I created a linked server with Oracle Provider for OLE DB to Oracle Database from SQL Server 2000 SP 4. Afterwards I created Views from this linked server and granted SQL Server users "select" access to the Views.
Unfortunately when they try to access the views the following error message was generated (see the attached image - error.gif).
Can someone please explain to me what is wrong?
Thanks
Victor
Thanks in advance in reading this post ! I'm facing a situation in sql server 2000 sp4 with partitioned views.
I have a partition views that joins about 10 tables, in each table there is a check constraint.
For example, if a exec a select count(*) from VIEW where col1 = '20080101' , it goes for the table that has data for '20080101' .
If I exec a select col1,col2,col3,col4 from VIEW where col1='20080101', it goes to all tables and make an index seek.
I want the beaviour of query 1, beause it is just looking on 1 table and not one the 10.
Thanks in advance !
If column1 in SQL Server column is text: 19980701What is the syntax in the select statement to convert it to a datelike: 07/01/1998Thanks for any helpRbollinger
View 1 Replies View Related