about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.
NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegr oups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>
|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End
|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegr oups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,
Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thank
s,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

Ability to create dynamic subreports within report

I have the need to create a report that contains four subreports. Each of the four subreports can be any one of 20-30 different reports (layouts differ).
Is there a way to programatically change the name (or source) of the subreport at runtime? Would this end up creating a new rdl for each report?
Any help or ideas are greatly appreciated!!
RickGcan you put "all" your sub reports under the master report and hide your sub
reports.
then dynamically, simply show the targeted sub reports.
"RickG" <RickG@.discussions.microsoft.com> a écrit dans le message de
news:BAF79348-FB1D-45B0-861B-4A3BDA30DE58@.microsoft.com...
> I have the need to create a report that contains four subreports. Each of
the four subreports can be any one of 20-30 different reports (layouts
differ).
> Is there a way to programatically change the name (or source) of the
subreport at runtime? Would this end up creating a new rdl for each report?
> Any help or ideas are greatly appreciated!!
> RickGsql

Ability to create and schedule jobs on shared system

I have a shared SQL Server, used by applications that donâ't need an
instance, just a DB. Users with DBO rights to their DB, no system roles, can
still create and schedule jobs under sql agent. I donâ't want to allow this,
how can I prevent it?Hello Jason,
You could use sqlagent proxy account to run the jobs if they are not in
sysadmin server role. If you want to prevent it, you could use the
following method:
1. Start SQL Server Enterprise Manager.
2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
expand SQL Server Group.
3. Expand the instance of SQL Server that you want to set up the proxy
account for.
4. Expand Management, right-click SQL Server Agent, and then click
Properties.
5. In the SQL Server Agent Properties dialog box, click the Job System
tab.
6. Under Non-SysAdmin job step proxy account, click to check the Only
users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
steps check box.
If you have any further questions on the issue, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||i looked at this setting, it currently is checked, however there is a user w/
only dbo rights to their db that can create, schedule jobs and run jobs.
i checked the roles, he has none.
this is a windows account, it if has some high level, domain admin type
right in AD, could AD be working behind the scenes to enable the features in
SQL Server for this user?
""Peter YangMSFT]"" wrote:
> Hello Jason,
> You could use sqlagent proxy account to run the jobs if they are not in
> sysadmin server role. If you want to prevent it, you could use the
> following method:
> 1. Start SQL Server Enterprise Manager.
> 2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
> expand SQL Server Group.
> 3. Expand the instance of SQL Server that you want to set up the proxy
> account for.
> 4. Expand Management, right-click SQL Server Agent, and then click
> Properties.
> 5. In the SQL Server Agent Properties dialog box, click the Job System
> tab.
> 6. Under Non-SysAdmin job step proxy account, click to check the Only
> users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
> steps check box.
> If you have any further questions on the issue, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hello Jason,
Yes. If it's domain admin, it's by default in the group local admin groups.
Local admin group is added in SQL server with sysadmin rights by default.
You may want to check the logins on the server to check this.
You may want to temporarily remove the login to test if the issue still
occurs. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607|||i have found another user who is NOT a domain admin, OR server admin. he is
DBO on his db and is able to create/run a job that runs a select from the
database.
i guess becuase these to meet the criteria of that checkbox.
that checkbox you mentioned, what actually is considered a cmdexec step?
assuming that is the case, is there any other way to block users from
creating jobs?
""Peter YangMSFT]"" wrote:
> Hello Jason,
> Yes. If it's domain admin, it's by default in the group local admin groups.
> Local admin group is added in SQL server with sysadmin rights by default.
> You may want to check the logins on the server to check this.
> You may want to temporarily remove the login to test if the issue still
> occurs. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
>|||Hello Jason,
I'd like to confirm if check the "Only users with SysAdmin priviledges can
execute CmdExec and ActiveScripting job
steps" check box in the sqlagent property dialog. If so, it's supposed that
a domain user without sysadmin rights shall be able to create job but the
job cannot run properly because there is no proxy agent account to run the
job.
If you have proxy account configured, you are not able to block only one
user/login using the proxy account because it's used as proxy for all users
without sysadmin rights.
I think cmdexec step in a job is actually run as the proxy account.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support|||that check box is checked.
upon further testing, this is what I see.
only some commands are blocked as indicated by the descriptive text.
e.g. a user can create and run a job that does a simple select. but user
cannot run a job that kicks off a DTS package as it requires a cmdexec cmd,
he can create it though. I was wanting to block the running off all jobs to
force users to schedule them through a DBA.
""Peter YangMSFT]"" wrote:
> Hello Jason,
> I'd like to confirm if check the "Only users with SysAdmin priviledges can
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed that
> a domain user without sysadmin rights shall be able to create job but the
> job cannot run properly because there is no proxy agent account to run the
> job.
> If you have proxy account configured, you are not able to block only one
> user/login using the proxy account because it's used as proxy for all users
> without sysadmin rights.
> I think cmdexec step in a job is actually run as the proxy account.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>|||Hello Jason,
Sorry for my fault and I spoke too quick based on my memory. In SQLServer
2000, the following table describes the user context under which a
scheduled SQLServer Agent job is run depending on whether the owner of the
job is sysadmin in SQLServer or not.
Owner of job Job Type Job executed as (user)
=========== ========= =============
non-sysadmin TSQL [owner of Job]
non-sysadmin CmdExec/Activex [SQL Agent proxy account]
sysadmin TSQL [owner of Job]
sysadmin CmdExec/Activex [SQLServer startup account]
Also, scheduled DTS packages fall under CmdExec job type.
You are correct that the checkbox only blocks CmdExec and ActiveScripting
job of non-sysadmin users. They could still schedule and run jobs that
don't use CmdExec and ActiveScripting job etc.
Since the SQLagent will "impersonate" the user only when the user runs the
job step, actually there is no option to "deny" the user to access the
database objects that he has permissions in the first place.
However, you could deny the user to create jobs by the following method:
1. Add the user as a database user of msdb database.
2. Run the following statement to deny its permission to
use msdb
deny execute on msdb.dbo.sp_add_job to username
If you have any further questions or concerns, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Ability to create and schedule jobs on shared system

I have a shared SQL Server, used by applications that don’t need an
instance, just a DB. Users with DBO rights to their DB, no system roles, can
still create and schedule jobs under sql agent. I don’t want to allow this,
how can I prevent it?
Hello Jason,
You could use sqlagent proxy account to run the jobs if they are not in
sysadmin server role. If you want to prevent it, you could use the
following method:
1. Start SQL Server Enterprise Manager.
2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
expand SQL Server Group.
3. Expand the instance of SQL Server that you want to set up the proxy
account for.
4. Expand Management, right-click SQL Server Agent, and then click
Properties.
5. In the SQL Server Agent Properties dialog box, click the Job System
tab.
6. Under Non-SysAdmin job step proxy account, click to check the Only
users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
steps check box.
If you have any further questions on the issue, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||i looked at this setting, it currently is checked, however there is a user w/
only dbo rights to their db that can create, schedule jobs and run jobs.
i checked the roles, he has none.
this is a windows account, it if has some high level, domain admin type
right in AD, could AD be working behind the scenes to enable the features in
SQL Server for this user?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> You could use sqlagent proxy account to run the jobs if they are not in
> sysadmin server role. If you want to prevent it, you could use the
> following method:
> 1. Start SQL Server Enterprise Manager.
> 2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
> expand SQL Server Group.
> 3. Expand the instance of SQL Server that you want to set up the proxy
> account for.
> 4. Expand Management, right-click SQL Server Agent, and then click
> Properties.
> 5. In the SQL Server Agent Properties dialog box, click the Job System
> tab.
> 6. Under Non-SysAdmin job step proxy account, click to check the Only
> users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
> steps check box.
> If you have any further questions on the issue, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||Hello Jason,
Yes. If it's domain admin, it's by default in the group local admin groups.
Local admin group is added in SQL server with sysadmin rights by default.
You may want to check the logins on the server to check this.
You may want to temporarily remove the login to test if the issue still
occurs. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
|||i have found another user who is NOT a domain admin, OR server admin. he is
DBO on his db and is able to create/run a job that runs a select from the
database.
i guess becuase these to meet the criteria of that checkbox.
that checkbox you mentioned, what actually is considered a cmdexec step?
assuming that is the case, is there any other way to block users from
creating jobs?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> Yes. If it's domain admin, it's by default in the group local admin groups.
> Local admin group is added in SQL server with sysadmin rights by default.
> You may want to check the logins on the server to check this.
> You may want to temporarily remove the login to test if the issue still
> occurs. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
>
|||Hello Jason,
I'd like to confirm if check the "Only users with SysAdmin priviledges can
execute CmdExec and ActiveScripting job
steps" check box in the sqlagent property dialog. If so, it's supposed that
a domain user without sysadmin rights shall be able to create job but the
job cannot run properly because there is no proxy agent account to run the
job.
If you have proxy account configured, you are not able to block only one
user/login using the proxy account because it's used as proxy for all users
without sysadmin rights.
I think cmdexec step in a job is actually run as the proxy account.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
|||that check box is checked.
upon further testing, this is what I see.
only some commands are blocked as indicated by the descriptive text.
e.g. a user can create and run a job that does a simple select. but user
cannot run a job that kicks off a DTS package as it requires a cmdexec cmd,
he can create it though. I was wanting to block the running off all jobs to
force users to schedule them through a DBA.
""Peter YangMSFT]"" wrote:

