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 RepliesI want to replace every single 1/1/1900 date I have in my system with null, on an entire database level. I never want them, they cause multiple issues, and I need them gone.
I have made a trigger on a test table that works for inserts, but not updates:
CREATE TRIGGER UpdateDate ON test FOR INSERT AS
IF EXISTS (select * FROM inserted WHERE CheckDate ='1/1/1900')
BEGIN
UPDATE test SET CheckDate=Null
FROM test T INNER JOIN inserted I ON T.AutoID=I.AutoID
WHERE I.CheckDate='1/1/1900'
END
Also, it only works on this table, and the specific field mentioned.
I am looking for a global, databse level trigger that will replace 1/1/1900 with null whenever it appears from an insert, or an update statement.
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 RelatedHey,
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
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
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.
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
Hi,
I created a database,login,user and schema like belows.
-- 2. create database
CREATE DATABASE MyTempDatabase;
-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;
--
USE MyTempDatabase;
-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;
-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;
The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.
How can I grant permissions to the user?(or schema?)
I failed to grant by T-SQL query.
Additionally, what is purppose of the ROLE? Should I create or use it?
I'm confusing in security concept(login,user,schema,role).
Thanks.
sqlplus for Oracle has a CREATE DATABASE LINK command. What is the equivalent functionality for MSSQL using sqlcmd or other script mechanism.
View 1 Replies View Related
Scenario:
I have installed SQL Express 2005 and I want to create a new DB using sqlcmd.
I am running a command like: sqlcmd -i file.ini (I am logged like Administrator of PC)
the content of the file.ini is:
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = 'c:Program FilesMyTestdatabase'
-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE DataCollection ON
( NAME = DC_dat,
FILENAME = '''+ @data_path + 'DC.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = DC_log,
FILENAME = '''+ @data_path + 'DClog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
) ;
GO
I obtain this error:
Msg 1802, Level 16, State 4, Server TESTQ5SQLEXPRESS, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server TESTQ5SQLEXPRESS, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'c:Program FilesMyTestdatabaseDC.mdf'.
If I change the security of directory database (I add "modify" permission to Users windows group) the error disappear.
I am logged like Administrator... why is it necessary to change directory permission?
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedWhat is the best way to create a blank copy of my database? I really want some of the tables to keep the data in them, these are some of the lookup table.
I appreciate your help.
Hi, I'm a noob who sucks at programming, and sucks even more when it comes to database.
I'm operating a small website with an SQL Server database that drives my shopping cart. I want to close this website down, but I don't want to lose all the data in the database in case I want to do something in the future.
How do you backup the entire database into a file so i might be able to import it at later times? Do I have to do this with SQL, T-SQL, or the manager?
I have built a template database which I'm finally pleased with, however I want to periodically duplicate the design - not data into a new database. How can I duplicate a database? I was hoping to right mouse, copy, then right mouse, paste, and then be prompted for the new name but no such luck.
View 6 Replies View RelatedIs there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.
Any ideas?
Thanks in advance.
Hi friends
Suppose i have a table of 100 cols and 10000 rows i want to search a particular field called 'Newyork' . I dont no what the col is ?
Can anyone tell me how can i search that
Vicky
Hi, this is my first post on these forums, so please excuse me if this topic has already been covered.
I'm currently working in a power station for student vacation work placement. I need to export data from a database that gets it's data from machinery and inputs out in the plant. The machines that provide this input put it into a database, and I need to find the relevant data to export.
My problem is that, in some cases, the sample data that i'm given may be under different field names, in a completely unrelated table. I was looking for a way to search the entire database (250+ tables) for a certain string, so I can find where it is in the database, and run queries on the table it originates from. For example:
My sample data shows me that I have an object with the ID Y03A3DEA_TH1. I know this ID will occur somewhere else in the database, but i'm just not sure where.
If anyone knows of any way that I can search the entire database for specific data, either using tools in MS SQL 2000, or 3rd party apps, i would greatly appreciate their help.
Thanks a lot,
Jack Smith
Hi,
Is it possible to copy a database from the existing database. I mean to say creating a duplicating database.
Note: I can do it by using "Generate SQL Server Script Wizard" or "Database Publishing Wizard" which saves a some filename.sql and later executing this will create another database by assigning some xyzdatabase name.
My main motive is copying a database in a single line script without using this lengthy sciprt.
Example
FirstDatabase (contains 10 tables 10 stored procedures)
SecondDatabase (also contains 10 tables 10 stored procedures)
If possible, can you write a script that attach a database from the C:MyTemporaryDatabase
Because this i need through programming Visual Basic using SQL Server. I want to create some temporary database.
Waiting for your reply.
Thanks.
Best Regards
Kashif Chotu
Im building a site on my pc using SQLExpress and i have a question about something. The database im working on has data inside the tables and i was wondering how i would move that to a different server though a script with all the data in the table.
View 3 Replies View RelatedI'm running the below query for listing all the primary key in a database but it is retiving 0 rows.
DECLARE @cmdstr varchar(100)
CREATE TABLE dbo.TempTable
(TABLE_QUALIFIER varchar(10),
TABLE_OWNER varchar(5),
Table_Name varchar(10),
COLUMN_NAME varchar(15),
KEY_SEQ int,
PK_NAME varchar(50),
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_pkeys "?"'''
INSERT INTO TempTable EXEC(@cmdstr)
SELECT Table_Name,COLUMN_NAME,PK_NAME FROM TempTable ORDER BY Table_Name
DROP TABLE TempTable
Any body can give advice regarding this.
Thanks,
Ravi
I have to replace gl_number 25050-80 with gl_number 25050-80 in the entire database. I know how to do that in a table but not in all tables.
View 2 Replies View Related
I have saved a word somewhere in somefield which i can't remember..
is there any way to search for the word in the entire datebase and show in which table and field it is stored?
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
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 ??
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?
Hi,
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
Online, but is seemed that only save file to server. How can I save backup
file to local computer? or there is other way as easy as this statement for
backup/restore database?
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
Thanks,
Tien,
After found out the `transfer object` command in MS SQL EM didn`t work well
in my databases, I started code by myself to generate the `Create xxx` statements to copy objects across databases (structure only) using SQL.
I`ve coded out most of them, logins, defaults, UDT, tables, stored procedures,
but then I started to have problems with primary keys and foreign key const.
Has anyone tried to do the same thing before or is there some SQL scripts created to handle this already? Please advise me.
I am thinking to use SQL-DMO with VBscript now. But I still prefer a good
SQL script (old thought, the DB-Lib based `isql` is more reliable than the
SQL-OLE).
Thanks for any suggestion.
- Ken
Hi,
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
Online, but is seemed that only save file to server. How can I save backup
file to local computer? or there is other way as easy as this statement for
backup/restore database?
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
Thanks,
Tien,
I am attempting to only query the lowest year, and month from my entire production database. I am using the following query and when I do it individually it will bring back the lowest year but also with additional years. How can I remove those other years so I can only query the lowest year and month from the entire database?
Code:
select distinct RRCID, MIN(YEAR)as YEAR, MONTH
from Gas_Prodv1
where RRCID = 1221376
group by RRCID, MONTH
order by YEAR, MONTH
Results:
122137620071 <----- Only want this record
122137620072
122137620073
122137620075
122137620076
122137620077
122137620078
122137620079
1221376200710
1221376200711
1221376200712
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!
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.
This problem may appear trivial to you guys but is troubling me since quite some time now! The problem is that I created a website using express studio happily and it worked flawlessly on the local host. Now I want to move it to a remote server and my host created a database name ASPNETDB for me on the server. The problem is that that ASPNETDB database is virtually empty and I want to copy all my tables,stored procedures etc etc(complete database) to the remote ASPNETDB database. How can I do that? In the management studio express edition there is no command available to copy paste all contents of the database to another database. Please help I am quite confused!
View 4 Replies View Related