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:

reading a transaction log

Does anyone know of a utilty that i can use to open and view the transaction
log file.
one such utility: Log Explorer from Lumigent www.lumigent.com
Keith
"Emil" <laserview@.hotmail.com> wrote in message
news:eUFUu7QaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Does anyone know of a utilty that i can use to open and view the
transaction
> log file.
>
|||Microsoft doesn't provide a tool for reading the transaction log files, but
you might want to look at Lumigent Log Explorer (www.lumigent.com) or Log
P.I. (www.logpi.com).
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Emil" <laserview@.hotmail.com> wrote in message
news:eUFUu7QaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Does anyone know of a utilty that i can use to open and view the
> transaction
> log file.
>
|||In message <#w9QtDRaEHA.2972@.TK2MSFTNGP12.phx.gbl>, Keith Kratochvil
<sqlguy.back2u@.comcast.net> writes
>one such utility: Log Explorer from Lumigent www.lumigent.com
>
Just for completeness, there are a couple more-
Log PI
(http://www.logpi.com/)
Log Navigator
http://www.apexsql.com/index_lognavigator.htm
Personally I have used Log Explorer quite happily.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Reading a text file from a stored procedure.

A rookie question - all I want to do is open a text file x.txt and read each line - no bcp or bulk insert required.
Is there a simple way to do this ?
Thanks in advance to all who reply !Not with ANSI-92 syntax. You'd have to use specific DBMS extensions for it. Pick the engine (Oracle, SQL Server, UDB, etc.) and post accordingly.|||I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply !|||I use sp_OAxxx with FileSystemObject.|||Originally posted by BrutusBuckeye
I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply ! In my opinion, this is one of the fundamental design flaws in Oracle. They are attempting to make PL/SQL a programming language instead of a data[base] manipulation language.

If you stop and think about it, reading text the way that you want to do it is a client side activity. Using BCP or BULK INSERT are server side activities. There is a fundamental difference between them (which machine the code actually runs on)!

Any solution you find for MS-SQL will involve server side activity. Sybase (now Microsoft) never intended for Transact-SQL scripts to run on the client, they always assumed that those Transact-SQL scripts would run on the server. That is exactly why user interface code, file access, etc are absent from Transact-SQL... The absence is by design.

Using Microsoft Transact-SQL, you'll need to either adopt a server centric point of view, or write your client side code using the client language. There is a clear distinction between the client and server in Transact-SQL.

-PatP|||BrutusBuckeye, Pat has a very strong opinion about all this ;)

I'd still use sp_OAxxx if you insist on reading a text file one line at a time, but why bother? Use BULK INSERT and then deal with it in a recordset-based fashion!|||UNCLE !!!! :)

Thanks for all your help. I am indeed going the Bulk Insert route.

Thanks again from the school of hard knocks.. :)|||Originally posted by rdjabarov
BrutusBuckeye, Pat has a very strong opinion about all this ;) Dang! Did I let that secret out again ?!?!

-PatP

reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
Greg Stigers, MCSA
remember to vote for the answers you like
NT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/de...classtopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>

reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
--
Greg Stigers, MCSA
remember to vote for the answers you likeNT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/d...rclasstopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>sql

Reading a record without placing a lock

Hi
> 1. Am I doing it correctly?
No. What if some user inserts/deletes the row while you are reading. You
are about to get an inconsistent data. For example
you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
so while you read page A another connection inserst the value let me say
50, but you have already read data from the page A , so it moves the all
data to a new created page so now that data looks like a)10,40 ,50,
b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
60(duplicate) from page D as well.

> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
at SNAPSHOT ISOLATION LEVEL in the BOL
<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> No. What if some user inserts/deletes the row Xwhile Xyou are reading.You
> are about to get an inconsistent data. For example
> Xyou have tree pages with data Xlike a) 10,40,60 b) 80,100,90 Xc)110,70,85 ,
> so while Xyou read page A Xanother connection inserst the value let mesay
> 50, but you have already read data Xfrom the page A , so it moves the all
> data to a new created page so now that data looks like Xa)10,40 ,50,
> b) 80,100,90 Xc)110,70,85 ,d)60 ... and Xas you keep reading Xyou get
> 60(duplicate) from page D as well.
>
> Yes , you can use TABLOCK hint Xor if you use SQL Server 2005 Xtake a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
|||<<There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).>>
The ANSI SQL Compliant way to describe how much you want to be isolated from other users is the SET
TRANSACTION ISOLATION command. SQL Server supports this, and READ UNCOMMITTED seems to do what you
want. Apparently Informix has a non-standard command named LOCK MODE, something that SQL Server do
not have. Assuming these indeed do the same thing, I support MS for using the ANSI SQL compliant
name for the command instead of some other command name. If they do not do the same, perhaps you can
enlighten un in what way they differ?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
>
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message

Reading a record without placing a lock

Dear All,
I have one server application running which continously reading and
updating a DB.
While there is a Reporting tool which generating reports.
The reports can fail, but the server application cannot. So, I need to
run a query in the Reporting tool without placing a lock on the DB
(totally transaprent to the server).
Currently this is what I am doing.
SELECT * FROM Data WITH (NOLOCK);
Question:
1. Am I doing it correctly?
2. Is there a better way of doing? Example: setting the LOCK MODE
(instead of specifying NOLOCK on every command)
Thanks in advance.<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.
Hi
You can set the transaction isolation level to read uncommitted for the
session, but then you are potentially going to have dirty reads. Other ways
to do this would be to offload the reporting database either by using log
shipping, replication, mirroring or a snapshot.
John|||Hi
> 1. Am I doing it correctly?
No. What if some user inserts/deletes the row while you are reading. You
are about to get an inconsistent data. For example
you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
so while you read page A another connection inserst the value let me say
50, but you have already read data from the page A , so it moves the all
data to a new created page so now that data looks like a)10,40 ,50,
b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
60(duplicate) from page D as well.
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
at SNAPSHOT ISOLATION LEVEL in the BOL
<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.|||Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row =A0while =A0you are reading.= You
> are about to get an inconsistent data. For example
> =A0you have tree pages with data =A0like a) 10,40,60 b) 80,100,90 =A0c)110=,70,85 ,
> so while =A0you read page A =A0another connection inserst the value let me= say
> 50, but you have already read data =A0from the page A , so it moves the al=l
> data to a new created page so now that data looks like =A0a)10,40 ,50,
> b) 80,100,90 =A0c)110,70,85 ,d)60 ... and =A0as you keep reading =A0you g=et
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint =A0or if you use SQL Server 2005 =A0take a =look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message|||<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
Hi
I can't see how unreliability and inconsistence made the user requirements
for this system!
John|||<<There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).>>
The ANSI SQL Compliant way to describe how much you want to be isolated from other users is the SET
TRANSACTION ISOLATION command. SQL Server supports this, and READ UNCOMMITTED seems to do what you
want. Apparently Informix has a non-standard command named LOCK MODE, something that SQL Server do
not have. Assuming these indeed do the same thing, I support MS for using the ANSI SQL compliant
name for the command instead of some other command name. If they do not do the same, perhaps you can
enlighten un in what way they differ?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message

Reading a long string

Hi there,
How can i see what a long string value is? I have a stored procedure which
creates a dynamic xml string and when i select it at the end of the SP the
result is about 100 chars long although i know it is about 7000 long and I
need to inspect the whole string, put it in an xml reader interface etc to
play around with. Even if i send the result to file, it is still truncated.
How can I read the whole string?
thanksWhat version of SQL are you using? XML data needs an XML client.
In SQL 2005 there's a built-in reader, that can display the XML in humanly
readable form, but for SQL 2000 you'd need a client application - look into
using SQLXML.
ML
http://milambda.blogspot.com/|||"louise raisbeck" <louiseraisbeck@.discussions.microsoft.com> wrote in
message news:B8A6F36D-818D-44AB-A992-95744A9E1056@.microsoft.com...
> Hi there,
> How can i see what a long string value is? I have a stored procedure which
> creates a dynamic xml string and when i select it at the end of the SP the
> result is about 100 chars long although i know it is about 7000 long and I
> need to inspect the whole string, put it in an xml reader interface etc to
> play around with. Even if i send the result to file, it is still
> truncated.
> How can I read the whole string?
> thanks
If this is SQL 2k and you are using Query Analyser, ensure that you have
set your results length long enough.
Look at Tools->Options Choose Results tab, then put 8000 in the box for
Maximum Characters Per Column
Rick Sawtell
MCT, MCSD, MCDBA|||THANKS!!! i didnt realise you could set this option.
In response to ML - I am creating my own custom xml string. I wont go into
reasons but in a nutshell it is completely dynamic, not as simple as return
this resultset as xml. I then use that as an xml datasource in a .net page.
Regards
"Rick Sawtell" wrote:

