Converting database to MS-SQLServer from PostGRESQL

Forgive me if this question is a bit too generic, if it is, feel free to
just not respond.

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.

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.

pg_dump has a number of flags that may help, e.g. --inserts

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.

converting boolean to bit

I have a control with checkboxes. The checkbox.checked property returns a boolean value. If I want to insert a record into a table that has a corresponding column of type bit, how do I do it?

I tried

CType(myChkBox.checked,String)

, which returns the strings "True" or "False". But, I get the error

The name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

I then tried

"CAST(" & CType(myChkBox.checked,String) & ",bit)"

, but got the same error.

I can write a function that will return a 1 or a 0 but that seems ludicrous. Surely there must be a more elegant way to use the result of a checkbox in an SQL statement.

Thanks
Martindoes this work ?

IIf(CheckBox1.Checked = True, 1, 0)

|||There is, you should be using parameters not on-the-fly sql strings.|||pkr, I agree that I should be using parameters, and I will get around to it before I finalise this control. I am not in front of my code at the moment, do you think that

dim prmChecked as new SqlParameter("@.myParam",SqlDbType.Bit)
prmChecked = myChkBox.checked

will work? According to the doco, the SqlDataType Bit takes an unsigned integer of 1 or 0. So, I would still have the same problem.

ndinaker, thanks for your suggestion. Is this the way its usually done?

Martin|||The checked flag is a boolean. Even *if* it doesn't automatically convert boolean to bit, it's trivial to convert boolean to int.

Convert field

I am having a bit of a problem. I have a date field that is comming from
a different platform as a float. I need to convert these records to
datetime or small datetime. Is their a Stored Procedure to do this in SQL
or what is the quickest way to do this?
Thanks>> I have a date field [sic] that is comming from a different platform as a floa
t. <<
You don't know that a column is not anything like a field!
You don't know that a row is not anything like a record!
You might be able to do a CAST(foobar AS DATETIME). the quickest way
is to keep temporal data in temproal columns.|||You need to find out the starting point, for inhstance, what does 0.5
mean? Noon of what day?
Suppose 0 means midningt of January 1st, 2000.
Use dateadd function to add days (the integer part of the float) to
that start date
Then use dateadd to add seconds|||I have to suggestions.
1. The code which is performing the transformation from the other platform
may be able to convert as it goes.
2. You can Cast the float to a datetime list this...
Declare @.Mydate Float
Set @.Mydate = Cast( 1.125 as Float )
Select Cast( @.Mydate as DateTime)
However, it will require that the float from the other platform is using 0
as the same Epoch of 1900-01-01 00:00:00.000 .
Regards
Colin Dawson
www.cjdawson.com
"JimS" <noholycowsspam@.ya_NoJunk_hoo.com> wrote in message
news:O1Al2EOXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I am having a bit of a problem. I have a date field that is comming from
> a different platform as a float. I need to convert these records to
> datetime or small datetime. Is their a Stored Procedure to do this in SQL
> or what is the quickest way to do this?
> Thanks
>

Convert Date to String

Hi all,
I have a bit of a task...
We are hitting an old VMS system with a sql statement through an
Intersystems Cache ODBC driver (very old - not the most recent driver - not
upgrade-able).
I'm firing the query through a DTS.
In leighmans (sp?) I seem to have to use very "simple" queries in order to
not create an error message - anything a bit "clever" seems to kill it.
I need to query the database using a date, however, the company that wrote
the database in the first place stored the date as a string in this format:
ccyymmdd (20060424 for example).
I need to get the current date - less 2 days - and then convert it to that
format in order to use it in the sql statement. My problem seems to be that
I dont get the ' ' around the value and therefore it never works - throws a
very unhelpful error message up (just a long number with a minus sign in
front of it).
Can anyone shed any light on this at all for me - I appreciate the above is
a bit vague...
I've tried using parameters but that didn't work either...
If I hard code in the date it works fine - ie... WHERE their_date_column =
'20060424'
Any help would be appreciated.
Regards
RobCan you use the following code without it 'killing' it?
Select ... WHERE their_date_column = convert(char(8),getdate() -2,112)
HTH
Adam
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> Hi all,
> I have a bit of a task...
> We are hitting an old VMS system with a sql statement through an
> Intersystems Cache ODBC driver (very old - not the most recent driver - no
t
> upgrade-able).
> I'm firing the query through a DTS.
> In leighmans (sp?) I seem to have to use very "simple" queries in order to
> not create an error message - anything a bit "clever" seems to kill it.
> I need to query the database using a date, however, the company that wrote
> the database in the first place stored the date as a string in this format
:
> ccyymmdd (20060424 for example).
> I need to get the current date - less 2 days - and then convert it to that
> format in order to use it in the sql statement. My problem seems to be th
at
> I dont get the ' ' around the value and therefore it never works - throws
a
> very unhelpful error message up (just a long number with a minus sign in
> front of it).
> Can anyone shed any light on this at all for me - I appreciate the above i
s
> a bit vague...
> I've tried using parameters but that didn't work either...
> If I hard code in the date it works fine - ie... WHERE their_date_column
=
> '20060424'
> Any help would be appreciated.
> Regards
> Rob
>
>|||"Adam Warne" wrote ...

> Can you use the following code without it 'killing' it?
> Select ... WHERE their_date_column = convert(char(8),getdate() -2,112)
Hi Adam,
Many thanks for your reply - unfortunately it would seem not...
The "standard" error message I seem to get whenever I try something that
doesn't work is as follows:
HResult of 0x80040e14 (-2147217900) returned
Unexpected error occurred. An error result was returned without an error
message.
I think it has problems using what I would call "sql server" functions via
the Cache stuff - but I dont know for sure...
My previous idea was to do the "clever" bit - ie the above and slap the
result into another table - then use a
WHERE their_date_column IN (SELECT myNewDate FROM myNewTable)
etc...problem then is that I cant use a linked server in the query, I guess
this is something to do with the DTS - when I click on the "black bar" that
joins the object in the designer its obvious that its connecting to the
remote database and therefore I think I'm limited as to what else I can
connect to.
Originally we wanted to do all of this in .net - but we had major problems
connecting to the source - after 3 months we managed it with a dts, and 2
months and we were able to query some tables, 1 more month and we're where
we are now!
I can get ALL data but its like 2 years worth and takes over 30 minutes to
import - the information being "got" is from a Pharmacy in a hospital and we
need the results to appear on a web based report in more of a "real-time"
fashion - ie, not running every 1 hours as I currently have it (sometimes it
takes longer than 30 minutes , some times less)..
Regards
Rob|||OK Rob, I'm clutching at straws now ;-) How about
declare @.sqlstring nvarchar(300)
select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
'''' + convert(char(8),getdate() -2,112) + ''''
exec (@.sqlstring)
This will build the query as
SELECT * FROM UserTable WHERE their_date_column = '20060422'
and then execute it.
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> "Adam Warne" wrote ...
>
> Hi Adam,
> Many thanks for your reply - unfortunately it would seem not...
> The "standard" error message I seem to get whenever I try something that
> doesn't work is as follows:
> HResult of 0x80040e14 (-2147217900) returned
> Unexpected error occurred. An error result was returned without an error
> message.
>
> I think it has problems using what I would call "sql server" functions via
> the Cache stuff - but I dont know for sure...
> My previous idea was to do the "clever" bit - ie the above and slap the
> result into another table - then use a
> WHERE their_date_column IN (SELECT myNewDate FROM myNewTable)
> etc...problem then is that I cant use a linked server in the query, I gues
s
> this is something to do with the DTS - when I click on the "black bar" tha
t
> joins the object in the designer its obvious that its connecting to the
> remote database and therefore I think I'm limited as to what else I can
> connect to.
> Originally we wanted to do all of this in .net - but we had major problems
> connecting to the source - after 3 months we managed it with a dts, and 2
> months and we were able to query some tables, 1 more month and we're where
> we are now!
> I can get ALL data but its like 2 years worth and takes over 30 minutes to
> import - the information being "got" is from a Pharmacy in a hospital and
we
> need the results to appear on a web based report in more of a "real-time"
> fashion - ie, not running every 1 hours as I currently have it (sometimes
it
> takes longer than 30 minutes , some times less)..
> Regards
> Rob
>
>|||"Adam Warne" wrote ...

