Changing Results View in Query Analyzer

A w ago, I had to change my view to 'Results in Text' in SQL Query
Analyzer. All I did was choose 'Results in Text' from the menu and ran my
query. Now, whenever I open SQLQA, it always puts the results in text,
instead of results in grid (which is what I want). I've looked in the
options window, but see nothing. I've also tried to set the proper option
and save the query I'm working on, but to no avail. Does anybody know how
to get Results in Grid back to the default view option?
Thanks,
BradTools | Options | Results tab | Default results target
These settings apply to the current connection and future connections.
The last connection window closed determines the default when Q/A is
next opened.
To make sure you have the desired default settings, close all session
windows first, then set the options before closing Q/A.
footyplayer wrote:
> A w ago, I had to change my view to 'Results in Text' in SQL Query
> Analyzer. All I did was choose 'Results in Text' from the menu and ran my
> query. Now, whenever I open SQLQA, it always puts the results in text,
> instead of results in grid (which is what I want). I've looked in the
> options window, but see nothing. I've also tried to set the proper option
> and save the query I'm working on, but to no avail. Does anybody know how
> to get Results in Grid back to the default view option?
> Thanks,
> Brad
>|||Hi
Under the tools/options/results menus and tables there is a default results
option which you can choose from a combo.
John
"footyplayer" <anonymous@.discussions.microsoft.com> wrote in message
news:u1WM3G$sFHA.3252@.TK2MSFTNGP10.phx.gbl...
>A w ago, I had to change my view to 'Results in Text' in SQL Query
>Analyzer. All I did was choose 'Results in Text' from the menu and ran my
>query. Now, whenever I open SQLQA, it always puts the results in text,
>instead of results in grid (which is what I want). I've looked in the
>options window, but see nothing. I've also tried to set the proper option
>and save the query I'm working on, but to no avail. Does anybody know how
>to get Results in Grid back to the default view option?
> Thanks,
> Brad
>|||Thanks Trey!
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:esp6wZ$sFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Tools | Options | Results tab | Default results target
> These settings apply to the current connection and future connections. The
> last connection window closed determines the default when Q/A is next
> opened.
> To make sure you have the desired default settings, close all session
> windows first, then set the options before closing Q/A.
> footyplayer wrote:

Changing Database within a stored procedure

I need to create a stored procedure in the master database that can
access info to dynamically create a view in another database. It
doesn't seem like it should be very hard, but I can't get it to work.
Here's an example of what I want to do.

CREATE PROCEDURE create_view @.dbname sysname
AS
BEGIN
DECLARE @.query varchar(1000)
SELECT @.query = 'use ' + @.dbname + ' go CREATE VIEW ......'
EXEC(@.query)
END

In this case, I get an error with the word "go". Without it, I get a
"CREATE VIEW must be the first statement in a batch" error. I tried a
semicolon in place of "GO" but that didn't help either.

Thanks"Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0404122312.e0cf00f@.posting.google.co m...
> I need to create a stored procedure in the master database that can
> access info to dynamically create a view in another database. It
> doesn't seem like it should be very hard, but I can't get it to work.
> Here's an example of what I want to do.
> CREATE PROCEDURE create_view @.dbname sysname
> AS
> BEGIN
> DECLARE @.query varchar(1000)
> SELECT @.query = 'use ' + @.dbname + ' go CREATE VIEW ......'
> EXEC(@.query)
> END
> In this case, I get an error with the word "go". Without it, I get a
> "CREATE VIEW must be the first statement in a batch" error. I tried a
> semicolon in place of "GO" but that didn't help either.
> Thanks

It would probably be easier to do this from a client-side script - it's easy
to pass the database name to osql.exe, for example. In addition, you may
want to rethink your approach slightly, as it would be better to implement a
controlled deployment process for your code, so you can take a view script
from your source control system and create it in any database you want. See
this link also:

http://www.sommarskog.se/dynamic_sql.html#Dyn_DB

But if you really need to do it in TSQL, then this is one way:

CREATE PROCEDURE create_view @.dbname sysname
AS
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + dbname + ' -Q "CREATE VIEW..."'
exec master..xp_cmdshell @.cmd, NO_OUTPUT
END

Simon|||"Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0404122312.e0cf00f@.posting.google.co m...
> I need to create a stored procedure in the master database that can
> access info to dynamically create a view in another database. It
> doesn't seem like it should be very hard, but I can't get it to work.
> Here's an example of what I want to do.
> CREATE PROCEDURE create_view @.dbname sysname
> AS
> BEGIN
> DECLARE @.query varchar(1000)
> SELECT @.query = 'use ' + @.dbname + ' go CREATE VIEW ......'
> EXEC(@.query)
> END
> In this case, I get an error with the word "go". Without it, I get a
> "CREATE VIEW must be the first statement in a batch" error. I tried a
> semicolon in place of "GO" but that didn't help either.
> Thanks

It would probably be easier to do this from a client-side script - it's easy
to pass the database name to osql.exe, for example. In addition, you may
want to rethink your approach slightly, as it would be better to implement a
controlled deployment process for your code, so you can take a view script
from your source control system and create it in any database you want. See
this link also:

http://www.sommarskog.se/dynamic_sql.html#Dyn_DB

But if you really need to do it in TSQL, then this is one way:

CREATE PROCEDURE create_view @.dbname sysname
AS
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + dbname + ' -Q "CREATE VIEW..."'
exec master..xp_cmdshell @.cmd, NO_OUTPUT
END

Simon|||Hi Simon,

Thanks for the help. This stored procedure is installed by customers,
so I have no idea what databases they have or what databases they will
want to create this view on. I will give your solution a try.

Thanks,
Bruce|||Hi Simon,

Thanks for the help. This stored procedure is installed by customers,
so I have no idea what databases they have or what databases they will
want to create this view on. I will give your solution a try.

Thanks,
Bruce

changing database owner to access diagrams

hello,

i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.

i am using sql server 2005 express edition with visual web developer.

thanks!

Hi,

you can achieve this by sp_changedbowner store procedure refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm in SQL BOL.

Hemantgiri S. Goswami

|||

thank you for the response. unfortunately, i am new to sql and do not know where or how to use this command. do i use VWD? Sql Server Management Studio Express does not allow me to navigate to the directory where the database resides. also, one additional piece of information: only the machine name changed.

old name: "old_machine_name/steve"

new name: "new_machine_name/steve"

which EXEC command should I use to change ownership of the database to the new name?

EXEC sp_changedbowner 'new_machine_name/steve'

or simply,

EXEC sp_changedbowner 'steve'

i am concerned that there is a larger problem with permissions and sql. anytime I add a new website and new database in VWD, the default "owner" field comes up with the old name, "old_machine_name/steve". how do I change this default so that all future databases and web sites have the new owner, "new_machine_name/steve"?

thanks!

|||

Hi,

When you rename your Server name it will reflect to your SQL Server too, your sql server's name also change refer http://blog.opsan.com/archive/2005/05/10/465.aspx for more. If you wants to change the DB Owner Open

SQL Server Management Studio -> connect -> Click on New Query -> run exec sp_changedbowner <schema> Refer http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx and http://weblogs.asp.net/eporter/archive/2004/10/29/249627.aspx for more on DB owner change.

Hemantgiri S. Goswami

|||

ok i tried using Sql Server Management Studio Express but was unable to navigate into the "C:\Documents and Settings\Steve\My Documents\My Web Sites\WebSite1\AppData" user directory. even though Steve is a user with admin rights, the Add database window would not open any directories below the Steve directory. therefore, i was unable to even see the mdf file to open.

i tried copying the complete web site to a c:\temp directory and was able to navigate to the AppData directory and open the database. when I ran the sql command in a query window:

EXEC sp_changedbowner 'Steve'

i received the error:

"Cannot find the principal 'Steve', because it does not exist or you do not have permission."

I received the same error when using this command: EXEC sp_changedbowner 'new_machine_name/Steve'

i did try renaming the computer name back to the old_machine_name, rebooted, and was successfully able to open the Diagrams directory of the database in the user Steve directory. Although I find this very strange, i do not know why it fails when I simply change the computer name to new_machine_name. do you have any insight to this? it still seems that there is a default setting using the old_machine_name/Steve stored somewhere in VWD or SQL Server 2005 Express.

thanks!

|||

Hi,

That is because of Schema refer Schema in BOL and below articles http://www.databasejournal.com/features/mssql/article.php/3481751 and http://www.sswug.org/see/SQL_Server_2005_-_Schema_Definition-19357

Hemantgiri S. Goswami

|||

i finally gave up and called Microsoft. the root cause was not found, but the fix was to create a new user with admin rights, then move all the files over to the new user account. the new machine name is now recognized and i am able to access the diagrams folder for the database. i hope this helps others with similar user account issues.

thanks for your help!

changing database owner to access diagrams

hello,

i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.

i am using sql server 2005 express edition with visual web developer.

thanks!

Hi,

you can achieve this by sp_changedbowner store procedure refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm in SQL BOL.

Hemantgiri S. Goswami

|||

thank you for the response. unfortunately, i am new to sql and do not know where or how to use this command. do i use VWD? Sql Server Management Studio Express does not allow me to navigate to the directory where the database resides. also, one additional piece of information: only the machine name changed.

old name: "old_machine_name/steve"

new name: "new_machine_name/steve"

which EXEC command should I use to change ownership of the database to the new name?

EXEC sp_changedbowner 'new_machine_name/steve'

or simply,

EXEC sp_changedbowner 'steve'

i am concerned that there is a larger problem with permissions and sql. anytime I add a new website and new database in VWD, the default "owner" field comes up with the old name, "old_machine_name/steve". how do I change this default so that all future databases and web sites have the new owner, "new_machine_name/steve"?

thanks!

|||

Hi,

When you rename your Server name it will reflect to your SQL Server too, your sql server's name also change refer http://blog.opsan.com/archive/2005/05/10/465.aspx for more. If you wants to change the DB Owner Open

SQL Server Management Studio -> connect -> Click on New Query -> run exec sp_changedbowner <schema> Refer http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx and http://weblogs.asp.net/eporter/archive/2004/10/29/249627.aspx for more on DB owner change.

Hemantgiri S. Goswami

|||

ok i tried using Sql Server Management Studio Express but was unable to navigate into the "C:\Documents and Settings\Steve\My Documents\My Web Sites\WebSite1\AppData" user directory. even though Steve is a user with admin rights, the Add database window would not open any directories below the Steve directory. therefore, i was unable to even see the mdf file to open.

i tried copying the complete web site to a c:\temp directory and was able to navigate to the AppData directory and open the database. when I ran the sql command in a query window:

EXEC sp_changedbowner 'Steve'

i received the error:

"Cannot find the principal 'Steve', because it does not exist or you do not have permission."

I received the same error when using this command: EXEC sp_changedbowner 'new_machine_name/Steve'

i did try renaming the computer name back to the old_machine_name, rebooted, and was successfully able to open the Diagrams directory of the database in the user Steve directory. Although I find this very strange, i do not know why it fails when I simply change the computer name to new_machine_name. do you have any insight to this? it still seems that there is a default setting using the old_machine_name/Steve stored somewhere in VWD or SQL Server 2005 Express.

thanks!

|||

Hi,

That is because of Schema refer Schema in BOL and below articles http://www.databasejournal.com/features/mssql/article.php/3481751 and http://www.sswug.org/see/SQL_Server_2005_-_Schema_Definition-19357

Hemantgiri S. Goswami

|||

i finally gave up and called Microsoft. the root cause was not found, but the fix was to create a new user with admin rights, then move all the files over to the new user account. the new machine name is now recognized and i am able to access the diagrams folder for the database. i hope this helps others with similar user account issues.

thanks for your help!