Exporting MSSQL database

Hello all

Before I begin - I have very little experience with SQL and databases in general, so please if you are kind enough to offer your advice, please be kind to break it down, even if it seems obvious to you.

Here is the problem - I am hosting a DotNetNuke website with a certain company, whom I would like to leave. In order to migrate my site to another hosting provider I need a copy of my DB, in a .mdf file format (according to the new hosting provider), which I am unable to get from my current provider, why I do not know, and if truth be told dont' particularly care. Anyways. What can I do? Another problem is that I can only access my existing DB thru hosting provider's web portal, not MSSQL Server Management studio. I have an option to export into .CSV file or .XML file. But what do I do with them after that? Bring it into the studio, save it as .mdf? This all probably sounds really funny, and naive to someone who knows about these things, but I am really frustrated. Many thanks in advance.

Quote:

Originally Posted by yellofsh

Hello all

Before I begin - I have very little experience with SQL and databases in general, so please if you are kind enough to offer your advice, please be kind to break it down, even if it seems obvious to you.

Here is the problem - I am hosting a DotNetNuke website with a certain company, whom I would like to leave. In order to migrate my site to another hosting provider I need a copy of my DB, in a .mdf file format (according to the new hosting provider), which I am unable to get from my current provider, why I do not know, and if truth be told dont' particularly care. Anyways. What can I do? Another problem is that I can only access my existing DB thru hosting provider's web portal, not MSSQL Server Management studio. I have an option to export into .CSV file or .XML file. But what do I do with them after that? Bring it into the studio, save it as .mdf? This all probably sounds really funny, and naive to someone who knows about these things, but I am really frustrated. Many thanks in advance.

Well if you could get into the management studio it'd be easy you just create a backup of the database. Otherwise if you can only save the data in each table in your database to a .csv file then it'll be more tricky depending on how many tables are in the database.

First of all you'll need to brush up on your SQL (don't worry it's easy). Start by creating a new database (CREATE DATABASE myDB), then you'll have to create each table manually and define all the fields (unless the web portal will script the CREATE TABLE statements for you) . Then for each table you'll need to import the data from the .csv file into the table using a BULK INSERT statement.

Once all this is done you have your database and you'll find the .mdf file somewhere in the SQL Server directory.

This is only a rough guide but hope it point you in the right direction.

exporting event viewer logs to db..

l am trying to see how to export event veiwer log to database(mssql) and incremental updates.
sql MS server and with window 2000
if anyone knows how to do this in .net or third party software pls let me know.Are you talking about event viewer logs on the server or off of workstations?

Exporting diagram

When using the DTS to copy a db from my local MSSQL to the remote server (connecting via IP address) everything is copied ok except the diagram laying out the relations.
The table relations *are* copied because the damn thing works as expected also on the remote server.

I figured out that the diagrams are saved in the sysfile 'dtproperties' but when copying using the DTS query tool from local to remote this table is not visible.

I need to remove some relations on certain tables when bulk loading new data and was just wondering how I got to copy the diagram (as opposed to creating a new diagram on the remote server and adding relations manually).

Thanks a lot for any ideas,
Nielsdtproperties doesnt show up in the query builder. just write it by hand "select * from dtproperties"
also, if you dont create the destination file/object (i.e. flat text file) prior to running this it will complain about an object not being found. for instance if you name your destination file dtproperties.txt in the exporter but it doesnt exist it will complain -- at least it did for me.

Exporting DB as SQL using SQL Server 2005

Hi all,

I have a MSSQL database running on my SQL 2005 server and I want to export the entire contents to a file as SQL statements with INSERT statements. How do I do this?

I've discovered how to export the contents as a flat comma deliminiated file (although only one table at a time) and how to export the CREATE statements in SQL for each table but so far I cannot find a way to just export the entire DB as SQL.

Thanks!

There are no SQL Server tools that will create the INSERT statements for you...but it should be fairly staightforward to create a C# program that would gererate the CREATE TABLE statements using SMO and then code to retrieve the data for generating the INSERT statements.

Thanks,

Peter Saddow

|||

Hi,

tzake a look at this one here: http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Just to give you an example SMO for creating table scripts (DDL) is quite easy, that a sample posted a few days ago:

string Script = new Microsoft.SqlServer.Management.Smo.Server(".").Databases["Adventureworks"].Tables["SomeTable"].Script();

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Exporting data from SQL Server to Excel from a Stored Procedure

I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.

I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.

Any ideas

ThanksI'd use the Data | Get External Data | New Database Query menuitem in Excel. When you get to MS-Query, don't select any tables. Use the SQL button and type in the EXECUTE command for the stored procedure. Save this query and then the users can simply execute it!

-PatP

Exporting data between databases

Just starting out with MSSQL 2000 and have a bit of a problem.
I have a remote database that is of the same design of a local database. What I want to do is update one particular field of each record in my local database with data from the remote one. Of course the primary key of each of the records from the remote dbase and the local dbase must match (as even though the remote database has the most up-to-date information, it has more records in it than my local one (but I do not require the additional records).

How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?

Any help would be appreciated!

Thanks.If you have ACCESS 2000 or 2002 you can create two linked tables.

One to each table and then use access to run your update, then you can run you insert of the additional fields at the same time.|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records? And using Inner Join in the update statement.

Best regards!|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records?

Best regards!

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the mysql table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If mysql can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:

> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the mysql table's strucutre and it was also set to varbinary(8)
> Please help.
>sql

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the MySQL table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If MYSQL can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:
> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the MySQL table's strucutre and it was also set to varbinary(8)
> Please help.
>