to install the evaluation version of SQL Server 2005 instead. A
straight upgrade does not seem to be possible. Does someone know if
backing up the databases, uninstalling the desktop engine, installing
SQL Server 2005 and then importing/restoring the databases would be an
option to look into?
Here is an article on Upgradeing MSDE to SQL 2005 Express, you could follow this example then it is an easy upgrade to the Full SQL 2005 Product.
sqlsqlI have a database which has been running in PostgreSQL for a number of
years at this stage which I want to port into MS SQL server.
It seems that the SQL that Postgre outputs when I do a backup is not
syntactically correct within MS-SQL server.
My question is, does anyone have any documentation on how to convert a
database from the Postgre platform to SQL server? Is it possible using
an ODBC connection to import a database structure including table
definitions, views etc into SQL Server?
Failing this, does anyone have any suggestions on where I might start -
I did attempt to go through the SQL code and modify it to suit SQL
server, but it's about 3,500 lines of code excluding the insert
statements (which themselves are also wrong) and almost every line needs
something changed when comparing SQL syntax from Postgre to MSSQL server
Thanks in advance for any comments/suggestions.
Engada.
--
Posted via a free Usenet account from http://www.teranews.comEngada wrote:
Quote:
Originally Posted by
I have a database which has been running in PostgreSQL for a number of
years at this stage which I want to port into MS SQL server.
>
It seems that the SQL that Postgre outputs when I do a backup is not
syntactically correct within MS-SQL server.
What specific types of syntax errors do you encounter?
Googling (PostgreSQL export) turns up some third-party programs designed
to simplify this type of port.
When running this query, several records are returned but then sql server gives the follwing error. Can you see how it can be solved please? Thanks
select
case
when isnumeric([Column 9]) = 1 then convert(decimal(24, 4), [Column 9])
else
null
end
from
tblCEMTradeFeed
Error:
Error converting data type varchar to numeric.
You can not trust function ISNUMERIC a 100%. This function returns 1 also for values like '.', '2E3' (scientific notation), '+', '-', but not all of them can be conevrted to numeric data type.
select cast('2E3' as float)
go
select cast('2E3' as numeric(5, 2))
go
What is wrong with IsNumeric()?
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
AMB
|||How do I fix the sql then please?|||The following expression might fix a issue,
Note:
Is numeric function will return true for “.”, “$” & etc.
Code Snippet
case when
isnumeric([Column 9]) = 1
and patindex('%[0-9]%',[column 9]) <> 0
then convert(decimal(24, 4), [Column 9])
else
null
end
|||Hi Manivannan.D.Sekaran,
I guess the OP is expecting a decimal separator in the data, if not, why to convert to numeric(24, 4).
I think that the expression:
> and patindex('%[0-9]%',[column 9]) <> 0
will not yield the expected result. The following value will cause an error '2E3'.
select
case
when isnumeric([Column 9]) = 1 and patindex('%[0-9]%',[Column 9]) <> 0 then convert(decimal(24, 4), [Column 9])
else null end
from
(select '2E3' as [Column 9]) as t
go
AMB
|||Did you check the link I attached to the post?
AMB
|||Hi HUNCHBACK (padern me i can't able to get your orginal name),
Yes I agree with you. I hope STR should be a right choice to convert the string to decimal
Code Snippet
select
[Column 9],
Case When
patindex('%[0-9]%',[Column 9])<>0
Then
Case When
Isnumeric(str(replace([Column 9],'$',''),24,4)) =1
Then Cast(str(replace([Column 9],'$',''),24,4) as Numeric(24,4))
End
End
from
(
select '2E3' as [Column 9]
Union All
select '2E24' as [Column 9]
Union All
select '2E80' as [Column 9]
Union All
select '100' as [Column 9]
Union All
select '.' as [Column 9]
Union All
select '$' as [Column 9]
Union All
select '$9' as [Column 9]
Union All
select '878.8373738' as [Column 9]
Union All
Select 'mani'
) as t
|||
Hi Manivannan.D.Sekaran,
Much better. Try adding:
(
...
union all
select '$9'
) as t
AMB
i am running a package which extracts result of count(x), count(y),sum(z) from db2(as400) servers
i want to store the result of all the three in a table to a column of datatype varchar(25).
can someone help me with the convert/cast function in db2 which i can include in the query.
something like : select cast(count(x) as varchar(25)) from lib.file
No, but we can help you use SSIS to convert them...Have you searched IBM's site for a SQL reference document containing commands?|||
yaa Phil,even i thought it is better to use a data converter transformation.
the problem iam running into is iam trying to store int and decimal datatypes into the same output column.
which is a varchar.
the convertor works fine for int datatype but is giving error for decimal.
"datatype of output column 'colname' doesnot mach with the datatype 'system.decimal' of the source column 'columnname' "
"component DataReaderSource failed validation and returned validation status VS_NEEDSNEWMETADATA"
what i understand is it not problem with the conver transformation its problem with the data source reader.
let me be more clear i have 6 querys running agains AS400 in a loop 1,2,4,5 querys give output in int and querys 3,6 give output numeric.
when i put each of this querys(1,2,4,5) in datasource reader the output datasource reader pics DT_I4 as the result datatype and for querys 3,6 it pics DT_NUMERIC
i think i am left with option to change in the query itself
help needed.... thanks in advance
