Saving query plan

I would like to save a query plan (estimated or actual)
created in Query Analyzer -- paste it into a document,
or simply print. It doesn't seem to be possible to
select and copy the Execution Plan window, and printing
it creates microscopic gibberish which is a waste of
paper. Is it possible to do this?

Set showplan_text is of limited help for the SP I'm
looking at -- while analyzing the SP, it reads ahead
and complains that a temp table created inside the SP
doesn't exist (yet) and exits. Using Ctrl-K to capture
the query plan allows the SP to complete, but saving the
plan is the problem.

Thanks,
Jim Geissman(jim_geissman@.countrywide.com) writes:
> I would like to save a query plan (estimated or actual)
> created in Query Analyzer -- paste it into a document,
> or simply print. It doesn't seem to be possible to
> select and copy the Execution Plan window, and printing
> it creates microscopic gibberish which is a waste of
> paper. Is it possible to do this?
> Set showplan_text is of limited help for the SP I'm
> looking at -- while analyzing the SP, it reads ahead
> and complains that a temp table created inside the SP
> doesn't exist (yet) and exits. Using Ctrl-K to capture
> the query plan allows the SP to complete, but saving the
> plan is the problem.

SET STATISTICS PROFILE ON is what you are looking for.

You can also use Profiler, and filter for the event
Performance:Execution Plan.

In SQL 2005 you can actually save the graphical plan (in XML format)
from Managment Studio, the replacement for Query Analyzer and Enterprise
Manager.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland.

Jim

Saving a stored procedure or a user defined function in SQL 2000

