CLR Function Equivalent In SQL 2000
Jan 31, 2008
Hi,
I have successfully created a CLR function (C#) in SQL 2005 and call it from a stored procedure. I need to be able to provide the same functionality for SQL 2000 servers. Is this at all possible? I have read loads of conflicting information on the web about using COM to access the dll €“ can it be done or not? The function accepts serveral input parameters and internally access the data store using a context connection and a datareader.
Thanks.
View 6 Replies
ADVERTISEMENT
Jul 20, 2005
Hi All,I am facing a problem with a sql what i used in MS Access but its notreturning the same result in MS Sql Server 2000. Here i am giving thesql:SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHEREMY_ID=1The above sql in ACCESS return me the date in below format in onecolumn:Friday 09, 2003But in Sql server 2000 i am not getting the same format eventhough iam using convert function, date part function etc.Please if you find the solution would be helpful for me..ThanksHoque
View 3 Replies
View Related
Nov 16, 2006
...For Oracles DECODE function?
I am trying to get a conditional output RETURNed to the Grid output and have not found it in SQL Help. Help!!!
Here is Oracles example:
Select Distinct City,
DECODE (City, 'Cincinnati', 'Queen City', 'New York', 'Big Apple', 'Chicago',
'City of Broad Shoulders', City) AS Nickname
From Cities;
View 4 Replies
View Related
Jan 21, 2005
Hi,
I'd like to perform an update on a database only when certain conditions are met. Hence, one of those conditions would be a positive answer from the user running the query. Basically, I'd like to display a message with a yes/no option for the user to choose from. If and only if the user user selects the YES option should we perform the update.
Inside a transact SQL "IF ... THEN", how can I achieve this?
Thanks,
SC
View 10 Replies
View Related
Mar 8, 2005
what is the equivalent of val() function in SQL server?
and cstr() function
View 4 Replies
View Related
Dec 3, 2007
Trying to write the below in an expression box, I still have not figured out an equivalent to €śIN€? in SQL syntax, so far I€™ve had to use €śOR€? to get it to work. Anyone know the right way? I€™m trying to avoid writing this in the stored proc
Example:
Iif (Fields!INVESTOR_NBR.Value in (0, 265, 465), "DIRECT", "PARTICIPATIONS")
View 1 Replies
View Related
Oct 14, 2005
Hi All,
Once again I find myself smacking my head into a wall (and it will probably be for naught). I'm trying to find a specific character in a string within a specific record. I've tried substring, but it's not quite getting me where I want to be. I even tried "nesting" the substring and got nowhere (but I could have had the syntax wrong when I nested it).
Here is a sample
Select substring(productimagefile, 20, 5)
from my table
What I need is something like this:
Select substring(findoneof(productimagefile, _r, 1), 1, 5)
from my table
Actually, on the start of the substring, I need it to be where it finds the _r, just not sure how to tell it that.
The replace function is the closest I've seen to what I need, but I don't want to replace it with anything, I just need to find it in the string.
So, if anyone has any ideas, I'd be grateful (and my head would stop hurting....)
Schimelcat
View 1 Replies
View Related
Jul 23, 2005
Hello. I have an Informix SQL statement that I need to run in MS SQLServer. When I try to execute it I get the following error message fromQuery Analyzer:Server: Msg 195, Level 15, State 10, Line 4'date' is not a recognized function name.Can anyone help me convert this informix sql statement into and MS SqlStatement? Here is the query I have:selecta.comp_code,a.comp_date,case when date(date(date(comp_date - day(comp_date) +1) - 2 unitsmonth) - 1 units day) < b.inception_date then b.inception_dateelse date(date(date(comp_date - day(comp_date) +1) - 2 units month) - 1units day)end prior_date,a.net_return,a.net_uv,a.gross_return,a.gross_uv,a.estimated_flagfrom composite_perf a, composite_detail bwhere(month(a.comp_date) in (3,6,9,12) or a.comp_date = b.inception_date)and a.comp_code = 'AEU'and a.comp_code = b.comp_codeinto #tmp_composite_dataThanks in advance.
View 4 Replies
View Related
May 5, 2006
Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
View 14 Replies
View Related
May 5, 2006
Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
View 4 Replies
View Related
Mar 7, 2005
what is the ISNull () equivalent in MS SQL 2000?
Syntax please
View 2 Replies
View Related
Sep 7, 2005
There are three columns I need to query and rank, then group and total. I'm using MS SQL server 2000 and it doesn’t have a ranking function like the newer SQL server and oracle has. Does anyone have a clever way to create or simulate a ranking function? Thanks so much!
Jake :confused:
View 1 Replies
View Related
Oct 11, 2007
I am looking for the MS SQL equivalent of MySQL LIMIT.
I know that the code in MySQL is
SELECT col_name FROM tbl_name LIMIT x,y
where x = the starting row and y = the offset
E.g. SELECT movie_name FROM my_movies LIMIT 10,5
This will give me the records from 11-15
I would like to know what is the MS SQL equivalent for the above?
View 4 Replies
View Related
Jan 19, 2007
Hello Guys!i have been working with oracle with quite a time. No i migrated to sqlserver 2000 and i want to create a trigger on a table.the trigger function has to update the Modification field to getdate()whenever a row is being updated.i tried lots of thingsif anyone can help i would appreciate a lot!Regards
View 2 Replies
View Related
Mar 29, 2006
What is the equivalent of the SQL Server 2000 Full-Text Search Service in SQL 2005?
I need to know cos i got a forum app implementing this in SQL 2000 but my company is using SQL 2005 Enterprise.
cos i cannot find this option in sql 2005.....
View 1 Replies
View Related
Mar 3, 2005
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
View 7 Replies
View Related
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
Feb 27, 2004
I need to use the mod function on a numeric field with 2digits decimal.
I was told that SQL server does not support mod on a decimal number.
Is it correct? If yes, is there any way around this?
Thanks in advance
View 14 Replies
View Related
Jun 20, 2004
Hello,
I want to select the characters before "-" from Column A.
For example:
12345-HSC
222-ABC
I'd would like to select it as:
12345
222
Please Help!!!!!!!!!!!!!!!!!!!!! :confused:
View 3 Replies
View Related
Mar 1, 2005
I had built a one calander in SQLSERVER
View 1 Replies
View Related
Aug 5, 2005
My Table Structure is
Mem IDNameBoss ID
=======================
1Mohan0
2Raju1
3Prem1
4Priya2
5Sara3
6Ray4
7Chan0
I need to procedure to retrive all the down Members by giving a member number.
I have created a recresive function to achive this but at one stage it's giving error(above 32 level it's throwing an error). So i want a procedure which populates the temp. table with the required Information.
View 2 Replies
View Related
Dec 7, 2007
I have a stored procedure that raises an error just fine, but the problem is when i want to catch that particular error in client code, how do i go able doing so, i want to display a message in a label when that particular error occurs.
right now all i know about the raiserror function is RAISERROR("Message", Severity, Stage) how do i give it number so i can catch it in code?
ALTER Stored Procedure [dbo].[spName]
..............................................
...............................................
IF (@DummySetKey IS NULL)
BEGIN
RAISERROR('Error: This setkey number does not exist as a valid entry.', 11, 1);
SELECT @ErrCode = @@ERROR
END
IF @ErrCode <> 0 GOTO ERROR_HANDLER
ERROR_HANDLER:
ROLLBACK TRANSACTION
RETURN @@ERROR
client code:
Public Function ExecuteNonQuery(byval connstr as string) as boolean
dim conn as new SqlConnection(connstr)
Dim cmd as sqlCommand(spName, conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
try
dim rowsAffected as Integer = cmd.ExecuteNonQuery()
If ex.ErrorCode = 5021 Then
' display in textbox
Else
Throw
End If
Finally
conn.Close()
end try
if rowsAffected > 0 Then
return true
else
return false
end if
End Function
View 1 Replies
View Related
Jul 20, 2005
Hi Group...In one of my tables in a SQL-2000 db, do I have a calculated column callinga user defined function.That's not a problem, but now I need to update another column in the sametable form this udf.It seems not to be possible in SQL-2000, so how is such thing normally done?Do I need to call e stored procedure from within the function, with thenessesary parameters, and then let this stored procedure perform the update,or is it a better idea to change the function used in the computed column toa stored procedure ?If so, how am I programming the call to the procedure?Something similar to the function-call which is done like:CREATE TABLE table1 (column1 int AS dbo.fn_calculate_something (parameter1, parameter2),column2 .....column3.....)Thanks in advance.Steen
View 1 Replies
View Related
Sep 12, 2006
A stored procedure in the cache is automatically recompiled when a table it refers to has a table structure change. User defined functions are not. Here's a simplified code sample:
set nocount on
go
create table tmpTest (a int, b int, c int)
insert into tmpTest (a, b, c) values (1, 2, 3)
insert into tmpTest (a, b, c) values (2, 3, 4)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fTest]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fTest]
GO
CREATE FUNCTION dbo.fTest (@a int)
RETURNS TABLE
AS
RETURN (SELECT * from tmpTest where a = @a)
GO
select * from fTest(1)
CREATE TABLE dbo.Tmp_tmpTest
(
a int NULL,
b int NULL,
d int NULL,
c int NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.tmpTest)
EXEC('INSERT INTO dbo.Tmp_tmpTest (a, b, c)
SELECT a, b, c FROM dbo.tmpTest TABLOCKX')
DROP TABLE dbo.tmpTest
EXECUTE sp_rename N'dbo.Tmp_tmpTest', N'tmpTest', 'OBJECT'
select * from fTest(1)
drop table tmpTest
Running it, the output is:
a b c
----------- ----------- -----------
1 2 3
Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'tmpTest'.
a b c
----------- ----------- -----------
1 2 NULL
(I know that "select *" is bad, but it's a lot of legacy code that I'm working with here, and that's how it's written.)
The function doesn't detect that the table has changed in structure, or even that there is no longer a dependency on tmpTest. (Appending a column rather than inserting has the same effect, in that only the first 3 columns are returned.)
DBCC FREEPROCCACHE has no effect, not that I really expected it to, but you never know...
Is there any way, other than dropping and recreating, to force a recompilation of a particular function in memory, or perhaps all functions?
Thanks in anticipation.
Tom
View 6 Replies
View Related
May 19, 2006
My company would like to start keeping track of everytime a table, stored procedure, or function is changed.
What is the best way to do this?
View 4 Replies
View Related
Jul 1, 2015
I have a query that uses the PIVOT function and works fine in SQL 2012. I've been asked to move the query to a database that has the compatibility level set to 80(SQL 2000). I receive an "Incorrect syntax near" error when I try to excute the query on the SQL 2000 database. I would like to duplicate the exiting PIVOT functionality in SQL 2000.The existing query retrieves employee names and the order that the employee should be displayed from a table. The names will appear on the report according to the order that is retrieved from the database. Also, the users have requested that only 5 names appear on each row of the report. This is why the PIVOT function was needed. Below is an example of how the existing query works.
Table
CREATE TABLE [dbo].[EmpGuest](
 [Guest_ID] [int] NOT NULL,
 [Guest_Name] [varchar](80) NULL,
 [Display_Order] [int] NULL
) ON [PRIMARY]
[code]....
View 4 Replies
View Related
Sep 21, 2004
I am facing a problem with Convert function in SQL server 2000 with sp3 installed , Japanese version
In sql server 2000 (sp1 installed) Japanese version, the convert(datetime,,111) gives me date in the format yyyy-mm-dd hh:min:sec
But in sql server 2000 (sp3 instaled) Japanese version, the convert(datetime, ,111) gives me date in the format yyyy-mm-dd.
I need the hh:min:sec to do an exact date comparison.
The problem is solved if I use Convert(datetime,,120).
Any pointers on the root cause of why the problem occurs with convert() function once sp3 is installed?
View 1 Replies
View Related
Feb 19, 2008
Is it possible to define your own function? If so could you give me an example.
Keep in mind that I said in SQL Server 2000. I want no CLR SQL Server 2005 solutions.
Cheers,
David
View 1 Replies
View Related
Jul 20, 2005
HI,i got a problem while using StrConv function in sql server.My requirement is:-suppose in a name field i have "jhon smith" Now i want to run a sqlwhich will give me the result like "Jhon Smith"--which means uppercase first.Now i am running a query like below which is giving me "Jhon smith"but i want "Jhon Smith".SELECT [CustomerID] ,upper(left(firstname,1)) + lower(right(firstname,len(firstname)-1)) AS [FirstName_] from my_table;But the above query is not giving me the right result. Pleasesuggest...ThanksHoque
View 2 Replies
View Related
Nov 23, 2005
I have looked around and found the equivalent for IIF (Access) to be aSELECT CASE in SQL. I have tried this with no success. I am also lookingfor the equivalent of MAX and have had no luck. The portion of thestring I am trying to SQL'ize is:SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN FROM ADCNINNER JOIN Sheet ON ADCN.RecordID = Sheet.RecordID WHERE (Sheet.Drawing= '" & x & "') AND (Sheet.SheetNumber = 0);This portion is the most important:SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Aug 15, 2006
Recently, my company's deveolpment machine crashed. We had to format the harddrive which meant we lost some of our databases.
We managed to restore the databases, but we do not know in what state, i.e. which stored procedures were written between the restore point and the point the machine crashed.
Tow questions related to this:
1. I was wondering if there is any product that is similar to soursesafe but appied to databases, so that this problem will not happen again. Does Enterprise manager do something like this?
2. I was also wondering if there was anyway to undo an sql command. e.g. my colleague recently ran a command to update a database, but forgot to write the where clause!! That data was recovered aswell, but it would be nice to have some sort of undo button.
Thanks in advance.
Jagdip
View 1 Replies
View Related
Dec 28, 2006
Is there anyway I can return only the last row of a query, like TOP does for the top most items?I would like to return something like this:SELECT BOTTOM 1 Column_CFROM Table1WHERE Column_A = somethingAny help would be greatly appreciated. Thank you!
View 3 Replies
View Related
Jan 7, 2008
Hi everyoneI have a table 'users' with column 'residence' New York, NY Houston, TXSan Antonio, TX In MySQL I can select the city by running the following command SELECT SUBSTRING_INDEX(residence, ',' , 1) FROM users. How would I accomplish the same thing using ms sql? Thanks in advance
View 2 Replies
View Related
