|
|
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
|
|
|
|
|
That is a good solution, thank you. In regards to the profile aspect, how would you handle role specific attributes? Say, I want to store a lawyer's license number, or a client's height and weight?
|
|
|
|
|
You have 2 real choices, add each attribute as a column in the person table (accepting that a lot will be blank) or add another table of person attributes and only link the valid attributes to each person record.
AttrType
AttrTypeID
Attr [Licence No]
PersonAttr
AttrID
AttrTypeID
AttrValue [123456A]
The attributed solution is technically the most correct method, it is also the most complex and that complexity can cause issues. The column method means that every time you need to add an attribute you have to touch the code. It then becomes a business decision which way to go.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Seems like another good answer. What is the purpose of the AttrId column in the PersonAttr table? A simple primary key? Or is it foreign key for another table?
|
|
|
|