error in Querry Analizer

Topics: Database Publishing Wizard
Oct 25, 2006 at 4:01 AM
Great timing...this is just what I was starting to bang my head against the desk about.

But I am still having a problem. Hope this post helps you, and I hope you can help me!

OK, SQL Server 2005 on Shared hosting at GoDaddy.

Everything runs fine locally on my desktop with VS2005 Standard and SQL Server Management Studio Express.
Nothing major, just 3 roles and a few users.

(I am new to all of this, so bear with me.)
I followed the "This could be a miracle..." thread and, first off, instead of "DataSource=" in the connection string, I had to use "Server="

After that, the script seemed to generate just fine.

I went over to GoDaddy's control panel->sql manager, tried running the script in the Querry Analyser sic and had a few goofy sounding errors, so I decided to give it a clean start. I removed the SQL from my GoDaddy set-up entirely, then set up a new one.

I tried running the script and recieved this error:
quote
Error -2147217900
Unclosed quotation mark before the character string '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...
/quote

GoDaddy gives you an option for installing the "ASP.Net 2.0 SQL Server Schema Features"
I tried the generated script both with and without the schema features installed and recieved the same error.

Any thoguhts? (please say I am just doing something dumb, or forgetting something)

Thanks.
Oct 25, 2006 at 5:30 AM
Hi

Can you check if the output script created by SqlPubWiz.exe has a "GOTO" as the first statement in a line? There is a known issue in the "Query Analyser" script execution window on GoDaddy.com due to which it issues an error if GOTO is the first statement in a line.

For example, trying to run the following TSQL on GoDaddy.com will give the same error that you are encountering

EXEC dbo.sp_executesql @statement = N'
Create Procedure TestProcedure
as
Begin
select value = 1
GOTO cleanup
Cleanup:
select value = 2

End
'

The work around for this is to open the script file generated by sqlpubwiz.exe and move the GOTO statements to the previous line. In case of the TSQL above the modified TSQL would look like

EXEC dbo.sp_executesql @statement = N'
Create Procedure TestProcedure
as
Begin
select value = 1 GOTO cleanup
Cleanup:
select value = 2

End
'


Thanks
Himanshu
Oct 25, 2006 at 6:27 AM
Well, in that case, it worked like a charm.

There were about a dozen throughout it.
Script ran fine.
Server looks jsut like the local machine. :)

Thanks.


Now, are there plans that this tool also be able to "update" the DB on the server?
For example, if I add a few things to the local DB, such as adding more things to a user profile ?

Worked fine though...shame GoDaddy is goofy like that.

I may have to make a simple GUI for this, i liked it so much.
Oct 25, 2006 at 7:31 AM
Luckymutt - Thanks for posting w/ your question and feedback.

Re: Update Publishing - This is definitely on the radar. If you've got further thoughts on what exactly you'd want to see enabled here, we'd be vey interested to hear them.

Re: GUI - this in the works and will be incorporated into the next CTP (11/15).

Thanks,
Dan
Dec 23, 2006 at 5:53 PM
Hi :-)
Like LuckyMut, I am using GoDaddy shared hosting services.
I created an SQLServer database without the "ASP.Net 2.0 SQL Server Schema Features"

One difference I noticed is that my server version is Microsoft SQL Server 2000 - 8.00.2039 (Intel X86).

I changed the setup parameters to generate a .sql file for the 2000 version.

Now I am receiving the following error message from the Query Analyzer:

EXECUTE permission denied on object 'sp_addextendedproperty', database 'master', owner 'dbo'.

Thanks in advance for your comments.
Cheers / Ben
Dec 24, 2006 at 7:26 AM
Hi bboyle1234 -

It appears that objects in your local database have extended properties, and GoDaddy has prevented you from adding those properties by revoking access to the stored procedure.

Your best bet for now is to go to the local database and drop all extended properties from your local database. You can do this in T-SQL or Management Studio / Management Studio Express. This page http://msdn2.microsoft.com/en-us/library/ms186989.aspx has information on how to list the extended properties. The stored procedure sp_dropextendedproperty can be used to remove the extended properties in the local database http://msdn2.microsoft.com/en-us/library/ms178595.aspx

Alternately, you can look in the script, find all the references to the stored procedure sp_addextendedproperty, and delete those T-SQL commands.

Hope this helps.

Thanks,
alex
Jan 5, 2007 at 3:49 PM
NOTE: Goaddy shared hosting SQL is SQL SERVER 2000 not 2005, just set the script to generate for 2000 and you wont have any more problems.