Your Cart

Shopping Cart Items: 0   Sub-Total : $0.00    View Cart    Checkout
Welcome, Guest ( Customer Panel | Login )


Home | Profile | Register | Active Topics | Members | Search | FAQ


Forgot your Password?
 All Forums
 VPCart Forum
 General help me questions
 SQL05: Identity order in tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nxpage
Starting Member

49 Posts
Posted - January 03 2007 :  15:36:02  Show Profile  Reply with Quote
We updated production/server with all our products, categories, configurations, etc. I then wanted to update my localhost SQL db to mimic the tables in production for further testing, etc. I'm using Export Data through MS SQL Management Studio as follows:

On localhost
1. Dropped all tables
2. Ran create600sqlserver.sql to recreate tables/indexes

Then in Export Data edited mappings to Append Rows and Enable Identity Insert.

Everything runs fine but nothing matches on localhost (products in wrong categories, cross-sells attached to wrong items, etc.)

I looked at the raw SQL tables in production and the Identity columns are NOT in order ASCENDING, they are in a random order. Hence the append on localhost creates the Identity (e.g. products "catalogid") in numerical order ASC. Yet in the same table on server the catalogid is not in any order (9,13,14,1,2,3,11,10...)

I've never seen this before (have other projects that are fine).
Edited by - nxpage on January 03 2007 15:37:23

dafragan
Starting Member

45 Posts
Posted - January 03 2007 :  15:42:11  Show Profile  Reply with Quote
Hi!

Can you try making a backup file of your database and then restoring it on your local instead of using DTS?

You'd have to do the same thing with dropping all tables then recreating them using the create600sqlserver.sql script, but then restore the BAK file instead of using DTS.

I'm not sure if it will work, but it's worth a try!

Seb
Go to Top of Page

nxpage
Starting Member

49 Posts
Posted - January 03 2007 :  16:02:27  Show Profile  Reply with Quote
Thanks Sebastian, but getting a SQL backup file requires the intervention of our host (and in some instances a cost to do that). DTS is the simplest/fastest way to sync with production data on a regular basis.

I just noticed that the end of create600sqlserver.sql has all the tables with NONCLUSTERED indexes. I believe that's the problem.

VPASP question -- why are they done that way? CLUSTERED indexes have a number of advantages over NONCLUSTERED. Now that we've input all our data I'm not sure how we can change this (not a SQL guru by any means).

*** EDIT *** Sorry, they are ALTER TABLE - CONSTRAINTS as nonclustered:

constraint [pk_products] primary key nonclustered
Edited by - nxpage on January 03 2007 16:07:52
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000
0 Item(s)
$0.00
  • Your Cart Is Empty