Create table in schema with Enterprise Manager

How can I specify the schema I want the table to be created in with
Enterprise Manager 2005?
It justs asks for a table name and if you put a schema name in it just
treats it as part of the table name
Paul
Paul Hatcher (PaulHatcher@.discussions.microsoft.com) writes:
> How can I specify the schema I want the table to be created in with
> Enterprise Manager 2005?
> It justs asks for a table name and if you put a schema name in it just
> treats it as part of the table name
CREATE TABLE schemaname.tbl
Let the graphic tools be. They are some funny toys that have ended up in
the wrong place. And, whatever, never use them to change your tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

CREATE TABLE and column order

I create a table by sending a CREATE TABLE command to the database. The
create is successful but when I look at the table in Enterprise Manager
the order of the columns is in alphabetic order and not in the order I
specified when I issued the CREATE TABLE command. Have the columns
really been created in the order in which I see them under Enterprise
Manager ? If so, how do I enforce that the order of the columns is the
same as the order I specify when I created the table ?"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?
Edward,
Does it really matter what order the columns are in? On the data page
itself, the column are put in to an order that SQL Server specifies complete
with headers on each row, null and varchar bitmaps for the row data, and
then the actual row data. If you are using blob objects (text, ntext,
image) then they don't necessarily even live in the row itself, but have
16-byte pointers to other data pages.
To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensure
that you specify a column list in these statements.
Rick Sawtell
MCT, MCSD, MCDBA|||Edward
Are you sure?
I did small test
CREATE TABLE Test8
(
B INT,
A INT
)
Looking in EM I see the same order
Why the order of the columns is so important for you?
When you perform SELECT statement you can specify any order of columns.
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?|||You can create a view on the table specifying the order on your own.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> schrieb im
Newsbeitrag news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>I create a table by sending a CREATE TABLE command to the database. The
>create is successful but when I look at the table in Enterprise Manager the
>order of the columns is in alphabetic order and not in the order I
>specified when I issued the CREATE TABLE command. Have the columns really
>been created in the order in which I see them under Enterprise Manager ? If
>so, how do I enforce that the order of the columns is the same as the order
>I specify when I created the table ?|||Even though tables in relational databases do not have a "column order"
associated with them, SQL often associates positional significance to the
order of columns in a table. However except in a few circumstances, such
significance of the column order offer little or no benefits.
Not necessarily. To find the order of columns in t-SQL, you can query the
metadata and verify the ORDINAL_POSITION column like:
EXEC sp_columns tbl
Anith|||Rick Sawtell wrote:
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>
>
> Edward,
> Does it really matter what order the columns are in?
Very much so. Are you telling me that I can not ensure the column order
in SQL Server when I create a table ?

> On the data page
> itself, the column are put in to an order that SQL Server specifies comple
te
> with headers on each row, null and varchar bitmaps for the row data, and
> then the actual row data.
When you say "the data page", to what are you referring ?

> If you are using blob objects (text, ntext,
> image) then they don't necessarily even live in the row itself, but have
> 16-byte pointers to other data pages.
> To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensur
e
> that you specify a column list in these statements.
That is not the issue. I need to ensure the actual order of columns is
the same as what I specified when I created the table. Is this the case,
and Enterprise Manager is not showing me the actual column order ? Or is
it the case that SQL Server actually changes the column order from what
I specified when I created the table ? The latter would be terrible.|||Anith Sen wrote:
> Even though tables in relational databases do not have a "column order"
> associated with them, SQL often associates positional significance to the
> order of columns in a table. However except in a few circumstances, such
> significance of the column order offer little or no benefits.
>
>
> Not necessarily. To find the order of columns in t-SQL, you can query the
> metadata and verify the ORDINAL_POSITION column like:
> EXEC sp_columns tbl
>
Sorry, this does work but the order of columns is not what I specified
when I created the table. SQL Server has moved the order of columns.
This is really horrible. There must be some way to ensure that the order
of columns in the table is the same as what I specified when I created
the table.|||Uri Dimant wrote:
> Edward
> Are you sure?
> I did small test
> CREATE TABLE Test8
> (
> B INT,
> A INT
> )
> Looking in EM I see the same order
Try adding primary keys not on the first column.

> Why the order of the columns is so important for you?
I am migrating data from one RDBMS to SQL Server. It is much easier if
the column order is the same in the from and to tables.
> When you perform SELECT statement you can specify any order of columns.
>
>
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>|||Did you specify the correct table name in place of "tbl"?
Note that the column returned as ORDINAL_POSITION by sp_columns does
NOT necessarily reflect the order in which the columns were defined. If
you insert columns with EM then the table is recreated and you may not
see the result you expect. Also, this behaviour is subject to change in
future versions because EM is going away. Don't rely on it.
David Portas
SQL Server MVP
--|||Edward wrote on Mon, 23 May 2005 11:41:44 -0400:

> Anith Sen wrote:
> Running this stored procedure under SQL Server 7 Query Analyzer I get no
> rows returned.
Check you are putting your own table name in place of tbl, and you are in
the right database. Works fine here on my SQL 7 and SQL 2K servers.
Dan

Create Script

I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RMWhat error did you get exactly?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RM" <darasingha_nospam@.yahoo.com> wrote in message
news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RM|||Incorrect syntax - near primary (at the end of the first constraint
statement)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eDhhMYzAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> What error did you get exactly?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "RM" <darasingha_nospam@.yahoo.com> wrote in message
> news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I am trying to use a script to create a table that already exists in one
DB
> in another DB. I used Enterprise Manager, "copied" the table and pasted
it
> into notepad which gave me the following script. However, I cannot get
SQL
> query analyzer to accept this script. Not sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Hello RM,
In your create script you have your table name as [dbo.ReportSite] which
if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
So change your script to be dbo.[ReportSite] and everything should be ok.
Aaron Weiker
http://aaronweiker.com/

> I am trying to use a script to create a table that already exists in
> one DB in another DB. I used Enterprise Manager, "copied" the table
> and pasted it into notepad which gave me the following script.
> However, I cannot get SQL query analyzer to accept this script. Not
> sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Thanks - worked.
RM.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:138326632422751463292620@.news.microsoft.com...
> Hello RM,
> In your create script you have your table name as [dbo.ReportSite] which
> if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
> So change your script to be dbo.[ReportSite] and everything should be ok.
> --
> Aaron Weiker
> http://aaronweiker.com/
>
>sql

CREATE RULE for a Default Type

