How to insert the date

Dear all,
I got a fieldA as datetime (length 8).
When I select in query analyzer, it shows the following value
: 2003-10-27 11:36:27.640
now I get fieldA into a ADODB.recordset in VB with fields
rs_A("fieldA")
And I try to update another table with Field (FieldB) Field B
is also datetime with length 8)
with rs_A ("fieldA") i.e. update tableA set FieldB =
rs_A("fieldA")
The final update statement is query analyzer is :
update tableA set FieldB = 2003-10-27 11:36:27.640
The query analyzer complains incorrect syntax near 11:36 . It
cannot be updated.
How can I insert to FieldB with the complete date and time from
FieldA. Is that something like
update tableA set FieldB = '2003-10-27 11:36:27.640' or
update tableA set FieldB = format(2003-10-27 11:36:27.640)
Please help.
Thanks.hon123456 a écrit :
> Dear all,
> I got a fieldA as datetime (length 8).
> When I select in query analyzer, it shows the following value
> : 2003-10-27 11:36:27.640
> now I get fieldA into a ADODB.recordset in VB with fields
> rs_A("fieldA")
> And I try to update another table with Field (FieldB) Field B
> is also datetime with length 8)
> with rs_A ("fieldA") i.e. update tableA set FieldB =
> rs_A("fieldA")
> The final update statement is query analyzer is :
> update tableA set FieldB = 2003-10-27 11:36:27.640
> The query analyzer complains incorrect syntax near 11:36 . It
> cannot be updated.
> How can I insert to FieldB with the complete date and time from
> FieldA. Is that something like
> update tableA set FieldB = '2003-10-27 11:36:27.640' or
> update tableA set FieldB = format(2003-10-27 11:36:27.640)
1) there si no format about DATETIME SQL type storage it is just
DATETIME (in fact two integers)
2) when casting a string to a DATETIME the only format that is universal
is the ISO short DATETIME format which is :
YYYYMMDD HH:MM:SS.nnn
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose after
4) if you want to use exotic formats about DATETIME as string, set your
session dateformat parameter as above :
SET DATEFORMAT { ymd | mdy | dmy | ydm | myd | ymd }
5) prefer always use a explicit CAST like :
SET DATEFORMAT myd
SELECT CAST('03199722 00:11:22.333' AS DATETIME)
...
A +

> Please help.
> Thanks.
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks SQLpro,
For my query : update tableA set FieldB = 2003-10-27
11:36:27.640
Query Analyzer complains incorrect syntax near 11. What can I do to
make this query work?
Thanks.|||Thanks SQLpro,
Sorry to post again. I do not understand your point 3
suggestion
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose
after
Is that mean I need to change the query as follows:
update tableA set FieldB = 2003-10-27-11:36:27.640
Thanks.|||On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Is that mean I need to change the query as follows:
>update tableA set FieldB = 2003-10-27-11:36:27.640
Put quotes around the date string, and remove the dash between the
date and time:
update tableA set FieldB = '2003-10-27 11:36:27.640'
Roy Harvey
Beacon Falls, CT|||> Put quotes around the date string, and remove the dash between the
> date and time:
... and better yet, put a T between the date and a time part, making int th
e ISO 8601 format which
isn't dependent on any language setting for the login, etc.
(Hi, Roy! I'm happy to see you here :-) .)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:h3lt42hna2q4odv454dpujnrp1hib49od6@.
4ax.com...
> On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
> wrote:
>
> Put quotes around the date string, and remove the dash between the
> date and time:
> update tableA set FieldB = '2003-10-27 11:36:27.640'
> Roy Harvey
> Beacon Falls, CT

How to insert primary keys without using identity

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

AndreIt is possible with cursors, but would be awkward and I expect subject to problems if the table was having lots of records added by lots of users at the same time...

Why do you not want to use Autoincrement?

How to insert data with dollar ($) first with sqlxml ?

Hi,
I wish to insert the following data into an sql table using sqlxml. All
columns in the table are varchar. The crafted xml is as follow:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync><updg:after>
<temp_Outstanding drawdownApp="LOA"
drawdownId="XYZ"
facilityId="$J5EZEFN">
</temp_Outstanding>
</updg:after></updg:sync>
</ROOT>
But this lamely fail with the following error: Invalid XML elements found
inside sync block (HRESULT="0x80004005").
The code to perform the update is as follow:
SqlXmlCommand cmd = new SqlXmlCommand(conn);
cmd.CommandType = SqlXmlCommandType.UpdateGram;
cmd.CommandText = content;
stm = cmd.ExecuteStream();
The trouble seems to come from the $J5EZEFN field. Is there a way to insert
something into a table that begin with a dollar ($) sign ?
It seems that $XXX is used to pass parameters - but, well, I do not want to
pass parameter. I just want to insert a data with a dollar ($) first.
Is there a way to do this without using stored procedure and altering the
data ?
Thanks,
- Pierre
A brief discussion on handling "invalid XML data" is covered here:
http://msdn.microsoft.com/library/de...egram_375f.asp
Check section:
C. Dealing with valid SQL Server characters that are not valid in XML
- good luck,
Geoff -
"Pierre CHALAMET" wrote:

> Hi,
> I wish to insert the following data into an sql table using sqlxml. All
> columns in the table are varchar. The crafted xml is as follow:
> <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <updg:sync><updg:after>
> <temp_Outstanding drawdownApp="LOA"
> drawdownId="XYZ"
> facilityId="$J5EZEFN">
> </temp_Outstanding>
> </updg:after></updg:sync>
> </ROOT>
> But this lamely fail with the following error: Invalid XML elements found
> inside sync block (HRESULT="0x80004005").
> The code to perform the update is as follow:
> SqlXmlCommand cmd = new SqlXmlCommand(conn);
> cmd.CommandType = SqlXmlCommandType.UpdateGram;
> cmd.CommandText = content;
> stm = cmd.ExecuteStream();
>
> The trouble seems to come from the $J5EZEFN field. Is there a way to insert
> something into a table that begin with a dollar ($) sign ?
> It seems that $XXX is used to pass parameters - but, well, I do not want to
> pass parameter. I just want to insert a data with a dollar ($) first.
> Is there a way to do this without using stored procedure and altering the
> data ?
> Thanks,
> - Pierre
>
|||XXX is perfectly valid in xml and even encoding this as &x36; does not solve
any problem. I strongly believe that $XYZ is reserved for parameter.
Is there a way to disable parameters in this kind of xml ? I just want to
know how to espace dollar ($) when used as 1st char in a field.
- Pierre
"Geoff Ely" wrote:
[vbcol=seagreen]
> A brief discussion on handling "invalid XML data" is covered here:
> http://msdn.microsoft.com/library/de...egram_375f.asp
> Check section:
> C. Dealing with valid SQL Server characters that are not valid in XML
> - good luck,
> Geoff -
> "Pierre CHALAMET" wrote:
|||The problem is that when the Updategram does not have an associated
mapping-schema, the Updategram processor treats the "$" sign as a special
currency symbol and expects a numerical value after it.
As far as I know the workaround is to use a mapping schema.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided "AS IS" and confers on rights or warranties
"Pierre CHALAMET" <PierreCHALAMET@.discussions.microsoft.com> wrote in
message news:B82E01B2-B043-4E32-9048-ADF49C469805@.microsoft.com...[vbcol=seagreen]
> XXX is perfectly valid in xml and even encoding this as &x36; does not
> solve
> any problem. I strongly believe that $XYZ is reserved for parameter.
> Is there a way to disable parameters in this kind of xml ? I just want to
> know how to espace dollar ($) when used as 1st char in a field.
> - Pierre
>
>
> "Geoff Ely" wrote:
|||I think I could not provide a mapping schema. The reason is that I'm using
BizTalk Server 2004 with an SQL send port (which does use sqlxml behind the
scene).
Since I'm having a bug with an orchestration because of a bad placed dollar
I sadly thought this could be solved easily with the understanding of sqlxml
behaviour.
I'll go back to biztalk server newsgroup so...
- Pierre

How to insert data with dollar ($) first with sqlxml ?

Hi,
I wish to insert the following data into an sql table using sqlxml. All
columns in the table are varchar. The crafted xml is as follow:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync><updg:after>
<temp_Outstanding drawdownApp="LOA"
drawdownId="XYZ"
facilityId="$J5EZEFN">
</temp_Outstanding>
</updg:after></updg:sync>
</ROOT>
But this lamely fail with the following error: Invalid XML elements found
inside sync block (HRESULT="0x80004005").
The code to perform the update is as follow:
SqlXmlCommand cmd = new SqlXmlCommand(conn);
cmd.CommandType = SqlXmlCommandType.UpdateGram;
cmd.CommandText = content;
stm = cmd.ExecuteStream();
The trouble seems to come from the $J5EZEFN field. Is there a way to insert
something into a table that begin with a dollar ($) sign ?
It seems that $XXX is used to pass parameters - but, well, I do not want to
pass parameter. I just want to insert a data with a dollar ($) first.
Is there a way to do this without using stored procedure and altering the
data ?
Thanks,
- PierreA brief discussion on handling "invalid XML data" is covered here:
http://msdn.microsoft.com/library/d...
egram_375f.asp
Check section:
C. Dealing with valid SQL Server characters that are not valid in XML
- good luck,
Geoff -
"Pierre CHALAMET" wrote:

> Hi,
> I wish to insert the following data into an sql table using sqlxml. All
> columns in the table are varchar. The crafted xml is as follow:
> <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <updg:sync><updg:after>
> <temp_Outstanding drawdownApp="LOA"
> drawdownId="XYZ"
> facilityId="$J5EZEFN">
> </temp_Outstanding>
> </updg:after></updg:sync>
> </ROOT>
> But this lamely fail with the following error: Invalid XML elements found
> inside sync block (HRESULT="0x80004005").
> The code to perform the update is as follow:
> SqlXmlCommand cmd = new SqlXmlCommand(conn);
> cmd.CommandType = SqlXmlCommandType.UpdateGram;
> cmd.CommandText = content;
> stm = cmd.ExecuteStream();
>
> The trouble seems to come from the $J5EZEFN field. Is there a way to inser
t
> something into a table that begin with a dollar ($) sign ?
> It seems that $XXX is used to pass parameters - but, well, I do not want t
o
> pass parameter. I just want to insert a data with a dollar ($) first.
> Is there a way to do this without using stored procedure and altering the
> data ?
> Thanks,
> - Pierre
>|||XXX is perfectly valid in xml and even encoding this as &x36; does not solve
any problem. I strongly believe that $XYZ is reserved for parameter.
Is there a way to disable parameters in this kind of xml ? I just want to
know how to espace dollar ($) when used as 1st char in a field.
- Pierre
"Geoff Ely" wrote:
> A brief discussion on handling "invalid XML data" is covered here:
> http://msdn.microsoft.com/library/d...tegram_375f.asp
> Check section:
> C. Dealing with valid SQL Server characters that are not valid in XML
> - good luck,
> Geoff -
> "Pierre CHALAMET" wrote:
>|||The problem is that when the Updategram does not have an associated
mapping-schema, the Updategram processor treats the "$" sign as a special
currency symbol and expects a numerical value after it.
As far as I know the workaround is to use a mapping schema.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided "AS IS" and confers on rights or warranties
"Pierre CHALAMET" <PierreCHALAMET@.discussions.microsoft.com> wrote in
message news:B82E01B2-B043-4E32-9048-ADF49C469805@.microsoft.com...
> XXX is perfectly valid in xml and even encoding this as &x36; does not
> solve
> any problem. I strongly believe that $XYZ is reserved for parameter.
> Is there a way to disable parameters in this kind of xml ? I just want to
> know how to espace dollar ($) when used as 1st char in a field.
> - Pierre
>
>
> "Geoff Ely" wrote:
>|||I think I could not provide a mapping schema. The reason is that I'm using
BizTalk Server 2004 with an SQL send port (which does use sqlxml behind the
scene).
Since I'm having a bug with an orchestration because of a bad placed dollar
I ly thought this could be solved easily with the understanding of sqlxml
behaviour.
I'll go back to biztalk server newsgroup so...
- Pierre

How to INSERT a string that contains single-quotes?

My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great':

UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503'

Here's the error I get when I try this code in SQL Server:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work.

Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox:

string strUpdate = "UPDATE Comments SET Comment='";
strUpdate = strUpdate + FCKeditor1.Value;
//strUpdate = strUpdate + ThisUserCommentTextBox.Text;
strUpdate = strUpdate + "' WHERE UserID='";
strUpdate = strUpdate + (string)Session["UserID"];
strUpdate = strUpdate + "'";
strUpdate = strUpdate + " AND GameID='";
strUpdate = strUpdate + Request.QueryString["GameID"];
strUpdate = strUpdate + "'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

I'm using SQL Server 2005 and ASP.NET 2.0

Much thanks

ok i wont go through your code...but i can tell you the key point

try run

SELECT 'AAAA' SQL retuen AAAA

SELECT '''AAAA''' SQL return 'AAAA' (you need 3 * ' + AAAA + 3 * ' )

hope this give u idea

|||

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

|||

limno:

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

This is the best advice you will get today. If you value your applications you will never ever build an Sql statement that way again

string strUpdate = "UPDATE Comments SET Comment= @.Comment WHERE UserID = @.UserID AND GameID = @.GameID'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

myCommand.Parameters.AddWithValue( "@.Comment", FCKeditor1.Value );
myCommand.Parameters.AddWithValue( "@.UserID", Session["UserID"] );
myCommand.Parameters.AddWithValue( "@.GameID", Request.QueryString["GameID"]);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

|||

Thanks, it works now.

How to incorporate column-header click-driven sorting, and column header click-driven grou

How to incorporate column-header click-driven sorting, and column
header click-driven grouping in SQL Server Reporting Services:
Create the following report parameters with the following details (you
can change the parameter names to suit your naming convention):
(1) Name=3DprmSortBy
Prompt=3DSort By
Data Type=3Dstring
Allow blank value: checked; Allow null value: checked
Available values: Select non-queried
Default values: none
(2) Name=3DprmDirection
Prompt=3DDirection
Data Type=3Dstring
Allow blank value: checked
Available values: Label: ASC; Value:ASC
Label: DESC; Value: DESC
Default values: Non-queried, with the value "ASC" in the text box next
to it.
(3) Name=3DprmGroupBy
Prompt=3DGroup By
Data Type=3Dstring
Allow blank value: checked; Allow null value: checked
Available values: select non-queried
Default values: None
Scenario:
Suppose you have a report created using the traditional table. Let's
also suppose you have the following columns in your report: ClientID,
ClientName, City and SSN (clientID is numeric and the rest being alpha
numeric data types). You wish to sort by the "ClientName" column and
group by the "City" column.
Task: Sorting by the ClientName column:
=E2=80=A2 In the ClientName column header (that sits in a cell on the table
header), right click and select properties.
=E2=80=A2 Then click the advanced button to go to "Advanced Textbox
properties"
=E2=80=A2 Visibility tab settings:
=E2=80=A2 Initial visibility: visible
=E2=80=A2 Initial appearance of the toggle image...: collapsed
=E2=80=A2 Navigation tab settings:
=E2=80=A2 Hyperlink Action: select Jump to report and enter the name of the
report in the textbox (ex. rptClient)
=E2=80=A2 Click on the "Parameters" button to go to the "Parameters" dialog
box
=E2=80=A2 Parameters Dialog box entries:
=E2=80=A2 Here, you must list ALL of the parameters defined in your report
and assign values to them. Having done that, for our case, we concern
ourselves with the sorting and grouping parameter value settings
parameter name parameter value column
prmSortBy =3D"ClientName"
prmDirection =3DCode.DetermineDirection("ClientName",Parameters!prmSort= By.Value,Parameters!prmDirection.Value)
prmGroupBy =3Diif(Parameters!prmGroupBy.Value=3D"","None",Parameters!pr= mGroupBy.Value)
Note: "DetermineDirection" is a function we incorporated into our
report to return a sorting direction (ascending or descending) and
below is the function (if you are unsure where to incorporate the
function, highlight the table that holds the report and click on report
menu and go to properties and on the "code" tab, you enter the
function)
Public Function DetermineDirection(ByVal strColumnName as String, ByVal
strPreviousColumn as String, ByVal strDirection as String) As String
If strColumnName =3D strPreviousColumn Then
If strDirection =3D "ASC" Or strDirection =3D "" Then
Return "DESC"
Else
Return "ASC"
End If
Else
Return "ASC"
End If
End Function
Highlight the table that contains the report, right-click and select
properties. On the "Table Properties" dialog, go to the "Sorting" tab
and enter the following:
Expression =3D
iif(Parameters!prmDirection.Value=3D"ASC",Fields(iif(Parameters!prmSortBy.V= alue
Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
Direction:
Ascending
Expression =3D
=3Diif(Parameters!prmDirection.Value=3D"DESC",Fields(iif(Parameters!prmSort= By.Value
Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
Direction:
Descending
That should be it! When you run the repot and click on the ClientName
column header, it will refresh the report with the data sorted by the
ClientName.
Note: Remember that for each column you wish to sort by, you have to go
through the above drill and make sure that you set the values for all
the parameters defined on your report. This is very essential since you
do not wish to lose parameter values between sorting since the report
refreshes the data with each sort. Also you need to set the SortBy
parameter value to the column name you are sorting by and to obtain the
sort direction, you can use the function I have above. The advantage of
using the function above is that it allows you to sort the same column
in ascending or descending mode when you click the column repeatedly
(it alternates). If however you click on a new column for the very
first time, it defaults to ascending sort.
Task: Grouping by a column
In order to group by a certain column we created a small icon that we
embedded in the cell under the column header in a separate row right
below the column header. First off, we inserted a group row by using
the =E2=80=9Cinsert group=E2=80=9D menu. Here are the settings for that gro= up
(under Edit Group menu)
=E2=80=A2 General tab (grouping and sorting properties)
o Name: Gp1
o Group On: Expression=3D
=3Diif(Parameters!prmGroupBy.Value=3DFields!None.Value,"",Parameters!prmGro= upBy.Value
& ": " & Fields(iif(Parameters!prmGroupBy.Value
=3DFields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
o Sorting tab: Sort on expressions:
=EF=82=A7 =3DParameters!prmDirection.Value=3D=E2=80=9DASC=E2=80=9D; Directi= on: Ascending
=EF=82=A7 =3DParameters!prmDirection.Value=3D=E2=80=9DDEC=E2=80=9D; Directi= on: Descending
NOTE: We are using an expression for the groupBy since you can have any
column that you could group your report by. This way you can have
dynamic grouping in your report. In our dataset for the report, we
included a column called =E2=80=9CNone=E2=80=9D that returned an empty stri= ng. We
used that to default the value of the groupBy parameter when no
grouping was selected. Otherwise we would get an error each time the
report is loaded for the first time since the prmGroupBy would not be
populated.
=E2=80=A2 Moving on, for our case we would have an icon under the =E2=80=9C= City=E2=80=9D
column. Right click on that cell and go to properties.
=E2=80=A2 It will then take you to =E2=80=9Cimage properties dialog box=E2= =80=9D.
=E2=80=A2 From here, go to the Navigation tab
o Navigation Tab: Select Jump to Report radio button and enter the
report name (without the .rdl extension of course)
o Click on the Parameters button to go to the Parameters dialog as
before
o Insure that values are assigned to ALL of the remaining parameters on
the report as before.
o Below are the settings specific to the sort and group by parameters
Parameter name parameter value column
prmSortBy =3DParameters!prmSortBy.Value (note the difference between
this and the one before)
prmDirection =3DParameters!prmDirection.Value
prmGroupBy =3Diif(Parameters!prmGroupBy.Value=3D "City","None","City")
(Replace City with the name of the column you are grouping by)
Documented by raghus, from Kizan Technologies
Edited and checked by Bryan AveryHi Bryan,
I have followed all your steps below, but am getting the following error
message when trying to view the report:
1) The value expression for the textbox â'textbox3â' contains an error:
[BC30057] Too many arguments to 'Public Function
DetermineDirection(strColumnName As String) As Object'.
2) There is an error on line 0 of custom code: [BC30203] Identifier expected.
Do you know what would be causing this?
Thanks
Darren
"Bryan Avery" wrote:
> How to incorporate column-header click-driven sorting, and column
> header click-driven grouping in SQL Server Reporting Services:
> Create the following report parameters with the following details (you
> can change the parameter names to suit your naming convention):
> (1) Name=prmSortBy
> Prompt=Sort By
> Data Type=string
> Allow blank value: checked; Allow null value: checked
> Available values: Select non-queried
> Default values: none
> (2) Name=prmDirection
> Prompt=Direction
> Data Type=string
> Allow blank value: checked
> Available values: Label: ASC; Value:ASC
> Label: DESC; Value: DESC
> Default values: Non-queried, with the value "ASC" in the text box next
> to it.
> (3) Name=prmGroupBy
> Prompt=Group By
> Data Type=string
> Allow blank value: checked; Allow null value: checked
> Available values: select non-queried
> Default values: None
> Scenario:
> Suppose you have a report created using the traditional table. Let's
> also suppose you have the following columns in your report: ClientID,
> ClientName, City and SSN (clientID is numeric and the rest being alpha
> numeric data types). You wish to sort by the "ClientName" column and
> group by the "City" column.
> Task: Sorting by the ClientName column:
> â?¢ In the ClientName column header (that sits in a cell on the table
> header), right click and select properties.
> â?¢ Then click the advanced button to go to "Advanced Textbox
> properties"
> â?¢ Visibility tab settings:
> â?¢ Initial visibility: visible
> â?¢ Initial appearance of the toggle image...: collapsed
> â?¢ Navigation tab settings:
> â?¢ Hyperlink Action: select Jump to report and enter the name of the
> report in the textbox (ex. rptClient)
> â?¢ Click on the "Parameters" button to go to the "Parameters" dialog
> box
> â?¢ Parameters Dialog box entries:
> â?¢ Here, you must list ALL of the parameters defined in your report
> and assign values to them. Having done that, for our case, we concern
> ourselves with the sorting and grouping parameter value settings
> parameter name parameter value column
> prmSortBy ="ClientName"
> prmDirection =Code.DetermineDirection("ClientName",Parameters!prmSortBy.Value,Parameters!prmDirection.Value)
> prmGroupBy =iif(Parameters!prmGroupBy.Value="","None",Parameters!prmGroupBy.Value)
> Note: "DetermineDirection" is a function we incorporated into our
> report to return a sorting direction (ascending or descending) and
> below is the function (if you are unsure where to incorporate the
> function, highlight the table that holds the report and click on report
> menu and go to properties and on the "code" tab, you enter the
> function)
> Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> strPreviousColumn as String, ByVal strDirection as String) As String
> If strColumnName = strPreviousColumn Then
> If strDirection = "ASC" Or strDirection = "" Then
> Return "DESC"
> Else
> Return "ASC"
> End If
> Else
> Return "ASC"
> End If
> End Function
> Highlight the table that contains the report, right-click and select
> properties. On the "Table Properties" dialog, go to the "Sorting" tab
> and enter the following:
> Expression => iif(Parameters!prmDirection.Value="ASC",Fields(iif(Parameters!prmSortBy.Value
> Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
> Direction:
> Ascending
> Expression => =iif(Parameters!prmDirection.Value="DESC",Fields(iif(Parameters!prmSortBy.Value
> Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
> Direction:
> Descending
> That should be it! When you run the repot and click on the ClientName
> column header, it will refresh the report with the data sorted by the
> ClientName.
> Note: Remember that for each column you wish to sort by, you have to go
> through the above drill and make sure that you set the values for all
> the parameters defined on your report. This is very essential since you
> do not wish to lose parameter values between sorting since the report
> refreshes the data with each sort. Also you need to set the SortBy
> parameter value to the column name you are sorting by and to obtain the
> sort direction, you can use the function I have above. The advantage of
> using the function above is that it allows you to sort the same column
> in ascending or descending mode when you click the column repeatedly
> (it alternates). If however you click on a new column for the very
> first time, it defaults to ascending sort.
> Task: Grouping by a column
> In order to group by a certain column we created a small icon that we
> embedded in the cell under the column header in a separate row right
> below the column header. First off, we inserted a group row by using
> the â'insert groupâ' menu. Here are the settings for that group
> (under Edit Group menu)
> â?¢ General tab (grouping and sorting properties)
> o Name: Gp1
> o Group On: Expression=> =iif(Parameters!prmGroupBy.Value=Fields!None.Value,"",Parameters!prmGroupBy.Value
> & ": " & Fields(iif(Parameters!prmGroupBy.Value
> =Fields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
> o Sorting tab: Sort on expressions:
> ï?§ =Parameters!prmDirection.Value=â'ASCâ'; Direction: Ascending
> ï?§ =Parameters!prmDirection.Value=â'DECâ'; Direction: Descending
> NOTE: We are using an expression for the groupBy since you can have any
> column that you could group your report by. This way you can have
> dynamic grouping in your report. In our dataset for the report, we
> included a column called â'Noneâ' that returned an empty string. We
> used that to default the value of the groupBy parameter when no
> grouping was selected. Otherwise we would get an error each time the
> report is loaded for the first time since the prmGroupBy would not be
> populated.
> â?¢ Moving on, for our case we would have an icon under the â'Cityâ'
> column. Right click on that cell and go to properties.
> â?¢ It will then take you to â'image properties dialog boxâ'.
> â?¢ From here, go to the Navigation tab
> o Navigation Tab: Select Jump to Report radio button and enter the
> report name (without the .rdl extension of course)
> o Click on the Parameters button to go to the Parameters dialog as
> before
> o Insure that values are assigned to ALL of the remaining parameters on
> the report as before.
> o Below are the settings specific to the sort and group by parameters
> Parameter name parameter value column
> prmSortBy =Parameters!prmSortBy.Value (note the difference between
> this and the one before)
> prmDirection =Parameters!prmDirection.Value
> prmGroupBy =iif(Parameters!prmGroupBy.Value= "City","None","City")
> (Replace City with the name of the column you are grouping by)
>
> Documented by raghus, from Kizan Technologies
> Edited and checked by Bryan Avery
>|||BTW: built-in support for sorting table columns by clicking on the table
headers will be available in the next release (RS 2005).
--
Regards
<<<Bryan Avery>>
"Darren" wrote:
> Hi Bryan,
> I have followed all your steps below, but am getting the following error
> message when trying to view the report:
> 1) The value expression for the textbox â'textbox3â' contains an error:
> [BC30057] Too many arguments to 'Public Function
> DetermineDirection(strColumnName As String) As Object'.
> 2) There is an error on line 0 of custom code: [BC30203] Identifier expected.
> Do you know what would be causing this?
> Thanks
> Darren
> "Bryan Avery" wrote:
> > How to incorporate column-header click-driven sorting, and column
> > header click-driven grouping in SQL Server Reporting Services:
> >
> > Create the following report parameters with the following details (you
> > can change the parameter names to suit your naming convention):
> > (1) Name=prmSortBy
> > Prompt=Sort By
> > Data Type=string
> > Allow blank value: checked; Allow null value: checked
> > Available values: Select non-queried
> > Default values: none
> >
> > (2) Name=prmDirection
> > Prompt=Direction
> > Data Type=string
> > Allow blank value: checked
> > Available values: Label: ASC; Value:ASC
> > Label: DESC; Value: DESC
> > Default values: Non-queried, with the value "ASC" in the text box next
> > to it.
> >
> > (3) Name=prmGroupBy
> > Prompt=Group By
> > Data Type=string
> > Allow blank value: checked; Allow null value: checked
> > Available values: select non-queried
> > Default values: None
> >
> > Scenario:
> > Suppose you have a report created using the traditional table. Let's
> > also suppose you have the following columns in your report: ClientID,
> > ClientName, City and SSN (clientID is numeric and the rest being alpha
> > numeric data types). You wish to sort by the "ClientName" column and
> > group by the "City" column.
> >
> > Task: Sorting by the ClientName column:
> >
> > â?¢ In the ClientName column header (that sits in a cell on the table
> > header), right click and select properties.
> > â?¢ Then click the advanced button to go to "Advanced Textbox
> > properties"
> > â?¢ Visibility tab settings:
> > â?¢ Initial visibility: visible
> > â?¢ Initial appearance of the toggle image...: collapsed
> > â?¢ Navigation tab settings:
> > â?¢ Hyperlink Action: select Jump to report and enter the name of the
> > report in the textbox (ex. rptClient)
> > â?¢ Click on the "Parameters" button to go to the "Parameters" dialog
> > box
> >
> > â?¢ Parameters Dialog box entries:
> > â?¢ Here, you must list ALL of the parameters defined in your report
> > and assign values to them. Having done that, for our case, we concern
> > ourselves with the sorting and grouping parameter value settings
> > parameter name parameter value column
> > prmSortBy ="ClientName"
> > prmDirection =Code.DetermineDirection("ClientName",Parameters!prmSortBy.Value,Parameters!prmDirection.Value)
> > prmGroupBy =iif(Parameters!prmGroupBy.Value="","None",Parameters!prmGroupBy.Value)
> > Note: "DetermineDirection" is a function we incorporated into our
> > report to return a sorting direction (ascending or descending) and
> > below is the function (if you are unsure where to incorporate the
> > function, highlight the table that holds the report and click on report
> > menu and go to properties and on the "code" tab, you enter the
> > function)
> >
> > Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> > strPreviousColumn as String, ByVal strDirection as String) As String
> > If strColumnName = strPreviousColumn Then
> > If strDirection = "ASC" Or strDirection = "" Then
> > Return "DESC"
> > Else
> > Return "ASC"
> > End If
> > Else
> > Return "ASC"
> > End If
> > End Function
> >
> > Highlight the table that contains the report, right-click and select
> > properties. On the "Table Properties" dialog, go to the "Sorting" tab
> > and enter the following:
> >
> > Expression => > iif(Parameters!prmDirection.Value="ASC",Fields(iif(Parameters!prmSortBy.Value
> > Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
> > Direction:
> > Ascending
> >
> > Expression => > =iif(Parameters!prmDirection.Value="DESC",Fields(iif(Parameters!prmSortBy.Value
> > Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
> > Direction:
> > Descending
> >
> > That should be it! When you run the repot and click on the ClientName
> > column header, it will refresh the report with the data sorted by the
> > ClientName.
> >
> > Note: Remember that for each column you wish to sort by, you have to go
> > through the above drill and make sure that you set the values for all
> > the parameters defined on your report. This is very essential since you
> > do not wish to lose parameter values between sorting since the report
> > refreshes the data with each sort. Also you need to set the SortBy
> > parameter value to the column name you are sorting by and to obtain the
> > sort direction, you can use the function I have above. The advantage of
> > using the function above is that it allows you to sort the same column
> > in ascending or descending mode when you click the column repeatedly
> > (it alternates). If however you click on a new column for the very
> > first time, it defaults to ascending sort.
> >
> > Task: Grouping by a column
> > In order to group by a certain column we created a small icon that we
> > embedded in the cell under the column header in a separate row right
> > below the column header. First off, we inserted a group row by using
> > the â'insert groupâ' menu. Here are the settings for that group
> > (under Edit Group menu)
> > â?¢ General tab (grouping and sorting properties)
> > o Name: Gp1
> > o Group On: Expression=> > =iif(Parameters!prmGroupBy.Value=Fields!None.Value,"",Parameters!prmGroupBy.Value
> > & ": " & Fields(iif(Parameters!prmGroupBy.Value
> > =Fields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
> > o Sorting tab: Sort on expressions:
> > ï?§ =Parameters!prmDirection.Value=â'ASCâ'; Direction: Ascending
> > ï?§ =Parameters!prmDirection.Value=â'DECâ'; Direction: Descending
> >
> > NOTE: We are using an expression for the groupBy since you can have any
> > column that you could group your report by. This way you can have
> > dynamic grouping in your report. In our dataset for the report, we
> > included a column called â'Noneâ' that returned an empty string. We
> > used that to default the value of the groupBy parameter when no
> > grouping was selected. Otherwise we would get an error each time the
> > report is loaded for the first time since the prmGroupBy would not be
> > populated.
> >
> > â?¢ Moving on, for our case we would have an icon under the â'Cityâ'
> > column. Right click on that cell and go to properties.
> > â?¢ It will then take you to â'image properties dialog boxâ'.
> > â?¢ From here, go to the Navigation tab
> > o Navigation Tab: Select Jump to Report radio button and enter the
> > report name (without the .rdl extension of course)
> > o Click on the Parameters button to go to the Parameters dialog as
> > before
> > o Insure that values are assigned to ALL of the remaining parameters on
> > the report as before.
> > o Below are the settings specific to the sort and group by parameters
> > Parameter name parameter value column
> > prmSortBy =Parameters!prmSortBy.Value (note the difference between
> > this and the one before)
> > prmDirection =Parameters!prmDirection.Value
> > prmGroupBy =iif(Parameters!prmGroupBy.Value= "City","None","City")
> > (Replace City with the name of the column you are grouping by)
> >
> >
> > Documented by raghus, from Kizan Technologies
> > Edited and checked by Bryan Avery
> >
> >|||The following needs to be on one line:
Public Function DetermineDirection(ByVal strColumnName as String, ByVal
strPreviousColumn as String, ByVal strDirection as String) As String|||thanks for resolving this
--
Regards
<<<Bryan Avery>>
"Nick Name" wrote:
> The following needs to be on one line:
> Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> strPreviousColumn as String, ByVal strDirection as String) As String
>

how to include the nulls?

Hi, I have the following query stored:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)

Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...

As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.

The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.

Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.

I'd get

Prod | Qty | Week

1 23 1

1 12 2

2 10 3

3 22 1

3 15 2

3 12 3

3 4 4

Although this looks fine - what I actually need is:

1 23 1

1 12 2

1 0 3

1 0 3

2 0 1

2 0 2

2 10 3

2 0 4

3 22 1

3 15 2

3 12 3

3 4 4

Does that make sense?

Any ideas on how to do this?

Yes, sure that makes sense, but therefore yopu will have to join your "data" table either with a calendar table, or with a subquery returning the weeks that are present for all products to appear in the resultset (cross joining)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

ok the calendar tbl sounds the correct option - is something already in my db, or do i have to create it?

if so, any examples of how to do this?

|||Hi,

look here: http://www.aspfaq.com/2519

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

ok I'm really a novice with all that..

I created it based on just pasting the example they had.. but that's not what I wanted..

I guess what I want is a calendar with the following:

Year, Week and that's it...

that one in that example had a load of extra data which I couldn't make head nor tail of....

|||

ok nearly there.. got my calendar as I want it, but now I've hit a bit of a problem with my join.

If I join my orders tbl by orderdate with the Dt column in my calendar.. then it doesn't return any retults.. here we are:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek,
dbo.Calendar.W AS SalesWeek, dbo.Calendar.Y AS SalesYear
FROM dbo.Calendar LEFT OUTER JOIN
dbo.Orders_Retail ON dbo.Calendar.dt = dbo.Orders_Retail.OrderDate INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(dbo.Calendar.D, dbo.Calendar.dt, @.StartDate) <= 0) AND (DATEDIFF(dbo.Calendar.D,
dbo.Calendar.dt, @.EndDate) >= 0)
GROUP BY dbo.Calendar.Y, dbo.Calendar.W, dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, dbo.Calendar.Y, dbo.Calendar.W

Any ideas?

|||

ok I've narrowed it down to the fact that it doesn't like the join.

The one that joins the orderdate in my orders tbl with the dt field in my calendar tbl..

Even though both are datetime fields, both the same.. the only difference I can see between the two in terms of the values they have is that the order date usually has the date and then time, whereas the dt field just has the date...

therefore I was thinking the:

dbo.Calendar ON dbo.Orders_Retail.OrderDate = dbo.Calendar.dt

needs to be changed so that the orderdate vlaue removes it's time from it perhaps?

Any ideas?

James

||||||

Hi!,

I don't think I mad myself clear - I'm not getting an errors -I'm just not getting any results.

|||

anyone got any ideas?

I know exactly what the issue is....

in the calendar the date is held as 1/1/2003.. and up to a point the dates where held in the db like this also, however after a certain date, the time was also held so the date looks like 1/1/2003 00:00:00

This the results that aren't returned.

As I'm doing my JOIN on the dt held in the calendar tbl and then dt in the orders tbl.. as the times are held in the orders tbl also, this means that the join won't work as the calendar tbl will not hold the EXACT date and time held in the orders tbl..

does that make sense?

How do I fix that?

|||

Could you please post you table definitions to get a clear idea on how your tables look like. Seems like the datetime formats in the two columns are different. Schema definition would help in this case.

HTH,

~riyaz~

|||

ok I'm getting somewhere with this now.. however I have another problem.. here's the current query

SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.ProductBrand, SUM(dbo.OrderDetails_Retail.Quantity) AS Qty, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.Orders_Retail INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID INNER JOIN
dbo.Calendar ON CONVERT(datetime, CONVERT(Nvarchar, dbo.Orders_Retail.OrderDate, 102), 102) = CONVERT(datetime, CONVERT(Nvarchar,
dbo.Calendar.dt, 102), 102) RIGHT OUTER JOIN
dbo.Products ON dbo.OrderDetails_Retail.ProductID = dbo.Products.ProductID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> '
Deleted ') AND
(dbo.Orders_Retail.PayStatus <> ' Pending ') AND (dbo.Orders_Retail.OrderStatus <> ' Refunded ') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate,
@.StartDate) <= 0) AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.ProductBrand
ORDER BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductBrand, dbo.Products.ProductName

What this does is first get the products tbl, join that the orders_detail tbl, joins that the orders tbl which is finally joined to the calendar tbl.

What I'm finding is although it's working as intended, it's still not returning the product that haven't been bought..

I've narrowed this down to the following:

It I just to a Outer join on the products tbl to the order details tbl with no WHERE queries.. this returned what I want,, however as soon as I add the WHERE clause, it loses all the products and only shows the ones that have been purchased.

I would have thoughy my Outer join on the products tbl would get round this problem?

how to improve this query, thanks

I have a pretty good db server with four CPUs, it has not any other loads on it, but the following query takes 4ms to return. I use the syscolumns this way quite often, I am not sure why it takes it that long to return, any idea?

select 'master',id,colid,name,xtype,length,xprec,xscale,status from [ablestatic].[dbo].syscolumns
where id=(select id from [ablestatic].[dbo].sysobjects where name='link_data_ezregs')

You might want to run profiler to see whats happening. The query by itself doesnt seem to be doing much. See if this makes any diff (although it shoudnt)

SELECT 'master',id,colid,name,xtype,length,xprec,xscale,status

FROM [ablestatic].[dbo].syscolumns sc

JOIN sysobjects so on sc.id = so.id
WHERE so.name='link_data_ezregs' and so.xtype = 'U'

|||

thanks.

I got the 2 - 4 ms when I run the query in one of my SP and watch it with profiler.

If I run it in query analyzer and in profile I got 15 ms.

|||15ms = milli secs or minutes?|||

millisecond for sure

|||I doubt if theres much you can do. you could use the stored proc as you suggested but even T-SQL should be as efficient, if its a single T-SQL statement.|||Check the execution plan when the query is executed inside/outside a sp, it may be caused by parameter sniffing, here are some related articles:

http://forums.asp.net/2/1377161/ShowThread.aspx

Understanding Plan Guides

How to improve query

Following is a query based on Northwind. I need to output the highest
order value for each date.
The following works. However I want to see if there is any way to
remove the nested query and do some joins.
select a.orderid, a.orderdate, sum(b.quantity * b.unitprice) as total
from orders a, [order details] b
where a.orderid = b.orderid
group by a.orderid, a.orderdate
having sum(b.quantity * b.unitprice) = (select max(total1)
from (select sum(quantity*unitprice) as total1, orders.orderdate as
date, orders.orderid
from [order details], orders
where [order details].orderid = orders.orderid
group by orders.orderdate, orders.orderid
) as C
where c.date = a.orderdate)
order by a.orderdate asc
Thanks,Here is another way (but I'm not sure if it's better, though; in fact,
the execution plan indicates that it's worse):
select x.orderid, x.orderdate, x.total
from (
select o1.orderid, o1.orderdate,
sum(d1.quantity * d1.unitprice) as total
from orders o1
inner join [order details] d1 on o1.orderid = d1.orderid
group by o1.orderid, o1.orderdate
) x inner join (
select orderdate, max(total) as max_total
from (
select o2.orderid, o2.orderdate,
sum(d2.quantity * d2.unitprice) as total
from orders o2
inner join [order details] d2 on o2.orderid = d2.orderid
group by o2.orderid, o2.orderdate
) y
group by orderdate
) z on x.orderdate=z.orderdate and x.total=z.max_total
order by x.orderdate
We can use a view (or a CTE in SQL Server 2005), instead of the x and y
derived tables, but this will not improve the performance (just the
readability).
Razvan

How to import XML

I grabbed the following .xml and .xsd files from an online tutorial, and tried to import them into SQL Server by using XML Source. I want to be able to import ALL the data; however, the simpleType elements under the root <shiporder> are all missing from the XML source, e.g <orderpersion>. The root element, including any attributes from the root element are also missing...e.g <shiporder orderid="889923"

How can I import these missing fields? The .xml and .xsd files are below.

Any help will be greatly appreciated. Thanks.

.XML and .XSD files below. please note that the website replaces ": s" with

shiporder.xml
-
<?xml version="1.0" encoding="ISO-8859-1"?>

<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>
-

shiporder.xsd...
-
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xschema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xsimpleType name="stringtype">
<xs:restriction base="xstring"/>
</xsimpleType>

<xsimpleType name="inttype">
<xs:restriction base="xsositiveInteger"/>
</xsimpleType>

<xsimpleType name="dectype">
<xs:restriction base="xsecimal"/>
</xsimpleType>

<xsimpleType name="orderidtype">
<xs:restriction base="xstring">
<xsattern value="[0-9]{6}"/>
</xs:restriction>
</xsimpleType>

<xs:complexType name="shiptotype">
<xsequence>
<xs:element name="name" type="stringtype"/>
<xs:element name="address" type="stringtype"/>
<xs:element name="city" type="stringtype"/>
<xs:element name="country" type="stringtype"/>
</xsequence>
</xs:complexType>

<xs:complexType name="itemtype">
<xsequence>
<xs:element name="title" type="stringtype"/>
<xs:element name="note" type="stringtype" minOccurs="0"/>
<xs:element name="quantity" type="inttype"/>
<xs:element name="price" type="dectype"/>
</xsequence>
</xs:complexType>

<xs:complexType name="shipordertype">
<xsequence>
<xs:element name="orderperson" type="stringtype"/>
<xs:element name="shipto" type="shiptotype"/>
<xs:element name="item" maxOccurs="unbounded" type="itemtype"/>
</xsequence>
<xs:attribute name="orderid" type="orderidtype" use="required"/>
</xs:complexType>

<xs:element name="shiporder" type="shipordertype"/>

</xschema>

The XML Source expects a dummy root for some reason. Adding one to the XML and XSD should get you what you need.

Code Snippet

<root>
<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>
</root>

Code Snippet

<xs:complexType name="roottype">
<xs:sequence>
<xs:element name="shiporder" type="shipordertype"/>
</xs:sequence>
</xs:complexType>

<xs:element name="root" type="roottype"/>

|||

Hey JayH,

That was it. Thanks a lot.

How to import this schema and file into SQL with bulkload

Hi,
I have been given the following file that includes both the schema and the
XML data. It is about 6MB so I just show the start and the end of the file
<START SCHEMA SECTION>
<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPData xml:space="preserve">
<xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="VFPData" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="lotdata" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="lh_recno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_refno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_seqno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_ltype" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="3"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="bh_stowloc" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"
processContents="lax"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<END SCHEMA SECTION>
<DATA SECTION>
<lotdata lh_recno=" 91870" lh_refno=" 91870" lh_seqno="230 "
lh_ltype="CLP" lh_code=" 4993" lh_sale="M33 " lh_season="04"
lh_schdate="2005-02-17" lh_sroom="ROOM2 " lh_csect="ODD "
lh_gsect="ODDS" lh_catflg="false" lh_source=" " lh_aucent=" "
lh_chgdate="2005-02-15T08:44:57" lh_catpage="298" lh_porg="ESMQ "
lh_selorg="ESMQ " lh_relorg="AWLQ " lh_storg="AWLQ " lh_stcent="Q "
lh_sdate=" - - " lh_sldate=" - - T : : " lh_sorg=" "
lh_psorg=" " lh_qsch1=" " lh_qsch2=" "
lh_catsym="BN " lh_wstate="GRSY " lh_grsyflg="true" lh_packin="STD "
lh_bales="3" lh_tbales="3" lh_rnetkg="465.00" lh_rtare="6.00"
lh_pprice="0.0" lh_pbasis=" " lh_pclnkg="0.00" lh_pybasis=" "
lh_pcurr=" " lh_pcurate="0.000000" lh_sprice="0.0" lh_sbasis=" "
lh_sclnkg="0.00" lh_rprice="0.0" lh_rbasis=" " lh_saleout=" "
lh_verify=" " lh_ppsch="20.10" lh_pcalbas="$/BALE" lh_wtx="PAID " lh_comm="
" lh_intcomm=" " lh_tsycodc=" " lh_typec="
" lh_typflgc="false" lh_tsycod1="IDS" lh_type1="XLF5E.40
" lh_typflg1="false" lh_tsycod2=" " lh_type2="
" lh_typflg2="false" lh_tsycod3=" " lh_type3="
" lh_typflg3="false" lh_tsycod4=" " lh_type4="
" lh_typflg4="false" lh_typeq=" "
lh_fghtchg="0.00" lh_fghtrbt="5.96" lh_delbas="DND " lh_delcent=" "
lh_scode=" " ct_netkg="465.00" ct_sampkg="0.00" ct_type1="PTC "
ct_prefx1="3" ct_code1="07454266" ct_suffx1="B" ct_chk1="7" ct_lab1="AWTA "
ct_wbase="62.39" ct_mic="24.3" ct_mictype=" " ct_afmic="0.0" ct_lsmic="24.3"
ct_lssdm="5.6" ct_lscvm="23.0" ct_lscomft="88.2" ct_ofmic="0.0"
ct_ofsdm="0.0" ct_ofcvm="0.0" ct_ofcomft="0.0" ct_vmb="1.40" ct_hh="0.0"
ct_vm1="0.2000" ct_vm2="1.2000" ct_vm3="0.0000" ct_yld1="72.00"
ct_ytyp1="SDRY1" ct_clkg1="0.00" ct_yld2="76.40" ct_ytyp2="SCD17"
ct_clkg2="0.00" ct_yld3="73.50" ct_ytyp3="JCS " ct_clkg3="0.00"
ct_yld4="69.80" ct_ytyp4="ACARB" ct_clkg4="0.00" ct_minmic="0.0"
ct_maxmic="0.0" ct_minvmb="0.0" ct_maxvmb="0.0" ct_fctwb="0.00"
ct_fctvmb="0.00" ct_fcthh="0.00" ct_fctmic="0.000000" ct_rtst1="NORM "
ct_reiss1=" " ct_type2=" " ct_prefx2=" " ct_code2=" "
ct_suffx2=" " ct_chk2=" " ct_lab2=" " ct_slen="0" ct_slencv="0"
ct_str="0" ct_minlen="0" ct_maxlen="0" ct_minstr="0" ct_maxstr="0"
ct_pobt="0" ct_pobm="0" ct_pobb="0" ct_haut="0" ct_ehaut="0.0" ct_lstype="
" ct_lsecur="false" ct_type3=" " ct_prefx3=" " ct_code3=" "
ct_suffx3=" " ct_chk3=" " ct_lab3=" " ct_colx="0.0" ct_coly="0.0"
ct_colz="0.0" ct_ecolyz="0.0" ct_colyz="0.0" ct_mincolx="0.0"
ct_maxcolx="0.0" ct_mincoly="0.0" ct_maxcoly="0.0" ct_mincolz="0.0"
ct_maxcolz="0.0" ct_minclyz="0.0" ct_maxclyz="0.0" ct_reiss3=" "
ct_cvh="0.0" ct_noil="0.0" ct_dark="N" bh_brandp="SR/COR
" bh_descp="AAALM " bh_area="N28 " bh_stowloc="
"/>
</VFPData>
<END DATA SECTION>
As you can see from the data, I have left out lots of fields to fit in post.
Anyway, we want to do a bulk load of this data, but none of the schema
sections look like examples on the web where you can setup 'sql:' options
for the field names etc.. In fact this XML/schema file don't look like
anything I have seen on the web. I guess it is just a bit more complex than
usual.
So would I be able do a bulkload with this data?
I have cut out the schema and pasted into another file but I don't know
where to go next as the file looks so different from others I have seen.
Can anyone help me with some options on how to use these files for a bulk
load?
TIA
Nathan
Hi Nathan,
my suggestion:
DId you design a database (inclusive tables and relations) already? If NOT,
do this...
(maybe with the help of XMLSpy ... export xml data to database...)
then
Take your XML Datafile, open it with XMLSpy (Enterprise Edition in
evaluation version available), and go through:
--> Convert --> Create XML schema from DB structure
Now you have mapping-schema. ToDo: Add the missing relationships
like...
<xs:annotation>
<xs:appinfo>
<sql:relationship name="name"
parent="Table" parent-key="Table_ID"
child="RelatedTable" child-key="Table_ID"/>
</xs:appinfo>
</xs:annotation>
...
then, add the sql:is-constant for your root-element like ...
<xs:element name="ROOT" sql:is-constant="1">
and your mapping schema is complete.
Regards
SUsanne
"Nathan Simpson" wrote:

> Hi,
> I have been given the following file that includes both the schema and the
> XML data. It is about 6MB so I just show the start and the end of the file
> <START SCHEMA SECTION>
> <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
> <VFPData xml:space="preserve">
> <xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
> <xsd:element name="VFPData" msdata:IsDataSet="true">
> <xsd:complexType>
> <xsd:choice maxOccurs="unbounded">
> <xsd:element name="lotdata" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="lh_recno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_refno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_seqno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_ltype" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="3"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="bh_stowloc" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="20"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"
> processContents="lax"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> <END SCHEMA SECTION>
> <DATA SECTION>
> <lotdata lh_recno=" 91870" lh_refno=" 91870" lh_seqno="230 "
> lh_ltype="CLP" lh_code=" 4993" lh_sale="M33 " lh_season="04"
> lh_schdate="2005-02-17" lh_sroom="ROOM2 " lh_csect="ODD "
> lh_gsect="ODDS" lh_catflg="false" lh_source=" " lh_aucent=" "
> lh_chgdate="2005-02-15T08:44:57" lh_catpage="298" lh_porg="ESMQ "
> lh_selorg="ESMQ " lh_relorg="AWLQ " lh_storg="AWLQ " lh_stcent="Q "
> lh_sdate=" - - " lh_sldate=" - - T : : " lh_sorg=" "
> lh_psorg=" " lh_qsch1=" " lh_qsch2=" "
> lh_catsym="BN " lh_wstate="GRSY " lh_grsyflg="true" lh_packin="STD "
> lh_bales="3" lh_tbales="3" lh_rnetkg="465.00" lh_rtare="6.00"
> lh_pprice="0.0" lh_pbasis=" " lh_pclnkg="0.00" lh_pybasis=" "
> lh_pcurr=" " lh_pcurate="0.000000" lh_sprice="0.0" lh_sbasis=" "
> lh_sclnkg="0.00" lh_rprice="0.0" lh_rbasis=" " lh_saleout=" "
> lh_verify=" " lh_ppsch="20.10" lh_pcalbas="$/BALE" lh_wtx="PAID " lh_comm="
> " lh_intcomm=" " lh_tsycodc=" " lh_typec="
> " lh_typflgc="false" lh_tsycod1="IDS" lh_type1="XLF5E.40
> " lh_typflg1="false" lh_tsycod2=" " lh_type2="
> " lh_typflg2="false" lh_tsycod3=" " lh_type3="
> " lh_typflg3="false" lh_tsycod4=" " lh_type4="
> " lh_typflg4="false" lh_typeq=" "
> lh_fghtchg="0.00" lh_fghtrbt="5.96" lh_delbas="DND " lh_delcent=" "
> lh_scode=" " ct_netkg="465.00" ct_sampkg="0.00" ct_type1="PTC "
> ct_prefx1="3" ct_code1="07454266" ct_suffx1="B" ct_chk1="7" ct_lab1="AWTA "
> ct_wbase="62.39" ct_mic="24.3" ct_mictype=" " ct_afmic="0.0" ct_lsmic="24.3"
> ct_lssdm="5.6" ct_lscvm="23.0" ct_lscomft="88.2" ct_ofmic="0.0"
> ct_ofsdm="0.0" ct_ofcvm="0.0" ct_ofcomft="0.0" ct_vmb="1.40" ct_hh="0.0"
> ct_vm1="0.2000" ct_vm2="1.2000" ct_vm3="0.0000" ct_yld1="72.00"
> ct_ytyp1="SDRY1" ct_clkg1="0.00" ct_yld2="76.40" ct_ytyp2="SCD17"
> ct_clkg2="0.00" ct_yld3="73.50" ct_ytyp3="JCS " ct_clkg3="0.00"
> ct_yld4="69.80" ct_ytyp4="ACARB" ct_clkg4="0.00" ct_minmic="0.0"
> ct_maxmic="0.0" ct_minvmb="0.0" ct_maxvmb="0.0" ct_fctwb="0.00"
> ct_fctvmb="0.00" ct_fcthh="0.00" ct_fctmic="0.000000" ct_rtst1="NORM "
> ct_reiss1=" " ct_type2=" " ct_prefx2=" " ct_code2=" "
> ct_suffx2=" " ct_chk2=" " ct_lab2=" " ct_slen="0" ct_slencv="0"
> ct_str="0" ct_minlen="0" ct_maxlen="0" ct_minstr="0" ct_maxstr="0"
> ct_pobt="0" ct_pobm="0" ct_pobb="0" ct_haut="0" ct_ehaut="0.0" ct_lstype="
> " ct_lsecur="false" ct_type3=" " ct_prefx3=" " ct_code3=" "
> ct_suffx3=" " ct_chk3=" " ct_lab3=" " ct_colx="0.0" ct_coly="0.0"
> ct_colz="0.0" ct_ecolyz="0.0" ct_colyz="0.0" ct_mincolx="0.0"
> ct_maxcolx="0.0" ct_mincoly="0.0" ct_maxcoly="0.0" ct_mincolz="0.0"
> ct_maxcolz="0.0" ct_minclyz="0.0" ct_maxclyz="0.0" ct_reiss3=" "
> ct_cvh="0.0" ct_noil="0.0" ct_dark="N" bh_brandp="SR/COR
> " bh_descp="AAALM " bh_area="N28 " bh_stowloc="
> "/>
> </VFPData>
> <END DATA SECTION>
> As you can see from the data, I have left out lots of fields to fit in post.
> Anyway, we want to do a bulk load of this data, but none of the schema
> sections look like examples on the web where you can setup 'sql:' options
> for the field names etc.. In fact this XML/schema file don't look like
> anything I have seen on the web. I guess it is just a bit more complex than
> usual.
> So would I be able do a bulkload with this data?
> I have cut out the schema and pasted into another file but I don't know
> where to go next as the file looks so different from others I have seen.
> Can anyone help me with some options on how to use these files for a bulk
> load?
> TIA
> Nathan
>
>
|||Sorry, you don't have to open your XML datafile...
You can create the schema from your database without it
"susanne" wrote:
[vbcol=seagreen]
> Hi Nathan,
> my suggestion:
> DId you design a database (inclusive tables and relations) already? If NOT,
> do this...
> (maybe with the help of XMLSpy ... export xml data to database...)
> then
> Take your XML Datafile, open it with XMLSpy (Enterprise Edition in
> evaluation version available), and go through:
> --> Convert --> Create XML schema from DB structure
> Now you have mapping-schema. ToDo: Add the missing relationships
> like...
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="name"
> parent="Table" parent-key="Table_ID"
> child="RelatedTable" child-key="Table_ID"/>
> </xs:appinfo>
> </xs:annotation>
> ...
> then, add the sql:is-constant for your root-element like ...
> <xs:element name="ROOT" sql:is-constant="1">
> and your mapping schema is complete.
> Regards
> SUsanne
>
> "Nathan Simpson" wrote:

How to import this schema and file into SQL with bulkload

Hi,
I have been given the following file that includes both the schema and the
XML data. It is about 6MB so I just show the start and the end of the file
<START SCHEMA SECTION>
<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPData xml:space="preserve">
<xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="VFPData" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="lotdata" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="lh_recno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_refno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_seqno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_ltype" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="3"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="bh_stowloc" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"
processContents="lax"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<END SCHEMA SECTION>
<DATA SECTION>
<lotdata lh_recno=" 91870" lh_refno=" 91870" lh_seqno="230 "
lh_ltype="CLP" lh_code=" 4993" lh_sale="M33 " lh_season="04"
lh_schdate="2005-02-17" lh_sroom="ROOM2 " lh_csect="ODD "
lh_gsect="ODDS" lh_catflg="false" lh_source=" " lh_aucent=" "
lh_chgdate="2005-02-15T08:44:57" lh_catpage="298" lh_porg="ESMQ "
lh_selorg="ESMQ " lh_relorg="AWLQ " lh_storg="AWLQ " lh_stcent="Q "
lh_sdate=" - - " lh_sldate=" - - T : : " lh_sorg=" "
lh_psorg=" " lh_qsch1=" " lh_qsch2=" "
lh_catsym="BN " lh_wstate="GRSY " lh_grsyflg="true" lh_packin="STD "
lh_bales="3" lh_tbales="3" lh_rnetkg="465.00" lh_rtare="6.00"
lh_pprice="0.0" lh_pbasis=" " lh_pclnkg="0.00" lh_pybasis=" "
lh_pcurr=" " lh_pcurate="0.000000" lh_sprice="0.0" lh_sbasis=" "
lh_sclnkg="0.00" lh_rprice="0.0" lh_rbasis=" " lh_saleout=" "
lh_verify=" " lh_ppsch="20.10" lh_pcalbas="$/BALE" lh_wtx="PAID " lh_comm="
" lh_intcomm=" " lh_tsycodc=" " lh_typec="
" lh_typflgc="false" lh_tsycod1="IDS" lh_type1="XLF5E.40
" lh_typflg1="false" lh_tsycod2=" " lh_type2="
" lh_typflg2="false" lh_tsycod3=" " lh_type3="
" lh_typflg3="false" lh_tsycod4=" " lh_type4="
" lh_typflg4="false" lh_typeq=" "
lh_fghtchg="0.00" lh_fghtrbt="5.96" lh_delbas="DND " lh_delcent=" "
lh_scode=" " ct_netkg="465.00" ct_sampkg="0.00" ct_type1="PTC "
ct_prefx1="3" ct_code1="07454266" ct_suffx1="B" ct_chk1="7" ct_lab1="AWTA "
ct_wbase="62.39" ct_mic="24.3" ct_mictype=" " ct_afmic="0.0" ct_lsmic="24.3"
ct_lssdm="5.6" ct_lscvm="23.0" ct_lscomft="88.2" ct_ofmic="0.0"
ct_ofsdm="0.0" ct_ofcvm="0.0" ct_ofcomft="0.0" ct_vmb="1.40" ct_hh="0.0"
ct_vm1="0.2000" ct_vm2="1.2000" ct_vm3="0.0000" ct_yld1="72.00"
ct_ytyp1="SDRY1" ct_clkg1="0.00" ct_yld2="76.40" ct_ytyp2="SCD17"
ct_clkg2="0.00" ct_yld3="73.50" ct_ytyp3="JCS " ct_clkg3="0.00"
ct_yld4="69.80" ct_ytyp4="ACARB" ct_clkg4="0.00" ct_minmic="0.0"
ct_maxmic="0.0" ct_minvmb="0.0" ct_maxvmb="0.0" ct_fctwb="0.00"
ct_fctvmb="0.00" ct_fcthh="0.00" ct_fctmic="0.000000" ct_rtst1="NORM "
ct_reiss1=" " ct_type2=" " ct_prefx2=" " ct_code2=" "
ct_suffx2=" " ct_chk2=" " ct_lab2=" " ct_slen="0" ct_slencv="0"
ct_str="0" ct_minlen="0" ct_maxlen="0" ct_minstr="0" ct_maxstr="0"
ct_pobt="0" ct_pobm="0" ct_pobb="0" ct_haut="0" ct_ehaut="0.0" ct_lstype="
" ct_lsecur="false" ct_type3=" " ct_prefx3=" " ct_code3=" "
ct_suffx3=" " ct_chk3=" " ct_lab3=" " ct_colx="0.0" ct_coly="0.0"
ct_colz="0.0" ct_ecolyz="0.0" ct_colyz="0.0" ct_mincolx="0.0"
ct_maxcolx="0.0" ct_mincoly="0.0" ct_maxcoly="0.0" ct_mincolz="0.0"
ct_maxcolz="0.0" ct_minclyz="0.0" ct_maxclyz="0.0" ct_reiss3=" "
ct_cvh="0.0" ct_noil="0.0" ct_dark="N" bh_brandp="SR/COR
" bh_descp="AAALM " bh_area="N28 " bh_stowloc="
"/>
</VFPData>
<END DATA SECTION>
As you can see from the data, I have left out lots of fields to fit in post.
Anyway, we want to do a bulk load of this data, but none of the schema
sections look like examples on the web where you can setup 'sql:' options
for the field names etc.. In fact this XML/schema file don't look like
anything I have seen on the web. I guess it is just a bit more complex than
usual.
So would I be able do a bulkload with this data?
I have cut out the schema and pasted into another file but I don't know
where to go next as the file looks so different from others I have seen.
Can anyone help me with some options on how to use these files for a bulk
load?
TIA
NathanHi Nathan,
my suggestion:
DId you design a database (inclusive tables and relations) already? If NOT,
do this...
(maybe with the help of XMLSpy ... export xml data to database...)
then
Take your XML Datafile, open it with XMLSpy (Enterprise Edition in
evaluation version available), and go through:
--> Convert --> Create XML schema from DB structure
Now you have mapping-schema. ToDo: Add the missing relationships
like...
<xs:annotation>
<xs:appinfo>
<sql:relationship name="name"
parent="Table" parent-key="Table_ID"
child="RelatedTable" child-key="Table_ID"/>
</xs:appinfo>
</xs:annotation>
...
then, add the sql:is-constant for your root-element like ...
<xs:element name="ROOT" sql:is-constant="1">
and your mapping schema is complete.
Regards
SUsanne
"Nathan Simpson" wrote:

> Hi,
> I have been given the following file that includes both the schema and the
> XML data. It is about 6MB so I just show the start and the end of the file
> <START SCHEMA SECTION>
> <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
> <VFPData xml:space="preserve">
> <xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
> <xsd:element name="VFPData" msdata:IsDataSet="true">
> <xsd:complexType>
> <xsd:choice maxOccurs="unbounded">
> <xsd:element name="lotdata" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="lh_recno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_refno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_seqno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_ltype" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="3"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="bh_stowloc" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="20"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"
> processContents="lax"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> <END SCHEMA SECTION>
> <DATA SECTION>
> <lotdata lh_recno=" 91870" lh_refno=" 91870" lh_seqno="230 "
> lh_ltype="CLP" lh_code=" 4993" lh_sale="M33 " lh_season="04"
> lh_schdate="2005-02-17" lh_sroom="ROOM2 " lh_csect="ODD "
> lh_gsect="ODDS" lh_catflg="false" lh_source=" " lh_aucent=" "
> lh_chgdate="2005-02-15T08:44:57" lh_catpage="298" lh_porg="ESMQ "
> lh_selorg="ESMQ " lh_relorg="AWLQ " lh_storg="AWLQ " lh_stcent="Q "
> lh_sdate=" - - " lh_sldate=" - - T : : " lh_sorg=" "
> lh_psorg=" " lh_qsch1=" " lh_qsch2=" "
> lh_catsym="BN " lh_wstate="GRSY " lh_grsyflg="true" lh_packin="STD "
> lh_bales="3" lh_tbales="3" lh_rnetkg="465.00" lh_rtare="6.00"
> lh_pprice="0.0" lh_pbasis=" " lh_pclnkg="0.00" lh_pybasis=" "
> lh_pcurr=" " lh_pcurate="0.000000" lh_sprice="0.0" lh_sbasis=" "
> lh_sclnkg="0.00" lh_rprice="0.0" lh_rbasis=" " lh_saleout=" "
> lh_verify=" " lh_ppsch="20.10" lh_pcalbas="$/BALE" lh_wtx="PAID " lh_comm
="
> " lh_intcomm=" " lh_tsycodc=" " lh_typec="
> " lh_typflgc="false" lh_tsycod1="IDS" lh_type1="XLF5E.40
> " lh_typflg1="false" lh_tsycod2=" " lh_type2="
> " lh_typflg2="false" lh_tsycod3=" " lh_type3="
> " lh_typflg3="false" lh_tsycod4=" " lh_type4="
> " lh_typflg4="false" lh_typeq=" "
> lh_fghtchg="0.00" lh_fghtrbt="5.96" lh_delbas="DND " lh_delcent=" "
> lh_scode=" " ct_netkg="465.00" ct_sampkg="0.00" ct_type1="PTC
"
> ct_prefx1="3" ct_code1="07454266" ct_suffx1="B" ct_chk1="7" ct_lab1="AWTA
"
> ct_wbase="62.39" ct_mic="24.3" ct_mictype=" " ct_afmic="0.0" ct_lsmic="24.
3"
> ct_lssdm="5.6" ct_lscvm="23.0" ct_lscomft="88.2" ct_ofmic="0.0"
> ct_ofsdm="0.0" ct_ofcvm="0.0" ct_ofcomft="0.0" ct_vmb="1.40" ct_hh="0.0"
> ct_vm1="0.2000" ct_vm2="1.2000" ct_vm3="0.0000" ct_yld1="72.00"
> ct_ytyp1="SDRY1" ct_clkg1="0.00" ct_yld2="76.40" ct_ytyp2="SCD17"
> ct_clkg2="0.00" ct_yld3="73.50" ct_ytyp3="JCS " ct_clkg3="0.00"
> ct_yld4="69.80" ct_ytyp4="ACARB" ct_clkg4="0.00" ct_minmic="0.0"
> ct_maxmic="0.0" ct_minvmb="0.0" ct_maxvmb="0.0" ct_fctwb="0.00"
> ct_fctvmb="0.00" ct_fcthh="0.00" ct_fctmic="0.000000" ct_rtst1="NORM "
> ct_reiss1=" " ct_type2=" " ct_prefx2=" " ct_code2=" "
> ct_suffx2=" " ct_chk2=" " ct_lab2=" " ct_slen="0" ct_slencv="0"
> ct_str="0" ct_minlen="0" ct_maxlen="0" ct_minstr="0" ct_maxstr="0"
> ct_pobt="0" ct_pobm="0" ct_pobb="0" ct_haut="0" ct_ehaut="0.0" ct_lstype="
> " ct_lsecur="false" ct_type3=" " ct_prefx3=" " ct_code3="
"
> ct_suffx3=" " ct_chk3=" " ct_lab3=" " ct_colx="0.0" ct_coly="0.0"
> ct_colz="0.0" ct_ecolyz="0.0" ct_colyz="0.0" ct_mincolx="0.0"
> ct_maxcolx="0.0" ct_mincoly="0.0" ct_maxcoly="0.0" ct_mincolz="0.0"
> ct_maxcolz="0.0" ct_minclyz="0.0" ct_maxclyz="0.0" ct_reiss3=" "
> ct_cvh="0.0" ct_noil="0.0" ct_dark="N" bh_brandp="SR/COR
> " bh_descp="AAALM " bh_area="N28 " bh_stowloc="
> "/>
> </VFPData>
> <END DATA SECTION>
> As you can see from the data, I have left out lots of fields to fit in pos
t.
> Anyway, we want to do a bulk load of this data, but none of the schema
> sections look like examples on the web where you can setup 'sql:' options
> for the field names etc.. In fact this XML/schema file don't look like
> anything I have seen on the web. I guess it is just a bit more complex tha
n
> usual.
> So would I be able do a bulkload with this data?
> I have cut out the schema and pasted into another file but I don't know
> where to go next as the file looks so different from others I have seen.
> Can anyone help me with some options on how to use these files for a bulk
> load?
> TIA
> Nathan
>
>|||Sorry, you don't have to open your XML datafile...
You can create the schema from your database without it
"susanne" wrote:
> Hi Nathan,
> my suggestion:
> DId you design a database (inclusive tables and relations) already? If NOT
,
> do this...
> (maybe with the help of XMLSpy ... export xml data to database...)
> then
> Take your XML Datafile, open it with XMLSpy (Enterprise Edition in
> evaluation version available), and go through:
> --> Convert --> Create XML schema from DB structure
> Now you have mapping-schema. ToDo: Add the missing relationships
> like...
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="name"
> parent="Table" parent-key="Table_ID"
> child="RelatedTable" child-key="Table_ID"/>
> </xs:appinfo>
> </xs:annotation>
> ...
> then, add the sql:is-constant for your root-element like ...
> <xs:element name="ROOT" sql:is-constant="1">
> and your mapping schema is complete.
> Regards
> SUsanne
>
> "Nathan Simpson" wrote:
>

How to import Pervasive Database into SQL Server 2005

Hi

I have tried to import Pervasive Database to SQL Server 2005.

I am not getting any option in Data Source for Pervasive So I tried following

I have Provided the following Connection String to SQL server Import and Export Wizard (in .net framework data provider for ODBC)

Connectio String: Dsn=DEMODATA;Driver={Pervasive ODBC Engine Interface}

DSN: DEMODATA

Driver:Pervasive ODBC Engine Interface

but i m getting error that "Can not get supported data types from th database connection."

Can you please look in to it and tell me possible ways by which I can import pervasive Database into SQL server 2005.

and let me know if there is any other tool by which we can see "Pervasive ODBC Engine Interface"

in Data Source

Thanks,

Arun

Unless there is another Pervasive user out there who can help you, I would suggest the following: try building the package in the designer rather than through the wizard. The designer in Business Intelligence Development Studio offers more options than the wizard, which only covers simple scenarios.

Having said that - if the provider does not return data type information, it may yet be a problem.

Donald

|||Did you ever get this resolved?|||

I am Currently working in a ETL project that extracts data from Pervasive V8 DB. I wanted to use OLE DB connection but I could not make it work; so I am using ODBC. I installed the ODBC driver in my the development stations and the SSIS server (as a part of the Client tool Pervasive.SQL Data Manager installation); then I defined the Dsn. Whitin SSIS I defined my datasaource as:

Provider: .Net Providers\Odbc Data Provider

Use user or System data SOurce Name: <Choose from dropdown list my dsn>

The conction string of the data source is just simple: Dsn=<MyDsn>

And I was ready to go...

Then I had problems in the server because it is x64 bit machine; but that is a different story.

Rafael Salas

|||

I'm on Pervasive 2000i... my issue is that I'm trying to use the demodata dsn and I get the following error:

Cannot get the supported data types from the database connection "Dsn=demodata;Driver={Pervasive ODBC Engine Interface}".

Additional information:

--> Specified cast is not valid. (System.Data)

|||

German,

Did you try what Donald Suggested you? All what I did was inside of BIDS and actually I never tried with the wizard since I needed to perform some transformations before writing into the destination table

Rafael Salas

How to import a XSD and XML file into a SQL table

I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
KarenDownload SQLXML 3.0
(http://www.microsoft.com/downloads/...&DisplayLang=en)
and read the documentation on the XML Bulk Load component. It's a COM
component you can call from a script (such as a .vbs) that will import data
from an XML file into a SQL Server database based on the mappings defined in
a schema.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegroups.com...
I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
Karen|||http://msdn.microsoft.com/library/d... />
sqlxml.asp
Download the sample code, project for this article as well as reading it...
One of the sample projects shows the nearly exaclty the code you inquired
about...|||Just a clarification: XMLDATA does not generate an XSD but an XDR file. You
have to use a mid-tier tool that converts the XDR into XSD if you really
need an XSD (or use SQL Server 2005 where you can specify XMLSCHEMA to get
an XSD).
Best regards
Michael
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegroups.com...
>I have generated the output of a SQL query against Northwind in the
> form of the XSD and the XML file for a query using the following query:
> For generating the XML output:
> SELECT * FROM Customers
> FOR XML AUTO
> Similarly, I use the following query to generate a XSD output of the
> above query from the Northwind database:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
>
> Now I have the dump of the customers table in a XSD and XML format I
> want to import the data in the resulting table into another database
> using DTS or whatever to interpre the .XSD and the .XML files.
> Please share the code to import a arbitrary .XML and .XSD file into a
> SQL table and the meta data SQL must determine dynamically to create a
> table in the target database.
> Thanks
> Karen
>|||Anyone knows how to dump the result of the XML AUTO query to an XML
file?
I have a query and used bcp to dump it and i named it with an XML
extension. If the result dump is very short like 3 records only. It
is displaying OK when I try to opien it in IE Browser. But when the
result of my BCP dump is big.. it is producing an error. The thing is,
If I executr my query directly over the Browser (using HTTP with my
database tied up to a Virtual Directory in IIS), It is displaying all
the right results.
Can anyone help me on this? I just need to dump the XML result into a
file so it can be archived and viewed.
Thanks,
Jeff
pongaski
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1626243.html|||Programmatically, you should use ADO or ADO.Net to write the FOR XML result
into the file stream. Alternatively, you can use the HTTP access through the
virtual directory and use view source, save as to save it...
Best regards
Michael
"pongaski" <pongaski.1pudhz@.mail.mcse.ms> wrote in message
news:pongaski.1pudhz@.mail.mcse.ms...
> Anyone knows how to dump the result of the XML AUTO query to an XML
> file?
> I have a query and used bcp to dump it and i named it with an XML
> extension. If the result dump is very short like 3 records only. It
> is displaying OK when I try to opien it in IE Browser. But when the
> result of my BCP dump is big.. it is producing an error. The thing is,
> If I executr my query directly over the Browser (using HTTP with my
> database tied up to a Virtual Directory in IIS), It is displaying all
> the right results.
> Can anyone help me on this? I just need to dump the XML result into a
> file so it can be archived and viewed.
> Thanks,
> Jeff
>
> --
> pongaski
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1626243.html
>|||"Michael Rys [MSFT]" wrote:

> Programmatically, you should use ADO or ADO.Net to write the FOR XML resul
t
> into the file stream. Alternatively, you can use the HTTP access through t
he
> virtual directory and use view source, save as to save it...
> Best regards
> Michael
It is not true. Lower I write stored proc wich write result "FOR XML" query
to txt file.
CREATE PROCEDURE usp_SQLtoXMLfile (
@.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
@.FileNameInput varchar(255), --File name with full path
@.headInput nvarchar(4000), --Prefix expression in result file
@.sufixInput nvarchar(4000) --postfix expression in result file
) AS
DECLARE @.FileName varchar(255) --\
DECLARE @.Text1 nvarchar(4000) --|
DECLARE @.FS int --|
DECLARE @.OLEResult int -- \
DECLARE @.FileID int -- /The bloc variables for work with files
DECLARE @.hr int --|
DECLARE @.source varchar(30) --|
DECLARE @.desc varchar (200) --/
DECLARE @.text NVARCHAR(4000) --bufer only
declare @.cur_edit cursor
declare @._kfvalue sql_variant --Return value
DECLARE @.sqlXmlQuerry NVARCHAR(4000) --Variable for query
BEGIN
----
--
EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
OUTPUT --create object for work with file system
IF @.OLEResult <> 0 --chek errors
BEGIN
PRINT 'Scripting.FileSystemObject'
GOTO Error_Handler
END
SET @.FileName=@.FileNameInput
execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
@.FileName --Create text file
IF @.OLEResult <> 0
BEGIN
PRINT 'CreateTextFile'
GOTO Error_Handler
END
----
--
IF LEN(@.headInput)>0 --Check prefix empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
--Write to text file prefix
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
----
--
SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
Server};SERVER='+@.@.SERVERNAME+''','''+@.s
qlXmlQuerry+''') ' --Adapt query for
cursor
SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+' open
@.cur_edit ' --Add to query string declaration and opening cursor
--PRINT @.sqlXmlQuerry --for debug
exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
sql_variant', @.cur_edit output, @._kfvalue --Execute the string
FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
WHILE (@.@.fetch_status = 0)
BEGIN
----
--
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add to
text file value from @.text
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
FETCH NEXT FROM @.cur_edit INTO @.text
END
CLOSE @.cur_edit
DEALLOCATE @.cur_edit
----
--
IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
--Write postfix expression to text file
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
----
--goto Done
Error_Handler:
PRINT '*** ERROR ***'
EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description = @.desc
Done:
EXECUTE @.OLEResult = sp_OADestroy @.FileID
EXECUTE @.OLEResult = sp_OADestroy @.FS
END
GO
PS Sorry for my english...|||You can use sp_OA stored procs, but it is not something that I recommend
(note that I say "should" and not "it is the only way" :-)).
Thanks for the posting though and no worries about the English. TSQL crosses
language-boundaries :-)
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:C525C398-29B0-4FF1-A494-29D6B61CD66E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> It is not true. Lower I write stored proc wich write result "FOR XML"
> query
> to txt file.
> CREATE PROCEDURE usp_SQLtoXMLfile (
> @.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
> @.FileNameInput varchar(255), --File name with full path
> @.headInput nvarchar(4000), --Prefix expression in result file
> @.sufixInput nvarchar(4000) --postfix expression in result file
> ) AS
> DECLARE @.FileName varchar(255) --\
> DECLARE @.Text1 nvarchar(4000) --|
> DECLARE @.FS int --|
> DECLARE @.OLEResult int -- \
> DECLARE @.FileID int -- /The bloc variables for work with files
> DECLARE @.hr int --|
> DECLARE @.source varchar(30) --|
> DECLARE @.desc varchar (200) --/
> DECLARE @.text NVARCHAR(4000) --bufer only
> declare @.cur_edit cursor
> declare @._kfvalue sql_variant --Return value
> DECLARE @.sqlXmlQuerry NVARCHAR(4000) --Variable for query
> BEGIN
> ----
--
> EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
> OUTPUT --create object for work with file system
> IF @.OLEResult <> 0 --chek errors
> BEGIN
> PRINT 'Scripting.FileSystemObject'
> GOTO Error_Handler
> END
> SET @.FileName=@.FileNameInput
> execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
> @.FileName --Create text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'CreateTextFile'
> GOTO Error_Handler
> END
> ----
--
> IF LEN(@.headInput)>0 --Check prefix empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
> --Write to text file prefix
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> ----
--
> SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
> SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
> Server};SERVER='+@.@.SERVERNAME+''','''+@.s
qlXmlQuerry+''') ' --Adapt query
> for
> cursor
> SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+'
> open
> @.cur_edit ' --Add to query string declaration and opening cursor
> --PRINT @.sqlXmlQuerry --for debug
> exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
> sql_variant', @.cur_edit output, @._kfvalue --Execute the string
> FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
> WHILE (@.@.fetch_status = 0)
> BEGIN
> ----
--
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add
> to
> text file value from @.text
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> FETCH NEXT FROM @.cur_edit INTO @.text
> END
> CLOSE @.cur_edit
> DEALLOCATE @.cur_edit
> ----
--
> IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
> --Write postfix expression to text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> ----
--goto
> Done
> Error_Handler:
> PRINT '*** ERROR ***'
> EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
> SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description =
> @.desc
> Done:
> EXECUTE @.OLEResult = sp_OADestroy @.FileID
> EXECUTE @.OLEResult = sp_OADestroy @.FS
> END
> GO
>
> PS Sorry for my english...
>|||"Michael Rys [MSFT]" wrote:
> You can use sp_OA stored procs, but it is not something that I recommend
> (note that I say "should" and not "it is the only way" :-)).

> Thanks for the posting though and no worries about the English. TSQL cross
es
> language-boundaries :-)
> Michael
You right of all points of course ;)
Why you not recommend use sp_OA stored procs? I know, it is not TSQL in pure
form, but it is one of ways to to solve the problem... Can you describe a
problems wich may appear?|||It is very easy to shoot yourself into the foot and corrupt the server if
you are not careful.
And it has many not so clear interactions (thread safety, memory etc) that
you need to be aware of.
So this is clearly black-belt territory.
Finally, I rather have us provide solutions to these problems instead of
letting us say: "Oh, but sp_OA stored procs can do it" :-).
Best regards
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:E3855E10-23B3-43EA-A416-B743C71E2E0E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> You right of all points of course ;)
> Why you not recommend use sp_OA stored procs? I know, it is not TSQL in
> pure
> form, but it is one of ways to to solve the problem... Can you describe a
> problems wich may appear?