Last Access Time For A Database/Table

Jan 10, 2008



Hi there is there any way in tsql that I can tell when a database/table was last accessed? We have a lot of *** on our servers that need cleaning up.

If it is not possible could I put a trace on the servers that will tell me if any access has occured and run this for a couple of weeks for instance to see if any access has occured?

Thanks

Daz

View 3 Replies


ADVERTISEMENT

Is There Any Way To Know The Last Access Time Of A Table?

Dec 13, 2006

I have some junk tables in my database, but I am not sure if they are really not in use. Does sql server log the last time that a table gets accessed? when I say access I mean any actions toward the table, including select, update, insert, etc.thanks in advance.

View 2 Replies View Related

Database Is Slow To Access After Being Idle For Some Time

Feb 10, 2007

I've noticed that after the database have been idle for some time, it takes up to 10 seconds to get it started when something needs to access it. In the event viewer it says that the database <name> have been started.

Obviously, there is some idle timeout setting.

I saw an option in the database properties that is called "Auto Close" which is set to true. I assume this is what i'm looking for. Can someone confirm that? (it could take some time to test myself...)

But what i'm actually wondering is:

1. Is it possible to adjust how long it would wait before timing out?
2. What advantages does closing the database bring? Does it free up (a noticeable amount of) ressources? Or is it only that it's unlocking the files, so that it's possible to copy the database source files?

View 4 Replies View Related

How To Access Database In Single User Mode All Time

Aug 23, 2004

Dear All,
I want my SQL database to be accessed always in a single user mode through my application. How can I do this ?

Thanks in advance.

Sushma

View 5 Replies View Related

MS Access Table Load With SSIS - Date/Time Field Problem

Jan 25, 2006

I am trying to load a table from MS Access into SQL Server. The Table has several columns defined as Date/Time. When I define the transform I get an error saying that the conversion between DT_DBDATE and DT_DBTIMESTAMP is not supported.

How do I get around this?

View 1 Replies View Related

How To Find Database Table And Their Field Name Run Time.

Jul 8, 2007

 I am using sql server as back end. Through connection string
I am getting database name. But in A dropdown I want to show list of table in
that database. And in another B drop down I want to show fields of table
selected in A dropdown. can any one help in getting the field .

 

View 2 Replies View Related

Data Access :: How To Load Data From CSV File In Temp Table At Run Time

May 28, 2015

how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time. 

View 3 Replies View Related

How To: Send Time To Database Table On Button Click

Mar 21, 2008

Hello, I need to know how I can make a button on my page send the Time of Click, to a table in a database, and repeat this everytime the button is clicked. Thanks for any guidance on this matter 

View 9 Replies View Related

Access A Table From A Different Database

Aug 13, 2001

Hi all,
I'm writing a procedure that require to access a table from a different database but from the same server. Can the procedure able to read this table. If so how should my procedure use that table?
Thank you for all your help.

View 3 Replies View Related

How To Access Table From Different Database

Apr 11, 2008

hi,
i am using stored procedure which put data in another table fron different database.

so i am using servername.databasename.dbo.tablename.

if I am change the servername then i have to edit the Stored procedure.
Is there any way to access server name.so that if server name is chane then also i dont have to change stored procedure.

Like in c we use #define where we change value of variable that is reflected in whole program.

View 1 Replies View Related

How To Access Table In Different Database?

Apr 29, 2008

Hi,
I have 2 databases in SQL Server named Database1 and Database2. I have logged in Database1. I would like to see the records of the Table which is present in Database2. Could anyone please tell me How to access the tables in Database2 from Database1?


Thanks,
Sathish kumar D

Sathish kumar D

View 1 Replies View Related

Determine Last Access Of A Database Or Table

Dec 18, 2007

Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!   

View 2 Replies View Related

Transferring ACCESS DataBase(ABC.mdb) To SQL Table Using C#

Feb 14, 2008

I want to transfer ACCESS DataBase(ABC.mdb) to SQL Table Using c#.(SSIS Programming)
i need to use this query on access database

which will when run create 4 fileds
ABC.mdb
Sorce_Db_Id_Col

Attribute_Type

Attribute_Value

Query_Flag

And sorce database
Indentity_col
Att_typ
Att_value
Att_catg

I m very new in this stuff. so please please any one have any sample code for this then send me !
please i realy need it
My email id is ripal.parikh@softwaresolutionsindia.com
Thanks lot!!!!

View 3 Replies View Related

How To Access The Info Of A Table Created In Other Database

Aug 24, 2006

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V

View 2 Replies View Related

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm

Jun 4, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?



Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,





View 1 Replies View Related

Stored Procedure Can't Access Table On External Database

Mar 6, 2005

Hi

I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.

My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').

I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.

Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.


Example of stored procedure on external database:

/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/
CREATE PROCEDURE dbo.AddUser
(
@Username Varchar(20),
@Password Varchar(20)
)
AS
(
Select User_ID From UserList
Where User_Username = @Username
)
GO

Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!

Any help would be greatly appreciated as I am completely stuck.

John

View 2 Replies View Related

How To Import Access Table Into SQL Express 2005 Database?

Mar 7, 2007

Can anyone help me import a ms access (97) table into a sql 2005 express database? The sql database already contains other tables but I would like to add some of my older access tables to this database without having to re-enter all the data. Please help! Thanks!

View 4 Replies View Related

Denormalized Access Table To Normalized Database TableS

Apr 17, 2006

Hello,

I am pretty new to SSIS, so please excuse me if this is a trivial question.

I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.

I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?

I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.

The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!

TIA

-Brian

View 1 Replies View Related

Can Never Access Database Table From Code After Copying To Another SQL Server...

Nov 29, 2007

I am not sure I understand the problem I am causing, but I am a beginner!

Here's the situation: I have a table located on MS SQL server database number 1. Said table, which we'll call WIDGET_PRICES, is accessed regularly by my existing source code and has no problems.

At some point, I decide to move operations to MS SQL database number 2 and do a very simple database copy of WIDGET_PRICES from database 1 to database 2 using the Microsoft SQL Server Management Studio.

The end result, inevitably, is that my source code can no longer access the very same table as it is located on the new database server. The code hasn't changed, it's still trying to access WIDGET_PRICES as always. And, from what I see on my screen through Management Studio, WIDGET_PRICES appears just fine.

An example error is the one I just got:



Microsoft OLE DB Provider for SQL Server error '80040e37'

Invalid object name 'YB_ITEMS'.

/yardbark/tampabay/header.asp, line 27




The only clue is that while my transferred tables often look named like "database1.WIDGET_PRICES on database 1, they wind up looking like database2.WIDGET_PRICES on dabase 2.




I include a little more detail and screenshots of the tables in questions at this web page.




Dave

View 3 Replies View Related

Connecting To SSCE3.5 Database Using Access Link Table?

May 20, 2008



I'm a long-time Access developer who is looking to migrate his approach to SQL Server CE 3.5 instead of having all these MDBs. One thing that I do like is using Access to surf through databases; it's a lightweight way to see the data without having to fire up the overhead of Visual Studio etc. Plus there's the QBE grid for easy query design for those of us whose tool-less SQL coding is a bit rusty...

Anyway, I was wondering whether it was possible to "attach" to a SSCE database so you can see it in Access. I've tried but haven't found a way of doing it. Any thoughts? Or is this an intentional lack of capability so we have to use a more robust tool such as Management Studio or Visual Studio?

Thanks in advance!

View 1 Replies View Related

Data Access :: Grant A User Permissions To Only One Table In A Database?

Jul 28, 2015

How do I grant a user permissions to only one table in a database.  How would it affect him using our Main App which is NAV with regards to his user's permission in NAV

View 2 Replies View Related

VB6 &&amp; VB2005 Runtime Error When Writting To Access Database Table

May 1, 2007

In both VB6 and VB2005 Both are loaded on same computer. When attempting to write to an access database table using ADO I get a runtime error: -2147217887(80040e21) "mutiple-step operation generates errors. Check each status value. " Reading the database is no problem. I only get the errors when writting to database. I have used the following code forever in VB6 and never got this error. Now that I have Visual Studio 2005 loaded on the same machine. I get this error. I get the exact same error when using this code in VB2005. I know VB6 but am just learning 2005. The following code is writting to a database table from various user imputed text and combo boxes. I used basicly the same code in VB2005 and get the same exception error. This code works on machines that do not have VB .net loaded. How do I fix this. My application is useless unless the user can make changes to their data.



Public Function ActionToDb()
Dim noPo As Integer
Dim SQL As String, rst As ADODB.Recordset, dataSourceName As String

dataSourceName = DBPATH & "LVBURV Database.mdb"
dbConn.ConnectionString = "DBQ=" & dataSourceName & ";Driver={Microsoft Access Driver (*.mdb)}"
dbConn.Open

On Error GoTo ErrorHandler

