Hi,
Is it possible to get metadata (i.e. descriptions of tables etc.) in
sql-server? In Oracle you can retrieve this information with tables like
all_objects, user_tables, user_views etc. For example, this query selects
the owner of the table 'ret_ods_test' (in Oracle!):
select owner
from all_objects
where object_name = 'ret_ods_test'
What's the equivalent in sql server?
Thanks a lot.
What is "MetaData" in "Data Transormation service" and what is"MetaData services" ?and in which field I can use them?Thanks
View 1 Replies View RelatedHi All!
I recently added a column to an existing table with a getdate default. When doing a query from that server everything works fine. When a query is ran from a remote server I get an SQLOLEDB error message saying 'inconsistant metadata'. I've tried dropping the remote server and reconnecting but that didn't seem to resolve the problem. Can anyone tell me how to resolve this error. I believe the error number is 7353.
Any suggestions appreciated!
Thanks Jeff!
hi,
Is there a way to find out which user defined procs/child packages etc are been called in SSIS packages using some metadata. The idea is to have a document which lists the number of packages called, whats sprocs and child packages are executed by those pkgs..
I have checked the SSIS metadata whitepaper but that is too generic.
What do you do to address this:
[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.
A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?
I'm working on capturing metadata from my SSIS packages. I have found multiple postings and a white paper that
reference a download toolkit for just this purpose. The link is dead and a search renders no results - can anyone help?
SQL Server 2005 Business Intelligence Metadata Whitepaper
-We are using SSIS packages for various kind of data load from excel source.
-If there are any change in the data type or format of excel, the package cries for the Metadata mismatch.
-During design time if you accept the metadata changes, all things work fine.
But in our case we have deployed the packages on Production Server, now the excel file format/data has changed. The packages are expecting a different metadata so they are not working at all.
Do you have any suggestions for the above problem?
Thanks, Vijay.
i am using icolumnsrowset interface to get some metadata about the columns in a rowset, but i never got unique and primary key columns in the columnsrowset, they always return null, when using sqlcedataadapter i simply add addwithkey option to the adapter to determine it, but i dont know how to do it by using ole db interfaces, i have tried to set DBCOLUMN_KEYCOLUMN flag to true on ccommand<cynamicaccessor, crowset> but it seems it rejects it, generating an unknown error, error object says almost nothing except that 'errors occured[,,,,,]' text
can someone tell me, how can i retrieve columnsrowset with that unique and primary key sections filled?
Hello, i would like to know if it's possible to generate automatically a word document or an excel document that will contain all the metadata definition, for example containing the source columns names, their datatype, and the destination with their datatypes, so that it would easy to create a data dictionnary .
Thank you in advance.
Hello everybody,
It's now quite some time that one particular behaviour of SSIS is really frustrating me and I would like to know if I'm the only one experiencing this problem or if other people have the same problem.
The issue I'm talking about is SSIS 'dependency on what is written in the XML files describing the flows.
Particularly with the Data Types of columns.
I'm explaining myself: Imagine your are developping a flow containing several numeric(18,0) columns...
During the flow you have to perform a lookup on an Integer Field.... Of course this operation is not allowed as a numeric is not mappable with an Integer... (This is, in my opinion, a nonsense as an implicit conversion has to be possible).
as a result of this behaviour, I decide to change the datatype (numeric) from my source query to an integer and use it in the Lookup which of course succeeds but now I have a second problem: each lookup in my flow has an error handling branch which I'm joining back using a Union transform. and there we have the second irritation: the Union transform doesn't replicate the Data Type changes that occured upwards in the flow... worse: it even has no interface to let you modify the data types like the advanced editor of some transforms or data sources. (I've just lost a complete dataflow while trying to modify it manually in the xml file directly :-( for those who are considering modifying directly the XML, don't!! You are asking for trouble and a lot of frustration when you'll switch back to the designer to see the effects )
My question is now: Am I misusing SSIS?? Is there somewhere an option to activate in order to get this behaviour fixed??
Has anyone else experienced this problem?? How are you solving this??
Are there any plans in the future to loose this dependency on the datatypes or at least add some implicit conversions??
Thanks in advance for your replies, suggestions,questions and other thaughts about this subject :-)
Alain
Hello-
I have a question regarding my metadata information. I finally setup my fixed width file which took some time. Is there a way that I can backup my metadata so I wont have to recreate these setting again. I'm thinking the format of the file is stored in the metadata so if I have a user running the SSIS package from the Business Intell Studio they wont reset all of my columns. Is there a file I can restore or backup if this should happen
Shanon
Hi all of you,
I'm focused on writing a SSIS metadata application. I'm trying to get all the metadata for all the DTSX packages from a concrete server.
I'm loading successfully tasks as Execute Sql Task, ForEach task and so on but how could I do the same for
Data Flow Task and its components. ?żż
For a Data Flow I don't know how to begin...
If TypeOf tmpTaskHost.InnerObject Is ????????????????? Then
My central code is the following:
sServer = "TEST1"
pkgIn = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
'''''''''''''''Para cada carpeta creada a partir de MSDB
For Each pkgCarpeta In pkgIn
If pkgIn.Item(y).PackageDataSize <= 0 Then
pkgCarpetaSSIS = app.GetPackageInfos(pkgCarpeta.Name, sServer, Nothing, Nothing)
Else
pkgCarpetaSSIS = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
End If
If pkgCarpetaSSIS.Count >= 1 Then
While i < pkgCarpetaSSIS.Count
'cargar en memoria el SSIS en el objeto PAQUETE
pkg = app.LoadFromSqlServer(pkgCarpetaSSIS.Item(i).Folder & "" & pkgCarpetaSSIS.Item(i).Name, _
sServer, Nothing, Nothing, Nothing)
Dim task As Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask
Dim task2 As Microsoft.SqlServer.Dts.Runtime.ForEachLoop
Dim exe As Executable
Dim tmpTaskLoop As ForEachLoop = CType(exe, ForEachLoop)
Dim tmpTaskHost As TaskHost = CType(exe, TaskHost)
For Each exe In pkg.Executables
Select Case exe.ToString
Case "Microsoft.SqlServer.Dts.Runtime.ForEachLoop"
tmpTaskLoop = CType(exe, ForEachLoop)
Case "Microsoft.SqlServer.Dts.Runtime.TaskHost"
tmpTaskHost = CType(exe, TaskHost)
End Select
issue raises here.
If TypeOf tmpTaskHost.InnerObject Is ExecuteSQLTask.ExecuteSQLTask Then
n1 = tmpTaskHost.Properties.Item(33).GetValue(tmpTaskHost)
For y = 0 To 42
Console.WriteLine(tmpTaskHost.Properties.Item(y).Name)
Next
End If
Next
'contador de paquetes
i += 1
End While
y += 1
End If
Next
..
..
..
..
..
Thanks a lot for ideas and thoughts!!!!
As title, such as the PK, data type of each column, etc.
Thanks,
Ricky.
Hi,
I'm running the following query in SQL Server 2005:
select name from master..syslogins;
It is being executed from within a stored procedure.
For user 'sa' - I get the complete list of users.
For a user (say 'user1') with NO sysadmin privilege - I get only two names: 'sa' and 'user1'
Is there a way for me to retrieve the complete list of users even for 'user1' without making any changes to his profile (or making very MINIMAL changes to profile)?
I don't want to give sysadmin profile to this user.
I know 'GRANT VIEW ANY DEFINITION TO public' works, but don't want to do that either.
-Anshul
I wanted to see if anyone has explored posting a Report KB based on metadata in the RS portal? If so, have you found a way to post the information on the RS portal?
I really am looking to add additional properties to a report. Other than author and description. I would like to add 2 to 3 more fields that would feed over to the catelog table on the report server. Then write a few reports that will allow for definitions, metadata, and links to the reports.
Does anyone have any ideas for something like this?
hi, I try to post a new question about metadata refresh...even if i see other thread that work on a similar problem.
I have a ssis package that import an xml huge file (500 mb); These are the main step:
1) generate a XSD file against xml using xsd.exe utility
2) using xml task, make a diff between the old xsd and the new
3) if there are no difference, I start the data flow task that import xml in sql server; otherwise I stop all the task, edit the data flow task, change the xsd reference in advanced editor and then make many "double clik / OK" on every single flow....
The underling idea is that xml file change because some columns are added but these columns are not interesting for my elaboration, so i can ignore this new column and work without mapping it.
What I'm looking for is a way for make, via SSIS, the "double clik / OK" steps....in other words, to update the metadata.
Could anybody suggest me a way? it's a sort of macro, or keyboard recorder...I'm trying to study xml package configuration; is this a good way ?
another way is to give to the end user the task to update metadata; for making this I need to open the package editor (visual studio..:!) in a more confortable environment....For example, is possible to edit the ssis package in ms access? probably i know the answer...
please, help me!
thank tou in advance
alessandro
I'm trying to create a Data Dictionary view from system table info like tablename, fieldname, datatype etc. I can find all that I need except for the "description" field which is displayed in Enterprise Manager/ Repository metadata pane.
How can I locate this field so I can reference it in a view? Isn't it stored in a system table? Master, model? Where is it?
Thank in advance,
Phil
Hey there,Im wondering if there is a way to determine which views in my database use the "order by" statement. The reason I need this is because we need to migrate over to MS SQL 2005 where the order by statements are ignored within the views themselves. Now(in mssql 2005) you need to explicityly state the order by now when calling a view ie. select * from [viewname] order by column x, y desc, z instead of ie. select * from [viewname] where the view already had the applicable sorting done within the view.If those order by statements are ignored, some production software which rely on the ordered data will corrupt.Please let me know if there's a way to query the actual database and determine which views have 'order by' statements in them.thx
View 7 Replies View RelatedDid some searching and didn't seem to find what I'm looking for. I'm pretty new to SQL Server (most of my experience is on DB2 for z/OS).I'm building some new tables, and want to find a way to add comments to the metadata for the column. In DB2 the syntax is:COMMENT ON COLUMN TB_CREATOR.TB_NAME.COLUMN_NAME IS 'comments here';ORCOMMENT ON TB_CREATOR.TB_NAME (COLUMN1 IS ' comment here',COLUMN2 IS ' comment here', );Is there anything like this in SQL Server?Thanks!
View 9 Replies View RelatedI'm new to dba'ing so go easy on me. I'm looking after a brand new
installation of sql server 2005. I've created a simple db with 1 table but when I go to MSAccess ( as a newly created user) and create a linked table to sql database all the metadata is showing and I can't stop it.
I've tried going through securables but its still visible no matter how much I try to deny. From what I've read this shouldn't be possible as making metadata invisible should be standard.Can anyone throw any light on this?
Be careful when implementing views (from SQL Server 97/2K). SQL Serverstores the metadata on the view at creation (or the last time it wassaved). This means if you have:SELECT * FROM table1it will put all the fields of table1 in the view's metadata. If youthen change table1 and add (for example) another field, this fieldwill not be visible in the view until you open it in design view andclick save (to update it).
View 1 Replies View RelatedI had a pervious thread on a metadata KB but didn't get a response. So I was wondering if anyone has added on a web based help file app to SSRS? Something that you could assign additional metadata to each report. Something that would be searchable.
At this point SSRS doesn't provide enough fields for metadata. I am looking to assign some thing like:
1. Owner/Requestor of report
2. Fields in report
3. Description (other than the one provided)
4. etc..
Does anyone have any ideas of a add on, utility, or software?
Thanks
fsugeiger
I've tried looking in sys.syscolums and sys.syscomments, but I can't seem to find where the Description information is retain for a Field in the system tables -- any hints?
Thanks, Rob.
Hi
When using unpivot transformation, what exactly this error denote
"Incorrect UnPivot metadata. In an UnPivot transform, all input columns with a PivotKeyValue that is set, and are pointing to the same DestinationColumn, must have metadata that exactly matches "
data on which i was trying unpivoting is -
Name
Pd1
Pd2
Pd3
Pd4
Utsav
111
211
311
411
Verma
122
222
322
422
Nucleus
133
233
333
433
Noida
144
244
344
444
Good afternoon,
I'm having the following error when I run my mining model.
Errors in the metadata manager. The dimension with the ID of 'TMP ~MC-ID' cannot be found in the '' database.
I think the error is in caseprocessor.cpp at the ProcessCase method. There's a code snippet below:
The intention of the following code snippet is to run over the attributes of a case and put them on a vector that holds all the cases.
for (UINT iAttribute = 0; iAttribute < _cAttribute; iAttribute++) {
if(iAttribute == 0)
continue;
if(iAttribute == 1)
continue;
if(iAttribute == 2)
continue;
if(iAttribute == 3) {
vet_termos.push_back(rgdmstatevalue[iAttribute].ch);
continue;
if(iAttribute == 4)
vet_dados.push_back(rgdmstatevalue[iAttribute].r8);
DBL dblValue = 0;
if (_vbCategorical[iAttribute]) {
dblValue = (_bMissingAtRandom)
? ::DblCategoricalMAR(rgdmstatevalue[iAttribute])
: ::DblCategorical(rgdmstatevalue[iAttribute]);
}
if (_viewtype == CASEREADER::viewtypeSparse) {
if (dblValue != dblMissing) {
CHECK_STL_MEM(_vdblValue.push_back(dblValue));
CHECK_STL_MEM(_viAttribute.push_back(iAttribute));
}
}
else {
_vdblValue[iAttribute] = dblValue;
}
The attributes of the table are like following:
Index (iAttribute) == 0 -> This is an auto-incremental ID and is the primary key. Integer.
Index (iAttribute) == 1 -> Another ID. Non-key on this table. It is Integer.
Index (iAttribute) == 2 -> Another ID. Non-key on this table. It is integer.
Index (iAttribute) == 3 -> A term. It's a word. This is non-key and the type is varchar.
Index (iAttribute) == 4 -> Weight of the word. Non-key and type double.
As you can notice I also changed the union DM_STATE_VALUE definition on dmalgo.h to accept the reading of a string (if (iAttribute == 3)) :
typedef /* [public][public][public][public][public][public][public] */ union __MIDL___MIDL_itf_dmalgo_0000_0002 {
DM_STATE_INDEX si;
DOUBLE r8;
char* ch;
} DM_STATE_VALUE;
So... Does anyone can help me with the error I discribed on the metadata manager ? I would be very pleased with any help.
Thanks a lot for your attention.
-Renan Souza
Hi All,
Is it possible to capture the runtime metadata relating to the number of rows processed by a graph, the number of rows inserted/updated in the target blah blah using SSIS.
I have only seen metadata extensions for starttime, machine name etc. but not for this
THanks
Manish
Does anyone know how to obtain the physical server name that a SQL failover cluster instance is running on through the system tables or other database commands? Thanks in advance.
View 3 Replies View RelatedUsually, the XML Source will refresh its metadata when I don't want it to, and will cause me to repair all downstream metadata when it does. This time, I actually want it to refresh, as the schema changes I've made are significant.
Yet, I don't see any "Refresh" button in the XML Source UI. Even clicking "Browse" and browsing to the schema doesn't force a refresh. I've had to change the schema name (it has to be to an existing schema!) then change it back in order to force a refresh.
Is there a "Right" way to do this?
And is there any way to "right-size" the amount of metadata disruption this causes? I was pleased to see that updating a database schema limits the SQL Server Destination to updating only the changed metadata. Yet, any change at all to the XML Schema or XML file will cause all of the XML Source metadata to be invalidated, a process that takes several minutes at 100% cpu usage!
I am looking for anyone who can help me find an existing tool that will allow my programming team to build reports and data extracts simply and easily from a form of query builder. Unfortunatley, my database is structured as a metadata store using name data pairs. The actual structure is something like:
DateTime; Customer (integer); fieldname(string); fieldvalue (string)
In addition, I have another table that is structured similarly to store numeric data
DateTime; Customer (integer); fieldname(string); fieldvalue (Integer)
So, for the query tool to even start, it needs to scan both tables for distinct values in the fieldname column for a specific date/time range for a specific customer. Once that is done, it can display the field names and begin to search for the data I am looking for.
In addition, If I want the names of all people in the system who have bought a pie from my customer 1, the query tool will have to search for the fieldvalue "pie", return the key cells and then search for the fieldnames "Name" where the fields match (that or do it in a compound query).
Is there any tool anywhere out there that might have such capabilities?
Hello, I'm gettting a deadlock. One of the resources is a metadata lock in service broker.
this is from the deadlock graph.
<process id="process1fc2325c8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 5 CONVERSATION_GROUP($hash = 0xa927b1ef:0x691c259b:0xd5edc9)" waittime="13250" ownerId="3702420299" transactionname="GetDialogByHandle" lasttranstarted="2008-04-22T10:46:31.457" XDES="0x927baaf8" lockMode="X" schedulerid="2" kpid="7316" status="suspended" spid="173" sbid="2" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-04-22T10:46:31.457" lastbatchcompleted="2008-04-22T10:45:51.567" clientapp="SQL Queue Activation Service" hostname="82994-APP1" hostpid="2396" loginname="VIAGOGORSQueue.Activation" isolationlevel="serializable (4)" xactid="3702420292" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
would anyone shed some light??
thanks!
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 .
Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.
Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.
Any Help would be appreciated!
Dave.
SQL table and column metadata
Is there a way to add or update the column or table (using the extended properties) description metadata via T-SQL (from within a stored procedure) or via a program (such as VB.NET using ADO)?
These metadata properties are available via the SSMS interface:
Columns via the Column Properties/Table Designer/Description
Tables via the Table Properties/Extended Properties/[Extended Property Name]
Thanks in advance,
Mark