how can i print what i see after i expand a drill down?

I've created a report with drill downs that are hidden initially. While viewing the report in preview, I'll expand the drill down to see the data.

If I go and print the report, the report is printed as collapsed (which is the initial setting) rather than expanded (which i expanded in the preview).

Is there a way to print in the "what-you-see-is-what-you-get" kinda way expanding and collapsing drill downs?

Thanks in advance!

Try setting the InitialToggleState to Expanded in the properties section of that Drill down columns.

Hope this helps.....

- Deepak

|||

it odd, if i start out in expanded and then collapse it in the preview tab, the printing will follow along (meaning i can collapse 1 and print it as i see it), but if i start out collapse and try to expand it in the preview tab, the print doesn't follow?

anyone else have any ideas?

How can I print table structures in SQLExpress

I need to get a print out of column names, types, and properties for each of the tables. We just started using SQL Express from Access. I cannot find any reference to printing the structures in Microsoft SQL Server Management Studio Express.

Thanks

hi,

this is actually non avaliable at all..

you can have a "similar" result via SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en) "Diagrams" features.. add all required tables + views and print it out.. not very beautifull but available.. or get them to clipboard and pasting them into Paint or the like...

regards

|||

Sigh............................

Thanks

|||

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

|||

Subtle wrote:

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

How about using sp_describe_cursor_columns? Then you can print the output from C# or something?

|||

hi,

I'd better go for INFORMATION_SCHEMA ANSI view or, if your really want to go a quiet unsupported way, you could even resort on an unsupported/undocumented stored procedure, sp_MSForEach (http://www.databasejournal.com/features/mssql/article.php/3441031)..

then you can define your query to be executed against all database tables...

for instance something based on catalog views, similar to

SET NOCOUNT ON;

USE AdventureWorks;

GO

DECLARE @.cmd varchar(MAX);

SET @.cmd = 'SELECT ''?'' AS [ObjName];

SELECT clmns.name AS [Name]

, usrt.name AS [DataType], ISNULL(baset.name, N'''') AS [SystemType]

, clmns.is_nullable, clmns.is_identity, clmns.is_rowguidcol, clmns.is_computed

, CASE WHEN clmns.name IN (''timestamp'', ''rowversion'') THEN 1 ELSE 0 END AS is_timestamp

, CASE WHEN clmns.default_object_id <> 0 THEN 1 ELSE 0 END AS [has_default_value]

, CASE WHEN clmns.default_object_id = 0 THEN '''' ELSE

(SELECT d.definition

FROM sys.default_constraints d

WHERE(clmns.object_id = d.parent_object_id)

AND d.parent_column_id = clmns.column_id)

END AS [Default Definition]

, CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length]

, CAST(clmns.precision AS int) AS [NumericPrecision]

, CAST(clmns.scale AS int) AS [NumericScale]

FROM sys.tables AS tbl

INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id

WHERE clmns.object_id = OBJECT_ID(''?'')

ORDER BY clmns.column_id ASC;'

EXEC sp_MSforeachtable @.cmd;

--<--

ObjName

-

[Production].[ProductProductPhoto]

Name DataType SystemType is_nullable is_identity is_rowguidcol is_computed is_timestamp has_default_value Default Definition Length NumericPrecision NumericScale

- -- -- -- - -- -- -- -- -

ProductID int int 0 0 0 0 0 0 4 10 0

ProductPhotoID int int 0 0 0 0 0 0 4 10 0

Primary Flag bit 0 0 0 0 0 1 ((0)) 1 1 0

ModifiedDate datetime datetime 0 0 0 0 0 1 (getdate()) 8 23 3

...other objs..

you are then obviously free to add/remove projections as required..

regards

How can I print my SQL Server agent jobs?

