Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Hi All,

I have a xml column in a table. As part of converting a XML into rowset i wrote a small proc like;

/****************************/

DECLARE @.DocHandle int

DECLARE @.XmlDocument nvarchar(1000)

SET @.XmlDocument = N'<XMLDATA>

<COLUMNS>

<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>

<Column name="Val2"> 2 </Column>

<Column name="Val3"> 3 </Column>

<Column name="Val4"> Test0 </Column>

<Column name="Val5"> Test1 </Column>

<Column name="Val6"> Test2 </Column>

<Column name="Val7"> Test3 </Column>

<Column name="Val8"> Test4 </Column>

</COLUMNS>

</XMLDATA>'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @.DocHandle OUTPUT, @.XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@.DocHandle, '/XMLDATA/COLUMNS/Column',3)

WITH (Header varchar(50) '@.name',

Val varchar(50) 'text()')

EXEC sp_xml_removedocument @.DocHandle

/************************/

This works fine, but my query is how to modify this proc to read the XML from a table rather than hard code it in the proc itself.

Thanks in Advance

Since you mention xml column, so I assume you are using sql 2005. To process xml column in table especially you might have > 1 rows of xml data, you want to use xml nodes() method. For you example:

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
go

sql

Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Hi All,

I have a xml column in a table. As part of converting a XML into rowset i wrote a small proc like;

/****************************/

DECLARE @.DocHandle int

DECLARE @.XmlDocument nvarchar(1000)

SET @.XmlDocument = N'<XMLDATA>

<COLUMNS>

<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>

<Column name="Val2"> 2 </Column>

<Column name="Val3"> 3 </Column>

<Column name="Val4"> Test0 </Column>

<Column name="Val5"> Test1 </Column>

<Column name="Val6"> Test2 </Column>

<Column name="Val7"> Test3 </Column>

<Column name="Val8"> Test4 </Column>

</COLUMNS>

</XMLDATA>'

-- Create an internal representation of the XML document.

EXECsp_xml_preparedocument @.DocHandle OUTPUT, @.XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT*

FROMOPENXML(@.DocHandle,'/XMLDATA/COLUMNS/Column',3)

WITH(Header varchar(50)'@.name',

Val varchar(50)'text()')

EXECsp_xml_removedocument @.DocHandle

/************************/

This works fine, but my query is how to modify this proc to read the XML from a table rather than hard code it in the proc itself.

Thanks in Advance

Since you mention xml column, so I assume you are using sql 2005. To process xml column in table especially you might have > 1 rows of xml data, you want to use xml nodes() method. For you example:

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
go

Reading a variable in a script taks used as source

Hi,

I'm using a script task as source and I need to read a global variable.

I tried stuff I found on the internet, but they all use Dts.VariableDispenser.LockForRead(varName)

This gives an errero on 'Dts.' saying it can not resolve it.

If you'd like, you can predict what variable you'd like to read or write to, you can use the ReadOnlyVariable or ReadWriteVariable properties to pass in the variable to your script. At that point, you won't need the variable dispenser. Then, you could read the variable by using code as easy as this:

LocalVariable = DTS.Variables("VariableName").Value

This video may help you as well: http://www.jumpstarttv.com/Media.aspx?vid=6

-- Brian Knight

|||Hi,

I've typed in the variables in the ReadOnlyVariables, but how can I access them in the script?

Also, 'LocalVariable = DTS.Variables("VariableName").Value' doesn't work (because it's a source script?). I get an error that VB is unable to resolve 'dts'.

I'm using folowing import:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts
Imports Microsoft.SqlServer.Dts.Runtime

Thank you

reading a txt file

I import data by omporting a txt file that is tab delimited.
On of the fileds in the txt file has a date 08/06/2007 (for example)
I need to read that into my field in my table that is set as datetime.
Is there somethign special that need to get done? When I try, i get teh
following error:
Conversion invalid for datatypes on column pair 1 (source column
'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I import data by omporting a txt file that is tab delimited.
> On of the fileds in the txt file has a date 08/06/2007 (for example)
> I need to read that into my field in my table that is set as datetime.
> Is there somethign special that need to get done? When I try, i get teh
> following error:
> Conversion invalid for datatypes on column pair 1 (source column
> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
You should change the source data type to datetime or use text for
both.
|||it's a text file.
Before I export it to a text file, I have it formated as datetime in excel.
"SB" <othellomy@.yahoo.com> wrote in message
news:1186485944.181307.110770@.w3g2000hsg.googlegro ups.com...
> On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> You should change the source data type to datetime or use text for
> both.
>
|||On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> it's a text file.
> Before I export it to a text file, I have it formated as datetime in excel.
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186485944.181307.110770@.w3g2000hsg.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Hi,
I think it be easier if you import the text file AS IS that is all
columns as text (including the column with datetime) you can call it
Excel_to_text_TEMP. Once you have this table imported in the server
you can populate your main table from this temp table and use a
conversion function for datetime etc. HTH.
|||I do intailly load it into a tmp table, as after its in teh temps table, I
have it go and only copy new original records into the main table, I also
have it update exsisting records. so with that said...
How do I convert it to datetime as it goes from the tmp table to teh main
table?
I am not an sql master or anythign, heck, i'm not ever a sql novice lol
so please have your example as clear as possible..
THANK YOU
"SB" <othellomy@.yahoo.com> wrote in message
news:1186545477.061884.9860@.19g2000hsx.googlegroup s.com...
> On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> Hi,
> I think it be easier if you import the text file AS IS that is all
> columns as text (including the column with datetime) you can call it
> Excel_to_text_TEMP. Once you have this table imported in the server
> you can populate your main table from this temp table and use a
> conversion function for datetime etc. HTH.
>
|||On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do intailly load it into a tmp table, as after its in teh temps table, I
> have it go and only copy new original records into the main table, I also
> have it update exsisting records. so with that said...
> How do I convert it to datetime as it goes from the tmp table to teh main
> table?
> I am not an sql master or anythign, heck, i'm not ever a sql novice lol
> so please have your example as clear as possible..
> THANK YOU
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186545477.061884.9860@.19g2000hsx.googlegroup s.com...
>
>
>
>
>
>
> - Show quoted text -
Hi,
Then you should drop the TEMP table and re-create it where all columns
should be varchar(255) or varchar(1000) etc. Then once the temp table
is loaded you should run a query such as (preferably in a sored
procedure, see Books online for syntax):
insert Excel_To_Text (
column1,
column2,
etc...,
date_Column1,
etc...)
select column1,
column2,
etc...,
convert(datetime,Date_Column1),
etc...
from Excel_To_Text_TEMP
Let me know if you have questions. Regards,
SB

reading a txt file

I import data by omporting a txt file that is tab delimited.
On of the fileds in the txt file has a date 08/06/2007 (for example)
I need to read that into my field in my table that is set as datetime.
Is there somethign special that need to get done? When I try, i get teh
following error:
Conversion invalid for datatypes on column pair 1 (source column
'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I import data by omporting a txt file that is tab delimited.
> On of the fileds in the txt file has a date 08/06/2007 (for example)
> I need to read that into my field in my table that is set as datetime.
> Is there somethign special that need to get done? When I try, i get teh
> following error:
> Conversion invalid for datatypes on column pair 1 (source column
> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
You should change the source data type to datetime or use text for
both.|||it's a text file.
Before I export it to a text file, I have it formated as datetime in excel.
"SB" <othellomy@.yahoo.com> wrote in message
news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
> On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> You should change the source data type to datetime or use text for
> both.
>|||On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> it's a text file.
> Before I export it to a text file, I have it formated as datetime in excel
.
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
Hi,
I think it be easier if you import the text file AS IS that is all
columns as text (including the column with datetime) you can call it
Excel_to_text_TEMP. Once you have this table imported in the server
you can populate your main table from this temp table and use a
conversion function for datetime etc. HTH.|||I do intailly load it into a tmp table, as after its in teh temps table, I
have it go and only copy new original records into the main table, I also
have it update exsisting records. so with that said...
How do I convert it to datetime as it goes from the tmp table to teh main
table?
I am not an sql master or anythign, heck, i'm not ever a sql novice lol
so please have your example as clear as possible..
THANK YOU
"SB" <othellomy@.yahoo.com> wrote in message
news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
> On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> Hi,
> I think it be easier if you import the text file AS IS that is all
> columns as text (including the column with datetime) you can call it
> Excel_to_text_TEMP. Once you have this table imported in the server
> you can populate your main table from this temp table and use a
> conversion function for datetime etc. HTH.
>|||On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do intailly load it into a tmp table, as after its in teh temps table, I
> have it go and only copy new original records into the main table, I also
> have it update exsisting records. so with that said...
> How do I convert it to datetime as it goes from the tmp table to teh main
> table?
> I am not an sql master or anythign, heck, i'm not ever a sql novice lol
> so please have your example as clear as possible..
> THANK YOU
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hi,
Then you should drop the TEMP table and re-create it where all columns
should be varchar(255) or varchar(1000) etc. Then once the temp table
is loaded you should run a query such as (preferably in a sored
procedure, see Books online for syntax):
insert Excel_To_Text (
column1,
column2,
etc...,
date_Column1,
etc...)
select column1,
column2,
etc...,
convert(datetime,Date_Column1),
etc...
from Excel_To_Text_TEMP
Let me know if you have questions. Regards,
SB|||SB <othellomy@.yahoo.com> wrote in
news:1186631817.654696.162750@.19g2000hsx.googlegroups.com:

> On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> Hi,
> Then you should drop the TEMP table and re-create it where all columns
> should be varchar(255) or varchar(1000) etc. Then once the temp table
> is loaded you should run a query such as (preferably in a sored
> procedure, see Books online for syntax):
> insert Excel_To_Text (
> column1,
> column2,
> etc...,
> date_Column1,
> etc...)
> select column1,
> column2,
> etc...,
> convert(datetime,Date_Column1),
> etc...
> from Excel_To_Text_TEMP
> Let me know if you have questions. Regards,
> SB
You might eliminate the text file and instead:
SELECT F1 AS [Col1], ... CAST(Fn AS datetime) AS [DateCol], ...
INTO [DestinationTable]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8. 0;HDR=YES;Database=full_path_to\filename
.xls',
'SELECT * FROM [SheetName$]')
Use the Microsoft.ACE.OLEDB.12.0 provider if you don't have Jet or if the
Excel version is 2007 (and, for Excel 2007, change Excel 8.0 to Excel
12.0).
You will probably find this a little tricky to get working (I surely
did!) so build up from a simple import, e.g. of a single integer column.

reading a txt file

I import data by omporting a txt file that is tab delimited.
On of the fileds in the txt file has a date 08/06/2007 (for example)
I need to read that into my field in my table that is set as datetime.
Is there somethign special that need to get done? When I try, i get teh
following error:
Conversion invalid for datatypes on column pair 1 (source column
'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I import data by omporting a txt file that is tab delimited.
> On of the fileds in the txt file has a date 08/06/2007 (for example)
> I need to read that into my field in my table that is set as datetime.
> Is there somethign special that need to get done? When I try, i get teh
> following error:
> Conversion invalid for datatypes on column pair 1 (source column
> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
You should change the source data type to datetime or use text for
both.|||it's a text file.
Before I export it to a text file, I have it formated as datetime in excel.
"SB" <othellomy@.yahoo.com> wrote in message
news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
> On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I import data by omporting a txt file that is tab delimited.
>> On of the fileds in the txt file has a date 08/06/2007 (for example)
>> I need to read that into my field in my table that is set as datetime.
>> Is there somethign special that need to get done? When I try, i get teh
>> following error:
>> Conversion invalid for datatypes on column pair 1 (source column
>> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> You should change the source data type to datetime or use text for
> both.
>|||On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> it's a text file.
> Before I export it to a text file, I have it formated as datetime in excel.
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>
> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> >> I import data by omporting a txt file that is tab delimited.
> >> On of the fileds in the txt file has a date 08/06/2007 (for example)
> >> I need to read that into my field in my table that is set as datetime.
> >> Is there somethign special that need to get done? When I try, i get teh
> >> following error:
> >> Conversion invalid for datatypes on column pair 1 (source column
> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> > You should change the source data type to datetime or use text for
> > both.- Hide quoted text -
> - Show quoted text -
Hi,
I think it be easier if you import the text file AS IS that is all
columns as text (including the column with datetime) you can call it
Excel_to_text_TEMP. Once you have this table imported in the server
you can populate your main table from this temp table and use a
conversion function for datetime etc. HTH.|||I do intailly load it into a tmp table, as after its in teh temps table, I
have it go and only copy new original records into the main table, I also
have it update exsisting records. so with that said...
How do I convert it to datetime as it goes from the tmp table to teh main
table?
I am not an sql master or anythign, heck, i'm not ever a sql novice lol
so please have your example as clear as possible..
THANK YOU
"SB" <othellomy@.yahoo.com> wrote in message
news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
> On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
>> it's a text file.
>> Before I export it to a text file, I have it formated as datetime in
>> excel.
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>>
>> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> >> I import data by omporting a txt file that is tab delimited.
>> >> On of the fileds in the txt file has a date 08/06/2007 (for
>> >> example)
>> >> I need to read that into my field in my table that is set as datetime.
>> >> Is there somethign special that need to get done? When I try, i get
>> >> teh
>> >> following error:
>> >> Conversion invalid for datatypes on column pair 1 (source column
>> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
>> > You should change the source data type to datetime or use text for
>> > both.- Hide quoted text -
>> - Show quoted text -
> Hi,
> I think it be easier if you import the text file AS IS that is all
> columns as text (including the column with datetime) you can call it
> Excel_to_text_TEMP. Once you have this table imported in the server
> you can populate your main table from this temp table and use a
> conversion function for datetime etc. HTH.
>|||On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do intailly load it into a tmp table, as after its in teh temps table, I
> have it go and only copy new original records into the main table, I also
> have it update exsisting records. so with that said...
> How do I convert it to datetime as it goes from the tmp table to teh main
> table?
> I am not an sql master or anythign, heck, i'm not ever a sql novice lol
> so please have your example as clear as possible..
> THANK YOU
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
>
> > On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> >> it's a text file.
> >> Before I export it to a text file, I have it formated as datetime in
> >> excel.
> >> "SB" <othell...@.yahoo.com> wrote in message
> >>news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
> >> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> >> >> I import data by omporting a txt file that is tab delimited.
> >> >> On of the fileds in the txt file has a date 08/06/2007 (for
> >> >> example)
> >> >> I need to read that into my field in my table that is set as datetime.
> >> >> Is there somethign special that need to get done? When I try, i get
> >> >> teh
> >> >> following error:
> >> >> Conversion invalid for datatypes on column pair 1 (source column
> >> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> >> > You should change the source data type to datetime or use text for
> >> > both.- Hide quoted text -
> >> - Show quoted text -
> > Hi,
> > I think it be easier if you import the text file AS IS that is all
> > columns as text (including the column with datetime) you can call it
> > Excel_to_text_TEMP. Once you have this table imported in the server
> > you can populate your main table from this temp table and use a
> > conversion function for datetime etc. HTH.- Hide quoted text -
> - Show quoted text -
Hi,
Then you should drop the TEMP table and re-create it where all columns
should be varchar(255) or varchar(1000) etc. Then once the temp table
is loaded you should run a query such as (preferably in a sored
procedure, see Books online for syntax):
insert Excel_To_Text (
column1,
column2,
etc...,
date_Column1,
etc...)
select column1,
column2,
etc...,
convert(datetime,Date_Column1),
etc...
from Excel_To_Text_TEMP
Let me know if you have questions. Regards,
SB|||SB <othellomy@.yahoo.com> wrote in
news:1186631817.654696.162750@.19g2000hsx.googlegroups.com:
> On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I do intailly load it into a tmp table, as after its in teh temps
>> table, I have it go and only copy new original records into the main
>> table, I also have it update exsisting records. so with that
>> said...
>> How do I convert it to datetime as it goes from the tmp table to teh
>> main table?
>> I am not an sql master or anythign, heck, i'm not ever a sql novice
>> lol
>> so please have your example as clear as possible..
>> THANK YOU
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
>>
>> > On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
>> >> it's a text file.
>> >> Before I export it to a text file, I have it formated as datetime
>> >> in excel.
>> >> "SB" <othell...@.yahoo.com> wrote in message
>> >>news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>> >> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> >> >> I import data by omporting a txt file that is tab delimited.
>> >> >> On of the fileds in the txt file has a date 08/06/2007 (for
>> >> >> example)
>> >> >> I need to read that into my field in my table that is set as
>> >> >> datetime.
>> >> >> Is there somethign special that need to get done? When I try,
>> >> >> i get teh
>> >> >> following error:
>> >> >> Conversion invalid for datatypes on column pair 1 (source
>> >> >> column 'DoRecd'(dbstype_str), destination column
>> >> >> 'doRecd'(DBTYPE_DBtimestamp)
>> >> > You should change the source data type to datetime or use text
>> >> > for both.- Hide quoted text -
>> >> - Show quoted text -
>> > Hi,
>> > I think it be easier if you import the text file AS IS that is all
>> > columns as text (including the column with datetime) you can call
>> > it Excel_to_text_TEMP. Once you have this table imported in the
>> > server you can populate your main table from this temp table and
>> > use a conversion function for datetime etc. HTH.- Hide quoted text
>> > -
>> - Show quoted text -
> Hi,
> Then you should drop the TEMP table and re-create it where all columns
> should be varchar(255) or varchar(1000) etc. Then once the temp table
> is loaded you should run a query such as (preferably in a sored
> procedure, see Books online for syntax):
> insert Excel_To_Text (
> column1,
> column2,
> etc...,
> date_Column1,
> etc...)
> select column1,
> column2,
> etc...,
> convert(datetime,Date_Column1),
> etc...
> from Excel_To_Text_TEMP
> Let me know if you have questions. Regards,
> SB
You might eliminate the text file and instead:
SELECT F1 AS [Col1], ... CAST(Fn AS datetime) AS [DateCol], ...
INTO [DestinationTable]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=full_path_to\filename.xls',
'SELECT * FROM [SheetName$]')
Use the Microsoft.ACE.OLEDB.12.0 provider if you don't have Jet or if the
Excel version is 2007 (and, for Excel 2007, change Excel 8.0 to Excel
12.0).
You will probably find this a little tricky to get working (I surely
did!) so build up from a simple import, e.g. of a single integer column.sql

Reading a transaction log file

I'm having alot of trouble reading a SQL 2000 transaction log backup file.
As far as I can see, Microsoft do not provide such a tool and so far I've
tried a product called "SLR" and "ApexSQL Log". Both look OK and work OK with
"small" log files.
My problem is that the file I need to look at is 581MB and both products
just fail miserably when trying to look at these files (I think there are
about 1.3 million transcations in this file).
Before asking why I need to look at this file, I must stress that its
because of problem (server running out of disk space, so log backups were not
happening) that the log grew so big. Unfortunately, the site does not have a
DBA and I've been called in, as this all came to light when 5 important
tables appeared to have been "re-created" (dropped and created empty, with no
indexes, etc...). That has all been recovered, but they want to know how this
happened!!
I know for a fact that this 581mb log backup contains the transactions that
did the damage.
Any other ideas will be welcome!!
Is there not a command line utility, such simpy dumps all the info in these
file to a nice readable text file?? Or should I just persevere with the 2
products mentioned above?
Thanks
Hi,
There is no command line utility to convert the transaction log to text
file. Probably you could try the Logexplorer from Lumigent.
www.lumigent.com
Thanks
Hari
SQL Server MVP
"Jason Harrington" <Jason Harrington@.discussions.microsoft.com> wrote in
message news:A0EAB1FD-BAC2-4201-8153-695DEEF11210@.microsoft.com...
> I'm having alot of trouble reading a SQL 2000 transaction log backup file.
> As far as I can see, Microsoft do not provide such a tool and so far I've
> tried a product called "SLR" and "ApexSQL Log". Both look OK and work OK
> with
> "small" log files.
> My problem is that the file I need to look at is 581MB and both products
> just fail miserably when trying to look at these files (I think there are
> about 1.3 million transcations in this file).
> Before asking why I need to look at this file, I must stress that its
> because of problem (server running out of disk space, so log backups were
> not
> happening) that the log grew so big. Unfortunately, the site does not have
> a
> DBA and I've been called in, as this all came to light when 5 important
> tables appeared to have been "re-created" (dropped and created empty, with
> no
> indexes, etc...). That has all been recovered, but they want to know how
> this
> happened!!
> I know for a fact that this 581mb log backup contains the transactions
> that
> did the damage.
> Any other ideas will be welcome!!
> Is there not a command line utility, such simpy dumps all the info in
> these
> file to a nice readable text file?? Or should I just persevere with the
> 2
> products mentioned above?
> Thanks
|||I'm currently in discussion with a UK reseller of this product. The
evaluation version of this product only allows you to run against "northwind"
database and one of their own.
I asked if it would be able to read a 581mb file and they said if we were
interested in buying the product, they would test it for us!!!
Not really what I wanted to hear!!
I've been running SLR all day since 9.30am this morning, its now 3.30pm and
its about 75% of the way through reading it!!! We 'll see.....
Thanks for your response.
Jason
"Hari Prasad" wrote:

> Hi,
> There is no command line utility to convert the transaction log to text
> file. Probably you could try the Logexplorer from Lumigent.
> www.lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Jason Harrington" <Jason Harrington@.discussions.microsoft.com> wrote in
> message news:A0EAB1FD-BAC2-4201-8153-695DEEF11210@.microsoft.com...
>
>
|||We use Log Explorer and I've used it on much bigger log files than that with
no real issues.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jason Harrington" <JasonHarrington@.discussions.microsoft.com> wrote in
message news:907814B5-F9D3-492A-B024-E8DC00EFF538@.microsoft.com...[vbcol=seagreen]
> I'm currently in discussion with a UK reseller of this product. The
> evaluation version of this product only allows you to run against
> "northwind"
> database and one of their own.
> I asked if it would be able to read a 581mb file and they said if we were
> interested in buying the product, they would test it for us!!!
> Not really what I wanted to hear!!
> I've been running SLR all day since 9.30am this morning, its now 3.30pm
> and
> its about 75% of the way through reading it!!! We 'll see.....
> Thanks for your response.
> Jason
> "Hari Prasad" wrote: