Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

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 SP

I have stored procedure with parameters.
Can I exec stored procedure somehow with result set of select statement, for
example:
exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
Or I must declare each parameter:
declare @.par1 int,@.par2 int,@.par3 int
SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
and then exec my procedure:
exec dbo.myProcedure @.par1,@.par2,@.par3
In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.
lp,S>> Can I exec stored procedure somehow with result set of select statement,
No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.
Anith|||To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>|||Hi
If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @. to the names!)
If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.
John
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>

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 OPENQUERY

Hi,

I'm trying to execute an openquery statement from SQL Server 2005 against a linked server AS2005 (both on same machine) from Management Studio.

If I use a one connection with full rights I have no problem. But if I use the specific connection for that application I got this error:

OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2" returned message "The following system error occurred: A specified logon session does not exist. It may already have been terminated. .".

Msg 7303, Level 16, State 1, Line 5

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2".

"Ad hoc data-mining query" is checked.

MSOLAP Provider has "Allow inprocess" checked.

Code Snippet

SELECT * FROM OPENQUERY(ASLOCAL2,'SELECT non empty {[Measures].[Prices Avg] ,[Measures].[Prices Max] ,[Measures].[Prices Min] } ON COLUMNS,non empty [Tbl DW Dim Type].[Name].&[1] ON ROWS FROM [DW DEV] ')

Any ideas ?

Thnx.

Have you tried running a profiler trace against SSAS while trying to run the OPENQUERY() ?

I am wondering if it is a permissions issue for the account that the application is using for the connection.

|||The only difference between these tow accounts is that one account is Windows account and the other one is Sql server account.|||

Well that is probably your issue. SSAS only supports windows authentication. You would need to have the linked server setup to authenticate using the current login's security context, which works fine for windows accounts, but if you use a sql account, SQL Server will fall back to authenticating against the SSAS server using the account that the SQL Server is running under, which probably does not have permissions to query the cubes.

So your choices are to either only use windows accounts against the linked server or to make sure that the account that SQL Server is running under has access to the appropriate objects in SSAS.

|||

You're right.

I changed the way that the (web)application is connecting to the Sql server (until now I used sql account) and now I am using an windows account.

Executing Dynamic SQL with update

I have a temp table #Temp(id int, statement varchar, result int)
The statement column contains a prebuilt sql select statement e.g
id statement
Result
1 select count(*) from books where authorname like 'A%'.
2 select count(*) from books where authorname like 'J%'.
I want run an update statement on the table so that i can set the result
column to the result of the select statement in the statement column.
e.g. update #temp
set result = [statement result]
I want to aviod cursors. Can this be done?
Please help.This is in general, a poor approach. You can use certain undocumented
procedures ( in SQL 2000 ) to get this done, but it is complex, error prone
and rarely worth it.
Instead of having SQL statements as data values & updating #temp tables,
consider using a view. Alternatively, depending on tables & wild card
patterns involved, in some cases you might be able to resolve the problem
with a single query with CASE.
If you want a workable solution, pl. refer to www.aspfaq.com/5006 and post
the required information along with a brief explanation of your
requirements.
In case you are wondering how to get the scalar result of a SELECT statement
into a variable dynamically, refer to the procedure sp_ExecuteSQL in SQL
Server Books Online.
Anith

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

sql

executing analysis services query via openrowset

Can you kindly tell me what settings are required to execute an MDX statement via openrowset.

Currently I am executing it by impersonating my user (sql user) as "sa" account and everything goes ok.

The following is the query i am using

SELECT "[Dim Agent].[Dim Agent].[Dim Agent].[MEMBER_CAPTION]" AS AgentNumber,

"[Dim Application].[Dim Application].[Dim Application].[MEMBER_CAPTION]" AS ApplicationId,

ISNULL("[Dim Event].[Dim Event].&[1]",0) AS PropertyViews,

ISNULL("[Dim Event].[Dim Event].&[2]",0) AS ScheduleAShowing,

ISNULL("[Dim Event].[Dim Event].&[3]",0) AS ContactMe

