MS SQL Server :: Urgent!!!!!!!! Decode Function In Sql

Help In MAX Aggregate Function, Plz, Urgent
wat i want
wat i need to get is all maxBids wich are more than the last bid made and i shuould get the maximumBid if the same user(uId in this case) has two entried of maxBids. and i don't want the last bidder(uId) to get into the result u can see it below, where i have showed the results.
so i want
1) uId and maxBid::: with no redundancy, but should get largest maxBid offered by uId if more than one maxBid is offered by same uId , look at the table, i have marked it with X in bold
2) don't want to get the uId of last bidder. marked with BOLD Y

situation is like i m trying to do proxy bidding(automated bidding).

select temp1.uId,maxBid,bidDate
from temp1
where temp1.auctionId = 7
and temp1.uId <> 2 I don't want last bidder, how to discard last entry, wat should be this query, how to get last bidder's uId
and temp1.maxBid > = (select max(currBid) from temp1 where auctionId=7)
order by bidDate

heres the table

bidIdauctionIduIdcurrBidmaxBidbidDate
17715500015500012/5/2003 3:31:51 AM
27816000018000012/5/2003 4:27:03 AM---X
37216500016500012/5/2003 5:37:29 AM
47817000019000012/5/2003 5:39:29 AM---X
57117500017500012/5/2003 5:44:29 AM---Y

heres the result

uIdmaxBid bidDate
8180000.00002003-12-05 04:27:03.000-----Z smaller bid of same uId
8190000.00002003-12-05 05:39:29.000
1175000.00002003-12-05 05:44:29.000-----Z latest/last bidder

i have all userIds in t_user table.
i don't want the rows with X

View Replies !

Urgent : Update() Function
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTrigger
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< DROPPED TABLE dbo.TestTrigger >>>'
END
go
CREATE TABLE dbo.TestTrigger
(
colA int NULL,
colB int NULL
)
go
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TestTrigger >>>'
go

IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
BEGIN
DROP TRIGGER dbo.TestTrigger_i
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< FAILED DROPPING TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< DROPPED TRIGGER dbo.TestTrigger_i >>>'
END
go
CREATE TRIGGER dbo.TestTrigger_i
ON dbo.TestTrigger
FOR INSERT AS

IF UPDATE(colA)
select "updating col A"

IF UPDATE(colB)
select "updating col B"

go
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< CREATED TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< FAILED CREATING TRIGGER dbo.TestTrigger_i >>>'
go

insert into TestTrigger
(colA)
values
(1)
go

insert into TestTrigger
(colA, colB)
values
(2,3)
go

View Replies !

A Transact SQL Function -urgent! -
HI there,
Is there a function on transact SQL which test if a character belong to expression,I want to select from a table only the address that contain '@'.

Thanks in advance
Jalal

View Replies !

URGENT CONVERT FUNCTION
CAN SQL GURUS(PESO,MADHAVAN,VOLTER,KRISTEN) PROVIDE ME EXAMPLE OF CONVERT AND CAST FUNCTION CLEARLY WITH EXAMPLE

View Replies !

DECODE?
I have to run a query to give a column a value based on a time range. Can Iuse DECODE?select decode(trans_date, trans_date>='01-Jul-2002' andtrans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,from. . .where. . .

View Replies !

DECODE In MS SQL
In Oracle we have expression

SELECT
.....
DECODE (T.LANGUAGE, 'E', T.DESC_ENG, T.DESC)
FROM
TABLE T

if acts like that :
if T.LANGUAGE = 'E' it returns T.DESC_ENG. If it is different - T.DESC.

Is there some way to make the same in MS SQL Server ?

View Replies !

Question On Convert Function URGENT Please!!
I have the following code in a stored procedure which needs modification as a result of the change in the input format for display_cur_price_tx field(price range).
------------------------------------------------------------------------------
old format for display_cur_price_tx is: $69 - $99

Old code for sp:

case charindex ('-', p.display_cur_price_tx)
when 0 then rtrim(convert (char(12), Convert (Numeric(7, 2), rtrim (substring (p.display_cur_price_tx, 2, len(p.display_cur_price_tx))))))
else rtrim (Convert(Char(12), Convert(Numeric(7 ,2), substring (p.display_cur_price_tx, 2, convert (int, charindex ('-', p.display_cur_price_tx) - 2))))) + ' - ' +rtrim(Convert (Char(12), convert(Numeric(7, 2), substring (p.display_cur_price_tx, charindex ('-', p.display_cur_price_tx) + 3, len(p.display_cur_price_tx)))))
endas price

------------------------------------------------------------------------------
New format for display_cur_price_tx is: $69-$99 9without the spaces inbetween).

What needs to be new code for sp?

If I still run the old code mentioned above now, I am getting the following error:

Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

Any help on how to modify the above code to suit the new format for price range?? Any help is appreciated.
Thanks.
Sheila.

View Replies !

Decode In SQL Server
Hi

I have oracle statement and I want to translate it in SQL Server:

select DECODE(count(bid_Vendor), 1, NULL, COUNT(BID_VENDOR))
from bid_total

I tried it this way:
Select case(count(bid_vendor) when 1 then null else count(bid_vendor)end) as cs from bid_total

It gave me error i.e.

Incorrect syntax near the keyword 'when'

View Replies !

Decode Funktion
Hi!

I have a question about the "Decode" funktion. is it only avaiable in Oracle? The reason for my question is that I need to SELECT a colum in a table based on a int value. So if the uservalue is lower then that take that colum, lower then that take colum...etc.

Can I use decode? or/and is there better funktion (I'm using MS SQL 2005)

Thanks in advance

Mark

View Replies !

GROUP BY On DECODE
Hi,I have two questions, firstly..
I am hoping there is a way to GROUP BY on a concatenated output like the following decode etc..

SELECT
DECODE(PRIM_REASN,'','Reasons not entered',PRIM_REASN)
|| ' '
||SECDY_REASN
|| ' '
|| TERT_REASN) Downtime_Reasons,
EVENT_DUR/60 Minutes

FROM
DOWNTIME_WEB
WHERE
DOWNTIME_WEB.TIMESTAMP>= (SELECT PRODN_SHIFT.START_DATIME
FROM PRODN_SHIFT
WHERE
PRODN_SHIFT.START_DATIME <=sysdate
AND PRODN_SHIFT.END_DATIME > sysdate)
AND
TRIM(DOWNTIME_WEB.STATUS_TEXT) = 'STOPPED'
AND
TRIM(DOWNTIME_WEB.WORK_CTR_NAME) = 'PACK2'
ORDER BY DOWNTIME_WEB.EVENT_DUR

I have tried to put the DECODE etc into the GROUP BY statement but I get an error - " not a valid GROUP BY clause"

Secondly, is there a way of modifying value in field A based on the value in field B

Erroneous example follows - in an effort to explain what I want to do (from above):

DECODE(PRIM_REASN,'',(DECODE(EVENT_DUR,<200,"Low",>200,"High"),PRIM_REASN)
ie:If PRIM_REASN is null I want EVENT_DUR to be tested to determine the new value for PRIM_REASN.
Except that tthe above won't work and I don't know what the right function/argument should be..

This is my first foray into SQL server, I am trying to migrate some access queries to work with web services and hence Xcelsius.

Cheers.

View Replies !

Alternate To Decode
hi i have a view in oracle. in that i am using decode function.
same query i want to write it in sqlserver. what it is the alternate
to decode.

this is a cross tab query

SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2,
SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4
FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code

thanks

suji

View Replies !

Case? Decode?
Hello,

I need to do the following:

Lets say we have the following table.

A B C
1 XYZ XYZ
100

Now if the value of a is "100" then i need to do a join and get a value from another table, else I put the value of b a "test"

I am trying to do this using case

like this

select case
(when a = 100 then (select col from ....where newtable.id = a.id
else "test"
end) as xyz
from a,b,other tables.

But the problem is that the subquery returns more than one row..(it should return one row)

How do I do this?

View Replies !

URGENT!!! Search Function Database Problem!
Hi all,
I have a search function in my website, which is fully functionable.
But currently, this is the issue that I am having problem with.
For example:
I have only the name 'John' in my database.
When the user search for 'John Tan', no results is shown as there was no such name in the database
But if this happens, I want 'John' to be always returned and shown, no matter what 'John' the user search - E.g "john tan", John Loh", john wee"
So, how do I go about doing this? I have done some research on how to go about doing it, what I found useful was the .Replace() function. However, I can never predict in advance what user is going to key in.
Any help is welcome as this is a rather pressing problem of mine. Thanks for all the help in advance!!!

View Replies !

Urgent - Replacement Of Number Function In Sql Server
Hey folks,

In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..

Select Number (*) from x

The output will be

Number(*)
1
2
3
4
5
6
and so on..

I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..

Can any one solve this issue...

Please help me in this....

Urs
VJ

View Replies !

How To Decode In SQL Server 2005
I am a novice to SQL Server, so this is probably a really easy problemto fix. I'm translating an Oracle query and need to change the'decode' to something compatible. Everything I've read points me tousing 'case' but no matter how I write it I can't get it to work andget a syntax error. Suggestions?select SYST CTR, isnull(substring(CD_A, 1, 3), ' ') RESCODE, DES DESCRIPTION, decode (substring(CD_A, 1, 3), CODE,PRICE,0) UNIT_PRICE, count (distinct OR_NO) QTYW

View Replies !

NULLIF Or CASE - Decode
Hi,-- SQL Stmt 1SELECT * FROM Table1WHERE Field1 = 123AND Field2 = 234AND Field3 = 345AND Field4 = 456AND Field5 = 567AND Field6 = 678-- SQL Stmt 2IF @@ROWCOUNT = 0SELECT * FROM Table1WHERE Field1 = 123AND Field2 = 234AND Field3 = 345AND Field4 = 456AND Field5 = 567I want to use a single SQL Stmt instead of 2 SQL Stmts.I am not looking for "(Field1 = 123 ..... and Field6 = 678) or (field1 = 123... and field5 = 567)"I know this can be achieved in Oracle with Decode.I was looking for a similar solution on SQL Server.May be possible with NULLIF and or Case.I am attaching script for creating a tableCREATE TABLE Table1(Field1 INT,Field2 INT,Field3 INT,Field4 INT,Field5 INT,Field6 INT)-- Hareesh

View Replies !

DECODE In SQL Server?&"
What is the function in SQL that works like DECODE in Oracle?"Thanks,N

View Replies !

Equallent To Decode In Oracle?
Hi, Here is my oracle statement. How can I change it to be compatible with SQL Server?

update propertytable set visible =decode(propertyid, 1,0, 2,0, 3,1, 5,1, 6,1, 7,0, 9,1, 10,1, 11,0, 14,1, 30,1, 38,1, 60,0, 232,0, 233,0, 415,1, 605,0) where parentid between 2000006001 and 2000006020

Thanks...

View Replies !

What Is The DECODE Statement In SQLSERVER?
I need to know what is the DECODE statement called in SQLSERVER?

Can somebody provide me simple decode statement for sqlserver.

Thanks,
Ranjan

View Replies !

How To Translate Oracle Decode
Hi,

How to translate oracle Decode without changed code I mean:

I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I

Wrote this function

select dbo.fnDecode( 1 , 2 , 3 )

first parameter always int, and the others parameters could be char, int or float.

I would like to test first value and return second or third value

-> sql_variant for all parameters, ok

but I can use + or -

I can't do this

select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )

If I put cast ok, but I is there another way, overload this call?

With clr doesn't work, because Sql Server doesn't accept function overload calls from C#

Any ideia????????????????????

cheers,

View Replies !

Decode Base64 To Image!
Hi,

I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.

The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)

Thanx

View Replies !

URGENT!!! Search Tool Function: Splitting A String
Hi All!!!
I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem:
The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned.
So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character.
Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID
2 FROM CRPL
3 WHERE (Trans LIKE '%' + @Trans + '%') OR
4 (Name LIKE '%' + @Name + '%') OR
5 (Address1 LIKE '%' + @Address1 + '%') OR
6 (Address1T LIKE '%' + @Address1T + '%') OR
7 (Address2 LIKE '%' + @Address2 + '%') OR
8 (Address2T LIKE '%' + @Address2T + '%') OR
9 (City LIKE '%' + @City + '%') OR
10 (CityT LIKE '%' + @CityT + '%')

Thanks for all your help in advance!!!

View Replies !

Joining On And Grouping By CASE Function Column Alias (URGENT)
I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC Ross

View Replies !

Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded
Hi all,

I have writen a Function which call's the same function it self. I'm getting the error as below.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.

CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

View Replies !

Timeout Expired -- URGENT, URGENT, URGENT!!!
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++

We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.

Please help!!!

View Replies !

Urgent Help Capture Part Of Data From A Field Urgent Help??
hi, I have a field named city_state that contains city and state together (Warren, OH) in the same field. I need to create two seperate columns one city, another state. how can I extract the state out of the city_state column and have two different column.
thanks

Ahmed

View Replies !

Urgent, Urgent !! My Sql Server Refused To Start Due To Encrypton
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed.
I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment.
The error is:
19015: The encrypton is required but no available certificat has been found.

Please help me to start sql server.

Thanks.

Michel

View Replies !

URGENT.Sql Server Does Not Recognise MAPI , Or Profile Name URGENT
hi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed

I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.

I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.

My next question, How to configure MAPI in Sql server if what I did was wrong.

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 !

Urgent Urgent Please.(Access SQL Pass Through Queries)
Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;

It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))

Thank you very much for your concern.

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 !

Urgent, Urgent, I Need Help To Uninstall SQL Server 2000 From Windows 2000 Cluster Node
Please help me !
I would like to know step-by-step the producre to uninstall properly SQL Server 2000 installed in a Cluster Windows 2000 Advanced Server.

Thanks for Helps

Michel

View Replies !

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 !

Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View Replies !

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

View Replies !

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View Replies !

Scheduling(URGENT... URGENT)
Hi everybody,

I have a batch which needs to be run every day night at 2:00 am.I Using At command but it is not working. if u have idea, please let me know

I AM USING THE FOLLOWING COMMAND :
AT 2:00AM C:SCHED.BAT

Thanks in advance
Krishna

View Replies !

Urgent Really Urgent Dbcc
hi all
its urgent really urgent
please reply soon

I am getting error in sysindexes when i run dbcc checkdb on a production db.
the error is Server: Msg 8928, Level 16, State 1, Line 1
please help me to remove this
all the options of dbcc checkdb as well as table are not helping me

thanks in advance

View Replies !

Use Getdate Function In A Own Function
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG

View Replies !

Calling A Function From A Function?
Hi All

Yesterday Peso was gracious enough to help me with creating function/views/sp's

I took those examples and extended what had from excel into function in SQL

however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?

Here are excerpts two functions I have:

We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)

and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)

As you can see the first part of the both query are simlar. In particular the:

SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]

and also the variables @StartDate and @EndDate.

In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?

Thank you!

View Replies !

Help Me With SQL DMO!! Urgent!!
Hi, I am trying to make a backup of my database using Sql-DMO

When I do the backup on my server, my code runs fine. But when I backup from across the network from another computer, the database is saved on the server and not on the computer from where I am executing my application. How set SQLl-DMO to backup on the computer from where I am?

here is my code (found on the net)... Any suggestions greatly appreciated!

Cursor = Cursors.WaitCursor
'create an instance of a server class
Dim my_srv As SQLDMO._SQLServer = New SQLDMO.SQLServerClass
'connect to the server
my_srv.Connect("servername", "userid", "password")

'create a backup class instance
Dim my_backup As SQLDMO.Backup = New SQLDMO.BackupClass
'set the backup device = files property
my_backup.Devices = my_backup.Files
'set the files property to the File Name text box
my_backup.Files = Me.txtFilePath.Text
'set the database to the chosen database
my_backup.Database = "MYDB"
'perform the backup
my_backup.SQLBackup(my_srv)
MsgBox("Database successfully backed up.", MsgBoxStyle.Information)
Cursor = Cursors.Default

View Replies !

T-SQL URGENT URGENT URGENT
Hello
I'm trying to execute the following
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
but its giving me an error becuase of @dbName which is a variable decleared as the followingDECLARE @dbName as varchar(100)
the Database name is assigned to @dbName
but i can't take the value of @dbName to combine it to the rest of the statment
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id

