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 7th, 2007, 09:02 PM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL 2005 Syntax checker Tool

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.



 
Old May 8th, 2007, 01:40 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,

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.
 
Old May 8th, 2007, 08:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 9th, 2007, 12:36 AM
Authorized User
 
Join Date: Apr 2007
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

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

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
 
Old May 9th, 2007, 08:11 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

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

 
Old May 9th, 2007, 05:06 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

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






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

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



 
Old May 10th, 2007, 01:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Rob,

Quote:
quote:You cannot have a "GOTO" beginning any line in the script.
Has it changed in SQL 2005 compared to sql 2000?

_________________________
- Vijay G
Strive for Perfection
 
Old May 10th, 2007, 01:33 AM
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 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.





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.