February 5, 2009 at 6:29 am
Hi,
TableA has follwing columns and data types:
col1 int
col2 varchar(100)
col3 ntext
col4 varchar(100)
this table has 100 rows
I need to copy this to another table:
select * into dbo.[TableB] from dbo.TableA
while copying I need the ntext to be converted to nvarchar(max)
After copying I can simply alter the column from ntext to nvarchar(max)
but i need it to be automated.
how to achieve this?
I read some Article/post regarding this in this forum, but lost the link,
can anyone help me
Thanks
Thanks,
Santhosh
February 5, 2009 at 7:32 am
Hi
Would this give what you need?:
selectcol1,
col2,
cast(col3 as nvarchar(max)) as col3,
col4
intodbo.TableB
fromdbo.TableA
Alun
February 5, 2009 at 9:51 pm
hkphooey (2/5/2009)
HiWould this give what you need?:
selectcol1,
col2,
cast(col3 as nvarchar(max)) as col3,
col4
intodbo.TableB
fromdbo.TableA
Alun
yes this will do for the table given.
but what if the ntext column is in different position?
I am trying to write a stored procedure which identifies the ntext
data type in the table and converts it to nvarchar(max)
Thanks
Thanks,
Santhosh
February 6, 2009 at 3:42 am
I think you'll have to write some CASE logic around the information held in:
select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'ntext'
Andy
February 6, 2009 at 4:08 am
AndyD (2/6/2009)
I think you'll have to write some CASE logic around the information held in:select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'ntext'
Andy
Yes Andy,
I am doing the same...
Can you tell me in a stored procedure how to execute the below statement?
ALTER TABLE Atemp_table ALTER COLUMN col_name nvarchar(max)
I am trying the below, but its saying some error:
--works for tables having only one ntext column
declare @Atable varchar(200),
--@Atemp_table varchar(8000),
@col_name varchar(200),
@sql varchar(8000),
@sql1 varchar(8000),
@sql2 varchar(8000),
@t_temp varchar(100),
@dt_type varchar(100)
set @Atable='csd lessons learned log'
--set @Atable='sub activity'
set @Atable='AccessDb...['+@Atable+']' --AccessDb -> linked server
set @sql='select * into dbo.Atemp_table from '+@Atable
exec(@sql)
if exists(
select column_name,data_type,character_maximum_length
from information_schema.columns
where table_name='Atemp_table' and data_type='ntext')
begin
--set @t_temp='Atemp_table'
--set @dt_type='ntext'
print 'has ntext column and converting it to nvarchar(max)'
select @col_name=column_name from information_schema.columns
where table_name='Atemp_table' and data_type='ntext'
--print @col_name
set @sql='nvarchar(max)'
set @sql2='alter table Atemp_table alter column '+@col_name +''+@sql
exec(@sql2)
print 'ntext column changed to narchar(max)'
end
else
print 'no ntext columns'
error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
I am sorry if you are confused with my variables
Thanks
Thanks,
Santhosh
February 6, 2009 at 5:30 am
As you have to EXEC a snippet of SQL code (hmmm, unless you updated the schema tables directly....), probably easier to run this in a Cursor. May not be the most efficient method, but it is reliable and easily understood:
declare @myTable nvarchar(255);
set @myTable = 'aaaTable';--could pass this value into the SP
declare @myColumn nvarchar(255);
declare @mySQL nvarchar(1000);
--declare the cursor
declare curChangeDataType cursor fast_forward for
select COLUMN_NAME
from information_schema.columns
where TABLE_NAME= @myTable and DATA_TYPE = 'ntext';
--open the cursor
open curChangeDataType
fetch next from curChangeDataType into @myColumn
--loop through the cursor
while @@fetch_status = 0
begin
--update the data type for the column
set @mySQL = 'alter table ' + @myTable + ' alter column ' + @myColumn + ' nvarchar(max)';
exec(@mySQL);
--load the next row
fetch next from curChangeDataType into @myColumn
end
close curChangeDataType
deallocate curChangeDataType;
An alternative option, as it looks like you are connecting to a Linked Server, is to create an SSIS project, and manipulate the column data types in these.
Andy
February 8, 2009 at 10:34 pm
AndyD (2/6/2009)
As you have to EXEC a snippet of SQL code (hmmm, unless you updated the schema tables directly....), probably easier to run this in a Cursor. May not be the most efficient method, but it is reliable and easily understood:
declare @myTable nvarchar(255);
set @myTable = 'aaaTable';--could pass this value into the SP
declare @myColumn nvarchar(255);
declare @mySQL nvarchar(1000);
--declare the cursor
declare curChangeDataType cursor fast_forward for
select COLUMN_NAME
from information_schema.columns
where TABLE_NAME= @myTable and DATA_TYPE = 'ntext';
--open the cursor
open curChangeDataType
fetch next from curChangeDataType into @myColumn
--loop through the cursor
while @@fetch_status = 0
begin
--update the data type for the column
set @mySQL = 'alter table ' + @myTable + ' alter column ' + @myColumn + ' nvarchar(max)';
exec(@mySQL);
--load the next row
fetch next from curChangeDataType into @myColumn
end
close curChangeDataType
deallocate curChangeDataType;
An alternative option, as it looks like you are connecting to a Linked Server, is to create an SSIS project, and manipulate the column data types in these.
Andy
Hi Andy,
Ya I got it, thanks...
You said an alternative option is to create an SSIS package.
Is there any guarantee that the data(values) of Access table is imported as it is to SQL Server?
I heard in this forum that, if "abc" of type varchar(10) of Access will be imported as "abc" of type varchar(10) to SQL Server
then no need to worry about this. Since SQL and SSIS will validate the data before importing.
Is that true?
Please let me know if there is any document on this.
Thanks
Thanks,
Santhosh
February 9, 2009 at 1:40 am
I'm sure there are docs out there to help with importing from Access. I haven't done it for a while, so I created a demo table in Access 2003 with various data types, then imported the table into SQL Server 2005.
My very quick experiment showed that:
Text, length 50, in Access is automatically imported as nvarchar(50)
Text, length 255, in Access is automatically imported as nvarchar(255)
Decimal in Access is imported as decimal with the same precision and scale (I didn't try all possible combinations...)
Memo in Access is imported as nvarchar(max)
Primary Key (autonumber) in Access is imported as int.
All this is very logical and sensible. I can't see any problems relying on the import/SSIS functions of MSSQL Server 2005. It is great to see that a Memo datatype in Access is automatically mapped to nvarchar(max). That should help you out...
Andy
February 9, 2009 at 1:52 am
AndyD (2/9/2009)
I'm sure there are docs out there to help with importing from Access. I haven't done it for a while, so I created a demo table in Access 2003 with various data types, then imported the table into SQL Server 2005.My very quick experiment showed that:
Text, length 50, in Access is automatically imported as nvarchar(50)
Text, length 255, in Access is automatically imported as nvarchar(255)
Decimal in Access is imported as decimal with the same precision and scale (I didn't try all possible combinations...)
Memo in Access is imported as nvarchar(max)
Primary Key (autonumber) in Access is imported as int.
All this is very logical and sensible. I can't see any problems relying on the import/SSIS functions of MSSQL Server 2005. It is great to see that a Memo datatype in Access is automatically mapped to nvarchar(max). That should help you out...
Andy
Yes definitely this helps me out Andy,
but what about the contents?
I mean, suppose a MEMO data type in Access has some 65535 characters - Will all these characters will be imported to SQL Server as it is to nvarchar(max)?(or is there any chance of data corruption or misplacement while importing)
(Suppose Access MEMO has a word "SQLServerCentral"
is there any chance that it might be imported as "SQL)ææÑ)))))"
some junk values as shown)
Thanks
Thanks,
Santhosh
February 9, 2009 at 3:30 am
Always a possibility!!! All I can suggest is that you run some tests.
Andy
February 9, 2009 at 3:36 am
AndyD (2/9/2009)
Always a possibility!!! All I can suggest is that you run some tests.Andy
Hi Andy, this is what I needed.
Some tests in the sense which kind of test?
How can it be made?
Any suggestions?
Thanks
Thanks,
Santhosh
February 9, 2009 at 3:59 am
A basic test would be to import a table from an Access DB, which you have created to include the columns and data that you want to check.
In MSSQL Server, the quick way to see how the table will import is to use SSMS, right-click on a DB and choose TASKS | Import Data; then just browse to the Access DB.
Andy
February 9, 2009 at 4:21 am
AndyD (2/9/2009)
A basic test would be to import a table from an Access DB, which you have created to include the columns and data that you want to check.In MSSQL Server, the quick way to see how the table will import is to use SSMS, right-click on a DB and choose TASKS | Import Data; then just browse to the Access DB.
Andy
Yes Andy,
I did it,
1. Through SQL Server Import Export Wizard I imported an Access Table
2. Now how to check whether the imported data is correct(as i said in
prev post "SQLServerCentral" --> "SQL{)|)))Äææ"
One way I am trying to do this is:
1. Import the table from Access through SSIS package
2. Now link the access database to sql server using:
exec sp_addlinkedserver
@server='AccessDb',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='full path of the database' --(e.g.: 'c:\test.mdb')
--and
exec sp_addlinkedsrvlogin
@rmtsrvname='AccessDb',
@useself='false',
@rmtuser='Admin',
@rmtpassword=''
3.Create the stored procedure to compare the tables
Create procedure usp_CompareTables @table1 varchar(100), @table2 varchar(100)
as
declare @sql varchar(8000)
--Added [ ] for table names that contains white spaces in between
set @table1='['+@table1+']'
set @table2='AccessDb...['+@table2+']'
–-AccessDb is the linked server name
set @sql = 'select ''' + @table1 + ''' as tblName, * from
(select * from ' + @table1 + '
except
select * from ' + @table2 + ') x
union all
select ''' + @table2 + ''' as tblName, * from
(select * from ' + @table2 + '
except
select * from ' + @table1 +') x'
exec(@sql)
4.This procedure works fine for every table except the tables having
MEMO data type in AccessDb(linked server), which will be ntext when
linked to SQL Server 2005
5.This MEMO data type, when imported through SSIS --> automatically converts to nvarchar(max)
6.Now, my question is, how to link the Access database to SQL Server 2005
which implicitly converts MEMO to nvarchar(max) and not ntext.
Also,
I am thinking like, if implicit conversion to nvarchar(max) is not possible by linked server then
1.Copy the table from linked server(AccessDb) to some temporary table in SQL Server
2.Identify NTEXT columns and replace them with nvarchar(max)
as you gave the code earlier
declare @myTable nvarchar(255);
set @myTable = 'csd_san'; --could pass this value into the SP
declare @myColumn nvarchar(255);
declare @mySQL nvarchar(1000);
--declare the cursor
declare curChangeDataType cursor fast_forward for
select COLUMN_NAME
from information_schema.columns
where TABLE_NAME= @myTable and DATA_TYPE = 'ntext';
--open the cursor
open curChangeDataType
fetch next from curChangeDataType into @myColumn
--loop through the cursor
while @@fetch_status = 0
begin
--update the data type for the column
set @mySQL = 'alter table ' + @myTable + ' alter column ' + @myColumn + ' nvarchar(max)';
exec(@mySQL);
--load the next row
fetch next from curChangeDataType into @myColumn
end
close curChangeDataType
deallocate curChangeDataType;
3.Now compare this converted temporary table with the table imported from SSIS package.
Does this sounds good?
Please let me know your idea on this
Sorry for taking this issue a long way.
Thanks
Thanks,
Santhosh
February 9, 2009 at 4:45 am
As far as I know, using the Import Data wizard will use the same Jet OLEDB connection as a linked server (but I might be wrong).
Ran a quick test and yes, when directly querying a MEMO datatype via a Linked Server, MSSQL implicitly converts to NTEXT. Although for an import, it uses nvarchar(max).
So I think I would go with your second option; get all the data from the linked server into MSSQL (either a #temp table or the final table), then convert all occurrences of NTEXT to nvarchar(max). It all sounds a bit like hard work; there might be a simpler way, for example using extended attributes of the Jet OLEDB connection (but I don't know how you would do this).
Andy
February 9, 2009 at 4:50 am
It all sounds a bit like hard work; there might be a simpler way,
Yes I too think there is a much simpler way
for example using extended attributes of the Jet OLEDB connection (but I don't know how you would do this).
Andy
I too do not know how to go about this.
I think there are many built-in stored procedures related to linked servers
but I do not know which are they and how to use them.
Please help me regarding this.
Thanks
Thanks,
Santhosh
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply