Identity Seed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identity Seed

Author

Topic

cmh
Starting Member

20 Posts

Posted - 2004-09-08 : 15:13:40

What exactly does the identity seed do? I have copied a table to use in another application and the identity seed is 227 in that table. I copied the table and then deleted all but 2 records of data inside the table. When I run my list program I get an error. What is the significance of the 227? A 4 digit primary key is designated for this table also - whether this matters or not I don't know.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:17:02

Identity seed is used to determine what identity value the next row will get when an insert occurs. To change the seed, you can use DBCC CHECKIDENT.

What is the error?

Tara

cmh
Starting Member

20 Posts

Posted - 2004-09-08 : 15:21:04

error '80020009' Exception occurred.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:21:59

There's no more information to that error? What is the query?

Tara

cmh
Starting Member

20 Posts

Posted - 2004-09-08 : 15:36:37

error '80020009'
Exception occurred.

THe following is the asp code I'm using to list the table. The table lists the records in it and then puts the error at the bottom of the page.

<% 'Document Variables
strPageTitle = "Previous Job Postings"
strPageTab = "Previous Job Postings" 'MUST NOT EXCEED 20 CHARACTERS!!!
strSiteOwner = "Connie Kocher"
strSiteOwnerEmail = "ckocher@hendrickson-intl.com" %>



<table width="936" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="middle" background="images/sub_tab_middle.gif"> <table width="95%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="left" valign="bottom"><font size="2" face="Myriad, Arial, Verdana"><img src="images/spacer.gif" width="1" height="19"><%=strPageTab %></font></td>
<td align="right" valign="bottom"><font size="1" face="Myriad, Arial, Verdana">
Last Updated: <% =FormatDateTime(Date(),1) %>
</font></td>
</tr>
</table></td>
</tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="3744" colspan="4"><img src="images/sub_border_top.gif" alt="" width="936" height="14"></td>
</tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td background="images/sub_border_middle.gif"><table width="100%" border="0" cellpadding="0" cellspacing="0" background="images/sortbar.jpg">
<tr>
<td width="85%" align="right" valign="middle"><font color="#FFFFFF" size="3">
<%
Select Case request.querystring("SortMethod")
Case "DateDsc"
Response.Write "<a href=""previousjobpostings.asp?SortMethod=DateAsc""><img src=""images/PrevBulltSortDateDesc.jpg"" border=""0""></a>"
Case "DateAsc"
Response.Write "<a href=""previousjobpostings.asp?SortMethod=DateDsc""><img src=""images/PrevBulltSortDateAsc.jpg"" border=""0""></a>"
Case Else
Response.Write "<a href=""previousjobpostings.asp?SortMethod=DateDsc""><img src=""images/PrevBulltSortDate.jpg"" border=""0""></a>"
End Select %>
<%
Select Case request.querystring("SortMethod")
Case "PointOfContactDsc"
Response.Write "<a href=""previousjobpostings.asp?SortMethod=PointOfContactAsc""><img src=""images/PrevBulltSortPOCDesc.jpg"" border=""0""></a>"
Case "PointOfContactAsc"
Response.Write "<a href=""previousjobpostings.asp?SortMethod=PointOfContactDsc""><img src=""images/PrevBulltSortPOCAsc.jpg"" border=""0""></a>"
Case Else
Response.Write "<a href=""previousjobpostings.asp?SortMethod=PointOfContactDsc""><img src=""images/PrevBulltSortPOC.jpg"" border=""0""></a>"
End Select %>
</font></td>
<td width="15%" valign="middle"> </td>
</tr>
</table></td>
</tr>
<tr>
<td background="images/sub_border_middle.gif"> <table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
<%
Session("ConnInfo") = "dsn=intranet;" & strSQLUser & strSQLPassword & """"
Session("Table") = "IntranetJobPostings"
Set objBulletinsRecordSet = Server.CreateObject("ADODB.Recordset")
objBulletinsRecordSet.ActiveConnection = Session("ConnInfo")
Select Case request.querystring("SortMethod")
Case "DateDsc"
SQL = "Select * FROM IntranetJobPostings order by PubDate DESC; "
Case "DateAsc"
SQL = "Select * FROM IntranetJobPostings order by PubDate; "
Case "PointOfContactAsc"
SQL = "Select * FROM IntranetJobPostings order by PointOfContact; "
Case "PointOfContactDsc"
SQL = "Select * FROM IntranetJobPostings order by PointOfContact DESC; "
Case Else
SQL = "Select * FROM IntranetJobPostings order by PubDate DESC; "
End Select
objBulletinsRecordSet.Source = SQL
objBulletinsRecordSet.CursorType = 0
objBulletinsRecordSet.CursorLocation = 2
objBulletinsRecordSet.LockType = 3
objBulletinsRecordSet.Open
If objBulletinsRecordSet.EOF Then ' Display no results were found
Response.Write "<table width=""94%"" align=""center"" border=""0"" cellspacing=""0"" cellpadding=""0"">"
Response.Write "<tr>"
Response.Write "<td width=""3%"" align=""left"" valign=""middle""><img src=""images/spacer.gif"" width=""19"" height=""15"" border=""0""></td>"
Response.Write "<td width=""97%""><font size=""2"">No job postings found in database. Please contact IT Support.</font></td></tr>"
Response.Write "<tr bgcolor=""#C5C5C5"">"
Response.Write "<td colspan=""2"" align=""left"" valign=""middle""><img src=""images/spacer.gif"" width=""1"" height=""1"" border=""0""></td></tr>"
Else
Response.Write "<table width=""97%"" align=""center"" border=""0"" cellspacing=""0"" cellpadding=""0"">"
intBulletinCounter = 0
While intBulletinCounter < intPreviousBulletinDisplay
Response.Write "<tr><td width=""3%"" align=""left"" valign=""middle"">"
If DateDiff("m", Now, objBulletinsRecordSet.fields.item("PubDate")) = 0 then
Response.Write "<img src=""images/front_bullet_red.gif"" alt=""New new month"" width=""19"" height=""15"" border=""0"">"
Else
Response.Write "<img src=""images/front_bullet_grey.gif"" alt="""" width=""19"" height=""15"" border=""0"">"
End If
Response.Write "</td><td width=""97%""><font size=""2"">"
Response.Write " ["
Select Case request.querystring("SortMethod")
Case "DateDsc"
Response.Write FormatDateTime(objBulletinsRecordSet.Fields.Item("PubDate"),VBShortDate)
Case "DateAsc"
Response.Write FormatDateTime(objBulletinsRecordSet.Fields.Item("PubDate"),VBShortDate)
Case "PointOfContactAsc"
Response.Write objBulletinsRecordSet.Fields.Item("PointOfContact")
Case "PointOfContactDsc"
Response.Write objBulletinsRecordSet.Fields.Item("PointOfContact")
Case Else
Response.Write FormatDateTime(objBulletinsRecordSet.Fields.Item("PubDate"),VBShortDate)
End Select
Response.Write "] "
Response.Write "<a href=""displayjobposting.asp?bulletinid=" & objBulletinsRecordSet.Fields.Item("ID") & """>" & objBulletinsRecordSet.Fields.Item("Title") & "</a></font></td>"
Response.Write "</tr><tr bgcolor=""#C5C5C5"">"
Response.Write "<td colspan=""2"" align=""left"" valign=""middle""><img src=""images/spacer.gif"" alt="""" width=""1"" height=""1"" border=""0""></td></tr>"
objBulletinsRecordSet.movenext
intBulletinCounter = intBulletinCounter + 1
Wend
End If
%>
</table></td>
</tr>
</table>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><img src="images/sub_border_bottom.gif" alt="" width="936" height="13" border="0"></td>
</tr>
</table></td>
</tr>
</table>


</body>
</html>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:38:39

Do the queries work in Query Analyzer? Are you trying to insert a value into an identity column? If so, you need to turn identity insert on for that table. But then that begs the question of why do you want to do that.

Tara

cmh
Starting Member

20 Posts

Posted - 2004-09-08 : 15:44:23

I'm using asp to present the records on the screen. I am not doing any inserts at this point - just listing the contents of a table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:47:56

If you are just selecting data out, then the identity seed isn't relevant. I'd suggest googling your error to find the cause. I typed in "error '80020009' Exception occurred." and got a few hundred hits.

Tara

cmh
Starting Member

20 Posts

Posted - 2004-09-08 : 15:51:29

I tried Google yesterday, but will try again. I'm trying to understand why the other developer started the identity seed at 227 instead of 1. Can you answer that? Thanks for all your time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:54:10

I don't have any idea. Can't you just ask the developer?

Tara

cmh
Starting Member

20 Posts

Posted - 2004-09-08 : 15:58:26

no longer with the company

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 16:00:22

I guess we'll never know then.

Tara

Kristen
Test

22859 Posts

Posted - 2004-09-12 : 04:49:45

Could it be that the first 226 records were deleted? Perhaps they were imported, found to be wrong, deleted and reimported.

I did that recently, deleted the duff data, forgot to do a DBCC CHECKIDENT, and that table now has rows starting from some arbitary number in the 4 million range! Very frustrating ...

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-12 : 07:45:46

"that table now has rows starting from some arbitary number in the 4 million range"

Even though this is understandably annoying it will not generate an exception. "Exception ocurred" is usually not caused by an error thrown by the sql-server but something in the asp. My guess is that it has something to do with the connection-properties you are setting, and more specifically this *could* have something to do with it: objBulletinsRecordSet.ActiveConnection = Session("ConnInfo")

Sessions are killed by default after 20 mins of inactivity so it might be lost when you're doing this, and that would create an exception. Storing connections/connstrings/anything that has to do with connection to the database in a session/application-variable is not recomended.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-12 : 10:41:06

it may be that u need to convert your session object to connection object:
objBulletinsRecordSet.ActiveConnection = (Connection)Session("ConnInfo")

as lumabago pointed out don't store connections in sessions. connection is best stored in a different file which is included into a page:

i use JavaScript, so you'd have to translate this into VbScript

filename = "dbconnect.asp"


<SCRIPT LANGUAGE="JavaScript" RUNAT="server">

function DBConnect()
{
	var conn = "";
	try
	{
		var connString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Username;Password=password;Initial Catalog=DataBaseName;Data Source=ServerName;";
		conn = new ActiveXObject("ADODB.Connection");
		conn.Open( connString );
	} 
	catch (e)
	{  
		Response.write("ERROR:" + e.description + "<br/>");
	}    
	return conn; 
}

function DBDisconnect(conn)
{
	if ((conn) != null)
	{
		if (conn.State == 1) 
		{ 
			conn.close();
		}
		conn = null;
	} 
	return 1;
}
</SCRIPT>

and in your asp file you do:


</HEAD>
<!-- #include file="dbconnect.asp" -->
<BODY>

<%
...
var conn = DBConnect(); 
...
%>

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources