September 11, 2020 at 1:51 pm
Hi,
I recently ran into something really strange, that I would like to just understand how this can work.
I was asked to move some stored procedures and their tables to a server on Azure, no problem.
However about three of them would now go, and when I looked at them I saw why.
The tables had an (now seeded on incremented ) Id field in them, but the procedures did not have this. So the procedure could not be added.
When I added the id field in the Input parameters and the inserts statement of the procedure it then naturally worked.
However, if it is working now, I do not see how, or how they got the procedure into the system without an id field?
I am going to look and try and see if these procedure are being used.
Any ideas please let me know thank you.
An
September 11, 2020 at 7:59 pm
if the ID field was an IDENTITY, you generally don't include it in your INSERT statements as SQL will auto-increment it. If you include a value, you need to SET IDENTITY_INSERT <table name> ON first.
IF the ID field was an IDENTITY type on your on-premise setup but NOT on the Azure, that is likely your problem and would explain why it works locally but not in Azure.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 14, 2020 at 11:30 am
No that is what gets me it is not an IDENTITY field on any of theses tables. so I am not seeing how they were implemented of how they are working now which they are.
Any ideas?
September 14, 2020 at 1:19 pm
Sequence?
September 14, 2020 at 1:55 pm
Sure here is the table and the procedure:
Notice that while the tabel has a id field the procedure does not. I do not see how this si possable or what I should do,
And there is not squenceas as Rick asked.
CREATE TABLE [dbo].[tblAccountSearchHistory](
[id] [int] IDENTITY(1,1) NOT NULL,
[AttemptedSSN] [nchar](50) NULL,
[AttemptedFirstName] [nchar](50) NULL,
[AttemptedLastName] [nchar](50) NULL,
[SearchResult] [nchar](50) NULL,
[Date] [datetime] NULL,
[IpAddress] [nchar](30) NULL,
[BrowserType] [nchar](100) NULL,
[BrowserName] [nchar](100) NULL,
[BrowserVersion] [nchar](100) NULL,
[UserAgentString] [nchar](200) NULL,
CONSTRAINT [PK_tblAccountSearchHistory] PRIMARY KEY CLUSTERED
(
[id] 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
ALTER Procedure [dbo].[spWriteToAccountSearchHistory]
@AttemptedSSN nvarchar(50),
@AttemptedFirstName nvarchar(50),
@AttemptedLastName nvarchar(50),
@SearchResult nvarchar(50),
@Date datetime,
@IpAddress nvarchar(30),
@BrowserType nvarchar(100),
@BrowserName nvarchar(100),
@BrowserVersion nvarchar(100),
@UserAgentString nvarchar(200)
as
Begin
Insert into tblAccountSearchHistory values (@AttemptedSSN, @AttemptedFirstName, @AttemptedLastName, @SearchResult, @Date, @IpAddress, @BrowserType, @BrowserName, @BrowserVersion, @UserAgentString)
End
Thank you
September 14, 2020 at 2:07 pm
it appears you were wrong in this post
No that is what gets me it is not an IDENTITY field on any of theses tables. so I am not seeing how they were implemented of how they are working now which they are.
Any ideas?
Note the first column in your table:
CREATE TABLE [dbo].[tblAccountSearchHistory](
[id] [int] IDENTITY(1,1) NOT NULL,
...
Notice the keyword IDENTITY. There was a identity in the table.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 14, 2020 at 2:18 pm
Hi,
I am sorry. I went back to the tables and realized that I was looking at the wrong tables. this makes sense now.
Sorry to have bothered you guys with this.
Thank you for pointing it out and your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply