Create a View Command in Query Analyzer

Hey...a simple question that I forgot...
May I know how to create a view under Query Analyzer...below is what i have
type, but command succesfully, but view not created...wonder why ?
CREATE VIEW dbo.VIEW1
AS
SELECT * FROM Table1If the command was executed successfull the view was created unless you
didnt turn on something like SET Parseonly etc. Did youdirectly Select from
the row after it was created ? "Select * from View1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>|||but I tried to go to Enterprise Manager, SQL Server, mydatabase, then under
the VIEWS there...
i click...the newly created View1 is not shown there, but I am able to
Select * from View1...
May I know how to make my View1 to be able to see from the enterprise
manager.
thank you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||The gui not always refreshs the right way, did you right click on the View
node and selected Refresh ? That should do it if you placed it in the right
database. Perhaps you issued the command in the wrong database ? Just proof
It via Select @.@.servername,DB_NAME().
If the view was created it should appear in the INFORMATION_SCHEMA:
Select * from INFORMATION_SCHEMA.Tables
Where TABLE_NAME LIKE 'View1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:OAKfHkPXFHA.2540@.tk2msftngp13.phx.gbl...
> but I tried to go to Enterprise Manager, SQL Server, mydatabase, then
> under the VIEWS there...
> i click...the newly created View1 is not shown there, but I am able to
> Select * from View1...
> May I know how to make my View1 to be able to see from the enterprise
> manager.
> thank you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||YES, Jens...I am done with that...
Just wonder how could I delete a view then ?
thanks you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have a suggestion here.
Do you have dbo right to view all objects?
Since you have created it using dbo, I am not sure whether you are using dbo
right to view the VIEW1.
another thing is, you can check BOL on how to drop a view.
Syntax
DROP VIEW { view } [ ,...n ]
thanks.
Leo Leong|||Drop View [Viewname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:%23PT391PXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> YES, Jens...I am done with that...
> Just wonder how could I delete a view then ?
> thanks you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||I think you were connected to a different database in Query Analyzer than
you think you were (the one you were looking in in Enterprise Manager). You
can use the Object Search (F4) in Query Analyzer to search for a view called
View1 in all your databases.
Jacco Schalkwijk
SQL Server MVP
" A_PK" <pk999@.hotmail.com> wrote in message
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>sql

create a .txt file from a table using sql script

Hi all,
Im new on SQL. I need to create a sql script to be run in Query Analyzer.
This script must create a table in .txt format on a server folder
(d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
I need to make it with many tables (about 30) and many times (every week).
Your time is really appreciate it.
Thanks
Ped_esc
Seems you are looking at generating DDL and/or data for that DDL. I have listed some options here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ped_Esc" <PedEsc@.discussions.microsoft.com> wrote in message
news:FB03A791-F776-4B57-B163-82F392CB0AF4@.microsoft.com...
> Hi all,
> Im new on SQL. I need to create a sql script to be run in Query Analyzer.
> This script must create a table in .txt format on a server folder
> (d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
> I need to make it with many tables (about 30) and many times (every week).
> Your time is really appreciate it.
> Thanks
> Ped_esc
>
sql

create a .txt file from a table using sql script

Hi all,
Im new on SQL. I need to create a sql script to be run in Query Analyzer.
This script must create a table in .txt format on a server folder
(d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
I need to make it with many tables (about 30) and many times (every week).
Your time is really appreciate it.
Thanks
Ped_escSeems you are looking at generating DDL and/or data for that DDL. I have listed some options here:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ped_Esc" <PedEsc@.discussions.microsoft.com> wrote in message
news:FB03A791-F776-4B57-B163-82F392CB0AF4@.microsoft.com...
> Hi all,
> Im new on SQL. I need to create a sql script to be run in Query Analyzer.
> This script must create a table in .txt format on a server folder
> (d:\backup\tablename.txt). I know I can make it using DTS Export Wizard, but
> I need to make it with many tables (about 30) and many times (every week).
> Your time is really appreciate it.
> Thanks
> Ped_esc
>

Cpu Usage High.

Hi,
can we check cpu usage of our server by query analyzer,I dont have direct ac
cess to server and simply i've registered it by entr manager.
Site is too slow..there are no locks no processesing..but still its moving l
ike dead..
i want to check by query analyzer..
1) cpu usage
2) buffer cache ratio
3) ram utilised and free on server.
regards
sunnyHi,
The easiest way is that, you can connect to Performace monitor remotely.
Open Performance monitor and click the ADD counter butter,
where you can mention the SQL server name (\\Servername). But you should
have required OS level previlages in the SQL Server machine.
Buffer hit ratio can verified using the below DBCC command
DBCC PERFMON
Note:
Incase if you found all the hardware resouces usage is below limit than
using profiler identify the TSQL/ Procedure causing
the bottle neck. After that try to tune using SQL Serevr Query execution
plan and Index tuning wizard.
Thanks
Hari
MCDBA
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:7EDA9B30-F184-40DD-8FEE-FE144D43D9B8@.microsoft.com...
> Hi,
> can we check cpu usage of our server by query analyzer,I dont have direct
access to server and simply i've registered it by entr manager.
> Site is too slow..there are no locks no processesing..but still its moving
like dead..
> i want to check by query analyzer..
> 1) cpu usage
> 2) buffer cache ratio
> 3) ram utilised and free on server.
> regards
> sunny
>|||can use sp_monitor to check cpu activity since sql was started. . .cpu_busy
column tells you how much time cpu has spent servicing sql server requests

Cpu Usage High.

Hi,
can we check cpu usage of our server by query analyzer,I dont have direct access to server and simply i've registered it by entr manager.
Site is too slow..there are no locks no processesing..but still its moving like dead..
i want to check by query analyzer..
1) cpu usage
2) buffer cache ratio
3) ram utilised and free on server.
regards
sunnyHi,
The easiest way is that, you can connect to Performace monitor remotely.
Open Performance monitor and click the ADD counter butter,
where you can mention the SQL server name (\\Servername). But you should
have required OS level previlages in the SQL Server machine.
Buffer hit ratio can verified using the below DBCC command
DBCC PERFMON
Note:
Incase if you found all the hardware resouces usage is below limit than
using profiler identify the TSQL/ Procedure causing
the bottle neck. After that try to tune using SQL Serevr Query execution
plan and Index tuning wizard.
Thanks
Hari
MCDBA
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:7EDA9B30-F184-40DD-8FEE-FE144D43D9B8@.microsoft.com...
> Hi,
> can we check cpu usage of our server by query analyzer,I dont have direct
access to server and simply i've registered it by entr manager.
> Site is too slow..there are no locks no processesing..but still its moving
like dead..
> i want to check by query analyzer..
> 1) cpu usage
> 2) buffer cache ratio
> 3) ram utilised and free on server.
> regards
> sunny
>|||can use sp_monitor to check cpu activity since sql was started. . .cpu_busy column tells you how much time cpu has spent servicing sql server requests

coupla sql questions

say i have this file (below) and want to enter it into a table i've made. why cant i do this in query analyzer? and whats the easiest/best way to do it

insert into myTable

values(1,"StaticHtml","StaticHtmlPageControl"
2,"Questions","QuestionsPageControl"
3,"Login","LoginPageControl"
4,"SubmitSurvey","SubmitSurveyPageControl"
5,"Registration","RegistrationPageControl"
7,"SubmitUserAttributesSurvey","SubmitUserAttributesSurveyControl"
10,"RepeatPageGroup","RepeatPage"
11,"ThankYouPage","ThankYouPageControl")

also

whats the easiest way to make a copy of a database with and without the data - on the same server

cheers

Your SQL syntax isn't quite right. You'll need to write an INSERT INTO for each row you want to insert. i.e.

INSERT INTO myTable VALUES(1,"StaticHtml","StaticHtmlPageControl")

INSERT INTO myTable VALUES(2,"Questions","QuestionsPageControl")

You can script the database structure in order to create a blank instance of your database - Enterprise Manager / SQL Management Studio has options for this. To "copy" your database including data, you should take a backup.

|||

Thx man

bit annoying how u have to write insert into for each row, especially if there's heaps of rows.. i was hoping there might have been a better/easier way

i was also hoping you could copy a database like a table eg: select * into db1 from db2 - or something similar

anyway, i'll just keep hoping

cheers

|||

If you're selecting data from one table into another, you can do multiple rows - like this

INSERT INTO tblTable1
SELECT * FROM tblTable2

If the fields don't match, just specify the columns:

INSERT INTO tblTable1 (Field1, Field2, Field3)
SELECT FieldA, FieldB, FieldC FROM tblTable2