> "louise raisbeck" <louiseraisbeck@.discussions.microsoft.com> wrote in
> message news:B8A6F36D-818D-44AB-A992-95744A9E1056@.microsoft.com...
> If this is SQL 2k and you are using Query Analyser, ensure that you have
> set your results length long enough.
> Look at Tools->Options Choose Results tab, then put 8000 in the box for
> Maximum Characters Per Column
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Reading a log file

Hi all,
Is it possible to read a log file to see what changes have been done on a
particular table at a particular time? And if yes, how?
Thanks,
Ivan> Is it possible to read a log file to see what changes have been done on a
> particular table at a particular time? And if yes, how?
http://www.aspfaq.com/2449|||Keep in mind that transaction logs are truncated during backups.
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OsmBwZfjFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> Is it possible to read a log file to see what changes have been done on a
> particular table at a particular time? And if yes, how?
> Thanks,
> Ivan
>

Reading a flat file

I get the following error when reading a flat file : [Credit Information 1 [1]] Error: Data conversion failed. The data conversion for column "AccountName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

I did check all the mappings, and everything seems to be fine, the field is read in as a string. I also check for any strange characters that can possibly cause this error but the value of the field only contains a person's name and spaces at the end.

Does anyone have any ideas what might be the cause of the error?What is the source data type of the AccountName field?

What is the data type of the mapped field in SSIS?

What code page are you working with? 1252?sql

Reading a Filename which has a Wildcard

Hi - I need to read a file but I do not know the whole filename!

For example, I need to read a file whose name is:- Finance_200510.xls

I know the prefix is "Finance" and I know the suffix is ".xls", but the bit in the middle could be anything. In the above example, it is a date which will vary from month to month.

I could achieve this as follows:-

1. I already need a .CMD file which contains a DTEXEC command.
2. So, I could insert the following line before the DTEXEC command:-
DIR Finance*.xls > ListFile.txt
(Alternatively, this command could be invoked from within the Package)
3. I could read the file: ListFile.txt using a SSIS Script Component
and extract the first occurrence of the string starting with "Finance"
and ending with ".xls" and save the filename to a package variable: @.[user::filename]
4. The package variable @.[user::filename] could be used in the expression property of a Connection Manager
5. The Connection Manager could then read the file.
6. Optionally, I could generate a RENAME command using a Script Component to rename Finance_200510.xls to Finance_200510.done so that it is not re-processed.

Can anybody suggest a simpler solution, please?!

Thanks.You can use multiFlatfile connection to provide file name with wildcard. In your case, you will have to make sure that there are no other unwanted files in that folder which might match the wildcard you are using. This will eliminate the need for listing all the files in the folder, and use script component to get the file whose name starts with "Finance". In your multiflatfile connection, you can just use Finance*.xls, and it will catch your needed file.

See BOL topic
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/extran9/html/31fc3f7a-d323-44f5-a907-1fa3de66631a.htm
for more on Multiple Flat File Connection Manager. The ideal use for Multiple flat file connection manager is to process more than one file which are of the same format (instead of using For Each Loop), but it will also solve your purpose of choosing file with Name that matches a wildcard.

HTH,
Ranjeeta Nanda|||Or similarly, you could use the Foreach Loop with the For Each File enumerator and a file pattern of "Finance*.xls."

If you really really wanted the exact name of the file before you started processing, or needed to check for other conditions, you could use the System.IO namespace within a Script Task to retrieve files that match the pattern, make sure there's only one, and pop the filename into a package variable for use by the next task.

-Doug

Reading a file using UTL_FILE.

Hai,

I am working in an IBM AIX machine, with Oracle 8i.
I am trying to read a file through UTL_FILE read.
The directory which has the file does not have any permisions for the
others. It is restricted to 750.
The file belongs to a local user and group.

So, as workaround we added our local user to the secondary group of oracle, so that oracle user also has the same access permissions as the local user. But still we were not able to read the file from the procedure.

Do we need to restart oracle ?
will be happy if anyone can advice.Is your UTL_FILE_DIR parameter correct in init.ora ?

If not add it and restart database

Originally posted by Anandraj
Hai,

I am working in an IBM AIX machine, with Oracle 8i.
I am trying to read a file through UTL_FILE read.
The directory which has the file does not have any permisions for the
others. It is restricted to 750.
The file belongs to a local user and group.

So, as workaround we added our local user to the secondary group of oracle, so that oracle user also has the same access permissions as the local user. But still we were not able to read the file from the procedure.

Do we need to restart oracle ?
will be happy if anyone can advice.|||Originally posted by ndu35
Is your UTL_FILE_DIR parameter correct in init.ora ?

If not add it and restart database

Hai ndu35,

Thanks for that suggestion. Anyway the entry is already present in init.ora. Also the database was restarted immediately.

Reading a file name that changes

Brand new to SSIS so bear with me, if something is obvious.

I want to be able to read a file from a certain directory. But the file name changes every day. So today its File20061203 tomorrow File20061304 or the next day it could be FileNB4434. The format in the file will always be the same though. I just want for a user to be able to drop a file in a directory and the package pick it up once a day.

Would I have to to create a script task or could I use a variable. I have been trying to use the variable but have not been able to get them to work. This calls for only looking for 1 text file in a folder but any additional links that show some good variable examples would be appreciated. One where only part of the variable changes File(Variable)Division.txt

Thanks in advance

Try using the ForEach Loop Container with a file enumerator. That will store the file name in a variable you specify. Set the connect string on the File connection manager by using an expression (@.User:<name of your variable>).

Normally the Loop container is used when you have a number of files, but it works just fine with one.

Reading a field from Cursor

Hi,
I have a cursor that reads a table. During the reading of the table I want
to read e "field" directly (Not usign FETCH NEXT FROM authors_cursors INTO
@.col1, @.col2).
Thanks!claude81 wrote:
> Hi,
> I have a cursor that reads a table. During the reading of the table I
> want to read e "field" directly (Not usign FETCH NEXT FROM
> authors_cursors INTO @.col1, @.col2).
>
You have to FETCH NEXT INTO. There is no other way.
Cursors should be a last resort.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thaks!!
"Bob Barrows [MVP]" wrote:

> claude81 wrote:
> You have to FETCH NEXT INTO. There is no other way.
> Cursors should be a last resort.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Reading a directory and submitting multiple TSQL scripts

I need to come up with a mechanism that will allow me to automatically submi
t
multiple TSQL scripts residing in directory structure. I tried to write a
command script but found out that osql.exe does not allow for variabalizatio
n
of most of its switches. Without being able to read variables into -d -q
switches of osql.exe I can't automate it. Can anyone help?
Oscar Elleseff
Oscar.Elleseff@.eclipsys.comNot sure I understand everything...
Are you using batch files?
Here is one I use that accepts variable values
If you need ehhence variable functionnality I suggest you take a look at
SQLCMD.exe that come for free with SQLEXPRESS and SQL Server 2005
It has the new switch -v for passing variables at the command line to
replace into script syntax within the script is $(VarName)
@.echo off
rem !!! Only compatible with SQL Server 2005 or SQLExpress !!!
rem !!! User running the install must have admin priviledge !!!
rem %1 is the SQLExpress server instance name
rem %2 is the database name
set i=%1
set d=%2
if "%1" == "" set i=%COMPUTERNAME%\SQLEXPRESS
if "%2" == "" set d=AVDBTOOLS
rem start logging
echo Starting installation ...
echo Starting installation ... > INSTALL.log
echo ***** >> INSTALL.log
echo TODO should be checking version to install on
echo TODO should be checking version to install on >> INSTALL.log
echo ***** >> INSTALL.log
echo checking if %i% SQL Server instance name exists
echo checking if %i% SQL Server instance name exists > INSTALL.log
osql -E -S%i% -dmaster -Q"set nocount on select srvname+' exists' from
master.dbo.sysservers where srvid = 0" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO badcon
echo ***** >> INSTALL.log
echo checking if %d% database exists and if not will create it
echo checking if %d% database exists and if not will create it >> INSTALL.l
og
osql -E -S%i% -dmaster -Q"set nocount on IF NOT EXISTS(SELECT 1 FROM
master.dbo.sysdatabases WHERE name = '%d%') CREATE DATABASE [%d%]" -n -h
-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO baddb
echo ***** >> INSTALL.log
echo Creating TEMPOBJECTS table
echo Creating TEMPOBJECTS table >> INSTALL.log
osql -E -S%i% -d%d% -i"TEMPOBJECTS.tab" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get home folder
echo Get home folder >> INSTALL.log
cd > HOME.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load Home folder to TEMPOBJECTS
echo Load Home folder to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "HOME.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Check if current installation script path matches given %i%\%d%
parameters
echo Check if current installation script path matches given %i%\%d%
parameters >> INSTALL.log
osql -E -S%i% -d%d% -Q"if (select Result from dbo.TEMPOBJECTS) not like
'%%%i%\%d%' raiserror('Script path does not match local installation.
%i%\%d%',16,1)" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_CheckDirExist
echo Creating dbtsp_CheckDirExist >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_CheckDirExist.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_validateConnection
echo Creating dbtsp_validateConnection >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_validateConnection.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitServer
echo Creating dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitServer.prc" -n -h-1 -w8000 -b >> INSTALL.lo
g
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitServer
echo Running dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_InitServer" -n -h-1 -w8000 -b >> INSTALL.l
og
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitConstant
echo Creating dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitConstant.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitConstant
echo Running dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on DECLARE @.Home nvarchar(256) select
@.home = Result from dbo.TEMPOBJECTS exec dbtsp_InitConstant @.Home = @.Home" -
n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Cleaning up TEMPOBJECTS
echo Cleaning up TEMPOBJECTS >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on delete from dbo.TEMPOBJECTS" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get procedure list
echo Get procedure list >> INSTALL.log
dir /B *.prc > PROCLIST.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load procedure list to TEMPOBJECTS
echo Load procedure list to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "PROCLIST.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_RunExtScript
echo Creating dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_RunExtScript.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_RunExtScript
echo Running dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_RunExtScript @.DBName = '%d%'" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_CreateSchedule
echo Running dbtsp_CreateSchedule >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_CreateSchedule" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Loading known server compatible version scripts
echo Loading known server compatible version scripts >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtScript @.ServerName =
'%i%',@.Refresh = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing Server level tasks
echo Initializing Server level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing database level tasks
echo Initializing database level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w800
0
-b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo initializing objects level tasks
echo initializing objects level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadAllBatch @.DBMS_Name = NULL,
@.ServerName = '%i%', @.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1" -n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Install completed successfully.
echo Install completed successfully. >> INSTALL.log
goto end
:badcon
echo ***** >> INSTALL.log
echo Could not connect to SQL Server.
echo If SQLExpress, verify that SQL Browser service is automatically started
echo and that tcpip protocol is activated.
echo Could not connect to SQL Server. >> INSTALL.log
echo If SQLExpress, verify that SQL Browser service is automatically started
echo and that tcpip protocol is activated. >> INSTALL.log
goto bad
:baddb
echo ***** >> INSTALL.log
echo Could not create database. See error for more detail.
echo Could not create database. See error for more detail. >> INSTALL.log
goto bad
:bad
echo ***** >> INSTALL.log
echo Unsuccessful. Install did not complete. See log file.
echo Unsuccessful. Install did not complete. See log file. >> INSTALL.log
:end
echo ***** >> INSTALL.log
-- Good Luck
Andre
"Oscar" wrote:
[vbcol=seagreen]
> I need to come up with a mechanism that will allow me to automatically sub
mit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalizat
ion
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.com|||you may want to check out DB Ghost which has a builder that can process
scripts by directory at approx 1000 per minute. http://www.dbghost.com
"Oscar" wrote:

> I need to come up with a mechanism that will allow me to automatically sub
mit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalizat
ion
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.comsql

Reading a directory and submitting multiple TSQL scripts

I need to come up with a mechanism that will allow me to automatically submit
multiple TSQL scripts residing in directory structure. I tried to write a
command script but found out that osql.exe does not allow for variabalization
of most of its switches. Without being able to read variables into -d -q
switches of osql.exe I can't automate it. Can anyone help?
Oscar Elleseff
Oscar.Elleseff@.eclipsys.comNot sure I understand everything...
Are you using batch files?
Here is one I use that accepts variable values
If you need ehhence variable functionnality I suggest you take a look at
SQLCMD.exe that come for free with SQLEXPRESS and SQL Server 2005
It has the new switch -v for passing variables at the command line to
replace into script syntax within the script is $(VarName)
@.echo off
rem !!! Only compatible with SQL Server 2005 or SQLExpress !!!
rem !!! User running the install must have admin priviledge !!!
rem %1 is the SQLExpress server instance name
rem %2 is the database name
set i=%1
set d=%2
if "%1" == "" set i=%COMPUTERNAME%\SQLEXPRESS
if "%2" == "" set d=AVDBTOOLS
rem start logging
echo Starting installation ...
echo Starting installation ... > INSTALL.log
echo ***** >> INSTALL.log
echo TODO should be checking version to install on
echo TODO should be checking version to install on >> INSTALL.log
echo ***** >> INSTALL.log
echo checking if %i% SQL Server instance name exists
echo checking if %i% SQL Server instance name exists > INSTALL.log
osql -E -S%i% -dmaster -Q"set nocount on select srvname+' exists' from
master.dbo.sysservers where srvid = 0" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO badcon
echo ***** >> INSTALL.log
echo checking if %d% database exists and if not will create it
echo checking if %d% database exists and if not will create it >> INSTALL.log
osql -E -S%i% -dmaster -Q"set nocount on IF NOT EXISTS(SELECT 1 FROM
master.dbo.sysdatabases WHERE name = '%d%') CREATE DATABASE [%d%]" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO baddb
echo ***** >> INSTALL.log
echo Creating TEMPOBJECTS table
echo Creating TEMPOBJECTS table >> INSTALL.log
osql -E -S%i% -d%d% -i"TEMPOBJECTS.tab" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get home folder
echo Get home folder >> INSTALL.log
cd > HOME.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load Home folder to TEMPOBJECTS
echo Load Home folder to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "HOME.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Check if current installation script path matches given %i%\%d%
parameters
echo Check if current installation script path matches given %i%\%d%
parameters >> INSTALL.log
osql -E -S%i% -d%d% -Q"if (select Result from dbo.TEMPOBJECTS) not like
'%%%i%\%d%' raiserror('Script path does not match local installation.
%i%\%d%',16,1)" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_CheckDirExist
echo Creating dbtsp_CheckDirExist >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_CheckDirExist.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_validateConnection
echo Creating dbtsp_validateConnection >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_validateConnection.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitServer
echo Creating dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitServer.prc" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitServer
echo Running dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_InitServer" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitConstant
echo Creating dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitConstant.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitConstant
echo Running dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on DECLARE @.Home nvarchar(256) select
@.home = Result from dbo.TEMPOBJECTS exec dbtsp_InitConstant @.Home = @.Home" -n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Cleaning up TEMPOBJECTS
echo Cleaning up TEMPOBJECTS >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on delete from dbo.TEMPOBJECTS" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get procedure list
echo Get procedure list >> INSTALL.log
dir /B *.prc > PROCLIST.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load procedure list to TEMPOBJECTS
echo Load procedure list to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "PROCLIST.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_RunExtScript
echo Creating dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_RunExtScript.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_RunExtScript
echo Running dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_RunExtScript @.DBName = '%d%'" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_CreateSchedule
echo Running dbtsp_CreateSchedule >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_CreateSchedule" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Loading known server compatible version scripts
echo Loading known server compatible version scripts >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtScript @.ServerName ='%i%',@.Refresh = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing Server level tasks
echo Initializing Server level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing database level tasks
echo Initializing database level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w8000
-b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo initializing objects level tasks
echo initializing objects level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadAllBatch @.DBMS_Name = NULL,
@.ServerName = '%i%', @.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1" -n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Install completed successfully.
echo Install completed successfully. >> INSTALL.log
goto end
:badcon
echo ***** >> INSTALL.log
echo Could not connect to SQL Server.
echo If SQLExpress, verify that SQL Browser service is automatically started
echo and that tcpip protocol is activated.
echo Could not connect to SQL Server. >> INSTALL.log
echo If SQLExpress, verify that SQL Browser service is automatically started
>> INSTALL.log
echo and that tcpip protocol is activated. >> INSTALL.log
goto bad
:baddb
echo ***** >> INSTALL.log
echo Could not create database. See error for more detail.
echo Could not create database. See error for more detail. >> INSTALL.log
goto bad
:bad
echo ***** >> INSTALL.log
echo Unsuccessful. Install did not complete. See log file.
echo Unsuccessful. Install did not complete. See log file. >> INSTALL.log
:end
echo ***** >> INSTALL.log
-- Good Luck
Andre
"Oscar" wrote:
> I need to come up with a mechanism that will allow me to automatically submit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalization
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.com|||you may want to check out DB Ghost which has a builder that can process
scripts by directory at approx 1000 per minute. http://www.dbghost.com
"Oscar" wrote:
> I need to come up with a mechanism that will allow me to automatically submit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalization
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.com

Reading a directory and submitting multiple TSQL scripts

I need to come up with a mechanism that will allow me to automatically submit
multiple TSQL scripts residing in directory structure. I tried to write a
command script but found out that osql.exe does not allow for variabalization
of most of its switches. Without being able to read variables into -d -q
switches of osql.exe I can't automate it. Can anyone help?
Oscar Elleseff
Oscar.Elleseff@.eclipsys.com
Not sure I understand everything...
Are you using batch files?
Here is one I use that accepts variable values
If you need ehhence variable functionnality I suggest you take a look at
SQLCMD.exe that come for free with SQLEXPRESS and SQL Server 2005
It has the new switch -v for passing variables at the command line to
replace into script syntax within the script is $(VarName)
@.echo off
rem !!! Only compatible with SQL Server 2005 or SQLExpress !!!
rem !!! User running the install must have admin priviledge !!!
rem %1 is the SQLExpress server instance name
rem %2 is the database name
set i=%1
set d=%2
if "%1" == "" set i=%COMPUTERNAME%\SQLEXPRESS
if "%2" == "" set d=AVDBTOOLS
rem start logging
echo Starting installation ...
echo Starting installation ... > INSTALL.log
echo ***** >> INSTALL.log
echo TODO should be checking version to install on
echo TODO should be checking version to install on >> INSTALL.log
echo ***** >> INSTALL.log
echo checking if %i% SQL Server instance name exists
echo checking if %i% SQL Server instance name exists > INSTALL.log
osql -E -S%i% -dmaster -Q"set nocount on select srvname+' exists' from
master.dbo.sysservers where srvid = 0" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO badcon
echo ***** >> INSTALL.log
echo checking if %d% database exists and if not will create it
echo checking if %d% database exists and if not will create it >> INSTALL.log
osql -E -S%i% -dmaster -Q"set nocount on IF NOT EXISTS(SELECT 1 FROM
master.dbo.sysdatabases WHERE name = '%d%') CREATE DATABASE [%d%]" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO baddb
echo ***** >> INSTALL.log
echo Creating TEMPOBJECTS table
echo Creating TEMPOBJECTS table >> INSTALL.log
osql -E -S%i% -d%d% -i"TEMPOBJECTS.tab" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get home folder
echo Get home folder >> INSTALL.log
cd > HOME.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load Home folder to TEMPOBJECTS
echo Load Home folder to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "HOME.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Check if current installation script path matches given %i%\%d%
parameters
echo Check if current installation script path matches given %i%\%d%
parameters >> INSTALL.log
osql -E -S%i% -d%d% -Q"if (select Result from dbo.TEMPOBJECTS) not like
'%%%i%\%d%' raiserror('Script path does not match local installation.
%i%\%d%',16,1)" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_CheckDirExist
echo Creating dbtsp_CheckDirExist >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_CheckDirExist.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_validateConnection
echo Creating dbtsp_validateConnection >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_validateConnection.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitServer
echo Creating dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitServer.prc" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitServer
echo Running dbtsp_InitServer >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_InitServer" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_InitConstant
echo Creating dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_InitConstant.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_InitConstant
echo Running dbtsp_InitConstant >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on DECLARE @.Home nvarchar(256) select
@.home = Result from dbo.TEMPOBJECTS exec dbtsp_InitConstant @.Home = @.Home" -n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Cleaning up TEMPOBJECTS
echo Cleaning up TEMPOBJECTS >> INSTALL.log
osql -E -S%i% -d%d% -Q"set nocount on delete from dbo.TEMPOBJECTS" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Get procedure list
echo Get procedure list >> INSTALL.log
dir /B *.prc > PROCLIST.bcp
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Load procedure list to TEMPOBJECTS
echo Load procedure list to TEMPOBJECTS >> INSTALL.log
bcp "%d%.dbo.TEMPOBJECTS" in "PROCLIST.bcp" -S%i% -T -c >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Creating dbtsp_RunExtScript
echo Creating dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -i"dbtsp_RunExtScript.prc" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_RunExtScript
echo Running dbtsp_RunExtScript >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_RunExtScript @.DBName = '%d%'" -n -h-1
-w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Running dbtsp_CreateSchedule
echo Running dbtsp_CreateSchedule >> INSTALL.log
osql -E -S%i% -d%d% -Q"EXEC dbtsp_CreateSchedule" -n -h-1 -w8000 -b >>
INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Loading known server compatible version scripts
echo Loading known server compatible version scripts >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtScript @.ServerName =
'%i%',@.Refresh = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing Server level tasks
echo Initializing Server level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Initializing database level tasks
echo Initializing database level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadExtBatch @.ServerName = '%i%',
@.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1, @.init = 1" -n -h-1 -w8000
-b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo initializing objects level tasks
echo initializing objects level tasks >> INSTALL.log
osql -E -S%i% -d%d% -Q"exec dbtsp_LoadAllBatch @.DBMS_Name = NULL,
@.ServerName = '%i%', @.DatabaseName = '%d%', @.Refresh = 2, @.Schedule = 1" -n
-h-1 -w8000 -b >> INSTALL.log
IF ERRORLEVEL == 1 GOTO bad
echo ***** >> INSTALL.log
echo Install completed successfully.
echo Install completed successfully. >> INSTALL.log
goto end
:badcon
echo ***** >> INSTALL.log
echo Could not connect to SQL Server.
echo If SQLExpress, verify that SQL Browser service is automatically started
echo and that tcpip protocol is activated.
echo Could not connect to SQL Server. >> INSTALL.log
echo If SQLExpress, verify that SQL Browser service is automatically started[vbcol=seagreen]
echo and that tcpip protocol is activated. >> INSTALL.log
goto bad
:baddb
echo ***** >> INSTALL.log
echo Could not create database. See error for more detail.
echo Could not create database. See error for more detail. >> INSTALL.log
goto bad
:bad
echo ***** >> INSTALL.log
echo Unsuccessful. Install did not complete. See log file.
echo Unsuccessful. Install did not complete. See log file. >> INSTALL.log
:end
echo ***** >> INSTALL.log
-- Good Luck
Andre
"Oscar" wrote:

> I need to come up with a mechanism that will allow me to automatically submit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalization
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.com
|||you may want to check out DB Ghost which has a builder that can process
scripts by directory at approx 1000 per minute. http://www.dbghost.com
"Oscar" wrote:

> I need to come up with a mechanism that will allow me to automatically submit
> multiple TSQL scripts residing in directory structure. I tried to write a
> command script but found out that osql.exe does not allow for variabalization
> of most of its switches. Without being able to read variables into -d -q
> switches of osql.exe I can't automate it. Can anyone help?
> Oscar Elleseff
> Oscar.Elleseff@.eclipsys.com