I hope this is a right form for ADO .net type of question.
My question is, can you call SQL function the way you call stored procedure from ADO .net. I coded it this way and does not seems to be getting result set back. The DataReader is seems to be coming back with nothing. Can someone post an example. I know you can write "SELECT udf_function()" but I really mean the way the stored procedure is called. Thanks.
I've probably not given the best title to this topic, but thatreflects my relative "newbie" status.I have a table that goes essentiallyTSDATETIMEjobnumberVARCHARjobentryVARCHAR...the TS is a time stamp, and the other two fields are job numberand entries. There are (many) more fields, but this is the core ofit. The relationship is there will be several entries per job, withone row in the table per entry (i.e several rows per job).In constructing a web interface I want to create a list of recentjob numbers, and summarize it broadly as followsmax(ts)jobnumbercount(jobentry)...I can do this by a select command as followsselect top 30 max(ts) as time, jobnumber, count(jobentry)from Jobsgroup by jobnumberorder by time descHowever I'm now finding that this is quite slow now that my teat tablehas around 400,000 entries (Even though I've added indexes to mostrelevant fields). In particular it's much slower thanselect top 30 jobnumberfrom Jobsgroup by jobnumberorder by jobnumber descleading me to suspect that the aggregate functions are slowing thisdown. I would guesstimate the difference in speed is around a factorof 10-20.As I type this I think I've just realised it's theorder by time descthat is probably causing the problem (in that it will need tocalculate max(ts) for all jobnumber's in the table in order toexecute the "order by" clause).I think I can see the solution now (amazing what typing out a longquestion can do for you :-)My question was going to be if there was any approved method/tricksfor avoiding this sort performance hit.It seemed to me that if I could first grab a decent number of recentrecords (quickly) I could then execute the aggregate functions againstthat smaller set of records. I know in broad terms how any entriesthere can be per job, so I could select enough to make at least 30jobs, and then execute the real query against that. In my case therewill be probably less than 10 entries per job, so I could grab 300records and execute against that, instead of against the whole400,000.That being the case is this best donea) as a subquery, and if so, howb) by creating a temporary intermediate table (which isin essence a more explicit version of (a) isn't it?)Another solution that occurred wasc) to create and maintain a summary table, with justone record per jobnumber with a view to having itserve this particular (common) enquiry.For (c) to work I would probably need triggers on the Jobs table tomaintain the summary table. This would probably represent moreoverhead overall, but should certainly give a better responsewhen the query outlined above is executed on the web site.The response time to this query is becoming critical on the webinterface to avoid timeouts.Any suggestions or comments are welcome. If it's RTFM, then I'dappreciate page numbers :-)Thanks for reading this far :-)--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 4 Replies View RelatedI was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
View 9 Replies View RelatedDear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
Hi.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Any help would be greatly appreciated.
Thanks
Geoff.
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
Guys I need help with sql server 2005 syntax
My goal is up update a table called UserStats.
I have numerous functions in SQL that return Scalars (one for each statistics I want to use)
How do I then use a stored proceedure to return a table of the values for use on my site?
WIthin SQL Server 2005, there are functions. This feature is new to me and I haven't found anyone that has written their own fucntions? I'm wondering if functions are written the same as stored procedures, and can a function be called from a stored procedure or even from within a query.
hello Im having a difficult time translating this query from Access to SQL because it uses the First/Last functions.
I have a 'Projects' Table and a 'Project_Comments' table, each has a 'Project_ID' field which links the 2 together. What I need to do is retrieve a Project List from the Projects Table and also the first Comment of each project based on the Commend_date field in the Project_Comments table. This is the MS ACCESS query:
SELECT Projects.Project_Number, Projects.Project_Name, First(Project Comments.Comment_Date), First(Project_Comments.Notes)
FROM Projects Left Join Projec_Comments ON
Projects.Project_Number = Project_Comments.Project_Number
GROUP BY Projects.Project_Number, Projects.Project_Name
Now I can use Min() for the Date instead of First, however I dont know what to do with the Notes field. Any help on how to get this over to sql would be greatly appreciated!
Hi,
I have created a function that returns a comma seperated list of product id's from a table. I need to call this function from a stored procedure to help filter my product results, something like the following:
SET @SQL = 'SELECT dbo.Products.ProductID FROM dbo.Products WHERE dbo.Products.ProductID IN (' + dbo.GetModels('dbo.Products.ProductID', '') + '))'
The problem I am having when executing the above is:
"Conversion failed when converting the varchar value 'dbo.Products.ProductID' to data type int."
Can anyone shed some light on how I can call the function, feeding through the product ID from the row of the select statement I am trying to execute (if this makes sense).
Any help would be great.
Matt
Iam trying to convert a date string to date format.....in access I could just use CDate, but SQL apparently does not allow this.
Any help appreciated
Thanks
Hello,how I can use a function of my own within a select statement. I triedcreate function funny() returns intas beginreturn( 2 )end goand then "select funny()" but 'funny' is not a recognized function name.How can I solve this?thanks and regardsMark
View 1 Replies View RelatedHi,,I'm having a problem with calling a function from an activex scriptwithin a data transformation. the function takes 6 inputs and returnsa single output. My problem is that after trying all of the stuff onBOL I still can't get it to work. It's on the same database and I'mrunning sql 2000.when I try to call it I get an error message saying "object requiredfunctionname" If I put dbo in front of it I get "object required dbo".Can anyone shed any light on how i call this function and assign theoutput value returned to a variable name.thanks.
View 7 Replies View RelatedHello,
I've created a function that performs modulo. I understand that SQL server 2000 / 2005 uses % for modulo, but we have an application that was written for Oracle. Oracle has a mod(dividend, divisor) function.
As to not rewite the queries, I would like to implement the function below:
the function executes properly but I must prefix it with the dbo schema.
Net: I can execute --- select dbo.mod(9,2) and it returns a 1 just like it should.
but I can not execute --- select mod(9,2) I receive the error "'mod' is not a recognized function name." on SQL 2000 and 2005.
If I can execute select mod(9,2) then I won't need to re-write any queries.
Also, on SQL 2005, I have tried to adjust the default shema, and the execute as clause, but neither helped my cause.
I'm going to try building the function in the CLR, but I think I will be faced with the same problem.
Can someone point me in the right direction?
Thanks
Tom
create function mod
(
@dividend int,
@divisor int
)
RETURNS int
as
begin
declare @mod int
select @mod = @dividend % @divisor
return @mod
end
Hi,
I have to calculate data in function with "EXEC". During runtime I get the Error:
"Only functions and extended stored procedures can be executed from within a function."
I would use a Stored Procedure, but the function is to be called from a view. I don't understand, why that should not be possible. Is there any way to shut that message down or to work around?
btw: Storing all the data in a table, would mean a lot of work, I rather not like to do. ;-)
Thx for any help
Blubb10
I just installed SSRS 2005 and I have experience with SQL.
How come this function does not work?
SELECT SUBSTRING(YEAR_MONTH, 1, 2) AS Expr1
FROM table1
I get a message which states that this command is not supported by the provider?
It works fine with other SQL tools like winsql?
thanks
Can someone explain what the different SQL CE DLL's functions are? I can issue a successful merge replication and these seem to be the DLL's loaded just after it completes.
RSSWM.exe base address: 2C000000
=========================
sqlceoledb30.dll 00CB0000 35000 A
sqlceca30.dll 00CF0000 75000 A
sqlceqp30.dll 00D70000 DD000 A
sqlcese30.dll 00E50000 6A000 A
sqlceer30en.dll 00EC0000 24000 A
itcnetreg.dll 00EF0000 15000 A
itcswinsock.dll 00F10000 D000 A
itc50.dll 00F30000 27000 A
sqlceme30.dll 00F70000 10000 A
rsshelper.dll 00F80000 6000 A
tcpconnectiona.dll 00F90000 10000 A
edbgtl.dll 00FA0000 14000 A
itceventlog.dll 01110000 B000 A
rsaenh.dll 01350000 2B000 RO, H, S, C, RAM from ROM
iq_lapi_c_wrapper.dll 01B80000 27000 RO, XIP
ssapi.dll 01BB0000 17000 RO, XIP
mscoree2_0.dll 01D90000 C3000 RO, H, S, XIP
After some application activity, I attempt a merge replication again and it fails with a "DLL could not be loaded" yadda, yadda message. These seem to be the loaded DLL's at this point.
RSSWM.exe base address: 2C000000
=========================
sqlceqp30.dll 00D70000 DD000 A
sqlcese30.dll 00E50000 6A000 A
sqlceer30en.dll 00EC0000 24000 A
itcnetreg.dll 00EF0000 15000 A
itcswinsock.dll 00F10000 D000 A
itcadcdevmgmt.dll 00F20000 9000 A
itc50.dll 00F30000 27000 A
sqlceme30.dll 00F70000 10000 A
rsshelper.dll 00F80000 6000 A
tcpconnectiona.dll 00F90000 10000 A
edbgtl.dll 00FA0000 14000 A
itceventlog.dll 01110000 B000 A
rsaenh.dll 01350000 2B000 RO, H, S, C, RAM from ROM
iq_lapi_c_wrapper.dll 01B80000 27000 RO, XIP
ssapi.dll 01BB0000 17000 RO, XIP
mscoree2_0.dll 01D90000 C3000 RO, H, S, XIP
I'm at a loss as to why SQL CE cannot load unless I am out of my 32MB of process space for some reason.
(App on an Intermec 751 with WM5 and 128MB RAM)
TIA for any help
I am trying to execute a SP in the execute SQL task in SSIS 2005..
but I keep getting an error:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Load_Gs_Modifier_1
SSIS package "Package.dtsx" finis
I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????
I usually access stored procedures using SQL data source. But now I need a string returned from the database. If I write a function in SQL how do I access it from an aspx.vb file?
View 3 Replies View RelatedHi y'all I think i have some problem in here perhaps you guys can help.I have this code: 1 public void AddQstrWhere(ref string TmpStr,string Parameter)
2 {
3 if(TmpStr=="")
4 {
5 TmpStr +="WHERE"+Parameter;
6 }
7 else
8 {
9 TmpStr +="AND"+Parameter;
10 }
11 }
12
13
14 public string querySlsPerson;
15 //public string queryLastSls;
16 public ArrayList CLSGetSalesman(string szSalesmanID)
17 {
18
19
20 // TODO: Add CLSReportManager.CLSGetSalesman implementation
21 BOSTx Tex = GetTx(CLSReportConstants.TrnMasterID.FQN2,"QueryRptSales");
22 try
23 {
24 string querySalesPerson = "SELECT dbo.BOS_GL_Workplace.WpszState, BOS_PI_Employee_1.szName, dbo.BOS_PI_Employee.szName AS Expr1, dbo.BOS_PI_Employee.szEmployeeId, "+
25 " dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription "+
26 " FROM dbo.BOS_PI_Employee LEFT OUTER JOIN "+
27 " dbo.BOS_GL_Workplace ON dbo.BOS_PI_Employee.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN "+
28 " dbo.BOS_PI_Division ON dbo.BOS_PI_Employee.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN "+
29 " dbo.BOS_PI_Team ON dbo.BOS_PI_Employee.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN "+
30 " dbo.BOS_PI_Employee BOS_PI_Employee_1 ON dbo.BOS_PI_Employee.szSupervisorId = BOS_PI_Employee_1.szEmployeeId ";
31 //"WHERE dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
32 //" WHERE BOS_GL_Workplace.WpszState = '" + szProvince + "' AND BOS_PI_Employee.szName = '" + szsalesPrsn + "' AND BOS_PI_Employee_1.szName = '" + szSupervisor + "'";
33
34 string wheretext = " dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
35 string queryLastSls;
36 //AddQstrWhere(szSalesmanID,wheretext);
37 if(szSalesmanID==string.Empty)
38 {
39 queryLastSls = querySalesPerson;
40 }
41 else
42 {
43 //string querySlsPerson;
44 AddQstrWhere(ref queryLastSls,wheretext);
45
46 queryLastSls = querySalesPerson + querySlsPerson;
47 }
48
49 OleDbCommand comd = Tex.GetTextCommand();
50 comd.CommandText = queryLastSls;
51
52 DataTable slTable = new DataTable();
53 OleDbDataAdapter adapt = new OleDbDataAdapter(comd);
54 adapt.Fill(slTable);
55
56
57 ArrayList DataSalesArr = new ArrayList();
58 foreach(DataRow row in slTable.Rows)
59 {
60 CSDatasales sls = new CSDatasales();
61 sls.sz_province = row["WpszState"].ToString();
62 sls.sz_supervisorname = row["szName"].ToString();
63 sls.sz_salemanname = row["Expr1"].ToString();
64 sls.sz_salesmancode = row["szEmployeeId"].ToString();
65 sls.sz_Divisi = row["Divisi"].ToString();
66 sls.sz_typeOp = row["szDescription"].ToString();
67
68 DataSalesArr.Add(sls);
69 }
70 Tex.CloseConnection();
71 //AddQstrWhere("",wheretext);
72
73 return (DataSalesArr);
74
75 }
76 catch(Exception ex)
77 {
78 throw ex;
79 }
80
81 }
82 You see I want to make the query more dynamic. So instead of making the SQL parameter permanent I wanna make it dynamic, so the usercan choose whether they want to use the parameter or not. I already declare the parameter which is szSalesmanID. There's a function called AddQStrWhere which can make the query more dynamic. But the thing is when I wanna use the parameter it seems the code doesn't take it, it just use the regular query I dont know why.I already use the function but it just wont work, can you guys tell me what's wrong, Please I really do need some help from you guys, feelin; a lil' bit desperate in here.I appreciate any kinds of help, thanks. FYI: I use Visual Studio.NET 2003, SQL Server 2000.Best Regards.
I have a table that is used for employee evaluations. There are six questions that are scored either 1, 2, 3, 4, or 5. I want to tally the responses on a page, but I wonder if I can do it without 35 separate calls to the database (I also want to get the average response for each question). I know I can do "SELECT COUNT(intWorkQuality) AS Qual1 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '1' " and then "SELECT COUNT(intWorkQuality) AS Qual2 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '2' " and so on. But can I somehow do the aggregating at the page level, and just refer back to a datasource that uses a generic statement like "SELECT intWorkQuality, intDepend, intAnalyze, intWrite, intOral, intCompatibility FROM dbo.Summer_Project_Req"? If I can, I am not sure what type of control would be best to use or what syntax to use to write the code-behind. I would like the results to be displayed in a grid format. Thanks in advance for your help.
View 3 Replies View RelatedHi all,
I have a problem with date functions...
In my program I use weeks, and with this variable I need to know which is the first day of the selected week...
For example... I put week 52 in my program... how I can obtain the first day of this week? -> (22/12/2003).
I have proved with this functions...
SET DATEFIRST 1 (to configure Monday as first day of week)
SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)
With this I have the day of the current week... but I can't put my week in this function... aarrgggg.
Please help me...
Thanks a lot.
I'm trying to use the date() function in an openquery statement in query analyzer and I keep getting an illegal symbol ")" error. The statement is :
select *
from openquery([Big Blue], 'select cde_date, cde_item from acch1 where cde_date < date()')
The objective is to find records where cde_date is prior to today. The syntax works fine if I execute it on the mainframe in QMF and the ODBC connection works fine if I hard code the value. What I need is the ability to use the function so I can run the query on subsequent days without having to edit it.
Any suggestions??
Hello all,
Recently I changed over a ASP script from our old Access 97 database to our new SQL database. When I changed it over, some of my SQL pulls on my Active Server Page started to give me erros. One of them is the function
date(). When I used it pulling from Access like this :
strSQLQ = "SELECT * FROM cocoitem WHERE CustNum = '" & strcustnum & "' AND stat = 'C' AND [due-date] > DateAdd('yyyy', -1, Date()) Order By [cust-po], [due-date] ASC ;"
Then it worked fine. When I redirected the ASP to the new SQL server I recieved an error like this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
'Date' is not a recognized function name.
/scripts/order/shippingstatsclose.asp, line 45
So my question is, what is the SQL server equivalent of the function Date()?
I have a challenge and am not very versed with coding this situation. The situation is breaking up an address into separate portions: number, number suffix, direction, street name, thoroughfare, street suffix, etc. I am having troubles getting started, could anyone point me somewhere in the right direction with a little guidance or has anyone done this and have the code stored? This is not my strong point and have been trying to improve, sorry. D--
View 5 Replies View RelatedDoes anyone know how to call a stored procedure from within a SELECT statement? I know sybase has stored functions, but can not figure this out in SQL 7.0.
Example:
Select A.ID, sp_Amount(A.ID,1) as 'Late Fees', sp_Amount(A.ID,2) as 'Payments' from A
I want the ability to return the "amounts" as columns, but without having to go through the hassle of dealing with temp tables or cursors.
Is there a way to add functions to SQL (other than stored proceedures) to emulate functions in other systems?
I would like to create a FOXPRO type OCCURS function:
Select * FROM Table1 WHERE OCCURS('Y', FLAGS) > 3
If FLAGS is a VARCHAR(7) and equals "YYYYNNN" then OCCURS would return 4 and the row would return. The database that I am
accessing has >165,000,000 rows and >500 columns per row so you see that I need all the speed I can get!
'
' This is a VB function which works.
' Can this be compiled into a DLL and
' somehow made available to SQL?
'
Public Function OCCURS(expr1 As String, expr2 As String) As Integer
Dim i As Integer
Dim count As Integer
For i = 1 To Len(expr2)
count = count + Abs((Mid(expr2, i, 1) = expr1))
Next
OCCURS = count
End Function
1.I am trying to write following query in SQLserver(this is oracle query),
IS their any equivalent for GREATEST and LEAST functions in SQLServer ???
Update mwebtemp
Set Temp_amount1 = (SELECT SUM(AuthAttr_Amount*(num_Business_Days
(GREATEST(AuthAttr_Start_Date, @dtstartdate),
LEAST(AuthAttr_Finish_Date, @dtenddate))))
.....where...
etc...
.....
Note: this is part of a stored procedure...
2. Also how do you write and call a function in SQLServer (syntax ??), eg.num_Business_Days
is a function returning number of business days between the 2 dates....
thanks a lot
I have a table of sales transactions that I'd like to group by date, but this is a datetime field so it groups by the date and time, and so it returns more records that I want. Is there an easier way to do this other than using the datepart function to build a date? How can I just look at only the date of a datetime field?
View 1 Replies View RelatedHello All,
Does anyone know if stored functions can be written for sql similar to stored procedures? And if theey
can, what language?
Thanks
any disadvanges using user defined functions calling from stored procedures??
View 4 Replies View RelatedI am re writing my help desk app so that it can use popular databases at the backend. Although I am OK with MySQL I have no experience with MsSql. Most of my querys are standard stuff, but I have some datetime querys which I need help with.
Firstly can I use iso date times (yyyy-mm-dd hh:mm:ss)
How would I convert the following MySql query to MsSql or could someone point me in the right direction to get the required info?
PHP Code:
$today = "2005-05-19 21:13:00";
$querystr = "
SELECT
WEEKDAY('$today') as js_weekday,
DAYOFMONTH('$today') as js_dayofmonth,
MONTH('$today') as js_month,
YEAR('$today') as js_year,
HOUR('$today') as js_hour,
MINUTE('$today') as js_minute,
p.p_name, p.p_hours as priority_hours,
ct.cont_name, ct.sunday, ct.monday, ct.tuesday, ct.wednesday, ct.thursday,
ct.friday, ct.saturday, ct.hours as contract_hours, ct.bankhols,
HOUR(ct.start_time) as ss_hour,
MINUTE(ct.start_time) as ss_minute,
HOUR(ct.end_time) as se_hour,
MINUTE(ct.end_time) as se_minute,
(time_to_sec(ct.end_time)-time_to_sec(ct.start_time))/60 as std_minutes
FROM customer c, priority p, cont_cat ct
WHERE c.cust_id = $cust_id
AND p.priority_cat_id = $priority_id
AND ct.cat_type_id = p.p_cat_type_id
AND c.cont_type_id = ct.cont_type_id";