I ran the following command to create a synonym for a function -
create synonym testfunc for myschema.myfunc
Then testfunc will be created in the dbo schema. When I call this function from my stored procedure by 'testfunc', I received an error indicating 'testfunc' is not a recognized build-in function name. If I call it by 'dbo.testfunc' then it will work.
If I create a synonym for a table, I can access the table using the synonym in my stored procedure without any problem.
Is it true that synonym works differently on tables vs. functions?
Suppose that a synonym foobar exists pointing to the table foo.bar. I also have a table with the same name in my schema. (mpswaim.foobar)
If I do a select
select * from foobar
Which table does the select run against? mpswaim.foobar, or foo.bar?
In Oracle, mpswaim.foobar would win, and we used this to all ow individual developers to have their own version of application tables during development.
Hi all. Informix and DB2 support something called synonyms that allow you to basically create sort of an alias for a table at the database level. Think of it sort of as a shortcut or link to a table. Does SQL Server 2000 have a similar ability and if so how?
I know someone will ask why you want to do this, so heres a quick example: If you have one legacy application that expects to write to one particualr table, but you wish to partition that table across several tables, you can break table1 up into tablea,tableb,tablec and then create a synonym called table1 that would point to only the appropriate table at the appropriate time. This way you can break a huge HUGE table up into logically discreet smaller tables and manage the creation of the appropriate synonym in some wrapper that sits in front of the legacy application...thus allowing you to retool a table that has outgrown its original design without having to crack open dreaded legacy code.
I may just be completely missing something here but, when I view a query plan from a SQL statment that involves a join with a synonym I do not see any reference to the synonym or the underlying table referenced by it in the query plan? Any thoughts?
Can anyone tell me why I am getting this error when I try to select * from a table through a newly created synonym? I have admin rights to both db, but they are on separate servers.
<Error>
OLE DB provider "SQLNCLI" for linked server "srvDEV" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
</error>
<code>
CREATE SYNONYM ARContractTerms_syn FOR srvDEV.EricsAdeptCastle.dbo.tblARContractTerms
SELECT * FROM ARContractTerms_syn
</code>
Am I running into schema problems?
Thanks all
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
exec ('CREATE SYNONYM tblsynonym FOR ' + @cx + '..TableName')
---Procedure
drop synonym tblsynonym
The application i'm working on uses stored procedures that will at some point be called by more than one user at a time. At the start of the stored procedure the synonym is created and then it is dropped when the procedure completes, the issue is this: if two users access the same stored procedure at the same time then the first procedure will create the synonym and the second will fail because the syonym already exists.
I'm using SQL Server 2008R2. I am developing a database which requires access to data from other servers. So far I have been creating views using OPENQUERY (where there's a performance benefit) to select specifically the columns I want. Generally, for my purposes, I find these OPENQUERY based views to perform better (some times significantly so) to simple SELECT <COLUMNS> FROM <SERVER>.<DATABASE>.<SCHEMA>.<TABLE> WHERE <Where clause Statements> format views. My understanding is that this is because an OPENQUERY "pushes" the query processing to the remote server and simply returns the final result set to the local server i.e. there's no cross-server join/synchronization going on.
My question is, if I were to create a Synonym for a table object on the remote server, where does the processing happen if I query from this Synonym or create a join with this synonym to a table in my local database?Essentially, I am trying to understand if there are any "hidden gotcha's" primarily from a performance perspective, to using synonyms.
A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.
To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:
SET XACT_ABORT ON; BEGIN TRANSACTION; DROP SYNONYM [dbo].[some_proc]; CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc]; GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public; COMMIT TRANSACTION;
When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.
While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:
Error=-2147217900, Id=0, Meaning=IDispatch error #3092, Source=Microsoft OLE DB Provider for ODBC Drivers, Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.
This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.
Any way to prevent it besides a client-side retry?
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 need to get the object type (view, table ...etc) for a synonym base object inside a script. The only place where I see something related to this stored is in column "base_object_name" in sys.synonyms but there I can see only the same with format [database].[user/schema].[name]. After some testing playing with different users without specifying database/schema I think that maybe the object_id must be stored in a another place, my first idea was parent_object_id in sys.synonyms but it isn't stored there.
know if object_id for the base object is stored in any other place ?
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!
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).
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
Hi,,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.
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
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. ;-)
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.
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?
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.
Hi 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.
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.
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--
