Log shipping of SQL2012 databases failing - Database Discussion Boards

am executing it in workbench, am i right.

still am getting error at @

How are you calling the procedure?

It should work if you call the stored procedure in one of the two following ways:

SQL

getpassword @member = 'fred smith'

getpassword 'fred smith'

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

modified 10-Jan-14 8:21am.

You need to put a semi-colon at the end of your statement before the END keyword :

SQL

CREATE PROCEDURE getpassword( IN member varchar(20))
BEGIN
    SELECT password FROM admin WHERE member_id = member;
END

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

How to test it in workbench, whether it's working or not.

thanq , how can i test in workbench, whether in query, or at stored procedures

how can i test whether it's working or not in workbench.

am testing in query as:

execute getpassword 'stl123';
but it's giving errors.

and in my c# code is:

MySqlCommand command = new MySqlCommand("getpassword", con);
                  command.CommandType = System.Data.CommandType.StoredProcedure;
                  command.Parameters.AddWithValue("@member", txtuser.Text);
                  con.Open();
                  using (MySqlDataReader rdr = command.ExecuteReader())
                  {
                      if (rdr.Read())
                      {

reader object rdr is showing no rows.

Change this:

Member 10263519 wrote:
command.Parameters.AddWithValue("@member", txtuser.Text);

To this(remove the @ as it is not required from .net):

Member 10263519 wrote:
command.Parameters.AddWithValue("member", txtuser.Text);

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

In my environment I have two servers that host several production databases each - for this example I shall call them PROD1 and PROD2

I also have two servers that host logshipped copies of the production databases - one for reporting by users, the second for report development by the IT team. These I shall call REP1 and REP2.

The setup is as follows:
PROD1 - Logships 4 databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby
PROD2 - Logships 4 other databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby

Logshipping copy jobs are set to copy the transaction logs to a NAS device, to different folders for each of the reporting servers.

REP1 and REP2 therefore each host 8 logshipped copies of the production databases, set to Standby/Readonly

Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis, and the equivalent two jobs, plus two of the jobs from PROD2 to REP2 have started failing on REP2.

The job histories for the failing jobs don't show aas failed jobs, as the database connection of the jobs seems to be lost, meaning that logging of the rrors fails.

The error in the history are as follows:

Date 09/01/2014 07:30:00
Log Job History (LSRestore_PROD1_dBFirst)

Step ID 1
Server REP1
Job Name LSRestore_PROD1_dBFirst
Step Name Log shipping restore log job step.
Duration 00:01:56
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
2014-01-09 07:31:55.93 *** Error: Could not apply log backup file '\\nas2\dbbackups\TransactionLog\DBFirst_20140108210000.trn' to secondary database 'ReportdbFirst'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: An error occurred while processing the log for database 'ReportdbFirst'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
An error occurred during recovery, preventing the database 'ReportdbFirst' (13:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'ReportdbFirst', file 'livesystem_Data' on file 1.
Processed 1 pages for database 'ReportdbFirst', file 'livesystem_Log' on file 1.(.Net SqlClient Data Provider) ***
2014-01-09 07:31:55.93 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2014-01-09 07:31:55.93 Skipping log backup file '\\nas2\dbbackups\TransactionLog\dbFirst_20140108210000.trn' for secondary database 'ReportdbFirst' because the file could not be verified.
2014-01-09 07:31:55.93 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***

The database is left in "Restoring" mode and cannot then be used for reporting.

I have tried all sorts of things - introducing delays between the copy and restore jobs, moving the copy locatios to physical drives on the servers etc, but it still happens at least once per day, meaning I have to backup and restore the production databases manually to allow users/developers to work with the data.

Anyone any ideas? I have googled extensively and found several people reporting the same problems, but no working solutions.

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Chris Quinn wrote:
Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis

Usually when I see this sort of thing when it appears that connections are failing in odd ways I immediately presume that it is a firewall problem. Of course unless you are firewall administrator getting someone to admit that they did in fact change something in the firewall can be problematic.

The servers are all behind the same firewall, so I think this is unlikely

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Chris Quinn wrote:
The servers are all behind the same firewall

And you are positive that that statement means that the traffic in fact doesn't go through the firewall? (Versus firewall rules that 'should' allow it to behave as though the firewall wasn't impacting it.)

Hi Guys, 
I want to write a query in Oracle/SQL Server (doesn’t make a lot of difference) 
I have 3 tables: 
EMSINFO (EMSINFOID, DEVICEID, DATE, PlateNumber)
Devices (DEVICEID, INOUT)
Roads (ROADID, ROADNAME)

EMSINFO Contains the enters and exits of vehicles and it’s captured by a device
We have the device id, 
And roads has the lists of all the roads in the zone
based on the INOUTwe know whether its enter or exit
Now I want to select PlateNumber, total time in the zone (enter time – exit time) and 
Details of when entered and when exited in a text format in one record, because each vehicle can enter and exit multiple times in a particular day

Return Fields of store procedure:
PlateNo: Plate number	
text: Enter from (road name) at (time) 
      Exited from (road name) at ( time)
Duration: Total Duration of being in the zone

My question is generating that text or enters and exits
I appreciate the help

Mr.K

There is no info on which road the device controls.
Should possibly be:
Devices (DEVICEID, INOUT, ROADID)

Wrong is evil and must be defeated.
- Jeff Ello[^]

Yes, that's right, neglected to mention it, there is a ROADIDin Device table, now do you have any solution for this problem?

There are a number of solutions to this, you can use a CTE or 2 queries.

Select all the records using an inner join on the foreign keys and order by platenumber road and date.This should give you the reads for each plate so you can get the max/min for each plate/road and do a simple timespan function on the dates.

Never underestimate the power of human stupidity
RAH

Try something like this:

SQL

WITH Entries AS (
    SELECT  PlateNumber
           ,DATE
           ,RoadName
    FROM    EMSINFO e
           ,Devices d
           ,Roads r
    WHERE   e.deviceID = d.DeviceID
    AND     d.RoadID = r.RoadID
    AND     d.InOut = 'IN'
    )
,Exits AS (   
    SELECT  e.PlateNumber
           ,e2.DATE EntryTime
           ,e2.RoadName EntryRoad
           ,Min(DATE) ExitTime
    FROM    Entries e2
           ,EMSINFO e
           ,Devices d
    WHERE   e2.platenumber = e.platenumber
    AND     e.deviceID = d.DeviceID
    AND     d.InOut = 'OUT'
    AND     e.DATE > e2.DATE
    GROUP BY e.PlateNumber
            ,e2.DATE
            ,e2.RoadName
    )
SELECT  e.PlateNumber
       ,EntryTime
       ,EntryRoad
       ,ExitTime
       ,r.RoadName ExitRoad
FROM    Exits e
       ,EMSINFO e2
       ,devices d
       ,Roads r
WHERE   e.platenumber = e2.platenumber
AND     e.ExitTime = e2.DATE
AND     e2.DeviceID = d.DeviceID
AND     d.RoadID = r.RoadID

There are probably better implementations to your problem, Search for sql+islands+and+gaps[^].
"Islands and Gaps" is the name of this classic problem.

Wrong is evil and must be defeated.
- Jeff Ello[^]

Thanx, I will check it out

hi to all
i want to use a sub report in rdlc
i use a store procedure for data sourse of subreport that no contain any parameter
and when i want to run main reort i get this error!!!!?
An error occurred during local report processing.
Value cannot be null. Parameter name: value
i dont have any parameter in my sub report
and this errot occuered when i have sub report in main report.
my main report is ok.
thank for any help

Hi Experts, in an existing sp at our company I find this:

SET @MonthFrom=(@Month%12)-1

where @Monthis a calling parameter and @MonthFrom is declared within the sp.

Can someone please explain what "%12" means?

Thank you, Bernd

modified 7-Jan-14 2:11am.

It is called The modulo arithmetic operator[^]
It returns the remainder of dividing @month by 12.

Hi Peter, thx for your help. I could no determine that from the code and without a clue I was unable to find it in the internet. Now I wonder what sense this makes? None?!?
Greets Bernd

You are welcome. Please do one thing, according to CP instruction : "If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you." Thanks.

Hi to all
How can I write a query that get me this result:
My table is :
ColumnA ColumnB ColumnC ColumnD
A1 B1 Can be any thing Can be any thing
A1 B2 Can be any thing Can be any thing
A1 0 Can be any thing Can be any thing
A1 0 Can be any thing Can be any thing
A3 1 Can be any thing Can be any thing

I want to get this result after run a query:
ColumnA ColumnB ColumnC ColumnD
A1 B1 Can be any thing Can be any thing
A1 0 Can be any thing Can be any thing
A1 0 Can be any thing Can be any thing
A3 1 Can be any thing Can be any thing

If ColumnA have B1 and also have a row with B2 I want to just get A1 B1 Row
How can I do this
Thanks for any idea

How should the query behave if you have a row with B2 but none with B1? Should the row with B2 be included or not?

Wrong is evil and must be defeated.
- Jeff Ello[^]

I'm not very good at SQL, but I did write this for SQL Server 2012

I'm trying to write TSQL to get all the stuff sold on a date, distinct list of stuff based on part number, and just add up the total qty sold, and the subtotal based on price for each item.

I'm not sure if I should loop the @Temptable results using WHERE partnumber
I'm stuck here, not even sure what words to use to search for learning. I could use a pointer or suggested method to use here.

DECLARE <a href="/Members/Year">@Year</a>AS int;
DECLARE <a href="/Members/month">@Month</a>AS int;
DECLARE <a href="/Members/Day">@Day</a>AS int;
SET <a href="/Members/Year">@Year</a>= 2013;
SET <a href="/Members/month">@Month</a>= 8;
SET <a href="/Members/Day">@Day</a>= 29;

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
SET @startDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 23, 59, 59, 999);

DECLARE <a href="/Members/temp">@Temp</a>TABLE(
    PartNumber VarChar(80),
    ThumbNail VarChar(250),
    Qty INT,
    Cost Decimal,
    Price Decimal       
)
INSERT INTO <a href="/Members/temp">@Temp</a>(PartNumber, ThumbNail, Qty, Cost, Price)
SELECT PartNumber, ThumbNail, Qty, Cost, Price From CompletedOrdersCart WHERE OrderDate > @startDate AND OrderDate < @stopDate

////////////////////////////////////////////////////
// Here I have a table returned with 8 items in it called <a href="/Members/temp">@Temp</a>
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
06-CRL10, /images/06-cr10.jpg, 1, 6.45, 10.50
33-8114, /images/33114.jpg, 1, 3.45, 6.00
33-8114, /images/33114.jpg, 1, 3.45, 6.00
33-8114, /images/33114.jpg, 1, 3.45, 6.00
33-8114, /images/33114.jpg, 1, 3.45, 6.00
////////////////////////////////////////////////////

DECLARE @SummaryItems TABLE(
    PartNumber VarChar(80),
    ThumbNail VarChar(250),
    Qty INT,
    Cost Decimal,
    Price Decimal,
    SubTotal Decimal    
)
INSERT INTO @SummaryItems (PartNumber, ThumbNail, Cost, Price)
SELECT DISTINCT(PartNumber), ThumbNail, Cost, Price FROM <a href="/Members/temp">@Temp</a>

/////////////////////////////////////////////////////////
// Here I have a table returned with 2 distinct items, 
06-CRL10, /images/06-cr10.jpg, null, 6.45, 10.50, null
33-8114, /images/33114.jpg, null, 3.45, 6.00, null
/////////////////////////////////////////////////////////

// I want to add up the total qty, and create a subtotal of SUM(qty * price)
// from the table results above <a href="/Members/temp">@Temp</a>


// display the final results here
SELECT PartNumber, Thumbnail, Qty, Cost, Price, SubTotal FROM @SummaryItems;