I have Server1(MSSQL7) & Server2(MSSQL 2000) are linked. For trigger purpose I need to access latest identity value from Server2.mydatabase.mytable in Server1 so I have statement as follows
select @id = IDENT_CURRENT(‘server2.mydatabase.mytable’)
I get following error
'IDENT_CURRENT' is not a recognized function name.
Any idea why I am getting this error? Any help is appreciate
Hello,
I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.
It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...
I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.
Thank
Felix Pageau
fpageau@str.ca
You can test the functionnality with the following code:
create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
Declare @count as int
set @count = (select count(IDnumber) from identCurrentBugExeample)
--Print the number of record in the table
print @count
--Supposed to print 0 because there isn't any identity values that has been used
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 1 because the last identity used is 1
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 2 because the last identity used is 2
print ident_current('identCurrentBugExeample')
drop table identCurrentBugExeample
I found a strange problem with IDENT_CURRENT returning NULL value in all my SP's in which it is used.Following are the general reasons why it returns NULL :1. Its executed against the Empty table.2. Table does not Identity column.But i could check that above two point were not a prolblem for me. The problem was the database user has only db_executor permissions. If it is set to db_owner all my insert SP's work's as it should. But it fails otherwise.Can anybody throw some lights here ?
View 1 Replies View RelatedHi thereI have small problem with IDENT_CURRENT...We have a table where PK is generated automatically.What I want is to get that value and insert it to another,corresponding table.So here is what I am doing...BEGIN TRANInsert Into table() --> PK is created.SELECT IDENT_CURRENT('Table_Name') AS lastValueCOMMIT TRanIT works fine, but there is a possibility to insert another record byanother user before I get my IDENT_CURRENT.For instance:BEGIN TRANInsert Into table() --> PK is created.---->somebody else inserts another record into same table.SELECT IDENT_CURRENT('Table_Name') AS lastValue---->this is not the value that I generated....COMMIT TRan
View 6 Replies View RelatedI have a table that uses an identity column. I have a second column that will contain the current identity value or, when the original row is replaced, the identity value of the replacing row.
USE [test]
GO
/****** Object: Table [dbo].[test1] Script Date: 04/10/2008 14:04:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test1](
[col1] [int] IDENTITY(1,1) NOT NULL,
[col2] [int] NOT NULL,
[col3] [varchar](max) NOT NULL,
[ts] [timestamp] NOT NULL,
CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
My question is on the insert is it safe to use the following statement to insert the new identity value to the second column?
INSERT INTO test1
(col2, col3)
VALUES (IDENT_CURRENT('test1'), 'test')
BOL says (or has had added ) on the subject of IDENT_CURRENT
Exceptions
Returns NULL on error or if a caller does not have permission to view the object.
In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object.
However am finding that SCOPE_IDENTITY does continue to function for a user who has not been granted meta data permisisons or directly (select) on the underlying table.
can somebody explain why they are different behaviours. I need to be able to capture the ID of a row just inserted (under the insert rights of my application role) I'm glad SCOPE_IDENTITY works but am nervous am working in an area of undefined behaviour ! after all its also a metadata-emitting, built-in function
My application role is the owner of the schema and the storedprocedure which trys to use IDENT_CURRENT but it consistently returns me a null when logged in as a user without admin priveleges
any info much appreciated
Iain
Hello,
The following statement behave different between SQL2000 and SQL2005.
On our database we have some insert statements like that, and as we test our DB for SQL2005, we have found this difference. Can anyone tell me why?
create table #tmp (a int identity(1,1), b int)
insert into #tmp
values(ident_current('#tmp'))
select * from #tmp
insert into #tmp
values(ident_current('#tmp'))
select * from #tmp
drop table #tmp
SQL2000
a b
----------- -----------
1 1
a b
----------- -----------
1 1
2 1
SQL2005
a b
----------- -----------
1 1
a b
----------- -----------
1 1
2 2
I'm using visual basic and SQL
Here's my string:
SELECT intD1 = IDENT_CURRENT(ServerLogID)FROM ServerLog
I'm trying to select the primary key of the last record from a table and place it in the variable 'intD1'
I think I'm missing something, but I'm not quite sure what!
hi to all
who is the best use among IDENT_CURRENT and @@IDENTITY and SCOPE_IDENTITY when i wnat to get last inserted id from a table.
and also give the reason why because i am little bit confuse for useing these..
thanks in advance.
arvind
Hi,can somebody explain me, why the IDENT_CURRENT from an empty table is 1?After insert of the first record it is still 1 (assuming that start valueis 1) which is okay. But if i check the IDENT_CURRENT from a newly createdtable the result should be NULL, or not?bye,Helmut
View 10 Replies View RelatedHi
I am trying to get the last ID value of a table.
The following code worked when a SQLConnection object was used, how do I achieve the same result using an SQLDataSource?
this.sqlSelectCmd2 = new System.Data.SqlClient.SqlCommand("SELECT IDENT_CURRENT('Assets')",sqlConnection1);
sqlConnection1.Open();
int nID = Convert.ToInt32( this.sqlSelectCmd2.ExecuteScalar() ) ;
nID++;
dc.FocusedRow["ID"] = nID.ToString();
Cheers
Pen
Hi
I have a SSIS package that imports data into a staging table from an excel sheet (This works fine). From the staging tabler i want it to insert the values into my members table, take that unique indentityID that gets created and insert the other values into other tables for that member that was just created.
In the staging table, i have all the values for a single member. But the structure of the database needs all the values inserted into seperate tables. There is no conditions ID in my members table, so the member first has to be created and from there i need to use the newly created member's MemberID and insert the conditions into a seperate table using the MemberID
I have created some sample data that can be used. I think i have an idea of how to do it, but i'm not totally sure if it will work that way, i have however included it in the sample data.
Code Snippet
DECLARE @ImportedStagingData TABLE
(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50),
[Chronic Heart Failure] INT,
[Colon Cancer] INT
)
INSERT INTO @ImportedStagingData VALUES ('Carel', 'Greaves', 'CarelG@Email.com', 1,0)
INSERT INTO @ImportedStagingData VALUES ('Jamie', 'Jameson', 'JamieJ@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Sarah', 'Bolls', 'SarahB@Email.com', 0,1)
INSERT INTO @ImportedStagingData VALUES ('Bells', 'Scotch', 'BellsS@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Stroh', 'Rum', 'StrohR@Email.com', 0,0)
DECLARE @Conditions TABLE
(
ID INT IDENTITY(1,1),
Condition VARCHAR(50)
)
INSERT INTO @Conditions VALUES ('Chronic Heart Failure')
INSERT INTO @Conditions VALUES ('Colon Cancer')
DECLARE @Members TABLE
(
MemberID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50)
)
DECLARE @memConditions TABLE
(
MemberID INT,
ConditionID INT
)
SELECT * FROM @ImportedStagingData
SELECT * FROM @Conditions
SELECT * FROM @Members
SELECT * FROM @memConditions
/* --- This is the part that i am battling with ---
DECLARE @CurrentValue INT
DECLARE @numValues INT
SET @numValues = (SELECT COUNT(ID) FROM @ImportedStagingData)
WHILE @numValues <> 0
BEGIN
INSERT INTO @Members
SELECT Name, surname, email
FROM @ImportedStagingData
GO
SET @CurrentValue = (SELECT IDENT_CURRENT('@ImportedStagingData'))
INSERT INTO @memConditions (MemberID), (ConditionID)
VALUES (@CurrentValue, --ConditionValue from @ImportedStagingData, all the values that have a 1)
@numValues = @numValues - 1
END
END
*/
All help will be greatly appreciated.
Kind Regards
Carel Greaves
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 3 Replies View RelatedHi 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
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
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
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
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?
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 3 Replies View Related
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
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)
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!
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 5 Replies View RelatedI have a table:tblRateUserUserIDJudge intUserIDJudged intscore int
this table contains the userid of the person giving a score (judge) and the person receiving the score (judged) and the score itself.
Each user can only score another user once.
tblRateUser content:judged judge score6 5 87 5 107 6 515 7 415 5 9
When a new score is inserted I want to get the average score for the rated user by counting all scores for that user and divide by the number of records.
declare @avgscore decimal(4,1) set @avgscore=(select avg(score) from tblRateUser WHERE UserCodeJudged=@ID) print 'avg score: '+ cast(@avgscore as nvarchar(15)) in the above example this results for usercode judged 7 in an average score of 7.0 whereas I would expect 7.5 (10+5)/2
what am I doing wrong?
I have this scalar -valued function in sql called dbo.GetGoodCustomer ALTER function [dbo].[GetGoodCustomer](@client_id int)returns numeric(10, 2)asbegin declare @getgoodcustomer as numeric(10, 2) declare @review_type as int select @review_type = item_num from customers where client_id = @client_id if @review_type = 0 begin select @getgoodcustomer = getgoodcustomer from customers where client_id = @client_id end if @review_type > 0 begin select @getgoodcustomer = l.g from customers c inner join detail l on c.client_id = l.client_id where c.client_id = @client_Id and c.item_num = l.item_num endReturn @getgoodcustomerendNow, how can I use this function in asp.net (vb.net) ?Because whatever is getgoodcustomer would give me I need to display that in datagrid's text box.Loop thru datagrid If CType(dgItem.FindControl("txtID"), TextBox).Text = 13 ThenDim txtgoodcustomer As TextBox = CType(dgItem.FindControl("txtcust"), TextBox)basically amount returns from function would be showing in txtcust textbox.I don't know I have never done this before..
View 19 Replies View RelatedDECLARE @TotalAmount AS INTEGER DECLARE @TotalPoints As INTEGER SELECT @TotalAmount = SUM(Amount) From Rewards WHERE MemberID = @MemberID AND Redeemed = 1 @TotalPoints = @TotalAmount MOD 20
I have the code above but I keep getting an error message around the last line that I am not sure what it means. Can anyone please help?? Thanks in Advanc3e
SQL Server has no Last() and Max() pulls the Max not the last. Is there a way to pull the Last payment amount with the date of that payment, in SQL Server????
tblClients - ClientID (PK)
tblPayments - PaymentID (PK)
Thanks for any help,
Can a UDF return a table query as result? Please kindly provide sample T-SQL. Thanks.
View 6 Replies View RelatedHi
I'm creating DTS packages to load data from text files. I'm finding that a lot of the data needs to be transformed in the same way (e.g trailing "." removed). Rather than writing individual activex scripts to do this, is it possible to write my own DTS function which I call from my DTS packages?
Thanks
Andy
Hi,
I am upgrading an AccessXP database to SQL 2000 and have an issue with week numbers.
There is currently a piece of VBA that calculates the week number for time recording purposes using the DatePart function as
DefaultVal_WeekNo = DatePart("ww", TaxDate, vbMonday, vbFirstFourDays)
I have tried to get this replicated in SQL by using
declare @TaxDate as datetime
declare @TheWeek float
set @TaxDate = @TheDate -90 -7
set @TheWeek = cast(datepart("ww",@TheDate) as numeric (30))
-
cast(datediff("wk", @TaxDate, @TheDate) as numeric (30))
but this is not reurning the correct values, i belive this is because the VB function has the vbMonday argument.
Can anyone tell me how i can get wround this please?
TIA
Is there a SQL MTD function ! Let's I want to delete MTD rows. Need something
like:
delete from abc where date_entered = mtd
Suggestions are appreciated.
We have len() function in some of our stored procedures. Why one of our SQL servers (6.5) will not recognize this function while others do? While we transfer the stored procedures, the stored procedure can not be transfered to the database of that server.
Your immediate help will be appreciated.
Su