error handling flow: execution

Showing posts with label execution. Show all posts

Thursday, March 29, 2012

Error loading Query Execution Plan

I am trying to tune a very long running query (18 minutes on an Axim X51, 8secs on my laptop), but I can't get the query plan file that is generated on the device to load in the Sql Server Management Studio. I am using the Sql Everywhere CTP on the device, and version 9.00.2047 of the management studio shell.

FWIW, when I try to create the execution plan by running the same query on a .sdf file local on my laptop, I get a similar error trying to view the query plan.

Apart from the query plan issues, it would appear (just from the query execution time) that the indexes defined on the sdf file are not being used when executing the query on the device, but are being used when executing the query on the laptop. This is pure SWAG on my part, though.

I can't figure out how to attach a file to the post, unfortunately.

Thanks for any help you can offer.

Matthew Belk, BizSpeed, Inc.

Matthew,

What is the error that you get when loading the query execution plan in Management Studio? My guess is that it would be an issue in rendering the graphical execution plan. However, you can still see the plan information from the plan file (xml format).

Also, the query plan usually does not differ on the laptop and the device.

|||

I was able to identify the query bottleneck by manually interpreting the plan file, but it was an arduous task sifting through all that XML. There was a similar issue posted to the Feedback section in the SQL Server area of the Microsoft Connect website (issue id 231137) that I validated and commented on. That issue was marked closed/fixed, but there were no additional comments.

Thanks, Matthew

Here is the text of the plan file:

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

Error loading execution plan XML file C:\Documents and Settings\mbelk\My Documents\batitem.sqlplan. (SQLEditors)

Program Location:

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.LoadXml(String xmlFile)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.Editor.Microsoft.VisualStudio.Shell.Interop.IPersistFileFormat.Load(String fileName, UInt32 grfMode, Int32 readOnly)

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

Exception has been thrown by the target of an invocation. (mscorlib)

Program Location:

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertToObjectWrapper(Object item)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.PropertyFactory.CreateProperty(PropertyDescriptor property, Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectParser.ParseProperties(Object parsedItem, PropertyDescriptorCollection targetPropertyBag, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder.Execute(Object dataSource)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.LoadXml(String xmlFile)

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

Exception has been thrown by the target of an invocation. (mscorlib)

Program Location:

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

Object reference not set to an instance of an object. (SqlMgmt)

Program Location:

at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ExpandableArrayWrapper.PopulateProperties(ICollection collection)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertToObjectWrapper(Object item)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.PropertyFactory.CreateProperty(PropertyDescriptor property, Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectParser.ParseProperties(Object parsedItem, PropertyDescriptorCollection targetPropertyBag, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ExpandableObjectWrapper..ctor(Object item, String defaultPropertyName, String displayName)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.Convert(ScanRangeType item)

|||

Sorry about that. I checked out the details.

This error shows up whenever more than one column of a composite index is being used for a seek or scan.

e.g.

select * from
stores, sales where
sales.stor_id = stores.stor_id
and sales.ord_num = 'test'
- with a composite index on sales.stor_id and sales.ord_num.

For a work around, choose only one column from the index while tuning the query initially.

e.g.

select * from
stores, sales where
sales.stor_id = stores.stor_id

The plan that you get should be similar to the actual plan. For the final pass of tuning the query, use the full query!

Thanks

Pragya

sql

Sunday, March 11, 2012

error in query execution plan when using linked server

One of our jobs stopped working. It gets to a certain update statement and
never finishes. We've tried new linked servers. I looked at an execution plan
yesterday and then today I added another linked server to test with and got
this error when I tried to look at the execution plan - "OLE DB provider
'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
from the index rowset.]."
And now when I go back to using the original linked server I get the same
error.
Any ideas?
Thanks,
Dan D.
I guess the issue as to why you get the error now on the
original linked server is answered in your post on settings.
In terms of the specific error you are getting, it can
depend on what provider you are using and what options you
have selected for the provider. I've seen the error when
using Index As Access path for the provider option. You
typically use the option when the data source is on the
same server as SQL Server. You can find more information on
this option in books online under the topic:
Keyset-Driven Cursors Requirements for OLE DB Providers
Try removing the option to see if that specific error goes
away.
In terms of troubleshooting why the job stopped working, you
can turn on a trace flag to try to get additional error
information. Execute:
dbcc traceon(7300,3604)
and then run the query used by the job in Query Analyzer.
You could also use profiler and capture the OLEDB Errors
event.
-Sue
On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>One of our jobs stopped working. It gets to a certain update statement and
>never finishes. We've tried new linked servers. I looked at an execution plan
>yesterday and then today I added another linked server to test with and got
>this error when I tried to look at the execution plan - "OLE DB provider
>'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
>'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
>[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
>from the index rowset.]."
>And now when I go back to using the original linked server I get the same
>error.
>Any ideas?
>Thanks,
|||Yes that's the option. When I stopped using it the error went away.
I'll try the traceon and profiler.
Thanks Sue,
Dan D.
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>
|||Hi,
I am developing an ole db provider and am getting the same error. It occurs
right after sql server reads the INDEXES schema rowset. I have set the
"Index as access path option" on and need to make it work with this option
on.
I have found that if I change the schema rowset to indicate that it is an
integrated index, the error goes away. Also, it seems odd to me that the
message is generated before the IOpenRowset call to open the index rowset
even occurs. There must be something in the schema rowset that sql server
uses to produce this error message.
What changes do I need to make to the index schema entries to prevent this
error ?
Thanks,
Bill Emerson
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>

error in query execution plan when using linked server

One of our jobs stopped working. It gets to a certain update statement and
never finishes. We've tried new linked servers. I looked at an execution plan
yesterday and then today I added another linked server to test with and got
this error when I tried to look at the execution plan - "OLE DB provider
'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
from the index rowset.]."
And now when I go back to using the original linked server I get the same
error.
Any ideas?
Thanks,
--
Dan D.I guess the issue as to why you get the error now on the
original linked server is answered in your post on settings.
In terms of the specific error you are getting, it can
depend on what provider you are using and what options you
have selected for the provider. I've seen the error when
using Index As Access path for the provider option. You
typically use the option when the data source is on the
same server as SQL Server. You can find more information on
this option in books online under the topic:
Keyset-Driven Cursors Requirements for OLE DB Providers
Try removing the option to see if that specific error goes
away.
In terms of troubleshooting why the job stopped working, you
can turn on a trace flag to try to get additional error
information. Execute:
dbcc traceon(7300,3604)
and then run the query used by the job in Query Analyzer.
You could also use profiler and capture the OLEDB Errors
event.
-Sue
On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>One of our jobs stopped working. It gets to a certain update statement and
>never finishes. We've tried new linked servers. I looked at an execution plan
>yesterday and then today I added another linked server to test with and got
>this error when I tried to look at the execution plan - "OLE DB provider
>'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
>'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
>[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
>from the index rowset.]."
>And now when I go back to using the original linked server I get the same
>error.
>Any ideas?
>Thanks,|||Yes that's the option. When I stopped using it the error went away.
I'll try the traceon and profiler.
Thanks Sue,
Dan D.
"Sue Hoegemeier" wrote:
> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >One of our jobs stopped working. It gets to a certain update statement and
> >never finishes. We've tried new linked servers. I looked at an execution plan
> >yesterday and then today I added another linked server to test with and got
> >this error when I tried to look at the execution plan - "OLE DB provider
> >'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
> >'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
> >[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
> >from the index rowset.]."
> >
> >And now when I go back to using the original linked server I get the same
> >error.
> >
> >Any ideas?
> >
> >Thanks,
>|||Hi,
I am developing an ole db provider and am getting the same error. It occurs
right after sql server reads the INDEXES schema rowset. I have set the
"Index as access path option" on and need to make it work with this option
on.
I have found that if I change the schema rowset to indicate that it is an
integrated index, the error goes away. Also, it seems odd to me that the
message is generated before the IOpenRowset call to open the index rowset
even occurs. There must be something in the schema rowset that sql server
uses to produce this error message.
What changes do I need to make to the index schema entries to prevent this
error ?
Thanks,
Bill Emerson
"Sue Hoegemeier" wrote:
> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >One of our jobs stopped working. It gets to a certain update statement and
> >never finishes. We've tried new linked servers. I looked at an execution plan
> >yesterday and then today I added another linked server to test with and got
> >this error when I tried to look at the execution plan - "OLE DB provider
> >'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
> >'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
> >[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
> >from the index rowset.]."
> >
> >And now when I go back to using the original linked server I get the same
> >error.
> >
> >Any ideas?
> >
> >Thanks,
>

error in query execution plan when using linked server

One of our jobs stopped working. It gets to a certain update statement and
never finishes. We've tried new linked servers. I looked at an execution pla
n
yesterday and then today I added another linked server to test with and got
this error when I tried to look at the execution plan - "OLE DB provider
'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
[Non-interface error: OLE/DB provider returned an invalid bookmark ordi
nal
from the index rowset.]."
And now when I go back to using the original linked server I get the same
error.
Any ideas?
Thanks,
--
Dan D.I guess the issue as to why you get the error now on the
original linked server is answered in your post on settings.
In terms of the specific error you are getting, it can
depend on what provider you are using and what options you
have selected for the provider. I've seen the error when
using Index As Access path for the provider option. You
typically use the option when the data source is on the
same server as SQL Server. You can find more information on
this option in books online under the topic:
Keyset-Driven Cursors Requirements for OLE DB Providers
Try removing the option to see if that specific error goes
away.
In terms of troubleshooting why the job stopped working, you
can turn on a trace flag to try to get additional error
information. Execute:
dbcc traceon(7300,3604)
and then run the query used by the job in Query Analyzer.
You could also use profiler and capture the OLEDB Errors
event.
-Sue
On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>One of our jobs stopped working. It gets to a certain update statement and
>never finishes. We've tried new linked servers. I looked at an execution pl
an
>yesterday and then today I added another linked server to test with and got
>this error when I tried to look at the execution plan - "OLE DB provider
>'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
>'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
>[Non-interface error: OLE/DB provider returned an invalid bookmark ord
inal
>from the index rowset.]."
>And now when I go back to using the original linked server I get the same
>error.
>Any ideas?
>Thanks,|||Yes that's the option. When I stopped using it the error went away.
I'll try the traceon and profiler.
Thanks Sue,
Dan D.
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Hi,
I am developing an ole db provider and am getting the same error. It occurs
right after sql server reads the INDEXES schema rowset. I have set the
"Index as access path option" on and need to make it work with this option
on.
I have found that if I change the schema rowset to indicate that it is an
integrated index, the error goes away. Also, it seems odd to me that the
message is generated before the IOpenRowset call to open the index rowset
even occurs. There must be something in the schema rowset that sql server
uses to produce this error message.
What changes do I need to make to the index schema entries to prevent this
error ?
Thanks,
Bill Emerson
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>

Wednesday, March 7, 2012

error in execution time

hi all

i am working in sql reporting 2000.

In my report i have included field to display the execution time.

That time is not changing if i am running the report again & again.

Its only showing the time at which report was run first time in current Internet Explorer window.

how can i get current time in report?

plz help me.

In your text box, put this:

="Report Created On: " & Globals.ExecutionTime

|||

Thanks for reply.

I am already using Globals.ExecutionTime to display execution time in my report .

But if i am rexecuting the report then also time is not getting updated.

If i am running other report & after that report if i am running this report then also its showing the last time at which the report was executed.

So what is the solutin for this?

Plz help me

|||The reports might have been cached on the server if you specifically have set it to keep the last run report. So it may not be running the report, but instead getting the last cached report. I haven't tinkered with the settings, but it's an idea. You might want to check the report properties and then the execution tab..

Error in Execution of a Job..

I am getting an error while executing a job. Can any one
give an idea how to resolve this...
Executed as user: GECCEFDBY\##MSSQLServer.
cdspcoin1ccefge.master.dbo.xp_fixeddrives [SQLSTATE 01000]
(Message 0) Access to the remote server is denied because
no login-mapping exists. [SQLSTATE 42000] (Error 7416).
The step failed.
Thanks much in advance..If this is a linked server, you may need to set up
an explicit sql server login in order to run this command.
Otherwise, logging sql services in as a domain user or
using kerberos may be required.
"Harcharan" <harcharan.jassal@.seepz.tcs.co.in> wrote in message
news:3b5f01c34086$c8e804f0$a601280a@.phx.gbl...
> I am getting an error while executing a job. Can any one
> give an idea how to resolve this...
>
> Executed as user: GECCEFDBY\##MSSQLServer.
> cdspcoin1ccefge.master.dbo.xp_fixeddrives [SQLSTATE 01000]
> (Message 0) Access to the remote server is denied because
> no login-mapping exists. [SQLSTATE 42000] (Error 7416).
> The step failed.
> Thanks much in advance..

Friday, February 24, 2012

Error in Copy Database Wizard execution

I've been moving databases to 2005 and have always received useful error messages regarding failures using the Copy Database wizard... until the database I am working on now - below is the entire error message - not very useful huh? - any ideas on where to look for the problem? (substituted xxxx for servernames etc)

Thanks, Chris

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 1/17/2007
Time: 11:13:02 AM
User: xxxxxxx
Computer: xxxxxxx
Description:
Event Name: OnError
Message: The requested objects failed to transfer.
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: xxxxxxxxxxxxxxxxxx
Source Name: xxxx01_xxxxx02_Transfer Objects Task
Source ID: {0BDAA262-18CB-4BC8-928C-607680E0B634}
Execution ID: {83CB3AB4-3AD7-4AFE-BE5D-9EBA89434874}
Start Time: 1/17/2007 11:13:02 AM
End Time: 1/17/2007 11:13:02 AM
Data Code: 0

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Hi Chris,

This error basically says that an error occurred during the transfer of this database from server A to server B, which indeed is not very helpful.

However, Copy Database Wizard has been widely improved and you should try to use the current CTP version available:

http://www.microsoft.com/sql/ctp.mspx

There is a good chance the problem you are facing had already been fixed in this CTP.

Thanks,
Oscar.

|||

Thanks Oscar - Do I only need to install the CTP on my dev box / workstation? Or do I need to install it on the destination server? (which is where the wizard puts the package and executes it)

If I install the CTP on my workstation will SSIS packages I create / edit etc be able to be opened / edited on non SP2 CTP workstations?

(just trying to see if installing SP2 CTP is something I can do.. I can't on any servers that I am working with but might be able to on my workstation)

|||

Hi Chris,

Regarding the Copy Database Wizard, you should always install the latest version on the target machine, precisely for the reason you just described (i.e. the target machine is where the wizard puts the package and executes it). You could then launch the wizard from here and select the source database on the corresponding machine. This is assuming that it is ok for you to upgrade to this version.

Changing the version on the client side won't help much, because most of the logic is executed at the destination.

Hope this helps!

Cheers,
Oscar.

|||

OK now that I have installed SP2 CTP... Database Copy Wizard packages are failing that succeeded yesterday (so its gotten worse).

Errors are similar in all failures - error about data not accessible and then a little further down it will say the AnsiPaddingStatus is not available. I am running this under the SQL Agent account (which is dbo). These databases are going from a 2000 server to a 2005 server. (If I run 2005 to 2005 as a test I do not receive the error - only when using a 2000 box as the source)

Message: The Table '[dbo].[xxxxxxxxxx]' cannot be scripted as its data is not accessible.

...

InnerException-->Property AnsiPaddingStatus is not available for Column '[xxxxxxx]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

|||

Hi Chris,

Could you please tell me what are the main options you are selecting from the wizard? For example, I believe you are using the SMO Transfer method instead of Detach/Attach, right? Are you selecting any extra objects apart from the whole database as part of the copy? At the end of the CDW wizard and right before you perform the copy, there is summary page. This has useful information that we could look at as well. Please, make sure you are selecting the "Save Transfer Logs" option before clicking "Finish". This will generate a TransferDump*.sql file for each object being copied (e.g. if you are copying one database with one extra object, then there will be two such files). They will be located under:

C:\Documents and Settings\onaim\Local Settings\Application Data\Microsoft\SQL Server\Smo

for that particular user, assuming SQL Server is installed on the "C" drive in this example.

You could also select "Write to a log file" versus to the Windows event viewer.

If you could send me these two files, I would have more information to be able to help you.

Thanks,
Oscar.

|||

I also forgot to mention that you do need to have sysadmin rights on the destination, otherwise you will not be able to successfully run CDW.

Cheers,
Oscar.

|||

Hello Oscar

I have a problem with Copy Database Wizard to.

Have agent account as sysadmin in both source and target database

Target server has CTP Dec 2006 installed. Bat I can’t install it to the source “production” system

Managed to transfer five databases fixing the error found at the error log file

At the sixth database I have an error that I can not understand

The error appears when trying to create a view at the target system.

errorCode=-1073548784 description=Executing the query "create view [dbo].[VShift_Cost]

select sf.ShiftID, SFD.UnitID, sum(sfd.charge) as Shift_Charge

from shiftcostdetail sfd inner join shiftcost sf on sfd.shiftcostid = sf.shiftcostid

group by sf.shiftid, SFD.unitID

" failed with the following error: "Invalid object name 'shiftcostdetail'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The shiftcostdetail is an existing table and the view works fine at the source system.

Can you help?

Thanks

|||

Hi Panzin,

I already responded to your message in this other thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169786&SiteID=1

Thanks,
Oscar.

|||

Yes, SMO (do not have the problem with the detach/attach - it works fine). No extra objects, I remove the logins option. Yes I was using the "Save Transfer Logs" option.

I've looked at those SQL files and saw nothing out of the ordinary in them (actually I do one db at a time becuase there is no way to specify order of dbs in the wizard (hint would be great new feature :) so its only 1 file and it runs on the destination)

The log you mentioned says same thing... hopefully this helps:

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 1/17/2007
Time: 4:43:39 PM
User: xxxxxxxxxxx
Computer: RSSQLK01
Description:
Event Name: OnError
Message: The Table '[dbo].[tblxxxxxxxxxxxx]' cannot be scripted as its data is not accessible.
StackTrace: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Transfer.Microsoft.SqlServer.Management.Common.ITransferMetadataProvider.SaveMetadata()
at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.Configure(ITransferMetadataProvider metadataProvider)
at Microsoft.SqlServer.Management.Smo.Transfer.GetTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->Property AnsiPaddingStatus is not available for Column '[vcSymbol]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
StackTrace: at Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32 index)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(String propName)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](String propName)
at Microsoft.SqlServer.Management.Smo.Table.GetColumnPadding(Column c)
at Microsoft.SqlServer.Management.Smo.Table.GetTableAnsiPadded()
at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
Operator: xxxxxxxxxxxxxxxxxxx
Source Name: xxxxxxxxxxxxxxxxxxxxxxxx
Source ID: {3DE71A4C-00D6-4C2F-B188-EE680B19AFD9}
Execution ID: {EBD7814A-6828-4041-B08D-E8CA73CD75AF}
Start Time: 1/17/2007 4:43:39 PM
End Time: 1/17/2007 4:43:39 PM
Data Code: 0

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Just to make sure this is clear - if I detach + attach method onto the 2005 box its fine, if I then take that and use SMO to another box its fine as well... its only when using SMO directly from 2000 to 2005.

Sorry I can't be more helpful - due to the SMO issues I was more than a day behind on the project so I ended up doing the project using the detach method even though that was not desired (original request called for the source to not go offline - but we comprimised by running only in off hours). Oh and I have full dbo rights on the source and destination.

|||

Hi Chris,

Thank you for the information. Let me investigate the issue based on the information that you have sent me so far and I will get back to you as soon as possible. In the meantime, it would be great if you could something for me. Could you please go to that particular table in SQL 2000 and script it to a query window and send me the output?

Thanks,
Oscar.

|||

CREATE TABLE [tblTradeCurrencyHedgeImport] (
[iTradeCurrencyHedgeImportID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[iCurrencyHedgeImportID] [int] NOT NULL ,
[dtDate] [smalldatetime] NULL ,
[vcSymbol] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iSettlementDate] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcBuySell] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iAccountID] [int] NULL ,
[iHedge] [int] NULL ,
[dTradePrice] [decimal](19, 8) NULL ,
[dFxAllInRate] [decimal](19, 8) NULL ,
[iNumberOfContractsPerBreak] [decimal](13, 2) NULL ,
[iContractSize] [int] NULL ,
[dMinimumTick] [decimal](19, 8) NULL ,
[dClosePrice] [decimal](19, 8) NULL ,
[dPricePercentDiff] [decimal](19, 8) NULL ,
[dtSettlementDate] [datetime] NULL ,
[dtInsertTime] [datetime] NULL CONSTRAINT [DF__tblTradeC__dtIns__56757D0D] DEFAULT (getdate()),
[vcInsertUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__tblTradeC__vcIns__5769A146] DEFAULT (suser_sname()),
[dtUpdateTime] [datetime] NULL CONSTRAINT [DF__tblTradeC__dtUpd__585DC57F] DEFAULT (getdate()),
[vcUpdateUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__tblTradeC__vcUpd__5951E9B8] DEFAULT (suser_sname()),
CONSTRAINT [PK_tblTradeCurrencyHedgeImport] PRIMARY KEY CLUSTERED
(
[iTradeCurrencyHedgeImportID]
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY]
GO

FYI this is scripted using 2000... it will not script the table in 2005 client tools - same error about not being able to determine ansi padding

|||

Hi Chris,

This is exactly what I was thinking. However, I haven't been able to reproduce it here at the lab and it would be great if you could help us by filing a bug using Microsoft Connect, including the steps that you had to follow in order to reproduce the problem. The URL for Microsoft Connect is:

http://connect.microsoft.com

I will make sure that this bug gets triaged as soon as possible.

Thanks again for your time and please let me know if I can be of any further assistance. We are here to help!

Cheers,
Oscar.

|||

The following links are also useful:

http://connect.microsoft.com/SQLServer/Feedback/

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=840523&SiteID=1

Cheers,
Oscar.

|||https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253839