IDENTITY() not supported

Nov 5, 2009 at 6:10 PM

Hi,

Im using codesmith and nettiers template to generated my database stored procedures and I keep getting this error when running the SQLAzureMW on all the Insert statements

IDENTITY() is not supported in current version of SQL Azure

Was wondering if you could help me put a fix in the NotSupportedByAzureFile.config.

thanks,

chad

Coordinator
Nov 5, 2009 at 7:28 PM

Hi Chad,

Can you send me the TSQL that SQLAzureMW is questioning?  If you are using an IDENTITY function in a select statement, it is not supported.  I believe that to use an identity function you have to have it in an SELECT INTO type statement as well and SELECT INTO is also not supported.  If you have some TSQL that does work in SQL Azure that says otherwise, please send it to me.

If you want to take out the check for IDENTITY, go to line 281 in the config file and remove IDENTITY.  It will look something like this:

<NotSupported Text="(ORIGINAL_LOGIN|HOST_(ID|NAME))" ReplaceWith="" SeverityLevel="2" ReplaceString="false" DisplayWarning="true" DefaultMessage="true" WarningMessage="" />

 That will get rid of the check for Identity.  You also might want to change line 39 to be:

  <NotSupported Text="SELECT[\w\W]*INTO" ReplaceWith="" SeverityLevel="2" ReplaceString="false" DisplayWarning="true" DefaultMessage="false" WarningMessage="SELECT INTO statement is not supported in this version of SQL Azure." />

This changes the statement to look for any select into statement (and it will catch any IDENTITY issue since it is requited to be used in a select into statement).

Anyway, if you have TSQL that works, but SQLAzureMW says otherwise, please send it to me!

Thanks,
George

Nov 5, 2009 at 8:23 PM
Below is the stored proc that nettiers generates for me. I think that the Scope_Identity will work but don't know why its pulling this up
--~ StoredProcedure [dbo].[ugp_Address_Insert] -- IDENTITY() is not supported in current version of SQL Azure

CREATE PROCEDURE dbo.ugp_Address_Insert
(

	@ID int    OUTPUT,
	@Street nvarchar (200)  ,
	@City nvarchar (50)  ,
	@StateID int   ,
	@ZipCode nvarchar (10)  ,
	@CreateDate datetime   ,
	@CreateUser int   ,
	@LastUpdatedDate datetime   ,
	@LastUpdatedUser int   ,
	@Suite nvarchar (50)  
)
AS
			
	INSERT INTO [dbo].[Address]
	(
                   [Street]
		,[City]
		,[StateID]
		,[ZipCode]
		,[CreateDate]
		,[CreateUser]
		,[LastUpdatedDate]
		,[LastUpdatedUser]
		,[Suite]
	)
	VALUES
	(
		@Street
		,@City
		,@StateID
		,@ZipCode
		,@CreateDate
		,@CreateUser
		,@LastUpdatedDate
		,@LastUpdatedUser
		,@Suite
	)
		-- Get the identity value
		SET @ID = SCOPE_IDENTITY()
	 
Im also experiencing this error witht he aspnet Security stored proc
StoredProcedure [dbo].[aspnet_PersonalizationAdministration_ResetUserState] -- setUser is not supported in current version of SQL Azure
thanks,
chad

Nov 5, 2009 at 9:01 PM

I think I figured out why the IDENTITY and setUser errors occured. I don't believe the string lookup on these words are looking for the words by themselves. I think it is looking at any instance no matter if its apart of another word or not.

Coordinator
Nov 6, 2009 at 1:36 AM

Hi Chad,

If you make the changes above (line 39 and 281) and make this change to line 210:

<NotSupported Text="(IS_SRVROLEMEMBER|(^|\n)\s*SETUSER|SYSTEM_USER|suser_id\s?\(['\w\s]*\))" ReplaceWith="" SeverityLevel="2" ReplaceString="false" DisplayWarning="true" DefaultMessage="true" WarningMessage="" />

Basically, I am telling Regex to look for SETUSER at the beginning of the line.  If you can, can you make these changes and then test again?  If you still get any warnings, please send me the TSQL.

Thanks!
George

 

Coordinator
Nov 7, 2009 at 7:58 PM

V1.9.1 has the modifications to NotSupportedByAzure.config that we talked about above.

Let me know if you have any more issues.

Regards,

George