Has SELECT INTO With IDENTITY() Function Changed The Way #'s Are Assigned?
Oct 10, 2007
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
View 4 Replies
ADVERTISEMENT
Apr 6, 2006
Hello,
Is there a way to assign multiple variables to one select statement as in the following example?
DECLARE @FirstName VARCHAR(100)
DECLARE @MiddleName VARCHAR(100)
DECLARE @LastName VARCHAR(100)
@FirstName, @MiddleName, @LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.
Thank you!
Cody
View 1 Replies
View Related
Jun 19, 2001
I have an IDENTITY value in a table that was incorrectly created, and needs to be modified. Can this be accomplished?
Thanks.
View 1 Replies
View Related
Oct 12, 2007
I had an issue that I ran across that I got solved but it was very monotonous. Here is my issue:
I have an SQL function that takes in 2 parameters (lookup category and lookup code) and with those parameters is goes out and find the lookup code description. I have this function in about 40 computed columns. It isn€™t the best way to do things but we need it for some legacy stuff. The problem occurs when I need to make a change to that function. I can€™t because it is being reference by all of the tables. So what I have to do is go in to each of the tables and remove the function from the column formula. Then change the function and then finally add that function back to the column.
Here is what I did.
I went to the syscolumn table to find all of the computer columns. I went to each of those columns and removed the formula and added it to a text document. Then I changed the function. I then wrote a stored procedure that would add the column back. ( I couldn't find a way to use the ALTER COLUMN to add the function so I had to drop and then add the column )
Here are the possible scenarios that I want to see if they are possible.
1) Somehow €œturn off€? the dependency between the function and the column. That way I can just update the function.
OR
2) Programmatically grab the data from the column formula and write that some place and clear out the formula. Then update the function. Then finally take all of the data that was cleared out and write it back to the column.
Does anyone know how to make this easier?
View 3 Replies
View Related
Jun 4, 2012
I am hopelessly lost on a MDX-Statement. We are trying to select the last changed value of a measure within the last 30 days and compare this to the current measure value to see wether this has increased, decreased or not changed at all.
We have basically 2 Dimensions (Timeline and Opportunity) and 1 Fact (Revenue). As already described we now want to evaluated, whether revenue has changed within the last 30 days. The result will be displayed in a SSRS-Report (don't have a problem here).
"All" I need to select via MDX, would be the last changed value and the current value. I tried to use the KPI function, but unfortunately to no avail.
How to do such a select in MDX?
View 1 Replies
View Related
Jun 9, 2015
I have a product table and my task is to select only the products that had been paid for, but later their status has been changed.
I need to report the original paid date, the most recent status, and most recent updated date. Here is the sample table:
CREATE TABLE #Products (primKey int, productId int, productName varchar(100), productStatus varchar(50), logDate datetime )
Insert into #Products(primKey, productId, productName, productStatus, logDate)
Values
(1, 201, 'pen', 'received', '01/01/2011'),
(2, 201, 'pen', 'sold', '01/02/2011'),
(3, 201, 'pen', 'paid', '01/03/2011'),
(4, 201, 'pen', 'returned', '01/04/2011'),
(5, 201, 'pen', 'refurbished', '01/05/2011'),
(6, 202, 'pencil', 'received', '01/06/2011'),
(7, 202, 'pencil', 'sold', '01/07/2011'),
(8, 202, 'pencil', 'paid', '01/08/2011'),
(9, 201, 'pen', 'sold', '01/09/2011'),
(10, 201, 'pen', 'paid', '01/10/2011')
/* temp table records */
Select * From #Products order by productId
/* The desired outcomes would be showing only the Record 3 and 10.
This is a "fake" query to get the results:
*/
Select * From #Products Where primKey in (3, 10) order by productId
View 9 Replies
View Related
May 14, 2004
What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory
I cant seem to get the identity to return to my script.
This is my SP
CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)
AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)
select @@identity
return
GO
I tested in query analyser, and it works fine, so It must be my code. this is my function
Sub Insert_pic(sender as object, e as eventargs)
Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))
SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)
SQLCommander.Commandtype = Commandtype.StoredProcedure
SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)
'// this section not working right, it wont write return id
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue
response.write(SQLCommander.Parameters("ReturnValue").Value)
SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()
End sub
Can anybody see anything I missing? I appreciate any imput
View 4 Replies
View Related
Jun 4, 2008
declare @number int
set @number = 100
SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(int, @number, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
Is there any way i can use variable inside the identity function like in the above example?.
Is there any other alternative?
Thanks in advance
View 6 Replies
View Related
Aug 11, 2006
is it possible to pass a variable to an identity funtion
example
declare @max_note int
select @max_note = max(key ) from notes
select m_key = identity( int, @max_note, 1),
name
into #prod_note
from prod_note
View 3 Replies
View Related
Sep 6, 2007
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
GO
Anyone have any ideas?
Thanks,
Deb
View 5 Replies
View Related
Nov 6, 2000
i am getting an 'Incorrect syntax' error while setting @@identity into a variable
declare @vcount int
set @vcount =Select @@identity
can someone help me?
regards,
View 3 Replies
View Related
Sep 18, 2007
Hi guys,
I've a question about the 'select into' clause when using an identity column with an order by clause.
I've attached a sample of code at the bottom.
I've recently read an article (http://support.microsoft.com/kb/273586) where it states that you can't guarantee the order of the identity columns using this statement, however I'm not sure whether this applies to 2005 or not.
Using this statement, I always get the correct order.
----------
begin
CREATE TABLE #x (FileName varchar(50))
INSERT INTO #x(FileName) SELECT 'E'
INSERT INTO #x(FileName) SELECT 'A'
INSERT INTO #x(FileName) SELECT 'C'
INSERT INTO #x(FileName) SELECT 'B'
INSERT INTO #x(FileName) SELECT 'D'
SELECT * FROM #x
SELECT identity(int,1,1) AS ID, FileName
INTO #y
FROM #x
ORDER BY FileName
SELECT * FROM #y
DROP TABLE #x
DROP TABLE #y
end
----------
Can anyone tell me if it is safe to use rely on the ordering of this statement in 2005, is there an article which discusses this ?
Sean
View 6 Replies
View Related
Jul 20, 2005
I have the following query that has been working so far. I modifiedit slightly to work from another source table (new_products). Now,SQL Server complains that the identity is inherited already:"Cannot add identity column, using the SELECT INTO statement, to table'dbo.my_products', which already has column 'id' that inherits theidentity property."Any suggestions for a work aorund?This is the query:SELECT IDENTITY(INT,1,1) as seq_number, prod_number, prod_nameINTO my_productsFROM ( SELECT prod_number, prod_nameFROM new_productsWHERE ...ORDER BY ...) sub_table
View 1 Replies
View Related
Mar 6, 2008
Hi just a beginner question.. is this the same? SELECT @@IDENTITYandselect scope_identity() as returnidentity"; Thanks!
View 5 Replies
View Related
Apr 6, 2006
I am moving some code that was created in visual studio 2002 into Visual Web Developer 2005 express edition and am getting a warning on some of my code before compilation.
The warning is "Variable lSqlTransaction is used before it has been assigned a value. A null reference exception could result at runtime". I experienced no problems in vs2002.
The offending line is the rollback command within the exception. If I move it out of the exception it no longer flags the warning, if I move it after the throw statement it now longer flags the warning. Any help appreciated.
Heres the code
Public Shared Sub ExecuteNonQuery(ByVal lSQLCommand As SqlCommand)
Dim lSQLConnection As New SqlConnection(SQLConnString(enmSQLDB.enmSQLDB_TPSDB))
Dim lSQLTransaction As SqlTransaction
Try
'-----------------------------------------------------
'Try and open the database
'-----------------------------------------------------
lSQLConnection.Open()
'-----------------------------------------------------
'Create a transaction
'-----------------------------------------------------
lSQLTransaction = lSQLConnection.BeginTransaction
'-----------------------------------------------------
'Assign the connection and transaction to our object
'-----------------------------------------------------
lSQLCommand.Connection = lSQLConnection
lSQLCommand.Transaction = lSQLTransaction
'-----------------------------------------------------
'Execute the stored proc
'-----------------------------------------------------
lSQLCommand.ExecuteNonQuery()
'-----------------------------------------------------
'Use the transaction to commit the changes
'-----------------------------------------------------
lSQLTransaction.Commit()
Catch ex As Exception
'-----------------------------------------------------
'If any errors have been thrown then roll back without
'commiting....
'-----------------------------------------------------
lSQLTransaction.Rollback()
Throw New Exception("An error has occured whilst trying to update the database. " & _
"Your changes have not been saved.", ex)
Finally
If lSQLConnection.State = ConnectionState.Open Then
lSQLConnection.Close()
End If
End Try
End Sub
View 1 Replies
View Related
Nov 20, 2007
I'm tryin to copy information from a fact table from one server to another server but when try using a DTS package to do this, an error pops up that says "the Filegroup <filegroup_name> has no files assigned to it."
what am i missing here?
View 4 Replies
View Related
Feb 14, 2008
Greetings,
We have a standard audit trigger on one of our tables. The trigger type is "after insert, update". The trigger populates four table columns, telling us the login and time the row was created and last updated.
We use replication to synchronize three servers. The trigger specifies "not for replication" on all servers.
The code used to grab the identity and time of the last update is shown below.
LastEditedDate = GETUTCDATE(),
LastEditedBy = SUSER_SNAME()
What I observe is that the LastEditedBy value is sometimes different for the same row on different servers even though the time value is identical. I don't know how to explain this. It looks like the login value is being recalculated during replication while the edit time is not.
For the record, the correct login will be something like "MYDOMjoe" while the incorrect value on another server will read "NT AUTHORITYNETWORK SERVICE". Do these symptoms ring a bell with anyone?
Thanks,
BCB
View 5 Replies
View Related
Apr 23, 2007
I have a problem with datatype mismatches. In my Data Flow I am reading a Flat File that includes a DateOfBirth which SSIS correctly assigns a datatype of [DT_DATE]. I need to check to see if the record has previously been submitted, so I do a Lookup that uses a SQL statement that combines fields (LastName, Zip & BirthDate) from three different tables. My problem is that SSIS assigns a datatype of [DT_DBTIMESTAMP] to BirthDate, which I can't use because of the mismatch. I have tried every form of re-formating the field in SQL that I can think of, but it still gets assigned that DT. And I'm unaware of a way of changing the DT once SSIS assigns it in the Lookup task.
Help!...very frustrated,
Dave
View 3 Replies
View Related
Mar 25, 2008
Hello,
Is it possible to generate a identityfield dynamically upon select, like this:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC
I want the output to be:
1 - Name12 - Name23 - Name3
The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.
Like:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC
I want the output to be:
1 - Name32 - Name23 - Name1
Patrick
View 4 Replies
View Related
Jul 8, 2004
Hi all. Just a quick question for you guys...I did a search on Select @@identity and I'm not sure I understand it. The program I'm migrating over (from sql server to oracle) has "Select @@identity" at the end of some insert statements. I'm just tryin to figure out what this does and what the Oracle equivlant would be? i have read posts(or in this case a blog) like this http://weblog.anthonyeden.com/archives/000054.html that states it gives you the value of the PK.
I've also read this http://www.kamath.com/tutorials/tut007_identity.asp stating select @@identity is handy to use as a "connection specific global variable." This all makes sense, however in the code I am examining select @@identity is not assigned to any variable. Basically, my question is if select @@ identity is not assigned to a variable in your code, what use does putting it at the end of a select statement serve? thank you
View 2 Replies
View Related
May 16, 2007
Hi!
We are porting a database from DB2 to SQL-Server 2005. We use WAS 5.1. We have problems with insert staments followed by an SELECT @@IDENTITY. The sql error is that no result set is returned. The syntax is "<insert statment>;SELECT @@IDENTITY.
We have no problems with this in the 1.1 version (there we have the known "read date" error).
Joachim
View 9 Replies
View Related
Dec 18, 2006
I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4); if (_officeId > 0) { OfficeIdParam.Value = _officeId; } OfficeIdParam.Direction = ParameterDirection.Output; The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId) begin RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist. return -1 end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin
View 1 Replies
View Related
Jan 10, 2007
I have a report where certain columns have values that get repeated, but the client has a rigid requirement for not wanting these columns as groups in the reports (other programs and exports doing specific tasks with those values). In these reports, the "hide duplicates" value is checked, so as to give an aesthetic sense of grouping.
The data for the report, in raw form is:
Value 1
A
Value 1
B
Value 1
C
Value 2
D
Value 2
E
Value 2
F
Value 3
G
Value 3
Hetc...
In its aesthetic form, it is:
Value 1
A
B
C
Value 2
D
E
F
Value 3
G
H
My question is, how can I use RunningValue() to give me the following row numbers based upon those repeated values acting as grouping, since I'm not using formal grouping in the report design?
Value 1
1
A
2
B
3
C
Value 2
1
D
2
E
3
F
Value 3
1
G
2
H
Thanks for any input!
View 3 Replies
View Related
Aug 21, 2007
Hello all,
This seems like a simple question but I still haven't been able to get an answer from Microsoft Canada. We're looking at buying 75 device cals to connect to a single instance of SQL Server Standard Edition. Our question is on how those licenses are assigned and/or activated.
Do we need to manually do something for each station or is it an automated process done the first time a station connects to the server? We also wat to understand what happens when a workstation dies and is replaced by another one. Do you have to do something on the server to transfer the license to the new station ?
In other words, when using device cals (and/or user cals), do you have to do any type of license management or is it all transparent ?
View 1 Replies
View Related
Aug 28, 2007
At our site, we page DBAs whenever an alert with a severity of 20 or greater is raised. This never was an issue until SQL Server 2005. Now every SSPI error is paged since it is a severity 20. I don't believe the errors are severe enough to qualify an assignment of 20. Anybody else have any thoughts on this?
Thanks.
View 4 Replies
View Related
Mar 29, 2007
Hi! I am having a problem getting the inserted id from a table.
Here is the table:
/*********************** Companies **********************************/
CREATE TABLE Companies(
CompanyId int IDENTITY(1,1) PRIMARY KEY,
Description nvarchar(30) NOT NULL
)
GO
When I type SELECT @@IDENTITY in SQL Management Studio after a successful insert, it works fine. However, in code (System.Data.SqlServerCe), I get an error using this code:
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
ret = (int)cmd.ExecuteScalar();
I saw a thread with this solution, but do I really have to go to this trouble?
DECLARE @MyTable table
( MyIdentity int )
INSERT INTO Employees ( FirstName, LastName )
OUTPUT inserted.EmployeeID INTO @MyTable
VALUES ( 'Bill', 'Smith' )
SELECT MyIdentity
FROM @MyTable
scope_identity does not seem to work either. Am I missing something?
Thanks!
Michael
View 11 Replies
View Related
Mar 9, 2008
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:
"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"
Compact 3.5 doesnt like the SELECT statement claiming that:
There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]
Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?
I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View 9 Replies
View Related
Nov 9, 2005
Hi,
In SQL Server Express Edition, what are the rights that are assigned to a Normal Windows User and PowerUser by default ?
When I install SQL Express on a clean machine and login as Power User I can add/edit/delete data but when I login as Normal Windows User I can see the data but not change it.
Please help me in this regard.
View 1 Replies
View Related
May 5, 2008
Hello everyone,
I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches.
So far i have tried:
SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' "
with no success.
Any help is appreciated
View 6 Replies
View Related
Nov 28, 2005
Greetings,If I use a "select into" to clone a table, all attributes are createdcorrectly, however, if I use the same statement across a linked server,my identity column loses its IDENTITY specification.Is this a known issue or basic functionality of using "select into"with linked servers?Kurt
View 2 Replies
View Related
Jun 5, 2007
Does SELECT @@identity works with in SQL Server compact edition? I have tried in SQL management sql query. It does work however when i use in VS.NET 2005 / Orcas beta1 , the generated tableAdapter does not recongize the syntax. So any workaround with it ? or any alternative that i can return the new inserted ID in my application.
Thanks.
View 1 Replies
View Related
Jun 19, 2008
I restored a sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user.
Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio.
$Gorlaz(Restricted User)
View 3 Replies
View Related
May 9, 2008
Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.
-- Create User
use master
go
create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON
use AdventureWorks
go
create user dba from login DBA
alter user dba with DEFAULT_SCHEMA=dbo
go
-- Create Role
use AdventureWorks
go
create role sp_ddl_role AUTHORIZATION dbo
grant CREATE TABLE to sp_ddl_role
-- assign user to Role
use AdventureWorks
go
exec sp_addrolemember sp_ddl_role, dba
go
-- Create Table statement then run with following error
Error:
Msg 2760, Level 16, State 1, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Remedy: Grant ALTER on schema::dbo to sp_ddl_role
My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.
Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA?
View 1 Replies
View Related
