0

I have a issue with transferring images from one SQL server to another SQL server database.

Here is the scenario: Move the data(images) from server1.database1.images-table to server2.database2.images-table.

I have server1.database1.imagestable. I do not have login privileges to this database. We do not have the sa password to add me as a user. The images table have a few fields that contain images. The OS is Microsoft Windows NT 6.3 (9600). I am trying to move the data into another server.

The script to create images table in server1 indicates that the primary key ID has autoincrement on it (IDENTITY(1,1)). So while creating images table in server 2, I removed the autoincrement on the ID primary key as I cannot keep the old key values.

I tried downloading the data into a flat file and .csv but both failed as the size of the images exceeded the 8000 char limit.

I tried to do a data transfer through SSMS (tasks --> import data) from server1 to server2 from table 1 to table 2. The import worked. It created and extra column (no name with an auto increment ranging from 1 onwards), then followed by the ID column (key value copied from server2.images table. When I run a SQL query with column names (select ID, IDBatch from images) it does not seem to recognize the field names. However when I run query for all fields (select * from images) it returns a result set.

Did I make a mistake with the script? Thanks

PS. When I tried to do a data transfer through SSMS (tasks --> import data) from server1 to server2 from table 1 to table 2 with just the fields that I needed, I am getting a parse error:

The statement could not be parsed. Invalid object name database2.dbo.images

Dale K
  • 25,246
  • 15
  • 42
  • 71
user7907880
  • 27
  • 6

2 Answers2

0

Have you tried:

Select * into new_table  from  old_table

See: How to create a table from select query result in SQL Server 2008

Andrew
  • 761
  • 7
  • 9
  • Andrew - the problem here was with credentials. We did not have an SA password for me to access the data. Once we did get the credentials (please see me answer below), – user7907880 Aug 31 '23 at 15:12
0

The problem I had with this issue is I had access to the first database and not to the second one. Once I got the credentials, I was able to import that data into the first database using SSMS.

  1. GOTO the database that you'd like to import teh data into
  2. Right click --> tasks --> import data
  3. choose the data source (the DB you'd like the data exported from) --> SQL Server Native Client 11.0
  4. Choose a destination data source
  5. Follow the import instructions...

Use Caution: If your data has images then do not try and download teh data into a flat file or a CSV, as the maximum size of the data that can be uploaded through that method is 8,000 bytes.

user7907880
  • 27
  • 6