Getting a file name from Query Analyzer

Hi all,

im trying to write a stored procedure that will basically browse a folder and get me the first file that it sees. Is there any way that I can do this in TSQL or using CLR in C#? I was thinking something along the lines of using the dos dir command and triyng to pipe it into a variable, not sure how to go about doing this. Any suggestions?

dir /b ...gives me the bare file names, but it lists all the files in the folder, any way that i can just get the first file ( i dont really care what file).

create table #filelist
(
files varchar(500)
)

truncate table #filelist

insert #filelist
EXEC xp_cmdshell 'dir c:*.* /b'

select top 1 * from #filelist

|||

This might help out:

http://stevekass.com/blog/wp-content/Folders/sql/TextDriver.htm

You can use TOP 1 to get just one file name.

Steve Kass

Drew University

http://www.stevekass.com

YoungEngineer@.discussions.microsoft.com wrote:

> Hi all,

>

> im trying to write a stored procedure that will basically browse a

> folder and get me the first file that it sees. Is there any way that I

> can do this in TSQL or using CLR in C#? I was thinking something along

> the lines of using the dos dir command and triyng to pipe it into a

> variable, not sure how to go about doing this. Any suggestions?

>

> dir /b ...gives me the bare file names, but it lists all the files in

> the folder, any way that i can just get the first file ( i dont really

> care what file).

>

>

>

>

Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and executes the contents a number of other "children" .sql files
Would anybody know how to do this
What happens if one of the .sql files fails? Will the others keep on running
ThankDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
--
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and
executes the contents a number of other "children" .sql files.
Would anybody know how to do this?
What happens if one of the .sql files fails? Will the others keep on runnin
g?
ThanksDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getting @@ROWCOUNT from BULK INSERT with BATCHSIZE set

Hello!
I have a stored procedure that takes a file name as a parameter and
imports the data from there into the appropriate table using the
correct format file. I would like to save the number of rows inserted
at the end of my stored procedure. Since the file name is variable I
use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
generated.
Since some of these files are extremely large I have set the BATCHSIZE
for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
show the number of rows inserted in the last batch. Since the table
name is variable I don't have an easy way of getting the count(*) from
it.
Any suggestions?
Thanks!
-Tom.Aardvark (tom_hummel@.hotmail.com) writes:
> I have a stored procedure that takes a file name as a parameter and
> imports the data from there into the appropriate table using the
> correct format file. I would like to save the number of rows inserted
> at the end of my stored procedure. Since the file name is variable I
> use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
> generated.
> Since some of these files are extremely large I have set the BATCHSIZE
> for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
> show the number of rows inserted in the last batch. Since the table
> name is variable I don't have an easy way of getting the count(*) from
> it.
> Any suggestions?
You could run SELKCT COUNT(*) on the table before and after, but that's
of course ont very appealing on a large table. (The fact that the
table is dynamic should not be a problem. You do know sp_executesql,
don't you? Else read
http://www.sommarskog.se/dynamic_sq...#sp_executesql.
The other option would be to have the client to catch those rows
affected messages for each batch committed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> You could run SELKCT COUNT(*) on the table before and after, but that's
> of course ont very appealing on a large table. (The fact that the
> table is dynamic should not be a problem. You do know sp_executesql,
> don't you? Else read
> http://www.sommarskog.se/dynamic_sq...#sp_executesql.
> The other option would be to have the client to catch those rows
> affected messages for each batch committed.
Thanks for the advice Erland. I had forgotten that sp_executesql
allowed for output parameters, so that should give me what I need using
SELECT COUNT(*). Not the most elegant way and it will suffer a
performance hit since some of the tables can be quite large, but it's a
process that runs for up to 12 hours once a month, so a couple minutes
to get a count from a table shouldn't be a problem.
Thanks!
-Tom.

GetImage=8.00.878.00f1_rsc_htmlviewer.html

Hi
I am trying to get the HTML viewer help file to appear. When I click the
help icon I go get a new window with the URL
http://myserver1/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
which is blank ( file exixts on the server )
when I goto another server
http://myserver2/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
the file pops right up.
I have changed some of the report renderings on both servers 1 & 2
( Identical changes ).
THere is no documentation that really helps here ( especially if 8.00.878.00
is getting the english language version)
Do I have to set some language setting in RS ?
TIA
Ray
--
Illusion is the first of all pleasures.I can't really think of a reason why you would not see the file. Here are
some things you can try:
Try a ctrl-F5 refresh, the help page is set to live on the client for 30
days, perhaps the client copy is corrupt.
Ensure each language folder has the help file and that they are not blank.
As for the language, RS will query the browser language and select the help
file based on that. If the language is not supported then you should get
English.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"punchcardRay" <punchcardRay@.discussions.microsoft.com> wrote in message
news:42B3F70D-1383-4789-8CF1-9091C20DAFC7@.microsoft.com...
> Hi
> I am trying to get the HTML viewer help file to appear. When I click the
> help icon I go get a new window with the URL
>
http://myserver1/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
> which is blank ( file exixts on the server )
> when I goto another server
>
http://myserver2/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
> the file pops right up.
> I have changed some of the report renderings on both servers 1 & 2
> ( Identical changes ).
> THere is no documentation that really helps here ( especially if
8.00.878.00
> is getting the english language version)
> Do I have to set some language setting in RS ?
> TIA
> Ray
>
> --
> Illusion is the first of all pleasures.|||Daniel
I did the refresh with no luck, I also checked to make sure the files were
there and they were. I have seen two other posts that were basically the same
as mine, is this issue on a defect list ?
Ray
"Daniel Reib [MSFT]" wrote:
> I can't really think of a reason why you would not see the file. Here are
> some things you can try:
> Try a ctrl-F5 refresh, the help page is set to live on the client for 30
> days, perhaps the client copy is corrupt.
> Ensure each language folder has the help file and that they are not blank.
> As for the language, RS will query the browser language and select the help
> file based on that. If the language is not supported then you should get
> English.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "punchcardRay" <punchcardRay@.discussions.microsoft.com> wrote in message
> news:42B3F70D-1383-4789-8CF1-9091C20DAFC7@.microsoft.com...
> > Hi
> > I am trying to get the HTML viewer help file to appear. When I click the
> > help icon I go get a new window with the URL
> >
> http://myserver1/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
> > which is blank ( file exixts on the server )
> > when I goto another server
> >
> http://myserver2/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
> > the file pops right up.
> >
> > I have changed some of the report renderings on both servers 1 & 2
> > ( Identical changes ).
> > THere is no documentation that really helps here ( especially if
> 8.00.878.00
> > is getting the english language version)
> > Do I have to set some language setting in RS ?
> >
> > TIA
> > Ray
> >
> >
> >
> > --
> > Illusion is the first of all pleasures.
>
>|||I'm not sure what would cause this behavior. I will investigate this
further and see if I can come up with a resolution.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"punchcardRay" <punchcardRay@.discussions.microsoft.com> wrote in message
news:65BA2102-916C-4879-A99D-EFA6DD59E7F0@.microsoft.com...
> Daniel
> I did the refresh with no luck, I also checked to make sure the files were
> there and they were. I have seen two other posts that were basically the
same
> as mine, is this issue on a defect list ?
> Ray
>
> "Daniel Reib [MSFT]" wrote:
> > I can't really think of a reason why you would not see the file. Here
are
> > some things you can try:
> >
> > Try a ctrl-F5 refresh, the help page is set to live on the client for 30
> > days, perhaps the client copy is corrupt.
> >
> > Ensure each language folder has the help file and that they are not
blank.
> >
> > As for the language, RS will query the browser language and select the
help
> > file based on that. If the language is not supported then you should
get
> > English.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "punchcardRay" <punchcardRay@.discussions.microsoft.com> wrote in message
> > news:42B3F70D-1383-4789-8CF1-9091C20DAFC7@.microsoft.com...
> > > Hi
> > > I am trying to get the HTML viewer help file to appear. When I click
the
> > > help icon I go get a new window with the URL
> > >
> >
http://myserver1/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
> > > which is blank ( file exixts on the server )
> > > when I goto another server
> > >
> >
http://myserver2/ReportServer?rs:Command=Get&rc:GetImage=8.00.878.00f1_rsc_htmlviewer.htm
> > > the file pops right up.
> > >
> > > I have changed some of the report renderings on both servers 1 & 2
> > > ( Identical changes ).
> > > THere is no documentation that really helps here ( especially if
> > 8.00.878.00
> > > is getting the english language version)
> > > Do I have to set some language setting in RS ?
> > >
> > > TIA
> > > Ray
> > >
> > >
> > >
> > > --
> > > Illusion is the first of all pleasures.
> >
> >
> >sql

Get with the program

It is very interesting to me that most people in this group can't do simple
SQL. Are you viewing MySQL as a simple file system?As long as you do that,
you will
not understand SQL in any dialect. So from now on:

When you have a question about SQL. post the table structures. Uh, "With
Create".
Post some sample data in the form of INSERTS.

Regards,
Rich

--
The journey is the reward."Rich R" <rryan@.cshore.com> wrote in message
news:IxhHd.19290$by5.3314@.newssvr19.news.prodigy.c om...
> It is very interesting to me that most people in this group can't do
simple
> SQL. Are you viewing MySQL as a simple file system?As long as you do that,
> you will
> not understand SQL in any dialect. So from now on:
> When you have a question about SQL. post the table structures. Uh, "With
> Create".
> Post some sample data in the form of INSERTS.
>
> Regards,
> Rich
> --
> The journey is the reward.

Sorry, wrong group. Hate when that happens. Please ignore.

Regards,
Rich|||"Rich R" <rryan@.cshore.com> wrote in message
news:0AhHd.19291$by5.12203@.newssvr19.news.prodigy. com...
> "Rich R" <rryan@.cshore.com> wrote in message
> news:IxhHd.19290$by5.3314@.newssvr19.news.prodigy.c om...
>> It is very interesting to me that most people in this group can't do
> simple
>> SQL. Are you viewing MySQL as a simple file system?As long as you do
>> that,
>> you will
>> not understand SQL in any dialect. So from now on:
>>
>> When you have a question about SQL. post the table structures. Uh, "With
>> Create".
>> Post some sample data in the form of INSERTS.
>>
>>
>> Regards,
>> Rich
>>
>> --
>> The journey is the reward.
>
> Sorry, wrong group. Hate when that happens. Please ignore.
> Regards,
> Rich

That's alright, its a universal issue, just ask --CELKO--

Get the error in dos cmd line

Hi everyone,

I have a cmd file that executes sql statements:

something like this:

Code Snippet

OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -

...

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1

GOTO EXIT

if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)

What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.

Any help please

Thanx in advance

I really need to know if there is some variable that gives me the sql error because when I execute the cmd, if there's an error

the error is printed int the cmd window.

The errorlevel var just give me the number "1" but I would like to get something like

"An error in database ocurred due to database already exists" or something like that.

Please I'll apreciate any ideias you may have
|||

Instead of overwriting the error log file, I would just append the error to the file instead.

Code Snippet

ECHO OFF

OSQL -S %sqlServer% -E -b -n -i test.sql > %1
IF ERRORLEVEL 1 GOTO error
ECHO "" > %1
EXIT

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] >> %1

This way you either have a blank file, or you have a file with an error message and errorLevel code.

|||Thanx for the answer ShawnNWF, that will do the trick for sure

Get the error in dos cmd line

Hi everyone,

I have a cmd file that executes sql statements:

something like this:

Code Snippet

OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -

...

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1

GOTO EXIT

if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)

What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.

Any help please

Thanx in advance

I really need to know if there is some variable that gives me the sql error because when I execute the cmd, if there's an error

the error is printed int the cmd window.

The errorlevel var just give me the number "1" but I would like to get something like

"An error in database ocurred due to database already exists" or something like that.

Please I'll apreciate any ideias you may have
|||

Instead of overwriting the error log file, I would just append the error to the file instead.

Code Snippet

ECHO OFF

OSQL -S %sqlServer% -E -b -n -i test.sql > %1
IF ERRORLEVEL 1 GOTO error
ECHO "" > %1
EXIT

:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] >> %1

This way you either have a blank file, or you have a file with an error message and errorLevel code.

|||Thanx for the answer ShawnNWF, that will do the trick for sure

Get report design from Report Manager

If you cannot find the current Visual Studio sln project file, can you obtain
the report design from the Report Manger to plug back into the designer to
ensure that you start from the most current report verision held on the
server?
Thanks
IanYes. Using Report Manager, select the report you want and from the
Properties tab click on the Edit link to download the rdl file. FYI -
there is a nice and free utility called Reporting Services Scripter by
Jasper Smith that can grab multiple files and download ot transfer them
to another RS - http://www.sqldbatips.com/showarticle.asp?ID=62
Matt A|||Thanks Matt I'd completely forgotten this Edit link facility
"reportdude" wrote:
> Yes. Using Report Manager, select the report you want and from the
> Properties tab click on the Edit link to download the rdl file. FYI -
> there is a nice and free utility called Reporting Services Scripter by
> Jasper Smith that can grab multiple files and download ot transfer them
> to another RS - http://www.sqldbatips.com/showarticle.asp?ID=62
> Matt A
>

