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.
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
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?
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 RelatedHi, 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,
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,
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
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?
How can i find databases' last access times in server
View 1 Replies View RelatedMy manager has asked me to pull out the following info from the SQL Server.
Who access the system, How many times?
Average duration of access?
Average daily/weekly… use of the system?
I'm new to SQL Server and do not know, how to get these following things out of the system. Would anybody please advice me, how to get it. I would really apprecaite it.
Thanks a million for your help.
Sindi
Is there a way to determine the last logon time for a given SQL Login?
View 1 Replies View RelatedHi All,
Which some 1 can help me this.
I've developed an application and everything work fine after deploy. But after restarted my PC, i'm unable to login to my application which tie to the xxx.mdf DB and prompt Database error with no description. Until when the i click second time, only i'm able to acess into it. What happened actually?
I'm using sql express as embedded db on my local pc. This is without any remoting access.
Access Connection
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!
Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
Any ideas please help
thanks in advance
Dave
Hi All,
Is there a way in SQL Server 7.0 or 2000, where I can grant/deny/revoke access rights on a database objects like Table, Stored Procedures for a particular time of the day.
Example: I want to prevent user A from acessing Table x and Stored Procedure Y from 9Am to 12 noon everyday. After 12 Noon till 8.59 AM he can have access to Table x and Stored Procedure Y.
Is there a way to do this at SQL Server level.
Thanks
Sri
I have a stored procedure which creates 3 temporary tables. Every table is about ten rows and 25 columns. The inserts in the tables goes fast (< 30 ms). The selects from them is also that fast. BUT the first select takes about 3200 ms one each of the temptables. (I first do insert, then select from them.) So the SP executes at about 13 seconds instead of 3.
Any suggestions, anyone, please?
I want to learn access connection gives how many user to connect at the same time.
http://www.narcv.com/
I use MS ACCESS and tables linked to MS SQL SERVER tables.
There are indexes defined (they are visible in MS ACCESS too).
Sometimes DLOOKUP functions are taking long time.
Why?
We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.
In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.
When we access Guider we have no problems getting data.
When we access Talker we have a login failure:
Cannot open database 'Talker' requested by the login. The login failed.
Login failed for user 'AcornCommunicationServices'.
The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.
Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!
Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using
Management Studio Express to manage the database.
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?
Hi Everyone,
I have set up a link from ACCESS to a SQL 7.0 database using ODBC (File DSN saved on a shared DRIVE). The link works well only from the workstation where the link was created. But How can I create a link so a group of users can view the linked table in ACCESS without type a password? Any suggestion is appreciated.
Lunjun
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
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
Every day between 1AM to 2AM client disconnect happens and it's not too long also...it's about 30secs...nothing is recorded from SQL side and application server side and then we checked the network...there is no disconnect from network side too. How can i find the cause for disconnect.Â
View 4 Replies View RelatedSQL server job or SP to deny access to an AD login for certain period of time to SQL server instance...i.e. to deny access to login ADxyz from 12 PM to 10 PM and revoke access to same login at 10:01 PM...
View 3 Replies View Relatedneed help please on update only if .
i need to update only the field "val_holiday " (in table B from table A)
and olso to check on table B the "ID" + "new_date" only if exist
and update the field "val_holiday " (in table B)
and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B
select from table A
update table B
WHERE ..........................HOW ?
----------------------------------------------------------------- table A
ID fname new_date val_holiday field_check_update_if _ok
---------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 999 0
111 aaaa 16/03/2008 888 0
111 aaaa 18/03/2008 77 0
111 aaaa 19/03/2008 9 0
111 aaaa 20/03/2008 111 0
111 aaaa 21/03/2008 12 0
222 bbb 02/05/2008 15 0
222 bbb 03/05/2008 16 0
222 bbb 04/05/2008 9 0
222 bbb 05/05/2008 3 0
222 bbb 06/05/2008 90 0
222 bbb 07/05/2008 3 0
222 bbb 08/05/2008 3 0
222 bbb 09/05/2008 3 0
333 ccc 03/04/2008 4 0
333 ccc 04/04/2008 4 0
----------------------------------------------------------------- table B
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
------------------------------------------------------------------------------
TNX for the help and for all
need help please on update only if .
i need to update only the field "val_holiday " (in table B from table A)
and olso to check on table B the "ID" + "new_date" only if exist
and update the field "val_holiday " (in table B)
and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B
Code Snippet
select from table A
update table B
WHERE ..........................HOW ?
----------------------------------------------------------------- table A
ID fname new_date val_holiday field_check_update_if _ok
---------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 999 0
111 aaaa 16/03/2008 888 0
111 aaaa 18/03/2008 77 0
111 aaaa 19/03/2008 9 0
111 aaaa 20/03/2008 111 0
111 aaaa 21/03/2008 12 0
222 bbb 02/05/2008 15 0
222 bbb 03/05/2008 16 0
222 bbb 04/05/2008 9 0
222 bbb 05/05/2008 3 0
222 bbb 06/05/2008 90 0
222 bbb 07/05/2008 3 0
222 bbb 08/05/2008 3 0
222 bbb 09/05/2008 3 0
333 ccc 03/04/2008 4 0
333 ccc 04/04/2008 4 0
----------------------------------------------------------------- table B
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
------------------------------------------------------------------------------
TNX for the help and for all
I have a table with following data
(Id, date ,time)
11 2015/8/1
12:20:00
11
2015/8/1 12:21:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
11:21:00
12 2015/8/1
18:10:20
I need the table with following record only
(Id, date ,time)
11 2015/8/1
12:20:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
18:10:20
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
Hi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
thanks
i want to add more than one row in the same time i tried to use for loop but didnt work any ideas please
View 2 Replies View Relatedhii,,i am using asp.net 2005 and sql server 2005,,i have a page called as demo.aspx..in that i have used sqldatasource and gridveiw to display the data from more than one table,,i have also given the edit,update and cancel commands thr gridview,,prob is how can i update data in more than one table whn i click on update link,,heres the select code so tht u can get an idea what all tables i have used ,,how to write the update code for the same,,need help,,thnks in advance
SELECT SME_Master.FirstName, SME_Master.LastName, Agency_Master.Agency_Name, Certification_Master.Cert_Name, Certification_Details.Cert_Date, Certification_Details.Percentage, SME_Master.SME_Id
FROM SME_Master INNER JOIN Certification_Details ON SME_Master.SME_Id = Certification_Details.Sme_Id
INNER JOIN Certification_Master ON Certification_Details.Cert_Id = Certification_Master.Cert_Id
LEFT OUTER JOIN Agency_Master ON SME_Master.Agency_id = Agency_Master.Agency_Id
WHERE (Certification_Master.Cert_Id = @Cert_Id)
_____________________________
pls reply asap...