|
hi there,
i m using sql server 2005, and i wanna know how can i change primary key data
for example :
i have 3 tables
1. have 3 columns(EmpID, Salary, RegID)
where EmpID is Primary key and RegID is unique Key
2. have 2 columns(EmpID, Work)
where EmpID is Foreign key
3. have 2 columns(EmpID, Status)
where EmpID is Foreign key
now i wanna change EmpID in all 3 tables,
so i try do it something like this, as i know it will give error and wont work
Update Table1 set EmpID = XX where RegID = XX
can someone tell how can i do this... mean if i change EmpID in table 1 then it automatically change EmpID in others tables
is it possible? or any other way?
thanks
Becoming Programmer...
|
|
|
|
|
Well, its a bit of a strange thing to do as primary keys aren't supposed to change and the foreign keys are there to maintain integrity between tables.
I think you'd need to drop your foreign key constraints, perform the update on the main table, then perform updates on table 2 and 3 so everything points to where it should and reintroduce your foreign keys.
Regards,
Rob Philpott.
|
|
|
|
|
If you specify ON UPDATE CASCADE option when creating the foreign keys, updates to Primary key will reflect in Foreign keys.
Check MSDN to know how to specify this option using T-SQL code or management studio.
|
|
|
|
|
thanks dude
Becoming Programmer...
|
|
|
|
|
Hi,
I have got a problem on a XP Pro development machine using SQL Server 2005, VS 2005. During my development and testing of a Web 2.0 app, I have sporadic times in executing the same query. A query will run within a second for a while and then will start taking 30 seconds to complete. I have tried restarting sql server with no effect. The only thing that fixes it is the restart the machine. It doesn't seem to be linked to a particular query. Has anyone else had this problem?
Thanks, Jared
|
|
|
|
|
Once the query starts taking a long time to return in the asp.net app, I have copy the query into management studio and ran it to check it has anything to do with asp.net and it still takes a long time to run.
|
|
|
|
|
I believe that this is probably due to not having enough memory on my machine. I have 1GB which is nearly all used with those hungry microsoft products. I will need to do some further testing to confirm this.
|
|
|
|
|
I am trying to run this query;
declare @StartDate DateTime
declare @EndDate DateTime
set @StartDate = cast('2007/10/22' as DateTime)
set @EndDate = cast('2007/11/05' as DateTime)
SELECT RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, COUNT(RT_MCP_Alarm_Text) AS Error_Count, CONVERT(char(10), RT_MCP_Date_Time, 111) as ErrorDate, RT_MCP_Bit_State as BOOLEAN
FROM RT_MCP_Historic_Alarms
where RT_MCP_Date_Time >@StartDate and RT_MCP_Date_Time <dateadd(day, 1,@enddate)="" and="" rt_mcp_alarm_text="" <u="">RT_MCP_Bit_State <> FALSE
GROUP BY RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, CONVERT(char(10), RT_MCP_Date_Time, 111)
ORDER BY RT_MCP_Date_Time, Error_Count DESC, RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text
I recieve this error when I try to run it. The BOLDED column is a BIT type, for a True/False choice. I do not need to know when the items go false, only when the meet the TRUE bit.
Thanks,
|
|
|
|
|
Use 0 and 1 to test a BIT value
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Figured this out after I wrote it.
The other problem I had was;
ORDER BY RT_MCP_Date_Time, Error_Count DESC, RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text
I declared the RT_MCP_Date_Time as ErrorDate in the select statement and didn't put it in the ORDER BY statement correctly.
Thanks for your help.
|
|
|
|
|
Hi, I'm new with the SQL language. I would like to ask for tips
or advice on what part of the SQL server to start?
I'll really appreciate the help.
Thanks.
|
|
|
|
|
meki_2118 wrote: I'm new with the SQL language. I would like to ask for tips
or advice on what part of the SQL server to start?
Download the Northwind or AdvertureWorks database from the Microsoft website (there will be instructions on how to add it to your SQL Server). You can then play with them in order to get to grips with the language.
I would also recommend the step-by-step books by Microsoft Press.
|
|
|
|
|
meki_2118 wrote: I would like to ask for tips
or advice
This site and forum are a good start 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Frenz,
I have created a cube using sql server analysis services and i have imported the cube in to excel using get external data and it has also been successfully imported as a pivot table. This uses windows authentication.
Now what I want exactly is.. all the users should not be able to slice and dice the data thru all dimensions..
For example :
If a Project Manager manages 3 or 4 projects.. in the projects field from the pivot table he should be able to slice and dice the data of those 3 or 4 projects and he should not be able to view the other data..
Is there any way that we can specify this access restriction at cube level itself in such a way that
it should take the userid of the project manager from the active directory thru an excel vba script and based on the data fetched from AD... Can it be passed to the cube to filter out only certain data and fields for the person logged in???
Kindly help @ the earliest
Thanks and Regards
Sathya Narayanan
s.sathyanarayanan@gmail.com
+919884450583
|
|
|
|
|
Multiple business partners of master need to use the details of all the sales stores stored in msdb database server.The details include the name of store,name of store owner,and email address of store owner.As per company's security policy,access to company's databases can't be provided to any external entity.
HOW WILL YOU MAKE THE LIST AVAILABLE TO OTHER ORGANISATIONS WITHOUT ANY ADDITIONAL OVERHEAD?
BALA
|
|
|
|
|
baala.m wrote: HOW WILL YOU MAKE THE LIST AVAILABLE TO OTHER ORGANISATIONS WITHOUT ANY ADDITIONAL OVERHEAD?
You can't. Any solution will require additional "overhead" in order to securely expose your data to the relevant parties.
Removing that constraint, then the most obvious solution would be to create a web service that gives them appropriate access.
|
|
|
|
|
Dear All,
I need your help
Does anyone know exactly what statement/function to put AFTER the "Truncate" function in Transact-SQL, so that the query will return the first (DEFAULT) row in the database?
For example : I have a small table named "USERS". This table has only 5 columns. But, the identity column (which is also the primary key) is the USER_ID column.
I have assigned this column the IDENTITY INCREMENT, and of course, IDENTITY SEED "1".
I issued a TRUNCATE function to erase ALL rows/records.
However, I would like to erase all rows EXCEPT the first one (which is also the DEFAULT row).
What should I state after the TRUNCATE function??
Thanks for your help.
|
|
|
|
|
craneprince wrote: Does anyone know exactly what statement/function to put AFTER the "Truncate" function in Transact-SQL, so that the query will return the first (DEFAULT) row in the database?
Well, according to the documentation[^] you can't put anything after that.
craneprince wrote: I issued a TRUNCATE function to erase ALL rows/records.
However, I would like to erase all rows EXCEPT the first one (which is also the DEFAULT row).
Then use DELETE rather than TRUNCATE.
|
|
|
|
|
Could you give me the function SYNTAX?
thanks
|
|
|
|
|
|
Truncate doesn't allow you exclude rows from the delete operation. You would need to do something like "DELETE FROM users WHERE user_id <> 1", assuming 1 is the ID for the default row.
Now keep in mind that Truncate runs much faster than Delete does, but you will probably only notice it if you have a lot of rows.
Take care,
Tom
-----------------------------------------------
Check out my blog at http://tjoe.wordpress.com
|
|
|
|
|
Hi Tom
thanks for your response.
Actually, I do know about the "DELETE FROM....WHERE" clause.
But, it is actually the REVERSE of what I need. I do not wish to delete the DEFAULT. I want to delete EVERY ROW, EXCEPT THE DEFAULT.
I assumed that TRUNCATE would be the best here. But, if DELETE is better, then how do I delete EVERY ROW, but leave the FIRST/DEFAULT row intact?
Regards
ER
|
|
|
|
|
The Delete statement I provide would delete EVERY ROW, EXCEPT the row with an ID of 1. The Where clause says "where the id is not 1", which would match every row (because their IDs would not be 1) except the default row (because it's ID is 1).
This is all assuming that the ID for the default row is 1, if it's another number (say -1), then you just have to plug that in place of 1.
Take care,
Tom
-----------------------------------------------
Check out my blog at http://tjoe.wordpress.com
|
|
|
|
|
i am a part time student in web design and i am trying to learn asp.net and ado.net at the moment. i do not delve into SQL until a little bit later in my course.
[i am using visual studio 2003 and sql server express 2005]
i am having integrated security issues. i keep getting this message;
""
! ADO Error: '
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
""
i can vaguely remember somebody mentioning to me, that i must change the security settings within SQL Server to allow visual studio to communicate with it. i dont know whwat to change! and there are so many options and different security folders.. guest, dbo, etc..
any help would be timelessly appreciated
many thanks in advance
John Michael Kinsella
kinsellajohn@hotmail.com
|
|
|
|
|
John,
Make sure your connection string includes the following:
ConnectionString = "Data Source=.\SQLEXPRESS;" _
+ "Integrated Security=True;" _
+ "AttachDBFilename=" + [DB filename and path] & ";" _
+ "Initial Catalog=" + [DB filename] + ";" _
+ "Connect Timeout=30;" _
+ "User Instance=True"
example:
"Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDBFilename=C:\mydb.mdf;Initial Catalog=mydb;Connect Timeout=60;User Instance=True"
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
