Metadata - Order By Information

Jun 14, 2006

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


ADVERTISEMENT

Connection Manager Keeps External Metadata Information

Mar 2, 2007

We have a Main package and which is calling 2 more other packages.
The first package contains a connection and we are using a Dataflow task. The data flow task has OleDB Data source which is taking getting columns using a Stored Procedure. And the output we need to write in a Flat File.

The second Package also contains the same(The same Tasks, Database and Stored Procedure Calling)
The difference is in the stored procedure Parameters. Based on the different parameters Stored procedures returns the different Columns and Rows output.
When we are trying to Get the second package output in OleDb Data source it shows all the columns which is the output of the First Package because it stores External Meta Data.

So My understanding is the Connection to the same database keeps the External metadata information with the connection and because of that it is always getting the same output columns in Ole DB Data source task in the second Package also.

How to Get my correct output from the second package in this case?
Or If we dont want to store external Meta data with the Connection then is that possible? If yes then How?


Thanks in advance.

View 4 Replies View Related

Extract Distinct Information And Order The Results

Sep 24, 2007

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.


I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description
5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.


SELECT * FROM SAMPLE
WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query
5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards
Deceptive

View 9 Replies View Related

SecurityException Class SQLServerDatabaseMetaData's Signer Information Does Not Match Signer Information Of Other

May 23, 2007

Hello,

I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g.
java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package

I found this Feedback but it seems to be closed.

A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.

Can anyone help me with this problem?

Simon

View 4 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
 but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!

View 2 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Order By Clause In View Doesn't Order.

May 18, 2006

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth



If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View 16 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons


Any ideas?

View 4 Replies View Related

MetaData

Jul 20, 2005

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 Related

Insconsistant Metadata

Aug 22, 2001

Hi 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!

View 2 Replies View Related

Retrieving Metadata

Jul 29, 2005

Hi,Is it possible to get metadata (i.e. descriptions of tables etc.) insql-server? In Oracle you can retrieve this information with tables likeall_objects, user_tables, user_views etc. For example, this query selectsthe owner of the table 'ret_ods_test' (in Oracle!):select ownerfrom all_objectswhere object_name = 'ret_ods_test'What's the equivalent in sql server?Thanks a lot.

View 2 Replies View Related

SSIS Metadata...

Mar 18, 2007

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.



View 5 Replies View Related

Metadata Refresh?

May 15, 2006

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?

View 11 Replies View Related

SSIS Metadata

Feb 26, 2008

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

View 7 Replies View Related

Metadata Refresh

Feb 26, 2007

-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.

View 1 Replies View Related

Metadata Question

Apr 4, 2008

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?

View 5 Replies View Related

Metadata Definition ?

Apr 5, 2006

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.

View 2 Replies View Related

METADATA DEPENDENCY

Nov 9, 2007

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

View 14 Replies View Related

MetaData Backup

Feb 12, 2008



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

View 3 Replies View Related

Metadata Application

Sep 26, 2007

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!!!!

View 4 Replies View Related

How To Get The Metadata Of A Table

May 11, 2007

As title, such as the PK, data type of each column, etc.



Thanks,

Ricky.

View 6 Replies View Related

Metadata Visibility

Feb 25, 2008

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

View 4 Replies View Related

Metadata/Report KB

Jun 26, 2007

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?



View 2 Replies View Related

Metadata Update

Dec 28, 2006

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

View 2 Replies View Related

Where Is &#34;description&#34; Col In Repository Metadata

Apr 20, 2000

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

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved