|
Thank you very much, Richard Deeming, for the help.
Have a very good day.
|
|
|
|
|
Hello,
I have a SQL table with some existing data. In that I have a columns named "Name" and SL No (which is a primary key). For example say I have one entry for that column as "Thilak". Suppose if a next entry comes with same name i.e "Thilak", I want to rename the existing "Thilak" as "Thilak_Slno" and new entry as "Thilak_Slno". And If again any Thilak repeats I want append serial number to that before saving or soon after saving to the database.
The purpose is simple, I am giving option to post a query in my software based on username. while selecting the name, if same name exists in entry that will be difficult to select the Name, if name comes with serial number it will be easier.
So whenever a new entry comes which is same as the existing name, I am planning to append the serial number to the existing names also to the incoming name.
So, now whether it is possible to change the name as it enters into the table ? or should I change once it get saved to database ?
How to do this ?
Please help me.
|
|
|
|
|
If you want all names to be unique, then just add the serial number in the first place. Then you do not need the extra steps of searching for an existing name and changing it when adding to your database. Alternatively just accept that there may be duplicates which you can process when searching.
|
|
|
|
|
You shouldn't change it at all. They are two separate facts, and should each be in their separate field. That way you don't need to "substring" on the name when the numbering changes, and can easily add another field if your combination is no longer unique.
The database is not there to store "formatted" values, but data. Upon retrievel, you paste to two fields together and have what you want.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I am using SQL express 2008 r2. Some times while running SQL backup query,
Text
BACKUP DATABASE [DBName] TO disk = 'TargetPath' WITH CHECKSUM
I am getting the error 'detected an error on page'. But if I run query without checksum then it runs successfully.
My question is,
1. Why that error is coming ?
2. What should I do if that error comes ?
3. Can I rely on the backup without checksum ?
Please help me.
|
|
|
|
|
Sounds like you might have some page-level corruption in your database.
Try running:
DBCC CHECKDB (N'Your-Database-Name') WITH NO_INFOMSGS, ALL_ERRORMSGS;
If you still need help, you'll need to post the full error message.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This is the error I am getting,
Msg 3043, Level 16, State 1, Line 1
BACKUP 'broken' detected an error on page (1:143) in file 'c:\sql\mydb.mdf'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
|
|
|
|
|
That error message means your database is corrupt. Try running DBCC CHECKDB, as I suggested.
You'll probably need to restore your database from the most recent valid backup.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Brent Ozar recently published a great blog-post covering the details of what you need to do:
What to Do When DBCC CHECKDB Reports Corruption[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Good Day All
i have been using the below declared Cursor from the Stored-procedure for around 3 years now. The last couple of weeks i have been getting the error
Incorrect syntax near the keyword 'CROSS'.
I dont have a Cross Join in this Cursor and this started after Infrastructures upgraded the hardware on the SQL Server , i noticed other settings were lost but was able to recover them.
USE [DBPS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[DATABASE_MAINTANANCE_PLAN]
AS
DECLARE @DatabaseName SYSNAME = DB_NAME(), @TableName VARCHAR(256)
DECLARE @FILLFACTOR INT = 85
DECLARE @DynamicSQL NVARCHAR(max) =
'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +
''.'' + TABLE_NAME AS TABLENAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'''
BEGIN
EXEC sp_executeSQL @DynamicSQL
OPEN curAllTablesInDB
FETCH NEXT FROM curAllTablesInDB INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'
PRINT @DynamicSQL
EXEC sp_executeSQL @DynamicSQL
FETCH NEXT FROM curAllTablesInDB INTO @TableName
END
CLOSE curAllTablesInDB
DEALLOCATE curAllTablesInDB
END
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vimalsoft.com
vuyiswa[at]vimalsoft.com
|
|
|
|
|
Thanks
I had a table named "CROSS" Cross is a keyword in SQL , that caused a problem for that Dynamic Query. Thanks its resolved now.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vimalsoft.com
vuyiswa[at]vimalsoft.com
|
|
|
|
|
Rather than using dynamic SQL and a cursor, why not use the undocumented sp_MSforeachtable procedure[^]?
ALTER PROC [dbo].[DATABASE_MAINTANANCE_PLAN]
AS
BEGIN
DECLARE @command nvarchar(max);
DECLARE @FillFactor int = 85;
SET @command = N'ALTER INDEX ALL ON [?] REBUILD WITH (FILLFACTOR = ' + Convert(nvarchar(3), @FillFactor) + N')';
EXEC sp_MSforeachtable @command;
END
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello,
I am planning to migrate from MSFlexGrid to MsHflexGrid. In MSFlexGrid, when the .col /.row property is changed programmatically, the rowcolchange event gets fired. But in MsHflexGrid the same doesn’t work. However, on manually changing the .col/.row property through mouse click or using navigation keys, the event gets fired in MSHFlexGrid
Please help.
|
|
|
|
|
I think you may be better off asking this question in QA[^]. As this seems to be more about changing your grid control than actual database systems.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
You are going to have a lot of problems getting support for vb6 controls, it is a dead language.
You would be better served by scrapping the entire application and rewriting in a current language where you can get support.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I am using Microsoft SQL server 2008. I have 2 SQL tables Table 'Product1' and Table 'Product2'. In 'Product1' Table I have columns named
1.SLNO,
2.Name,
3.Place
4.State
And 'Product2' have columns named,
1.SLNO,
2.Name,
3.Place
4.State
5.regno
here 'regno' if FK to 'SLNO' (of product1 table ).
My requirement is to update the columns in 'Product2' table when I modify data in 'Product1' table ( 'name' 'Place' and 'state' column).
Is it possible to update 'Product2' table without running individual queries for each rows ?
If yes how to do this ?
Please help.
|
|
|
|
|
Since the columns in Product2 always have to match the columns in Product1, wouldn't it make more sense to remove them from the Product2 table? Otherwise, you're just duplicating data for no reason.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
yes you are right. But for time being I cannot do that. However I got a one method which partially works. Here it goes..
So I have added 'Unique constraint' for 3 columns (Name, Place and State) in table1 table using,
ALTER TABLE table1 ADD
UNIQUE(column)
and created foreign key in table2 table using,
ALTER TABLE table2 ADD
FOREIGN KEY (column) REFERENCES table1(column);
While adding 'ON DELETE CASCADE' to table2 I am getting error, that
"There are no primary or candidate keys in the referenced table"
Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
Also how to add "ON DELETE CASCADE" for some foreign keys ? In my case I have 3 foreign keys ( Name, Place and State). But I want to add "ON DELETE CASCADE" only for Place and State.
Please help.
|
|
|
|
|
srikrishnathanthri wrote: Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
A foreign key must reference either a primary key, or a column with a unique constraint. To set up a foreign key between the tables, you'll need a unique constraint on the SLNO column in table1.
srikrishnathanthri wrote: But I want to add "ON DELETE CASCADE" only for Place and State.
I don't think you've understood what ON DELETE CASCADE means. When you delete the row from table1, any rows in table2 with the same SLNO will be deleted as well. It doesn't do anything when you change a value in the referenced row, and it doesn't delete the value from specific columns.
Cascading Referential Integrity Constraints[^]
You could do this with a trigger[^] on table1, but it's a pretty nasty workaround, which can lead to hard-to-trace problems. As it's only two tables, it would be simpler to update both at the same time.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ok. I got your second point, yes now I understood 'ON DELETE CASCADE' correctly. But I have doubt in my first question.
Here I am referring foreign key to a column with a unique constraint. I have created foreign keys refereeing to unique constraint without any error.
The point I am getting error is when I run,
ALTER TABLE [dbo].[Product2] ADD CONSTRAINT
[FK_ProductDetails_Products] FOREIGN KEY([name],[place],[state])
REFERENCES [dbo].[Product1] ([name],[Place],[state])
ON UPDATE CASCADE
ON DELETE CASCADE
Can you guess what might be the error.
|
|
|
|
|
Do you have a unique constraint on Product1 for name, place and state?
ALTER TABLE dbo.Product1
ADD CONSTRAINT UX_Product1_Name_Place_State
UNIQUE NONCLUSTERED (name, Place, state)
If not, you can't create a foreign key pointing to it.
If you have, then you'll probably get an error relating to multiple cascade paths, since you already have a foreign key relationship between the two tables.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Now I have a doubt,
I want to another column named 'regno' and I want to make it primary key of product1. Now is it possible to add FK in prodcut2 for regno, name, state and place. ?
Because I am getting an error..
Introducing FOREIGN KEY constraint 'FK_ProductDetails_Products' on table 'Product2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
In my case,
regno refers to Prooduct1's regno ( which is primary key)
name, place and state refers to Prooduct1's name, place and state ( which are unique constraints )
Why I am getting this error ?
|
|
|
|
|
Because you have multiple FKs between the tables. It's a bad design, but it's possible - so long as only one of the FKs specifies a "cascade" rule for update or delete.
[A] table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement.
You're trying to use a foreign key for something that it wasn't designed for. If you can't change the table design, switch to issuing multiple queries, or using a trigger to push the changes from Product1 to Product2.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi
I would suggest you spend some time understanding Normalization of tables and reference integrity. If you have a good understanding of these concepts then it will be a lot easier for you designing the tables in the right way and fixing any errors.
|
|
|
|
|
Hi,
In MySQL, I have basket field call item_name in my order_basket table.
I want an SQL statement to take all item_name and INSERT it into a variable call param_details whch a TEXT type with a "\n" between them.
something like this
DECLARE param_details text;
INSERT CONTACT(item_name, "\n" INTO param_details FROM order_basket
How can I do this please?
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
