mapping data types

I am building a .NET app that uses sqlDataReader.GetSchemaTable method. this
returns a table which includes the column "Provider Type" which is the
database data type of the column, however it is a numeric value, are the
mappings between these numeric values and the textual data type names
documented anywhere?
eg Money appears to be 9
and Date appears to be 15For SQL Server 2000, the systypes system table contains mappings between the
data type name and a number called 'xtype'. They don't seem to line up with
your observations though.
xtype Data type name
-- --
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar
"guy" wrote:

> I am building a .NET app that uses sqlDataReader.GetSchemaTable method. th
is
> returns a table which includes the column "Provider Type" which is the
> database data type of the column, however it is a numeric value, are the
> mappings between these numeric values and the textual data type names
> documented anywhere?
> eg Money appears to be 9
> and Date appears to be 15
>|||Mark,
hmmm interesting, so far i have found:-
2 bit
3 char
6 float
8 Int
9 money
15 smalldatetime
16 smallint
22 varchar
cheers
"Mark Williams" wrote:
> For SQL Server 2000, the systypes system table contains mappings between t
he
> data type name and a number called 'xtype'. They don't seem to line up wit
h
> your observations though.
> xtype Data type name
> -- --
> 34 image
> 35 text
> 36 uniqueidentifier
> 48 tinyint
> 52 smallint
> 56 int
> 58 smalldatetime
> 59 real
> 60 money
> 61 datetime
> 62 float
> 98 sql_variant
> 99 ntext
> 104 bit
> 106 decimal
> 108 numeric
> 122 smallmoney
> 127 bigint
> 165 varbinary
> 167 varchar
> 173 binary
> 175 char
> 189 timestamp
> 231 sysname
> 231 nvarchar
> 239 nchar
>
> "guy" wrote:
>

Manipulate the 'deleted record' flag on DBF files

Hi,

I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.

These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.

It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.

What are the options available for me to be able to query and differentiate the 'deleted' records ?

Thank you in advance,

Bertrand Larsy

Hi Bertrand,

Here's some example VB code to work with the deleted status of a row:

Try

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
'-- Make some VFP data to play with
Dim cmd1 As New OleDbCommand( _
"Create Table TestDBF (Field1 I, Field2 C(10))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into TestDBF Values (1, 'Hello')", cn1)
Dim cmd3 As New OleDbCommand( _
"Insert Into TestDBF Values (2, 'World')", cn1)
Dim cmd4 As New OleDbCommand( _
"Delete From TestDBF Where Field1 = 1", cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd4.ExecuteNonQuery()
cn1.Close()

Dim cn2 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn2.Open()

Dim cmd5 As New OleDbCommand( _
"Select * From TestDBF", cn2)
Dim da1 As New OleDbDataAdapter(cmd5)
Dim ds1 As New DataSet
Dim dr1 As DataRow
da1.Fill(ds1)
For Each dr1 In ds1.Tables(0).Rows
Console.WriteLine( _
dr1.Item(0).ToString() & ", " & dr1.Item(1).ToString)
Next
Console.ReadLine()
cn2.Close()

Dim cn3 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn3.Open()

Dim cmd6 As New OleDbCommand( _
"Set Deleted Off", cn3)
cmd6.ExecuteNonQuery()
Dim cmd7 As New OleDbCommand( _
"Select Deleted('TestDBF') As IsDeleted, TestDBF.* From TestDBF", cn3)
Dim da2 As New OleDbDataAdapter(cmd7)
Dim ds2 As New DataSet
Dim dr2 As DataRow
da2.Fill(ds2)
For Each dr2 In ds2.Tables(0).Rows
Console.WriteLine( _
dr2.Item(0).ToString() & ", " & dr2.Item(1).ToString() & ", " & dr2.Item(2).ToString())
Next
Console.ReadLine()
cn2.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

|||

Thank you,

This is indeed a workable solution.

Now, is there a way to perform the same work in a T-SQL script ?

I tried, but could not manage to "chain" successfully a 'SET DELETED OFF' statement and a SELECT with OPENQUERY or OPENDATASOURCE:

SELECT [name]

FROM OPENQUERY(linkedServer,'SELECT name FROM Members')

This returns all records, but not the deleted ones

SELECT [name]

FROM OPENQUERY(linkedServer,'SET Deleted OFF;

SELECT name FROM Members')

This produces the error '

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "SET Deleted OFF;

SELECT name FROM Members". The OLE DB provider "VFPOLEDB" for linked server "linkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

'

When doing it with an EXECUTE statement as pass-through query on a linked server (using OLE DB provider for FoxPro, of course), it says it successfully executes, but does not return the result of the select query:

DECLARE @.Query varchar(max)

SET @.Query='SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This returns all records, but not the deleted ones

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This produces the query output 'Command(s) completed successfully.', but does not return any result.

Kind regards,

Bertrand Larsy

|||

Hi Bertrand,

I don't have time to try it now but it might work with a semicolon: "Set Deleted Off;Select * From MyTable..."

|||

Neither of the 2 following samples give any result:

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

Anyway, thank you for your answers, I will make a CLR stored procedure of your first reply.

Kind regards,

Bertrand Larsy