> OK Rob, I'm clutching at straws now ;-) How about
> declare @.sqlstring nvarchar(300)
> select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
> '''' + convert(char(8),getdate() -2,112) + ''''
> exec (@.sqlstring)
> This will build the query as
> SELECT * FROM UserTable WHERE their_date_column = '20060422'
> and then execute it.
Hi Adam,
Thanks again for the reply.
We tried something like this on Friday - albeit that the date building bit
was different (about 5 times as long :oD) and both that one, and your
example above fail with the same error as earlier :o/
We also tried it without the date creation bit and simply put a hard coded
date in there (in the right format) - but it still didn't work - obviously
doesn't like the parameter stuff...
Any more thoughts? It's getting to the point where I'll be setting up a
reminder in Outlook to go and advance a hard coded date by 1 month each
month :o(
Rob|||No worries Rob ... problem shared and all that :-)
Did you say you were using a DTS package? If this is the case have you
looked at the possibility of using a Global variable? This means you can us
e
a '?' in place of the date and set the ? to a global variable that you can
set up though the 'Package Properties'.
The variable can be checked and changed every time it runs.
If this sounds feasible and you want more info, let me know. If this
doesn't sound like a possibility let me know anyway and I'll see if I can up
my brain a gear! ;-)
Cheers
Adam
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> "Adam Warne" wrote ...
>
> Hi Adam,
> Thanks again for the reply.
> We tried something like this on Friday - albeit that the date building bit
> was different (about 5 times as long :oD) and both that one, and your
> example above fail with the same error as earlier :o/
> We also tried it without the date creation bit and simply put a hard coded
> date in there (in the right format) - but it still didn't work - obviously
> doesn't like the parameter stuff...
> Any more thoughts? It's getting to the point where I'll be setting up a
> reminder in Outlook to go and advance a hard coded date by 1 month each
> month :o(
> Rob
>
>|||On Mon, 24 Apr 2006 09:35:16 +0100, Rob Meade wrote:
(snip)
>In leighmans (sp?) I seem to have to use very "simple" queries in order to
>not create an error message - anything a bit "clever" seems to kill it.
Hi Rob,
Can you call user-defined functions? Or execute stored procedures? Both
might be able to work around your problem.
Hugo Kornelis, SQL Server MVP|||"Adam Warne" wrote ...

> No worries Rob ... problem shared and all that :-)
> Did you say you were using a DTS package? If this is the case have you
> looked at the possibility of using a Global variable? This means you can
> use
> a '?' in place of the date and set the ? to a global variable that you can
> set up though the 'Package Properties'.
> The variable can be checked and changed every time it runs.
> If this sounds feasible and you want more info, let me know. If this
> doesn't sound like a possibility let me know anyway and I'll see if I can
> up
> my brain a gear! ;-)
Hi Adam,
Many thanks for the reply.
I dont know whether or not this will work but am more than happy to give it
a try.
Any help would be appreciated - and yes - its via a DTS.
Regards
Rob|||"Hugo Kornelis" wrote ...

> Can you call user-defined functions? Or execute stored procedures? Both
> might be able to work around your problem.
Hi Hugo,
Thanks for the reply.
I dont think so - in the "transformation" part of the DTS I have a SQL Query
box to enter the sql statement - the problems seems to be that because its
source is the remote machine, I cant use linked servers or anything like
that - so unless the tables exist on the remote database I cant use them -
the same is true of SP's...|||"Adam Warne" wrote:

> OK Rob, I'm clutching at straws now ;-) How about
> declare @.sqlstring nvarchar(300)
> select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
> '''' + convert(char(8),getdate() -2,112) + ''''
> exec (@.sqlstring)
> This will build the query as
> SELECT * FROM UserTable WHERE their_date_column = '20060422'
> and then execute it.
> --
> Adam J Warne, MCDBA
Essentially the same thing, but maybe try one of the following variations of
the above solution:
SELECT @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
CHAR(39) + CONVERT(CHAR(8), GETDATE() -2, 112) + CHAR(39)
SELECT @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
QUOTENAME( CONVERT(CHAR(8), GETDATE() -2, 112), '''' )
Cheers,
dave

Convert character to NULL

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Under this scenario, is that the only value in the column? Or are you searching/replacing (.) with NULLs?|||

I am using a conditional evaluation in the derived column transformation:

[Coulmn1] == "." ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : [Column1]

The columns would never have a value that contains a dot, only values or the dot

|||

crancilio wrote:

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Not that I am awre of. I am afraid you have to do the same thing for every column affected by that logic. Perhaps, you could try to use an script component where you could use the magic of copy and paste...

|||Let's use a script component instead of a derived column. You'll be very happy with the following solution:

Instead of the derived column, add a script component, set it to be a transformation.

Select the columns you wish to work with. Set their usage types to "ReadWrite." ONLY select the columns you wish to process with this logic.

Below is the script. I don't understand it fully, and I've hacked something that our forum user, jaegd wrote:

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1
'Credit has been given where credit is due
'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

Public Class ScriptMain
Inherits UserComponent

Private inputBuffer As PipelineBuffer
Private cols As Dictionary(Of Int32, ColumnInfo) = New Dictionary(Of Int32, ColumnInfo)
Private currentColumnInfo As ColumnInfo = New ColumnInfo

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Setup control counter
Dim counter As Integer = 0
'Loop through segments
'MsgBox(currentColumnInfo.colIndex.ToString)
For Each currentcolumn As KeyValuePair(Of Int32, ColumnInfo) In cols
'MsgBox(inputBuffer.GetString(currentcolumn.Key))
If (inputBuffer.GetString(currentcolumn.Key)) = "." Then
inputBuffer.SetString(currentcolumn.Key, Chr(0))
End If
Next

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
' Get the Pipeline Buffer for subsequent ordinal column access
inputBuffer = Buffer
MyBase.ProcessInput(InputID, Buffer)
End Sub

Public Overrides Sub PreExecute()
BuildColumnDictionary()
End Sub

Private Sub BuildColumnDictionary()
Dim indexes() As Integer
Dim input As IDTSInput90
Dim col As IDTSInputColumn90
Dim offset As Integer = 0

input = Me.ComponentMetaData.InputCollection(0)
'presumes GetColumnIndexes order matches iterator order
'as BufferManager is not available to my knowledge in ScriptComponent
indexes = Me.GetColumnIndexes(input.ID)
For Each col In input.InputColumnCollection
Dim columnStructure As New ColumnInfo
With columnStructure
.colName = col.Name
.colLength = col.Length
.colIndex = indexes(offset)
'Normally, BufferManager would be used, but its not exposed in Script Component
.colPrecision = col.Precision
.colScale = col.Scale
.colType = col.DataType
End With
cols.Add(indexes(offset), columnStructure)
offset += 1
Next
End Sub

Public Structure ColumnInfo
Dim colName As String
Dim colType As DataType
Dim colIndex As Int32
Dim colLength As Int32
Dim colPrecision As Int32
Dim colScale As Int32
End Structure

End Class

A screenshot of the results: HERE|||Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.|||This is great - thanks! I will let you know how it goes|||

Phil - thank you so much for your help!

This code was exactly what I was looking for. The only tweak I had to make was to use SetNull() instead of SetString() - the Chr(0) actually added an empty string rather than a null.

Thanks again!

|||

Phil Brammer wrote:

Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.

How's this?

Note - this is really only useful for generically accessing each column, and has no type safety, so the code only works for string columns. jaegd's original code provided a lot more information about the columns, including type information, which would allow you to add conditional logic to use the appropriate accessor (GetString, GetInt32, etc). I've also used the System.Reflection to access the buffer, but I need to test the performance a bit more before pushing that as a solution.

Code Snippet

'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1

'Credit has been given where credit is due

'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

'Trimmed to smaller set of code by jwelch

Imports Microsoft.SqlServer.Dts.Pipeline

PublicClass ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

PublicOverridesSub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim counter AsInteger = 0

For counter = 0 To inputBuffer.ColumnCount - 1

If (inputBuffer.GetString(counter)) = "."Then

inputBuffer.SetString(counter, Chr(0))

EndIf

Next

EndSub

PublicOverridesSub ProcessInput(ByVal InputID AsInteger, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

EndSub

EndClass

|||

jwelch wrote:

How's this?

Yep, I like that MUCH better. Knew jaegd had much more stuff in there, I just didn't quite know where to begin to trim it down... Ran out of time too... The golf course beckoned.