Board index MS SQL Server

Format the output of a stored proc

Format the output of a stored proc

Post by Sean Arm » Wed, 12 Jul 2000 04:00:00



I have the following stored procedure:

CREATE PROCEDURE [db_FileSize] AS

SET NOCOUNT ON


PRINT '-------- Database Space Usage Report --------'

DECLARE database_cursor CURSOR FOR
SELECT [name]
FROM master..sysdatabases
--ORDER BY [name]

OPEN database_cursor

FETCH NEXT FROM database_cursor


BEGIN
    PRINT ''

    PRINT ''

    FETCH NEXT FROM database_cursor

END

CLOSE database_cursor

DEALLOCATE database_cursor

When I run this with xp_sendmail or as a regular job and have it create an
output file is looks like this.  Does anyone know a way I can format the
output?

Thanks,

-------- Database Space Usage Report --------

Database: master

name                                             db_size
 owner                                            dbid   created
 status

 ----------------------------------------------- -------------
 ------------------------------------------------ ------ -----------
 -----------------------------------------------------------------------
 -----------------------------------------------------------------------
 -----------------------------------------------------------------------
 ------------------------------------------
master                                                10.94 MB
 sa                                                    1 Nov 13 1998
 trunc. log on chkpt.

name

                                    fileid
 filename

 filegroup

 size
 maxsize
 growth                               usage
 ---------------------------------------------------------------------------
---
 -----------------------------------------------------------------------
 -----------------------------------------------------------------------
 ---------------------------------- ------
 -----------------------------------------------------------------------
 -----------------------------------------------------------------------
 -----------------------------------------------------------------------
 ------------------------------------------
 -----------------------------------------------------------------------
 -----------------------------------------------------------------------
 -----------------------------------------------------------------------
 ------------------------------------------
 ------------------------------------
 ------------------------------------
 ------------------------------------ ---------
master

                                         1
 e:\MSSQL7db\data\master.mdf

 PRIMARY

 9920 KB
 Unlimited
 10%                                  data only
mastlog

                                         2
 e:\MSSQL7db\data\mastlog.ldf

 NULL

 1280 KB
 Unlimited
 10%                                  log only

 
 
 
Top

Format the output of a stored proc

Post by BPMargoli » Wed, 12 Jul 2000 04:00:00


Sean,

Perhaps you can execute the stored procedure from isql/osql. Both isql and osql
have a -w parameter that controls the width of the output.

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.


> I have the following stored procedure:

> CREATE PROCEDURE [db_FileSize] AS

> SET NOCOUNT ON


> PRINT '-------- Database Space Usage Report --------'

> DECLARE database_cursor CURSOR FOR
> SELECT [name]
> FROM master..sysdatabases
> --ORDER BY [name]

> OPEN database_cursor

> FETCH NEXT FROM database_cursor


> BEGIN
>     PRINT ''

>     PRINT ''

>     FETCH NEXT FROM database_cursor

> END

> CLOSE database_cursor

> DEALLOCATE database_cursor

> When I run this with xp_sendmail or as a regular job and have it create an
> output file is looks like this.  Does anyone know a way I can format the
> output?

> Thanks,

> -------- Database Space Usage Report --------

> Database: master

> name                                             db_size
>  owner                                            dbid   created
>  status

>  ----------------------------------------------- -------------
>  ------------------------------------------------ ------ -----------
>  -----------------------------------------------------------------------
>  -----------------------------------------------------------------------
>  -----------------------------------------------------------------------
>  ------------------------------------------
> master                                                10.94 MB
>  sa                                                    1 Nov 13 1998
>  trunc. log on chkpt.

> name

>                                     fileid
>  filename

>  filegroup

>  size
>  maxsize
>  growth                               usage
>  ---------------------------------------------------------------------------
> ---
>  -----------------------------------------------------------------------
>  -----------------------------------------------------------------------
>  ---------------------------------- ------
>  -----------------------------------------------------------------------
>  -----------------------------------------------------------------------
>  -----------------------------------------------------------------------
>  ------------------------------------------
>  -----------------------------------------------------------------------
>  -----------------------------------------------------------------------
>  -----------------------------------------------------------------------
>  ------------------------------------------
>  ------------------------------------
>  ------------------------------------
>  ------------------------------------ ---------
> master

>                                          1
>  e:\MSSQL7db\data\master.mdf

>  PRIMARY

>  9920 KB
>  Unlimited
>  10%                                  data only
> mastlog

>                                          2
>  e:\MSSQL7db\data\mastlog.ldf

>  NULL

>  1280 KB
>  Unlimited
>  10%                                  log only


 
 
 
Top

1. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

I apologize in advance for the long post and if I don't make any sense but I
will try to explain my problem:

I am having a hard time figuring out how to retrieve an output parameter
from a dynamically generated stored proc call within another stored proc.

We're running SQL Server 7.

The problem: several stored procedures need to get a return value from a
second stored procedure on the same database that calls yet another stored
procedure on a linked server.  This final stored procedure on the linked
server returns a value and must pass it back through to the first proc.

The kicker is that the 'middle' stored proc that calls the linked server
stored proc gets what linked server it is dynamically as a parameter.  So I
have to dynamically build an 'execute' string.  And this is where I am
having trouble concatenating on the OUTPUT portion.

So some simplified code kind of looks like this:

On Server #1

CREATE PROCEDURE DoSomethingThatNeedsKey AS




BEGIN




END

In the same database...


AS

BEGIN

END

On Server #2:


AS
BEGIN


END

Now I would expect when I get back to the 'DoSomethingThatNeedsKey'

The code fails in 'GetNewKey1' proc, and gives me an error of

"Must declare the variable 'NewKey1' "

I thought that NewKey1 was already declared in the parameter list!  Am I not



value and move it outside of the quotes, I get a "Cannot use the OUTPUT
option when passing a constant to a stored procedure." error.

I am definitely a T-SQL novice, but I have tried everything I can think of:
creating a cursor in GetNewKey1, trying to do an sp_ExecuteSQL call, but
nothing seems to work.  Either I get the errors above or I just can't
compile.  All of the examples that I have seen that try to pass back an
output parameter in dynamically generated calls are always using SELECT
queries and not EXECUTES to other procs.

I have used this technique in the past and it always worked.  But that was
without OUTPUT tacked on.

Am I even making sense?  Is there a better way to set a variable to the
result of an EXECUTE statement?

Any and all help sincerely appreciated.

-David

2. Help Loading Text File

3. ? max len of output param for stored proc

4. tempdb filling up

5. store proc output into temp table

6. Progress Programmer/Analysts needed-U.S.-(Recruiter)

7. Stored Proc Return values / Output Params w ADO and SQL Server 7

8. Performance Issues With Informix ODBC Driver?

9. large output params from a stored proc

10. Stored Proc Return values / Output Params w ADO and SQL Server 7

11. Returning Text field as stored proc output parameter

12. Trying to send stored proc output to a file

13. Multiple OUTPUT variables in Stored Proc


All times are UTC
Board index