I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?
Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
Monica Frintu
"semloh" wrote:
> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the same
> table?
> In the example below how do I represent the "Phone" as a complex type in the
> xsd and have the bulk upload load the elements to the same table as my root
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provide
> the sql:relationship so I don't get the error?
>
Showing posts with label executing. Show all posts
Showing posts with label executing. Show all posts
Wednesday, March 21, 2012
error when executing sql xml bulk load
I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
---
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
---
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
--
Monica Frintu
"semloh" wrote:
> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type
is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the sa
me
> table?
> In the example below how do I represent the "Phone" as a complex type in t
he
> xsd and have the bulk upload load the elements to the same table as my roo
t
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provid
e
> the sql:relationship so I don't get the error?
>
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
---
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
---
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
--
Monica Frintu
"semloh" wrote:
> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type
is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the sa
me
> table?
> In the example below how do I represent the "Phone" as a complex type in t
he
> xsd and have the bulk upload load the elements to the same table as my roo
t
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provid
e
> the sql:relationship so I don't get the error?
>
Error when executing Report
Hi all,
I have created about 40 reports that access our core ERP solution. All
these reports function perfectly. In the last day I have created 2 new
reports that access a different server/database thus a new shared datasource
was created. However, I am intermittantly receiving the following error
when I attempt to execute the two new reports:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
a.. Cannot create a connection to data source 'Blah2'.
(rsErrorOpeningConnection) Get Online Help
a.. SQL Server does not exist or access denied.
The reports function everytime in Visual Studio. What is going on?
Thanks
ClintSounds like a security issue. What does your datasource specify for a
username/password?
Steve
"AshVsAOD" wrote:
> Hi all,
> I have created about 40 reports that access our core ERP solution. All
> these reports function perfectly. In the last day I have created 2 new
> reports that access a different server/database thus a new shared datasource
> was created. However, I am intermittantly receiving the following error
> when I attempt to execute the two new reports:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> a.. Cannot create a connection to data source 'Blah2'.
> (rsErrorOpeningConnection) Get Online Help
> a.. SQL Server does not exist or access denied.
> The reports function everytime in Visual Studio. What is going on?
> Thanks
> Clint
>
>|||I thought that too. I have changed the datasource to be both shared and
custom. The username and password are correct. I am at a loss..
Especially seeing how it is so intermittant.
"SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> Sounds like a security issue. What does your datasource specify for a
> username/password?
> Steve
> "AshVsAOD" wrote:
> > Hi all,
> >
> > I have created about 40 reports that access our core ERP solution. All
> > these reports function perfectly. In the last day I have created 2 new
> > reports that access a different server/database thus a new shared
datasource
> > was created. However, I am intermittantly receiving the following error
> > when I attempt to execute the two new reports:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
Get
> > Online Help
> > a.. Cannot create a connection to data source 'Blah2'.
> > (rsErrorOpeningConnection) Get Online Help
> > a.. SQL Server does not exist or access denied.
> > The reports function everytime in Visual Studio. What is going on?
> >
> > Thanks
> > Clint
> >
> >
> >|||Not sure whether this will help. When u deploy the shared datsource to the
reportserver, try inputting the connection info and then run the report. I
had the same issue, and the connection trying the dba changed pointed to a
different instance..
Suresh
"AshVsAOD" wrote:
> I thought that too. I have changed the datasource to be both shared and
> custom. The username and password are correct. I am at a loss..
> Especially seeing how it is so intermittant.
> "SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
> news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> > Sounds like a security issue. What does your datasource specify for a
> > username/password?
> >
> > Steve
> >
> > "AshVsAOD" wrote:
> >
> > > Hi all,
> > >
> > > I have created about 40 reports that access our core ERP solution. All
> > > these reports function perfectly. In the last day I have created 2 new
> > > reports that access a different server/database thus a new shared
> datasource
> > > was created. However, I am intermittantly receiving the following error
> > > when I attempt to execute the two new reports:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> Get
> > > Online Help
> > > a.. Cannot create a connection to data source 'Blah2'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > a.. SQL Server does not exist or access denied.
> > > The reports function everytime in Visual Studio. What is going on?
> > >
> > > Thanks
> > > Clint
> > >
> > >
> > >
>
>sql
I have created about 40 reports that access our core ERP solution. All
these reports function perfectly. In the last day I have created 2 new
reports that access a different server/database thus a new shared datasource
was created. However, I am intermittantly receiving the following error
when I attempt to execute the two new reports:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
a.. Cannot create a connection to data source 'Blah2'.
(rsErrorOpeningConnection) Get Online Help
a.. SQL Server does not exist or access denied.
The reports function everytime in Visual Studio. What is going on?
Thanks
ClintSounds like a security issue. What does your datasource specify for a
username/password?
Steve
"AshVsAOD" wrote:
> Hi all,
> I have created about 40 reports that access our core ERP solution. All
> these reports function perfectly. In the last day I have created 2 new
> reports that access a different server/database thus a new shared datasource
> was created. However, I am intermittantly receiving the following error
> when I attempt to execute the two new reports:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> a.. Cannot create a connection to data source 'Blah2'.
> (rsErrorOpeningConnection) Get Online Help
> a.. SQL Server does not exist or access denied.
> The reports function everytime in Visual Studio. What is going on?
> Thanks
> Clint
>
>|||I thought that too. I have changed the datasource to be both shared and
custom. The username and password are correct. I am at a loss..
Especially seeing how it is so intermittant.
"SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> Sounds like a security issue. What does your datasource specify for a
> username/password?
> Steve
> "AshVsAOD" wrote:
> > Hi all,
> >
> > I have created about 40 reports that access our core ERP solution. All
> > these reports function perfectly. In the last day I have created 2 new
> > reports that access a different server/database thus a new shared
datasource
> > was created. However, I am intermittantly receiving the following error
> > when I attempt to execute the two new reports:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
Get
> > Online Help
> > a.. Cannot create a connection to data source 'Blah2'.
> > (rsErrorOpeningConnection) Get Online Help
> > a.. SQL Server does not exist or access denied.
> > The reports function everytime in Visual Studio. What is going on?
> >
> > Thanks
> > Clint
> >
> >
> >|||Not sure whether this will help. When u deploy the shared datsource to the
reportserver, try inputting the connection info and then run the report. I
had the same issue, and the connection trying the dba changed pointed to a
different instance..
Suresh
"AshVsAOD" wrote:
> I thought that too. I have changed the datasource to be both shared and
> custom. The username and password are correct. I am at a loss..
> Especially seeing how it is so intermittant.
> "SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
> news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> > Sounds like a security issue. What does your datasource specify for a
> > username/password?
> >
> > Steve
> >
> > "AshVsAOD" wrote:
> >
> > > Hi all,
> > >
> > > I have created about 40 reports that access our core ERP solution. All
> > > these reports function perfectly. In the last day I have created 2 new
> > > reports that access a different server/database thus a new shared
> datasource
> > > was created. However, I am intermittantly receiving the following error
> > > when I attempt to execute the two new reports:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> Get
> > > Online Help
> > > a.. Cannot create a connection to data source 'Blah2'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > a.. SQL Server does not exist or access denied.
> > > The reports function everytime in Visual Studio. What is going on?
> > >
> > > Thanks
> > > Clint
> > >
> > >
> > >
>
>sql
Error when executing DTS from sp_OA
What does this error mean? I get this error when i execute a DTS
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
time to refresh memory. Is this a bug?
Richard Ding wrote:
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
quote:|||That's what I'm thinking. I just don't want to have to restart all the
> What does this error mean? I get this error when i execute a DTS
> package using SP_OA from within T-SQL....
> I'm stumped.
>
> 0x80070008 Microsoft Data Transformation Services (DTS) Package
>
> Not enough storage
> is available to process this command.
>
time to refresh memory. Is this a bug?
Richard Ding wrote:
quote:
> This may indicate the memory size is not big enough to hold distributed
> query operations. You need to restart sql server to refresh memory.
> "Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
> news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
>
>
>
Error when executing DTS from sp_OA
What does this error mean? I get this error when i execute a DTS
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
> What does this error mean? I get this error when i execute a DTS
> package using SP_OA from within T-SQL....
> I'm stumped.
>
> 0x80070008 Microsoft Data Transformation Services (DTS) Package
>
> Not enough storage
> is available to process this command.
>|||That's what I'm thinking. I just don't want to have to restart all the
time to refresh memory. Is this a bug?
Richard Ding wrote:
> This may indicate the memory size is not big enough to hold distributed
> query operations. You need to restart sql server to refresh memory.
> "Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
> news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
>>What does this error mean? I get this error when i execute a DTS
>>package using SP_OA from within T-SQL....
>>I'm stumped.
>>
>>0x80070008 Microsoft Data Transformation Services (DTS) Package
>>
>> Not enough storage
>>is available to process this command.
>
>
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
> What does this error mean? I get this error when i execute a DTS
> package using SP_OA from within T-SQL....
> I'm stumped.
>
> 0x80070008 Microsoft Data Transformation Services (DTS) Package
>
> Not enough storage
> is available to process this command.
>|||That's what I'm thinking. I just don't want to have to restart all the
time to refresh memory. Is this a bug?
Richard Ding wrote:
> This may indicate the memory size is not big enough to hold distributed
> query operations. You need to restart sql server to refresh memory.
> "Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
> news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
>>What does this error mean? I get this error when i execute a DTS
>>package using SP_OA from within T-SQL....
>>I'm stumped.
>>
>>0x80070008 Microsoft Data Transformation Services (DTS) Package
>>
>> Not enough storage
>>is available to process this command.
>
>
Error when executing ALTER DATABASE statement
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
ALTER DATABASE <Your database name>
SET RECOVERY FULL
but I am getting the following error:
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajesh
It looks like a permission issue. Are you a member of sysadmin, dbcreator
or db_owner?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:4EC91AF7-A227-4A52-86F1-C4F0529A8AAD@.microsoft.com...
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
ALTER DATABASE <Your database name>
SET RECOVERY FULL
but I am getting the following error:
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajesh
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
ALTER DATABASE <Your database name>
SET RECOVERY FULL
but I am getting the following error:
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajesh
It looks like a permission issue. Are you a member of sysadmin, dbcreator
or db_owner?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:4EC91AF7-A227-4A52-86F1-C4F0529A8AAD@.microsoft.com...
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
ALTER DATABASE <Your database name>
SET RECOVERY FULL
but I am getting the following error:
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajesh
Error when executing ALTER DATABASE statement
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
RajeshIt looks like a permission issue. Are you a member of sysadmin, dbcreator
or db_owner?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:4EC91AF7-A227-4A52-86F1-C4F0529A8AAD@.microsoft.com...
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajesh
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
RajeshIt looks like a permission issue. Are you a member of sysadmin, dbcreator
or db_owner?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:4EC91AF7-A227-4A52-86F1-C4F0529A8AAD@.microsoft.com...
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajesh
Error when executing ALTER DATABASE statement
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
RajeshIt looks like a permission issue. Are you a member of sysadmin, dbcreator
or db_owner?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:4EC91AF7-A227-4A52-86F1-C4F0529A8AAD@.microsoft.com...
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajeshsql
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
RajeshIt looks like a permission issue. Are you a member of sysadmin, dbcreator
or db_owner?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:4EC91AF7-A227-4A52-86F1-C4F0529A8AAD@.microsoft.com...
Hi,
Problem description:
For taking transactional log backup of sql sever2005, I need to put database
into "RECOVERY FULL" mode. To do that I am using the following query in
master database as:
---
ALTER DATABASE <Your database name>
SET RECOVERY FULL
----
but I am getting the following error:
----
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ipr_qxcr239146' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
----
I am using windows authentication while connecting to SQL server and
database name is also correct in query. But not able to find where is the
problem
Please help me out.
Regards,
Rajeshsql
Error when executing a distributed query on a linked server
Hi,
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Error when executing a distributed query on a linked server
Hi,
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Friday, March 9, 2012
error when an OLEDB source points to an OLEDB destination.
Hi all,
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
my input columns firstname and lastname have 936 as codepages
but the destination columns lastname and firstname are set to 1252 codepages....and SSIS complains different codepages are used.
How do i change the defualt codepages to 936 for these two columns - firstname and lastname?
|||You may want to try 1) set DefaultCodePage property at OLEDBDest to 936. 2) set AlwaysUseDefaultCodePage property at OLEDBDest to true.
That should fix the design time issue you got. However, you also need to make sure you set the destination column types properly at your dest table so as to receive good result.
HTH
wenyang
|||
I notice even if i set as 936 for the codepage...the default still doesnt change...so i add a data conversion transform which converts the column to 936 and add it to the destination column at 936 for codepage....
Wednesday, March 7, 2012
Error using scalar valued function in Stored Procedure
Hi,
I am getting the following error msg while executing a stored procedure (getAllCollections) which uses a scalar valued function.
error msg:
Could not find server 'Select dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Stored procedure:
ALTER PROCEDURE [dbo].[getAllCollections]
(
@.pCids varchar(7500)
)
AS
declare @.cmd varchar(8000)
set @.cmd='Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (' +@.pCids + ')'
print @.cmd
exec @.cmd
Where as the 'dbo.getCollectionList' is the scalar valued function. If i execute the query separately as below it works fine:
Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (1234)
I believe i am doing some silly mistake somewhere. Any help will be much appreciated.
Thanks.
Saran:
I think all you need to do is change this:
exec @.cmd
to this:
exec ( @.cmd )
|||
Dave
When you do exec @.cmd it takes the contents of @.cmd as a stored procedure name. Since you had 3 periods in your string, it was thinking 'SELECT dbo' was a server name, and 'collection_id) as cList from CollectionT c WHERE c' as a database.
Like Dave said, use exec (@.cmd)
|||You should replace the dynamic SQL with a safer solution. You can use a TSQL table-valued function to split the list of IDs and use it like:
Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c
WHERE exists(
select * from split_str as s
where s.value = c.collection_id
)
Please see the link below for some solutions that show how to split a string into a table or list of values.
http://www.sommarskog.se/arrays-in-sql.html
|||Thanks a lot. It solved the issue. Silly me. :)
- Saran.
Friday, February 24, 2012
Error trying to execute a sp on a remote server with an SQL agent
Job 'bla' : Step 1, 'execute spbla' : Began Executing 2004-10-13 10:33:13
Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated by
SETUSER. [SQLSTATE 42000]
I get the above error when I try to execute a sp sitting on a remote server
with a SQL Server Agent job.
The sp executes fine if I run it from Query analyser.
Maybe the problem lies with how my linked server is set up, or why does it
try to connect to the remote server with NT security and using SQL
authentication?
Can I force it to connect with SQL Authentication ?
Rgds,
Nico
Nico,
Possibly 811031 PRB: SQL Server Agent Job Fails When the Job Uses a Linked
Server and
http://support.microsoft.com/?id=811031
John Gose
MCDBA
Microsoft SQL Server Support
This posting is provided "AS IS", with no warranties, and confers no rights
Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| Thread-Topic: Error trying to execute a sp on a remote server with an SQL
agent
| thread-index: AcSxAO/Fym/0adp3SrSXr6fzDw/fvQ==
| X-WBNR-Posting-Host: 168.210.90.181
| From: "=?Utf-8?B?Tmljbw==?=" <Nico@.discussions.microsoft.com>
| Subject: Error trying to execute a sp on a remote server with an SQL agent
| Date: Wed, 13 Oct 2004 01:45:05 -0700
| Lines: 15
|
<Snip of Header Stuff>
|
| Job 'bla' : Step 1, 'execute spbla' : Began Executing 2004-10-13 10:33:13
| Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated
by
| SETUSER. [SQLSTATE 42000]
|
| I get the above error when I try to execute a sp sitting on a remote
server
| with a SQL Server Agent job.
| The sp executes fine if I run it from Query analyser.
|
| Maybe the problem lies with how my linked server is set up, or why does
it
| try to connect to the remote server with NT security and using SQL
| authentication?
| Can I force it to connect with SQL Authentication ?
|
| Rgds,
| Nico
|
Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated by
SETUSER. [SQLSTATE 42000]
I get the above error when I try to execute a sp sitting on a remote server
with a SQL Server Agent job.
The sp executes fine if I run it from Query analyser.
Maybe the problem lies with how my linked server is set up, or why does it
try to connect to the remote server with NT security and using SQL
authentication?
Can I force it to connect with SQL Authentication ?
Rgds,
Nico
Nico,
Possibly 811031 PRB: SQL Server Agent Job Fails When the Job Uses a Linked
Server and
http://support.microsoft.com/?id=811031
John Gose
MCDBA
Microsoft SQL Server Support
This posting is provided "AS IS", with no warranties, and confers no rights
Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| Thread-Topic: Error trying to execute a sp on a remote server with an SQL
agent
| thread-index: AcSxAO/Fym/0adp3SrSXr6fzDw/fvQ==
| X-WBNR-Posting-Host: 168.210.90.181
| From: "=?Utf-8?B?Tmljbw==?=" <Nico@.discussions.microsoft.com>
| Subject: Error trying to execute a sp on a remote server with an SQL agent
| Date: Wed, 13 Oct 2004 01:45:05 -0700
| Lines: 15
|
<Snip of Header Stuff>
|
| Job 'bla' : Step 1, 'execute spbla' : Began Executing 2004-10-13 10:33:13
| Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated
by
| SETUSER. [SQLSTATE 42000]
|
| I get the above error when I try to execute a sp sitting on a remote
server
| with a SQL Server Agent job.
| The sp executes fine if I run it from Query analyser.
|
| Maybe the problem lies with how my linked server is set up, or why does
it
| try to connect to the remote server with NT security and using SQL
| authentication?
| Can I force it to connect with SQL Authentication ?
|
| Rgds,
| Nico
|
Error trying to execute a sp on a remote server with an SQL agent
Job 'bla' : Step 1, 'execute spbla' : Began Executing 2004-10-13 10:33:13
Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated by
SETUSER. [SQLSTATE 42000]
I get the above error when I try to execute a sp sitting on a remote server
with a SQL Server Agent job.
The sp executes fine if I run it from Query analyser.
Maybe the problem lies with how my linked server is set up, or why does it
try to connect to the remote server with NT security and using SQL
authentication?
Can I force it to connect with SQL Authentication ?
Rgds,
NicoNico,
Possibly 811031 PRB: SQL Server Agent Job Fails When the Job Uses a Linked
Server and
http://support.microsoft.com/?id=811031
John Gose
MCDBA
Microsoft SQL Server Support
This posting is provided "AS IS", with no warranties, and confers no rights
Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| Thread-Topic: Error trying to execute a sp on a remote server with an SQL
agent
| thread-index: AcSxAO/Fym/0adp3SrSXr6fzDw/fvQ==
| X-WBNR-Posting-Host: 168.210.90.181
| From: "examnotes" <Nico@.discussions.microsoft.com>
| Subject: Error trying to execute a sp on a remote server with an SQL agent
| Date: Wed, 13 Oct 2004 01:45:05 -0700
| Lines: 15
|
<Snip of Header Stuff>
|
| Job 'bla' : Step 1, 'execute spbla' : Began Executing 2004-10-13 10:33:13
| Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated
by
| SETUSER. [SQLSTATE 42000]
|
| I get the above error when I try to execute a sp sitting on a remote
server
| with a SQL Server Agent job.
| The sp executes fine if I run it from Query analyser.
|
| Maybe the problem lies with how my linked server is set up, or why does
it
| try to connect to the remote server with NT security and using SQL
| authentication?
| Can I force it to connect with SQL Authentication ?
|
| Rgds,
| Nico
|
Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated by
SETUSER. [SQLSTATE 42000]
I get the above error when I try to execute a sp sitting on a remote server
with a SQL Server Agent job.
The sp executes fine if I run it from Query analyser.
Maybe the problem lies with how my linked server is set up, or why does it
try to connect to the remote server with NT security and using SQL
authentication?
Can I force it to connect with SQL Authentication ?
Rgds,
NicoNico,
Possibly 811031 PRB: SQL Server Agent Job Fails When the Job Uses a Linked
Server and
http://support.microsoft.com/?id=811031
John Gose
MCDBA
Microsoft SQL Server Support
This posting is provided "AS IS", with no warranties, and confers no rights
Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| Thread-Topic: Error trying to execute a sp on a remote server with an SQL
agent
| thread-index: AcSxAO/Fym/0adp3SrSXr6fzDw/fvQ==
| X-WBNR-Posting-Host: 168.210.90.181
| From: "examnotes" <Nico@.discussions.microsoft.com>
| Subject: Error trying to execute a sp on a remote server with an SQL agent
| Date: Wed, 13 Oct 2004 01:45:05 -0700
| Lines: 15
|
<Snip of Header Stuff>
|
| Job 'bla' : Step 1, 'execute spbla' : Began Executing 2004-10-13 10:33:13
| Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated
by
| SETUSER. [SQLSTATE 42000]
|
| I get the above error when I try to execute a sp sitting on a remote
server
| with a SQL Server Agent job.
| The sp executes fine if I run it from Query analyser.
|
| Maybe the problem lies with how my linked server is set up, or why does
it
| try to connect to the remote server with NT security and using SQL
| authentication?
| Can I force it to connect with SQL Authentication ?
|
| Rgds,
| Nico
|
Sunday, February 19, 2012
error to executing job on Sql Server Enterprise Manager
... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error
string: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
e:\private.dbc. Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider Error: 171
(AB) Error string: [Microsoft][ODBC Visual FoxPro Driver]Cannot
open file e:\private.dbc. Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTS... Process Exit Code
4. The step failed.
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error
string: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
e:\private.dbc. Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider Error: 171
(AB) Error string: [Microsoft][ODBC Visual FoxPro Driver]Cannot
open file e:\private.dbc. Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTS... Process Exit Code
4. The step failed.
anyone know becaouse if i execute this job whit the Schedule package
dont work but i execute manually it's work ?
pls help me ...Initial guess would be that the account that is used for running SQL
Agent can not see e:\private.dbc... Double check what user account is
being used to run SQL Server service versus SQL Agent service and double
check permissions on the directory. Also, test what user accounts can
run the DTS package from Ent Man. It may be that the system or service
account can not run the scheduled job because service account does not
have access where as your account does via Ent Man.
Maurizio Amoroso wrote:
> ... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnError:
> DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error
> string: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
> e:\private.dbc. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 171
> (AB) Error string: [Microsoft][ODBC Visual FoxPro Driver]Cannot
> open file e:\private.dbc. Error source: Microsoft OLE DB
> Provider for ODBC Drivers Help file: Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTS... Process Exit Code
> 4. The step failed.
> anyone know becaouse if i execute this job whit the Schedule package
> dont work but i execute manually it's work ?
> pls help me ...|||"Maurizio Amoroso" <maurizio.amoroso@.mlink.it> wrote in message
news:933ecd1c.0401280623.498fed23@.posting.google.c om...
> ... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnError:
> DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error
> string: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
> e:\private.dbc. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 171
> (AB) Error string: [Microsoft][ODBC Visual FoxPro Driver]Cannot
> open file e:\private.dbc. Error source: Microsoft OLE DB
> Provider for ODBC Drivers Help file: Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTS... Process Exit Code
> 4. The step failed.
> anyone know becaouse if i execute this job whit the Schedule package
> dont work but i execute manually it's work ?
> pls help me ...
Check out this KB article:
http://support.microsoft.com/defaul...4&Product=sql2k
The account running the job probably doesn't have access to E:\ (NTFS
permissions, unknown drive mapping), and the article explains quite well how
to work out what context the job is running in.
Simon
Subscribe to:
Posts (Atom)