February 27, 2007 at 11:08 am
Hi
As a T-SQL developer, not a DBA, I am banging my head on the wall regarding a replication issue. We have a server on our network configured to publish and distribute a compressed snapshot of a databse. There are two push subscribers which are external to our network but over which we have admin control. The replication istelf works fine but we want to fire a script before and after replication which changes the default page on the websites affected by the replication so we added the relevant script paths to Snapshot tab in the replication properties dialog.
However, when we run it, we get the following error - Failed to launch osql (could not find the specified path). Now, we know the scripts get packaged into the cab file but we cannot work out why when they are unpacked at the subscriber SQL cannot find the location it thinks it unpacked them to.
Help!! I have years of experience in writing T-SQL but little to no experience of replication as I've always previously worked in places where I can go pester the DBA about this kind of thing so I'm learning as I go. If I haven't explained this clearly enough I can always elaborate if anyone thinks they can help and needs more info. Any help would be much appreciated!
Thanks in advance
Steve Pettifer
Head of Development, TMTI Ltd
February 27, 2007 at 11:32 pm
Check the BOL topic "Executing Scripts Before and After the Snapshot is Applied"
You can specify the scripts to run before and/or after snapshot...it is very useful if some one want to create/modify indexes/triggers...and it can be used in your scenario...
MohammedU
Microsoft SQL Server MVP
February 28, 2007 at 2:52 am
Thanks for the reply but if you read my previous post, you will see that we know how to add the scripts to the replication job but the issue we are having is that the job itself is not executing the scripts.
The scripts are packaged into the cab file correctly and the job seems to extract them but then can't execute them as it can't find them in the location that it expects. The session details screen looks like this
and as you can see, the file seems to be extracted OK but launching osql fails. If you then go to the error screen, you see this screen which suggests that it cannot find the location where it thinks it extracted the file to.
Is there some kind of issue with using pre and post scripts in compressed snapshots or have we forgotten to do something?
Any and all help appreciated!
Steve
February 28, 2007 at 2:35 pm
It is confused error, it could be the file path is incorrect or osql.exe path is not set...
Are you passing the right path of the file to OSQL when it is executing?
Is OSQL path set the destinations server? Try specifiying the full path of osql.exe in the code...
MohammedU
Microsoft SQL Server MVP
February 28, 2007 at 3:20 pm
If not already tried here are a couple of things for you to check:
1. Ensure that the account(s) used to run the SQL Server and SQL Agent services on the SQL Publisher, Distributor and Subscriber have been assigned the appropriate access rights
2. Get more information by adding detailed logging to your distribution or merge agent that is applying the snapshot eg. add the following parameters
"-output \\<distrib. server>\<distrib. share>\Logs\rep.log -outputverboselevel 3"
then start the agent
Note, the specified logfile will contain details of the SQL replication agent actions in applying the snapshot to the subscriber however the OSQL actions will be logged in a different logfile that is normally located in the Windows temp folder on the SQL distribution server.
March 5, 2007 at 3:55 am
Hi Mark
We checked the accounts and they were correct so we added the verbose logging as suggested and it was just the ticket. As a result, we discovered firstly that SQL was using an incorrect path for osql. It was using
C:\Program Files\Microsoft SQL Server\80\Tool\Binn\osql.exe
whereas, being a standard installation of SQL server, it should have been
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe
The problem was a registry key (HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup\SQLPath) which seems to have been missing the s at the end of the word 'Tools'. Quite why, we have no idea as all the other servers are fine and this is a recently built machine which used the same install scripts as our other servers but it rang a bell as I had seen another post somewhere else mentioning this key.
Once we had sorted this out, the verbose logging then caught an error in the sql script but, once fixed and the snapshot rebuilt, the whole shebang ran like a dream and the websites got the correct holding page displayed as the default whilst the replication was in progress and then reverted back once finished. Now we don't get error messages from google every night when it crawls the site whilst replication is running!
Many thanks for the help
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
