Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server


SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.





SQL Server 2005 &&"lag&&" Function


 

Hello,
 
Does SQL Server 2005 have anything like a "lag" function that, in a sigle query allow you to retrive a previous record? The syntax would be like:
 
SELECT
transactionid
,transactiondatetime
,amount
,amount + LAG(amount) OVER(ORDER BY transactiondatetime) balance
FROM
bankaccount

 
Thanks for any advice




View Complete Forum Thread with Replies

Related Forum Messages:
ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )
Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly.
I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.
 I have two SQL Server instances installed.
 1- UserLT (this is sql 2000)2- UserLTSQL2005 (this is SQL 2005 named instance)
But when i try to add a database to my VS website project I get the following error:
Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925
I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)
and changed the "SQLEXPRESS" to "USERLTSQL2005".
But I still get the same error message. Any ideas how i can resolve this issue?

View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly
hello,

i've installed SQL server 2005 express and Visual web developper 2005 express.

when i whant to create a database in VWD the following error occures:

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251

i searched the internet but can't find the solution, i already reinstalled my complete workstation but the problem stays.

please help!

View Replies !
Dont Think Discretization Function Is Necessary On The Analysis Service Server In SQL Server 2005
Hi, all here,

I am just wondering about the Discretization function available on analysis service server (which can not actually discretize data into user-defined expressions) . Isnt it redundant ? I mean since users can discretize their data  more meaningfully based on their own expressions in database engine of SQL Server 2005.

Thanks a lot.

View Replies !
VS2005 Error: Connection To SQL Server Files (*.mdf) Requires SQL Server Express 2005 To Function Properly.
 

Good Evening All,
 
I've serached this forum and Google'd for a resolution to this issue, to no avail.  Here's the scenario:
I'm running VS 2005 on Windows Media Center laptop and need to create ASP.net membership for my web application built using VB.  I have SQL Server Developer installed with instance name MSSQLSERVER.  Previously, I uninstalled SQL Express and installed Developer edition and can now open and utilize Management Studio.
 
Now, when I try to create a new SQL Server database in my solution's App_Data directory, I receive the above error.  I already changed instance name in VS2005 per Tools-Options-Database Tools-Data Connections to MSSQLSERVER. 
 
Could someone provide me with a list of procedures to ensure proper setup of VS2005 with SQL Server Developer Edition?
 
Thanks much for your help.

View Replies !
Split Function - Sql Server 2005
In my table, column1 having a comma separated values.I want to display in rowwise.
for example:
column1
aa,ss,ff
 
output should be
aa
ss
ff
 
Pls. help me..I want to do thr query.

View Replies !
Count Function: SQL Server 2005
is it possible to do a count on the same column but under different circumstances posting the results in two different result columns?
 

View Replies !
Sql Server 2005 GETDATE() Function
I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector.

When I use the SQL Mangment Studio Express to execute this query:

SELECT GETDATE()
it returns 2008-04-22 16:25:03.690 which is OK !!

I want to get that same value using a Java program so I do that query but when I display it it shows as:

2008-04-22  and there is no time there !
I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM
So maybe the time isn´t there ?

This is the code i am using:

            Statement st = con.createStatement();
            String sql = "SELECT GETDATE()";
            ResultSet result1 = st.executeQuery(sql);

result1.next();

            Date today = result1.getDate(1);


Any help would be appreciated.

View Replies !
SQL Server 2005 Query With Date Function
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!  Function:create function dbo.CreateDateList(@start datetime, @end datetime)returns @t table ( [date] datetime )asbegin 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 returnend ---------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   

View Replies !
How To Run User Defined Function On Sql Server 2005
Dear all
I wants to run sql server user defined function when linked two server.
 
I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?
 
Regards
Jerminxxx

View Replies !
SQL Server 2005 Query/trigger/function (whatever It Is That I Need)
Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario
 table 1 = stock table
 table 2 = Purchase order table
 in table 2 there are line items that have ordered quantities for parts that we have ordered
 in table 1 under each part number is a field for "quantity on order"
