I am sorry to continue bothering this forum with the
continuation of this question but here it is. And thank you to Craig for
giving me the equivalent of the function Date() in SQL. Now when I pull from
the SQl Server with the old ASP pull with this statement using GETDATE()
strSQLQuery1 = "SELECT * FROM cocoitem WHERE CustNum = '" & strcustnum & "' AND (stat = 'O' OR stat = 'F') AND [due-date] > DateAdd('yyyy', -1, GETDATE()) Order By [cust-item], [due-date] ASC;"
I get this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid parameter 1 specified for dateadd.
/scripts/order/shippingstatsopen.asp, line 28
So I guess I need to also know the equivalent of DateAdd . Also, does anyone
know of a Access Function to Sql 7 function comparison chart so I can write
for the new database comprehendingly?
I 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
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site. I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part. While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as "Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
I have another question regarding migration process to diffrent SQL Server.
Last time when I did migration I didn't restore system databases (master in particular). I only restored user databases and of course I've got login errors such Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database. I just used sp_change_users_login to fix those orphans.
I've read articles in MS knowledge base and not quite sure is this a good idea to restore master database prior to restoring all user databases on the new server in order to keep all logins?
I'll try to use recommended article for transfering logins with password instead of restoring master database from backup http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP
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()?
Hi I have a problem which I’m not sure how to resolve! I have a aspx with two drop down list; 1st one has (annual salary, daily salary, hourly rate) 2nd one has ( 0-4999, 5000-9999......)
The second one is generated by the value selected in the first one. I have stored the values in a table (as nvarchar) and used sqldatasource to run a query, which matches the entry in the first box and fill the second drop down list accordingly.
How ever I have a problem, when I want some one to search for example; an average salary of 5000-9999, it should output entry's that have a similar daily rate, and hourly rate... But I’m not sure how I can accomplish this, does any one have any ideas! Many thanks
Hi,the Soundex search words that sounds similar.Does MS SQL Server has some function to make some intuitive search?For example, for search term database, it should return rows that contains: "database" word, but also rows that contains "Oracle", "MySQL", "MS SQL" etc. terms.
Can someone tell me if this is a SQL Server bug? I tried this in both version 7 and 2000, the results are the same.
DECLARE @timeA DATETIME DECLARE @timeB DATETIME DECLARE @msDiff INT
SET @timeA = GETDATE() SET @msDiff = 0
WHILE @msDiff <= 10 BEGIN SET @timeB = DATEADD(ms,@msDiff,@timeA) PRINT 'If adding ' + CONVERT(VARCHAR,@msDiff) + ' milliseconds to Time B, then Time B is ' + CONVERT(VARCHAR,DATEDIFF(ms,@timeA,@timeB)) + ' millisecond greater than Time A' SET @msDiff = @msDiff + 1 END
This seems like a serious bug if an application depends heavily on milliseconds comparison.
I am new to this, SQL Server. I hv worked in Oracle. Now I am learning 'SQL Server'. In Oracle, it has features like Packages and functions (PL/SQL), like that in SQL Server, is there any facility available?.
Hi, I am still learning the bells and whistles of SQL Server and was wondering if I can find out the query that caused my trigger to fire, so that I can log this in another audit table. I have an If Update ( My_Column ) trigger set up, where once an update happens to My_Column much information from the updated row along with , Host_Name and App_Name is sent. I also want to send the exact query used to update it, any ideas? Any comments, suggestions will be greatly appreciated. Thanks, Kartik
I'm moving some queries out of an Access front end and creating views out ofthem in SQL Server 2005 express. In some of the numeric fields, I use nzquite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.Is there anything equivalent to this in SQL Server? Right now I'm usingCASE WHEN ... but it seems like an awful lot of script to write just toreplace null with a zero.Any help would be greatly appreciated.Thanks!
I would like to know how to write a function that will go through datain a column and change it. For example, I have a column of ISBN's forbooks, and the ISBN's have a period in them randomly distributed. I'dlike to pull the period out.Any help I can get will be appriciated.Thanks,Bill
Hi,Do any versions of SQL Server support the following functions, asthey appear in the Oracle Database:-1) XMLElement2) XMLAttributes3) XMLForestThanks in Advance for your replyByeAmardeep Verma
Does anyone have financial functions to be run in SQL Server 2000? For example, future value, interest rate, payments, and so on. Or where can I find them on Internet?
I'm upsizing MS-Access to SQL Server 2005. I need to convert the following functions: TRANSFORM PIVOT FORMAT MID
Are there any similar functions in SQL Server?
Also I have a query as follows:
SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate])," ",Format([statusdate],"yyyymmdd")) AS [Date] FROM tblResult;
I have an xml stored in a coulum of a table and I use the following query to extract an xml element :
select CONVERT(XML,CONVERT(NVARCHAR(max),Response)).value('(/Quote/error)[1]','nvarchar(max)') as Excepiton .The result of the expression is :
TL43:The product has no marked price.;I would like to select only the code : TL43and then separately I would like to select The product has no marked price.Is there a way I can do it?
I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.
Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
SELECT ot1.Full_Order_No , ot2.Order_No FROM OrderTable1 ot1 INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
Few notes:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query. -The portion I need (24954) can be more or less than the 5 current digits. -There will always be 6 portions to the order number, split up between 5 dashes.
We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.
In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.
When we access Guider we have no problems getting data.
When we access Talker we have a login failure:
Cannot open database 'Talker' requested by the login. The login failed.
Login failed for user 'AcornCommunicationServices'.
The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.
Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!
Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using
I have 3 fields in my table say (F1, F2, F3). I want to get the max value out of the three fields for each row. I can create a user-defined function which accepts 3 arguments and then return the max value if i am using SQL Server 2000. But now i am using only SQL Server 7.0 (it does not support user-defined functions :confused: )
So any one could kindly let me know how could i do it in SQL Server 7.0
Hi, I saw in some websites that there are functions freeze and thaw in SQL server.I want to freeze the SQL server for some time and then use the thaw to unfreeze.I want to know how it could be done in SQL server 2005
Hi all!!Does anybody know how I can create a function in SQL 7.0?? I have tocreate functions that return a value that can be used in a selectstatement. I think SQL Server version 7.0 doesn't support CREATEFUNCTION, does it?Ex:Select MyFunction(Parameter)From MyTableThanks a lot,
Hi , I have a question about calling functions in SQl Server 2005.
Let's say that I have created as a dbo a function called Calculations.
If I want to call it from T-SQL I will write Select dbo.Calculations (arguments if any) etc.
My question is If I can skip the "dbo" part. Call the function without using the dbo . Can I do that ? Should I create the function as supervisor ? Does Sql Server has a property or something which will allow me to call the function without using the "dbo." ?
I'm trying to replace a particular part of a row in a table with a new value.
The row is called "DataPath" and it has a lot of values like so:
mashOperationsComponent Data FilesSantec
I want to run a query to replace the mash with our DFS namespace share name companysharesDepartments but keep everything else past the mash part of the original row.
I'm currently running this query, it says it is altering 30,000 rows, but it doesn't look like it's doing anything at all..
UPDATE dbo.Part SET DataPath = REPLACE(DataPath,'company.localsharesDepartments','mash') WHERE DataPath like 'mash\%'
So for example, it would change the mash above to
company.localsharesDepartmentsOperationsComponent Data FilesSantec
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
I use IBM WebSphere Portal and am desperately trying to move data from the default Cloudspace Database to MS SQL Server 2005, of course following the official guidelines.
What happens is that WebSphere's Configuration Wizard fails because of an error caused by the SQL Script. Trouble is, I can't bring it to work not even in the SQL Server Management Studio.
What follows is the code generated by the script. The error is caused by the last "check"-constraint (colored in red). CREATE TABLE community.APP_DESC ( OID BINARY(18) NOT NULL, TYPE INTEGER NOT NULL, APP_NAME NVARCHAR(255) NOT NULL, IS_ACTIVE INTEGER NOT NULL, JSR_VERSION NVARCHAR(255), GUID NVARCHAR(255), WEB_MOD_OID BINARY(18), WEB_MOD_SL BINARY(18), WSRP_PROD_OID BINARY(18), WSRP_PROD_SL BINARY(18), DEFAULT_LOCALE NVARCHAR(64), CREATED BIGINT NOT NULL, MODIFIED BIGINT NOT NULL, WSC_GROUP_ID NVARCHAR(255), CONSTRAINT PK20 PRIMARY KEY NONCLUSTERED (OID), CONSTRAINT FK20A FOREIGN KEY (WEB_MOD_OID) REFERENCES community.WEB_MOD (OID) ON DELETE CASCADE, constraint FK20B FOREIGN KEY (WSRP_PROD_OID) REFERENCES community.WSRP_PROD (OID) ON DELETE CASCADE, CONSTRAINT CC20A CHECK (((community.APP_DESC.WEB_MOD_OID IS NULL) AND (community.APP_DESC.WEB_MOD_SL IS NOT NULL)) OR ((community.APP_DESC.WEB_MOD_OID IS NOT NULL) AND (community.APP_DESC.WEB_MOD_SL IS NULL))) )
And that's what SQL Server 2005 told me: Msg 4104, Level 16, State 1, Line 1The multi-part identifier "community.APP_DESC.WEB_MOD_OID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "community.APP_DESC.WEB_MOD_SL" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "community.APP_DESC.WEB_MOD_OID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "community.APP_DESC.WEB_MOD_SL" could not be bound.
BTW, as this is a generated script I do not have the possibility to change it. Because it has been released by IBM I am rather convinced that it is correct - therefore I was wondering whether SQL Server 2005 has a known bug that makes it refuse "check"-constraints.
I have developed a VB6 application that works with SQL server 2000. My client does not have a SQL server installed on their machine. I wonder do I have to purchase a copy of SQL server 2000 and install it to client's computer ?
How much would a basic version of SQL server 2000 cost ?
I am new to the softwarel world. Please give me some advise.
