|
Hello,
I posted this over at sqlserverentral.com but have not had any reply's.
If you are a member here is the link:
Code[^]
What this will do:
Allows you to show the usage of any table, procedure, column, or view in any database on the server where the code is running.
Example:
exec spFindObjectUsage 'MyTableOfParts'
This will return all the views and procedures that call out MyTableOfParts.
Here is the code:
<br />
<br />
CREATE PROCEDURE spFindObjectUsage<br />
@ObjectToFind NVARCHAR(100) = ''<br />
,@ResultMessage VARCHAR(200) = '' OUTPUT<br />
AS<br />
DECLARE<br />
@ReturnCode INT<br />
,@StringToExecute NVARCHAR(1500)<br />
,@DBToProcess INT<br />
,@ServerName VARCHAR(200)<br />
,@TableWithServer VARCHAR(200)<br />
,@NameOfDatabase VARCHAR(50)<br />
--<br />
DECLARE @ServerDatabaseTables TABLE<br />
(TempTblID INT NOT NULL IDENTITY(1,1)<br />
,DBName VARCHAR(100) NOT NULL DEFAULT ''<br />
,Processed BIT NOT NULL DEFAULT 0)<br />
--<br />
CREATE TABLE #ServerDatabaseObjectUsage <br />
(UsageID INT NOT NULL IDENTITY(1,1)<br />
,DBName VARCHAR(100) NOT NULL DEFAULT ''<br />
,ObjectUsedIn VARCHAR(200) NOT NULL DEFAULT ''<br />
,TypeOfObject VARCHAR(50) NOT NULL DEFAULT ''<br />
,IsColumnOfTable BIT NOT NULL DEFAULT 0)<br />
--<br />
-- first get all the databases on the current server<br />
--<br />
INSERT INTO @ServerDatabaseTables<br />
(DBName)<br />
SELECT<br />
name<br />
FROM master.dbo.sysdatabases<br />
--<br />
SET @TableWithServer = ''<br />
SET @NameOfDatabase = ''<br />
--<br />
SET NOCOUNT ON<br />
-- each database has it's own listing of System Objects so inorder to get<br />
-- a correct listing we will need to go through every database.<br />
-- the only way I know to do this is using sqlexec.<br />
-- I know it is not the best way but we will need the ability to dynamically<br />
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect<br />
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN<br />
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)<br />
--<br />
SELECT @ServerName = DBName + '.dbo.'<br />
,@NameOfDatabase = DBName<br />
FROM @ServerDatabaseTables<br />
WHERE TempTblID = @DBToProcess<br />
--<br />
SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +<br />
'(DBName' +<br />
',ObjectUsedIn' +<br />
',TypeOfObject' + <br />
',IsColumnOfTable) ' +<br />
'SELECT DISTINCT ' +<br />
char(39)+@NameOfDatabase+char(39)+ <br />
',obj.NAME' + <br />
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) + <br />
' THEN ' + char(39) + 'PROCEDURE' + char(39) +<br />
' WHEN ' + char(39) + 'V' + char(39) + <br />
' THEN ' + char(39) + 'VIEW' + char(39) +<br />
' WHEN ' + char(39) + 'U' + char(39) + <br />
' THEN ' + char(39) + 'USER TABLE' + char(39) +<br />
' WHEN ' + char(39) + 'D' + char(39) + <br />
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +<br />
' WHEN ' + char(39) + 'F' + char(39) + <br />
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +<br />
' WHEN ' + char(39) + 'IF' + char(39) + <br />
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +<br />
' WHEN ' + char(39) + 'FN' + char(39) + <br />
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +<br />
' WHEN ' + char(39) + 'TF' + char(39) + <br />
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +<br />
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +<br />
',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +<br />
'THEN 1 ELSE 0 END)' + <br />
'FROM ' + @ServerName + 'sysobjects as obj ' + <br />
'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' + <br />
'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' + <br />
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + <br />
' OR syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + <br />
' ORDER BY obj.name '<br />
SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))<br />
PRINT LEN(@StringToExecute)<br />
--<br />
exec sp_executesql @StringToExecute<br />
--<br />
IF (@@ERROR != 0) BEGIN<br />
SET @ReturnCode = 1<br />
GOTO END_PROCEDURE<br />
END<br />
--<br />
UPDATE @ServerDatabaseTables<br />
SET Processed = 1<br />
WHERE TempTblID = @DBToProcess<br />
END<br />
--<br />
SELECT * FROM #ServerDatabaseObjectUsage<br />
--<br />
DROP TABLE #ServerDatabaseObjectUsage<br />
--<br />
SET @ReturnCode = 0<br />
<br />
END_PROCEDURE:<br />
IF (@ReturnCode != 0) BEGIN<br />
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))<br />
END ELSE BEGIN<br />
SET @ResultMessage = 'OK'<br />
END<br />
RETURN @ReturnCode<br />
I am looking for feed back on the code:
If you like it?
Is there a better way to do this?
And any other comments or suggestions are welome.
Please let me know,
William O'Malley
I hate users. Not all of them, just the ones who talk.CP member: Al Einstien
|
|
|
|
|
Would you be so kind as to put your 2 cents in on this code?
Thanks,
Will
I hate users. Not all of them, just the ones who talk.CP member: Al Einstien
|
|
|
|
|
Hi,
As a relative newbie to SQL Server/ASP.NET I'm hoping someone here
can help with my problem. I'm developing a timesheet application in
ASP.NET C# using Visual Studio 2003 with a database built in MSDE.
One of my forms needs to return a simple list of resources from my
database. I have followed the guide on the MSDN libraries, but for
some reason I continuously get the same error message.
What I've done so far is create the database, tables, and populate
with some sample data using using Server Explorer in Visual Studio. I have
connected to the database (using integrated security) and I am
trying to get the contents of the Resource table to appear on my
form. I have then created a DataAdapter (tested the connection, set
the SQL as a simple SELECT * from Resource, etc), which also generates an sqlConnection for me. To test this I have previewed the generated data, and it returns what I want, so I have chosen to generate a DataSet of this. I am then trying to get this data into a simple DataGrid. On the properties of the DataGrid I have changed the DataSource to point at my Dataset. As I
understand it, I then have to add the following to my Page Load
section of my code.
sqlConnection1.Open();
this.sqlDataAdapter1.Fill(this.dsResource);
DataGrid1.DataBind();
sqlConnection1.Close();
The form builds fine, but when I browse to the particular form I get
the following error for the sqlConnection1.Open(); line. If I remove this line the error simply moves to the line below.
Exception Details: System.Data.SqlClient.SqlException: Cannot open
database requested in login 'SCMS'. Login fails. Login failed for
user 'AL-NOTEPAD\ASPNET'.
To me this is an error with my connection string. My database
instance is actually 'AL-NOTEPAD\VSDOTNET'. However the properties
for sqlConnection1 are pointing to the correct datasource. I do not
know why the application is looking for user 'AL-NOTEPAD\ASPNET'. It does not exist, to my knowledge.
Any help with this would be greatly appreciated, as I get the same
error with my code for forms-based login...
Thanks in advance..
|
|
|
|
|
Check the web.config file if you have added the connectionstring there, and the source for the SqlConnecton1 again to double where it is pointing to.
|
|
|
|
|
i've developed an application to manage my table of logins
from that i can insert,modify,delete and view the login details of a user the problem is when i choose to view the details of a user with administrator rights it shows an error, also if i create a login user with administrator rights in the table the user is added as user and not as administrator why?
|
|
|
|
|
There's no way to answer this question with anything useful without knowing anything about your "table of logins", where this table is stored and what security options your using on this table.
But, in most systems anyway  , normal users cannot create Administrator accounts, for obvious reasons, and cannot see any details about them.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I derived a Class "CProtocol" from CRecordset which is linked with but one database table.
Everything works fine until I call AddNew(), here I receive a assertion Faile Message, referring to dbcore.cpp
Code sniplet ( .cpp file):
CString csTimeNow;
// Hole Hostnamen
DWORD dwHostLength = MAX_COMPUTERNAME_LENGTH + 1;
GetComputerName( csComputerName.GetBuffer( 0), &dwHostLength);
// Hole UserNamen
DWORD dwUserLength = UNLEN + 1;
GetUserName( csUserName.GetBuffer( 0), &dwUserLength);
// Hole aktuelle Zeit
COleDateTime coTimeNow(COleDateTime::GetCurrentTime());
csTimeNow.Format( "%04d%02d%02d%02d%02d%02d00", coTimeNow.GetYear(),
coTimeNow.GetMonth(),
coTimeNow.GetDay(),
coTimeNow.GetHour(),
coTimeNow.GetMinute(),
coTimeNow.GetSecond());
// Instanz zur Datenbank aufbauen
CString csConnectionString;
csConnectionString = "DSN=ODR_CRM;UID=ODRGMBH;PWD=origin";
m_pDB = new CDatabase();
if( m_pDB->OpenEx(csConnectionString,CDatabase::noOdbcDialog))
{
// We´re connected to the database
}
else
{
// we have a problem connecting to the database
}
// Access to Class Protokoll Table
m_pProtocol = new CProtokoll( m_pDB);
m_pProtocol->Open(); // ready to use
m_pProtocol->m_Timestamp = csTimeNow;
m_pProtocol->m_Benutzer = csUserName;
m_pProtocol->m_Rechner = csComputerName;
m_pProtocol->m_Aktion = csMessage;
if( m_pProtocol->IsOpen() )
{
m_pProtocol->AddNew(); // Ready to add new records
}
m_pProtocol->Update(); // Update Table
m_pProtocol->Requery(); //
.h file
CDatabase* m_pDB; // Pointer to CDatabase Class
CProtokoll* m_pProtocol; // Pointer to CRecordset Class
|
|
|
|
|
Hi,
I want to query two tables:
Select * FROM table1, table2
but I only want one column from table2.
Can I query * from table1 and just Column1 from table2?
thanks,
Ron
|
|
|
|
|
Select A.*, B.Fld FROM A , B
where A.Fld = B.Fld
|
|
|
|
|
|
While M Javed Khan's answer is correct, you should be aware that it is generally bad practice to SELECT * in code. I only ever do that while trying things out in the query analyzer.
The reason for that is that SELECT * can break production code because someone changed the database schema and that change is being propogated to the application. The application isn't expecting the different set of columns it is now receiving so it will perform unexpectedly.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
|
Give the alias names to your tables in the following way
select t2.columnname ,t1.columnname from table1 t1,table2 t2 where condition follows
Here Column name indicates the name of the column which you want to display from the second table table 2 .This way you can diasplay the columns you want to display .
|
|
|
|
|
|
Hi,
We have a weird trouble with our SQL 2000 Server. When we turn the machines on (the server and our only work-station{client}, both in the same Domain) we can access SQL from the client using standard Windows authentication, and it works just fine, but after a while it does not work any more.
This is the Error Msg we get: Cannot generate SSPI context. Using the Command Prompt from the client machine, we issue a ping "Server" as well as a ping "IP address" and both work; This makes us think that the DNS is working properly. But then, why is it failing after a few minutes. If we try to connect using the SQL Server Authentication it works just fine.
This is the first time we encounter this problem. We Re-Installed SQL in the Server as well as the client tools on our other machine, but got the same behavior.
After searching Inet for some help we found an article by MS about something called Kerberos, that honestly did not understand at all.
Thanks for your time and help.
A very lost guy(who is a big time ignorant about all this)
-- modified at 18:27 Thursday 2nd February, 2006
|
|
|
|
|
Presumably that was this article[^]. In a Windows 2000 (or later) Active Directory domain, Kerberos is the protocol used to perform authentication. When you make a connection to SQL Server, the client library uses the Kerberos protocol to get a ticket from the Ticket-Granting-Service on a domain controller. It then presents that ticket to the SQL Server. If the ticket is valid, SQL Server allows the connection.
Kerberos tickets have timestamps on them so they're only valid for a short-ish period and cannot be replayed by an attacker. You should ensure that the clocks on the servers, domain controllers and clients are all synchronised. If you have multiple domain controllers, ensure that they're all in sync with each other - the default configuration for member servers and workstations is to synchronise with a DC. Ideally DCs should get their time from a true external time source, but synchronising with an online time server is fine. We use our ISP's NTP server; if you don't know yours or they don't have one, see the public NTP servers list[^]. On the DC, use NET TIME /SETSNTP to set the NTP servers to use.
Otherwise I suggest you follow the suggestions in that KB article and then call MS support.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I need to match entries of two tables Survey and Phones . Donno what am I missing on
Tables and example :
Phones
[CaseID], [SurveyID],[DateTime], [TelephoneNumber]
90 13875 2006-01-05 10:31:00.000 2814211345 NULL
90 13875 2006-01-05 10:33:00.000 2814211345 NULL
Survey has following columns
[CaseID], [SurveyID], [CaseStartTime],[Phone]
90 13875 2006-01-05 10:31:00.000 2814211345 NULL
90 13875 2006-01-05 10:33:00.000 2814211345 NULL
There are multiple entries of CaseID in both the tables. When a call is made, there is an entry in Phones table and after that an entry is made in Survey table. Timelag could vary from millisec to 2 or 3 mins.So the way to identify is through timestamp, factoring in mins difference.
I am using this query - does not match correctly though
SELECT t1.CaseID, t1.CaseStartTime,t1.ITS, t2.CaseID,t2.DateTime,
DateDiff("n", t2.DateTime,t1.CaseStartTime)as "Min_Diff"
FROM Survey AS t1,Phones AS t2
WHERE t1.CaseID = t2.CaseID and t1.CaseID=90
and DateDiff("n", t2.DateTime,t1.CaseStartTime) <= 2 and DateDiff("n", t2.DateTime,t1.CaseStartTime) >0
Order by t1.CaseID;
Result
90 2006-01-05 10:33:00.000 47 90 2006-01-05 10:31:00.000 2
Minoo S
|
|
|
|
|
Minoo S wrote: Donno what am I missing on
Proper relationships? Primary Keys?
I really don't know where to start on this. There is apparently no real relationship between these two tables. In which case, matching them up is going to be sporatic and error prone.
To get the situation right:
A phone call is made. At the start of the call a row is inserted in the Phones table.
At the end of the call a row is inserted in the Survey table.
Minoo S wrote: Timelag could vary from millisec to 2 or 3 mins.So the way to identify is through timestamp, factoring in mins difference.
No it isn't. Really! That isn't the way to design a database.
Why does the survey table not have a foreign key that matches the primary key of the Phones table? (Or if there is the possibility of a many-to-many join - why is there no intermediate table?)
I'm hoping that there is missing information here and you just need to post it. What is the structure of the two tables? (I want DDL or a description of column names, types and primary/foreign keys)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanx for your response Colin.
Scenarios : When a call is placed, it writes to Phone Log. After that logic goest to the Survey software and writes the information in Survey log. After writing in the survey log, the interviewer gets to talk to the respondent.
The information I am pulling out is from Phone log and Survey log. There is more complexity to the case as there could be a row in Phone log that does not exist in Survey log and vice versa. So while matching I need to have left and right join information as well. If I cannot resolve in sql, then may be I should work with some scripting langauge and write a prg to match. Can you think of any prg lang I could do this real quick ??
Minoo S
|
|
|
|
|
Minoo S wrote: There is more complexity to the case as there could be a row in Phone log that does not exist in Survey log and vice versa.
Okay - but that doesn't seem to excuse not having proper primary / foreign keys. Two times *close* to each other really does not cut it. For a database to join things up properly you need to be precise.
Minoo S wrote: may be I should work with some scripting langauge and write a prg to match.
That would be slow. Scripting languages are useless for this sort of thing.
Minoo S wrote: Can you think of any prg lang I could do this real quick ??
SQL - But only if your database is designed right in the first place.
Some questions:
What is the relationship between PhoneLog and SurveyLog? one-to-many, many-to-many, many-to-one, one-to-one
(This is a repeat of something I asked before - I really would like to know because it will help solve your problem) What are the primary keys of each of these tables as it stands now?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanx Colin . I will try to answer your questions.
Some questions:
What is the relationship between PhoneLog and SurveyLog? one-to-many, many-to-many, many-to-one, one-to-one
Sorry, I missed response on this one, its one to one relationship. So one entry in Phone can have only one entry in Survey LOG . Just that Phone log might have entries that were not recorded in Survey log and vice versa.
(This is a repeat of something I asked before - I really would like to know because it will help solve your problem) What are the primary keys of each of these tables as it stands now?
Primary Key : I will have to put ID to make the rows unique,I think that might join the rows mentioned in original examples correctly, if I index by unique rows ?? Presently , I parsed the logs and pulled out information and did not set Primary key. I understand it is gross mistake and may be main cause of the issue in first place ! I thot I could make CaseID and timestamps together but then we cannot use it as foreign key as the timestamp will not match in Survey table. Any ideas ?
Minoo S
|
|
|
|
|
HI ! Any clue from this point ?? Need more info ??
Minoo S
|
|
|
|
|
Minoo S wrote: Any clue from this point ??
I thought you'd figured it out already:Previously Minoo S wrote: Primary Key : I will have to put ID to make the rows unique,I think that might join the rows mentioned in original examples correctly, if I index by unique rows ??
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thx for your help on this one. My new question (calculations has the correct table structure, thx to you. Sorry on forgetting the rating on your response, one the issue was resolved, I was on another fast burn project !
Minoo S
|
|
|
|
|
Hi All,
We use MOM (Microsoft Operations Manager) software to monitor our systems. One problem it alerts us to is SQL blocks. However, it's too noisy and I need to tweak the alert rule. To make my testing easier, I'd like to be able to simply start and stop SQL blocking at my command (rather than wait for usage blocks to occur).
I'm not a SQL expert (by any stretch  ) and I've been looking for a script I could run (maybe in two QueryAnalyzer windows) to force a SQL block to begin. I could then KILL the SPID when I was done testing.
Do any of you know of something like this? Is this so easy, no one would ever post it on the web . If so, please let me know how I should go about this. If it's possible to create blocks without altering data, that'd be best.
Thanks!
Justin
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