GlacierCall me old school, but sometimes I'd like to just print out a SQL Server agent job for review.
Is there a built-in feature for this?
Does somebody know of an external script, shareware tool, etc. that can do this?
Thanks!
--Wayne
SQL Server 2005:
Open Management Studio
Open Object Explorer
Expand (server/instance x) registration
Expand SQL Server Agent
Expand Jobs
Right-click (job x) and choose Script Job As > Create To >
Choose New Query Editor Window, FIle, or Clipboard
SQL Server 2000:
Open Enterprise Mangler
Expand (server/instance x) registration
Expand Management
Expand SQL Server Agent
Left-click on Jobs
In the right-hand pane, Right-click (job x) and choose All Tasks > Generate
SQL Script
Enter a file name (with path)
Click OK
"Wayne Erfling" <wayne_erfling@.mindspring.com> wrote in message
news:%23w$CEKbWHHA.600@.TK2MSFTNGP05.phx.gbl...
GlacierCall me old school, but sometimes I'd like to just print out a SQL
Server agent job for review.
Is there a built-in feature for this?
Does somebody know of an external script, shareware tool, etc. that can do
this?
Thanks!
--Wayne
|||I did that, but the "substance" of the stored procedures (what the steps execute) is a relatively small percentage of the total script.
I was looking for something more formatted, and thought maybe there was something like that as part of somebody's tool or add-on. "print" and "job" are so common that I just get a lot of useless pages when I do a [e.g. Google] search.
--Wayne
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:eu4F13cWHHA.5060@.TK2MSFTNGP06.phx.gbl...
SQL Server 2005:
Open Management Studio
Open Object Explorer
Expand (server/instance x) registration
Expand SQL Server Agent
Expand Jobs
Right-click (job x) and choose Script Job As > Create To >
Choose New Query Editor Window, FIle, or Clipboard
SQL Server 2000:
Open Enterprise Mangler
Expand (server/instance x) registration
Expand Management
Expand SQL Server Agent
Left-click on Jobs
In the right-hand pane, Right-click (job x) and choose All Tasks > Generate
SQL Script
Enter a file name (with path)
Click OK
"Wayne Erfling" <wayne_erfling@.mindspring.com> wrote in message
news:%23w$CEKbWHHA.600@.TK2MSFTNGP05.phx.gbl...
GlacierCall me old school, but sometimes I'd like to just print out a SQL
Server agent job for review.
Is there a built-in feature for this?
Does somebody know of an external script, shareware tool, etc. that can do
this?
Thanks!
--Wayne
|||So are you saying that you only care about the SQL code embedded within each
step? Then you are not trying to print the job, you are trying to print the
SQL code and/or the stored procedures that are called.
Ideally, your jeb steps call stored procedures, then it's just a matter of
EXEC sp_helptext procedure_name and printing the result.
Otherwise, you have to go after the raw SQL code in the sys tables in msdb.
What version of SQL Server are you using?
"Wayne Erfling" <wayne_erfling@.mindspring.com> wrote in message
news:%23cySwWnWHHA.1636@.TK2MSFTNGP02.phx.gbl...
I did that, but the "substance" of the stored procedures (what the steps
execute) is a relatively small percentage of the total script.
I was looking for something more formatted, and thought maybe there was
something like that as part of somebody's tool or add-on. "print" and "job"
are so common that I just get a lot of useless pages when I do a [e.g.
Google] search.
--Wayne
|||Thanks for sticking around.
I'm just looking for something to bring a multiple step job together as a whole (and that I will be able to print for reference). For example:
Step 1 "name of step" (database=<database>)
EXEC INITIALIZE_BIG_JOB
Step 2 "name of step" (database=<database>)
EXEC INSERT_SOME_DATA
Step 2 "name of step" (database=<database>)
EXEC UPDATE_SOME_DATA
and so on.
I'm using SSMS as my front end, mostly with SQL Server 2000 data bases.
I guess I could put something together from the sys tables. I found a real useful tool to sort project files in SSMS, and just thought somebody must have written some tools to help with SQL jobs.
Thanks again.
--Wayne
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:OChxbfnWHHA.4216@.TK2MSFTNGP02.phx.gbl...
So are you saying that you only care about the SQL code embedded within each
step? Then you are not trying to print the job, you are trying to print the
SQL code and/or the stored procedures that are called.
Ideally, your jeb steps call stored procedures, then it's just a matter of
EXEC sp_helptext procedure_name and printing the result.
Otherwise, you have to go after the raw SQL code in the sys tables in msdb.
What version of SQL Server are you using?
"Wayne Erfling" <wayne_erfling@.mindspring.com> wrote in message
news:%23cySwWnWHHA.1636@.TK2MSFTNGP02.phx.gbl...
I did that, but the "substance" of the stored procedures (what the steps
execute) is a relatively small percentage of the total script.
I was looking for something more formatted, and thought maybe there was
something like that as part of somebody's tool or add-on. "print" and "job"
are so common that I just get a lot of useless pages when I do a [e.g.
Google] search.
--Wayne

How can I print column name only from table?

Is there some Sp out there?select column_name
from information_schema.columns where
table_name = 'tbl1'
tbl1 is the table for which you want to find the list of columns.
Hope this is what you want.
--
"coke" wrote:

> Is there some Sp out there?|||coke,
Check the view INFORMATION_SCHEMA.COLUMNS.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'Orders'
order by ordinal_position
go
AMB
"coke" wrote:

> Is there some Sp out there?sql

How can i print a ssrs report from a aspx page without opening the report

Hello friends

How can i print a ssrs report from a aspx page without opening the report in viewer

I need to print the report when the user clicks on the print button

Thank you

Sara

Please.....Can somebody help.

Sara

|||You'll need to use the ssrs web service. I don't know the details, but it's in the MS book online.|||

Thanks dr_99 , I will look into it

regards Sara

How can I print a multi-value parameter

I have a multi-value parameter and I need to print its value in report.
But if I put in a text box =Parameters!Mine.Label when rendering I obtain #Error
How can I do it?
Thanks in advanceUse the .NET Framework function Join(). You can specify the delimiter as well.

How can I print a field that is in the dataset on each page and show the table hearder on each p

How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."

How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.

Thanks.

DanYeung

Hello Dan,

For the table header, right click on the row and go to properties. Change the RepeatOnNewPage property to True.

For the data field, you can add a textbox in your report header and reference the field by the textbox name that it is located in. Like this:

=First(ReportItems!TextBox9.Value)

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your help. I took care the table header.

For the data field, I added a textbox in the body and the value is =Fields!vchrProjectName.Value . I made the textbox invisible. I also added a textbox in the page header and the value is =First(ReportItems!TextBox9.Value) as you suggested. It printed on the first page only. I think the textbox in the body has no value on the second page, so the textbox in the page header got noghting. What did I missed? Do I have put something in the RepeatWith property?

Thanks.

DanYeung

|||

I fixed the page header problem by adding one more table header row before the table header. Added a textbox on the row and used =Fields!ProjectName.value in the value property. Thanks for the idea.

DanYeung

|||

I have the same issue and I tried your suggestion by adding the text box in the body and reference that in the page header. Only the first page work, not others. Any other suggestions or I did something wrong?

Regards

Eric

|||

You have to add a table in the body. Select the table header row. Change the RepeatOnNewPage property to true. Change the table header background color to while. Add a textbox on the table header and add the field in the textbox. Adding the textbox in the body prints only once. You need the table.

DanYeung

|||

Forgive me, I don't understant the second last statement. "Adding the textbox in the body prints only once". Can you explain a little bit more? Also, why to change background color to white?

Best Regards,

Eric

|||

Adding a textbox directly in the body won't print on the second page. If you want to repeat on every page, you have to add a table. The default background color for table header is grey. You don't have to change to white if you prefer grey.

DanYeung

|||

Hey I couldn't find an easy fix so what I would up doing was creating a report parameter set it as internal so it did not prompt when the report was being created and then for available and default values i selected it as a "from query" and pulled the value from there (with a linked dataset). Works like a charm for me in preview and exporting to PDFs. Here is the XML view of the report I created pulling fields, EmpName, MgrName and EmpPosition as EmpName, MgrName, and Position. The name of my dataset is dsGoals.

Code Snippet

<ReportParameter Name="EmpName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="MgrName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="Position">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

How can I print a field that is in the dataset on each page and show the table hearder on each p

How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."

How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.

Thanks.

DanYeung

Hello Dan,

For the table header, right click on the row and go to properties. Change the RepeatOnNewPage property to True.

For the data field, you can add a textbox in your report header and reference the field by the textbox name that it is located in. Like this:

