Hi,
I have a legacy sql 2000 database with numerous stored procedure and tables.
I need to find out the top 20 most used/accessed tables in the database
Any one know how i could do this type of trace with profiler?
I am trying to find a select on sysprocesses that would list all the activelogins. An active login is a login that has a TSQL statment being executedon the server,This didnt work to well! Any ideas. Thanks in advance.select sp.loginame,-- more columnsfrom master..sysprocesses spwhere sp.status not in ('sleeping','background' )order by 1
View 2 Replies View RelatedHi,
I am in the eary stages of planning a big reporting services roll out, and I have a problem. I want to be able to filter what is show in a parameter list, preferably based on a antive directory group the user is in.
As an example i could have a parameter called States that under normal circumstances would contain a list of states. I would want a sales person to see only there states, a regional manager to see only states in there region and board member to be able to see all the states. They wont nesseseraly be any information in the database about the users runing the reports.
The only way i can think of doing it is using active directory groups. Is this something i can do in reporting services, and if so has anyone any examples or pointers
Cheers
Luke
--
ACTIVE/ACTIVE SQL Server 2000 Cluster
This was my plan.
Physical box - box1,box2
virtual box - virtual1
i have a active/passive cluster virtual1 (primary node box1, secondary box2)
I am creating another virtual box on box2 as primary node by name virtual2)
whenever my installation setup runs my default server name is greyed out. it always requires a instance name.
Is that the way it should work?
i can only use instance for active/active failover?
please advice
We run several SQL 2000 SP4 instances on IA64 active/active cluster. The OS we run with is Windows Server 2003 SP1. We have different network cards : 2 network cards teamed for production purposes in domain X and 1 network card dedicated for problem solving in domain Y.
First we configured the cluster with only the 2 teamed network cards for production purposes in domain X. Later we introduced the second network card for problem solving in domain Y.
Everything looks fine. The (virtual) SQL instance listens on two different IP adresses on TCP port 1034. If we try to make connection via isql, EM or Query analyzer than we can directly contact the SQL instance from a workstation/server within domain X but this doesn't work form a workstation/server within domain Y. However if we use the specific TCP port in the connection in domain Y the connection is setup. We wish however not to use explicit TCP ports in setting up connections.
Has anyone experienced the same problem before or has anyone an idea how to solve the problem?
I am trying to find the list of active connections on SQL SERVER , more and more granular way ...like below , I know a way using active monitor or Profiler or some DMVs give me some list , but is there a way to find what is the server name ,instance name connectedc to it , who is the clinet connected to it , whats the application being used now?
what database it is connected to it ? what login being used to connect ? what table and column being used right now and what kind of DML or DDL are happeming ?
I want to create a report and find Dependancay evaluation so that we will re architech or re design the existing databases ...
ServerName InstanceName Client Application Database Login Table Column Select Insert Update Delete Truncate Alter
Hi
Is there anyone who knows where I can get some information about
installing an ACTIVE/ACTIVE SQL 2000 Cluster server (MSCS).
I have no problem setting up an active/passive failover cluster.
Where can I get more information? Do I need two instances? Two databases?
What's the difference between active/active and active/passive?
Thanks!
I have many tables in one database, and some of them are empty. How can I list / find (for eg. names) only non-empty tables?
View 2 Replies View RelatedWe have about 60 several tables with order id as a column in all of them . Every day I have to check if certain order id’s exist in the 60 tables. I just need to get the name of the table if it exists. Is there a way to dynamically keep changing the name of the table instead of having all 45 tables in the script?
View 2 Replies View RelatedSo I have a question and I will give a related example. Say I have a list of records in a table:
Product Cost Active
hat 1.00 false
coat 2.00 true
I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer
Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.
Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.
It's me again guys. First let me say that I truly appreciate the knowledge that I see on this forum. Now on to my question.
How can I use SQL Server to import the active accounts from our Exchange Server? Thanks.
Everything I know I learned from the web.
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
View 2 Replies View RelatedHi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?
Cheers,
Mathi
India.
I have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..
View 1 Replies View RelatedI am attempting to document a sql server 2000 based accounting system.Is there any way to see what tables a stored procedure affectswithout diving into the code?Regards,Ty
View 1 Replies View RelatedI was wondering how exactly one can search the Active Directory with SQL Server 2000. There is all kind of stuff on the Internet about how to connect them and how useful it is to connect them together - plus how you can use Active Directory to query SQL Server, but I`ve never encountered any article where they explain how SQL Server can search the Active Directory.
I`m thinking about how one has their user-database in Active Directory with all users in groups, but only uses groups in SQL Server. How exactly can SQL Server figure out how a user belongs to a group?
Does Active Directory tell SQL Server about that when the user tries to access something from SQL Server, or does SQL Server already knows which group is accessing it as soon as the user authenticates in Active Directory?
Hello All,I have a linked ADSI Server to our company Active Directory andeverything is fine. I'm running queries and such using LDAP. BUT howcan I aquire a list of attributes for the classes and categories. Suchas if i wanted to see a user's telephone number and email address foruse in a corporate database? Is this possible?Thanks
View 4 Replies View RelatedHi all,
just want to confirm this:
We have a department and want to grant them access to various databases on a sql server 2000 instance. Can we create an AD group for these users and just add the group as a login to SQL Server? Or do we have to create a db user for each member of the group and map the logins one by one?
Thanks
I have an active x script written in vb that just counts the records in a table and fails the step if there are not enough records. It is simple and effective. But when I transfered this task to my SSIS package I get a function not found error.
I am not familiar with active x or vb script so I have no idea what is wrong. I can do without while I build the package but I need it in the end to prevent possible problems....
Here is the script...
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim myConn
dim myRecordset
dim iRowCount
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")
' Init ADO Connection object
With myConn
.ConnectionString = "File Name=D:Data Linksmusic_load.udl;" 'Full path to the UDL
.Open
End With
mySQLCmdText = "Select 'totalcount' = Count(*) from fulltext"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("totalcount")
If iRowCount.Value < 1000000 then
' MsgBox "The number of products is: " & iRowCount.Value & ". The total number is wrong"
Main = DTSTaskExecResult_Failure
Else
' MsgBox "The number of products is: " & iRowCount.Value & ". The total number is good"
Main = DTSTaskExecResult_Success
End If
End Function
Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue.
I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode
In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int).
I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way.
What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field.
Any help or guidance that anybody can give will be most appreciated.
Dale
I have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are
1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'
Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.
View 3 Replies View Related
Hello,
I have two tables with the same field layout, and they both have the same field as the Primary Key. They just contain different data. I would like to know if a record exists in one, or both, tables.
The tables are InvTemp1 and SalesTemp1. The key for both is stock_number.
Here is the command so far:
SELECT COUNT(*)
FROM InvTemp1 INNER JOIN SalesTemp1 ON InvTemp1.Stock_number = SalesTemp1.Stock_number
WHERE (InvTemp1.Stock_number = '101053')
Thank you for any ideas,
Tom
We're trying to figure out whether it's possible to link AD objects to SQL tables. For example, if we have a table of Users and a table of Companies, we'd like to enter the users into Active Directory, create the Companies as either OU's or Security Groups, then have those entities magically appear in the corresponding SQL Server tables. Is that possible? Thanks in advance.
View 1 Replies View RelatedHello --I'm trying to get SQL Server 2000 on a Windows 2000 Server to be ableto query an Active Directory. We've got two domain servers one Win2000and one Win2003. However, I'm having problems:I've run the following query to setup the linked server:sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces','ADSDSOObject', 'adsdatasource'Using Enterprise Manager I've changed the login details for the ADSIlinked server to:Be made using the following security context:Username: MyDomainAdministratorPassword: ****I've then tried to run the following query in Query Analyzer:SELECT * FROMOpenQuery(ADSI,'<LDAP://CN=Users,DC=MyDomain,DC=com>;(&(objectCategory=Person)(objectClass=User));name,ad spath')But get the following error message:Could not execute query against OLE DB provider 'ADSDSOObject'.Does anyone have any ideas on what this message means or how to getthis working?Thanks--James Allanjames [at] allan-home.co.uk
View 2 Replies View RelatedWe have just installed a SQL Server 2000 (SP 3A) onto a computer thathas Windows-2003 Server on it. Now, we cannot get access to thatdatabase server from other computers. Seem like this may be an issuewith Active Directory.Our network consists of Windows-2000 Servers (SP 4) and Windows-2000workstations and Windows-XP workstations. We also have SQL Server 2000(SP2) in three Windows-2000 servers. All work fine. Recently, we get anew server that has already had Windows-2003 Server pre-installed.After we have installed SQL Server 2000 (SP 3A) on the new server, wefind that we cannot get access to that new database server from othercomputers.I have tried connecting to that new database server using QueryAnalyzer (using both the sa account and Windows Authentication), and Iget this error message:Unable to connect to server NEWDBSRV:Server: Msg 17, Level 16, State 1[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does notexist or access denied.I have tried connecting to that new database server using ISQL (usingboth the sa account and Windows Authentication), and I get this errormessage:DB-Library: Unable to connect: SQL Server is unavailable or does notexist.Unable to connect: SQL Server does not exist or network accessdenied.Net-Library error 53: ConnectionOpen (Connect()).I have also tried to register the new database server using EnterpriseManager (again, using both the sa account and Windows Authentication),and I get this error message:NEWDBSRV - SQL Server does not exist or access denied.ConnectionOpen(Connect())The only way that I can get access to the new database server is toopen Enterprise Manager directly on the new server.I have compared the properties of the new database server and those ofthe other existing database servers. The only differences are:-Other database servers are running Windows-2000. The new databaseserver is running Windows-2003.-The new database server is missing the "Active Directory" page-tagwhen I check its properties using Enterprise Manager. We use ActiveDirectory in our network, and the Active Directory is being run from aWindows-2000 server (that is currently also a database server).According to our network administrator, he has already put the newserver into our Active Directory. He doesn't know why it is missingthe Active Directory page-tag. He believes this has something to dowith its having Windows-2003 that is different from other servers(that have Windows-2000).Thanks in advance for any info.Jay Chan
View 2 Replies View RelatedHi all,
I have a customer with an RS installation in a DMZ, using ADAM for its authentication.
The problem I have is as follows :
When setting up the execution log reporting (supplied as part of the standard install) all information being captured for report execution e.g Which user ran a specific report and when, is coming up with 'UNKNOWN' for the user name.
Does anyone have any idea where to look to fix the recording of user information? I am not sure whether it is a RS or A.D.A.M issue.
Thanks
Clint Pugh
HI ,
I Created one database named login.sdf in sql Ce ,i created one table login having two fields (username and password).it is working well.I trying to synchronise these with SQL2000 using Active sync.it is not working ..And one more things ,i tried to connect the default pocket PC 2002 emulator with Active sync.it is also not working.can anybody give some solution for these problem
Thanks in Advance
Regards
Ihsan
Hello everyone,
We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.
So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.
Linked server 'ADSI' is set up with the sp_addlinkedserver command:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active
Directory Services 2.5', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
Two table functions in our test DB (trying to test both ways I've found in docs):
ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@userId nvarchar(50),
@password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @userId
)
ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@userId nvarchar(50),
@password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @userId
)
So calling either of these table functions from our custom application gives the same result:
select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')
ResultSet:
'astonaker', 'Anthony', 'Stonaker' 'NULL'
So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.
I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.
Any thoughts/suggestions are greatly appreciated!
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
View 4 Replies View RelatedI am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;
In our Production db we have all most all tables have the column created or createdate.
I need to find out all tables without the created or createdate column
SELECT t8.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c
ON t8.OBJECT_ID = c.OBJECT_ID
[code]....
Below I have a query which list the relations (constraints) between tables.
I want to list all the relations which are visible in the Database Diagrams.
The list is not complete. How do I get a complete list ?
--
-- Query to show the relations (constraints) between tables.
-- This does not show the complete list
--
SELECT A.constraint_name,
B.table_name AS Child,
C.table_name AS Parent,
[Code] ...