Executing SQL scripts from files

Hi, I want to create many files with SQL statements and then I want to write one SQL script file, which executes SQL statement in the other files. How can I do it?

Thanks, Radco

You could do it with Stored Procedures - create a bunch of them - then create one last one, which will execute the others.

If this doesn't do what you want - maybe you could explain your scenario in more detail

|||No it is not what I wanted. I can say to it, that I have a very long script which creates a database for testing, It has more than 2100 lines of SQL statements (mostly INSERT). It begins to be hardly manageable. So I want to split this one script into many files to simplify it. If I want to change something now I need to search 2100 lines of code. If I split it I will only change one smaller file.|||

It is rather not possible to read SQL statement from file and run on SQL server using T-SQL, but you can create your files and next merge them together and run final one. or you can write simple VB application which will read your query and execute them on SQL server.

You can also try to use command line SQL tool osql to run your queries from inside dos batch file.

Thanks

Executing password protected packages

Hi - I have a master package that executes a series of other packages. Each of these 'sub' packages has the security property Encryption Level set to 'EncryptSensitiveWithPassword'.

The master package has a series of file connections in the Connection Manager, one for each sub package, in which the password of the corresponding sub package is provided.

When I run the master package in BIDS (in interactive mode) it opens each of the sub packages, requests the password and gives the 'Document contains one or more lines of extremely long text' dialog box.

Is there any way to suppress the repeated password requests (seeing as it has already been provided in the Connection Manager) and warnings about long lines of text when executing the master package?

Thanks . . . Ed

There is no way to suppress the warnings about long lines of text - that is an annoying VS thing. Hopefully it will disappear in some upcoming service pack.

I don't know anything about the password requests thing I'm afraid.

-Jamie

|||

Hey Jamie,

What's up buddy, long time no talk to...

How have you handled this encryption piece before when working working with many packages, ever use the encrypt sensitive by password, etc...?

|||

Hiya Jason,

No, we never use encryption. We store passwords in .dtsconfig files.

I've just re-read Ed's initial post and realised why the prompt for a password is appearing. Simply its because the package has a password on it so whenever it opens up, you need to provide that password.

Ed, how are you supplying the password for each package within the connection manager?

-Jamie

|||

Ah, that makes sense, thanks...

I would recommend you do this Ed, just to eliminate the annoyances of having to enter passwords all the time.

|||

Hi Jamie

Apologies, my original post was badly worded . . . the password for the package is provided in the Execute Package task properties (the PackagePassword property).

Is this property ignored when the package is run in BIDS in interactive mode? Am I misunderstanding something?

Thanks . . . Ed

|||

Ah right. Yes, that makes more sense :)

Running this in BIDS is just a peculiarity I guess. BIDS opens up a package, notices it has a password, and asks for the password. it'd be nice if BIDS were clever enough to say "Ah, this package is being executed from elsewhere, perhaps the password is supplied there".

I suspect that this would be very difficult to do though.

-Jamie

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can see it in the task manager), but it block in that step and still in the task manager.
I tried with the calc.exe command with de same result.
?only DOS commands can execute the agent or there is another way to make it work?
Thanks in advance
Lomu
Hi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can see it in the task manager), but it block in that step and still in the task manager.
I tried with the calc.exe command with de same result.
¿only DOS commands can execute the agent or there is another way to make it work?
Thanks in advance
LomuHi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> ¿only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can se
e it in the task manager), but it block in that step and still in the task m
anager.
I tried with the calc.exe command with de same result.
?only DOS commands can execute the agent or there is another way to make it
work?
Thanks in advance
LomuHi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing an SSIS package from TSQL without using xp_cmdshell?

How can I execute an SSIS package from TSQL without using xp_cmdshell?

I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.

I want to do this:-
DECLARE @.returncode int
EXEC @.returncode = xp_cmdshell 'dtexec /sq pkgOne"'

Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?

Thanksandyabel,

Well...you could create a SQL job that has the command to execute xp_cmdshell in it and then in your web app run the following in TSQL

EXEC MSDB..SP_Start_Job @.Job_Name = 'Your Job Name Here'

The SQL job would not be scheduled to run and would only be run when you tell it to via your app. You would not have to worry about the security policy because the job is going to fire on the SQL server and that is where the xp_cmdshell is going to run from.

The bad is that the SSIS return code is going to be passed back to the SQL job and not your web app. But, if you post the return code to a table on your database and then have your app scan that table you can get the code that way...if you need it.

Executing a batch script from "Execute Process" Task

Hello,

I have an "execute process" task which executes a perl script. When I run the task, it shows a prompt with the message "the publisher can not be verified". It gives the option to continue the task or to cancel.

The problem for me is that I want to schedule this package to run automatically, and I don't want the automated process to show this dialog. I need to override the security setting and have SSIS execute my batch scripts without prompting. Assuming I don't want to rewrite the Perl code into a script task, is there a way to do acomplish this?

Thanks!

Arkadiy

Normally, Digitally Signing whatever is being called into question could resolve this type of error, but I have not seen this error occur in SSIS as of yet (usually see this in IE).

Here's some stuff you can try:

Try testing after lowering the internet security (on the test system) to a lower setting (for example low - or "custom level" and play around with the settings here). Be sure to set this back when you are done testing.

You could look into digitally signing the perl script. I'm not sure if this pertains to that or not:

http://www.cpan.org/authors/id/R/RO/ROODE/perlsign-0.04

"This program invokes GPG to digitally sign Perl source files. The GPG signature becomes embedded into the source file as POD paragraphs. The functionality of the program or module isn't affected. This may be useful to prove authorship of a program, or to prove that changes have or have not been made to a source file."

You could read this to see if it gives you any insight:

http://www.microsoft.com/technet/Security/bestprac/mblcode.mspx

Anyway, if this is not relavent, can you provide more details as to how you are executing the perl script? Like where the perl script is stored, etc.

|||

Thanks,

I found another way of solving this without changing the security settings. The trick is to invoke executable or dll (such as perl.exe) and not a batch script. So in the "execute process" task, the executable became perl.exe and everything else became command line argument. Then it works fine.

Thanks again for your help anyway!

Arkadiy

executeBatch()

Hi there,
when I use a PreparedStatement for a executing a batch of inserts, the
driver executes a single sp_executesql for EACH statement in the batch.
Is this a limitation of the database or of the driver?
Kein Plan berlebt die erste Feindberhrung.
Steffen Ramlow wrote:

> Hi there,
> when I use a PreparedStatement for a executing a batch of inserts, the
> driver executes a single sp_executesql for EACH statement in the batch.
> Is this a limitation of the database or of the driver?
The question is whether those sp_executesql calls are sent one-at-a-time
with a wait for a DBMS response for each one, or whether they are sent
all together in one network packet, with whatever parameter calls needed
in between. The best performance would of course be to send everything in
one packet. If it doesn't, it's because the driver implemented the syntax of
batches without reaching for the best performance.
Joe

> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> The question is whether those sp_executesql calls are sent
> one-at-a-time with a wait for a DBMS response for each one, or
> whether they are sent
> all together in one network packet, with whatever parameter calls
> needed
> in between. The best performance would of course be to send
> everything in one packet. If it doesn't, it's because the driver
> implemented the syntax of batches without reaching for the best
> performance.
> Joe
One call per statement. Maybe this is necessary to get the results for each
statement?
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> One call per statement. Maybe this is necessary to get the results for each
> statement?
>
Nope. the DBMS will send everything back in order anyway. In fact you can do your own
speedy batching that is optimal:
StringBuffer myBatch = new StringBuffer();
while (make batch)
{
myBatch.append("insert into my table values(" + myVal1 + ", " + myVal2 + ")\n"); // newline at end is important
}
stmt.executeUpdate( myBatch.toString() ); // all goes at once.

> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> Nope. the DBMS will send everything back in order anyway. In fact you
> can do your own
> speedy batching that is optimal:
> StringBuffer myBatch = new StringBuffer();
> while (make batch)
> {
> myBatch.append("insert into my table values(" + myVal1 + ", " +
> myVal2 + ")\n"); // newline at end is important }
> stmt.executeUpdate( myBatch.toString() ); // all goes at once.
If it is so easy (and i think it is), why makes the driver a single call per
statement?
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> If it is so easy (and i think it is), why makes the driver a single call per
> statement?
>
Well, it really isn't so easy all the time, because it is superior for
whole-system performance for the DBMS to get the SQL to be repeated once,
and compile it once to be re-used, rather than to has the DBMS parse it
each time every time, but you and I agree that there is no reason the driver
couldn't do the batching of the execute-sql calls and send them all at once.
That would be faster.
Joe
> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> Well, it really isn't so easy all the time, because it is superior for
> whole-system performance for the DBMS to get the SQL to be repeated
> once, and compile it once to be re-used, rather than to has the DBMS
> parse it each time every time, but you and I agree that there is no
> reason the driver couldn't do the batching of the execute-sql calls
> and send them all at once. That would be faster.
Well - it depends. I made some tests against mssql 2000 and oracle 9.
1000 insert statements (varchar(10), varchar(50), numeric(10)), no index.
It took about 2,5 s to do this with a prepared batch and mssql 2000.
It took about 0,1 s to do this with a prepared batch and oracle.
It took about 1,1 s to do this with a single db-call, with all statements in
it and mssql 2000.
It took about 4,8 s to do this with a single db-call, with all statements in
it and oracle.
So it is very DB-dependend, how efficient the driver AND the DB can handle
this sort of task.
This specific DB-behavior should be known to the driver vendor.
Hm, the oracle-driver guys know their DB, the mssql-driver guys not!? ;)
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> Well - it depends. I made some tests against mssql 2000 and oracle 9.
> 1000 insert statements (varchar(10), varchar(50), numeric(10)), no index.
> It took about 2,5 s to do this with a prepared batch and mssql 2000.
> It took about 0,1 s to do this with a prepared batch and oracle.
> It took about 1,1 s to do this with a single db-call, with all statements in
> it and mssql 2000.
> It took about 4,8 s to do this with a single db-call, with all statements in
> it and oracle.
> So it is very DB-dependend, how efficient the driver AND the DB can handle
> this sort of task.
Good test, but were you able to determine whether the oracle driver sent the real batch
execute in one packet, like we want the MS driver to do? It might be that the MS
driver/DBMS was also extremely fast is it sent all the sp_execute calls at once too,
and this also seems to show a weakness in the oracle sql parser...

> This specific DB-behavior should be known to the driver vendor.
> Hm, the oracle-driver guys know their DB, the mssql-driver guys not!? ;)
Well, the MS driver comes from a company that makes drivers for several DBMSes,
so there is probably a considerable amount of generic code and algoritms that
is being used in the MS driver.

> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> Good test, but were you able to determine whether the oracle driver
> sent the real batch execute in one packet,
Yes, (IIRC) I could see it in the oracle trace I made.

> It might be that the MS driver/DBMS was also extremely fast is
> it sent all the sp_execute calls at once too,
No SQL Server Profiler says one call per statement.

> and this also seems to
> show a weakness in the oracle sql parser...
It seems that these guys think, everybody uses bind variables

> Well, the MS driver comes from a company that makes drivers for
> several DBMSes,
> so there is probably a considerable amount of generic code and
> algoritms that
> is being used in the MS driver.
Should I test merants oracle driver too!?
But what I did, I have tested i-net's seropto, it took 2,8 s instead of the
0,1 s with the oracle driver.
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> Yes, (IIRC) I could see it in the oracle trace I made.
>
>
> No SQL Server Profiler says one call per statement.
>
>
> It seems that these guys think, everybody uses bind variables
>
>
> Should I test merants oracle driver too!?
> But what I did, I have tested i-net's seropto, it took 2,8 s instead of the
> 0,1 s with the oracle driver.
> --
> Kein Plan berlebt die erste Feindberhrung.
god luck. e live in interesting times...
Joe
>
>

Execute SSIS Package Job fails

Hey Folks,

I've got here a strange Problem. If I try to execute the SQL Server Agent Job, that executes my SSIS Package, it fails. The job succeedes when I run the Job as the Proxy, that maps on the User, that has deployed the Package, or when I run the Job under an System Administrator Proxy. Now my Question - how must I set up an Service-Account, which is no Admin and not has deployed the Package?

I already know, that the User has to be in the sysadmin role, and in all msdb SQLAgent*

Thanks and greez

Karsten

I assume the service account is suitable for running SQL Agent. To be sure you may want to review this rather long BOL topic "Setting Up Windows Service Accounts" (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm), but plenty of good info buried in there.

So if the job fails when using the base service account rather than a higher privileged proxy it says to me that the service account does not have correct permissions. If it errors, then you need to find out why. Here are some tips on doing just that, in particular look at using a CmdExec step, and the Package Store File Permissions section.

Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

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);