Help With Solution For Use Order BY Inside Function And VIEW On The Sql Server
Apr 8, 2008
help with solution for use order BY inside function and VIEW on the sql server
i explain the problem
i am a web developer in asp page
i BUY an DATA grid object for my web site
the problem in this object i can use only ONE field to use ORDER BY
can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ?
so whan i do
SELECT *
FROM dbo.tb_pivot_big
the sql server ORDER for ME the result and run this (inside the sql server )
Code Snippet
SELECT *
FROM dbo.tb_pivot_big
ORDER BY new_unit, Fname ASC, val_orginal desc
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database. I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple... One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')' EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed). Does somebody know how to make what i want?? I prefer to avoid using temporary tables. Thanks!
Good afternoon, This is a simple question....how can I order packages by name in solution explorer ? I have a solution with more or less 50 packages and it's very ime consuming to find a package ! ehehe
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?
I want to download data from an AS/400 to a SQL 2005 Database.
Within Sql server 2000 it was possible to do more than 1 transformation with just 1 source connenction and 1 destination.
In SSIS I have created:
- new solution
- data source
- Data source viewer ( I made named queries to format the data as i want it to be in Sql)
Although it is possible to view the named query data it does not seem to be possible to import the data in the data viewer into my sql 2005 database. (For each dataflow I have to define each query again)
I have added the data source in the connection manager.
Also I am searching to import more than 1 file in 1 package !
I can use however a Dts-2000 package but why then use SQL server 2005
I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").
How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....
I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.
Function: ----------------------------------- ALTER FUNCTION [dbo].[func_Split] ( @DelimitedString varchar(8000),
[Code].....
Not sure what I am missing in the above view why it doesn't split the string.
i have created the table empid,empname,dob,salary for the insert i need to create procedure of insert empid,empname,dob,salary i got the function for autogenerated number for empid mmy question how to put function inside the procedure? my function below,pls guide me its very urgent. create function NextCustomerNumber() returns char(5) as begin declare @lastval char(5) set @lastval = (select max(customerNumber) from Customers) if @lastval is null set @lastval = 'C0001' declare @i int set @i = right(@lastval,4) + 1 return 'C' + right('000' + convert(varchar(10),@i),4) end
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working... Thanks ______________________________________________________________________ set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey from rt_'+@platform+'_CallLegs where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5)) exec @sql
when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in (SELECT Panel_Position FROM tblMeasurement_Results WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ? GROUP BY Panel_Position)
i get this error returned: 0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.
I don't know where inside the sql string a conversion is necessary/fails. Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in (SELECT Panel_Position FROM tblMeasurement_Results WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ? GROUP BY Panel_Position)
The only difference between the 2 statements is the ABS() function inside the sub query.
More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.
I would appreciate it very much if someone out there knows an answer or a hint and could tell me.
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
I have created an assembly with permission set safe, and a function inside the assembly. The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like: "The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error. And my .dll is also signed.
Has this something to do with writing something a config file? I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.
I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.
I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.
I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.
However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue AS ( declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt return @ConcatenatedString )
I have a view in SQL SERVER 2005 which includes ORDER BY in my SQL expression. When I press "execute", the results of the view come out ordered by the selected field, as they should be. When I close the view and choose "Open View", the results come out not ordered. The same thing happens when I'm trying to show the results in a GridView. Why does it happen?
I have to provide the Order by Value from Front End (VB) to the DB , so that I can receive the Crystal Report in the order that I have given. thanks in advance... :confused:
i m required to use order by in a view, how to do it, simple order by doesn't work for it, any other way,i have heard there is one in oracle, but wat abt SQL Server???:confused:
I have make a view where i have a order by when i execute my view in modify my result are allright but when i say open view to see my view it is not in order by.
I use the view in a asp site to the internet and i can't use order by here because the field i orderby shall not come up in my site, i only use this field to order by
I have downloaded and installed SQL Server Express 2003 Adv.. I have created a new database in it and also a table called Customers. In that I have created one field called CustomerName as nvar(255). I have made 10 records by filling in the field with various names. I have then created a new view and asked it to order the records in descending order. When I save and show the view it shows the records still in the order that I inserted them not in descending alphabetical order. If I make it show in ascending order the same problem still occurs. Collation settings are correct for the computer at Latin1. However, I have tried a few others, but none make any difference. SQL Server Express 2003's view will not list my Customers in either ascending or decending alphabetical order. Can anyone give me any suggestions?
I have the view below and if I use vwRouteReference as the rowsourcefor a combo box in an MS Access form or run "SELECT * FROMvwRouteReference" in SQL Query Analyzer, the rows don't come throughsorted by Numb.Everything I've read on the web suggests that including the TOPdirective should enable ORDERY BY in views. Does someone have an ideawhy the sorting is not working correctly for this particular view? thanks.CREATE VIEW vwRouteReferenceASSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,tblItem.Numb, tblQuestion.DescrPrimary AS TypeFROM tblItem INNER JOIN tblQuestionON (tblItem.ID = tblQuestion.Item_ID)WHERE (((tblItem.Category_ID)>0))UNIONSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS TypeFROM tblItemWHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR(tblItem.Type) = 'Route'))ORDER BY tblItem.Numb
When I create a view in SQL and include an ORDER BY clause i can see itin Management Studio. However, when I call the same view from an ASPpage the order goes completely haywire.Any ideas?
I have created a view which uses 3 tables, i also have a sort on one of the columns. However when I open the view the sort does not work. It does however sort the view correctly when executing the query within design view
Can anyone explain this or is it a bug within SQL Server Express 2005?
I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue.
CREATE VIEW [dbo].[vw_SalesRep_Chaining]
AS
SELECT Top 100 percent Rep_id AS RepId,
Rep_cd AS RepCode,
Region as Region,
CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico'
WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro'
WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other'
I have a view created using the following code. The view works perfectly but does not order by the name column as I've asked it to do. In the view designer if I click on execute then the order is applied but if I save the view and run it externally (i.e. in an ASP page or within the management terminal) it does not order correctly and seems to order by the Id column.
Any help would be much appreciated. Here's the code:
SELECT TOP (100) PERCENT dbo.Members.DivisionID, COUNT(*) AS Members, dbo.Country.Name FROM dbo.Members INNER JOIN dbo.Country ON dbo.Members.DivisionID = dbo.Country.CountryID AND dbo.Members.CountryID = dbo.Country.CountryID GROUP BY dbo.Members.DivisionID, dbo.Country.Name ORDER BY dbo.Country.Name
And another, more simpler view, that doesn't sort as it's supposed to.
SELECT TOP (100) PERCENT CountryID, Name, RegionID, IsActive, HasFlag, URL, Comments FROM dbo.Country ORDER BY Name
I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.
This is the view :
CREATE VIEW hsarti01_VD1 AS SELECT TOP 100 PERCENT * FROM hsarti01 WITH(index(hsarti01_PK)) ORDER BY 1,2 desc,3,4
When I do the "select * from hsarti01_VD1" in sql server 2000, I see in the result that the order by is been using. but in sql server 2005 it's just using the order of the primary key and not the order by !
I built a view that runs against the aspnet_WebEvent_Events table, part of the ASP.Net Health Monitoring.
Here is the view SQL: SELECT TOP (100) PERCENT EventTime, EventType, EventSequence, EventOccurrence, EventCode, EventDetailCode, Message, ApplicationPath, ApplicationVirtualPath, MachineName, RequestUrl, ExceptionType, Details FROM dbo.aspnet_WebEvent_Events ORDER BY EventTime DESC
What I found is that when the query is ran within the View Designer that the view is ordered by the correct EventTime (datetime) field.
But when the view is ran from right-clicking on the view and selecting "Open View" the order by does not take place.
