kwilliams Posted June 26, 2009 Share Posted June 26, 2009 I have a stored procedure (SP) that imports 2 internal XML feeds into a SQL Server 2005 table using Bulk Load/OPENROWSET without a problem. But I'd also like to import one more external XML feed that's not located on my domain into that same SP. When I enter the URL to that XML feed, I get this error message when I run the job:Msg 4861, Level 16, State 1, Line 2Cannot bulk load because the file "http://codeamber.org/a1xl04act/amberalert.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).(1 row(s) affected)From research, I'm *thinking* this is an issue regarding accessing data from another site/domain, but I can't say for sure. I'm able to do this in ASP.NET/VB.NET code without a problem, so I'm not sure why it wouldn't work within SQL Server 2005 somehow. I'm including the entire SP code below. If anyone can let me know what I'm doing wrong, it would be greatly appreciated. Thanks.spXMLImport: USE [DATABASENAME]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spXMLImport]ASBEGINSET NOCOUNT ON;DROP TABLE tblXMLFeedsCREATE TABLE tblXMLFeeds( record_id VARCHAR(50), xmlTitle VARCHAR(100), xmlFileName VARCHAR(300), xml_data xml, datestamp VARCHAR(23))DECLARE @record_id_aa VARCHAR(50)DECLARE @record_id_bb VARCHAR(50)DECLARE @record_id_cc VARCHAR(50)DECLARE @xmlTitle_aa VARCHAR(100)DECLARE @xmlTitle_bb VARCHAR(100)DECLARE @xmlTitle_cc VARCHAR(100)DECLARE @xmlFileName_aa VARCHAR(300)DECLARE @xmlFileName_bb VARCHAR(300)DECLARE @xmlFileName_cc VARCHAR(300)DECLARE @datestamp VARCHAR(23)SELECT @record_id_aa = 'aaID'SELECT @record_id_bb = 'bbID'SELECT @record_id_cc = 'ccID'SELECT @xmlTitle_aa = 'Internal Feed 1'SELECT @xmlTitle_bb = 'Internal Feed 2'SELECT @xmlTitle_cc = 'External Feed'SELECT @xmlFileName_aa = '\\SERVERNAME\DIRECTORY\docs\xml\internalfeed1.xml'SELECT @xmlFileName_bb = '\\SERVERNAME\DIRECTORY\docs\xml\internalfeed2.xml'SELECT @xmlFileName_cc = 'http://codeamber.org/a1xl04act/amberalert.xml' --<<<----EXTERNAL FEED--******************I'VE ALSO TRIED THE FOLLOWING:******************--SELECT @xmlFileName_cc = '//codeamber.org/a1xl04act/amberalert.xml'--SELECT @xmlFileName_cc = '\\codeamber.org\a1xl04act\amberalert.xml'--SET datestamp as ISO-8601 datetime formatSELECT @datestamp = CONVERT(VARCHAR(23), GETDATE(), 126)EXEC('INSERT INTO tblXMLFeeds(record_id, xmlTitle, xmlFileName, xml_data, datestamp)SELECT ''' + @record_id_aa + ''', ''' + @xmlTitle_aa + ''', ''' + @xmlFileName_aa + ''', xmlData, ''' + @datestamp + ''' FROM( SELECT * FROM OPENROWSET (BULK N''' + @xmlFileName_aa + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)INSERT INTO tblXMLFeeds(record_id, xmlTitle, xmlFileName, xml_data, datestamp)SELECT ''' + @record_id_bb + ''', ''' + @xmlTitle_bb + ''', ''' + @xmlFileName_bb + ''', xmlData, ''' + @datestamp + '''FROM( SELECT * FROM OPENROWSET (BULK N''' + @xmlFileName_bb + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)INSERT INTO tblXMLFeeds(record_id, xmlTitle, xmlFileName, xml_data, datestamp)SELECT ''' + @record_id_cc + ''', ''' + @xmlTitle_cc + ''', ''' + @xmlFileName_cc + ''', xmlData, ''' + @datestamp + '''FROM( SELECT * FROM OPENROWSET (BULK N''' + @xmlFileName_cc + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)')END Link to comment Share on other sites More sharing options...
justsomeguy Posted June 26, 2009 Share Posted June 26, 2009 SQL Server just needs a file that's either local or on a network share, it can't load a remote URL. Link to comment Share on other sites More sharing options...
kwilliams Posted July 7, 2009 Author Share Posted July 7, 2009 SQL Server just needs a file that's either local or on a network share, it can't load a remote URL.I received this response from someone else:Should use SSIS package to do this...1st step, import the file to your domain. Then do a bulk insert.Are you saying that what's mentioned is not possible? Thanks for your help. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 7, 2009 Share Posted July 7, 2009 I don't know if you need a certain package to do it, but you do need to have the file locally accessible (that's what "import the file to your domain" means). Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.