Can i insert bulk data to remote sqlserver

snehasish

hi, My problem is that when i want to insert bulk data to my local sqlserver database it execute successfully.the code is:

insert into xmlproperty select * from openrowset(bulk 'C:\celtic\App_Data\PW_XMLfeed_15224.xml',single_clob) as XmlColumn

But when i insert same in remote database it generate error:

Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the bulk load statement.

My question is can i insert bulk data to sqlserver like this way.if not, how can i insert? wating for reply. Thanks snehasish

Wendelius

I think you need ADMINISTER BULK OPERATIONS server privilege.

The need to optimize rises from a bad design.My articles[^]

snehasish

i am using share hosting so sqlserver and xml file is in different places.can i insert this xml data to remote sql server using bulk copy?Does both database and file need to remain in same server?

Wendelius

snehasish wrote:

can i insert this xml data to remote sql server using bulk copy

Yes you can but the SQL Server must have access to the file. Most likely you need to use domain credentials for the account under which the SQL Server is executed and make sure that this account has access to the file via network. Also use UNC-paths to specify the location.

snehasish wrote:

Does both database and file need to remain in same server

As I described they don't have to, but it makes things a whole lot easier.

The need to optimize rises from a bad design.My articles[^]

snehasish

thanks.i will talk with hosting company according to your advice.

Wendelius

snehasish wrote:

thanks

No problem :) If for some reason it becomes a problem to get access from host to the client, you always have other options. For example: - you could create a stored procedure which takes the xml-data as a parameter. After that you load the data into the xmlproperty-table inside the procedure (not necessarily using file in this case). - you could use ftp or something else to upload the file to the database server (or near it) and after that you use openrowset from the ftp target location - if you're using .Net framework at client side, you could use SqlBulkCopy class at the client to load the data to the table etc.

The need to optimize rises from a bad design.My articles[^]

snehasish

hi, Many thanks for giving possible soln. i have tried this one:

........
ds.readxml("http://www.myweb.com/xyz.xml")
Dim xmldoc As New XmlDataDocument(ds)
Dim xd As String = xmldoc.OuterXml()
Dim con As New SqlConnection(constr)
Dim sqlstr As String = "insert into xmlproperty values( convert (xml," & xd & " ))"
Dim com As New SqlCommand(sqlstr, con)
con.Open()
Dim i As Integer = com.ExecuteNonQuery()
con.Close()
.........

first i fill dataset by readxml() method.Then i take xml in variable xd.then i am trying to insert this xml to xmlproperty table.But i get an error:

Incorrect syntax near '<'. The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure. asp.net

how to fix this error. Thanks.

snehasish

hi, i still getting sql error for this code: ........ ds.readxml("http://www.myweb.com/xyz.xml") Dim xmldoc As New XmlDataDocument(ds) Dim xd As String = xmldoc.OuterXml() Dim con As New SqlConnection(constr) Dim sqlstr As String = "insert into xmlproperty values( convert (xml," & xd & " ))" Dim com As New SqlCommand(sqlstr, con) con.Open() Dim i As Integer = com.ExecuteNonQuery() con.Close() ......... Error:Incorrect syntax near '<'. The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure. Any help me.. Thanks Snehasish :(

Wendelius

snehasish wrote:

Dim sqlstr As String = "insert into xmlproperty values( convert (xml," & xd & " ))"

snehasish wrote:

Incorrect syntax near '<'. The label 'http' has already been declared

Don't concatenate the xml to the SQL statement. Instead use parameters (see: SqlParameter[^]).

The need to optimize rises from a bad design.My articles[^]

snehasish

Hi; For some reason i had to go to out of station.According to your suggestion i have solved this.I think you are great.All of your given possible soln is very good.Thank you again.Thanks a lot. :) :-D :) :-D this is my code in vb.net:

Dim ds As New DataSet
ds.ReadXml("http://www.myweb.com/abc.xml")
Dim doc As New XmlDataDocument(ds)
Dim con As New SqlConnection("Server=x.x.x.x;Database=celtic;User ID=xxxx;Password=xxxx;Trusted_Connection=False")
Dim com As New SqlCommand("sp_InsertXmlData", con)
com.CommandType = CommandType.StoredProcedure
com.Parameters.Add("@XMLDOC", SqlDbType.Text).Value = doc.OuterXml
con.Open()
Dim i As Integer = com.ExecuteNonQuery()
con.Close()

This is stored procedure:

Create PROCEDURE [dbo].[sp_InsertXmlData]
@XMLDOC text
AS
BEGIN
insert into xml_properties(xmldoc)
values(@XMLDOC)
END

snehasish

Wendelius

Thanks for your kind words :-O

The need to optimize rises from a bad design.My articles[^]