Log Users Querying Table
May 17, 2000We have been asked to keep a log of users querying a particular table.
User's can query any odbc compliant tool, not just one application.
Can we do this w/ sql server 7?
ADVERTISEMENT
Transact SQL :: Querying Data From Master Table Into Transaction Table?
Oct 13, 2015I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
Querying Using Another Table
Apr 30, 2008Hello all,
I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
does anyone have an idea?
thanks in advance.
Querying Table More Than Once
Jun 12, 2008Hi All
Thx in advanced for any help anyone can offer me.
I have 2 tables Staff and Class which are as follows
Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .
Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Thx for any help Scott.
Querying Between Table Adapters ?
Sep 12, 2006Hi all, I am currently moving from access queries to SQL and am using the query Builder In Visual web developerto help me with the SQL Code..This has been easy while just querying the one table adapter but Now I am stuck..I have a table adapter that links to an SQL Server database through a connection in web.config.I have another table adapter that links to DB2 through a different Connetion in web.config.My question is ..How do you query from one table adapter to another.Each table adpater can only query based on the 1 connection ? Thx,Ray..
View 1 Replies View RelatedQuerying The Size Of Table Fields?
Jun 5, 2008Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database.
for example instead of declaring a parameter like this:
@ProjectDescription varchar(500)
I would like to do something like this:
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
Thanks for the help!
How To Keep Original Order When Querying A Table?
Sep 10, 2004Hello, everyone:
I have a table like:
ColName
b
b
b
d
d
d
a
a
c
c
c
I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c
However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d
Can any one have the idea to handle that? Thanks
ZYT
Querying Table In Arbitrary Database
May 1, 2006Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.
What I've tried:
DECLARE @DBName varchar(10)
SET @DBName = 'WBTST'
SELECT TOP 100 PERCENT Col1, Col2
FROM [@DBName].dbo.Table1
GROUP BY Col1
Result:
Invalid object name '@DBName.dbo.UPR00100'.
I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.
Thanks for the help
Wes
Querying A Really Badly Designed Table
Mar 28, 2006I am working on an application with a number of tables (which I can't
change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3,
c3... The tables contain 5 to 10 sets of data in each row.
I want to create a stored procedure to return a cursor with a row for
each set of data for a row of the table. I know I can use unions:
select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk
union
select a2, b2, c2... from sometable where pk=@pk
union
select a3...
Is there a way that is more efficient (eliminates requerying the table and all the unions)?
SQL Query Question- Querying The Middle Table In A M To M
May 17, 2000I need help with a query, and I haven't seen anything like this in a SQL book.
I will use the Northwind database for this example. The tables I am using are Products, Order_Detail, and Orders. There is a many to many relationship between Products and Orders, that is resolved by the Order_Detail table. Suppose I want to find out which products are related to each order. How would I query the tables?
Thanks everyone,
Nathan
Querying Single Table For Multiple Summaries - How?
Jul 20, 2005Folks,While I still have some hair left, can someone help me with thisquery?I have a table "TestRunInfo". Amongst other fields there are"TestRunIndex" (Pri Key), "TesterID", "Duration", and "Status".The Status field links to a Status table, which links the index valueto a more meaningful label "Pass", "Fail" etc...As you may have guessed, there is a record for each test that anindividual tester runs, and with that record is a duration, and status(1,2,3 etc).What Im trying to do, is create a datasheet view, with a single rowfor each testerID, summarising that Testers work as follows:TesterID, Total Duration, Count of passed tests, Count of failed testsSo far I have:Select TesterID, sum(Duration), count(Status) FROM TestRunInfo GROUPBY TesterIDBut this of course purely gives the total number of tests run by thatengineer as the count. I need to break it down. Help? Someone?Please?!?!?TIASteve
View 2 Replies View RelatedTransact SQL :: Is Table Will Be Extracted When Querying To DB Views
Sep 15, 2015I wonder how the query will be executed when querying to views.Is database engine will be extract the tables that are used to create the view and query for the result ?
View 5 Replies View RelatedData Warehousing :: Querying In Fact Table
May 2, 2015I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
1. XML
2. Spatial
3. Clustered
4. Non-Clustered
SQL Server 2012 :: Querying Table With Several Date Type Columns
Oct 30, 2014I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...
The rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.
Obviously because I have all the columns in the select statement... So I need something like
Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
Query A SQL Server Compact Table While Querying A SQL 2005 Server Table
May 21, 2008Hi there,
I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.
SELECT * from RemoteDB.TESTDB.dbo.Objects
WHERE Last_Updated > '2008-05-21 10:51:00'
AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)
Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.
Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.
Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?
Thanks in advance
SQL Server 2012 :: Querying A Supersession Two Column Table With Multiple Supersessions In Both Columns
Jan 29, 2014I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
SQL Server Admin 2014 :: How To Read And Load Extended Events Into A Table For Querying
Jun 19, 2015I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.
Locking Users Out Of Table
Jul 20, 2005SQL Server 2000, MSDE 2000I have a procedure in my application that I would like only one user at atime to be able to run. Is there a TSQL command I can run that willesentially lock a set of tables so others cannot access the table until theuser is done with the procedure or until the user disconnects from thatsession (in case of a hung app, I would like to lock released)TIA--Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehiclerelated website.http://www.vehiclewebstudio.com
View 3 Replies View RelatedHow Generate SID On Users Table
Nov 12, 2007Hi,
How can I generate the user SID on ReportServer.dbo.Users table?
Tks
Updating A Users Table In SQL Server
Nov 10, 2003Hello all,
I am relatively new to this platform and am trying to update a table of user information and passwords through a series of SQL commands. We import a new file of users every two hours and I would like to have three SQL statements:
- One to add new users not currently in Users table
- One to delete users no longer in our source file
- One to update every field (but one or two) for already existing users.
Any help on creating the SQL (as well as where to store it and how to automate it) is greatly appreciated!
Thanks!
Hierarchical Table - How To Get Data From Users
Nov 30, 2013I have two table. Department is hierarchical table.
Department
--- id (int primary key)
--- name (varchar)
--- parent (int)
Users
--- Id
--- name
--- department_id
This query return all data from departments. But i cannot understand how get data from users
SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3
FROM Department AS t1
LEFT JOIN Department AS t2 ON t2.parent = t1.id
LEFT JOIN Department AS t3 ON t3.parent = t2.id
Referencing A Users Table For History
May 6, 2008I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
Reporting Services :: Adding (new) Child Domain Users To SSRS As System Users?
Jul 28, 2015We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?
Mar 6, 2008I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedSQL Security :: Did Not Create Any New Users And There Are No Other Users Listed In Accounts Section
Sep 28, 2015I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies View RelatedCOPY TABLE Users AS UsersCopy WHERE UserId &> 500 ?
Nov 22, 2005I must extract rows (with criteria) from a table with a few millions of rows in MS SQL 2000
Is there a way to COPY a table ?
if so is there a way to COPY it in a more selective way ?
something like :
COPY TABLE users AS usersCopy WHERE UserId > 500
thank you
Allocating Rows In A Table To Multiple Users
Jul 19, 2006Dear all
In our organization there is a call center where operators entry call details and custormer qruery in a table using front end application. For these queries there are multiple no of executive who will reply the query using a front end application. using the application the exectives retrive specific no of records from call center table(if there are unreplied call exists) and reply them. When a excutive retrives specfic no of rows, i want to update a flag in that rows, so that others do not get them.
I use the following procedure which is creating dead lock when multiple users execute it simultaneously
create procedure get_from_maintab @cnt int
as
declare @slno int,@curcnt int
select @curcnt=1
set rowcount 1
while @curcnt<=@cnt
begin
begin tran
select @slno=serial_no from maintab where getflag=''
if @@rowcount=0
begin
rollback
return
end
update maintab set getflag='Y' where serial_no=@slno and getflag=''
if @@rowcount = 0
begin
rollback
return
end
insert into detab select fld1,fld2,fld3 from maintab where serial_no=@slno
if @@rowcount = 0
begin
rollback
return
end
commit tran
select @curcnt=@curcnt+1
end
set rowcount 0
return
How To Check How Many Users Are Logging In A Database And Table?
May 21, 2004Hello, everyone:
I want to check how many users are using a special database and table. How to do that? Thanks.
ZYT
How To Handle Users Hits To Single Table
Feb 4, 2008Hello friends
I am new to SQL database design.I am having a table name customerletter_master in which all details of customer letters are saved.At a time i.e in single second around 200- 400 entry are made in customerletter_master table. Hits to customerletter_master table are more.
Following fields are used in customerletter_master – CID (autogen number), letterno(primary key),consignee, consignor, letterstatus1, letterstatus2, letterstatus3, POD, and some more fields. When letter passes to different stages letterstatus1 is filled to yes and then letterstatus2 and letterstatus3 are filled according to passage of letter.
And at same time many user can accesses the customerletter_master table to search any letter according to letterno. Therefore customerletter_master is used to enter data and to search data at same time and there can be more than 200-400 users doing add and search records to and from customerletter_master
how should i design this table. What should i use to enter and search record and minimize the table hits made by the user at same time. Should i use store procedures or any other method to add and search record. Plz help me out by giving some example.
Prevent Other Users From Changing Data Of A Table
Sep 20, 2006hi..
How do i prevent other users from changing the data of my tables? Means one can change data using only my login rest others cannot even DBA or also from server administrator
How To Send Email Automatically To Users From Table?
May 2, 2008Hi,
First of all I want to apologize if I am in the wrong forum. I am new to SQL Server.
I have a table with a field containing email address from user input. Is there a build-in function or script in SQL Server 2005 that I can send a template of a email message to users? If not in what way can I acheive this?
Thanks for all your reply.
Stored Procedure To Get All Users Or All Users Of A Specific Country
Apr 30, 2008hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
SQL Server Admin 2014 :: List Of Users That Accessed A Table
Jul 22, 2015I'm trying to find out what tables are being used in a Database.
I don't want the last User but the User and the Dates.
I have a script that return the last user but that is not going to work.
The following script returns the last user but not all users and the Login Name:
ITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
[Code] .....