Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Error with XML document insert SQL 2005

Posted on 2007-09-27
13
Medium Priority
?
226 Views
Last Modified: 2008-02-07
I am trying to create a stored procedure in SQL 2005 to insert a XML document in to a table - when saving I receive the following error  " Inncorrect syntex near '(' "

Can anyone see anything wrong with the following?

DECLARE @HANDLEXML INTEGER

EXEC SP_XML_PREPAREDOCUMENT @HANDLEXML OUTPUT, @XMLTEXT

INSERT INTO back_issues
      (
      back_issue_id, Title_code, Variant_code, Price, Stock, Issue_year, Issue_month, Issue_day
      )
SELECT  Back_Issue_Id, Title_code, Variant_Code, Price, Stock, Issue_year, Issue_month, Issue_day
FROM OPENXML (@HANDLEXML, '/NewDataSet/Table', 2) WITH
      (
      Back_Issue_Id INT,Title_code VARCHAR(255),Variant_Code VARCHAR(255),Price MONEY,Stock VARCHAR(50),Issue_year VARCHAR(50),Issue_month VARCHAR(255),Issue_day VARCHAR(50)
      )

EXEC SP_XML_REMOVEDOCUMENT @HANDLEXML
0
Comment
Question by:Molior
  • 6
  • 3
  • 3
12 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 19975655
Please post the entire stored procedure as it stands there is nothing intrinsically wrong with that code.
0
 

Author Comment

by:Molior
ID: 19976969
CREATE PROCEDURE spBackIssues_BulkInsert
      (
            @XMLTEXT NTEXT,
            @BackupFile VARCHAR(255)
      )
AS

SET XACT_ABORT ON
BEGIN TRANSACTION

-- Backup table data to text file
EXECUTE spExportTable_ToFile
'SELECT *  FROM ocs_uk.dbo.back_issues ORDER BY back_issue_id',
@BackupFile

-- Bulk insert of new data from XML
DECLARE @HANDLEXML INTEGER

EXEC SP_XML_PREPAREDOCUMENT @HANDLEXML OUTPUT, @XMLTEXT

INSERT INTO back_issues
      (
            back_issue_id, Title_code, Variant_code, Price, Stock, Issue_year, Issue_month, Issue_day
      )
SELECT  Back_Issue_Id, Title_code, Variant_Code, Price, Stock, Issue_year, Issue_month, Issue_day
FROM OPENXML (@HANDLEXML, '/NewDataSet/Table', 2) WITH
      (
            Back_Issue_Id INT,Title_code VARCHAR(255),Variant_Code VARCHAR(255),Price MONEY,Stock VARCHAR(50),Issue_year VARCHAR(50),Issue_month VARCHAR(255),Issue_day VARCHAR(50)
      )

EXEC SP_XML_REMOVEDOCUMENT @HANDLEXML

COMMIT TRANSACTION
0
 

Author Comment

by:Molior
ID: 19976980
The SQL error I receive is:

Msg 102, Level 15, State 1, Procedure spBackIssues_BulkInsert, Line 32
Incorrect syntax near '('.

when trying to save the above from both Visual Studio 2005 and if trying directly in SQL 2005.

Many thanks.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 19980080
I still do not see anything that would cause that type of error.  After I commented out the line:
EXECUTE spExportTable_ToFile
'SELECT *  FROM ocs_uk.dbo.back_issues ORDER BY back_issue_id',
@BackupFile

So that I would not get the following error:
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'spExportTable_ToFile'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

The stored Procedure compiled fine.  Can you post a typical Xml document so that I can fully test it out?
0
 

Accepted Solution

by:
Molior earned 0 total points
ID: 19997649
Looks like I have found the answer to this problem, the database was imported from a previous version of MSSQL (7) and was still running in version 7 compatibility mode, changing this to 2005 seems to have fixed the issue although I dont understand why.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20000403
>>although I dont understand why.<<
That is because SQL Server 7 did not support OPENXML() or any of the Xml functions available in SQL Server 2000.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20000430
>>Please leave any recommendations here.<<
I think my answer speaks for itself.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20027451
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20061694
So pray tell, why even bother asking for recommendations if you are going to promptly ignore them?

Anthony
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20061785
I take it that you think the words "I think my answer speaks for itself." constitute a 'recommendation'?

For starters, I have no way of knowing which comment you consider your 'answer' to be.

I work all day, every day moderating between Askers and Experts.
If you expect me to help you resolve a disagreement, you need to give me enough information to do so.

Vee_Mod
Community Support Moderator
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20061836
Fair enough.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20061897
Good to go.
See you around the Zones.
V
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question