Showing posts with label analyzer. Show all posts
Showing posts with label analyzer. Show all posts

Friday, March 23, 2012

How to get the full contents of the text column in syscomments

HI All ,

I need to extract the full text of a procedure from the syscomments table. When I run the query in query Analyzer and do a 'save as' to a textfile, I get only part of the text.

Appreciate any response on this.

Thanks

Two things, the first one is that the definition, column [text], is nvarchar(4000), so the output in QA should be a least 4000 characters. You can set it changing "Tools - Options... - Results - Maximum characters per column:" to 8192 (8k).

Second, if the definition, for example of the stored procedure, is greater than 4000, then you will have to select from multiple rows. The value of the column [colid] give you the sequence.

AMB

|||Thanks a lot ,it worked.sql

Monday, March 19, 2012

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
Ashish
You can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish
|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:

> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

Friday, March 9, 2012

How to get Recordset count

I'm currently using SQL 2005 but in previous versions I remember that within Query Analyzer there was a recordset count that displayed in the lower right hand corner. Currently within 2005 I can't find a way to do this without doing it manually which I can't do now since the stored procedure I'm testing has almost 100 recordsets. Any help would be greatly appricated.

Jerry:

That feature is still available; check and see if you are displaying the "status bar" (I think). In the meantime I'll do some more checking about that feature.

Kent

|||

In SSMS, it should be visible in the status bar of the Results window.

Welcome as a new user to SQL Server Jerry. Hopefully, you will be able to get the help you need from these forums, and as your skills progress, please don't forget to drop in occasionally and help others. (You will ALWAYS know something someone else can benefit from...)

It's not a 'recordset' -the output from a SQL query is referred to as a 'resultset'.

|||

My mistake on the 'recordset' comment. I know it's a resultset.

I've been using SQL for a bit now but the result set count still doesn't show up. I have all the bars visible. I see "Ln", "Col", "Ch" & I see the number of rows returned and server i'm running against under the resultset window.

|||

I think that perhaps you are looking to get the message that "n row(s) affected" that may follow an activity. (It sounds like perhaps you are seeing the "n rows" in the status bar.)

To get the "n row(s) affected" message, use this command:

SET NOCOUNT OFF

To stop getting the "n row(s) affected" message, use this command:

SET NOCOUNT ON

|||I thought in previous versions of SQL it actually said how man result sets were coming back. Not the number of rows but the number of result sets. I need 97 result sets to come back with a series of stored procedures that are being executed. I don't want to go through and count every result set.|||

I'm not aware of any facility within T-SQL that reports the number of resultsets returned. One query = one resultset.

I think that may be available with some of the ADO objects, specifically the datareader object.

I supose you could add a counter variable in the procedure, and check each query for @.@.ROWCOUNT, incremented as desired, and then return a statement using that variable.

|||

I don't remember one either.

You could possibly return the data to the grid and then (assuming no errors or print statements) you could get a count from the

(1 row(s) affected)

messages in the messages pane..