get path of the database file using a query

Hi guys,
all i want to do is , get the path of the location of my database and log
file using a query of a command i could write down in the query anylyser.
Thanks
Sameer MuzammilTry using:
sp_helpfile
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"sameer" <sameer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi,
You could also use
sp_helpdb <dbname>
Thanks
Hari
SQL Server MVP
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi Sameer,
You can extract info from sysdatabses system table. As Jens replied you
sp_helpfile this system stored procdure also make use of this system table.
select name,filename from sysdatabases
go
Thanks
Syed Zulfiqar
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil

get path of the database file using a query

Hi guys,
all i want to do is , get the path of the location of my database and log
file using a query of a command i could write down in the query anylyser.
Thanks
Sameer Muzammil
Try using:
sp_helpfile
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"sameer" <sameer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil
|||Hi,
You could also use
sp_helpdb <dbname>
Thanks
Hari
SQL Server MVP
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil
|||Hi Sameer,
You can extract info from sysdatabses system table. As Jens replied you
sp_helpfile this system stored procdure also make use of this system table.
select name,filename from sysdatabases
go
Thanks
Syed Zulfiqar
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil

get path of the database file using a query

Hi guys,
all i want to do is , get the path of the location of my database and log
file using a query of a command i could write down in the query anylyser.
Thanks
Sameer MuzammilTry using:
sp_helpfile
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"sameer" <sameer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi,
You could also use
sp_helpdb <dbname>
Thanks
Hari
SQL Server MVP
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi Sameer,
You can extract info from sysdatabses system table. As Jens replied you
sp_helpfile this system stored procdure also make use of this system table.
select name,filename from sysdatabases
go
Thanks
Syed Zulfiqar
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil

Get only a certain piece of a string

I have a table that holds file paths for reports. Let's say it looks like this:

C:\Jeremy\Testing\JCScoobyRS\Testing.txt

Let's assume that none of the files are in the same directory and the directory is not known, as I'm running a report to get only file names. How can I get only file names from the string listed above? Thanks, JeremyBetter to store PATH & FILENAME in seperate variables.

Refer to this link (http://www.nigelrivett.net/CheckIfFileExists.html) for XP_FILEEXISTS which searches for the specified file, where you can use it for your task.

HTH|||That's my (lazy) way to do it:

declare @.FP varchar(255)
select @.FP='C:\Jeremy\Testing\JCScoobyRS\Testing.txt'
select reverse(substring(reverse(@.FP),1,charindex('\',rev erse(@.FP))-1))|||Valid tip Kukuk, keep it up.