I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1
 
quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match
so for part number 516 i have this
 
table 2
poNumber             partNumber                 quantity
1                             516                             1
2                             516                             12
3                             516                             4
 
table 1
partNumber            inStock              onOrder
516                          0                        17(this is what i am trying to figure out how to compute)
 
any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.

View Replies !
EMERGENCY:rollback Function For Sql Server 2005?
is there a roll back function for sql server 2005 that would change data back to what it was before the execution of last update  query?

View Replies !
Does SQL Server 2005 Has Similar Function Like Mysql_fetch_row ?
I just began to use SQL Server 2005 as database programming and
found out that I have to translate mysql_fetch_row into SQL Server 2005
but I cannot find some related functions/api, and I was wondering
Does SQL Server 2005 has similar function like mysql_fetch_row ?
Or if not, any advice how I can program to acheive similar functions ?
thank you in advance.

View Replies !
How To Create Assembly Function Using Dll Files In SQL Server 2005???
Hiiiiiiii all
 
I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration
 
I have tried like this
 

CREATE ASSEMBLY abc
FROM 'C:abc.dll'

WITH PERMISSION_SET = SAFE
 
but it gives me
incorrect syntax error 
so plzzzzz anyone help me wht to do in my probbbbbbbbb???????
 
Pratik Kansara

View Replies !
How To Read CSV File In SQL Server 2005 Using OpenRowSet Function
Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

 

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

View Replies !
SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record
Hello,
 
I am trying to figure out how to use the select maximum command in SQL Server 2005.  I have already created a database and I have it populate it with multiple fields and multiple records.  I Would like to create a new column or field which contains the maximum value from four of the fields.  I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column. 
 
Any help you can provide will be greatly appreciated!
 
Thank you,
Nathan

View Replies !
ODBC Link From Access To SQL Server 2005 Stored Function
If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC?

I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access.

I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time.

What am I missing?

Suggestions?

View Replies !
Problem With (SQL Server 2005) Reporting Services Drill Through Using SWITCH Function In Sharepoint Integration Mode
When deploying reports to a report server in Sharepoint Integration mode (post SQL 2005 SP2), when using the SWITCH function when drilling through to another report ('jump to report'), you have to append the ".rdl" entension to the report name.

 

For example, on a report server in native mode, the following will work:

 

=SWITCH(Fields!Region.Value="US","USRegionDrillDownReport",

                   Fields!Region.Value="Europe","EURegionDrillDownReport")

 

When deploying to a report server in Sharepoint Integration mode, you must do the following:

 

=SWITCH(Fields!Region.Value="US","USRegionDrillDownReport.rdl",

                   Fields!Region.Value="Europe","EURegionDrillDownReport.rdl")

 

When doing this, the deployed report will work, but the link in the Visual Studio developer environment will no longer work (and vice versa).

View Replies !
No TRIM Function In 2005 - Are You Serious!?
I cannot believe that there is yet another version of SQL Server without a TRIM function.  So for SQL 2005 (like I've done in 7.0 and 2000) my T-SQL will look like dog poo because I will have to write LTRIM(RTRIM(MyValue)) anytime I want to remove whitespace from both ends of character values instead of just writing TRIM(MyValue).

View Replies !
Can A Sql 2005 Function Return More Than A Variable
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance

View Replies !
CONCAT Function + SQL Sever 2005
I cant find a clear answer to the syntax of concat in for SQL 2005. Can anyone help.

View Replies !
Calling VB.NET Function From Trigger In SQL 2005
I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running.
 

I need help on how can I capture insert trigger event VS 2003 console application?

View Replies !
Fail To Create CLR Function In SQL 2005
Can anyone help me to create a URL decode user defined function in SQL Server 2005?
I want to use the method [System.Web.HttpUtility.UrlDecode] in .net framework, and I try to add it as a CLR function to SQL Server but always fail. It depends on [System.Web.dll], and when I try to create assembly [System.Web] using following scripts, it will fail:
 
    CREATE ASSEMBLY [System.Web] FROM 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
    WITH PERMISSION_SET = UNSAFE
 
Error:
CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE. 
The assembly is authorized when either of the following is true:
the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on;
or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
 
I have searched the MSDN and then add following scripts before mine:
 
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
 
CREATE ASYMMETRIC KEY SystemWebKey FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY SystemWebKey
GRANT UNSAFE ASSEMBLY TO CLRLogin
 
But unfortunately  it fails again with same error.

View Replies !
2005: Creating Aggregate Function From .NET Assembly
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/

View Replies !
How To Write A Function For MS SQL 2005 To Save Value After Underscore?
Hi Gurus,

How do I write a T-SQL function for a string before a underscore is found?


Thanks Gurus.

View Replies !
Create A TO_DATE Function For Use In SQLServer 2005
Hi ,

I 'm working with visual studio 2005 and I have created an SQLServer Project.
I'm using the CLR functionality which comes with SQLserver 2005. This means that I can write VB.nEt code and use it inside Sqlserver 2005.So far so good.
I am now inside the .NET
I have created a Function(must remind you that I have created an SQLserver Project) which takes two string arguments. The date value in a string format and the string format.

In Our case the function returns a string.It will return a datetime although.
So we have

Dim Datetime_Val As DateTime = Nothing
Dim Date_Val As Date = Nothing
Dim StrTemp As String = ""
Dim StrDateTemp As String = Nothing
Dim StrTimeTemp As String = Nothing
Dim ls_return As String = Nothing
Dim lindexof As Integer
Dim Counter As Integer = 0

lindexof = 0
Select Case StrFormat
Case "DD-MM-YYYY HH24:MIS"
For Counter = 1 To 2
lindexof = StrDate.IndexOf("-", lindexof + 1)
Next
lindexof += 5

StrDateTemp = StrDate.Substring(0, lindexof).Trim
StrTimeTemp = StrDate.Substring(StrDateTemp.Length, StrDate.Length - StrDateTemp.Length).Trim
ls_return = StrDateTemp & " " & StrTimeTemp

End Select

The above is a simple code. As you can see I'm trying to convert the TO_DATE function ,which work with ORACLE, to make it work with SQLServer 2005.
I've been trying unsuccessfully to combine the variables StrDateTemp and StrTimeTemp into a datetime value. I used the following code but nothing

Datetime_Val = CDate(StrDateTemp & " " & StrTimeTemp)
Didn't work

Datetime_Val = Convert.ToDateTime(StrDateTemp & " " & StrTimeTemp)
Didn't work

Datetime_Val = DateTime.Parse(StrDateTemp & " " & StrTimeTemp)
Didn't work

Inside SQlServer I used this SQL statement

Select dbo.TO_DATE('31-12-1990 00:26:46','DD-MM-YYYY HH24:MIS')

But I am receiveing an error. I want to avoid changing all of my applications with a specific format.This sql statement without the dbo prefix I'm using in Oracle. I want to keep the format of the SQL and let VB.NET do the parsing for me. It is easier for me to put in my SQLs the dbo infront rather changing the complete SQL.
I have two questions . How am I going to create a TO_DATE function which Oracle uses and write something similar in SQLserver ?
And If I cannot do that how am I going to get the database 's datetime format and create with VB.NET the Datetime value from the two variables ?

My problem I believe is quite complex. I would be mostly appreciated if you could help me on this.

Thank you

View Replies !
Sql 2005 ... Invalid Length Parameter Passed To The SUBSTRING Function.
Hi,
I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error,
€œSQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. €œ
Through research I have no clue as what I need to do, or where to look.
The package runs without error when I execute the package itself.
Any help is greatly appreciated.
Thanks,
Lori

View Replies !
'Copy Website' Function In VS 2005 Updates Or Overwrites Database?
I have a sql 2005 express database uploaded to my website with important information in it.

Now, I had to make some table change and need to update the online database.

I am not sure if the 'Copy Website' function in Visual Studio 2005 will update the database structure and data or will simply overwrite it.

Does anybody know the answer? If it overwrites it, would you please point me to information on how can I update the database structure and data without ruining it?

Thanks.

View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View Replies !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,
 
I executed the following sql script successfuuly:
 
shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO
 
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
 
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
 
shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

 
I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

 
Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
 
Thanks in advance,
Scott Chang

View Replies !
A Function Smilar To DECODE Function In Oracle
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View Replies !
Using RAND Function In User Defined Function?
Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View Replies !
I Want A Function Like IfNull Function To Use In Expression Builder
Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o)  if the input value is null ( Like ifnull(colum1,0)  )

 

i hope to have the answer because i need it so much.

 

Maylo

View Replies !
SQL Server Function And Asp.net
Is it possible to call a SQL function from asp.net? I have created ( or rather, modified) two sql functions to return the number of business days within a given date range. I suppose I could create a stored procedure to handle this so I may have just answered my own question. But in the off-chance that I don't need to create a stored procedure, could someone please assist?

TIA

View Replies !
Least Function In SQL Server
Hi,

I want to use least function equalant in SQL Server. my statement is like this:
SELECT SUM(PRICE_WORDS)/(least(9,5)-1) from ABC

Which fucntion I would use?

Any help will be highly appreciated.

View Replies !
How To Apply SQL Server 2005 Express SP1 To The Version Of SQL Server 2005 Express Which Installs With Visual Studio 2005?
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.

How can apply SQL Server 2005 Express SP1 to update this existing instance?

Currently, if I run this query:

SELECT @@version

I get the following:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

After applying SP1, I should get 9.00.2047.00.


Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:

http://msdn.microsoft.com/vstudio/express/sql/download/


Thank you,

Bashman

View Replies !
Access Val Function In SQL Server
What is the counterpart of this function when using SQL Server 2000? Im
getting this error "System.Data.SqlClient.SqlException: 'Val' is not a
recognized function name"

What do I have to change in my queryString?

SELECT ASP_MainForm.UserID, Val([FormNo]) AS Expr1,
ASP_MainForm.DateCreated, ASP_MainForm.DateNeeded,
ASP_MainForm.FormStatus, ASP_MainForm.Print, ASP_MainForm.PRNo,
ASP_MainForm.ForUse, [FirstName]+' '+[LastName] AS CompName FROM
ASP_MainForm INNER JOIN CEN_USERS ON ASP_MainForm.UserID =
CEN_USERS.UserBadgeNo WHERE
(((ASP_MainForm.FormStatus)<>'Approved') AND
((ASP_MainForm.ForUse)<>'Test')) ORDER BY Expr1 DESC

Pls help thanks

View Replies !
How To Use Date Function In Sql Server
HiI am trying to do a simple select using a date value.For eg:-in oracle i would do the followingselect count(*) from TEMP_TABLE where to_char(modif_time,'mm/dd/yyyy')='10/04/03'How do I accomplish the same in sqlserver?Thanks in Advancesk

View Replies !
Trouble Using The CONTAINS() Function In Sql Server
I am having trouble using the CONTAINS function in sqlserver(enterprise manager). I am typing the following:Select *FROM mytableWHERE CONTAINS(myfield,'mystring')This returns the error:Query Designer encountered a MS Design Tools error:ODBC error:[Microsoft][ODBC SQL Server Driver]'CONTAINS' is not arecognizedfunction name.[Microsoft][ODBC SQL Server Driver][SQLServer]Statement(s) couldnot be prepared.According to the T-SQL reference on msdn this is a supported function?Am I typing something incorrectly? If this is not supported, isthere an equivalent to this function? In my field I have some commadelimited values ie 'apple,orange'. So I need to be able to discernwhich records contain orange. The LIKE keyword doesn't seem to workfor this scenario. Any help would be greatly appreciated.

View Replies !
The Median Function In SQL Server
I read the follow query about calculating median posted by Daivd Portaon 10/8/03.CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOTNULL)INSERT INTO SomeValues VALUES ('A',1)INSERT INTO SomeValues VALUES ('B',2)INSERT INTO SomeValues VALUES ('C',3)INSERT INTO SomeValues VALUES ('D',4)INSERT INTO SomeValues VALUES ('E',5)SELECT S1.valuex AS medianFROM SomeValues AS S1, SomeValues AS S2GROUP BY S1.valuexHAVING SUM(CASE WHEN S2.valuex <= S1.valuexTHEN 1 ELSE 0 END)[color=blue]>= ((COUNT(*) + 1) / 2)[/color]AND SUM(CASE WHEN S2.valuex >= S1.valuexTHEN 1 ELSE 0 END)[color=blue]>= (COUNT(*)/2 + 1)[/color]I have difficulty to understand the having clause. If S1 and S2 arethe same table, what it means by S2.valuex >= S1.valuex? Could somegive me a help?Also, if I have a table structured as:classID field1 field2 field3c1 1 2 3c1 4 5 6c1 7 8 9c2 9 8 7c2 6 5 4c2 3 2 1Is there a way to create a user-defined function that can get themedian for each field as easy as the average function. Such asselect distinct classID,median(field1),median(field2),median(field3)from [tablename]group by classIDThanks in advance

View Replies !
Function And Linked Server
I have my own function, which I can use:declare @dt as datetimeselect @dt='20040121 12:22:33'select * from index_gold_iif(@dt)When I try do it from linked serwer:declare @dt as datetimeselect @dt='20040121 12:22:33'select * fromlewiatan.e_.dbo.index_gold_iif(@dt)I have massage:Server: Msg 170, Level 15, State 31, Line 2Line 4: Incorrect syntax near '('.Did server can't see function from linked server? Is it true or I have donesome error (what)?MarekPS. all done as "sa" user, linked server linked with sa account

View Replies !
Server Heartbeat Function
Hi all. I have a simple vbs script I use to connect to sql server, tryand read from a table, and if it fails notify me that the sql servermight be down. Works fine in its very simple way, but what I amwondering is if I should try and connect to one table from everyspecific database on the server? Is it possible for one database onthis server to be up, say for instance the master db and one of myuser db's to not be up? Just wondering about false positives and if Ishould test deeper.Not trying to make it to complicated, it really is the equivanlent ofa dashboard idiot light!

View Replies !
Equivalent Of Val() Function In SQL Server?
what is the equivalent of val() function in SQL server?
and cstr() function

View Replies !
SQL Select From A Function On A Different Server
Hi all, in SQL 2000 I can select data from a different server using the sintax
"select * from server_name.db_name.owner.table_name".
Can i do the same thing with a table function (e.g "select * from server_name.db_name.owner.MyFun_name()")?
Is there a way to do it?

Thank you all, Andrew

View Replies !
Function Can&#39;t Run In SQL Server 2000
Hi,

i can't use f_test

alter function dbo.f_test(@targetname varchar(255))
returns varchar(255)
as
begin
return(left(@targetname,1))
end
go
grant all on f_test to dbo
go
select f_test(targetname) from targets

It gives back the following message : why ???

Server: Msg 195, Level 15, State 10, Line 1
'f_test' n'est pas un nom de fonction reconnu.

Thanks to your answers
Axel

View Replies !
Function Creation In SQL Server 7.0
HI,
I need to create a function in SQL Server 7.0. Is there any way I can do this in this version.
I appreciate your help in advance.
thanks,
Sravan.

View Replies !
SQL Server Call C++ Function?
Hi,

Does anyone know if/how SQL server can call a function in a C++ library?

Cheers,

Xiaobing

View Replies !

Copyright © 2005-08 www.BigResource.com, All rights reserved