=First(ReportItems!TextBox9.Value)

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your help. I took care the table header.

For the data field, I added a textbox in the body and the value is =Fields!vchrProjectName.Value . I made the textbox invisible. I also added a textbox in the page header and the value is =First(ReportItems!TextBox9.Value) as you suggested. It printed on the first page only. I think the textbox in the body has no value on the second page, so the textbox in the page header got noghting. What did I missed? Do I have put something in the RepeatWith property?

Thanks.

DanYeung

|||

I fixed the page header problem by adding one more table header row before the table header. Added a textbox on the row and used =Fields!ProjectName.value in the value property. Thanks for the idea.

DanYeung

|||

I have the same issue and I tried your suggestion by adding the text box in the body and reference that in the page header. Only the first page work, not others. Any other suggestions or I did something wrong?

Regards

Eric

|||

You have to add a table in the body. Select the table header row. Change the RepeatOnNewPage property to true. Change the table header background color to while. Add a textbox on the table header and add the field in the textbox. Adding the textbox in the body prints only once. You need the table.

DanYeung

|||

Forgive me, I don't understant the second last statement. "Adding the textbox in the body prints only once". Can you explain a little bit more? Also, why to change background color to white?

Best Regards,

Eric

|||

Adding a textbox directly in the body won't print on the second page. If you want to repeat on every page, you have to add a table. The default background color for table header is grey. You don't have to change to white if you prefer grey.

DanYeung

|||

Hey I couldn't find an easy fix so what I would up doing was creating a report parameter set it as internal so it did not prompt when the report was being created and then for available and default values i selected it as a "from query" and pulled the value from there (with a linked dataset). Works like a charm for me in preview and exporting to PDFs. Here is the XML view of the report I created pulling fields, EmpName, MgrName and EmpPosition as EmpName, MgrName, and Position. The name of my dataset is dsGoals.

Code Snippet

<ReportParameter Name="EmpName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="MgrName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="Position">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

how can I post back a statement from a store procedure to the .aspx page

Hi all,
Anyone can show me how can I catch the 'Print' statement that I have defined in my store procedure using SQL server 2000 DB on the .aspx page? ( I am using ASP.NET 1.0)
My store procedure as follow:
CREATE PROC NewAcctType
(@.acctType VARCHAR(20))
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
PRINT 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
PRINT 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
Thanks for all your replies

The best thing to do would be to either create another parameter and set its type to output or return the statement as a select.
@.message varchar(100) output
set @.message = 'The account type is already exists'
or
select 'The account type is already exist' as message

Nick

|||Hi Nick,
As you stated:
@.message varchar(100) output
set @.message = 'The account type is already exists'
Do I put a return statement like "Return @.message"?
How about if there is no errror in the procedure, do I still need to return any value to the .aspx page?
Thanks.|||Hi Nick,
when I executed my store procedure in SQL 2000 server, I got an error said"Cannot use the OUTPUT option in a DECLARE statement"
But without the Declare keyword, I got an incorrect syntax error, so how can I solve this problem? Is that necessary to put the "output" keyword at the end of the declare varaible statement?
Thanks|||syntax is:
create procedure whateverName
@.message varchar(100) output
as
set @.message = ''
if @.@.ERROR
set @.message = 'your text here.'
If you have no error, the top set statement will allow a blank to be passed back.
Nick|||

Nick,
Here is my syntax:
CREATE PROC DeleteCust
(@.SSN VARCHAR(12), @.message VARCHAR(40) output)
AS
BEGIN

--checks if the SSN is already exist
IF NOT EXISTS (SELECT * FROM Customer WHERE SSN = @.SSN)
BEGIN
SET @.message = 'The SSN is not exist!'
RETURN
END
BEGIN TRANSACTION
DELETE FROM Customer WHERE SSN = @.SSN

--if there is an error on the delete, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Delete failure on Customer table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END

I executed this proc as:
declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
Given that SSN is invalid, I suppose got the message 'The SSN is not exist!", however, I didn't get that message from the execution instead the system message showed "The command(s) completed successfully.", so anywhere I was wrong with the above SP or the execute statement?
Appreciated your reply

|||

If your just looking for the value after a run in QA, add:

declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
select @.message
Nick

|||

Thanks nick. I got the message when I run the Store Procedure in SQL server. However, how can I get the error message when I called the Store Procedure on my .aspx page?
I have these codes on my page: ('DeleteCust' is my store procedure name, 'SSN.Text' is the value from the input box)
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));

var myCommand : SqlDataAdapter = new SqlDataAdapter("DeleteCust", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.SSN", SqlDbType.VarChar, 12)).Value = SSN.Text;
Then, what should I put it here to catch the @.message value? The @.message is VARCHAR, and I have a Return keyword within my procedure, and the return value type is INT?
ping

|||

Add (Code is in VB):

dim parm as new sqlParameter("@.message", sqlDBtype.varchar, 100)

parm.direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parm)
After you do your call to stored proc:
strMessage(Or whatever variable you are adding to) = myCommand.SelectCommand.Parameters(1).Value
Nick

|||Hi Nick,
Could u explain more detail for these statements coz I am new to doing asp, I want to know it more about the meaning of those codes.
parm.direction = ParameterDirection.Output
strMessage(what variable should I add it here? could u give me an example? are u talking about @.message?)
when do I use strMessage() ?
Many thanks.

|||

Here is a quick and dirty article on output parms.

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

Nick

How can I get the result of a SQL PRINT Statement

I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.

I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.

I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.If you have the SQL Client tools then you can use Profiler to catch the statement that is sent as D-SQL. This is the best way to extract the D-SQLs that are getting executed rather than the Print statement ...

You can on the contrary use a Select and pass this D-SQL statement and catch it in your recordset returned ...

These are some of the options I can think of ...|||Where would I get the SQL Client tools?|||The simplest answer might be to create a DebugLog table and insert your dynamic SQL statement into it.

First, create the table to log your SQL statement:

CREATE TABLE
DebugLog
(
SQLStatement varchar(8000),
AddDate datetime DEFAULT GETDATE()
)

Next, add code to your stored procedure to insert your statement into the log file:

DECLARE @.SQLStatement Varchar(8000)
SET @.SQLStatement = 'SELECT * FROM test'
INSERT INTO DebugLog (SQLStatement) VALUES (@.SQLStatement)
EXEC(@.SQLStatement)

Next, take a look at the SQL statements that were executed (the most recent statement will be on top):

SELECT * FROM DebugLog ORDER BY AddDate DESC

Alternately, you could create an OUTPUT parameter in your sproc and pass the value of @.SQLStatement back to it and display it on your ASP.NET page.

Terri|||Thanks for the help. I'll try that. In point of fact the dynamic sql I'm trying to debug is using sp_executesql with an output parameter. I am trying to retrieve the recordset count based on the criteria parameters I'm passing to it from my asp.net application. In the application, the value returned is zero, which I know cannot be correct. Once I verify the proper assembly of the query, I can concentrate on the syntax of sp_executesql.|||Hi Terri,

When I first saw this post, I was going to respond that PRINT only writes to Query Analyzer. But upon researching it, I found that BOL has this to say about it in the Using PRINT topic:

"The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement."

This suggests that one could capture the error and do something with the PRINT text. But there can be multiple PRINT statements in a sproc, so I assume this would be an SqlException with nested exceptions.

Anyone ever done anything with this? Could be an interesting thing to explore.

Don|||yes, in my data access layer the error handling is like so.

try
{
oCmdExecute.ExecuteNonQuery(); // or ExecuteReader etc...
}
catch(SqlException ex)
{
foreach(SqlError err in ex.Errors)
{
strErrorString = strErrorString + "SqlError: #" + err.Number.ToString () + "\n" + err.Message;
}
strErrorString = strErrorString + "\n\nStored proc: " + sSql + "\n";
}

the SqlError collection will contain all the print statements, of course this only when the stored procedure fails, so you would have to call RAISERROR(' test error ', 16, 1) to force it into the exception block.

Note: that is RAISERROR not RAISEERROR go figure?|||Cool.

Note: that is RAISERROR not RAISEERROR go figure?

Yeah, that's been something odd since the statement was first added to T-SQL.

Don