how to Get the result of executed query in file

All ,

Is it possible that i can get the result of executed select statement in a .txt file.

some thing like this

select * from mytablw to <some file name.txt>

Regards,

Ashish

You need to use BCP.

You either invoke it from a cmd window or use xp_cmdshell in SS.

Code Snippet

From cmd:

bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd

From SQL Server:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd'

Look up BCP in BOL for more options and parameters.

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

How to get the log file with /logger option

Hi,

I want to get the log for the below command.

DTExec /FILE "C:\Test.dtsx" /CONNECTION DestinationConnectionFlatFile;"c:\test.csv" /CONNECTION SourceConnectionOLEDB;"Data Source=test;User ID=user;Initial Catalog=DATA_CONV;Provider=SQLNCLI;Auto Translate=false;PASSWORD=password" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /logger "DTS.LogProviderTextFile;log.txt" /Set "\package.Connections[log.txt].Properties[ConnectionString];c:\log.txt"

But I got the error as below. Please advise.

Error: 2006-10-10 09:57:21.07
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by C
onnections collection when the specific connection element is not found.
End Error
Error: 2006-10-10 09:57:21.10
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2006-10-10 09:57:21.14
Code: 0x8001F02F
Source: Test
Description: Cannot resolve a package path to an object in the package ".Connections[log.txt].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2006-10-10 09:57:21.17
Code: 0x80012017
Source: Test
Description: The package path referenced an object that cannot be found: "\package.Connections[log.txt].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set \package.Connections[log.txt].Properties[ConnectionString]
value to c:\log.txt.

Are you using dtexecui.exe to build the command line? If not, please do that, as it will build properly formed dtexec parameter set.

If you want to add a logger from the command line, it must reference a pre-existent connection manager in the package. So add a file connection manager to the package.

Now if you want a logger without a pre-existent connection manager, and you don't mind coding, create your own LogProvider by creating a subclass of LogProviderBase and overriding of meaning of the ConfigString, when generally points to a connection manager in stock loggers.

Two custom log providers with source code are available in the SQL 2005 samples available at http://www.microsoft.com/downloads/details.aspx?FamilyID=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

How to get the images files size?

Hi,
I want to retrive the images from SQL database and store into microsoft access database.
But only images file which is <than 500mb is allowed to retireve.How can i get the images files size if i just store the image file name into my sql database.(there is another folder to store images)

p@.ywen.You can use something like this:

// Create a reference to the image directory.
System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(strFileDirectory);
// Create an array representing the files in the current directory.
System.IO.FileInfo[] fi = di.GetFiles("*.jpg");
// then get file size
int fileSize = fi[0].Length;

|||Thanks for your reply, but one more question here.

After i create the array to store the images, then when i wan to get the files size
i need to loop until EOF right?|||No, you can just loop through the array using a standard for or a foreach.sql

how to get the duplicate file in ssis

is it possible that i can retrieve the list of all records that are duplicate and put them in a excel files

Hi There,

Use this query to get the duplicate records from a table, if you are using flat file as a source, load it to the database table and use this query:

select coulmn a from <tablename> where column a in (select column a from <tablename> group by column a having count(*) > 1). This query will give you the list of records which is duplicate. Later you can add an excel destination to load these duplicate records.

Regards,

Raju

|||

Hi

thanks for the reply, It work nice, somehow it disappoint me a bit (not on your answer), cause i thought i won't use the query anymore to disect the data and I thought ssis has tool for this. Anyway thanks!!!

Jagapathi Raju wrote:

Hi There,

Use this query to get the duplicate records from a table, if you are using flat file as a source, load it to the database table and use this query:

select coulmn a from <tablename> where column a in (select column a from <tablename> group by column a having count(*) > 1). This query will give you the list of records which is duplicate. Later you can add an excel destination to load these duplicate records.

Regards,

Raju

How to get the database file into .bak file format using C#

Hi,

I want to retrive the values from the database 'northwind' and then i want to store the backup files in"D:/Sample/north_database.bak" format(local machine).

I retrive the database values in .txt,XML format. Now i want to take in .bak format.

give the Suitable solution for this.

Subashini.G

subashi

Check out this article and then download the zip file, to see a sample of the code you want

http://www.codersource.net/csharp_sqldmo_sqlserver.aspx

HTH

|||

Hi,

They Above Article is useful for me. But this coding isstored is database Queries in text(txt) format but i need thedatabase table values stored in .bak format.

Give the solution please

|||

subashini

Take 2

Try

http://www.csharphelp.com/archives2/archive345.html

I think this gets you what you need.

HTH

|||

Thank you Very Much. This coding is very useful for me.....

Regards

Subashini

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
Ashish
You can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish
|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:

> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

how to get schema

Hi,

Could anyone tell me on how to get schema of a table(s) into excel or even simple well formated. text file. I tried scripts but seems complicated.

All i want is field name, datatype, size... That's it.

Thank you.

The following query will help you..

Select
T.Name TableName
,C.Name ColumnName
,Ty.name DataType
,C.Length
,C.Prec
,C.Scale
,Case When C.IsNullable =0 Then 'NULL' Else 'NOT NULL' End as Nullable
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType
Where
T.Type='U'
Order By
T.Name,
C.Colorder

|||I owuld prefer using the INFORMATION_SCHEMA.COLUMNS view.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to get list of backup log files from msdb of standby server?

How do we retrieve log file names of primary backup from msdb of
standby database?
In case of primary server unavailable, I need to get the logfile names
from standby server for disater recovery test.
Thanks in advance
Tram
Msdb..backupfile gives you a list of data/log files that are backed up.
For logshipping, take a look at msdb..log_shipping_plan_databases.
-oj
"tram" <tram_e@.hotmail.com> wrote in message
news:1120079449.879078.169120@.o13g2000cwo.googlegr oups.com...
> How do we retrieve log file names of primary backup from msdb of
> standby database?
> In case of primary server unavailable, I need to get the logfile names
> from standby server for disater recovery test.
> Thanks in advance
> Tram
>

How to get list of backup log files from msdb of standby server?

How do we retrieve log file names of primary backup from msdb of
standby database?
In case of primary server unavailable, I need to get the logfile names
from standby server for disater recovery test.
Thanks in advance
TramMsdb..backupfile gives you a list of data/log files that are backed up.
For logshipping, take a look at msdb..log_shipping_plan_databases.
-oj
"tram" <tram_e@.hotmail.com> wrote in message
news:1120079449.879078.169120@.o13g2000cwo.googlegroups.com...
> How do we retrieve log file names of primary backup from msdb of
> standby database?
> In case of primary server unavailable, I need to get the logfile names
> from standby server for disater recovery test.
> Thanks in advance
> Tram
>

How to get list of backup log files from msdb of standby server?

How do we retrieve log file names of primary backup from msdb of
standby database?
In case of primary server unavailable, I need to get the logfile names
from standby server for disater recovery test.
Thanks in advance
TramMsdb..backupfile gives you a list of data/log files that are backed up.
For logshipping, take a look at msdb..log_shipping_plan_databases.
--
-oj
"tram" <tram_e@.hotmail.com> wrote in message
news:1120079449.879078.169120@.o13g2000cwo.googlegroups.com...
> How do we retrieve log file names of primary backup from msdb of
> standby database?
> In case of primary server unavailable, I need to get the logfile names
> from standby server for disater recovery test.
> Thanks in advance
> Tram
>

how to get latest file

how to get the name of the latest file of a particular directory
thru query
i.e
i have files in a directory
1130am.txt
11.45am.txt
1200am.txt
my query should return 1200am.txtOriginally posted by vrsrinivas
how to get the name of the latest file of a particular directory
thru query

i.e
i have files in a directory

1130am.txt
11.45am.txt
1200am.txt

my query should return 1200am.txt

Assuming that all your file names are prefixed with a time stamp then you should be able to use the MAX() function to return the desired record.

create table #test (theFileName varchar(20))

insert into #test (theFileName) values ('1130am.txt')
insert into #test (theFileName) values ('11.45am.txt')
insert into #test (theFileName) values ('1200am.txt')

select max(theFileName) from #test|||I'm thinking the hard part is getting the info from the directory...

Delete From Ledger_Folder

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

Delete From Ledger_Folder_Parsed

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

How to get last lsn for my DB

I need to restore multiple set file from a transaction log backup.
To know from which position of this file starting my restore, i need to know
the last lsn point restored on my DB.
while @.id_start < @.id_stop
begin
set @.id_start = @.id_start + 1
RESTORE LOG [MYDB]
FROM DISK = N'Y:\MYDB_LOG'
WITH STANDBY = 'C:\Programmi\Microsoft SQL Server\MSSQL\UNDO_MYDB.ldf',
FILE = @.id_start
if @.@.error > 0
begin
set @.id_stop = 0
BREAK
end
end
I need to know the @.id_start to begin the restore for all backup set missing
.
ThanksHi
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 3, recovery
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:7BF739FD-E00E-4C13-ABDF-75CC564F31CD@.microsoft.com...
> I need to restore multiple set file from a transaction log backup.
> To know from which position of this file starting my restore, i need to
know
> the last lsn point restored on my DB.
>
> while @.id_start < @.id_stop
> begin
> set @.id_start = @.id_start + 1
> RESTORE LOG [MYDB]
> FROM DISK = N'Y:\MYDB_LOG'
> WITH STANDBY = 'C:\Programmi\Microsoft SQL
Server\MSSQL\UNDO_MYDB.ldf',
> FILE = @.id_start
> if @.@.error > 0
> begin
> set @.id_stop = 0
> BREAK
> end
> end
> I need to know the @.id_start to begin the restore for all backup set
missing.
> Thanks

How to get file path of datafiles etc

I know I can use sp_helpdb <db name> to get information about a database.
This SP gives me 2 result sets - one with name, owner, size etc and one with
a list of the files that make up the db.
What I want to do is get the filename column of the second result set into a
variable in my script. Is there a way I can do this ? Maybe use a cursor
in a way I don't know that allows me to specify that it should use the
second result set ?
TIA
Steve
See sp_helpfile in SQL Server Books Online. You could save the results of
this proc to a temporary table, using the INSERT...EXEC syntax.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Steve W" <lsl@.btconnect.com> wrote in message
news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I know I can use sp_helpdb <db name> to get information about a database.
> This SP gives me 2 result sets - one with name, owner, size etc and one
with
> a list of the files that make up the db.
> What I want to do is get the filename column of the second result set into
a
> variable in my script. Is there a way I can do this ? Maybe use a cursor
> in a way I don't know that allows me to specify that it should use the
> second result set ?
> TIA
> Steve
>
|||Thanks Vyas,
That's just what I needed !
Steve
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> See sp_helpfile in SQL Server Books Online. You could save the results of
> this proc to a temporary table, using the INSERT...EXEC syntax.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Steve W" <lsl@.btconnect.com> wrote in message
> news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
database.[vbcol=seagreen]
> with
into[vbcol=seagreen]
> a
cursor
>
|||Hi,
You can also use the below solution. This gives filenames for all the
databases directly.
select substring(db_name(dbid),1,30) as database_name,filename from
sysaltfiles
-- to put the result into a temp table
select substring(db_name(dbid),1,30) as database_name,filename into #temp
from sysaltfiles
select * from #temp
Thanks
Hari
MCDBA
"Steve W" <lsl@.btconnect.com> wrote in message
news:eRxuf9vaEHA.3352@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks Vyas,
> That's just what I needed !
> Steve
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
> database.
one
> into
> cursor
>

How to get file path of datafiles etc

I know I can use sp_helpdb <db name> to get information about a database.
This SP gives me 2 result sets - one with name, owner, size etc and one with
a list of the files that make up the db.
What I want to do is get the filename column of the second result set into a
variable in my script. Is there a way I can do this ? Maybe use a cursor
in a way I don't know that allows me to specify that it should use the
second result set ?
TIA
SteveSee sp_helpfile in SQL Server Books Online. You could save the results of
this proc to a temporary table, using the INSERT...EXEC syntax.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Steve W" <lsl@.btconnect.com> wrote in message
news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I know I can use sp_helpdb <db name> to get information about a database.
> This SP gives me 2 result sets - one with name, owner, size etc and one
with
> a list of the files that make up the db.
> What I want to do is get the filename column of the second result set into
a
> variable in my script. Is there a way I can do this ? Maybe use a cursor
> in a way I don't know that allows me to specify that it should use the
> second result set ?
> TIA
> Steve
>|||Thanks Vyas,
That's just what I needed !
Steve
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> See sp_helpfile in SQL Server Books Online. You could save the results of
> this proc to a temporary table, using the INSERT...EXEC syntax.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Steve W" <lsl@.btconnect.com> wrote in message
> news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > I know I can use sp_helpdb <db name> to get information about a
database.
> > This SP gives me 2 result sets - one with name, owner, size etc and one
> with
> > a list of the files that make up the db.
> >
> > What I want to do is get the filename column of the second result set
into
> a
> > variable in my script. Is there a way I can do this ? Maybe use a
cursor
> > in a way I don't know that allows me to specify that it should use the
> > second result set ?
> >
> > TIA
> >
> > Steve
> >
> >
>|||Hi,
You can also use the below solution. This gives filenames for all the
databases directly.
select substring(db_name(dbid),1,30) as database_name,filename from
sysaltfiles
-- to put the result into a temp table
select substring(db_name(dbid),1,30) as database_name,filename into #temp
from sysaltfiles
select * from #temp
Thanks
Hari
MCDBA
"Steve W" <lsl@.btconnect.com> wrote in message
news:eRxuf9vaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Thanks Vyas,
> That's just what I needed !
> Steve
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > See sp_helpfile in SQL Server Books Online. You could save the results
of
> > this proc to a temporary table, using the INSERT...EXEC syntax.
> > --
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> >
> >
> > "Steve W" <lsl@.btconnect.com> wrote in message
> > news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > I know I can use sp_helpdb <db name> to get information about a
> database.
> > > This SP gives me 2 result sets - one with name, owner, size etc and
one
> > with
> > > a list of the files that make up the db.
> > >
> > > What I want to do is get the filename column of the second result set
> into
> > a
> > > variable in my script. Is there a way I can do this ? Maybe use a
> cursor
> > > in a way I don't know that allows me to specify that it should use the
> > > second result set ?
> > >
> > > TIA
> > >
> > > Steve
> > >
> > >
> >
> >
>

How to get file path of datafiles etc

I know I can use sp_helpdb <db name> to get information about a database.
This SP gives me 2 result sets - one with name, owner, size etc and one with
a list of the files that make up the db.
What I want to do is get the filename column of the second result set into a
variable in my script. Is there a way I can do this ? Maybe use a cursor
in a way I don't know that allows me to specify that it should use the
second result set ?
TIA
SteveSee sp_helpfile in SQL Server Books Online. You could save the results of
this proc to a temporary table, using the INSERT...EXEC syntax.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Steve W" <lsl@.btconnect.com> wrote in message
news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I know I can use sp_helpdb <db name> to get information about a database.
> This SP gives me 2 result sets - one with name, owner, size etc and one
with
> a list of the files that make up the db.
> What I want to do is get the filename column of the second result set into
a
> variable in my script. Is there a way I can do this ? Maybe use a cursor
> in a way I don't know that allows me to specify that it should use the
> second result set ?
> TIA
> Steve
>|||Thanks Vyas,
That's just what I needed !
Steve
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> See sp_helpfile in SQL Server Books Online. You could save the results of
> this proc to a temporary table, using the INSERT...EXEC syntax.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Steve W" <lsl@.btconnect.com> wrote in message
> news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
database.[vbcol=seagreen]
> with
into[vbcol=seagreen]
> a
cursor[vbcol=seagreen]
>|||Hi,
You can also use the below solution. This gives filenames for all the
databases directly.
select substring(db_name(dbid),1,30) as database_name,filename from
sysaltfiles
-- to put the result into a temp table
select substring(db_name(dbid),1,30) as database_name,filename into #temp
from sysaltfiles
select * from #temp
Thanks
Hari
MCDBA
"Steve W" <lsl@.btconnect.com> wrote in message
news:eRxuf9vaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Thanks Vyas,
> That's just what I needed !
> Steve
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
> database.
one[vbcol=seagreen]
> into
> cursor
>

how to get file list from sysfiles table of .mdf

hi,
i have a strange requirement.
I have all the .mdf/.ndf/.ldf files for a given
database on different drives of same server.
I do not know which files put together make up the
database.
so in order to exec sp_attachdb or "create database
wit attach" syntax (files >16)
from where do i get the list of files pertaining to 1
db.
Can i access/read sysfiles system table in the .mdf
before attaching it.
any suggestion wud be welcome
Thanks
RahulIt's a reasonable requirement, but...
You'll have to keep track of the list of db files yourself.
You can't query a database file before it's attached.
However, if you haven't moved the files since they were detached, all you
need do is attach the primary datafile and it will find the other files (it
internally scans "sysfiles" during the attach to perform this).
"rahulnagarmath" <rahulnagarmath@.hotmail.com> wrote in message
news:2891001c39148$292d18c0$a601280a@.phx.gbl...
> hi,
> i have a strange requirement.
> I have all the .mdf/.ndf/.ldf files for a given
> database on different drives of same server.
> I do not know which files put together make up the
> database.
> so in order to exec sp_attachdb or "create database
> wit attach" syntax (files >16)
> from where do i get the list of files pertaining to 1
> db.
> Can i access/read sysfiles system table in the .mdf
> before attaching it.
> any suggestion wud be welcome
> Thanks
> Rahul
>