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 14th, 2007, 03:12 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Thomas,

I just ran the script you sent me against a 2005 database, and it ran without any problems.


I ended up with a database with two Wikman accounts and an Administrator account.

I didn't have to change a single line of code; I could just execute the file against SQL Server 2005 in a new query window.

You should definitely talk to your host as *they* need to fix this. If they keep being incompetent and can't tell the difference between the OS version the database is running on and the actual database version, I wouldn't trust them too much and consider a new host.

So, "Scott's tool" (although his team wrote it and and he only blogged about it) is fine from a database and script perspective. It's your host and their tools that are messing with your head....

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 14th, 2007, 05:21 PM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wow! That is cool! Yes two Wikman accounts and an Administrator account is what it is. A slight variation of chapter four of Beginning ASP.NET 2.0 with C#.

I am very very greatful for your doing this!

Thank you very much!

Would you allow me to email them a link to this thread if they keep being difficult. I happen to also have found out that you are a major authority on ASP.NET.

I have started looking into to other hosting options. I will tell you how things turns out in this thread.

Again thank you very much!



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

Sure, as long as you don't send them stuff as "Imar says you're incompetent, so now you have to fix things".... :-)

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 15th, 2007, 07:28 AM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK thanks a million Imar!

 
Old May 27th, 2007, 01:04 AM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

I just wanted to mention what finally happened regarding this problem. It could be interesting to anyone who has a shared server account with godaddy.com

It took some effort, but I was able to make godaddy consider the fact that the sql file I tried to import did not have syntax errors and that their import wizard actually had something wrong with it. The issue was sent to "Advanced on-line support". They confirmed that the problem with the Query Analyzer exists and they also confirmed that the sql file I tried to import was error free, just like Imar did.

The GOTO problem has been solved (they claim) but the issue is that an unknown limitation prevents larger (or rather not-entirely-tiny) sql files from being imported. They believe it is a CPU limitation. sql files need to be cut in small pieces around 100K and if you are lucky you get all pieces in. I complained that this was very difficult because it seemed to fail even on the smaller pieces at random occasions. They agreed that could be true, and offered to do the import, if I at least try first.

So there it is, if you have a problem with importing sql files using the godaddy Query Analyzer, it may not be your fault. There really is a problem with ir that they have not yet solved. They recommend dedicated servers.

Anyway, thank you Imar, and Rob (rsearing) for your great help!



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

Hi Thomas,

You're welcome, and thanks for the update!

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 June 13th, 2007, 03:29 PM
Registered User
 
Join Date: Jun 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thomas, if you are still having this issue, I use Godaddy as well, Deluxe Hosting. I just ran across the syntax error issue and found the syntax error was the GOTO Statements in the sql file on my part. I opened the sql file in SQL Server Management Studio (looks best displayed in there as it shows everything on different lines unlike notepad), and put all the GOTO Statements on the previous line before it with only one space as many have explained here. Evidently their Query Analyzer has an issue that all GOTO statements read as GO instead. Then since I used the Publishing Wizard and had some data in my database I had to go thru and take out anywhere I had the word "go" in a sentence. If you use the word go as part of your data, it will throw the error as well.
I noticed you had a foreign key error. When you create your Web Site in VWD, it of course creates the mdf file for you with membership/Roles tables as well. I was not able to successfully upload that whole DB using Database Publhising Wizard. What I did do if this helps in any way and you may have even already tried this I am not sure. I am posting this also in case there is anyone whom gets this far and needs assistance with Amin setup / Membership using the Godaddy Acct. Please read the whole post before you begin this task below and make sure it fits your needs.

Godaddy and Syntax Errors and Create Admin Acct on Godaddy Hosting

1. Start with a clean (delete old one) Godaddy Database. Making sure you select to Install Schema Features. This will set up the aspnet membership/roles tables.

2. Use Database Publishing Wizard to export your Tables out to the SQL File, but do not select the membership/roles tables, only the tables you have created for your data.

3. Go thru the SQL file by opening in SQL Server Management Studio. I say use this program as opening with notepad is a nightmare to try to seek out GOTO statements and actually have a clue to what line they are really on.

4. Backspace all the GOTO statements to the previous line leaving one space of course. I believe you already tried this and will understand how to accomplish this. If not, read around here on the forum how to do this, it is possibly even in this thread. Then make sure you have no words inside any data in the file that say “go” that is without the quotation marks of course. If so, delete the words and you can add it back if you have some admin setup to re enter data. If not, then I am not sure what to tell you but if you have the word GO in there, it won’t go! You will keep getting that syntax error.

5. Using Godaddys Query Analyzer tool you will import the data with the SQL file and hopefully it will import correctly this time around.

6. Next you will have to create a user for the aspnet_users table. What I did was just use my own “create new account” page that I had on the server. You can do this via the query tool if you so desire.

7. Now that you have a user on the account you can set them as admin by doing the following very carefully.

8. Next, enter this into the query tool to Create Amdin Role
exec aspnet_Roles_CreateRole '/', 'Admin'

9. Now to associate the user as an Admin, Copy the following statement into the Query Analyzer window:

SELECT RoleId FROM aspnet_Roles WHERE RoleName = 'Admin'


Click Submit and save the return value (a string of numbers) for use in step 11. Click Reset.


10. Copy the following statement into the Query Analyzer window:

SELECT UserId FROM aspnet_Users WHERE UserName = '<username>'


Replace <username> with the user name of the account to which admin privileges will be granted. This is the User Acct you just made earlier. Retain the single quotation marks around this case-sensitive value but remove the brackets<>. Click Submit and save the return value for use in step 11. Click Reset.

11 Now run this in Query Analyzer and use the two ID's that you received from steps 9 and 10 such as the example below.

 I have made up role id's below but wanted to show you how it should look.

INSERT INTO aspnet_UsersInRoles (RoleId, UserId) VALUES ('3XFF2D78-5FCC-22RE-58M8-32BEA76DC662', 'CCB01123-5F7B-553A-860E-H63F7278DJ9W')

12. You should now have created a user that is in the Role of Admin. This was useful for me as I have a site that I am not yet implementing any login for users but do have an Administration area that I wanted locked down and access for only myself.

I do got guarantee the results of this example and not liable for any damage you incur to your DB nor yourself :) . There may be a simpler way but I looked for weeks before I was able to find resolutions that worked for me. I thought I would share.
 
Old June 14th, 2007, 11:44 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi rkipper,

Thanks for sharing your solution; I am sure people will benefit from your notes when they try to make their GoDaddy account work with ASP.NET Membership.

You can make steps 6 through 12 a little easier by using the Membership and Role APIs. If you add the code below to your Global.asax in the Application_Start event, the user and the roles will be created the first time the site starts. You should remove the code afterwards as it can be a security risk to leave things like user names and password in this code.

This code first creates the user if it doesn't exist. It then creates the Role if it doesn't exist and finally if the user is not assigned to the role, the code assigns the user to the specified role. Personally, I find this a little cleaner that messing with GUIDs and stored procedures directly, although that certainly works as well.

Cheers,

Imar
Code:
void Application_Start(object sender, EventArgs e)
{
    // Code that runs on application startup

    MembershipCreateStatus status;
    string userName = "YourUserName";
    string roleName = "Admin";

    if (Membership.GetUser(userName) == null)
    {
        Membership.CreateUser(userName, "password", "you@you.com", "Secret question",
              "secret answer", true, out status);
    }

    if (!Roles.RoleExists(roleName))
    {
        Roles.CreateRole(roleName);
    }

    if (!Roles.IsUserInRole(userName, roleName))
    {
        Roles.AddUserToRole(userName, roleName);
    }
}
 
Old June 15th, 2007, 08:41 AM
Registered User
 
Join Date: Jun 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar, That is an awesome additional way to go about it.

I really had a hard time when I would run across these issues with Godaddy Hosting and would look for days for articles, etc on how someone else had the same issue and had taken the time to write what did work for them. I found some really good posts here and there but It took a great deal of time to find them and some really creative google searching. Thanks! :)

 
Old June 15th, 2007, 12:09 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

You're welcome.

I think because the problems are so specific to the GoDaddy account, it's hard to find good information about it.

Hopefully, future visitors have less trouble finding this thread... ;)

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.





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.