Investigating Deleted Records

I am investigating an issue where a bunch (6000) records got deleted
from a table. I am using SQL 2000 Standard and have my data recovery
model set to Full. I am using Red-Gate SQL log rescue to look at the
log and to recover the records.
My real question is: Is there a way to find out what SQL statement
created a given log entry? Is there some way to match a log entry to a
trace in the SQL Profiler?
--
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/27/2007 5:51:50 PMTransaction logging only log the effect of the commands submitted. So, any log mining tool cannot
get the actual command from the transaction log. However, you might be able to correlated the log
records with a profiler trace based on things like spid, date and time. I would assume that a log
mining tool could do this if provided with a profiler trace, but the tool would of course need to
have such feature.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Japheth Nolt" <japheth.remove@.landiscomputer.com> wrote in message
news:xn0f47mo0ktq9v001@.msnews.microsoft.com...
>I am investigating an issue where a bunch (6000) records got deleted
> from a table. I am using SQL 2000 Standard and have my data recovery
> model set to Full. I am using Red-Gate SQL log rescue to look at the
> log and to recover the records.
> My real question is: Is there a way to find out what SQL statement
> created a given log entry? Is there some way to match a log entry to a
> trace in the SQL Profiler?
> --
> Japheth Nolt
> Microsoft SBF Specialist
> Landis Computer
> www.landiscomputer.com
> 3/27/2007 5:51:50 PMsql

Investigating Deleted Records

I am investigating an issue where a bunch (6000) records got deleted
from a table. I am using SQL 2000 Standard and have my data recovery
model set to Full. I am using Red-Gate SQL log rescue to look at the
log and to recover the records.
My real question is: Is there a way to find out what SQL statement
created a given log entry? Is there some way to match a log entry to a
trace in the SQL Profiler?
--
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/27/2007 5:51:50 PMTransaction logging only log the effect of the commands submitted. So, any l
og mining tool cannot
get the actual command from the transaction log. However, you might be able
to correlated the log
records with a profiler trace based on things like spid, date and time. I wo
uld assume that a log
mining tool could do this if provided with a profiler trace, but the tool wo
uld of course need to
have such feature.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Japheth Nolt" <japheth.remove@.landiscomputer.com> wrote in message
news:xn0f47mo0ktq9v001@.msnews.microsoft.com...
>I am investigating an issue where a bunch (6000) records got deleted
> from a table. I am using SQL 2000 Standard and have my data recovery
> model set to Full. I am using Red-Gate SQL log rescue to look at the
> log and to recover the records.
> My real question is: Is there a way to find out what SQL statement
> created a given log entry? Is there some way to match a log entry to a
> trace in the SQL Profiler?
> --
> Japheth Nolt
> Microsoft SBF Specialist
> Landis Computer
> www.landiscomputer.com
> 3/27/2007 5:51:50 PM

Investigating Deleted Records

I am investigating an issue where a bunch (6000) records got deleted
from a table. I am using SQL 2000 Standard and have my data recovery
model set to Full. I am using Red-Gate SQL log rescue to look at the
log and to recover the records.
My real question is: Is there a way to find out what SQL statement
created a given log entry? Is there some way to match a log entry to a
trace in the SQL Profiler?
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/27/2007 5:51:50 PM
Transaction logging only log the effect of the commands submitted. So, any log mining tool cannot
get the actual command from the transaction log. However, you might be able to correlated the log
records with a profiler trace based on things like spid, date and time. I would assume that a log
mining tool could do this if provided with a profiler trace, but the tool would of course need to
have such feature.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Japheth Nolt" <japheth.remove@.landiscomputer.com> wrote in message
news:xn0f47mo0ktq9v001@.msnews.microsoft.com...
>I am investigating an issue where a bunch (6000) records got deleted
> from a table. I am using SQL 2000 Standard and have my data recovery
> model set to Full. I am using Red-Gate SQL log rescue to look at the
> log and to recover the records.
> My real question is: Is there a way to find out what SQL statement
> created a given log entry? Is there some way to match a log entry to a
> trace in the SQL Profiler?
> --
> Japheth Nolt
> Microsoft SBF Specialist
> Landis Computer
> www.landiscomputer.com
> 3/27/2007 5:51:50 PM

Invalid value for key attachdbfilename.

HI,

We upgraded to SQL Server 2005 Standard Edition for our ASP.NET 2.0 website. We were using SQL Server Express 2005. That worked fine. Now we are unable to connect to the database. I have googled, but I just cann't figure out what is going on. Any help is appreciated. Here is the error.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

In our firewall sqlbrowser.exe and sqlservr.exe are allowed.

Thanks Matt

Matt,

I have seen this error everytime I install MS SQL 2005, you need to enable TCP/IP as by default is not

Open the program -> SQL Server Surface Configuration

Go To -> Surface Area Configuration and Connections

Go To -> Remote Connections

and change Local Connections only to

Local and Remote connections

|||

AL,

Thank you for the quick reply. I did what you said and it was configured to,

Local and Remote Connections

Using both TCP/IP and named pipes,

already.

My databases reside inside my App_Data folder in my ASP.NET 2.0 application.

Do you have any other ideas?

Could it be my connection string?

Thank you again.

Matt

|||

Matt,

My biggest apologies, I jump to the answer as I have seen this message more than once. Again sorry.

If you already configured that, and you open all the ports on the firewall, I do not remember the port number but you can test from one computer to the other by opening a dos box and doing

telnet servername portnumber

If connects, you are good!! If does not ... open the port in the firewall

|||

Al,

Thank you again for helping me. I think that it is my fault. I don't think I have given you enough information about my situation.

I am trying to move my asp.net 2.0 website from sql server express to sql server 2005 standard edition.

So far all I have done is uninstall sql express and then installed sql server 2005.

I configured sql server 2005 with, what I assume to be the standard setup. I just used the wizard and did not change anything else. Those settings from the previous post were what was already checked when you told me to look at them. I did not set them.

I think from the little bit of research I have done, that I cann't use the AttachDbFile feature that sql express uses for sql server 2005?

At this point I am completely lost and frustrated. I am fairly new to this and trying hard to figure it out.

I do appreciate the help.

Any suggestion or ideas?

Thank You Again

Matt

|||

Actually attaching the database is the best solution. Also check on the logins after attaching that the username and password are correct. You can use VS2005 to connect to a database to see if that test works, there is a great wizard with great errors that might tell you what the problem is.

Besides that I am pretty lost, should work :-(

|||

Al,

Thank you for the help. I got it to work with one database. I used the wizrd and it worked great.

I also created a user in sql server 2005. I gave it a bunch of permissions just to get it to work. I think I should narrow thoses down to only the ones it needs.

My only other question is, I ran the aspnet_regsql.exe and it created a database in sql server 2005. But I was unable to use the ASP.NET Configuration tool to create roles and users. I made the same type of connection as with my other database. Error below.

The following message may help in diagnosing the problem:A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Any ideas?

Thank You Again

Matt

|||When you use ASP.NET Configuration, make sure you give it a connection string that will work on the NEW DATABASE for users and roles. If you connect to the new database, you'll see the providers tab|||

Al,

Thanks again. I got it all working now. It was the connection string.

Thanks

Matt

|||

Did you figure it by going to ASP.NET Configuration?

Glad you fix it! Stick around!

|||

No, I worked through it on a post by scottgu. I had .mdf in my connectionstring. When I removed .mdf, it made the connection.

Thanks Matt

|||Ah! Mr Guthrie's blog is like a gold mine!

Invalid Connection to SQL2000 after installing SP3

After having installed SP3 on SQL 2000 server standard edition running
on Windows 2000 server, users cannot access the database whether
through Enterprise Manager, Query Analyser, or my application.
It was running fine before. Users are running Windows XP. The database
is a named instance and is used for replication. Funny enough, I may
access the database from my development machine which is running
Windows 2000 server.
Help is most welcome
PatrickPatrick,
What are the symptoms? What does "users cannot access the database"
mean? Does it time-out, do they get an error? Please paste the error
message.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick wrote:
> After having installed SP3 on SQL 2000 server standard edition running
> on Windows 2000 server, users cannot access the database whether
> through Enterprise Manager, Query Analyser, or my application.
> It was running fine before. Users are running Windows XP. The database
> is a named instance and is used for replication. Funny enough, I may
> access the database from my development machine which is running
> Windows 2000 server.
> Help is most welcome
> Patrick|||Mark,
Thank you for your interest in our problem. The error message is just
"Invalid Connection". The server is running SQL server 2000 with sp3 n
Windows server 2000.
the client are running Windows XP pro.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Patrick,
Connect from Query Analyzer and paste the message here, you will get
something more than "Invalid Connection".
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick Menage wrote:
> Mark,
> Thank you for your interest in our problem. The error message is just
> "Invalid Connection". The server is running SQL server 2000 with sp3 n
> Windows server 2000.
> the client are running Windows XP pro.
> Best Regards
> Patrick
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||> Thank you for your interest in our problem. The error message is just
> "Invalid Connection".
? What software is giving this "error message"?
http://www.aspfaq.com/
(Reverse address to reply.)|||I have just found the solution. There was another SQL server instance
running with the same port number. I changed it and everythings is
working fine.
Thank you for your support.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Invalid Connection to SQL2000 after installing SP3

After having installed SP3 on SQL 2000 server standard edition running
on Windows 2000 server, users cannot access the database whether
through Enterprise Manager, Query Analyser, or my application.
It was running fine before. Users are running Windows XP. The database
is a named instance and is used for replication. Funny enough, I may
access the database from my development machine which is running
Windows 2000 server.
Help is most welcome
PatrickPatrick,
What are the symptoms? What does "users cannot access the database"
mean? Does it time-out, do they get an error? Please paste the error
message.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick wrote:
> After having installed SP3 on SQL 2000 server standard edition running
> on Windows 2000 server, users cannot access the database whether
> through Enterprise Manager, Query Analyser, or my application.
> It was running fine before. Users are running Windows XP. The database
> is a named instance and is used for replication. Funny enough, I may
> access the database from my development machine which is running
> Windows 2000 server.
> Help is most welcome
> Patrick

Invalid Connection to SQL2000 after installing SP3

After having installed SP3 on SQL 2000 server standard edition running
on Windows 2000 server, users cannot access the database whether
through Enterprise Manager, Query Analyser, or my application.
It was running fine before. Users are running Windows XP. The database
is a named instance and is used for replication. Funny enough, I may
access the database from my development machine which is running
Windows 2000 server.
Help is most welcome
Patrick
Patrick,
What are the symptoms? What does "users cannot access the database"
mean? Does it time-out, do they get an error? Please paste the error
message.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick wrote:
> After having installed SP3 on SQL 2000 server standard edition running
> on Windows 2000 server, users cannot access the database whether
> through Enterprise Manager, Query Analyser, or my application.
> It was running fine before. Users are running Windows XP. The database
> is a named instance and is used for replication. Funny enough, I may
> access the database from my development machine which is running
> Windows 2000 server.
> Help is most welcome
> Patrick
|||Mark,
Thank you for your interest in our problem. The error message is just
"Invalid Connection". The server is running SQL server 2000 with sp3 n
Windows server 2000.
the client are running windows XP pro.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Patrick,
Connect from Query Analyzer and paste the message here, you will get
something more than "Invalid Connection".
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick Menage wrote:
> Mark,
> Thank you for your interest in our problem. The error message is just
> "Invalid Connection". The server is running SQL server 2000 with sp3 n
> Windows server 2000.
> the client are running windows XP pro.
> Best Regards
> Patrick
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||> Thank you for your interest in our problem. The error message is just
> "Invalid Connection".
? What software is giving this "error message"?
http://www.aspfaq.com/
(Reverse address to reply.)
|||I have just found the solution. There was another SQL server instance
running with the same port number. I changed it and everythings is
working fine.
Thank you for your support.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Invalid attempt to read when no data is present

I am using a standard dbreader type of loop in a query to retrieve data. I am running over what should be end of record set, every time.

I have altered my read procedures to use while dbreader.read() and if dbreader.read(), to attempt to avoid getting the error. Neither is stopping it.

While debugging it, as I get to the last item and actually get the error, if I check the dbreader status, it still indicates that it has rows.

Anyone have any ideas on how to get around this?

TIA, Tom

Can you post your code?
|||

Sure can. The below posted is the whole thing. The sqlcommand, connection, etc. should be irrelavant.

' get the data from the database and pass it back.

Function getTheData(ByVal cmd As String) As DataTable

' note that the passed cmd is the below sqlString

Dim sqlString As String = "SELECT CONVERT(char(20), Date, 107) AS Date, City, State, Company, Position, JobNumber, Row_Number() Over (ORDER BY JobNumber DESC) as Item FROM JobsDB "

' define the new datatable to hold our results

dt = New DataTable("Jobs")

' define the columns we will be saving

Dim dcIt As New DataColumn("Item", GetType(String))

Dim dcDt As New DataColumn("Date", GetType(String))

Dim dcCt As New DataColumn("City", GetType(String))

Dim dcSt As New DataColumn("State", GetType(String))

Dim dcJN As New DataColumn("JobNumber", GetType(Integer))

Dim dcKW As New DataColumn("KeyWords", GetType(String))

Dim dcPos As New DataColumn("Position", GetType(String))

Dim dcCo As New DataColumn("Company", GetType(String))

' add columns

dt.Columns.Add(dcIt)

dt.Columns.Add(dcDt)

dt.Columns.Add(dcCt)

dt.Columns.Add(dcSt)

dt.Columns.Add(dcJN)

dt.Columns.Add(dcKW)

dt.Columns.Add(dcPos)

dt.Columns.Add(dcCo)

' define datarow

Dim dr As DataRow

' build sql command info

sqlCmd = New SqlCommand

sqlCmd.Connection = sqlConn

sqlCmd.CommandType = CommandType.Text

sqlCmd.CommandText = sqlString

Try

sqlConn.Open()

dbReader = sqlCmd.ExecuteReader()

If dbReader.HasRows Then

While dbReader.Read() 'this can be changed to if dbreader.read(), no diff

' build the data table item from the database

dr = dt.NewRow()

dr("Item") = dbReader.Item("Item").ToString()

dr("Date") = dbReader.Item("Date").ToString()

dr("City") = dbReader.Item("City").ToString()

dr("State") = dbReader.Item("State").ToString()

dr("JobNumber") = dbReader.Item("JobNumber").ToString()

dr("Position") = dbReader.Item("Position").ToString()

dr("Company") = dbReader.Item("Company").ToString()

dr("KeyWords") = keywords

dt.Rows.Add(dr)

End While

Else

lblNoData.Visible = True

End If

Catch ex As Exception

Dim exMsg As String = Request.ServerVariables("Script_Name") + ", getTheData(cmd=" & sqlString & "): msg=" + ex.Message.ToString()

utils.writeApplicationLog(exMsg, System.Configuration.ConfigurationManager.AppSettings("UtilityDbName"))

Response.Redirect(ConfigurationManager.AppSettings("errorPage") & ConfigurationManager.AppSettings("errCatchAll") & "&return=" & Request.ServerVariables("Script_Host"))

End Try

Return dt.Copy

End Function

Thanks, Tom

|||The code worked for me using sample data.

Some thoughts: Make the declarations of sqlConn, sqlCmd, and dbReader local to the function instead of global. Add a Finally section that contains dbReader.Close, sqlConn.Close, and sqlConn.Dispose.

If that doesn't change anything, is it possible that there's something in your data that is causing the problem?