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!
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 RelatedI 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
Hi 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
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
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
Sorry I am a newbie at this SQL thing.
I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..
a. Error log for failed inserts.
b. A command output file.
Any information would be greatly appreciated.
Jim
I am tying to call BCP to output the contents of a table to a text file from with in a stored procedure.
The procedure will be called from an ASP page ...
My question is were does the file get created ??? I want to create the text file on server ONE and SQL server is running on server TWO and IIS is running on server THREE... do i have to have a drive letter mapped to server ONE and if so is it mapped on the SQl server or the IIS server ....
ie:
exec master..xp_cmdshell bcp db..table out h:est.out -Uxx -Pxx -Sx
thanks for any help on this subject ...
Rob
I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..
a. Error log for failed inserts.
b. A command output file.
Any information would be greatly appreciated.
Jim
I have two questions,
1) Could anyone please point me in the right direction concerning information pertaining to NT Server Enterprise Edition verses NT Server Workstations. We are having problems running SQL Server 7.0 and the Enterprise Edition together on the same machine and was wanting to find information about compatability issues, if there are any,
2) I, on a SQL Server 6.5 database shrunk it by 2 Gb. When I looked to see if SQL Server released those 2 Gb back to the hard drive, I was amazed that it didn't! Did I miss something or will SQL Server 6.5 not release the space because of the initial set-up. And why did SQL Server 6.5 automatically take the space from the Transaction logs when neither of the devices were specified? Is the Transaction Log the default area for shrinkage?
Thanks in Advance!
Daimon Russell
daimon_r@hotmail.com
Hi, does anyone know how to explain how SQL transactions get mapped into TDS and TDS gets mapped into TCP/IP packets?
Or can you please point me in the right direction? I need to figure out how TDS and TCP/IP relate?
We're using replication and are having some latency issues and I'm trying to find out how SQL handles TDS and TCP/IP etc...
Thanks for your time.
1. When we create DTS in SQL Server through DTS designer, where are they stored physically?
2. What would be the best way to modify a DTS without using DTS designer?
3. Is there any other way to create DTS apart from DTS designer and Visual Basic?
4. Is there any website which has detailed information for DTS? (which has more FAQs like above?)
In our production environment, we keep changing the servers frequently, and everytime that happens, I have to change the connection properties in all the DTS going to them one by one.
thanks,
sanjay.
I am not too familiar with SQL Server, but my supervisor gave me the task of finding out the difference between SQL Enterprise and SQL Standard. He also asked me to research the difference between processor licences and client access licences. I will use the Internet as a resource, but I would also like to hear the opinions of someone who uses these programs or is knowledgable about them. So please any suggestions or any useful links would be very helpful.
View 3 Replies View RelatedI have two questions.
1) If a database is suspect we can have that trace from sysdatabases.There is a column named status.My question is in case of suspect datatbase what will be value in the field status of sysdatabases?
2) The password of an user login(created by using sp_addlogin stored procedure or any other way) is stored in the table sysxlogins of master database.The password is stored in a varbinary format.How can I get the actual password(means in a char format)?I mean how can I convert the varbinary value to a readable format?
Hi All,
I am new in SQL SERVER 2000.I have few questions -
1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG?
2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE?
3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP?
4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?
Could I do periodicity backups to another computer(mediaserver) using VDI??
I mean , Could I config a Virtual Device so that I can do backups like disk or tape, I can use
'backup database ...to virtual_device='...' ' to do backup to another computer(mediaserver)?
suppose that I have finished the interface of mediaserver.
If this is impossible,how can I do periodicity backups to another computer??
I have finished a program using VDI that can do backup to another computer,and I know how to do periodicity backups to disk or tape. but I am puzzled about the periodicity backup using VDI.
How should I do?
Thanks,All.
I am putting together a proposal for my church, the current DB software (Access) has been outgrown. One proponent of a no name brand software insists that to implement SQL could take a year and a team of programmers, is this true??
View 3 Replies View RelatedI have to judge the aptitude of a few trainees for SQL DBA training ....
Can the gurus suggest some questions for the same ....
Hi Could any one tell me the answers for these questions.
1. how do we troubleshoot a datbase if it is in suspect mode.What is the reason for a database to be in suspect mode.
2. how can we move a file from C drive to D drive, so that the file location in C drive is completely moved .
3.In a particular primary file group there are many objects.
How can we move some of the objects from this primary file group in to another file group.
4. can we install a sql server on a remote server so that it will not ask any inputs like domain name, authentication modes. .
5.If we want to implement clustering , can we use the virtual ip address on clustering as the ip address of our system or we have to use another ip address for clustering
6. In 2000 we have DTs package.can we run the same DTS package in 2005.
Hi
Im relatively new to ASP/SQL and have been thrown into the deep end by work. Ive got courses to go on, but not for another 3-6months.
Ive got an ASP file calling a database using SQL. Once you hit the "go" button, it puts this data into an Excel file, under a new window (still showing the asp file in the address bar).
It currently shows:
1)
Item numbers that end in "0" i.e. 3.10, 12.20 appear as 3.1 and 12.2 respectively in the Bill of Materials … i.e. being treated as decimals … I need them to show-up as text.
************************************************** *******************
2)
When saving the spreadsheet … it would be good to get "X" to set the default file name to:
<Quote ID>_<Customer>_<Platform>_<version>.[xls|pdf]
************************************************** *******************
3)
When a spreadsheet is displayed, it is in an editable Excel format.
Is it possible to lock the file automatically when its opened or password protect it?
************************************************** *******************
4)
If a spreadsheet window is open and you try and open a new one, the old one pops-up/is still there. Need to be able to close the old one and re-open a new one automatically.
If necessary i will post the code.
Any takers?
Thanks
Hello,
I need help with a few sql questions, but you need to look at picture containing the tables and the relationships between them. How do I post or upload the picture first?
Thanks.
Hi all,I'd like to know if it's possible to sort twice in a same SQL query.I use SQL for retrieving Data into an Excel Spreadsheet.(Excel 2000 or XP) and to paste the queries results into an Excelspreadsheet.1. I tried to sort by date and the only way was to use the serail numberinstead of the litteral nameI tried SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003"but this does not work.I then tried SELECT * FROM [Sheet1$] Where [Date] <= 37822" and itworks.Do you know a way to use 07/20/2003?2. I need to sort my data twice, first by name and second by date. Is itpossible to sort twice in one query?Something like:"SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003 ORDER BY [NAME] ASC,ORDER BY [DATE] DESC"3. Even if the first row of my Excel sheet includes the headers, I cannot perform a query other than SELECT * FROM, for exemple, SELECT [NAME]FROM, does not work. do you know how to do that?Thanks in advance for sharing your experience,Phil*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related1. If the TempDB Database is deleted from MS-SQL Server what willhappen..?2. How to insert a not null column in an existing table withrecords..?3. If a table is deleted, what will happen for the Stored proceduresand Views, which that table reffered..?
View 1 Replies View RelatedHi All,
I was asked couple of questions yesterday and I thought I had to still learn a lot. Questions sound very normal but as for as I am concerned, don't know the answers hence this forum.
Please advise.
Here the questions asked to me
1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem.
2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are,
id, name, description)
id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem?
3) How would you change the identity column value or can we change the identity value and how?
4) How would you use the shared, update, exclusive locks in the sql statement.?
Please let me know.
Regards,
nw
Q1: What is the differences between green and red connections between objects in Data Flow tab in SSIS project?
Q2: In Flat File Connection Manager --> New Button --> header row delimiter --> what is meant by {CR}-{LF}?
Thanks alot,
Bishoy
Good Morning..
i have started developing on DTS recently. and i have a few question about "How-To" issues.
Hope you guys can help
1) I noticed, whenever i want to "redirect row" whenever an error happen, i need to set my AccessMode to OpenRowSet... This mode as i noticed, cant be used when i am tranferring data into a sql table which has primary key set
e.g. Ms Access which has identity -> SQL table with PK set
Is there anyway, to capture the row error in my situation?
2) Another question is, in a data flow... can i have a source which has multiple output arrow.
Sorry for the inconvenince,Thank you in advance