GoDaddy Error Running Script

Topics: Database Publishing Wizard, Hoster Discussion
Apr 20, 2007 at 9:25 PM
I used this excellent tool to generate a script for a SQL Server 2005 Express db. I did use the "2000" switch as I realize GoDaddy only supports SQL Server 2000.

When I paste in the script and click Submit I get the following error:

Error -2147217900
Line 1: Incorrect syntax near '('.

Here's the first few lines of my script:

----------

/****** Object: ForeignKey FK_Categories_Categories Script Date: 04/20/2007 12:50:16 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.FK_Categories_Categories') AND type = 'F')
ALTER TABLE dbo.Categories DROP CONSTRAINT FK_Categories_Categories
GO
/****** Object: Default DF_Categories_NumActiveSupportRequests Script Date: 04/20/2007 12:50:16 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.DF_Categories_NumActiveSupportRequests') AND type = 'D')
BEGIN
ALTER TABLE dbo.Categories DROP CONSTRAINT DF_Categories_NumActiveSupportRequests
END

GO

----------

I read the issues with GOTO, but I don't have any GOTO statements in the generated script. Even so, I moved the GO statement around to see if that would help and no luck.

This has to be a fairly common issue for those of us who have to use GoDaddy's quirky database UI.

Does anyone have any ideas what the problem might be?

Patrick


Apr 21, 2007 at 12:01 AM
I have resolved this issue. Turns out the offending syntax was the use of "TOP (100) PERCENT" in the SELECT statement. I had created a View in a SQL Server 2005 Express via the GUI tools in VWD Express. I didn't notice it had generated this syntax, which I certainly didn't need. It still puzzles me why this won't work on GoDaddy's db server, at least in a CREATE VIEW statement executed in Query Analyser.
Apr 21, 2007 at 12:27 AM
OK, I'm just going to keep posting my findings here so if anyone else has these issues they won't have to go through them like I have...

While trying to get the ASPNETDB installed on GoDaddy using this same tool, I noticed that in the GoDaddy Hosting Manager my ASPNETDB I had created said Version 2005. I thought GoDaddy didn't have SQL Server 2005 databases, so this puzzled me a bit. I figured it was because I had installed the ASP.NET 2.0 schema.

Not so, I have pleasantly discovered! I dunno if GoDaddy has added SQL Server 2005 databases in the last day or so, but I deleted the database that did in fact say Version 2000 and created a new one that now says Version 2005, and I did not add the ASP.NET 2.0 schema.

I then re-ran the SQL Server Database Publishing Wizard without the targetserver switch, deleted any use of sp_addextendedproperty in the script (there were a few--this was a slightly modified version of the ASP.NET 2.0 Classifieds starter kit db), and voila! all worked without a hitch when I pasted the SQL into the GoDaddy Query Analyzer.
Apr 21, 2007 at 1:49 AM
I have also successfully installed the ASPNETDB on GoDaddy. I simply created a database (again, the default version was 2005) and this time added the ASP.NET 2.0 schema. I then used the SQL Server Database Publishing Wizard against my local ASPNETDB, but with the -dataonly switch. The generated script worked without a hitch in the GoDaddy Query Analyzer.

I didn't have to make any other changes to the ASPNETDB database.
Apr 22, 2007 at 1:15 AM
Thank you! I've been banging my head for days trying to figure out what was wrong and why my sql file would always return with syntax errors. I also tried using the Runsql.aspx strategy, but GoDaddy doesn't let you fun files like that. (Grr!)

Anyway, I too deleted my old database from GoDaddy and you're right: yes! A 2005 database was in its place. I then checked the box "create schema". I'm a novice user and didn't realize that GoDaddy tells you it's a syntax error when really they don't allow you to create your asp.net schema with the Query Analyzer and your sql file. (Double Grr!) I finally realized I had to check "create schema", and that was the only way to do it.

After that, I could upload the data using the Publishing wizard just like PBINHR said. It worked....finally!

Thank you, PBINHR for your post. I am very thankful!
Apr 23, 2007 at 5:06 PM

pbinhr wrote:
I have also successfully installed the ASPNETDB on GoDaddy. I simply created a database (again, the default version was 2005) and this time added the ASP.NET 2.0 schema. I then used the SQL Server Database Publishing Wizard against my local ASPNETDB, but with the -dataonly switch. The generated script worked without a hitch in the GoDaddy Query Analyzer.

I didn't have to make any other changes to the ASPNETDB database.


We've been aware that GoDaddy has been rolling out SQL Server 2005 in the past few days, so good to know that that solved your problem.

I'm not quite sure why the "TOP (100) PERCENT" syntax did not work on SQL Server 2000, since that syntax was valid on 2000. Perhaps it is the parentheses. We've struggled on how to properly detect when views and stored procedures reference functionality not available on the target version, since it is not easy to get things perfectly right.

alex