How to create spool in PL/SQL

I want to create many different spools in PL/SQL programs, therefore i can't use 'spool' command for create a single in .sql file.
Can any one help me? Thx alot...Urgent!Originally posted by wwwdavy
I want to create many different spools in PL/SQL programs, therefore i can't use 'spool' command for create a single in .sql file.

Can any one help me? Thx alot...Urgent!
Perhaps the UTL_FILE package is what you need? This allows PL/SQL to open, read/write and close files on the server.|||Originally posted by andrewst
Perhaps the UTL_FILE package is what you need? This allows PL/SQL to open, read/write and close files on the server.

That's GOOD! Thx very much! Nice to meet u ^_^

how to create report by month from vb

hi all, i m using this code to display report of month selected from combo1
it shows records but only single of current month and moreover if there is no record of a month it shows previous result.
plz help me soon
bye

///////////
mon = Combo1.Text
cmd.CommandText = "Select * from travel"
rs2.Open cmd, , adOpenStatic, adLockReadOnly
Do While Not rs2.EOF
mo = Format(rs2.Fields("report_date"), "mmm")

If mo = mon Then

crxreport.RecordSelectionFormula = "{travel.travel_id} = " & rs2.Fields("travel_id")
CRViewer1.ReportSource = crxreport

End If

rs2.MoveNext

Loop
rs2.Close

CRViewer1.Zoom (100)
CRViewer1.ViewReportu cn do it easily by inserting the required field of that month into a temp table.

& then link the temp table to ur report

i think it will do|||thanks for response,
sorry i not understand what u mean by temp table
and how i can pass month name from combo box in vb to crystal report for displaying record of that month

waiting for reply
bye|||insert a combobox containg all the month in the form where u u load ur report .
Then make a query(for ur defined month from the combobox) 2 select the data from the original .insert these data 2 a temp table which is linked 2 the crystal report.

Then load the report.

bye.|||thanks
please tell me what is problem in my code given above
or give some sample code
to pass month name from vb form
to report
thankssql

How to create ODBC to SQLServer in a LAN accessed by another LAN through gateway ?

Hi all!
I have two LAN connected with a single PC with two network cards. IP
addresses are 10.0.0.x for the first LAN and 192.168.0.x for the
second. I have a SQLServer at 192.168.0.109 and I would like to define
an ODBC that point to it from the other network, for example on a PC
with IP 10.0.0.164. TCP/IP ping works fine from 10.0.0.164 to
192.168.0.109, but ODBC doesn't work!
Anyone suggest some ideas or point me in the right direction ?
Thanks in advance
Eugenio ALESSIEugenio,
Do you have firewall between this networks?
try from 10.0.0.164: telnet 192.168.0.109 1433.
--
Regards,
Rodrigo Fernandes
"eugenio alessi" wrote:
> Hi all!
> I have two LAN connected with a single PC with two network cards. IP
> addresses are 10.0.0.x for the first LAN and 192.168.0.x for the
> second. I have a SQLServer at 192.168.0.109 and I would like to define
> an ODBC that point to it from the other network, for example on a PC
> with IP 10.0.0.164. TCP/IP ping works fine from 10.0.0.164 to
> 192.168.0.109, but ODBC doesn't work!
> Anyone suggest some ideas or point me in the right direction ?
> Thanks in advance
> Eugenio ALESSI
>|||No, I haven't any firewall...
Is there some user access policy to connect to SQLServer (I use sa
nopassword...)
Eugenio

how to create array column and how to retrive in sqlserver

hi

i am using database sqlserver,
i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server

1.how to create array column in a table using sqlserver

if possible how can i use select query for that

there is no array concept in sql server. you can however store the values as a concatenated string with a delimiter and use some custom function to parse through the string to split them up.|||

Try the link below for samples using the string functions in SQL Server and Oracle PL/SQL is closer to C++ than T-SQL. I would also look at Ken Henderson books at my local bookstore run a search for String functions in the BOL(books online). Hope this helps.

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||Why do you want to store multiple values in a single column? Thishas some pretty huge implications from a relational modelingstandpoint, not to mention the fact that it's going to kill performanceif you ever need to query the thing. If you can post someinformation about what business problem you're trying to solve, I'mcertian we can help you find a better solution.

|||

Sorry I forgot I have a link with SQL Server Arrays. Try the link below. Hope this helps.

http://www.sommarskog.se/arrays-in-sql.html

How to create an table with composite Key?

Hi all,

well i want to have an web-based database application in which in an single webform , i need to insert the values to 5 different tables.

here empid is primary key (PK) in one table and in the rest of the tables its an Foreign Key(FK)

my question is, how can i create these tables with composite key?

ie, i want to creat an table which will have FK and as well another key which both will uniquely idenitfies a row.

Well i am using SQL server 2000 and i wish to create these tables using the enterprise manager.

here are the tables

1) empidtable

empid,fname,lname

2)empcountrytable

empid,country

3)empvisatable

empid,visa

4)empdoctable

empid,doc

5)empfile

empid,filename,filecontenttype,filesize,filedata

Plz do help me

thanx in advance

If you're using Enterprise Manager, go to Design View of the table anduse control + click to select all the columns you want as part of thecomposite key, then right-click Set Primary Key.
|||The table with the composite key will have all the primary key columns of the five tables because a composite index must be on one table. You can have up to 16 columns and 900bytes so if you are using unicode you can only have NVARCHAR (450). The alternative which is used by Microsoft Consulting is to create a small Clustered index and add the column of the clustered index in all your nonclustered index on the table. You can use the index tuning Wizard which is part of the profiler to tell you the indexes you need on a table. Hope this helps.|||

Hey thanks for that.

will try to implement the same

How to create a single reusable alert in SQL Server Agent?

Hi,
I have numerous SQL scripts that run as jobs under SQL Server Agent.
I've been playing with the alert system and got it working using a
test script.
However, it seems that it's not possible to create a single alert and
associate it with every script.
When right-clicking on a job under SQL Server Agent in SSMS and
selecting the 'Alerts' tab and the 'Add...' option, there is no way
to
select an alert that I have already created. So, this forces me to
create a new alert, which basically does exactly the same thing
(raises an alert when the job message contains the word 'error') as
one that I created previously, meaning the only difference will be
the
alert name.
The problem is that I have over a two dozen scripts that I want to
set
an alert on.
Am I missing something or are multiple duplicate scripts the right
way
to go?
Thanks,
Frank.
You can also write a VBScript that sends email alerts using SMTP. This way
you can pass parameters like probably the names of the jobs and call this
script inside your SQL job. This is what I did for log shipping alerts for a
clustered SQL Server 2000 since IMAP is not supported in this scenario
<francis.moore@.gmail.com> wrote in message
news:801cbe16-c882-4f96-bfb6-7dec556ddd50@.j44g2000hsj.googlegroups.com...
> Hi,
> I have numerous SQL scripts that run as jobs under SQL Server Agent.
> I've been playing with the alert system and got it working using a
> test script.
> However, it seems that it's not possible to create a single alert and
> associate it with every script.
> When right-clicking on a job under SQL Server Agent in SSMS and
> selecting the 'Alerts' tab and the 'Add...' option, there is no way
> to
> select an alert that I have already created. So, this forces me to
> create a new alert, which basically does exactly the same thing
> (raises an alert when the job message contains the word 'error') as
> one that I created previously, meaning the only difference will be
> the
> alert name.
> The problem is that I have over a two dozen scripts that I want to
> set
> an alert on.
> Am I missing something or are multiple duplicate scripts the right
> way
> to go?
> Thanks,
> Frank.
|||Tibor,
Thanks for the response.
I'm still confused, although I can see what you are saying.

> When an alert fires, you can specify to execute a job.
I can see that now, but what I want to do is execute a job using SQL
Server Agent and then if that job fails, I want it to trigger an alert
that emails me the fact that the job has failed. I've had a few
occasions recently where batch jobs have failed and it hasn't been
noticed until a day or so later.
In SSMS, when selecting a job under SQL Server Agent | Jobs and right-
clicking, you can get to a Properties dialog for that job.
On that dialog there are some tabs, General, Steps, Schedules, Alerts,
Notifications and Targets.
Selecting Alerts give you a page with a button titled 'Add...' at the
bottom of it.
Clicking 'Add...' brings up a 'New Alert' dialog.
I am expecting to enter details in there to create an alert for that
job.
It allows me to do this once, but when I go to the next job, to create
a similar alert for that job, I can neither pick the alert that I just
created, nor can I create another alert similar to the pervious one as
it says that one like this already exists.
What I would really like to do is create one alert (i.e. when message
contains text like 'error') and then select that to be the alert that
I can use for that job and possibly others as well. However, it
doesn't seem to work like that. So, I'm still confused.
So, is it possible to use alerts as I want to in SQL Server 2005?
And if not, how would you let an operator know that a batch job has
failed (preferably by email)?
Many thanks for your time,
Frank.
|||Tibor,
Once someone points it out to you it's becomes so blatantly
obvious :-)
You're a star!
Many thanks,
Frank.
|||On Nov 27, 12:40 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I'm glad you "see the light", Frank.
> Actually, I've always been confused what the "Alert" tab does for a job. I do know what alerts are
> (EventLog, Perfmon and now WMI), but I just failed to understand why we would have an alert tab for
> the job. It just felt ... backwards. You configure and alert, and then what job to fire. So your
> post made me investigate what this alert tab (for a job) does and indeed it doesn't bring any new
> functionality, it just allow us to configure that this job is to be fired for an alert - in a pretty
> backwards kind of way (and indeed the same config as you do for the alert).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <francis.mo...@.gmail.com> wrote in message
> news:d59e3dd6-8aa5-4bea-bc03-515257f5a3b3@.a35g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
Frank - you should check out the xSQL Software's RSS Reporter for SQL
Server (http://www.xsqlsoftware.com/Product/
Sql_Server_Rss_Reporter.aspx) - it is a great way to notify someone on
the status of SQL Server Jobs. It automatically generates RSS feeds
that aggregate job status information from multiple servers - it
avoids the hassles associated with the email notifications, it allows
you to drill down on a job to see what step of it actually failed
etc.
JC

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
.mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files '
Thanks in advance.
Barry
in Oregon"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.
> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files '
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files.
>> This was basically because we backed the files up to a UNIX system that
>> had
>> a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape
>> backup system and it would seem preferable to append all the 2G .ndf
>> files
>> into one 10G .mdf file on the new system.
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want
>> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf
>> files
>> on the new Sql Server 2005 server.
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
>> Is there some tool or method I can use that will allow me to create a
>> single
>> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
>> appended .ndf Sql Server 2000 data files '
> Once the files are empty you can use ALTER DATABASE to remove the file.
>> Thanks in advance.
>> Barry
>> in Oregon
> HTH
> John|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files. This was basically because we backed the files up to a UNIX system
>> that had a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape backup system and it would seem preferable to append all the 2G .ndf
>> files into one 10G .mdf file on the new system.
> There isn't any pressing need to do that.
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want to move and recreate the original 2G .mdf and the 6 additional 2G
>> .ndf files on the new Sql Server 2005 server.
>> Is there some tool or method I can use that will allow me to create a
>> single .mdf on the Sql Server 2005 side that consists of the original
>> .mdf and appended .ndf Sql Server 2000 data files '
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
..mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files ?
Thanks in advance.
Barry
in Oregon
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.

> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files ?
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David
|||Hi Barry
"frostbb" wrote:

> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that had
> a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
There may still be advantages to having two files especially if they are on
different sets of discs. If they are the same filegroup the allocation of
space to each file will be balanced. If the files are different filegroups
then you can partition your tables/indexes to separate I/O
> Unfortunately all of the Restore, Move, etc options that I've explored want
> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
> on the new Sql Server 2005 server.
It is probably easier to do this re-configuration on the new server. If they
are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
option. if they are different filegroups in each file, then changing the
clustered index so that it is on the primary filegroup will move it to the
..mdf file. If you have text columns defined to be on the second file group
then you might be able to get away with altering the column to varchar(MAX),
failing that you would need to create a new table (with text in the other
filegroup), move the data, remove the original table and then rename the new
table.

> Is there some tool or method I can use that will allow me to create a single
> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
> appended .ndf Sql Server 2000 data files ?
>
Once the files are empty you can use ALTER DATABASE to remove the file.

> Thanks in advance.
> Barry
> in Oregon
>
HTH
John
|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
> Once the files are empty you can use ALTER DATABASE to remove the file.
> HTH
> John
|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> There isn't any pressing need to do that.
>
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
.mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files '
Thanks in advance.
Barry
in Oregon"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.

> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files '
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> There isn't any pressing need to do that.
>
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create .dbf file from within asp.net pages?

Hi,I'd like to create a single .dbf file from within my asp.net application and populate it with some data in SQL Server database,How can I do?Any help will be greatly appreciated!Thanks in advance!You could try looking at the xBase Engine for C# and ASP.NET, written by Ricardo Federico Villafañes:http://www.c-sharpcorner.com/Code/2002/Feb/xBaseEngineRFV.asp

Terri

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***
"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>
|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***
|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.developersdex.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > We got a production database that gets
> >
> > tables added to it daily.
> > So, every single day we transfer those new
> >
> > tables via DTS to five (5) other servers!
> >
> > Can we accomplish the same thing using a
> >
> > 'Select' statement?
> > I found no way to use the 'select' stmt
> >
> > across servers.
> >
> > I'm not conversant with TSQL (yet!) and
> >
> > would very much appreciate any help you can
> >
> > give me.
> >
> > Thank you very much
> >
> > mike
> >
> >
> > Mike
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:
> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
> > "mike" <-nospam@.yahoo.com> wrote in message
> > news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > We got a production database that gets
> > >
> > > tables added to it daily.
> > > So, every single day we transfer those new
> > >
> > > tables via DTS to five (5) other servers!
> > >
> > > Can we accomplish the same thing using a
> > >
> > > 'Select' statement?
> > > I found no way to use the 'select' stmt
> > >
> > > across servers.
> > >
> > > I'm not conversant with TSQL (yet!) and
> > >
> > > would very much appreciate any help you can
> > >
> > > give me.
> > >
> > > Thank you very much
> > >
> > > mike
> > >
> > >
> > > Mike
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> >
> >
> > Did I understand you correctly? Your database schema changes DAILY? I know
> > nothing of your application obviously but new tables daily sounds like you
> > have some major issues that ought to be better fixed by a different
> > architecture or a better change control process.
> >
> > The best way to replicate schema changes is to script them and then apply
> > the scripts. Or invest in a tool like RedGate SQL Compare
> > (http://www.red-gate.com/). Don't you do that anyway in order to test the
> > changes?
> >
> > --
> > David Portas, SQL Server MVP
> >
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> >
> > SQL Server Books Online:
> > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > --
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
--
Adam J Warne, MCDBA
"mike" wrote:
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
>