EXECUTING A STRING (URGENT HELP PLEASE)

Dear friends,
i have a problem here that as much as i go through it looks worth.
I have build a dynamic query with a string like below:
[blue]
CREATE PROCEDURE PROC1
AS
DECLARE @.TempQString nvarchar(1000)
SET @.TempQString = 'DECLARE @.resultvalue int'
SET @.TempQString =@.TempQString + 'EXEC @.resultvalue=[MyStoredProcedure]'
EXEC sp_executesql @.TempQString
GO
[/blue]
now in the body of my main stored procedure (PROC1) i want to get the
return value if the [MyStoredProcedure] that was executed through a string!!!!!!!!!!
i've tried to insert the return value in a temp table (#table)
but this also didnt work, cuz out of the string execution my temp table was dropped!! also i cant use a global temp table (##table)
because many users may execute the PROC1 at the same time!!
[red]PLEASE HELP THIS IS VERY IMPORTANT FOR ME[/red]

Firstly I have to question why you are execution your stored procedures in this way as it doesn't seem to be particularly efficient. Is there another way you can perform the task?

If you still need to use this method then try the example below.

Chris

Code Snippet

CREATE PROCEDURE PROC1

AS

DECLARE @.TempQString nvarchar(1000)

DECLARE @.resultvalue INT

SET @.TempQString = 'EXEC @.resultvalue=[MyStoredProcedure]'

EXEC sp_executesql @.TempQString, @.Parameters = N'@.resultvalue int output', @.resultvalue = @.resultvalue OUTPUT

RETURN ISNULL(@.resultvalue, -1)

GO

sql

executing a dymamically built string with an OUTPUT parameter

Hi friends,

I have a dynamically built string that i need to execute and set a parameter to. I have come accross help in the books online but I need to set a variable to the answer of the dynamically executed string. It looks as follows:

DECLARE @.sRCDQueryString nvarchar(1000)
DECLARE @.sActivityName varchar(100)
DECLARE @.sRCDParmDefinition nvarchar(500)
DECLARE @.lGLCodeID int
DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID = 1--391
SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT ' + @.sActivityName +
' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'
SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';
EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

When I execute this code, I get a value by the line in red but it doesn't seem to be allocating a the result correctly to @.rRCDUnitValue since this variable is null

How can i get the result correctly allocated to the variable?

Regards

Use the following statement, you missed the value assignment,

Code Snippet

DECLARE @.sRCDQueryString nvarchar(1000)

DECLARE @.sActivityName varchar(100)

DECLARE @.sRCDParmDefinition nvarchar(500)

DECLARE @.lGLCodeID int

DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID =1--391

SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT @.Value=' + @.sActivityName +

' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'

SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';

EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

|||

You are declaring a parameter, but that parameter is not being used at all inside the dynamic statement. See this example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.orderid int

declare @.order_total money

set @.sql = N'select @.order_total = sum(quantity * unitprice * (1.00 - discount)) from dbo.[order details] where orderid = @.orderid'

set @.orderid = 10250

exec dbo.sp_executesql @.sql, N'@.orderid int, @.order_total money OUTPUT', @.orderid, @.order_total OUTPUT

select @.order_total

go

AMB

|||

You got the answers for how to fix yoru code. But why do you need dynamic SQL in the first place? Are you aware of the risks and performance problems (benefits in some cases) with dynamic SQL code?

|||Hi Manivannan,

The @.sActivityName is actually the name of an unknown column that is looked up on another table, wich has been pivoted on the current table i'm trying to do a lookup on.

Thanks for the response
Mike
|||Hi Umachandar,

Yes I am aware of the performance knock but I have no choice in this matter to use a dynamic string since the columns I need to lookup/use are unknown at the point of execution since they are looked up from another table..

Essentially they are the result of a table that had been pivoted.

Regards
Mike
|||Ahhh, Thank you indeed sir, you have showed me the mistake and I appreciate it coz my variable now returns a value.

Kind Regards
Mike
sql

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 SSIS package from a ASN.NET 2.0

Dear Friends,

I have a SSIS project (You can see in my blog) with the main parameters, StartDate and EndDate.

How can I refresh this parameters? Where I save it?

Use a table in database with the fields Startdate and Enddate, and link it to my variables in SSIS?

Use the package configuration of SSIS?

Give me some tips!

regards!

How often are they updated? If they change frequently, I'd store it in a database, and use an Execute SQL task to populate variables in my package. If they don't change often, I'd use configurations.|||

jwelch,

Change each day... I will run this package one or two time per day, and the parameters receive the startdate and enddate. For almost the case, because I will import data for each date, the startdate will be Currentdate-1 and the enddate will be CurrentDate, but could be changed for some cases by the user administrator.

The user administrator sometimes would require to import dates for a different interval of dates...

Do you thinks is better to save in a table in database?

Thanks!

|||Yes, that's how I've implemented similiar functionality in the past.

Execute Script component after 2 sequence finished with sucess

Dear Friends,

In the control flow, I have more than one sequence containers, and I have a script component that I want to be executed only when of 2 last sequence finished with sucess... these 2 sequences does not have any relation with each other...

Regards!

If you want the script task to be executed after both sequences complete successfully, add a precedence constraint connecting each sequence container directly to the script task. If you want the script task to be executed when either of the sequence containers complete successfully, do the same, but set the LogicalAnd property of the precedence constraints to False.|||

Dear jwelch,

I already tried it but didnt work...

Check the image on the image in my blog:

http://pedrocgd.blogspot.com/2007/06/ssis-temporary-image-to-msdn-forum.html

Thanks!

|||The dotted lines for the precedence constraints show that you have set it to be an OR condition. double click on one of those constraints and set it to AND which means both have to succeed before the next task is executed.|||

I made a mistake... I already had this works and I was confusing!!!

Thanks both!!

Execute Dos Commands in T-SQL Script

Friends,
I know there is a way to execute DOS commands inside T-Sql script, but can't
seem to find it. For example: using the MOVE command inside a stored
procedure to move .txt files into another directory after they've been
imported.
Can someone point me in the right direction?
Thanks ...The thing you are looking for is called xp_cmdshell
Have a look in the BOL for the details. You need to be symin to run this
command
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:BBD58F20-E45C-418D-9B5A-E1221B81BE05@.microsoft.com...
> Friends,
> I know there is a way to execute DOS commands inside T-Sql script, but
> can't
> seem to find it. For example: using the MOVE command inside a stored
> procedure to move .txt files into another directory after they've been
> imported.
> Can someone point me in the right direction?
> Thanks ...
>|||Thanks, Greg ... I did run into that one, but wasn't getting it to work righ
t
(am running it on my own version of SQL Server so shouldn't be having an
Admin problem).
Now that I'm sure that's what I need to pursue, I'll give it another try.
"GregO" wrote:

> The thing you are looking for is called xp_cmdshell
>
> Have a look in the BOL for the details. You need to be symin to run th
is
> command
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
> news:BBD58F20-E45C-418D-9B5A-E1221B81BE05@.microsoft.com...
>
>|||Basic question I assume is also true, you are a SysAdmin on the server?
Where are you trying to make calls to? If it is on a network share, then
your SQL Server service account needs permissions there. If you are running
under a local system account, then you will have to grant the machine
account access to the share.
Here is a basic statement that should return values for you:
exec xp_cmdshell 'dir c:'
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:CC18C513-D5DB-44DB-B849-BFB29965B1A8@.microsoft.com...
> Thanks, Greg ... I did run into that one, but wasn't getting it to work
> right
> (am running it on my own version of SQL Server so shouldn't be having an
> Admin problem).
> Now that I'm sure that's what I need to pursue, I'll give it another try.
> "GregO" wrote:
>