Is there any way to set the properties of one object depending on the
contents of a textbox or other object in a report? I'm trying to hide a
table column when the report is rendered if the footer value is zero.
Thanks in advance!You can use an expression on the visibility hidden properties of the header,
details & footer cell of the column you want to hide.
=IIF(ReportItems!colfooter1.Value is nothing,True,False)
where colfooter1 contains the value you are checking for zero.
hope that helps
Mark
"Boilin'Oil" wrote:
> Is there any way to set the properties of one object depending on the
> contents of a textbox or other object in a report? I'm trying to hide a
> table column when the report is rendered if the footer value is zero.
> Thanks in advance!
Showing posts with label referencing. Show all posts
Showing posts with label referencing. Show all posts
Wednesday, March 21, 2012
Referencing xs:schema within a SCHEMA COLLECTION
Hi all,
I have an XML schema that has an element declaration that references a W3C
XML Schema type:
<xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
. . .
<xs:element ref="xs:schema"/>
When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
error:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
The problem is obviously that the schema processor does not have a copy of
the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
Schema reference from the W3C and cleaning up the documentation nodes
(riddled with single quotes). The schema processor then gave me an "invalid
target namespace specified error.
Any ideas for having a node that contains a schema? How would the schema
for WSDL be accepted? Thanks,
- ErikHi Eric,
You don't need to import the schema for schemas (apparently it even causes
problems) because its built-in to SQL Server 2005. Just refer to it in your
schema. Like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
...
<xs:element name="yourtype" type="xs:string" />
</xs:schema>
Deriving types would work the same way.
Weird error message, I'll admit.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
> Hi all,
> I have an XML schema that has an element declaration that references a W3C
> XML Schema type:
> <xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
> . . .
> <xs:element ref="xs:schema"/>
> When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
> error:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> The problem is obviously that the schema processor does not have a copy of
> the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
> Schema reference from the W3C and cleaning up the documentation nodes
> (riddled with single quotes). The schema processor then gave me an
> "invalid
> target namespace specified error.
> Any ideas for having a node that contains a schema? How would the schema
> for WSDL be accepted? Thanks,
> - Erik
>|||Thanks Bob.
If I declare the schema type as xs:string, I'll have to escape XML markup in
the embedded schema node. The work-around I picked instead was to declare a
n
xs:any rather than an xs:element. I can tell the schema process to skip
processing on the embedded node that way.
But I really wanted to have SQL Server validate my documents fully,
including the embedded schemas. I'll think about that over the w
end and
let everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:
> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in you
r
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the wend
> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
>|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:
> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:
>
I have an XML schema that has an element declaration that references a W3C
XML Schema type:
<xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
. . .
<xs:element ref="xs:schema"/>
When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
error:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
The problem is obviously that the schema processor does not have a copy of
the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
Schema reference from the W3C and cleaning up the documentation nodes
(riddled with single quotes). The schema processor then gave me an "invalid
target namespace specified error.
Any ideas for having a node that contains a schema? How would the schema
for WSDL be accepted? Thanks,
- ErikHi Eric,
You don't need to import the schema for schemas (apparently it even causes
problems) because its built-in to SQL Server 2005. Just refer to it in your
schema. Like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
...
<xs:element name="yourtype" type="xs:string" />
</xs:schema>
Deriving types would work the same way.
Weird error message, I'll admit.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
> Hi all,
> I have an XML schema that has an element declaration that references a W3C
> XML Schema type:
> <xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
> . . .
> <xs:element ref="xs:schema"/>
> When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
> error:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> The problem is obviously that the schema processor does not have a copy of
> the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
> Schema reference from the W3C and cleaning up the documentation nodes
> (riddled with single quotes). The schema processor then gave me an
> "invalid
> target namespace specified error.
> Any ideas for having a node that contains a schema? How would the schema
> for WSDL be accepted? Thanks,
> - Erik
>|||Thanks Bob.
If I declare the schema type as xs:string, I'll have to escape XML markup in
the embedded schema node. The work-around I picked instead was to declare a
n
xs:any rather than an xs:element. I can tell the schema process to skip
processing on the embedded node that way.
But I really wanted to have SQL Server validate my documents fully,
including the embedded schemas. I'll think about that over the w
end andlet everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:
> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in you
r
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the w
end> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
>|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:
> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:
>
Referencing xs:schema within a SCHEMA COLLECTION
Hi all,
I have an XML schema that has an element declaration that references a W3C
XML Schema type:
<xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
. . .
<xs:element ref="xs:schema"/>
When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
error:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
The problem is obviously that the schema processor does not have a copy of
the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
Schema reference from the W3C and cleaning up the documentation nodes
(riddled with single quotes). The schema processor then gave me an "invalid
target namespace specified error.
Any ideas for having a node that contains a schema? How would the schema
for WSDL be accepted? Thanks,
- Erik
Hi Eric,
You don't need to import the schema for schemas (apparently it even causes
problems) because its built-in to SQL Server 2005. Just refer to it in your
schema. Like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
...
<xs:element name="yourtype" type="xs:string" />
</xs:schema>
Deriving types would work the same way.
Weird error message, I'll admit.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
> Hi all,
> I have an XML schema that has an element declaration that references a W3C
> XML Schema type:
> <xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
> . . .
> <xs:element ref="xs:schema"/>
> When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
> error:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> The problem is obviously that the schema processor does not have a copy of
> the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
> Schema reference from the W3C and cleaning up the documentation nodes
> (riddled with single quotes). The schema processor then gave me an
> "invalid
> target namespace specified error.
> Any ideas for having a node that contains a schema? How would the schema
> for WSDL be accepted? Thanks,
> - Erik
>
|||Thanks Bob.
If I declare the schema type as xs:string, I'll have to escape XML markup in
the embedded schema node. The work-around I picked instead was to declare an
xs:any rather than an xs:element. I can tell the schema process to skip
processing on the embedded node that way.
But I really wanted to have SQL Server validate my documents fully,
including the embedded schemas. I'll think about that over the weekend and
let everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:
> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in your
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>
|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...[vbcol=seagreen]
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the weekend
> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:
> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>
|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:
I have an XML schema that has an element declaration that references a W3C
XML Schema type:
<xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
. . .
<xs:element ref="xs:schema"/>
When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
error:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
The problem is obviously that the schema processor does not have a copy of
the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
Schema reference from the W3C and cleaning up the documentation nodes
(riddled with single quotes). The schema processor then gave me an "invalid
target namespace specified error.
Any ideas for having a node that contains a schema? How would the schema
for WSDL be accepted? Thanks,
- Erik
Hi Eric,
You don't need to import the schema for schemas (apparently it even causes
problems) because its built-in to SQL Server 2005. Just refer to it in your
schema. Like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
...
<xs:element name="yourtype" type="xs:string" />
</xs:schema>
Deriving types would work the same way.
Weird error message, I'll admit.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
> Hi all,
> I have an XML schema that has an element declaration that references a W3C
> XML Schema type:
> <xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
> . . .
> <xs:element ref="xs:schema"/>
> When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
> error:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> The problem is obviously that the schema processor does not have a copy of
> the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
> Schema reference from the W3C and cleaning up the documentation nodes
> (riddled with single quotes). The schema processor then gave me an
> "invalid
> target namespace specified error.
> Any ideas for having a node that contains a schema? How would the schema
> for WSDL be accepted? Thanks,
> - Erik
>
|||Thanks Bob.
If I declare the schema type as xs:string, I'll have to escape XML markup in
the embedded schema node. The work-around I picked instead was to declare an
xs:any rather than an xs:element. I can tell the schema process to skip
processing on the embedded node that way.
But I really wanted to have SQL Server validate my documents fully,
including the embedded schemas. I'll think about that over the weekend and
let everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:
> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in your
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>
|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...[vbcol=seagreen]
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the weekend
> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:
> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>
|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:
referencing value in subreport in calculated field on parent
I wish to reference the value in a subreport in a calculated field
elsewhere...
this subreport has one txtbox, no grid or any other objects.
is this possible?
what is the syntax...'
i.e. =mysubReport.mytxtbox.value...........
thanksOn Sep 28, 2:51 pm, r...@.mgk.com wrote:
> I wish to reference the value in a subreport in a calculated field
> elsewhere...
> this subreport has one txtbox, no grid or any other objects.
> is this possible?
> what is the syntax...'
> i.e. =mysubReport.mytxtbox.value...........
> thanks
You will need to create a dataset in the main report that accesses the
same data (i.e., query/stored procedure) that is used in the subreport
and add the calculation the same way (basically duplicating the
efforts). Then, in the main report, reference this dataset via an
aggregate expression, for example:
=Max(Fields!SomeFieldName.Value, "NewDataSetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Sep 29, 10:27 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 28, 2:51 pm, r...@.mgk.com wrote:
> > I wish to reference the value in a subreport in a calculated field
> > elsewhere...
> > this subreport has one txtbox, no grid or any other objects.
> > is this possible?
> > what is the syntax...'
> > i.e. =mysubReport.mytxtbox.value...........
> > thanks
> You will need to create a dataset in the main report that accesses the
> same data (i.e., query/stored procedure) that is used in the subreport
> and add the calculation the same way (basically duplicating the
> efforts). Then, in the main report, reference this dataset via an
> aggregate expression, for example:
> =Max(Fields!SomeFieldName.Value, "NewDataSetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
argh! i was afraid of that...sql
elsewhere...
this subreport has one txtbox, no grid or any other objects.
is this possible?
what is the syntax...'
i.e. =mysubReport.mytxtbox.value...........
thanksOn Sep 28, 2:51 pm, r...@.mgk.com wrote:
> I wish to reference the value in a subreport in a calculated field
> elsewhere...
> this subreport has one txtbox, no grid or any other objects.
> is this possible?
> what is the syntax...'
> i.e. =mysubReport.mytxtbox.value...........
> thanks
You will need to create a dataset in the main report that accesses the
same data (i.e., query/stored procedure) that is used in the subreport
and add the calculation the same way (basically duplicating the
efforts). Then, in the main report, reference this dataset via an
aggregate expression, for example:
=Max(Fields!SomeFieldName.Value, "NewDataSetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Sep 29, 10:27 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 28, 2:51 pm, r...@.mgk.com wrote:
> > I wish to reference the value in a subreport in a calculated field
> > elsewhere...
> > this subreport has one txtbox, no grid or any other objects.
> > is this possible?
> > what is the syntax...'
> > i.e. =mysubReport.mytxtbox.value...........
> > thanks
> You will need to create a dataset in the main report that accesses the
> same data (i.e., query/stored procedure) that is used in the subreport
> and add the calculation the same way (basically duplicating the
> efforts). Then, in the main report, reference this dataset via an
> aggregate expression, for example:
> =Max(Fields!SomeFieldName.Value, "NewDataSetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
argh! i was afraid of that...sql
Referencing to System.Data.SqlServerCe causes large files to deploy to Emulator
Hello everyone,
This is my first time posting here, I hope this questions has not been asked before. I tried to search for it but I came not with nothing.
Recreating the error :
I am using VS2005. I created a Pocket PC 2003 project.
I have downloaded the SQL Server Compact Edition and installed it. I get the System.Data.SqlServerCe.dll file from the installation directory.
I reference to that DLL using Add Reference in VS2005.
Build it. In the Bin folder, a long list of files suddenly appears.
System.data.dll
System.data.oracleClient.dll
system.web.dll
system.enterpriseservices.dll
system.enterpriseservices.wrapper.dll
system.transactions.dll
and the rest of your original files in Bin
The worst of it all, all of these files are deployed into the Emulator! Causing it to run out of memory and unable to deploy.
Something is not right here, I just cannot figure it out! If this happens, each mobile devices can hold one applications. Thats not the way it should be, right?
If you have solved this before, do help. I am at my wits end at the moment.
Thanking you in advance.
Sincerely,
Lasker
That means you have a reference to some desktop DLL(s) which brings pretty much entire desktop framework with it. It might be desktop version of System.Data.SqlServerCe.dll or it might be any other desktop DLL you’re referencing. You’d need to remove that reference and replace it with device one.
Referencing to System.Data.SqlServerCe causes large files to deploy to Emulator
Hello everyone,
This is my first time posting here, I hope this questions has not been asked before. I tried to search for it but I came not with nothing.
Recreating the error :
I am using VS2005. I created a Pocket PC 2003 project.
I have downloaded the SQL Server Compact Edition and installed it. I get the System.Data.SqlServerCe.dll file from the installation directory.
I reference to that DLL using Add Reference in VS2005.
Build it. In the Bin folder, a long list of files suddenly appears.
System.data.dll
System.data.oracleClient.dll
system.web.dll
system.enterpriseservices.dll
system.enterpriseservices.wrapper.dll
system.transactions.dll
and the rest of your original files in Bin
The worst of it all, all of these files are deployed into the Emulator! Causing it to run out of memory and unable to deploy.
Something is not right here, I just cannot figure it out! If this happens, each mobile devices can hold one applications. Thats not the way it should be, right?
If you have solved this before, do help. I am at my wits end at the moment.
Thanking you in advance.
Sincerely,
Lasker
That means you have a reference to some desktop DLL(s) which brings pretty much entire desktop framework with it. It might be desktop version of System.Data.SqlServerCe.dll or it might be any other desktop DLL you’re referencing. You’d need to remove that reference and replace it with device one.
referencing to a function..
I was created a function named aa.
It is possible to reference to it without using the dbo. prnounce ?
eg: select aa(), not select dbo.aa()
thanks,
ViktorThe owner name is required for scalar UDFs but not for table-valued UDFs.
Otherwise it could be difficult to identify from the syntax that a scalar
UDF was being referenced.
--
David Portas
SQL Server MVP
--
Tuesday, March 20, 2012
Referencing the temporary Dataset in Lookup control SQL
Does anyone know how to reference the ongoing dataset within the dataflow in sql?
So here is the scenario I have.
1) OLE DB SOURCE
2) Lookup a value and that column gets added to the dataset
3) Lookup another value but this time I would rather code the sql rather than select a referencing table. How do I reference the current dataset?
Thanks,
ChestonI'm not clear on what you are trying to do.
You can use SQL in a lookup transformation. You can even build a lookup table with literal values in SQL in that lookup transformation without hitting a table.|||Let me see if I can be more clear on what I am trying to do.
Table 1 - Ole DB source control
Lookup 1 - Reference another table and based on keys add a foreign key
Lookup 2 - I wish to perform a sql statement to update the newly added foreign key from lookup 1 to a default value if it is null. How do I reference the dataset?
So in the Lookup 2 I select SQL query:
Update dataset set foreign key = 0 where foreign key is null
Then continue on with the dataset with more lookups/transformations.
Does this help?
Cheston|||Use a derived column transformation to set that column to 0 if it is null.
Referencing the return value of a Subreport
I have a subreport inside my main report and it is working just fine. The subreport is called sbrCaseSummary.
In an expression of another txtbox in the main report I want to reference the value returned from the subreport. How do you reference it? I can't find out how that's done. Help.
I am having the exact same problem. Anybody have any ideas?
BJ
Referencing textbox values in expressions
I have a table displaying output from a query. I have cells "a" and "b"
that contain expressions performing calculations on fields in the query. I
would like to create an expression in cell "c" that performs a calculation
on the values in cells "a" and "b".
I can't seem to figure out how to reference the values of the textboxes in
the table. Even putting something as simple as "=a.text" or "=a.value"
returns an error that "a" is not declared ("a" being the name of the
textbox control).
Considering you need "Fields!" for referencing fields and "Parameters!" for
referencing parameters, I suspect that there is something comparable for
referencing the report controls, but I can't find any references to it in
the Books Online.
Does anyone know if this is possible, and if so how?
Thanks.One thing you might want to consider is adding a field to your dataset. When
you have the list of fields that you would drag and drop from when in the
layout tab, do a right mouse click, add. Pick calculated field. Add the
expressions for cell a and cell b. Now these are considered a field like
anything else. You can now create an expression in c that refers to field a
and field b. I find things more understandable and cleaner. Plus, let's say
you want to do a sum or something else on a grouping. No problem, it is just
a field like any other.
This is not real discoverable but in some situations it really makes things
simplier.
HTH,
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kursplat" <newsgroup-spam@.microsoft.com> wrote in message
news:%23$H72k1%23FHA.2676@.TK2MSFTNGP10.phx.gbl...
>I have a table displaying output from a query. I have cells "a" and "b"
> that contain expressions performing calculations on fields in the query.
> I
> would like to create an expression in cell "c" that performs a calculation
> on the values in cells "a" and "b".
> I can't seem to figure out how to reference the values of the textboxes in
> the table. Even putting something as simple as "=a.text" or "=a.value"
> returns an error that "a" is not declared ("a" being the name of the
> textbox control).
> Considering you need "Fields!" for referencing fields and "Parameters!"
> for
> referencing parameters, I suspect that there is something comparable for
> referencing the report controls, but I can't find any references to it in
> the Books Online.
> Does anyone know if this is possible, and if so how?
> Thanks.|||I would do that if I could (I've tried), but the expression in the
source textboxes are calling functions in an Assembly, which I've
learned the hard way can't be called from the query (it crashes Visual
Studio every time I try).
So, I'm still looking for a way to have the expression of one textbox in
a table reference the value of another textbox in that table.
Thanks for the suggestion, though.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> One thing you might want to consider is adding a field to your
> dataset. When you have the list of fields that you would drag and drop
> from when in the layout tab, do a right mouse click, add. Pick
> calculated field. Add the expressions for cell a and cell b. Now these
> are considered a field like anything else. You can now create an
> expression in c that refers to field a and field b. I find things more
> understandable and cleaner. Plus, let's say you want to do a sum or
> something else on a grouping. No problem, it is just a field like any
> other.
> This is not real discoverable but in some situations it really makes
> things simplier.
> HTH,
>|||I found it in another post. You use "ReportItems!" to reference the
controls on the report.
Kursplat <newsgroup-spam@.microsoft.com> wrote in news:eHD9dGB$FHA.1028
@.TK2MSFTNGP11.phx.gbl:
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.|||Hi Kursplat,
For a workaround, in your query, you can set up the calculations there,
then refer to them in cells a and b in your table. From there, you can add
another cell c, and use the Fields! method to call the values in cells a and
b and manipulate those values as you wish in an expression.
Does that make sense?
Cheers,
Daniel.
"Kursplat" wrote:
> I would do that if I could (I've tried), but the expression in the
> source textboxes are calling functions in an Assembly, which I've
> learned the hard way can't be called from the query (it crashes Visual
> Studio every time I try).
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.
> Thanks for the suggestion, though.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> > One thing you might want to consider is adding a field to your
> > dataset. When you have the list of fields that you would drag and drop
> > from when in the layout tab, do a right mouse click, add. Pick
> > calculated field. Add the expressions for cell a and cell b. Now these
> > are considered a field like anything else. You can now create an
> > expression in c that refers to field a and field b. I find things more
> > understandable and cleaner. Plus, let's say you want to do a sum or
> > something else on a grouping. No problem, it is just a field like any
> > other.
> >
> > This is not real discoverable but in some situations it really makes
> > things simplier.
> >
> > HTH,
> >
> >
>
that contain expressions performing calculations on fields in the query. I
would like to create an expression in cell "c" that performs a calculation
on the values in cells "a" and "b".
I can't seem to figure out how to reference the values of the textboxes in
the table. Even putting something as simple as "=a.text" or "=a.value"
returns an error that "a" is not declared ("a" being the name of the
textbox control).
Considering you need "Fields!" for referencing fields and "Parameters!" for
referencing parameters, I suspect that there is something comparable for
referencing the report controls, but I can't find any references to it in
the Books Online.
Does anyone know if this is possible, and if so how?
Thanks.One thing you might want to consider is adding a field to your dataset. When
you have the list of fields that you would drag and drop from when in the
layout tab, do a right mouse click, add. Pick calculated field. Add the
expressions for cell a and cell b. Now these are considered a field like
anything else. You can now create an expression in c that refers to field a
and field b. I find things more understandable and cleaner. Plus, let's say
you want to do a sum or something else on a grouping. No problem, it is just
a field like any other.
This is not real discoverable but in some situations it really makes things
simplier.
HTH,
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kursplat" <newsgroup-spam@.microsoft.com> wrote in message
news:%23$H72k1%23FHA.2676@.TK2MSFTNGP10.phx.gbl...
>I have a table displaying output from a query. I have cells "a" and "b"
> that contain expressions performing calculations on fields in the query.
> I
> would like to create an expression in cell "c" that performs a calculation
> on the values in cells "a" and "b".
> I can't seem to figure out how to reference the values of the textboxes in
> the table. Even putting something as simple as "=a.text" or "=a.value"
> returns an error that "a" is not declared ("a" being the name of the
> textbox control).
> Considering you need "Fields!" for referencing fields and "Parameters!"
> for
> referencing parameters, I suspect that there is something comparable for
> referencing the report controls, but I can't find any references to it in
> the Books Online.
> Does anyone know if this is possible, and if so how?
> Thanks.|||I would do that if I could (I've tried), but the expression in the
source textboxes are calling functions in an Assembly, which I've
learned the hard way can't be called from the query (it crashes Visual
Studio every time I try).
So, I'm still looking for a way to have the expression of one textbox in
a table reference the value of another textbox in that table.
Thanks for the suggestion, though.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> One thing you might want to consider is adding a field to your
> dataset. When you have the list of fields that you would drag and drop
> from when in the layout tab, do a right mouse click, add. Pick
> calculated field. Add the expressions for cell a and cell b. Now these
> are considered a field like anything else. You can now create an
> expression in c that refers to field a and field b. I find things more
> understandable and cleaner. Plus, let's say you want to do a sum or
> something else on a grouping. No problem, it is just a field like any
> other.
> This is not real discoverable but in some situations it really makes
> things simplier.
> HTH,
>|||I found it in another post. You use "ReportItems!" to reference the
controls on the report.
Kursplat <newsgroup-spam@.microsoft.com> wrote in news:eHD9dGB$FHA.1028
@.TK2MSFTNGP11.phx.gbl:
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.|||Hi Kursplat,
For a workaround, in your query, you can set up the calculations there,
then refer to them in cells a and b in your table. From there, you can add
another cell c, and use the Fields! method to call the values in cells a and
b and manipulate those values as you wish in an expression.
Does that make sense?
Cheers,
Daniel.
"Kursplat" wrote:
> I would do that if I could (I've tried), but the expression in the
> source textboxes are calling functions in an Assembly, which I've
> learned the hard way can't be called from the query (it crashes Visual
> Studio every time I try).
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.
> Thanks for the suggestion, though.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> > One thing you might want to consider is adding a field to your
> > dataset. When you have the list of fields that you would drag and drop
> > from when in the layout tab, do a right mouse click, add. Pick
> > calculated field. Add the expressions for cell a and cell b. Now these
> > are considered a field like anything else. You can now create an
> > expression in c that refers to field a and field b. I find things more
> > understandable and cleaner. Plus, let's say you want to do a sum or
> > something else on a grouping. No problem, it is just a field like any
> > other.
> >
> > This is not real discoverable but in some situations it really makes
> > things simplier.
> >
> > HTH,
> >
> >
>
Referencing Textbox Values
Is there a way to reference a value from a textbox in a matrix? In other words I want to pull the value in the textbox that is the column header into a cell in the matrix under certain conditions.
You can try this:
=ReportItems!textbox3.value
or
You should be able to share a value in a hidden textbox using code.
Create a hidden textbox. Add the expression =Code.SetValue(mytextboxcontent) to the textbox.
Add the expression =Code.GetValue() to the matrix.
Create 2 functions
public myval as object
function setValue(value as object) as object
myval = value
return value
end function
function getValue() as object
return myval
end function
Referencing textbox name in expression
I would like to reference a textbox name in an expression. When I write the
following expression, I get an error saying that the textbox name is
"private":
=ReportItems!txtPatientName.Name
Error: The value expression for the textbox â'textbox1â' contains an error:
[BC30390]
'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem.Private
ReadOnly Property Name() As String' is not accessible in this context because
it is 'Private'.I should probably mention that I am trying to ultimately reference the NAME
of the column/field returned from the stored procedure. Since that seems
impossible, I was hoping to set the name of the textbox to the name of the
column/field and then reference that. For example:
SELECT AdmitDate as [AdmitDate],
DischargeDate as [DischargeDate]
FROM Table_Name
In the report I would set the name of the textbox that displays the
AdmitDate field to "AdmitDate" so that I could reference the name of the
column/field being returned by the procedure with this statement:
=ReportItems!AdmitDate.Name
Is there any way to reference the name of the field/column being returned by
the procedure or the name of a textbox?
"mssarahlynn" wrote:
> I would like to reference a textbox name in an expression. When I write the
> following expression, I get an error saying that the textbox name is
> "private":
> =ReportItems!txtPatientName.Name
> Error: The value expression for the textbox â'textbox1â' contains an error:
> [BC30390]
> 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem.Private
> ReadOnly Property Name() As String' is not accessible in this context because
> it is 'Private'.
>
>
following expression, I get an error saying that the textbox name is
"private":
=ReportItems!txtPatientName.Name
Error: The value expression for the textbox â'textbox1â' contains an error:
[BC30390]
'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem.Private
ReadOnly Property Name() As String' is not accessible in this context because
it is 'Private'.I should probably mention that I am trying to ultimately reference the NAME
of the column/field returned from the stored procedure. Since that seems
impossible, I was hoping to set the name of the textbox to the name of the
column/field and then reference that. For example:
SELECT AdmitDate as [AdmitDate],
DischargeDate as [DischargeDate]
FROM Table_Name
In the report I would set the name of the textbox that displays the
AdmitDate field to "AdmitDate" so that I could reference the name of the
column/field being returned by the procedure with this statement:
=ReportItems!AdmitDate.Name
Is there any way to reference the name of the field/column being returned by
the procedure or the name of a textbox?
"mssarahlynn" wrote:
> I would like to reference a textbox name in an expression. When I write the
> following expression, I get an error saying that the textbox name is
> "private":
> =ReportItems!txtPatientName.Name
> Error: The value expression for the textbox â'textbox1â' contains an error:
> [BC30390]
> 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem.Private
> ReadOnly Property Name() As String' is not accessible in this context because
> it is 'Private'.
>
>
Referencing Text Boxes from other Text Boxes
Is it possible to reference the value of a text box (txtField1) in
another text box's expression?
Example:
txtField1 Expression (txtField1 is in Group1 footer)
=Sum(WidgetsCount)
txtField2 Expression (txtField2 is in Group2 footer)
=Sum(WidgetsCount)
txtResultLbl Expression (txtResultLbl is in Group1 header)
=IIf(txtField1 > txtField2, "Greater", "Less")
I am not sure if the cell needs to be referenced through the properties
of the table in which all the cells reside. (Table1)
Your help is apprectiated,
sturgisYou are making a conceptual mistake. You do not want to reference the text
box. For this sort of thing you need to be referencing the field not the
text box. For instance, if you have a group (for a subtotal let's say) then
you put this in:
= sum(Fields!Fieldname1.Value)
On the table footer you can put:
=Sum(Fields!Fieldname1.Value) or you can put
=Sum(Fields!Fieldname1.Value,"Datasetname")
What the textbox is called matters not at all.
Hope that helps.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128357572.656545.186070@.f14g2000cwb.googlegroups.com...
> Is it possible to reference the value of a text box (txtField1) in
> another text box's expression?
> Example:
> txtField1 Expression (txtField1 is in Group1 footer)
> =Sum(WidgetsCount)
> txtField2 Expression (txtField2 is in Group2 footer)
> =Sum(WidgetsCount)
> txtResultLbl Expression (txtResultLbl is in Group1 header)
> =IIf(txtField1 > txtField2, "Greater", "Less")
> I am not sure if the cell needs to be referenced through the properties
> of the table in which all the cells reside. (Table1)
> Your help is apprectiated,
> sturgis
>|||I don't believe I am making a conceptual mistake since this can easily
be done in Microsoft Access reports.
I am creating a header label that is conditioned upon the totals from
two different groups. You would never expect the group closest to the
detail to have a value less than the next group up but, in some cases,
the group totals could be the same. When they are the same, I don't
want to show the group identifier for the group closest to the detail,
only the identifier for the outer group. This cannot be done by
referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
always equal.
Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Hdr2 - NOT USED
Ftr2 (Visible Condition set to equality of DivisionTotals and
AreaTotals)
Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Microsoft Access allows you to reference the text boxes in footers from
expressions in text boxes within headers. Reporting Services has taken
a step backwards if it cannot also be done.
Thanks,
sturgis|||Access and RS are different. You can access textboxes but for what you are
trying to do you should be creating your formulaes based on the field
values. Everything you want to do is possible. RS can do some things Access
can't and Access can do some things that RS can't. Regardless, even if the
end result is the same that does not mean they do it the same way.
Again, you are missing a concept. In BOL search on the phrase expressions.
There will be 5 titles to select from. I suggest reading all 5. They show
doing what you are interested in doing.
If you do as I suggest it will help you make the leap from Access to RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128360396.740290.263370@.g14g2000cwa.googlegroups.com...
>I don't believe I am making a conceptual mistake since this can easily
> be done in Microsoft Access reports.
> I am creating a header label that is conditioned upon the totals from
> two different groups. You would never expect the group closest to the
> detail to have a value less than the next group up but, in some cases,
> the group totals could be the same. When they are the same, I don't
> want to show the group identifier for the group closest to the detail,
> only the identifier for the outer group. This cannot be done by
> referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
> always equal.
> Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Hdr2 - NOT USED
> Ftr2 (Visible Condition set to equality of DivisionTotals and
> AreaTotals)
> Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Microsoft Access allows you to reference the text boxes in footers from
> expressions in text boxes within headers. Reporting Services has taken
> a step backwards if it cannot also be done.
> Thanks,
> sturgis
>|||The nice part of Access reporting was that you didn't have to replicate
the formula in any expression that used it. You merely referenced the
text box with the initial formula. This is better programming practice.
If the formula changes, it changes in all expressions that reference
it. You don't have to try to remember where all the occurances of your
formula are (albeit, there should not be that many).
I have read the 5 titles to which you referred and found no reference
to what I am doing.
Thanks,
sturgis|||I was able to access the text boxes through the ReportItems collection.
Thanks,
sturgis
another text box's expression?
Example:
txtField1 Expression (txtField1 is in Group1 footer)
=Sum(WidgetsCount)
txtField2 Expression (txtField2 is in Group2 footer)
=Sum(WidgetsCount)
txtResultLbl Expression (txtResultLbl is in Group1 header)
=IIf(txtField1 > txtField2, "Greater", "Less")
I am not sure if the cell needs to be referenced through the properties
of the table in which all the cells reside. (Table1)
Your help is apprectiated,
sturgisYou are making a conceptual mistake. You do not want to reference the text
box. For this sort of thing you need to be referencing the field not the
text box. For instance, if you have a group (for a subtotal let's say) then
you put this in:
= sum(Fields!Fieldname1.Value)
On the table footer you can put:
=Sum(Fields!Fieldname1.Value) or you can put
=Sum(Fields!Fieldname1.Value,"Datasetname")
What the textbox is called matters not at all.
Hope that helps.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128357572.656545.186070@.f14g2000cwb.googlegroups.com...
> Is it possible to reference the value of a text box (txtField1) in
> another text box's expression?
> Example:
> txtField1 Expression (txtField1 is in Group1 footer)
> =Sum(WidgetsCount)
> txtField2 Expression (txtField2 is in Group2 footer)
> =Sum(WidgetsCount)
> txtResultLbl Expression (txtResultLbl is in Group1 header)
> =IIf(txtField1 > txtField2, "Greater", "Less")
> I am not sure if the cell needs to be referenced through the properties
> of the table in which all the cells reside. (Table1)
> Your help is apprectiated,
> sturgis
>|||I don't believe I am making a conceptual mistake since this can easily
be done in Microsoft Access reports.
I am creating a header label that is conditioned upon the totals from
two different groups. You would never expect the group closest to the
detail to have a value less than the next group up but, in some cases,
the group totals could be the same. When they are the same, I don't
want to show the group identifier for the group closest to the detail,
only the identifier for the outer group. This cannot be done by
referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
always equal.
Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Hdr2 - NOT USED
Ftr2 (Visible Condition set to equality of DivisionTotals and
AreaTotals)
Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Microsoft Access allows you to reference the text boxes in footers from
expressions in text boxes within headers. Reporting Services has taken
a step backwards if it cannot also be done.
Thanks,
sturgis|||Access and RS are different. You can access textboxes but for what you are
trying to do you should be creating your formulaes based on the field
values. Everything you want to do is possible. RS can do some things Access
can't and Access can do some things that RS can't. Regardless, even if the
end result is the same that does not mean they do it the same way.
Again, you are missing a concept. In BOL search on the phrase expressions.
There will be 5 titles to select from. I suggest reading all 5. They show
doing what you are interested in doing.
If you do as I suggest it will help you make the leap from Access to RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128360396.740290.263370@.g14g2000cwa.googlegroups.com...
>I don't believe I am making a conceptual mistake since this can easily
> be done in Microsoft Access reports.
> I am creating a header label that is conditioned upon the totals from
> two different groups. You would never expect the group closest to the
> detail to have a value less than the next group up but, in some cases,
> the group totals could be the same. When they are the same, I don't
> want to show the group identifier for the group closest to the detail,
> only the identifier for the outer group. This cannot be done by
> referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
> always equal.
> Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Hdr2 - NOT USED
> Ftr2 (Visible Condition set to equality of DivisionTotals and
> AreaTotals)
> Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Microsoft Access allows you to reference the text boxes in footers from
> expressions in text boxes within headers. Reporting Services has taken
> a step backwards if it cannot also be done.
> Thanks,
> sturgis
>|||The nice part of Access reporting was that you didn't have to replicate
the formula in any expression that used it. You merely referenced the
text box with the initial formula. This is better programming practice.
If the formula changes, it changes in all expressions that reference
it. You don't have to try to remember where all the occurances of your
formula are (albeit, there should not be that many).
I have read the 5 titles to which you referred and found no reference
to what I am doing.
Thanks,
sturgis|||I was able to access the text boxes through the ReportItems collection.
Thanks,
sturgis
Referencing tables on a remote server
I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?
Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?
Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>
Referencing tables on a remote server
I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>I have multiple stored procedures which include queries
that reference tables
>on a remote, linked server. For example,
>select id, name from remoteserver.db.dbo.table1
>In order to speed up my queries and avoids lockouts while
data is being
>inserted into those remote tables, I would like to use
the WITH (NOLOCK)
>clause. However, i cannot use that on remote tables. I
thought of creating
>local views for each of those remote tables, for example,
>create view v_table1 as select id, name from
remoteserver.db.dbo.table1
>and then using that view instead of the original table in
my queries. Would
>this be faster? Most of my queries involve multiple
tables. The remote
>tables do not have primary keys, though they do have
multiple indexes.
>I should add that I do not have permission to change
anything on the remote
>server. That includes adding stored procedures to that
remote database or
>adding indexes or keys.
>Also, the remote database is massive, multi-terrabytes
massive. The tables
>I am interested in have millions of rows.
>In a related question, when you create a view that
references tables on a
>remote server, how quickly/often does it get updated when
the data in those
>tables gets changed? Am I creating a CPU burden on the
remote server or only
>my own? My application does not insert/update/delete
data on the remote
>server, only queries it. On the remote server, however,
there is a great
>deal of data insertion going on throughout the day and
night.
>Any suggestions?
>.
>
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>I have multiple stored procedures which include queries
that reference tables
>on a remote, linked server. For example,
>select id, name from remoteserver.db.dbo.table1
>In order to speed up my queries and avoids lockouts while
data is being
>inserted into those remote tables, I would like to use
the WITH (NOLOCK)
>clause. However, i cannot use that on remote tables. I
thought of creating
>local views for each of those remote tables, for example,
>create view v_table1 as select id, name from
remoteserver.db.dbo.table1
>and then using that view instead of the original table in
my queries. Would
>this be faster? Most of my queries involve multiple
tables. The remote
>tables do not have primary keys, though they do have
multiple indexes.
>I should add that I do not have permission to change
anything on the remote
>server. That includes adding stored procedures to that
remote database or
>adding indexes or keys.
>Also, the remote database is massive, multi-terrabytes
massive. The tables
>I am interested in have millions of rows.
>In a related question, when you create a view that
references tables on a
>remote server, how quickly/often does it get updated when
the data in those
>tables gets changed? Am I creating a CPU burden on the
remote server or only
>my own? My application does not insert/update/delete
data on the remote
>server, only queries it. On the remote server, however,
there is a great
>deal of data insertion going on throughout the day and
night.
>Any suggestions?
>.
>
referencing system/CLR assemblies in SQLCLR
Throughout the course of this book and even before it I have come across conflicting information regarding how SQLCLR attempts to resolve system/CLR assembly references. For example, prior to my latest read thourgh April BOL 2005, I thought SQLCLR attempted to resolve these references implicity for you via the local machine's GAC. Yet here is what I found while trying to help another person in this forum yesterday in BOL...
Assembly Validation
SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:
The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.
The set of system assemblies it references is one of the following supported assemblies in SQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, and System.Data.SqlXml.dll. Other system assemblies can be referenced, but they must be explicitly registered in the database.
For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:
The assembly code should be type-safe. Type safety is established by running the common language runtime verifier against the assembly.
The assembly should not contain any static data members in its classes unless they are marked as read-only.
The classes in the assembly cannot contain finalizer methods.
The classes or methods of the assembly should be annotated only with allowed code attributes. For more information, see Custom Attributes for CLR Routines.
Besides the previous checks that are performed when CREATE ASSEMBLY executes, there are additional checks that are performed at execution time of the code in the assembly:
Calling certain Microsoft .NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.
For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.
COULD SOMEONE PLEASE GIVE ME THE DEFENITE ANSWER ON HOW THIS WORKS!
And as a side note even if there is nothing more "to it" then these few lines in the CREATE ASSEMBLY topic in BOL, I think both this forum and the others prove that more information needs to be in BOL regarding this topic. How it relates to the subset of the .Net framework we have access to, HPAs, and CAS permissions.|||Hi Derek,
Where have you found conflicting statements on the SQL CLR assembly loading process? If it's in BOL or in another MS reference, let me know or file a bug on connect.microsoft.com so it can be corrected.
The passage from BOL above is accurate: only the system assemblies on the "Supported .NET Framework Libraries" list can be implicitly referenced from the GAC; all others must be created explicitly through CREATE ASSEMBLY.
I think that CAS and HPA are also covered pretty thoroughly in BOL. "CLR Integration Code Access Security" describes all the permissions available under each permission set and you can use a tool such as PermView to see which ones are requested by your code. "Host Protection Attributes and CLR Integration Programming" provides the same treatment for HPAs, along with listing every class or method in the supported system assemblies that can't be used in SAFE or EXTERNAL_ACCESS.
Actually, I just noticed that "CLR Integration Programming Model Restrictions" does contain an error: the custom attributes it lists are not explicitly disallowed in the UNSAFE permission set (although they are still not recommended and may not work as expected). I'll file a bug to get this fixed.
Steven
Referencing system message types
I need to handle system messages being put on my queue.
I have 2 ways of doing this, by message_type_id or by name.
As BOL only mentions names of message types I assume the only gauranteed method is the "by name" option. or can I guarantee the values of message_type_ids, the reason I thought the latter was possible was that Remus included ids in his recent performance blog post.
Thanks
The safest way is to look up the id by name at the procedure start and then use the id. The system message type ids are the same on all deployed SQL 2005 SKUs/versions, but we cannot guarantee this for future versions.
HTH,
~ Remus
referencing subreport value
What is the syntax to reference a subreports returning value?
I need to subtotal values returning from a subreport, but I don't know how to reference it.
Thanks,
Hi,
From your description, it seems that you want to reference a subreport in the parent report, right?
Based on my knowledge, you cannot reference a subreport from the parentreport. I suggest that you should have a DataSet in the parent report which achieves the summing work from the datasource or just create a subreport with the subtotals you need.
Thanks.
|||
I found out a way to this with a subreport. Nevermind.
Neither of your suggestions will work because the data in my subreport is referencing a child table.
I cannot use a subreport to do the summing because I need to attach these values when a group changes.
Any other ideas?
Referencing SQLDMO in UNSAFE assembly
I created a SQL Server Project in VS 2005, and tried to add a reference to SQLDMO Object dll. But the reference --> add reference, does not allow it. It does not work, even if I set the Assembly Permission Level to UNAFE/EXTERNAL ACCESS.
Reason I'm trying to do this:
We have a SQL 2000 stored procedure that uses SQLDMO using sp_OACreate to BCP files to database. We are converting to 2005 and because of SOX restrictions, I'm trying to replace the sp_OACreate part with external stored procedure written in C#.
According to BOL, I thought atleast UNSAFE should support this, but it seems not.
Probably, I'm expecting too much..
Your help is greatly appreciated.
Thanks
Thanks to Vineet Rao's posting to one of of the answers on this forum, to a similar problem.
I registered "signed" InterOp.SQLDMO.dll (which is obtained by referecing to a regular project) , as UNSAFE, and it appeared in the Reference section of SQL Server Project.
Thanks
Baskar
Referencing SQLDataSource In a Sub
I have a SqlDataSource - The select statementselects the DB row with an ID that equals a querystring value... So Iknow I am only selecting one row..
Now I want to create a littleSub that grabs any field(s) of my choice and then I can assign thevalue of it to a textbox on my page or a variable if I need to... Anyidea how I actually reference the fields via the SQLDataSource in asub?
I see you can reference parameters using..
SqlDataSource1.SelectParameters()
I was hoping I could use something similar to get the actual DB fields like the below...
SqlDataSource1.Select("MyDataBaseField").Value = TextBox.Text
Can anyone help please??
Thanks
Hmmm I feared that... As I didn't want to use a gridview of details veiw etc...
I'll have a re-think... Thanks
I wrote a blog post about doing what I think you need:
http://peterkellner.net/2006/10/27/smalllistaccessimproved/
just use sqldatasource instead of objectdatasource and the same technique should work.
|||Thanks Peter... I think I was trying to do something that just wasnt possible... I have had a re-think... Thanks for the link though|||If you don't want to use the data controls, then don't use the sqldatasource.
In your sub, just do your own sqlconnection and command and get your data and assign the values to your controls...
Dim conn as new sqlconnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectString)
dim cmd as new sqlcommand("SELECT field1,field2 FROM MyTable WHEREID=@.ID",conn)
cmd.parameters.add("@.ID",sqldbtype.integer).value=request.querystring("ID")
dim dr as datareader
conn.open
dr=cmd.executereader
dr.read
textbox1.text=dr("field1")
textbox2.text=dr("field2")
dr.close
conn.close
|||
Hi Motley,,,, Thanks for the advice
http://forums.asp.net/thread/1468801.aspx
Any ideas? Seems to work fine if I don't use the connection string from my web.config... Even though I use the connection for loads of other pages (SQLDataSources) and its fine? Any ideas? Its driving me nuts
Subscribe to:
Posts (Atom)