"replace value of" and enum

I've created XML SCHEMA COLLECTION AS
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order" nillable="true" type="Order" />
<xs:complexType name="Order">
<xs:sequence>
<xs:element minOccurs="1" maxOccurs="1" name="Status"
type="OrderStatus" />
</xs:sequence>
</xs:complexType>
<xs:simpleType name="OrderStatus">
<xs:restriction base="xs:string">
<xs:enumeration value="New" />
<xs:enumeration value="Pending" />
<xs:enumeration value="Complete" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
Now I'd like to update OrderStatus:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with "Expired"')
I get:
Msg 2247, Level 16, State 1, Procedure Orders_Select
XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
which is not a subtype of the expected type "OrderStatus".
The below does not work either:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
OrderStatus("Expired")')
How do I make this work?
ThanksNever mind, ("Expired" cast as OrderStatus) does the trick.
"Chris Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:%23AHmjrHKGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've created XML SCHEMA COLLECTION AS
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema elementFormDefault="qualified"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Order" nillable="true" type="Order" />
> <xs:complexType name="Order">
> <xs:sequence>
> <xs:element minOccurs="1" maxOccurs="1" name="Status"
> type="OrderStatus" />
> </xs:sequence>
> </xs:complexType>
> <xs:simpleType name="OrderStatus">
> <xs:restriction base="xs:string">
> <xs:enumeration value="New" />
> <xs:enumeration value="Pending" />
> <xs:enumeration value="Complete" />
> </xs:restriction>
> </xs:simpleType>
> </xs:schema>
>
> Now I'd like to update OrderStatus:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> "Expired"')
> I get:
> Msg 2247, Level 16, State 1, Procedure Orders_Select
> XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
> which is not a subtype of the expected type "OrderStatus".
> The below does not work either:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> OrderStatus("Expired")')
>
> How do I make this work?
>
> Thanks
>
>
>

"replace value of" and enum

I've created XML SCHEMA COLLECTION AS
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order" nillable="true" type="Order" />
<xs:complexType name="Order">
<xs:sequence>
<xs:element minOccurs="1" maxOccurs="1" name="Status"
type="OrderStatus" />
</xs:sequence>
</xs:complexType>
<xs:simpleType name="OrderStatus">
<xs:restriction base="xs:string">
<xs:enumeration value="New" />
<xs:enumeration value="Pending" />
<xs:enumeration value="Complete" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
Now I'd like to update OrderStatus:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with "Expired"')
I get:
Msg 2247, Level 16, State 1, Procedure Orders_Select
XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
which is not a subtype of the expected type "OrderStatus".
The below does not work either:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
OrderStatus("Expired")')
How do I make this work?
Thanks
Never mind, ("Expired" cast as OrderStatus) does the trick.
"Chris Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:%23AHmjrHKGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've created XML SCHEMA COLLECTION AS
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema elementFormDefault="qualified"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Order" nillable="true" type="Order" />
> <xs:complexType name="Order">
> <xs:sequence>
> <xs:element minOccurs="1" maxOccurs="1" name="Status"
> type="OrderStatus" />
> </xs:sequence>
> </xs:complexType>
> <xs:simpleType name="OrderStatus">
> <xs:restriction base="xs:string">
> <xs:enumeration value="New" />
> <xs:enumeration value="Pending" />
> <xs:enumeration value="Complete" />
> </xs:restriction>
> </xs:simpleType>
> </xs:schema>
>
> Now I'd like to update OrderStatus:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> "Expired"')
> I get:
> Msg 2247, Level 16, State 1, Procedure Orders_Select
> XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
> which is not a subtype of the expected type "OrderStatus".
> The below does not work either:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> OrderStatus("Expired")')
>
> How do I make this work?
>
> Thanks
>
>
>

"Object reference not set to an instance of an object" When Retrieving Data/Schema in Desi

Hi There,

This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.
I have a project that I was working on with this ms access db and using sql controls, everything was working just fine
since one day I started getting "Object reference not set to an instance of an object" messages when I try to design
a query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lot
of work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado components
but nothing seems to fix it, did this ever happen to any of you guys?

any tip is really appreciated

thanks a lot

Hi faguiar,

You wouldn't have to use DataSet Typed. You should have to use BusinessEntities.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

but it used to work fine before, I'm pretty sure this is a nasty ide bug that needs a hack but I can't find a solution myself.

thank you

"Not enough server storage is available to process this command"

Hi,
We have several customers using replication on similar databases - all with the same schema, published using essentially the same script. One of them has been getting the "Not enough server storage" message when they try to access any network shares on th
e server with the published database on it. This starts happening between 24-36 hours after they first run the snapshot agent.
The application log contains an entry saying "The process could not create file \\[SERVER]\REPLDATA\unc",
and the system log has one saying "The server was unable to allocate from the system paged pool because the pool was empty".
When they turn off the replication everything is fine.
I suggested increasing the IRPStackSize value in the registry, as this was mentioned in the closest article I could find on MSDN/KB (something about Norton AntiVirus), but this didn't make any difference.
This is SQL Server 2000 running on Windows 2000 Server, all with up-to-date service packs.
Any ideas or suggestions would be appreciated.
Mark
from your earlier post i understand that the snapshot creation part gives
out this message.
If the error occurs while the snapshot is being generated, check the
following"
1. Check if the sql server service account has appropriate NTFS and share
level permissions to write to the repldata directory.
2. Check if there is enough storage space on the disk for creating files
in repldata directory.
3. Check if you can run xp_cmdshell "dir
\\distributorname\c$\mssql\repldata"
and dir \\distributor\c$\mssql\repldata from command prompt.
If the error occurs after the snapshot has been completely generated, Refer
-
* KB : 285089 IRPStackSize Parameter in Windows 2000
http://support.microsoft.com/?id=285089
Pls do let me know of the SQL server version (build number). and the event
id's of the event log messages.
Does this message stop replication? are the subscribers and publisher in
sync? - Just to confirm if this message is obstructing replication from
being in sync.

"dbo" schema permissions question.

I have some ASP.NET 2.0 code executing
SqlDependency.Start(connectionstring) to a SQL 2005 database and I am
getting the following error:
System.Data.SqlClient.SqlException: The specified schema name "dbo"
either does not exist or you do not have permission to use it.
As a general SQL 2005 Security question, can anyone please inform me as
to how i would give my user permissions to the "dbo" schema? At
present the user has "dbo" set up as it's default schema, but I am not
sure what that does. Also my code works fine if I give the user
sysadmin rights.
Thanks,
ChrisHi Chris,
It would probably be better to have the user in SqlDependency.Start have a
different default schema than dbo (create schema foo authorization youruser;
alter user youruser with default_schema = foo). Otherwise you would have to
give it access to create objects in the dbo schema. Once you've done that,
the user needs CREATE SERVICE, CREATE QUEUE, and CREATE PROCEDURE. Because
SERVICE is at database scope (not schema scope) its queue or procedure
that's causing the error. Don't forget GRANT SUBSCRIBE QUERY NOTIFICATION to
the user executing the commands too.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"chris" <chris@.cubed-c.com> wrote in message
news:1138483703.893069.160850@.g43g2000cwa.googlegroups.com...
>I have some ASP.NET 2.0 code executing
> SqlDependency.Start(connectionstring) to a SQL 2005 database and I am
> getting the following error:
> System.Data.SqlClient.SqlException: The specified schema name "dbo"
> either does not exist or you do not have permission to use it.
> As a general SQL 2005 Security question, can anyone please inform me as
> to how i would give my user permissions to the "dbo" schema? At
> present the user has "dbo" set up as it's default schema, but I am not
> sure what that does. Also my code works fine if I give the user
> sysadmin rights.
> Thanks,
> Chris
>|||Bob,
First of all thanks for your article about this subject back when it
was still in beta.
Your solution worked! Very easy, thanks for your help.
Chris

multiple schema report

tnx in advance for any help.
I have a multiple enterprise system in which every enterprise is being
modeled as a schema in the DB. This means that schemas are identical in
structure and when you connect to one schema, you are connecting to one
logical enterprise.
Now, I need to write a bunch of reports with the ability to connect
indiferently to on Enterprise or another (the user selects against which
Enterprise he wants to run the report to).
RS allows to define one or more Datasets for every report. But that has to
be an SQL instruction with schema qualifying "hardcoded" for every table you
use.
I've thought about dynamic SQL inside a stored procedure, and the enterprise
(schema) being passed as a parameter, but that's not such an elegant
solution.
So any suggestion will be gratefully welcome...
regardsIn RS 2005 you can base a data source on an expression. This sounds like
your scenario is exactly what this feature was put in for.
From Books Online:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources shares the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edmundo J. Davila" <edavila@.monisa.com> wrote in message
news:19A56F20-96FE-4751-846F-9E8B2D5CF689@.microsoft.com...
> tnx in advance for any help.
> I have a multiple enterprise system in which every enterprise is being
> modeled as a schema in the DB. This means that schemas are identical in
> structure and when you connect to one schema, you are connecting to one
> logical enterprise.
> Now, I need to write a bunch of reports with the ability to connect
> indiferently to on Enterprise or another (the user selects against which
> Enterprise he wants to run the report to).
> RS allows to define one or more Datasets for every report. But that has to
> be an SQL instruction with schema qualifying "hardcoded" for every table
> you
> use.
> I've thought about dynamic SQL inside a stored procedure, and the
> enterprise
> (schema) being passed as a parameter, but that's not such an elegant
> solution.
> So any suggestion will be gratefully welcome...
> regards
>
begin 666 copycode.gif
M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
MT?_______P``````````````````````````````````````````````````
M`````````````````````````````````````````````````"'Y! $``&8`
M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
`
end|||Hello Edmundo,
Have you got the the problem sorted? I am having the same trouble. In my
case, I need to select a db schema at the run time. Here are methods I've
tried:
1. create a parameter to contain the db name. Add in the ODBC connection
string "database=Parameters!db_name.Value", this doesnt work. The alert
"database-Parameters!db_name.Value doesn't exist" turns up.
2. delete the "database = XXX" part from the connection string, so no
specific database in the DB server will be connected, a connection to the DB
server itself is established. create a parameter to contain the db name. in
the query use ?.attr_name=XXX. By doing this, I expect the parameter can
specify the DB name at the run time. this doesnt work either.
So if you have the solution, could you please post it up. It'll be really
appreciated!
cheers,
P
"Bruce L-C [MVP]" wrote:
> In RS 2005 you can base a data source on an expression. This sounds like
> your scenario is exactly what this feature was put in for.
> From Books Online:
> Data Source Expressions
> You can put an expression into a connection string to allow users to select
> the data source at run time. For example, suppose a multinational firm has
> data servers in several countries. With an expression-based connection
> string, a user who is running a sales report can select a data source for a
> particular country before running the report.
> The following example illustrates the use of a data source expression in a
> SQL Server connection string. The example assumes you have created a report
> parameter named ServerName:
> Copy Code
> ="data source=" & Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the following
> guidelines when defining a data source expression:
> a.. Design the report using a static connection string. A static
> connection string refers to a connection string that is not set through an
> expression (for example, when you follow the steps for creating a
> report-specific or shared data source, you are defining a static connection
> string). Using a static connection string allows you to connect to the data
> source in Report Designer so that you can get the query results you need to
> create the report.
>
> b.. When defining the data source connection, do not use a shared data
> source. You cannot use a data source expression in a shared data source. You
> must define a report-specific data source for the report.
>
> c.. Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
>
> d.. Add a report parameter to specify a data source. For parameter values,
> you can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
>
> e.. Be sure that the list of data sources shares the same database schema.
> All report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
>
> f.. Before publishing the report, replace the static connection string
> with an expression. Wait until you are finished designing the report before
> you replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Edmundo J. Davila" <edavila@.monisa.com> wrote in message
> news:19A56F20-96FE-4751-846F-9E8B2D5CF689@.microsoft.com...
> > tnx in advance for any help.
> >
> > I have a multiple enterprise system in which every enterprise is being
> > modeled as a schema in the DB. This means that schemas are identical in
> > structure and when you connect to one schema, you are connecting to one
> > logical enterprise.
> >
> > Now, I need to write a bunch of reports with the ability to connect
> > indiferently to on Enterprise or another (the user selects against which
> > Enterprise he wants to run the report to).
> >
> > RS allows to define one or more Datasets for every report. But that has to
> > be an SQL instruction with schema qualifying "hardcoded" for every table
> > you
> > use.
> >
> > I've thought about dynamic SQL inside a stored procedure, and the
> > enterprise
> > (schema) being passed as a parameter, but that's not such an elegant
> > solution.
> >
> > So any suggestion will be gratefully welcome...
> >
> > regards
> >
>
>