I can create stored procedures and user defined functions in the Query
Analyzer, but the only option I see for saving are to save to an external
sql file. Is there a way to save directly to the database while in Query
Analyzer?
BillThey are normally saved by default. If you start your stored procedure, for
example, with CREATE PROCEDURE myProcname etc. and you execute that query
you have just created (or saved sort of) that myProcname Stored Procedure
into your database.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46575c07$0$16706$4c368faf@.roadrunner.com...
>I can create stored procedures and user defined functions in the Query
>Analyzer, but the only option I see for saving are to save to an external
>sql file. Is there a way to save directly to the database while in Query
>Analyzer?
> Bill
>|||To clarify. Not "by default" you have to actually execute a query to save
it. This query is the CREATE syntax for creating the object. When you
execute that it is being saved. To modify it you would then ALTER it. If you
go to books online and search for CREATE PROCEDURE you will see more on
that.
HTH
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
> They are normally saved by default. If you start your stored procedure,
> for example, with CREATE PROCEDURE myProcname etc. and you execute that
> query you have just created (or saved sort of) that myProcname Stored
> Procedure into your database.
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an external
>>sql file. Is there a way to save directly to the database while in Query
>>Analyzer?
>> Bill
>|||Mike,
I tried this for a stored procedure and it did save it to my database. But
I can't get it to work for a user defined function. In Query Analyzer the
user defined function parses with no errors, but it won't create in the
database. Any thoughts?
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
> To clarify. Not "by default" you have to actually execute a query to save
> it. This query is the CREATE syntax for creating the object. When you
> execute that it is being saved. To modify it you would then ALTER it. If
> you go to books online and search for CREATE PROCEDURE you will see more
> on that.
> HTH
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an external
>>sql file. Is there a way to save directly to the database while in Query
>>Analyzer?
>> Bill
>>
>|||> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
What error message are you getting when you execute your CREATE FUNCTION
statement?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to save
>> it. This query is the CREATE syntax for creating the object. When you
>> execute that it is being saved. To modify it you would then ALTER it. If
>> you go to books online and search for CREATE PROCEDURE you will see more
>> on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>|||Hi Bill
So when you execute the code that says CREATE FUNCTION ...
what happens? If you don't get any error, the function should be saved in
your database.
What makes you think it isn't created?
Perhaps you are looking for it in the Object Explorer and you didn't
refresh?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to save
>> it. This query is the CREATE syntax for creating the object. When you
>> execute that it is being saved. To modify it you would then ALTER it. If
>> you go to books online and search for CREATE PROCEDURE you will see more
>> on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>|||Mike,
It appears the function did save into the database, although it's not
showing up under User Defined Functions. It is showing up when I view
permissions under Users for user dbo, and an error message is popping up
there as follows:
"Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not found
in the UserDefinedFunctions collection. If the name is a qualified name,
use [] to separate various parts of the name, and try again."
I can't change the name or delete the function because I can't see it in the
database. Is there a way to repair this?
Bill
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to save
>> it. This query is the CREATE syntax for creating the object. When you
>> execute that it is being saved. To modify it you would then ALTER it. If
>> you go to books online and search for CREATE PROCEDURE you will see more
>> on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>|||Have you refreshed the list of User Defined Functions as Kalen suggested? If
it is showing up as an object that you can assign permissions to it is in
your database.
When are you getting that error? When trying to assign permissions? Try and
close Enterprise Manager and reopen it.
Can you call the function and use it?
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46577035$0$4642$4c368faf@.roadrunner.com...
> Mike,
> It appears the function did save into the database, although it's not
> showing up under User Defined Functions. It is showing up when I view
> permissions under Users for user dbo, and an error message is popping up
> there as follows:
> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
> found in the UserDefinedFunctions collection. If the name is a qualified
> name, use [] to separate various parts of the name, and try again."
> I can't change the name or delete the function because I can't see it in
> the database. Is there a way to repair this?
> Bill
>
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46576681$0$4901$4c368faf@.roadrunner.com...
>> Mike,
>> I tried this for a stored procedure and it did save it to my database.
>> But I can't get it to work for a user defined function. In Query
>> Analyzer the user defined function parses with no errors, but it won't
>> create in the database. Any thoughts?
>> Bill
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to
>> save it. This query is the CREATE syntax for creating the object. When
>> you execute that it is being saved. To modify it you would then ALTER
>> it. If you go to books online and search for CREATE PROCEDURE you will
>> see more on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>>
>|||I did the refresh on User Defined Functions and the function is now in the
list. Before, I was getting the error message when clicking on Permissions
for the dbo user who owns the function. This went away after the refresh.
Thanks to all for your help.
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Have you refreshed the list of User Defined Functions as Kalen suggested?
> If it is showing up as an object that you can assign permissions to it is
> in your database.
> When are you getting that error? When trying to assign permissions? Try
> and close Enterprise Manager and reopen it.
> Can you call the function and use it?
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46577035$0$4642$4c368faf@.roadrunner.com...
>> Mike,
>> It appears the function did save into the database, although it's not
>> showing up under User Defined Functions. It is showing up when I view
>> permissions under Users for user dbo, and an error message is popping up
>> there as follows:
>> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
>> found in the UserDefinedFunctions collection. If the name is a qualified
>> name, use [] to separate various parts of the name, and try again."
>> I can't change the name or delete the function because I can't see it in
>> the database. Is there a way to repair this?
>> Bill
>>
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46576681$0$4901$4c368faf@.roadrunner.com...
>> Mike,
>> I tried this for a stored procedure and it did save it to my database.
>> But I can't get it to work for a user defined function. In Query
>> Analyzer the user defined function parses with no errors, but it won't
>> create in the database. Any thoughts?
>> Bill
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to
>> save it. This query is the CREATE syntax for creating the object. When
>> you execute that it is being saved. To modify it you would then ALTER
>> it. If you go to books online and search for CREATE PROCEDURE you will
>> see more on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored
>> procedure, for example, with CREATE PROCEDURE myProcname etc. and you
>> execute that query you have just created (or saved sort of) that
>> myProcname Stored Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>>
>>
>|||Glad to help have a great weekend.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46578928$0$15089$4c368faf@.roadrunner.com...
>I did the refresh on User Defined Functions and the function is now in the
>list. Before, I was getting the error message when clicking on Permissions
>for the dbo user who owns the function. This went away after the refresh.
> Thanks to all for your help.
> Bill
>
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Have you refreshed the list of User Defined Functions as Kalen suggested?
>> If it is showing up as an object that you can assign permissions to it is
>> in your database.
>> When are you getting that error? When trying to assign permissions? Try
>> and close Enterprise Manager and reopen it.
>> Can you call the function and use it?
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46577035$0$4642$4c368faf@.roadrunner.com...
>> Mike,
>> It appears the function did save into the database, although it's not
>> showing up under User Defined Functions. It is showing up when I view
>> permissions under Users for user dbo, and an error message is popping up
>> there as follows:
>> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
>> found in the UserDefinedFunctions collection. If the name is a
>> qualified name, use [] to separate various parts of the name, and try
>> again."
>> I can't change the name or delete the function because I can't see it in
>> the database. Is there a way to repair this?
>> Bill
>>
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46576681$0$4901$4c368faf@.roadrunner.com...
>> Mike,
>> I tried this for a stored procedure and it did save it to my database.
>> But I can't get it to work for a user defined function. In Query
>> Analyzer the user defined function parses with no errors, but it won't
>> create in the database. Any thoughts?
>> Bill
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to
>> save it. This query is the CREATE syntax for creating the object. When
>> you execute that it is being saved. To modify it you would then ALTER
>> it. If you go to books online and search for CREATE PROCEDURE you will
>> see more on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored
>> procedure, for example, with CREATE PROCEDURE myProcname etc. and you
>> execute that query you have just created (or saved sort of) that
>> myProcname Stored Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>>I can create stored procedures and user defined functions in the
>>>Query Analyzer, but the only option I see for saving are to save to
>>>an external sql file. Is there a way to save directly to the
>>>database while in Query Analyzer?
>>>
>>> Bill
>>>
>>
>>
>>
>>
>>
>sql

Saving a query to a csv file

Let's say I have a query such as

SELECT * FROM WORKORDER

I want to save the query to a .csv file which I understand I can set up in the query analyzer iptions. However, I want to be able to overqrite any existing data that is in the file and I don't want to be prompted about saving the file. I need to do this because this will be part of a job that runs this query and then I need to e-mail the results to another user. This job will run around midnight when no one is in the office and the .csv file should be available the first thing the next morning for use. Is there a way to do this by adding to the code abave?You can use bcp with a format file or command-line option to do this easily. Optionally, you can also use OSQL with command-line options but it is slightly tedious to get the correct output since you have to tweak lot of parameters. Lastly, you can actually use ISQLW itself from command-line. Use bcp since that gives more flexibility.

save an image into a table using Query Analyzer

I have a table with two fields Part_num and Pic in SQL server 2000
Pic is of Image type. Is there a way I can save images for each part_num using Query analyzer?You can use BULK INSERT or bcp.

From BOL:

xp_cmdshell 'bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservername'

--------------------
The Bcp.fmt file:

8.0
1
1 SQLIMAGE 0 5578 "" 1 c1

Using the BULK INSERT statement to bulk copy the Test.doc data file into the bitmap table in the pubs database, execute from a query tool, such as SQL Query Analyzer:

BULK INSERT pubs..bitmap FROM 'c:\test.doc'
WITH (
FORMATFILE = 'c:\Bcp.fmt'
)|||Sorry I'm new to this, Will this work if I have an image, lets say 150.bmp on C: drive? So would it be
BULK INSERT pubs..bitmap FROM 'c:\150.bmp'
WITH (
FORMATFILE = 'c:\150.bmp'
) ?

Originally posted by snail
You can use BULK INSERT or bcp.

From BOL:

xp_cmdshell 'bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservername'

--------------------
The Bcp.fmt file:

8.0
1
1 SQLIMAGE 0 5578 "" 1 c1

Using the BULK INSERT statement to bulk copy the Test.doc data file into the bitmap table in the pubs database, execute from a query tool, such as SQL Query Analyzer:

BULK INSERT pubs..bitmap FROM 'c:\test.doc'
WITH (
FORMATFILE = 'c:\Bcp.fmt'
)|||Check this (I tested it):

BULK INSERT pictures FROM 'c:\tmp\bridge.jpg'
WITH (
FORMATFILE = 'c:\tmp\bcp.fmt'
)

bcp.fmt
3935 - size of image, you have to change collation if it needs.

See attachment.