sql database integrity: schema

Showing posts with label schema. Show all posts

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Hi ...
I need to bulk insert the xml data below. I am working on some issues related to the Itentity column (you may have read the other question).
The xml below uses elements with attributes. Is the schema file formated correctly to extract the attributes from the SITE element and then also extract the attributes from the ATMOSPHERIC element ... ? Not sure about the schema file ?
Thanks,
Chris

////////////////////////////////////////////////////
// schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="atmospheric" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime"/>
<xsd:attribute name="airtemp" type="xsd:string" sql:field="Temperature"/>
<xsd:attribute name="dewpoint" type="xsd:string" sql:field="DewPoint"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

////////////////////////////////////////////////////
// xml data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>

////////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestRWISRawAtmospheric] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_DataSourceId] DEFAULT ('OH'),
[ProductInstanceId] [char] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_ProductInstanceId] DEFAULT ('5abbbc86-fb2c-4703-9589-b55f763ee150'),
[SystemId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RpuId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SensorId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObsDateTime] [datetime] NULL ,
[InsertDateTime] [datetime] NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_InsertDateTime] DEFAULT (getdate()),
[Temperature] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DewPoint] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelativeHumidity] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPD] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPDGust] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMin] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRAvg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMax] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipIntensity] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipType] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipRate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pressure] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Visability] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMax] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMin] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WetBulbTemp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastPrecipStart] [datetime] NULL ,
[LastPrecipEnd] [datetime] NULL ,
[PrecipAccum1Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum3Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum6Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum12Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum24Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_MacgowanTestRWISRawAtmospheric] PRIMARY KEY NONCLUSTERED
(
[RecordId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...

Sorry about the confusion. I was having some issues with bulk

loading using the IDENTITY Column in the target table. I was

setting the COM attribute KeepIdentity to TRUE rather than FALSE.

The schema and xml are correct.

Thanks,

Chris

Creating xsd schema file for a xml file ...

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...
Sorry about the confusion. I was having some issues with bulk loading using the IDENTITY Column in the target table. I was setting the COM attribute KeepIdentity to TRUE rather than FALSE.
The schema and xml are correct.
Thanks,
Chris

Sunday, March 25, 2012

Creating user to own schemas?

I have noticed that some folks create a user to own a schema, with both having the same name (ex: userowner = pfm schema = pfm) and others make dbo the owner for all their schemas. From what I can tell, it doesn't really matter; what does matter is if your database users are granted access to the schema. Since most users only have public rights and dbo has database owner rights, is there any particular reasoning to use one scenario over the other?

Besides better granularity in your DB application management, one very good reason is to have better isolation between different applications. For example, if all the schemas (and therefore all tables and modules by default) are owned by the same principal (i.e. dbo) it would be very difficult to control access to the data on principals who can create/execute modules (i.e. SPs) as ownership chaining will be available for all tables in the database.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you Raul, I agree this does make sense. I just tried to create a user (ampfm) and it failed due to their not being a login named ampfm. However, when I looked at logins, there does not seem to be one for sys or INFORMATION_SCHEMA. How were those users created without also requiring login accounts?

|||

Those are builtin-in principals, and they are not intended for any interactive usage. Because they are built-in and not intended for interactive usage the SIDs are set to NULL. I strongly recommend against reusing these built-in principals in any way other than the supported scenarios. Please consult BOL for more information on them.

If you want to create a user without a login you can use the WITHOUT LOGIN syntax:

CREATE USER [ampfm] WITHOUT LOGIN

I hope this information helps.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks again Raul. That is exactly what I needed and it worked great!

Tuesday, March 20, 2012

creating table with different SCHEMA...

Hi all,
i m using sql server2005 CTP...i created a database called TEL and in that database i created a user(in security) as
USE [TEL]
GO
/****** Object: User [COLL_DB] Script Date: 09/27/2005 15:38:51 ******/
GO
CREATE USER [COLL_DB] FOR LOGIN [loginName] WITH DEFAULT_SCHEMA=[COLL_DB]

Now,when i m trying to create a table in the database TEL as
CREATE TABLE [COLL_DB].abc (c numeric)
commit;

it gives me error saying
The specified schema name "COLL_DB" either does not exist or you do not have permission to use it.

Now,can someone tell me...what i have to do to fix this error???
thanks...I'm gonna go with that COLL_DB has not been assigned any permissions, and needs probably to be added to a server role.

Monday, March 19, 2012

Creating SQL Schema using objects not script?

Hey new in the .NET SQLDMO thing...

Can you in C# programtically create a SQL DB and then create tables, references etc...programmatically without having to use Sql scripting?

What is the root namespace for this anyone?

Thanks ideas on examples much appreciated!No. SQL is a bridge between programming language and Database, so far.|||Does not matter...

I used a combination of XSD and DataSets to come up with a decent enough solution.

Eric|||Yes, you could use XML/XSL to access SQL Server with appropriate setup of SQL Server, but it seems you still need to use a combined SQL script.

Anyway, do you mind to share your sample with us?|||Yep...or use SQLXML3.0 ...

I think you can do that with and OleDb connection objects..create Schema structures in SQL.

I have it working with and AccessDB right now...using T-SQL script source dynamically.

Thanks,

Eric|||I knew and used SQLXML 3.0 and even previous version well in VS 6.0 age, but now it seems the trick has been replaced by Web Service in most scenarios. I am curious of your using XSD associated with DataSet. If with several line code you could demo the sample, would you please show me the code? Otherwise, never mind.|||yeah it is quite large...really...

I only had to do it because the XSD.exe tool barfed on my XSD it was simply too large...So had to do it manually.

But its' simply creating a defintion file for the data structure in an xsd...define tables, rows, columns, types ect...the use the XSD.exe tool and it will generate a strongly typed dataset deriving from your data structure xsd template.

now you can use that strongly typed class as destination source....iterate over your xsd find what you need to define xsd:complexTypes and xsd:elements etc...then use the strongly typed collection ..

myClassTable.Add("xsdElementToBeNamedATable") etc..for type defs column defs etc...

Then you have a complete composite structure of your db in a DataSet....you can create what ever you want from this...out put the xsd again..if you want etc...

Now you can iterate over the DataSet Tables and create your T-SQL script on that structure. Alternatively you can use a straight XmlSchema iteration over the XSD document and then inline your T-SQL script code during the looping over the various types...xsd:attributes, xsd:sequences, xsd:elements etc...

It's kinda hairy at first.

LOL|||It's kinda hairy at first.

Yes, you are right. I believe so. But, what is your reason to do so? Do you try to reach the database through HTTP protocol?|||Customer provided me a schema and nothing else...

Build me a DB...

No ERD nothing just this monster of an XSD. to the tune of like 30 imports and a file size of like 500k...humongous...

Every tool on the market barfed when trying to parse the XSD.

I have 2 Gig on my machine and serious hardware...the problem is that XMLSpyEnterprise is the only tool that I have seen any bright spots with. I wish you could modify the amount of ram that the tool uses...like the java engines allow you to do...it just runs out of memory...the program stack is simply too small.

Eric|||OK, understandable.

Although the XML stuff (including XSD) seems a new and advanced technology, it has never been a normal release as a tool for Microsoft products until the .NET age when the XML technology is still not explicitly used but with the form of Web Service. In fact, Web Service is called XML Web Service, it could let you use XML/SOAP protocol wihtout expert knowledge on it, actually without XML/SOAP knowledge on it.

In your case, you somehow has a legacy product from your customer with it quite troublesome. If this is a small piece or you have no way to control it, you might just keep it and use it. Otherwise, you'd better to upgrade it to Web Service to enjoy the advantage of .NET.

This thread,248757, might help you, too.|||Actually,

They want a DB for all kinds of reasons...I am under NDA so I can't say too much other than it involves InfoPath, Office2003, SharePont Server V2.0, SQLServer and a host of XML technologies...very cool stuff..

LOL|||Web services are fairly trivial...I mean I like then and use them for my own corporate stuff. It's a fantastic technology combine it with the WSDL.exe can you got some seriously cool prodcutvitiy tools for creating strongly typed web serviced classes.

This is different...how is using a web service going to solve my DB problem?

Just curious...

Eric|||You didn't state your DB or other problems clearly, how can I know the way to solve them. I even don't know your structure of the problem, and don't know if your problem need to use either XML or Web Service. Basically, the XML/SOAP technology is used for interplatform/interlanguage communications through HTTP protocol. A lot of people use them just in a not necesary scenatio. But, this might not be your case. You think your job is fantastic, that is fantastic.

Sunday, March 11, 2012

Creating Schema

Hello!
In a doctor-patient table relationship, I need the patient to only have one
doctor but the doctor can have as many patients as he wants.
What is the easiest way to set this up?
Thank you!Never mind. Got it!
Thank you!
"Bahman" wrote:

> Hello!
> In a doctor-patient table relationship, I need the patient to only have on
e
> doctor but the doctor can have as many patients as he wants.
> What is the easiest way to set this up?
> Thank you!
>|||Never mind. God it!
Thank you!
"Bahman" wrote:

> Hello!
> In a doctor-patient table relationship, I need the patient to only have on
e
> doctor but the doctor can have as many patients as he wants.
> What is the easiest way to set this up?
> Thank you!
>

Tuesday, February 14, 2012

Creating DB schema from a script or stored procedure?

Hi there,

I am a fairly experienced programmer, but new to SQL Server - I understand basic DB theory well enough, but don't have much practical experience with using SQL Server.

I'm working on a project at the moment, where, as part of the spec, users can create 'systems' in the database. For example, in a parts database for a pumping station, there may be 10,000 parts. Rather than have one huge database for, say, 10 pumping stations, we would prefer to have 10 smaller databases, each dedicated to its own system. The schemas would be identical.

I think one approach to this would be have an empty database in SQL server (with the correct tables/schemas/relationships etc) and then copy that within SQL server, with a new name (the system name), probably using a stored procedure.

My question: Is this possible, is there already a stored procedure in SQL Server (2000) to do this, or do I have to write one? Writing a SP to physically create the database from scratch would be a nightmare, I'm hoping there is a simple 'copy_db to new_db' type stored procedure. Maybe there is a program can read a DB and create a script to re-create the DB under a new name?

Any information greatly appreciated.

Mark Wills.In my experience the multi-database approach is never worth the considerable administrative requirements to support it. Unless you anticipate the database size to increase way beyond the ability of a single database server to support it, you should serious rethink your plan and consider adding an additional field or table to your schema to make it scalable.|||I am in agreement with Blindman but if you persist with this scheme you can create your initial database by scripting it out or using the Enterprise Mangeler (whoops) Manager (EM).

Once you have your template defined, in the EM you can right click on your database and go to ALL TASKS and choose Generate SQL Scripts. You can make the EM script out all or some of your objects. then I guess you can wrap all of that code in a SP.

There is another way. If all of your DBs are going to have the same structure and objects on this instance of SQL server I believe you can alter your model db and everytime you issue CREATE DATABASE, you should get all of the objects you created in model. I have never used this second method so I would read up on it first before trying.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.

1.5 TERA BYTES? :eek: Holy cow, thats one hell of an MP3 collection :)

Ok guys, thanks for the information. I'll do it like you say. As I say, whilst I'm vaguely familar with DB theory, SQL Server is a whole new ballgame for me - it's a bit daunting, so I'm sure I'll be back with many questions! Thanks for taking the time to reply.

Mark.

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Creating xsd schema file for a xml file ...

Sunday, March 25, 2012

Creating user to own schemas?

Tuesday, March 20, 2012

creating table with different SCHEMA...

Monday, March 19, 2012

Creating SQL Schema using objects not script?

Sunday, March 11, 2012

Creating Schema

Tuesday, February 14, 2012

Creating DB schema from a script or stored procedure?

sql database integrity

Blog Archive

About Me