Importing XML File via URL using MSSQL 2005

I have an XML file that is hosted on a mainframe web server. I need to read
the file via a URL and import this into my SQL database.
I'm fairly new to SQL 2005 but this seems to be a simple task. I've looked
at using the Import Export wizard but I don't see a simpl way to do this.
I've tried selecting "Flat File" as my data source but this does not seem to
be able to read XML. I've tried the SQLSML data sources but I don't think
this will work if the web server is not IIS.
Does anyone have any ideas?"Bryan" <Bryan@.discussions.microsoft.com> wrote in message
news:37B66D17-FE91-4CFD-B502-DB80BD2B8360@.microsoft.com...
>I have an XML file that is hosted on a mainframe web server. I need to
>read
> the file via a URL and import this into my SQL database.
> I'm fairly new to SQL 2005 but this seems to be a simple task. I've
> looked
> at using the Import Export wizard but I don't see a simpl way to do this.
> I've tried selecting "Flat File" as my data source but this does not seem
> to
> be able to read XML. I've tried the SQLSML data sources but I don't think
> this will work if the web server is not IIS.
> Does anyone have any ideas?
Write a SQLCLR function in C# or VB to retrieve the data from the web
server.

importing queries from access

im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
mark
Replied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>

importing queries from access

im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissio
nAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_Oth
erBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCo
mmission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions
.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,&#
91;Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,&#
91;Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipeagreen">
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusineeagreen">
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissioneagreen">
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBueagreen">
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,
[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,
[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>sql

importing queries from access

im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>

Importing MySQL BLOB to MSSQL

Hi all!
I'm trying importing a MySQL database to my MSSQL server. I've donwloaded the MySQL ODBC drivers and I can connect succesfully throught a DSN to my MySQL server. Everything works fine EXEPT my BLOB field. I'm using a BLOB field for storing a binary file.

When I choose preview while importing my bob field is empty.

I'm desperate!

:eek:Viewing a BLOB is pretty much a waste of time. Use DTS or a linked server to move it, and life should be good.

-PatP

Importing from PL/SQL to MSSQL

Hi there,

I've done some searching around on this subject and can't find
anything helpful or non-expensive so far.

I need to export some tables from my companies Oracle server locally
so I can work on a reporting tool which (hopefully) will speed some
things up on our weekly report runs. I know I could just use an ODBC
link and get the data directly from our server but unfortunatly it's
not the newest or fastest of things and these are pretty big reports,
so my idea is to export the tables and run the queries through an
MSSQL database on a faster machine.

However, not being an Oracle or MSSQL developer I'm not too sure how
to export the tables I need. I've looked all over for a script or a
tool to help me do this, but all I have found are tools that cost a
hell of a lot of money, and for something that I don't even know will
work just yet, I don't want to put a PO through for nothing.

I was wondering if anyone has ever used a script that is available or
a GPL'd tool that might help me out here? Or at least point me in the
direction so that I can get started on this.

Thanks in advance for any suggestions

--
StuartIIRC there is a SPOOL command that will redirect the ouput of a SELECT
statement to a file. It should be fairly easy to import that to MSSQL
using DTS or BCP. However, there are probably several ways of doing
this and I'm not an Oracle expert so for better advice you may want to
repost in an Oracle group.

--
David Portas
SQL Server MVP
--|||SQL Server DTS is your best bet for this.

Importing Errors when using BCP

Alright, I've been getting an interesting error when using BCP between two databases on the same MSSQL Server 2000. I am bulk copying data from an older version of a database into a newer one, which includes new tables and columns. When bcp gets to a certain table, I get this error

SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

If anyone would happen to know exactly what SQL is executed by BCP or where to find such information, that would be great. None of the columns' data types have been changes in the newer version of the database.
-MikeYou migt try running profiler while using BCP but I don't think you will find much. The error seems to indicate a problem converting data.

Could you post the table structures, BCP command(s) and any BCP format files?|||DTS might give you more of a clue to where the actual error is.

I had a problem like this once that turned out to be typos in date fields that were before 1/1/1753 or whatever the magic "start date" in SQL server is (like '12/31/0999' instead of '12/31/1999').|||You should get a nice picture of the problem with the -o(filename) switch on BCP. The output file will just contain the rows that are causing the errors. I had a similar problem with "Right string truncation" errors in BCP, and the -o spotlighted the problem immediately.

Importing a TXT file to MSSQL

hi, I have a TXT file with several fields with semicolom between fields.

But after Importing is done, Decimal field Dont appears with
comma.

in other words , if I type in a DataBase using Enterprise Manager 123,45678 I get exactily 123,45678, but from
Txt file I get 1234568.

what do i missing?
Tks
Carlos Lages
ps. Txt files is "12345678" even I put Txt file "123,45678"
I get the same result in databaseIn your table definition is the field a character field or numeric? What data type is it? This is just a thought, but maybe during the import job, DTS? is treating the field as some form of numeric field and ignoring the "," as that is not relevant..

Hope this helps, or am i on the wrong track?

Originally posted by Carlos lages
hi, I have a TXT file with several fields with semicolom between fields.

But after Importing is done, Decimal field Dont appears with
comma.

in other words , if I type in a DataBase using Enterprise Manager 123,45678 I get exactily 123,45678, but from
Txt file I get 1234568.

what do i missing?
Tks
Carlos Lages
ps. Txt files is "12345678" even I put Txt file "123,45678"
I get the same result in database