Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

Execute trigger as a specific user

Hi,
Within my database I have some triggers on the tables (in database a)
which execute stored procedures and alter data in a separate database
(database b).
I have a security issue here as when ever I alter the tables in
database a, I have to make sure that the user exists in database b. If
not, the transaction will fail.
Does anyone know if it is possible to force a trigger to execute a
specified user? This would allow me to hardcode the user in the
trigger in database a to use a username with is already established
within database b....
Any suggestions would be great as I've been struggling with this for a
long long time.
All the best
AllanThere is no 'execute as' functionality in SQL 2000. A user must have a
security context in the other database in order to access objects therein.
If you don't want to add the user to databaseB too, an alternative is to
enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
that have not been granted access to databaseB explicitly can then access
the database using the guest user and are limited to those permissions
granted to the guest.user or public role.
However, you probably don't want to grant object permissions to public or
guest. In this case, you can enable 'db chaining' in both databases. This
will honor cross-database ownership chaining so that permissions are not
needed on objects in databaseB referenced by your proc as long as all
objects are owned by the same user. If your objects are owned by 'dbo',
both databases also need to be owned by the same login so that the 'dbo'
user ownership chain is unbroken..
Note that you should enable cross-database chaining only if you fully
understand the security implications. You need to fully trust users that
have permissions to create dbo-owned objects in those databases. Never
enable cross-database chaining in an sa-owned database unless only symin
role members can create dbo-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0502050820.70385c2e@.posting.google.com...
> Hi,
> Within my database I have some triggers on the tables (in database a)
> which execute stored procedures and alter data in a separate database
> (database b).
> I have a security issue here as when ever I alter the tables in
> database a, I have to make sure that the user exists in database b. If
> not, the transaction will fail.
> Does anyone know if it is possible to force a trigger to execute a
> specified user? This would allow me to hardcode the user in the
> trigger in database a to use a username with is already established
> within database b....
> Any suggestions would be great as I've been struggling with this for a
> long long time.
> All the best
> Allan|||fantastic... this section worked for me. Thanks very very much.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
Allan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<e5fX4#6CFHA.2180
@.TK2MSFTNGP10.phx.gbl>...
> There is no 'execute as' functionality in SQL 2000. A user must have a
> security context in the other database in order to access objects therein.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
> However, you probably don't want to grant object permissions to public or
> guest. In this case, you can enable 'db chaining' in both databases. Thi
s
> will honor cross-database ownership chaining so that permissions are not
> needed on objects in databaseB referenced by your proc as long as all
> objects are owned by the same user. If your objects are owned by 'dbo',
> both databases also need to be owned by the same login so that the 'dbo'
> user ownership chain is unbroken..
> Note that you should enable cross-database chaining only if you fully
> understand the security implications. You need to fully trust users that
> have permissions to create dbo-owned objects in those databases. Never
> enable cross-database chaining in an sa-owned database unless only symi
n
> role members can create dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0502050820.70385c2e@.posting.google.com...|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"Allan Martin" <allan.martin@.gmail.com> wrote in message
news:7ef7970c.0502090104.7d4c2781@.posting.google.com...
> fantastic... this section worked for me. Thanks very very much.
> Allan
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<e5fX4#6CFHA.2180@.TK2MSFTNGP10.phx.gbl>...

Execute Permission for Database Role

There are about 300 stored procedures and we are trying to separate EXECUTE
permission for Readers and Writers. Here is the scenario:
1) Two Domain groups created named SQLWriters and SQLReaders consisting of
respective users.
2) Two SQL logins created named SQLWriters and SQLReaders using Domain
groups SQLWriters and SQLReaders respectively. SQLWriters are in
db_datareader and db_datawriter roles and SQLReaders are in db_datareader
role only.
3) Two database roles created (a) db_executor for SQLWriters (b)
db_executor_reader for SQLReaders.
(4) db_executor has execute permission on all the stored procedures that
have Select Statements and Insert, Update, Delete Statements.
(5) db_executor_reader has execute permission on the stored procedures that
only have Select statement in the stored procedure.
When NT domain users from SQLReaders group are executing stored procedure
with Insert statement in it where their role (db_executor_reader) does not
have execute permission, it is still getting executed and doing the insert
and update. Any idea what else may be required here. Thanks.I had to explicitly dely execute premission on the SP. For some reason it
gives execute by default to all the stored procedures to all users. I can't
find documentation on why it is doing that. Will investigate more maybe one
of the MVPs can shed some light on this.
But you can check the effictive premissions to check what each Group has
access to.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fraz" wrote:

> There are about 300 stored procedures and we are trying to separate EXECUT
E
> permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures tha
t
> only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.|||Fraz (Fraz@.discussions.microsoft.com) writes:
> There are about 300 stored procedures and we are trying to separate
> EXECUTE permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures
> that only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.
Since I don't see your database, or know which version of SQL Server you
have, it's sort of difficult to say what is wrong. But I would suspect
that you at some earlier point granted execute rights to public. You can
use sp_helprotect to examine this. It could also be that users are members
of other roles and get permission this way.
To test that you have the actual setup correct, create an empty database
and set up users, procedure and permissions, and test that that works.
Then you can examine what is wrong in the target database.
In the end, DENY as Mohit suggested may be the best way, as it also
protects you against future accidents.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx