Data Lost

Excuse me if my redaction is bad, but I don't speak english very well.
My Question is:
I have a SQL DataBase in which every day I save a lot of records.
I'm sured that the records are in the Database because I can see them.
I made queries in DB using a Windows application but next day I query again
and I can't see the records.
There are no proccess like stored procedures that delete the records; even
the application doesn't have an option to delete them.
First time, I thougt the reason was the DataBase Server wasn't shut down
correctly. But this time Server wasn't shut down.
I really need help because it's very stranger. can somebody help me to find
the phantom?
Thanks!!!
Thats an odd one.
What I would sugest is you run a trace on your database to see who or what
is removing the data.
The thing is it could be justabout anything or anyone.
Something you could do in the meanwhile is to denign delete permission on
the table execpt for DBA's
Peter
"Edward" wrote:

> Excuse me if my redaction is bad, but I don't speak english very well.
> My Question is:
> I have a SQL DataBase in which every day I save a lot of records.
> I'm sured that the records are in the Database because I can see them.
> I made queries in DB using a Windows application but next day I query again
> and I can't see the records.
> There are no proccess like stored procedures that delete the records; even
> the application doesn't have an option to delete them.
> First time, I thougt the reason was the DataBase Server wasn't shut down
> correctly. But this time Server wasn't shut down.
> I really need help because it's very stranger. can somebody help me to find
> the phantom?
> Thanks!!!
|||I will do it, but I'm sure that nothing is deleting the records, this is a
thecnical problem.
Thanks
"Peter Nolan" wrote:
[vbcol=seagreen]
> Thats an odd one.
> What I would sugest is you run a trace on your database to see who or what
> is removing the data.
> The thing is it could be justabout anything or anyone.
> Something you could do in the meanwhile is to denign delete permission on
> the table execpt for DBA's
> Peter
> "Edward" wrote:
|||Hi Edward
Are you inserting the records into a temporary table by any chance?
Can you please send the table structure and the query that you are using to
view the data. Also please check in the database if data is present
thanks and regards
Chandra
"Edward" wrote:

> Excuse me if my redaction is bad, but I don't speak english very well.
> My Question is:
> I have a SQL DataBase in which every day I save a lot of records.
> I'm sured that the records are in the Database because I can see them.
> I made queries in DB using a Windows application but next day I query again
> and I can't see the records.
> There are no proccess like stored procedures that delete the records; even
> the application doesn't have an option to delete them.
> First time, I thougt the reason was the DataBase Server wasn't shut down
> correctly. But this time Server wasn't shut down.
> I really need help because it's very stranger. can somebody help me to find
> the phantom?
> Thanks!!!
|||Hi chandra!!!
If I send you the inserts and updates made in the tables I'll send many
statements, because they are many tables involved in a transaction that is
handled by the same stored procedure, I think that the problem is cause at
transaction level, but I do not understand either, because if the problem
were of this type, the rest of clients applications that works with the
database would be locked.
another thing is happened to me ? The backup log that is made over the
database
every day may cause that the last transactions commited will be losted?
"Chandra" wrote:

> Hi Edward
> Are you inserting the records into a temporary table by any chance?
> Can you please send the table structure and the query that you are using to
> view the data. Also please check in the database if data is present
> thanks and regards
> Chandra

Data Lost

Excuse me if my redaction is bad, but I don't speak english very well.
My Question is:
I have a SQL DataBase in which every day I save a lot of records.
I'm sured that the records are in the Database because I can see them.
I made queries in DB using a Windows application but next day I query again
and I can't see the records.
There are no proccess like stored procedures that delete the records; even
the application doesn't have an option to delete them.
First time, I thougt the reason was the DataBase Server wasn't shut down
correctly. But this time Server wasn't shut down.
I really need help because it's very stranger. can somebody help me to find
the phantom?
Thanks!!!Thats an odd one.
What I would sugest is you run a trace on your database to see who or what
is removing the data.
The thing is it could be justabout anything or anyone.
Something you could do in the meanwhile is to denign delete permission on
the table execpt for DBA's
Peter
"Edward" wrote:

> Excuse me if my redaction is bad, but I don't speak english very well.
> My Question is:
> I have a SQL DataBase in which every day I save a lot of records.
> I'm sured that the records are in the Database because I can see them.
> I made queries in DB using a Windows application but next day I query agai
n
> and I can't see the records.
> There are no proccess like stored procedures that delete the records; even
> the application doesn't have an option to delete them.
> First time, I thougt the reason was the DataBase Server wasn't shut down
> correctly. But this time Server wasn't shut down.
> I really need help because it's very stranger. can somebody help me to fin
d
> the phantom?
> Thanks!!!|||I will do it, but I'm sure that nothing is deleting the records, this is a
thecnical problem.
Thanks
"Peter Nolan" wrote:
[vbcol=seagreen]
> Thats an odd one.
> What I would sugest is you run a trace on your database to see who or what
> is removing the data.
> The thing is it could be justabout anything or anyone.
> Something you could do in the meanwhile is to denign delete permission on
> the table execpt for DBA's
> Peter
> "Edward" wrote:
>|||Hi Edward
Are you inserting the records into a temporary table by any chance?
Can you please send the table structure and the query that you are using to
view the data. Also please check in the database if data is present
thanks and regards
Chandra
"Edward" wrote:

> Excuse me if my redaction is bad, but I don't speak english very well.
> My Question is:
> I have a SQL DataBase in which every day I save a lot of records.
> I'm sured that the records are in the Database because I can see them.
> I made queries in DB using a Windows application but next day I query agai
n
> and I can't see the records.
> There are no proccess like stored procedures that delete the records; even
> the application doesn't have an option to delete them.
> First time, I thougt the reason was the DataBase Server wasn't shut down
> correctly. But this time Server wasn't shut down.
> I really need help because it's very stranger. can somebody help me to fin
d
> the phantom?
> Thanks!!!|||Hi chandra!!!
If I send you the inserts and updates made in the tables I'll send many
statements, because they are many tables involved in a transaction that is
handled by the same stored procedure, I think that the problem is cause at
transaction level, but I do not understand either, because if the problem
were of this type, the rest of clients applications that works with the
database would be locked.
another thing is happened to me ? The backup log that is made over the
database
every day may cause that the last transactions commited will be losted?
"Chandra" wrote:

> Hi Edward
> Are you inserting the records into a temporary table by any chance?
> Can you please send the table structure and the query that you are using t
o
> view the data. Also please check in the database if data is present
> thanks and regards
> Chandra

Data Lost

Excuse me if my redaction is bad, but I don't speak english very well.
My Question is:
I have a SQL DataBase in which every day I save a lot of records.
I'm sured that the records are in the Database because I can see them.
I made queries in DB using a Windows application but next day I query again
and I can't see the records.
There are no proccess like stored procedures that delete the records; even
the application doesn't have an option to delete them.
First time, I thougt the reason was the DataBase Server wasn't shut down
correctly. But this time Server wasn't shut down.
I really need help because it's very stranger. can somebody help me to find
the phantom?
Thanks!!!Thats an odd one.
What I would sugest is you run a trace on your database to see who or what
is removing the data.
The thing is it could be justabout anything or anyone.
Something you could do in the meanwhile is to denign delete permission on
the table execpt for DBA's
Peter
"Edward" wrote:
> Excuse me if my redaction is bad, but I don't speak english very well.
> My Question is:
> I have a SQL DataBase in which every day I save a lot of records.
> I'm sured that the records are in the Database because I can see them.
> I made queries in DB using a Windows application but next day I query again
> and I can't see the records.
> There are no proccess like stored procedures that delete the records; even
> the application doesn't have an option to delete them.
> First time, I thougt the reason was the DataBase Server wasn't shut down
> correctly. But this time Server wasn't shut down.
> I really need help because it's very stranger. can somebody help me to find
> the phantom?
> Thanks!!!|||I will do it, but I'm sure that nothing is deleting the records, this is a
thecnical problem.
Thanks
"Peter Nolan" wrote:
> Thats an odd one.
> What I would sugest is you run a trace on your database to see who or what
> is removing the data.
> The thing is it could be justabout anything or anyone.
> Something you could do in the meanwhile is to denign delete permission on
> the table execpt for DBA's
> Peter
> "Edward" wrote:
> > Excuse me if my redaction is bad, but I don't speak english very well.
> >
> > My Question is:
> > I have a SQL DataBase in which every day I save a lot of records.
> > I'm sured that the records are in the Database because I can see them.
> > I made queries in DB using a Windows application but next day I query again
> > and I can't see the records.
> > There are no proccess like stored procedures that delete the records; even
> > the application doesn't have an option to delete them.
> > First time, I thougt the reason was the DataBase Server wasn't shut down
> > correctly. But this time Server wasn't shut down.
> > I really need help because it's very stranger. can somebody help me to find
> > the phantom?
> >
> > Thanks!!!|||Hi Edward
Are you inserting the records into a temporary table by any chance?
Can you please send the table structure and the query that you are using to
view the data. Also please check in the database if data is present
thanks and regards
Chandra
"Edward" wrote:
> Excuse me if my redaction is bad, but I don't speak english very well.
> My Question is:
> I have a SQL DataBase in which every day I save a lot of records.
> I'm sured that the records are in the Database because I can see them.
> I made queries in DB using a Windows application but next day I query again
> and I can't see the records.
> There are no proccess like stored procedures that delete the records; even
> the application doesn't have an option to delete them.
> First time, I thougt the reason was the DataBase Server wasn't shut down
> correctly. But this time Server wasn't shut down.
> I really need help because it's very stranger. can somebody help me to find
> the phantom?
> Thanks!!!|||Hi chandra!!!
If I send you the inserts and updates made in the tables I'll send many
statements, because they are many tables involved in a transaction that is
handled by the same stored procedure, I think that the problem is cause at
transaction level, but I do not understand either, because if the problem
were of this type, the rest of clients applications that works with the
database would be locked.
another thing is happened to me ¿ The backup log that is made over the
database
every day may cause that the last transactions commited will be losted?
"Chandra" wrote:
> Hi Edward
> Are you inserting the records into a temporary table by any chance?
> Can you please send the table structure and the query that you are using to
> view the data. Also please check in the database if data is present
> thanks and regards
> Chandra

Data from Excel into SQL

Please excuse the cross posting I was not sure which newsgroup would produce
the required advice.
I have a requirement to update a SQL table with data from an Excel
worksheet.
Can anyone suggest the best way to do this...ADO??
PWS
There are numerous variables here. Is this a one-time requirement, or an on-going need? Do you
want to write a program, or just use a utility? If programming, what languages are you open to
using?
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would produce the required advice.
> I have a requirement to update a SQL table with data from an Excel worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||ADO is a great way - IMHO. There are many others too. If you fancy
taking the ADO route here's a few useful links:
'Exhaustive list of objects, properties, methos etc.
http://www.devguru.com/Technologies/...cts_index.html
'getting started
http://msdn.microsoft.com/library/de...tml/sqlado.asp
'a good FAQ for commonly experienced problems - beats waiting for
replies on an NG!
http://www.oblique.ch/ms/ADO_Faq.html
And here's some sample code to get you started. Note, there's many ways
to work with ADO and Access - I use RecordSet to retrieve data and
Command to insert or update. Seems to work ok...
HTH
Gareth
'--
Private Const myDBConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB.mdb"
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
'Once we've opened a connection we leave it open!
'This is closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean
'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State <> adStateClosed Then
fcnConnectToDB = True
Exit Function
End If
End If
'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
.CursorLocation = adUseClient
.connectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With
End Function
'
'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant
'Check we're connected to the database
fcnConnectToDB
'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic 'adLockBatchOptimistic
.Open mySQL 'cmd
End With
'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close
'tidy up
Set rs = Nothing
'(We leave the connection permanently open - you may like to
' close it afterwards)
End Function
Public Function ADO_UpdateDB(mySQL As String) As Boolean
'Check we're connected to the database
fcnConnectToDB
'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
On Error Resume Next
.Execute
If Err.Number = 0 Then ADO_UpdateDB = True
On Error GoTo 0
End With
'tidy up
Set cmd = Nothing
'(We leave the connection permanently open)
End Function
Paul Smith wrote:
> Please excuse the cross posting I was not sure which newsgroup would produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm
It demonstrates how to use SQL in Excel's VBA to:
* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.
DAO and ADO files available.
You can also download the demonstration file called "excelsql.zip".
The code is open and commented.
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would
produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||I found the chicken vindaloo recipe particularly useful too - made it,
ate it, loved it. Thanks
Andy Wiggins wrote:
> This might be a help for getting data to and from Excel and Access: It
> includes examples of using variables in SQL queries.
> http://www.bygsoftware.com/examples/sql.html
> Or you can get there from the "Excel with Access Databases" section on page:
> http://www.bygsoftware.com/examples/examples.htm
> It demonstrates how to use SQL in Excel's VBA to:
> * create a database,
> * create a table
> * insert records
> * select records,
> * update records,
> * delete records,
> * delete a table,
> * delete a database.
> DAO and ADO files available.
> You can also download the demonstration file called "excelsql.zip".
> The code is open and commented.
>

Data from Excel into SQL

Please excuse the cross posting I was not sure which newsgroup would produce
the required advice.
I have a requirement to update a SQL table with data from an Excel
worksheet.
Can anyone suggest the best way to do this...ADO'?
PWSThere are numerous variables here. Is this a one-time requirement, or an on
-going need? Do you
want to write a program, or just use a utility? If programming, what langua
ges are you open to
using?
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would produ
ce the required advice.
> I have a requirement to update a SQL table with data from an Excel workshe
et.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||ADO is a great way - IMHO. There are many others too. If you fancy
taking the ADO route here's a few useful links:
'Exhaustive list of objects, properties, methos etc.
http://www.devguru.com/Technologies...ects_index.html
'getting started
http://msdn.microsoft.com/library/d...
ado.asp
'a good FAQ for commonly experienced problems - beats waiting for
replies on an NG!
http://www.oblique.ch/ms/ADO_Faq.html
And here's some sample code to get you started. Note, there's many ways
to work with ADO and Access - I use RecordSet to retrieve data and
Command to insert or update. Seems to work ok...
HTH
Gareth
'--
Private Const myDBConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB.mdb"
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
'Once we've opened a connection we leave it open!
'This is closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean
'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State <> adStateClosed Then
fcnConnectToDB = True
Exit Function
End If
End If
'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
.CursorLocation = adUseClient
.connectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With
End Function
'
'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant
'Check we're connected to the database
fcnConnectToDB
'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic 'adLockBatchOptimistic
.Open mysql 'cmd
End With
'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close
'tidy up
Set rs = Nothing
'(We leave the connection permanently open - you may like to
' close it afterwards)
End Function
Public Function ADO_UpdateDB(mySQL As String) As Boolean
'Check we're connected to the database
fcnConnectToDB
'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
On Error Resume Next
.Execute
If Err.Number = 0 Then ADO_UpdateDB = True
On Error GoTo 0
End With
'tidy up
Set cmd = Nothing
'(We leave the connection permanently open)
End Function
Paul Smith wrote:
> Please excuse the cross posting I was not sure which newsgroup would produ
ce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm
It demonstrates how to use SQL in Excel's VBA to:
* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.
DAO and ADO files available.
You can also download the demonstration file called "excelsql.zip".
The code is open and commented.
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would
produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||I found the chicken vindaloo recipe particularly useful too - made it,
ate it, loved it. Thanks
Andy Wiggins wrote:
> This might be a help for getting data to and from Excel and Access: It
> includes examples of using variables in SQL queries.
> http://www.bygsoftware.com/examples/sql.html
> Or you can get there from the "Excel with Access Databases" section on pag
e:
> http://www.bygsoftware.com/examples/examples.htm
> It demonstrates how to use SQL in Excel's VBA to:
> * create a database,
> * create a table
> * insert records
> * select records,
> * update records,
> * delete records,
> * delete a table,
> * delete a database.
> DAO and ADO files available.
> You can also download the demonstration file called "excelsql.zip".
> The code is open and commented.
>