Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Sunday, 30 December 2007

Message
Executed as user: {removed}. Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "{removed}". [SQLSTATE 42000] (Error 7346)  OLE DB provider "OraOLEDB.Oracle" for linked server "{removed}" returned message "ORA-01403: no data found". [SQLSTATE 01000] (Error 7412).  The step failed.

Once again, my most favorite.

This is from a sql agent job, in sql server 2005, that calls a stored procedure that uses a Oracle Linked server.  Now what really is strange, is that i can take that stored procedure and run it from a query window and it runs fine !  BUT NOT AS A JOB !

Stopped SQL Agent, still Fails.

Finally had to stop and start the ENTIRE INSTANCE.  I think this is overkill, but I didn't have hours to research and I was still in the maintenance window.  Not sure if this is related to the MS DTC Service or an Oracle issue.

This is on an HP 585, Windows 2003, 64 bit, Oracle 10g client.

%^&$#$#%$%

Sunday, 30 December 2007 14:21:57 (Central Standard Time, UTC-06:00) | Comments [0] |  Linked Server#
Sunday, 23 December 2007

First off it's always important to have lots of logging on for your dts packages.  These packages are difficult to troubleshoot, even with the logs.  So SET LOGGING ON, in the data pumps, in sql jobs, everywhere (don't forget you may need to have some clean up scripts to keep your log files from growing out of control).

Recently ran across a package, failing in production, ran fine of course in development and test.  Now this wasn't the greatest package, but unfortunately the DBA doesn't always get consulted on the design of things, but of course, it became the DBA Groups problem to fix!

The package has quite a few tasks, data pumps from Oracle to SQL and even runs an external c# program to pull data from Active Directory \ Exchange.

Nothing obvious for the error (it seems obvious to you realize it's bogus !), the log file is below.  Finally after researching and research could find nothing for why the "incorrect syntax error happenned".  To this day I beleive that to be a "bogus" error.  

I focused on that provider error of 170 (AA), remembering back to my ancient days of programming, I looked the error up:  AA        170 BUSY: The requested resource is in use  .  I began to throttle the parrallell processes to lower the amount of resources simultaneously in use by the package, ultimately I had to set it to 1.  It's run fine every day since.  I'm not sure if the Oracle provider interface on this machine is outdated and caused the error, or if the "requested resource in use" was some other item !

Don't forget those error codes... http://www.lifeasbob.com/Code/ErrorCodes.aspx

DTSRun OnError:  DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005)
   Error string:  Error at Destination for Row number 1. Errors encountered so far in this task: 1.
   Error source:  DTS Data Pump
   Help file: 
   Help context:  0
 
Error Detail Records:
 
Error:  -2147467259 (80004005); Provider Error:  170 (AA)
   Error string:  Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0
 
DTSRun OnError:  DTSStep_DTSDataPumpTask_2, Error = -2147213206 (8004206A)
   Error string:  The number of failing rows exceeds the maximum specified.
   Error source:  Microsoft Data Transformation Services (DTS) Data Pump
   Help file:  sqldts80.hlp
   Help context:  0
 
 Error Detail Records:
 
Error:  -2147213206 (8004206A); Provider Error:  0 (0)
   Error string:  The number of failing rows exceeds the maximum specified.
   Error source:  Microsoft Data Transformation Services (DTS) Data Pump
   Help file:  sqldts80.hlp
   Help context:  0

Error:  -2147467259 (80004005); Provider Error:  170 (AA)
   Error string:  Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file: 
   Help context:  0
 
DTSRun OnFinish:  DTSStep_DTSDataPumpTask_2
DTSRun:  Package execution complete.

Sunday, 23 December 2007 15:36:47 (Central Standard Time, UTC-06:00) | Comments [0] |  DTS#
Wednesday, 19 December 2007

We have had this problem since SQL Server 2005, all versions, service packs and hotfixes.

A cursor of system databases that randomly skips / leaves out  certain databases depending on how the cursor is declared.

Generally we have maintenance jobs, custom (not maintenance plans), that declare a cursor and loop through each database to perform some task, dbcc, reindex / defragment, backup etc.

The syntax is generally:

DECLARE dbreindex_cursor CURSOR FOR
 SELECT name
 FROM master.sys.databases
 WHERE name NOT IN ('tempdb', 'model','master','msdb')
 AND State <> 1 --not being loaded/restored
 ORDER BY name

THIS IS NOT 100% RELIABLE !!! 

The only way we catch this is we have a job that tracks DBCC's and Backups and then a SSRS Report that shows any database without a dbcc or backup in the past 24 hours.  What we find is that occassionaly it will "skip" a database with the above syntax.  We can never reproduce it on demand, and the only way we can find it happenning is with this report.  Now we do have 100's of SQL Servers and only see it on one or two every couple weeks, but it is maddenning to trouble shooot.

Eventually we changed the cursor to use a different syntax, and it never has happenned on a server using the below syntax ..

DECLARE dbreindex_cursor CURSOR  Local Forward_Only STATIC READ_ONLY TYPE_WARNING FOR
 SELECT name
 FROM master.sys.databases
 WHERE name NOT IN ('tempdb', 'model','master','msdb')
 AND State <> 1 --not being loaded/restored
 ORDER BY name

I've hardle ever seen a refernce to this, we did let our MS Rep and TAM know, but since it is so rare and can not be reproduced never really could figure out what is wrong with the above cursor. 

I have seen one other reference with someone else experiencing the same thing, so I know we are not alone... http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/d4e6549cf10cfe0c/4211b0612a33368f?lnk=st&q=sql+server+2005+cursor+databases&rnum=3&hl=en#

Pay particular attention to how a cursor is declared in sql server 2005....something is more "sensitive"...

 

 

 

Wednesday, 19 December 2007 09:45:22 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Sunday, 16 December 2007

  Spring Fornicator brewed up...

Not sure the name is appropriate for an Easter Brew, but it's definetly time to get working on those Lagers that take 3 months.

John Bull Marris Otter Extract 3.3 lbs

BierKeller Light Extract 3.5 lbs

12 Oz. Carapils Crushed Malt (mash 30 minutes 158, 1 gallon of water).

2lbs Clover Wildflower Honey (last 15 minutes)

1 Oz. Tettnanger Hops (bittering)

1 Oz Saaz Hops (finishing)

White Labs Zurich Lager yeast (swiss lager).

Standard 1 hour Boil and hopping schedule.

Not sure how the Zurich yeast will work, but it is a lager yeast and wanted something a bit different.  I expect this to take 6 weeks of fermentation (2 weeks primary, 4 weeks secondary) and at least 4-6 weeks of lagering in the refridgerator.  Plan on 1 week in primary at 50 degrees F, than cranking down to 40 degrees.  Will raise temp day before bottling to 55 degrees to "wake up" yeast, hold bottles at 55 degress and crank down temp every other day 2 degress until 45 degrees than hold there for 2-3 weeks and then work down to 40.

So long to wait...

Transferred 12/29/2007.

Bottled 1/19/2008 - Beer smelled great, sampled great, did not clear much at all, cloudy (protein haze from honey?), who needs clear beer ? I have a liver! 

Sunday, 16 December 2007 09:52:14 (Central Standard Time, UTC-06:00) | Comments [2] |  Beer #
Wednesday, 05 December 2007

   Finally couldn't take the squeaking and hesitation of my 2000 ford windstar, so I got to it and replaced the serpentine belt.

After spending about 15 minutes to detemine the tensioner pulley was located on the very bottom, under the power steering pump (damn why didn't they put that up top ?).  This means I couldn't release the tension and pull the belt off (because I had too lay on my side and reach up under and through the passenger wheel well).  Called the wife and she pulled the belt off from the top.

Review of the belt shows wear, nothing overly wrong with it, but with 115K miles; it's time to get a new one.

Down to local FLAPS (Friendly local auto parts store), get talked into the $40 belt vs $20 (sucker !).

Installation goes as expected, not hard, just have to be patient and work the belt around it's appropriate places (course Ford couldn't be smart and put a picture of the serpentine diagram somewhere in the engine compartment, but I drew a picture before taking the old one off (see i am wiser), and the new belt came with a diagram too).

Getting the thing back on resulted in the same problem as getting it off, it just wasn't possible for me to grab the wrench and release the tension and put the belt back on (see description above). 

Call Wife, she tries to slip belt back on from top, but either doesn't have the hand strength or doesn't want to get pinched putting belt over idler pulley.  I talk her into laying on the ground and reaching up to through the wheel well to the wrench on tensioner pulley....we were done 2 minutes later !

Car is running great no hesitiation, less squeaking; can only assume the hesitation was caused by the belt slipping occasionally and causing the alternator to "jump".  Squeaking still makes let's me know that in the near future a water pump, alternator, power steer pump, tension or idler pulley will be calling out out to me, but that is expected with high mileage.

Total time, 2 hours (including run to FLAPS).

Wednesday, 05 December 2007 09:31:53 (Central Standard Time, UTC-06:00) | Comments [0] |  Windstar#
Monday, 03 December 2007

Recently had a request to access some DMV's by the Administrators of an application.

I couldn't really think of a reason why not to grant access to the following DMV's:

sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests

Supposedly this request was so they could monitor the connections for their applications and ensure things weren't "going wild".  Sounds good to me, a pro-active application team, permission granted.  They may not be sys-admins, but if they see lots of connections from a particular web server, they can go to that web server and reset IIS, saving an alert going to the DBA Team about excessive connections.

Also note that if you don’t have VIEW SERVER STATE permission on the server you will see only the current session, not all executing sessions in the instance of SQL Server.

Monday, 03 December 2007 13:37:41 (Central Standard Time, UTC-06:00) | Comments [0] |  Security#

Burned again by the famous combination of Oracle and SQL Server linked servers, and a poorly configured SQL Agent job.

Scenario:

  1. Job Runs every 5 minutes
  2. Job Runs a standard T-SQL Stored Procedure
  3. Stored Procedure makes use of Oracle Linked Server (full 4 part naming and Open query used)
  4. Oracle goes down for scheduled maintenance
  5. SQL DBA's forget to stop job
  6. Job pages on-call (me)
  7. Disable job until Oracle maintenance complete
  8. Oracle maintenance complete.
  9. Connectivity of linked server verified
  10. Re-enable job
  11. JOB Fails !
  12. T-SQL Stored procedure runs from SSMS fine, but won't run as a job

Here is the poorly configured sql agent.  1st off the job output file is set to over-write, so know as I write this I can't quote the exact error, 2nd the system was set at the default job history retention, so I can't go into the job history to grab the error either.

Basically the error was something about reading a row from sysoledbservers or something.

The solution was to Stop and Start SQLAgent; now why SQLAgent, running tsql using a linked server would not work, when the sqm tsql would run just fine from SSMS is beyond me, which is why I wish i had the exact error, as I was going to call pss support, which i didn't do at 1am in the morning.

Monday, 03 December 2007 09:02:28 (Central Standard Time, UTC-06:00) | Comments [0] |  Linked Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
DTA - Failed to initialize MSDB dat...
SQL Server Security, not where it n...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
May, 2017 (2)
April, 2017 (1)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll