copy database structure only

Yes, script the database. In SQL Server 2005 rigth-click your database,
select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Neil" wrote:

> Is there a way to copy a database without all the data? I want to end up
> with the same database but "empty".
> Thanks
>
>Great. Thank you.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:9981EBA5-00B0-4303-96C4-476485D6CF94@.microsoft.com...[vbcol=seagreen]
> Yes, script the database. In SQL Server 2005 rigth-click your database,
> select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Neil" wrote:
>|||Is there a way to copy a database without all the data? I want to end up
with the same database but "empty".
Thanks|||Yes, script the database. In SQL Server 2005 rigth-click your database,
select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Neil" wrote:

> Is there a way to copy a database without all the data? I want to end up
> with the same database but "empty".
> Thanks
>
>|||Great. Thank you.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:9981EBA5-00B0-4303-96C4-476485D6CF94@.microsoft.com...[vbcol=seagreen]
> Yes, script the database. In SQL Server 2005 rigth-click your database,
> select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Neil" wrote:
>

Copy Database Fails in SSMS - Invalid Object Name

This is a carry on from my earlier post about generating scripts to recreate
a database.
Instead of generating a database script, I just tried to copy it, using the
wizard in SSMS. It fails in the same way my generated script did earlier.
I have discovered that if I send the log output to a text file instead of
the windows event log I get much more detailed information (I wish it would
say that on the screen, given that event log is the default; it would saved
me hours). I also realise now that Copy Database just scripts the database
and then executes it, which is why I am getting the same problem here as I
was when I scripted the database separately and then ran the script.
The output log says 'invalid object name' when creating a view in the
script, and it picks out an object - another view, for example - that the
first view is dependent upon. Initially, it would complain about views named
'dbo.MyView1', so I removed the 'dbo.' and it fell over on another view. So,
I naturally thought that it didn't like the 'dbo.' prefix. However, once I
had removed all the prefixes, it started to complain about MyView1 again, so
that obviously wasn't the real problem.
The strange thing is that this used to work. I have copied this database
before (perhaps a month ago) without problem, but I can't begin to think
what might have changed to cause it to fall over now. Could it be something
to do with permissions, or context? I connect using the database owner's
login, but I have tried others (all of which are in the sysadmin group), but
to no avail.
Can anyone suggest what could be going on, or how I can get closer to the
real cause?
TIA
CharlesHi
Could it be that you try to create an object (view) that contains another
object (another view) that does not exist yet?
"Charles Law" <blank@.nowhere.com> wrote in message
news:eYgkq4MCIHA.2004@.TK2MSFTNGP06.phx.gbl...
> This is a carry on from my earlier post about generating scripts to
> recreate a database.
> Instead of generating a database script, I just tried to copy it, using
> the wizard in SSMS. It fails in the same way my generated script did
> earlier.
> I have discovered that if I send the log output to a text file instead of
> the windows event log I get much more detailed information (I wish it
> would say that on the screen, given that event log is the default; it
> would saved me hours). I also realise now that Copy Database just scripts
> the database and then executes it, which is why I am getting the same
> problem here as I was when I scripted the database separately and then ran
> the script.
> The output log says 'invalid object name' when creating a view in the
> script, and it picks out an object - another view, for example - that the
> first view is dependent upon. Initially, it would complain about views
> named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another
> view. So, I naturally thought that it didn't like the 'dbo.' prefix.
> However, once I had removed all the prefixes, it started to complain about
> MyView1 again, so that obviously wasn't the real problem.
> The strange thing is that this used to work. I have copied this database
> before (perhaps a month ago) without problem, but I can't begin to think
> what might have changed to cause it to fall over now. Could it be
> something to do with permissions, or context? I connect using the database
> owner's login, but I have tried others (all of which are in the sysadmin
> group), but to no avail.
> Can anyone suggest what could be going on, or how I can get closer to the
> real cause?
> TIA
> Charles
>|||Hi Uri
Well that was what I thought originally, but now I am not so sure. It will
frequently be the case that a view uses another view or another object.
Therefore, the Copy Database operation in SSMS must be able to create
objects in the correct order. Either that, or it mustn't care if an object
does not exist yet. Either way, Copy Database would be useless if it
couldn't work out how to copy a database without hitting this problem,
wouldn't you say?
Charles
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4nelCNCIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Hi
> Could it be that you try to create an object (view) that contains another
> object (another view) that does not exist yet?
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:eYgkq4MCIHA.2004@.TK2MSFTNGP06.phx.gbl...
>> This is a carry on from my earlier post about generating scripts to
>> recreate a database.
>> Instead of generating a database script, I just tried to copy it, using
>> the wizard in SSMS. It fails in the same way my generated script did
>> earlier.
>> I have discovered that if I send the log output to a text file instead of
>> the windows event log I get much more detailed information (I wish it
>> would say that on the screen, given that event log is the default; it
>> would saved me hours). I also realise now that Copy Database just scripts
>> the database and then executes it, which is why I am getting the same
>> problem here as I was when I scripted the database separately and then
>> ran the script.
>> The output log says 'invalid object name' when creating a view in the
>> script, and it picks out an object - another view, for example - that the
>> first view is dependent upon. Initially, it would complain about views
>> named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another
>> view. So, I naturally thought that it didn't like the 'dbo.' prefix.
>> However, once I had removed all the prefixes, it started to complain
>> about MyView1 again, so that obviously wasn't the real problem.
>> The strange thing is that this used to work. I have copied this database
>> before (perhaps a month ago) without problem, but I can't begin to think
>> what might have changed to cause it to fall over now. Could it be
>> something to do with permissions, or context? I connect using the
>> database owner's login, but I have tried others (all of which are in the
>> sysadmin group), but to no avail.
>> Can anyone suggest what could be going on, or how I can get closer to the
>> real cause?
>> TIA
>> Charles
>>
>|||Charles Law (blank@.nowhere.com) writes:
> The output log says 'invalid object name' when creating a view in the
> script, and it picks out an object - another view, for example - that
> the first view is dependent upon. Initially, it would complain about
> views named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on
> another view. So, I naturally thought that it didn't like the 'dbo.'
> prefix. However, once I had removed all the prefixes, it started to
> complain about MyView1 again, so that obviously wasn't the real
> problem.
> The strange thing is that this used to work. I have copied this database
> before (perhaps a month ago) without problem, but I can't begin to think
> what might have changed to cause it to fall over now. Could it be
> something to do with permissions, or context? I connect using the
> database owner's login, but I have tried others (all of which are in the
> sysadmin group), but to no avail.
The tools can only do as good as they can given the input. And in the input
in this case is the view sys.sql_dependencies, which by its design cannot
be wholly reliable. To wit, references are stored by object_id. So guess
what happens if you try:
create view a as select n = 12
go
create view b as select n from a
go
exec sp_depends b
go
drop view a
go
create view a as select n = 123
go
exec sp_depends b
go
drop view a, b
By dropping a and recreating it, the information that b depends on a is
lost.
One way out is to create all views WITH SCHEMABINDING. Had b in the example
above been defined WITH SCHEMABINDING, it would not be possible to drop or
alter a without first dropping b. Then again, this measure may be to
harsh for you.
If you are on SQl 2005 SP2, there is a way out for you: you can use
sp_refreshsqlmodule to refresh the dependency information for a view
or a procedure. You would have to run it for all your views before you
script.
Another option is to simply maintain a build-script the loads the views in
the right order. Note that this script would not be an SQL script - it
would be a script in VB-script, Perl, C# or even a BAT file that reads the
files with the view definition and load them to SQL Server. The files
would of course be retrieved from the version-control system.
In SQL 2008, currently in beta, they have a new style for reference
information, so that information is held by name and not by object id.
Thereby dependencies are not lost when an object id is dropped. I have
not tested whether the tools actually make use of this features.
Personally, I find that the new feature has several shortcomings compared
to the old reference information, particularly with regards to information
on column level.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Copy data from tables with script!

Hello!

I cant figure out how to copy data from tables in my default database to my new automate-generated database.

I can generate Store Procedures, I can generate the Tables but are there anyone one who knows how to copy the data from tables and put it into my new automate-generated shell. The automate-generate funktion i have built just creating a shell/empty database. How to fill it with a sql-script or similar?

Anyone have any tip?

Regards

If you mean you want to script out the data into the same script with the DDL for the tables try the Database Publishing Wizard:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901

|||

It worked perfectly!

Thanks!

copy a table with sql2005

Using managment studio, how can i copy a table and his records, from a
server (2005) to another server (express) ?
I know how to create a script with table structure, but not with data into..
thanks
MauroSome options here: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mauro Miotello" <sviluppo@.ashnet.it> wrote in message news:e1k0kk$9q2$1@.nnrp.ngi.it...
> Using managment studio, how can i copy a table and his records, from a
> server (2005) to another server (express) ?
> I know how to create a script with table structure, but not with data into..
> thanks
> Mauro
>

copy a table with sql2005

Using managment studio, how can i copy a table and his records, from a
server (2005) to another server (express) ?
I know how to create a script with table structure, but not with data into..
thanks
MauroSome options here: http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mauro Miotello" <sviluppo@.ashnet.it> wrote in message news:e1k0kk$9q2$1@.nnrp.ngi.it...[vbco
l=seagreen]
> Using managment studio, how can i copy a table and his records, from a
> server (2005) to another server (express) ?
> I know how to create a script with table structure, but not with data into
.
> thanks
> Mauro
>[/vbcol]

Converting tables to Upper case

Hello, we've an Oracle transition in the pipeline and want to convert
all our database objects to upper case. Any one got a script or
technique (other than manual) to do it?

Many thanks, Kevin.The two statements below should get you started. If you need to rename
indexes in addition to tables then you can change the u.type in the
WHERE clause of the first statement. Run these statements in every
database in which you need to do this. It will generate the code that
you need to run, so copy and paste the results in the query window and
run that.

I ran both statements and eyeballed the results and they looked ok, but
I have not actually tested this by running the results, so you should
go over it yourself as well.

HTH,
-Tom.

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + o.name + ']'', ''[' +
UPPER(o.name) + ']'''
FROM sysobjects o
INNER JOIN sysusers u ON u.uid = o.uid
WHERE o.type = 'U'

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + t.name + '].[' + c.name
+ ']'', ''[' + UPPER(c.name) + ']'', ''COLUMN'''
FROM sysobjects t
INNER JOIN sysusers u ON u.uid = t.uid
INNER JOIN syscolumns c ON c.id = t.id
WHERE t.type = 'U'|||Thanks very much, this works a treat and is a great idea. Apologies
for delay in replying.

kevin.

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message news:<1112379305.650858.281710@.f14g2000cwb.googlegroups. com>...
> The two statements below should get you started. If you need to rename
> indexes in addition to tables then you can change the u.type in the
> WHERE clause of the first statement. Run these statements in every
> database in which you need to do this. It will generate the code that
> you need to run, so copy and paste the results in the query window and
> run that.
> I ran both statements and eyeballed the results and they looked ok, but
> I have not actually tested this by running the results, so you should
> go over it yourself as well.
> HTH,
> -Tom.
> SELECT 'EXEC sp_rename ''[' + u.name + '].[' + o.name + ']'', ''[' +
> UPPER(o.name) + ']'''
> FROM sysobjects o
> INNER JOIN sysusers u ON u.uid = o.uid
> WHERE o.type = 'U'
> SELECT 'EXEC sp_rename ''[' + u.name + '].[' + t.name + '].[' + c.name
> + ']'', ''[' + UPPER(c.name) + ']'', ''COLUMN'''
> FROM sysobjects t
> INNER JOIN sysusers u ON u.uid = t.uid
> INNER JOIN syscolumns c ON c.id = t.id
> WHERE t.type = 'U'

Converting SQL Server Data into XML

Hi Folks!

I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.

My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error.
I am using the following commnd:
exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C:\test.xml" -U -P -c -r -t'

Is there any way I can tune my query to fix that error?

Thanks so much for your help!

-ParulHi Folks!

I am aware of the tedious nature of the select statements,
-Parul

