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
Showing posts with label manager. Show all posts
Showing posts with label manager. Show all posts
Tuesday, March 27, 2012
Wednesday, March 21, 2012
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
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
Monday, March 19, 2012
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 ***
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 ***
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
>
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
>
Wednesday, March 7, 2012
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
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
Friday, February 24, 2012
Create File Option In File Connection Manager
May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.
1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task
2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)
3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.
IMHO, these features would be very important for developer productivity.
Marco Russo
http://sqljunkies.com/weblog/sqlbi
All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.
For #2, Copy/Paste might be a short term solution.
regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.
(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)
|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.
Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!
-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?
regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.
Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.
Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.
Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:
- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.
|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||
Ok here's my wish list,
1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)
2. Read only access to the whole package from componentmetadata, not just that related to the component.
3. Parallel For each loops. Performance.
4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop
5. Debug support for script component (not just the task)
6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.
7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.
8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance
9. IIS Log file connection both source and destination would be good. But would settle for source.
10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.
11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers
That'll do for now.
|||Thanks Simon. Excellent input. Thanks!Anyone else?
K|||
I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.
1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.
2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead 
3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.
These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.
What everyone else think about these features.
Thanks
Sutha
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx
-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!
It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.
-Jamie
Create File Option In File Connection Manager
May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.
1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task
2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)
3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.
IMHO, these features would be very important for developer productivity.
Marco Russo
http://sqljunkies.com/weblog/sqlbi
All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.
For #2, Copy/Paste might be a short term solution.
regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.
(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.
Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!
-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?
regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.
Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.
Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.
Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:
- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||
Ok here's my wish list,
1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)
2. Read only access to the whole package from componentmetadata, not just that related to the component.
3. Parallel For each loops. Performance.
4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop
5. Debug support for script component (not just the task)
6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.
7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.
8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance
9. IIS Log file connection both source and destination would be good. But would settle for source.
10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.
11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers
That'll do for now.
|||Thanks Simon. Excellent input. Thanks!Anyone else?
K|||
I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.
1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.
2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead
3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.
These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.
What everyone else think about these features.
Thanks
Sutha
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx
-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!
It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.
-Jamie
Sunday, February 19, 2012
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'
>
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'
>
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'
>
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'
>
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'
>
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'
>
Create Disaster recovery site (Seperate physical location)
Hello, let me just say thank you in advance for any responses.
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able to
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....
You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able to
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....
You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
Create Disaster recovery site (Seperate physical location)
Hello, let me just say thank you in advance for any responses.
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able t
o
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able t
o
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
Create Disaster recovery site (Seperate physical location)
Hello, let me just say thank you in advance for any responses.
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able to
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able to
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
Create dimension without member
Hi
Analysis manager wont let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanksI'm confused. Is this really a dimension? Or is this a dimension that doesn't exist now, but will in the future? (i.e. you're rolling out a new type of product and you've created new product dimension, but the fact table has no corresponding records). Can you plug an 'unknown' attribute into your fact and dimension?
Let me know if I'm way off.|||Fact table has field called Due date which contains null values. When I create Due_date dimension, it won’t let me create, gives a message unable to count the members. I have to create dimensions out of all the fields in the fact table and send the cub to this person. Later this person will load the cub into his server and change data source and run the cub.
But my question is , If in his source table due_Date has null values(no data) , when this person runs the cube , it will give the error right?
Right now his source table due_date doen’t have values, but later he will had value into it.
He wants me to create each field in the fact table one dimension, doesn’t matter the fact table filed contains data or not? Can I do that?|||Is this 2000 or 2005? I just tried this in 2000 and it worked. No errors.|||It's 2000, you mean , it let you create a dimension with out a member.
Analysis manager wont let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanksI'm confused. Is this really a dimension? Or is this a dimension that doesn't exist now, but will in the future? (i.e. you're rolling out a new type of product and you've created new product dimension, but the fact table has no corresponding records). Can you plug an 'unknown' attribute into your fact and dimension?
Let me know if I'm way off.|||Fact table has field called Due date which contains null values. When I create Due_date dimension, it won’t let me create, gives a message unable to count the members. I have to create dimensions out of all the fields in the fact table and send the cub to this person. Later this person will load the cub into his server and change data source and run the cub.
But my question is , If in his source table due_Date has null values(no data) , when this person runs the cube , it will give the error right?
Right now his source table due_date doen’t have values, but later he will had value into it.
He wants me to create each field in the fact table one dimension, doesn’t matter the fact table filed contains data or not? Can I do that?|||Is this 2000 or 2005? I just tried this in 2000 and it worked. No errors.|||It's 2000, you mean , it let you create a dimension with out a member.
Tuesday, February 14, 2012
Create Data Driven Subscription via C# code? SSRS 2005 EE (or Batch Reports?)
Hello,
Does anyone know if you can create a Data Driven Subscription via code (C#)? The web based Report Manager does it via the Subscriptions.aspx (I assume). Seems like they are using theMicrosoft.ReportingServices.UI.Pages.Subscriptions namespace.
I can't find any info on anybody doing it. So, here's my need, please tell me if I am on the right track.
In my ASP.NET 2.0 C# app, we use the SSRS Web Service (reportservice2005) to generate PDF reports in a new window. If the User requests a large amount of pages (say 1000), the request is timing out, as well as tying up the web server.
So, the thought was, why not submit these reports to a "Batch". It seems that Data Driven Subscriptions could do it but what I'd want is to create the Subscription on the fly and clean them up after they run (or weekly).
Should I go this approach,OR should I write a Windows Service (or Console App) that just uses the same SSRS Web Service (reportservice2005) to create my reports in batch mode?
Any thoughts are helpful.
Nothing like answering your own question. I guess after a good night's sleep I Googled the right phrase to see this link:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005_methods.aspx
There is a CreateDataDrivenSubscription() method.
However, given my original question on proper approach,should I be doing it this way or write the windows service or console app to do it?
Thoughts?
|||We are looking into the same concept. We are trying to create a single data-driven subscription that runs nightly and all the "requests" for the day are processed. We're achieving this by inserting the "request" into the database. The only problem we are having now is how to do this for multi-value parameters since each row creates a different subscription and you can only reference one column per parameter. Good luck!
Subscribe to:
Posts (Atom)