Creating a table script that includes the data in the table

Hello,

I'm having a bear of a time moving my data from one database to another. Unfortunately both these databases are at webhosting providers so I don't readily have access to backup images. I might be able to get the new provider to put the backup image somewhere where I can reach it but the old provider is playing dead so I can't get to any backup image I could generate.

I've tried using Enterprise Manager for SQL2000 to move data from one to another. This grinds away for a while and eventually errors out with permission problems. I will try this again tonight as the provider has attempted to give me the needed permissions. In the meantime I'd like to try to pull the data out of the database just in case it goes belly-up. I've tried to create a script for my tables but all I get is the table structure, no data.

I don't have access to a full Enterprise Manager right now, only SQL Server Management Studio Express (someone needs to shorten that name :).

I'm a big SQL Server fan but it sure is easy to create one big SQL script for a MySQL databsae that you pull out of one db and apply to another one. Poof! Cloned copy. How do I do this with MSSQL and freely available tools? Command line is fine. Surely this is a problem that people have all the time?

Thanks,

Sander

Hi,

for a TSQL solution this could do the trick for you:

http://vyaskn.tripod.com/code.htm#inserts

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hello,

thanks for posing this solution! I succeeded in the meantime to copy the data using EM but I still want this tool to work so I'll give that a try as well.

Thanks,

Sander

Creating a project from an existing Database

I have the full blown Microsoft SQL Server Management Studio (MSSMS) installed on my workstation.

We have a number of existing databases that I'd like to manage with MSSMS and put into source control.

How do I get MSSMS to "import" or "Convert" an existing SQL server 2000 database into a project that I can manage with MSSMS? We have not used Source safe up to this point, but would like to start doing so now.

This seems like it ought to be explained well up front in any discussion of converting from SQL 2000 to SQL 2005 or installing 2005, but I can't find ANYTHING useful in the BOL or other help.

Thanks for any help you can give me.

-Rob Marmion

All what you need is to script your code (and other objects, if you'd like) into separate files and add them to the SSMS project (simply drag'n'dropping). The problem is that SSMS is not able to script your database objects into separate files, only into one, but you can use Enterprise Manager "Generate SQL Script" feature.

Creating a new login with limited rights

what combonation do you use to create a user with rights to add alertsand to
create backup jobs. This user should be able to modify databases or other u
sers.Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.|||-- Hari wrote: --
Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.
Bummer, Since I'm new at this my role as DBA needs to be limited until I lea
rn more. What is the posibility of creating a user on our live server that c
an creat backup jobs, assign notification in the jobs created, and to view t
he event long on the s
erver itself, not the SQL log. Also, I want to limit the ability to change a
ny settings as far as the databases are concerned.
My login I created contains the following.
No server roles are selected
All databases are selected
Public-is checked
db_securityadmin is checked
db_backupoperator is checked

Creating a Monthend Database

Before I moved all the Access Databases to SQL I was able to create a monthend database for each database we had. It wasn't too time consuming as it was a simple copy and paste each month. Now that everything is moved over to SQL I had told the Acctg De
pt that I would prefer not to continue with the monthend companies as it is more time consuming to administer. All they use the monthend copies for are for reference if needed throughout the month. When I questioned how much they actually refer back to
it, it is not very often. They were quite persistent and old habits are hard to break so I need to find an efficient way of creating the monthend databases and then copying them each month. Does anyone have any suggestions? Your input is great apprecia
ted!
Thanks
Deb
If your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb

Creating a Monthend Database

Before I moved all the Access Databases to SQL I was able to create a monthend database for each database we had. It wasn't too time consuming as it was a simple copy and paste each month. Now that everything is moved over to SQL I had told the Acctg Dept that I would prefer not to continue with the monthend companies as it is more time consuming to administer. All they use the monthend copies for are for reference if needed throughout the month. When I questioned how much they actually refer back to it, it is not very often. They were quite persistent and old habits are hard to break so I need to find an efficient way of creating the monthend databases and then copying them each month. Does anyone have any suggestions? Your input is great appreciated
Thank
DebI'm going to start with an assumption. What you are
talking about in a monthend database (it means different
things to different people) is a reporting thing that you
transfer to the accounting section.
If thats the case then you may want to consider something
like DTS. You can either run this yourself or create a
job in SQL Agent.
With DTS you can set it up so you do a database query
that exports into say excel, which means you do not have
to cut and paste.
If all else fails however you can go back to how you were
doing it until you get something more concrete.
J
>--Original Message--
>Before I moved all the Access Databases to SQL I was
able to create a monthend database for each database we
had. It wasn't too time consuming as it was a simple
copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer
not to continue with the monthend companies as it is more
time consuming to administer. All they use the monthend
copies for are for reference if needed throughout the
month. When I questioned how much they actually refer
back to it, it is not very often. They were quite
persistent and old habits are hard to break so I need to
find an efficient way of creating the monthend databases
and then copying them each month. Does anyone have any
suggestions? Your input is great appreciated!
>Thanks
>Deb
>.
>|||If your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb

Creating a Monthend Database

Before I moved all the Access Databases to SQL I was able to create a monthe
nd database for each database we had. It wasn't too time consuming as it wa
s a simple copy and paste each month. Now that everything is moved over to
SQL I had told the Acctg De
pt that I would prefer not to continue with the monthend companies as it is
more time consuming to administer. All they use the monthend copies for are
for reference if needed throughout the month. When I questioned how much t
hey actually refer back to
it, it is not very often. They were quite persistent and old habits are har
d to break so I need to find an efficient way of creating the monthend datab
ases and then copying them each month. Does anyone have any suggestions? Y
our input is great apprecia
ted!
Thanks
DebIf your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

GO

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

GO

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Creating a Bottleneck.

I’m using Benchmark factory (TPC-C) on a server that has 3 instances of SQL
Server. Each instance has between 1-3 databases on it. Have set min and max
server memory to 512MB.
What I want:
When the TPC-C transactions are executed I want them to create a memory
and a disk I/O bottleneck.
Apart from the above memory setting what else can I change so that it will
result in a I/O bottleneck?
Cheers!
sqlcatz
If the SQL instance only has 512MB, you almost always get a memory and/or I/O
bottleneck with TPC-C when you configure the number of warehouses to be
larger than 10 and include sufficient number of users. If you want to
increase the I/O load, you can reduce the ratios of the read-only
transactions (OrderStatus and StockLevel) in the transaction mix. If you
don't have a really fast I/O subsystem that has a lot of cache, you should
see I/O bottleneck as long as the ffective size of the test database is
significantly larger than the memory size.
Linchi
"SQLCatz" wrote:

> I’m using Benchmark factory (TPC-C) on a server that has 3 instances of SQL
> Server. Each instance has between 1-3 databases on it. Have set min and max
> server memory to 512MB.
> What I want:
> When the TPC-C transactions are executed I want them to create a memory
> and a disk I/O bottleneck.
> Apart from the above memory setting what else can I change so that it will
> result in a I/O bottleneck?
> Cheers!
> sqlcatz
>
|||Hello Linchi!
Thank you for the suggestion.
I tried it.
Apart from getting memory related errors (expected) - and lots of deadlocks
I dont get anything else. On checking the AvgDiskQueueLength - it's just
2.73. I was expecting a much higher value. What else can I do to get the
bottleneck?
Cheers!
sqlcatz

Creating a Bottleneck.

I’m using Benchmark factory (TPC-C) on a server that has 3 instances of SQ
L
Server. Each instance has between 1-3 databases on it. Have set min and max
server memory to 512MB.
What I want:
When the TPC-C transactions are executed I want them to create a memory
and a disk I/O bottleneck.
Apart from the above memory setting what else can I change so that it will
result in a I/O bottleneck?
Cheers!
sqlcatzIf the SQL instance only has 512MB, you almost always get a memory and/or I/
O
bottleneck with TPC-C when you configure the number of warehouses to be
larger than 10 and include sufficient number of users. If you want to
increase the I/O load, you can reduce the ratios of the read-only
transactions (OrderStatus and StockLevel) in the transaction mix. If you
don't have a really fast I/O subsystem that has a lot of cache, you should
see I/O bottleneck as long as the ffective size of the test database is
significantly larger than the memory size.
Linchi
"SQLCatz" wrote:

> I’m using Benchmark factory (TPC-C) on a server that has 3 instances of
SQL
> Server. Each instance has between 1-3 databases on it. Have set min and m
ax
> server memory to 512MB.
> What I want:
> When the TPC-C transactions are executed I want them to create a memory
> and a disk I/O bottleneck.
> Apart from the above memory setting what else can I change so that it will
> result in a I/O bottleneck?
> Cheers!
> sqlcatz
>|||Hello Linchi!
Thank you for the suggestion.
I tried it.
Apart from getting memory related errors (expected) - and lots of deadlocks
I dont get anything else. On checking the AvgDiskQueueLength - it's just
2.73. I was expecting a much higher value. What else can I do to get the
bottleneck?
Cheers!
sqlcatz

Creating a Bottleneck.

Iâ'm using Benchmark factory (TPC-C) on a server that has 3 instances of SQL
Server. Each instance has between 1-3 databases on it. Have set min and max
server memory to 512MB.
What I want:
When the TPC-C transactions are executed I want them to create a memory
and a disk I/O bottleneck.
Apart from the above memory setting what else can I change so that it will
result in a I/O bottleneck?
Cheers!
sqlcatzIf the SQL instance only has 512MB, you almost always get a memory and/or I/O
bottleneck with TPC-C when you configure the number of warehouses to be
larger than 10 and include sufficient number of users. If you want to
increase the I/O load, you can reduce the ratios of the read-only
transactions (OrderStatus and StockLevel) in the transaction mix. If you
don't have a really fast I/O subsystem that has a lot of cache, you should
see I/O bottleneck as long as the ffective size of the test database is
significantly larger than the memory size.
Linchi
"SQLCatz" wrote:
> Iâ'm using Benchmark factory (TPC-C) on a server that has 3 instances of SQL
> Server. Each instance has between 1-3 databases on it. Have set min and max
> server memory to 512MB.
> What I want:
> When the TPC-C transactions are executed I want them to create a memory
> and a disk I/O bottleneck.
> Apart from the above memory setting what else can I change so that it will
> result in a I/O bottleneck?
> Cheers!
> sqlcatz
>|||Hello Linchi!
Thank you for the suggestion.
I tried it.
Apart from getting memory related errors (expected) - and lots of deadlocks
I dont get anything else. On checking the AvgDiskQueueLength - it's just
2.73. I was expecting a much higher value. What else can I do to get the
bottleneck?
Cheers!
sqlcatz

Creating 1 backup for multiple DB

is there a way to create one backup from multiple databases ?

Hello,

If you mean being able to append multiple database backusp to a single backup file, then yes this is possible. You do this via NOINIT:

backup database Database_A to disk = 'C:\DBBackupFile.BAK'

backup database Database_B to disk = 'C:\DBBackupFile.BAK' WITH NOINIT

Then, the file C:\DBBackupFile.BAK will contain two backup sets. To reference either of the backup sets, you then need to use the FILE clause of the restore command as such:

restore filelistonly from disk = 'C:\DBBackupFile.BAK' with FILE=2 (this referrs to Database_B)

Cheers,

Rob

|||

If what you are looking for is a backup at one point in time for multiple databases, so that they can all be brought back to a common point in time, the way to accomplish that is with marked transactions.

There is not a mechanism to create a single backup spanning multiple databases.

You can, however use the full recovery model to roll a group of databases forward to a single point in time.

The mechanism for making sure that this point is exactly coordinated across databases is to use the

BEGIN TRANSACTION 'MyTransaction' WITH MARK

statement to begin a marked transaction. If that is a distributed transaction (accomlished by touching each of the databases within the transaction), then the mark will be saved in each database's log.

You can then restore each database independantly, and apply logs using the STOPATMARK = 'MyTransaction' clause to cause all databases to roll forward to exactly the same point in time.

Creating "installation" script

Hi all,

I′m newbie with SQL Server.

I need to create a installation script SQL
(creating tables, indexes, permissions, databases).
This script calls other scripts.
In Oracle I use @.script_name.sql, but in SQL Server
I don′t know to do this.

Other question: Is there a way to call the Query Analyzer
in .BAT file, where the .BAT file calls installation scripts SQL.

Thank U very much,

[]′s
Yes sure you can do this, you have to call the equivalent to sqlplus.exe for SQL Server, its OSQL.EXE. The -o Option takes an input file with the statements that you want to execute. Other options are described in detail -S for the server -U for the User -P for the password and so on.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank u very much!!!!

[]′s

Create view with data from multiple servers

Hi everyone,

I have 5 servers, all with identical databases just different data. I
have a rather lengthy SQL statement (in a View) to hit one database and
pull-in certain data, but I'd like to somehow run this same SQL
statement within the view but hit all 5 servers so we don't have 5
different versions of this data to mess with.

I'm not opposed to creating an update query in a stored procedure to
hit all 5 databases and update a table or even do this within a DTS,
but I'd prefer to keep it as simple as possible and as dynamic so the
users can simply run the view and get live data anytime based on all 5
tables.

Is this possible ?

Thanks,

rlanglySee "Creating a Partitioned View" in BOL.

AMB

--
Message posted via http://www.sqlmonster.com|||Ringo Langly (rlangly@.gmail.com) writes:
> I have 5 servers, all with identical databases just different data. I
> have a rather lengthy SQL statement (in a View) to hit one database and
> pull-in certain data, but I'd like to somehow run this same SQL
> statement within the view but hit all 5 servers so we don't have 5
> different versions of this data to mess with.
> I'm not opposed to creating an update query in a stored procedure to
> hit all 5 databases and update a table or even do this within a DTS,
> but I'd prefer to keep it as simple as possible and as dynamic so the
> users can simply run the view and get live data anytime based on all 5
> tables.

While you could set up a distributed partitioned view of the servers,
as suggested by Alejandro, but distributed partitioned views is
not really for the faint of heart. Anyway, it requires that the
tables has a partitioning column.

You can still up a view over the tables with UNION ALL, but the view
may not be updatable, and it sounds like you want to do this for an
update operation, but I might be misunderstanding something.

It's a little difficult to say that much more with that small amount
of information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create View from 2 servers.

Hi,
Can we create a view from databases residing on different servers?
Thanks,
Harriet.yes, you can do that. Setup one server as a linked server on the other one and you can access objects in the linked server by using their full names (server.database.owner.objectname). See for more info on setting up a linked server in books online.|||hey, thanks a ton buddy. It was of great help.
Thanks once again

btw, do you have any idea as to how I would be able to run udfs on version 7.0?
Cheers!!
Hsm