Set rst = New ADODB.Recordset
With rst
SQL = "Select [mLVbuNo], [mStratInit], [mMerticLv],[mSubMetLv],[mTaskAction],[mImpact]," & _
"[mStatus], [mOwner], [mTitle],[mOrigDate],[mCurrentDate],[mCopmpleteDt],[mComment],[mSource]," & _ "[mStatColor],[mHyperlink],[CompCheck] From [tblMasterActionItem]"
.CursorLocation = adUseClient
.Open SQL, dbConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
noPo = .RecordCount
'If .RecordCount > 0 Then
.MoveLast
.AddNew
rst.Fields("mLVbuNo") = giRecNo
rst.Fields("mStratInit") = frmActionDe.cboStatInit.Text
rst.Fields("mMerticLv") = frmActionDe.cboMetricLever.Text
rst.Fields("mSubMetLv") = frmActionDe.cboSubMet.Text
rst.Fields("mTaskAction") = frmActionDe.txtTask.Text
rst.Fields("mImpact") = frmActionDe.txtImpact.Text
rst.Fields("mStatus") = frmActionDe.txtStatus.Text
rst.Fields("mOwner") = frmActionDe.cboOwn.Text
rst.Fields("mTitle") = gstOwnT
rst.Fields("mOrigDate") = frmActionDe.txtOrigDt.Text
rst.Fields("mCurrentDate") = frmActionDe.txtCurDt.Text
rst.Fields("mCopmpleteDt") = frmActionDe.txtCompDt.Text
rst.Fields("mComment") = frmActionDe.txtComments.Text
rst.Fields("mSource") = frmActionDe.cboSource.Text
rst.Fields("mStatColor") = frmActionDe.txtStatColor.Text
rst.Fields("mHyperlink") = frmActionDe.txtHyperlink.Text
If optCompCheck.Value = True Then
rst.Fields("CompCheck") = True
Else
rst.Fields("CompCheck") = False
End If
'End If
.Update
.Close
End With
dbConn.Close

ExitSub:
On Error Resume Next
dbConn.Close
Set rst = Nothing
Set dbConn = Nothing
Exit Sub
ErrorHandler:
WriteToErrorLogEnd Err.Description & " in ActionToDb"
Resume

End Function

View 6 Replies View Related

Unexpected Error From External Database Driver (MS Access Linked Table)

Oct 19, 2006

OS: Windows XP (SP2)
App: MS Access 2003 (SP2)
DB: MS SQL 2000 (SP4, 2040)

User is using MS Access linked table to query database (using ODBC). User can open the query in MS Access. However when the user goes to export the data to MS Excel format, the user receives the error message:

"Unexpected Error from External Database Driver (22)."

A trace on the db, reveals that the user process is attempting to log in to the server as 'Admin'. However, when the query with the linked table is first opened, the user is prompted for username/password (non Windows authentication).

Any ideas? I googled and found some stuff, but nothing directly related and most of what I found was supposedly fixed with SP1.

Any help appreciated. I am not an Access guru.

Regards,

hmscott

View 1 Replies View Related

Data Access :: How To Retrieve Remote Database Table Value To String Variable

May 28, 2015

I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it. 

And my code is :

string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();

View 6 Replies View Related

How Can I Copy The Content Of MS Access Table In Database SQL Using Visual Studio Basic

Sep 26, 2007

Code Snippet

Hi there,

I'm struggeling for more than a week now with this problem, without a finding the solution.

I have two databases, MS Access and SQL Server 2005 Express Edition

Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)

I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...

Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?


Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).

the procedure goes like this;


' Create a connection to the MS Access Database
Dim connectionToAccess As New OleDbConnection(DBConnectionAccString)
strsql = "SELECT * FROM [TABELLA]"
connectionToAccess.Open()
Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)

Dim DsAccess As New DataSet("ACCESS")
DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella")
DaAccess.Fill(DsAccess, "Tabella")

' Create a connection to the SQL Database
Dim connectionToSQL As New SqlConnection(DBConnectionSQLString)
connectionToSQL.Open()
Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)

Dim DsSQL As New DataSet("SQL")
DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella")
DaSQL.Fill(DsAccess, "Tabella")

DaSQL.Update(DsSQL, "Tabella")

Note I tried also the following, withou a result;


DsSQL = DsAccess.Copy
DaSQL.Update(DsSQL, "Tabella")

Please is there someone who could respond !!???

View 6 Replies View Related

Splitting SQL Server Date/Time Column Into Access Date Column And Access Time Column

Jan 24, 2008

I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.

What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?

Thanks,

Steve

View 3 Replies View Related

SQL 2012 :: Converting Time String In Temp Table To Military Time Then Cast As Integer?

Dec 26, 2014

I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:

Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')

how this can be done while my temp table is in session?

View 2 Replies View Related

DB Engine :: Time Testing Azure Point In Time Database Restores?

Sep 21, 2015

I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.

View 3 Replies View Related

Reporting Services :: SSRS Loses Ability To Delegate User Credentials To Database Time To Time?

Apr 30, 2015

we have problems with our SQL Reporting Service 2012 (SSRS) server . We have setup Kerberos delegation between SSRS and the database server (SQL Server Always-on cluster) so users are authenticated down to the database. The issue occurs from time to time that SSRS loses the ability to delegate the user credentials to the database. At this point in time the Report Server logs contain rejected database connections because of ANONYMOUS logon. After restarting SSRS the problem is gone.

View 2 Replies View Related

Database Access Via COM Objects - V- Database Access Via Stored Procedures

Aug 17, 2000

We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?

View 1 Replies View Related

Loading Images In A SQL Server Express Database Table At Design Time Using Visual Basic Net Express Editon

Jun 24, 2007

I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me

View 3 Replies View Related

Last Access Time

Sep 18, 2013

How can i find databases' last access times in server

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved