Querying SQL Server Logs

Feb 17, 2005

I've been digging for a while to try to find something, even had a consultant looking for me, but came up empty haned.

What I want to do is this:

I activated the Security Audit level for all logins. I want to be able to report last login date for every user from the log, and all login events for SA or any other defined super users.

Is it possible to Query the SQL Server Logs to report this information?

This is for proposed Sarbox Database Security settings.

View 1 Replies


ADVERTISEMENT

SQL Server Logs (error Logs)

Aug 28, 2001

When looking at SQL Server error logs, I noticed that the current error
log had grown to 1MB whereas most of the files are only a few KB.

QUESTION 1: Can I set the max size for an error log file? If so, how?

QUESTION 2: If not, is there a workaround?

Thanks

View 1 Replies View Related

DB Engine :: Write Logs To Windows Event Logs?

Aug 6, 2015

OS: Windows 2012 Enterprise

SQL Server: 2012 Enterprise

I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log. 

View 3 Replies View Related

SSIS Logs Vs Custom Logs Components

Feb 12, 2007

Hi,

In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.

Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.

Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.

Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.

What do you think? Kindly advise.

Thanks in advance for your help and time.

Regards,

$wapnil

View 3 Replies View Related

Querying Across Databases On Same Server

Jul 23, 2005

I know that a heterogeneous query joining tables from two different servershas performance penalties but is the same true when joining tables from twodifferent databases on the same SQL Server 2000 instance?We are looking at setting up a Data Warehouse using DTS on a SQL Server boxand I'm wondering about the best way to logically set it up; i.e. one bighonking db or several dbs determined by some logical organization. With thelatter there will still be some queries that would need data from more thanone db and I'm wondering if that will have worse performance than if theywere all in one db.I thought that was the case in older versions of SS, but I couldn't findanything in 2000's BOL that indicated a problem with that.TIA

View 1 Replies View Related

Querying SQL 6.0 Server From SQL 7.0 - SQLOLEDB Error

Mar 31, 1999

I am remotely logging onto a MS SQL 6.0 server using sp_addlinkedsrvlogin.
When I try to query against the table on this server I get the following error
message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: The data source can not be used, because it DBMS version is less than 6.5.0.]

I know that 6.0 doesn't support OLEDB but shouldn't 7.0 be able to access this
using odbc. Do I need to do anything other than set up the dsn on odbc.

Any feedback on this would be great.
Cheers,
bhanly@apcc.com

View 1 Replies View Related

Querying Active Directory Using Sql Server

Jul 1, 2005

Any idea on how to query active directory to find out list of groups nad users etc..using sql server?

View 6 Replies View Related

How Do I Specify Catalogue While Querying A Linked Server?

Dec 2, 2005

How do I specify the Catalogue while querying a linked server?

For exmaple, I have a remote SQL 2000 server as a linked server on my server. I will refer to it as "ServerRemote." I was given access to that server through my Active Directory Domain account and the catalogue 'master' was set as my default database.

Aside from having the admin change my default database, is there a way to query a linked server and specify the catalogue in the query?

Right now I use the following code to query the default catalogue for the linked server:
SELECT * FROM ServerRemote...ViewName

I've tried various queryies for a different catalogue but always get a table not found error:

SELECT * FROM ServerRemote..DatabaseName.ViewName
or
SELECT * FROM ServerRemote.DatabaseName..ViewName
or
SELECT * FROM ServerRemote.DatabaseName.ViewName
all return errors

View 1 Replies View Related

Querying Tables From Different Databases On Same Sql Server

Nov 15, 2006

Hi all,

How do I query two tables in different databases on the same SQL Server?

In short, I want to do:

Select A.*
from database 1. table 1 as A
inner join database 2. table 1 as B
on A.COL1 = B.COL1

Both database 1 and 2 are on the same SQL Server.

Please advise.

Thanks,

V

View 4 Replies View Related

SQL Server 2012 :: Querying XML From SSMS?

May 5, 2014

I have an xml document that (for this example) I've simplified to look like this:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>

[code]....

When I try querying the xml document in SQL, I get nothing back, unless I remove the schema information. I'm using this:

declare @x xml
select @x = P
from openrowset (bulk 'E:VehicleOption0514.xml', single_blob) as Products(P)
declare @hdoc int
exec sp_xml_preparedocument @hdoc output, @x

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Querying From XML Type

May 23, 2014

I have a plain XML variable

<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>

When I insert this into a temp table, the order get mixed up.

SELECT I.value('@Id', 'INT') ProductId
INTO #ProductList
FROM @Products.nodes('/BS/B') AS T(I)

When I select out of #ProductList the order is random and different from the XML.

View 8 Replies View Related

SQL Server 2k Querying Varchar Without Apostrophes

Jul 20, 2005

I am currently migrating our Intranet from SQL Server 7 to SQL Server2000 and have hit a problem with one of the applications. Theapplication in question executes the folloiwing query against thedatabase:select * from employee where emp_id = 760In SQL Server 7 this works without a problem, however, in SQL server2k the following error is returned:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error convertingthe varchar value 'Con25' to a column of data type int.This is clearly because the column emp_id is a varchar, I understandthat this code has not exactly followed best practices but do not wantto have to trawl through the application and surround all instanceswith apostrophes.Is it possible to make SQL 2k act in the same way as SQL 7 or will Ihave to trawl through the code and correct the SQL statements.Thanks for any help.John

View 2 Replies View Related

Querying SQL Server 2005 Enterprise

Jul 16, 2007

Can I run queries against a SQL DB that is running on SQL Server 2005 Enterprise from the Express edition? I just need to get the info in a table. I have been told I have windows authentication to the DB, but the DB does not show up in my Express' list of networked severs.

Your help is appreciated. I have only worked with MySQL so this is all new to me.

View 5 Replies View Related

Linked Server Problem When Querying

May 29, 2006

I have a linked SQL Server on another machine which is created using a stored procedure that executes the following:

EXEC sp_addlinkedserver @server = 'AchillesMixed', @srvproduct = ' ', @provider = 'SQLNCLI', @datasrc = 'ArchillesMixed', @catalog = 'DB_INTRANET'

The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:

SELECT * FROM DB_INTRANET...Employees

I get the following error:

OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.

I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.

Does anyone have any suggestions please.



View 6 Replies View Related

Querying A Linked Server With Local Database...

Mar 25, 2008

I would like to query a linked server and join that database to my local database. I finally was able to get it to work and I got this query to work:

select * from openquery([hcda-storagesrvlaserfiche], 'select * from toc where parentid=358')

I would like to join one of my tables from my local database to the query. How can I do this? Thanks!

View 5 Replies View Related

SQL Server 2008 :: Querying Last Quarter Data

Feb 26, 2015

We have this query that pulls number of days worked from the current Quarter to Date.

(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],

I need changing it to bring up LAST Quarter's data.

View 1 Replies View Related

Continuously Querying A Datastream Using SQL Server 2005

Nov 28, 2006

I was wondering if it is possible to continuously query a real-timedatastream using SQL Server. Does anyone have any experience of this? Ihave found LINUX based systems such as Borealis and STREAM. I wouldprefer to use a Windows based system as the program using the queryresults is Windows based.

View 4 Replies View Related

Looking For References For Querying Active Directory (AD) Through SQL Server

Sep 11, 2007


Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.



Thanks.

View 1 Replies View Related

C0000005 EXCEPTION_ACCESS_VIOLATION When Querying Linked Server

Apr 2, 2007

I have SQL 2K with SP4. Oracle client is installed on the Server. I have created linked server to Oracle. I am abld to connect to Oracle Server through DTS but when I query an oracle linked server SQl Server crashes with "EXCEPTION_ACCESS_VIOLATION" error.



Info on the Server:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)



I have the SQL dump if you need that. Please help.



Thanks

Sreejith

View 3 Replies View Related

SQL Server Logs

Jan 3, 2001

In Enterprise Manager, Management and then SQL Server Logs, when I clicked on Current or Archive#1 or 2 etc, nothing happens. This has been going on for the past 3 weeks does any one knows what is causing such problems?

View 1 Replies View Related

SQL Server Logs

Nov 9, 2006

Is there any way we can log in the info like this in SQL Server

Who logged in
What date
OS Name
Ipaddress
SQL Login Name

View 2 Replies View Related

SQL Server Logs

Jun 12, 2007

Hi

I'm very novice when it comes to SQL Server Administration.

I have been asked if the SQL Server Log can trace information on whether data supplied to an individual has been tampered with, or if it is the full data set provided.

Apologies for this vague description, but that is what I have to go on also.

I am trying to find an article which details what the purpose of SQL Server logs are and if I can extract this sort of information from them.

Any idea if the SQL server log can provide tracing/editing/exporting information?

Thanks!!

View 6 Replies View Related

Sql Server Logs Help

Mar 4, 2008

We are using sql server 2000. I checked the sql server logs and noticed that everytime a user logs into sql an error log gets created stating, "starting up database 'CORT'". This db has nothing to do with users logging in as far as I can tell. What can check to find out what's causing this error log?

View 3 Replies View Related

Querying A TEXT Datatype Column In SQL Server 2000

Jan 17, 2008

HI,
I'm trying to implement a site search.  The only problem is that some of the pages have more than 8000 characters (like the press release pages) so I had to use the TEXT datatype (sql server 2000).  I wrote a simple stored procedure to query the column, however it does not return any results.  I'm assuming this is due to the fact that it is the TEXT datatype.  ANY help would be GREATLY appreciated.  Code below:
  CREATE PROCEDURE sp_SiteSearch
@keyword text

AS

SELECT PageText
FROM tbl_Pages
WHERE PageText LIKE '%@keyword%'
GO 

View 9 Replies View Related

SQL Server 2000 && Active Directory: Problems Querying

Jul 20, 2005

Hello --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 Related

Error Querying Data From DB2 Linked Server Via IBMDASQL

May 2, 2007

I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.

Error:
Msg 7372, Level 16, State 4, Line 1
Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723
Linked Server Script:
/****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true'
I've tried searching the Internet for solution but yielded no results. Can anyone help?

View 3 Replies View Related

Exporting IIS Server Logs For Use By DTS

Nov 8, 2001

I have a question that I hope someone can help with. I am attempting to load my IIS logs into SQL Server using DTS. I have done this sucessfully by manually specifying the source file. I would like to automate this process but, I have been unable to figure out how to do this. The IIS logs changes daily and the file name changes. How do I configure DTS to grab the log for the previous day?

I believe that the solution is to first copy the log to an alternate location then let DTS do it's thing. How do I programatically accomplish this ?

Thanks
kevin

View 2 Replies View Related

Sql Server Logs, No Views?

Feb 27, 2006

not sure if this is by default or restricted,
but I can't view the sql server logs using ctp

an error is generated saying the tsql could not be retrieved or something, very long message to post here

is this normal?

TIA

--------------------
keeping it simple...

View 5 Replies View Related

SQ: Server Logs Are Blank

Jan 10, 2007

I just installed the SSMS for 2005 Express and cannot view any SQL Server Logs. I see Current and Archive #1,#2 etc but the right pane is blank.

View 7 Replies View Related

Sql Server Transaction Logs

Jul 20, 2005

I recently performed an SQL server 2000 installation. Other thanplacing the program files on C: and data on D:, I saw no option toinstall transaction logs in an alternate location.What is the best practice with SQL server 2000 for location oftransaction logs? I remember that Microsoft used to take the positionthat transaction logs should be placed on their own FAT partition.

View 3 Replies View Related

Cannot View Server Logs

Jun 12, 2007

I have SQL Server 2005 Express on a Windows XP SP2 machine. Everything works fine and I have several dbs running. How ever I was wanting to optimize some programs and needed to watch the server and see what it is processing. I log into SQL Management Studio Express and when I go to Management->Server Logs I see the logs and the Activity Monitor. If I right click on the log all I see is Refresh. If I doule click nothing happens. I can view the Activity Monitor and the processes going.



I am sure it is a permission thing or something along those lines. I can not for the life of me figure it and have spent a couple days on google and these forums. I have tried with the SA, my defined users, and the local windows administrator. Is log viewing not allowed in SQL Server Express?



Thanks!

Michael

View 6 Replies View Related

Query For The Sql Server Logs?

Apr 28, 2008

Hi,

I understand that there is a SQL Server Logs section in Enterprise Manager 2000 where we can view SQL Server activity. Is it possible that we can get those logs by using query? I had been searching over the Internet, system databases, system SP and still have no idea how to achieve it.

Thanks in advance.

Regards,
maxChin

View 6 Replies View Related

SQL Server 2012 :: Querying Table With Several Date Type Columns

Oct 30, 2014

I 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()))

View 2 Replies View Related







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