Creating New Fields in SQL 2005
-
I have just gotten SQL 2005 installed and configured. I restored my 2000 dbs to 2005. The problem I am having is that I created new fields in a table, which I can see when open the table. However, when I added the new items to my asp form and run the code it comes back and tells me that those fields do not exist. If I run a query inside SQL it finds it just fine.
What am I doing wrong. SQL 2005 is so different from 2000 that I am getting frustrated.
How do I get SQL to recognize those fields when queried from an ASP page? The spellings are correct...it is just Loc and mcp.Thanks in advance!
Cheryl
-
Cheryl,
There are a few possibilities that come to mind:
1. You forgot to change the connection string on the ASP page to point at the new SQL 2005 server so it is still pointing at the SQL 2000 server with old schema.
2. The user that the ASP page uses when connecting to the database has column level permissions instead of table level permissions.
3. You are not updating the table directly but are instead going through a stored procedure or view which have not been altered since the creation of the new columns on the underlying table.
-
I know that it is not item #1. Sql 2005 replaced our sql 2000. How do I tell if the user has column or table level permissions? The way you to get around in 2005 is quite confusing.
When I created the new column in the table, I did it directly inside the table as I would have sql 2000. Then I read that you had to generate a script, so I back tracked and went that route but that didn't work. The ASP page still couldn't read from that field.
-
In SQL Server Management Studio, connect to the SQL Server 2005 instance in the object explorer on the left. Expand the server, right click on the database that contains the table and choose "Task>Generate Scripts..." > Next > Next > Make sure that Script Object-Level Permissions is set to True then click Next > put a check by tables then click Next > Find your table in the list and put a check beside it then click Finish.
This will open a new query window showing you the create script for the table along with the grant commands for all permissions currently on that table.
If the script doesn't currently show the new columns listed in the create script then they weren't really added.
If the columns are there look at the permissions for the user in question. If the permissions for that user don't exist, follow the format of the existing permission script to add permissions for that user. If the permissions exist for that user and are in the format of
GRANT UPDATE ON [dbo].[myTable] TO [myUser]
then they are table-level permissions and everything should be good to go.
If they are in the format of
GRANT UPDATE ON [dbo].[myTable] ([myColumn1]) TO [myUser]
then they are column level permissions and you'll need to run a script adding permissions to each of your new columns using the existing column permissions script as a template.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply