I am looking for a way to read a seperate .txt file and extract the
paramaters for a query. I am given a .txt file with several thousand numbers
a day, to query against the database. I would very much like to call the
file and read it's contents in as the paramater. Is this possible through
using just sql?
for example,
current method (paraphrased extensively)
select * from emp where emp_number in (1, 2, 3, 4, 5, 6 etc...);
desired method
select * from emp where emp_number in (call txt_file_with_numbers.txt);
Perhaps you can create a C or C++ program that creates your SELECT stmt at
run time by reading in these values from a text file.
Something along these lines...
char szSqlString[1000];
char szNumbersList[1000];
// Create a function that reads in the list of numbers from a text file and
writes them to 'szNumbersList'...
GetListOfNumbersFromTextFile(szTextFileName, szNumbersList);
snprintf(szSqlString, 1000, "SELECT * from emp where emp_number in ( %s )",
szNumbersList);
SQLExecDirect(hstmt, szSqlString, SQL_NTS);
Alternatively, you could try to do this with parameters, but this would be
MUCH more work.
|||By golley, that works... Thanks.
"Warren Read" wrote:
> Perhaps you can create a C or C++ program that creates your SELECT stmt at
> run time by reading in these values from a text file.
> Something along these lines...
>
> char szSqlString[1000];
> char szNumbersList[1000];
> // Create a function that reads in the list of numbers from a text file and
> writes them to 'szNumbersList'...
> GetListOfNumbersFromTextFile(szTextFileName, szNumbersList);
> snprintf(szSqlString, 1000, "SELECT * from emp where emp_number in ( %s )",
> szNumbersList);
> SQLExecDirect(hstmt, szSqlString, SQL_NTS);
>
> Alternatively, you could try to do this with parameters, but this would be
> MUCH more work.
>
>
>
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Tuesday, March 20, 2012
Query paramaters
I am looking for a way to read a seperate .txt file and extract the
paramaters for a query. I am given a .txt file with several thousand number
s
a day, to query against the database. I would very much like to call the
file and read it's contents in as the paramater. Is this possible through
using just sql?
for example,
current method (paraphrased extensively)
select * from emp where emp_number in (1, 2, 3, 4, 5, 6 etc...);
desired method
select * from emp where emp_number in (call txt_file_with_numbers.txt);Perhaps you can create a C or C++ program that creates your SELECT stmt at
run time by reading in these values from a text file.
Something along these lines...
char szSqlString[1000];
char szNumbersList[1000];
// Create a function that reads in the list of numbers from a text file and
writes them to 'szNumbersList'...
GetListOfNumbersFromTextFile(szTextFileN
ame, szNumbersList);
snprintf(szSqlString, 1000, "SELECT * from emp where emp_number in ( %s )",
szNumbersList);
SQLExecDirect(hstmt, szSqlString, SQL_NTS);
Alternatively, you could try to do this with parameters, but this would be
MUCH more work.|||By golley, that works... Thanks.
"Warren Read" wrote:
> Perhaps you can create a C or C++ program that creates your SELECT stmt at
> run time by reading in these values from a text file.
> Something along these lines...
>
> char szSqlString[1000];
> char szNumbersList[1000];
> // Create a function that reads in the list of numbers from a text file an
d
> writes them to 'szNumbersList'...
> GetListOfNumbersFromTextFile(szTextFileN
ame, szNumbersList);
> snprintf(szSqlString, 1000, "SELECT * from emp where emp_number in ( %s )"
,
> szNumbersList);
> SQLExecDirect(hstmt, szSqlString, SQL_NTS);
>
> Alternatively, you could try to do this with parameters, but this would be
> MUCH more work.
>
>
>
paramaters for a query. I am given a .txt file with several thousand number
s
a day, to query against the database. I would very much like to call the
file and read it's contents in as the paramater. Is this possible through
using just sql?
for example,
current method (paraphrased extensively)
select * from emp where emp_number in (1, 2, 3, 4, 5, 6 etc...);
desired method
select * from emp where emp_number in (call txt_file_with_numbers.txt);Perhaps you can create a C or C++ program that creates your SELECT stmt at
run time by reading in these values from a text file.
Something along these lines...
char szSqlString[1000];
char szNumbersList[1000];
// Create a function that reads in the list of numbers from a text file and
writes them to 'szNumbersList'...
GetListOfNumbersFromTextFile(szTextFileN
ame, szNumbersList);
snprintf(szSqlString, 1000, "SELECT * from emp where emp_number in ( %s )",
szNumbersList);
SQLExecDirect(hstmt, szSqlString, SQL_NTS);
Alternatively, you could try to do this with parameters, but this would be
MUCH more work.|||By golley, that works... Thanks.
"Warren Read" wrote:
> Perhaps you can create a C or C++ program that creates your SELECT stmt at
> run time by reading in these values from a text file.
> Something along these lines...
>
> char szSqlString[1000];
> char szNumbersList[1000];
> // Create a function that reads in the list of numbers from a text file an
d
> writes them to 'szNumbersList'...
> GetListOfNumbersFromTextFile(szTextFileN
ame, szNumbersList);
> snprintf(szSqlString, 1000, "SELECT * from emp where emp_number in ( %s )"
,
> szNumbersList);
> SQLExecDirect(hstmt, szSqlString, SQL_NTS);
>
> Alternatively, you could try to do this with parameters, but this would be
> MUCH more work.
>
>
>
Monday, March 12, 2012
Query output into a file
Hi,
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram GIt depends on which database engine (DB2, Microsoft, Oracle, etc) you are using, and what language was used to write the stored procedure.
-PatP|||Hi,
The database is SQLSERVER2000 and it uses T-SQL.
Bharathram|||Hi,
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram G
T-SQL by itself has no support for saving the output of queries/stored procedures to text files. But you could achieve this using the command line utilities like isql.exe and osql.exe. You could either invoke these exe files directly from command prompt/batch files or from T-SQL using the xp_cmdshell command. Here are the examples:
From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"
From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'
Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.
BCP and Data Transformation Services (DTS) let you export table data to text files.
I hope this will clear your doubts.
Rudra
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram GIt depends on which database engine (DB2, Microsoft, Oracle, etc) you are using, and what language was used to write the stored procedure.
-PatP|||Hi,
The database is SQLSERVER2000 and it uses T-SQL.
Bharathram|||Hi,
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram G
T-SQL by itself has no support for saving the output of queries/stored procedures to text files. But you could achieve this using the command line utilities like isql.exe and osql.exe. You could either invoke these exe files directly from command prompt/batch files or from T-SQL using the xp_cmdshell command. Here are the examples:
From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"
From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'
Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.
BCP and Data Transformation Services (DTS) let you export table data to text files.
I hope this will clear your doubts.
Rudra
query out to XML
hi,
i need to write a query and export the result to an xml file. how would I
do that?
thanksHave a look at the 'FOR XML AUTO' extension to the select statement i.e.
select * from yourTable for xml auto
Peter
"Denial ain't just a river in Egypt."
Mark Twain
"Rafael Chemtob" wrote:
> hi,
> i need to write a query and export the result to an xml file. how would I
> do that?
> thanks
>
>
i need to write a query and export the result to an xml file. how would I
do that?
thanksHave a look at the 'FOR XML AUTO' extension to the select statement i.e.
select * from yourTable for xml auto
Peter
"Denial ain't just a river in Egypt."
Mark Twain
"Rafael Chemtob" wrote:
> hi,
> i need to write a query and export the result to an xml file. how would I
> do that?
> thanks
>
>
Wednesday, March 7, 2012
Query only new records
Hello,
Can I have some advise on how to do the following.
I need to create a csv file based on a query of new records from a table. In
other words the query will run periodically and I dont want it to pick up
the records that were already queried.
ThanksSorry Wont way to put it.
I need to insert not update...
"Al" wrote:
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table. In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||Several ways you can do this, but might require some schema changes on your
part. If you have a DATETIME column, you can use that to determine which
ones are new and which aren't. You could add a CHAR(1) column to indicate
if a row has been queried already or not (you would need to change the value
after the query to indicate that it's been queried). If you have some sort
of incremental key, such as an IDENTITY column you can store the highest
value for that column at query time and the next time you query just grab
any rows with a higher value in that column.
Basically you'll have to store some sort of "state" value so your app will
know where it left off the last time it ran.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EB5EFD0D-8C92-4EFB-B6F2-1D041113852D@.microsoft.com...
> Sorry Wont way to put it.
> I need to insert not update...
> "Al" wrote:
>> Hello,
>> Can I have some advise on how to do the following.
>> I need to create a csv file based on a query of new records from a table.
>> In
>> other words the query will run periodically and I dont want it to pick
>> up
>> the records that were already queried.
>> Thanks|||Use some timestamp column..
Jayesh
"Al" <Al@.discussions.microsoft.com> wrote in message
news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
> In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>> Hello,
>> Can I have some advise on how to do the following.
>> I need to create a csv file based on a query of new records from a table.
>> In
>> other words the query will run periodically and I dont want it to pick
>> up
>> the records that were already queried.
>> Thanks
>
Can I have some advise on how to do the following.
I need to create a csv file based on a query of new records from a table. In
other words the query will run periodically and I dont want it to pick up
the records that were already queried.
ThanksSorry Wont way to put it.
I need to insert not update...
"Al" wrote:
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table. In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||Several ways you can do this, but might require some schema changes on your
part. If you have a DATETIME column, you can use that to determine which
ones are new and which aren't. You could add a CHAR(1) column to indicate
if a row has been queried already or not (you would need to change the value
after the query to indicate that it's been queried). If you have some sort
of incremental key, such as an IDENTITY column you can store the highest
value for that column at query time and the next time you query just grab
any rows with a higher value in that column.
Basically you'll have to store some sort of "state" value so your app will
know where it left off the last time it ran.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EB5EFD0D-8C92-4EFB-B6F2-1D041113852D@.microsoft.com...
> Sorry Wont way to put it.
> I need to insert not update...
> "Al" wrote:
>> Hello,
>> Can I have some advise on how to do the following.
>> I need to create a csv file based on a query of new records from a table.
>> In
>> other words the query will run periodically and I dont want it to pick
>> up
>> the records that were already queried.
>> Thanks|||Use some timestamp column..
Jayesh
"Al" <Al@.discussions.microsoft.com> wrote in message
news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
> In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>> Hello,
>> Can I have some advise on how to do the following.
>> I need to create a csv file based on a query of new records from a table.
>> In
>> other words the query will run periodically and I dont want it to pick
>> up
>> the records that were already queried.
>> Thanks
>
Query only new records
Hello,
Can I have some advise on how to do the following.
I need to create a csv file based on a query of new records from a table. In
other words the query will run periodically and I dont want it to pick up
the records that were already queried.
ThanksSorry Wont way to put it.
I need to insert not update...
"Al" wrote:
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||Several ways you can do this, but might require some schema changes on your
part. If you have a DATETIME column, you can use that to determine which
ones are new and which aren't. You could add a CHAR(1) column to indicate
if a row has been queried already or not (you would need to change the value
after the query to indicate that it's been queried). If you have some sort
of incremental key, such as an IDENTITY column you can store the highest
value for that column at query time and the next time you query just grab
any rows with a higher value in that column.
Basically you'll have to store some sort of "state" value so your app will
know where it left off the last time it ran.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EB5EFD0D-8C92-4EFB-B6F2-1D041113852D@.microsoft.com...[vbcol=seagreen]
> Sorry Wont way to put it.
> I need to insert not update...
> "Al" wrote:
>|||Use some timestamp column..
Jayesh
"Al" <Al@.discussions.microsoft.com> wrote in message
news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
> In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>
Can I have some advise on how to do the following.
I need to create a csv file based on a query of new records from a table. In
other words the query will run periodically and I dont want it to pick up
the records that were already queried.
ThanksSorry Wont way to put it.
I need to insert not update...
"Al" wrote:
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||Several ways you can do this, but might require some schema changes on your
part. If you have a DATETIME column, you can use that to determine which
ones are new and which aren't. You could add a CHAR(1) column to indicate
if a row has been queried already or not (you would need to change the value
after the query to indicate that it's been queried). If you have some sort
of incremental key, such as an IDENTITY column you can store the highest
value for that column at query time and the next time you query just grab
any rows with a higher value in that column.
Basically you'll have to store some sort of "state" value so your app will
know where it left off the last time it ran.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EB5EFD0D-8C92-4EFB-B6F2-1D041113852D@.microsoft.com...[vbcol=seagreen]
> Sorry Wont way to put it.
> I need to insert not update...
> "Al" wrote:
>|||Use some timestamp column..
Jayesh
"Al" <Al@.discussions.microsoft.com> wrote in message
news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
> Hello,
> Can I have some advise on how to do the following.
> I need to create a csv file based on a query of new records from a table.
> In
> other words the query will run periodically and I dont want it to pick up
> the records that were already queried.
> Thanks|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>|||You still need to store the highest timestamp used for the last batch
somewhere...
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:%239FsEdDjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Use some timestamp column..
> Jayesh
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:FD248A9C-EB29-407A-ACF2-6D811BE73E66@.microsoft.com...
>
query on data and log file
If I have to query the two filenames of a database for both data and log file, how can I do
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log file, how can I do
>> it from master database. One method I know is to use the database and call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> > If I have to query the two filenames of a database for both data and log file, how can I
> > do
> > it from master database. One method I know is to use the database and call sp_helpfile.
> > But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log
>> file, how can I do
>> it from master database. One method I know is to use the database and
>> call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file
>> of a database.
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log file, how can I do
>> it from master database. One method I know is to use the database and call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> > If I have to query the two filenames of a database for both data and log file, how can I
> > do
> > it from master database. One method I know is to use the database and call sp_helpfile.
> > But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log
>> file, how can I do
>> it from master database. One method I know is to use the database and
>> call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file
>> of a database.
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
query on data and log file
If I have to query the two filenames of a database for both data and log fil
e, how can I do
it from master database. One method I know is to use the database and call s
p_helpfile.
But I want the query to run from the masterdb about the data and log file of
a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log f
ile, how can I
> do
> it from master database. One method I know is to use the database and call
sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a dat
abase.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
e, how can I do
it from master database. One method I know is to use the database and call s
p_helpfile.
But I want the query to run from the masterdb about the data and log file of
a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log f
ile, how can I
> do
> it from master database. One method I know is to use the database and call
sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a dat
abase.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
query on data and log file
If I have to query the two filenames of a database for both data and log file, how can I do
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.
|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.
|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
Monday, February 20, 2012
Query Log File and Usage Based Optimization
Hi
I have successfully created a QueryLog.trc file but when I enter the Usage Based Optimization wizard it complains that I have not configured the server parameter QueryLog\ QueryLogConnectionString. Does the wizard support the use of a file rather than a table?
Im using the RTM version.
Cheers
The Usage Based Optimization wizard expects to find a SQL Server table.
Here is whitepaper on how to setup a Query Log: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Edward
Thanks.
Subscribe to:
Posts (Atom)