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 a store procedure

Hi,

I Have the next problem:

I have a store procedure in my db which is called from an aplication (developed in Java). That sp contains a cursor which updates regs from a table. After calling from the java application I notice that some regs of the cursor have not entered in it, i mean if the sp executes 200 iterations in the cursor only 150 have worked. But all the iterations enter when i call the sp from the query analyzer!!! and it takes more time too.

Does anybody know something about it???Hi,

I Have the next problem:

I have a store procedure in my db which is called from an aplication (developed in Java). That sp contains a cursor which updates regs from a table. After calling from the java application I notice that some regs of the cursor have not entered in it, i mean if the sp executes 200 iterations in the cursor only 150 have worked. But all the iterations enter when i call the sp from the query analyzer!!! and it takes more time too.

Does anybody know something about it???

I've never heard of Java having this effect on anything and find it rather hard to believe. Have you tried running profiler and seeing what calls are being made. You should do this and compare the results of one being ran in QA and one being ran with your Java Application.

Also, you should really get rid of the cursor. Look on SQLTeam.com (search the forums) for cursors. You will find many set-based and while loop methods to avoid using a cursor.

Executing a queries contained in a column of a table IN SQL Server.

Hi,
I have a Table that contains SQL queries in one of its columns.I need to execute those query and finally want to retrive the result in another table i cannot use Cursors,Its working extremely slow, near about 1 min only for 2000 rows.Please tell me how can i minimize my time.Or any solution (without cursor) for such problem.Please help its very urgent.
e.g Say TableContainQuery(PKID,QueryField)
Now above table have 10000 of records,
I need to retrieve data from by executing queries contain in above table. I am using SQL Server.

Regards,
Dheeraj

Using SQL Server, queries that are slow are generally slow because there are not proper indexes on the tables involved.
Have you ensured that there are useful indexes on the tables involved?|||Yes,
There is a primary key in the table I suppose indexing will automatically done.
Regards,
Dheeraj Verma|||No, indexing, other than indexes you created, willnot automatically be done. If all your queries use only columns involved in the primary keys, then you are fine. However, if your query references other columns in joins or where clauses, you must add indexes yourself.

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

ExecuteOutOfProcess calling a transactional child package causes Access is Denied.

I have a master package that contains an Execute Package Task whose ExecuteOutOfProcess flag is True, and that calls a child package whose TransactionOption = Required. The job is running in Sql Agent, and the step that calls the master package is configured to run under a certain domain account that is not in the local Administrators group. With this, I get the following:

messageText: Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_Various_TransactionalChannels.dtsx". Access is denied.

When I add the domain account to the local Administrators group, this error does not occur. From a blog entry, I read that when a child package is executed out of process, the resultant OS process is called dtshost.exe (http://blogs.conchango.com/jamiethomson/comments/1414.aspx). Do I simply need to give my domain account permission to spawn this process? If so, what permission is it? Is there a group that contains this permission?

Is it possible that the domain account does not have access to "C:\program files\microsoft sql server\90\dts\Packages\ETL\"

-Jamie?

|||Unfortunately, no. The account has full control over that path.

Execute SQL from File: How Can I process the record set?

I want to be able to pass the location of a file (contains SQL to be executed) to my package at run time. To do this I was going to override the connection string for the file.

I've created a 'Execute SQL Task' that opens the sql script and stores the full result set into an variable (system.object). I can execute this and it works fine i.e turns green :).

However I can't work out how to get the data back out of the variable. I have found a doc on SQLIS (The ExecuteSQL Task) that explained how to get the data in to a variable but didn't tell me how to process the data afterwards. There is another article on there that shows how to shred a recordset (Shredding a Recordset) but this example uses an OLE DB source and the 'Recordset Destination' object. This would work but but the only options are sql from a variable or the option to type in the command.

I really have two questions here.

1. Using the first method how can I pass the data stored in the variable into a data flow so that I can use it.

2. using the second method, how can I pass the SQL into a variable at runtime from a file?

Has anyone got examples of how they read SQL from a file and process the data without having to hard code the sql or sql file name in the package.

#1) If you have data in an object and want to use it in a data-flow then you're going to need to loop over the records in the object and add them to the pipeline. Its custom source adapter time!!!

Here's how you do it in a script task: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx I dare say you can take this code and adapt it to use it in a script component. I have to say, I haven't actually tried it.

#2) Again you may need a custom/script task to do this. I don't have time to look at this now but I'll try later. It shouldn't be too difficult, just use System.IO namespace.

-Jamie|||To configure the file connection at runtime. You need to use expressions. On the properties of the data flow task select expressions. In here you can set the connection string property of the flat file to the variable (containing a filename).

Execute Package Task Failed when deployed to server..

Hi,

I have a package that contains an Execute Package Task... it works fine when on my local machine and on our test-server.
After deploying the SSIS solution to our customers server, I open the project in Visual Studio and run the mainPackage in debug mode and the Task fails when trying to execute the package.. if I remove everything in the childpackage it still fails... the path to the package is correct and I know that I have rigths to execut it. I do not use any package security/encryption..
If i create a new package in the project and create a new fileconnection to it the Execute Package Task executes correctly, with the exact same content as the original package...

So my guess is that something in the FileConnetion object is set wrong, but i cannot understand what it is. The errormessage i get is something like this. "Execute Package Failed. Could not execute package".

Anyone had the same problem as I? I′m grateful for any help!

/erik

Hi Erik,

This could be the package ProtectionLevel property. You can check if this is the problem by setting it to EncryptSensitiveWithPassword and then supplying a password. You will need to pass the password in using the /Decrypt switch in DTExec or by setting it in an SSIS job step definition.

Hope this helps,
Andy

|||Sticky|||I still do not know what caused the problem. I "solved" it by creating a new package with the exact same content. I tried to do as Andy suggested but it dit not help...|||

Hi Erik,

Thanks for the feedback - glad you got it working.

Andy