Problem With Using Dynamic SQL To CREATE XML SCHEMA COLLECTION In SQL 2005

Apr 8, 2008

This works in SQL Server 2005:
CREATE XML SCHEMA COLLECTION Version2_1 AS
'<xs:schema xmlns="http://www.icpsr.umich.edu/DDI"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:doc="http://www.icpsr.umich.edu/doc"
    targetNamespace="http://www.icpsr.umich.edu/DDI"
    elementFormDefault="qualified" attributeFormDefault="unqualified">
    <xs:annotation>
        <xs:documentation>
            This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification.
            This schema is intended for use in producing electronic versions of codebooks for quantitative social science data.
            CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $
        </xs:documentation>
    </xs:annotation>
</xs:schema>'

 
This version, however, gives errors:
 
DECLARE @sql VARCHAR(max)
SET @sql =
'CREATE XML SCHEMA COLLECTION Version2_1 AS <xs:schema xmlns="http://www.icpsr.umich.edu/DDI"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:doc="http://www.icpsr.umich.edu/doc"
targetNamespace="http://www.icpsr.umich.edu/DDI"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:annotation>
    <xs:documentation>
        This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification.
        This schema is intended for use in producing electronic versions of codebooks for quantitative social science data.
        CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $
    </xs:documentation>
</xs:annotation>
</xs:schema>'
EXEC (@sql)

Ultimately, I am trying to create a stored procedure that will take as parameters the name of the new schema and the .xsd file it comes from, so that I can use it in code.  I am a novice, so all explanations would be greatly appreciated.

Thanks,
Ed Graham
 

View 3 Replies


ADVERTISEMENT

Adding A XML Schema To XML Schema Collection

Apr 19, 2006

I used SSEUtil to add a schema to my database but I am having problems.  Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database.  Any ideas on what I am doing wrong or why this might be happening?ThanksKevin

View 3 Replies View Related

XML Schema Collection Question

Nov 13, 2007

If I have N different xml document formats and I want to store those xml data files as typed xml in one table (one column), can I do it using xml schema collection (by adding schema file to schema collection for each document type and assigning xml schema collection to this column)?

Is this possible using xml schema collection? Or did I miss something about xml schema collection usage scenario?



Thank you!

View 4 Replies View Related

EXEC Permission On A SCHEMA COLLECTION???

Jan 15, 2007

I have a permissions problem with a table/procedure that I hope someone can help me with.

To set the scene .......

All my procs/tables/functions etc are owned by dbo.
I have a windows security group that is granted permissions to EXEC all procs. No one has permissions to tables.
I have a table that has an XML column and the column has a schema collection bound to it.
The table has a computed column that relies on a function to extract a datetime element from the XML in the XML column and I have an index on this computed column.
I have a proc that selects from this table and uses the computed date column for filtering. However, the select statement is build dynamically and uses sp_execute to perform the SELECT. This of course breaks the ownership chain.
To fix the above I have a user that was created from a certificate and the above proc is signed with the certificate. The user is granted select privileges on the table. This fixes the problem. (In fact, all procs/functions/triggers are signed in this way).

Now (finally) the problem ....

When I run the above proc as admin, it works fine.

When I run it as a member of the security group (mentioned earlier) I receive ...

EXECUTE permission is denied on object 'my_schema_collection', database 'mydb', schema 'dbo'

The 'my_schema_collection' mentioned above is the schema collection to which my xml column is bound.

What? How can I grant EXEC permission to a schema collection?

Anyone have any ideas?

Thanks,

~swg

View 4 Replies View Related

SQL Server Admin 2014 :: How To Get XML Schema Collection Definition

Oct 29, 2015

I am trying to copy the table data from db to another db, because some tables has XML column with a schema collection, I would like to get difinition of the XML schema collection and create it on the target db, otherwise I will get below error:

Xml columns cannot refer to schemata across databases.

My plan is for those tables with xml column with schema collection, I will:

1. Get all tables/columns that requires xml schema

SELECT t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_nam,
c2.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
inner join sys.xml_schema_collections c2 on c.xml_collection_id = C2.xml_collection_id

2. Get table definition

DECLARE
@object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(MAX)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID('TableName', 'U')) o

[code].....

3. Get XML Schema definition

4. Combine 2 and 3.

5 insert into newtable select .... cast(xmlColumnas xml) from OldDB.dbo.oldtable

I am stuck at step 3 now....

View 3 Replies View Related

Dynamic Data Elements For A Data Collection Application

Sep 20, 2005

What is the better table design for a data collection application.1. Vertical model (pk, attributeName, AttributeValue)2. Custom columns (pk, custom1, custom2, custom3...custom50)Since the data elements collected may change year over year, whichmodel better takes of this column dynamicness

View 7 Replies View Related

Dynamic Field DB Schema Brainstorming

Sep 6, 2005

Hello - Have a project where we are going to build a form creation application.  (ASP.NET).  This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.This is a fairly common thing.  Are there any resources out there as to where to start designing the DB schema?  I'm not looking to reinvent the wheel.  Here's the basic objects I'm seeing:TablesFormsFormFieldNamesFormFieldTypesFormFieldJSAny tips on the right direction to go?ThanksRob

View 4 Replies View Related

Dynamic XSD Schema Location For XML Source

Jul 14, 2006

I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?

View 4 Replies View Related

How Build Web Application (asp.net + C# ) With Dynamic Database Schema?

Mar 26, 2008

Hi all,

I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).

With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).

Therefore, I want to ask all of you that
- Is there anyway to solve this problem?
- how to design database that meet extended requirements as I present above?

Thanks for all of your advices.

Quan.

View 17 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

SSIS- The Element Cannot Be Found In A Collection. This Error Happens When You Try To Retrieve An Element From A Collection On A

May 19, 2008

hi,

this is sanjeev,
i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.


it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.


the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package



this is my code



{
Package pkg = new Package();
string str = (string)entry.Key;
pkg.Name = str;
alEntity = (ArrayList)entry.Value;

ConnectionManager conMgr;
Executable chPackage;
TaskHost executePackageTask;

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();



//string PackagePath = @"c:Genesis.dtsx";

//p = app.LoadPackage(PackagePath, null);
p = new Package();
p.LoadFromXML(parentPackageBody, null);


p.Name = str;

//Sequence seqContainer;

IDTSSequence seqContainer;
//seqContainer = (Sequence)p.Executables["Extract Genesis Data"];
seqContainer = ((Sequence)p.Executables[0]);

string packageLocation = @"Geneva Packages";
conMgr = p.Connections["SQLChildPackagesConnectionString"];






foreach (string val in alEntity)
{
if (seqContainer.Executables.Contains("Load_" + val) == false)
{
chPackage = seqContainer.Executables.Add("STOCK:ExecutePackageTask");



executePackageTask = (TaskHost)chPackage;
executePackageTask.Name = "Load_" + val;
executePackageTask.Description = "Execute Package Task";


executePackageTask.Properties["Connection"].SetValue(executePackageTask, conMgr.Name);
executePackageTask.Properties["PackageName"].SetValue(executePackageTask, packageLocation + ddlApplication.SelectedItem.Text + @"" + executePackageTask.Name);



}
}


app.SaveToXml(Server.MapPath("../SynchronizeScript/Packages/" + ddlApplication.SelectedItem.Text + @"") + str + ".dtsx", p, null);
}






please let me know what is the wrong in my code.

thanks in advance.

regards
sanjeev bolllina
sanjay.bollina@gmail.com

View 14 Replies View Related

Migrating Oracle PL/SQL Objects And Collection Types Into SQL Server 2005

Mar 7, 2008



Hi

Can anyone help me out in migrating Oracle stored procedures & Functions which contains collection types (user-defined datatypes like VARRAYS) into SQL Server 2005. How to achieve this taks? This is very important for me and it's very urgent. Please someone help me.

Thanks & Regards
K P Kumar

View 1 Replies View Related

Create Schema

Feb 26, 2008

what wrong ?

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE [name] = 'ATypes' )
BEGIN
CREATE SCHEMA [ATypes] AUTHORIZATION Owner_SchATypes;
END
GO

why I get an error ?


Noam Graizer

View 3 Replies View Related

How To Create A New Schema

Apr 24, 2007



How can I create a new schema inside a database from Managment studio express.



Also can someone explain me the concept of schema clearly. If database is like a house, then is schema like a room in it ??

View 3 Replies View Related

SQL Help. CREATE SCHEMA

Nov 2, 2007

I€™m trying to create an index on a view. I get the error that €˜the view is not schema bound.€™

Soooo, I try to put it in a schema.

This is the syntax I am using -

CREATE SCHEMA AUTHORIZATION painter
CREATE VIEW large_paints AS
SELECT paint_id,color FROM paint WHERE paint_size=100
CREATE INDEX idx on large_paints (paint_id);

I get the error - Incorrect syntax near the keyword 'INDEX'.

Any ideas?

View 1 Replies View Related

Create Schema Error

Mar 25, 2008

IF NOT EXISTS (SELECT 1 FROM Sys.Schemas WHERE [Name] = N'HR4')
CREATE SCHEMA HR4 AUTHORIZATION [dbo]

The above statements gives me an error saying "Incorrect syntax near schema". But the following code works fine.


DECLARE @sql varchar(100)
set @sql='CREATE SCHEMA HR4 AUTHORIZATION [dbo]'
IF NOT EXISTS (SELECT 1 FROM Sys.Schemas WHERE [Name] = N'HR4')
exec(@sql)

Any ideas on what is causing this error?

Thanks!

View 1 Replies View Related

Create A Default Schema

May 22, 2008



I Would like to create a schema sample and make it as default schema instead of dbo.

If a user logs in and creates a table like create table t1 (no int ) .
it would be assigned to sample schema and displayed as sample.t1 not dbo.t1.


How to set the user created schema as primary schema.

View 9 Replies View Related

Create Database Models From XML Schema (.xsd)

Dec 19, 2006

Hi .Net Guru’s,I have an urgent requirement for my project; the issue is mentioned below;Using .Net(C#/VB.Net) I need to generate/created Database objects from XML schemas.I don't have any sample xml schema file to give you. You just imagine you have a sample .xsd file and this .xsd file will be used to create database tables.Please let me know if you have any queries. Thanks,nick

View 1 Replies View Related

Create An XML Schema For A SQL Database Structure

Nov 5, 2004

Hey,

I was wondering does anyone know a way to get the structure, with relationships, of a Sql Database and generate a XML Schema. I want to use the Schema to build a CrystalReport.

Thanks!
-Kevin

View 1 Replies View Related

Create New Schema Using Management Studio

Jul 30, 2007

instead of CREATE SCHEMA using T-SQL

View 1 Replies View Related

CREATE SCHEMA In Db A From A Stored Procedure In Db B

Apr 18, 2006

Hi All
I have a SP that i create tables and other objects on another database.

Creating table work well.

declare @s nvarchar(2000)
set @s = 'use db01'
set @s = @s + 'CREATE TABLE ABC (recid int)
exec (@s)
------------------------------------------------------------------------

But if i try to create a schema it gives error :
'CREATE SCHEMA' must be the first statement in a query batch.

declare @s nvarchar(2000)
set @s = 'use db01'
set @s = @s + 'CREATE SCHEMA AAA
exec (@s)
How can i solve it?

Thanks.

View 5 Replies View Related

How To Create An SQLCE Database From An XML Schema?

Oct 11, 2007

Greetings,



I've searched through this and other SQLCE newsgroups, and it would
appear that whenever this question is asked, it is deflected or left
unanswered. I'll try again:





Is there any way to create SQLCE database tables from an XML schema
(preferably in .NET with C#)? Of course this can be done with the .NET
DataSet, but there doesn't appear to be any way to get the DataSet to
create the tables in the SQLCE database (although the reverse can be
done). I won't go into long explanations of the application, but it
does not ever have any connection to an SQL server, and we don't want
to create the SQLCE tables in code. We *only* want to create them
using an XML schema. Is the only option writing our own code to do this?



TIA

View 3 Replies View Related

Deny Create Schema Still Does Not Work In 2k8

Aug 3, 2007

You can't assign a default schema for a user that maps to a Windows group. OK fine. But if you create a user for that group anyway with no default schema, any objects created by members of that group will automatically cause a schema to be created for them, EVEN WHEN THAT USER HAS BEEN EXPLICITLY DENIED THE CREATE SCHEMA PERMISSION IN THE DATABASE. This is the same as it was in 2K5, but has still not been fixed in 2k8.

To reproduce, do the following steps:
Create a windows group, either in the doman or local to the box.
Add at least one Windows user to that group.
Create a SQL login for that Windows group.
Map that login to a new user in some database.
Explicitly 'deny create schema to ' that group/user.
Grant 'create procedure' to that group/user.
Log onto SQL with a Windows login that is a member of that Windows group
Use the database that we set the permissions in above.
Create a dummy stored procedure without qualifying the name with a schema ie: 'create prodedure test1 as select 'hello world').
Look to see what you ended up with. You will have a new schema named the same as the user who created the sproc. This will now be their default schema implicitly, and the test1 stored procedure will be in that schema, EVEN WHEN THAT USER WA EXPLICITLY DENIED THE CREATE SCHEMA PERMISSION IN THE DATABASE.

Could you please fix this?

View 3 Replies View Related

How To Create An Object Without Being The Owner Of The Schema

Apr 6, 2006

Hi

I have a schema called Accounts owned by fred

User bob has create procedure permission as follows:

grant create procedure to bob

bob would like to create a procedure in schema Accounts.

When he issues create proc Accounts.sp_proc.... it fails with:

Msg 2760, Level 16, State 1, Procedure sp_proc, Line 3
The specified schema name "Accounts" either does not exist or you do not have permission to use it.


What permission do I need to grant bob in order to allow this?

Thanks

View 4 Replies View Related

How To Create A CLR Integrated Trigger On Table In Schema?

Oct 6, 2006

In CLR integrated trigger:

If I want to make a trigger:
- For Insert
- With name: NewEmployeeInserted
- On table dbo.Employees

I add the following attribute above the desired .net method logic:
[SqlTrigger(Event = "For Insert", Name = "NewEmployeeInserted", Target = "Employees")]

How to make a trigger on for example: Production.Employees table?
where Production is the schema where this table resides.

Thank you.

View 1 Replies View Related

Can I Create A Database Schema From A DataSet And/or .xsd File?

Mar 20, 2007

I have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files.

I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas.

For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database? It seems like all the tools are there...

For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file? Or given the generated .xsd file, is there any way to create a SQL database from that?

Thanks in advance for any replies.

View 5 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Is It Possible To Create A Schema Or Table From A Dbf File Instead Of Manully Creating It

Oct 26, 2007

Is it possible to create a schema or table in sql server from a dbf file instead of manully creating it
 
Regards
Karen

View 1 Replies View Related

Command To Create Script For Entire Database Schema

Aug 3, 2012

Any command which will make create script for my entire database including all sp's , tables, functions..i dont want to backup the db ..i want to create scripts for creating an entire database schema (only the structure and not data)

View 1 Replies View Related

Restrict Role To CREATE TABLE On An Assigned SCHEMA

May 9, 2008

Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.


-- Create User

use master

go

create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON

use AdventureWorks

go

create user dba from login DBA
alter user dba with DEFAULT_SCHEMA=dbo

go

-- Create Role

use AdventureWorks

go

create role sp_ddl_role AUTHORIZATION dbo

grant CREATE TABLE to sp_ddl_role



-- assign user to Role

use AdventureWorks

go

exec sp_addrolemember sp_ddl_role, dba

go

-- Create Table statement then run with following error
Error:

Msg 2760, Level 16, State 1, Line 1

The specified schema name "dbo" either does not exist or you do not have permission to use it.

Remedy: Grant ALTER on schema::dbo to sp_ddl_role

My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.

Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA?

View 1 Replies View Related

SQL 2012 :: Revoke Create Schema Permissions Without Removing DDL Permission?

Apr 17, 2015

I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).

View 2 Replies View Related

Keep Existing Object Unchanged Or Create Schema At Subscriber =false?

Aug 15, 2006

 Hi,

I have the same database(3GB) in 2 physical locations in usa and europe

I need to create a publisher and subscriber between these 2 database without sending any snapshot  because i have the same data in the 2 sides, is it possible ?

Thx

Jim

View 13 Replies View Related

How Do I Move My Database Schema From 1 Machine To Another? (generate Create Script Is Greyed Out)

Jan 15, 2005

how do I move my database schema from 1 machine to another? Its a MSDE 2000 DB and Im using visual studio 2003.

In VS when I goto the server explorer and right click on a DB the "generate create script" options is greyed out.

What am I doing wrong?


If you cant help me with the above question could you please recommend another way for me to get my DB schema with all its constraints from one of my work stations to another. I dont want to manually recreate it.

Thankyou

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved