Showing posts with label job. Show all posts
Showing posts with label job. Show all posts

Tuesday, March 20, 2012

book on EVERYTHING in SQL

i havnt used sql in a long time and now my job requires it. il be using it in access, maby oricle and maby sql server. i need a book that teaches general sql. all about updates, deletes,dates, joins, corrrelates sub queries, aliases,and anything else i could encounter that will cause me to pull my hair out in sql.maby even it it went into TSQL that would be useful.any recommendations?Did you visit Amazon.com, for example? Search through "books" and use keyword "SQL" (as you don't know which one you'll use). Check readers' reviews - could be helpful.|||The SQL Bible is probably good. I heard it will blow your mind what all it has. :) I may pick up both the one for Access and SQL. The books titled "The complete reference book of ____" are also good i heard|||I prefer:

Transact-SQL:
The Guru's Guide to Transact SQL and XML (or something like that) by Ken Henderson
Teach Yourself Transact-SQL in 21 Days by Sam's (believe it or not)

Architecture:
Inside SQL Server by Kalen Delaney (it's the best out there)

Performance Tuning:
SQL Server 2000 Performance Tuning Manual by Microsoft Press|||Each SQL implementation is different. Of the three you've listed, there is a wide variety of syntax supported. MS-SQL implementes full SQL-92. Jet (the engine used by MS-Access) implements a large subset of SQL-87 with a lot of the SQL-92 features grafted on in peculiar ways. Oracle supports most of the features of SQL-92, but using Oracle's non-standard syntax.

I don't know of any one book that covers all of the possible SQL-like variations. I like Derrick's suggestions for MS-SQL. If you are looking for a very generic SQL overview, I'd suggest SQL for Dummies. If you want quality background that will help you understand how you should use SQL (but not get far into any particular implementation), I'd recommend Joe Celko's SQL For Smarties.

In short, I don't think exactly what you want can exist, but there are lots of choices that might be "good enough" for what you really want... You just have to pick the one that seems the closest to what you think you'll need and go from there.

-PatP

BOL update

Hi!

When may we expect SP1 BOL update? It's hard to update job scripts which is used tokens since the sintax is changed and SP1 CTP BOL updates is not reflecting that!

? Alexey, The BOL update is expected to be online "very soon" but I can't give a specific date and time. I am looking forward to having access to a copy too. Andrew Watt [MVP] <Alexey Shirshov@.discussions.microsoft.com> wrote in message news:e6649386-99b2-43e1-adc8-bc54caef9801@.discussions.microsoft.com... Hi! When may we expect SP1 BOL update? It's hard to update job scripts which is used tokens since the sintax is changed and SP1 CTP BOL updates is not reflecting that!|||? On http://msdn.microsoft.com/sql/ there is a link to what is supposed to be April BOL. Unfortunately it currently takes you to the December BOL. Hopefully it will be fixed soon. Andrew Watt [MVP] "Andrew Watt [MVP]" <SVGDeveloper@.aol.com> wrote in message news:uHJi62RZGHA.1472@.TK2MSFTNGSA01.privatenews.microsoft.com... Alexey, The BOL update is expected to be online "very soon" but I can't give a specific date and time. I am looking forward to having access to a copy too. Andrew Watt [MVP] <Alexey Shirshov@.discussions.microsoft.com> wrote in message news:e6649386-99b2-43e1-adc8-bc54caef9801@.discussions.microsoft.com... Hi! When may we expect SP1 BOL update? It's hard to update job scripts which is used tokens since the sintax is changed and SP1 CTP BOL updates is not reflecting that!

Monday, March 19, 2012

Blocks and Locks in SQL

If you have an job interview question of how do handle SQL locks and blocks
whats your answer?"Lontae Jones" wrote:
> If you have an job interview question of how do handle SQL locks and block
s
> whats your answer?
I would talk about my experiences using Profiler, server-side traces, and
the really slick sp_blocker_pss80 lock / block analysis tool. I'd then ask
for them to put more context around the issues; for example: if the locking
condition was readily repeatable, I'd try to catch the lead blocker with
sp_who2, and run the DBCC INPUTBUFFER on the blocked/blocking SPID's to try
to get a handle on the situation causing the blocking. If the situation was
occurring in a distributed app and the sp_blocker_pss80 stored procedure was
reporting nested transactions, I'd start looking into how the app made DB
calls (i.e. - if they are asynchronous and nesting transactions, they may be
causing client app blocking issues that could be resolved by going a serial
route instead).
When I'm asked very broad questions I usually take the opportunity to flip
the situation on them and grill for specifics. The devil is always in the
details.|||Talk about transaction isolation level and NOLOCK Hints as well. I'm sure
that is what they're fishing for.
Greg Jackson
PDX, Oregon|||Look up Understanding and Avoiding Blocking in Books OnLine.
Here are a few suggestions from BOL to avoid blocking:
-Do not use or design an application that allows users to fill in edit boxes
that generate a long-running query. For example, do not use or design an
application that prompts the user for inputs but rather allows certain
fields to be left blank or a wildcard to be entered. This may cause the
application to submit a query with an excessive running time, thereby
causing a blocking problem.
-Do not use or design an application that allows user input within a
transaction.
-Allow for query cancellation.
-Use a query or lock time out to prevent a runaway query and avoid
distributed deadlocks.
-Immediately fetch all result rows to completion.
-Keep transactions as short as possible and in one batch.
-Explicitly control connection management.
-Stress test the application at the full projected concurrent user load.
Other suggestions:
-A query time-out for each query.
-Lock time-out for each query.
-Use a low isolation level.
-Use bound connections.
-Avoid using cursors
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:0E46E9EA-4975-4FC5-BAE3-6C1AE7D87D9C@.microsoft.com...
> If you have an job interview question of how do handle SQL locks and
> blocks
> whats your answer?

Thursday, March 8, 2012

Blocking in distribution database...

Hello...
I am noticing that everytime the the Agent history clean
up job runs... it causes contention and blocking in the
distribution database.
Has anyone come across this? If so, how did you resolve
this. I am using SQL Server 2000 and every server in my
topology uses SQL Server 2000 as well.
Please advise...
Thanks,
Brett
are you running datamirror? Datamirror does not clean up efficiently.
Blocking in the distribution database is caused by 2 factors
1) too many push agents running simultaneously. Try to stagger and schedule
them. If you can migrate to pull subscriptions.
2) too much replication metadata pooling in your distribution database due
to subscribers who are frequently off line. If you are using anonymous
subscribers this exacerbates the problem. Consider using named subscribers.
"Brett" <anonymous@.discussions.microsoft.com> wrote in message
news:1ae2001c41e7a$fc3ac590$a101280a@.phx.gbl...
> Hello...
> I am noticing that everytime the the Agent history clean
> up job runs... it causes contention and blocking in the
> distribution database.
> Has anyone come across this? If so, how did you resolve
> this. I am using SQL Server 2000 and every server in my
> topology uses SQL Server 2000 as well.
> Please advise...
> Thanks,
> Brett
>

Wednesday, March 7, 2012

Blocking

I am experiencing blocking problems on SQL Server 2000, SP3a. I have
read the posts and set up a job SQL agent to report on these
occurences I save the results to a table before executing an sp to
kill the offending process id. I am puzzled as to how a process that
does no updating can be guilty of blocking. Typically, I think what
is happening is a process that does no updates is blocking processes
that are trying to do updates. Can someone explain this to me? What
is the best way for me to resolve this?

Thanks!Hal (hforman1@.cfl.rr.com) writes:
> I am experiencing blocking problems on SQL Server 2000, SP3a. I have
> read the posts and set up a job SQL agent to report on these
> occurences I save the results to a table before executing an sp to
> kill the offending process id. I am puzzled as to how a process that
> does no updating can be guilty of blocking. Typically, I think what
> is happening is a process that does no updates is blocking processes
> that are trying to do updates. Can someone explain this to me? What
> is the best way for me to resolve this?

The fact that a reader can block a writer is not strange at all. Say
that you have a report function that performs some serious calculation
and has to scans several big tables. You can get big blocking issues
in this way.

What is the best in your case, is difficult to tell since I don't
know about the blocking situation. If indeed the problem is with
reports, when having a dedicated report server may be an idea.

But it could also be as simple that a few indexes can speed up the
blocking queries.

The action anyway is to get more information about the blocking queries,
and analyse them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, February 12, 2012

Bizarre maintenance plan failure (db backup)

I am getting a failure on the db backup job of one of my maintenance
plans. It is coming back with the generic error message of,

"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
failed."

I then checked the Database Maintenance Plan History page, but this
shows all the steps having run successfully. If I check the drives for
the actual backup files, they exist and look healthy too!

There is plenty of space on the drives, so it is not that.

I've checked the NT logs and all they say is,

"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan for All User Databases''
(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "

Which doesn't give me any clues.

Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?

Anyone had anything similar?(nielsonj1976@.yahoo.co.uk) writes:
> I am getting a failure on the db backup job of one of my maintenance
> plans. It is coming back with the generic error message of,
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
> failed."
> I then checked the Database Maintenance Plan History page, but this
> shows all the steps having run successfully. If I check the drives for
> the actual backup files, they exist and look healthy too!
> There is plenty of space on the drives, so it is not that.
> I've checked the NT logs and all they say is,
> "SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan for All User Databases''
> (0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
> 2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
> Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "
> Which doesn't give me any clues.

So did you look at the Job History? Find the backup job, right-click
and select Show History. In this dialog check "Show step details". The
step details usually have some more meat for information.

> Since the backups have actually run to success, I am going to take off
> the option on the maintenance plan to "Verfiy the integrity of the
> backup upon completion". Maybe it is this that is causing problems,
> rather than the backup?

Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> So did you look at the Job History? Find the backup job, right-click
> and select Show History. In this dialog check "Show step details". The
> step details usually have some more meat for information.

This doesn't really give me any clues either. If I click show step
details, I get it displaying 2 steps,

Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.

Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
35 (Schedule 1). The last step to run was step 1 (Step 1).

> > Since the backups have actually run to success, I am going to take off
> > the option on the maintenance plan to "Verfiy the integrity of the
> > backup upon completion". Maybe it is this that is causing problems,
> > rather than the backup?

> Ehum, if the problem is that the backups don't verify, that I would
> not like to know that and not sweep it under the carpet.

Having checked this morning the backup status is still showing Failed
even with the verify option off. The only reason I thought it *could*
be this is that the backups are fairly large (25gb+) so SQL server
could be having problems verifying with the disk space available?|||nielsonj1976@.yahoo.co.uk (nielsonj1976@.yahoo.co.uk) writes:
> This doesn't really give me any clues either. If I click show step
> details, I get it displaying 2 steps,
> Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
> [SQLSTATE 42000] (Error 22029). The step failed.
> Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
> 35 (Schedule 1). The last step to run was step 1 (Step 1).

I rarely set into maintenance job, so I don't really know where sqlmaint
might write its errors. But looking at the properties of a plan that I've
actually set up, I see that there is a Reporting tab. Have you checked
what's yours, and if that leads to a useful file?

>> > Since the backups have actually run to success, I am going to take off
>> > the option on the maintenance plan to "Verfiy the integrity of the
>> > backup upon completion". Maybe it is this that is causing problems,
>> > rather than the backup?
>> Ehum, if the problem is that the backups don't verify, that I would
>> not like to know that and not sweep it under the carpet.
> Having checked this morning the backup status is still showing Failed
> even with the verify option off. The only reason I thought it *could*
> be this is that the backups are fairly large (25gb+) so SQL server
> could be having problems verifying with the disk space available?

I will have to retract what I said above. I recalled a discussion among
my MVP colleagues who are into server administration. The consensus was
that verification of backups as offered by RESTORE VERIFYOLNLY is useless.
The only way to really verify a backup is to restore it. And run DBCC checks
on the restored copy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do your maint plans cover more than 1 database? If so check the recovery modes of each - this error seems to pop up if there are mixed modes within 1 plan.

bizarre job behavior

Look in the 'sysjobs' table of the msdb database. See if
the there is an entry for the jobs. If there is, check
the 'originating_server' column. It may still have the old
server name in it......

>--Original Message--
>Over the weekend I transferred our non Clustered SQL
Server to a new
>Clustered environment. I moved the jobs by method of
scripting. Since then,
>serveral of my jobs have been failing with different
problems. The one thing
>all of these jobs have in common is that they all call
DTS Packages that
>read/ write to files. (.txt, .xls, etc.)
>For the record, the account that the SQL Agent runs in
has full control over
>these files. If I log directly onto the console with this
account, I can
>create/ edit/ drop anything I want.
>Another fun fact is that I can run all of these DTS
Packages manually.
>Now here is all of the problems I'm having with these
jobs.
>1. Access denied for the account that SQL runs in to
read/ write to the
>file.
>2. I start the job manually, it never runs. RClick/ Start
Job... nothing
>ever happens.
>3. Although the job has a schedule, it shows (Date and
time are not
>available.) under the Next Run Date column.
>4. Jobs have a staus of "Performing completion actions"
forever. I found
>this one in KB, but think its really a by product of the
other issues.
>Something is obviously very wrong here. Any ideas are
greatly appreciated.
>
>--
>SQL2K SP3
>TIA, ChrisR
>
>.
>No, it has the correct server name since I scripted/ created the jobs
instead of moving them.
"Coskun" <anonymous@.discussions.microsoft.com> wrote in message
news:157b01c51ab8$685e91e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Look in the 'sysjobs' table of the msdb database. See if
> the there is an entry for the jobs. If there is, check
> the 'originating_server' column. It may still have the old
> server name in it......
>
> Server to a new
> scripting. Since then,
> problems. The one thing
> DTS Packages that
> has full control over
> account, I can
> Packages manually.
> jobs.
> read/ write to the
> Job... nothing
> time are not
> forever. I found
> other issues.
> greatly appreciated.

bizarre job behavior

Over the weekend I transferred our non Clustered SQL Server to a new
Clustered environment. I moved the jobs by method of scripting. Since then,
serveral of my jobs have been failing with different problems. The one thing
all of these jobs have in common is that they all call DTS Packages that
read/ write to files. (.txt, .xls, etc.)
For the record, the account that the SQL Agent runs in has full control over
these files. If I log directly onto the console with this account, I can
create/ edit/ drop anything I want.
Another fun fact is that I can run all of these DTS Packages manually.
Now here is all of the problems I'm having with these jobs.
1. Access denied for the account that SQL runs in to read/ write to the
file.
2. I start the job manually, it never runs. RClick/ Start Job... nothing
ever happens.
3. Although the job has a schedule, it shows (Date and time are not
available.) under the Next Run Date column.
4. Jobs have a staus of "Performing completion actions" forever. I found
this one in KB, but think its really a by product of the other issues.
Something is obviously very wrong here. Any ideas are greatly appreciated.
--
SQL2K SP3
TIA, ChrisRLook in the 'sysjobs' table of the msdb database. See if
the there is an entry for the jobs. If there is, check
the 'originating_server' column. It may still have the old
server name in it......
>--Original Message--
>Over the weekend I transferred our non Clustered SQL
Server to a new
>Clustered environment. I moved the jobs by method of
scripting. Since then,
>serveral of my jobs have been failing with different
problems. The one thing
>all of these jobs have in common is that they all call
DTS Packages that
>read/ write to files. (.txt, .xls, etc.)
>For the record, the account that the SQL Agent runs in
has full control over
>these files. If I log directly onto the console with this
account, I can
>create/ edit/ drop anything I want.
>Another fun fact is that I can run all of these DTS
Packages manually.
>Now here is all of the problems I'm having with these
jobs.
>1. Access denied for the account that SQL runs in to
read/ write to the
>file.
>2. I start the job manually, it never runs. RClick/ Start
Job... nothing
>ever happens.
>3. Although the job has a schedule, it shows (Date and
time are not
>available.) under the Next Run Date column.
>4. Jobs have a staus of "Performing completion actions"
forever. I found
>this one in KB, but think its really a by product of the
other issues.
>Something is obviously very wrong here. Any ideas are
greatly appreciated.
>
>--
>SQL2K SP3
>TIA, ChrisR
>
>.
>|||No, it has the correct server name since I scripted/ created the jobs
instead of moving them.
"Coskun" <anonymous@.discussions.microsoft.com> wrote in message
news:157b01c51ab8$685e91e0$a601280a@.phx.gbl...
> Look in the 'sysjobs' table of the msdb database. See if
> the there is an entry for the jobs. If there is, check
> the 'originating_server' column. It may still have the old
> server name in it......
>
> >--Original Message--
> >Over the weekend I transferred our non Clustered SQL
> Server to a new
> >Clustered environment. I moved the jobs by method of
> scripting. Since then,
> >serveral of my jobs have been failing with different
> problems. The one thing
> >all of these jobs have in common is that they all call
> DTS Packages that
> >read/ write to files. (.txt, .xls, etc.)
> >
> >For the record, the account that the SQL Agent runs in
> has full control over
> >these files. If I log directly onto the console with this
> account, I can
> >create/ edit/ drop anything I want.
> >
> >Another fun fact is that I can run all of these DTS
> Packages manually.
> >
> >Now here is all of the problems I'm having with these
> jobs.
> >
> >1. Access denied for the account that SQL runs in to
> read/ write to the
> >file.
> >2. I start the job manually, it never runs. RClick/ Start
> Job... nothing
> >ever happens.
> >3. Although the job has a schedule, it shows (Date and
> time are not
> >available.) under the Next Run Date column.
> >4. Jobs have a staus of "Performing completion actions"
> forever. I found
> >this one in KB, but think its really a by product of the
> other issues.
> >
> >Something is obviously very wrong here. Any ideas are
> greatly appreciated.
> >
> >
> >
> >--
> >SQL2K SP3
> >
> >TIA, ChrisR
> >
> >
> >.
> >

bizarre job behavior

Look in the 'sysjobs' table of the msdb database. See if
the there is an entry for the jobs. If there is, check
the 'originating_server' column. It may still have the old
server name in it......

>--Original Message--
>Over the weekend I transferred our non Clustered SQL
Server to a new
>Clustered environment. I moved the jobs by method of
scripting. Since then,
>serveral of my jobs have been failing with different
problems. The one thing
>all of these jobs have in common is that they all call
DTS Packages that
>read/ write to files. (.txt, .xls, etc.)
>For the record, the account that the SQL Agent runs in
has full control over
>these files. If I log directly onto the console with this
account, I can
>create/ edit/ drop anything I want.
>Another fun fact is that I can run all of these DTS
Packages manually.
>Now here is all of the problems I'm having with these
jobs.
>1. Access denied for the account that SQL runs in to
read/ write to the
>file.
>2. I start the job manually, it never runs. RClick/ Start
Job... nothing
>ever happens.
>3. Although the job has a schedule, it shows (Date and
time are not
>available.) under the Next Run Date column.
>4. Jobs have a staus of "Performing completion actions"
forever. I found
>this one in KB, but think its really a by product of the
other issues.
>Something is obviously very wrong here. Any ideas are
greatly appreciated.
>
>--
>SQL2K SP3
>TIA, ChrisR
>
>.
>
No, it has the correct server name since I scripted/ created the jobs
instead of moving them.
"Coskun" <anonymous@.discussions.microsoft.com> wrote in message
news:157b01c51ab8$685e91e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Look in the 'sysjobs' table of the msdb database. See if
> the there is an entry for the jobs. If there is, check
> the 'originating_server' column. It may still have the old
> server name in it......
>
> Server to a new
> scripting. Since then,
> problems. The one thing
> DTS Packages that
> has full control over
> account, I can
> Packages manually.
> jobs.
> read/ write to the
> Job... nothing
> time are not
> forever. I found
> other issues.
> greatly appreciated.

Friday, February 10, 2012

bit of a logic one?

I need a scheduled job, or a stored proc, or something, that can run against two databases at once. Is this possible?

I've currently got two databases that are nearly identical in structure. They both run online forums. Each of them runs a SP as a scheduled job step every ten minutes. Both SP's check the same Outlook mailbox (a restriction of SQL Mail), for messages relating to that forum, and processes them if necessary. They check whether a message relates to "their" forum/database by comparing the email's "from" address against the email addresses in the "forum_users" table.

The problem is with spam and other miscellany that neither of the databases decide is for them. I can't make the SP's run on a "if it's not for me, delete it" basis, because what if it's for the other database?

So I need a "cleaning" SP - one that will run through and compare the email "from" address against the email addresses in BOTH databases - and then delete anything that doesn't match.

Any ideas?Maybe it's more complex but...

You can access another database from an SP, by qualifying like this:

create procedure dbo.multidb as

select * from table1

select * from database2.dbo.table2

GO|||You could use UNION along with a linked server setup to do something like this:

IF EXISTS
(SELECT * FROM
(
SELECT * FROM SERVER1.DB1.dbo.forum_users
UNION
SELECT * FROM SERVER2.DB1.dbo.forum_users
) a
WHERE a.User = @.User
)
BEGIN
...
END

-Dan