Really tedious Parul,For the first time I am seeing so big a proc... rather a Mamoth ;)|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?
You can increase the max server memory,but I am not sure whether that will help you...check this (http://sqljunkies.com/PrintContent.aspx?type=tutorial&id=0D4FF40A-695C-4327-A41B-F9F2FE2D58F6)|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?
have you tried XPath queries in this case ?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?
check this (http://www.aspfree.com/c/a/MS-SQL-Server/XML-and-the-SQL-2000-Server-part-3-XML-Data-with-XPath-Queries/1/) ...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...
An easy option can be Altova xmlspy 2006.
Try this www.altova.com (http://www.altova.com) and download XMLSPY 2006|||Thanks! BTW, I got my sql query to work! I replaced all the unions with inserts - now I am inserting all the data into an actual physical data, and then running FOR XML EXPLICIT on it. It seems to be working fine now!

I had a question though - do you know if there is a sql command to merge two files. Basically, I want to merge the header for the xml file with actual xml data in another file - is that possible without running any DOS commands?

Thanks so much!

converting sql 2000 script to sql 7. gurus... please help!

We have a customer who got their site developed somewhere else. Well,
this host sent me all of the files along with a sql script to create
all the tables, sps, views, etc. I tried running it on our customer
sql box which is sql 7 and I got a number of errors. When I ran it on
my own machine which is developer 2000, it works fine. So, I've found
the main 3 lines that are causing the problems. Is there a way to
convert these lines to some syntax that is compatible with sql 7?

error 1: Incorrect syntax near the keyword 'ON'.
offending line: ON DELETE CASCADE ON UPDATE CASCADE

error 2: 'scope_identity' is not a recognized function name.
offending line: select @.access_id=scope_identity()

error 3: Incorrect syntax near the keyword 'level'.
offending line: select a.*,b.name level,c.username

I tried on error 3 doing "select a.*, b.name AS level, c.username" but
it still gave the same error.On 5 Aug 2004 06:06:49 -0700, geoff wrote:

>We have a customer who got their site developed somewhere else. Well,
>this host sent me all of the files along with a sql script to create
>all the tables, sps, views, etc. I tried running it on our customer
>sql box which is sql 7 and I got a number of errors. When I ran it on
>my own machine which is developer 2000, it works fine. So, I've found
>the main 3 lines that are causing the problems. Is there a way to
>convert these lines to some syntax that is compatible with sql 7?
>error 1: Incorrect syntax near the keyword 'ON'.
>offending line: ON DELETE CASCADE ON UPDATE CASCADE
>error 2: 'scope_identity' is not a recognized function name.
>offending line: select @.access_id=scope_identity()
>error 3: Incorrect syntax near the keyword 'level'.
>offending line: select a.*,b.name level,c.username
>I tried on error 3 doing "select a.*, b.name AS level, c.username" but
>it still gave the same error.

Hi Geoff,

Yes, it can be done.

re error 1: cascading foreign key constraints are new in SQL Server 2000.
To convert toi SQL Server 7.0, you'll have to remove the foreign key
("REFERENCES") constraint completely and replace it with your own checking
routines in triggers. I believe Books Online has some examples on this; if
not and you do need further assistance, let me know.

re error 2: I usually try to avoid identity so I'm not very experiences
with this, but I believe you can replace scope_identity() with either
IDENNT_CURRENT or @.@.IDENTITY. Do read the info in Books Online for
possible differences!

re error 3: level was a reserved keyword up to SQL Server 7.0. You either
have to usa another column alias, or enclose it in [brackets] or
"quotation marks": select a.*, b.name AS [level], c.username

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Converting Numbers

Hi Everyone,
We have a database that has numbers and we want to CONVERT all the numbers
to 0. How would we go about writing a script that can do such a thing.
Thanks in advance for everyones help.
Jay :)If you actually want to change the values stored in the database, use an
UPDATE statement
Check it out in The Books OnLIne or in any SQL book...
"James Mueller" wrote:

> Hi Everyone,
> We have a database that has numbers and we want to CONVERT all the numbers
> to 0. How would we go about writing a script that can do such a thing.
> Thanks in advance for everyones help.
> Jay :)|||More info needed.
Do you want to change all numeric data type columns from all tables to 0?
If so:
See INFORMATION_SCHEMA.COLUMNS
which returns information about each column
including tableName, columnName and column type.
A cursor, some dynamic SQL and you're done.
"James Mueller" <JamesMueller@.discussions.microsoft.com> wrote in message
news:AD68E5C6-8CEC-4BAD-AD66-850E3C8DC1DD@.microsoft.com...
> Hi Everyone,
> We have a database that has numbers and we want to CONVERT all the numbers
> to 0. How would we go about writing a script that can do such a thing.
> Thanks in advance for everyones help.
> Jay :)|||Yes we had various values ranging from 1 - 65,000. We needed to setup each
one back to 0. We used CBretana's advice and used the UPDATE function. Thi
s
will work for us because we only need to change it one time and that is when
the database and tables are initially created.
Thanks for everyones help.
Newbie (Jay)
"Raymond D'Anjou" wrote:

> More info needed.
> Do you want to change all numeric data type columns from all tables to 0?
> If so:
> See INFORMATION_SCHEMA.COLUMNS
> which returns information about each column
> including tableName, columnName and column type.
> A cursor, some dynamic SQL and you're done.
> "James Mueller" <JamesMueller@.discussions.microsoft.com> wrote in message
> news:AD68E5C6-8CEC-4BAD-AD66-850E3C8DC1DD@.microsoft.com...
>
>

converting mysql 2 sql 2000

how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanksOriginally posted by m.inckle
how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanks
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there any way to do this in a script ???|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.|||Originally posted by m.inckle
is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.
no. as far as my knowledge goes you can't add identity to an existing column thru alter table, u have to add a new column for it.
so the easiest way is to use enterprise manager.