Error converting data type varchar to int

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

General SQL Server Forums

New to SQL Server Programming

Error converting data type varchar to int

Author

Topic

TLundThuen
Starting Member

3 Posts

Posted - 2009-12-10 : 03:03:45

Hi,
I have 2 databases one AX and AX_Germany. The database and all tables are identically. AX contains million of records and AX_Germany contains no records. I need to copy all rows regarding Germany from each table in AX to the related table in AX_Germany. These records can be identified by the column dataareaid.

I made the below script:

use AX_Tyskland
EXECUTE sp_MSforeachtable
'
DECLARE @test varchar(50)
DECLARE @sql varchar(4000)
SET @test = SUBSTRING(convert (varchar (50), "?"),8,100)
SET @test = LEFT(@test,Len(@test)-1)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @test AND COLUMN_NAME = "dataareaid")
BEGIN
SET @sql = "insert into ax.dbo."+@test+" select * from "+@test+" where dataareaid=''DEB''"
exec(@sql)
END
';

I am new to SQL/TSQL so the above might be inefficeint or wrong

When I run the script the rows starts copying but eventually I get an error "Error converting data type varchar to int" The data that is being copied and fails is the string 'DEB'. In both source and destination tabel the column is registered as a varchar(3).

What am i missing here?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-10 : 04:53:22

SET @sql = "insert into ax.dbo."+@test+" select * from "+@test+" where dataareaid=''DEB''"
select @sql
exec(@sql)

Then you can find out which table it is failing on and have a look at the data.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-10 : 04:53:37

Hi TLundThuen,

You can do this easily by using the SSIS packages which is intact with SQL server 2005.
select Data flow task in the control flo..add the OLEDB data sorce and OLEDB destination and map the columsn.
No need to write single line of code to accomplish this.

Hope this will help you.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-10 : 05:33:03

quote:
Originally posted by gangadhara.ms

Hi TLundThuen,

You can do this easily by using the SSIS packages which is intact with SQL server 2005.
select Data flow task in the control flo..add the OLEDB data sorce and OLEDB destination and map the columsn.
No need to write single line of code to accomplish this.

Hope this will help you.

But this doesn't allow you to dynamically select tables to copy from one database to another.

shijobaby500
Starting Member

5 Posts

Posted - 2009-12-11 : 02:11:13

http://sqlerrormessages.blogspot.com/2009/12/error-converting-data-type-varchar-to.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 04:09:24

quote:
Originally posted by shijobaby500

http://sqlerrormessages.blogspot.com/2009/12/error-converting-data-type-varchar-to.html

Again, you are posting

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60435

Madhivanan

Failing to plan is Planning to fail

TLundThuen
Starting Member

3 Posts

Posted - 2009-12-11 : 07:54:04

Hi,
thank you for your suggestions. I will look further into them. Anyway I found the error. When the errors occour the fields are exactly the same but the table column order is not. So in source I have column A(str) B(str) C(int) and in destination I have A(str) C(int) B(str) - which ofcourse will throw an error.

Any suggestions how to solve this one? I am doing a test right now. I dropped all tables i destination db. Then I used the script wizard in the source db to create a query that creates all the tables. It is running right now (1500 tables). After it finishes the plan is to run this query on the destination db. And after that I assume that my insert into script should work

any comments?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-11 : 08:27:54

Put in a column list, this can be accomplished selecting from sys.columns or the information_schema. This way you can ensure that a column is mapping to the correct column on the second database.

TLundThuen
Starting Member

3 Posts

Posted - 2009-12-11 : 08:36:34

quote:
Originally posted by RickD

Put in a column list, this can be accomplished selecting from sys.columns or the information_schema. This way you can ensure that a column is mapping to the correct column on the second database.

Hi,
sounds more easy and I get the idea. I am new to SQL. Do you have an example. I assume that I need some kind of while loop in which I will create the column list.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-11 : 09:09:28

something like:


declare @collist varchar(max)

select @collist = coalesce(@collist + name + ',','')
from sys.columns 
where object_id = object_id('tablename') 

select substring(@collist1,len(@collist)-1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources