SQL / XML People - in here!

[sist_si]

Hi guys,
I have a VBScript file which is running on a server, pulling XML from a URL and creating an XML file. This file then needs to be imported into a database and stored in an XML field. It's about 30MB in size.

Now, I created a stored proc to do the importing and it works fine when I test it from Management Studio 2005 but when I try to call it from my .vbs script it doesn't error but it also doesn't do anything!
I have a feeling it's maybe a permission thing? I'm not 100% sure though. Any help you can give would be fantastic.

Here are the code snippets:

VBS File
--------------------------------
strSQL = "exec up_insert_xmlfile '" & defaultDir & "','" & strXMLFile & "'"
MsgBox strSQL

Set dataConn = CreateObject("ADODB.Connection")
dataConn.Open strConnect

Set rsUpdate = dataConn.execute(strSQL)
Set rsUpdate = Nothing

dataConn.Close
Set dataConn = Nothing


Stored Procedure:
--------------------------------
EXEC('INSERT INTO dbo.xmlData(feedTypeID, xml_feed)
SELECT 4, xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @filePath + @fileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')

 

[GravyMonster]

what does strConnect contain? It should have a username and password of a dbuser which has the acces rights for the database.

You may also want to add the below to the vbscript:

on error resume next

[i][what you already have in here][/i]

if err.number <> 0  then
  msgbox(err.number & " - " & err.description)
  end
end if

 

[sist_si]

Cheers Spunkey. The VBS script has a lot more going on (inc. error handling). I've just provided the important snippet

Anyway, I've just confirmed it is a permission prob. when i run the vbs file as 'sa' it works. just gotta figure out what to grant a standard user to get it to work for them.
Any ideas on that one????

 

[GravyMonster]

No worries. I thought after I posted it that if you could knock a script together that imports frmo XML you've probably heard of error handling

For the script to run you should just need db_datareader and db_datawriter.

 

[sist_si]

hmm, strange. i have that on my user i'm connecting with and nowt