Exec a resultset to .txt file directly.

Hi all.
Is there a way I can have my resultset saved to a file directly from a SP
using SQL2000.
E.g.
use northwind
CREATE PROCEDURE ToTxt
AS
SELECT *
FROM Customers
/* I want something like*/
/*
SELECT *
FROM Customers
TO c:\Cust.txt
*/
/* Then I can run the following to create the file */
Exec ToTxt
Thanx all.
ghNo, One way is to use xp_cmdshell to execute either OSQL or BCP to output th
e text file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geir Holme" <geir@.multicase.no> wrote in message news:Odb$wT9JGHA.1312@.TK2MSFTNGP09.phx.gb
l...
> Hi all.
> Is there a way I can have my resultset saved to a file directly from a SP
> using SQL2000.
> E.g.
> use northwind
> CREATE PROCEDURE ToTxt
> AS
> SELECT *
> FROM Customers
> /* I want something like*/
> /*
> SELECT *
> FROM Customers
> TO c:\Cust.txt
> */
> /* Then I can run the following to create the file */
> Exec ToTxt
> Thanx all.
> gh
>
>

Exec a resultset to .txt file directly.

Hi all.
Is there a way I can have my resultset saved to a file directly from a SP
using SQL2000.
E.g.
use northwind
CREATE PROCEDURE ToTxt
AS
SELECT *
FROM Customers
/* I want something like*/
/*
SELECT *
FROM Customers
TO c:\Cust.txt
*/
/* Then I can run the following to create the file */
Exec ToTxt
Thanx all.
ghTry something like :
EXEC master..xp_cmdshell 'osql.exe -S MyServer -U MyUserName -P MyPassword -
Q "EXEC storedProcedure" -o "C:\savedResultset.txt"'
--
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains - www.ciquery.com/articles/art_
04.asp
"Geir Holme" <geir@.multicase.no> wrote in message news:%23rEX5SyJGHA.2808@.TK2MSFTNGP15.phx.
gbl...
> Hi all.
> Is there a way I can have my resultset saved to a file directly from a SP
> using SQL2000.
>
> E.g.
>
> use northwind
> CREATE PROCEDURE ToTxt
> AS
> SELECT *
> FROM Customers
> /* I want something like*/
> /*
> SELECT *
> FROM Customers
> TO c:\Cust.txt
> */
>
> /* Then I can run the following to create the file */
> Exec ToTxt
>
> Thanx all.
>
> gh
>
>|||It would be something like
e.g.: XP_cmdshell 'OSQL -E -Q"SELECT * FROM Customers" -O"C:\Cust.txt"'
paramters may vary on your server settings.
HTH, Jens Suessmeyer.sql

Excel to Excel drill down

I have 2 reports, Parent and Child, Parent drills-down to the Child. The
parent is an RS report which goes directly to Excel, therefore the hyperlink
in the Parent, points to the Child. Now I also want the child to go directly
to Excel, however I can't make it work. I can't use the rs:Format as a
parameter, it won't let me - anybody got this to work please ?
Cheers,
--
Mike StephenI think the closest you can get is instead of using a drillthrough action,
you use an expression to dynamically construct a hyperlink action that uses
URL access to request the report with the desired parameter values and
exports to Excel. You may want to read up in BOL on the Globals collection
(which contains for instance the name of the report server) and how to pass
parameter values correctly encoded on URL access.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Stephen" <MikeStephen@.discussions.microsoft.com> wrote in message
news:EC400A67-E402-4678-A9F1-C509FCFD5873@.microsoft.com...
>I have 2 reports, Parent and Child, Parent drills-down to the Child. The
> parent is an RS report which goes directly to Excel, therefore the
> hyperlink
> in the Parent, points to the Child. Now I also want the child to go
> directly
> to Excel, however I can't make it work. I can't use the rs:Format as a
> parameter, it won't let me - anybody got this to work please ?
> Cheers,
> --
> Mike Stephen|||OK, thanks Robert, I'll have a dig around BOL. If I get the answer I'll post
it here. Cheers,
Mike.
--
Mike Stephen
"Robert Bruckner [MSFT]" wrote:
> I think the closest you can get is instead of using a drillthrough action,
> you use an expression to dynamically construct a hyperlink action that uses
> URL access to request the report with the desired parameter values and
> exports to Excel. You may want to read up in BOL on the Globals collection
> (which contains for instance the name of the report server) and how to pass
> parameter values correctly encoded on URL access.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Mike Stephen" <MikeStephen@.discussions.microsoft.com> wrote in message
> news:EC400A67-E402-4678-A9F1-C509FCFD5873@.microsoft.com...
> >I have 2 reports, Parent and Child, Parent drills-down to the Child. The
> > parent is an RS report which goes directly to Excel, therefore the
> > hyperlink
> > in the Parent, points to the Child. Now I also want the child to go
> > directly
> > to Excel, however I can't make it work. I can't use the rs:Format as a
> > parameter, it won't let me - anybody got this to work please ?
> > Cheers,
> > --
> > Mike Stephen
>
>|||OK, I've given a try to the suggestions in BOL - but the problem is to be
able to parameterise the URL. The examples that BOL gives, shows how to
hard-code the URL, fine, but my drill from the Parent to the Child isn't
fixed. How can I parameterise the URL please ?
Many thanks,
Mike.
--
Mike Stephen
"Mike Stephen" wrote:
> OK, thanks Robert, I'll have a dig around BOL. If I get the answer I'll post
> it here. Cheers,
> Mike.
> --
> Mike Stephen
>
> "Robert Bruckner [MSFT]" wrote:
> > I think the closest you can get is instead of using a drillthrough action,
> > you use an expression to dynamically construct a hyperlink action that uses
> > URL access to request the report with the desired parameter values and
> > exports to Excel. You may want to read up in BOL on the Globals collection
> > (which contains for instance the name of the report server) and how to pass
> > parameter values correctly encoded on URL access.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Mike Stephen" <MikeStephen@.discussions.microsoft.com> wrote in message
> > news:EC400A67-E402-4678-A9F1-C509FCFD5873@.microsoft.com...
> > >I have 2 reports, Parent and Child, Parent drills-down to the Child. The
> > > parent is an RS report which goes directly to Excel, therefore the
> > > hyperlink
> > > in the Parent, points to the Child. Now I also want the child to go
> > > directly
> > > to Excel, however I can't make it work. I can't use the rs:Format as a
> > > parameter, it won't let me - anybody got this to work please ?
> > > Cheers,
> > > --
> > > Mike Stephen
> >
> >
> >|||Just add a textbox in the report and "play" with expressions till the
evaluated expression will generate a URL that contains what you need. The
Globals collection is documented here:
http://msdn.microsoft.com/library/en-us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp
For instance, start with:
=Globals!ReportServerUrl & "?" & Globals!ReportFolder &
"DrillthroughReportName&rs:Command=Render&rs:Format=EXCEL&rc:Parameters=false&YearParameter="
& Fields!Year.Value
Note: parameter values in general should always get encoded by using
HttpUtility.UrlEncode("<parameter value") - otherwise the generated URL may
be invalid and get blocked by IE, report server, etc.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Stephen" <MikeStephen@.discussions.microsoft.com> wrote in message
news:4FC84921-27D8-45D5-AEAE-2D2CC63A9542@.microsoft.com...
> OK, I've given a try to the suggestions in BOL - but the problem is to be
> able to parameterise the URL. The examples that BOL gives, shows how to
> hard-code the URL, fine, but my drill from the Parent to the Child isn't
> fixed. How can I parameterise the URL please ?
> Many thanks,
> Mike.
> --
> Mike Stephen
>
> "Mike Stephen" wrote:
>> OK, thanks Robert, I'll have a dig around BOL. If I get the answer I'll
>> post
>> it here. Cheers,
>> Mike.
>> --
>> Mike Stephen
>>
>> "Robert Bruckner [MSFT]" wrote:
>> > I think the closest you can get is instead of using a drillthrough
>> > action,
>> > you use an expression to dynamically construct a hyperlink action that
>> > uses
>> > URL access to request the report with the desired parameter values and
>> > exports to Excel. You may want to read up in BOL on the Globals
>> > collection
>> > (which contains for instance the name of the report server) and how to
>> > pass
>> > parameter values correctly encoded on URL access.
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Mike Stephen" <MikeStephen@.discussions.microsoft.com> wrote in message
>> > news:EC400A67-E402-4678-A9F1-C509FCFD5873@.microsoft.com...
>> > >I have 2 reports, Parent and Child, Parent drills-down to the Child.
>> > >The
>> > > parent is an RS report which goes directly to Excel, therefore the
>> > > hyperlink
>> > > in the Parent, points to the Child. Now I also want the child to go
>> > > directly
>> > > to Excel, however I can't make it work. I can't use the rs:Format as
>> > > a
>> > > parameter, it won't let me - anybody got this to work please ?
>> > > Cheers,
>> > > --
>> > > Mike Stephen
>> >
>> >
>> >