Hello I have the following problem. I used the Database Publishing Wizard to convert an mdf file for an ASP.NET 2.0 project into sql format (SQL Server 2005). Next I wanted to import this sql file into an SQL 2005 database provided to me by my third party hosting service godaddy.com. The problem is that the data base manager tool claims my sql file contains syntax errors, but say nothing about where. Godaddy.com support does know anything about their tool.

In my opinion it cannot be possible that the SQL file contain syntax errors since the Database Publishing Wizard started out with a fully functional mdf file and generated an sql file without reporting any errors.

However, to state my case I need a tool that can load an sql data base and report any errors if there are any, or report none if there aren't any. I know there must be a way of doing this otherwise SQL data bases would be impossible to work with, but I cannot find any information on it, I guess because I don't even know what I am looking for. Sorry I know almost nothing about SQL databases.

Any help is highly appreciated.

Hi Thomas,

Did you try my other suggestion: create a very simple script and test it out? Maybe there's still a version mismatch between the tools.

If you have Microsoft SQL Server Management Studio, you can verify the Query using Query Designer | Verify Query Syntax.

However, I doubt indeed it's the SQL.

The odd ' characters you saw are probably due to strings. The DPW creates long strings with SQL commands that it executes at the server.

Cheers,

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.

A common problem is with strings. SQL syntax uses single quotes ' where other languages may use doublequotes " . That one trips me up all the time when I switch between SQL and VB.

Rand

Thank you both of you for your help. The problem with making a small script is that the only way I know how to make SQL scripts is by using Visual Studio and do things like dragging and configuring, for example, the Login Control from chapter 4 in Beginning ASP.NET 2.0. However, that simple action creates an sql file with thousands of lines (I have zero SQL education). Perhaps one of the controls make small script.

From your post(s) it is clear that what I really need is the "Microsoft SQL Server Management Studio" that you are referring to. That looks like a wonderful tool. The problem I have is that I have Microsoft SQL Server 2005 (from the start menu) but no "Microsoft SQL Server Management Studio" item under "Microsoft SQL Server 2005".

From reading other posts on this forum I can see that might be because I really have SQL Server 2005 Express not SQL Server 2005 and I should uninstall SQL Server 2005 Express then install SQL Server 2005. The problem with that is that I don't have any "SQL Server Tools Express" in my "Add/Remove programs" I only have "Microsoft SQL Server 2005", but still no "Microsoft SQL Server Management Studio" anywhere.

Or am I confusing things?

You can download the Express Edition here:
http://www.microsoft.com/downloads/d...displaylang=en

If that doesn't offer all you need, I think you can download a trial version of the full version of SQL Server and then install the client tools only, including Management Studio.

It will expire after some time, but it at least gives you a chance to see if it works.

Cheers,

Imar

Thomas,

I ran into a similar problem when I was trying to implement my database with GoDaddy--and the problem had nothing to do with GoDaddy, but in the script I generated. I am at work now, and all my notes/links are at home--but basically I had to do the following:

a) Download this tool that is basically a plug in for Visual Studio 2005 that allows you to generate CREATE statements for all your database tables that can be found in your "Database" explorer tab (on the left)
b) Go through the script and check for this word combo when it starts a new line. I forget the word (something like a SET or...sheesh, I can't remember). All I know is that the Script has issues being interpretted when this word starts a new line. You basically have to back it up to the end of the previous line--just so that a new line doesn't begin with that word.

I can provide you the name of the plug in and the word by the time I get home--unless someone has that information by that time.

Kind Regards,
Rob Searing

Ok...got it.

First, before I begin, there was an excellent article I had found that helped with this. It is an article by Scott Guthrie:

http://weblogs.asp.net/scottgu/archi...nt-part-1.aspx

Basically, the tool you need is the "Database Publishing Wizard" (link in above article) to create the scripts. This is an excellent tool plug-in for Visual Studio that allows you to work inside VS, rather than messing with SQL Server tools.

Now, I don't believe that's your issue, however. Your issue is the error you get when you try to upload the script.

Well, there is a bug with the script in regards to "GOTO" statements. You cannot have a "GOTO" beginning any line in the script. You literally have to go through the entire script and back the GOTO statement up to the end of the previous line. So, if you had:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.FK_Categories_Categories') AND type = 'F')
CREATE TABLE dbo.Categories
GOTO SOMETHING

You would need to change to:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.FK_Categories_Categories') AND type = 'F')
CREATE TABLE dbo.Categories GOTO SOMETHING

If you go through the entire script and make that change everywhere you see a "GOTO" statement, you should be Ok. I found it easy just to put the mouse cursor at the end of the previous line and hit "delete".

Hope that helps,
Rob

Thank you all of you for this valuable information. I tried what you suggested Rob, and you are right the Database Publishing Wizard generates syntax with lines that start with GOTO, I had that in about a dozen places. Unfortunately changing this did not help.

However, I am a little confused about why it would be wrong for a line to start with GOTO. Why would a tool like the Database Publishing Wizard generate syntax errors?

Could it be that you inadvertantly removed offending end of line characters when you deleted the end of lines to move up the GOTO? I noticed that the Database Publishing Wizard generates end of lines characters in an inconsistent fashion, which incidentally makes notepad go bananas, so I using Visual Studio to open the sql file.

On the other hand maybe godaddy.com really does not like lines to start with GOTO, but then they have their own dialect of SQL, or am I wrong about this (I am a novice). If so are there other differences between Microsoft SQL and Godaddy.com SQL? Differences that I am a victim of but not you.

I tried importing parts of the sql file and found that this works as long as I don't break quotes (single quotes seems to be what Database Publishing Wizard uses and this seems to be fine). However, certain parts cannot be imported I have included the offending code below that the Godaddy Query Wizard blows up on (sorry for the long listing). It complains about-->

N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]

Yes for the first time it reported what it did not like.

In any case it is very interesting to meet someone who is also using godaddy.com. Do you also use a shared server or do you have a dedicated server?




WARNING---LONG Listing below
================================================== ===============
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
    @ApplicationName nvarchar(256),
    @UserName nvarchar(256),
    @TablesToDeleteFrom int,
    @NumTablesDeletedFrom int OUTPUT
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT @UserId = NULL
    SELECT @NumTablesDeletedFrom = 0

    DECLARE @TranStarted bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
    SET @TranStarted = 0

    DECLARE @ErrorCode int
    DECLARE @RowCount int

    SET @ErrorCode = 0
    SET @RowCount = 0

    SELECT @UserId = u.UserId
    FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
    WHERE u.LoweredUserName = LOWER(@UserName)
        AND u.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@UserId IS NULL)
    BEGIN GOTO Cleanup
    END

    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
    BEGIN
        DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
               @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 ) GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
    IF ((@TablesToDeleteFrom & 2) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )
    BEGIN
        DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 ) GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
    IF ((@TablesToDeleteFrom & 4) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
    BEGIN
        DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 ) GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
    IF ((@TablesToDeleteFrom & 8) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 ) GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (@TablesToDeleteFrom & 2) <> 0 AND
        (@TablesToDeleteFrom & 4) <> 0 AND
        (@TablesToDeleteFrom & 8) <> 0 AND
        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
    BEGIN
        DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 ) GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:
    SET @NumTablesDeletedFrom = 0

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END'
END

Rob,

Has it changed in SQL 2005 compared to sql 2000?

_________________________
- Vijay G
Strive for Perfection

It's not a bug in the SQL tool but a bug in the way some web hosts handle the files. They see GOTO as a GO statement with an illegal TO value.

Check the link Rob posted http://weblogs.asp.net/scottgu/archi...nt-part-1.aspx and search for GOTO. Scott mentions it explicitly, and refer to a CodePlex page with more details.

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.