Load from several CSV files

I have to load around 68 CSV files into one table. I have named the files
1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
load these. I am not very proficient with VBScriptTry using a global variable for the file name, a Dynamic Properties Task and
an ActiveX Script Task to programmatically loop through and change the name
of the input file for each import.
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:uzU8pR7vFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I have to load around 68 CSV files into one table. I have named the files
>1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
>load these. I am not very proficient with VBScript
>|||You can concatenate the files and create a big one to be imported.
This is the help for dos command "copy".
*****
C:\>copy /?
Copies one or more files to another location.
COPY [/D] [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
[+ source [/A | /B] [+ ...]] [destination [/A | /B]]
source Specifies the file or files to be copied.
/A Indicates an ASCII text file.
/B Indicates a binary file.
/D Allow the destination file to be created decrypted
destination Specifies the directory and/or filename for the new file(s).
/V Verifies that new files are written correctly.
/N Uses short filename, if available, when copying a file with a
non-8dot3 name.
/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.
/-Y Causes prompting to confirm you want to overwrite an
existing destination file.
/Z Copies networked files in restartable mode.
The switch /Y may be preset in the COPYCMD environment variable.
This may be overridden with /-Y on the command line. Default is
to prompt on overwrites unless COPY command is being executed from
within a batch script.
To append files, specify a single file for destination, but multiple files
for source (using wildcards or file1+file2+file3 format).
*****
AMB
"XXX" wrote:

> I have to load around 68 CSV files into one table. I have named the files
> 1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
> load these. I am not very proficient with VBScript
>
>

load files

the connection string in my application daynamic ..changed by changing the development environmet ..how can i load a data from file to sql server destination without hard coded the connection

thx

hi,

You could use xml files, registry entries or sql tables in order to save these parameters. (Package configurations when you click on the right mouse button) and then load them on demand.

|||Many links regarding package configurations can be found here: http://www.google.com/search?hl=en&q=ssis+package+configurations|||

Begin here: http://msdn2.microsoft.com/en-us/library/ms137592.aspx

load files

the connection string in my application daynamic ..changed by changing the development environmet ..how can i load a data from file to sql server destination without hard coded the connection

thx

hi,

You could use xml files, registry entries or sql tables in order to save these parameters. (Package configurations when you click on the right mouse button) and then load them on demand.

|||Many links regarding package configurations can be found here: http://www.google.com/search?hl=en&q=ssis+package+configurations|||

Begin here: http://msdn2.microsoft.com/en-us/library/ms137592.aspx

load csv into SQL Server 2005

I have been pulling my hair out all afternoon trying to figure out an easy
way to import csv files into SQL Server 2005. They are perfmon logs in csv
format with TONS of columns, so creating the table and columns beforehand is
not practical. Isn't there some very easy, straightfoward way to simply
create a table and its columns based up on a csv file import?
thanks in advance,
BillIn SQL Server Managemetn Studio, right click on the database name and
choose Tasks... Import Data. This opens the Import Export Wizard.
Specify a Flat File data source. Give it your .csv file name. If
your csv file has column names in the first line, check that box. Use
Preview to see how it looks, and make whatever adjustments it takes to
make it look right. Keep following the steps, and you should end up
with a new table in your database. It will probably not be exactly
what you want!!! Data types will be a bit screwy, names will probably
not be formatted correctly.
Now script that table so you have a CREATE TABLE command. Edit it
until the names and types are right and make the new table. Then
repeat the process above, but this time in the panel labelled Select
Source and Table Views change the Desitnation to the new table you
created. Take through the rest of the steps and the data should load.
I have simplified things a bit - there are far too many details to go
into here - but the approach should get you where you want to go.
Good luck!
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:

>I have been pulling my hair out all afternoon trying to figure out an easy
>way to import csv files into SQL Server 2005. They are perfmon logs in csv
>format with TONS of columns, so creating the table and columns beforehand i
s
>not practical. Isn't there some very easy, straightfoward way to simply
>create a table and its columns based up on a csv file import?
>thanks in advance,
>Bill
>|||Take a look at the Windows utility relog.exe
Linchi
"bu" wrote:

> I have been pulling my hair out all afternoon trying to figure out an easy
> way to import csv files into SQL Server 2005. They are perfmon logs in cs
v
> format with TONS of columns, so creating the table and columns beforehand
is
> not practical. Isn't there some very easy, straightfoward way to simply
> create a table and its columns based up on a csv file import?
> thanks in advance,
> Bill
>
>|||Do you know if that same procedure applies in SQL Server 2005 Express as
well as Standard and the others?
thanks again!
Bill
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:06jf529da1ocliljc48nhkihkli7mm5j99@.
4ax.com...[vbcol=seagreen]
> In SQL Server Managemetn Studio, right click on the database name and
> choose Tasks... Import Data. This opens the Import Export Wizard.
> Specify a Flat File data source. Give it your .csv file name. If
> your csv file has column names in the first line, check that box. Use
> Preview to see how it looks, and make whatever adjustments it takes to
> make it look right. Keep following the steps, and you should end up
> with a new table in your database. It will probably not be exactly
> what you want!!! Data types will be a bit screwy, names will probably
> not be formatted correctly.
> Now script that table so you have a CREATE TABLE command. Edit it
> until the names and types are right and make the new table. Then
> repeat the process above, but this time in the panel labelled Select
> Source and Table Views change the Desitnation to the new table you
> created. Take through the rest of the steps and the data should load.
> I have simplified things a bit - there are far too many details to go
> into here - but the approach should get you where you want to go.
> Good luck!
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
>
easy[vbcol=seagreen]
csv[vbcol=seagreen]
is[vbcol=seagreen]|||I do not believe it applies to Express, as I do not think Express
comes with SSIS. SSIS is what the Wizard builds.
Roy
On Tue, 2 May 2006 21:26:51 -0400, "me" <me@.nospam.com> wrote:

>Do you know if that same procedure applies in SQL Server 2005 Express as
>well as Standard and the others?
>thanks again!
>Bill
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:06jf529da1ocliljc48nhkihkli7mm5j99@.
4ax.com...
>easy
>csv
>is
>

load csv into SQL Server 2005

I have been pulling my hair out all afternoon trying to figure out an easy
way to import csv files into SQL Server 2005. They are perfmon logs in csv
format with TONS of columns, so creating the table and columns beforehand is
not practical. Isn't there some very easy, straightfoward way to simply
create a table and its columns based up on a csv file import?
thanks in advance,
BillIn SQL Server Managemetn Studio, right click on the database name and
choose Tasks... Import Data. This opens the Import Export Wizard.
Specify a Flat File data source. Give it your .csv file name. If
your csv file has column names in the first line, check that box. Use
Preview to see how it looks, and make whatever adjustments it takes to
make it look right. Keep following the steps, and you should end up
with a new table in your database. It will probably not be exactly
what you want!!! Data types will be a bit screwy, names will probably
not be formatted correctly.
Now script that table so you have a CREATE TABLE command. Edit it
until the names and types are right and make the new table. Then
repeat the process above, but this time in the panel labelled Select
Source and Table Views change the Desitnation to the new table you
created. Take through the rest of the steps and the data should load.
I have simplified things a bit - there are far too many details to go
into here - but the approach should get you where you want to go.
Good luck!
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
>I have been pulling my hair out all afternoon trying to figure out an easy
>way to import csv files into SQL Server 2005. They are perfmon logs in csv
>format with TONS of columns, so creating the table and columns beforehand is
>not practical. Isn't there some very easy, straightfoward way to simply
>create a table and its columns based up on a csv file import?
>thanks in advance,
>Bill
>|||Take a look at the Windows utility relog.exe
Linchi
"bu" wrote:
> I have been pulling my hair out all afternoon trying to figure out an easy
> way to import csv files into SQL Server 2005. They are perfmon logs in csv
> format with TONS of columns, so creating the table and columns beforehand is
> not practical. Isn't there some very easy, straightfoward way to simply
> create a table and its columns based up on a csv file import?
> thanks in advance,
> Bill
>
>|||Do you know if that same procedure applies in SQL Server 2005 Express as
well as Standard and the others?
thanks again!
Bill
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:06jf529da1ocliljc48nhkihkli7mm5j99@.4ax.com...
> In SQL Server Managemetn Studio, right click on the database name and
> choose Tasks... Import Data. This opens the Import Export Wizard.
> Specify a Flat File data source. Give it your .csv file name. If
> your csv file has column names in the first line, check that box. Use
> Preview to see how it looks, and make whatever adjustments it takes to
> make it look right. Keep following the steps, and you should end up
> with a new table in your database. It will probably not be exactly
> what you want!!! Data types will be a bit screwy, names will probably
> not be formatted correctly.
> Now script that table so you have a CREATE TABLE command. Edit it
> until the names and types are right and make the new table. Then
> repeat the process above, but this time in the panel labelled Select
> Source and Table Views change the Desitnation to the new table you
> created. Take through the rest of the steps and the data should load.
> I have simplified things a bit - there are far too many details to go
> into here - but the approach should get you where you want to go.
> Good luck!
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
> >I have been pulling my hair out all afternoon trying to figure out an
easy
> >way to import csv files into SQL Server 2005. They are perfmon logs in
csv
> >format with TONS of columns, so creating the table and columns beforehand
is
> >not practical. Isn't there some very easy, straightfoward way to simply
> >create a table and its columns based up on a csv file import?
> >
> >thanks in advance,
> >Bill
> >|||I do not believe it applies to Express, as I do not think Express
comes with SSIS. SSIS is what the Wizard builds.
Roy
On Tue, 2 May 2006 21:26:51 -0400, "me" <me@.nospam.com> wrote:
>Do you know if that same procedure applies in SQL Server 2005 Express as
>well as Standard and the others?
>thanks again!
>Bill
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:06jf529da1ocliljc48nhkihkli7mm5j99@.4ax.com...
>> In SQL Server Managemetn Studio, right click on the database name and
>> choose Tasks... Import Data. This opens the Import Export Wizard.
>> Specify a Flat File data source. Give it your .csv file name. If
>> your csv file has column names in the first line, check that box. Use
>> Preview to see how it looks, and make whatever adjustments it takes to
>> make it look right. Keep following the steps, and you should end up
>> with a new table in your database. It will probably not be exactly
>> what you want!!! Data types will be a bit screwy, names will probably
>> not be formatted correctly.
>> Now script that table so you have a CREATE TABLE command. Edit it
>> until the names and types are right and make the new table. Then
>> repeat the process above, but this time in the panel labelled Select
>> Source and Table Views change the Desitnation to the new table you
>> created. Take through the rest of the steps and the data should load.
>> I have simplified things a bit - there are far too many details to go
>> into here - but the approach should get you where you want to go.
>> Good luck!
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
>> >I have been pulling my hair out all afternoon trying to figure out an
>easy
>> >way to import csv files into SQL Server 2005. They are perfmon logs in
>csv
>> >format with TONS of columns, so creating the table and columns beforehand
>is
>> >not practical. Isn't there some very easy, straightfoward way to simply
>> >create a table and its columns based up on a csv file import?
>> >
>> >thanks in advance,
>> >Bill
>> >
>

Livestats.XSP having trouble importing log files.

My company is running Livestats.XSP v8. We have been trying to import log files for a website for a few days now but Livestats keeps getting stuck randomly throughout the import process. What i mean by this is that i will import all of the log files by date which have been imported, then its stop and displays "Not Imported....."

We really need to get this remedied as soon as possible and it seems that support for Livestats is slim. Does anyone any idea that could help me? Thanks in advance.

Guess this is a application related problem which either should be answered through the support of the vendor or the support forums for the application.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

List the folder contents using SQL

Hi all,
I have built a Disaster Recovery Site for my DB.on periodic basis, trn files from production server reach DR server and DR setup will apply them locally.

If the flow is smooth then no issues, if one of the file does not reach DR
the entire setup will halt for the want of the file and I dn't have any means to
know which file is missing.

Both my production and DR site located remotely behind firewalls, i.e i can't
physically access the servers or remotely login to the server.

Can anyone tell me how to see the contents in a folder in the server using
SQL query .

Any help is highly appreciated

Thanks and Regards
Srinivas VaranasiI'd use xp_cmdshell (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp).

-PatP|||use

master..xp_cmdshell 'dir c:\'|||Thanks Pat.|||Harshal, sorry to find "STUPID" as Ur Signature

List remote server and directory

Hi
Trying to work on a script which is able to list files on a remote server
and its drive but unsure of the syntax. Can anyone advise as below doesnt
work.
xp_cmdshell 'dir "\\server::d:\"
Thanks
Hi,
The remote server needs to have a share exposed that you can use to get a
directory listing. The syntax would be:
exec master.dbo.xp_cmdshell 'dir \\myserver\d$'
If the path contains blanks then you need to quote the parameter with
double-quotes:
exec master.dbo.xp_cmdshell 'dir "\\myserver\c$\Program Files"'
RLF
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:C2CC72B2-F903-423A-A499-36E639532789@.microsoft.com...
> Hi
> Trying to work on a script which is able to list files on a remote server
> and its drive but unsure of the syntax. Can anyone advise as below doesnt
> work.
> xp_cmdshell 'dir "\\server::d:\"
>
> Thanks

List remote server and directory

Hi
Trying to work on a script which is able to list files on a remote server
and its drive but unsure of the syntax. Can anyone advise as below doesnt
work.
xp_cmdshell 'dir "\\server::d:\"
ThanksHi,
The remote server needs to have a share exposed that you can use to get a
directory listing. The syntax would be:
exec master.dbo.xp_cmdshell 'dir \\myserver\d$'
If the path contains blanks then you need to quote the parameter with
double-quotes:
exec master.dbo.xp_cmdshell 'dir "\\myserver\c$\Program Files"'
RLF
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:C2CC72B2-F903-423A-A499-36E639532789@.microsoft.com...
> Hi
> Trying to work on a script which is able to list files on a remote server
> and its drive but unsure of the syntax. Can anyone advise as below doesnt
> work.
> xp_cmdshell 'dir "\\server::d:\"
>
> Thanks

List remote server and directory

Hi
Trying to work on a script which is able to list files on a remote server
and its drive but unsure of the syntax. Can anyone advise as below doesnt
work.
xp_cmdshell 'dir "\\server::d:\"
ThanksHi,
The remote server needs to have a share exposed that you can use to get a
directory listing. The syntax would be:
exec master.dbo.xp_cmdshell 'dir \\myserver\d$'
If the path contains blanks then you need to quote the parameter with
double-quotes:
exec master.dbo.xp_cmdshell 'dir "\\myserver\c$\Program Files"'
RLF
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:C2CC72B2-F903-423A-A499-36E639532789@.microsoft.com...
> Hi
> Trying to work on a script which is able to list files on a remote server
> and its drive but unsure of the syntax. Can anyone advise as below doesnt
> work.
> xp_cmdshell 'dir "\\server::d:\"
>
> Thanks

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.|||Before you start getting bent out of shape over SQL Server, there an easy way
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...
> Before you start getting bent out of shape over SQL Server, there an easy way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the following
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know and
> we can go from there.
> Scott
> "New to MS SQL" wrote:
> > Is there an easy way to print out a simple text report of
> > all database names plus their internal database filenames
> > & logfile names plus the actual physical locations of each
> > file in SQL Server 2000?
> >
> > I'm an Oracle admin who's just inherited an SQL Server
> > full of multiple databases and I need to make a structural
> > diagram of what all lives where inside this server. In
> > Oracle, a simple sql script dumps out a text list of all
> > these kinds of things, but all I've been able to discover
> > thus far in MS SQL Enterprise Manager is an unfriendly GUI
> > interface that makes you have to repeatedly point, click,
> > and browse many times over and over again and again to get
> > this info one tiny piece at a time, which isn't very
> > efficient.
> >

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.
New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.
|||Before you start getting bent out of shape over SQL Server, there an easy way
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:

> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>
|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...[vbcol=seagreen]
> Before you start getting bent out of shape over SQL Server, there an easy way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the following
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know and
> we can go from there.
> Scott
> "New to MS SQL" wrote:

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.|||Before you start getting bent out of shape over SQL Server, there an easy wa
y
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:

> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...[vbcol=seagreen]
> Before you start getting bent out of shape over SQL Server, there an easy
way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the followi
ng
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know a
nd
> we can go from there.
> Scott
> "New to MS SQL" wrote:
>

List in order

Say I have a table with the following files in them but not sorted and I
wanted to sort them in order like these based on the timestamp part of it.
How can I do so ?
DB1_tlog_200503122235.TRN
DB1_tlog_200503122240.TRN
DB1_tlog_200503122245.TRN
DB1_tlog_200503122250.TRN
DB1_tlog_200503122255.TRN
DB1_tlog_200503122300.TRN
DB1_tlog_200503122305.TRN
DB1_tlog_200503122310.TRN
DB1_tlog_200503122315.TRN
I think we need to find the datetime portion and that would be before the
".trn" and after the "_tlog_"
And then be able to sort the string "200503122235" which represents
2005-03-12 22:35 .. How can I do this ?
ThanksHassan
drop table #tEST
CREATE TABLE #Test
(
col VARCHAR(50) NOT NULL
)
INSERT INTO #Test VALUES ('200503122235')
INSERT INTO #Test VALUES ('200503122138')
INSERT INTO #Test VALUES ('200503121845')
INSERT INTO #Test VALUES ('200503122125')
INSERT INTO #Test VALUES ('200503122030')
INSERT INTO #Test VALUES ('200503122430')
SELECT *
FROM #Test
ORDER BY CONVERT(DATETIME,LEFT(col,4)+SUBSTRING(c
ol,5,2)+
SUBSTRING(col,8,2)+'
'+REPLACE(SUBSTRING(col,9,2),'24','00')+
':'+SUBSTRING(col,11,2) ,112)
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u1$UWDGKFHA.2796@.tk2msftngp13.phx.gbl...
> Say I have a table with the following files in them but not sorted and I
> wanted to sort them in order like these based on the timestamp part of it.
> How can I do so ?
> DB1_tlog_200503122235.TRN
> DB1_tlog_200503122240.TRN
> DB1_tlog_200503122245.TRN
> DB1_tlog_200503122250.TRN
> DB1_tlog_200503122255.TRN
> DB1_tlog_200503122300.TRN
> DB1_tlog_200503122305.TRN
> DB1_tlog_200503122310.TRN
> DB1_tlog_200503122315.TRN
> I think we need to find the datetime portion and that would be before the
> ".trn" and after the "_tlog_"
> And then be able to sort the string "200503122235" which represents
> 2005-03-12 22:35 .. How can I do this ?
> Thanks
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u1$UWDGKFHA.2796@.tk2msftngp13.phx.gbl...
> Say I have a table with the following files in them but not sorted and I
> wanted to sort them in order like these based on the timestamp part of it.
> How can I do so ?
> DB1_tlog_200503122235.TRN
> DB1_tlog_200503122240.TRN
> DB1_tlog_200503122245.TRN
> DB1_tlog_200503122250.TRN
> DB1_tlog_200503122255.TRN
> DB1_tlog_200503122300.TRN
> DB1_tlog_200503122305.TRN
> DB1_tlog_200503122310.TRN
> DB1_tlog_200503122315.TRN
> I think we need to find the datetime portion and that would be before the
> ".trn" and after the "_tlog_"
> And then be able to sort the string "200503122235" which represents
> 2005-03-12 22:35 .. How can I do this ?
If all of the filenames have the same prefix and extension, and all use the
above format for the date/time part, then it's simply a case of ordering the
results by that column. The date/time formatting is already fine for
sorting, so there's no need to convert it to a real date/time.
Dan

List all files and autogrowth option

Can I get a list of all files a database comprise of and whether the
autogrowth option for the file is turned on or off
Output should be
DbName FileName Autogrowth_on_off
ABC c:\abc.mdf on
ABC d:\abc1.ndf off
ABC d:\abc2.ndf on
ABC e:\abc_log.ldf onEXEC sp_helpfile
or
SELECT *
FROM master.dbo.sysaltfiles
David Portas
SQL Server MVP
--|||Hi,
Execute the below stored procedure.
sp_helpdb <DBNAME>
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Can I get a list of all files a database comprise of and whether the
> autogrowth option for the file is turned on or off
> Output should be
> DbName FileName Autogrowth_on_off
> ABC c:\abc.mdf on
> ABC d:\abc1.ndf off
> ABC d:\abc2.ndf on
> ABC e:\abc_log.ldf on
>
>|||Guys, none of those options tell me if autogrowth is on or off
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
> Hi,
> Execute the below stored procedure.
> sp_helpdb <DBNAME>
>
> Thanks
> Hari
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>|||I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
determines if Autogrow is on or not. Another function you might be
interested in for various pieces of information related to the db is
DATABASEPROPERTYEX.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Guys, none of those options tell me if autogrowth is on or off
>
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
>|||its the growth column in sysaltfiles. A zero indicates no autogrowth. I
could not find any databasepropertyex property to find this out for me
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
> I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
> determines if Autogrow is on or not. Another function you might be
> interested in for various pieces of information related to the db is
> DATABASEPROPERTYEX.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
>|||The DatabaseEx properties was for future references.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eCkMZglaFHA.3132@.TK2MSFTNGP09.phx.gbl...
> its the growth column in sysaltfiles. A zero indicates no autogrowth. I
> could not find any databasepropertyex property to find this out for me
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
>

List all files and autogrowth option

Can I get a list of all files a database comprise of and whether the
autogrowth option for the file is turned on or off
Output should be
DbName FileName Autogrowth_on_off
ABC c:\abc.mdf on
ABC d:\abc1.ndf off
ABC d:\abc2.ndf on
ABC e:\abc_log.ldf on
EXEC sp_helpfile
or
SELECT *
FROM master.dbo.sysaltfiles
David Portas
SQL Server MVP
|||Hi,
Execute the below stored procedure.
sp_helpdb <DBNAME>
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Can I get a list of all files a database comprise of and whether the
> autogrowth option for the file is turned on or off
> Output should be
> DbName FileName Autogrowth_on_off
> ABC c:\abc.mdf on
> ABC d:\abc1.ndf off
> ABC d:\abc2.ndf on
> ABC e:\abc_log.ldf on
>
>
|||Guys, none of those options tell me if autogrowth is on or off
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
> Hi,
> Execute the below stored procedure.
> sp_helpdb <DBNAME>
>
> Thanks
> Hari
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>
|||I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
determines if Autogrow is on or not. Another function you might be
interested in for various pieces of information related to the db is
DATABASEPROPERTYEX.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Guys, none of those options tell me if autogrowth is on or off
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
>
|||its the growth column in sysaltfiles. A zero indicates no autogrowth. I
could not find any databasepropertyex property to find this out for me
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
> I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
> determines if Autogrow is on or not. Another function you might be
> interested in for various pieces of information related to the db is
> DATABASEPROPERTYEX.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
>
|||The DatabaseEx properties was for future references.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eCkMZglaFHA.3132@.TK2MSFTNGP09.phx.gbl...
> its the growth column in sysaltfiles. A zero indicates no autogrowth. I
> could not find any databasepropertyex property to find this out for me
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
>

List all files and autogrowth option

Can I get a list of all files a database comprise of and whether the
autogrowth option for the file is turned on or off
Output should be
DbName FileName Autogrowth_on_off
ABC c:\abc.mdf on
ABC d:\abc1.ndf off
ABC d:\abc2.ndf on
ABC e:\abc_log.ldf onEXEC sp_helpfile
or
SELECT *
FROM master.dbo.sysaltfiles
David Portas
SQL Server MVP
--|||Hi,
Execute the below stored procedure.
sp_helpdb <DBNAME>
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Can I get a list of all files a database comprise of and whether the
> autogrowth option for the file is turned on or off
> Output should be
> DbName FileName Autogrowth_on_off
> ABC c:\abc.mdf on
> ABC d:\abc1.ndf off
> ABC d:\abc2.ndf on
> ABC e:\abc_log.ldf on
>
>|||Guys, none of those options tell me if autogrowth is on or off
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
> Hi,
> Execute the below stored procedure.
> sp_helpdb <DBNAME>
>
> Thanks
> Hari
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>|||I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
determines if Autogrow is on or not. Another function you might be
interested in for various pieces of information related to the db is
DATABASEPROPERTYEX.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Guys, none of those options tell me if autogrowth is on or off
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
>|||its the growth column in sysaltfiles. A zero indicates no autogrowth. I
could not find any databasepropertyex property to find this out for me
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
> I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
> determines if Autogrow is on or not. Another function you might be
> interested in for various pieces of information related to the db is
> DATABASEPROPERTYEX.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
>|||The DatabaseEx properties was for future references.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eCkMZglaFHA.3132@.TK2MSFTNGP09.phx.gbl...
> its the growth column in sysaltfiles. A zero indicates no autogrowth. I
> could not find any databasepropertyex property to find this out for me
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
>

List all files and autogrowth option

Can I get a list of all files a database comprise of and whether the
autogrowth option for the file is turned on or off
Output should be
DbName FileName Autogrowth_on_off
ABC c:\abc.mdf on
ABC d:\abc1.ndf off
ABC d:\abc2.ndf on
ABC e:\abc_log.ldf onEXEC sp_helpfile
or
SELECT *
FROM master.dbo.sysaltfiles
--
David Portas
SQL Server MVP
--|||Hi,
Execute the below stored procedure.
sp_helpdb <DBNAME>
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Can I get a list of all files a database comprise of and whether the
> autogrowth option for the file is turned on or off
> Output should be
> DbName FileName Autogrowth_on_off
> ABC c:\abc.mdf on
> ABC d:\abc1.ndf off
> ABC d:\abc2.ndf on
> ABC e:\abc_log.ldf on
>
>|||Guys, none of those options tell me if autogrowth is on or off
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
> Hi,
> Execute the below stored procedure.
> sp_helpdb <DBNAME>
>
> Thanks
> Hari
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
> > Can I get a list of all files a database comprise of and whether the
> > autogrowth option for the file is turned on or off
> >
> > Output should be
> >
> > DbName FileName Autogrowth_on_off
> > ABC c:\abc.mdf on
> > ABC d:\abc1.ndf off
> > ABC d:\abc2.ndf on
> > ABC e:\abc_log.ldf on
> >
> >
> >
> >
>|||I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
determines if Autogrow is on or not. Another function you might be
interested in for various pieces of information related to the db is
DATABASEPROPERTYEX.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Guys, none of those options tell me if autogrowth is on or off
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
>> Hi,
>> Execute the below stored procedure.
>> sp_helpdb <DBNAME>
>>
>> Thanks
>> Hari
>> SQL Server MVP
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>> > Can I get a list of all files a database comprise of and whether the
>> > autogrowth option for the file is turned on or off
>> >
>> > Output should be
>> >
>> > DbName FileName Autogrowth_on_off
>> > ABC c:\abc.mdf on
>> > ABC d:\abc1.ndf off
>> > ABC d:\abc2.ndf on
>> > ABC e:\abc_log.ldf on
>> >
>> >
>> >
>> >
>>
>|||its the growth column in sysaltfiles. A zero indicates no autogrowth. I
could not find any databasepropertyex property to find this out for me
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
> I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
> determines if Autogrow is on or not. Another function you might be
> interested in for various pieces of information related to the db is
> DATABASEPROPERTYEX.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
> > Guys, none of those options tell me if autogrowth is on or off
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
> >> Hi,
> >>
> >> Execute the below stored procedure.
> >>
> >> sp_helpdb <DBNAME>
> >>
> >>
> >> Thanks
> >> Hari
> >> SQL Server MVP
> >>
> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
> >> > Can I get a list of all files a database comprise of and whether the
> >> > autogrowth option for the file is turned on or off
> >> >
> >> > Output should be
> >> >
> >> > DbName FileName Autogrowth_on_off
> >> > ABC c:\abc.mdf on
> >> > ABC d:\abc1.ndf off
> >> > ABC d:\abc2.ndf on
> >> > ABC e:\abc_log.ldf on
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||The DatabaseEx properties was for future references.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eCkMZglaFHA.3132@.TK2MSFTNGP09.phx.gbl...
> its the growth column in sysaltfiles. A zero indicates no autogrowth. I
> could not find any databasepropertyex property to find this out for me
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OcOOJMdaFHA.2884@.tk2msftngp13.phx.gbl...
>> I believe the MAXSIZE columnin the sysaltfiles that David pointed you to
>> determines if Autogrow is on or not. Another function you might be
>> interested in for various pieces of information related to the db is
>> DATABASEPROPERTYEX.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:ehW9vlYaFHA.3144@.TK2MSFTNGP14.phx.gbl...
>> > Guys, none of those options tell me if autogrowth is on or off
>> >
>> >
>> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> > news:%23MXBnFUZFHA.2884@.tk2msftngp13.phx.gbl...
>> >> Hi,
>> >>
>> >> Execute the below stored procedure.
>> >>
>> >> sp_helpdb <DBNAME>
>> >>
>> >>
>> >> Thanks
>> >> Hari
>> >> SQL Server MVP
>> >>
>> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> >> news:OUO3JoTZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>> >> > Can I get a list of all files a database comprise of and whether the
>> >> > autogrowth option for the file is turned on or off
>> >> >
>> >> > Output should be
>> >> >
>> >> > DbName FileName Autogrowth_on_off
>> >> > ABC c:\abc.mdf on
>> >> > ABC d:\abc1.ndf off
>> >> > ABC d:\abc2.ndf on
>> >> > ABC e:\abc_log.ldf on
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>