Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot insert the value NULL into column

Posted on 2011-03-19
13
Medium Priority
?
4,444 Views
Last Modified: 2013-11-05
I recently moved to a new hosting company.  When I moved my SQL database, I signed into the old DB and copied everything into the new database.

I tried today to enter a category, I received this error:
Microsoft OLE DB Provider for SQL Server error '80040e2f'
Cannot insert the value NULL into column 'catID', table 'merchres.dbo.gportal_Categories'; column does not allow nulls. INSERT fails.
/admin/categories_Add.asp, line 82
if eerr = 0 then
				Set Con = Server.CreateObject ("ADODB.Connection")
				Con.Open strCon

				sql = "insert into gportal_Categories (catName, parentID, catNavi, catTitle, metaTags) VALUES ("
				sql = sql & "'"& catName &"', "& catID &", '"& navi &"', '"& catTitle &"', '"& metaTags &"')"
				Con.execute(sql)
				set r = Con.execute("SELECT @@IDENTITY from gportal_Categories")				
				new_catID = r(0)
				urlrewrite_cat new_catID, catName, ""
				Do UNTIL catID = 0
				set rec = Con.execute("SELECT catName, parentID FROM gportal_Categories WHERE catID =" & catID &";")
				Con.Execute ("UPDATE gportal_Categories SET gportal_Categories.cCats = gportal_Categories.cCats + 1 WHERE catID = " & catID &";")
				navi = Rec("catName") & "/"& navi
				catID = rec("parentID")
				rec.Close
				LOOP
				Con.Execute ("UPDATE gportal_Categories SET catNavi = '"& navi &"' WHERE catID = " & new_catID &";")		
				response.Write("<table align=""center"" cellpadding=""0"" cellspacing=""1"" bgcolor=""#333333"">")
				response.Write("<tr>")
				response.Write("<td style=""background-color:#ffffff; padding:10px"">")				
				response.Write("Category has been created ...<br/>")
				response.Write("<a href=""categories.asp?catID="& new_CatID &""">"& catName &"</a>")
				response.Write("</td></tr></table>")
				end if
				end if
			%><br>			<br>

Open in new window

Line 82 is
Con.execute(sql)

Open in new window


I then tried to enter a listing and received:
Microsoft OLE DB Provider for SQL Server error '80040e2f'
Cannot insert the value NULL into column 'linkID', table 'merchres.dbo.gportal_Links'; column does not allow nulls. INSERT fails.
/admin/AddListing.asp, line 100
Set Con = Server.CreateObject ("ADODB.Connection")
					Con.Open strCon
					sql = "insert into gportal_Links (linkName, linkDesc, linkUrl, catID, submitDate, active, contactMail, hotLink) VALUES "
					sql = sql & "('"& linkName &"', '"& linkDesc &"', '"& linkUrl &"', "& catID &", "
					if strDB = "SQL" then
					sql = sql & "getDate(), "
					else
					sql = sql & "Date(), "
					end if					
					sql = sql & " "& statu &", '"& linkEmail &"' , "& hotLink &")"
					Con.execute(sql)
					set r = Con.execute("SELECT @@IDENTITY from gportal_Links")
					linkID = r(0)
					urlrewrite_link linkID, catID
					Do UNTIL catID = 0
					set rec = Con.execute("SELECT catName, parentID FROM gportal_Categories WHERE catID =" & catID &";")
					Con.Execute ("UPDATE gportal_Categories SET gportal_Categories.cLinks = gportal_Categories.cLinks + 1 WHERE catID = " & catID &";")
					navi = Rec("catName") & "/"& navi
					catID = rec("parentID")
					rec.Close
					LOOP

				response.Write("<br/><table align=""center"" width=""290"" cellpadding=""0"" cellspacing=""1"" bgcolor=""#333333"">")
				response.Write("<tr>")
				response.Write("<td style=""background-color:#ffffff; padding:10px"">")				
				response.Write("Listing has been updated ...<br/>")
				response.Write("<a href=""dir.asp?catID="& scatID &""">Display Category</a> | ")
				response.Write("<a href=""listing_info.asp?linkID="& linkID &""">Display Listing</a>")
				response.Write("</td></tr></table><br/><br/>")


					
					else
			%>

Open in new window

Line 100 is
Con.execute(sql)

Open in new window

I don't know if I can run the script to help "fix" the problem without erasing my data?  

The complete Addlisting.asp (is 8053061) and categories_Add.asp is 8053062.

Thank you!
<!--#include file="config.asp"-->
<!--#include file="webdir_functions.asp"-->
<!--#include file="urlrewrite.asp"-->
<%

	pLevel = int(Session("site_level"))
	if (pLevel = 0) or (pLevel = 3) then
		response.Redirect("no_auth.asp")
	end if
%><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Web Directory &amp; News Portal - Administrative Backend</title>
<link href="styles.css" rel="stylesheet" type="text/css">

<SCRIPT language=javascript>
function checkform() {

    var message = ""; // create error message string variable, with nothing in it

    if (document.newForm.catName.value.length == 0) {
      message = message + "- Category Name is Null.\n\n";
      }

    if ( message.length > 0 ) { // is there an error message?
        message = "Please, fill the form completely :\n\n" + message ;
        alert( message ); // display error message
      return false; // return bad, not ok to process
      }
    /* else {
      alert( "Your New Category Published." );
      return true; // no error message to display, return ok to process

      }*/

    } // end of the function checkform()

    //end hiding of JavaScript code -->
  </SCRIPT>
</head>
<body>
<center>
<table width="90%" border="0" cellpadding="0" cellspacing="2">
<tr>
<td width="100%" valign="top" bgcolor="#FFFFFF">
<!-- ALL CONTENT -->
<!--#include file="topmenu.asp">-->
	<table width="100%" cellpadding="0" cellspacing="0">
		<tr>
			<td  align="left" class="menuAlt">
			<a href="categories.asp">Manage Categories</a> &nbsp;|&nbsp;
			<a href="AddListing.asp">Add Listing</a> &nbsp;|&nbsp;
			<a href="ApproveSubmission.asp">Approve Submission</a> &nbsp;|&nbsp;
			<a href="ApproveReviews.asp">Approve Reviews</a> &nbsp;|&nbsp;
			<a href="Re-BuildNavigation.asp">Re-Build Navigation</a> &nbsp;|&nbsp;
			<a href="RecalculateSystem.asp">Recalculate System</a>
			</td>		
		</tr>
	</table>
	<br/>
			<%
				if request.form("func") = "Add New Category" then
				catID = int(request.form("catID"))
				catName = server.htmlencode(request.form("catName"))
				catTitle = server.htmlencode(request.form("catTitle"))
				catName = replace(catName, "'", "''")	
				catTitle = replace(catTitle, "'", "''")	
				navi = catName	

							eerr = 0
				if len(catname) > 200 or len(catname) < 3 then
					eerr = eerr + 1
					response.write "Category name can be shorter than 5 char. and longer than 200 char."
				end if
				
				if eerr = 0 then
				Set Con = Server.CreateObject ("ADODB.Connection")
				Con.Open strCon

				sql = "insert into gportal_Categories (catName, parentID, catNavi, catTitle, metaTags) VALUES ("
				sql = sql & "'"& catName &"', "& catID &", '"& navi &"', '"& catTitle &"', '"& metaTags &"')"
				Con.execute(sql)
				set r = Con.execute("SELECT @@IDENTITY from gportal_Categories")				
				new_catID = r(0)
				urlrewrite_cat new_catID, catName, ""
				Do UNTIL catID = 0
				set rec = Con.execute("SELECT catName, parentID FROM gportal_Categories WHERE catID =" & catID &";")
				Con.Execute ("UPDATE gportal_Categories SET gportal_Categories.cCats = gportal_Categories.cCats + 1 WHERE catID = " & catID &";")
				navi = Rec("catName") & "/"& navi
				catID = rec("parentID")
				rec.Close
				LOOP
				Con.Execute ("UPDATE gportal_Categories SET catNavi = '"& navi &"' WHERE catID = " & new_catID &";")		
				response.Write("<table align=""center"" cellpadding=""0"" cellspacing=""1"" bgcolor=""#333333"">")
				response.Write("<tr>")
				response.Write("<td style=""background-color:#ffffff; padding:10px"">")				
				response.Write("Category has been created ...<br/>")
				response.Write("<a href=""categories.asp?catID="& new_CatID &""">"& catName &"</a>")
				response.Write("</td></tr></table>")
				end if
				end if
			%><br>			<br>
<center>
<form method="POST" action="categories_Add.asp" name="newForm" onSubmit="return checkform()" >
    <center>
	<table width="80%" cellpadding="0" cellspacing="1" bgcolor="#222222">
		<tr>
		<td bgcolor="#66cc66" style="padding:3px; font-size:13px;"><b>Add New Category : </b></td>
		</tr>
	</table>

	<table width="80%" cellpadding="0" cellspacing="1" bgcolor="#222222">
	<tr>
        <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Category</b>
          :</td>
        <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
<%	
		catID = int(request.QueryString("catID"))
		if catID > 0 then
		Set Con = Server.CreateObject ("ADODB.Connection")
		Con.Open strCon
		sql = "Select catID, catName, catNavi from gportal_Categories where catID = "& catID
		sql = sql & " order by catNavi asc"
		Set objRec = Con.execute(sql)
		
		response.Write("<input type=""hidden"" name=""catID"" value="""& objRec("catID") & """>")
		response.write "[ "& objRec("catNavi") & " ]" & vbCrlf
		objRec.Close
		Con.Close
		Set objRec = nothing
		Set Con = nothing
		else
		response.Write("<input type=""hidden"" name=""catID"" value=""0"">")
		response.write "[Directory Home]" & vbCrlf
		
		end if		
%>                </td>
      </tr>
  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Category Name :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
&nbsp;<input type="text" name="catName" size="45">                  
       </td>
  </tr>
  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Page Title :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
&nbsp;<input type="text" name="catTitle" size="45">                  
       </td>
  </tr>
  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Meta-Tags :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
&nbsp;<textarea rows="4" name="metaTags" style="width:96%"></textarea>                  
    
    </td>
  </tr>
  <tr>
    <td width="80%" bgcolor="#d5eadb"  colspan="2"><br>
      <p align="center"><input type="submit" value="Add New Category" name="func"></p>
            <br>
      </td>
  </tr>
 </table>
 <p>&nbsp;</p>
</form>
</center>
<br/>
<table width="100%" border="0" cellpadding="0" cellspacing="0" >
<tr>
	<td align="center" class="menu" style="padding:8px">
	
</td></tr></table>
			
			</td></tr></table></center>

</body>
</html>

Open in new window

<!--#include file="config.asp"-->
<!--#include file="webdir_functions.asp"-->
<!--#include file="urlrewrite.asp"-->

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Web Directory &amp; News Portal - Administrative Backend</title>
<link href="styles.css" rel="stylesheet" type="text/css">
<SCRIPT language=javascript>
       function checkform() {

    var message = ""; // create error message string variable, with nothing in it


    if (document.newForm.catID.value.length == 0) {
      message = message + "- You did not select a category.\n\n";
      }

    if (document.newForm.linkName.value.length == 0) {
      message = message + "- Link Name is Null.\n\n";
      }

    if (document.newForm.linkUrl.value.length == 0) {
      message = message + "- Url field is Null.\n\n";
      }

    if (document.newForm.linkEmail.value.length == 0) {
      message = message + "- Email field is Null.\n\n";
      }

    if (document.newForm.linkDesc.value.length == 0) {
      message = message + "- Description field is Null.\n\n";
      }

    if (document.newForm.linkDesc.value.length > 850) {
      message = message + "- Description field is can not be longer than 250 chars.\n\n";
      }


    if ( message.length > 0 ) { // is there an error message?
        message = "Please, fill the form completely :\n\n" + message ;
        alert( message ); // display error message
      return false; // return bad, not ok to process
      }
    /* else {
      alert( "Your New Category Published." );
      return true; // no error message to display, return ok to process

      }*/

    } // end of the function checkform()

    //end hiding of JavaScript code -->
  </SCRIPT>
</head>
<body>

<center>
<table width="90%" border="0" cellpadding="0" cellspacing="2">
<tr>
<td width="100%" valign="top" bgcolor="#FFFFFF">
<!-- ALL CONTENT -->
<!--#include file="topmenu.asp">-->

	<table width="100%" cellpadding="0" cellspacing="0">
		<tr>
			<td  align="left" class="menuAlt">
			<a href="categories.asp">Manage Categories</a> &nbsp;|&nbsp;
			<a href="AddListing.asp">Add Listing</a> &nbsp;|&nbsp;
			<a href="ApproveSubmission.asp">Approve Submission</a> &nbsp;|&nbsp;
			<a href="ApproveReviews.asp">Approve Reviews</a> &nbsp;|&nbsp;
			<a href="Re-BuildNavigation.asp">Re-Build Navigation</a> &nbsp;|&nbsp;
			<a href="RecalculateSystem.asp">Recalculate System</a>
			</td>		
		</tr>
	</table>
	<br/>
			<%
			if request.Form("func") = "Add Listing" then
					catID = int(request.Form("catID"))	
					scatID = int(request.Form("catID"))	
					linkName = replace(trim(request.Form("linkName")), "'", "''")
					linkDesc = replace(trim(request.Form("linkDesc")), "'", "''")
					linkUrl = replace(trim(request.Form("linkUrl")), "'", "''")
					linkEmail = replace(trim(request.Form("linkEmail")), "'", "''")
					statu = int(request.Form("statu"))
					hotLink = int(request.Form("hotLink"))
					
					Set Con = Server.CreateObject ("ADODB.Connection")
					Con.Open strCon
					sql = "insert into gportal_Links (linkName, linkDesc, linkUrl, catID, submitDate, active, contactMail, hotLink) VALUES "
					sql = sql & "('"& linkName &"', '"& linkDesc &"', '"& linkUrl &"', "& catID &", "
					if strDB = "SQL" then
					sql = sql & "getDate(), "
					else
					sql = sql & "Date(), "
					end if					
					sql = sql & " "& statu &", '"& linkEmail &"' , "& hotLink &")"
					Con.execute(sql)
					set r = Con.execute("SELECT @@IDENTITY from gportal_Links")
					linkID = r(0)
					urlrewrite_link linkID, catID
					Do UNTIL catID = 0
					set rec = Con.execute("SELECT catName, parentID FROM gportal_Categories WHERE catID =" & catID &";")
					Con.Execute ("UPDATE gportal_Categories SET gportal_Categories.cLinks = gportal_Categories.cLinks + 1 WHERE catID = " & catID &";")
					navi = Rec("catName") & "/"& navi
					catID = rec("parentID")
					rec.Close
					LOOP

				response.Write("<br/><table align=""center"" width=""290"" cellpadding=""0"" cellspacing=""1"" bgcolor=""#333333"">")
				response.Write("<tr>")
				response.Write("<td style=""background-color:#ffffff; padding:10px"">")				
				response.Write("Listing has been updated ...<br/>")
				response.Write("<a href=""dir.asp?catID="& scatID &""">Display Category</a> | ")
				response.Write("<a href=""listing_info.asp?linkID="& linkID &""">Display Listing</a>")
				response.Write("</td></tr></table><br/><br/>")


					
					else
			%>
			<br>			<br>			
    <center> 
	<form method="POST" action="AddListing.asp" name="newForm" onSubmit="return checkform()"> 
	<table width="90%" cellpadding="0" cellspacing="1" bgcolor="#222222">
		<tr>
		<td bgcolor="#92bb87" style="padding:3px; font-size:13px;"><b>Add Listing : </b></td>
		</tr>
	</table>

	<table width="90%" cellpadding="0" cellspacing="1" bgcolor="#222222">
	<tr>
        <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Category</b>
          :</td>
        <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
		<%	
			if int(request.QueryString("catID")) = 0 then
			Set objCon = Server.CreateObject ("ADODB.Connection")
			objCon.Open strCon
			Set objRec = objCon.execute("Select catID, catName, catNavi from gportal_Categories order by catNavi asc")
			response.write "&nbsp;<SELECT name=catID size=""1""> "
			response.write "<option>Choose Category</option>"
			do while not objRec.eof
			response.write "<option value="& objRec("catID") &">"& objRec("catNavi") & "</option>" & vbCrlf
			objRec.Movenext
			loop
			response.write "</select>"
			objRec.Close
			objCon.Close
			Set objRec = nothing
			Set objCon = nothing		
			else
		Set Con = Server.CreateObject ("ADODB.Connection")
		Con.Open strCon
		sql = "Select catID, catName, catNavi from gportal_Categories where catID = "& int(request.QueryString("catID"))
		sql = sql & " order by catNavi asc"
		Set objRec = Con.execute(sql)
		
		response.Write("<input type=""hidden"" name=""catID"" value="""& objRec("catID") & """>")
		response.write "&nbsp;[ "& objRec("catNavi") & " ]" & vbCrlf
		objRec.Close
		Con.Close
		Set objRec = nothing
		Set Con = nothing

			end if
		%>        
		</td></tr>
  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Listing Name :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
			&nbsp;<input type="text" name="linkName" size="45" >                 
       </td>
  </tr>
  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Description :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
			&nbsp;<textarea rows="4" cols="80" name="linkDesc"></textarea>
       </td>
  </tr>
  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Url :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
			&nbsp;<input type="text" name="linkUrl" size="35" >                 
       </td>
  </tr>
    <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Email :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
			&nbsp;<input type="text" name="linkEmail" size="25" >                 
       </td>
  </tr>
  
  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Statu :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
			&nbsp;<input type="radio" name="active" value="0" checked="checked">Live
			&nbsp;<input type="radio" name="active" value="1">Pending
       </td>
  </tr>

  <tr>
    <td bgcolor="#a4d1ed" style="padding:3px; padding-bottom:4px; padding-top:4px">&nbsp;<b>Sponsor :</b></td>
    <td bgcolor="#d5eadb" style="padding:3px; padding-bottom:4px; padding-top:4px">
			&nbsp;<input type="radio" name="hotLink" value="1">True
			&nbsp;<input type="radio" name="hotLink" value="0" checked="checked">False
       </td>
  </tr>

    <tr>
    <td width="80%" bgcolor="#92bb87"  colspan="2"><br>
      <p align="center"><input type="submit" value="Add Listing" name="func"></p>
            <br>
      </td>
  </tr>

		</table><% end if %>
		<br/>
<table width="100%" border="0" cellpadding="0" cellspacing="0" >
<tr>
	<td align="center" class="menu" style="padding:8px">
	
</td></tr></table>
			
			</td></tr></table></center>

</body>
</html>

Open in new window

0
Comment
Question by:coreybryant
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 2
13 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35172245
>>  I signed into the old DB and copied everything into the new database.

Did you copy everything exactly? It sounds like you've inadvertently made a column "not null".
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35172661
I might have.  I might still be able to access the old DB.  

If I sign into the new server with the SQL Server Management Studio and the the SQL script (attached), will this erase my data?  

I tried to export the data - one option using a Flat File and it seemed to allow me to use one table (Articles) but that errored.  

I also have SQLBackupandFTP if that matters.

Thanks!
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_Links]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_Links](
	[linkID] [int] IDENTITY(1,1) NOT NULL,
	[linkName] [nvarchar](250) NOT NULL,
	[linkDesc] [nvarchar](650) NOT NULL,
	[linkUrl] [nvarchar](350) NOT NULL,
	[catID] [int] NULL CONSTRAINT [DF_gportal_Links_catID]  DEFAULT ((0)),
	[submitDate] [datetime] NULL,
	[active] [int] NULL CONSTRAINT [DF_gportal_Links_active]  DEFAULT ((0)),
	[contactMail] [nvarchar](250) NULL,
	[hotLink] [int] NULL CONSTRAINT [DF_gportal_Links_hotLink]  DEFAULT ((0)),
	[cVotes] [int] NULL CONSTRAINT [DF_gportal_Links_cVotes]  DEFAULT ((0)),
	[rating] [int] NULL CONSTRAINT [DF_gportal_Links_rating]  DEFAULT ((0)),
	[todayHits] [int] NULL CONSTRAINT [DF_gportal_Links_todayHits]  DEFAULT ((0)),
	[totalHits] [int] NULL CONSTRAINT [DF_gportal_Links_totalHits]  DEFAULT ((0)),
	[lastAccess] [datetime] NULL,
 CONSTRAINT [PK_gportal_Links] PRIMARY KEY CLUSTERED 
(
	[linkID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_MailingList]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_MailingList](
	[emailID] [int] IDENTITY(1,1) NOT NULL,
	[email] [nvarchar](250) NOT NULL,
	[active] [int] NULL CONSTRAINT [DF_gportal_MailingList_active]  DEFAULT ((1)),
 CONSTRAINT [PK_gportal_MailingList] PRIMARY KEY CLUSTERED 
(
	[emailID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_MailTemplates]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_MailTemplates](
	[tempID] [int] IDENTITY(1,1) NOT NULL,
	[subject] [nvarchar](300) NOT NULL,
	[email] [ntext] NOT NULL,
	[createDate] [datetime] NULL,
	[send] [int] NULL CONSTRAINT [DF_gportal_MailTemplates_send]  DEFAULT ((0)),
 CONSTRAINT [PK_gportal_MailTemplates] PRIMARY KEY CLUSTERED 
(
	[tempID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_Reviews]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_Reviews](
	[reviewID] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](250) NOT NULL,
	[email] [nvarchar](250) NOT NULL,
	[subject] [nvarchar](250) NOT NULL,
	[postDate] [datetime] NULL,
	[review] [nvarchar](450) NOT NULL,
	[approve] [int] NULL CONSTRAINT [DF_gportal_Reviews_approve]  DEFAULT ((0)),
	[rating] [int] NULL CONSTRAINT [DF_gportal_Reviews_rating]  DEFAULT ((0)),
	[linkID] [int] NULL CONSTRAINT [DF_gportal_Reviews_linkID]  DEFAULT ((0)),
 CONSTRAINT [PK_gportal_Reviews] PRIMARY KEY CLUSTERED 
(
	[reviewID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_Settings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_Settings](
	[settingID] [int] IDENTITY(1,1) NOT NULL,
	[appUrl] [nvarchar](250) NOT NULL,
	[appName] [nvarchar](200) NOT NULL,
	[email] [nvarchar](250) NULL,
	[mailComponent] [nvarchar](250) NULL,
	[mailServer] [nvarchar](250) NOT NULL,
	[templateID] [int] NULL CONSTRAINT [DF_gportal_Settings_templateID]  DEFAULT ((0)),
 CONSTRAINT [PK_gportal_Settings] PRIMARY KEY CLUSTERED 
(
	[settingID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_Topics]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_Topics](
	[topicID] [int] IDENTITY(1,1) NOT NULL,
	[topicName] [nvarchar](250) NOT NULL,
	[orderView] [int] NULL CONSTRAINT [DF_gportal_Topics_orderView]  DEFAULT ((0)),
	[parentID] [int] NULL CONSTRAINT [DF_gportal_Topics_parentID]  DEFAULT ((0)),
	[topicDesc] [nvarchar](250) NULL,
	[navi] [nvarchar](450) NULL,
 CONSTRAINT [PK_gportal_Topics] PRIMARY KEY CLUSTERED 
(
	[topicID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_UserList]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_UserList](
	[userID] [int] IDENTITY(1,1) NOT NULL,
	[pword] [nvarchar](50) NULL,
	[passLevel] [int] NOT NULL CONSTRAINT [DF_gportal_UserList_passLevel]  DEFAULT ((1)),
	[email] [nvarchar](250) NOT NULL,
	[realName] [nvarchar](250) NULL,
 CONSTRAINT [PK_gportal_UserList] PRIMARY KEY CLUSTERED 
(
	[userID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_Articles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_Articles](
	[articleID] [int] IDENTITY(1,1) NOT NULL,
	[headline] [nvarchar](250) NOT NULL,
	[editorID] [int] NOT NULL,
	[articleDate] [datetime] NOT NULL,
	[endDate] [datetime] NULL,
	[summary] [ntext] NOT NULL,
	[fullStory] [ntext] NOT NULL,
	[overAllHit] [int] NULL CONSTRAINT [DF_gportal_Articles_overAllHit]  DEFAULT ((0)),
	[dailyHit] [int] NULL CONSTRAINT [DF_gportal_Articles_dailyHit]  DEFAULT ((0)),
	[lastAccess] [datetime] NULL,
	[catID] [int] NULL CONSTRAINT [DF_gportal_Articles_catID]  DEFAULT ((0)),
	[statu] [int] NULL CONSTRAINT [DF_gportal_Articles_statu]  DEFAULT ((0)),
	[flashNews] [int] NULL CONSTRAINT [DF_gportal_Articles_flashNews]  DEFAULT ((0)),
	[extUrl] [nvarchar](50) NULL,
 CONSTRAINT [PK_gportal_Articles] PRIMARY KEY CLUSTERED 
(
	[articleID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_Stats]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_Stats](
	[hit] [int] NULL,
	[statDate] [smalldatetime] NULL
) ON [PRIMARY]
END
 
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gportal_Categories]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[gportal_Categories](
	[catID] [int] IDENTITY(1,1) NOT NULL,
	[catName] [nvarchar](250) NOT NULL,
	[parentID] [int] NULL CONSTRAINT [DF_gportal_Categories_parentID]  DEFAULT ((0)),
	[cLinks] [int] NULL CONSTRAINT [DF_gportal_Categories_cLinks]  DEFAULT ((0)),
	[cCats] [int] NULL CONSTRAINT [DF_gportal_Categories_cCats]  DEFAULT ((0)),
	[lastLink] [datetime] NULL,
	[hits] [int] NULL CONSTRAINT [DF_gportal_Categories_hits]  DEFAULT ((0)),
	[catNavi] [nvarchar](650) NULL,
	[catTitle] [nvarchar](250) NULL,
	[metaTags] [nvarchar](850) NULL,
	[dirnavi] [nvarchar](250) NULL,
 CONSTRAINT [PK_gportal_Categories] PRIMARY KEY CLUSTERED 
(
	[catID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

Open in new window

0
 
LVL 29

Author Comment

by:coreybryant
ID: 35172686
It looks like I might be able to connect to the old DB actually.  What can I do to make sure everything gets copied over?

I think I copied everything every thing last but I am not certain.  

Thanks again!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Author Comment

by:coreybryant
ID: 35172793
Sorry - one other thing, since I the ability to create a new database, would that be the best and the copy the from the old DB to the new DB?
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35172810
(Sorry getting ahead of myself, I meant to say would it be better to import the data to the new database, or export the DB from the old to the new)
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35172916
I tried to export the data
 Operation SroppedWhen I clicked on messages, this was the error:
- Validating (Error)
Messages
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "articleID".
 (SQL Server Import and Export Wizard)
 Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
 (SQL Server Import and Export Wizard)
 Error 0xc004706b: Data Flow Task 1: "component "Destination - gportal_Articles" (58)" failed validation and returned validation status "VS_ISBROKEN".
 (SQL Server Import and Export Wizard)
 Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
 (SQL Server Import and Export Wizard)
 Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
 (SQL Server Import and Export Wizard)

Some of the other information:
Source Location : old DB
Source Provider : SQLNCLI10
Destination Location : new db
Destination Provider : SQLNCLI10

Copy rows from [dbo].[gportal_Articles] to [dbo].[gportal_Articles]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_Categories] to [dbo].[gportal_Categories]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_Links] to [dbo].[gportal_Links]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_MailingList] to [dbo].[gportal_MailingList]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_MailTemplates] to [dbo].[gportal_MailTemplates]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_Reviews] to [dbo].[gportal_Reviews]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_Settings] to [dbo].[gportal_Settings]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_Stats] to [dbo].[gportal_Stats]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_Topics] to [dbo].[gportal_Topics]
The new rows will be appended to the existing table.
Copy rows from [dbo].[gportal_UserList] to [dbo].[gportal_UserList]
The new rows will be appended to the existing table.

The package will not be saved.
The package will be run immediately.
(I changed the source / destination just for security reasons)

The other error messages
Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Error)
      Messages
      * Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "articleID".
       (SQL Server Import and Export Wizard)
      
      * Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
       (SQL Server Import and Export Wizard)
      
      * Error 0xc004706b: Data Flow Task 1: "component "Destination - gportal_Articles" (58)" failed validation and returned validation status "VS_ISBROKEN".
       (SQL Server Import and Export Wizard)
      
      * Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
       (SQL Server Import and Export Wizard)
      
      * Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
       (SQL Server Import and Export Wizard)
      

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Success)

- Copying to [dbo].[gportal_Articles] (Stopped)

- Copying to [dbo].[gportal_Categories] (Stopped)

- Copying to [dbo].[gportal_Links] (Stopped)

- Copying to [dbo].[gportal_MailingList] (Stopped)

- Copying to [dbo].[gportal_MailTemplates] (Stopped)

- Copying to [dbo].[gportal_Reviews] (Stopped)

- Copying to [dbo].[gportal_Settings] (Stopped)

- Copying to [dbo].[gportal_Stats] (Stopped)

- Copying to [dbo].[gportal_Topics] (Stopped)

- Copying to [dbo].[gportal_UserList] (Stopped)

- Post-execute (Stopped)
specify-table-copy-or-query.jpg
select-source-tables-and-views.jpg
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35172945
I need to verify with the hosting company, but just curious - if they are using MSSQL 2000, would the above still work?

I tried to export the data to a local MSSQL, and almost succeeded but I received this error
Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Warning)
Messages
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "catName" with a length of 250 to database column "catName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "catNavi" with a length of 650 to database column "catNavi" with a length of 255.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "linkName" with a length of 250 to database column "linkName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "linkUrl" with a length of 350 to database column "linkUrl" with a length of 250.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "contactMail" with a length of 250 to database column "contactMail" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "email" with a length of 250 to database column "email" with a length of 185.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "subject" with a length of 300 to database column "subject" with a length of 250.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "name" with a length of 250 to database column "name" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "review" with a length of 450 to database column "review" with a length of 250.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "appName" with a length of 200 to database column "appName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "email" with a length of 250 to database column "email" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "topicName" with a length of 250 to database column "topicName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "navi" with a length of 450 to database column "navi" with a length of 255.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "pword" with a length of 50 to database column "pword" with a length of 15.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "email" with a length of 250 to database column "email" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "realName" with a length of 250 to database column "realName" with a length of 100.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "catName" with a length of 250 to database column "catName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "catNavi" with a length of 650 to database column "catNavi" with a length of 255.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "linkName" with a length of 250 to database column "linkName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "linkUrl" with a length of 350 to database column "linkUrl" with a length of 250.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "contactMail" with a length of 250 to database column "contactMail" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "email" with a length of 250 to database column "email" with a length of 185.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "subject" with a length of 300 to database column "subject" with a length of 250.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "name" with a length of 250 to database column "name" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "review" with a length of 450 to database column "review" with a length of 250.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "appName" with a length of 200 to database column "appName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "email" with a length of 250 to database column "email" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "topicName" with a length of 250 to database column "topicName" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "navi" with a length of 450 to database column "navi" with a length of 255.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "pword" with a length of 50 to database column "pword" with a length of 15.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "email" with a length of 250 to database column "email" with a length of 150.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 2: Truncation may occur due to inserting data from data flow column "realName" with a length of 250 to database column "realName" with a length of 100.
 (SQL Server Import and Export Wizard)
 

- Prepare for Execute (Success)

- Pre-execute (Success)

- Executing (Error)
Messages
Error 0xc02020c5: Data Flow Task 2: Data conversion failed while converting column "mailComponent" (107) to column "mailComponent" (147).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 2: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "mailComponent" (147)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "mailComponent" (147)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 2: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion 6 - 0" (139) failed with error code 0xC0209029 while processing input "Data Conversion Input" (140). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 

- Copying to `gportal_Articles` (Success)
5 rows transferred

- Copying to `gportal_Categories` (Success)
28 rows transferred

- Copying to `gportal_Links` (Success)
78 rows transferred

- Copying to `gportal_MailingList` (Success)
0 rows transferred

- Copying to `gportal_MailTemplates` (Success)
0 rows transferred

- Copying to `gportal_Reviews` (Stopped)

- Copying to `gportal_Settings` (Stopped)

- Copying to `gportal_Stats` (Stopped)

- Copying to `gportal_Topics` (Stopped)

- Copying to `gportal_UserList` (Stopped)

- Post-execute (Success)
Messages
Information 0x4004300b: Data Flow Task 1: "component "Destination - gportal_Articles" (58)" wrote 5 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 1: "component "Destination 1 - gportal_Categories" (152)" wrote 28 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 1: "component "Destination 2 - gportal_Links" (260)" wrote 78 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 1: "component "Destination 3 - gportal_MailingList" (341)" wrote 0 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 1: "component "Destination 4 - gportal_MailTemplates" (395)" wrote 0 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 2: "component "Destination 5 - gportal_Reviews" (43)" wrote 1 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 2: "component "Destination 6 - gportal_Settings" (115)" wrote 0 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 2: "component "Destination 7 - gportal_Stats" (179)" wrote 551 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 2: "component "Destination 8 - gportal_Topics" (234)" wrote 5 rows.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 2: "component "Destination 9 - gportal_UserList" (294)" wrote 1 rows.
 (SQL Server Import and Export Wizard)
0
 
LVL 3

Assisted Solution

by:rkharko
rkharko earned 2000 total points
ID: 35173798
I see in your code for Insert records you don't provide the primary key value "CatID" and in the new database somehow it is missing the default value "newID()" for this column ( maybe it was lost during restoring database)


The solution should be easy - open in table designer for "merchres.dbo.gportal_Categories" in SQL Server management studio and add default value newid() for column CatID

Next make the same steps to assign default value= newid() for column linkID in table merchres.dbo.gportal_Links
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35175448
OK - let me take a look to see if I can find this information (fingers crossed).  I am guessing you might be talking about the first database as well?  
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35175483
Before I made any changes, I thought I would take a picture of the new DB that I copied over.
These are the two images from the DB that I exported from the OLD DB to the new DB:
 Design Categories - Exported from Old DB Design Links - Exported from Old DBThese are the two images from the DB that I created, and the ran the SQL script above
 Design Categories - Created from the SQL Design Links - Created from the SQL
Hopefully this helps some.  Do I still need to make the changes and to which database?  

Thanks again!
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35176806
A related question MSSQL 2008 Express DB to MSSQL 2005 or MS Access.  I am sure this is probably an easy fix but I don't know enough about this unfortunately.

Thanks!
0
 
LVL 3

Accepted Solution

by:
rkharko earned 2000 total points
ID: 35176879
Hi,
The changes should be applied only to new database.
You need to select column "CatID" and then in the panel "Column Properties" find  item "Identity specification", expand this group and set the value "Is Identity"  = Yes

When column used for primary key is defined as Identity then the value will be assigned by system during insert, but it is recommended to use bigint type instead of int
see reference on Identity http://msdn.microsoft.com/en-us/library/aa933196%28v=sql.80%29.aspx
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35202025
Thanks - I have actually decided to just copy and re-enter the data.  It seems like it will be easier for me in the long run.  There is not too much data at least
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question