Upgraded SQL express to SQL 2005 standard

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Upgraded SQL express to SQL 2005 standard

Author

Topic

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-08 : 14:53:36

Hello!

I have just installed SQL server 2005 standard after completely uninstalling sql express and used a database back up from the sql express to restore from. Since the upgrade i have been experiencing an error when updating from the DB's "products" table I have not applied any service packs in fear of creating a bigger problem.

This error only happens when the action is trying to update a column in the products table. And the action does change multiple records at once.

the exact error is: (i have removed the actual column name)

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'column name'.

/shopa_addproduct.asp, line 424

PLease help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 14:54:41

Could you post the query and the DDL for that table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-08 : 15:02:27

I will post the query and and i dont know what a DDL is but i dont understand why it wouldent work after an upgrade to SQL standard. Is there something else...because the ASP cpde worked fine with SQL express and now with standard it does not..is there a difference in syntax?

this is the bit of code that updates the multiple records at once for something called shared components

Sub PUpdateSharedC (catalogid, stock, mcpartnum, price, pother1, pother5)
dim rscategory, rsc, prs
dim arrcategories
dim catlist
dim i, sqlc, sqlcat, psql

Set rscategory = Server.CreateObject ("adodb.recordset")
sqlcat = "select intcategoryid from prodcategories where intcatalogid =" & catalogid
sqlcat = sqlcat & " and not intcategoryid like '0' Order by intcategoryid asc"
rscategory.Open sqlcat, myconn, adOpenKeyset, adLockOptimistic
while not rscategory.eof
If catlist<>"" then
catlist=catlist & ","
end if
catlist=catlist & rscategory("intcategoryid")
rscategory.movenext
wend
arrcategories=split(catlist,",")
i=0
do while i < ubound(arrcategories)+1
Set rsc = Server.CreateObject ("adodb.recordset")
sqlc ="select catextra from categories where categoryid =" & arrcategories(i)
rsc.Open sqlc, myconn, adOpenKeyset, adLockOptimistic
if not rsc.eof then
if rsc("catextra")="sharedcomponent" then
Set prs = Server.CreateObject ("adodb.recordset")
psql="SELECT products.* FROM prodcategories LEFT JOIN products ON prodcategories.intcatalogid = products.catalogid WHERE (((prodcategories.intcategoryid) = '" & arrcategories(i) & "') and products.hide = '0') ORDER BY products.cstock asc;"
prs.Open psql, myconn, adOpenKeyset, adLockOptimistic
Do While Not prs.EOF
prs("cstock")=stock
prs("mcpartnum")= mcpartnum
prs("cprice")=price
prs("pother1")=strpother1
prs("pother5")=strpother5
prs.update
prs.MoveNext
Loop
closerecordset prs
end if
end if
closerecordset rsc
i=i+1
loop
closerecordset rscategory
end sub
' end stock update code

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-08 : 15:40:02

Any one have any Ideas??

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-09-08 : 16:48:58

The column you are calling for is apparently not on the table, or any of the tables. Check the spelling, and see what you get.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 16:51:46

Check the case too in case your previous version was case insensitive and your new one was installed to be case sensitive.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-08 : 17:04:39

Mrcrowley The column does exist on the table and the spelling is correct

tkizer all of the tables are in lower case just like in the code

thank you for your replies, any other suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 17:11:58

What column and table are you trying to update (you modified the error, so we can't tell)? Post the results of this to show us the columns:

SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'prodcategories'
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'products'

I don't see an UPDATE query in your code, so I'll presume that .NET switched it to an update somewhere in there for you, perhaps at prs.update.

Do you have enough experience with SQL Profiler to trace the query? Once you capture the query and the exception, we'd like to see both.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-08 : 21:21:58

the table is products and the column is mcpartnum

if i remove any one column in the defined column's list the error just returns with the next column in the sub with the same error
""Sub PUpdateSharedC (catalogid, stock, mcpartnum, price, pother1, pother5)""

if i comment out everything after catalog ID in the defined list and remove "shared component" in the if statement it all works fine, just not the way its supposed to (updating multiple records at once)...is there something in the upgrade i have missed that i need to do to these columns? Maybe?

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-09 : 09:36:29

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-09-09 : 10:51:04

Which line is line 424 in the code above? We should make sure we are looking at the right query. You can also run Profiler to see what is actually being passed to SQL Server.

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-09 : 10:56:44

Do While Not prs.EOF
prs("cstock")=stock
prs("mcpartnum")= mcpartnum
prs("cprice")= price
prs("pother1")= strpother1
prs("pother5")= strpother5
line 424----> prs.update
prs.MoveNext

Jamesobright
Starting Member

8 Posts

Posted - 2008-09-09 : 11:08:47

Sorry, i did not realize that my copy/paste would left align when i posted. So i Apololgize for that...

here is the only obscurity in the profiler when i try to execute the problematic commands:


exec sp_executesql N'
                                    declare @BatchID uniqueidentifier

                                    set @BatchID = newid()

                                    UPDATE [Notifications] WITH (TABLOCKX)
                                        SET [BatchID] = @BatchID,
                                        [ProcessStart] = GETUTCDATE(),
                                        [ProcessHeartbeat] = GETUTCDATE()
                                    FROM (
                                        SELECT TOP 4  [NotificationID] FROM [Notifications] WITH (TABLOCKX) 
WHERE ProcessStart is NULL and
	                                    (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY 
[NotificationEntered]
                                    ) AS t1
                                    WHERE [Notifications].[NotificationID] = t1.[NotificationID]

                                    select top 4
		                                    -- Notification data
		                                    N.[NotificationID],
		                                    N.[SubscriptionID],
		                                    N.[ActivationID],
		                                    N.[ReportID],
		                                    N.[SnapShotDate],
		                                    N.[DeliveryExtension],
		                                    N.[ExtensionSettings],
                                            N.[Locale],
		                                    N.[Parameters],
		                                    N.[SubscriptionLastRunTime],
		                                    N.[ProcessStart],
		                                    N.[NotificationEntered],
		                                    N.[Attempt],
		                                    N.[IsDataDriven],
		                                    SUSER_SNAME(Owner.[Sid]),
		                                    Owner.[UserName],
		                                    -- Report Data
		                                    O.[Path],
		                                    O.[Type],
		                                    SD.NtSecDescPrimary,
                                            N.[Version]
	                                    from 
		                                    [Notifications] N with (TABLOCKX) inner join [Catalog] O 
on O.[ItemID] = N.[ReportID]
		                                    inner join [Users] Owner on N.SubscriptionOwnerID = 
Owner.UserID
		                                    left outer join [SecData] SD on O.[PolicyID] = 
SD.[PolicyID] AND SD.AuthType = @AuthType
	                                    where 
		                                    N.[BatchID] = @BatchID
                                    ORDER BY [NotificationEntered]
                                    ',N'@AuthType tinyint',@AuthType=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources