Reading .trc files on SQL Server 2000/2005

Hi,
We have a requirement to read the .trc files generated by SQL server
in real-time or atleast near real-time. We do not want to interfere
with existing SQL server setup by running functions like
fn_trace_get_table. The requirement is to get the events logged in .trc
files by directly reading them. SQL Profiler can read the trace files,
but looks like the .trc file format is not published by Microsoft. But
I also see some tools like Idera's SQL Compliance Manager are capable
of directly reading the trace files. Can any one suggest an approach to
read trace file without having to disturb the existing SQL server
setup.
Thanks
ad kompellaSQLBenchmarkPro is a tool I designed to organise trace scheduling,
collection & analysis accross multiple server concurrently - for both SQL
2000 and SQL 2005. www.gajsoftware.com
Regards,
Greg Linwood
SQL Server MVP
"adkompella" <adkompella@.gmail.com> wrote in message
news:1156924170.713163.303500@.h48g2000cwc.googlegroups.com...
> Hi,
> We have a requirement to read the .trc files generated by SQL server
> in real-time or atleast near real-time. We do not want to interfere
> with existing SQL server setup by running functions like
> fn_trace_get_table. The requirement is to get the events logged in .trc
> files by directly reading them. SQL Profiler can read the trace files,
> but looks like the .trc file format is not published by Microsoft. But
> I also see some tools like Idera's SQL Compliance Manager are capable
> of directly reading the trace files. Can any one suggest an approach to
> read trace file without having to disturb the existing SQL server
> setup.
> Thanks
> ad kompella
>|||On 30 Aug 2006 00:49:30 -0700, "adkompella" <adkompella@.gmail.com>
wrote:
> We have a requirement to read the .trc files generated by SQL server
>in real-time or atleast near real-time. We do not want to interfere
>with existing SQL server setup by running functions like
>fn_trace_get_table. The requirement is to get the events logged in .trc
>files by directly reading them. SQL Profiler can read the trace files,
>but looks like the .trc file format is not published by Microsoft. But
>I also see some tools like Idera's SQL Compliance Manager are capable
>of directly reading the trace files. Can any one suggest an approach to
>read trace file without having to disturb the existing SQL server
>setup.
If you're in a serious hurry, I believe you can write your own
listener using debug API hooks.
J.|||Thanks for the reply. But I have no idea about the debug API hooks, can
you give me any pointers to the debug API hooks for this particular
requirements?
kompella
JXStern wrote:
> If you're in a serious hurry, I believe you can write your own
> listener using debug API hooks.
> J.|||On 30 Aug 2006 21:45:31 -0700, "adkompella" <adkompella@.gmail.com>
wrote:[vbcol=seagreen]
>Thanks for the reply. But I have no idea about the debug API hooks, can
>you give me any pointers to the debug API hooks for this particular
>requirements?
>kompella
>JXStern wrote:
I'm not current on this, but if you search the online doc and post to
the windows API newsgroups, should be able to figure it out.
Sorry not to have it at hand.
J.

Reading .trc files on SQL Server 2000/2005

Hi,
We have a requirement to read the .trc files generated by SQL server
in real-time or atleast near real-time. We do not want to interfere
with existing SQL server setup by running functions like
fn_trace_get_table. The requirement is to get the events logged in .trc
files by directly reading them. SQL Profiler can read the trace files,
but looks like the .trc file format is not published by Microsoft. But
I also see some tools like Idera's SQL Compliance Manager are capable
of directly reading the trace files. Can any one suggest an approach to
read trace file without having to disturb the existing SQL server
setup.
Thanks
ad kompellaSQLBenchmarkPro is a tool I designed to organise trace scheduling,
collection & analysis accross multiple server concurrently - for both SQL
2000 and SQL 2005. www.gajsoftware.com
Regards,
Greg Linwood
SQL Server MVP
"adkompella" <adkompella@.gmail.com> wrote in message
news:1156924170.713163.303500@.h48g2000cwc.googlegroups.com...
> Hi,
> We have a requirement to read the .trc files generated by SQL server
> in real-time or atleast near real-time. We do not want to interfere
> with existing SQL server setup by running functions like
> fn_trace_get_table. The requirement is to get the events logged in .trc
> files by directly reading them. SQL Profiler can read the trace files,
> but looks like the .trc file format is not published by Microsoft. But
> I also see some tools like Idera's SQL Compliance Manager are capable
> of directly reading the trace files. Can any one suggest an approach to
> read trace file without having to disturb the existing SQL server
> setup.
> Thanks
> ad kompella
>|||On 30 Aug 2006 00:49:30 -0700, "adkompella" <adkompella@.gmail.com>
wrote:
> We have a requirement to read the .trc files generated by SQL server
>in real-time or atleast near real-time. We do not want to interfere
>with existing SQL server setup by running functions like
>fn_trace_get_table. The requirement is to get the events logged in .trc
>files by directly reading them. SQL Profiler can read the trace files,
>but looks like the .trc file format is not published by Microsoft. But
>I also see some tools like Idera's SQL Compliance Manager are capable
>of directly reading the trace files. Can any one suggest an approach to
>read trace file without having to disturb the existing SQL server
>setup.
If you're in a serious hurry, I believe you can write your own
listener using debug API hooks.
J.|||Thanks for the reply. But I have no idea about the debug API hooks, can
you give me any pointers to the debug API hooks for this particular
requirements?
kompella
JXStern wrote:
> >
> If you're in a serious hurry, I believe you can write your own
> listener using debug API hooks.
> J.|||On 30 Aug 2006 21:45:31 -0700, "adkompella" <adkompella@.gmail.com>
wrote:
>Thanks for the reply. But I have no idea about the debug API hooks, can
>you give me any pointers to the debug API hooks for this particular
>requirements?
>kompella
>JXStern wrote:
>> >
>> If you're in a serious hurry, I believe you can write your own
>> listener using debug API hooks.
I'm not current on this, but if you search the online doc and post to
the windows API newsgroups, should be able to figure it out.
Sorry not to have it at hand.
J.

Reading .mdmp files (i.e. SQLDmpr0001.mdmp)

Are there any utilities our there for reading .mdmp files (i.e.
SQLDmpr0001.mdmp), other than just sending them off to Microsoft to read?
Thanks!
MichaelHi
Not as far as I know!
Being able to read them is one thing interpretting them correctly is another!
John
"BATMAN" wrote:
> Are there any utilities our there for reading .mdmp files (i.e.
> SQLDmpr0001.mdmp), other than just sending them off to Microsoft to read?
> Thanks!
> Michael

Read\Write Files in SQL 2000

Hi, I was just wondering if it was possible/ the best way to read and write text files for example in SQL 2000?

Is it only possible to do this using extended stored procedures or do SQL commands exist to perform file IO?

Thanks very much,

Ewen

What is your purpose for this ? Do you intend to write query results to text file ? You can use BCP to do that. Or for importing a text file into a table ? use BULK INSERT

read/write BLOBS in sqlser using asp.net

here i have some problem related to BLOBS in sqlserver
i need to store .bmp or .jpg files in sqlserver2000 and using
asp.net i need to retrive that images and show in a webform.
if user want to insert a new Image into database i need to do
it from frontend using asp.net
just like i need add,update,edit images using asp.net
can any one give me idea how to approch.

thanks in advance..
-SridharHere's a KnowledgeBase article that explains it:HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic .NET. It includes links to equivalent versions of the article for C++ and C#.

Don

read xml file content - sql server 2005

There is a folder which contains several different xml files.

Question

for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?

Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?

Thanks

Hi

Try this

Code Snippet

CREATE PROCEDURE [dbo].[spInsertXMLData]

(

@.strXML varchar(8000)

)

AS

DECLARE @.iDoc int

EXECUTE sp_XML_PrepareDocument @.iDoc OUTPUT, @.strXML

INSERT INTO Test (col1, col2, col3)

(SELECT * FROM OpenXML(@.iDoc, '/parentnode/childnode/,2) WITH

(col1 varchar(100),

col2 varchar(100),

col3 varchar(100)))

EXECUTE sp_XML_RemoveDocument @.iDoc

GO

This sp will accept the contents of an XML file and insert it into a table, all you need to do is to read the contents of the XML file into a string\varchar variable and pass it as a paramater to the sp.

Richard

|||

Hi,

yes, This is what I am doing down the line but first I would like to know how to read all the contents of the xml file as say varchar.

Thanks

|||

Yes you can..

Code Snippet

--SQL Server 2005

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Create table #xmlcontent

(

Xml varchar(max)

)

Insert into #xmlcontent

Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.Path + ''', SINGLE_CLOB) as D')

Select XML From #XMLContent

Code Snippet

--SQL Server 2000

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Set @.Path = 'Type ' + @.Path

Create Table #XMLLines

(

Line Varchar(8000)

)

Insert Into #XMLLines

Exec xp_cmdshell @.Path

Select * from #XMLLines

|||

Hi

This is how I have done it using ActiveX Scripting in a DTS package

Code Snippet

Const DB_CONNECT_STRING = "" ' SQL Connection String
Const XMLPath = "" 'Enter UNC file Path

Function Main()
RunImport
Main = DTSTaskExecResult_Success
End Function

Private Function ReadHTML(strFileName)
Dim filesys
Dim readFile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readFile = filesys.opentextfile(strFileName, 1, False)
ReadHTML = readFile.read(8000)
readFile.Close
Set filesys = Nothing
Set readFile = Nothing
End Function

Private Sub RunImport()
Dim cnn1
Dim strExportFiles, fso, File
Dim FileContents
Set cnn1 = CreateObject("ADODB.Connection")
cnn1.Open DB_CONNECT_STRING
Set fso = CreateObject("Scripting.FileSystemObject")
Set strExportFiles = fso.GetFolder(XMLPath).Files
For Each File In strExportFiles
FileContents = ReadHTML(File)
Dim strSQL
strSQL = "spInsertXMLData @.strXML = " + "'" & FileContents & "'"
cnn1.execute (strSQL)
Next
End Sub

This will loop through a specified directory and read the contents of each file to a varchar and run the sp.

|||

hi,

Using sql 2005

error is :

Cannot bulk load. The file "C:\aud_df.xml" does not exist

The file is indeed in that location.

Thanks

|||

this seems to work on the server but not the local machine.?

Do you know why?

|||

Yes. It will read the data from client only.

Try with Server UNC path.. \\servername\folder\file.xml

|||

Hi

I think this is because it is looking for the file in a differnet location if you use a UNC path it should be fine i.e. \\sql\xml files\xmlfile1.xml

Richard

|||

What do you mean by UNC please?

Please note the files are located on my local machines and I am running the sql query analyser from my local machine too.

Thanks

|||

Hi

You are running sql query analyser from your local machine, but I am assuming SQL server itself is running on the server, therefore when you specify C:\aud_df.xml SQL will look on the servers c:\ drive for the file not your local PC's c:\drive.

So you need to specify the full path including your computername in order for SQL to see the file. You will probably also need to setup a share on your local PC. Alternatively copy the xml files to the server and it should work.

|||Many thanks|||

This sounds like a perfect job for SSIS!!

read xml file content

There is a folder which contains several different xml files.

Question

for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?

Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?

Thanks

Hi

Try this

Code Snippet

CREATE PROCEDURE [dbo].[spInsertXMLData]

(

@.strXML varchar(8000)

)

AS

DECLARE @.iDoc int

EXECUTE sp_XML_PrepareDocument @.iDoc OUTPUT, @.strXML

INSERT INTO Test (col1, col2, col3)

(SELECT * FROM OpenXML(@.iDoc, '/parentnode/childnode/,2) WITH

(col1 varchar(100),

col2 varchar(100),

col3 varchar(100)))

EXECUTE sp_XML_RemoveDocument @.iDoc

GO

This sp will accept the contents of an XML file and insert it into a table, all you need to do is to read the contents of the XML file into a string\varchar variable and pass it as a paramater to the sp.

Richard

|||

Hi,

yes, This is what I am doing down the line but first I would like to know how to read all the contents of the xml file as say varchar.

Thanks

|||

Yes you can..

Code Snippet

--SQL Server 2005

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Create table #xmlcontent

(

Xml varchar(max)

)

Insert into #xmlcontent

Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.Path + ''', SINGLE_CLOB) as D')

Select XML From #XMLContent

Code Snippet

--SQL Server 2000

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Set @.Path = 'Type ' + @.Path

Create Table #XMLLines

(

Line Varchar(8000)

)

Insert Into #XMLLines

Exec xp_cmdshell @.Path

Select * from #XMLLines

|||

Hi

This is how I have done it using ActiveX Scripting in a DTS package

Code Snippet

Const DB_CONNECT_STRING = "" ' SQL Connection String
Const XMLPath = "" 'Enter UNC file Path

Function Main()
RunImport
Main = DTSTaskExecResult_Success
End Function

Private Function ReadHTML(strFileName)
Dim filesys
Dim readFile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readFile = filesys.opentextfile(strFileName, 1, False)
ReadHTML = readFile.read(8000)
readFile.Close
Set filesys = Nothing
Set readFile = Nothing
End Function

Private Sub RunImport()
Dim cnn1
Dim strExportFiles, fso, File
Dim FileContents
Set cnn1 = CreateObject("ADODB.Connection")
cnn1.Open DB_CONNECT_STRING
Set fso = CreateObject("Scripting.FileSystemObject")
Set strExportFiles = fso.GetFolder(XMLPath).Files
For Each File In strExportFiles
FileContents = ReadHTML(File)
Dim strSQL
strSQL = "spInsertXMLData @.strXML = " + "'" & FileContents & "'"
cnn1.execute (strSQL)
Next
End Sub

This will loop through a specified directory and read the contents of each file to a varchar and run the sp.

|||

hi,

Using sql 2005

error is :

Cannot bulk load. The file "C:\aud_df.xml" does not exist

The file is indeed in that location.

Thanks

|||

this seems to work on the server but not the local machine.?

Do you know why?

|||

Yes. It will read the data from client only.

Try with Server UNC path.. \\servername\folder\file.xml

|||

Hi

I think this is because it is looking for the file in a differnet location if you use a UNC path it should be fine i.e. \\sql\xml files\xmlfile1.xml

Richard

|||

What do you mean by UNC please?

Please note the files are located on my local machines and I am running the sql query analyser from my local machine too.

Thanks

|||

Hi

You are running sql query analyser from your local machine, but I am assuming SQL server itself is running on the server, therefore when you specify C:\aud_df.xml SQL will look on the servers c:\ drive for the file not your local PC's c:\drive.

So you need to specify the full path including your computername in order for SQL to see the file. You will probably also need to setup a share on your local PC. Alternatively copy the xml files to the server and it should work.

|||Many thanks|||

This sounds like a perfect job for SSIS!!

Read Write Variable Cannot access before PostExecute

I have a for each loop on a directory of files, each file has to be imported with a unique surrogate key added. For this I am selecting the max id that exists in the target table and assigning that value to a variable.

Within a script transformation I am copying this value to a variable declared in a script task, and incrementing it on each row processed. Obviously I now want to write this value back out to the higher scoped variable so it is available for the next file.

If I make the variable read write it is not even available for reading until the PostExecute. Is this correct or have I missed something?

To work round this I have created a second higher scoped variable that I can write to in the PostExecute and the other variable is passed in as read only and added another script task to update the variable values.Philip is a colleague of mine and we've just been taking a look at this.

The workaround is to use a multiflatfile adapter because the metadata of the files is identical.

Philip's requirement to be able to read a ReadWriteVariable in PreExecute() is, I feel, a valid one. Is there a reason that this cannot be done?

-Jamie|||You can actually access write-able variables anywhere you want just not the ones on the read/write list. The component has a VariableDispenser that you can lock variables for read and/or write and use them as you will. The limitation we place is only for the ones you specify in the ReadWriteVariables line and this was done to keep locking to a minimum. If we gave access during row processing then because we don't know the usage we would need to keep the variable locked during the entire ProcessInput call. If some other transform needed this variable as well then we have concurrency issues. This way the user can lock variables for write but has to do it explicitly so that it can be unlocked explicitly as well and hopefully the locking time can be kept to a minimum since the script author is controlling the locking.

HTH,
Matt

read trn log backup files

What product is out there that can read a Transaction Backup file?
A restore was done but they didn't apply a couple of transaction logs. I
need to see what data is in these backup logs. If there is a product that
can do this can it apply the data from the logs or dump it to a file?
Thanks.
I've listed some on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message news:OS70VXD3HHA.212@.TK2MSFTNGP05.phx.gbl...
> What product is out there that can read a Transaction Backup file?
> A restore was done but they didn't apply a couple of transaction logs. I
> need to see what data is in these backup logs. If there is a product that
> can do this can it apply the data from the logs or dump it to a file?
>
> Thanks.
>

read trn log backup files

What product is out there that can read a Transaction Backup file?
A restore was done but they didn't apply a couple of transaction logs. I
need to see what data is in these backup logs. If there is a product that
can do this can it apply the data from the logs or dump it to a file?
Thanks.I've listed some on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message news:OS70VXD3HHA.212@.TK2MSFTNGP05.phx.g
bl...
> What product is out there that can read a Transaction Backup file?
> A restore was done but they didn't apply a couple of transaction logs. I
> need to see what data is in these backup logs. If there is a product that
> can do this can it apply the data from the logs or dump it to a file?
>
> Thanks.
>

read trn log backup files

What product is out there that can read a Transaction Backup file?
A restore was done but they didn't apply a couple of transaction logs. I
need to see what data is in these backup logs. If there is a product that
can do this can it apply the data from the logs or dump it to a file?
Thanks.I've listed some on my links page:
http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message news:OS70VXD3HHA.212@.TK2MSFTNGP05.phx.gbl...
> What product is out there that can read a Transaction Backup file?
> A restore was done but they didn't apply a couple of transaction logs. I
> need to see what data is in these backup logs. If there is a product that
> can do this can it apply the data from the logs or dump it to a file?
>
> Thanks.
>sql

read sql log files

hi,
There is anyway or tools that I could read sql log backup(.bak)files?
ThnakaI hopfully understand your question correct. I assume you want to see
history of your backup jobs and you use SQL Server 2005. If not, please let
me know.
Fire up SQL Server Management Studio.
Connect to your instance, expand SQL Server Agent and then expand Jobs.
Right click on the backup job and click View History.
To see about your jobs' last events, go to SQL Server Agent node in SSMS and
double click on the Job Activity Monitor.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23yg0ufW8HHA.5456@.TK2MSFTNGP05.phx.gbl...
> hi,
> There is anyway or tools that I could read sql log backup(.bak)files?
> Thnaka
>|||mecn,
I believe that you are asking to read the transaction logs, is that correct?
SQL Server itself does not provide a facility, but others sell software:
http://www.apexsql.com/sql_tools_log.asp
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm - For SQL Server
2000, Free.
http://www.lumigent.com/Products/le_sql.html
Check it out.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23yg0ufW8HHA.5456@.TK2MSFTNGP05.phx.gbl...
> hi,
> There is anyway or tools that I could read sql log backup(.bak)files?
> Thnaka
>|||In article <#yg0ufW8HHA.5456@.TK2MSFTNGP05.phx.gbl>, mecn2002@.yahoo.com
says...
> hi,
> There is anyway or tools that I could read sql log backup(.bak)files?
> Thnaka
>
>
Lumigent Log Reader is excellent if a bit pricey. Also adds several
capabilities to your recovery processing.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edusql

Read Only MDF LDF files

I have a couple of SQL database files, MDF and LDF, that were distributed on
CD media (they came w/ a Microsoft Press book).
I can attach the database, but it is read only. However, if I try to set
the DB to not read-only, I get an error:
Error 5105: Device activation error. They physical file name "C:\program
files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
incorrect.
Could not restart database "contactsdb". Reverting to old status.
ALTER DATABASE statement failed
sp_dboption command failed.
If I set the files to NOT read-only (they were read only since they were
copied from a CD), the DB gets marked suspect and/or I get the error 9003:
The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
How am I supposed to attach this dataabase in a write-enabled mode?
--
Thanks,
Martin SchmidIt sounds like the files might be bad, and I don't think just marking them
readable in the file system would do that. You could try just copying the
MDF file to the local directory (leaving the LDF on the CD), changing the
file's readonly attribute, and then try attaching the mdf only (maybe with
sp_attach_single_file_db. It might be able to build a new log for you.
Good Luck
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> I have a couple of SQL database files, MDF and LDF, that were distributed
on
> CD media (they came w/ a Microsoft Press book).
> I can attach the database, but it is read only. However, if I try to set
> the DB to not read-only, I get an error:
> Error 5105: Device activation error. They physical file name "C:\program
> files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> incorrect.
> Could not restart database "contactsdb". Reverting to old status.
> ALTER DATABASE statement failed
> sp_dboption command failed.
> If I set the files to NOT read-only (they were read only since they were
> copied from a CD), the DB gets marked suspect and/or I get the error 9003:
> The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> How am I supposed to attach this dataabase in a write-enabled mode?
> --
> Thanks,
> Martin Schmid
>|||I DON'T want the DB read only... the file is marked read only because it was
copied from READ ONLY media.
--
Thanks,
Martin Schmid, EIT, CCSA, MCDBA, MCSE
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:euGR0OCeDHA.2436@.TK2MSFTNGP11.phx.gbl...
> Hi Martin !
> Even you want the database to be read-only. you cannot achieve it by
setting
> the attribute to read-only, because during normal database checkpoints are
> written to the LOG-Files and the database. What you can do is to copy the
DB
> set the attribute to writeable and set the database (logically) to
readonly,
> so that only records can be read.
> HTH, Jens Süßmeyer.
>
> "Martin Schmid" <martinschmid@.sbcglobal.net.nospam> schrieb im Newsbeitrag
> news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > I have a couple of SQL database files, MDF and LDF, that were
distributed
> on
> > CD media (they came w/ a Microsoft Press book).
> >
> > I can attach the database, but it is read only. However, if I try to
set
> > the DB to not read-only, I get an error:
> > Error 5105: Device activation error. They physical file name
"C:\program
> > files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> > incorrect.
> > Could not restart database "contactsdb". Reverting to old status.
> > ALTER DATABASE statement failed
> > sp_dboption command failed.
> >
> > If I set the files to NOT read-only (they were read only since they were
> > copied from a CD), the DB gets marked suspect and/or I get the error
9003:
> > The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> >
> > How am I supposed to attach this dataabase in a write-enabled mode?
> >
> > --
> > Thanks,
> > Martin Schmid
> >
> >
>|||Perfect! Thanks.
--
Thanks,
Martin Schmid, EIT, CCSA, MCDBA, MCSE
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OgkrngBeDHA.1828@.TK2MSFTNGP10.phx.gbl...
> It sounds like the files might be bad, and I don't think just marking them
> readable in the file system would do that. You could try just copying the
> MDF file to the local directory (leaving the LDF on the CD), changing the
> file's readonly attribute, and then try attaching the mdf only (maybe with
> sp_attach_single_file_db. It might be able to build a new log for you.
> Good Luck
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
> news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > I have a couple of SQL database files, MDF and LDF, that were
distributed
> on
> > CD media (they came w/ a Microsoft Press book).
> >
> > I can attach the database, but it is read only. However, if I try to
set
> > the DB to not read-only, I get an error:
> > Error 5105: Device activation error. They physical file name
"C:\program
> > files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> > incorrect.
> > Could not restart database "contactsdb". Reverting to old status.
> > ALTER DATABASE statement failed
> > sp_dboption command failed.
> >
> > If I set the files to NOT read-only (they were read only since they were
> > copied from a CD), the DB gets marked suspect and/or I get the error
9003:
> > The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> >
> > How am I supposed to attach this dataabase in a write-enabled mode?
> >
> > --
> > Thanks,
> > Martin Schmid
> >
> >
>

Read log files

hi,
how do i read the transaction log file. Actualy i've got a database given my some 1 else, we do not have the db diagrams and no relationship between the tables and no source code, can we find out that when the front end application updates the tables, wh
ich are the table where entries r made? I wanted to use that data futher?...
Check out Lumigent's "Log Explorer" product - it's built for performing log
analysis etc.
www.lumigent.com
Regards,
Greg Linwood
SQL Server MVP
"sim sim" <sim sim@.discussions.microsoft.com> wrote in message
news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> hi,
> how do i read the transaction log file. Actualy i've got a database given
my some 1 else, we do not have the db diagrams and no relationship between
the tables and no source code, can we find out that when the front end
application updates the tables, which are the table where entries r made? I
wanted to use that data futher?...
>
|||hi Greg,
thanks 4 the reply, but this site give a 3rd party software 2 do the same... can't we read the log file without this. or is there any way by which i can find which tables are getting updated by recent transactions.
regards,
"Greg Linwood" wrote:

> Check out Lumigent's "Log Explorer" product - it's built for performing log
> analysis etc.
> www.lumigent.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "sim sim" <sim sim@.discussions.microsoft.com> wrote in message
> news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> my some 1 else, we do not have the db diagrams and no relationship between
> the tables and no source code, can we find out that when the front end
> application updates the tables, which are the table where entries r made? I
> wanted to use that data futher?...
>
>
|||You might consider running a Profiler trace to track application activity.
This can be saved to a table for subsequent analysis.
Hope this helps.
Dan Guzman
SQL Server MVP
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:266C54C8-505E-46BF-8CFB-4C55C6838D40@.microsoft.com...
> hi Greg,
> thanks 4 the reply, but this site give a 3rd party software 2 do the
same... can't we read the log file without this. or is there any way by
which i can find which tables are getting updated by recent transactions.[vbcol=seagreen]
> regards,
>
> "Greg Linwood" wrote:
log[vbcol=seagreen]
given[vbcol=seagreen]
between[vbcol=seagreen]
made? I[vbcol=seagreen]
|||Hi Dan
Thanks a lot u'r suggestion was of great help. Can u suggest some site where i can get good help on making use of the SQL Profiler.
Regards
"Dan Guzman" wrote:

> You might consider running a Profiler trace to track application activity.
> This can be saved to a table for subsequent analysis.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sim sim" <simsim@.discussions.microsoft.com> wrote in message
> news:266C54C8-505E-46BF-8CFB-4C55C6838D40@.microsoft.com...
> same... can't we read the log file without this. or is there any way by
> which i can find which tables are getting updated by recent transactions.
> log
> given
> between
> made? I
>
>
|||I suggest you start with the Books Online
<adminsql.chm::/ad_mon_perf_88c3.htm>. You can save the results to a table
for ad-hoc analysis. The events of interest here are the 'completed' events
for batches and RPCs. You can also add sp:statement completed if needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:9BF8F3EE-D25B-4089-9E4D-9B34CB99BF69@.microsoft.com...
> Hi Dan
> Thanks a lot u'r suggestion was of great help. Can u suggest some site
where i can get good help on making use of the SQL Profiler.[vbcol=seagreen]
> Regards
>
> "Dan Guzman" wrote:
activity.[vbcol=seagreen]
the[vbcol=seagreen]
transactions.[vbcol=seagreen]
performing[vbcol=seagreen]
database[vbcol=seagreen]
end[vbcol=seagreen]

Read log files

hi,
how do i read the transaction log file. Actualy i've got a database given my some 1 else, we do not have the db diagrams and no relationship between the tables and no source code, can we find out that when the front end application updates the tables, which are the table where entries r made? I wanted to use that data futher?...Check out Lumigent's "Log Explorer" product - it's built for performing log
analysis etc.
www.lumigent.com
Regards,
Greg Linwood
SQL Server MVP
"sim sim" <sim sim@.discussions.microsoft.com> wrote in message
news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> hi,
> how do i read the transaction log file. Actualy i've got a database given
my some 1 else, we do not have the db diagrams and no relationship between
the tables and no source code, can we find out that when the front end
application updates the tables, which are the table where entries r made? I
wanted to use that data futher?...
>|||hi Greg,
thanks 4 the reply, but this site give a 3rd party software 2 do the same... can't we read the log file without this. or is there any way by which i can find which tables are getting updated by recent transactions.
regards,
"Greg Linwood" wrote:
> Check out Lumigent's "Log Explorer" product - it's built for performing log
> analysis etc.
> www.lumigent.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "sim sim" <sim sim@.discussions.microsoft.com> wrote in message
> news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> > hi,
> >
> > how do i read the transaction log file. Actualy i've got a database given
> my some 1 else, we do not have the db diagrams and no relationship between
> the tables and no source code, can we find out that when the front end
> application updates the tables, which are the table where entries r made? I
> wanted to use that data futher?...
> >
> >
>
>|||You might consider running a Profiler trace to track application activity.
This can be saved to a table for subsequent analysis.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:266C54C8-505E-46BF-8CFB-4C55C6838D40@.microsoft.com...
> hi Greg,
> thanks 4 the reply, but this site give a 3rd party software 2 do the
same... can't we read the log file without this. or is there any way by
which i can find which tables are getting updated by recent transactions.
> regards,
>
> "Greg Linwood" wrote:
> > Check out Lumigent's "Log Explorer" product - it's built for performing
log
> > analysis etc.
> >
> > www.lumigent.com
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "sim sim" <sim sim@.discussions.microsoft.com> wrote in message
> > news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> > > hi,
> > >
> > > how do i read the transaction log file. Actualy i've got a database
given
> > my some 1 else, we do not have the db diagrams and no relationship
between
> > the tables and no source code, can we find out that when the front end
> > application updates the tables, which are the table where entries r
made? I
> > wanted to use that data futher?...
> > >
> > >
> >
> >
> >|||Hi Dan
Thanks a lot u'r suggestion was of great help. Can u suggest some site where i can get good help on making use of the SQL Profiler.
Regards
"Dan Guzman" wrote:
> You might consider running a Profiler trace to track application activity.
> This can be saved to a table for subsequent analysis.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sim sim" <simsim@.discussions.microsoft.com> wrote in message
> news:266C54C8-505E-46BF-8CFB-4C55C6838D40@.microsoft.com...
> > hi Greg,
> >
> > thanks 4 the reply, but this site give a 3rd party software 2 do the
> same... can't we read the log file without this. or is there any way by
> which i can find which tables are getting updated by recent transactions.
> >
> > regards,
> >
> >
> > "Greg Linwood" wrote:
> >
> > > Check out Lumigent's "Log Explorer" product - it's built for performing
> log
> > > analysis etc.
> > >
> > > www.lumigent.com
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "sim sim" <sim sim@.discussions.microsoft.com> wrote in message
> > > news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> > > > hi,
> > > >
> > > > how do i read the transaction log file. Actualy i've got a database
> given
> > > my some 1 else, we do not have the db diagrams and no relationship
> between
> > > the tables and no source code, can we find out that when the front end
> > > application updates the tables, which are the table where entries r
> made? I
> > > wanted to use that data futher?...
> > > >
> > > >
> > >
> > >
> > >
>
>|||I suggest you start with the Books Online
<adminsql.chm::/ad_mon_perf_88c3.htm>. You can save the results to a table
for ad-hoc analysis. The events of interest here are the 'completed' events
for batches and RPCs. You can also add sp:statement completed if needed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:9BF8F3EE-D25B-4089-9E4D-9B34CB99BF69@.microsoft.com...
> Hi Dan
> Thanks a lot u'r suggestion was of great help. Can u suggest some site
where i can get good help on making use of the SQL Profiler.
> Regards
>
> "Dan Guzman" wrote:
> > You might consider running a Profiler trace to track application
activity.
> > This can be saved to a table for subsequent analysis.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "sim sim" <simsim@.discussions.microsoft.com> wrote in message
> > news:266C54C8-505E-46BF-8CFB-4C55C6838D40@.microsoft.com...
> > > hi Greg,
> > >
> > > thanks 4 the reply, but this site give a 3rd party software 2 do
the
> > same... can't we read the log file without this. or is there any way by
> > which i can find which tables are getting updated by recent
transactions.
> > >
> > > regards,
> > >
> > >
> > > "Greg Linwood" wrote:
> > >
> > > > Check out Lumigent's "Log Explorer" product - it's built for
performing
> > log
> > > > analysis etc.
> > > >
> > > > www.lumigent.com
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "sim sim" <sim sim@.discussions.microsoft.com> wrote in message
> > > > news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> > > > > hi,
> > > > >
> > > > > how do i read the transaction log file. Actualy i've got a
database
> > given
> > > > my some 1 else, we do not have the db diagrams and no relationship
> > between
> > > > the tables and no source code, can we find out that when the front
end
> > > > application updates the tables, which are the table where entries r
> > made? I
> > > > wanted to use that data futher?...
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Read log files

