Execute sql on multiple objects (DBs or tables)

Hi,
Because I'me a newbie on this... and I don't want to make a monstrous-query, please some advice on this:

In pseudo-code:

for objectname in (specified list of objects)
do
some sql code (i.e. create table xyz)
done

With 'objects' I mean a database or table name.

I've searched and found the foreachdb option, but I don't want to execute the sql n ALL db's but only the ones specified.

Any help is appreciated!sticking to your '(list of objects)' syntax, you could do this:

declare @.x int
declare @.dbname varchar(500)
set @.x = 1

create table #databases
(
ID int IDENTITY,
name varchar(500)
)

insert #databases
select name
from master..sysdatabases
where name in(<your list of databases separated by comma>)

while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
--<capture @.dbname for dynamic sql>
print @.dbname
set @.x = @.x + 1
end

drop table #databases

Good luck.

Execute sp_start_job from stored procedure

I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job? This is all run from a web page and NOT the Query Window.

The Agent is just a robot itmusthave correct permissions to run Jobs and other things replication included. So you clone admin level permissions to run it. Try the link below for SQL Server proxy account. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190698.aspx

EXECUTE permission denied on [various DB objects] within SQL Express db

First attempt at using SQL Express developing web app. All works fine within VS2005 dev web server. However, after compiling and creating IIS7 site on same machine without changingconnectionString="Server=xxxx\SQLEXPVISTA;Database=ABCtest;Integrated Security=true" (SQLVISTA is named instance of SQL Express)

The only easy way I can avoid the 'EXECTUTE permissions denied...' is to give the NT AUTHORITY\NETWORK SERVICE db_owner role membership. Should I worry? Or should I go through the objects individually and specify permissions? Eventually, this will be on public web server.

In advance, thanks.

ASM

Hi,

From your description, it seems that you met "'EXECTUTE permissions denied..." error when you are trying to run your application from your IIS, right?

Based on my understanding, the cause of the issue is the account of IIS has not the permission to access your database. Generally, the ASPNET account is be authorized by default in your SQLExpress, so your application should work while running from the development server of VisualStudio. But when you deploy your application into IIS, the login account has changed, so you should add the login account into the logins of SQLExpress.

Thanks.

Execute installation script.

Hello. I will export the database generated script for all objects, I want to make an installer that executes that script on the remote server, I think the installer must ask for sa password; anyway thats not the problem, How can I make with SMO execute an script file?

Thanks

SMO expects you to either use the SMO object model to modify the database interactivly or else script the changes and run them externally later. If you are just executing SQL script, you can use sqlcmd.exe for that.

For something fancier that gets passwords and other user input and then runs the script, you would need to write a wrapper script/application to gather the user input and then send it off to the server.|||If you see DotNetNuke when you navigate to the first page it takes a lot of time, that page creates all the objects on the database taking as input some .sql file that are already on the package. However its in .net 1.1 and not 2.0 and I want to use SMO.

It seems to be very difficult because I havent find good info about this.

Tks|||Use Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery Method with your script in parameter
(at first you must create a database)|||How can I Pass this method a .sql file?|||You can do something like:

using System.IO;

~~~~~~~~~~~~~~~
Server srv = new Server("MyServer");
string filePath = "c:\\create.sql";

FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

StreamReader sr = new StreamReader(file);

string s = sr.ReadToEnd();

sr.Close();

srv.Databases["tempdb"].ExecuteNonQuery(s);

|||I use this:

string script = System.IO.File.ReadAllText(file);

Execute installation script.

Hello. I will export the database generated script for all objects, I want to make an installer that executes that script on the remote server, I think the installer must ask for sa password; anyway thats not the problem, How can I make with SMO execute an script file?

Thanks

SMO expects you to either use the SMO object model to modify the database interactivly or else script the changes and run them externally later. If you are just executing SQL script, you can use sqlcmd.exe for that.

For something fancier that gets passwords and other user input and then runs the script, you would need to write a wrapper script/application to gather the user input and then send it off to the server.|||If you see DotNetNuke when you navigate to the first page it takes a lot of time, that page creates all the objects on the database taking as input some .sql file that are already on the package. However its in .net 1.1 and not 2.0 and I want to use SMO.

It seems to be very difficult because I havent find good info about this.

Tks|||Use Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery Method with your script in parameter
(at first you must create a database)|||How can I Pass this method a .sql file?|||You can do something like:

using System.IO;

~~~~~~~~~~~~~~~
Server srv = new Server("MyServer");
string filePath = "c:\\create.sql";

FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

StreamReader sr = new StreamReader(file);

string s = sr.ReadToEnd();

sr.Close();

srv.Databases["tempdb"].ExecuteNonQuery(s);

|||I use this:

string script = System.IO.File.ReadAllText(file);

Execute DTS package from ADO in VB

I'm using ADO objects in a VB Macro for Excel. I'd like to execute a DTS package located on SQLServer.

What is the syntax to do this?

Here's my current database connection code:Option Explicit

Dim db_connection As ADODB.Connection
Dim db_results As ADODB.Recordset
Dim db_error As ADODB.Error

Private Sub DB_Initialize()
Set db_connection = New ADODB.Connection
db_connection.Open "Provider='SQLOLEDB';Data Source='BACK_SQL';" & _
"Initial Catalog='my_db';Integrated Security='SSPI';"
Set db_results = New ADODB.Recordset
End SubIs it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?|||Originally posted by odinsdream
Is it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?

I don't know very much about VB but I definately know my DTS packages.. yes you can execute a DTS package via a stored procedure

server_name=server name dts package is on
user_name=login to access server
password=user's password
package_name = DTS package name
package_password=DTS package pwd

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name" /Mpackage_password'

GO
-------

if your package does not have a pwd then your sp should look like this

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name"

GO|||So how would one execute that stored procedure from VBA for Access?