what should i do ! please help :(

View Replies !

Urgent Help Please
Hi Guys
i write a thread before asking for help with reading an uploaded csv file,
i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to take the read information and import it into my sql express db.
Heres the code<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If (IsPostBack) Then
Grid1.Visible = True
Else
Grid1.Visible = False
End If
End Sub
Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Save the uploaded file to an "Uploads" directory
' that already exists in the file system of the
' currently executing ASP.NET application.
' Creating an "Uploads" directory isolates uploaded
' files in a separate directory. This helps prevent
' users from overwriting existing application files by
' uploading files with names like "Web.config".
Dim saveDir As String = "Data"

' Get the physical file system path for the currently
' executing application.
Dim appPath As String = Request.PhysicalApplicationPath

' Before attempting to save the file, verify
' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
Dim savePath As String = appPath + saveDir + FileUpload1.FileName

' Call the SaveAs method to save the
' uploaded file to the specified path.
' This example does not perform all
' the necessary error checking.
' If a file with the same name
' already exists in the specified path,
' the uploaded file overwrites it.
FileUpload1.SaveAs(savePath)

' Notify the user that the file was uploaded successfully.
UploadStatusLabel.Text = "Your file was uploaded successfully."

Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If

End Sub

Sub DisplayButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:inetpubwwwrootMerlinLocalPostOfficeAppData;Extended Properties=""text;HDR=NO;FMT=Delimited"""
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()
Dim objCmdSelect As New OleDbCommand("SELECT * FROM csv.txt", objConn)
Dim objAdapter1 As New OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "csv.txt")
Grid1.DataSource = objDataset1.Tables(0).DefaultView
Grid1.DataBind()
objConn.Close()
End Sub

</script>
My csv file does not have headers so the default value is F1, F2, F3 F4, F5,F6, F7, my database has the following columns, ID,AddressLine1,AddressLine2,AddressLine3,AddressLine4,AddressLine5,AddressLine6,Postcode
I need to know how to import the information direct into the db rather than displaying it on the page, ive tried but im really new to this and cant get it to work. I cant use DTS or bulk insert as the server this will go on doesnt have sql on it, the db is an MDF file so is transportable with the app.
Thanks for your help

View Replies !

I Need Urgent Help
hi

I have two web forms
In the first form i am collecting some information.
i am passing this imformation to second form.
based on this information from first form i am inserting the image in sql server 2000 database. i have wrritten the code for inserting the image in database in second form.

i can pass the value to the second form.

The problem is

it is not inserting the image in the sql server 2000 database.

it is not showing any error.

here is the code.

private void Page_Load(object sender, System.EventArgs e)
{

if (!IsPostBack)
{
CB = (CreateBzzzrings) Context.Handler;

}
}

public void UploadBtn_Click(object sender, System.EventArgs e)
{
if (Page.IsValid)
{
Stream imgStream = UploadFile.PostedFile.InputStream;
int imgLen = UploadFile.PostedFile.ContentLength;
string imgContentType = UploadFile.PostedFile.ContentType;
byte[] imgBinaryData = new byte[imgLen];
int n = imgStream.Read(imgBinaryData,0,imgLen);
int RowsAffected = SaveToDB(imgBinaryData);
if ( RowsAffected>0 )
{
Response.Write("<BR>The Image was saved");
}
else
{
Response.Write("<BR>An error occurred uploading the image");

}
}

}
private int SaveToDB(byte[] imgbin)
{
string connstring = HttpContext.Current.Application["connstring"].ToString();

try
{
SqlConnection connection = new SqlConnection(connstring);
SqlCommand command = new SqlCommand("Update BZZZ_Bzzzrings_Group set GroupImage = '" + imgbin + "' WHERE UserID = " + nUID + " AND GroupName = '"+ CB.GroupName + "' AND CategoryName = '" + CB.CategoryName +"'",connection);

connection.Open();
int numRowsAffected = command.ExecuteNonQuery();
connection.Close();
return numRowsAffected;

}
catch (SqlException ex)
{
throw new Exception(ex.ToString());
}
finally
{

}

}

can any one help me

Thanks

View Replies !

Urgent!!!!!!!! Decode Function In Sql

how can I write decode function in sql such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource such that if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on

how can I write decode function in sql

such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource

such that if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on