|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Import Data succeeds but doesn't copy data?I have a dB on a remote SQL2000sp4/win2k server I'm trying to import into
SQL2005x64 (sp1) on Server2003x64. I have a schema that I didn't write that created the database on both servers. During config on the import wizard I select to delete existing data and enable identity insert, and when I run the copy all the steps complete, including "copied xxx rows", and there are no errors in the report. But when I check the table, there is no data in it! Would a constraint on the table prevent the data from actually being inserted, even though it reports the rows were copied successfully? If a CONSTRAINT was involved with preventing the data from being imported,
there would be error messages, and you would not receive a "copied xxx rows" message. I'm wondering about the location of the resulting table. Are you sure that you did NOT use the same server for both the source and destination? -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "geek-y-guy" <no***@nowhere.com> wrote in message news:OSWC9ovxGHA.4232@TK2MSFTNGP05.phx.gbl... >I have a dB on a remote SQL2000sp4/win2k server I'm trying to import into >SQL2005x64 (sp1) on Server2003x64. I have a schema that I didn't write that >created the database on both servers. > > During config on the import wizard I select to delete existing data and > enable identity insert, and when I run the copy all the steps complete, > including "copied xxx rows", and there are no errors in the report. > > But when I check the table, there is no data in it! Would a constraint on > the table prevent the data from actually being inserted, even though it > reports the rows were copied successfully? > > If a CONSTRAINT was involved with preventing the data from being imported, That's what I assumed...> there would be error messages, and you would not receive a "copied xxx > rows" message. > > I'm wondering about the location of the resulting table. Are you sure that That would be a likely mistake for me to make :o> you did NOT use the same server for both the source and destination? .... but in this case I'm sure that isn't what's happening...I'm copying from the remote server and targeting the dB on the local server (I've tried it numerous times). In each case, I'm logging in to the remote server with a SQL Login/pwd and to the local server with a trusted connection. I'm selected "delete rows in target table" and "enable identity insert". Here's my transaction: //--begin transaction Click Finish to perform the following actions: Copy rows from [myDataBase].[myDataBase].[carthead] to [myDataBase].[myDataBase].[carthead] Any existing rows in the target table will be deleted. Copy rows from [myDataBase].[myDataBase].[cartrows] to [myDataBase].[myDataBase].[cartrows] Any existing rows in the target table will be deleted. Copy rows from [myDataBase].[myDataBase].[CartRowsOptions] to [myDataBase].[myDataBase].[CartRowsOptions] Any existing rows in the target table will be deleted. Copy rows from [myDataBase].[myDataBase].[Categories] to [myDataBase].[myDataBase].[Categories] Any existing rows in the target table will be deleted. Copy rows from [myDataBase].[myDataBase].[Categories_Products] to [myDataBase].[myDataBase].[Categories_Products] Any existing rows in the target table will be deleted. The package will not be saved. The package will be run immediately. The execution was successful //--end transaction And here's the report upon completion: //--begin report - Create a TransferProvider. (Success) - Create a temporary file for destructive SQL. (Success) - Create a temporary file for constructive SQL. (Success) - Create a temporary XML file for table metadata. (Success) - Configure the TransferProvider with files and Source/Destination connection info. (Success) - Execute the transfer with the TransferProvider. (Success) - Execute the destructive SQL. (drop tables) (Success) - Execute the constructive SQL. (create tables) (Success) - Create a temporary table transfer package for [myDataBase].[carthead] (Success) - Execute transfer package for [myDataBase].[carthead] (Success) - Copying to [myDataBase].[carthead] (Success) * 45 rows transferred - Create a temporary table transfer package for [myDataBase].[cartrows] (Success) - Execute transfer package for [myDataBase].[cartrows] (Success) - Copying to [myDataBase].[cartrows] (Success) * 65 rows transferred - Create a temporary table transfer package for [myDataBase].[CartRowsOptions] (Success) - Execute transfer package for [myDataBase].[CartRowsOptions] (Success) - Copying to [myDataBase].[CartRowsOptions] (Success) * 0 rows transferred - Create a temporary table transfer package for [myDataBase].[Categories] (Success) - Execute transfer package for [myDataBase].[Categories] (Success) - Copying to [myDataBase].[Categories] (Success) * 3 rows transferred - Create a temporary table transfer package for [myDataBase].[Categories_Products] (Success) - Execute transfer package for [myDataBase].[Categories_Products] (Success) - Copying to [myDataBase].[Categories_Products] (Success) * 13 rows transferred //--end report So, it looks like everything executes...but when I open any table in the dB, the data is not there! Show quote > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "geek-y-guy" <no***@nowhere.com> wrote in message > news:OSWC9ovxGHA.4232@TK2MSFTNGP05.phx.gbl... >>I have a dB on a remote SQL2000sp4/win2k server I'm trying to import into >>SQL2005x64 (sp1) on Server2003x64. I have a schema that I didn't write >>that created the database on both servers. >> >> During config on the import wizard I select to delete existing data and >> enable identity insert, and when I run the copy all the steps complete, >> including "copied xxx rows", and there are no errors in the report. >> >> But when I check the table, there is no data in it! Would a constraint on >> the table prevent the data from actually being inserted, even though it >> reports the rows were copied successfully? >> > > Look closely at the messages...
> Copy rows from [myDataBase].[myDataBase].[carthead] to Looks like you are copying from a database [MyDatabase] to the SAME > [myDataBase].[myDataBase].[carthead] [MyDatabase] I still suspect that you are using the same source and destination servers. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "geek-y-guy" <no***@nowhere.com> wrote in message news:%2383%23QrWyGHA.3552@TK2MSFTNGP02.phx.gbl... >> If a CONSTRAINT was involved with preventing the data from being >> imported, there would be error messages, and you would not receive a >> "copied xxx rows" message. >> > That's what I assumed... > >> I'm wondering about the location of the resulting table. Are you sure >> that you did NOT use the same server for both the source and destination? > > That would be a likely mistake for me to make :o > > ... but in this case I'm sure that isn't what's happening...I'm copying > from the remote server and targeting the dB on the local server (I've > tried it numerous times). In each case, I'm logging in to the remote > server with a SQL Login/pwd and to the local server with a trusted > connection. I'm selected "delete rows in target table" and "enable > identity insert". Here's my transaction: > > //--begin transaction > Click Finish to perform the following actions: > Copy rows from [myDataBase].[myDataBase].[carthead] to > [myDataBase].[myDataBase].[carthead] > Any existing rows in the target table will be deleted. > Copy rows from [myDataBase].[myDataBase].[cartrows] to > [myDataBase].[myDataBase].[cartrows] > Any existing rows in the target table will be deleted. > Copy rows from [myDataBase].[myDataBase].[CartRowsOptions] to > [myDataBase].[myDataBase].[CartRowsOptions] > Any existing rows in the target table will be deleted. > Copy rows from [myDataBase].[myDataBase].[Categories] to > [myDataBase].[myDataBase].[Categories] > Any existing rows in the target table will be deleted. > Copy rows from [myDataBase].[myDataBase].[Categories_Products] to > [myDataBase].[myDataBase].[Categories_Products] > Any existing rows in the target table will be deleted. > The package will not be saved. > The package will be run immediately. > The execution was successful > //--end transaction > > And here's the report upon completion: > > //--begin report > - Create a TransferProvider. (Success) > - Create a temporary file for destructive SQL. (Success) > - Create a temporary file for constructive SQL. (Success) > - Create a temporary XML file for table metadata. (Success) > - Configure the TransferProvider with files and Source/Destination > connection info. (Success) > - Execute the transfer with the TransferProvider. (Success) > - Execute the destructive SQL. (drop tables) (Success) > - Execute the constructive SQL. (create tables) (Success) > - Create a temporary table transfer package for [myDataBase].[carthead] > (Success) > - Execute transfer package for [myDataBase].[carthead] (Success) > - Copying to [myDataBase].[carthead] (Success) > * 45 rows transferred > - Create a temporary table transfer package for [myDataBase].[cartrows] > (Success) > - Execute transfer package for [myDataBase].[cartrows] (Success) > - Copying to [myDataBase].[cartrows] (Success) > * 65 rows transferred > - Create a temporary table transfer package for > [myDataBase].[CartRowsOptions] (Success) > - Execute transfer package for [myDataBase].[CartRowsOptions] (Success) > - Copying to [myDataBase].[CartRowsOptions] (Success) > * 0 rows transferred > - Create a temporary table transfer package for [myDataBase].[Categories] > (Success) > - Execute transfer package for [myDataBase].[Categories] (Success) > - Copying to [myDataBase].[Categories] (Success) > * 3 rows transferred > - Create a temporary table transfer package for > [myDataBase].[Categories_Products] (Success) > - Execute transfer package for [myDataBase].[Categories_Products] > (Success) > - Copying to [myDataBase].[Categories_Products] (Success) > * 13 rows transferred > //--end report > > So, it looks like everything executes...but when I open any table in the > dB, the data is not there! > >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "geek-y-guy" <no***@nowhere.com> wrote in message >> news:OSWC9ovxGHA.4232@TK2MSFTNGP05.phx.gbl... >>>I have a dB on a remote SQL2000sp4/win2k server I'm trying to import into >>>SQL2005x64 (sp1) on Server2003x64. I have a schema that I didn't write >>>that created the database on both servers. >>> >>> During config on the import wizard I select to delete existing data and >>> enable identity insert, and when I run the copy all the steps complete, >>> including "copied xxx rows", and there are no errors in the report. >>> >>> But when I check the table, there is no data in it! Would a constraint >>> on the table prevent the data from actually being inserted, even though >>> it reports the rows were copied successfully? >>> >> >> > > >> Copy rows from [myDataBase].[myDataBase].[carthead] to Nope...I double-checked and it just doesn't work. Just to be sure, I created >> [myDataBase].[myDataBase].[carthead] > > Looks like you are copying from a database [MyDatabase] to the SAME > [MyDatabase] > > I still suspect that you are using the same source and destination > servers. a new blank database on the destination server and did an "import data" from the remote server...it copied all the data over successfully. The problem is, the app reading the database gives a permissions error when trying to read the copied database...I think the ownership is an issue, but I'm not sure. The app (an ecommerce cart), has an option to "fix" a database, but it just reruns the schema-creation script and wipes the entire database. When I try to "import" into this newly initialized dB, is when I get the error. I mentioned that I copied to a new dB on the destination server successfully...I then tried to import data into the re-initialized database from the local 2nd database, and the same thing happens as I outlined below. Now, I agree with you...it looks like the target database is the same as the destination database...but this must be some flaw in the SSMS import wizard because I am definitely NOT specifying the same server and dB for the target/destination! Show quote > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "geek-y-guy" <no***@nowhere.com> wrote in message > news:%2383%23QrWyGHA.3552@TK2MSFTNGP02.phx.gbl... >>> If a CONSTRAINT was involved with preventing the data from being >>> imported, there would be error messages, and you would not receive a >>> "copied xxx rows" message. >>> >> That's what I assumed... >> >>> I'm wondering about the location of the resulting table. Are you sure >>> that you did NOT use the same server for both the source and >>> destination? >> >> That would be a likely mistake for me to make :o >> >> ... but in this case I'm sure that isn't what's happening...I'm copying >> from the remote server and targeting the dB on the local server (I've >> tried it numerous times). In each case, I'm logging in to the remote >> server with a SQL Login/pwd and to the local server with a trusted >> connection. I'm selected "delete rows in target table" and "enable >> identity insert". Here's my transaction: >> >> //--begin transaction >> Click Finish to perform the following actions: >> Copy rows from [myDataBase].[myDataBase].[carthead] to >> [myDataBase].[myDataBase].[carthead] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[cartrows] to >> [myDataBase].[myDataBase].[cartrows] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[CartRowsOptions] to >> [myDataBase].[myDataBase].[CartRowsOptions] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[Categories] to >> [myDataBase].[myDataBase].[Categories] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[Categories_Products] to >> [myDataBase].[myDataBase].[Categories_Products] >> Any existing rows in the target table will be deleted. >> The package will not be saved. >> The package will be run immediately. >> The execution was successful >> //--end transaction >> >> And here's the report upon completion: >> >> //--begin report >> - Create a TransferProvider. (Success) >> - Create a temporary file for destructive SQL. (Success) >> - Create a temporary file for constructive SQL. (Success) >> - Create a temporary XML file for table metadata. (Success) >> - Configure the TransferProvider with files and Source/Destination >> connection info. (Success) >> - Execute the transfer with the TransferProvider. (Success) >> - Execute the destructive SQL. (drop tables) (Success) >> - Execute the constructive SQL. (create tables) (Success) >> - Create a temporary table transfer package for [myDataBase].[carthead] >> (Success) >> - Execute transfer package for [myDataBase].[carthead] (Success) >> - Copying to [myDataBase].[carthead] (Success) >> * 45 rows transferred >> - Create a temporary table transfer package for [myDataBase].[cartrows] >> (Success) >> - Execute transfer package for [myDataBase].[cartrows] (Success) >> - Copying to [myDataBase].[cartrows] (Success) >> * 65 rows transferred >> - Create a temporary table transfer package for >> [myDataBase].[CartRowsOptions] (Success) >> - Execute transfer package for [myDataBase].[CartRowsOptions] >> (Success) >> - Copying to [myDataBase].[CartRowsOptions] (Success) >> * 0 rows transferred >> - Create a temporary table transfer package for [myDataBase].[Categories] >> (Success) >> - Execute transfer package for [myDataBase].[Categories] (Success) >> - Copying to [myDataBase].[Categories] (Success) >> * 3 rows transferred >> - Create a temporary table transfer package for >> [myDataBase].[Categories_Products] (Success) >> - Execute transfer package for [myDataBase].[Categories_Products] >> (Success) >> - Copying to [myDataBase].[Categories_Products] (Success) >> * 13 rows transferred >> //--end report >> >> So, it looks like everything executes...but when I open any table in the >> dB, the data is not there! >> >>> -- >>> Arnie Rowland, Ph.D. >>> Westwood Consulting, Inc >>> >>> Most good judgment comes from experience. >>> Most experience comes from bad judgment. >>> - Anonymous >>> >>> >>> "geek-y-guy" <no***@nowhere.com> wrote in message >>> news:OSWC9ovxGHA.4232@TK2MSFTNGP05.phx.gbl... >>>>I have a dB on a remote SQL2000sp4/win2k server I'm trying to import >>>>into SQL2005x64 (sp1) on Server2003x64. I have a schema that I didn't >>>>write that created the database on both servers. >>>> >>>> During config on the import wizard I select to delete existing data and >>>> enable identity insert, and when I run the copy all the steps complete, >>>> including "copied xxx rows", and there are no errors in the report. >>>> >>>> But when I check the table, there is no data in it! Would a constraint >>>> on the table prevent the data from actually being inserted, even though >>>> it reports the rows were copied successfully? >>>> >>> >>> >> >> > > <egg-on-face>
I went back to the import data script and looked at it carefully, and I realized that the tables were being copied, but under a different schema, so I ended up with a duplicate set of tables that DID have the data in them :( </egg-on-face> My question is, is there any way to alter the schema identity of a table? IOW: the application is looking for a table with a schema of "dbo" like: [mydatabase].[dbo].[mytable] but when I'm copying the database, the tables are being created as: [mydatabase].[myschema].[mytable] is there any way to "map" those tables with [myschema] to [dbo]? If I run the app's script to create the tables, it creates them under [dbo], and if I try to copy directly to those tables, all SSMS will let me do is create new tables under schema [myschema]...help? -- Show quote"Arnie Rowland" <ar***@1568.com> wrote in message news:OGg7Y0WyGHA.4392@TK2MSFTNGP04.phx.gbl... > Look closely at the messages... > >> Copy rows from [myDataBase].[myDataBase].[carthead] to >> [myDataBase].[myDataBase].[carthead] > > Looks like you are copying from a database [MyDatabase] to the SAME > [MyDatabase] > > I still suspect that you are using the same source and destination > servers. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "geek-y-guy" <no***@nowhere.com> wrote in message > news:%2383%23QrWyGHA.3552@TK2MSFTNGP02.phx.gbl... >>> If a CONSTRAINT was involved with preventing the data from being >>> imported, there would be error messages, and you would not receive a >>> "copied xxx rows" message. >>> >> That's what I assumed... >> >>> I'm wondering about the location of the resulting table. Are you sure >>> that you did NOT use the same server for both the source and >>> destination? >> >> That would be a likely mistake for me to make :o >> >> ... but in this case I'm sure that isn't what's happening...I'm copying >> from the remote server and targeting the dB on the local server (I've >> tried it numerous times). In each case, I'm logging in to the remote >> server with a SQL Login/pwd and to the local server with a trusted >> connection. I'm selected "delete rows in target table" and "enable >> identity insert". Here's my transaction: >> >> //--begin transaction >> Click Finish to perform the following actions: >> Copy rows from [myDataBase].[myDataBase].[carthead] to >> [myDataBase].[myDataBase].[carthead] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[cartrows] to >> [myDataBase].[myDataBase].[cartrows] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[CartRowsOptions] to >> [myDataBase].[myDataBase].[CartRowsOptions] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[Categories] to >> [myDataBase].[myDataBase].[Categories] >> Any existing rows in the target table will be deleted. >> Copy rows from [myDataBase].[myDataBase].[Categories_Products] to >> [myDataBase].[myDataBase].[Categories_Products] >> Any existing rows in the target table will be deleted. >> The package will not be saved. >> The package will be run immediately. >> The execution was successful >> //--end transaction >> >> And here's the report upon completion: >> >> //--begin report >> - Create a TransferProvider. (Success) >> - Create a temporary file for destructive SQL. (Success) >> - Create a temporary file for constructive SQL. (Success) >> - Create a temporary XML file for table metadata. (Success) >> - Configure the TransferProvider with files and Source/Destination >> connection info. (Success) >> - Execute the transfer with the TransferProvider. (Success) >> - Execute the destructive SQL. (drop tables) (Success) >> - Execute the constructive SQL. (create tables) (Success) >> - Create a temporary table transfer package for [myDataBase].[carthead] >> (Success) >> - Execute transfer package for [myDataBase].[carthead] (Success) >> - Copying to [myDataBase].[carthead] (Success) >> * 45 rows transferred >> - Create a temporary table transfer package for [myDataBase].[cartrows] >> (Success) >> - Execute transfer package for [myDataBase].[cartrows] (Success) >> - Copying to [myDataBase].[cartrows] (Success) >> * 65 rows transferred >> - Create a temporary table transfer package for >> [myDataBase].[CartRowsOptions] (Success) >> - Execute transfer package for [myDataBase].[CartRowsOptions] >> (Success) >> - Copying to [myDataBase].[CartRowsOptions] (Success) >> * 0 rows transferred >> - Create a temporary table transfer package for [myDataBase].[Categories] >> (Success) >> - Execute transfer package for [myDataBase].[Categories] (Success) >> - Copying to [myDataBase].[Categories] (Success) >> * 3 rows transferred >> - Create a temporary table transfer package for >> [myDataBase].[Categories_Products] (Success) >> - Execute transfer package for [myDataBase].[Categories_Products] >> (Success) >> - Copying to [myDataBase].[Categories_Products] (Success) >> * 13 rows transferred >> //--end report >> >> So, it looks like everything executes...but when I open any table in the >> dB, the data is not there! >> >>> -- >>> Arnie Rowland, Ph.D. >>> Westwood Consulting, Inc >>> >>> Most good judgment comes from experience. >>> Most experience comes from bad judgment. >>> - Anonymous >>> >>> >>> "geek-y-guy" <no***@nowhere.com> wrote in message >>> news:OSWC9ovxGHA.4232@TK2MSFTNGP05.phx.gbl... >>>>I have a dB on a remote SQL2000sp4/win2k server I'm trying to import >>>>into SQL2005x64 (sp1) on Server2003x64. I have a schema that I didn't >>>>write that created the database on both servers. >>>> >>>> During config on the import wizard I select to delete existing data and >>>> enable identity insert, and when I run the copy all the steps complete, >>>> including "copied xxx rows", and there are no errors in the report. >>>> >>>> But when I check the table, there is no data in it! Would a constraint >>>> on the table prevent the data from actually being inserted, even though >>>> it reports the rows were copied successfully? >>>> >>> >>> >> >> > >
Show quote
> <egg-on-face> I figured it out <whew> ... changing the schema table-by-table fixed the > > I went back to the import data script and looked at it carefully, and I > realized that the tables were being copied, but under a different schema, > so I ended up with a duplicate set of tables that DID have the data in > them :( > > </egg-on-face> > > My question is, is there any way to alter the schema identity of a table? > > IOW: the application is looking for a table with a schema of "dbo" like: > > [mydatabase].[dbo].[mytable] > > but when I'm copying the database, the tables are being created as: > > [mydatabase].[myschema].[mytable] > > is there any way to "map" those tables with [myschema] to [dbo]? If I run > the app's script to create the tables, it creates them under [dbo], and if > I try to copy directly to those tables, all SSMS will let me do is create > new tables under schema [myschema]...help? problem. Owner/Schema differences was my next point of inspection. I'm glad that you
figured it out. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "geek-y-guy" <no***@nowhere.org> wrote in message news:uw1b3SsyGHA.1292@TK2MSFTNGP03.phx.gbl... >> <egg-on-face> >> >> I went back to the import data script and looked at it carefully, and I >> realized that the tables were being copied, but under a different schema, >> so I ended up with a duplicate set of tables that DID have the data in >> them :( >> >> </egg-on-face> >> >> My question is, is there any way to alter the schema identity of a table? >> >> IOW: the application is looking for a table with a schema of "dbo" like: >> >> [mydatabase].[dbo].[mytable] >> >> but when I'm copying the database, the tables are being created as: >> >> [mydatabase].[myschema].[mytable] >> >> is there any way to "map" those tables with [myschema] to [dbo]? If I run >> the app's script to create the tables, it creates them under [dbo], and >> if I try to copy directly to those tables, all SSMS will let me do is >> create new tables under schema [myschema]...help? > > I figured it out <whew> ... changing the schema table-by-table fixed the > problem. >  |
|||||||||||||||||||||||
Other interesting topics