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


ADVERTISEMENT

Creating Table With Different SCHEMA...

Sep 27, 2005

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...

View 1 Replies View Related

When Creating, How To Specify That Table Belongs To A Particular Schema?

Mar 9, 2006

Using Management Studio, when i create a new table/stored proc., the owner is always dbo. How do I change this when I create it? Also how to transfer it to another schema once it has been created?



View 4 Replies View Related

Not Getting Dbo Schema After Creating Then Saving A New Table

Jan 22, 2008

I am not getting the correct schema once I've created a new table in SQL Server 2005. I am expecting to see dbo.Table_Name. Instead I'm getting UserName1.Table_Name. I was set up as a DB owner. How can I get what I'm expecting to see?

View 5 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

Create Target Table Dynamically Based On Source Table Schema?

Sep 13, 2005

I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, 
Bryan Ax

View 9 Replies View Related

Tempoary Table Question - How To Create One With The Same Schema As An Existing Table?

Jul 28, 2006

Hello,

I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?

I'd like to do something like:

CREATE TABLE #tempTable LIKE anotherTable

..instead of...

CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...

I'm sure there must be a simple way to do this!

Many thanks,

Ben S

View 3 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

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

Help Needed: Granting Create Table Permisions On Specific Schema Options

Sep 6, 2007

Having some trouble getting my head around setting access to specificschemas- here's my problem:I've created a specific schema that I only want certain users tocontrolProblem: Even though I give them full access....the cannot createtables under that schema...my code is below (flyer is the schema,eflyerAdmin is the role, and eflyer is the user):GRANTALTER,CONTROL,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,TAKE OWNERSHIP,UPDATE,VIEW DEFINITIONON SCHEMA::flyerTO eflyerAdminGO-- Add an existing user to the roleEXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

View 1 Replies View Related

Can Table Created On Different File Group Based On Schema

Feb 17, 2012

Provided that we have 2 type of tables, the first type of tables with schema schema_a and the second type with schema schema_b.Also 2 filegroups apart from primary created: filegroup_a and filegroup_b.How can I configure so that when table with schema schema_a created, it can be put on filegroup filegroup_a automatically and when table with schema schema_b created, it can be put on filegroup filegroup_b automatically?

View 1 Replies View Related

How To Import Text File Using The Exising Table Schema

May 28, 2008

Hello;

I have table schema and text data file. After create the table, and then import the text data file. However, the text file always overwrite the table schema.

How could I using existing table schema to import the text file?

Thanks,

View 10 Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

May 3, 2004

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View 3 Replies View Related

Creating Sql Server Table Stucture From Xsd File

Dec 8, 2006

Can we do that and how pls?
The purpose is to avoid creating sql server table structure manually when we already have xsd file.

View 6 Replies View Related

Creating A New SQL Table By Importing In A Delimited File

Feb 17, 2008



How do I do this?

I cannot find any facility like there is a Access for getting external data.

View 3 Replies View Related

Incorrect Syntax Near 'XML'-While Creating XML Schema

Jul 3, 2007

 Hi,       Im trying to create a xml schema like     CREATE XML SCHEMA COLLECTION BooksSchemaCollection ASN'<?xml version="1.0" encoding="UTF-16"?><xsd:schema elementFormDefault="unqualified"   attributeFormDefault="unqualified"   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >    <xsd:element name="book">        <xsd:complexType mixed="false">            <xsd:sequence>                <xsd:element name="name" type="xsd:string"/>                <xsd:element name="author" type="xsd:string"/>                <xsd:element name="publisher" type="xsd:string"/>                <xsd:element name="cost" type="xsd:integer"/>                <xsd:element name="comments" type="xsd:string"/>            </xsd:sequence>                              </xsd:complexType>    </xsd:element></xsd:schema>';  

But when i execute , im getting a error  like Incorrect syntax near 'XML' .any one know why its comming?????? Thanks 

View 1 Replies View Related

Creating Schema Binding View

Jan 18, 2008

Dear All,
we are using one view which is having around 30000 rows.
it is taking too much time to retrieve data. that's why i've decided to create an indexed view.

now i'm getting this error.
Cannot schema bind view 'view1' because name 'Table19' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

what is the solution for this error?


Vinod
Even you learn 1%, Learn it with 100% confidence.

View 7 Replies View Related

Creating An Existing Db Schema Baseline

Jul 20, 2005

What is the best method of creating schema creation scripts that can bestored into a version control system. The process of using em togenerate a script is not an appealing option. I am still learning theMS Sql sys tables and have not found a useful list of all the codes &types to join the tables etc.mike--Posted via http://dbforums.com

View 2 Replies View Related

Receiving Msg 2760 After Creating Schema

Feb 20, 2008



I have just had SQL Server 2005 installed on my machine. I did have SQL Server 2000 on my machine previously and can still access parts of 2000.

In 2005 I do not have the option schema option under security for any of the databases; therefore, I tried creating the schema manually as you will see below.

In 2005 I execute the following

CREATE SCHEMA TEST
GO

The message returned is "COMMAND COMPLETED SUCCESSFULLY"

I, then, execute the following


CREATE TABLE TEST.SalesPeople

(

SalesPersonId INT,

SalesPersonName VARCHAR(50)

)

GO

The message returned is

Msg 2760, Level 16, State 1, Line 1

Specified owner name 'TEST' either does not exist or you do not have permission to use it.

Can you give me the correct syntax on how to create the schema manually? Do you know why this is not showing up under security for the databases?

Thanks in advance for your help.

Tara

View 15 Replies View Related

Create New Table With Csv File

Jun 27, 2007

this is the worst noob question ever but Google isn't helping and I searched all the threads in this forum...no help. I have a comma delimited csv file that i need to load into an existing database on MS SQL 2000. I need to know how to create the table and populate it with my data...Any pointers of the differences between syntax Of MSSQL and MySql or a tutorial of how to do it with enterprise manager would be great...Dang I feel noobie.

View 2 Replies View Related

Create A Table And Put It In A File

May 16, 2006

When I create a table I can create it in a filegroup, but how do I put it in a file. Example---

I have two files in one filegroup called "Secondary" file A and file B.

I want to create a new table called tableA and put it in file B on filegroup Secondary.

I looked at bookes online and can't find it.

Thanks

Thomas

View 1 Replies View Related

Creating DB Schema From A Script Or Stored Procedure?

Jan 7, 2005

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.

View 4 Replies View Related

Problem Creating Function To Be Schema Independent

Apr 1, 2008

I am writing a SQLServer script that I want to be schema name independent. I mean I know that all users of the script will have the same tables, but not necessarily the same schema name.

When I hard code the script to use the name of my schema, wcadmin, it works OK.

CREATE FUNCTION wcadmin.dectohex(@a numeric)
RETURNS varchar(8)
BEGIN
DECLARE @x varchar(8)
DECLARE @y varchar(1)
DECLARE @z numeric
DECLARE @w numeric

SET @w=@a
SET @x=''

WHILE @w > 0
BEGIN
SET @z = @w % 16;

SET @y= CASE @z
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE CAST(@z AS varchar)
END

SET @w = ROUND(@w/16,0,1)
SET @x = @y + @x
END

-- Pads the number with 0s on the left
SET @x = RIGHT(REPLICATE('0',8) + @x ,8)
RETURN @x
END;
GO

select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
wcadmin.dectohex(fi.uniqueSequenceNumber) VaultFile,
fm.path VaultPath
from
WTDocument di,
WTDocumentMaster dm,
HolderToContent hc,
ApplicationData ad,
FvItem fi,
FvFolder ff,
FvMount fm,
FvHost fh
where di.idA3masterReference = dm.idA2A2
and fm.idA3A5 = ff.idA2A2
and fm.idA3B5 = fh.idA2A2
and fi.idA3A4 = ff.idA2A2
and ad.idA3A5 = fi.idA2A2
and hc.idA3B5 = ad.idA2A2
and hc.idA3A5 = di.idA2A2

DROP FUNCTION wcadmin.dectohex;
GO

But when I remove my schema name I get the error

'dectohex' is not a recognized built-in function name.

In this case I'm just using :-

CREATE FUNCTION dectohex(@a numeric)
.
.
.
.
select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
dectohex(fi.uniqueSequenceNumber) VaultFile,
.
.
.
.
DROP FUNCTION dectohex;

Creating and dropping the function seems to work OK when I drop the schema name, I just can't call it.

I've been trying various permutations of dbo and [dbo] prefixes unsuccessfully.

Any suggestions?

Thanks


David

View 5 Replies View Related

Installing, Creating Database, Updating Schema?

Jul 20, 2005

Hi there,I have a database on my test machine that will need to be installed on usersmachines. I would like to create the database with the given schema on theusers machine and also with some suitable default values in the tables. Inote that although I can script the schema so that re-creating the structureof the database is simple on the users machine, I cannot script the contentsof the tables also (automatically). What I would like to do is take somekind of "snapshot", save it as a script and then run this script in myinstaller. Are there any tools available to do this?Secondly and related to the above: if I subsequently make changes to thedatabase schema (adding or removing columns, altering, adding or removingstored procedures etc.), how do I roll out those changes to a customer? DoI need to hand code an "upgrade" script, or is there a tool that willproduce a "difference between" script I can run on the customers machine?Thanks for any tips you can give me about this.Robin

View 3 Replies View Related

Create CSV File From A Data Table

Mar 6, 2008

 Hi,     I am working on SQL Server 2005 Express and what i want to do is to created a CSV file from a database Table or a View......I search around and find that I Can use BCP for that purpose..Ne other good idea if you people suggest....as BCP is not working on my PC while i am connected to database server...So plz suggest me the right way to do so...  thanx 

View 5 Replies View Related

How To Create Sql Table From Flat File

Jun 4, 2007

Any one can help for how to create sql table dynamically as per flat file format...
its required very urgently

View 1 Replies View Related

Create Table,fields Come From Csv File

Oct 24, 2006

I want to create a table automatically,and fields come from a csv file

any idea? TIA

View 1 Replies View Related

How To Create Table On Text File Import?

Sep 27, 2007

Hey guys,
I have a dilemma and hope someone can help.

I don't know of any utilities or commands in SQL that do this but I hope someone does.

What I need to do is something like a bcp import a text file in. I can do that with DTS as well. But what I wanted to do is create a table on the import. So lets say, I am importing a tab-delimited file with column names as the first row that is called ax.txt. On import, it would create the table ax with the column names in the file and then import the data into that table.

I hope I explained it clearly. Please let me know if there is anything I can use to do this without writing lots of code.

I have an idea how to do it the long way but hope there is a utility that already does it.

Thanks in advance.

View 10 Replies View Related

Create A Flat File For Each Record In A Table

Apr 20, 2006

I'm rather new to ssis and I've been reading and testing but didn't find a solution for this problem.Supose I've got a table Customer with some fields. One of the fields is CustID.I want to create as many flat files as there are Customers in the table with flat file name set to the CustID.If you could point me in a good direction, It would be nice.Greetings from Belgium

View 4 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







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