|
Hi friends. i have a table that has relation to it self. how can i write a select query that shows it's data sequentially from the most top parent to the last child? This table has n levels.
|
|
|
|
|
|
I am new to SQL Server, but not to Programming or Databases, and am stuck, though I have an inkling what is wrong and I don't know how to correct it !!! I'll describe things in detail below ...
I am running this software on a stand-alone PC with myself as the only user.
When I try to add a diagram to my Database I get a message telling me that "This database does not have a valid dbo user, or you do not have permissions to impersonate the dbo user, so database diagramming is not available. Do you want to make yourself the dbo of this database in order to use database diagramming ?"
If I then reply "Yes" it says "This server does not have one or more of the database objects required to use database diagramming. Do you wish to create them ?"
Again, I reply "Yes" and this is where I get the error, "Could not obtain information about Windows NT group/user 'Gary-PC\Gary', error code 0x534."
If I then try to do similar in MS SQL Server Management Studio I get a different message - "Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
Now, I think this is something to do with my PC being renamed, because it is called, and has been for ages, MeDesktop, but at one point I seem to recall it was called Gary-PC, so it could be that something just needs a pointer changed, but I don't know where or how to do that !!!
In MS SQL Server Management Studio, Object Explorer, the Connection Name is "MEDESKTOP (MEDESKTOP\Gary)", Display Name is "MEDESKTOP", Login Name is "MEDESKTOP\Gary" & Server Name is MEDESKTOP.
I have 4 System Databases, "master", "model", "msdb" & "tempdb", all of which have a Status of "Normal" and an Owner of "sa" ... I seem to remember something about "sa" from when I first installed this stuff, but it was a long time ago !!!
I then have my own new (and very small) database, called "myKADB" and this is where I have the problem. I can add and amend Tables & Columns, and I can access the database via my C# program, so most things appear to be working just fine, bar these error messages above. Now, if I look at the Properties of myKADB, I see that again the Status is "Normal", but the Owner is "Gary-PC\Gary", so I think this needs to be changed ... though I could of course be wrong !!!
In the Files option of Properties, there looks to be the option of amending the Owner, so I click on the little button with the 3 dots and I get a Window that says "Select Database Owner", The top button says "Object Types" and there is just the single option of "Logins", and then there is a Window that says "Enter the Object names to select", but this Window is empty. If I then click the Browse button, it says "7 objects were found matching the types you selected" and the 7 are, "##MS_PolicyEventProcessingLogin##", "##MS_PolicyTsqlExecutionLogin##", "Gary-PC\Gary", "NT AUTHORITY\SYSTEM", "NT SERVICE\MSSQLSERVER", "NT SERVICE\SQLSERVERAGENT" & "sa".
Hopefully this is everything I need to tell you, what do I need to do ?!?!?
|
|
|
|
|
|
Thanks, but I've uninstalled and re-installed SQL Server now and it's all working fine.
|
|
|
|
|
How we can find out Relationship of two system tables using database diagram in system databases
|
|
|
|
|
AFAIK, it's not possible to make database diagrams in the system databases in Sql Express. I don't have a full version here to verify whether it's supported there, but I doubt it.
FWIW, you can get a full map for the systables of Sql2k5 here[^].
Bastard Programmer from Hell 
|
|
|
|
|
You could look at the constaints on the foreign key. Does it allow duplicates, does it allow nulls?
|
|
|
|
|
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer.
When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available.
Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure.
Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
|
|
|
|
|
Erland Sommarskog has an article[^] about how to troubleshoot this kind of problems.
|
|
|
|
|
Just a thought. Do you use the same credentials to run the SP from the UI and on the Database directly? If not, you may want to investigate this option.
|
|
|
|
|
Check that it is not a deadlock condition. I believe SQL server will terminate (pick a victim) and terminate a session so that others can continue.
|
|
|
|
|
Do you have return statement somewhere in the code?
RETURN by itself I think is okay but RETURN someValue may not
|
|
|
|
|
Did u check the amount of data the procedure is returning. In case the procedure is returning too many rows of data, then that might be the cause of timeout when you run the procedure programmatically. But when you run the same procedure from database itself which i guess is Sql Management studio, only top 500 or 1000 rows would be returned depending on the SSMS setting. Hence the result comes up without time out.
|
|
|
|
|
I was working on a chunk of SQL in management studio. Finally got it to return the results I wanted. Then I converted it to a stored procedure, where it promptly stopped working. Upon tracking the bug down I found that SQL was handling Null variables differently in dynamic SQL vs my stored procedure.
I had this bit of code:
if (@Replace != '')
set @MaterialNumber = @Replace
In dynamic SQL if @Replace was null, we did not enter the if block. In a stored procedure when @replace was null we entered the if block and set @MaterialNumber to null. 
To the best of my knowledge, this should never happen. Is this a bug in SQL Server or is there a setting hidden somewhere that could account for this difference?
|
|
|
|
|
Whenever I check for NULL, I avoid doing so with comparison operators. There should be some IS NULL check available to use instead. 
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
That seems correct to me, but I'm unsure what you mean by "dynamic SQL".
|
|
|
|
|
By dynamic I meant SQL run directly in Enterprise Manager. When run in a stored procedure the SQL code behaved one way, when run directly in Enterprise Manager it behaved another way. This is the first instance I've discovered where there was a difference between the two.
|
|
|
|
|
In SQL-server '' is not the same as null, it's a zero length string.
|
|
|
|
|
May not be the best way but try:
IF (ISNULL(@Replace, '') != '')
SET @MaterialNumber = @Replace;
|
|
|
|
|
My concern wasn't so much the null handling, that's easy enough to work around.
I'm more concerned by the fact that the SQL code behaved differently when it was run in a stored procedure VS when it was run directly in Enterprise Manager. This is the first instance I've discovered where there was a difference.
|
|
|
|
|
thrakazog wrote: In dynamic SQL if @Replace was null, we did not enter the if block.
Create a stored procedure that demonstrates exactly that and then post it.
|
|
|
|
|
Gaaaah, I finally found the cause of the problem. I apparently created my original sproc with "SET ANSI_NULLS OFF". By default queries in management studio runs with ANSI_NULLS ON. DOH![^]
For a simple example of the sproc I was playing with:
create PROCEDURE [dbo].[Bob_Test]
@MaterialNumber varchar(18)
AS
DECLARE @Replace varchar(18)
SELECT @MaterialNumber
IF (@Replace != '')
SET @MaterialNumber = @Replace
SELECT @MaterialNumber
When I call this with ANSI_NULLS OFF
Bob_Test '123456' my results show 123456 from the first select in the sproc, and NULL for the select after the if statement.
With ANSI_NULLS ON both selects return 123456. My faith SQL Server has been restored. Tune in next week for more inept coding... 
|
|
|
|
|
More of a theory question than anything. I have a website where people who register fall under two different user types, say attorney and client for example.
If a person registers as a lawyer, they fill out a lawyer profile. If they register as a client, they fill out a client profile. The two profiles are different.
So I am trying to figure this out from a database standpoint. Do you think the best bet is a three table setup?
User
------
ID
Email
Password
User_Type (1 for lawyer, 2 for client)
Lawyer
-------
ID (AI field)
User_ID
First
Last
etc
Client
--------
ID (AI field)
User_id
First
Last
Violation
etc...
Or I could do a two table with just Lawyer and Client and check both tables at login time? It would be more code intensive but less data. What is a good approach here? Open to suggestions.
Cheers, --EA
|
|
|
|
|
Turn it on it's ear, create a person table and attach an attribute for role, use a many to many link table to cater for a lawyer who is also a client!
Person
PersonID
lnkPersonRole
LinkID
PersonID
RoleID
Role
RoleID
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