Hi all,
Using: SQL Server 2000 SP3A Enterprise Edition
I have setup a table that holds application information. One of the fields
holds the Applications Version Information.
I have created a default type called Version of nvarchar and length 43.
Version information is made up of 2, 3 or 4 parts, Major, Minor, Build and
Revision (Major.Minor[.Build[.Revision]]). Each part can hold up to 10 digits
up to 2,147,483,647 (int without comas). That makes 4 blocks of 10 plus up t
o
3 seperators (being the .) makes 43 the max length.
What I want to do is create a rule that will only allow a valid version
number to be stored in the field. I had something like this:
@.value LIKE '[0-9].[0-9]' OR @.value LIKE '[0-9].[0-9].[0-9]' OR @.value LIKE
'[0-9].[0-9].[0-9].[0-9]'
This will not allow 1.10.8903.56 as [0-9] specifies single characters only.
Is there quick way to do the validation as a rule without having to type
loads of LIKE statements for every possibility?Use a CHECK constraint rather than a RULE. Rules and user-defined types
are supported for backwards compatibility. Constraints are more much
easier to maintain and code.
In this case I think you'll find it easier to exclude the values you
don't want:
CREATE TABLE YourTable
(... , version VARCHAR(43) NOT NULL
CHECK (version NOT LIKE '[^.0-9]'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Oops. That should be:
CREATE TABLE YourTable
(version NVARCHAR(43) NOT NULL
CHECK (version NOT LIKE '%[^.0-9]%'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Hi,
Thanks for quick response to my question!
All working okay now!
Just 1 other question! Why use a Check instead of a Rule? I was using the
rule on the default type to save me typing the Check for every field as I
have many tables that contain this Version type field. Your code works both
as a Check and Rule.
Cheers
Paul|||Yes it will work as a Check and a Rule. User-defined types, defaults
and rules are designated as backwards compatibility features so they
won't necessarily be fully supported in future versions of SQL Server.
Books Online recommends using the ANSI/ISO standard alternatives, CHECK
and DEFAULT constraints, instead.
User-defined types are difficult to maintain because of the convoluted
syntax and binding - you have to remove all references and unbind
before you can make a change - a big problem if your type is used in
many columns. Constraints are declarative, unbound and much more
flexible.
CHECK constraints can also be used by the optimizer (although that's
unlikely to be useful with the constraint used here). I don't think the
optimizer can take advantage of Rules, although I confess I don't
recall where I've seen that documented so someone may correct me on
that point.
Finally, I suspect fewer SQL Server professionals will continue to use
and remember the old syntax in future so those who inherit your code
will probably be more productive if they don't have to cope with the
legacy stuff.
I think those are enough reasons not to use User-defined Types and
Rules. You want to save yourself some typing? Just cut-and-Paste the
CHECK constraint in Query Analyzer - that's no more work than pasting
the name of a user-defined type.
David Portas
SQL Server MVP
--|||Hi,
Thanks again for your information, very useful.
I have updated to use Check instead of Rule, was just trying to do the easy
way but as you pointed out sometimes the easy way can become problamatic in
the future.
Cheers again for your help.
Paul|||If you use a datamodeling tool (I use ERwin) you probably can do much the
same thing in the model, but generating them out as CHECK constraints. They
have domains that you can use in the model but only generate them as CHECKS.
Not sure if other tools have this, but it is a really feature of ERwin.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:667A4E1E-64D2-4030-A25E-399B07E3C9B8@.microsoft.com...
> Hi,
> Thanks again for your information, very useful.
> I have updated to use Check instead of Rule, was just trying to do the
> easy
> way but as you pointed out sometimes the easy way can become problamatic
> in
> the future.
> Cheers again for your help.
>
> Paul

Create query from table SS2K vs. SS2005

Hello,
When using SS2K, you can create a query by right-mouse click on the table
and then selecting "query" in the Enterprise Manager. How do you do this in
SS2005 when using the Management Studio? When I right-mouse click on the
table, the only option I have is to select "Open", which runs the whole
dataset.
Thanks in advance,
StevenHi Steven,
If you're just looking to add a filter before running the query, then
when you right click on the table, you can select
Script Table As > SELECT to > New Query Editor Window | Clipboard |
File
Hopefully that's close to what you had in mind.|||If it is the query builder you are after, you can reach it from the view fol
der. Right-click, new
view, construct the query and you then don't have to save it as a view. Pers
onally, I don't
appreciate query builders, though. I find them too limiting and they don't a
llow me to expand on my
SQL knowledge.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steven K0" <stroy@.api.com> wrote in message news:eDpZyG2LGHA.2316@.TK2MSFTNGP11.phx.gbl...[v
bcol=seagreen]
> Hello,
> When using SS2K, you can create a query by right-mouse click on the table
and then selecting
> "query" in the Enterprise Manager. How do you do this in SS2005 when usin
g the Management Studio?
> When I right-mouse click on the table, the only option I have is to select
"Open", which runs the
> whole dataset.
> --
> Thanks in advance,
> Steven
>[/vbcol]|||
> Personally, I don't appreciate query builders, though.
> I find them too limiting and they don't allow me to
> expand on my SQL knowledge.
Dear Tibor,
I beg to differ with you about limited functionality of query builders.
Recently I've found one that differs from the rest. Active Query Builder
can build queries with unions and sub-queries. I can't find nothing in
it that could be called 'limitation', this is just a useful addition to
the text editor. And it keeps my queries untouched even it has no
appropriate visual control for some of their clauses.
Sincerely
Sergey Smagin
*** Sent via Developersdex http://www.codecomments.com ***|||> I beg to differ with you about limited functionality of query builders.
Sorry, I should have said that I find the Query builder that comes with Ente
rprise Manager and
Visual Studio limiting. These are the only one I've tried (apart from the on
e found in Excel a long
time ago). I can imagine that there are other QBs out there that are more po
werful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Simnard" <nospam@.devdex.com> wrote in message news:uII9tUMMGHA.1532@.TK2MSFTNGP12.phx.gb
l...
>
> Dear Tibor,
> I beg to differ with you about limited functionality of query builders.
> Recently I've found one that differs from the rest. Active Query Builder
> can build queries with unions and sub-queries. I can't find nothing in
> it that could be called 'limitation', this is just a useful addition to
> the text editor. And it keeps my queries untouched even it has no
> appropriate visual control for some of their clauses.
> Sincerely
> Sergey Smagin
> *** Sent via Developersdex http://www.codecomments.com ***

Create query from table SS2K vs. SS2005

Hello,
When using SS2K, you can create a query by right-mouse click on the table
and then selecting "query" in the Enterprise Manager. How do you do this in
SS2005 when using the Management Studio? When I right-mouse click on the
table, the only option I have is to select "Open", which runs the whole
dataset.
Thanks in advance,
Steven
Hi Steven,
If you're just looking to add a filter before running the query, then
when you right click on the table, you can select
Script Table As > SELECT to > New Query Editor Window | Clipboard |
File
Hopefully that's close to what you had in mind.
|||If it is the query builder you are after, you can reach it from the view folder. Right-click, new
view, construct the query and you then don't have to save it as a view. Personally, I don't
appreciate query builders, though. I find them too limiting and they don't allow me to expand on my
SQL knowledge.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steven K0" <stroy@.api.com> wrote in message news:eDpZyG2LGHA.2316@.TK2MSFTNGP11.phx.gbl...
> Hello,
> When using SS2K, you can create a query by right-mouse click on the table and then selecting
> "query" in the Enterprise Manager. How do you do this in SS2005 when using the Management Studio?
> When I right-mouse click on the table, the only option I have is to select "Open", which runs the
> whole dataset.
> --
> Thanks in advance,
> Steven
>
|||
> Personally, I don't appreciate query builders, though.
> I find them too limiting and they don't allow me to
> expand on my SQL knowledge.
Dear Tibor,
I beg to differ with you about limited functionality of query builders.
Recently I've found one that differs from the rest. Active Query Builder
can build queries with unions and sub-queries. I can't find nothing in
it that could be called 'limitation', this is just a useful addition to
the text editor. And it keeps my queries untouched even it has no
appropriate visual control for some of their clauses.
Sincerely
Sergey Smagin
*** Sent via Developersdex http://www.codecomments.com ***
|||> I beg to differ with you about limited functionality of query builders.
Sorry, I should have said that I find the Query builder that comes with Enterprise Manager and
Visual Studio limiting. These are the only one I've tried (apart from the one found in Excel a long
time ago). I can imagine that there are other QBs out there that are more powerful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Simnard" <nospam@.devdex.com> wrote in message news:uII9tUMMGHA.1532@.TK2MSFTNGP12.phx.gbl...
>
> Dear Tibor,
> I beg to differ with you about limited functionality of query builders.
> Recently I've found one that differs from the rest. Active Query Builder
> can build queries with unions and sub-queries. I can't find nothing in
> it that could be called 'limitation', this is just a useful addition to
> the text editor. And it keeps my queries untouched even it has no
> appropriate visual control for some of their clauses.
> Sincerely
> Sergey Smagin
> *** Sent via Developersdex http://www.codecomments.com ***

Create query from table SS2K vs. SS2005

Hello,
When using SS2K, you can create a query by right-mouse click on the table
and then selecting "query" in the Enterprise Manager. How do you do this in
SS2005 when using the Management Studio? When I right-mouse click on the
table, the only option I have is to select "Open", which runs the whole
dataset.
--
Thanks in advance,
StevenHi Steven,
If you're just looking to add a filter before running the query, then
when you right click on the table, you can select
Script Table As > SELECT to > New Query Editor Window | Clipboard |
File
Hopefully that's close to what you had in mind.|||If it is the query builder you are after, you can reach it from the view folder. Right-click, new
view, construct the query and you then don't have to save it as a view. Personally, I don't
appreciate query builders, though. I find them too limiting and they don't allow me to expand on my
SQL knowledge.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steven K0" <stroy@.api.com> wrote in message news:eDpZyG2LGHA.2316@.TK2MSFTNGP11.phx.gbl...
> Hello,
> When using SS2K, you can create a query by right-mouse click on the table and then selecting
> "query" in the Enterprise Manager. How do you do this in SS2005 when using the Management Studio?
> When I right-mouse click on the table, the only option I have is to select "Open", which runs the
> whole dataset.
> --
> Thanks in advance,
> Steven
>

Create Login like another login...

One bit of functionality I like in Oracle's Enterprise manager, that I
haven't located in SQL Server is the ability to create a new login with all
the same rights, roles etc of an existing user.
I am using SQL Server 2005. I have tried
USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I ge
t
an error when executing it.
==================
Msg 213, Level 16, State 7, Line 2
Insert Error: Column name or number of supplied values does not match table
definition.
==================
Does anyone point me to a way of doing this?
PaulThat's a pretty old script you're using and it's written for SQL Server
2000, calling objects in the master database.
You can update the script using some of the security catalog views instead
of tables in master. For example, fn_my_permissions will tell you what
effective permissions the current user has.
As for the error message below, it looks like you probably had a typo
somewhere when creating or running the sproc. Probably a datatype mismatch.
joe.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:CFECA19B-9C34-4D52-B9FC-1FA95817EE78@.microsoft.com...
> One bit of functionality I like in Oracle's Enterprise manager, that I
> haven't located in SQL Server is the ability to create a new login with
> all
> the same rights, roles etc of an existing user.
> I am using SQL Server 2005. I have tried
> USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I
> get
> an error when executing it.
> ==================
> Msg 213, Level 16, State 7, Line 2
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
> ==================
> Does anyone point me to a way of doing this?
> Paul

Create Error Message and Abort

I would like to place the sql statement listed below in a SQL Server
Enterprise Manager job if this is true abort the job and send message.
If (select value from salary) < 10
Error Message 'Salary Less Than 10'Joe,
Are you trying to create a user-defined error message and invoke it in code?
If so use sp_addmessage and the T-SQL RAISERROR statment. See SQL BOL for
more details.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9BB22A3F-2617-4A06-A23E-F216250850AF@.microsoft.com...
>
> I would like to place the sql statement listed below in a SQL Server
> Enterprise Manager job if this is true abort the job and send message.
> If (select value from salary) < 10
> Error Message 'Salary Less Than 10'
>