|
Well you're screwed, a primary key that is dependent on your user input BBrrrttt WRONG.
This is one of the most basic errors in database design. Like using a phone number or email address or ANY other user data as a PK/FK. Keys should be stupid, they should have absolutely NO other function than to maintain your data structure.
Solution - create another field,BookID int with identity on your book table, everywhere you use the ISBN go there and add the new field and populate it based on existing structures (isbn). Now change you FKs to the BookID fields, now remove the FKs for the isbn. NOW you can edit your ISBN.
It is a lot of work but you have to do it to correct the design error. Never put off fixing these errors, they get into the corners of your database and breed and shortly you are totally f***ed.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hello every one!
i was wrote a storedprocedure and execute it (in sqlserver 2005)
this sp will get some data as parameters and after some complicated transactions delete some rows of the tables but it will complete with the following error
"Msg 2754, Level 16, State 1, Procedure sp_InsertHistoryTbl, Line 30
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option."
1) how can i get the error message as an admin?
2) dose any one know about this failure?
3) is there any list of error numbers (for example a list that has a table with error numbers and error messages and perhaps it`s cause and etc)?
please note me any thing about these
thanks!
|
|
|
|
|
So did you do some research or just come straight to CP. I found these responses[^] and it look like you have a locking issue, some research may provide you with a solution! Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks for your attention
yes u right may be its better to googeling first !
|
|
|
|
|
Did you find a solution? Never underestimate the power of human stupidity
RAH
|
|
|
|
|
yes i did
i was wrote a rise error with 27 severity level !!!!
shame on me!!!!
|
|
|
|
|
Hello,
I need to write a script that creates a table variable with a column for every instance of a row in a table. The data in the table it pulls from may change from time to time, so I'm looking for a dynamic approach.
Example table:
ColumnA: ColumnB:
1 Hello
2 World
Then my table variable declaration should look like:
DECLARE @TableVariable TABLE
(
Hello varchar(20),
World varchar(20)
)
Can anyone think of a performance-friendly solution to this?
Thanks,
Kevin
|
|
|
|
|
I thing you should dynamically create an sql string that creates the table.
On the other hand, why would you want a to create a column for each row? suppose. Keep in mind that SQL Server has a max column limit of 255(or 256) per table.
|
|
|
|
|
Kevin Leeds wrote: Can anyone think of a performance-friendly solution to this?
Yes.. I think.
I encounted a similar issue this week and solved it by using a pivot table. There is an excellent article on hear that gives a workable example.
Pivot two or more columns in SQL Server 2005[^]
Hope this helps...
Tim
|
|
|
|
|
Glad it was useful. Always a buz when an article is used as reference (by someone other than the author ) Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A pivot may do the job for you, organsing it will be a challenge.
Dynamic SQL is more straight forward EXCEPT if you use EXEC (@SQL) to create the table var it is created in a different proc domain and therefore not available to you. Creating a global ##Table will fix this but it is not recommended in a frequently called proc.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the responses. I wish I could take the time to test these, but we have decided to move on - not making this a dynamic proc.
|
|
|
|
|
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
WHERE (@LastName = PD_Users.LastName OR (@LastName = '' AND 1=1))
AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
AND (@UserStatus = (CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end) OR (@UserStatus = 0 AND 1=1))
ORDER BY PD_Users.FirstName DESC
I got the error: the order by clause is invalid in views inline functions ...
do you have any idea?
|
|
|
|
|
Have you tried typing the error message into Google? I did and I got 94,000 results; Maybe one of those could be of help?
|
|
|
|
|
What exactly are you trying to order? Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
order by users FirstName or LastName
|
|
|
|
|
If you want it ordered by either FirstName or LastName, then why does your original post also have an order by clause here;
<br />
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber<br />
The ORDER BY in this context is in error.Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I think you should not try to order intermediate results. You should only end-results.
|
|
|
|
|
can you change my SQL Statement as you think?
|
|
|
|
|
Actually, my answer was not correct. I think what's wrong with your query is the syntax i.e where the Order By Clause comes in. The syntax should be in form
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
|
|
|
|
|
Thanks Silim it works
bt now I have anoter problem
when I use ArticleCount I get the Invalid column name error
do you hae any idea?
|
|
|
|
|
I am inserting different records using this this:
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 0, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 0
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 1, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 1
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 2, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 2
I want after that to update the item_guid so all records having the same item_code should have the same item_guid, something like:
UPDATE prices SET item_guid = ONE_GUID_FOR-ALL where item_code = SAME_ITEM_CODE and price_level_id = WHAT_VER_ID_i_PASS
|
|
|
|
|
Try this:
declare @Guid uniqueidentifier
select @Guid = newid()
update prices set item_guid = @Guid ... rest of code.... Tychotics: take us back to the moon
"Life, for ever dying to be born afresh, for ever young and eager, will presently stand upon this earth as upon a footstool, and stretch out its realm amidst the stars."
H. G. Wells
|
|
|
|
|
I have a medical software and I want to know what's the best control to use and what's the best SQL field type for blood pressure?
The blood pressure as you know is in this format: 999/999
it also has a flag for either right hand (L) or left hand (R), something like radio control
I don't want the traditional way!! I mean two numeric and two radio?!
I thought of the ButtonEdit from DevExpress with a ###/### mask but I faced a problem with the L and R flags..
What is your advise?
|
|
|
|
|
This is not a database question. WHy are you spamming the fourms I know the language. I've read a book. - _Madmatt
|
|
|
|