Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages.
Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String)
Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType)
Dim Connection As New SqlConnection(ConnectionString)
Dim Command As New SqlCommand(QueryString, Connection)
Command.Connection.Open()
Return Command.ExecuteReader()
End Function
It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader).
Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!
When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
hi i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting.. chakri
I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. Incorrect syntax near '.'.
Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476
SELECT dv.product , dv.itemname , dv.u_vlgx_plc, dv.shorted , dv.onhand , dv.po_num , t10.docduedate FROM (SELECT t3.product , t7.itemname , t2.u_vlgx_plc, t3.shorted , t4.onhand , t6.cardname AS t6_cardname, MIN( CASE WHEN t8.linestatus = 'O' THEN t9.docnum ELSE NULL END) po_num FROM (SELECT t0.product product , SUM( CASE WHEN t0.qty_topick <> t0.qty_picked THEN t0.qty_topick - t0.qty_picked ELSE 0 END) shorted FROM rbeacon.dbo.shipline2 t0 INNER JOIN rbeacon.dbo.shiphist t1 ON t0.packslip = t1.packslip WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date GROUP BY t0.product ) t3 INNER JOIN comparison.dbo.vlgxplc t2 ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS LEFT JOIN (SELECT t0.product AS product, SUM(t0.quantity) AS onhand FROM rbeacon.dbo.binlocat t0 GROUP BY t0.product ) t4 ON t3.product = t4.product INNER JOIN wbau.dbo.oitm t5 ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.ocrd t6 ON t5.cardcode = t6.cardcode INNER JOIN wbau.dbo.oitm t7 ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.por1 t8 ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.opor t9 ON t8.docentry = t9.docentry WHERE t3.shorted <> 0 GROUP BY t3.product , t7.itemname , t2.u_vlgx_plc, t3.shorted , t4.onhand , t6.cardname ) dv
OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10
GROUP BY dv.product , dv.itemname , dv.u_vlgx_plc , dv.shorted , dv.onhand , t10.docduedate, dv.po_num , dv.t6_cardname ORDER BY dv.u_vlgx_plc, dv.t6_cardname, dv.product
I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
I have created the following query... and need to get the total records display for my report. I have tried adding in the count(*) function to my select list, but I get errors. Any help is appreciated.
CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60)) RETURNS @copdmailinglist TABLE ( list_ varchar(60) , title_ varchar(255) , desc_ varchar(255), message_id int , txt varchar(255) , cnt int , cnt_txt varchar(255) )
--Returns a result set that lists all the copds AS BEGIN WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS (select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc left outer join ListManager.dbo.get_mailingidcopd_('') gmc on gmc.name_ = osc.title_ where list_ = @list_ )
-- copy the required columns to the result of the function INSERT @copdmailinglist SELECT list_ , title_ , message_id , txt , cnt , cnt_txt FROM ListManager.dbo.[List Copd] RETURN END GO
i m getting error that Incorrect syntax near the keyword 'WITH'.
select t.value,sum(t.countvalue) as totalcount from (
select
sm.value,count(sm.value) as countvalue
from subjectbase s join stringmap sm on s.organizationid = sm.organizationid
inner join audit a on s.subjectid=a.subjectid
inner join incidentbase i on i.subjectid=s.subjectid
where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and sm.attributename = 'contractservicelevelcode' and sm.ObjectTypeCode = 112
group by sm.value
) t
group by t.value
)
value totalcount ------------------ NHLBI Employee329 NIH Employee329 Public329 VIP329
instead of different values i m getting same... there is something wrong in joins..can anyone help me?
I have a table (cars) with 3 fields:VIN, Class, sell_price101, sports, 10000102, sports, 11000103, luxury, 9000104, sports, 11000105, sports, 11000106, luxury, 5000107, sports, 11000108, sports, 11000109, luxury, 9000i need to write a query that WITHOUT USING A FUNCTION will return themedian selling price for each class of car. result should look like:Class, Med_Priceluxury, 9000sports, 11000thanks to all u SQLers
Code Snippet Declare @DBName as varchar(100) Declare @Query as varchar(8000)
SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue
Set @Query =' SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1 FROM ReciptItems LEFT OUTER JOIN ' + @DBName + '.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1'
I create a function to call stored procedure, and query a view, but I am having troubles with quering table-value function. I looked for an example on the net on how to do it, but I couldn't find any (I find only .net examples :-) )
Can anyone direct me to an example, or write a small sample?
I have a SQL database that has a function that returns an id value from a table after you pass in a text variable. I would like to test this functionality in Query Analyzer but when I try to do it this way: exec dbo.fnc_ORGUNIT_GetByName 'Dummy' It just says 'Query executed successfully' without any resutls in the results pane. What am I doing wrong? Thanks!
I am in the middle of creating an editable DatGrid:
Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs) AccessoryGrid.EditItemIndex = e.Item.ItemIndex End Sub
Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs) e.NewValues.Add("@AccessoryID", AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex) ("AccessoryID")) e.NewValues.Add("@AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text) e.NewValues.Add("@AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text) e.NewValues.Add("@AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text) e.NewValues.Add("@AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text) e.NewValues.Add("@AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text) End Sub
For some reason, I get an error message like this: Server Error in '/' Application.
Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?
I have been trying to get rid of this error by trying to change the field type within my database with no success, I keep on getting the same error either way.
Quote: select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName, count(f.memberID) as numMembers FROM Members a INNER JOIN groupLeaders b ON a.memberID = b.memberID Inner JOIN Chapters c ON c.chapterID = b.chapterID LEFT JOIN divisions d ON d.divisionID = c.divisionID Inner Join groupsOfEight e ON e.groupLeaderID = b.groupLeaderID Inner Join groupOfEightMembers f ON f.groupOfEightID = e.groupOfEightID Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName Order By divisionName, numMembers
This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.
Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.
[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.
Here is the query I started to build before I ran into serious trouble:
Quote: select a.divisionName, count('c.memberID') as numMembers From Divisions a Inner Join Chapters b On b.divisionID = a.divisionID Inner Join chapterMembers c ON c.chapterID = b.chapterID Left Join Members d ON d.memberID = c.memberID LEFT Join groupLeaders e On e.memberID = d.memberID Group By a.divisionName
This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...
Have I done an okay job of explaining the problem?
The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.
My SQL is extremly rusted so I need some help with a very basic function. I have a character field which is built up using a category code + '-' + number. The problem I have is that the category codes are all different lengths and the items were added using 9 instead of 09. I'm trying to clean up the data so that the same item with e.g. category code DZ20 cannot be added as DZ20-1 and DZ20-01. How do I find the position of the '-' in the Query Analyser for MSSQL 2000?
i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks. Thank you, Thomas
insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms) SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#, dbo.tblCustomersIOA.Active, iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) , dbo.tblCustomersIOA.CreditLimit, FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN dbo.tblCustomersIOA LEFT OUTER JOIN WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);
select bicycle_shop.bicycle_shop_id, bicycle_shop.company_name, order_.unit_price from order_ inner join bicycle_shop on order_.order_serial_number = bicycle_shop.order_serial_number order by order_.unit_price DESC;
this query will bring up a few records for each shop, of the one or more bikes they bought and each bike's price, how do i have these come out to a sum(of the price), so that each bike shop will have a sum of revenue. someone told me i need to use group by and the sum function but i dont know how
I'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB INTO #TMP1C from Vantage.dbo.SBB_BASE (NOLOCK) where PRIN_SBB in (6000,7500)
[Code] .....
Not entirely sure what I am doing wrong, or why the numbers are coming back so high.
Ok, im fairly new to SQL (real world use) uni wasn't all that, so im throwing out my idea for some feedback, and to see if it is at all possible.
I have 2 tables, 1 is called Hotels, and 1 is called Flights. I want...to list every Hotel, with price, availability date, and Flight date from a (GetDate()) function, up untill a date specified in a query.
At first this sounds easy. However... a hotel can be available for up to a month or two at a time, so at least 60 records for 1 hotel.
In this query i want a SUM of the Hotel price between the GetDate() date, and the end date specified in the query.
I wrote a function and a SQL to get the 3 columns Date,Total Orders & Amount, for dates between Date Started and Date Completed if I pass different Dates in the SQL I get the correct result but if I pass same dates then I don't get the result I am looking for .
For Instance,if I give Date From=1/02/2008 ;Date To=1/8/2008(Different dates )I am getting values for all the three columns. But I give same dates for Date From=01/02/2008 ;Date To=01/02/2008 then I am not getting the records.
Some how I could not trace what could be the error in my SQL Function.
I appreciate if I could get some work around for this.
Thanks!
create function dbo.CreateDateList(@start datetime, @end datetime) returns @t table ( [date] datetime ) as begin if @start is null or @end is null return
if @start > @end return
set @start = convert(datetime, convert(varchar(10), @start, 120), 120) set @end = convert(datetime, convert(varchar(10), @end, 120), 120)
while @start < @end begin insert into @t ( [date] ) values (@start) set @start = dateadd(day, 1, @start) end
return end
**********SELECT qUERY***********
SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID GROUP BY Convert(Varchar(15), l.[date],101) Union SELECT 'Grand Total' as Total,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID Order by Date
Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh
SELECT * FROM TRANSACCTION_HISTORY WHERE TXN_DATE=@RepDate
For the query parameter @RepDate, I would like to pass a function to it. The function is a calculation of date based on today's date (e.g. Today() function to be simple). So that users don't have to type in the date every time.
Now the question is: is it really possible to do that in RS? Because I am getting a type conversion error, when I put Today() in the Define Query Parameters disalog box.
I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of:
Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate](?, -1) AS [PreviousTradeDate]" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0.
The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable.
Odd thing is, if I replace the ? in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.
I have a question that seems easy but I can not figure out...
Premise:
Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.
I have a Dataset that has a calculation for a column within a select statement
Query Pseudocode:
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from
(subquery"blah" )
Union
(Subquery"blah")
Custom Code:
Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double if denominator = 0 then return 0 else return numerator/denominator end if End Function
How To use:
If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.
Problem:
How do I add this code reference in the following dataset select statement
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
Help!
P.S.
this is a Matrix report and this select statement is within one of the datasets that fill a matrix.
