SQL job failure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL job failure

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-07-12 : 11:18:49

Hi,

Bugging me for a couple of days.

A scheduled job is failing continously with this error

Executed as user: BLAH\admin. Failed to execute get connection string command. Inner Error ------------------> Row#: 0 Source: "Microsoft SQL Server Native Client 10.0" Instance: "DBShadow" Procedure: "sp_syscollector_get_warehouse_connection_string" Line#: 16 Error Number: 14686 Error State: 1 Error Severity: 16 Error Message: "The MDWInstance and MDWDatabase parameters of the configuration store cannot be null." Help File: "(null)", Help context: 0 GUID: {0C733A63-2A1C-11CE-ADE5-00AA0044773D}. OLE DB Error Record dump end. Process Exit Code 1. The step failed.

Even if I change the user, it doesn't work.

This is what the job is doing:

Step 1:
Type: Operating System (CmdExec)
dcexec -c -s 1 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))" -m 1

Step 2:
Type: TSQL

EXEC [dbo].[sp_syscollector_purge_collection_logs]

Step 3:
Type: Operating System (CmdExec)
dcexec -u -s 1 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))"

There are few different teams at play here. Not sure where to start looking at first. Can you please let me know where should I start here and where could be the problem?

Thanks a lot. Much appreciated

Regards,
D

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-12 : 11:39:12

have a look at msdb..sp_syscollector_get_warehouse_connection_string
and you'll see where the error comes from.

Which step is failing?

I would have thought the first one - but if it can get the machine/instance o connect then a bit surprised it can't pass it to the SP.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-07-12 : 16:02:41

I am not sure which job is failing. I am assuming it is the second one where the stored procedure is being run. The error message I posted is the only thing I have to work with.

Error message says the line 16 of the stored procedure and the segment failing is:


    DECLARE @instance_name sysname
    DECLARE @database_name sysname
    DECLARE @user_name sysname
    DECLARE @password sysname

    SELECT CONVERT(sysname,parameter_value)
    FROM [msdb].[dbo].[syscollector_config_store_internal]
    WHERE parameter_name = N'MDWInstance'

I ran this I am getting NULL. So, the parameters are not there. I am not sure this has to come from the step 1 or not.

Please let me know if there is another area I can look it as a cause.

Thanks a lot for your time..

- D

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-07-13 : 10:50:04

Hi,

I found an environment where the exact scenario works. So, in the environment it works, when I run the following it returns the Server/instance name.

But in the environment it is failing, it gives me "NULL"


SELECT CONVERT(sysname,parameter_value)
FROM [msdb].[dbo].[syscollector_config_store_internal]
WHERE parameter_name = N'MDWInstance'

Do you know where do I find the database MDWInstance.
I read through http://bassplayerdoc.blogspot.ca/2008/03/what-is-this-dcexecexe-running-on-my.html site and it say it creates this database to collect performance database.

If someone can point me in the right direction, that would be really helpful.

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources