Lỗi trong msg 207 level 16 state 1 line 1 năm 2024
This error occurs when Adaptive Server tries to use a column name that does not exist. Adaptive Server supports delimited identifiers for table, view, and column names. Delimited identifiers are object names enclosed within double quotation marks. Using them allows you to avoid certain restrictions on object names. Delimited identifiers can begin with non-alphabetic characters, including characters that would not otherwise be allowed. Delimited identifiers may even be Transact-SQL reserved words. Delimited identifiers can cause some SQL statements to fail. For example, error 207 would be raised for the column “none” in this example: 1> select au_name, "none", au_fname 2> from pubs2..authors 3> go ActionCheck the spelling of the column name specified in the error message. If the column name is wrong, correct it in your SQL statement and run it again. If this error message displays because of delimited identifiers, use single quotes instead of double quotes or turn delimited identifiers off. To turn delimited identifiers off, enter: 1> use 1> set quoted_identifier off 2> go When delimited identifiers is turned “on”, delimiting strings with double quotes causes Adaptive Server to treat them as identifiers. sp_dboption, quoted_identifier, error 207, msg 207, level 16, state 4, CR 728961, CR728961, 728961 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug FiledAbout this pageThis is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required). When I expand the table columns, I already see the added columns. Is there anything ellse I need to do for the stored procedure to see the cloumns?
The Alter Table statement succedded well. When I expand the table columns, I already see the added columns. Is there anything ellse I need to do for the stored procedure to see the cloumns? There is nothing else you need to do. Check that you are not running the alter procedure statement on a different database. For example, run your code as one entire script: ALTER TABLE dbo.EpisodePackage ADD WebTagBroadViewId VARCHAR(50) NULL , FormatSheetBroadViewId VARCHAR(50) NULL , OnAirOfferBroadViewId VARCHAR(50) NULL , FundingBedBroadViewId VARCHAR(50) NULL GO ALTER PROCEDURE dbo.UpdatePackage AS UPDATE dbo.EpisodePackage SET WebTagBroadViewId = '1' WHERE id = 586 GO ______________________________________________________________________________ How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
I have something like this in the stored proc: ... The data for updating EpisodePackage table and updating those new fields is coming from this table variable. I also added the fields to the table variable. Do you think I should do something different becuase there is table variable involved here? The only thing I can think of is maybe your table aliases are mixed up in the update statement. Post your whole proc. ______________________________________________________________________________ How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
I have something like this in the stored proc Have you tried recompiling the stored procedure? exec sp_recompile 'your_proc_name_goes_here'; exec sp_refreshsqlmodule 'your_proc_name_goes_here'; _____________________________________________________ Do not go past the mark you aimed for, but learn when to stop. You can find me on LinkedIn. I support The Programmer's Bill of Rights. MCITP, MCDBA, MCSD glothinSSC Rookie Points: 45 One thing important to note: GO is not a SQL Server command, but a tool of SQL Management studio. When you run that script directly, SQL MS executes it batches separated by the 'GO'. Therefore the new columns are committed/posted before update is ran. GO does not work in a stored procedure. When the procedure is compiled, it simply does not recognize the alter table. I have inherited a script with a similiar problem. I welcome a better solution than I have. My terrible temporary workaround is to create the table outside the stored procedure so that it contains all the columns it will eventually contain inside the stored procedure. Then the stored procedure will compile. If the table exists when the procedure is compiled it will work. Then my stored procedure will: 1. drops the table if it exists; 2. creates the new table with one field Junk(200) 3. Perform bulk insert into my table 4. alters my table and adds columns used to more precisely split up the data from the bulk insert 5. Performs updates and other commands In my case using the temp session tables improve the situation. The script can also be rewritten to extract data from the imported table to the correct columns or copy to a second table.toddasd SSCertifiable Points: 6298 As far as I can tell, the OP is not trying to alter a table within a proc. OP has run the alter table and is now trying to alter a proc which is returning the column not found error. We need to see the proc. |