how to ''create new/overwrite existing'' worksheets in xls output?

anyone know how you can do this?

Yes, If I'm not wrong you can use Execute Sql Task in order to create a new one using Excel Connection Manager:

Example:

CREATE TABLE `Z` (
`CODIGO_TERRITORIAL` Byte ,
`CODIGO_REGIONAL` Byte)

|||

Enric is right, you can create the sheet via execute sql task. In order to override it, the easiest way I know is to delete/move the file so the next time the file gets create again. There are lot of threads that tlaks about that.

I have an example that can help you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

how to create database diagram

i have a problem when i want to create diagram. i'll right clik and select add existing table and the box table appear but when i select table and clik button add the message error appear "CoInitialize has not been canceled". what the hell going on. pls help me out. i'm using msql 7.0even i am facing the same problem...can somebody help me on this

Tom

How to create column in existing table

Hi i want to create a store procedure
i have table called ABC which has 10 column and have lot of data now i want
to design a store procedure to add another emplty column into that table
which i will fill with another store procedure that i already have thanksUse Alter Table..ADD
see example
CREATE TABLE ABC (id INT,SomeColumn VARCHAR(49))
SELECT * FROM ABC
ALTER TABLE ABC
ADD SomeOtherColumn int
SELECT * FROM ABC
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You're going to do this once, or all the time? If this is something you're
going to be doing repeatedly, I'd strongly recommend reviewing your
requirements and coming up with a more sane implementation plan.
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:DB18DF5B-16CF-4918-A42D-8406A1617675@.microsoft.com...
> Hi i want to create a store procedure
> i have table called ABC which has 10 column and have lot of data now i
> want
> to design a store procedure to add another emplty column into that table
> which i will fill with another store procedure that i already have thankssql

How to create a table from an existing table.

I'm new to this table and my kniwledge of SQL is of intermediate level.
Could anyone help me/guide me how to create a tble from a nexisting table, without copying any of the records?
Any help is greatly appreciated.
Thanks in advance.
Kalpanatry Select * into <your new table name> from <your old table name> where 1 = 2|||ThankQ very much I have been thinking to include a condition which is always false. Good idea!|||Just remember that this will only copy the table structure and NOT handle any indexes, statistics or foreign keys.|||Thanks very much Paul.
Actually my problem is I'm writing a vb program as part of which I have to apply some query on table1 of db1 and save the results in table2 of db2. As of now I just thought of saving the results in the same database i.e db1. But, my real problem is still unsolved. I wonder if you could help me in this! Any ideas you could give!

Wishes
Kalpana|||It's just the fieldnames & data types, so required properties, default settings, all (check, foreign key, primary key) constraints as well as external structures like indices won't be copied.|||Originally posted by kalpana_lloyd
Thanks very much Paul.
Actually my problem is I'm writing a vb program as part of which I have to apply some query on table1 of db1 and save the results in table2 of db2. As of now I just thought of saving the results in the same database i.e db1. But, my real problem is still unsolved. I wonder if you could help me in this! Any ideas you could give!

Wishes
Kalpana

What have you worked out so far for this problem? Have you figured out how to connect ot your remote server? If you apply the same query on table1 two times what wil lyou call table2 the second time?|||I have two ADOconnection objects, each of which connects to a different database. My source table in in db1 which I have to filter and save in table2 of db2.
Each time the query is executed the name of table2 should be changed.
So, the second time table2 will not be table2, it might change to table3.|||Seems to me you have a few options here.

1. do your query on table1, get the results back to your app, analyse the resultset for datatypes returned, build a create table command in your app, create the table on db2 and then push the data to db2..table2.

2. similar to #1 above but extract the table structure directly from db1, build the table on db2 with the appropriate name change then move the data via your app.

3. Similar to #2 but use bcp to extract the resultset in bulk and then use insert bulk to push data into db2.

4. if your servers are linked together you could use a four part nameing convention to run a query on db1 stroeing the results on db2. To accomodate the need to chage the target table name you would need to execute dynamic sql.|||Thanks. As of now both of my tables are in the same p.c, but in different databases.
Im not able to find any objects which could allow me to do these transactions. As of now Im using ADOs. If you could suggest me which objects in ADO support any of these operations I shall be very grateful.|||okay how about this, The TSQL statment would be something like:

declare @.tblname varchar(50), @.tsql varchar(255)
set @.tblname = 'kalpana_lloyd'
set @.tsql = 'select * into db2..' + @.tblname + ' from db1..table1 where 1 = 2'
exec(@.tsql)

This could be sent as one statment in ADO or wrapped in an stored procedure.|||Thanks very much . I shall try this and see.|||Hint: You will need 2 connection objects, one for each database, unless both are running under the same instance of sql server.

Question: Do you have to create the destination table on the fly, or are you just copying data from table to table? If you have to create the new table on the fly, you may need to create some logic to test for an existing table - if you have Query Analyzer, go into the templates and take a look at the script for creating table, it will have a portion starting with an "IF EXISTS" that checks for the existance of the table name you are trying to create, and drops it from the database...

However, if you are just copying the data over, then the first time you would need to create the table, and each following time you would need an insert rather than "SELECT INTO"

Good luck!|||if the two dbs are on the same server only one connection is needed.|||Thanks Paul. Actually my tables are in Access database. Because one can use T-SQL in VB using ADO's I have posted this doubt in this forum,. Moreover, I could'nt find any apt forum to post my question. I'm trying all my best , what Paul has told is working fine in SQL Server, now I need to see how I can implement this in VB.Please accept my apologies for I haven't made my doubt clear.
Thanks.|||Thanks Paul. Your suggestion has just worked even with Access databases, though both of my databases are in different locations.
This is what I used in VB
"Select * into mailshot.mdb.." & txtTableName & " from Contacts " & strWhereClause
I have been struggling for this since one week. Thanks again.
Kalpana

Originally posted by kalpana_lloyd
Thanks Paul. Actually my tables are in Access database. Because one can use T-SQL in VB using ADO's I have posted this doubt in this forum,. Moreover, I could'nt find any apt forum to post my question. I'm trying all my best , what Paul has told is working fine in SQL Server, now I need to see how I can implement this in VB.Please accept my apologies for I haven't made my doubt clear.
Thanks.

How to create a table and delete it thro codings in asp.net(sql server)

hai,

i want to create a table in a existing database in sql and i have to move data to the table and after my process i have to delete it .This is should be done thro codings.please help me.

Thanks in advance

Viswa

So you need to code it in sql scipts or you want do to it in asp.net c#, vb code

|||

using the code behind vb.net

How to create a SQL ev DB?

Good day All

I'm new to databases with C#

There are lots of good examples that use an already existing SQLev db and vs2005, but I'm trying to use VS2005 standard to create the SQLev DB (table, etc), when I try to add a new item, is see only the .mdf selection. (I don't have Full SQL Server or SQL Express on my machine, if that means anything)

Can you please tell me how or point me to an tutorial

Thanks

Mike Greenway

I overlooked it!

I didn't see the Books Online Download with most of the answers.

Sorry for wasting your time.

For others like me

Microsoft Download Center.

|||

one catch

To make a new ssev db in vs2005 you need to use the "mobile 2003 or ce5.0" project type

else when you use "add new item" the ssev DB type will not be available

so, I have to make the DB in on project and then use it in my windows or winfx project, right?

How to create a SQL ev DB?

Good day All

I'm new to databases with C#

There are lots of good examples that use an already existing SQLev db and vs2005, but I'm trying to use VS2005 standard to create the SQLev DB (table, etc), when I try to add a new item, is see only the .mdf selection. (I don't have Full SQL Server or SQL Express on my machine, if that means anything)

Can you please tell me how or point me to an tutorial

Thanks

Mike Greenway

I overlooked it!

I didn't see the Books Online Download with most of the answers.

Sorry for wasting your time.

For others like me

Microsoft Download Center.

|||

one catch

To make a new ssev db in vs2005 you need to use the "mobile 2003 or ce5.0" project type

else when you use "add new item" the ssev DB type will not be available

so, I have to make the DB in on project and then use it in my windows or winfx project, right?

how to create a new database from an existing database saved to an external hard disk?

Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.

Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.

1. Install SQL Server onto the new server (if you haven't already).

2. Find where the old database files are on the disk. Search the external disk for *.mdf, *.ndf, and *.ldf to find them quickly. Once you find both the mdf (data file) and ldf (log file), as well as any ndf (secondary data file) that might exist for the database you want to restore, copy these files to the data directory of your new SQL server installation (probably "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" for SQL server 2005, but see if there are other mdf and ldf files there first).

3. Attach the copied database. In SQL Server Management Studio, right click Databases in the Object Explorer, and choose Attach. Click the Add button, point it to the mdf of the database, and set the correct file paths in the "database details" pane beneath that.

I'm not familiar enough with Reporting Services to know if this will transfer all of its related goods. You may need to transfer some data from the msdb database off of the old server, if that's where it lives.

how to create a database diagram

I'm looking for a tool (preferably open source) which would allow me to
create an ER diagram from an existing database. My database is hosted so I
don't have EM.
Thanks for any suggestions.Most decent tools will have the ability to reverse engineer the diagrams.
If you have MSDN, then visio (Enterprise Edition) will do it and then
there's Erwin of course (some cheaper ones here:
http://www.freedownloadmanager.org/...ftware
/).
Don't know of any decent free tools for this though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul
Thanks for the info. These solutions are a little out of my price range...
for the price of Erwin I could hire a graphic design artist to draw my model
;) I found a free plugin to WebMatrix at CarlosAG.net called DbDiagrams that
will suffice. A little roundabout, since I don't normally use WebMatrix but
it should do the trick.
Thanks again!
"Paul Ibison" wrote:

> Most decent tools will have the ability to reverse engineer the diagrams.
> If you have MSDN, then visio (Enterprise Edition) will do it and then
> there's Erwin of course (some cheaper ones here:
> http://www.freedownloadmanager.org/...ftwa
re/).
> Don't know of any decent free tools for this though.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

how to create a database diagram

I'm looking for a tool (preferably open source) which would allow me to
create an ER diagram from an existing database. My database is hosted so I
don't have EM.
Thanks for any suggestions.Most decent tools will have the ability to reverse engineer the diagrams.
If you have MSDN, then Visio (Enterprise Edition) will do it and then
there's Erwin of course (some cheaper ones here:
http://www.freedownloadmanager.org/downloads/entity_relationship_diagrams_software/).
Don't know of any decent free tools for this though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul
Thanks for the info. These solutions are a little out of my price range...
for the price of Erwin I could hire a graphic design artist to draw my model
;) I found a free plugin to WebMatrix at CarlosAG.net called DbDiagrams that
will suffice. A little roundabout, since I don't normally use WebMatrix but
it should do the trick.
Thanks again!
"Paul Ibison" wrote:
> Most decent tools will have the ability to reverse engineer the diagrams.
> If you have MSDN, then Visio (Enterprise Edition) will do it and then
> there's Erwin of course (some cheaper ones here:
> http://www.freedownloadmanager.org/downloads/entity_relationship_diagrams_software/).
> Don't know of any decent free tools for this though.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

how to create a data warehouse or data mart

i want to create a data mart from an existing OLTP database. for example northwind or i will create an OLTP database. i dont know how i can create data mart from OLTP database. i want to learn that step by step. help me? please!!There is no step-by-step method for creating data warehouses.
You could try Kimball's one-size-fits-all star schema approach to create a datamart, but I don't hold a high opinon of that.
My recommendation would be read some of the books by Inmon and his associates.|||i read it. but now i have a project. i need simple examples. i saw the nwindmart from the northwind database. but i dont understand how they create it from the northwind.|||Creating a true "data warehouse" is an iterative process that involves both the development team and the end-users. If you have read Inmon's work, then you know that the methodology is to start with a small set of important and related tables, and prune and enhance from there as business needs are determined.
Are you just talking about creating a data cube?|||firstly i must create a simple OLTP database. I will make it like northwind or pubs. then i must create a simple data mart from this OLTP database. this data mart has 4 or 5 dimension. and then i must create a cube that has 3 dimension. i have northwind database and nwindmart. i try to understand that how they create nwindmart from the northwind.

i need simple example for example how can create salesfact table from northwind? they used DTS or ...

i dont read that book exactly. but i read some parts.|||You need to read Books Online and other available documentation, and then come back to the forum with specific questions.

How to count existing items?

I want to check and see if an item exists within one of my tables
before I go and replicate. let's say that "John Doe" already exists in
a names database and the user tries to add him again, i want to notify
the user that this name already exists. also, i want to do this
programatically(sp?) in C#.
any help?Put a unique constraint on the identifying columns. When you try to
insert (using a stored procedure), SQL Server will throw an error
(2627). Handle the error in your C# app.
JLuv wrote:
> I want to check and see if an item exists within one of my tables
> before I go and replicate. let's say that "John Doe" already exists in
> a names database and the user tries to add him again, i want to notify
> the user that this name already exists. also, i want to do this
> programatically(sp?) in C#.
> any help?|||You could do this in this manner
USE Northwind
GO
IF NOT EXISTS
( SELECT (LastName)
FROM Employees
WHERE ( LastName = 'Fuller'
AND FirstName = 'Andrea'
)
) INSERT INTO Employees
( LastName
, FirstName
)
VALUES
( 'Fuller'
, 'Andrea'
)
Your application can check the RowCount (RowsAffected) to determine if more
0 (zero) rows were inserted. Then message the user appropriately.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"JLuv" <JLuv3k6@.gmail.com> wrote in message news:1151692692.332535.294860@.75g2000cwc.google
groups.com...
>I want to check and see if an item exists within one of my tables
> before I go and replicate. let's say that "John Doe" already exists in
> a names database and the user tries to add him again, i want to notify
> the user that this name already exists. also, i want to do this
> programatically(sp?) in C#.
> any help?
>|||i've never tried that before. is this method called a "unique
constraint"? what should i search for to find information on this?
Stu wrote:
> Put a unique constraint on the identifying columns. When you try to
> insert (using a stored procedure), SQL Server will throw an error
> (2627). Handle the error in your C# app.
>
> JLuv wrote:|||i did something like that. i went and updated the database with the
exact same information it already holds. it returns the correct # of
columns affected using ExecuteNonQuery().
Arnie Rowland wrote:
> You could do this in this manner
> USE Northwind
> GO
> IF NOT EXISTS
> ( SELECT (LastName)
> FROM Employees
> WHERE ( LastName = 'Fuller'
> AND FirstName = 'Andrea'
> )
> ) INSERT INTO Employees
> ( LastName
> , FirstName
> )
> VALUES
> ( 'Fuller'
> , 'Andrea'
> )
> Your application can check the RowCount (RowsAffected) to determine if mor
e 0 (zero) rows were inserted. Then message the user appropriately.
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "JLuv" <JLuv3k6@.gmail.com> wrote in message news:1151692692.332535.294860@.
75g2000cwc.googlegroups.com...|||You could also use the 'Primary Key' -it is by design a 'unique constraint'.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"JLuv" <JLuv3k6@.gmail.com> wrote in message
news:1151694549.094788.110810@.h44g2000cwa.googlegroups.com...
> i've never tried that before. is this method called a "unique
> constraint"? what should i search for to find information on this?
>
> Stu wrote:
>|||Not columns affected -BUT Rows Affected. Notice the use of IF NOT EXISTS.
That prevents adding a row that meets the WHERE clause criteria.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"JLuv" <JLuv3k6@.gmail.com> wrote in message
news:1151694709.234122.133440@.75g2000cwc.googlegroups.com...
>i did something like that. i went and updated the database with the
> exact same information it already holds. it returns the correct # of
> columns affected using ExecuteNonQuery().
>
> Arnie Rowland wrote:
>|||yea, i mean row, not column. and i'll try out IF NOT EXIST
Arnie Rowland wrote:
> Not columns affected -BUT Rows Affected. Notice the use of IF NOT EXISTS.
> That prevents adding a row that meets the WHERE clause criteria.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "JLuv" <JLuv3k6@.gmail.com> wrote in message
> news:1151694709.234122.133440@.75g2000cwc.googlegroups.com...|||The only real reason to use this 'IF NOT EXISTS' form is if you wish to have
an ELSE -such as update an existing record.
Otherwise, just insert the data and let the unique constraint or Primary key
force an error and your application can capture the error and react
accordingly.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"JLuv" <JLuv3k6@.gmail.com> wrote in message
news:1151695591.304350.187130@.i40g2000cwc.googlegroups.com...
> yea, i mean row, not column. and i'll try out IF NOT EXIST
>
> Arnie Rowland wrote:
>

how to copy one table from one database to another on different servers?

Hello.

I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server. I'm trying to use a SELECT INTO statement. Any idea how to do this?

I've tried

SELECT * INTO DestinationServer.dbo.DestinationDB.DestinationTable
FROM SourceTable AS SourceTable_1

But this doesn't work, saying there are too many prefixes.

Any idea how to do this?

If you are using SQL2000, you use DTS to copy the table or SSIS if using SQL2005. Alternatively you can use OPENROWSET in the target database to read the table in the source database.

|||

(1) SELECT .. INTO is different from INSERT INTO SELECT.

SELECT INTO tries to create the table in the INTO clause and then inserts the data from the SELECT into the table.

INSERT INTO SELECT does not create the destination table. It will directly try to insert the result of the SELECT into the destination table.

(2) The naming convention you have "DestinationServer.dbo.DestinationDB.DestinationTable" is incorrect. its ServerName.Database.dbo.Table. You have it the other way.

Depending on whether you are executing this from source or destination you have to use appropriate 4-part name (basically for whichever (source/target) is remote).

(3) Spell out all the column names if you are using INSERT INTO SELECT.

|||

Thanks guys. It's all working now.

|||how to copy one table from one database to another on different servers?

how to copy design of table

i want to create a new table with the existing design of any another table without copying the data of that table in query analyzer not by enterprise managerselect *
into newtable
from anyanothertable
where 1=0:)|||Be aware that Rudy's technique, while fast, will not copy indexes, triggers, or constraints, or identities.|||is this homework?

You seem to know how to script a table in EM, so why not?|||I would just create a script file for the table and it's indexes, etc. and run it in the new server/database. I would do this creation of the script by right-clicking on "all tasks" -> "generate SQL script" in EM. I would create the script that way because I am too lazy to write it by hand/BOL lookups.|||Hi,
we can get the exact schema of procedures, triggers using sp_helptext [object name] but to get the schema of the existing table we cant use sp_helptext. so you can use the procedure mentioned in the below link to know the DDL of the current table.

http://www.koders.com/sql/fid7D3195CD2CA27581E1073314FF58005D2DA4E82B.aspx?s =datediff

Ex:
The name of the procedure used is sp__revtable
sp__revtable [table_name] will give schema of the table. By using it, you can create the schema for the new table|||thanks i got it|||Hi,
we can get the exact schema of procedures, triggers using sp_helptext [object name] but to get the schema of the existing table we cant use sp_helptext. so you can use the procedure mentioned in the below link to know the DDL of the current table.

http://www.koders.com/sql/fid7D3195CD2CA27581E1073314FF58005D2DA4E82B.aspx?s =datediff

Ex:
The name of the procedure used is sp__revtable
sp__revtable [table_name] will give schema of the table. By using it, you can create the schema for the new table

that proc is for sybase, not sql server. parts of it may work, probably not the whole thing, though.|||that proc is for sybase, not sql server. parts of it may work, probably not the whole thing, though.Oh gee, so you mean that I can't take a control computer from a Toyota and put it into a Lexus?

-PatP