Executing Multi-lines SQL Scripts From Command-line

Hello,

I'm trying to execute a file containing few SQL statements.

CREATE VIEW test1 AS SELECT * FROM table1;
CREATE VIEW test2 AS SELECT * FROM table2;

The standard SQL way is to end a statement with semi-colon.
But doing that,it doesn't work in SQL Server.
After changing ";" to "GO", it works fine.

Is there anyway we can stick to ";" to indicate the end of statement.
I don't want to create scripts which works only in SQL Server.

Please comment.

Thanks in advance.Simon Hayes (sql@.hayes.ch) writes:
> Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by the
> SQL Server client tools as a batch delimiter. The semi-colon is the Oracle
> equivalent, as far as I know.

And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
He has semi-colons all over the place.

Semicolon as a statement terminator is indeed standard SQL, and it is a
pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
Microsoft added semicolons as a optional terminator in SQL7, but it would
constitute a major blow to existing code to make it mandatory. (But if
MS would supply a tool that added all missing semicolons to existing
code, it could be worth the effort.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||James (ehchn1@.hotmail.com) writes:
> I'm trying to execute a file containing few SQL statements.
> CREATE VIEW test1 AS SELECT * FROM table1;
> CREATE VIEW test2 AS SELECT * FROM table2;
> The standard SQL way is to end a statement with semi-colon.
> But doing that,it doesn't work in SQL Server.
> After changing ";" to "GO", it works fine.
> Is there anyway we can stick to ";" to indicate the end of statement.
> I don't want to create scripts which works only in SQL Server.

This is not legal T-SQL:

CREATE VIEW test1 AS SELECT * FROM table1;
go
CREATE VIEW test2 AS SELECT * FROM table2;
go

For some explicable reason ; is not permitted here. (Probably because
CREATE VIEW must be alone in a batch.

However, if you change the batch separator to with the -c option as
Simon Hayes suggested, this works:

CREATE VIEW test1 AS SELECT * FROM table1
;
CREATE VIEW test2 AS SELECT * FROM table2
;

And it still legal in ANSI-compliant engines.

Since the batch-separator must be alone on a line, this solution
can work decently. Of course if a developer for some reason puts a
lone semicolon in the middle of a stored procedure, he effectively
splits that procedure in two.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93D3E36904A63Yazorman@.127.0.0.1...
> Simon Hayes (sql@.hayes.ch) writes:
> > Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by
the
> > SQL Server client tools as a batch delimiter. The semi-colon is the
Oracle
> > equivalent, as far as I know.
> And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
> He has semi-colons all over the place.
> Semicolon as a statement terminator is indeed standard SQL, and it is a
> pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
> Microsoft added semicolons as a optional terminator in SQL7, but it would
> constitute a major blow to existing code to make it mandatory. (But if
> MS would supply a tool that added all missing semicolons to existing
> code, it could be worth the effort.)
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Interesting, I didn't know there was any standard at all in that area. It's
a good point about Celko's posts, though, given his insistence on
platform-independent code - I guess I should have worked that one out...

Simon

Executing an SSIS package containing a Data Mining Query task from a SQL job

Hi, I'm new to this forum, so please bare with me.

I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.

This package works fine in visual studio and when i deploy it to the server.

The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.

Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.

DontSaveSenstive is clearly not an option as this just creates an unusable package.

EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)

EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.

Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.

Thanks

Bob.

There is a comprehensive KB article that may cover your question:

http://support.microsoft.com/kb/918760

|||

Thanks, that has helped.

for reference i employed the DontSaveSensitive level of security and stored the Query String for the Data Mining Query task in an XML configuration file.

this is the only option on the KB article that worked for me.

Thanks.

Execute Multiple SQL statements in Stored Proc

Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)

Table Test

Id Description

1 Insert into test(Id,Name) Values (1,'Ron')
2 Update Test Set Name = 'Robert' where Id = 1
3 Delete from Test where Id = 1

In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks

ReoI hope this will help u.
--Insert statement
--insert into Test values(1, 'Insert into test(Id,Name) Values (1,''Ron'')')

declare @.sql varchar(8000)
select @.sql=Description from Test where Id=1
--print @.sql
exec (@.sql)|||Sounds like homework. What have you tried?