FROM OpenRowset('MSOLAP.3',

'DATASOURCE=RIGGINS2\LFDB2; Initial Catalog=PicassoLnfWebMetric;Integrated Security=SSPI',

'SELECT {[Dim Event].[Dim Event].&[1],[Dim Event].[Dim Event].&[2],[Dim Event].[Dim Event].&[3]} ON COLUMNS,

NON EMPTY([Dim Agent].[Dim Agent].[Dim Agent] * [Dim Application].[Dim Application].[Dim Application]) ON ROWS

FROM [Lnf Web Metric] WHERE {([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].&), ([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].[All].UNKNOWNMEMBER)}')

Can you kindly let me know what do i need to do to run this query by impersonating as some windows account?

Warm regards,

Sudhir

I don't think you can do this using OpenRowset(). You could try setting up a linked server and then using OpenQuery(). There are options when you set up a linked server that let you specify a security context. If your SQL and AS services are on the same machine you should be able to get this working, if they are on separate machines you would need to configure Kerberos authentication. (There are various whitepapers available on how to do this)|||can you redirect me to some whitepapers?|||

On configuring Kerberos? sure http://support.microsoft.com/kb/917409 & http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx - specifically relates to AS2005

On adding a linked server http://msdn2.microsoft.com/en-us/library/aa936675(SQL.80).aspx, you also have to make sure with AS that the provider is set to run In-process.

Executing a Stored Procedure in a loop

Hi There!

I need to execute a stored procedure for each row returned from a Select statement for now the only way for me to do it is by creating a cursor and loop thought the result and passing the parameters for the stored procedure and call it in the loop.

My question is. Is there any way I can put the 'execute [spStoredProcedure] param1, param2' with in the select statement so I won't need to create a cursor and manually call the stored procedure for each record?

Hi Lazer,

If you're using 2000, have you had a look at functions? EG:

select fn_MyFunction(a.Column1)

from MyTable

where something = something

Or, if using 2005, have you had a look at CROSS/OUTER APPLY?

select *

from MyTable a t

cross apply fn_MyFunction(t.Column1) as b

The difference being that cross/outer apply must be a table valued function...

Cheers

Rob

|||

Hi Rob,

i using 2000, i want to show a record set for user with the data only have monday date. even the date in database is not monday but i would like to take the early monday date. so can i have a sample how this loop function work?

select fn_MyFunction(a.dtanswerdate)

from answer

where something = something

if i want to update or change the date into monday date for display.

regards

terence chua

Executing a Stored Procedure in a loop

Hi There!

I need to execute a stored procedure for each row returned from a Select statement for now the only way for me to do it is by creating a cursor and loop thought the result and passing the parameters for the stored procedure and call it in the loop.

My question is. Is there any way I can put the 'execute [spStoredProcedure] param1, param2' with in the select statement so I won't need to create a cursor and manually call the stored procedure for each record?

Hi Lazer,

If you're using 2000, have you had a look at functions? EG:

select fn_MyFunction(a.Column1)

from MyTable

where something = something

Or, if using 2005, have you had a look at CROSS/OUTER APPLY?

select *

from MyTable a t

cross apply fn_MyFunction(t.Column1) as b

The difference being that cross/outer apply must be a table valued function...

Cheers

Rob

|||

Hi Rob,

i using 2000, i want to show a record set for user with the data only have monday date. even the date in database is not monday but i would like to take the early monday date. so can i have a sample how this loop function work?

select fn_MyFunction(a.dtanswerdate)

from answer

where something = something

if i want to update or change the date into monday date for display.

regards

terence chua

Executing .sql file from vb.net

Hi,

I have a .sql file that contains sql statement to create tables. Is there a way where I can execute the codes in this file creating the tables?

Thanks

EDIT

The code is in C# so use the code converter in the second link.

http://www.c-sharpcorner.com/UploadFile/mahesh/CreatingDBProgrammaticallyMCB11282005064852AM/CreatingDBProgrammaticallyMCB.aspx

http://www.carlosag.net/Tools/CodeTranslator/Default.aspx

|||

Hi,

Thanks for the link. Will try it out and post the outcome.

|||If it does not work it will be related to C# to VB conversion because I have helped a user clone a database and all the objects with ExecuteNonQuery.|||

Hi,

Yup the 'ExecuteSQL' did not seem to work, so I just removed that entire function and directly called an 'ExecuteNonQuery' and it worked fine.

Thanks!

Executing .sql file from vb.net

Hi,

I have a .sql file that contains sql statement to create tables. Is there a way where I can execute the codes in this file creating the tables?

Thanks

EDIT

The code is in C# so use the code converter in the second link.

http://www.c-sharpcorner.com/UploadFile/mahesh/CreatingDBProgrammaticallyMCB11282005064852AM/CreatingDBProgrammaticallyMCB.aspx

http://www.carlosag.net/Tools/CodeTranslator/Default.aspx

|||

Hi,

Thanks for the link. Will try it out and post the outcome.

|||If it does not work it will be related to C# to VB conversion because I have helped a user clone a database and all the objects with ExecuteNonQuery.|||

Hi,

Yup the 'ExecuteSQL' did not seem to work, so I just removed that entire function and directly called an 'ExecuteNonQuery' and it worked fine.

Thanks!

sql

Executin SQL statement from variable in functions

Hello All

Within my function depends on value forming a SQL statement

As Example

set @.SqlString = 'select ' + @.ColumnName + ' from ' + @.tableName + ' where ' + @.whereCond

How can i execute this Statement. within function

Thanks in advance

RK

> Within my function depends on value forming a SQL statement

Sorry, no can do, you cannot execute dynamic SQL in a function. Maybe you

meant to use a stored procedure, not a function.

|||

I can change function to stored procedure within storedprocedure how can i execute a dynamic SQL

My requirement is to open cursor with dynamic SQL.
table name, columns list and conditions are depends on user parameters

|||

> My requirement is to open cursor with dynamic SQL.

You need a cursor and dynamic SQL. Fantastic. First, please read this:

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

Then, if you can give better specs, maybe we can help.

http://www.aspfaq.com/5006

|||looks like the site was down (http://www.smmarskog.se/dynamic_sql.html)

is there a way i can a open cursor with dynamic sql.

Our requirement is we have set of related tables with different data but all integer columns

We are trying to create few statistical functions/procs So user can pass Table name and column name and optionally any conditions, so that my function/procs returns results.

|||

Sorry, typo

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

> looks like the site was down

ExecuteSQL task has changed

Since the last IDW.

The column "ParameterName" has been added to the ParameterMapping tab of the ExecuteSQL task.

I enter a statement of

SELECT * FROM TABLE WHERE COLUMN = ?

I map ? to a input variable. The default name of the parameter supplied is "NewParameterName"

My task now fails with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * FROM TheBigOne WHERE HostName = ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
What parameter? The one the task supplies?

Allan

Allan
I was able to get passed this by putting ? in the paramter name column.

Norman P.|||I cannot get it to work even using that and it would kinda make no sense either because what if I had

SELECT X FROM TABLE WHERE DATE BETWEEN ? AND ?

What would be the Parameter Names?

Allan|||Ok so i just got it to work

say I have a statement of

SELECT AddressID
FROM Person.Address
WHERE City = ?

The parmeter name should be @.City

I could not find this anywhere in the docs.

Allan|||Allan,

Are you using a managed provider?

ash|||Allan
I tried it your way which works but also used ? in both paramter names and it also worked provided the paramters where in the correct order.

Norman P.

ExecuteSQL Fails from Variable

I am executing the following statement to setup a database:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

If this is formed inside a script task, assigned to a variable and then executed from the variable it fails. If I past it into the Execute SQL Task as a string it succeeds. Any ideas on where the difference may be? I have set a breakpoint and verified that the variable is being filled in correctly.

I get this error:

SSIS package "BuildTemplates.dtsx" starting.

Error: 0x0 at Create Database: Incorrect syntax near the keyword 'CREATE'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0xC002F210 at Create Database, Execute SQL Task: Executing the query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

" failed with the following error: "Incorrect syntax near 'GO'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I suspect what is going on is that the \ in the paths need to be doubled. I did that and the problem appeared to go away.

Thanks,

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.