> Hello Jason,
> I'd like to confirm if check the "Only users with SysAdmin priviledges can
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed that
> a domain user without sysadmin rights shall be able to create job but the
> job cannot run properly because there is no proxy agent account to run the
> job.
> If you have proxy account configured, you are not able to block only one
> user/login using the proxy account because it's used as proxy for all users
> without sysadmin rights.
> I think cmdexec step in a job is actually run as the proxy account.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>

Ability to create and schedule jobs on shared system

I have a shared SQL Server, used by applications that don’t need an
instance, just a DB. Users with DBO rights to their DB, no system roles, ca
n
still create and schedule jobs under sql agent. I don’t want to allow thi
s,
how can I prevent it?Hello Jason,
You could use sqlagent proxy account to run the jobs if they are not in
sysadmin server role. If you want to prevent it, you could use the
following method:
1. Start SQL Server Enterprise Manager.
2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
expand SQL Server Group.
3. Expand the instance of SQL Server that you want to set up the proxy
account for.
4. Expand Management, right-click SQL Server Agent, and then click
Properties.
5. In the SQL Server Agent Properties dialog box, click the Job System
tab.
6. Under Non-SysAdmin job step proxy account, click to check the Only
users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
steps check box.
If you have any further questions on the issue, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||i looked at this setting, it currently is checked, however there is a user w
/
only dbo rights to their db that can create, schedule jobs and run jobs.
i checked the roles, he has none.
this is a windows account, it if has some high level, domain admin type
right in AD, could AD be working behind the scenes to enable the features in
SQL Server for this user?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> You could use sqlagent proxy account to run the jobs if they are not in
> sysadmin server role. If you want to prevent it, you could use the
> following method:
> 1. Start SQL Server Enterprise Manager.
> 2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and the
n
> expand SQL Server Group.
> 3. Expand the instance of SQL Server that you want to set up the proxy
> account for.
> 4. Expand Management, right-click SQL Server Agent, and then click
> Properties.
> 5. In the SQL Server Agent Properties dialog box, click the Job System
> tab.
> 6. Under Non-SysAdmin job step proxy account, click to check the Only
> users with SysAdmin priviledges can execute CmdExec and ActiveScripting jo
b
> steps check box.
> If you have any further questions on the issue, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Jason,
Yes. If it's domain admin, it's by default in the group local admin groups.
Local admin group is added in SQL server with sysadmin rights by default.
You may want to check the logins on the server to check this.
You may want to temporarily remove the login to test if the issue still
occurs. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/defaul...ProPhone#faq607|||i have found another user who is NOT a domain admin, OR server admin. he is
DBO on his db and is able to create/run a job that runs a select from the
database.
i guess becuase these to meet the criteria of that checkbox.
that checkbox you mentioned, what actually is considered a cmdexec step?
assuming that is the case, is there any other way to block users from
creating jobs?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> Yes. If it's domain admin, it's by default in the group local admin groups
.
> Local admin group is added in SQL server with sysadmin rights by default.
> You may want to check the logins on the server to check this.
> You may want to temporarily remove the login to test if the issue still
> occurs. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Please note that the newsgroups are staffed weekdays with a goal to provid
e
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for mor
e
> immediate assistance:
> [url]http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607[/ur
l]
>|||Hello Jason,
I'd like to confirm if check the "Only users with SysAdmin priviledges can
execute CmdExec and ActiveScripting job
steps" check box in the sqlagent property dialog. If so, it's supposed that
a domain user without sysadmin rights shall be able to create job but the
job cannot run properly because there is no proxy agent account to run the
job.
If you have proxy account configured, you are not able to block only one
user/login using the proxy account because it's used as proxy for all users
without sysadmin rights.
I think cmdexec step in a job is actually run as the proxy account.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support|||that check box is checked.
upon further testing, this is what I see.
only some commands are blocked as indicated by the descriptive text.
e.g. a user can create and run a job that does a simple select. but user
cannot run a job that kicks off a DTS package as it requires a cmdexec cmd,
he can create it though. I was wanting to block the running off all jobs to
force users to schedule them through a DBA.
""Peter YangMSFT]"" wrote:

> Hello Jason,
> I'd like to confirm if check the "Only users with SysAdmin priviledges ca
n
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed tha
t
> a domain user without sysadmin rights shall be able to create job but the
> job cannot run properly because there is no proxy agent account to run the
> job.
> If you have proxy account configured, you are not able to block only one
> user/login using the proxy account because it's used as proxy for all user
s
> without sysadmin rights.
> I think cmdexec step in a job is actually run as the proxy account.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>|||Hello Jason,
Sorry for my fault and I spoke too quick based on my memory. In SQLServer
2000, the following table describes the user context under which a
scheduled SQLServer Agent job is run depending on whether the owner of the
job is sysadmin in SQLServer or not.
Owner of job Job Type Job executed as (user)
=========== ========= =============
non-sysadmin TSQL [owner of Job]
non-sysadmin CmdExec/Activex [SQL Agent proxy account]
sysadmin TSQL [owner of Job]
sysadmin CmdExec/Activex [SQLServer startup account]
Also, scheduled DTS packages fall under CmdExec job type.
You are correct that the checkbox only blocks CmdExec and ActiveScripting
job of non-sysadmin users. They could still schedule and run jobs that
don't use CmdExec and ActiveScripting job etc.
Since the SQLagent will "impersonate" the user only when the user runs the
job step, actually there is no option to "deny" the user to access the
database objects that he has permissions in the first place.
However, you could deny the user to create jobs by the following method:
1. Add the user as a database user of msdb database.
2. Run the following statement to deny its permission to
use msdb
deny execute on msdb.dbo.sp_add_job to username
If you have any further questions or concerns, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

A Wizard to create Triggers

Hi Guys,

Is there any Wizard or software or tools that I can use to create Triggers for my tables ... !??

It is for MSSQL 2005 database

Thanks for your help,
Mehdi

What kind of triggers do you want ?|||

In Managenent studio try View->template explorer and next select Trigger from templates and select trigger you wont.

A view that include a table from a database in another sql server

I need to create a view that contain a table from a database that reside in
another sql server.
The view is a simple select statement joining two tables; i.e. table A inner
joining table B that resides in different server. I hope this make sense.
I thank you in advance for any recommendations.
JamesCreate a linked server to the SQL Server containing table B or replicate the
data from table B to another table locally for the join.
HTH
Jerry
"jamesww7" <jamesww7@.discussions.microsoft.com> wrote in message
news:30796887-26D4-44E1-B3CF-AA4EB31C2894@.microsoft.com...
>I need to create a view that contain a table from a database that reside in
> another sql server.
> The view is a simple select statement joining two tables; i.e. table A
> inner
> joining table B that resides in different server. I hope this make sense.
> I thank you in advance for any recommendations.
> --
> James

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

a view in a package for oracle

Ho do I create a view inside a package.Use EXECUTE IMMEDIATE:

BEGIN
...
EXECUTE IMMEDIATE 'create view v_emp as select * from emp';
END;

But why would you want to do that?|||I have to create install scripts for these, because they need to go into various schemas. along with functions and procedures. So I wanted to bundle them all up. That way a package can stay in the schema for future reference. Or can we store scripts on the schema.
Why, is this in-efficient?|||No, I thought maybe you were creating views dynamically from your application. Normally packages are used for code that will be run many times. What you are doing is unusual, but harmless.|||Hi, I am unable to figure out how to invoke this view from the package specification. here is what I have

CREATE OR REPLACE PACKAGE ABC IS
end;
/

I know something has to identify the view in the header but I cant figure out how to do that.

CREATE OR REPLACE PACKAGE BODY ABC AS
BEGIN
EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW ABCD
(LISTNAME, CODE, DESCRIPTION)
AS
SELECT
translate(CODELKUP.LISTNAME,"^`"""," ") LISTNAME,
translate(CODELKUP.CODE,"^`"""," ") CODE,
translate(CODELKUP.DESCRIPTION,"^`"""," ") DESCRIPTION
FROM
CODELKUP';
END;
/|||You need to create a procedure in the package like this:

CREATE OR REPLACE PACKAGE ABC IS
procedure create_view;
end;
/

CREATE OR REPLACE PACKAGE BODY ABC AS

procedure create_view is
begin
EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW ABCD
(LISTNAME, CODE, DESCRIPTION)
AS
SELECT
translate(CODELKUP.LISTNAME,"^`"""," ") LISTNAME,
translate(CODELKUP.CODE,"^`"""," ") CODE,
translate(CODELKUP.DESCRIPTION,"^`"""," ") DESCRIPTION
FROM
CODELKUP';
end;

END;
/

You will have problems with those quotes though. Oracle uses only single quotes (') to delimit text strings.

A very beginer question: error in creating table

I am new in T-SQL. I have just create a empty database and would like to create some table in it. So I type the following in SQL Query Analyzer:

CREATE TABLE mydatabasename.mytablename (

some code here
)
But when execute, it give me an error from the first line "Specified owner name 'mydatabasename' either does not exist or you do not have permission to use it."

when I remove mydatabasename it inserts the table in to the master database, not in my database.

How can I insert the table into my database? Should I use SQL Query Analyzer or use others program? I 've tried to use the command "create table" in enterprise manager but it immediately give another error, some what like "could not use empty column name ... "
Seem to be too simple question, but I am really appreciate your helpWhen you use the SQL Query Analyzer do you choose from the dropdown your newly created Database?|||CREATE TABLE mydatabasename.mytablename

should be

CREATE TABLE mydatabasename.dbo.mytablename

A fully qualified name is
servername.databasename.owner.object

You can't skip one like you're doing. You can omit them from left to right, but can't skip a part.|||Thanks all. I will try both, select the newly database and use databasename.dbo.tablename.

Infact, I use to think about using "databasename.dbo.tablename" but then think it 's ridiculous for a try. What a mistake. It's my because my poor English make me sometime not understand the full syntax.

Thank you.

a tricky view to create in Access; SQL is welcome !

Hi I am now sitting for almost ages in front of the screen, and may be
someone can help me...

Here's the thing

I have a table that looks like the following:

Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|

Mat-Nr: There are several million different materials
Package-Rule: there are only in total 2 different package rules
Package-Class: there are in total 9 different classes
Package-Material: there are 4 different package materials
Package-Nr: each package material has 1 or more package-numbers
Amount: there is at least 1 material in the amount

An example looks like this:

Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
102 01 01 V 003 10
102 01 01 Z 045 03
116 01 01 V 056 23
116 01 01 Z 067 20

My problem is now, I have to create a query or view in Access 2000 so
that the following information is in ONE row:

Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
102 01 01 V 003 10
..
..
..
Package-Material|Package-Nr|Amount|
Z 045 03
..
..
..

thank you very much
Stefanal_capone@.web.de (Stefan) wrote in message news:<249f98b3.0407070650.75f1ead5@.posting.google.com>...
> Hi I am now sitting for almost ages in front of the screen, and may be
> someone can help me...
> Here's the thing
> I have a table that looks like the following:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
>
> Mat-Nr: There are several million different materials
> Package-Rule: there are only in total 2 different package rules
> Package-Class: there are in total 9 different classes
> Package-Material: there are 4 different package materials
> Package-Nr: each package material has 1 or more package-numbers
> Amount: there is at least 1 material in the amount
> An example looks like this:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
> 102 01 01 V 003 10
> 102 01 01 Z 045 03
> 116 01 01 V 056 23
> 116 01 01 Z 067 20
>
> My problem is now, I have to create a query or view in Access 2000 so
> that the following information is in ONE row:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
> 102 01 01 V 003 10
> .
> .
> .
> Package-Material|Package-Nr|Amount|
> Z 045 03
> .
> .
> .
> thank you very much
> Stefan

Hello Stefan,

Just one question. Are you dealing with only two records for each
Mat-Nr field data? Meaning 102, 102, 166, 166 etc.?

If so, then perhaps you can make a grouping query to get
your data result like the following.

Create a query using your source table with all the fields.

Next, change it to a grouping/summing query.

Then, edit your last three fields to be like the example below.

PackMat01: Package-Material
PackNr01:Package-Nr
Amount01: Amount

Now, change Grouping to First

Then add 3 more fields accordingly.

PackMat02: Package-Material
PackNr02:Package-Nr
Amount02: Amount

Now, change Grouping to Last

Run the query. What this should show is the First record for 102
and the last record for 102 in your grouping query for the 01 and
02 field groups according to your requirements.

Good luck!

Regards,

Ray

A tough nut to crack

I have a class C# which dynamically generates the SQL to create a stored procedure, one of the parameters i write into to the SQL is @.Keyname, and i use it in the following way

WHERE @.KeyName = CONVERT(VARCHAR, @.KeyValue)

However when i run the generated SQL to actually create the stored proc is is created with the line exactly as it is above. I need to be able to take the keyname as specified when the proc is called, which is a quoted string (i.e 'keynamefield' and write it into the SQL as keynamefield without quotes to make the field lookup dynamic based on this keyname field.

I am presuming that i will need to use function against the keyname variable inside the stored procedure (as this cannot be called from C#) something like GetValue(@.KeyName)

??

Any Ideas would be greatly appreciated

You need to use dynamic SQL but that is not what you want to do. You should not write applications that passes column names and table names dynamically for manipulation. There are lot of security risks, performance issues among other things. Best is to create the SP in such a manner that you don't need dynamic SQL. There are many ways to do this. The link below discusses the techniques to do something like this:

http://www.sommarskog.se/dyn-search.html

|||

As Umachandar said, its highly risk to create a object from your code. Your database is widly open to any one. Security Issues..

Comming to your issue, you have to use NVARCHAR instead of VARCHAR for unicode characters(non-english alphabets).

|||

Got it sussed thanks, the procs use the sql in a pre-generated fashion (i.e the compiler calculates the select and then it is added to a stored proc and which sql is executed is controlled by paremeters.) The procs are also encrypted so no one can execute any code they fancy on the database. I wonder though, can you encrypt tables the same way toy can procedures (i.e. WITH ENCRYPTION)?

Thanks

|||No. You can't encrypt the table definitions.

A thought this would be simple...

I had originally thought a full outer join would solve this, but alas I'm a roadblock. Perhaps it's because it's a Friday!

The goal is to create the most efficient query that will display results of the daily calls and sales as
this:

Date EmpID Calls Sales
7/1/2006 1 20 5
7/1/2006 2 25 7
7/1/2006 3 NULL 1
7/1/2006 4 10 NULL

The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA

create table t1 (Date smalldatetime, EmpID int, Calls int)

create table t2 (Date smalldatetime, EmpID int, Sales int)

insert into t1
values ('7/1/2006', 1, 20)

insert into t1
values ('7/1/2006', 2, 25)

insert into t1
values ('7/1/2006', 4, 10)

insert into t2
values ('7/1/2006', 1, 5)

insert into t2
values ('7/1/2006', 2, 7)

insert into t2
values ('7/1/2006', 3, 1)

what about this?

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks for the response.

The problem I was running into is I need to include both EmpID and Date in the full outer join since the results need to be report only for a single date. So, when I exec this it does not return EmpID 3...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date

Any thoughts? TIA

|||

are you sure?

I get this back with both queries

2006-07-01 00:00:00 1 20 5
2006-07-01 00:00:00 2 25 7
2006-07-01 00:00:00 3 NULL 1
2006-07-01 00:00:00 4 10 NULL

|||

You're right!

I was doing this...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date where t1.date = '7/1/2006'

When I should have had the WHERE clause this...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date where t1.date = '7/1/2006' or t2.date = '7/1/2006'

That seems to work. Thanks!

a text file data source

I would know how to create a report which has as data source a text file

someone can help me?

Hi Maaloul-

To retrieve data directly from a text file, you would need to implement your own custom data extension. A sample data extension can be found here:

http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5

-Jon

|||Another option is to create an ODBC connection to the text file. This has been written up several times.