Problem With Analytic Sql Function (The OVER SQL Construct Or Statement Is Not Supported)
Sep 28, 2007
Hi All!
Could You comment the next situation:
I'm configuring my TableAdapter just like Scott Mitchell does in his tutorial
http://www.asp.net/learn/data-access/tutorial-70-vb.aspx
The only principal difference is that I need Insert/update and delete
methods to be generated (His aim is only SELECT).
I'm also using analytic function (ROW_NUMBER) and I'm also gettin
warning “The OVER SQL construct or statement is not supported.� You
say then that it could be ignored. But, in this case statements to
modify data (insert/update and delete) aren't being generated, though
after warning SQL command is executed without errors.
So, the question is obvious - why does this warning occur and how must
I perform configuration of TableAdapter based on SQL query with
analytic function?
I need to transmit data from a ##table to an .xls file. The data is exported only for the first time. However, after a while the table will be dropped by SQL Server 2005 automatically.
I'm using Execute SQL Task to check for the existence of an ##Table. And, if does not exists the ##table should be created or something like the example below.
Ex: IF NOT EXISTS ( SELECT * FROM ##StateProvince )
SELECT * INTO ##StateProvince FROM Person.StateProvince GO
To keep the session of the ## Table active, but the following error is thrown :
The NOT SQL construct or statement is not supported.
I'm trying to run following script in sql command mode in OLE DB Source and it giving me "The OVER SQL construct or statement is not supported." error msg.
Code Snippet
Drop
Table #Tmp_EXT_AATransactions
Select row_number() over (partition by A3.jrnentry,A32.aaGLDistID order by A32.aaGLDistID,A33.aaTrxDimID ) as rownum,
A33.aaTrxDimID,
A33.aaTrxCodeID,
A3.jrnentry,
A32.aaGLDistID,
G2.TRXDATE,
A3.GLPOSTDT,
A3.aaTRXType,
A31.ACTINDX,
A32.DEBITAMT,
A32.CRDTAMNT,
(A32.DEBITAMT - A32.CRDTAMNT) AS Amount
into #Tmp_EXT_AATransactions
From dbo.AAG30000 A3
Inner Join dbo.AAG30001 A31
On A3.aaGLHdrID = A31.aaGLHdrID
Inner Join dbo.AAG30002 A32
On A31.aaGLHdrID = A32.aaGLHdrID
And A31.aaGLDistID = A32.aaGLDistID
Right Outer Join dbo.AAG30003 A33
On A32.aaGLHdrID = A33.aaGLHdrID
And A32.aaGLDistID = A33.aaGLDistID
And A32.aaGLAssignID = A33.aaGLAssignID
Inner Join dbo.GL20000 G2
On A3.jrnentry = G2.jrnentry
And A31.ACTINDX = G2.ACTINDX
And A31.SEQNUMBR = G2.SEQNUMBR
--Where A3.jrnentry in ( '54227','54222','54225')
Select [JrnEntry],
[aaGLDistId],
[TrxDate],
[GLPostDT],
[aaTrxType],
Isnull([1],0) as Dim1,
Isnull([2],0) as Dim2,
Isnull([3],0) as Dim3,
Isnull([4],0) as Dim4,
Isnull([5],0) as Dim5,
Isnull([6],0) as Dim6,
Isnull([7],0) as Dim7,
Isnull([8],0) as Dim8,
Isnull([9],0) as Dim9,
Isnull([10],0) as Dim10,
[ActIndx],
[DEBITAMT],
[CRDTAMNT],
[Amount]
into #Tmp_EXT_AATransactions_Final
From
(
Select [aaTrxDimID],
[aaTrxCodeID],
[aaGLDistID],
[JrnEntry],
[TrxDate],
[GLPostDT],
[aaTrxType],
[ACTINDX],
[DebitAmt],
[CRDTAMNT],
[Amount],
Row_Number() Over (Partition By [aaTrxDimID],[aaGLDistID],[JrnEntry] Order By [aaTrxDimID],[aaGLDistID],[JrnEntry]) RowId
From #Tmp_EXT_AATransactions
) as Data
Pivot
(
Max([aaTrxCodeID]) For [aaTrxDimID] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) As PVT
Select *
From #Tmp_EXT_AATransactions_Final
I can parse and preview it successfully in OLE DB Source Editor but i can't see any columns in columns mapping. So when i click on Build Query in OLE DB Source Editor i get this error message. Also Its working fine in Management Studio so don't know what's the problem.
I am trying to use a Pivot T-SQL statement in the Table Adapter Configuration Wizard in Visual Studio 2005. I get the error message "The Pivot SQL construct or statement is not supported". Then it executes the SQL statement as if there were no error. Unfortunately, it will not create the table adapter because of the error. Below is the T-SQL statement I am using.
SELECT OrderNumber, OrderDate, custlastname, [1001] AS Dept01, [1002] AS Dept02, [1003] AS Dept03, [1004] AS Dept04, [1005] AS Dept05, [1006] AS Dept06, [1007] AS Dept07, [1008] AS Dept08, [1009] AS Dept09, [1010] AS Dept10, [1011] AS Dept11, [1012] AS Dept12 FROM
(SELECT Orders.Ordernumber, Orders.OrderDate, Customer.custlastname, DeptID, OrderDetail.DetailAmount FROM OrderDetails od JOIN Orders ON Orders.OrderNumber = od.OrderNumber JOIN Customer ON Orders.CustomerID = Customer.CustID WHERE (DATEPART([Year], OrderDate) = '2006')) p
PIVOT (SUM(OrderDetailAmount) FOR DeptID IN ([1001], [1002], [1003], [1004], [1005], [1006], [1007], [1008], [1009], [1010], [1011], [1012])) AS pvt ORDER BY OrderNumber
The statement works fine in Management Studio so I know the syntax is correct.
select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma] from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value
I believe I need to use the LIKE clause but i'm not sure how.
sorry to b a pest again! Before I made the decision to change the DB used in my app from SQL Server Express to SSCE, I had no problems with constructing a SELECT statement as laid out in the Title.
Basically, I have 2 tables with a one-many relationship between them. In the Parent table, I had a SQL Statement as follows:
SELECT DeptID, DeptName,
(SELECT DISTINCT COUNT(Active) FROM Documents WHERE (Documents.DeptID = Dept.DeptID) AND (Documents.Active = 'True') AS CountOfActive FROM Dept
Now in SSCE 3.1, I get an "Unable to parse query" error message when I construct the same SQL statement in my dataset designer.
I am writing a Reporting Services report pointed at an Analysis Services data cube, and in order to have more control over the MDX, I needed to switch my SSRS data source from the .NET Provider to the OLEDB provider (from ".NET Framework Service Provider for Analysis Services" to "OLEDB Provider for Analysis Services", that is). I'm also entering my MDX in as an expression in the data source.
I noticed that when I summarize this data in the table footer on the report, the summary values that use the Aggregate function (=aggregate(fields!fieldname.value) for example) are blank.
I'm assuming that since the OLEDB provider is an "older" data provider for SSAS data and the Aggregate function was new in 2005 with the .NET provider, the OLEDB provider doesn't support the Aggregate function. I was wondering if someone could confirm that this is the case?
Also, is there any other functionality that is not supported by OLEDB that would be supported by the newer .NET provider (aside from the ability to generate fields, parameters, and use the MDX query designer, of course)?
Hi, I am trying to create a maintenance plan on an MSX server and push it out to a TSX server. When I try to run the job on the TSX server it goes into suspended status.
If I look in the SQLAGENT log I see the following error
2008-04-30 15:04:11 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:08:19 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:09:28 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:27:36 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load. The job has been suspended 2008-04-30 15:35:13 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system) 2008-04-30 15:50:27 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load. The job has been suspended
my sql info is Microsoft SQL Server 2005 - 9.00.3239.00 (X64) Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Hi, Assume there is no sale with over 2000 units, the first query returns no row while the latter returns 1 row with a null value. I am not questioning the result, I would like to understand conceptually what semantic difference between the Sql99 analytic function and regular aggregate function makes the result different in this case.
select sum(Quantity) over() from orderdetails where Quantity > 2000
select sum(Quantity) from orderdetails where Quantity > 2000
I had a table named "info" and I would like to show Lot_Id as distinct. Besides that , I would also like to show wafer_id and wafer_starttime. May I know how to construct this using select statement?
My head hurts...it might be an easy one for many people, but cant seem to figure a way to do it.Ok, I have a table with a field called Ad_Price. This field is a nvarchar one, theres many different value in it, all of them corresponding to a price. Now, what I want to do is only get the row that are not written in a good price format (like123$ is wrong, but 123.00 is good, but only value following this exemple)For exemple, if I have all these value145.876785.34654$45 to negociate1bvcaa0.01876.556the value I want to have are:654$45 to negociate1bvcaa876.556because they dont follow the format I wantso im not sure if my explication was clear enough..all in all what I want to complete is the WHERE part of my SQL instruction...what should I put in:SELECT *FROM AdsWHERE ???????thansk for taking the time to read this
I am using the following code to construct an SQL 7.0 View Column
"CASE WHEN [DailyHours] > SUM([TransAmt]) THEN 0 ELSE 1 END"
and it works great!!
However, when I try the same line in SQL 2000, I get the message "The query designer does not support the case SQL construct"
The help screen is no help as all it says is "the syntax you entered is valid but is not supported visually by Query Designer. Be sure the verify your syntax before saving."
It will not let me save, so I'm not sure what to do from here now????
Dear Group, I'm upgrading a "classic" ASP app to ASP.NET 2.0. The database is stored on a SQL Server box in-house. I'm looking to inject a bit of flexibility into the SQLDataSource SelectCommand property. What I mean is this: depending on a selection a user makes in the form, I'd like to present a GridView object that displays data filtered by the user's selection. So I took the original long query string from ASP and broke it in two pieces. The first piece contains the bulk of the query and it ends just after the WHERE clause. The second piece is the ORDER BY clause. I'd like to have the ability to inject an additional term at the end of the WHERE clause (using AND). That would be the filter. For example,SELECT LastName, FirstName, InOut, TimeEntered, PhoneExt FROM TimeClock WHERE TimeEntered > '6/17/2008' AND EmploymentState > '' ORDER BY LastName, FirstName So, in between the "AND State > '' " and the ORDER BY clause I'd like to insert something like this:AND Department = 'Production' I'd like the SelectCommand of the SQLDataSource to look like this:SelectCommand="<%= (sql1 & " AND Department = 'Production' " & sql2) %>" sql1 and sql2 are defined in a <script runat="server"></script> block in the page <head>. The following errors occur even if I have a single variable containing the whole SQL query string (i.e., SelectCommand="<%= sql1 %>"). When I try to run the page I get this error:Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'. If I remove the quotation marks from around the SelectCommand statement I get:Parser Error Message: Server tags cannot contain <% ... %> constructs. What's funny about this is that the ConnectionString property of the SQLDataSource looks like this:ConnectionString="<%$ ConnectionStrings:PersonnelDatabaseConnectionString %>" No complaints from the compiler about the ConnectionString. Any ideas? Am I simply going to be unable to construct a SelectCommand on-the-fly?
Hi,I have three dropdown lists branch, month & year. Based on these I need to retrieve data in my Datagrid.I have written the following SQL statement & code in my Refreshgrid(): SqlCmd.CommandText = " SELECT * " SqlCmd.CommandText += " FROM HSECData" SqlCmd.CommandText += " where branchno=" &Branchno SqlCmd.CommandText += " And yearno=" &YearNo SqlCmd.CommandText += " And monthno=" &monthNoThis is working fine, initially the datagrid is empty and then displays data when I select say, branch- Melbourne, Year-2005, Month-October and other exisiting combinations.The problem I am stuck at is when the page loads, initially I want all the data to be displayed in datagrid, i.e. for 'All' branches, 'All' years and 'All' months and then when I select combinations of these dropwdown lists as above the data should be filtered and displayed in those selected combinations. but as of now I get and empty datagrid when I select 'All' from these 3 drop down lists or when the page opens the first time.Can someone kindly guide me with the correct SQL cinstruct to achieve both the requirements?Thanks,Aartee.P.S. I am sorry if I have posted this question in a wrong category in the forum, I was unsure whether I should post it in Datagrid category or SQL server category, I thought this was my best bet.My aplogies.
Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks
I am working with a form that I wish to construct a dynamic query from the results of. The forum has a date range and two radio buttons. Each radio button enables a list of items that can be clicked. So for example, if we assume the question,
"What is your favorite food, and what toppings do you like on it?" where the radio buttons are foods, the list boxes are toppings. Assuming the user can choose a Hamburger or a Salad with generic toppings, their choices are as such:
They can choose a Hamburger, with every topping They can choose a Hamburger with a single topping. They can choose a Hamburger with multiple toppings. They can choose a Salad with the same combinations as above. They cannot choose both a Hamburger and a Salad - mutually exclusive items.
Then, I wish to construct a query that, based on the conditions above, retrieves information relavent to their criteria, such a the number of food items to choose from, their price, etc. - basic information. What is the most efficient way to do this? Should I write a stored procedure with numerous conditionals and all available parameters, constructing the sproc as such:
BEGIN DECLARE @query varchar(300) SET @query = 'SELECT COUNT(DISTINCT ' + @FoodType + ') '
IF @FoodType = 'Hamburger' SET @query = @query + 'FROM Hamburgers '
ELSE SET @query = @query + 'FROM Salads '
IF @toppings <> 'ALL'
SET @query = @query ' WHERE Toppings = ' + @toppings
EXEC (@query)
Apologies of this syntax is incorrect, but you get the general idea. Of course, this is a small example - in reality, I would have 5-10 conditional requirements Or, should I generate a stored procedure (or simple query) for each operation? For example, assuming each is a stored procedure:
GetHamburgers <-- would get Hamburgers with all toppings GetHamburgersWithToppings GetSalads GetSaladsWithToppings
What is the best method for what I wish to achieve? What is fastest? Is there a better way than I have listed? Thank you.
Again, this is a small example, but I hope someone can help.
I am using the Instr(), Len(), and Mid() function in my SQL query and I keep getting errors stating those are not recognized functions. IS this correct? are there any equivelants?
In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.
I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".
What do I need to do to make this work? All help is greatly appreciated!
I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code: Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
CREATE FUNCTION GetPerson (@SSN integer, @NamePrefix varchar(10), @FirstName varchar(30), @MiddleName varchar(30), @LastName varchar(40), @NameSuffix varchar(10), @HomeID integer, @MailID integer, @DOB timestamp, @Gender varchar(1), @MaritalStatus varchar(1)) RETURNS integer as BEGIN DECLARE @PersonID integer set @PersonID=0 if @SSN>0 and @SSN<999999999 Begin select Min(lngPersonID) AS PersonID from Persons where lngSSN=@SSN End ELSE if @SSN is not null BEGIN IF @LastName is not null and @FirstName is not null and @MiddleName is not null and @NamePrefix is not null and @NameSuffix is not null Begin select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @NamePrefix and strFirstName=@FirstName and strMiddleName=@MiddleName and strLastName=@LastName and strNameSuffix=@NameSuffix and lngSSN=@SSN End ELSE if @LastName is not null BEGIN select MIN(lngPersonID) as PersonID from Persons where strLastName=@LastName and lngSSN = @SSN END END return (@personID) END
I m having problem with the "Select" function the error I m getting is Select statements included within a function cannot return data to a client (error 444)
Cann I use "select" statement in the function? If not what is the alternative?
I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.
I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!
The enc_timestamp is my date field.
Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname From template_audit a Join user_mstr b on a.created_by = b.user_id GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241) ORDER BY a.enc_timestamp, b.first_name, b.last_name;
how should i use replace function in sql statement??
in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.
my stored procedure accepts two parameters. Is there any way to solve my problem??
I've got a complex UDF I need to call twice in a select statement, as shown below:
SELECT
dbo.myFunction(colName), dbo.myFunction(colName) * 2 FROM
tableName
The problem is, the result of "dbo.myFunction(colName)" is not being cached, so the function is executed again for "dbo.myFunction(colName) * 2". This is having a significant impact on performance (doubling the amount of time it takes for the query to execute).
Is there any way I can write the query so that dbo.myFunction is only executed once?
WHEN 1 THEN (Select name From Table2 where Table2.Code=Table1.Code) WHEN 2 THEN (Select name From Table3 where Table3.Code=Table1.Code) WHEN 3 THEN (Select name From Table4 where Table4.Code=Table1.Code) END
FROM Table1
Do I need to use Function instead of CASE for better performance ?
Hi, All I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed: <% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind(); dataSource.UpdateCommand = "";%> <asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView> The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row. Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
Hi,In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:Codes in ASPX.VB: Dim SQL as String="SELECT * From Table1" IF UserOption <> Null then SQL = SQL & " WHERE Column1=" UserOption" End ifNow, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).Hope you can understanding what my mean and give me advices.Regards,Raymond
