Executing sql statements

When i execute the following in Sql query anlyzer

Declare @.dbname varchar(30),
@.str varchar(500),
@.emailID varchar(50)

set @.EmailID='santosh@.yahoo.com'

set @.dbname='DB_kms_prv'

set @.str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +
@.dbname + '.dbo.tblEmployee where officialEmail=' + @.emailID

exec (@.str)

I get error message

The column prefix 'santosh@.yahoo' does not match with a table name or
alias name used in the query.
How to get rid of it..?You need to put single quotes around the address:

set @.str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +
@.dbname + '.dbo.tblEmployee where officialEmail=''' + @.emailID + ''''

If you get syntax errors from dynamic SQL, then just 'SELECT @.sql'
before executing it, so you can see what the statement looks like -
that makes the problem much clearer.

But don't use dynamic SQL at all unless it's absolutely necessary - see
here for all the reasons why to avoid it, and alternative solutions:

http://www.sommarskog.se/dynamic_sql.html

Specifically for your case, see "Getting data from another database" in
this section:

http://www.sommarskog.se/dynamic_sql.html#Dyn_Db

Simonsql

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 Simple Statements - Newbie

Hi Gurus,

This is a question that I have for T-sql programmers.

Problem: To execute a statement with aritmetic operators in it.

Database: SQL Server 2000

For e.g. if my string has "5+6+9" I need to execute the string and
obtain a value of 20 in a variable. I read a little bit of
documentation on preparing a statement etc, but before going deeper
there, thought will write you you guys.

Thanks in advance!

Bhaskarsp_executesql is probably the best option:

declare
@.s varchar(100),
@.sql nvarchar(4000),
@.i int

set @.s = '5+6+9'
set @.sql = 'set @.i = ' + @.s
exec sp_executesql @.sql, N'@.i int output', @.i = @.i output
select @.i

But in general dynamic SQL can be awkward and there are a number of
important issues to consider - see here for the full story:

http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could do this easily in a front-end application, and
if you have more complex formulae to evaluate then that might be a
better option.

Simon|||Thanks much Simon,

It worked fine for me. However, is there a way I can use variables
within the statement?

set @.a = 5
set @.b = 9
set @.c = 11

i.e. @.str = '@.a + @.b + @.c"

and then be able to execute str?

I am trying to make calculations dynamic.

Thanks much!!!|||Thanks much Simon,

It worked fine for me. However, is there a way I can use variables
within the statement?

set @.a = 5
set @.b = 9
set @.c = 11

i.e. @.str = '@.a + @.b + @.c"

and then be able to execute str?

I am trying to make calculations dynamic.

Thanks much!!!|||Bkr (keepitliteus@.yahoo.com) writes:
> It worked fine for me. However, is there a way I can use variables
> within the statement?
> set @.a = 5
> set @.b = 9
> set @.c = 11
> i.e. @.str = '@.a + @.b + @.c"
> and then be able to execute str?
> I am trying to make calculations dynamic.

Maybe, but probably not. Or put in another way: this is not something
you normally do in SQL Server. You may still have a very good reason
for wanting to do this, but there is also the possibility that you
are approaching your real business problem incorrectly.

So I suggest that you give an overview of the real-world probelm
where these expressions come in.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bkr (keepitliteus@.yahoo.com) writes:
> It worked fine for me. However, is there a way I can use variables
> within the statement?
> set @.a = 5
> set @.b = 9
> set @.c = 11
> i.e. @.str = '@.a + @.b + @.c"
> and then be able to execute str?
> I am trying to make calculations dynamic.

Maybe, but probably not. Or put in another way: this is not something
you normally do in SQL Server. You may still have a very good reason
for wanting to do this, but there is also the possibility that you
are approaching your real business problem incorrectly.

So I suggest that you give an overview of the real-world probelm
where these expressions come in.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is the business reason that prompts me to do this. Business would
like to compute a set of say 50 values for about 30 different items. If
I have to hardcode the calculations it will be 50x30 = 1500 items.

Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
would need programmer intervention.

Hence if I had the expression itself in a table and had a way of
calculating it dynamically, it would help.

I will definately explore other methods of doing the same, but my
thought is there is a strong reason to have a functionality like this.

Thanks again
Bkr|||Bkr (keepitliteus@.yahoo.com) writes:
> Here is the business reason that prompts me to do this. Business would
> like to compute a set of say 50 values for about 30 different items. If
> I have to hardcode the calculations it will be 50x30 = 1500 items.
> Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
> would need programmer intervention.
> Hence if I had the expression itself in a table and had a way of
> calculating it dynamically, it would help.

Thanks, that gives me at least a glimpse of the requirements. I'm still
not sure that this is an easy way to.

You could store an expression as @.a + @.b + @.c, and then get that from
the table and put in an variable @.expr. Then you could say:

SELECT @.sql = '@.res = ' + @.expr
EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT',
@.a, @.b, @.c, @.result OUTPUT

If the formula would be changed to @.a + 2 * @.b / @.c it would still
work. But if the formula would become @.a + @.b + @.d / @.e you would have
to change the way you compute the value.

You could parse the string to find out which the values are, but
that is a tedious and tricky business to do in T-SQL. A language like
Perl would be a lot nicer for this sort of work.

One idea that occurred to me is that you should not store the expressions
complete strings, but instead should have a table like:

CREATE TABLE expressiontokens (exprid int NOT NULL,
rowno smallint NOT NULL,
tokens varchar(30) NOT NULL,
CONSTRAINT pk_exprtmers PRIMARY KEY expressionterms(exprid, term))

Then for one expression you could have somehing like

rowno token
1 @.a
2 +
3 @.b
4 *
5 (
6 @.c
7 +
...

@.a, @.b and @.c would then be key values to a table where you would look up
the actual values. In this way would not have to parse the expression at
run-time. (But you would have to parse the expression to store it. If
you were do this in T-SQL, you would still have to build the SQL statement
dynamically.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is the business reason that prompts me to do this. Business would
like to compute a set of say 50 values for about 30 different items. If
I have to hardcode the calculations it will be 50x30 = 1500 items.

Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
would need programmer intervention.

Hence if I had the expression itself in a table and had a way of
calculating it dynamically, it would help.

I will definately explore other methods of doing the same, but my
thought is there is a strong reason to have a functionality like this.

Thanks again
Bkr|||Bkr (keepitliteus@.yahoo.com) writes:
> Here is the business reason that prompts me to do this. Business would
> like to compute a set of say 50 values for about 30 different items. If
> I have to hardcode the calculations it will be 50x30 = 1500 items.
> Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
> would need programmer intervention.
> Hence if I had the expression itself in a table and had a way of
> calculating it dynamically, it would help.

Thanks, that gives me at least a glimpse of the requirements. I'm still
not sure that this is an easy way to.

You could store an expression as @.a + @.b + @.c, and then get that from
the table and put in an variable @.expr. Then you could say:

SELECT @.sql = '@.res = ' + @.expr
EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT',
@.a, @.b, @.c, @.result OUTPUT

If the formula would be changed to @.a + 2 * @.b / @.c it would still
work. But if the formula would become @.a + @.b + @.d / @.e you would have
to change the way you compute the value.

You could parse the string to find out which the values are, but
that is a tedious and tricky business to do in T-SQL. A language like
Perl would be a lot nicer for this sort of work.

One idea that occurred to me is that you should not store the expressions
complete strings, but instead should have a table like:

CREATE TABLE expressiontokens (exprid int NOT NULL,
rowno smallint NOT NULL,
tokens varchar(30) NOT NULL,
CONSTRAINT pk_exprtmers PRIMARY KEY expressionterms(exprid, term))

Then for one expression you could have somehing like

rowno token
1 @.a
2 +
3 @.b
4 *
5 (
6 @.c
7 +
...

@.a, @.b and @.c would then be key values to a table where you would look up
the actual values. In this way would not have to parse the expression at
run-time. (But you would have to parse the expression to store it. If
you were do this in T-SQL, you would still have to build the SQL statement
dynamically.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you!

Initially I thought of creating a table with a column for the prder in
which the formula was to be computed. Later, I thought every formula of
this type can be separated into Numerator and Denominator. So if I have
a table with 2 columns one for Numerator and another for Den. The onus
of giving the right expressions would then rest with the user and make
it easy to maintain and compute too.

Hence this approach. I tried using the code above. But it would not run
for me.
This is the error message that I get.

Server: Msg 170, Level 15, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '@.'.
(this error is right at the EXEC sp_executesql. If you can throw some
light on it it would be great. I am also reachable at 240-483-3242.

Thanks!|||Thank you!

Initially I thought of creating a table with a column for the prder in
which the formula was to be computed. Later, I thought every formula of
this type can be separated into Numerator and Denominator. So if I have
a table with 2 columns one for Numerator and another for Den. The onus
of giving the right expressions would then rest with the user and make
it easy to maintain and compute too.

Hence this approach. I tried using the code above. But it would not run
for me.
This is the error message that I get.

Server: Msg 170, Level 15, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '@.'.
(this error is right at the EXEC sp_executesql. If you can throw some
light on it it would be great. I am also reachable at 240-483-3242.

Thanks!|||Bkr (keepitliteus@.yahoo.com) writes:
> Hence this approach. I tried using the code above. But it would not run
> for me.
> This is the error message that I get.
> Server: Msg 170, Level 15, State 1, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near '@.'.

Obviously there should be a SET or SELECT first.

Working with dynamic SQL means that you will have understand the
syntax errors you get - because it's quite easy and go lost and
achieve one. Including something like:

IF @.debug = 1
PRINT @.sql

can often help to spot the problems.

As for mastering dynamic SQL, there is an article on my web site
about it: http://www.sommarskog.se/dynamic_sql.html.

> (this error is right at the EXEC sp_executesql. If you can throw some
> light on it it would be great. I am also reachable at 240-483-3242.

Not that I intend to call, but you should probably have included the
country code as well. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bkr (keepitliteus@.yahoo.com) writes:
> Hence this approach. I tried using the code above. But it would not run
> for me.
> This is the error message that I get.
> Server: Msg 170, Level 15, State 1, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near '@.'.

Obviously there should be a SET or SELECT first.

Working with dynamic SQL means that you will have understand the
syntax errors you get - because it's quite easy and go lost and
achieve one. Including something like:

IF @.debug = 1
PRINT @.sql

can often help to spot the problems.

As for mastering dynamic SQL, there is an article on my web site
about it: http://www.sommarskog.se/dynamic_sql.html.

> (this error is right at the EXEC sp_executesql. If you can throw some
> light on it it would be great. I am also reachable at 240-483-3242.

Not that I intend to call, but you should probably have included the
country code as well. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||My bad. I am sure you might have guessed where I am from :) The country
code is 011. (United States). That is a very interesting article by
you. My guess is I am writing to a BIG sql server guru. I guess i get
lucky sometimes.

I would definately like to call you if that is okay with you.

Here is the complete code that I am pasting. Not sure where I am going
wrong. Also am planning to buy a book on sql programming. Please have a
look and let me know.
--drop procedure sp_test
create procedure sp_test AS

DECLARE @.debug integer

DECLARE @.a integer
DECLARE @.b integer
DECLARE @.c integer
DECLARE @.expr nvarchar
declare @.result integer
declare @.res integer
declare @.s varchar(100),
@.sql nvarchar(4000),
@.i int

set @.a =5
set @.b = 9
set @.c = 11

set @.expr = '@.a + @.b + @.c'

SELECT @.sql = '@.res = ' + @.expr

IF @.debug = 1
PRINT @.sql

EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
IF @.debug = 1
PRINT @.sql

Thanks a lot!!!!!|||My bad. I am sure you might have guessed where I am from :) The country
code is 011. (United States). That is a very interesting article by
you. My guess is I am writing to a BIG sql server guru. I guess i get
lucky sometimes.

I would definately like to call you if that is okay with you.

Here is the complete code that I am pasting. Not sure where I am going
wrong. Also am planning to buy a book on sql programming. Please have a
look and let me know.
--drop procedure sp_test
create procedure sp_test AS

DECLARE @.debug integer

DECLARE @.a integer
DECLARE @.b integer
DECLARE @.c integer
DECLARE @.expr nvarchar
declare @.result integer
declare @.res integer
declare @.s varchar(100),
@.sql nvarchar(4000),
@.i int

set @.a =5
set @.b = 9
set @.c = 11

set @.expr = '@.a + @.b + @.c'

SELECT @.sql = '@.res = ' + @.expr

IF @.debug = 1
PRINT @.sql

EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
IF @.debug = 1
PRINT @.sql

Thanks a lot!!!!!|||Bkr (keepitliteus@.yahoo.com) writes:
> My bad. I am sure you might have guessed where I am from :) The country
> code is 011. (United States). That is a very interesting article by
> you. My guess is I am writing to a BIG sql server guru.

Actually, you are posting to a Usenet newsgroup, which are read by an
unknown number of people all over the world.

> Here is the complete code that I am pasting. Not sure where I am going
> wrong. Also am planning to buy a book on sql programming. Please have a
> look and let me know.
> --drop procedure sp_test
> create procedure sp_test AS

Don't call your stored procedures sp_<something>. This prefix is reserved
for system stored procedures, and if Microsoft would ship an sp_test,
you would have a great surprise.

> SELECT @.sql = '@.res = ' + @.expr
> IF @.debug = 1
> PRINT @.sql
> EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
> IF @.debug = 1
> PRINT @.sql

As I said in my previous post "Obviously there should be a SET or SELECT
first." (Also, see Simon's example earlier in the thread.)

Permit to be quite frank: you have a very trivial syntax error. Yes, you
got it from me, but just because it was from me, does not mean that it
is correct. Trivial syntax error crop up all the time when you work
with T-SQL (at least it does when I do :-). If you are not able to
deal with those, when you will have extreme difference of handling
dynamic expressions entered by users (which certainly is advanced usage).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bkr (keepitliteus@.yahoo.com) writes:
> My bad. I am sure you might have guessed where I am from :) The country
> code is 011. (United States). That is a very interesting article by
> you. My guess is I am writing to a BIG sql server guru.

Actually, you are posting to a Usenet newsgroup, which are read by an
unknown number of people all over the world.

> Here is the complete code that I am pasting. Not sure where I am going
> wrong. Also am planning to buy a book on sql programming. Please have a
> look and let me know.
> --drop procedure sp_test
> create procedure sp_test AS

Don't call your stored procedures sp_<something>. This prefix is reserved
for system stored procedures, and if Microsoft would ship an sp_test,
you would have a great surprise.

> SELECT @.sql = '@.res = ' + @.expr
> IF @.debug = 1
> PRINT @.sql
> EXEC sp_executesql @.expr, N'@.a int, @.b int, @.c int, @.res int OUTPUT'
> IF @.debug = 1
> PRINT @.sql

As I said in my previous post "Obviously there should be a SET or SELECT
first." (Also, see Simon's example earlier in the thread.)

Permit to be quite frank: you have a very trivial syntax error. Yes, you
got it from me, but just because it was from me, does not mean that it
is correct. Trivial syntax error crop up all the time when you work
with T-SQL (at least it does when I do :-). If you are not able to
deal with those, when you will have extreme difference of handling
dynamic expressions entered by users (which certainly is advanced usage).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 Large SQL statements

Hello,

I am trying to import a very large amount of data into an SQL database. The data is in a format of SQL statements already - it is all in one large text file consisting of a number of CREATE TABLE X and INSERT INTO X VALUES().

The problem is that the amount of values being inserted into some of the tables is so large, that I am unable to open the .SQL file using query analyzer to run it, because the line-size limit for query analyzer is 64kb, whereas actual line-size in the file is in some cases in excess of 15MB.

I would appreciate any advice on how to get all this data into a managable format. I keep thinking that there simply has to be a way to execute these over-size SQL statements.

Thank you in advance!

-SergeyTry to open the .SQL file in NOTEPAD and scratch into manageble sizes for that statement.

Or use OSQL utility to execute that .SQL file.

Executing Dynamic SQL larger than 8000 characters

Can anyone tell me if there is a way to get around the 8000 character limit for executing dynamic SQL statements? I have tried everything I can think of to get around this limitation but I can not figure out a way around this.

Here are a few of the things that I have tried that have not worked

Using VARCHAR(MAX) instead on VARCHAR(8000)

Using NVARCHAR(MAX) instead of NVARCHAR(4000)

Using nTEXT (BLOBs are not support for variables)

Executing the statement via .NET using the SqlCommand.CommandText (it accepts a data type of String which is limited to 8000 characters)

I can't believe this is sooo hard to figure out. I know somebody has run into this before. All help would be greatly appreciated.

Create multiple 8000 char strings, break your string into 8000 char blocks and run "EXEC (@.sql1+@.sql2+@.sql3+.......)"|||

Tom,

Thanks for the help! However, that did not work either. My query is 8621 chars long I broke the query into two VARCHAR(8000) variables, one was 7900 and the other was 721. Here is the error:

The character string that starts with 'SELECT ...' is too long. Maximum length is 8000.

Not sure why it is not working for me if it works for you... what is the data type fo the variables that you are using?

|||I haven't seen that error before. However, I am usually executing multiple "commands", not 1 single command greater than 8000 chars. That might be a limitation of SQL, the command buffer might only be 8000 chars.

Maybe someone from MS can answer if that is a "command buffer limit"?

You might have to break it further into multiple select statements.|||

Can you post the code. There shouldn't be a problem executing sql statement larger than 8000 via exec().

e.g.

declare @.a varchar(8000),@.b varchar(8000),@.c varchar(8000)
select @.a='select top 1 name,''',@.b=replicate('a',8000),@.c=''' from sysobjects'
exec(@.a+@.b+@.c)

|||

varchar(max) also should work just fine - could you please try something like the following?

declare @.cmd varchar(max)
set @.cmd = 'print /*' + replicate ('-', 7990);
set @.cmd = @.cmd + replicate ('-', 7990) + '*/ getdate()';
exec (@.cmd)
print datalength (@.cmd)

Feb 2 2007 2:23PM
16000

|||you have to use the new sys.sp_sqlexec stored proc that accepts a parameter of type text. have used this on a numberof occassions with sql strings in excess of 8k limit.|||Thanks for all the help. Looks like I have several options here.

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

|||etetetet

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

ExecuteNonQuery() not giving correct affected rows

When I use ExecuteNonQuery() with the stored procedure below it returns -1. However, when i tried to get rid of the if/else statements and just leave one insert statement for testing purposes, ExecuteNonQuery() returns the correct affected rows which is 1. So it seems like ExecuteNonQuery() doesn't work when the INSERT statement is inside the IF..ELSE. Can anybody help me with this problem? I haven't tried using @.@.RowCount because I really want to use ExecuteNonQuery() to do this because I don't want to rewrite my DAL. Thanks in advance

-- With if/else ExecuteNonQuery returns -1

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS

BEGIN

BEGIN TRAN
IF @.ObjectType = 2
BEGIN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 3
BEGIN
INSERT INTO FighterPhotoContributions
(FighterPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 4
BEGIN
INSERT INTO OrganizationContributions
(OrganizationId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 5
BEGIN
INSERT INTO EventContributions
(EventId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 6
BEGIN
INSERT INTO FightContributions
(FightId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 7
BEGIN
INSERT INTO FightPhotoContributions
(FightPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

END

-- Without if/else ExecuteNonQuery returns 1

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS

BEGIN

BEGIN TRAN

INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

END

1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]2 @.ObjectIdint,3 @.FanIdint,4 @.DateContributedDateTime,5 @.Notesnvarchar(512),6 @.ObjectTypeint7AS89BEGIN1011 BEGIN TRAN12 IF @.ObjectType = 213BEGIN14 INSERT INTO FighterContributions15 (FighterId, FanId, DateContributed, Notes)VALUES16 (@.ObjectId, @.FanId, @.DateContributed, @.Notes)17RETURN@.@.ROWCOUNT18END1920END212223

Try each statement like this

|||

RETURN @.@. ROWCOUNT on each statement won't work because it won't commit the transaction (it won't hit COMMIT TRAN).

|||

Perhaps you can add an OUTPUT Parameter to get the rows affected. Instead of this statement: IF @.@.ERROR <> 0, try:

SELECT @.rows = @.@.ROWCOUNT, @.Error = @.@.ERROr

IF @.ERROR <> 0

You will have to declare the @.Rows and @.Error variables. Add @.Rows to the parameters list as OUTPUT param. Check the value in @.rows from your front end.

|||

I think only one insert statment will execute at one time based on parameter.. don;t kwno why u are using transaction for single insert. ? Is this correct

|||

I'm sorry guys. I think I messed up while testing the method because right now the stored procedure in question is now working. I also added RETURN SCOPE_IDENTITY() to get the ID of the new inserted record and it works. So having INSERT statements inside IF ELSE is not a problem for executenonquery.

So this is the final procedure

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS
BEGIN
BEGIN TRAN
IF @.ObjectType = 2
BEGIN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 3
BEGIN
INSERT INTO FighterPhotoContributions
(FighterPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 4
BEGIN
INSERT INTO OrganizationContributions
(OrganizationId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 5
BEGIN
INSERT INTO EventContributions
(EventId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 6
BEGIN
INSERT INTO FightContributions
(FightId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 7
BEGIN
INSERT INTO FightPhotoContributions
(FightPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

RETURN SCOPE_IDENTITY()

END

For those who are interested, here is how to get the value of the new id. Ignore CreateParameter() method. It is just an abstraction.

IDataParameter param = CreateParameter("ReturnValue", DbType.Int32);
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
connection.Open();
if (command.ExecuteNonQuery() > 0)
{
newObjectId = (int)((IDataParameter)command.Parameters["ReturnValue"]).Value;
}

|||

satya_tanwar:

I think only one insert statment will execute at one time based on parameter.. don;t kwno why u are using transaction for single insert. ? Is this correct

Actually, I am going to add delete statements before the insert statements later. Anyways, thanks for helping out.

|||

thats good mark the post as answered and close the post

Execute Statements in Order

Dear friends,
I am using query analyzer to build a database,
I want to do certain command in order, that is: not to execute the next statement until the previous one has been finish execution.
What is the command used for this purpose
Thanks for your valuable helpGO

The message I have entered is too short|||You don't even need the GO command. Sequential statements in a script execute sequentially anyway.|||You don't even need the GO command. Sequential statements in a script execute sequentially anyway.
unless there is no 'goto' statement|||You need a GO ...If you have sequential steps SL server will open multiple threads and execute it independent of each other....|||You need a GO ...If you have sequential steps SL server will open multiple threads and execute it independent of each other....I've never seen separate statements in a SQL batch executed out of order. I don't believe that is possible.

You can use IF...THEN...ELSE, WHILE, and RETURN to control flow, and there is still GOTO (which is rarely used), but otherwise the individual (atomic) SQL statements are executed in the order that they are specified. Within a given statement like a SELECT, different clauses can execute unpredictably (for example the JOINs can materialize in whatever order the database engine finds convenient), but the individual SQL statements are always executed in sequence as directed by the flow of control statements.

-PatP|||You need a GO ...If you have sequential steps SL server will open multiple threads and execute it independent of each other....
Absolultely not. TSQL is a procedural language.|||From originator,

Thansk for all, but,
I believe that the statments will start excute sequentially, but
for example if i have 3 statments, the first needs 4 minites to finish execute
the second and third needs only one second,
in this case the server will start excute the first statment, then the second ( before the first finishes) , then the third

why i think like this,

I have around 30 statments to import data from MS Access into MS SQL
when i excute the statments by marking command by command , then pressing F5. It works fine,
but when i excutes all at the same time , it will give errors...

I tried GO but still giving errors

Thanks again for effort.|||Can you post your script? I'm not sure what problem(s) you are finding, but I can guarantee you that the statements will be processed one at a time, in the order that they appear in the script (unless you have statements that explicitly change the flow of control such as IF...THEN...ELSE).

-PatP|||The Script is as follows:

select Schools

truncate table [Log] --
truncate table Course --
truncate table Exam --
truncate table Exam4 --
truncate table ExamDef --
truncate table Payment --
truncate table Permit --
truncate table Prohibit --
truncate table SecTopicSub2 --
truncate table Student --
truncate table Groups --
truncate table DailyTransaction --
truncate table reGrouping --
truncate table rePayment --
truncate table SalesVoucher --

truncate table AccRestrict -- should keep some users
truncate table SecTopicSub -- should keep some users
truncate table SPass -- should keep some users
truncate table City
truncate table Nationality
truncate table Sales
truncate table CourseT
truncate table Classify
truncate table ClassRoom
truncate table Period
truncate table PermitNo
truncate table Reference
truncate table Remarks
truncate table [Static]
truncate table StaticB
truncate table Stations
truncate table Trade
truncate table SS1_Locked_Records

go

INSERT INTO AccRestrict (Code,User1,access) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'SELECT Code,User1,access from AccRestrict') as aa

INSERT INTO City (Code,Desc1) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'SELECT Code,Desc1 from City') as aa

INSERT INTO Classify (Class,Desc1) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'SELECT Class, Desc1 from Classify ') as aa

go

INSERT INTO ClassRoom (ClassNo,Seats,Desc1) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'SELECT ClassNo,Seats,Desc1 from ClassRoom ') as aa

INSERT INTO Course (CourseID,CourseT, CourseNo, CName, StartG,StartH, EndG ,EndH ,
Period, FromTime, ToTime,Open1,
EnterResult, Periods, Max1, Current1, Days, AllowAbs, Amount, Station, User1,
School, ClassRoom,Limit1,Limit2,Limit3,Limit4,Limit5,Regis ter1,Register2,Register3,Register4,Register5,
DateSG1,DateSG2,DateSG3,DateSG4,DateSG7,DateSH1,Da teSH2,DateSH3,DateSH4,DateSH7)
SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'SELECT CourseID,CourseT, CourseNo, CName, StartG,Str(StartH), EndG ,Str(EndH) ,Period, FromTime, ToTime,Open1,
EnterResult, Periods, Max1, Current1, Days, AllowAbs, Amount, Station, User1,
School, ClassRoom,Limit1,Limit2,Limit3,Limit4,Limit5,Regis ter1,Register2,Register3,Register4,Register5,
DateSG1,DateSG2,DateSG3,DateSG4,DateSG7,Str(DateSH 1),Str(DateSH2), Str(DateSH3),Str(DateSH4), Str(DateSH7)
from Course') as aa

------------------

INSERT INTO CourseT (CourseT, CName,Amount,Type1, Type2, PrintForm, Active, Remarks,
Days,Periods, AllowAbs, Class, ExamSort, StatSort, StatSortB, User1, StudList, Min_Age,
AllowDaysDistribution ) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'SELECT CourseT, CName,Amount,Type1, Type2, PrintForm, Active, Remarks,
Days,Periods, AllowAbs, Class, ExamSort, StatSort, StatSortB, User1, StudList, Min_Age,
AllowDaysDistribution from CourseT ') as aa
go
------------------
--truncate table exam
INSERT INTO Exam (StudID,Course,ExNo, ReExam, DateG, DateH ,Result, Result1, Result2, Result3,
Remarks, Station, User1, School, Sno)
SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'Select StudID,Course,ExNo, ReExam, iif(DateG>#01/01/1990# and DateG<#01/01/2010#, DateG ,null) as DateG1 ,
left(str(DateH),10) as DateH1 ,Result, Result1, Result2, Result3,
Remarks, Station, User1, School, Sno from Exam where SNo <> 33759085') as aa
go
-- select Top 20000 * from exam
Update Exam set dateh = '0'+DateH where substring(DateH,2,1)='/'
Update Exam set dateh = left(DateH,3)+'0'+substring(DateH,4,6) where substring(DateH,5,1)='/'
update exam set DateH = Substring(DateH,4,2) + '/' + left(dateh,2) + '/' + substring(dateH,7,4) where substring(DateH,4,2) > '12'

-- where SNo <> 33759085 ') as aa -- for Jizan only
------------------

set IDENTITY_INSERT Exam4 On
go

INSERT INTO Exam4 ([ID], StudID, Course, ExNo, DateG, DateH, ExpiryG, Expire, PermitID, Remarks, Address, Tel)
SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select ID, StudID, Course, ExNo, DateG, Str(DateH), ExpiryG, Expire, PermitID, Remarks, Address, Tel
from exam4') as aa
set IDENTITY_INSERT Exam4 off
------------------
go

INSERT INTO ExamDef (ExamNo,DateG, DateH, MaxNorm, MaxFail, CurrNorm, CurrFail, Status)
SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select ExamNo,iif(DateG<#01/01/1900#,#01/01/1900#, DateG), Str(DateH), MaxNorm, MaxFail,
CurrNorm, CurrFail, Status from ExamDef') as aa

------------------

INSERT INTO Nationality (Code,Desc1) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'SELECT Code,Desc1 from Nationality') as aa

go

-------------------
set IDENTITY_INSERT Payment On
-------------------

INSERT INTO Payment (PayNo, PayDateG, PayDateH,PayType,StudID,Course ,Amount,Result, Absence,
Group1, Printed ,DialogPrinted, OldPay,
OldSchool, WithDraw ,Station ,User1,Copied, School, Sno,CStartDay,List)
SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select PayNo, IIF(PayDateG<#01/01/1900#,#01/01/1900#,PayDateG) as PayDateG1,
left(Str(PayDateH),10) as PayDateH1,PayType,StudID,Course ,Amount,Result,
Absence, Group1, Printed ,DialogPrinted, OldPay,
OldSchool, WithDraw ,Station ,User1,Copied, School, Sno, CStartDay,List
FROM [payment]') AS aa where Len(PayDateH1)<=10 -- this last where is for Jizan = keep this since no need to such record (empty)

-------------------
set IDENTITY_INSERT Payment Off
go

------------------

------------------

INSERT INTO SecTopicSub SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select * FROM SecTopicSub ') AS aa

INSERT INTO SecTopicSub2 SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select * FROM SecTopicSub2 ') AS aa

INSERT INTO Spass (user1,UserName, access, [password], lastpchanged,
logged, [time], AutoList, IDFirst, AddMode, CourseFilter1, DialogPrint )
SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select user1,UserName, access, [password], lastpchanged,
logged, [time], AutoList, IDFirst, AddMode, CourseFilter1, DialogPrint FROM Spass ') AS aa

go

INSERT INTO Static SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select * FROM Static ') AS aa

INSERT INTO StaticB SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select * FROM StaticB ') AS aa

INSERT INTO Stations (Code,Desc1,PrinterTop1) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select Code,Desc1, iif(PrinterTop1<0,0,PrinterTop1) FROM Stations ') AS aa

------------------
--select * from stations
go

INSERT INTO groups SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select * FROM groups') AS aa

go

set IDENTITY_INSERT DailyTransaction On

INSERT INTO DailyTransaction (SNo,[Date],Amount,Posted,Batch) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select * FROM DailyTransaction') AS aa

set IDENTITY_INSERT DailyTransaction Off

go

INSERT INTO reGrouping SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select * FROM reGrouping') AS aa

go

set IDENTITY_INSERT rePayment On

INSERT INTO rePayment (NewPayNo, PayDateG,PayDateH,StudID,Amount,Station,User1, Course,CName,
PayType, PayNo,OldPay,OldSchool) SELECT * FROM
OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
'select NewPayNo, PayDateG,Str(PayDateH),StudID,Amount,Station,User1 , Course,CName,
PayType, PayNo,OldPay,OldSchool FROM rePayment') AS aa

set IDENTITY_INSERT rePayment Off

Go|||First observation, TRUNCATE TABLE is a complete wipe of the table... Nothing is ever left in a truncated table.

What kind of errors are you getting, and where are you getting them? The rest of this script looks Ok at least from a simple look.

-PatP|||after i implement GO, there was one problem, but i correct and the transaction works fine ,
thanks for help.

Ridwan|||how to close this Thread?|||I'm glad that you were able to find and fix your problem.

If implementing the GO between statements makes you happy, that's good, but it was definitely not part of your solution. While the use of the GO statements would not hurt anything, they would not help either, so removing those GO statements from your corrected script wouldn't change anything. There are a few SQL statements that must be the first or only statement in a batch (so they require the use of GO), but none of them are used in the script that you posted.

We don't normally close threads here at DBForums. It can be done, but it is pointless in nearly all cases.

-PatP

execute sql statements in text file

I am new to batch files. I have a text file that has several INSERT/UPDATE
sql statements. I would like to create a batch file to execute the sql
statements in the text file on my server. I am not having any success with
this. I thought I could create a batch file using osql that executes the sql
statements in text file but it's not working and I have no idea what I'm
doing wrong. Maybe there is a better way to do this? Any suggestions are
welcomed.
I am using the following:
osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> osql -S server -d database -E -I file pathname
Change -I to -i
Linchi
"fullertee via SQLMonster.com" wrote:
> I am new to batch files. I have a text file that has several INSERT/UPDATE
> sql statements. I would like to create a batch file to execute the sql
> statements in the text file on my server. I am not having any success with
> this. I thought I could create a batch file using osql that executes the sql
> statements in text file but it's not working and I have no idea what I'm
> doing wrong. Maybe there is a better way to do this? Any suggestions are
> welcomed.
> I am using the following:
> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
>|||Thanks Linchi
Sorry that was just a typo. It not working with the i.
osql -S server -d database -E -i file pathname
fullertee
Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>Change -I to -i
>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Error message? Can you post the exact command you execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fullertee via SQLMonster.com" <u21934@.uwe> wrote in message news:6066962294ae1@.uwe...
> Thanks Linchi
> Sorry that was just a typo. It not working with the i.
> osql -S server -d database -E -i file pathname
> fullertee
> Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>>Change -I to -i
>>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Thank you so much. Your question made me look at my bat file closer. I was
referencing the wrong database. Thanks! It's working now.
or Karaszi wrote:
>Error message? Can you post the exact command you execute?
>> Thanks Linchi
>> Sorry that was just a typo. It not working with the i.
>[quoted text clipped - 12 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

execute sql statements in text file

I am new to batch files. I have a text file that has several INSERT/UPDATE
sql statements. I would like to create a batch file to execute the sql
statements in the text file on my server. I am not having any success with
this. I thought I could create a batch file using osql that executes the sql
statements in text file but it's not working and I have no idea what I'm
doing wrong. Maybe there is a better way to do this? Any suggestions are
welcomed.
I am using the following:
osql -S server -d database -E -I file pathname
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1> osql -S server -d database -E -I file pathname
Change -I to -i
Linchi
"fullertee via droptable.com" wrote:

> I am new to batch files. I have a text file that has several INSERT/UPDAT
E
> sql statements. I would like to create a batch file to execute the sql
> statements in the text file on my server. I am not having any success wit
h
> this. I thought I could create a batch file using osql that executes the s
ql
> statements in text file but it's not working and I have no idea what I'm
> doing wrong. Maybe there is a better way to do this? Any suggestions are
> welcomed.
> I am using the following:
> osql -S server -d database -E -I file pathname
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1
>|||Thanks Linchi
Sorry that was just a typo. It not working with the i.
osql -S server -d database -E -i file pathname
fullertee
Linchi Shea wrote:[vbcol=seagreen]
>Change -I to -i
>Linchi
>
>[quoted text clipped - 5 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Error message? Can you post the exact command you execute?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fullertee via droptable.com" <u21934@.uwe> wrote in message news:6066962294ae1@.uwe...[vbcol
=seagreen]
> Thanks Linchi
> Sorry that was just a typo. It not working with the i.
> osql -S server -d database -E -i file pathname
> fullertee
> Linchi Shea wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1[/vbcol]|||Thank you so much. Your question made me look at my bat file closer. I was
referencing the wrong database. Thanks! It's working now.
or Karaszi wrote:[vbcol=seagreen]
>Error message? Can you post the exact command you execute?
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

Execute multiple statements at once...

I am not even sure if this is possible but I want to be able to excute several statements (or SPs) at once from inside a SPs (or any other method).

What I am doing is a I am taking data from a single column, multiple rows and making it into one row (i.e. data1 + data2 + data3....) But I am doing this to a total of 2.1 million individual rows and the result will be about 204k rows.... what I have written is basically a nested loop and it works fine but very slow... slow as in it has been running for 24 hours now and it is about 60% done... I need this to finish in under 36 hours preferably...

If I could handle more than one set of data at once (there will be no duplicates) I could speed up the process by how ever many I feel like working with...

So is there a way to execute a statement (sp or function) and go to the next statement without waiting for the first to finish?

Thanks Big Time... I hope this is possible.

OH.... SQL 2000 SP4 on Windows 2003.

You could open multiple connections from the client, but something tells me that there may be a better way to process your data.

Could you post the relevent schema and code?

|||

AFAIK within SQL server 2000 you do not have such a chance. On a single CPU, I doubt the case would be different under 2005 (but not an MSSQL expert).

However "24 hours" and even "hours" for 2.1 million rows processing to create a 204K data sounded to be very slow to me. Maybe if you select the data outside of SQL server with an isolation like uncommitted, prepare outside and do a single bulk load, it'd be much faster.

How do you do this? All in a single T-SQL batch?

|||

It is all within a single tsql SP.... It is extremely slow and I can't seem to get it to use more processor or memory...

I am thinking using a DTS package and working with multiple temp tables. Then combining all the temp tables in the end... it should be quicker that way and I might be able to push my server (which is enterprise with 12GB of ram and 2 processors)...

current schema is :

Final Table = ID_NUM (Key, char(10)), data (varchar(6000))

Working table is ID_NUM, date varchar(100), importance (smallint) - key is ID_NUM and DATA

Working temp table data varchar(100), importance smallint -- no key, always less than 200 rows.
Plus one variable @.data that is a varchar(6000)

Loop 1

Grab an ID_NUM

get list of data for ID_NUM into working table

Loop 2

Loop through till all rows have been added together or it goes over 6000 characters

@.data = Data + data + data

end loop 2

insert ID_NUM, @.DATA into final table

end loop 1

That is what I am going... no cursor, just while loops...

|||

William:

Where you say, "... date varchar (100), ... " for your "Working table" do you really mean "... data varchar (100) ... "?

Dave

|||

Yes that is what I mean...

sorry... working on a million things a once and don't always proof read..

|||

It sounds like you're just trying to concatenate all of your rows in the child table into a single row in a third table. If so, the following scenario may help.

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

You'll likely not get much parallelism in looping code that you desribed so if you can find a set-based method of processing then you'll be much better off.

|||

That actually looks good... the problem will be hitting the varchar limit.

I will have to see if I hit it or not...

What fun... lol...

Thanks for all the help.

|||

My apologies - I forgot you're on SQL Server 2000. The XML method of processing won't work in this situation. You still want a process where you can build the comma-separated list in a SET operation rather than one row at a time.

There's a method whereby you can assign a value to a variable in one row and use it in subsequent rows of an UPDATE statement. AFAIK, it's undocumented but it works in SQL 2005 as well so you're safe for a little while.

The pseudo-code would look something like this:

-- Place all of the data into a work table

CREATE TABLE #WorkTable (ID_NUM char(10), data varchar(6000), importance smallint, IdentCol INT IDENTITY, PRIMARY KEY(ID_NUM, IdentCol)

INSERT INTO #WorkTable (ID_NUM, data, importance)

SELECT ID_Num, data, Importance

FROM SourceTable

ORDER BY ID_NUM, Importance

-- Update work table. Each row has the data of itself + the row preceding it when there is a matching ID_NUM

DECLARE @.ID_NUM CHAR(10)

DECLARE @.data VARCHAR(6000)

UPDATE #WorkTable

SET @.data = data = CASE WHEN ID_NUM <> @.ID_NUM THEN data

ELSE @.data + data

END

,@.ID_NUM = ID_NUM

INSERT INTO FinalTable(ID_NUM, Data)

SELECT ID_NUM, MAX(DATA)

FROM #WorkTable

GROUP BY ID_NUM

In case the pseudo-code doesn't work, here's a working sample for Adventureworks database:

CREATE TABLE #JobTitles (job_id INT, job_desc VARCHAR(100), employees VARCHAR(1000), EmployeeName VARCHAR(100), PRIMARY KEY (job_id,

EmployeeName))

DECLARE @.job_id INT, @.EmployeeNames VARCHAR(1000)

INSERT INTO #JobTitles(job_id, job_desc, EmployeeName) SELECT J.job_id, J.job_desc, E.lname +', ' + E.fname AS EmployeeName FROM jobs J INNER JOIN employee E ON J.job_id = E.job_id

SET @.job_id = -1 -- Initialize this to a number that does not exist

-- in the list of values.

UPDATE #JobTitles

SET @.EmployeeNames = employees = CASE

WHEN @.job_id <> job_id THEN EmployeeName

ELSE @.EmployeeNames + '; ' + EmployeeName

END

, @.job_id = job_id

-- Grab the largest row of each grouping.

SELECT job_id, job_desc, MAX(employees) AS EmployeeList FROM #JobTitles GROUP BY job_id, job_desc ORDER BY job_id

DROP TABLE #JobTitles

|||

William:

Here is yet another example. I mocked this up with 2.1 million rows. I was able to get this to run in about 3 minutes and 20 seconds. This problem is definitely easier to solve with 2005 than 2000 and there has to be a better way of doing this, but this example might also help you see some direction; I hope it is helpful.

Dave

I used these two tables and faked the data:

create table dbo.final
( [key] char (10) not null,
[data] varchar (6000) not null
)

create table workingTable
( ID_Num varchar (10) not null,
[data] varchar (100) not null,
importance smallint not null,

constraint pk_mockInput primary key (ID_Num, [data])
)

insert into workingTable
select convert (varchar (5), a.iter),
'{ ' + convert (varchar (5), b.iter) + ' }'
+ replicate ('-', 1 + b.iter % 29 ) + '-->',
b.iter
from small_iterator a (nolock)
inner join small_iterator b (nolock)
on a.iter <= 21300
and b.iter <= 40 + a.iter % 119
and b.iter <= 200
order by a.iter,
b.iter

update statistics workingTable
exec sp_recompile workingTable

I would prefer not to use the function because there is a fair amount of overhead associated with functions; nonetheless, I implemented the method using the following function:

alter function dbo.assembleData
( @.arg_key varchar (10),
@.arg_minData varchar (100),
@.arg_maxData varchar (100)
)
returns varchar (6000)
as

begin

declare @.retValue varchar (6000)
set @.retValue = ''

select @.retValue = @.retValue + [data]
from workingTable
where [id_num] = @.arg_key
and [data] >= @.arg_minData
and [data] <= @.arg_maxData

return ( @.retValue )

end

I then tested out the overall procedure with this query:

--truncate table dbo.final

-- The whole thing takes about 3 minutes and 20 seconds
create table #sequencer
( [key] varchar (10) not null,
[data] varchar (100) not null,
importance smallint not null,
seq integer not null,
siz integer not null,

constraint pk_#sequencer primary key ([key], [data])
)

insert into #sequencer
select a.id_num,
a.[data],
a.importance,
count(*) as seq,
sum (datalength (b.[data])) as siz
from workingTable a
inner join workingTable b
on a.id_num = b.id_num
and b.[data] <= a.[data]
group by a.id_num, a.[data], a.importance
order by a.id_num, a.[data]

insert into dbo.final
select [key],
dbo.assembleData ( [key], min_data, max_data )
from ( select [key],
min ([data]) as min_data,
max ([data]) as max_data,
min (seq) as min_seq,
max (seq) as max_seq,
case when siz = 0 then 0
else siz - 1
end / 6000 as segment
from #sequencer
group by [key],
case when siz = 0 then 0
else siz - 1
end / 6000
) x

drop table #sequencer

select top 20 *
from dbo.final

|||

I will give that method a try...

I have been trying all kinds or things including DTS with multiple connections

all have been unsuccessful in speedy results... my last attempt took about 10 hours

|||

THAT WORKED GREAT!!!!!

My job now completes in 1 min 43 sec.... HUGE improvement...

Thanks.

|||

Great you solved it.

Would you tell me which method you used (I know that not the one I said:). I'm asking because I'm not SQL server oriented and for the last few days seriously diving into SQL server. Before I was using it "superficially". Though I was impressed with T-SQL in 2000, 2005 made me an addict:)

|||I used the solution posted by Jared Ko

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?

execute DBCC on linked server

Hi,

How do you execute 'DBCC' statements or 'EXEC ...' against a linked server (SQL 2000 sp4) ?

Regards,

A.E

EXEC [linkedserver].master.dbo.sp_executesql N'DBCC USEROPTIONS'

|||

Thanks Mark, your suggestion works fine but now I have another problem when I do the following for example:

INSERT [sometable] EXEC [LinkedServer].pubs.dbo.sp_executesql N'EXEC sp_helpfile'

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
The transaction active in this session has been committed or aborted by another session.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I followed the instructions in article 839279 but still get the error, any ideas?. I don't need DTC transaction support, is there a way to get the results without involving MSDTC ?

Regards,

A.E

|||

If you've tried everything in

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

then there's not a lot more I can add.

BTW, the last time I saw this, it was fixed simply by specifying
SET XACT_ABORT ON before the EXEC.