Bulk Load XML file to SQL Server (Express) Table

Hi All,

I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?

Thanks,

Claude.

Here is a working sample from Microsoft. Hope this helps.

http://support.microsoft.com/default.aspx/kb/316005/en-us

|||

Thanks for your reply.

Are there any examples of doing this in asp.net 2.0 using version 2 of the .Net framework? I am currently writing my app in c#.

TIA,

Claude.

|||

The data is going into SQL Server so you have to use XML SQL Server will accept. The links below covers SQL Server 2005 XML. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

http://msdn2.microsoft.com/en-us/library/ms191184.aspx

sql

BULK INSERT STATEMENT PROBLEM... ^^

Hi all,

I want to run BULK INSERT command from ASP.NET.. But, I get Error "You dont have permission to use BULK INSERT Command...". And i try to find out the solution from Internet and a lot of people say that I have to create user under Sysadmin or BULKadmin role.

But I dont see Sysadmin and BULKadmin from my MSSQL Enterprise manager. What I see is only Admin and Sys... Any solution??

Thanks

Suigion

Hi,

use sp_addsrvrolemember to add

SysAdmin and BulkAdmin are types of fixed server roles. You can find them under Security, Server Roles node in EM.

Hope this helps you

BULK INSERT PROBLEM

I am trying to BULK INSERT a unix data file (.csv) into temperary table, then replace all CHAR(13) into CHAR(10) or '\n'. In Classic ASP, I was using Replace(text,char(13),vbcrlf), but now in MS SQL, so I use Replace(@.text,char(13),'\n').
After finished converting, I will then BULK INSERT into a load_table with rowterminator, firstrow and fieldterminator. But, I got this Error:

============================================

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

==============CODE========================

DECLARE @.sLoadFileLayout AS NVARCHAR(1000)
DECLARE @.SQL AS NVARCHAR(1000)
DECLARE @.sFieldName AS NVARCHAR(1000)
DECLARE @.iRow AS INT
DECLARE @.TextLine AS NVARCHAR(4000)
DECLARE @.fieldTerminator AS char(3)

SET @.sLoadFileLayout = 'C:/folder/file.CSV'

CREATE TABLE #textfile (line varchar(8000))
SET @.SQL = 'BULK INSERT #textfile FROM ''' + @.sLoadFileLayout + ''''
EXEC sp_executesql @.SQL

DECLARE TableMap CURSOR FOR SELECT * FROM #textfile
OPEN TableMap
FETCH NEXT FROM TableMap INTO @.TextLine

SET @.iRow = 0
SET @.SQL = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.iRow = @.iRow + 1
IF @.iRow >= 1
BEGIN
IF @.SQL <> '' SET @.SQL = @.SQL + ','
SET @.sFieldName = REPLACE(@.TextLine, char(10),'\n')
SET @.SQL = @.SQL + ' ' + @.sFieldName
END
FETCH NEXT FROM TableMap INTO @.TextLine
END

CLOSE TableMap
DEALLOCATE TableMap

DROP TABLE #textfile

BULK INSERT MARKETING.DBO.LOAD_Table FROM #textfile WITH (DATAFILETYPE='char',FIELDTERMINATOR=',',FIRSTROW=4,ROWTERMINATOR='\n')

Strange. I tested your script and got the same rows in table #textfile and Load_Table. Seems your code works fine I'm using SQL2000, here is the content of the csv file:

One user can only have one user instance.
Replication will be disabled.
User Instance does not support SQL Server Authentication. Only Windows Authentication is supported.
The network protocol support for user instances is local named pipes only.
The user instance shares the registry entries of the parent instance.
There is no support for user instance with native code. This feature is only supported with ADO .NET.
WMI Provider for Server Events will not be supported on the dynamically spawned user instances. This should still work on the parent SQL Server Express instance. For more information on WMI provider see WMI Provider for Server Events in SQL Server 2005 Books Online.

BTW, I think you do not need to replace char(10) or char(13) with '\n', because SQL does accept carriage return.

Bulk Insert not working in ASP

---------------------------

I have the following ASP code:

'load the conversion_dump_daily table
Response.Write("<br>Load Conversion_Dump_Daily data for " & theDate & ".")
Response.Write("<br> File Name: " & inputFile & ".")
Response.flush
cmd.CommandText = "AddConversionDumpDaily"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("InFile", adVarChar, , 1000, "D:\DataSources\ConversionBuilder\test.txt")
On Error Resume Next
cmd.Execute
cmd.Parameters.Delete("InFile")

..... asp error checking follows

---------------------------

The stored procedure is as follows:

CREATE PROCEDURE AddConversionDumpDaily @.InFile varchar(1000)
AS
DECLARE @.ErrorSave int
SET @.ErrorSave = 0
-- Create a transaction so that a rollback can be performed in case of an error.
BEGIN TRAN
-- Bulk insert Conversion data from csv file.
EXEC('BULK INSERT Conversion_Dump_Daily FROM ''' + @.InFile + ''' WITH (FORMATFILE=''D:\FormatFiles\ConversionBuilder.fmt '') ')

-- Check to see if the insert was successful
if @.@.error <> 0
begin
SET @.ErrorSave = @.@.error -- Store the error code in a variable.
rollback tran
goto endOfBatch
end
COMMIT TRAN
endOfBatch:
RETURN @.ErrorSave -- Return the error code to determine success or failure of the process.
GO

---------------------------

The stored procedure works fine from the Query Analyzer, but nothing is inserted from the ASP page. I don't get any errors... it is just that nothing appears!

Any ideas anyone?I am not sure if you left out the code, but where are you creating the command object and adding the connection information. Also, you will not receive an error when you are using "On Error Resume Next" before your execute statement. By removing this, you will see the problem.

Good luck.

Building custom Reporting Services Toolbar

We have an ASP.Net web app that uses RS for reporting. We can't use the RS
toolbar since we will be generating reports using the SOAP API. Does
anybody have any ideas/suggestions/examples on building a custom toolbar for
RS? Any input will be greatly appreciated. TIA.You just need to create a user control that calls the RS SOAP API for the
functionality you need, e.g. Parameters, etc. The AwReporterWeb project
included in my book source code
(http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
demonstrates how you can get a list of reports and parameters. More involved
scenarios are implemented in the AwReporterWin WinForm demo (e.g. obtaining
the parameters values, multi-value parameters, etc.).
As a side note, you may find my AwReportViewer control useful
(http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
handle images, dataset binding (using my custom dataset extension) and more
:-)
Finally, please note that by going SOAP you are losing not only the report
toolbar but also report interactive features (at least in version 1.0 of
RS), as well as simplicity.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> We have an ASP.Net web app that uses RS for reporting. We can't use the
RS
> toolbar since we will be generating reports using the SOAP API. Does
> anybody have any ideas/suggestions/examples on building a custom toolbar
for
> RS? Any input will be greatly appreciated. TIA.
>
>|||One more thing... you can save yourself quite a bit of development time if
you could postpone the toolbar implementation until the RS 2005 controls are
out. They will include a custom toolbar, events, and will support both
connected and disconnected mode. The first public drop of the controls will
be in Yukon Beta 3 which is just a few months away.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
> You just need to create a user control that calls the RS SOAP API for the
> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
> included in my book source code
> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
> demonstrates how you can get a list of reports and parameters. More
involved
> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
obtaining
> the parameters values, multi-value parameters, etc.).
> As a side note, you may find my AwReportViewer control useful
> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
> handle images, dataset binding (using my custom dataset extension) and
more
> :-)
> Finally, please note that by going SOAP you are losing not only the report
> toolbar but also report interactive features (at least in version 1.0 of
> RS), as well as simplicity.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> > We have an ASP.Net web app that uses RS for reporting. We can't use the
> RS
> > toolbar since we will be generating reports using the SOAP API. Does
> > anybody have any ideas/suggestions/examples on building a custom toolbar
> for
> > RS? Any input will be greatly appreciated. TIA.
> >
> >
> >
>|||Thanks Leo. I've been planning on getting your book for some time now, but
haven't gotten around to it... yet. Now I will have to :-)
I would have prefered to use URL access, and custom authentication, but we
need to upgrade to Enterprise Edition to do that, and the cost is a bit too
much. I understand that the reports will lose some of its interactive
features if I use SOAP API.
"Teo Lachev" <teo@.prologika.com> wrote in message
news:uSDp6g4yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> One more thing... you can save yourself quite a bit of development time if
> you could postpone the toolbar implementation until the RS 2005 controls
> are
> out. They will include a custom toolbar, events, and will support both
> connected and disconnected mode. The first public drop of the controls
> will
> be in Yukon Beta 3 which is just a few months away.
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
>> You just need to create a user control that calls the RS SOAP API for the
>> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
>> included in my book source code
>> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
>> demonstrates how you can get a list of reports and parameters. More
> involved
>> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
> obtaining
>> the parameters values, multi-value parameters, etc.).
>> As a side note, you may find my AwReportViewer control useful
>> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
>> handle images, dataset binding (using my custom dataset extension) and
> more
>> :-)
>> Finally, please note that by going SOAP you are losing not only the
>> report
>> toolbar but also report interactive features (at least in version 1.0 of
>> RS), as well as simplicity.
>> --
>> Hope this helps.
>> ---
>> Teo Lachev, MVP [SQL Server], MCSD, MCT
>> Author: "Microsoft Reporting Services in Action"
>> Publisher website: http://www.manning.com/lachev
>> Buy it from Amazon.com: http://shrinkster.com/eq
>> Home page and blog: http://www.prologika.com/
>> ---
>> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
>> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
>> > We have an ASP.Net web app that uses RS for reporting. We can't use
>> > the
>> RS
>> > toolbar since we will be generating reports using the SOAP API. Does
>> > anybody have any ideas/suggestions/examples on building a custom
>> > toolbar
>> for
>> > RS? Any input will be greatly appreciated. TIA.
>> >
>> >
>> >
>>
>|||You don't have to :-)
I guess at the end you would you need to determine if the money you will
save by using Standard justifies the development effort getting around SOAP
addressability limitations. On the upside, it looks like the 2005 controls
will support drilldown with SOAP.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:ej40s59yEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Leo. I've been planning on getting your book for some time now,
but
> haven't gotten around to it... yet. Now I will have to :-)
> I would have prefered to use URL access, and custom authentication, but we
> need to upgrade to Enterprise Edition to do that, and the cost is a bit
too
> much. I understand that the reports will lose some of its interactive
> features if I use SOAP API.
> "Teo Lachev" <teo@.prologika.com> wrote in message
> news:uSDp6g4yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> > One more thing... you can save yourself quite a bit of development time
if
> > you could postpone the toolbar implementation until the RS 2005 controls
> > are
> > out. They will include a custom toolbar, events, and will support both
> > connected and disconnected mode. The first public drop of the controls
> > will
> > be in Yukon Beta 3 which is just a few months away.
> >
> > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
> >> You just need to create a user control that calls the RS SOAP API for
the
> >> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
> >> included in my book source code
> >> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
> >> demonstrates how you can get a list of reports and parameters. More
> > involved
> >> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
> > obtaining
> >> the parameters values, multi-value parameters, etc.).
> >>
> >> As a side note, you may find my AwReportViewer control useful
> >> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45)
to
> >> handle images, dataset binding (using my custom dataset extension) and
> > more
> >> :-)
> >>
> >> Finally, please note that by going SOAP you are losing not only the
> >> report
> >> toolbar but also report interactive features (at least in version 1.0
of
> >> RS), as well as simplicity.
> >>
> >> --
> >> Hope this helps.
> >>
> >> ---
> >> Teo Lachev, MVP [SQL Server], MCSD, MCT
> >> Author: "Microsoft Reporting Services in Action"
> >> Publisher website: http://www.manning.com/lachev
> >> Buy it from Amazon.com: http://shrinkster.com/eq
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >>
> >> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> >> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> >> > We have an ASP.Net web app that uses RS for reporting. We can't use
> >> > the
> >> RS
> >> > toolbar since we will be generating reports using the SOAP API. Does
> >> > anybody have any ideas/suggestions/examples on building a custom
> >> > toolbar
> >> for
> >> > RS? Any input will be greatly appreciated. TIA.
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>

Building an application on multiple data sources

ASP.NET on SQL Server

I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs

Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?

Any thoughts would be appreciated.

Thanks

William BalmerWilliam wrote:

Quote:

Originally Posted by

ASP.NET on SQL Server
>
I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs
>
Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?

Look into the Linked Server features of SQL server. You can link to the
Oracle db & run queries against the Oracle db thru SQL server. This may
reduce the maintenance - you'll only be writing in SQL Server syntax, or
calling SQL Server stored procedures that query the Oracle db.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)|||"Linked Server" can be one way to do this. However general network
latency and throughput restrictions due to bandwidth limitations or
network traffic, etc. can completely kill joins across the wire.

Also, be prepared to write views on the Oracle box to convert Oracle
data types to be more compatible with Sql Servers needs even using
linked servers.

Another option to consider it to "replicate" the data from the Oracle
box onto a Sql Server box, avoiding the need for joins across the wire.
This is the option I had to take to get acceptable performance for one
job I did.

William wrote:

Quote:

Originally Posted by

ASP.NET on SQL Server
>
I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs
>
Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?
>
Any thoughts would be appreciated.
>
Thanks
>
William Balmer

Build database application on Internet with ISP?

I have been building ASP, ASP.NET and Crystal Reports on SQL server
for a few years now.

I have build them mostly on an Intranet or with companies that have
their own web servers.

I now need to build an application for a company that doesn't not have
a web server. Just a small shop that wants an internet application.
How do I build an database application when the site will be host by
an ISP? The database will be SQL Server 2000, but located locally.
The site will be maintained by the ISP? How does the an application
hosted by an ISP interact with a database located locally (client's
site)?

If someone could point me toward some tutorial or tell me how to do
this, I would appreciate it."CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0406030344.5e22f306@.posting.google.c om...
> I have been building ASP, ASP.NET and Crystal Reports on SQL server
> for a few years now.
> I have build them mostly on an Intranet or with companies that have
> their own web servers.
> I now need to build an application for a company that doesn't not have
> a web server. Just a small shop that wants an internet application.
> How do I build an database application when the site will be host by
> an ISP? The database will be SQL Server 2000, but located locally.
> The site will be maintained by the ISP? How does the an application
> hosted by an ISP interact with a database located locally (client's
> site)?
> If someone could point me toward some tutorial or tell me how to do
> this, I would appreciate it.

You may want to consider looking for an ISP who can host MSSQL for you, as
that would help to avoid the security issues of exposing a database server
to the internet. Otherwise, if you can set up a VPN from the ISP's web
server to your MSSQL server, that might be a possible alternative. The web
server would need to have the MSSQL client tools, and it would connect like
any other client.

Simon