Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 10th, 2007, 09:14 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Another thing you might try--just a reach here--is to literally move the GOTO a space after the preceding line

So your:
IF( @ErrorCode <> 0 ) GOTO Cleanup

Would be:
IF( @ErrorCode <> 0 ) GOTO Cleanup

I remember doing this, and how I did it. I would just highlight all the text between "GOTO" and the end of the previous line (in the example case, the ")". Then hit the delete key and add a space (or simply hit the space)

Perhaps there is something the scripting tool is seeing still between the ")" and the "GOTO".

Also, another thing you can try, is to piece part the script. Highlight up to a part and try running the script to see where it breaks down. I had to do that numerous times.

Hope that helps--I really think it's still the GOTO.

Kind Regards,
Rob

 
Old May 10th, 2007, 11:24 PM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the information Imar,

I read the article you refer to from a previous post of yours, so I did not read it too well this time and missed the part about the GOTO. Sorry about that.

However this business with the GOTO is very interesting. From what the article says this means that the hosts (godaddy) management tool is not working correctly since it will not accept correct SQL syntax. That is pretty astounding considering that Godaddy themselves have not said a word about this despite me asking them directly. They blame the script and claims this is not their problem. What is good is that I can email them this article as proof.

================================================== =============

Thanks again Rob! I really appreciate your help, it is very kind of you. I should say that I did what you suggested but it still not working. The lines in previous post are still not possible to import despite the fact that I replaced the large white space with a single blank. Furthermore I cannot import the corrected SQL sample from the article you are referring to either.

Do you mind telling me what kind of account you have with godaddy, is it a shared server account? Or is it a dedicated server. I have a shared server DE LUXE account, and I am trying to import into SQL server database using the Query Analyzer.

 
Old May 10th, 2007, 11:35 PM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I can add that the error I get when I try to import the corrected sql script from the article mentioned above is>>

Could not drop object 'dbo.aspnet_Roles' because it is referenced by a FOREIGN KEY constraint

So not even Scott Guthrie can generate an sql script that is possible to import using the Godaddy tool.

Anyway Rob, I also tried your suggestion regarding importing piece by piece and you can certainly import pieces. However, you cannot import small pieces if they break up strings or reference procedures outside the selected piece, so it is difficult to pinpoint the exact offending line. Add to that there must another mysterious syntax inconsistency other GOTO, we just don't know what it is. So it is tricky.

However, a very good start for me would be to know what server plan you have. Mine might be worse than yours.

Thank you very much for your assistance.

 
Old May 11th, 2007, 07:29 AM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Well, now that is a very different error you are getting.

I'm not sure how much you know about SQL, but if you try dropping a table that is referenced as a foreign key in another table, before you drop the table in which it is referenced, it will not allow you to do that.

You have to drop tables in the right order.

I think this is now a "SQL" issue, and not a GoDaddy Scripting issue.

-Rob

 
Old May 11th, 2007, 10:28 AM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No I am new to ASP.NET as well as SQL. However, this issue was with Scott Guthries sql data base from your article, it was not my issue. So Scott Guthrie must have provided us with a snippet rather than the whole thing. That is too bad.

However, my sql file on the other hand is a different story, it was created by the "Database Publishing Wizard" without errors and is working offline. In this case the Godaddy import tool (Query Wizard) will not import it and does not give the reason for the failure. Fixing the GOTO problem is not enough for what ever reason. This is why I believe there is at least one more syntax incompatibility that I don't know about yet. I don't know why you don't see this when you import which is why I was wondering if we have the same type of hosting account (Shared server DE LUXE)



 
Old May 11th, 2007, 04:16 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

It's not a "snippet vs the whole thing" thing.

Database objects can refer to each other which makes it hard to determine which object to delete first. The script engine in SQL Server 2000 suffers from the same problem.

The best thing to do is to clear out your live database manually, with the tools that GoDaddy provides, then run the script again. You may need to delete the objects one by one, to avoid the reference problem.

If you have problems with the tool, make sure you log them on the CodePlex site, so these kind of issues can be fixed in a new release.

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old May 11th, 2007, 09:56 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Thomas,

Sorry it took me so long to get back, I actually forgot the password to the account, and I was at work.

I have an Economy Hosting Plan - 1 Year - Windows.

I would say your best bet is to do what Imar suggested--then try copying your script portions at a time--start small. Figure out where the error is happening in your script.

If you get anything other than a syntax error, it's most likely a SQL glitch with tables still remaining or such.

Trust me, I know how difficult Godaddy is to work with--they were extremely rude and very unwilling to help when I had my issues.

Good Luck,
Rob

 
Old May 12th, 2007, 05:08 PM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Imar. As it turns out I get the same error importing Scott Guthries sql corrected script even if I delete the database and start with an empty one. You have an option when you create it to include schema scripts. If you don't select that option you get an undefined syntax error instead. So Scott Guthries sql file must be referencing a data base object that is not defined within his script. So it can't be used for import test. Correct me if I am wrong.

With the "problems with the tool" are you referring to the godaddy import tool? Or another tool?

Hello Rob, thank you for all your help. So you have almost the same plan as I, so we would be using the same tool. My thought was that you might have a virtual dedicated server or a dedicated server in case which you would be using a different tool, but that is not the case.

Thanks for your suggestion as well. There are two problems with copying smaller snippets until you identify the offender. (1) Problems like Imar was eluding to, what if I reference an object not yet defined in that snippet, or the opposite I double define one. (2) My knowledge of SQL syntax is essentially NIL which means that picking out smaller snippets that are still valid code is difficult for me. For example, if I break a string I have syntax error but that is my fault not the import tools fault. Previously I included a large snippet that the Query Analyzer cannot handle (it can import everything up to this code). Well how do I chop this up into valid syntax snippets. That is hard.



 
Old May 13th, 2007, 03:22 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Something else must be going on:
Quote:
quote:Could not drop object 'dbo.aspnet_Roles' because it is referenced by a FOREIGN KEY constraint
You shouldn't get this error when you start with an empty database. Take a look at the SQL from the script responsible fro dropping the table:

/****** Object: Table [dbo].[aspnet_Roles] Script Date: 05/13/2007 10:02:34 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Roles]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[aspnet_Roles]
GO

So, *only* when the object exists, does the script try to delete it. That seems to suggest that you're *not* using an empty database.

I just tested the DPW in a few different scenarios. From SQL 2005 to 2000, and from 2005 to 2005. All worked without a problem, except for a few datatype issues that the tool warns about (the new MAX datatypes don't exist in 2000).

So, all in all, I think this has all to do with the way that GoDaddy imports your data, not with the DPW and the script it creates.

If your SQL script doesn't contain sensitive data, send me a copy and I'll see if I can import it in a 2000 installation, so you can be sure the tool and the script are OK.

And with "problems with the tool" I was referring to the scripting options in SQL Server 2000's client tools. Every now and then, when you use the Enterprise Manager to script your database, you end up with a reference problem like this as well, where multiple stored procedures refer to each other, making it hard to determine which one to delete or create first.

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old May 13th, 2007, 06:11 PM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I really appreciate your help Imar. I have an sql (2005) file generated by the Database Publisher Wizard (from chapter 4 in Beginning ASP.NET 2.0 with C#). I also have the same file adjusted so that it contains MS style EOL. Both have the same behavior. Once I figured out your email address I will send you a copy.

And speaking about EOL it is a little funny that the Database Publisher Wizard generates sql script with end of line that are inconsistent. Easy to fix but should it not all be MS style EOL?

Thanks again for your help!






Similar Threads
Thread Thread Starter Forum Replies Last Post
whats a good tool for auto-formatting SQL Syntax?? callagga SQL Language 1 August 29th, 2008 12:11 PM
reg conn to sql server 2005 from vb.net 2005.. veda SQL Server 2005 2 July 1st, 2008 12:16 AM
Tool for testing SQL syntax ThomasWikman BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 1 May 30th, 2007 06:29 AM
any sql tool recommendations? evaldesc SQL Server 2000 10 September 14th, 2005 08:05 PM
looking for sql server online administration tool method SQL Server 2000 1 March 9th, 2005 12:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.