hi,
how do i read the transaction log file. Actualy i've got a database given my
some 1 else, we do not have the db diagrams and no relationship between the
tables and no source code, can we find out that when the front end applicat
ion updates the tables, wh
ich are the table where entries r made? I wanted to use that data futher?...Check out Lumigent's "Log Explorer" product - it's built for performing log
analysis etc.
www.lumigent.com
Regards,
Greg Linwood
SQL Server MVP
"sim sim" <sim sim@.discussions.microsoft.com> wrote in message
news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> hi,
> how do i read the transaction log file. Actualy i've got a database given
my some 1 else, we do not have the db diagrams and no relationship between
the tables and no source code, can we find out that when the front end
application updates the tables, which are the table where entries r made? I
wanted to use that data futher?...
>|||hi Greg,
thanks 4 the reply, but this site give a 3rd party software 2 do the same...
can't we read the log file without this. or is there any way by which i can
find which tables are getting updated by recent transactions.
regards,
"Greg Linwood" wrote:

> Check out Lumigent's "Log Explorer" product - it's built for performing lo
g
> analysis etc.
> www.lumigent.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "sim sim" <sim sim@.discussions.microsoft.com> wrote in message
> news:73057BCA-FDEB-4D07-B5DA-40867DE76678@.microsoft.com...
> my some 1 else, we do not have the db diagrams and no relationship between
> the tables and no source code, can we find out that when the front end
> application updates the tables, which are the table where entries r made?
I
> wanted to use that data futher?...
>
>|||You might consider running a Profiler trace to track application activity.
This can be saved to a table for subsequent analysis.
Hope this helps.
Dan Guzman
SQL Server MVP
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:266C54C8-505E-46BF-8CFB-4C55C6838D40@.microsoft.com...
> hi Greg,
> thanks 4 the reply, but this site give a 3rd party software 2 do the
same... can't we read the log file without this. or is there any way by
which i can find which tables are getting updated by recent transactions.[vbcol=seagreen]
> regards,
>
> "Greg Linwood" wrote:
>
log[vbcol=seagreen]
given[vbcol=seagreen]
between[vbcol=seagreen]
made? I[vbcol=seagreen]|||Hi Dan
Thanks a lot u'r suggestion was of great help. Can u suggest some site where
i can get good help on making use of the SQL Profiler.
Regards
"Dan Guzman" wrote:

> You might consider running a Profiler trace to track application activity.
> This can be saved to a table for subsequent analysis.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sim sim" <simsim@.discussions.microsoft.com> wrote in message
> news:266C54C8-505E-46BF-8CFB-4C55C6838D40@.microsoft.com...
> same... can't we read the log file without this. or is there any way by
> which i can find which tables are getting updated by recent transactions.
> log
> given
> between
> made? I
>
>|||I suggest you start with the Books Online
<adminsql.chm::/ad_mon_perf_88c3.htm>. You can save the results to a table
for ad-hoc analysis. The events of interest here are the 'completed' events
for batches and RPCs. You can also add sp:statement completed if needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:9BF8F3EE-D25B-4089-9E4D-9B34CB99BF69@.microsoft.com...
> Hi Dan
> Thanks a lot u'r suggestion was of great help. Can u suggest some site
where i can get good help on making use of the SQL Profiler.[vbcol=seagreen]
> Regards
>
> "Dan Guzman" wrote:
>
activity.[vbcol=seagreen]
the[vbcol=seagreen]
transactions.[vbcol=seagreen]
performing[vbcol=seagreen]
database[vbcol=seagreen]
end[vbcol=seagreen]sql

READ LDF FILES

I would like to know is it possible and how to read LOG files with SQL-DMO?

My clients would like to know who is doing what and when. Actually I would like to trace table activites these are DELETES, UPDATES & INSERTS only. Also I know that it is possible to do it with triggers & jobs by creating new tables but imagine you have a database with size greater then 10 GB. It wouldn't be a good solution at all.

Thanks in advance,
SQL-DMO does not have that capability.

However, SMO has a namespace, which allows you do to live capture of trace data. See the Microsoft.SqlServer.Management.Trace namespace in Books Online.

However that is not reading LDF files, but starting a trace. If you want to read log files there are various 3rd party product out there that offer that capability.

read from backup files

hi,
is there any SP or Tsql statment can read the logical
files name from backup files.
I know i can use RESTORE FILELISTONLY but i need to access
the files name and use them on a script.
regards,
AymanFrom what script do you want to access this information? Whatever you use,
you will have to make a connection to SQL Server, to run RESTORE
FILELISTONLY command.--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ayman" <ayman.massri@.ihorizons.com> wrote in message
news:a1cf01c3ebfe$cb13d140$a101280a@.phx.gbl...
hi,
is there any SP or Tsql statment can read the logical
files name from backup files.
I know i can use RESTORE FILELISTONLY but i need to access
the files name and use them on a script.
regards,
Ayman|||hi,
I WANT TO CREATE A SCRIPT THAT RESTORE MY DB ONCE I HAVE A FAILURE
ACCOURDING TO MY BACKUP POLICY INSTEAD OF DOING THAT MANUALLY.
I need other way rather than RESTORE FILELISTONLY command.
regards,
Ayman
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||AFAIK, there are no other programs or commands that will read the header
info for a SQL Server backup. What is it that you don't like with RESTORE
HEADERONLY?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"ayman masri" <ayman.massri@.ihorizons.com> wrote in message
news:ugsGAii7DHA.3360@.tk2msftngp13.phx.gbl...
> hi,
> I WANT TO CREATE A SCRIPT THAT RESTORE MY DB ONCE I HAVE A FAILURE
> ACCOURDING TO MY BACKUP POLICY INSTEAD OF DOING THAT MANUALLY.
> I need other way rather than RESTORE FILELISTONLY command.
>
> regards,
> Ayman
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

read from backup files

hi,
is there any SP or Tsql statment can read the logical
files name from backup files.
I know i can use RESTORE FILELISTONLY but i need to access
the files name and use them on a script.
regards,
AymanFrom what script do you want to access this information? Whatever you use,
you will have to make a connection to SQL Server, to run RESTORE
FILELISTONLY command.--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ayman" <ayman.massri@.ihorizons.com> wrote in message
news:a1cf01c3ebfe$cb13d140$a101280a@.phx.gbl...
hi,
is there any SP or Tsql statment can read the logical
files name from backup files.
I know i can use RESTORE FILELISTONLY but i need to access
the files name and use them on a script.
regards,
Ayman

Read all xml files from a folder

I have a script that can read specific files that are in an xml format, but i
need to modifiy it to read all files from a folder. In this case, I do not
know the file names but I do know that I need to read all of them. Does
anyone have a VB activeX script for this for MS SQL server 2000?
Thanks
You would use the Scripting.FileSystemObject to iterate
through the files in folder:
http://www.eggheadcafe.com/articles/20030627b.asp
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:F70A712A-6D8C-4C9B-BE36-4EBA01B27A94@.microsoft.com...
>I have a script that can read specific files that are in an xml format, but
>i
> need to modifiy it to read all files from a folder. In this case, I do
> not
> know the file names but I do know that I need to read all of them. Does
> anyone have a VB activeX script for this for MS SQL server 2000?
> Thanks
|||Thanks Robbe. Your post/page/article worked like a charm!!
Ben
"Robbe Morris [C# MVP]" wrote:

> You would use the Scripting.FileSystemObject to iterate
> through the files in folder:
> http://www.eggheadcafe.com/articles/20030627b.asp
> --
> 2004 and 2005 Microsoft MVP C#
> Robbe Morris
> http://www.masterado.net
> Earn $$$ money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:F70A712A-6D8C-4C9B-BE36-4EBA01B27A94@.microsoft.com...
>
>
sql