View works locally but not on GoDaddy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

View works locally but not on GoDaddy

Author

Topic

ice-computer
Starting Member

4 Posts

Posted - 2007-06-29 : 16:56:55

I have an application that utilizes aspnet for membership.
The application utilizes the same database (in SQLServer) for storage of other things. I have a view as follows:


USE [DB_Name]
GO
/****** Object:  View [dbo].[Authorization]    Script Date: 06/29/2007 15:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW Authorization
AS
SELECT     dbo.Domain.Domain_ID, dbo.Domain.Domain_Name, dbo.Alias.Alias_Name, 
                      dbo.Alias.Alias_Authority, dbo.Domain.Domain_ProcessType, dbo.Domain.Domain_Status, 
                      dbo.aspnet_Users.UserName AS Owner_Name
FROM         dbo.Alias INNER JOIN
                      dbo.Domain ON dbo.Alias.Alias_Parent_ID = dbo.Domain.Domain_ID INNER JOIN
                      dbo.aspnet_Users ON dbo.Domain.Domain_Owner_ID = dbo.aspnet_Users.UserId

This works fine locally. However, I am hosting this on GoDaddy and though it allows me to create the table, it seems to have a problem (I'm guessing because it is referencing differing schemas?) and I get an error of:

Incorrect syntax near the keyword 'Authorization'.

through the SQLServer Web interface whenever I attempt to view the data from this view.
I don't doubt that there is some kind of difference between the manner that SQLServer has set up the schemas and how they are set up on my local version.
Any help would be greatly appreciated. I am at a loss of how to get this info and truly want to utilize the view as it would improve performance over a query - at least I believe so. My thinking is that although there may be a performance drop when the item is being added, I gain on the back-end where I really need the performance over utilizing a query.

Thanks,
Steven Henley

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-29 : 17:17:55

My guess would be that the database where you are not getting an error is on SQL Server 2000 or is in 8.0 compatibility mode on SQL Server 2005, and the one where you get the error is running on SQL Server 2005.

The ALTER AUTHORIZATION statement is used in SQL Server 2005 to change ownership of an entity.

Try changing the name of your view to a non-keyword, or enclose the view name in brackets whenever you use it.

And make sure your development and production environments use the same version of SQL Server.

CODO ERGO SUM

ice-computer
Starting Member

4 Posts

Posted - 2007-06-29 : 17:40:13

Michael -

You are a gent and a scholar indeed for the prompt reply.

I inadvertently posted a modification I tried to the 'alter' script - forgive my idiocy as I am a C programmer and not a DB guy.
The correct script (generated locally - this is SQLServer 2005 Express) is

USE [DB_Name]
GO
/****** Object:  View [dbo].[Authorization]    Script Date: 06/29/2007 15:49:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Authorization]
AS
SELECT     dbo.Domain.Domain_ID, dbo.Domain.Domain_Name, dbo.Alias.Alias_Name, 
                      dbo.Alias.Alias_Authority, dbo.Domain.Domain_ProcessType, dbo.Domain.Domain_Status, 
                      dbo.aspnet_Users.UserName AS Owner_Name
FROM         dbo.Alias INNER JOIN
                      dbo.Domain ON dbo.Alias.Alias_Parent_ID = dbo.Domain.Domain_ID INNER JOIN
                      dbo.aspnet_Users ON dbo.Domain.Domain_Owner_ID = dbo.aspnet_Users.UserId

.
I wish I had a choice of versions but do not even know how to get the version information from the production environment.
This is a GoDaddy SQLServer that I utilize SQL Server Web Admin to administrate it through.
The same tables are in both versions, same schemas appear, I am at a loss.
If it would help for me to get the version from the production environment, can u post the SQL to retrieve that info?
I will attempt to change the view name as I did not realize this was a keyword - maybe that will work.

Thanks,
Steven

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-29 : 17:49:55

The scrip on this link will give you the SQL Server version information.

Parse @@VERSION
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84713

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources