DTA fails when I tune a simple select statement

Question

Post reply

DTA fails when I tune a simple select statement

mbokam

SSC-Addicted

Points: 426
More actions
April 19, 2010 at 11:48 am

#220623

We are on:
Verison: SQL 2008 [10.0.2531] on windows server 2008. Both are enterprise editions.
When I highlight the query and right-click, then select Analyze Query in Database Engine Tuning Advisor, then click "Start Analysis", I get the following error in the "Message" column of "Progress" tab of DTA:
Cannot initialize tuning. [.Net SqlClient Data Provider]
If I click on this, it displays this message:
"Cannot find either column 'msdb' or the user-defined function or aggregate 'msdb.dbo.fn_DTA_unquote_dbname', or the name is ambiguous. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count=0, current count=1. (Microsoft SQL Server Error: 4121)
Please let me know if you guys have any ideas on this error.
Thanks,
Sam.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

sturner SSC-Insane Points: 22411 More actions · Answer 1

can you provide the actual query in question? Without that.... not much we can do.

The probability of survival is inversely proportional to the angle of arrival.

mbokam SSC-Addicted Points: 426 More actions · Answer 2

Any simple select statement. For example...

SELECT [ApplicationId]

,[PathId]

,[Path]

,[LoweredPath]

FROM [aspnetdb].[dbo].[aspnet_Paths]

-------------------------------------------------------------------------------------------

BTW this is the full error msg:

===================================

Cannot initialize tuning. (.Net SqlClient Data Provider)

===================================

Cannot find either column "msdb" or the user-defined function or aggregate "msdb.dbo.fn_DTA_unquote_dbname", or the name is ambiguous.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. (.Net SqlClient Data Provider)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476

------------------------------

Server Name: HQSQLAPP01\EPM

Error Number: 4121

Severity: 16

State: 1

Procedure: sp_DTA_add_session

Line Number: 60

------------------------------

Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.SqlServer.Management.DTA.Client.NonQuery.BeginQuery(ServerConnection connection)

at Microsoft.SqlServer.Management.DTA.Client.TuningConnection.RunSyncQuery(IQuery query)

at Microsoft.SqlServer.Management.DTA.Client.TuningConnection.ExecuteNonQuery(SqlCommand command)

at Microsoft.SqlServer.Management.DTA.Client.TuningSession.StoreSessionXml()

at Microsoft.SqlServer.Management.DTA.Client.TuningSession.Start(Boolean withProgressEvents)

at Microsoft.SqlServer.Management.DTA.Shell.SessionForm.ProgressThread()

sturner SSC-Insane Points: 22411 More actions · Answer 3

Are you trying to run DTA on the server box or on your client computer? If the latter, make sure you have installed the .net 3.5 service pack and/or SQL Server 2008 client components

If this is happening on the server console itself something is broken. Do you have the latest service pack?

The probability of survival is inversely proportional to the angle of arrival.

mbokam SSC-Addicted Points: 426 More actions · Answer 4

Thanks for the quick response.

Yes I am ruuning DTA from the server box.

Server version:

I see .NET 3.5 SP1 is installed on the server too.

Do you suggest I need to repair .NET framework?

Thanks,

Sam.

sturner SSC-Insane Points: 22411 More actions · Answer 5

I'm not sure... I would check configuration for allowed protocols.... both the SQL client and the Database engine. Beyond that I'm scratching my head.

Have you installed anything else on that server since you installed SQL server?

The probability of survival is inversely proportional to the angle of arrival.

mbokam SSC-Addicted Points: 426 More actions · Answer 6

Yes we installed a commvault client which is a backup tool, after the server install.

And does my error in anyway realted to this update:

http://support.microsoft.com/kb/970315

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Have you checked in the msdb database for the function it is trying to use?

On my system it looks like this:

CREATE function [dbo].[fn_DTA_unquote_dbname](@dbname nvarchar(258) )

returns sysname

as

begin

declare @unquote nvarchar(258)

set @unquote = @dbname

if(patindex(N'[[]%',@unquote) > 0)

select @unquote = right(@unquote, LEN(@unquote)-1)

if(patindex(N'%]',@unquote) > 0)

select @unquote = left(@unquote, LEN(@unquote)-1)

select @unquote =REPLACE (@unquote,N']]',N']')

return @unquote

end

Note this function is in msdb\programmability\Scalar-valued Functions

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mbokam SSC-Addicted Points: 426 More actions · Answer 8

Oh. You got it.

I don't even see that function in the msdb database of my sql server instance.

What should I do to fix it?

Thanks,

Sam.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

Restore msdb from backup?

You do back it up right?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mbokam SSC-Addicted Points: 426 More actions · Answer 10

Yes we do backup msdb.

But how do I know when was this last corrupted? Which day's backup should I restore?

Or is there any easier method?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

Seeing as our versions are the same, you could "suck it and see" and use the code I posted to recreate the missing function - then hope that was all it was....

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mbokam SSC-Addicted Points: 426 More actions · Answer 12

BTW how did you view the function code. I am trying to look it up in my other server.

Thanks for your help.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

In SSMS, find the function under msdb/programmability/Scalar-valued functions, then right-click on it and choose Modify (or script as create)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);